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 DECIMAL(18,2)=NULL,
		@PRICE NUMERIC(18, 0) =NULL,
		@NOTES NVARCHAR(500) =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 DECIMAL(18,2)=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

	

	---KIEM TRA CO THUE DAU RA HAY KHONG
	IF(@IS_PROMO = 'Y') --CO THUE VAT DAU RA, HACH TOAN BUT TOAN VAT
	BEGIN
		---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_VAT', @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	

		SET @VAT=10
		--TINH SO TIEN VAT
		SET @VAT_AMT = ROUND(@TOTAL_AMT*@VAT/100,0)

		--NO TK CHI PHI - CO TK VAT
		EXEC [dbo].[MW_ENTRIES_POST_Insert] @PRICE_ID, 'MW_OUT_VAT', @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
	ELSE
	BEGIN
		---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	
	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;

--doanptt 05/05/2022 thêm lịch sử xử lý
	INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
	VALUES(@p_OUT_ID,'KTAPP',@p_KT_CHECKER_ID,GETDATE(), N'Kế toán duyệt phiếu',N'Kế toán duyệt phiếu')

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;

GO
CREATE PROCEDURE [dbo].[MW_ENTRIES_POST_CoreByRef_VAT]
	@p_REF_ID varchar(200) = NULL,
	@p_TRN_TYPE VARCHAR(50) = ''---AMORT - KHẤU HAO, ADDNEW - THEM MOI, USE - XUAT, TRANSF - DIEU CHUYEN, UPDATE - UPDATE, COLLECT - THU HOI, LIQ - THANH LY
	
AS
	DECLARE @l_MAKER_ID VARCHAR(15), @l_CHECKER_ID VARCHAR(15), @l_TRN_DESC NVARCHAR(MAX), @lTRN_DATE DATETIME, @p_DO_BRN VARCHAR(50), @p_DO_BRN_CODE VARCHAR(50)
	SELECT TOP 1 @l_MAKER_ID = MAKER_ID,@l_CHECKER_ID = CHECKER_ID, @l_TRN_DESC = TRN_DESC, @lTRN_DATE = TRN_DATE, @p_DO_BRN = DO_BRN
	FROM MW_ENTRIES_POST 
	WHERE TRN_ID = @p_REF_ID AND TRN_TYPE = @p_TRN_TYPE AND (REF_NO IS NULL OR REF_NO = '') --AND CONVERT(DATE,TRN_DATE) >= CONVERt(DATE,GETDATE()) AND AMT > 0

	---THIEUVQ THEM DO_BRANCH 121120
	SET @p_DO_BRN_CODE = (SELECT BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = @p_DO_BRN)

	SELECT ACC.ET_ID,
			ACC.CR_ACCT,CRB.BRANCH_CODE AS CR_BRN,
			ACC.DR_ACCT,DRB.BRANCH_CODE AS DR_BRN, DP.DEP_CODE AS DR_DEP,
			ACC.AMT,
			'VND' AS CURRENCY, 1 AS EXCRATE, @l_MAKER_ID AS MAKER_ID, @l_CHECKER_ID AS CHECKER_ID, @l_TRN_DESC AS TRN_DESC, @p_DO_BRN_CODE AS DO_BRN, ACC.REF_ID
	FROM
	(
		SELECT  CR_BRN, CR_ACCT, DR_BRN, DEPT_ID DR_DEP, DR_ACCT, AMT, C2.ET_ID AS ET_ID, REF_ID
		--'VND' AS CURRENCY, 1.0 AS EXCRATE, @l_MAKER_ID AS MAKER_ID, @l_CHECKER_ID AS CHECKER_ID, @l_TRN_DESC AS TRN_DESC
		FROM MW_ENTRIES_POST  C2	
		WHERE C2.AMT > 0 AND TRN_ID = @p_REF_ID AND TRN_TYPE = @p_TRN_TYPE AND (REF_NO IS NULL OR REF_NO = '')-- AND CONVERT(DATE,TRN_DATE) >= CONVERt(DATE,GETDATE())
	) AS ACC
	LEFT JOIN CM_BRANCH CRB ON CRB.BRANCH_ID = ACC.CR_BRN
	LEFT JOIN CM_BRANCH DRB ON DRB.BRANCH_ID = ACC.DR_BRN
	LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = ACC.DR_DEP