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

ALTER PROCEDURE [dbo].[PL_IMPORT_App]

    @P_IMPORT_ID VARCHAR(15),

    @P_AUTH_STATUS VARCHAR(1),

    @P_CHECKER_ID VARCHAR(12),

    @P_APPROVE_DT DATETIME = NULL

AS

BEGIN TRY

BEGIN TRANSACTION;

DECLARE @IS_UPDATE BIT 

SET @IS_UPDATE = (SELECT IS_UPDATE FROM dbo.PL_IMPORT WHERE IMPORT_ID=@P_IMPORT_ID )

--VALIDATE dữ liệu

DECLARE @IMPORT_YEAR VARCHAR(4)

SET @IMPORT_YEAR=(SELECT CAST(IMP_YEAR AS VARCHAR(4)) FROM dbo.PL_IMPORT WHERE IMPORT_ID=@P_IMPORT_ID)

IF(@IS_UPDATE=0)

BEGIN

IF(EXISTS(

SELECT DT.TRADE_ID FROM dbo.PL_MASTER PM 

LEFT JOIN dbo.PL_TRADEDETAIL DT ON PM.PLAN_ID=DT.PLAN_ID

WHERE PM.YEAR=@IMPORT_YEAR AND (EXISTS(

	SELECT Tmp.GOOD_ID FROM PL_IMPORT_DT Tmp WHERE (Tmp.IMP_YEAR=PM.YEAR AND Tmp.PLAN_TYPE_ID=PM.PLAN_TYPE_ID 

									AND Tmp.COST_ID=PM.COST_ID AND Tmp.BRANCH_ID=PM.BRANCH_ID 

									AND Tmp.DEP_ID=PM.DEPT_ID AND Tmp.GOOD_ID=DT.GOODS_ID) AND( (Tmp.QTY < ISNULL(DT.QUANTITY_EXE,0) AND  ISNULL(DT.QUANTITY_EXE,0) >0) OR (Tmp.TOTAL_AMT< ISNULL(DT.AMT_EXE,0) AND ISNULL(DT.AMT_EXE,0) >0) )

 ) OR (NOT EXISTS(SELECT Tmp.GOOD_ID FROM PL_IMPORT_DT Tmp WHERE (Tmp.IMP_YEAR=PM.YEAR AND Tmp.PLAN_TYPE_ID=PM.PLAN_TYPE_ID 

									AND Tmp.COST_ID=PM.COST_ID AND Tmp.BRANCH_ID=PM.BRANCH_ID 

									AND Tmp.DEP_ID=PM.DEPT_ID AND Tmp.GOOD_ID=DT.GOODS_ID) ) AND( ISNULL(DT.QUANTITY_EXE,0) > 0 OR ISNULL(DT.AMT_EXE,0) >0)

))))

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' IMPORT_ID, N'Hạn mục chỉnh sửa đã sử dụng vượt hạn mức chỉnh sửa' ErrorDesc

		RETURN '-1'

END

END

-- Delete dữ liệu ko có trong import hiện tại.

IF(@IS_UPDATE=0)

BEGIN

UPDATE dbo.PL_TRADEDETAIL SET RECORD_STATUS=0 WHERE  NOT EXISTS(SELECT DT.IMP_DT_ID FROM dbo.PL_IMPORT_DT DT WHERE DT.TRADE_ID=PL_TRADEDETAIL.TRADE_ID) AND PL_TRADEDETAIL.PLAN_ID IN (SELECT PLAN_ID FROM dbo.PL_MASTER WHERE YEAR=@IMPORT_YEAR)

UPDATE dbo.PL_MASTER SET RECORD_STATUS=0 WHERE YEAR=@IMPORT_YEAR AND  NOT EXISTS(SELECT DT.IMP_DT_ID FROM dbo.PL_IMPORT_DT DT WHERE DT.PLAN_ID=PL_MASTER.PLAN_ID)

END

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)

SELECT @MARKER_ID=MAKER_ID,@EDIT_DT=EDIT_DT FROM dbo.PL_IMPORT WHERE IMPORT_ID=@P_IMPORT_ID

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_IMPORT IM

LEFT JOIN dbo.PL_IMPORT_DT IMDT ON IM.IMPORT_ID=IMDT.IMPORT_ID

WHERE IM.IMPORT_ID = @P_IMPORT_ID AND IMDT.IS_UPDATE=1

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 @PLAN_CODE= @DEP_CODE+(SELECT RIGHT(CAST( @YEAR AS VARCHAR(10)),2))

		IF(@PLAN_ID IS NULL OR @PLAN_ID='' )

		BEGIN

			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,@BRANCH_ID,@DEP_ID,GETDATE(),'',@IMP_AMT, @IMP_AMT,      

				1,@YEAR,'1', N'','1', @MARKER_ID,@EDIT_DT,@P_AUTH_STATUS, @P_CHECKER_ID, CAST(@P_APPROVE_DT AS DATE), @PLAN_TYPE_ID,@COST_ID  

			 )

		END

		ELSE

		BEGIN

		 UPDATE dbo.PL_MASTER SET MAKER_ID=@MARKER_ID,CREATE_DT=@EDIT_DT,AUTH_STATUS=@P_AUTH_STATUS,CHECKER_ID=@P_CHECKER_ID,APPROVE_DT=CAST(@P_APPROVE_DT AS DATE)

		 ,TOTAL_AMT=@IMP_AMT,APPROVE_VALUE=@IMP_AMT,VERSON=VERSON+1,PLAN_CODE=@PLAN_CODE,PLAN_NAME=@PLAN_NAME WHERE PLAN_ID=@PLAN_ID

		END

		 DECLARE Plan_Detail CURSOR FOR

		 SELECT IMP_DT_ID,GOOD_ID,QTY,TOTAL_AMT,NOTES,TRADE_ID,REF_CODE,REF_NAME,AMT_ETM,AMT_EXE,AMT_TF,AMT_RECEIVE_TF FROM dbo.PL_IMPORT_DT 

		 WHERE IMPORT_ID = @P_IMPORT_ID AND IS_UPDATE=1 AND PLAN_TYPE_ID=@PLAN_TYPE_ID AND ISNULL(COST_ID,'')=ISNULL(@COST_ID,'') AND BRANCH_ID=@BRANCH_ID AND DEP_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

			IF(@TRADE_ID IS NULL OR @TRADE_ID='')

			BEGIN

			EXEC SYS_CodeMasters_Gen 'PL_TRADEDETAIL', @TRADE_ID out

			IF @TRADE_ID='' OR @TRADE_ID IS NULL GOTO ABORT	

			DELETE FROM dbo.PL_TRADE_REF WHERE TRADE_ID=@TRADE_ID

			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,

				AMT_ETM,

				AMT_EXE,

				AMT_TF,

				AMT_RECEIVE_TF

			)

			VALUES

			(   @TRADE_ID,        -- TRADE_ID - varchar(15)

			    @PLAN_ID,        -- PLAN_ID - varchar(15)

			    @GOOD_ID,        -- GOODS_ID - varchar(15)

			    @QTY,      -- QUANTITY - decimal(18, 0)

			    @TOTAL_AMT,      -- PRICE - decimal(18, 0)			  

			   @NOTES,       -- NOTES - nvarchar(1000)

			    '1',        -- RECORD_STATUS - varchar(1)

			    @MARKER_ID,        -- MAKER_ID - varchar(12)

			   CAST( @EDIT_DT AS DATE), -- CREATE_DT - datetime

			    @P_AUTH_STATUS,        -- AUTH_STATUS - varchar(50)

			   @P_CHECKER_ID,        -- CHECKER_ID - varchar(12)

			  CAST( @P_APPROVE_DT AS DATE),  -- APPROVE_DT - datetime

			  @AMT_ETM,

			  @AMT_EXE,

			  @AMT_TF,

			  @AMT_RECIVE_TF

			    )	

				INSERT INTO dbo.PL_TRADE_REF

			(

			    TRADE_ID,

			    REF_CODE,

			    REF_NAME

			)

			VALUES

			(   @TRADE_ID, -- TRADE_ID - varchar(15)

			    @REF_CODE, -- REF_CODE - varchar(50)

			    @REF_NAME -- REF_NAME - nvarchar(500)

			    )

			END

			ELSE

			BEGIN

				UPDATE dbo.PL_TRADEDETAIL SET

				 QUANTITY=@QTY,

				 AMT_APP=@TOTAL_AMT,

				 NOTES=@NOTES,

				 MAKER_ID=@MARKER_ID,

				 CREATE_DT=CAST(@EDIT_DT AS DATE),

				 AUTH_STATUS=@P_AUTH_STATUS,

				 CHECKER_ID= @P_CHECKER_ID,

				 APPROVE_DT=CAST(@P_APPROVE_DT AS DATE)

				-- AMT_ETM=@AMT_ETM,

				-- AMT_EXE=@AMT_EXE,

				-- AMT_TF=@AMT_TF,

				-- AMT_RECEIVE_TF=@AMT_RECIVE_TF

				WHERE TRADE_ID=@TRADE_ID

			END

			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

UPDATE dbo.PL_IMPORT

SET AUTH_STATUS = @P_AUTH_STATUS,

    CHECKER_ID = @P_CHECKER_ID,

    APPROVE_DT = CAST( @P_APPROVE_DT AS DATE)

WHERE IMPORT_ID = @P_IMPORT_ID;

UPDATE dbo.PL_IMPORT_LOG SET AUTH_STATUS = @P_AUTH_STATUS,

    CHECKER_ID = @P_CHECKER_ID,

    APPROVE_DT = CAST( @P_APPROVE_DT AS DATE)

WHERE IMP_ID = @P_IMPORT_ID AND IMP_LOG_CODE= (SELECT TOP (1) IMP_LOG_CODE FROM dbo.PL_IMPORT_LOG WHERE IMP_ID=@P_IMPORT_ID ORDER BY IMP_LOG_ID DESC)

UPDATE dbo.PL_TRADEDETAIL SET PRICE = AMT_APP/ QUANTITY WHERE QUANTITY <> 0 OR QUANTITY IS NULL

UPDATE dbo.PL_TRADEDETAIL SET PRICE = AMT_APP WHERE QUANTITY = 0

UPDATE dbo.LIMIT_ACCUMULATE SET MAX_AMT=ISNULL(( 

SELECT TEMP.AMT_APP FROM (SELECT CD.DVDM_ID,SUM(ISNULL(PT.AMT_APP,0)) AS AMT_APP FROM dbo.CM_DVDM CD 

LEFT JOIN dbo.CM_GOOD_DVDM CGD ON CGD.KHOI_ID=CD.DVDM_ID

LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.GOODS_ID=CGD.GD_ID

WHERE CD.IS_KHOI=1 AND PT.PLAN_ID IN  (SELECT PLAN_ID FROM dbo.PL_MASTER WHERE YEAR=@IMPORT_YEAR)

GROUP BY CD.DVDM_ID

)TEMP WHERE TEMP.DVDM_ID=LIMIT_ACCUMULATE.DVDM_ID

),0) * (SELECT LIMIT_PERCENT FROM dbo.TL_SYSROLE_LIMIT WHERE TL_SYSROLE_LIMIT.ROLE_ID=LIMIT_ACCUMULATE.ROLE_ID AND LIMIT_TYPE='DCNS')/100

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;