AMS - VB

ALTER PROCEDURE dbo.MW_OUT_App 

    @p_OUT_ID VARCHAR(15) =NULL,

    @p_CHECKER_ID VARCHAR(100) =NULL,

    @p_APPROVE_DT VARCHAR(20) =NULL,

    @p_XmlData XML=NULL

AS

DECLARE @OUT_ID VARCHAR(15) =NULL,

        @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,

        @TOTAL_AMT NUMERIC(18, 2) =NULL,

        @NOTES NVARCHAR(1000) =NULL,

        @TO_BRN_ID VARCHAR(15) =NULL,

        @TO_DEPT_ID VARCHAR(15) =NULL;

DECLARE @hdoc INT;

DECLARE @INDEX INT =0

DECLARE @p_ID_MAS_BAL VARCHAR(15);

EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XmlData;

DECLARE @C_TO_BRN_ID VARCHAR(15), @C_TO_DEP_ID VARCHAR(15)

DECLARE @STATUS VARCHAR(50), @AUTH_STATUS VARCHAR(10)

DECLARE XmlData CURSOR FOR

SELECT *

  FROM

    OPENXML(@hdoc, '/Root/XmlData', 2)

    WITH(

        OUT_ID VARCHAR(15),

        MAST_BAL_ID VARCHAR(15),

        CUST_NAME NVARCHAR(200),

        QTY DECIMAL(18,2),

        PRICE NUMERIC(18, 0),

        TOTAL_AMT NUMERIC(18, 2),

        NOTES NVARCHAR(1000),

        TO_BRN_ID VARCHAR(15),

        TO_DEPT_ID VARCHAR(15)

        );

OPEN XmlData;

BEGIN TRANSACTION;

DECLARE @p_ID VARCHAR(15);

FETCH NEXT FROM XmlData

 INTO @OUT_ID,

      @MAST_BAL_ID,

      @CUST_NAME,

      @QTY,

      @PRICE,

      @TOTAL_AMT,

      @NOTES,

      @TO_BRN_ID,

      @TO_DEPT_ID;

DECLARE @l_SUM_QTY_BALANCE DECIMAL(18,2)=0;

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

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

IF(@@FETCH_STATUS=-1)BEGIN

    ROLLBACK TRANSACTION;

    SELECT '-1' AS Result,

        @p_OUT_ID OUT_ID,

        N'Vui lòng duyệt lại sau khi dữ liệu được load hoàn tất' ErrorDesc;

    RETURN '-1';

END;

IF((SELECT STATUS FROM MW_OUT mo WHERE mo.OUT_ID = @p_OUT_ID) <> 'SendApp')

BEGIN

	ROLLBACK TRANSACTION

  SELECT '-1' as Result, '' OUT_ID, N'Phiếu xuất chưa đến bước duyệt bạn không được phép cập nhật thông tin.' ErrorDesc

	RETURN '-1'

END

-- 24-03-19: KIEM TRA NEU DANG TRA VE THI KHONG DUOC PHEP DUYET

IF((SELECT 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

 DECLARE @CUR_PROCESS VARCHAR(20) DECLARE @p_BRANCH_LOGIN VARCHAR(15), @p_DEP_LOGIN VARCHAR(15)

  --Kiểm tra Kho nếu kho đặt biệt check đã yêu cầu hạch toán chưa nếu chưa yêu cầu chọn hạch toán mới được qua kế toán

  DECLARE @WARE_CODE VARCHAR(25),@ListWare_CODE VARCHAR(500);

  SELECT @WARE_CODE=cw.WARE_CODE FROM MW_OUT MO LEFT JOIN CM_WARE cw ON MO.WARE_ID = cw.WARE_ID WHERE MO.OUT_ID = @p_OUT_ID

  SELECT @ListWare_CODE = sp.ParaValue FROM SYS_PARAMETERS sp WHERE sp.ParaKey = 'MW_OUT_WARE_CUSTOMER'

  IF(EXISTS(SELECT 1 FROM STRING_SPLIT(@ListWare_CODE, ',') WHERE VALUE = @WARE_CODE))

  BEGIN

    --Nếu tồn tại 3 kho quà tặng Thì TDV duyệt phiếu chờ KSV phê duyệt(MW_OUT_KSV_KT_APP) thì mới trừ số liệu

    --- KIỂM TRA TỰ XUẤT HAY XUẤT KHÁC ĐƠN VỊ

    IF EXISTS(SELECT 1 

    FROM MW_OUT_DT A

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

    WHERE A.OUT_ID = @p_OUT_ID

        AND ((A.UNIT_RECEIVE = B.BRN_ID AND B.BRN_ID <> 'DV0001')

             OR (A.UNIT_RECEIVE = B.BRN_ID AND B.BRN_ID = 'DV0001' AND ISNULL(B.DEPT_ID,'') = ISNULL(A.DEP_RECEIVE,''))

            )

    )

    BEGIN

        PRINT N'TỰ XUẤT'

        IF((SELECT REQ_ACOUNT FROM MW_OUT WHERE OUT_ID = @p_OUT_ID) <> '1')

    	BEGIN

           BEGIN

--        		CLOSE XmlData

--        		DEALLOCATE XmlData

--        		ROLLBACK TRANSACTION

--        		SELECT '-1' as Result,@p_OUT_ID OUT_ID, N'Xuất kho đối với các kho quà tặng chưa cập nhật yêu cầu hạch toán. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc

--        		RETURN '-1'

            ------- TRẢ VỀ BƯỚC NHÂN VIÊN TẠO CẬP NHẬN YÊU CẦU HẠCH TOÁN ĐỂ TIẾP TỤC LÊN PHÒNG KẾ TOÁN XỬ LÝ

                --UPDATE MW_OUT SET STATUS = 'REQ_ACOUNT', AUTH_STATUS = 'A', KT_AUTH_STATUS = NULL WHERE OUT_ID =@p_OUT_ID

                UPDATE MW_OUT SET STATUS = 'KSV_KT_APP', AUTH_STATUS = 'A', KT_AUTH_STATUS = NULL WHERE OUT_ID =@p_OUT_ID

                UPDATE A SET A.QTY_RECEIVE = A.QTY, QTY_DAMAGED = 0, QTY_LOSS = 0

                FROM MW_OUT_DT A

                WHERE A.OUT_ID = @p_OUT_ID

        	END

    	END

        ELSE 

            BEGIN

                UPDATE MW_OUT SET STATUS = 'KSV_KT_APP', AUTH_STATUS = 'A', KT_AUTH_STATUS = NULL WHERE OUT_ID =@p_OUT_ID

                UPDATE A SET A.QTY_RECEIVE = A.QTY, QTY_DAMAGED = 0, QTY_LOSS = 0

                FROM MW_OUT_DT A

                WHERE A.OUT_ID = @p_OUT_ID

--                UPDATE MW_OUT SET STATUS = 'OUT_KT', AUTH_STATUS = 'A', KT_AUTH_STATUS = 'E' WHERE OUT_ID =@p_OUT_ID

--

--                UPDATE A SET A.QTY_RECEIVE = A.QTY, QTY_DAMAGED = 0, QTY_LOSS = 0

--                FROM MW_OUT_DT A

--                WHERE A.OUT_ID = @p_OUT_ID

             END

    END

    ELSE

    BEGIN

--    	IF((SELECT REQ_ACOUNT FROM MW_OUT WHERE OUT_ID = @p_OUT_ID) <> '1')

--        BEGIN

--        		CLOSE XmlData

--        		DEALLOCATE XmlData

--        		ROLLBACK TRANSACTION

--        		SELECT '-1' as Result,@p_OUT_ID OUT_ID, N'Xuất kho đối với các kho quà tặng chưa cập nhật yêu cầu hạch toán. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc

--        		RETURN '-1'

--        END

--    	ELSE 

        BEGIN

            BEGIN

            PRINT N'XUẤT KHÁC ĐƠN VỊ HOẶC PHÒNG BAN'

          	UPDATE [dbo].[MW_OUT]

            SET AUTH_STATUS='A',

            CHECKER_ID=@p_CHECKER_ID,

            STATUS ='KSV_KT_APP',

      	    APPROVE_DT =  CONVERT(DATETIME, @p_APPROVE_DT, 103)

            WHERE [OUT_ID]=@p_OUT_ID;

            DECLARE CUR_CONFIRM CURSOR FOR

            SELECT ISNULL(UNIT_RECEIVE,''), ISNULL(DEP_RECEIVE,'')

            FROM MW_OUT_DT 

            WHERE OUT_ID = @p_OUT_ID

            GROUP BY ISNULL(UNIT_RECEIVE,''), ISNULL(DEP_RECEIVE,'')

            OPEN CUR_CONFIRM

            FETCH NEXT FROM CUR_CONFIRM INTO @C_TO_BRN_ID,@C_TO_DEP_ID

            WHILE @@FETCH_STATUS = 0

            BEGIN

                DECLARE @p_OUT_CONF_ID_0 VARCHAR(15);

              	EXEC SYS_CodeMasters_Gen 'MW_OUT_CONF', @p_OUT_CONF_ID_0 OUT;

              	IF(NOT EXISTS(SELECT 1 FROM MW_OUT_CONF WHERE OUT_ID = @p_OUT_ID AND ISNULL(BRANCH_RECIVE,'') = ISNULL(@C_TO_BRN_ID,'') AND ISNULL(DEP_RECIVE,'') = ISNULL(@C_TO_DEP_ID,'')))

              	BEGIN

              		INSERT INTO MW_OUT_CONF (ID, OUT_ID, BRANCH_RECIVE, DEP_RECIVE, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, BRANCH_CREATE, RECORD_STATUS,STATUS)   

                  VALUES (@p_OUT_CONF_ID_0,@p_OUT_ID,@C_TO_BRN_ID,@C_TO_DEP_ID,NULL,NULL,NULL, NULL,NULL,'1','CONFIRM')

              	END

                FETCH NEXT FROM CUR_CONFIRM INTO @C_TO_BRN_ID,@C_TO_DEP_ID

            END

            CLOSE CUR_CONFIRM

            DEALLOCATE CUR_CONFIRM

                    SELECT @p_BRANCH_LOGIN = TU.TLSUBBRID, @p_DEP_LOGIN = TU.SECUR_CODE FROM TL_USER TU WHERE TU.TLNANME = (SELECT MO.MAKER_ID FROM MW_OUT MO WHERE MO.OUT_ID = @p_OUT_ID)

                  SET @CUR_PROCESS = (SELECT TOP 1 MRP.PROCESS_ID FROM MW_REQUEST_PROCESS MRP WHERE MRP.REQ_ID = @p_OUT_ID AND MRP.STATUS = 'C')

                  UPDATE MW_REQUEST_PROCESS SET STATUS = 'P' WHERE REQ_ID = @p_OUT_ID AND STATUS = 'C'

                  INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT,NOTES)

                  VALUES(@p_OUT_ID,'CONF',N'Nhân viên tạo phiếu xác nhận','C','NVTT',@p_BRANCH_LOGIN,@CUR_PROCESS,@p_DEP_LOGIN, 'Update',GETDATE(), N'Xác nhận giao')

          END

    	END

      IF @@Error<>0 GOTO ABORT;

    END

  END

  ELSE

  BEGIN

--- KIỂM TRA TỰ XUẤT HAY XUẤT KHÁC ĐƠN VỊ

    IF EXISTS(SELECT 1 

    FROM MW_OUT_DT A

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

    WHERE A.OUT_ID = @p_OUT_ID 

        AND ((A.UNIT_RECEIVE = B.BRN_ID AND B.BRN_ID <> 'DV0001')

             OR (A.UNIT_RECEIVE = B.BRN_ID AND B.BRN_ID = 'DV0001' AND ISNULL(B.DEPT_ID,'') = ISNULL(A.DEP_RECEIVE,''))

            )

    )

      BEGIN

        PRINT N'TỰ XUẤT'

      	UPDATE [dbo].[MW_OUT]

        SET AUTH_STATUS='A',

        CHECKER_ID=@p_CHECKER_ID,

        STATUS ='OUT_KT',

  	    APPROVE_DT =  CONVERT(DATETIME, @p_APPROVE_DT, 103)

        WHERE [OUT_ID]=@p_OUT_ID;

        UPDATE A SET A.QTY_RECEIVE = A.QTY, QTY_DAMAGED = 0, QTY_LOSS = 0

        FROM MW_OUT_DT A

        WHERE A.OUT_ID = @p_OUT_ID

      END

      ELSE -------------------

      BEGIN

        PRINT N'XUẤT KHÁC ĐƠN VỊ HOẶC PHÒNG BAN'

      	UPDATE [dbo].[MW_OUT]

        SET AUTH_STATUS='A',

        CHECKER_ID=@p_CHECKER_ID,

        STATUS ='CONFIRM',

  	    APPROVE_DT =  CONVERT(DATETIME, @p_APPROVE_DT, 103)

        WHERE [OUT_ID]=@p_OUT_ID;

---- Khai báo cur insert bước xác nhận cho người tạo upload file danh sách chứng thực xuất kho

        DECLARE CUR_CONFIRM CURSOR FOR

        SELECT ISNULL(UNIT_RECEIVE,''), ISNULL(DEP_RECEIVE,'')

        FROM MW_OUT_DT 

        WHERE OUT_ID = @p_OUT_ID

        GROUP BY ISNULL(UNIT_RECEIVE,''), ISNULL(DEP_RECEIVE,'')

        OPEN CUR_CONFIRM

        FETCH NEXT FROM CUR_CONFIRM INTO @C_TO_BRN_ID,@C_TO_DEP_ID

        WHILE @@FETCH_STATUS = 0

        BEGIN

            DECLARE @p_OUT_CONF_ID_1 VARCHAR(15);

          	EXEC SYS_CodeMasters_Gen 'MW_OUT_CONF', @p_OUT_CONF_ID_1 OUT;

          	IF(NOT EXISTS(SELECT 1 FROM MW_OUT_CONF WHERE OUT_ID = @p_OUT_ID AND ISNULL(BRANCH_RECIVE,'') = ISNULL(@C_TO_BRN_ID,'') AND ISNULL(DEP_RECIVE,'') = ISNULL(@C_TO_DEP_ID,'')))

          	BEGIN

          		INSERT INTO MW_OUT_CONF (ID, OUT_ID, BRANCH_RECIVE, DEP_RECIVE, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, BRANCH_CREATE, RECORD_STATUS,STATUS)   

              VALUES (@p_OUT_CONF_ID_1,@p_OUT_ID,@C_TO_BRN_ID,@C_TO_DEP_ID,NULL,NULL,NULL, NULL,NULL,'1','CONFIRM')

          	END

            FETCH NEXT FROM CUR_CONFIRM INTO @C_TO_BRN_ID,@C_TO_DEP_ID

        END

        CLOSE CUR_CONFIRM

        DEALLOCATE CUR_CONFIRM

                SELECT @p_BRANCH_LOGIN = TU.TLSUBBRID, @p_DEP_LOGIN = TU.SECUR_CODE FROM TL_USER TU WHERE TU.TLNANME = (SELECT MO.MAKER_ID FROM MW_OUT MO WHERE MO.OUT_ID = @p_OUT_ID)

              SET @CUR_PROCESS = (SELECT TOP 1 MRP.PROCESS_ID FROM MW_REQUEST_PROCESS MRP WHERE MRP.REQ_ID = @p_OUT_ID AND MRP.STATUS = 'C')

              UPDATE MW_REQUEST_PROCESS SET STATUS = 'P' WHERE REQ_ID = @p_OUT_ID AND STATUS = 'C'

              INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT,NOTES)

              VALUES(@p_OUT_ID,'CONF',N'Nhân viên tạo phiếu xác nhận','C','NVTT',@p_BRANCH_LOGIN,@CUR_PROCESS,@p_DEP_LOGIN, 'Update',GETDATE(), N'Xác nhận giao')

      END

      IF @@Error<>0 GOTO ABORT;

      WHILE @@FETCH_STATUS=0 

      BEGIN

    	  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 > (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, @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

        --Update MW_Mast_BAL khi TDV duyệt

        --UPDATE MW_MAST_BAL SET QTY_BALANCE = (QTY_BALANCE-@QTY), QTY_REAL = (QTY_REAL-@QTY), QTY_TEMP = (QTY_TEMP+@QTY) WHERE MAST_BAL_ID = @MAST_BAL_ID

--      	INSERT INTO dbo.MW_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)

--      	VALUES(@p_OUT_ID,'APPR',@p_CHECKER_ID,GETDATE(), N'Hành chính duyệt phiếu.',N'Hành chính duyệt phiếu thành công.')

        IF @@Error<>0 GOTO ABORT;

        FETCH NEXT FROM XmlData

         INTO @OUT_ID,

             @MAST_BAL_ID,

             @CUST_NAME,

             @QTY,

             @PRICE,

             @TOTAL_AMT,

             @NOTES,

             @TO_BRN_ID,

             @TO_DEPT_ID;

        END;

        CLOSE XmlData;

        DEALLOCATE XmlData;

  END

	INSERT INTO dbo.MW_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)

	VALUES(@p_OUT_ID,'APPNEW',@p_CHECKER_ID,GETDATE(), N'Trưởng đơn vị duyệt phiếu',N'Trưởng đơn vị duyệt phiếu thành công')

--NẾU CÁC KHO THƯỜNG THÌ TRỪ SỐ. - KHO QUÀ TẶNG THÌ TRỪ Ở BƯỚC KSV

  IF(NOT EXISTS(SELECT 1

            FROM STRING_SPLIT(@ListWare_CODE,',') A

            INNER JOIN CM_WARE B ON A.VALUE = B.WARE_CODE

            INNER JOIN MW_OUT C ON B.WARE_ID = C.WARE_ID

            WHERE C.OUT_ID = @p_OUT_ID

            ))

  BEGIN

      UPDATE MMB SET MMB.QTY_REAL = MMB.QTY_REAL - RE1.QTY, MMB.QTY_TEMP = MMB.QTY_TEMP + RE1.QTY

      FROM (SELECT SUM(RE.QTY) AS QTY, RE.MAST_BAL_ID

          FROM(

              SELECT A.MAST_BAL_ID, A.QTY

              FROM MW_OUT_DT A

              WHERE A.OUT_ID = @p_OUT_ID) RE

          GROUP BY RE.MAST_BAL_ID) RE1

      INNER JOIN MW_MAST_BAL MMB ON RE1.MAST_BAL_ID = MMB.MAST_BAL_ID 

  END

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

ALTER PROCEDURE dbo.MW_OUT_KSV_KT_App 

    @p_OUT_ID VARCHAR(15) =NULL,

    @p_CHECKER_ID VARCHAR(100) =NULL,

    @p_APPROVE_DT VARCHAR(20) =NULL,

    @p_XmlData XML=NULL

AS

DECLARE @OUT_ID VARCHAR(15) =NULL,@OUT_DT_ID VARCHAR(15) =NULL,

        @BRN_ID VARCHAR(15) =NULL,

        @DEPT_ID VARCHAR(20)=NULL,

        @MAST_BAL_ID VARCHAR(15) =NULL,

        @CUST_NAME NVARCHAR(200) =NULL,

        @QTY DECIMAL(18,2)=NULL,

        @PRICE NUMERIC(18, 0) =NULL,

        @TOTAL_AMT NUMERIC(18, 2) =NULL,

        @NOTES NVARCHAR(1000) =NULL,

        @TO_BRN_ID VARCHAR(15) =NULL,

        @TO_DEPT_ID VARCHAR(15) =NULL;

DECLARE @hdoc INT;

DECLARE @INDEX INT =0

DECLARE @p_ID_MAS_BAL VARCHAR(15);

EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XmlData;

DECLARE @C_TO_BRN_ID VARCHAR(15), @C_TO_DEP_ID VARCHAR(15)

DECLARE XmlData CURSOR FOR

SELECT *

  FROM

    OPENXML(@hdoc, '/Root/XmlData', 2)

    WITH(

        OUT_ID VARCHAR(15),

        OUT_DT_ID VARCHAR(15),

        MAST_BAL_ID VARCHAR(15),

        CUST_NAME NVARCHAR(200),

        QTY DECIMAL(18,2),

        PRICE NUMERIC(18, 0),

        TOTAL_AMT NUMERIC(18, 2),

        NOTES NVARCHAR(1000),

        TO_BRN_ID VARCHAR(15),

        TO_DEPT_ID VARCHAR(15)

        );

OPEN XmlData;

BEGIN TRANSACTION;

--Insert XmlData

--DELETE FROM MW_OUT_DT WHERE OUT_ID =@p_OUT_ID

DECLARE @p_ID VARCHAR(15);

FETCH NEXT FROM XmlData

 INTO @OUT_ID,@OUT_DT_ID,

      @MAST_BAL_ID,

      @CUST_NAME,

      @QTY,

      @PRICE,

      @TOTAL_AMT,

      @NOTES,

      @TO_BRN_ID,

      @TO_DEPT_ID;

DECLARE @l_SUM_QTY_BALANCE DECIMAL(18,2)=0;

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

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

  IF(@@FETCH_STATUS=-1)BEGIN

  		CLOSE XmlData

  		DEALLOCATE XmlData

      ROLLBACK TRANSACTION;

      SELECT '-1' AS Result,

          @p_OUT_ID OUT_ID,

          N'Vui lòng duyệt lại sau khi dữ liệu được load hoàn tất' ErrorDesc;

      RETURN '-1';

  END;

  IF((SELECT mo.STATUS FROM MW_OUT mo WHERE mo.OUT_ID = @p_OUT_ID)<> 'KSV_KT_APP')

  BEGIN

  	ROLLBACK TRANSACTION

    SELECT '-1' as Result, '' OUT_ID, N'Phiếu xuất chưa đến bước duyệt bạn không được phép cập nhật thông tin.' ErrorDesc

  	RETURN '-1'

  END

  IF NOT EXISTS(SELECT tugr.ROLE_ID FROM dbo.TL_USER_GET_ROLES(@p_CHECKER_ID) tugr WHERE tugr.ROLE_ID = 'KSV')

  BEGIN

  	ROLLBACK TRANSACTION

    SELECT '-1' as Result, '' OUT_ID, N'Người dùng không có quyền duyệt phiếu này.' ErrorDesc

  	RETURN '-1'

  END

-- 24-03-19: KIEM TRA NEU DANG TRA VE THI KHONG DUOC PHEP DUYET

  IF((SELECT 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

      DECLARE @CORE_NOTE NVARCHAR(1000) = NULL,

		@MATERIAL_ID VARCHAR(15) = NULL,

		@VAT NUMERIC(18, 0), @VAT_AMT NUMERIC(18, 0),@PRICE_VAT NUMERIC(18, 0),

		@FR_BRN_ID varchar(15) = NULL,

		@FR_DEPT_ID varchar(15) = NULL,

        @WARE_ID VARCHAR(15) = NULL

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

		DECLARE @l_DEP_CODE VARCHAR(15) = NULL;

        DECLARE @l_D_BRANCH_CODE VARCHAR(15) = NULL;

        DECLARE @l_C_BRANCH_CODE VARCHAR(15) = NULL

        DECLARE @l_MAT_CODE VARCHAR(15) = NULL;

        DECLARE @l_WARE_ID VARCHAR(15) = NULL;

        DECLARE @MATERIAL_ACCNO VARCHAR(15) = NULL;

        DECLARE @BR_CODE VARCHAR(25) = NULL;

        DECLARE @DP_CODE VARCHAR(25) = NULL;

        DECLARE @l_MAKER_ID VARCHAR(25) = NULL;

        DECLARE @p_MAKER_ID VARCHAR(25) = NULL;

        DECLARE @p_REQ_USER VARCHAR(25) = NULL;

--- GEN ID KSV ĐỂ QUẢN LÝ GD HẠCH TOÁN BƯỚC KSV ---

          DECLARE @p_OUT_KSV_ID VARCHAR(15);

        	EXEC SYS_CodeMasters_Gen 'OUT_KSV_ID', @p_OUT_KSV_ID OUT;

        ----- SO GIAO DICH

        DECLARE @l_TRN_NO VARCHAR(15), @l_MAKER_KT VARCHAR(100), @l_ETP_ID VARCHAR(20), @l_ET_ID VARCHAR(20), @BR_CR_TYPE VARCHAR(20), @PYC VARCHAR(20)

        SELECT @p_MAKER_ID = A.MAKER_ID, @WARE_ID = A.WARE_ID, @BR_CR_TYPE = CB.BRANCH_TYPE, @CORE_NOTE = A.NOTES, @PYC = A.MW_REQ_ID, @p_REQ_USER = A.REQ_USER

        FROM MW_OUT A LEFT JOIN CM_BRANCH CB ON A.BRN_ID = CB.BRANCH_ID

        WHERE A.OUT_ID = @p_OUT_ID

-------- NẾU CÓ PYC THÌ LẤY NGƯỜI TẠO PHIẾU LÀM NVHT, KHONG THÌ LẤY NGƯỜI YÊU CẦU ELSE MAKER

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

        BEGIN

        	SET @l_MAKER_ID = (SELECT MR.MAKER_ID FROM MW_REQ MR WHERE MR.REQ_ID = @PYC)

        END

        ELSE SET @l_MAKER_ID = ISNULL(@p_REQ_USER,@p_MAKER_ID)

        EXEC ENTRIES_POST_GEN_NO_MW @p_BRANCH_ID = @l_MAKER_ID

                        ,@p_TRN_DATE = @p_APPROVE_DT

                        ,@p_KeyGen = @l_TRN_NO OUT

        -----------------

  --Kiểm tra Kho nếu kho đặt biệt check đã yêu cầu hạch toán chưa nếu chưa yêu cầu chọn hạch toán mới được qua kế toán

  DECLARE @WARE_CODE VARCHAR(25),@ListWare_CODE VARCHAR(500);

  SELECT @WARE_CODE=cw.WARE_CODE FROM MW_OUT MO LEFT JOIN CM_WARE cw ON MO.WARE_ID = cw.WARE_ID WHERE MO.OUT_ID = @p_OUT_ID

  SELECT @ListWare_CODE = sp.ParaValue FROM SYS_PARAMETERS sp WHERE sp.ParaKey = 'MW_OUT_WARE_CUSTOMER'

  IF(EXISTS(SELECT 1 FROM STRING_SPLIT(@ListWare_CODE, ',') WHERE VALUE = @WARE_CODE))

  BEGIN

      IF EXISTS(SELECT 1 

        FROM MW_OUT_DT A

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

        WHERE A.OUT_ID = @p_OUT_ID

            AND ((A.UNIT_RECEIVE = B.BRN_ID AND B.BRN_ID <> 'DV0001')

                 OR (A.UNIT_RECEIVE = B.BRN_ID AND B.BRN_ID = 'DV0001' AND ISNULL(B.DEPT_ID,'') = ISNULL(A.DEP_RECEIVE,''))

                )

        )

      BEGIN

        	UPDATE [dbo].[MW_OUT]

            SET AUTH_STATUS='A',

            CHECKER_ID=@p_CHECKER_ID,

            STATUS = CASE WHEN (SELECT REQ_ACOUNT FROM MW_OUT WHERE OUT_ID = @p_OUT_ID) <> '1' THEN 'REQ_ACOUNT' ELSE 'OUT_KT' END,

      	    APPROVE_DT =  CONVERT(DATETIME, @p_APPROVE_DT, 103)

            , OUT_KSV_ID = @p_OUT_KSV_ID

            WHERE [OUT_ID]=@p_OUT_ID;

      END

      ELSE

      BEGIN

      	UPDATE [dbo].[MW_OUT]

        SET AUTH_STATUS='A',

        CHECKER_ID=@p_CHECKER_ID,

        STATUS ='CONFIRM',

  	    APPROVE_DT =  CONVERT(DATETIME, @p_APPROVE_DT, 103)

        , OUT_KSV_ID = @p_OUT_KSV_ID

        WHERE [OUT_ID]=@p_OUT_ID;

      END

      IF @@Error<>0 GOTO ABORT;

      WHILE @@FETCH_STATUS=0 

      BEGIN

  	    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 > (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, @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

        -- UPDATE MW_MAST_BAL KHI TDV DUYỆT LÔ VẬT LIỆU SẼ TRỪ SỐ LƯỢNG HỆ THỐNG, TRỪ SỐ LƯỢNG THỰC TẾ, CỘNG SỐ LƯỢNG TREO

--        UPDATE MW_MAST_BAL SET QTY_BALANCE = QTY_BALANCE-@QTY, QTY_REAL = QTY_REAL-@QTY, QTY_TEMP = QTY_TEMP+@QTY WHERE MAST_BAL_ID = @MAST_BAL_ID

--        IF @@Error<>0 GOTO ABORT;

        IF(EXISTS(SELECT 1

            FROM STRING_SPLIT(@ListWare_CODE,',') A

            INNER JOIN CM_WARE B ON A.VALUE = B.WARE_CODE

            INNER JOIN MW_OUT C ON B.WARE_ID = C.WARE_ID

            WHERE C.OUT_ID = @p_OUT_ID

            ))

        BEGIN

            UPDATE B SET B.QTY_BALANCE = B.QTY_BALANCE - A.QTY, B.QTY_REAL = B.QTY_REAL - A.QTY, B.QTY_TEMP = B.QTY_TEMP + A.QTY, B.TOTAL_AMT = B.TOTAL_AMT - A.TOTAL_AMT

            FROM MW_OUT_DT A

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

            WHERE A.OUT_DT_ID = @OUT_DT_ID

        END

------------- HACH TOAN BUOC DUYET KSV XUAT KHO ------------

        SELECT @EXP_ACCTNO = B.EXP_ACCTNO, @VAT_ACCTNO = B.VAT_ACCTNO , @l_MAT_CODE = B.MATERIAL_CODE

		FROM MW_MAST_BAL A

        LEFT JOIN MW_MATERIAL B ON A.MATERIAL_ID = B.MATERIAL_ID

        WHERE A.MAST_BAL_ID = @MAST_BAL_ID

        DECLARE @FR_BRN_CODE VARCHAR(20) = (SELECT BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = (SELECT MO.BRN_ID FROM MW_OUT MO WHERE MO.OUT_ID = @p_OUT_ID))

        DECLARE @FR_DEP_CODE VARCHAR(20) = (SELECT DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID = (SELECT MO.DEPT_ID FROM MW_OUT MO WHERE MO.OUT_ID = @p_OUT_ID))

        DECLARE @TO_BRN_CODE VARCHAR(20) = (SELECT BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = @TO_BRN_ID)

        DECLARE @TO_DEP_CODE VARCHAR(20) = (SELECT DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID = @TO_DEPT_ID)

        ----------------HACH TOAN 05/09/2023 KHIEMCHG------------------

        -------------------CHECK THEO LOAI KHO-------------------------

        DECLARE @l_HS_BRANCH_CODE VARCHAR(20) = (SELECT CB.BRANCH_CODE FROM CM_BRANCH CB WHERE CB.BRANCH_TYPE = 'HS')

        DECLARE @BRANCH_TYPE VARCHAR(20) = (SELECT CB.BRANCH_TYPE FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @TO_BRN_ID)

        SET @WARE_CODE = (SELECT CW.WARE_CODE FROM CM_WARE CW WHERE CW.WARE_ID = @WARE_ID)

        DECLARE @WARE_TRANSFER_NO VARCHAR(20) = (SELECT TOP 1 ACC_NO FROM ENTRIES_POST_ACCNO_MW WHERE WARE_CODE = @WARE_CODE AND ACC_TYPE LIKE 'TRANS_%')

        DECLARE @WARE_ACCNO VARCHAR(20) = (SELECT  TOP 1 CW.ACC_ACCOUNTING FROM CM_WARE CW WHERE CW.WARE_ID = @WARE_ID)

        DECLARE @ACCNO_KSV_OUT VARCHAR(20) = (SELECT  TOP 1 ACC_NO FROM ENTRIES_POST_ACCNO_MW WHERE ACC_TYPE = 'KSV_OUT')

  		--LAY MA BRANCH CODE

--        DECLARE @DB_ID VARCHAR(15) = (SELECT CW.DEP_ID FROM CM_WARE CW WHERE CW.WARE_ID = @WARE_ID)

--        SELECT @DP_CODE = CD.DEP_CODE, @BR_CODE = CB.BRANCH_CODE 

--        FROM CM_DEPARTMENT CD LEFT JOIN CM_BRANCH CB ON CD.BRANCH_ID = CB.BRANCH_ID 

--        WHERE CD.DEP_ID = @DB_ID

                BEGIN

                ---   TODO: NỢ

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID OUT

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST', @l_ET_ID OUT

                INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT_KSV', @p_OUT_KSV_ID,@FR_BRN_CODE, @ACCNO_KSV_OUT, 'VND', 'D', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_APPROVE_DT, 103), @l_MAKER_ID, @p_CHECKER_ID, @FR_DEP_CODE, @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

            	IF @@Error <> 0 GOTO ABORT;

            	---   TODO: CÓ

            	  EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID OUT

                INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT_KSV', @p_OUT_KSV_ID,@FR_BRN_CODE, @WARE_ACCNO, 'VND', 'C', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_APPROVE_DT, 103), @l_MAKER_ID, @p_CHECKER_ID, '', @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

            	IF @@Error <> 0 GOTO ABORT;

        END

        FETCH NEXT FROM XmlData

         INTO @OUT_ID,@OUT_DT_ID,

             @MAST_BAL_ID,

             @CUST_NAME,

             @QTY,

             @PRICE,

             @TOTAL_AMT,

             @NOTES,

             @TO_BRN_ID,

             @TO_DEPT_ID;

      END;

      CLOSE XmlData;

      DEALLOCATE XmlData;

      DECLARE CUR_CONFIRM CURSOR FOR

      SELECT ISNULL(UNIT_RECEIVE,''), ISNULL(DEP_RECEIVE,'')

      FROM MW_OUT_DT 

      WHERE OUT_ID = @p_OUT_ID

      GROUP BY ISNULL(UNIT_RECEIVE,''), ISNULL(DEP_RECEIVE,'')

      OPEN CUR_CONFIRM

      FETCH NEXT FROM CUR_CONFIRM INTO @C_TO_BRN_ID,@C_TO_DEP_ID

      WHILE @@FETCH_STATUS = 0

      BEGIN

          DECLARE @p_OUT_CONF_ID VARCHAR(15);

        	EXEC SYS_CodeMasters_Gen 'MW_OUT_CONF', @p_OUT_CONF_ID OUT;

        	IF(NOT EXISTS(SELECT 1 FROM MW_OUT_CONF WHERE OUT_ID = @p_OUT_ID AND ISNULL(BRANCH_RECIVE,'') = ISNULL(@C_TO_BRN_ID,'') AND ISNULL(DEP_RECIVE,'') = ISNULL(@C_TO_DEP_ID,'')))

        	BEGIN

        		INSERT INTO MW_OUT_CONF (ID, OUT_ID, BRANCH_RECIVE, DEP_RECIVE, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, BRANCH_CREATE, RECORD_STATUS,STATUS)   

            VALUES (@p_OUT_CONF_ID,@p_OUT_ID,@C_TO_BRN_ID,@C_TO_DEP_ID,NULL,NULL,NULL, NULL,NULL,'1','CONFIRM')

        	END

          FETCH NEXT FROM CUR_CONFIRM INTO @C_TO_BRN_ID,@C_TO_DEP_ID

      END

      CLOSE CUR_CONFIRM

      DEALLOCATE CUR_CONFIRM

      --Thêm lịch sử 

      INSERT INTO dbo.MW_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)

	    VALUES(@p_OUT_ID,'APPR',@p_CHECKER_ID,GETDATE(), N'Hành chính duyệt phiếu',N'Hành chính duyệt phiếu')

  END

  ELSE

  BEGIN  	

      UPDATE [dbo].[MW_OUT]

          SET AUTH_STATUS='A',

          CHECKER_ID=@p_CHECKER_ID, STATUS ='CONFIRM',

          --APPROVE_DT=CONVERT(DATE, @p_APPROVE_DT, 103)

    	    -- LUCTV 13092019 CHINH SUA LAI NGAY DUYET DE LAY DUNG DINH DANG NGAY THANG NAM H PHUT GIAY

    	    APPROVE_DT =  CONVERT(DATETIME, @p_APPROVE_DT, 103)

          WHERE [OUT_ID]=@p_OUT_ID;

      IF @@Error<>0 GOTO ABORT;

      WHILE @@FETCH_STATUS=0 

      BEGIN

    	  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 > (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, @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

        --Update MW_Mast_BAL khi TDV duyệt

--        UPDATE MW_MAST_BAL SET QTY_BALANCE = (QTY_BALANCE-@QTY), QTY_REAL = (QTY_REAL-@QTY), QTY_TEMP = (QTY_TEMP+@QTY) WHERE MAST_BAL_ID = @MAST_BAL_ID

        DECLARE CUR_CONFIRM CURSOR FOR

        SELECT ISNULL(UNIT_RECEIVE,''), ISNULL(DEP_RECEIVE,'')

        FROM MW_OUT_DT 

        WHERE OUT_ID = @p_OUT_ID

        GROUP BY ISNULL(UNIT_RECEIVE,''), ISNULL(DEP_RECEIVE,'')

        OPEN CUR_CONFIRM

        FETCH NEXT FROM CUR_CONFIRM INTO @C_TO_BRN_ID,@C_TO_DEP_ID

        WHILE @@FETCH_STATUS = 0

        BEGIN

            DECLARE @p_OUT_CONF_ID_1 VARCHAR(15);

          	EXEC SYS_CodeMasters_Gen 'MW_OUT_CONF', @p_OUT_CONF_ID_1 OUT;

          	IF(NOT EXISTS(SELECT 1 FROM MW_OUT_CONF WHERE OUT_ID = @p_OUT_ID AND ISNULL(BRANCH_RECIVE,'') = ISNULL(@C_TO_BRN_ID,'') AND ISNULL(DEP_RECIVE,'') = ISNULL(@C_TO_DEP_ID,'')))

          	BEGIN

          		INSERT INTO MW_OUT_CONF (ID, OUT_ID, BRANCH_RECIVE, DEP_RECIVE, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, BRANCH_CREATE, RECORD_STATUS,STATUS)   

              VALUES (@p_OUT_CONF_ID_1,@p_OUT_ID,@C_TO_BRN_ID,@C_TO_DEP_ID,NULL,NULL,NULL, NULL,NULL,'1','CONFIRM')

          	END

            FETCH NEXT FROM CUR_CONFIRM INTO @C_TO_BRN_ID,@C_TO_DEP_ID

        END

        CLOSE CUR_CONFIRM

        DEALLOCATE CUR_CONFIRM

      	INSERT INTO dbo.MW_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)

      	VALUES(@p_OUT_ID,'APPR',@p_CHECKER_ID,GETDATE(), N'Kiểm soát viên xuất kho.',N'Kiểm soát viên xuất kho duyệt phiếu thành công.')

        IF @@Error<>0 GOTO ABORT;

        FETCH NEXT FROM XmlData

         INTO @OUT_ID,

             @MAST_BAL_ID,

             @CUST_NAME,

             @QTY,

             @PRICE,

             @TOTAL_AMT,

             @NOTES,

             @TO_BRN_ID,

             @TO_DEPT_ID;

        END;

        CLOSE XmlData;

        DEALLOCATE XmlData;

  END

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

ALTER PROCEDURE dbo.MW_OUT_KT_App

    @p_OUT_ID VARCHAR(15) = NULL,

    @p_KT_CHECKER_ID VARCHAR(100) = 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(100) = 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,

        @UNIT_RECEIVE VARCHAR(50)= NULL,

        @UNIT_CHARGE VARCHAR(50)= NULL,

        @UNIT_PAY VARCHAR(50)= NULL,

        @QTY_RECEIVE DECIMAL(18,2) = NULL,

        @QTY_DAMAGED DECIMAL(18,2) = NULL,

        @QTY_LOSS DECIMAL(18,2) = NULL,

        @RECEIVE_NOTES NVARCHAR(1000) = NULL,

        @DEP_RECEIVE VARCHAR(15) = NULL,

        @DEP_PAY VARCHAR(15) = NULL

DECLARE @l_DEP_CODE VARCHAR(15) = NULL;

DECLARE @l_D_BRANCH_CODE VARCHAR(15) = NULL;

DECLARE @l_C_BRANCH_CODE VARCHAR(15) = NULL

DECLARE @l_MAT_CODE VARCHAR(15) = NULL;

DECLARE @l_WARE_ID VARCHAR(15) = NULL;

DECLARE @MATERIAL_ACCNO VARCHAR(15) = NULL;

DECLARE @ListWare_CODE VARCHAR(500);

SELECT @ListWare_CODE = sp.ParaValue FROM SYS_PARAMETERS sp WHERE sp.ParaKey = 'MW_OUT_WARE_CUSTOMER'

DECLARE XmlData CURSOR FOR

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

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

A.TO_BRN_ID, A.MAST_BAL_ID,A.CUST_NAME,A.QTY,A.PRICE,A.NOTES, A.COST_ACC,A.UNIT_RECEIVE,A.UNIT_CHARGE, A.UNIT_PAY, A.QTY_RECEIVE, A.QTY_DAMAGED, A.QTY_LOSS, A.RECEIVE_NOTES

, A.DEP_RECEIVE, A.DEP_CHARGE

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

DECLARE @ALLOCTED VARCHAR(1000), @QTY_ALLOCTION VARCHAR(1000),@MW_REQ_ID VARCHAR(1000)

DECLARE @p_ID_MAS_BAL VARCHAR(15);

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

SELECT @MW_REQ_ID=mo.MW_REQ_ID FROM MW_OUT mo WHERE mo.OUT_ID = @p_OUT_ID

DECLARE @MW_OUT_QTY TABLE ( MAST_BAL_ID VARCHAR(50), QTY_OUT VARCHAR(50));

OPEN XmlData;

BEGIN TRANSACTION;

  IF((SELECT A.STATUS FROM MW_OUT A WHERE OUT_ID = @p_OUT_ID) <> 'SendAppKT')

  BEGIN

  	  ROLLBACK TRANSACTION

  		SELECT '-1' as Result,@p_OUT_ID OUT_ID, N'Chưa đến bước duyệt vui lòng gửi duyệt' ErrorDesc

  		RETURN '-1'

  END

-- 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,

    STATUS = 'DONE'

WHERE [OUT_ID] = @p_OUT_ID;

IF @@Error <> 0

    GOTO ABORT;

        ----- SO GIAO DICH

        DECLARE @l_TRN_NO VARCHAR(15), @l_MAKER_KT VARCHAR(100), @l_ETP_ID VARCHAR(20), @l_ET_ID VARCHAR(20), @BR_CR_TYPE VARCHAR(20)

        SELECT @KT_MAKER_ID = KT_MAKER_ID, @l_WARE_ID = WARE_ID, @BR_CR_TYPE = ISNULL(CB.BRANCH_TYPE, CB1.BRANCH_TYPE)

        FROM MW_OUT A 

        LEFT JOIN CM_BRANCH CB ON A.BRANCH_CREATE = CB.BRANCH_ID

        LEFT JOIN CM_BRANCH CB1 ON A.BRN_ID = CB1.BRANCH_ID

        WHERE OUT_ID =@p_OUT_ID

        EXEC ENTRIES_POST_GEN_NO_MW @p_BRANCH_ID = @KT_MAKER_ID

                        ,@p_TRN_DATE = @p_KT_APPROVE_DT

                        ,@p_KeyGen = @l_TRN_NO OUT

        -----------------

--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)

--Kiểm tra phiếu xuất nếu có phiếu yêu cầu thì cập nhật MW_REQ_DT

FETCH NEXT FROM XmlData INTO @OUT_DT_ID, @TOTAL_AMT,@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,@UNIT_RECEIVE,@UNIT_CHARGE,@UNIT_PAY,@QTY_RECEIVE,@QTY_DAMAGED,@QTY_LOSS,@RECEIVE_NOTES, @DEP_RECEIVE, @DEP_PAY

  WHILE @@FETCH_STATUS = 0

  BEGIN

    SET @MATERIAL_ACCTNO = @EXP_ACCTNO

  	-------- 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 */ 

   	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

    IF((SELECT IS_DONE FROM MW_REQ WHERE REQ_ID=@MW_REQ_ID) ='1')

    BEGIN

    	ROLLBACK TRANSACTION

      SELECT '-1' as Result, N'Phiếu yêu cầu mã' OUT_ID, N'Đã hoàn thành cấp phát bạn không thể duyệt phiếu.' ErrorDesc

    	RETURN '-1'

    END

    SELECT @QTY_ALLOCTION=mrd.QUANTITY_ALLOCATION, @ALLOCTED=mrd.ALLOCATED FROM MW_REQ_DT mrd WHERE mrd.REQ_ID = @MW_REQ_ID AND mrd.MATERIAL_ID = @MATERIAL_ID

    IF(EXISTS(SELECT 1

            FROM STRING_SPLIT(@ListWare_CODE,',') A

            INNER JOIN CM_WARE B ON A.VALUE = B.WARE_CODE

            INNER JOIN MW_OUT C ON B.WARE_ID = C.WARE_ID

            WHERE C.OUT_ID = @p_OUT_ID

            ))

    BEGIN

        UPDATE B SET B.QTY_TEMP = B.QTY_TEMP - A.QTY

        FROM MW_OUT_DT A

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

        WHERE A.OUT_DT_ID = @OUT_DT_ID

    END

    ELSE

    BEGIN

        UPDATE B SET B.QTY_TEMP = B.QTY_TEMP - A.QTY, B.QTY_BALANCE = B.QTY_BALANCE - A.QTY

        FROM MW_OUT_DT A

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

        WHERE A.OUT_DT_ID = @OUT_DT_ID

    END

--  	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 --THUANTM THÊM CẬP NHẬT SỐ LƯỢNG HỆ THỐNG

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

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

--          WHERE MAST_BAL_ID=@MAST_BAL_ID;

--

--        UPDATE MW_MAST_BAL SET QTY_BALANCE = (QTY_BALANCE-@QTY), QTY_REAL = (QTY_REAL-@QTY), QTY_TEMP = (QTY_TEMP+@QTY) WHERE MAST_BAL_ID = @MAST_BAL_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,

--              QTY_REAL=QTY_REAL-@QTY

--          WHERE MAST_BAL_ID=@MAST_BAL_ID;

--        

--          --Cập nhật Số lượng treo khi KT duyệt

--          --UPDATE MW_MAST_BAL SET QTY_TEMP= QTY_TEMP-@QTY WHERE MAST_BAL_ID=@MAST_BAL_ID

--

--      END;

      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

  		--LAY MA BRANCH CODE

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

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

  		DECLARE @BR_CODE_X VARCHAR(20) = (SELECT BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_ID)

  		DECLARE @DP_CODE_X VARCHAR(20) = (SELECT DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID = @DEPT_ID)

    ----------------HACH TOAN 05/09/2023 KHIEMCHG------------------

    -------------------CHECK THEO LOAI KHO-------------------------

    DECLARE @l_HS_BRANCH_CODE VARCHAR(20) = (SELECT CB.BRANCH_CODE FROM CM_BRANCH CB WHERE CB.BRANCH_TYPE = 'HS')

    DECLARE @BRANCH_TYPE VARCHAR(20) = (SELECT CB.BRANCH_TYPE FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @TO_BRN_ID)

    DECLARE @WARE_CODE VARCHAR(20) = (SELECT CW.WARE_CODE FROM CM_WARE CW WHERE CW.WARE_ID = @l_WARE_ID)

    DECLARE @WARE_ACCNO VARCHAR(20) = (SELECT CW.ACC_ACCOUNTING FROM CM_WARE CW WHERE CW.WARE_ID = @l_WARE_ID)

	SELECT @l_MAT_CODE = A.MATERIAL_CODE, @MATERIAL_ACCNO = MG.EXP_ACCTNO

    FROM MW_MATERIAL A

    LEFT JOIN MW_GROUP MG ON A.GROUP_ID = MG.GROUP_ID

    WHERE MATERIAL_ID = @MATERIAL_ID

    DECLARE @ACC_VAT VARCHAR(20) =  (SELECT TOP 1 EPAM.ACC_NO FROM ENTRIES_POST_ACCNO_MW EPAM WHERE EPAM.ACC_TYPE = 'ACC_VAT_PROMO_NEC')

    DECLARE @ACC_TRANS VARCHAR(20) =  (SELECT TOP 1 EPAM.ACC_NO FROM ENTRIES_POST_ACCNO_MW EPAM WHERE EPAM.ACC_TYPE = 'ASSET_CCLD_N')

        DECLARE @ACCNO_KSV_OUT VARCHAR(20) = (SELECT  TOP 1 ACC_NO FROM ENTRIES_POST_ACCNO_MW WHERE ACC_TYPE = 'KSV_OUT')

    BEGIN

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

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

    END

        --IF @WARE_CODE = '10AP' OR @WARE_CODE = '11VL' SET @WARE_ACCNO = @MATERIAL_ACCNO

        --LAY MA BRANCH CODE

        DECLARE @DB_ID VARCHAR(15) = (SELECT CW.DEP_ID FROM CM_WARE CW WHERE CW.WARE_ID = @l_WARE_ID)

        --        SELECT @DP_CODE = CD.DEP_CODE, @BR_CODE = CB.BRANCH_CODE 

        --        FROM CM_DEPARTMENT CD LEFT JOIN CM_BRANCH CB ON CD.BRANCH_ID = CB.BRANCH_ID 

        --        WHERE CD.DEP_ID = @DB_ID

--- XUẤT SỬ DỤNG ĐỐI VỚI KHO QUÀ TẶNG KHÁCH HÀNG, KHO QUÀ TẶNG MARKETING, KHO QUÀ TẶNG NHÂN VIÊN

        IF @WARE_CODE = '06QT' OR @WARE_CODE = '07TT' OR @WARE_CODE = '08NV' 

        BEGIN

            IF @BRANCH_CREATE = @UNIT_CHARGE

            BEGIN

                ---   TODO: NỢ

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID OUT

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST', @l_ET_ID OUT

                INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE, @EXP_ACCTNO /*@MATERIAL_ACCNO*/, 'VND', 'D', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, @DP_CODE, @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

            	IF @@Error <> 0 GOTO ABORT;

                ---   TODO: CÓ

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE_X, @ACCNO_KSV_OUT , 'VND', 'C', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, @DP_CODE_X, @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

            	IF @@Error <> 0 GOTO ABORT;

                -------- ĐI CHI PHÍ THUẾ VAT ---

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST', @l_ET_ID OUT

                ---   TODO: NỢ

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE, @EXP_ACCTNO /*@MATERIAL_ACCNO*/, 'VND', 'D',@PRICE_VAT, 1,@PRICE_VAT, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, @DP_CODE, @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

            	IF @@Error <> 0 GOTO ABORT;

                --IF(@VAT > 0)

                BEGIN

                	---   TODO: CÓ

                	EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                    INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                    VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE, @ACC_VAT, 'VND', 'C',@PRICE_VAT, 1,@PRICE_VAT, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, '', @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

                	IF @@Error <> 0 GOTO ABORT;

                END

            END

            ELSE

            BEGIN

            	---   TODO: NỢ

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID OUT

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST', @l_ET_ID OUT

                INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE, @EXP_ACCTNO , 'VND', 'D', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, @DP_CODE, @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

            	IF @@Error <> 0 GOTO ABORT;

                ---   TODO: CÓ 5199

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE, @ACC_TRANS, 'VND', 'C', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, '', @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

                IF @@Error <> 0 GOTO ABORT;

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST', @l_ET_ID OUT

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE, @EXP_ACCTNO , 'VND', 'D', @PRICE_VAT, 1, @PRICE_VAT, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, @DP_CODE, @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

            	IF @@Error <> 0 GOTO ABORT;

                --IF(@VAT > 0)

                BEGIN

                	---   TODO: CÓ

                	EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                    INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                    VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE, @ACC_VAT, 'VND', 'C',@PRICE_VAT, 1,@PRICE_VAT, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, '', @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

                	IF @@Error <> 0 GOTO ABORT;

                END

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST', @l_ET_ID OUT

                ---   TODO: NỢ

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE_X, @ACC_TRANS, 'VND', 'D', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, '', @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

                IF @@Error <> 0 GOTO ABORT;

                ---   TODO: CÓ

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE_X, @ACCNO_KSV_OUT , 'VND', 'C', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, @DP_CODE_X, @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

            	IF @@Error <> 0 GOTO ABORT;

            END

            DECLARE @MAT_NAME NVARCHAR(1000), @UNIT NVARCHAR(50)

            SELECT @MAT_NAME = mm.MATERIAL_NAME, @UNIT = cu.UNIT_NAME FROM MW_MATERIAL mm

            LEFT JOIN CM_UNIT cu ON mm.UNIT_ID = cu.UNIT_ID

            WHERE mm.MATERIAL_CODE = @l_MAT_CODE

            INSERT INTO MTTB_INVOICE_ITEMS_DETAIL (item_id, trn_ref_no, branch_code, trn_ccy, txn_date, total_fee, total_vat, total_txn, stt, service_name, unit, quantity, unit_price, total, record_stat, order_no, tienthue, tongtiensauthue, thuesuat, app_code, ex_ref, etp_id)

            VALUES (@l_ET_ID, '', @BR_CODE, 'VND', CONVERT(DATE, @p_KT_APPROVE_DT, 103), @QTY*@PRICE, @PRICE_VAT, @QTY*@PRICE + @PRICE_VAT, CONVERT(VARCHAR(8), @INDEX), @MAT_NAME, @UNIT, CONVERT(VARCHAR(255), @QTY), CONVERT(VARCHAR(255), @PRICE), CONVERT(VARCHAR(255), @QTY*@PRICE), 'O', @INDEX, CONVERT(VARCHAR(255), @PRICE_VAT), CONVERT(VARCHAR(255), @QTY*@PRICE + @PRICE_VAT), CONVERT(VARCHAR(255), @VAT), 'AMS_AMS', @p_OUT_ID, @l_ET_ID)

        END

        ELSE 

        BEGIN

                    IF @BRANCH_CREATE = @UNIT_CHARGE

                    BEGIN

                        EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST', @l_ET_ID out

                	    ---   TODO: NỢ

                        EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                        INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                        VALUES (@l_ETP_ID, @l_ETP_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE, @EXP_ACCTNO /*@MATERIAL_ACCNO*/, 'VND', 'D', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, @DP_CODE, @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

                    	IF @@Error <> 0 GOTO ABORT;

                    	---   TODO: CÓ

                    	EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                        INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                        VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE_X, @WARE_ACCNO, 'VND', 'C', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, '', @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

                    	IF @@Error <> 0 GOTO ABORT;

                    END

                    ELSE IF @BRANCH_CREATE <> @UNIT_CHARGE

                    BEGIN

                    	EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST', @l_ET_ID out

                	    ---   TODO: NỢ

                        EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                        INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                        VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE, @EXP_ACCTNO /*@MATERIAL_ACCNO*/, 'VND', 'D', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, @DP_CODE, @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

                    	IF @@Error <> 0 GOTO ABORT;

                    	---   TODO: CÓ

                    	EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                        INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                        VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE, @ACC_TRANS, 'VND', 'C', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, '', @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

                    	IF @@Error <> 0 GOTO ABORT;

                        ---   TODO: NỢ

                EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST', @l_ET_ID OUT

                        EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                        INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                        VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE_X, @ACC_TRANS /*@MATERIAL_ACCNO*/, 'VND', 'D', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, '', @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

                    	IF @@Error <> 0 GOTO ABORT;

                    	---   TODO: CÓ

                    	EXEC SYS_CodeMasters_Gen 'MW_ENTRIES_POST_SYNC', @l_ETP_ID out

                        INSERT INTO MW_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_TYPE, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO, WARE_CODE)

                        VALUES (@l_ETP_ID, @l_ET_ID, 'MW_OUT', @p_OUT_ID,@BR_CODE_X, @WARE_ACCNO, 'VND', 'C', @QTY*@PRICE, 1, @QTY*@PRICE, @l_MAT_CODE,CONVERT(DATETIME, @p_KT_APPROVE_DT, 103), @KT_MAKER_ID, @p_KT_CHECKER_ID, '', @CORE_NOTE,@l_TRN_NO,@WARE_CODE);

                    	IF @@Error <> 0 GOTO ABORT;

                    END

        END

	----------------END HACH TOAN 05/09/2023 KHIEMCHG------------------

      FETCH NEXT FROM XmlData INTO @OUT_DT_ID, @TOTAL_AMT,@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,@UNIT_RECEIVE,@UNIT_CHARGE,@UNIT_PAY,@QTY_RECEIVE,@QTY_DAMAGED,@QTY_LOSS,@RECEIVE_NOTES, @DEP_RECEIVE, @DEP_PAY

  END

  CLOSE XmlData

  DEALLOCATE XmlData

--NẾU CÓ PYC THÌ CỘNG SL VÀO PYC

  DECLARE @REQ_ID VARCHAR(15) = (SELECT MT.MW_REQ_ID FROM MW_OUT MT WHERE MT.OUT_ID = @p_OUT_ID)

  IF(ISNULL(@REQ_ID,'') <> '')

  BEGIN

      UPDATE D SET D.ALLOCATED = ISNULL(D.ALLOCATED,0) + RE.QTY

      FROM (

        SELECT A.MAST_BAL_ID, SUM(A.QTY) AS QTY, A.UNIT_RECEIVE, ISNULL(A.DEP_RECEIVE,'') AS DEP_RECEIVE

        FROM MW_OUT_DT A    

        WHERE A.OUT_ID = @p_OUT_ID

        GROUP BY A.MAST_BAL_ID, A.UNIT_RECEIVE, ISNULL(A.DEP_RECEIVE,'')) RE 

      INNER JOIN MW_MAST_BAL B ON RE.MAST_BAL_ID = B.MAST_BAL_ID      

      INNER JOIN MW_REQ_DT D ON B.MATERIAL_ID = D.MATERIAL_ID AND D.BRANCH_USE = RE.UNIT_RECEIVE AND ISNULL(D.DEP_USE,'') = ISNULL(RE.DEP_RECEIVE,'')

      WHERE D.REQ_ID = @REQ_ID

      IF(EXISTS(SELECT 1 FROM MW_REQ_DT WHERE ISNULL(QUANTITY_ALLOCATION,0) < ISNULL(ALLOCATED,0) AND REQ_ID = @REQ_ID))

      BEGIN

          ROLLBACK TRANSACTION

    		  SELECT N'Số lượng cấp phát vượt yêu cầu của đơn vị.' AS Result,

               '' OUT_ID,

               '' ErrorDesc;

          RETURN '-1';

      END

      IF(NOT EXISTS(SELECT 1 FROM MW_REQ_DT MRD WHERE MRD.REQ_ID = @REQ_ID AND ISNULL(MRD.QUANTITY_ALLOCATION,0) <> ISNULL(MRD.ALLOCATED,0)))

      BEGIN

          UPDATE MW_REQ SET IS_DONE = '1' WHERE REQ_ID = @REQ_ID

      END

  END   

	INSERT INTO dbo.MW_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)

	VALUES(@p_OUT_ID,'KT_APP',@p_KT_CHECKER_ID,GETDATE(), N'Kiểm soát viên kế toán duyệt phiếu',N'Kiểm soát viê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

UPDATE MW_OUT SET TOTAL_AMT = (SELECT SUM(mod.TOTAL_AMT) FROM MW_OUT_DT mod WHERE mod.OUT_ID = MW_OUT.OUT_ID) WHERE OUT_ID IN ('MWOUT0000000002','MWOUT0000000005')