ALTER PROCEDURE dbo.TR_REQUEST_DOC_Ins
@p_REQ_CODE	nvarchar(100)  = NULL,
@p_REQ_NAME	nvarchar(200)  = NULL,
@p_REQ_DT	VARCHAR(30) = NULL,--
@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 = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(12)  = NULL,
@p_CREATE_DT	VARCHAR(30) = NULL,--
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	VARCHAR(30) = NULL,--
@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_ListGood XML,
@p_ListPlGood XML,
@p_ListTrREQFile XML,
@p_IS_KT bit = null
AS
DECLARE @sErrorCode VARCHAR(20)
DECLARE @TEMP TABLE
			(
				[KEY] varchar(15),
				[REF_ID] varchar(15),
				[TYPE] varchar(50)
			)

  BEGIN TRANSACTION
  
	exec [TR_CODE_GenKey] 'TR_REQUEST_DOC', '','PUR', @p_REQ_CODE OUT
    
	IF EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE [REQ_CODE] = @p_REQ_CODE)
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' Result, '' REQ_ID, N'Số phiếu yêu cầu đã tồn tại' ErrorDesc
		RETURN '-1'
	END

	IF(@p_REQ_CODE IS NULL OR @p_REQ_CODE ='')
	BEGIN
		ROLLBACK TRANSACTION
		SELECT 'REQ-00001' Result, '' REQ_ID, N'Mã phiếu yêu cầu bắt buộc nhập' ErrorDesc 
		RETURN '-1'
	END
		--insert master				
		DECLARE @l_REQ_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC', @l_REQ_ID out
		IF @l_REQ_ID='' OR @l_REQ_ID IS NULL GOTO ABORT


		INSERT INTO dbo.TR_REQUEST_DOC
		(
		    REQ_ID,
		    REQ_CODE,
		    REQ_NAME,
		    REQ_DT,
		    REQ_TYPE,
		    REQ_REASON,
		    REQ_CONTENT,
		    PL_REQ_ID,
		    TOTAL_AMT,
		    NOTES,
		    RECORD_STATUS,
		    MAKER_ID,
		    CREATE_DT,
		    AUTH_STATUS,
		    CHECKER_ID,
		    APPROVE_DT,
			BRANCH_DO,
			PROCESS_ID,
			BRANCH_CREATE,
			USER_REQUEST,
			BRANCH_DVMS,
			DEP_CREATE,
			REQ_PARENT_ID,SIGN_USER,IS_KT
		)
		VALUES
		(   @l_REQ_ID,        -- REQ_ID - varchar(15)
		    @p_REQ_CODE,        -- REQ_CODE - varchar(100)
		    @p_REQ_NAME,       -- REQ_NAME - nvarchar(200)
			CONVERT(DATETIME, @p_REQ_DT, 103), -- REQ_DT - datetime
		    @p_REQ_TYPE, 
			@p_REQ_REASON,        -- REQ_TYPE - int
		    @p_REQ_CONTENT,       -- REQ_CONTENT - nvarchar(1000)
		         -- REQ_REASON - nvarchar(500)
		    @p_PL_REQ_ID,     
		    @p_TOTAL_AMT,      -- TOTAL_AMT - decimal(18, 0)
		    @p_NOTES,       -- NOTES - nvarchar(1000)
		    @p_RECORD_STATUS,        -- RECORD_STATUS - varchar(1)
		    @p_MAKER_ID,        -- MAKER_ID - varchar(12)
		    CONVERT(DATETIME, @p_CREATE_DT, 103), -- CREATE_DT - datetime
		    'E',        -- AUTH_STATUS - varchar(50)
		    @p_CHECKER_ID,        -- CHECKER_ID - varchar(12)
		    CONVERT(DATETIME, @p_APPROVE_DT, 103),  -- APPROVE_DT - datetime
		    @p_BRANCH_DO,
			'',   -- PROCESS_ID - varchar(15)
			@p_BRANCH_CREATE,
			@p_USER_REQUEST,
			@p_BRANCH_CREATE,
			@p_DEP_CREATE,
			@p_REQ_PARENT_ID,@p_SIGN_USER,@p_IS_KT
			)
		IF @@Error <> 0 GOTO ABORT

		-- 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
		    @l_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(
			PL_REQDT_ID	varchar(15)  ,
			GOODS_ID	varchar(15)  ,
			[DESCRIPTION] nvarchar(500),
			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 *
		FROM OPENXML(@hDoc,'/Root/ListGood',2)
		WITH 
		(
			PL_REQDT_ID	varchar(15)  ,
			GOODS_ID	varchar(15)  ,
			[DESCRIPTION] nvarchar(500),
			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 
		@PL_REQDT_ID	varchar(15),
		@SUP_ID	varchar(15),
		@GOODS_ID	varchar(15),
		@DESCRIPTION nvarchar(500),
		@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 VARCHAR(30),
		@HH_ID VARCHAR(20),
		@CURRENCY	nvarchar(50),
		@EXCHANGE_RATE	decimal(18, 2),
		@DVDM_ID VARCHAR(20),
		@TAXES	decimal(18, 2),
		@UNIT_NAME NVARCHAR(100)

		FETCH NEXT FROM ListGoods INTO @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 = @l_REQ_ID AND PL_REQDT_ID = @PL_REQDT_ID),
			    @l_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 @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=@l_REQ_ID) WHERE REQ_ID=@l_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)
					@l_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 dbo.PL_REQUEST_PROCESS
		(
		    REQ_ID,
		    PROCESS_ID,
		    STATUS,
		    ROLE_USER,
		    BRANCH_ID,
		    CHECKER_ID,
		    APPROVE_DT,
		    PARENT_PROCESS_ID,
		    IS_LEAF,
		    COST_ID,
		    DVDM_ID,
		    NOTES,
		    IS_HAS_CHILD
		)
		VALUES
		(   @l_REQ_ID,        -- REQ_ID - varchar(15)
		    'NEW',        -- PROCESS_ID - varchar(10)
		    'C',        -- STATUS - varchar(5)
		    '',        -- ROLE_USER - varchar(50)
		    '',        -- BRANCH_ID - varchar(15)
		    '',        -- CHECKER_ID - varchar(15)
		    NULL,      -- APPROVE_DT - datetime
		    '',        -- PARENT_PROCESS_ID - varchar(10)
		    'N',        -- IS_LEAF - varchar(1)
		    '',        -- COST_ID - varchar(15)
		    '',        -- DVDM_ID - varchar(15)
		    N'Chờ gửi phê duyệt',       -- NOTES - nvarchar(500)
		    NULL       -- IS_HAS_CHILD - bit
		    )
		
		--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)
		)

		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')

			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)
				,@l_REQ_ID
				,@_NOTES
			)
			IF @@ERROR <> 0 GOTO ABORT1
			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
SELECT '0' as Result, @l_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