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

ALTER PROCEDURE [dbo].[MW_OUT_KT_App]

    @p_OUT_ID VARCHAR(15) = NULL,

    @p_KT_CHECKER_ID VARCHAR(15) = NULL,

    @p_KT_APPROVE_DT VARCHAR(20) = NULL,

    @p_XmlData XML = NULL

AS

DECLARE 

        @OUT_DT_ID VARCHAR(15) = NULL,@ERROR NVARCHAR(500),

        @TOTAL_AMT NUMERIC(18, 0) = NULL,

        @BRANCH_CREATE VARCHAR(15) = NULL,

        @CORE_NOTE NVARCHAR(1000) = NULL,

        @KT_MAKER_ID VARCHAR(20) = NULL,

        @BRANCH_ID VARCHAR(15) = NULL,

        @DEPT_ID VARCHAR(15) = NULL,

        @PRICE_ID VARCHAR(15) = NULL,

        @GROUP_ID VARCHAR(15) = NULL,

		@MATERIAL_ID VARCHAR(15) = NULL,@TO_BRN_ID VARCHAR(15) = NULL,@TO_DEPT_ID VARCHAR(15) = NULL,

		@IS_PROMO VARCHAR(1) = NULL, @VAT NUMERIC(18, 0), @VAT_AMT NUMERIC(18, 0),@PRICE_VAT NUMERIC(18, 0),

		--01-03-2019 : LUCTV BO SUNG NHUNG BIEN LIEN QUAN TOI PHAN UPDATE-INSERT KHI DUYET

		@BRN_ID VARCHAR(15) =NULL,

		@MAST_BAL_ID VARCHAR(15) =NULL,

		@CUST_NAME NVARCHAR(200) =NULL,

		@QTY INT=NULL,

		@PRICE NUMERIC(18, 0) =NULL,

		@NOTES NVARCHAR(1000) =NULL

DECLARE XmlData CURSOR FOR

SELECT A.OUT_DT_ID, A.TOTAL_AMT, A.TO_BRN_ID, A.TO_DEPT_ID, B.BRANCH_CREATE, B.CORE_NOTE,

B.KT_MAKER_ID, C.BRANCH_ID, C.DEPT_ID, D.PRICE_ID, E.GROUP_ID, E.MATERIAL_ID, A.IS_BCT /*E.IS_PROMO*/, E.VAT, E.PRICE_VAT,A.QTY,

A.TO_BRN_ID, A.MAST_BAL_ID,A.CUST_NAME,A.QTY,A.PRICE,A.NOTES, A.COST_ACC

FROM MW_OUT_DT A

INNER JOIN MW_OUT B ON A.OUT_ID = B.OUT_ID

INNER JOIN MW_MAST_BAL C ON A.MAST_BAL_ID = C.MAST_BAL_ID

INNER JOIN MW_MAST_PRICE D ON D.PRICE_ID = C.PRICE_ID

INNER JOIN MW_IN E ON D.MATERIAL_ID = E.IN_ID

WHERE A.OUT_ID = @p_OUT_ID

OPEN XmlData;

BEGIN TRANSACTION;

-- 02-04-19: KIEM TRA NEU DANG TRA VE THI KHONG DUOC PHEP DUYET

IF((SELECT KT_AUTH_STATUS FROM MW_OUT WHERE OUT_ID = @p_OUT_ID) ='R')

	BEGIN

		CLOSE XmlData

		DEALLOCATE XmlData

		ROLLBACK TRANSACTION

		SELECT '-1' as Result,@p_OUT_ID OUT_ID, N'Giao dịch đang bị trả về. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc

		RETURN '-1'

	END

UPDATE [dbo].[MW_OUT]

SET KT_AUTH_STATUS = 'A',

    KT_APPROVE_DT = CONVERT(DATETIME, @p_KT_APPROVE_DT, 103),

    KT_CHECKER_ID = @p_KT_CHECKER_ID

WHERE [OUT_ID] = @p_OUT_ID;

IF @@Error <> 0

    GOTO ABORT;

--Insert XmlData

--DELETE FROM MW_OUT_DT WHERE OUT_ID =@p_OUT_ID

DECLARE @EXP_ACCTNO VARCHAR(50), @MATERIAL_ACCTNO VARCHAR(50), @VAT_ACCTNO VARCHAR(50)

FETCH NEXT FROM XmlData INTO @OUT_DT_ID, @TOTAL_AMT,@TO_BRN_ID,@TO_DEPT_ID, @BRANCH_CREATE , @CORE_NOTE ,@KT_MAKER_ID ,@BRANCH_ID ,@DEPT_ID ,@PRICE_ID ,@GROUP_ID ,@MATERIAL_ID ,@IS_PROMO, @VAT, @PRICE_VAT,@QTY,

						     @TO_BRN_ID, @MAST_BAL_ID,@CUST_NAME,@QTY,@PRICE,@NOTES,@EXP_ACCTNO

DECLARE @l_SUM_QTY_BALANCE INT=0,@BR_CODE VARCHAR(15),@DP_CODE VARCHAR(25)

DECLARE @l_REMAIN_AMT NUMERIC(18, 2)=0;

DECLARE @l_TOTAL_AMT NUMERIC(18, 2)=0;

DECLARE @INDEX INT =0

WHILE @@FETCH_STATUS = 0

BEGIN

	-------- 01/03/2018 LUCTV: EDIT CODE KE TOAN DUYET THI MOI PHAT SINH UPDATE DU LIEU

	SELECT @l_SUM_QTY_BALANCE=SUM(QTY_BALANCE),

        @l_REMAIN_AMT=SUM(TOTAL_AMT)

     FROM MW_MAST_BAL

     WHERE MAST_BAL_ID=@MAST_BAL_ID;/*PRICE_ID=(SELECT PRICE_ID FROM MW_MAST_BAL WHERE MAST_BAL_ID=@MAST_BAL_ID)AND */ 

    --IF(@QTY>@l_SUM_QTY_BALANCE)BEGIN

    --    ROLLBACK TRANSACTION;

    --    SELECT '-1' AS Result,

    --        @p_OUT_ID OUT_ID,

    --        N'Số lượng xuất vượt quá số lượng tồn' ErrorDesc;

    --    RETURN '-1';

    --END;

 	SET @INDEX = @INDEX+1

	-- 07 -03 -2019 KIEM TRA LAI SO LUONG (VI SE CO TRUONG HOP VUA DIEU CHUYEN, VUA XUAT SD, VƯA THU HOI, VUA THANH LY TREN 1 MAST_BAL_ID)

	IF(@QTY > @l_SUM_QTY_BALANCE) /*(SELECT QTY_BALANCE FROM MW_MAST_BAL WHERE MAST_BAL_ID=@MAST_BAL_ID))*/

	BEGIN

		CLOSE XmlData

		DEALLOCATE XmlData

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, @p_OUT_ID OUT_ID, N'Dòng '+ CONVERT(VARCHAR(15),@INDEX) +N': Số lượng xuất sử dụng vượt quá số lượng tồn' ErrorDesc

		RETURN '-1'

	END

    DECLARE @p_OUT_DT_ID VARCHAR(15);

    EXEC SYS_CodeMasters_Gen 'MW_OUT_DT', @p_OUT_DT_ID OUT;

    IF @p_OUT_DT_ID='' OR @p_OUT_DT_ID IS NULL GOTO ABORT;

    SELECT @BRN_ID=O.BRN_ID FROM dbo.MW_OUT O WHERE O.OUT_ID=@p_OUT_ID;

	IF((@QTY-@l_SUM_QTY_BALANCE)=0)---LA LO CUOI CUNG

	BEGIN

        --SET @l_TOTAL_AMT=@l_TOTAL_AMT-@l_SUM_TOTAL_AMT; --THIEUVQ 070120

		SET @TOTAL_AMT = @l_REMAIN_AMT; --THIEUVQ 070120

        UPDATE MW_MAST_BAL

        SET TOTAL_AMT= 0, --TOTAL_AMT-@TOTAL_AMT,--THIEUVQ 070120

           QTY_BALANCE= 0 --QTY_BALANCE-@QTY--THIEUVQ 070120

        WHERE MAST_BAL_ID=@MAST_BAL_ID;

        --LUCTV:30-11-2018 NEU DOT XUAT SU DUNG DO LA CUOI CUNG. THI CAP NHAT NGUOC SO TIEN VE CHO BANG MW_OUT_DT

        --UPDATE MW_OUT_DT SET TOTAL_AMT=@TOTAL_AMT WHERE OUT_DT_ID=@OUT_ID;

    END;

    ELSE 

	BEGIN

		IF @TOTAL_AMT > @l_REMAIN_AMT SET @TOTAL_AMT = @l_REMAIN_AMT --THIEUVQ 070120

        UPDATE MW_MAST_BAL

        SET TOTAL_AMT=TOTAL_AMT-@TOTAL_AMT,

            QTY_BALANCE=QTY_BALANCE-@QTY

        WHERE MAST_BAL_ID=@MAST_BAL_ID;

    END;

    DECLARE @p_ID_MAS_BAL VARCHAR(15);

    EXEC SYS_CodeMasters_Gen 'MW_MAST_BAL_STMT', @p_ID_MAS_BAL OUT;

    INSERT INTO MW_MAST_BAL_STMT(STMT_ID, REF_ID, MAST_BAL_ID, TRN_DATE, TRN_TIME, TRN_TYPE, CRDR, QTY, TRN_DESC, PRICE, TOTAL_AMT)

    VALUES(@p_ID_MAS_BAL, @p_OUT_ID, @MAST_BAL_ID, (SELECT KT_APPROVE_DT FROM MW_OUT WHERE OUT_ID=@p_OUT_ID), (SELECT TRN_TIME FROM MW_OUT WHERE OUT_ID=@p_OUT_ID),

    'O' , 'D', @QTY, @NOTES, @PRICE, @TOTAL_AMT);

	----- END LUCTV 01-03-2019

	---LAY TAI KHOAN HACH TOAN

	SELECT /*@EXP_ACCTNO = EXP_ACCTNO,*/ @MATERIAL_ACCTNO = MATERIAL_ACCTNO, @VAT_ACCTNO = VAT_ACCTNO 

	FROM MW_MATERIAL WHERE MATERIAL_ID = @MATERIAL_ID

	---NEU XUAT CHO KHACH HANG THI DON VI NO LA DV CHU QUAN

	IF @TO_BRN_ID IS NULL OR @TO_BRN_ID = ''

	BEGIN

		SET @TO_BRN_ID = @BRANCH_ID

		SET @TO_DEPT_ID = @DEPT_ID

	END

	---TRUONG HOP CN XUAT PHONG BAN TRUC THUOC HOI SO

	IF @TO_DEPT_ID IS NOT NULL AND @TO_DEPT_ID <> ''

	BEGIN

		--LAY MA BRANCH CODE

		SET @BR_CODE = (SELECT BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = @TO_BRN_ID)

		SET @DP_CODE = (SELECT DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID = @TO_DEPT_ID)

		--KIEM TRA MA PHONG BAN KHAC MA DON VI QUAN LY HIEN TAI THI GAN LAI BRANCH_ID

		IF @BR_CODE <> LEFT(@DP_CODE,3)

		BEGIN

			SET @TO_BRN_ID = (SELECT BRANCH_ID FROM CM_BRANCH WHERE BRANCH_CODE = LEFT(@DP_CODE,3) )

		END

	END

	---NO TK CHI PHI - NEU NGUOI NHAN LA KHACH HANG THI HACH TOAN CHO DV CHU QUAN, NEU LA PHONG BAN NHAN THI HACH TOAN CHO PHONG BAN

	---CO TK VAT LIEU - DON VI CHU QUAN

	EXEC [dbo].[MW_ENTRIES_POST_Insert] @PRICE_ID, 'MW_OUT', @OUT_DT_ID, @BRANCH_CREATE, @MATERIAL_ACCTNO, @BRANCH_ID, @EXP_ACCTNO, @TO_BRN_ID, @TOTAL_AMT, 'Y', 

		@p_KT_APPROVE_DT, @CORE_NOTE, @KT_MAKER_ID, @p_KT_CHECKER_ID, @p_OUT_ID,@TO_DEPT_ID, @ERROR OUT

	IF @ERROR <> '0' GOTO ABORT	

	---KIEM TRA CO THUE DAU RA HAY KHONG

	IF @IS_PROMO = 'Y' --CO THUE VAT DAU RA, HACH TOAN BUT TOAN VAT

	BEGIN

		--TINH SO TIEN VAT

		--SET @VAT_AMT = ROUND(@TOTAL_AMT*@VAT/100,0)

		SET @VAT_AMT = ROUND(@TOTAL_AMT*10/100,0)

		--NO TK CHI PHI - CO TK VAT

		EXEC [dbo].[MW_ENTRIES_POST_Insert] @PRICE_ID, 'MW_OUT', @OUT_DT_ID, @BRANCH_CREATE, @VAT_ACCTNO, @BRANCH_ID, @EXP_ACCTNO, @TO_BRN_ID, @VAT_AMT, 'Y', 

			@p_KT_APPROVE_DT, @CORE_NOTE, @KT_MAKER_ID, @p_KT_CHECKER_ID, @p_OUT_ID,@TO_DEPT_ID, @ERROR OUT

		IF @ERROR <> '0' GOTO ABORT	

	END

    FETCH NEXT FROM XmlData INTO @OUT_DT_ID, @TOTAL_AMT,@TO_BRN_ID,@TO_DEPT_ID, @BRANCH_CREATE , @CORE_NOTE ,@KT_MAKER_ID ,@BRANCH_ID ,@DEPT_ID ,@PRICE_ID ,@GROUP_ID ,@MATERIAL_ID ,@IS_PROMO , @VAT, @PRICE_VAT,@QTY,

								 @TO_BRN_ID, @MAST_BAL_ID,@CUST_NAME,@QTY,@PRICE,@NOTES,@EXP_ACCTNO

END;

CLOSE XmlData;

DEALLOCATE XmlData;

COMMIT TRANSACTION;

SELECT '0' AS Result, @p_OUT_ID OUT_ID,'' ErrorDesc;

RETURN '0';

ABORT:

BEGIN

    CLOSE XmlData;

    DEALLOCATE XmlData;

    ROLLBACK TRANSACTION;

    SELECT '-1' AS Result,'' OUT_ID, '' ErrorDesc;

    RETURN '-1';

END;