ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_Upd]
@p_REQ_ID varchar(15),
@p_REQ_CODE	nvarchar(100)  = NULL,
@p_REQ_NAME	nvarchar(200)  = NULL,
@p_REQ_DT	varchar(30) = NULL,--CONVERT(DATETIME, @p_REQ_DT, 103)
@p_REQ_TYPE	int = NULL,
@p_REQ_CONTENT NVARCHAR(1000)=NULL,
@p_REQ_REASON NVARCHAR(500)=NULL,
@p_PL_REQ_ID VARCHAR(15),
@p_TOTAL_AMT	decimal(18,2) = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(12)  = NULL,
@p_CREATE_DT	varchar(30) = NULL,--CONVERT(DATETIME, @p_CREATE_DT, 103)
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	varchar(30) = NULL,--CONVERT(DATETIME, @p_APPROVE_DT, 103)
@p_BRANCH_DO VARCHAR(15)=NULL,
@p_BRANCH_CREATE VARCHAR(15)=NULL,
@p_DEP_CREATE VARCHAR(20)=NULL,
@p_REQ_PARENT_ID VARCHAR(20)=NULL,
@p_USER_REQUEST VARCHAR(15)=NULL,
@p_SIGN_USER VARCHAR(15)=NULL,
@p_Record_ID_1 VARCHAR(15)=NULL,
@p_Record_ID_2 VARCHAR(15)=NULL,
@p_Record_ID_3 VARCHAR(15)=NULL,
@p_ListGood XML,
@p_ListTrREQFile XML,
@p_ListPlGood XML
AS
DECLARE @TEMP TABLE
			(
				[KEY] varchar(15),
				[REF_ID] varchar(15),
				[TYPE] varchar(50)
			)
	IF(@p_REQ_CODE IS NULL OR @p_REQ_CODE ='')
	BEGIN
		SELECT 'REQ-00002' Result, '' REQ_ID, N'Mã phiếu yêu cầu bắt buộc nhập' ErrorDesc 
		RETURN '0'
	END

	--IF NOT EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE [REQ_CODE] = @p_REQ_CODE AND REQ_ID <> @p_REQ_ID)
	--BEGIN
	--	--SELECT ErrorCode Result, '' REQ_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'REQ-00002'
	--	SELECT 'REQ-00002' Result, '' REQ_ID, N'Mã phiếu yêu cầu mua sắm chưa tồn tại trong hệ thống' ErrorDesc 
	--	RETURN '0'
	--END 
  DECLARE @sErrorCode VARCHAR(20)
  BEGIN TRANSACTION

		UPDATE dbo.TR_REQUEST_DOC
		SET REQ_CODE=@p_REQ_CODE,REQ_NAME=@p_REQ_NAME,REQ_DT=CONVERT(DATETIME, @p_REQ_DT, 103),REQ_CONTENT=@p_REQ_CONTENT,REQ_REASON=@p_REQ_REASON,
		RECORD_STATUS=@p_RECORD_STATUS,PL_REQ_ID=@p_PL_REQ_ID,MAKER_ID=@p_MAKER_ID,TOTAL_AMT=@p_TOTAL_AMT,NOTES=@p_NOTES,CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103),USER_REQUEST=@p_USER_REQUEST,DEP_CREATE=@p_DEP_CREATE,REQ_PARENT_ID=@p_REQ_PARENT_ID,

		SIGN_USER =@p_SIGN_USER,
		BRANCH_DO = @p_BRANCH_DO
		WHERE REQ_ID=@p_REQ_ID
		-- UPDATE TOTAL_AMT MASTER
		UPDATE dbo.TR_REQUEST_DOC
		SET TOTAL_AMT=(SELECT SUM(ISNULL(TOTAL_AMT,0)) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_ID) WHERE REQ_ID =@p_REQ_ID
		IF @@Error <> 0 GOTO ABORT
			
		DELETE FROM dbo.TR_REQUEST_DOC_PL_DT WHERE REQ_DOC_ID = @p_REQ_ID
		DELETE FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID
		DELETE FROM dbo.TR_REQUEST_COSTCENTER WHERE REQ_ID=@p_REQ_ID

		-- LƯỚI HÀNG HÓA THEO TỜ TRÌNH
		DECLARE @lstPLDT TABLE(
			REQPL_DT_ID VARCHAR(15),
			REQ_DOC_ID VARCHAR(15),
			PL_REQDT_ID VARCHAR(15),
			GD_ID VARCHAR(15),
			SUP_ID VARCHAR(15),
			HH_ID VARCHAR(15),
			TRAN_TYPE_ID VARCHAR(15),
			DES_GOOD NVARCHAR(500),
			QUANTITY_PL DECIMAL(18, 0),
			PRICE_PL DECIMAL(18, 2),
			CURRENCY NVARCHAR(50),
			EXCHANGE_RATE DECIMAL(18, 2),
			TAXES DECIMAL(18, 2),
			TOTAL_AMT_ETM DECIMAL(18, 2),
			REASON_CDT NVARCHAR(500),
			REQ_DT VARCHAR(30),
			NOTES NVARCHAR(500)
		)
		
		DECLARE @doc INT
		Exec sp_xml_preparedocument @doc Output,@p_ListPlGood
		
		INSERT INTO @lstPLDT
		SELECT *
		FROM OPENXML(@doc,'/Root/ListPlGood',2)
		WITH 
		(
			REQPL_DT_ID VARCHAR(15),
			REQ_DOC_ID VARCHAR(15),
			PL_REQDT_ID VARCHAR(15),
			GD_ID VARCHAR(15),
			SUP_ID VARCHAR(15),
			HH_ID VARCHAR(15),
			TRAN_TYPE_ID VARCHAR(15),
			DES_GOOD NVARCHAR(500),
			QUANTITY_PL DECIMAL(18, 0),
			PRICE_PL DECIMAL(18, 2),
			CURRENCY NVARCHAR(50),
			EXCHANGE_RATE DECIMAL(18, 2),
			TAXES DECIMAL(18, 2),
			TOTAL_AMT_ETM DECIMAL(18, 2),
			REASON_CDT NVARCHAR(500),
			REQ_DT VARCHAR(30),
			NOTES NVARCHAR(500)
		)

		DECLARE PlGoodsCur CURSOR FOR SELECT * FROM @lstPLDT
		OPEN PlGoodsCur

		DECLARE
		@pl_REQPL_DT_ID VARCHAR(15),
		@pl_REQ_DOC_ID VARCHAR(15),
		@pl_PL_REQDT_ID VARCHAR(15),
		@pl_GD_ID VARCHAR(15),
		@pl_SUP_ID VARCHAR(15),
		@pl_HH_ID VARCHAR(15),
		@pl_TRAN_TYPE_ID VARCHAR(15),
		@pl_DES_GOOD NVARCHAR(500),
		@pl_QUANTITY_PL DECIMAL(18, 0),
		@pl_PRICE_PL DECIMAL(18, 2),
		@pl_CURRENCY NVARCHAR(50),
		@pl_EXCHANGE_RATE DECIMAL(18, 2),
		@pl_TAXES DECIMAL(18, 2),
		@pl_TOTAL_AMT_ETM DECIMAL(18, 2),
		@pl_REASON_CDT NVARCHAR(500),
		@pl_REQ_DT VARCHAR(30),
		@pl_NOTES NVARCHAR(500)

		FETCH NEXT FROM PlGoodsCur INTO @pl_REQPL_DT_ID, @pl_REQ_DOC_ID, @pl_PL_REQDT_ID, @pl_GD_ID, @pl_SUP_ID, @pl_HH_ID,
		@pl_TRAN_TYPE_ID, @pl_DES_GOOD, @pl_QUANTITY_PL, @pl_PRICE_PL, @pl_CURRENCY, @pl_EXCHANGE_RATE, @pl_TAXES, @pl_TOTAL_AMT_ETM, @pl_REASON_CDT, @pl_REQ_DT, @pl_NOTES
			
		WHILE @@FETCH_STATUS = 0	
		BEGIN

		DECLARE @l_PLDT_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC_PL_DT', @l_PLDT_ID out
		IF @l_PLDT_ID='' OR @l_PLDT_ID IS NULL GOTO ABORT

		INSERT INTO dbo.TR_REQUEST_DOC_PL_DT
		(
		    REQPL_DT_ID, REQ_DOC_ID, PL_REQDT_ID, GD_ID, SUP_ID, HH_ID, TRAN_TYPE_ID, MAKER_ID, CREATE_DT,
		    AUTH_STATUS, CHECKER_ID,APPROVE_DT,DES_GOOD,QUANTITY_PL,PRICE_PL,CURRENCY,EXCHANGE_RATE,TAXES,TOTAL_AMT_ETM,
			REASON_CDT,REQ_DT,NOTES, RECORD_STATUS
		)
		VALUES
		(   @l_PLDT_ID,        -- REQPL_DT_ID - varchar(15) -- primary key
		    @p_REQ_ID,        -- REQ_DOC_ID - varchar(15)
		    @pl_PL_REQDT_ID,        -- PL_REQDT_ID - varchar(15) -- chi tiết hàng hóa trong tờ trình
		    @pl_GD_ID,        -- GD_ID - varchar(15)
		    @pl_SUP_ID,        -- SUP_ID - varchar(15)
		    @pl_HH_ID,        -- HH_ID - varchar(15)
		    @pl_TRAN_TYPE_ID,        -- TRAN_TYPE_ID - varchar(15)
		    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
		    CONVERT(DATETIME, @p_CREATE_DT, 103), -- CREATE_DT - datetime
		    @p_AUTH_STATUS,        -- AUTH_STATUS - varchar(50)
		    @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
		    CONVERT(DATETIME, @p_APPROVE_DT, 103), -- APPROVE_DT - datetime
		    @pl_DES_GOOD,       -- DES_GOOD - nvarchar(500)
		    @pl_QUANTITY_PL,      -- QUANTITY_PL - decimal(18, 0)
		    @pl_PRICE_PL,      -- PRICE_PL - decimal(18, 2)
		    @pl_CURRENCY,       -- CURRENCY - nvarchar(50)
		    @pl_EXCHANGE_RATE,      -- EXCHANGE_RATE - decimal(18, 2)
		    @pl_TAXES,      -- TAXES - decimal(18, 2)
		    @pl_TOTAL_AMT_ETM,      -- TOTAL_AMT_ETM - decimal(18, 2)
		    @pl_REASON_CDT,       -- REASON_CDT - nvarchar(500)
		    CONVERT(DATETIME, @pl_REQ_DT, 103), -- REQ_DT - datetime
		    @pl_NOTES,       -- NOTES - nvarchar(500)
		    @p_RECORD_STATUS         -- RECORD_STATUS - varchar(1)
		)
		
		IF @@ERROR <> 0 GOTO ABORT1
		FETCH NEXT FROM PlGoodsCur INTO @pl_REQPL_DT_ID, @pl_REQ_DOC_ID, @pl_PL_REQDT_ID, @pl_GD_ID, @pl_SUP_ID, @pl_HH_ID,
		@pl_TRAN_TYPE_ID, @pl_DES_GOOD, @pl_QUANTITY_PL, @pl_PRICE_PL, @pl_CURRENCY, @pl_EXCHANGE_RATE, @pl_TAXES, @pl_TOTAL_AMT_ETM, @pl_REASON_CDT, @pl_REQ_DT, @pl_NOTES
		END
		CLOSE PlGoodsCur
		DEALLOCATE PlGoodsCur

		--Insert into TABLE PL_REQUEST_DOC_DT

		DECLARE @lstTRDT TABLE(
			REQDT_ID 	varchar(15)  ,
			PL_REQDT_ID	varchar(15)  ,
			GOODS_ID	varchar(15)  ,
			[DESCRIPTION] nvarchar(MAX),
			QUANTITY	decimal(18,0)  ,
			PRICE	decimal(18,2)  ,
			TOTAL_AMT	decimal(18,2),	
			NOTES	nvarchar(1000),
			REQ_DT varchar(30),
			AMORT_MONTH DECIMAL(18,2),
			TRADE_TYPE_ID varchar(15),
			SUP_ID varchar(15),
			HH_ID VARCHAR(20),
			CURRENCY	nvarchar(50),
			EXCHANGE_RATE	decimal(18, 2),
			TAXES	decimal(18, 2),
			DVDM_ID  VARCHAR(20),
			UNIT_NAME NVARCHAR(100)
		)


		Declare @hdoc INT
		Exec sp_xml_preparedocument @hdoc Output,@p_ListGood

		INSERT INTO @lstTRDT
		SELECT 
			REQDT_ID ,
			PL_REQDT_ID,
			GOODS_ID ,
			[DESCRIPTION],
			QUANTITY,
			PRICE ,
			TOTAL_AMT,	
			NOTES,
			Convert(Datetime,REQ_DT,103),
			AMORT_MONTH,
			TRADE_TYPE_ID,
			SUP_ID,
			HH_ID,
			CURRENCY,
			EXCHANGE_RATE,
			TAXES,
			DVDM_ID,
			UNIT_NAME
		FROM OPENXML(@hDoc,'/Root/ListGood',2)
		WITH 
		(
			REQDT_ID 	varchar(15)  ,
			PL_REQDT_ID	varchar(15)  ,
			GOODS_ID	varchar(15)  ,
			[DESCRIPTION] nvarchar(MAX),
			QUANTITY	decimal(18,0)  ,
			PRICE	decimal(18,2)  ,
			TOTAL_AMT	decimal(18,2),	
			NOTES	nvarchar(1000),
			REQ_DT varchar(30),
			AMORT_MONTH DECIMAL(18,2),
			TRADE_TYPE_ID varchar(15),
			SUP_ID varchar(15),
			HH_ID VARCHAR(20),
			CURRENCY	nvarchar(50),
			EXCHANGE_RATE	decimal(18, 2),
			TAXES	decimal(18, 2),
			DVDM_ID  VARCHAR(20),
			UNIT_NAME NVARCHAR(100)
			)

		DECLARE @p_REQ_DOC_DT_ROW_NUM INT = 0

		DECLARE ListGoods  CURSOR FOR
		SELECT * FROM @lstTRDT
		OPEN ListGoods

		Declare 
		@REQDT_ID 	varchar(15)  ,
		@PL_REQDT_ID	varchar(15),
		@SUP_ID	varchar(15),
		@GOODS_ID	varchar(15),
		@DESCRIPTION nvarchar(MAX),
		@QUANTITY	decimal(18,0),
		@PRICE	decimal(18,2),
		@TOTAL_AMT	decimal(18,2),		
		@NOTES	nvarchar(1000),
		@TRADE_TYPE_ID varchar(15),
		@AMORT_MONTH DECIMAL(18,2),
		@RED_DT DATETIME,
		@HH_ID VARCHAR(20),
		@CURRENCY	nvarchar(50),
		@EXCHANGE_RATE	decimal(18, 2),
		@TAXES	decimal(18, 2),
		@DVDM_ID VARCHAR(20),
		@UNIT_NAME NVARCHAR(100)


		FETCH NEXT FROM ListGoods INTO @REQDT_ID, @PL_REQDT_ID,@GOODS_ID,@DESCRIPTION,@QUANTITY,@PRICE,@TOTAL_AMT,
		@NOTES,@RED_DT,@AMORT_MONTH, @TRADE_TYPE_ID, @SUP_ID,@HH_ID,@CURRENCY,@EXCHANGE_RATE,@TAXES,@DVDM_ID,@UNIT_NAME
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			
			SET @p_REQ_DOC_DT_ROW_NUM = @p_REQ_DOC_DT_ROW_NUM + 1
			--------------Mô tả hàng hóa dịch vụ-----------
			IF(@DESCRIPTION IS NULL OR @DESCRIPTION = '')
			BEGIN
				CLOSE ListGoods
				DEALLOCATE ListGoods
				ROLLBACK TRANSACTION
				SELECT '-1' AS Result, 
					N'Lưới chi tiết mua sắm thực tế, dòng:' + CONVERT(VARCHAR,@p_REQ_DOC_DT_ROW_NUM)
					+ N'. Mô tả hàng hóa/ dịch vụ không được để trống' AS ErrorDesc
				RETURN '-1'
			END
			---------------Đơn vị tính----------------
			IF(@UNIT_NAME IS NULL OR @UNIT_NAME = '')
			BEGIN
				CLOSE ListGoods
				DEALLOCATE ListGoods
				ROLLBACK TRANSACTION
				SELECT '-1' AS Result, 
					N'Lưới chi tiết mua sắm thực tế, dòng:' + CONVERT(VARCHAR,@p_REQ_DOC_DT_ROW_NUM)
					+ N'. Đơn vị tính không được để trống' AS ErrorDesc
				RETURN '-1'
			END
			---------------Số lượng---------------
			IF(@QUANTITY IS NULL)
			BEGIN
				CLOSE ListGoods
				DEALLOCATE ListGoods
				ROLLBACK TRANSACTION
				SELECT '-1' AS Result, 
					N'Lưới chi tiết mua sắm thực tế, dòng:' + CONVERT(VARCHAR,@p_REQ_DOC_DT_ROW_NUM)
					+ N'. Số lượng không được để trống' AS ErrorDesc
				RETURN '-1'
			END
			--------------Đơn giá-------------------
			IF(@PRICE IS NULL)
			BEGIN
				CLOSE ListGoods
				DEALLOCATE ListGoods
				ROLLBACK TRANSACTION
				SELECT '-1' AS Result, 
					N'Lưới chi tiết mua sắm thực tế, dòng:' + CONVERT(VARCHAR,@p_REQ_DOC_DT_ROW_NUM)
					+ N'. Đơn giá không được để trống' AS ErrorDesc
				RETURN '-1'
			END
			--------------Loại tiền tệ---------------
			IF(@CURRENCY IS NULL OR @CURRENCY = '')
			BEGIN
				CLOSE ListGoods
				DEALLOCATE ListGoods
				ROLLBACK TRANSACTION
				SELECT '-1' AS Result, 
					N'Lưới chi tiết mua sắm thực tế, dòng:' + CONVERT(VARCHAR,@p_REQ_DOC_DT_ROW_NUM)
					+ N'. Loại tiền tệ không được để trống' AS ErrorDesc
				RETURN '-1'
			END
			----------------Thuế NTNN---------------
			IF(@TAXES IS NULL)
			BEGIN
				CLOSE ListGoods
				DEALLOCATE ListGoods
				ROLLBACK TRANSACTION
				SELECT '-1' AS Result, 
					N'Lưới chi tiết mua sắm thực tế, dòng:' + CONVERT(VARCHAR,@p_REQ_DOC_DT_ROW_NUM)
					+ N'. Thuế NTNN không được để trống' AS ErrorDesc
				RETURN '-1'
			END
			--------------Tỷ giá quy đổi-----------
			IF(@EXCHANGE_RATE IS NULL)
			BEGIN
				CLOSE ListGoods
				DEALLOCATE ListGoods
				ROLLBACK TRANSACTION
				SELECT '-1' AS Result, 
					N'Lưới chi tiết mua sắm thực tế, dòng:' + CONVERT(VARCHAR,@p_REQ_DOC_DT_ROW_NUM)
					+ N'. Tỷ giá quy đổi không được để trống' AS ErrorDesc
				RETURN '-1'
			END
			-------------Ngày cần--------------
			IF(@RED_DT IS NULL OR @RED_DT = '')
			BEGIN
				CLOSE ListGoods
				DEALLOCATE ListGoods
				ROLLBACK TRANSACTION
				SELECT '-1' AS Result, 
					N'Lưới chi tiết mua sắm thực tế, dòng:' + CONVERT(VARCHAR,@p_REQ_DOC_DT_ROW_NUM)
					+ N'. Ngày cần không được để trống' AS ErrorDesc
				RETURN '-1'
			END

			DECLARE @l_REQDT_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC_DT', @l_REQDT_ID out
			IF @l_REQDT_ID='' OR @l_REQDT_ID IS NULL GOTO ABORT
	
			SET @TOTAL_AMT= (@QUANTITY * @PRICE + @TAXES) * @EXCHANGE_RATE

			INSERT INTO dbo.TR_REQUEST_DOC_DT
			(
			    REQDT_ID,
				[REQPL_DT_ID],
			    REQ_DOC_ID,
			    PL_REQDT_ID,
			    GD_ID,
				TRAN_TYPE_ID,
				SUP_ID,
			    DESCRIPTION,
			    QUANTITY,
			    PRICE,
				PRICE_ETM,
			    TOTAL_AMT,
				TOTAL_AMT_ETM,
			    REQ_DT,
			    AMORT_MONTH,
			    NOTES,
			    RECORD_STATUS,
			    MAKER_ID,
			    CREATE_DT,
			    AUTH_STATUS,
			    CHECKER_ID,
			    APPROVE_DT,
				HANGHOA_ID,
				CURRENCY,
				EXCHANGE_RATE,
				TAXES,
				DVDM_ID, UNIT_NAME
			)	
			VALUES
			(   @l_REQDT_ID,        -- REQDT_ID - varchar(15)
				(SELECT TOP 1 REQPL_DT_ID FROM dbo.TR_REQUEST_DOC_PL_DT WHERE REQ_DOC_ID = @p_REQ_ID AND PL_REQDT_ID = @PL_REQDT_ID),
			    @p_REQ_ID,        -- REQ_ID - varchar(15)
			    @PL_REQDT_ID,        -- PLAN_ID - varchar(15)
			    @GOODS_ID,        -- GOODS_ID - varchar(15)
			   -- @TRADE_TYPE_ID,
			   CASE WHEN (SELECT TOP 1 TRADE_TYPE FROM dbo.PL_REQUEST_DOC_DT WHERE REQDT_ID = @PL_REQDT_ID) ='CDT' THEN 'TRN0000000003' ELSE 'TRN0000000009' END, -- LUCTV 17-03-2023 FIX
				-- NEU TTCT LA CDT THÌ PYCMS AUTO CDT & NGUOC LAI
				@SUP_ID,       -- NAME - nvarchar(200)
			    @DESCRIPTION,       -- DESCRIPTION - nvarchar(500)
				@QUANTITY,      -- QUANTITY - decimal(18, 0)
			    @PRICE,
				@PRICE,-- PRICE - decimal(18, 0)
			    ROUND(@TOTAL_AMT,0),   
				ROUND(@TOTAL_AMT,0), 
				CONVERT(DATETIME, @RED_DT, 103),
				@AMORT_MONTH,
				@NOTES,  -- TOTAL_AMT - decimal(18, 0)
			    @p_RECORD_STATUS,        -- RECORD_STATUS - varchar(1)
			    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
			    CONVERT(DATETIME, @p_CREATE_DT, 103), -- CREATE_DT - datetime
			    @p_AUTH_STATUS,        -- AUTH_STATUS - varchar(50)
			    @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
			    CONVERT(DATETIME, @p_APPROVE_DT, 103),
				@HH_ID,
				@CURRENCY,
				@EXCHANGE_RATE,
				@TAXES,
				@DVDM_ID,  -- APPROVE_DT - datetime
				@UNIT_NAME
			)
			
			
			IF @@ERROR <> 0 GOTO ABORT1
		-- next Group_Id
		FETCH NEXT FROM ListGoods INTO @REQDT_ID,@PL_REQDT_ID,@GOODS_ID,@DESCRIPTION,@QUANTITY,@PRICE,@TOTAL_AMT,
		@NOTES,@RED_DT,@AMORT_MONTH, @TRADE_TYPE_ID, @SUP_ID,@HH_ID,@CURRENCY,@EXCHANGE_RATE,@TAXES,@DVDM_ID,@UNIT_NAME
		END
		CLOSE ListGoods
		DEALLOCATE ListGoods

		UPDATE dbo.TR_REQUEST_DOC SET TOTAL_AMT=(SELECT SUM(TOTAL_AMT_ETM) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID) WHERE REQ_ID=@p_REQ_ID 

			IF @@Error <> 0 GOTO ABORT
		DECLARE @COST_ID VARCHAR(20)

		DECLARE lstCostCenter CURSOR FOR
		SELECT COST_ID FROM dbo.PL_REQUEST_COSTCENTER WHERE REQ_ID=@p_PL_REQ_ID
		GROUP BY COST_ID
		OPEN lstCostCenter
		FETCH NEXT FROM lstCostCenter INTO @COST_ID
		WHILE @@FETCH_STATUS=0
		BEGIN


			DECLARE @l_REQ_COST_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'TR_REQUEST_COSTCENTER', @l_REQ_COST_ID out
			IF @l_REQ_COST_ID='' OR @l_REQ_COST_ID IS NULL GOTO ABORT
				INSERT INTO dbo.TR_REQUEST_COSTCENTER
				(
					REQ_COST_ID,
					COST_ID,
					REQ_ID,
					NOTES,
					AUTH_STATUS,
					MAKER_ID,
					CREATE_DT,
					CHECKER_ID,
					APPROVE_DT
				)
				VALUES
				(   @l_REQ_COST_ID,        -- REQ_COST_ID - varchar(15)
					@COST_ID,        -- COST_ID - varchar(15)
					@p_REQ_ID,        -- REQ_ID - varchar(15)
					N'',       -- NOTES - nvarchar(500)
					'',        -- AUTH_STATUS - varchar(1)
					@p_MAKER_ID,        -- MAKER_ID - varchar(15)
					NULL, -- CREATE_DT - datetime
					'',        -- CHECKER_ID - varchar(15)
					NULL  -- APPROVE_DT - datetime
					)
				FETCH NEXT FROM lstCostCenter INTO @COST_ID
		END 
		CLOSE lstCostCenter
		DEALLOCATE lstCostCenter
		IF @@Error <> 0 GOTO ABORT
		

	
	--Insert into TABLE TR_REQUEST_DOC_FILE

		DECLARE @tableTrREQFile TABLE(
			TR_REQUEST_DOC_FILE_ID	varchar(20)  ,
			IS_VIEW	bit,
			REQ_ID varchar(20),
			NOTES  nvarchar(200)
		)


		Declare @fdoc INT
		Exec sp_xml_preparedocument @fdoc Output,@p_ListTrREQFile

		INSERT INTO @tableTrREQFile
		SELECT *
		FROM OPENXML(@fDoc,'/Root/ListTrREQ',2)
		WITH 
		(
			TR_REQUEST_DOC_FILE_ID	varchar(20)  ,
			IS_VIEW	bit,
			REQ_ID varchar(20),
			NOTES  nvarchar(200)
		)

		--delete TR_REQUEST_DOC_FILE where TR_REQUEST_DOC_FILE_ID not in (select TR_REQUEST_DOC_FILE_ID from @tableTrREQFile) and REQ_ID = @p_REQ_ID
    ------------BAODNQ 11/11/2022 : DELETE TR_REQUEST_DOC_FILE----------
    DELETE FROM TR_REQUEST_DOC_FILE WHERE REQ_ID = @p_REQ_ID
		DECLARE ListTrREQFile  CURSOR FOR
		SELECT * FROM @tableTrREQFile
		OPEN ListTrREQFile

		Declare 
		@TR_REQUEST_DOC_FILE_ID	varchar(20)  ,
		@IS_VIEW	bit,
		@REQ_ID varchar(20),
		@_NOTES nvarchar(200)

		FETCH NEXT FROM ListTrREQFile INTO @TR_REQUEST_DOC_FILE_ID,@IS_VIEW,@REQ_ID,@_NOTES
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			DECLARE @l_REQFile_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC_FILE', @l_REQFile_ID out
			--select @l_REQFile_ID
			--select * from TR_REQUEST_DOC_FILE where [TR_REQUEST_DOC_FILE_ID]= 'TRDF00000000523'
			IF @l_REQFile_ID='' OR @l_REQFile_ID IS NULL GOTO ABORT
			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_REQFile_ID, 'TR_REQUEST_DOC_FILE')
			IF(select count(*) from TR_REQUEST_DOC_FILE where TR_REQUEST_DOC_FILE_ID = @TR_REQUEST_DOC_FILE_ID ) > 0
			begin
				update TR_REQUEST_DOC_FILE set IS_VIEW=isnull(@IS_VIEW,0),REQ_ID = @p_REQ_ID,NOTES=@_NOTES where TR_REQUEST_DOC_FILE_ID = @TR_REQUEST_DOC_FILE_ID 
			end
			else
			begin
				INSERT INTO dbo.TR_REQUEST_DOC_FILE
				(
					 [TR_REQUEST_DOC_FILE_ID]
					,[IS_VIEW]
					,[REQ_ID]
					,[NOTES]
				)	
				VALUES
				(   
					@l_REQFile_ID
					,isnull(@IS_VIEW,0)
					,@p_REQ_ID,
					@_NOTES
				)
			end
			
			FETCH NEXT FROM ListTrREQFile INTO @TR_REQUEST_DOC_FILE_ID,@IS_VIEW,@REQ_ID,@_NOTES
		END
		CLOSE ListTrREQFile
		DEALLOCATE ListTrREQFile
		IF @@Error <> 0 GOTO ABORT

		if(select count(*) from @TEMP) = 0
		begin
			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_REQFile_ID, 'TR_REQUEST_DOC_FILE')
		end
COMMIT TRANSACTION
--Cuong LX 17/11/2021
	--IF(@p_Record_ID_1 is null OR @p_Record_ID_1 = '')--Xóa biên bản xét giá
	--BEGIN
	--	DELETE TR_REQ_DOC_XETGIA_DUOI_100M_DT WHERE RECORD_MASTER_ID = (SELECT TOP(1)RECORD_ID FROM TR_REQ_DOC_XETGIA_DUOI_100M WHERE REQ_DOC_ID = @p_REQ_ID)
	--	DELETE TR_REQ_DOC_XETGIA_DUOI_100M WHERE REQ_DOC_ID = @p_REQ_ID
	--END
	--IF(@p_Record_ID_2 is null OR @p_Record_ID_2 = '')--Xóa biên bản xét giá
	--BEGIN
	--	DELETE TR_REQ_DOC_XETGIA_100M_500M_DT_DGC WHERE RECORD_MASTER_ID = (SELECT TOP(1)RECORD_ID FROM TR_REQ_DOC_XETGIA_100M_500M WHERE REQ_DOC_ID = @p_REQ_ID)
	--	DELETE TR_REQ_DOC_XETGIA_100M_500M_DT_XG WHERE RECORD_MASTER_ID = (SELECT TOP(1)RECORD_ID FROM TR_REQ_DOC_XETGIA_100M_500M WHERE REQ_DOC_ID = @p_REQ_ID)
	--	DELETE TR_REQ_DOC_XETGIA_100M_500M_XH WHERE RECORD_MASTER_ID = (SELECT TOP(1)RECORD_ID FROM TR_REQ_DOC_XETGIA_100M_500M WHERE REQ_DOC_ID = @p_REQ_ID)
	--	DELETE TR_REQ_DOC_XETGIA_100M_500M WHERE REQ_DOC_ID = @p_REQ_ID
	--END
SELECT '0' as Result, @p_REQ_ID  REQ_ID,[REF_ID], [TYPE] , 
	@p_REQ_CODE REQ_CODE, '' AS ErrorDesc from @TEMP
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' AS Result
		RETURN '-1'
End
ABORT1:
BEGIN
		CLOSE ListGoods
		DEALLOCATE ListGoods
		ROLLBACK TRANSACTION
		SELECT '-1' AS Result
		RETURN '-1'
End