ALTER PROCEDURE [dbo].[PL_REQ_DOC_UPDATE_AFTER_APPROVE]
@p_REQ_ID	VARCHAR(15)
AS

	
BEGIN TRANSACTION
	
DECLARE
					@PLAN_ID VARCHAR(15),
					@TRADE_ID VARCHAR(15),
					@GOOD_ID VARCHAR(15),
					@QTY DECIMAL(18,0),
					@TOTAL_AMT DECIMAL(18,2),
					@REQ_DT DATETIME
					IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND IS_BACKDAY=1))
					BEGIN
						SET @REQ_DT = (SELECT REQ_DT FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) 
						UPDATE dbo.PL_REQUEST_DOC SET EFFEC_DT=@REQ_DT,APPROVE_DT=@REQ_DT WHERE REQ_ID=@p_REQ_ID
						UPDATE dbo.PL_REQUEST_PROCESS SET APPROVE_DT=@REQ_DT WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID <>'APPROVE'
					END
					ELSE
						UPDATE dbo.PL_REQUEST_DOC SET EFFEC_DT=GETDATE() WHERE REQ_ID=@p_REQ_ID


					

					IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND (TRADE_ID IS NULL OR TRADE_ID='')))
					BEGIN
						
						DECLARE @BRANCH_ID VARCHAR(20),@DEP_ID VARCHAR(20),@l_PLAN_ID VARCHAR(20),@l_PLAN_CODE VARCHAR(20),@DVDM_ID VARCHAR(20),@GD_ID VARCHAR(20),
						@l_PLAN_NAME NVARCHAR(200),@CREATE_DT DATETIME,@DEP_CODE VARCHAR(20),@MARKER_ID VARCHAR(20),@l_TRADE_ID VARCHAR(20)

						SELECT @BRANCH_ID=BRANCH_ID,@DEP_ID=DEP_ID,@CREATE_DT=CREATE_DT,@MARKER_ID=MAKER_ID  FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID
						SELECT @DEP_CODE=DEP_CODE FROM dbo.CM_DEPARTMENT WHERE DEP_ID=@DEP_ID


						DECLARE lstPLAN CURSOR FOR
						SELECT DVDM_ID FROM dbo.CM_GOOD_DVDM WHERE GD_ID IN (SELECT GOODS_ID  FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND (TRADE_ID IS NULL OR TRADE_ID=''))
						OPEN lstPLAN

						FETCH NEXT FROM lstPLAN INTO @DVDM_ID
						WHILE @@FETCH_STATUS=0
						BEGIN

							SET @l_PLAN_NAME=N'KẾ HOẠCH NGÂN SÁCH '+ CAST( YEAR(@CREATE_DT) AS VARCHAR(10))
							SET @l_PLAN_CODE= @DEP_CODE+(SELECT RIGHT(CAST(  YEAR(@CREATE_DT) AS VARCHAR(10)),2))

		
							EXEC SYS_CodeMasters_Gen 'PL_MASTER', @l_PLAN_ID out
							
								INSERT INTO dbo.PL_MASTER
								(
									PLAN_ID, PLAN_CODE,PLAN_NAME,BRANCH_ID,DEPT_ID,EFFECT_DT,PLAN_TYPE,TOTAL_AMT,APPROVE_VALUE, 
									VERSON,YEAR,STATUS,NOTES,RECORD_STATUS, MAKER_ID,CREATE_DT,AUTH_STATUS,CHECKER_ID,APPROVE_DT,PLAN_TYPE_ID,COST_ID)
								VALUES
								(   @l_PLAN_ID,@l_PLAN_CODE,@l_PLAN_NAME,@BRANCH_ID,@DEP_ID,GETDATE(),'',0, 0,      
									1,CAST( YEAR(@CREATE_DT) AS VARCHAR(10)),'1', N'','1', @MARKER_ID, GETDATE(),'A', @MARKER_ID, GETDATE(), '',@DVDM_ID  
								)

								DECLARE lstTRADE CURSOR FOR
								SELECT DT.GOODS_ID FROM dbo.PL_REQUEST_DOC_DT  DT
								LEFT JOIN dbo.CM_GOOD_DVDM CGD ON CGD.GD_ID=DT.GOODS_ID
								WHERE REQ_ID=@p_REQ_ID AND (TRADE_ID IS NULL OR TRADE_ID='') AND CGD.DVDM_ID=@DVDM_ID
								GROUP BY DT.GOODS_ID
								
								OPEN lstTRADE
								FETCH NEXT FROM lstTRADE INTO @GD_ID
								WHILE @@FETCH_STATUS=0
								BEGIN
									
									EXEC SYS_CodeMasters_Gen 'PL_TRADEDETAIL', @l_TRADE_ID OUT
									INSERT INTO dbo.PL_TRADEDETAIL
										(
											TRADE_ID,
											PLAN_ID,
											GOODS_ID,
											QUANTITY,
											AMT_APP,
											NOTES,
											RECORD_STATUS,
											MAKER_ID,
											CREATE_DT,
											AUTH_STATUS,
											CHECKER_ID,
											APPROVE_DT
										)
										VALUES
										(   @l_TRADE_ID,        -- TRADE_ID - varchar(15)
											@l_PLAN_ID,        -- PLAN_ID - varchar(15)
											@GD_ID,        -- GOODS_ID - varchar(15)
											0,      -- QUANTITY - decimal(18, 0)
											0,      -- PRICE - decimal(18, 0)			  
											'',       -- NOTES - nvarchar(1000)
											'1',        -- RECORD_STATUS - varchar(1)
											@MARKER_ID,        -- MAKER_ID - varchar(12)
											GETDATE(), -- CREATE_DT - datetime
											'A',        -- AUTH_STATUS - varchar(50)
											@MARKER_ID,        -- CHECKER_ID - varchar(12)
											GETDATE()  -- APPROVE_DT - datetime
										)	
                                    
									UPDATE dbo.PL_REQUEST_DOC_DT SET TRADE_ID=@l_TRADE_ID,PLAN_ID=@l_PLAN_ID WHERE REQ_ID=@p_REQ_ID AND GOODS_ID=@GD_ID AND (TRADE_ID IS NULL OR TRADE_ID='')
									UPDATE dbo.PL_REQUEST_TRANSFER SET TO_TRADE_ID=@l_TRADE_ID,TO_PLAN_ID=@l_PLAN_ID WHERE REQ_DOC_ID=@p_REQ_ID AND TO_GOOD_ID=@GD_ID AND (TO_TRADE_ID IS NULL OR TO_TRADE_ID='')

								    FETCH NEXT FROM lstTRADE INTO @GD_ID
								END
								CLOSE lstTRADE
								DEALLOCATE lstTRADE


						 FETCH NEXT FROM lstPLAN INTO @DVDM_ID
						END
						CLOSE lstPLAN
						DEALLOCATE lstPLAN

						
					END

					IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))
					BEGIN
						DECLARE @FR_PLAN_ID VARCHAR(20),@FR_TRADE_ID VARCHAR(20),@FR_GOOD_ID VARCHAR(20)
						,@TO_PLAN_ID VARCHAR(20),@TO_TRADE_ID VARCHAR(20),@TO_GOOD_ID VARCHAR(20)
						DECLARE TransferDT CURSOR FOR 
						SELECT FR_PLAN_ID,FR_TRADE_ID,FR_GOOD_ID,TO_TRADE_ID,TO_PLAN_ID,TO_GOOD_ID,TOTAL_AMT  FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID
						OPEN TransferDT
					FETCH NEXT FROM TransferDT INTO @FR_PLAN_ID,@FR_TRADE_ID,@FR_GOOD_ID,@TO_TRADE_ID,@TO_PLAN_ID,@TO_GOOD_ID,@TOTAL_AMT
					WHILE @@FETCH_STATUS = 0
					BEGIN
							UPDATE dbo.PL_TRADEDETAIL SET AMT_TF=ISNULL(AMT_TF,0)+@TOTAL_AMT WHERE PLAN_ID=@FR_PLAN_ID AND TRADE_ID=@FR_TRADE_ID AND GOODS_ID=@FR_GOOD_ID
							UPDATE dbo.PL_TRADEDETAIL SET AMT_RECEIVE_TF=ISNULL(AMT_RECEIVE_TF,0)+@TOTAL_AMT WHERE PLAN_ID=@TO_PLAN_ID AND TRADE_ID=@TO_TRADE_ID AND GOODS_ID=@TO_GOOD_ID
						FETCH NEXT FROM TransferDT INTO @FR_PLAN_ID,@FR_TRADE_ID,@FR_GOOD_ID,@TO_TRADE_ID,@TO_PLAN_ID,@TO_GOOD_ID,@TOTAL_AMT
					END 
					CLOSE TransferDT
					DEALLOCATE TransferDT
					
					END


					DECLARE  RequestDT CURSOR FOR
					SELECT PLAN_ID,TRADE_ID,GOODS_ID,QUANTITY,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID
					OPEN RequestDT
					FETCH NEXT FROM RequestDT INTO @PLAN_ID,@TRADE_ID,@GOOD_ID,@QTY,@TOTAL_AMT
					WHILE @@FETCH_STATUS = 0
					BEGIN
						UPDATE dbo.PL_TRADEDETAIL SET QUANTITY_ETM=ISNULL(QUANTITY_ETM,0)+@QTY,AMT_ETM=ISNULL(AMT_ETM,0)+@TOTAL_AMT WHERE PLAN_ID=@PLAN_ID AND TRADE_ID=@TRADE_ID AND GOODS_ID=@GOOD_ID
						FETCH NEXT FROM RequestDT INTO @PLAN_ID,@TRADE_ID,@GOOD_ID,@QTY,@TOTAL_AMT
					END 
					CLOSE RequestDT
					DEALLOCATE RequestDT

				

		
COMMIT TRANSACTION
RETURN 1
ABORT:
BEGIN
		ROLLBACK TRANSACTION	
		RETURN 1
End
ABORT1:
BEGIN
		CLOSE RequestDT
		DEALLOCATE RequestDT
		CLOSE TransferDT
		DEALLOCATE TransferDT
		ROLLBACK TRANSACTION	
		RETURN 1	
End