SELECT * FROM PL_REQUEST_DOC WHERE REQ_CODE ='0005/2021/TTr-0020005'
SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID ='PLRD00000201323'
SELECT * FROM TR_REQUEST_DOC_PL_DT WHERE PL_REQDT_ID ='PLRDD0000212827'
SELECT * FROM TR_REQUEST_DOC_PL_DT WHERE PL_REQDT_ID ='PLRDD0000212828'
SELECT * FROM TR_REQUEST_DOC_DT WHERE PL_REQDT_ID ='PLRDD0000212828'
SELECT * FROM TR_REQUEST_DOC_DT WHERE PL_REQDT_ID ='PLRDD0000212827'

UPDATE TR_REQUEST_DOC_PL_DT SET PRICE_PL = 1500000, TOTAL_AMT_ETM =1500000 WHERE PL_REQDT_ID ='PLRDD0000212827'
UPDATE TR_REQUEST_DOC_PL_DT SET PRICE_PL = 2000000, TOTAL_AMT_ETM =2000000 WHERE PL_REQDT_ID ='PLRDD0000212828'

UPDATE PL_REQUEST_DOC_DT SET PRICE =1500000 , TOTAL_AMT = 1500000 WHERE REQDT_ID ='PLRDD0000212827'
UPDATE PL_REQUEST_DOC_DT SET PRICE =2000000 , TOTAL_AMT = 2000000 WHERE REQDT_ID ='PLRDD0000212828'

UPDATE PL_TRADEDETAIL SET AMT_ETM = AMT_ETM -500000 WHERE TRADE_ID ='PLT000000270143'
UPDATE PL_TRADEDETAIL SET AMT_ETM = AMT_ETM +500000 WHERE TRADE_ID ='PLT000000270144'

UPDATE TR_REQUEST_DOC_DT SET PRICE_ETM =1500000, TOTAL_AMT_ETM =1500000, PRICE =1500000, TOTAL_AMT =1500000 WHERE PL_REQDT_ID ='PLRDD0000212827'
UPDATE TR_REQUEST_DOC_DT SET PRICE_ETM =2000000, TOTAL_AMT_ETM =2000000, PRICE =2000000, TOTAL_AMT =2000000 WHERE PL_REQDT_ID ='PLRDD0000212828'
UPDATE TR_REQUEST_DOC SET DMMS_ID ='DM0000000000052' WHERE REQ_CODE ='PUR/2021/000521'
¿
ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_Upd]
@p_REQ_ID VARCHAR(15)=NULL,
@p_REQ_CODE	nvarchar(100)  = NULL,
@p_REQ_NAME	nvarchar(200)  = NULL,
@p_REQ_DT	DATETIME = NULL,
@p_REQ_TYPE	int = NULL,
@p_REQ_CONTENT NVARCHAR(1000)=NULL,
@p_REQ_REASON NVARCHAR(500)=NULL,
@p_TOTAL_AMT	decimal = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(20)  = NULL,
@p_CREATE_DT	DATETIME = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(20)  = NULL,
@p_APPROVE_DT	DATETIME = NULL,
@p_BRANCH_ID VARCHAR(15)=NULL,
@p_DVDM_ID VARCHAR(20) = NULL,
@p_REQ_PARENT_ID VARCHAR(20) = NULL,
@p_BRANCH_FEE NVARCHAR(500) = NULL,
@p_DEP_ID VARCHAR(20)=NULL,
@p_DEP_FEE_ID VARCHAR(20)= NULL,
@p_IS_BACKDAY BIT= NULL,
@p_REQ_LINE VARCHAR(20),
@p_SIGN_USER VARCHAR(20) = NULL,
@p_IS_CHECKALL BIT = NULL,
@p_BASED_CONTENT NVARCHAR(3000) = NULL,
@p_PL_BASED_ID VARCHAR(15)= NULL,
@p_ListGood XML,
@p_ListCostCenter XML,
@p_ListTransfer XML
AS
SET @p_CREATE_DT =CAST(@p_CREATE_DT AS DATE)
SET @p_APPROVE_DT=CAST(@p_APPROVE_DT AS DATE)
SET @p_REQ_DT = CAST(@p_REQ_DT AS DATE)

	IF(@p_REQ_CODE IS NULL OR @p_REQ_CODE ='')
	BEGIN
		SELECT 'REQ-00001' Result, '' REQ_ID, N'Mã tờ trình chủ trương bắt buộc nhập' ErrorDesc 
		RETURN '-1'
	END
	IF EXISTS(SELECT * FROM PL_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-00001'
		RETURN '-1'
	END
	IF(CAST(@p_REQ_DT AS DATE) > CAST(GETDATE() AS DATE))
	BEGIN
			SELECT 'REQ-00001' Result, '' REQ_ID, N'Ngày tạo tờ trình không được phép hơn ngày hiện tại' ErrorDesc 
			ROLLBACK TRANSACTION
			RETURN '-1'
	END
	DECLARE @sErrorCode VARCHAR(20)
		
  BEGIN TRANSACTION
		
		DECLARE @BRANCH_CREATE VARCHAR(20),@DEP_CREATE VARCHAR(20)

		SET  @BRANCH_CREATE= (SELECT TLSUBBRID  FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)
		SET @DEP_CREATE=(SELECT SECUR_CODE  FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)
		
		IF(EXISTS(SELECT * FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE AND BRANCH_TYPE='HS') 
			OR EXISTS(SELECT DEP_ID FROM dbo.CM_DEPARTMENT WHERE DEP_ID=@DEP_CREATE AND DEP_CODE LIKE '069%'))		
			SET @DEP_CREATE=(SELECT SECUR_CODE  FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)
		ELSE
			SET @DEP_CREATE=''
		

		SET @p_DEP_ID=@DEP_CREATE
		SET @p_BRANCH_ID=@BRANCH_CREATE


		IF(CAST(@p_REQ_DT AS DATE) < CAST(GETDATE() AS DATE))
			SET @p_IS_BACKDAY=1;
		ELSE
			SET  @p_IS_BACKDAY=0

		UPDATE dbo.PL_REQUEST_DOC 
		SET REQ_CODE=@p_REQ_CODE,REQ_NAME=@p_REQ_NAME,REQ_DT=@p_REQ_DT,REQ_TYPE=@p_REQ_TYPE,REQ_CONTENT=@p_REQ_CONTENT,REQ_REASON=@p_REQ_REASON,RECORD_STATUS=@p_RECORD_STATUS
		,BRANCH_ID=@p_BRANCH_ID,TOTAL_AMT=@p_TOTAL_AMT,NOTES=@p_NOTES,MAKER_ID=@p_MAKER_ID,CREATE_DT=@p_CREATE_DT,CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=@p_APPROVE_DT,AUTH_STATUS=@p_AUTH_STATUS,DVDM_APP_ID=@p_DVDM_ID
		,REQ_PARENT_ID=@p_REQ_PARENT_ID,BRANCH_FEE=@p_BRANCH_FEE,IS_BACKDAY=@p_IS_BACKDAY,DEP_ID=@p_DEP_ID,DEP_FEE=@p_DEP_FEE_ID,REQ_LINE=@p_REQ_LINE,SIGN_USER = @p_SIGN_USER,IS_CHECKALL=@p_IS_CHECKALL,BASED_CONTENT=@p_BASED_CONTENT,
		PL_BASED_ID =@p_PL_BASED_ID
		WHERE REQ_ID=@p_REQ_ID
		DELETE FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID
		DELETE FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID
		DELETE FROM dbo.PL_REQUEST_COSTCENTER WHERE REQ_ID=@p_REQ_ID

		
		IF @@Error <> 0 GOTO ABORT
		--Insert into TABLE PL_REQUEST_DOC_DT
	DECLARE @TABLE TABLE(
			PLAN_ID	varchar(15)  ,
			TRADE_ID	varchar(15)  ,
			GOODS_ID	varchar(15)  ,
			[DESCRIPTION] nvarchar(500),
			UNIT_ID	varchar(15)  ,
			QUANTITY	decimal(18,0)  ,
			PRICE	decimal(18,2)  ,
			TOTAL_AMT	decimal(18,2),	
			NOTES	nvarchar(1000),
			REQDT_TYPE VARCHAR(1),
			NAME NVARCHAR(500),
			DVDM_ID VARCHAR(20),
			HH_ID VARCHAR(20),
			CURRENCY	nvarchar(50),
			EXCHANGE_RATE	decimal(18,2),
			TAXES	decimal(18, 2),
			SUP_ID VARCHAR(20),
			TRADE_TYPE VARCHAR(20),
			KHOI_ID VARCHAR(20),
			UNIT_NAME NVARCHAR(200), SUP_NAME NVARCHAR(250)
		)
		--Insert into TABLE PL_REQUEST_DOC_DT
		Declare @hdoc INT
		Exec sp_xml_preparedocument @hdoc Output,@p_ListGood
		INSERT INTO @TABLE
		SELECT PLAN_ID,
               TRADE_ID,
               GOODS_ID,
               DESCRIPTION,
               UNIT_ID,
               QUANTITY,
               PRICE,
               TOTAL_AMT,
               NOTES,
               REQDT_TYPE,
               NAME,
               DVDM_ID,
               HH_ID,
               CURRENCY,
               EXCHANGE_RATE,
               TAXES,
               SUP_ID,
               TRADE_TYPE,
			   KHOI_ID,UNIT_NAME, SUP_NAME
		FROM OPENXML(@hDoc,'/Root/ListGood',2)
		WITH 
		(
			PLAN_ID	varchar(15)  ,
			TRADE_ID	varchar(15)  ,
			GOODS_ID	varchar(15)  ,
			[DESCRIPTION] nvarchar(500),
			UNIT_ID	varchar(15)  ,
			QUANTITY	decimal(18,0)  ,
			PRICE	decimal(18,2)  ,
			TOTAL_AMT	decimal(18,2),	
			NOTES	nvarchar(1000),
			REQDT_TYPE VARCHAR(1),
			NAME NVARCHAR(500),
			DVDM_ID VARCHAR(20),
			HH_ID VARCHAR(20),
			CURRENCY	nvarchar(50),
			EXCHANGE_RATE	decimal(18,2),
			TAXES	decimal(18, 2),
			SUP_ID VARCHAR(20),
			TRADE_TYPE VARCHAR(20),
			KHOI_ID VARCHAR(20),
			UNIT_NAME NVARCHAR(200),
			SUP_NAME NVARCHAR(200)
		)


	

		DECLARE @TABLE_TRANSFER TABLE (
			FR_PLAN_ID	varchar(15),
			FR_TRADE_ID	varchar(15),
			FR_GOOD_ID	varchar(15),
			FR_BRN_ID	varchar(15),
			TO_BRN_ID	varchar(15),
			TO_PLAN_ID	varchar(15),
			TO_TRADE_ID	varchar(15),	
			TO_GOOD_ID	varchar(15),	
			QTY  DECIMAL(18,0),
			TOTAL_AMT	decimal(18),	
			NOTES	nvarchar(1000),
			FR_DEP_ID VARCHAR(20),
			TO_DEP_ID VARCHAR(20),
			FR_DVDM_ID VARCHAR(20),
			TO_DVDM_ID VARCHAR(20),
			FR_KHOI_ID VARCHAR(20),
			TO_KHOI_ID VARCHAR(20),
			FR_GD_TYPE VARCHAR(20),
			TO_GD_TYPE VARCHAR(20)
		)
		Exec sp_xml_preparedocument @hdoc Output,@p_ListTransfer
		INSERT INTO @TABLE_TRANSFER
		SELECT FR_PLAN_ID,
               FR_TRADE_ID,
               FR_GOOD_ID,
               FR_BRN_ID,
               TO_BRN_ID,
               TO_PLAN_ID,
               TO_TRADE_ID,
               TO_GOOD_ID,
               QTY,
               TOTAL_AMT,
               NOTES,
               FR_DEP_ID,
               TO_DEP_ID,
               FR_DVDM_ID,
               TO_DVDM_ID,
               FR_KHOI_ID,
               TO_KHOI_ID,
               FR_GD_TYPE,
               TO_GD_TYPE 
		FROM OPENXML(@hdoc,'/Root/ListTransfer',2)
		WITH 
		(
			FR_PLAN_ID	varchar(15),
			FR_TRADE_ID	varchar(15),
			FR_GOOD_ID	varchar(15),
			FR_BRN_ID	varchar(15),
			TO_BRN_ID	varchar(15),
			TO_PLAN_ID	varchar(15),
			TO_TRADE_ID	varchar(15),	
			TO_GOOD_ID	varchar(15),	
			QTY  DECIMAL(18,0),
			TOTAL_AMT	decimal(18),	
			NOTES	nvarchar(1000),
			FR_DEP_ID VARCHAR(20),
			TO_DEP_ID VARCHAR(20),
			FR_DVDM_ID VARCHAR(20),
			TO_DVDM_ID VARCHAR(20),
			FR_KHOI_ID VARCHAR(20),
			TO_KHOI_ID VARCHAR(20),
			FR_GD_TYPE VARCHAR(20),
			TO_GD_TYPE VARCHAR(20)
		
		)
		WHERE FR_BRN_ID !='' AND FR_BRN_ID IS NOT NULL

			



		DECLARE ListGoods  CURSOR FOR
		SELECT PLAN_ID,
               TRADE_ID,
               GOODS_ID,
               DESCRIPTION,
               UNIT_ID,
               QUANTITY,
               PRICE,
               TOTAL_AMT,
               NOTES,
               REQDT_TYPE,
               NAME,
               DVDM_ID,
               HH_ID,
               CURRENCY,
               EXCHANGE_RATE,
               TAXES,
               SUP_ID,
               TRADE_TYPE,KHOI_ID,UNIT_NAME,SUP_NAME FROM @TABLE

		OPEN ListGoods

		Declare 
		@PLAN_ID	varchar(15),
		@TRADE_ID	varchar(15),
		@GOODS_ID	varchar(15),
		@DESCRIPTION nvarchar(500),
		@UNIT_ID	varchar(15),
		@QUANTITY	decimal(18),
		@PRICE	decimal(18,2),
		@TOTAL_AMT	decimal(18,2),		
		@NOTES	nvarchar(1000),
		@REQDT_TYPE VARCHAR(1),
		@NAME NVARCHAR(500),
		@DVDM_ID VARCHAR(20),
		@HH_ID VARCHAR(20),
		@CURRENCY	nvarchar(50),
		@EXCHANGE_RATE	decimal(18, 2),
		@TAXES	decimal(18, 2),
		@SUP_ID VARCHAR(20),
		@TRADE_TYPE VARCHAR(20),
		@KHOI_ID VARCHAR(20),
		@UNIT_NAME NVARCHAR(200),
		@SUP_NAME NVARCHAR(200),
		@l_SUP_ID VARCHAR(15)
		FETCH NEXT FROM ListGoods INTO @PLAN_ID,@TRADE_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY,
		@PRICE,@TOTAL_AMT,@NOTES, @REQDT_TYPE, @NAME,@DVDM_ID,@HH_ID,@CURRENCY,@EXCHANGE_RATE,@TAXES,@SUP_ID,@TRADE_TYPE,@KHOI_ID,@UNIT_NAME,@SUP_NAME
		WHILE @@FETCH_STATUS = 0	
		BEGIN	
			-- KIEM TRA NEU HINH THUC CHI DINH THAU LA THEO QUY DINH VCCB THI KHONG CHO PHEP CHON NHA CUNG CAP
			--IF(@TRADE_TYPE IS NOT NULL AND @TRADE_TYPE <>'' AND @TRADE_TYPE ='VCCB' AND (@SUP_ID IS NOT NULL AND @SUP_ID <>''))
			--BEGIN
			--	SELECT 'REQ-00001' Result, '' REQ_ID, N'Nếu hình thức mua sắm theo quy định VCCB thì bạn không được phép chọn nhà cung cấp. Vui lòng để trống nhà cung cấp' ErrorDesc 
			--	ROLLBACK TRANSACTION
			--	RETURN '-1'
			--END
			--- THEM NHA CUNG CAP
			IF(@SUP_NAME IS NOT NULL AND @SUP_NAME <>'')
			BEGIN
				IF(NOT EXISTS(SELECT * FROM CM_SUPPLIER WHERE SUP_NAME =@SUP_NAME))
				BEGIN
					EXEC SYS_CodeMasters_Gen 'CM_SUPPLIER', @l_SUP_ID out
					IF @l_SUP_ID='' OR @l_SUP_ID IS NULL GOTO ABORT
					INSERT INTO CM_SUPPLIER([DISCIPLINES],[SUP_ID],[SUP_CODE],[SUP_NAME],[SUP_TYPE_ID],[REGION_ID],[ADDR],[EMAIL],[TAX_NO],[TEL],[CONTACT_PERSON],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],ACC_NUM)
					VALUES('', @l_SUP_ID ,@l_SUP_ID ,@SUP_NAME ,NULL ,NULL ,'' ,'' ,'' ,'' ,'' ,'' ,'1' ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,'U' ,NULL,NULL,NULL)
					SET @SUP_ID =@l_SUP_ID
				END
				ELSE
				BEGIN
					SET @l_SUP_ID =(SELECT TOP 1 SUP_ID FROM CM_SUPPLIER WHERE SUP_NAME =@SUP_NAME)
					IF(@l_SUP_ID <> '' AND  @SUP_ID IS NOT NULL)
					BEGIN
						SET @SUP_ID =@l_SUP_ID
					END
				--END
				END
				
			END
			-------
			DECLARE @l_REQDT_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'PL_REQUEST_DOC_DT', @l_REQDT_ID out
			IF @l_REQDT_ID='' OR @l_REQDT_ID IS NULL GOTO ABORT
			
		
			--SET @TOTAL_AMT=(@PRICE * @QUANTITY * @EXCHANGE_RATE) + (@TAXES * @EXCHANGE_RATE)

			INSERT INTO dbo.PL_REQUEST_DOC_DT
			(
			    REQDT_ID,
			    REQ_ID,
			    PLAN_ID,
			    TRADE_ID,
			    GOODS_ID,
			    NAME,
			    DESCRIPTION,
			    REQDT_TYPE,
			    UNIT_ID,
			    QUANTITY,
			    PRICE,
			    TOTAL_AMT,
			    RECORD_STATUS,
			    MAKER_ID,
			    CREATE_DT,
			    AUTH_STATUS,
			    CHECKER_ID,
			    APPROVE_DT,
				DVDM_ID,
				HANGHOA_ID,
				CURRENCY,
				EXCHANGE_RATE,
				TAXES,SUP_ID,TRADE_TYPE,KHOI_ID,UNIT_NAME
			)
			VALUES
			(   @l_REQDT_ID,        -- REQDT_ID - varchar(15)
			    @p_REQ_ID,        -- REQ_ID - varchar(15)
			    @PLAN_ID,        -- PLAN_ID - varchar(15)
			    @TRADE_ID,        -- TRADE_ID - varchar(15)
			    @GOODS_ID,        -- GOODS_ID - varchar(15)
			    @NAME,       -- NAME - nvarchar(200)
			    @DESCRIPTION,       -- DESCRIPTION - nvarchar(500)
			    @REQDT_TYPE,        -- REQDT_TYPE - varchar(1)
			    @UNIT_ID,        -- UNIT_ID - varchar(15)
			    @QUANTITY,      -- QUANTITY - decimal(18, 0)
			    @PRICE,      -- PRICE - decimal(18, 0)
			    @TOTAL_AMT,      -- TOTAL_AMT - decimal(18, 0)
			    @p_RECORD_STATUS,        -- RECORD_STATUS - varchar(1)
			    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
			     CAST(@p_CREATE_DT AS DATE) , -- CREATE_DT - datetime
			    @p_AUTH_STATUS,        -- AUTH_STATUS - varchar(50)
			    @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
			     CAST(@P_APPROVE_DT AS DATE),   -- APPROVE_DT - datetime
			    @DVDM_ID,
				@HH_ID,
				@CURRENCY,
				@EXCHANGE_RATE,
				@TAXES,@SUP_ID,@TRADE_TYPE,@KHOI_ID,@UNIT_NAME
				)
			IF @@ERROR <> 0 GOTO ABORT1
		-- next Group_Id
			FETCH NEXT FROM ListGoods INTO @PLAN_ID,@TRADE_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,@QUANTITY,
		@PRICE,@TOTAL_AMT,@NOTES, @REQDT_TYPE, @NAME,@DVDM_ID,@HH_ID,@CURRENCY,@EXCHANGE_RATE,@TAXES,@SUP_ID,@TRADE_TYPE,@KHOI_ID,@UNIT_NAME,@SUP_NAME
		END
		CLOSE ListGoods
		DEALLOCATE ListGoods


			IF @@Error <> 0 GOTO ABORT
		--Insert into TABLE PL_REQUEST_DOC_DT
		UPDATE dbo.PL_REQUEST_DOC SET TOTAL_AMT = (SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID =@p_REQ_ID) WHERE PL_REQUEST_DOC.REQ_ID=@p_REQ_ID
		


		DECLARE ListTransfers  CURSOR FOR
		SELECT *
		FROM @TABLE_TRANSFER
		OPEN ListTransfers

		Declare 
		@FR_PLAN_ID	varchar(15),
		@FR_TRADE_ID	varchar(15),
		@FR_GOOD_ID	varchar(15),
		@FR_BRN_ID	varchar(15),
		@TO_BRN_ID	varchar(15),
		@TO_PLAN_ID	varchar(15),
		@TO_TRADE_ID	varchar(15),	
		@TO_GOOD_ID	varchar(15),	
		@QTY  DECIMAL(18,0),
		@FR_DEP_ID VARCHAR(20),
			@TO_DEP_ID VARCHAR(20),
			@FR_DVDM_ID VARCHAR(20),
			@TO_DVDM_ID VARCHAR(20),
			@FR_KHOI_ID VARCHAR(20),
			@TO_KHOI_ID VARCHAR(20),
			@FR_GD_TYPE VARCHAR(20),
			@TO_GD_TYPE VARCHAR(20)

		FETCH NEXT FROM ListTransfers INTO @FR_PLAN_ID,@FR_TRADE_ID,@FR_GOOD_ID,@FR_BRN_ID,@TO_BRN_ID,@TO_PLAN_ID,
		@TO_TRADE_ID,@TO_GOOD_ID,@QTY,@TOTAL_AMT, @NOTES,@FR_DEP_ID,@TO_DEP_ID,@FR_DVDM_ID,@TO_DVDM_ID,@FR_KHOI_ID,@TO_KHOI_ID,@FR_GD_TYPE,@TO_GD_TYPE
		WHILE @@FETCH_STATUS = 0	
		BEGIN
		
			
			DECLARE @l_REQ_TRANSFER_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'PL_REQUEST_TRANSFER', @l_REQ_TRANSFER_ID out
			IF @l_REQ_TRANSFER_ID='' OR @l_REQ_TRANSFER_ID IS NULL GOTO ABORT
	
			INSERT INTO dbo.PL_REQUEST_TRANSFER
			(
			    REQ_TRANSFER_ID,
			    REQ_DOC_ID,
			    FR_PLAN_ID,
			    FR_TRADE_ID,
			    FR_GOOD_ID,
			    FR_BRN_ID,
			    TO_BRN_ID,
			    TO_PLAN_ID,
			    TO_TRADE_ID,
			    TO_GOOD_ID,
			    QTY,
			    TOTAL_AMT,
			    NOTES,
			    AUTH_STATUS,
			    MAKER_ID,
			    CREATE_DT,
			    CHECKER_ID,
			    APPROVE_DT,
				FR_DEP_ID,
				TO_DEP_ID,
				FR_DVDM_ID,
				TO_DVDM_ID,
				FR_KHOI_ID,
				TO_KHOI_ID
			)
			VALUES
			(   @l_REQ_TRANSFER_ID,        -- REQ_TRANSFER_ID - varchar(15)
				@p_REQ_ID,        -- REQ_DOC_ID - varchar(15)
			    @FR_PLAN_ID,        -- FR_PLAN_ID - varchar(15)
			    @FR_TRADE_ID,        -- FR_TRADE_ID - varchar(15)
			    @FR_GOOD_ID,        -- FR_GOOD_ID - varchar(15)
			    @FR_BRN_ID,        -- FR_BRN_ID - varchar(15)
			    @TO_BRN_ID,        -- TO_BRN_ID - varchar(15)
			    @TO_PLAN_ID,        -- TO_PLAN_ID - varchar(15)
			    @TO_TRADE_ID,        -- TO_TRADE_ID - varchar(15)
			    @TO_GOOD_ID,        -- TO_GOOD_ID - varchar(15)
			    @QTY,      -- QTY - decimal(18, 0)
			    @TOTAL_AMT,      -- TOTAL_AMT - decimal(18, 0)
			    @NOTES,       -- NOTES - nvarchar(500)
			    @p_AUTH_STATUS,        -- AUTH_STATUS - varchar(1)
			    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
			     CAST(@p_CREATE_DT AS DATE), -- CREATE_DT - datetime
			    @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
			    CAST(@p_APPROVE_DT AS DATE),
				@FR_DEP_ID,
				@TO_DEP_ID,
				@FR_DVDM_ID,
				@TO_DVDM_ID,
				@FR_KHOI_ID,
				@TO_KHOI_ID  -- APPROVE_DT - datetime
			    )
			
			
			IF @@ERROR <> 0 GOTO ABORT1
		-- next Group_Id
			FETCH NEXT FROM ListTransfers INTO @FR_PLAN_ID,@FR_TRADE_ID,@FR_GOOD_ID,@FR_BRN_ID,@TO_BRN_ID,@TO_PLAN_ID,
		@TO_TRADE_ID,@TO_GOOD_ID,@QTY,@TOTAL_AMT, @NOTES,@FR_DEP_ID,@TO_DEP_ID,@FR_DVDM_ID,@TO_DVDM_ID,@FR_KHOI_ID,@TO_KHOI_ID,@FR_GD_TYPE,@TO_GD_TYPE
		END
		CLOSE ListTransfers
		DEALLOCATE ListTransfers

			IF @@Error <> 0 GOTO ABORT
		--Insert into TABLE PL_REQUEST_DOC_DT
		Exec sp_xml_preparedocument @hdoc Output,@p_ListCostCenter
		DECLARE ListCostCenters  CURSOR FOR
		SELECT *
		FROM OPENXML(@hDoc,'/Root/ListCostCenter',2)
		WITH 
		(
			COST_ID	varchar(15),	
			NOTES	nvarchar(1000)
		
		)
		WHERE COST_ID <>'DVDM-CHUNG'
		OPEN ListCostCenters
		
		Declare 
		@COST_ID	varchar(15)


		FETCH NEXT FROM ListCostCenters INTO @COST_ID, @NOTES
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			
			DECLARE @l_REQ_COST_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'PL_REQUEST_COSTCENTER', @l_REQ_COST_ID out
			IF @l_REQ_COST_ID='' OR @l_REQ_COST_ID IS NULL GOTO ABORT
	
			INSERT INTO dbo.PL_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)
			    @NOTES,       -- NOTES - nvarchar(500)
			    @p_AUTH_STATUS,        -- AUTH_STATUS - varchar(1)
			    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
			      CAST(@p_CREATE_DT AS DATE) , -- CREATE_DT - datetime
			    @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
			        CAST(@p_APPROVE_DT AS DATE)  -- APPROVE_DT - datetime
			 )
			
			
			IF @@ERROR <> 0 GOTO ABORT1
		-- next Group_Id
			FETCH NEXT FROM ListCostCenters INTO @COST_ID, @NOTES
		END
		CLOSE ListCostCenters
		DEALLOCATE ListCostCenters
	
COMMIT TRANSACTION
SELECT '0' as Result, @p_REQ_ID  REQ_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' AS Result, ''  REQ_ID, '' ErrorDesc
		RETURN '-1'
End
ABORT1:
BEGIN
		CLOSE ListGoods
		DEALLOCATE ListGoods
		CLOSE ListCostCenters
		DEALLOCATE ListCostCenters
		CLOSE ListCostCenters
		DEALLOCATE ListCostCenters
		ROLLBACK TRANSACTION
		SELECT '-1' AS Result, ''  REQ_ID, '' ErrorDesc
		RETURN '-1'
End
¿