AMS - VB

/*INSERT INTO SYS_PREFIX(ID,Prefix,[Description]) VALUES('MW_MAST_BAL','MASB','') 

INSERT INTO SYS_CODEMASTERS(Prefix,CurValue,Active) VALUES('MASB','1','1')*/

/*INSERT INTO SYS_PREFIX(ID,Prefix,[Description]) VALUES('MW_OUT_DT','MWTDT','')

INSERT INTO SYS_CODEMASTERS(Prefix,CurValue,Active) VALUES('MWTDT','1','1')*/

ALTER PROCEDURE dbo.MW_LIQ_MASTER_App @p_LIQ_ID VARCHAR(15) =NULL,

    @p_CHECKER_ID VARCHAR(100) =NULL,

    @p_APPROVE_DT VARCHAR(20) =NULL,

    @p_XmlData XML=NULL

AS

DECLARE @LIQ_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;

EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XmlData;

DECLARE XmlData CURSOR FOR

SELECT *

  FROM

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

    WITH(

        LIQ_ID VARCHAR(15),

        MAST_BAL_ID VARCHAR(15),

        --FR_BRN_ID varchar(15) ,

        --FR_DEPT_ID varchar(15) ,

        --FR_PRICE_ID varchar(15) ,

        CUST_NAME NVARCHAR(200),

        QTY DECIMAL(18,2),

        --QTY_OLD int ,

        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 @LIQ_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_LIQ_ID LIQ_ID,

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

    RETURN '-1';

END;

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

IF((SELECT AUTH_STATUS FROM MW_LIQ_MASTER WHERE LIQ_ID = @p_LIQ_ID) ='R')

		BEGIN

			CLOSE XmlData

			DEALLOCATE XmlData

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, @p_LIQ_ID LIQ_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

--end

UPDATE [dbo].[MW_LIQ_MASTER]

   SET AUTH_STATUS='A', KT_AUTH_STATUS = 'E',

    CHECKER_ID=@p_CHECKER_ID,

	TOTAL_AMT = (SELECT SUM(TOTAL_AMT) FROM MW_LIQ_DT WHERE LIQ_ID =@p_LIQ_ID),

    APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103)

 WHERE [LIQ_ID]=@p_LIQ_ID;

 DECLARE @INDEX INT =0

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, @p_LIQ_ID LIQ_ID, N'Dòng '+ CONVERT(VARCHAR(15),@INDEX) +N': Số lượng thanh lý vượt quá số lượng tồn' ErrorDesc

--			RETURN '-1'

--		END

		DECLARE @p_ID_MAS_BAL VARCHAR(15);

		IF @@Error<>0 GOTO ABORT;

    FETCH NEXT FROM XmlData

     INTO @LIQ_ID,

         @MAST_BAL_ID,

         @CUST_NAME,

         @QTY,

         @PRICE,

         @TOTAL_AMT,

         @NOTES,

         @TO_BRN_ID,

         @TO_DEPT_ID;

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_LIQ_ID,'APPR',@p_CHECKER_ID,GETDATE(), N'Hành chính duyệt phiếu',N'Hành chính duyệt phiếu')

COMMIT TRANSACTION;

SELECT '0' AS Result, @p_LIQ_ID LIQ_ID, '' ErrorDesc;

RETURN '0';

ABORT:

BEGIN

    CLOSE XmlData;

    DEALLOCATE XmlData;

    ROLLBACK TRANSACTION;

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

    RETURN '-1';

END;