ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_PROCESS_Upd]
@p_REQ_ID VARCHAR(15)=NULL,
@p_MAKER_ID	varchar(20)  = NULL,
@p_BRANCH_ID VARCHAR(15)=NULL,
@p_TC_NOTES NVARCHAR(500),
@p_KT_NOTES NVARCHAR(500),
@p_ListCostCenter XML,
@p_ListTransfer XML
AS
	
	
		
	DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID VARCHAR(15),@ROLE_ID VARCHAR(20),
	@PROCESS_CURR VARCHAR(10),
	@STEP_CURR INT,
	@STEP_NEXT INT,
	@PROCESS_NEXT VARCHAR(10)

	SET @PROCESS_CURR = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C')
	SET @PROCESS_NEXT = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)

	SET @DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)
	SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)
	SET @ROLE_ID=(SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)
		BEGIN TRANSACTION
		IF(@PROCESS_CURR='KT')
		BEGIN
			IF(@p_KT_NOTES IS NULL OR @p_KT_NOTES ='')
			BEGIN
				SELECT '-1' Result, '' REQ_ID, N'Vui lòng nhập ý kiến của phòng Kế Toán' ErrorDesc 
				ROLLBACK TRANSACTION
				RETURN '-1'
			END
			UPDATE dbo.PL_REQUEST_DOC SET KT_NOTES=@p_KT_NOTES WHERE REQ_ID=@p_REQ_ID
		END
		ELSE IF(@PROCESS_CURR='TC')
		BEGIN
			UPDATE dbo.PL_REQUEST_DOC SET TC_NOTES=@p_TC_NOTES WHERE REQ_ID=@p_REQ_ID
		END
		ELSE
		BEGIN
		IF @@Error <> 0 GOTO ABORT
		Declare @hdoc INT
		
		Declare 
		@REQ_TRANSFER_ID	varchar(15),
		@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),
	
		@TOTAL_AMT  DECIMAL(18,0),
		@NOTES NVARCHAR(500),
		@AUTH_STATUS VARCHAR(5),
		@COST_ID	varchar(15),
		@REQ_COST_ID	varchar(15),
		@DVDM_ID VARCHAR(20)

		IF(EXISTS(SELECT REQ_COST_ID FROM dbo.PL_REQUEST_COSTCENTER WHERE REQ_ID=@p_REQ_ID AND AUTH_STATUS <>'A'))
		BEGIN 
				--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 
			(
				REQ_COST_ID VARCHAR(15),
				COST_ID	varchar(15),	
				AUTH_STATUS VARCHAR(5),
				NOTES	nvarchar(1000)
				
			)
			OPEN ListCostCenters

			FETCH NEXT FROM ListCostCenters INTO @REQ_COST_ID,@COST_ID,@AUTH_STATUS, @NOTES
			WHILE @@FETCH_STATUS = 0	
			BEGIN
				IF(EXISTS(SELECT REQ_COST_ID FROM dbo.PL_REQUEST_COSTCENTER WHERE COST_ID=@COST_ID AND REQ_ID=@p_REQ_ID AND AUTH_STATUS <> 'A'))
				BEGIN
						UPDATE dbo.PL_REQUEST_COSTCENTER SET NOTES=@NOTES WHERE REQ_COST_ID=@REQ_COST_ID AND COST_ID=@COST_ID 				
				END
			
				
				
				IF @@ERROR <> 0 GOTO ABORT1
			-- next Group_Id
				FETCH NEXT FROM ListCostCenters INTO @REQ_COST_ID,@COST_ID,@AUTH_STATUS, @NOTES
			END
			CLOSE ListCostCenters
			DEALLOCATE ListCostCenters
			
			--IF(NOT EXISTS( SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@PROCESS_CURR AND REQ_ID=@p_REQ_ID AND STATUS='C'))
			--BEGIN
						
			--			UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='C' WHERE PROCESS_ID=@PROCESS_NEXT AND REQ_ID=@p_REQ_ID
			--			UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID
						
			--END
		
		END

		
		IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND AUTH_STATUS <>'A'))
		BEGIN
			DECLARE @TABLE TABLE(
				REQ_TRANSFER_ID VARCHAR(15),
				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),	
				AUTH_STATUS VARCHAR(10),
				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		
			SELECT *
			FROM OPENXML(@hDoc,'/Root/ListTransfer',2)
			WITH 
			(
				REQ_TRANSFER_ID VARCHAR(15),
				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),	
				AUTH_STATUS VARCHAR(10),
				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)
		
			)
			
			--IF(EXISTS(SELECT * FROM(
			--SELECT TB.FR_TRADE_ID, SUM(TB.TOTAL_AMT) AS TOTAL_TRANSFER,SUM (ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0)- ISNULL(PT.AMT_TF,0)- ISNULL(PT.AMT_ETM,0)   ) AS TOTAL_AMT FROM @TABLE TB 
			--LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.TRADE_ID=TB.FR_TRADE_ID
			--GROUP BY TB.FR_TRADE_ID
			--) TEMP		
			--WHERE TEMP.TOTAL_TRANSFER > TEMP.TOTAL_AMT
			--))
			--BEGIN
			--		SELECT '-1' Result, '' REQ_ID, N'Số tiền điều chuyển lớn hơn số tiền dự kiến còn lại của hạng mục ngân sách' ErrorDesc 
			--		ROLLBACK TRANSACTION
			--		RETURN '-1'
			--END

			DECLARE ListTransfers  CURSOR FOR
			SELECT REQ_TRANSFER_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,
                   AUTH_STATUS,
                   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 @TABLE
			OPEN ListTransfers


			FETCH NEXT FROM ListTransfers INTO @REQ_TRANSFER_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,@AUTH_STATUS, @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
				--- KIEM TRA NGAN SACH DIEU CHUYEN TAI DVDC PHAI BAT BUOC NHAP
				IF(@FR_GOOD_ID IS NULL OR @FR_GOOD_ID ='')
				BEGIN
						SELECT '-1' Result, '' REQ_ID, N'Lưới điều chuyển ngân sách: Bạn là đơn vị điều chuyển ngân sách. Vui lòng chọn hạng mục ngân sách chuyển!' ErrorDesc 
						ROLLBACK TRANSACTION
						RETURN '-1'
				END
				----
				DECLARE @FR_GD_TYPE_NAME NVARCHAR(100),@TO_GD_TYPE_NAME NVARCHAR(100)
				IF(@FR_GOOD_ID IS NOT NULL AND @FR_GOOD_ID<>'')
				IF(NOT EXISTS(SELECT * FROM dbo.CM_GOODSTYPE WHERE GD_TYPE_ID= @FR_GD_TYPE AND @TO_GD_TYPE IN (SELECT value FROM dbo.wsiSplit(NOTES,';')) )  )
				BEGIN
				
				SET @FR_GD_TYPE_NAME=(SELECT GD_TYPE_NAME FROM dbo.CM_GOODSTYPE WHERE GD_TYPE_ID=@FR_GD_TYPE)
				SET @TO_GD_TYPE_NAME=(SELECT GD_TYPE_NAME FROM dbo.CM_GOODSTYPE WHERE GD_TYPE_ID=@TO_GD_TYPE)
					
					ROLLBACK TRANSACTION
					SELECT '-1' AS Result , ''  REQ_ID, N'Không được điều chuyển từ ' + @FR_GD_TYPE_NAME +N' sang ' + @TO_GD_TYPE_NAME ErrorDesc
					RETURN '-1'
					CLOSE ListTransfers
					DEALLOCATE ListTransfers
				END
				---  KIEM TRA HAN MUC CO HOP LE HAY KHONG
				IF(EXISTS(SELECT * FROM(
				SELECT TB.FR_TRADE_ID, SUM(TB.TOTAL_AMT) AS TOTAL_TRANSFER,SUM (ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0)- ISNULL(PT.AMT_TF,0)- ISNULL(PT.AMT_ETM,0)   ) AS TOTAL_AMT FROM @TABLE TB 
				LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.TRADE_ID=TB.FR_TRADE_ID
				GROUP BY TB.FR_TRADE_ID
				) TEMP		
				WHERE TEMP.TOTAL_TRANSFER > TEMP.TOTAL_AMT
				))
				BEGIN
						SELECT '-1' Result, '' REQ_ID, N'Số tiền điều chuyển lớn hơn số tiền dự kiến còn lại của hạng mục ngân sách' ErrorDesc 
						ROLLBACK TRANSACTION
						RETURN '-1'
				END
				----
				IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_TRANSFER_ID=@REQ_TRANSFER_ID AND AUTH_STATUS <> 'A'))
				BEGIN
					UPDATE dbo.PL_REQUEST_TRANSFER SET FR_GOOD_ID=@FR_GOOD_ID,FR_PLAN_ID=@FR_PLAN_ID,FR_TRADE_ID=@FR_TRADE_ID, NOTES=@NOTES,TOTAL_AMT=@TOTAL_AMT
					,FR_DVDM_ID=@FR_DVDM_ID,FR_KHOI_ID=@FR_KHOI_ID
					 WHERE REQ_TRANSFER_ID=@REQ_TRANSFER_ID	
				END
				
				
				IF @@ERROR <> 0 GOTO ABORT1
			-- next Group_Id
				FETCH NEXT FROM ListTransfers INTO @REQ_TRANSFER_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,@AUTH_STATUS, @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
	
			
			
		END
		END
		
		
		
		
		
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 ListCostCenters
		DEALLOCATE ListCostCenters
		CLOSE ListTransfers
		DEALLOCATE ListTransfers
		ROLLBACK TRANSACTION
		SELECT '-1' AS Result ,'' REQ_ID, '' ErrorDesc
		RETURN '-1'
End