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	NVARCHAR(20) = 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	NVARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(20)  = NULL,
@p_APPROVE_DT	NVARCHAR(20) = 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,
---------BAODNQ 23/6/2022: THÊM THAM SỐ PROCESS_ID----
@p_PROCESS_ID VARCHAR(10) = NULL,
@p_CREATOR_NOTES NVARCHAR(1000)  = NULL,
@p_ListGood XML,
@p_ListCostCenter XML,
@p_ListTransfer XML,
@p_ListAttachFile XML

AS
DECLARE @TEMP TABLE
			(
				[KEY] varchar(15),
				[REF_ID] varchar(15),
				[TYPE] varchar(50)
			)
SET @p_CREATE_DT =convert(datetime,@p_CREATE_DT,103)
SET @p_APPROVE_DT=convert(datetime,@p_APPROVE_DT,103)
SET @p_REQ_DT = convert(datetime,@p_REQ_DT,103)
  
  IF(NOT EXISTS(SELECT prd.REQ_ID FROM PL_REQUEST_DOC prd WHERE prd.REQ_ID = @p_REQ_ID AND prd.AUTH_STATUS IN ('E','R')))
  BEGIN
    SELECT '-1' Result, '' REQ_ID, N'Cập nhật thất bại! Tờ trình đã được gửi phê duyệt.' ErrorDesc 
		RETURN '-1'
  END
	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
	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(convert(datetime,@p_REQ_DT,103) < 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_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,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,CREATOR_NOTES = @p_CREATOR_NOTES,
		------------BAODNQ 23/6/2022 : SET THÊM GIÁ TRỊ PROCESS_ID---------
		PROCESS_ID = @p_PROCESS_ID
		WHERE REQ_ID=@p_REQ_ID

		----
		--- INSERT DATA CU VAO LOG : 14.10.2022. LUCTV
		INSERT INTO dbo.PL_REQUEST_DOC_DT_HIST SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID
		INSERT INTO dbo.PL_REQUEST_TRANSFER_HIST SELECT * FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID
		INSERT INTO dbo.PL_PROCESS
					(
					    REQ_ID,
					    PROCESS_ID,
					    CHECKER_ID,
					    APPROVE_DT,
					    PROCESS_DESC,
					    NOTES
					)
					VALUES
					(   @p_REQ_ID,        -- REQ_ID - varchar(15)
					    --@p_PROCESS_ID,        -- PROCESS_ID - varchar(10)
						'UPD',
					    @p_MAKER_ID,        -- CHECKER_ID - varchar(15)
					    GETDATE(), -- APPROVE_DT - datetime
					    N'Nhân viên cập nhật tờ trình' ,       -- PROCESS_DESC - nvarchar(1000)
					    N'Nhân viên cập nhật tờ trình thành công'        -- NOTES - nvarchar(1000)
					 )
		----

		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(4000),
			UNIT_ID	varchar(15)  ,
			QUANTITY	decimal(18,0)  ,
			PRICE	decimal(18,2)  ,
			TOTAL_AMT	decimal(18,2),	
			NOTES	nvarchar(4000),
			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(4000),
			UNIT_ID	varchar(15)  ,
			QUANTITY	decimal(18,0)  ,
			PRICE	decimal(18,2)  ,
			TOTAL_AMT	decimal(18,2),	
			NOTES	nvarchar(4000),
			REQDT_TYPE VARCHAR(1),
			NAME NVARCHAR(4000),
			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),
			FR_AMT_APP decimal(18),
      TO_AMT_APP decimal(18),
      FR_AMT_TF DECIMAL(18),
      TO_AMT_TF DECIMAL(18),
      FR_AMT_RECEIVE_TF DECIMAL(18),
      TO_AMT_RECEIVE_TF DECIMAL(18),
      FR_AMT_EXE DECIMAL(18),
      TO_AMT_EXE DECIMAL(18),
      FR_AMT_ETM DECIMAL(18),
      TO_AMT_ETM DECIMAL(18),
      FR_AMT_ETM_TMP decimal(18),	
  		TO_AMT_ETM_TMP decimal(18)	
		)
		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,
          FR_AMT_APP,
          TO_AMT_APP,
          FR_AMT_TF,
          TO_AMT_TF,
          FR_AMT_RECEIVE_TF,
          TO_AMT_RECEIVE_TF,
          FR_AMT_EXE,
          TO_AMT_EXE,
          FR_AMT_ETM,
          TO_AMT_ETM,
          FR_AMT_ETM_TMP,	
      		TO_AMT_ETM_TMP
		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),
			FR_AMT_APP decimal(18),
      TO_AMT_APP decimal(18),
      FR_AMT_TF DECIMAL(18),
      TO_AMT_TF DECIMAL(18),
      FR_AMT_RECEIVE_TF DECIMAL(18),
      TO_AMT_RECEIVE_TF DECIMAL(18),
      FR_AMT_EXE DECIMAL(18),
      TO_AMT_EXE DECIMAL(18),
      FR_AMT_ETM DECIMAL(18),
      TO_AMT_ETM DECIMAL(18),
      FR_AMT_ETM_TMP decimal(18),	
  		TO_AMT_ETM_TMP decimal(18)	
		
		)
		--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(4000),
		@UNIT_ID	varchar(15),
		@QUANTITY	decimal(18),
		@PRICE	decimal(18,2),
		@TOTAL_AMT	decimal(18,2),		
		@NOTES	nvarchar(4000),
		@REQDT_TYPE VARCHAR(1),
		@NAME NVARCHAR(4000),
		@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)
			     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
			    @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),
    @FR_AMT_APP decimal(18),
    @TO_AMT_APP decimal(18),
    @FR_AMT_TF DECIMAL(18),
    @TO_AMT_TF DECIMAL(18),
    @FR_AMT_RECEIVE_TF DECIMAL(18),
    @TO_AMT_RECEIVE_TF DECIMAL(18),
    @FR_AMT_EXE DECIMAL(18),
    @TO_AMT_EXE DECIMAL(18),
    @FR_AMT_ETM DECIMAL(18),
    @TO_AMT_ETM DECIMAL(18),
    @FR_AMT_ETM_TMP decimal(18),	
		@TO_AMT_ETM_TMP decimal(18)	

		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,
    @FR_AMT_APP,@TO_AMT_APP,@FR_AMT_TF,@TO_AMT_TF,@FR_AMT_RECEIVE_TF,@TO_AMT_RECEIVE_TF,@FR_AMT_EXE,@TO_AMT_EXE,@FR_AMT_ETM,@TO_AMT_ETM,@FR_AMT_ETM_TMP,@TO_AMT_ETM_TMP
		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,
        FR_AMT_APP,
        TO_AMT_APP,
        FR_AMT_TF,
        TO_AMT_TF,
        FR_AMT_RECEIVE_TF,
        TO_AMT_RECEIVE_TF,
        FR_AMT_EXE,
        TO_AMT_EXE,
        FR_AMT_ETM,
        TO_AMT_ETM,
        FR_AMT_ETM_TMP,
        TO_AMT_ETM_TMP
			)
			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)
			    CONVERT(DATETIME, @p_CREATE_DT, 103),
				--CAST(@p_CREATE_DT AS DATE), -- CREATE_DT - datetime
			    @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
			    CONVERT(DATETIME, @p_APPROVE_DT, 103),
				--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
        @FR_AMT_APP,
        @TO_AMT_APP,
        @FR_AMT_TF,
        @TO_AMT_TF,
        @FR_AMT_RECEIVE_TF,
        @TO_AMT_RECEIVE_TF,
        @FR_AMT_EXE,
        @TO_AMT_EXE,
        @FR_AMT_ETM,
        @TO_AMT_ETM,
        @FR_AMT_ETM_TMP,
        @TO_AMT_ETM_TMP
      )
			
			
			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,
    @FR_AMT_APP,@TO_AMT_APP,@FR_AMT_TF,@TO_AMT_TF,@FR_AMT_RECEIVE_TF,@TO_AMT_RECEIVE_TF,@FR_AMT_EXE,@TO_AMT_EXE,@FR_AMT_ETM,@TO_AMT_ETM,@FR_AMT_ETM_TMP,@TO_AMT_ETM_TMP
		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)
			      convert(datetime,@p_CREATE_DT,103) , -- CREATE_DT - datetime
			    @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
			        convert(datetime,@p_APPROVE_DT,103)  -- APPROVE_DT - datetime
			 )
			
			
			IF @@ERROR <> 0 GOTO ABORT1
		-- next Group_Id
			FETCH NEXT FROM ListCostCenters INTO @COST_ID, @NOTES
		END
		CLOSE ListCostCenters
		DEALLOCATE ListCostCenters


		-- Insert into TABLE PL_REQUEST_DOC_FILE
		DECLARE @tableAttachFile TABLE(
			PL_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_ListAttachFile

		INSERT INTO @tableAttachFile
		SELECT *
		FROM OPENXML(@fDoc,'/Root/ListAttachFile',2)
		WITH 
		(
			PL_REQUEST_DOC_FILE_ID	varchar(20)  ,
			IS_VIEW	bit,
			REQ_ID varchar(20),
			NOTES  nvarchar(200)
		)
    DELETE PL_REQUEST_DOC_FILE WHERE REQ_ID = @p_REQ_ID
		DECLARE ListAttachFile  CURSOR FOR
		SELECT * FROM @tableAttachFile
		OPEN ListAttachFile

		Declare 
			@PL_REQUEST_DOC_FILE_ID	varchar(20)  ,
			@IS_VIEW	bit,
			@REQ_ID varchar(20),
			@_NOTES nvarchar(200)
		
		FETCH NEXT FROM ListAttachFile INTO @PL_REQUEST_DOC_FILE_ID,@IS_VIEW,@REQ_ID,@_NOTES
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			DECLARE @l_File_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'PL_REQUEST_DOC_FILE', @l_File_ID out
			IF @l_File_ID='' OR @l_File_ID IS NULL GOTO ABORT


			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_File_ID, 'PL_REQUEST_DOC_FILE')

				
				INSERT INTO dbo.PL_REQUEST_DOC_FILE
				(
					 [PL_REQUEST_DOC_FILE_ID]
					,[IS_VIEW]
					,[REQ_ID]
					,[NOTES]
				)	
				VALUES
				(   
					@l_File_ID
					,isnull(@IS_VIEW,0)
					,@p_REQ_ID
					,@_NOTES
				)
			
			IF @@ERROR <> 0 GOTO ABORT1
			FETCH NEXT FROM ListAttachFile INTO @PL_REQUEST_DOC_FILE_ID,@IS_VIEW,@REQ_ID,@_NOTES
		END
		CLOSE ListAttachFile
		DEALLOCATE ListAttachFile
		
		IF @@Error <> 0 GOTO ABORT

		--IF (SELECT COUNT(*) FROM @TEMP) = 0
		--	INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_File_ID, 'PL_REQUEST_DOC_FILE')
	
COMMIT TRANSACTION
IF (SELECT COUNT(*) FROM @TEMP) = 0
BEGIN
	SELECT '0' as Result, @p_REQ_ID  REQ_ID,'', '' , @p_REQ_CODE AS ErrorDesc
	RETURN '0'
END
ELSE
BEGIN
	SELECT '0' as Result, @p_REQ_ID  REQ_ID,REF_ID, [TYPE] , @p_REQ_CODE AS ErrorDesc FROM @TEMP
	RETURN '0'
END
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
GO