ALTER PROCEDURE dbo.PL_REQUEST_DOC_Ins
@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(4000)=NULL,
@p_REQ_REASON NVARCHAR(4000)=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	varchar(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,
@p_CREATOR_NOTES NVARCHAR(1000)  = NULL,
@p_ListGood XML,
@p_ListCostCenter XML,
@p_ListTransfer XML,
@p_ListAttachFile XML

AS	
 BEGIN TRANSACTION

		DECLARE @DEP_ID VARCHAR(15) = (SELECT SECUR_CODE FROM TL_USER WHERE TLNANME = @p_MAKER_ID)

		-- TABLE INSERT FILE
		DECLARE @TEMP TABLE
		(
			[KEY] varchar(15),
			[REF_ID] varchar(15),
			[TYPE] varchar(50)
		)


		IF(@p_REQ_DT IS NULL)
		BEGIN
			SELECT 'REQ-00001' Result, '' REQ_ID, N'Ngày yêu cầu tờ trình không được phép để trống' ErrorDesc 
			ROLLBACK TRANSACTION
			RETURN '-1'
		END
		--IF(CAST(@p_REQ_DT AS DATE) > CAST(GETDATE() AS DATE))
		IF(CONVERT(DATETIME,@p_REQ_DT,103) > CONVERT(DATETIME,GETDATE(),103))
		BEGIN
			SELECT 'REQ-00001' Result, '' REQ_ID, N'Ngày yêu cầu không lớn hơn ngày hiện tại' ErrorDesc 
			ROLLBACK TRANSACTION
			RETURN '-1'
		END

		exec [dbo].[PL_REQUEST_DOC_CODE_GenKey] @p_BRANCH_ID,@DEP_ID,'TTCT', @p_REQ_CODE out
		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ắc buộc nhập' ErrorDesc 
			ROLLBACK TRANSACTION
			RETURN '-1'
		END
		IF EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE [REQ_CODE] = @p_REQ_CODE)
		BEGIN
			SELECT '-1' Result, '' REQ_ID, N'Tờ trình số:' + @p_REQ_CODE + N' đã tồn tại trong hệ thống!"' ErrorDesc 
			ROLLBACK TRANSACTION
			RETURN '-1'
		END
		DECLARE @sErrorCode VARCHAR(20)
		--insert master				
		DECLARE @l_REQ_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'PL_REQUEST_DOC', @l_REQ_ID out
		IF @l_REQ_ID='' OR @l_REQ_ID IS NULL GOTO ABORT


		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 @DEP_ID=@DEP_CREATE
		SET @p_BRANCH_ID=@BRANCH_CREATE

		--IF(CAST(@p_REQ_DT AS DATE) < CAST(GETDATE() AS DATE))
		IF(CONVERT(DATETIME,@p_REQ_DT,103) < CONVERT(DATETIME,GETDATE(),103))
			SET @p_IS_BACKDAY=1;
		ELSE
			SET  @p_IS_BACKDAY=0

		INSERT INTO dbo.PL_REQUEST_DOC
		(
		    REQ_ID,
		    REQ_CODE,
		    REQ_NAME,
		    REQ_DT,
		    REQ_TYPE,
		    REQ_CONTENT,
		    REQ_REASON,
		    BRANCH_ID,
		    TOTAL_AMT,
		    NOTES,
		    RECORD_STATUS,
		    MAKER_ID,
		    CREATE_DT,
		    AUTH_STATUS,
		    CHECKER_ID,
		    APPROVE_DT,
			DVDM_APP_ID,
			REQ_PARENT_ID,
			BRANCH_FEE,
			IS_BACKDAY,
			PROCESS_ID,
			DEP_ID,
			DEP_FEE,
			BRANCH_CREATE,
			DEP_CREATE,
			REQ_LINE,SIGN_USER,
			IS_CHECKALL,
			BASED_CONTENT,PL_BASED_ID,
      CREATOR_NOTES
		)
		VALUES
		(   @l_REQ_ID,        -- REQ_ID - varchar(15)
		    @p_REQ_CODE,        -- REQ_CODE - varchar(100)
		    @p_REQ_NAME,       -- REQ_NAME - nvarchar(200)
			--  CAST(@p_REQ_DT AS DATE), -- REQ_DT - datetime
			CONVERT(DATETIME,@p_REQ_DT,103),
		    @p_REQ_TYPE,         -- REQ_TYPE - int
		    @p_REQ_CONTENT,       -- REQ_CONTENT - nvarchar(1000)
		    @p_REQ_REASON,       -- REQ_REASON - nvarchar(500)
		    @p_BRANCH_ID,        -- BRANCH_ID - varchar(15)
		    @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),
		    --CAST(@p_CREATE_DT AS DATE), -- CREATE_DT - datetime
		    'E',        -- AUTH_STATUS - varchar(50)
		    @p_CHECKER_ID,        -- CHECKER_ID - varchar(12)
		    --CAST(@P_APPROVE_DT AS DATE),  -- APPROVE_DT - datetime
			CONVERT(DATETIME,@P_APPROVE_DT,103),
			@p_DVDM_ID,
			@p_REQ_PARENT_ID,
			@p_BRANCH_FEE,
			@p_IS_BACKDAY,
			'',
			@p_DEP_ID,
			@p_DEP_FEE_ID,
			@BRANCH_CREATE,
			@DEP_CREATE,
			@p_REQ_LINE,
			@p_SIGN_USER,
			@p_IS_CHECKALL,
			@p_BASED_CONTENT,@p_PL_BASED_ID,
      @p_CREATOR_NOTES
	  )
		
		
		IF @@Error <> 0 GOTO ABORT
		DECLARE @TABLE TABLE(
			PLAN_ID	varchar(15)  ,
			TRADE_ID	varchar(15)  ,
			GOODS_ID	varchar(15)  ,
			[DESCRIPTION] nvarchar(2000),
			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(2000),
			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)
		)
		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(2000),
		@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(250),
		@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'Lưới hàng hóa: Nếu chọn 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
			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)
			    @l_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),
				--CAST(@p_CREATE_DT AS DATE) , -- CREATE_DT - datetime
			    @p_AUTH_STATUS,        -- AUTH_STATUS - varchar(50)
			    @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
			    CONVERT(DATETIME, @P_APPROVE_DT, 103),
				--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 =@l_REQ_ID) WHERE PL_REQUEST_DOC.REQ_ID=@l_REQ_ID
		
		Exec sp_xml_preparedocument @hdoc Output,@p_ListTransfer
		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
			IF (@TO_BRN_ID IS NULL OR @TO_BRN_ID = '')
        SET @TO_BRN_ID = @BRANCH_CREATE
      IF (@BRANCH_CREATE = 'DV0001' AND (@TO_DEP_ID IS NULL OR @TO_DEP_ID = ''))
        SET @TO_DEP_ID = @DEP_CREATE
      IF (@BRANCH_CREATE = 'DV0001' AND (@TO_KHOI_ID IS NULL OR @TO_KHOI_ID = ''))
        SET @TO_KHOI_ID = (SELECT cd.KHOI_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_ID = @DEP_CREATE)
			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)
			    @l_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),
				CASE WHEN @FR_BRN_ID = 'DV0001' THEN @FR_DEP_ID ELSE NULL END,
				@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)
			    @l_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),
				--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)  -- 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(
			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_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)
		)

		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)
				,@l_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


		
COMMIT TRANSACTION
IF (SELECT COUNT(*) FROM @TEMP) = 0
BEGIN
	SELECT '0' as Result, @l_REQ_ID  REQ_ID,'', '' , @p_REQ_CODE AS ErrorDesc
	RETURN '0'
END
ELSE
BEGIN
	SELECT '0' as Result, @l_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