DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

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;