CREATE PROCEDURE [dbo].[PL_TRADE_TRANFER_TO_BRANCH]
	@P_REF_CODE VARCHAR(20),
    @p_BRANCH_ID VARCHAR(20),
	@p_DEP_ID VARCHAR(20),
	@p_YEAR INT
AS

BEGIN TRY
BEGIN TRANSACTION;

DECLARE @IS_UPDATE BIT 





DECLARE @PLAN_CODE VARCHAR(20),
		@PLAN_NAME NVARCHAR(200),
		@YEAR INT,
        @PLAN_TYPE_ID VARCHAR(15),
        @COST_ID VARCHAR(15),
        @BRANCH_ID VARCHAR(15),
		@BRANCH_CODE VARCHAR(15),
        @DEP_ID VARCHAR(15),
		@DEP_CODE VARCHAR(15),
        @GOOD_ID VARCHAR(15),
        @QTY INT,
        @TOTAL_AMT DECIMAL(18, 2),
		@AMT_ETM DECIMAL(18, 2),
		@AMT_EXE DECIMAL(18, 2),  
		@AMT_TF DECIMAL(18, 2),
		@AMT_RECIVE_TF DECIMAL(18, 2),
        @NOTES NVARCHAR(1000),
		@IMP_AMT NUMERIC(18, 2),
		@IMP_QTY NUMERIC(18, 0),
		@MARKER_ID VARCHAR(15),
		@EDIT_DT DATETIME,
		@PLAN_ID VARCHAR(15),
		@TRADE_ID VARCHAR(15),
		@REF_CODE VARCHAR(20),
		@REF_NAME NVARCHAR(200),
		@IMP_DT_ID VARCHAR(20)



DECLARE Plan_Master CURSOR FOR
SELECT IM.PLAN_CODE,IM.IMP_NAME,IM.IMP_YEAR,SUM(ISNULL(IMDT.QTY,0)) AS QTY,SUM(ISNULL(IMDT.TOTAL_AMT,0)) AS AMT, IMDT.PLAN_TYPE_ID, IMDT.COST_ID,IMDT.BRANCH_ID,IMDT.DEP_ID,
IMDT.PLAN_ID,IMDT.BRANCH_CODE,IMDT.DEP_CODE
FROM dbo.PL_TRADEDETAIL PT
LEFT JOIN dbo.PL_MASTER PM ON PM.PLAN_ID=PT.PLAN_ID
LEFT JOIN dbo.CM_PLAN_TYPE CT ON CT.PLAN_TYPE_ID=PM.PLAN_TYPE_ID
LEFT JOIN dbo.CM_DVDM CDM ON CDM.DVDM_ID=PM.COST_ID
LEFT JOIN dbo.PL_TRADE_REF REF ON REF.TRADE_ID = PT.TRADE_ID
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PM.BRANCH_ID
LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_ID=PM.DEPT_ID
LEFT JOIN dbo.PL_IMPORT_DT IMDT ON IMDT.PLAN_ID=PT.PLAN_ID AND IMDT.TRADE_ID=PT.TRADE_ID
LEFT JOIN dbo.PL_IMPORT IM ON IM.IMPORT_ID=IMDT.IMPORT_ID
WHERE IM.IMP_YEAR = @p_YEAR AND PT.RECORD_STATUS='1' AND REF.REF_CODE=@P_REF_CODE
GROUP BY IM.PLAN_CODE,IM.IMP_NAME,IM.IMP_YEAR,IMDT.PLAN_TYPE_ID,IMDT.COST_ID,IMDT.BRANCH_ID,IMDT.DEP_ID,IMDT.PLAN_ID,IMDT.BRANCH_CODE,IMDT.DEP_CODE
OPEN Plan_Master;

	FETCH NEXT FROM Plan_Master INTO @PLAN_CODE,@PLAN_NAME,@YEAR,@IMP_QTY,@IMP_AMT, @PLAN_TYPE_ID,@COST_ID,@BRANCH_ID,@DEP_ID,@PLAN_ID,@BRANCH_CODE,@DEP_CODE
	
	WHILE @@FETCH_STATUS = 0	
	BEGIN	

		SET @PLAN_NAME=N'KẾ HOẠCH NGÂN SÁCH '+ CAST( @YEAR AS VARCHAR(10))
		SET @DEP_CODE=(SELECT DEP_CODE FROM dbo.CM_DEPARTMENT WHERE DEP_ID=@p_DEP_ID)
		SET @PLAN_CODE= @DEP_CODE+(SELECT RIGHT(CAST( @YEAR AS VARCHAR(10)),2))

		EXEC SYS_CodeMasters_Gen 'PL_MASTER', @PLAN_ID out
		IF @PLAN_ID='' OR @PLAN_ID IS NULL GOTO ABORT	
	
		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
		(   @PLAN_ID,@PLAN_CODE,@PLAN_NAME,@p_BRANCH_ID,@p_DEP_ID,GETDATE(),'',@IMP_AMT, @IMP_AMT,      
			1,@YEAR,'1', N'Mở mới','1', @MARKER_ID,@EDIT_DT,'A', 'admin', GETDATE(), @PLAN_TYPE_ID,@COST_ID  
		)
		
		
		


		DECLARE Plan_Detail CURSOR FOR
		SELECT IMP_DT_ID,GOOD_ID,QTY,PT.AMT_APP,PT.NOTES,PT.TRADE_ID,REF.REF_CODE,REF.REF_NAME,PT.AMT_ETM,PT.AMT_EXE,PT.AMT_TF,PT.AMT_RECEIVE_TF 
		FROM dbo.PL_TRADEDETAIL PT
		LEFT JOIN dbo.PL_MASTER PM ON PM.PLAN_ID=PT.PLAN_ID
		LEFT JOIN dbo.CM_PLAN_TYPE CT ON CT.PLAN_TYPE_ID=PM.PLAN_TYPE_ID
		LEFT JOIN dbo.CM_DVDM CDM ON CDM.DVDM_ID=PM.COST_ID
		LEFT JOIN dbo.PL_TRADE_REF REF ON REF.TRADE_ID = PT.TRADE_ID
		LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PM.BRANCH_ID
		LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_ID=PM.DEPT_ID
		LEFT JOIN dbo.PL_IMPORT_DT IMDT ON IMDT.PLAN_ID=PT.PLAN_ID AND IMDT.TRADE_ID=PT.TRADE_ID
		LEFT JOIN dbo.PL_IMPORT IM ON IM.IMPORT_ID=IMDT.IMPORT_ID
		WHERE IM.IMP_YEAR = @p_YEAR AND PT.RECORD_STATUS='1' AND REF.REF_CODE=@P_REF_CODE AND PM.PLAN_TYPE_ID=@PLAN_TYPE_ID AND ISNULL(PM.COST_ID,'')=ISNULL(@COST_ID,'') AND PM.BRANCH_ID=@BRANCH_ID AND PM.DEPT_ID=@DEP_ID
		OPEN Plan_Detail
		FETCH NEXT FROM Plan_Detail INTO @IMP_DT_ID,@GOOD_ID,@QTY,@TOTAL_AMT,@NOTES,@TRADE_ID,@REF_CODE,@REF_NAME,@AMT_ETM,@AMT_EXE,@AMT_TF,@AMT_RECIVE_TF
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			
			
			UPDATE dbo.PL_TRADEDETAIL SET PLAN_ID=@PLAN_ID WHERE TRADE_ID=@TRADE_ID
			UPDATE dbo.PL_IMPORT_DT SET PLAN_ID=@PLAN_ID,TRADE_ID=@TRADE_ID WHERE IMP_DT_ID=@IMP_DT_ID

			FETCH NEXT FROM Plan_Detail INTO @IMP_DT_ID,@GOOD_ID,@QTY,@TOTAL_AMT,@NOTES,@TRADE_ID,@REF_CODE,@REF_NAME,@AMT_ETM,@AMT_EXE,@AMT_TF,@AMT_RECIVE_TF
			END
			CLOSE Plan_Detail
			DEALLOCATE Plan_Detail
		FETCH NEXT FROM Plan_Master INTO @PLAN_CODE,@PLAN_NAME,@YEAR,@IMP_QTY,@IMP_AMT, @PLAN_TYPE_ID,@COST_ID,@BRANCH_ID,@DEP_ID,@PLAN_ID,@BRANCH_CODE,@DEP_CODE
	END
	CLOSE Plan_Master
	DEALLOCATE Plan_Master


COMMIT TRANSACTION;
SELECT '0' AS Result,
       '' ErrorDesc;
RETURN '0';
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION;
    SELECT '-1' AS Result,
			'' ErrorDesc;
    RETURN '-1';		
END CATCH
ABORT:
BEGIN
    ROLLBACK TRANSACTION;
    SELECT '-1' AS Result,
           '' ErrorDesc;
    RETURN '-1';
END;