AMS - VB

ALTER PROCEDURE dbo.MW_OUT_Ins @p_TRN_Date VARCHAR(25) = NULL,

@p_TRN_TIME VARCHAR(50) = NULL,

@p_BRN_ID VARCHAR(15) = NULL, @p_DEPT_ID VARCHAR(15) = NULL,

@p_QTY DECIMAL(18, 2) = NULL, @p_PRICE NUMERIC(18, 0) = NULL,

@p_TOTAL_AMT NUMERIC(18, 2) = NULL, @p_NOTES NVARCHAR(1000),

@p_OUT_DESC NVARCHAR(500) = NULL,

@p_AUTH_STATUS VARCHAR(1) = NULL, @p_MAKER_ID VARCHAR(100) = NULL,

@p_CREATE_DT VARCHAR(25) = NULL, @p_CHECKER_ID VARCHAR(100) = NULL,

@p_APPROVE_DT VARCHAR(25) = NULL,

@p_KT_AUTH_STATUS VARCHAR(1) = NULL, @p_KT_MAKER_ID VARCHAR(100) = NULL,

@p_KT_CREATE_DT VARCHAR(25) = NULL, @p_KT_CHECKER_ID VARCHAR(100) = NULL,

@p_KT_APPROVE_DT VARCHAR(25) = NULL, @p_RECORD_STATUS VARCHAR(1) = NULL,

@p_XmlData XML = NULL, @p_CORE_NOTE NVARCHAR(500) = NULL,

@p_BRANCH_CREATE VARCHAR(15) = NULL, @p_WARE_HOUSE VARCHAR(15) = NULL,

@p_MW_REQ_ID VARCHAR(50) = NULL, @p_PROMOTION_ID VARCHAR(50) = NULL,

@p_WARE_ID VARCHAR(50) = NULL,

@p_STATUS VARCHAR(50) = NULL, @p_REQ_ACOUNT VARCHAR(5) = NULL, @p_REQ_USER VARCHAR(150) = NULL

AS

  DECLARE

  --DVSD

  --@OUT_DT_ID varchar(15),

  @p_OUT_CODE NVARCHAR(100) = NULL

 ,@OUT_ID VARCHAR(15) = NULL

 ,@MAST_BAL_ID VARCHAR(15) = NULL

 ,@CUST_NAME NVARCHAR(200) = NULL

 ,@QTY DECIMAL(18, 2) = NULL

 ,@QTY_OLD DECIMAL(18, 2) = NULL

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

 ,@EVENT_NAME NVARCHAR(1000) = NULL

 ,@IS_BCT VARCHAR(1) = 'N'

 ,-- LUCTV BO SUNG 05-04-2019 TEN SU KIEN TREN LUOI DS XUAT VAT LIEU

  @COST_ACC VARCHAR(50) = NULL

 ,@CUSTOMER_NAME NVARCHAR(100) = NULL

 ,@CUSTOMER_ID VARCHAR(100) = NULL

 ,@CUSTOMER_CCCD VARCHAR(100) = NULL

 ,@CUSTOMER_LOCATION NVARCHAR(1000) = NULL

 ,@UNIT_RECEIVE VARCHAR(50) = NULL

 ,@DEP_RECEIVE VARCHAR(50) = NULL

 ,@UNIT_CHARGE VARCHAR(50) = NULL

 ,@DEP_CHARGE VARCHAR(50) = NULL

 ,@UNIT_PAY VARCHAR(50) = NULL

  DECLARE @INDEX INT = 0

  DECLARE @PRICE_CODE VARCHAR(30)

  DECLARE @hdoc INT;

  DECLARE @QUANTITY_ALLOCATION DECIMAL(18, 2)

         ,@ALLOCATED DECIMAL(18, 2)

         ,@MATERIAL_ID VARCHAR(50);

  DECLARE @BRANCH_OUT VARCHAR(50)

         ,@UNIT_RECEIVE_TYPE VARCHAR(15)

         ,@UNIT_CHARGE_TYPE VARCHAR(15)

  EXEC sp_xml_preparedocument @hdoc OUTPUT

                             ,@p_XmlData;

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

  QTY_OLD DECIMAL(18, 2),

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

  EVENT_NAME NVARCHAR(1000),

  IS_BCT VARCHAR(1),

  COST_ACC VARCHAR(50),

  UNIT_RECEIVE VARCHAR(50),

  DEP_RECEIVE VARCHAR(50),

  UNIT_CHARGE VARCHAR(50),

  DEP_CHARGE VARCHAR(50),

  UNIT_PAY VARCHAR(50),

  CUSTOMER_NAME VARCHAR(100),

  CUSTOMER_ID VARCHAR(100),

  CUSTOMER_CCCD VARCHAR(100),

  CUSTOMER_LOCATION VARCHAR(1000))

  OPEN XmlData;

  BEGIN TRANSACTION;

  IF (@p_WARE_ID IS NULL

    OR @p_WARE_ID = '')

  BEGIN

    ROLLBACK TRANSACTION

    SELECT

      '-1' AS Result

     ,'' OUT_ID

     ,N'Loại kho đang để trống. Vui lòng chọn và thử lại' ErrorDesc

    RETURN '-1'

  END

  DECLARE @p_OUT_ID VARCHAR(15)

         ,@l_COST_ACC VARCHAR(50);

  EXEC [MW_OUT_CODE_Gen] @p_BRANCH_CREATE

                        ,@p_WARE_HOUSE

                        ,@p_OUT_CODE OUT;

  --IF()

  EXEC SYS_CodeMasters_Gen 'MW_OUT'

                          ,@p_OUT_ID OUT;

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

  SET @p_STATUS = 'ADDNEW'

  INSERT INTO [dbo].[MW_OUT] ([OUT_ID],

                              [TRN_Date],

                              [TRN_TIME],

                              [BRN_ID],

                              [DEPT_ID],

                              [QTY],

                              [PRICE],

                              [TOTAL_AMT],

                              [NOTES],

                              [OUT_DESC],

                              [AUTH_STATUS],

                              [MAKER_ID],

                              [CREATE_DT],

                              [CHECKER_ID],

                              [APPROVE_DT],

                              [RECORD_STATUS],

                              [CORE_NOTE],

                              [BRANCH_CREATE],

                              [WARE_HOUSE],

                              [OUT_CODE],

                              MW_REQ_ID,

                              PROMOTION_ID,

                              WARE_ID,

                              STATUS,

                              REQ_ACOUNT

                              , IS_CANCEL, REQ_USER)

                        VALUES (

                              @p_OUT_ID

                             ,CONVERT(DATETIME, @p_TRN_Date, 103)

                             ,@p_TRN_TIME

                             ,@p_BRN_ID

                             ,@p_DEPT_ID

                             ,@p_QTY

                             ,@p_PRICE

                             ,@p_TOTAL_AMT

                             ,@p_NOTES

                             ,@p_OUT_DESC

                             ,'E'

                             ,@p_MAKER_ID

                             ,CONVERT(DATETIME, @p_CREATE_DT, 103)

                             ,NULL

                             ,NULL

                             ,'1'

                             ,@p_CORE_NOTE

                             ,@p_BRANCH_CREATE

                             ,@p_WARE_HOUSE

                             ,@p_OUT_CODE

                             ,@p_MW_REQ_ID

                             ,@p_PROMOTION_ID

                             ,@p_WARE_ID

                             ,@p_STATUS

                             ,@p_REQ_ACOUNT

                             ,'0',@p_REQ_USER)

  IF @@error <> 0

    GOTO ABORT;

  --Insert XmlData

  FETCH NEXT FROM XmlData

  INTO @OUT_ID, @MAST_BAL_ID, @CUST_NAME, @QTY, @QTY_OLD, @QTY_REAL_OLD, @PRICE, @TOTAL_AMT, @NOTES, @TO_BRN_ID, @TO_DEPT_ID, 

        @EVENT_NAME, @IS_BCT, @COST_ACC, @UNIT_RECEIVE, @DEP_RECEIVE, @UNIT_CHARGE, @DEP_CHARGE, @UNIT_PAY, @CUSTOMER_NAME, @CUSTOMER_ID, @CUSTOMER_CCCD, @CUSTOMER_LOCATION

  WHILE @@fetch_status = 0

  BEGIN

  SET @UNIT_RECEIVE_TYPE = (SELECT

                              CB.BRANCH_TYPE

                            FROM CM_BRANCH CB

                            WHERE CB.BRANCH_ID = @UNIT_RECEIVE)

  SET @UNIT_CHARGE_TYPE = (SELECT

                              CB.BRANCH_TYPE

                            FROM CM_BRANCH CB

                            WHERE CB.BRANCH_ID = @UNIT_CHARGE)

  IF @UNIT_RECEIVE_TYPE = 'HS' AND (@DEP_RECEIVE IS NULL OR @DEP_RECEIVE = '')

  BEGIN

    ROLLBACK TRANSACTION

    CLOSE XmlData;

    DEALLOCATE XmlData;

    SELECT      '-1' AS Result ,'' OUT_ID ,N'Dòng ' + CONVERT(VARCHAR(5), @INDEX) + N': Phòng ban nhận không được để trống.' ErrorDesc

    RETURN '-1'

  END

  IF @UNIT_CHARGE_TYPE = 'HS' AND (@DEP_CHARGE IS NULL OR @DEP_CHARGE = '')

  BEGIN

    ROLLBACK TRANSACTION

    CLOSE XmlData;

    DEALLOCATE XmlData;

    SELECT  '-1' AS Result ,'' OUT_ID ,N'Dòng ' + CONVERT(VARCHAR(5), @INDEX) + N': Phòng ban chịu chi phí không được để trống.' ErrorDesc

    RETURN '-1'

  END

  --Thuantm TẠO PHIẾU XUẤT KIỂM TRA SỐ LƯỢNG CẤP PHÁT PHIẾU YÊU CẦU NẾU số lượng xuất vượt quá số lượng cấp phát thì chặn.

  SELECT

    @MATERIAL_ID = MATERIAL_ID

  FROM MW_MAST_BAL mmb

  WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID

  SELECT

    @QUANTITY_ALLOCATION = mrd.QUANTITY_ALLOCATION

   ,@ALLOCATED = mrd.ALLOCATED

  FROM MW_REQ_DT mrd

  WHERE mrd.REQ_CODE = @p_MW_REQ_ID

  AND mrd.MATERIAL_ID = @MATERIAL_ID

---- CHẶN SỐ LƯỢNG XUẤT LỚN HƠN PYC ----

DECLARE @CUR_QTY_PYC DECIMAL

IF(@p_MW_REQ_ID IS NOT NULL AND @p_MW_REQ_ID <> '')

BEGIN

	SELECT @CUR_QTY_PYC = MRD.QUANTITY_REQ FROM MW_REQ_DT MRD WHERE MRD.REQ_ID = @p_MW_REQ_ID AND MRD.BRANCH_USE = @UNIT_RECEIVE AND ISNULL(MRD.DEP_USE, '') = ISNULL(@DEP_RECEIVE, '') AND MRD.MATERIAL_ID = @MATERIAL_ID

    IF(@CUR_QTY_PYC < @QTY)

    BEGIN

        ROLLBACK TRANSACTION

        CLOSE XmlData;

        DEALLOCATE XmlData;

        SELECT  '-1' AS Result ,'' OUT_ID ,N'Dòng ' + CONVERT(VARCHAR(5), @INDEX) + N': Số lượng xuất lớn hơn số lượng được yêu cầu.' ErrorDesc

        RETURN '-1'

    END

END

  SET @INDEX = @INDEX + 1

  SET @PRICE_CODE = (SELECT

      PRICE_CODE

    FROM MW_MAST_PRICE

    WHERE PRICE_ID = (SELECT

        PRICE_ID

      FROM MW_MAST_BAL

      WHERE MAST_BAL_ID = @MAST_BAL_ID))

--  IF (@COST_ACC IS NULL OR @COST_ACC = '')

--  BEGIN

--    ROLLBACK TRANSACTION

--    CLOSE XmlData;

--    DEALLOCATE XmlData;

--    SELECT '1' AS Result, '' OUT_ID, N'Danh sách đơn vị/ khách hàng nhận, Dòng ' + CONVERT(VARCHAR(5), @INDEX) + N': Tài khoản chi phí không được để trống' 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;

  INSERT INTO [dbo].[MW_OUT_DT] ([OUT_DT_ID], [OUT_ID], [MAST_BAL_ID], [CUST_NAME], [QTY], [QTY_OLD], [QTY_REAL_OLD], [PRICE], [TOTAL_AMT], [NOTES], 

            [TO_BRN_ID], [TO_DEPT_ID], [EVENT_NAME], [IS_BCT], [COST_ACC], UNIT_RECEIVE, DEP_RECEIVE, UNIT_CHARGE, DEP_CHARGE, UNIT_PAY, CUSTOMER_NAME, CUSTOMER_ID, CUSTOMER_CCCD, CUSTOMER_LOCATION)

    VALUES (

      @p_OUT_DT_ID

     ,@p_OUT_ID

     ,@MAST_BAL_ID

     ,@CUST_NAME

     ,@QTY

     ,(SELECT mmb.QTY_BALANCE FROM MW_MAST_BAL mmb WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID)

     ,(SELECT mmb.QTY_REAL FROM MW_MAST_BAL mmb WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID)

     ,@PRICE

     ,@TOTAL_AMT

     ,@NOTES

     ,@TO_BRN_ID

     ,@TO_DEPT_ID

     ,@EVENT_NAME

     ,@IS_BCT

     ,@COST_ACC

     ,@UNIT_RECEIVE

     ,@DEP_RECEIVE

     ,@UNIT_CHARGE

     ,@DEP_CHARGE

     ,@UNIT_PAY

     ,@CUSTOMER_NAME

     ,@CUSTOMER_ID

     ,@CUSTOMER_CCCD

     ,@CUSTOMER_LOCATION)

  IF @@error <> 0

    GOTO ABORT;

  FETCH NEXT FROM XmlData

  INTO @OUT_ID, @MAST_BAL_ID, @CUST_NAME, @QTY, @QTY_OLD, @QTY_REAL_OLD, @PRICE, @TOTAL_AMT, @NOTES, @TO_BRN_ID, @TO_DEPT_ID, @EVENT_NAME, @IS_BCT, @COST_ACC, 

        @UNIT_RECEIVE, @DEP_RECEIVE, @UNIT_CHARGE, @DEP_CHARGE, @UNIT_PAY, @CUSTOMER_NAME, @CUSTOMER_ID, @CUSTOMER_CCCD, @CUSTOMER_LOCATION

  END;

  CLOSE XmlData;

  DEALLOCATE XmlData;

  UPDATE MW_OUT

  SET TOTAL_AMT = (SELECT SUM(TOTAL_AMT) FROM MW_OUT_DT WHERE OUT_ID = @p_OUT_ID)

  WHERE OUT_ID = @p_OUT_ID

  INSERT INTO dbo.MW_PROCESS (REQ_ID,

  PROCESS_ID,

  CHECKER_ID,

  APPROVE_DT,

  PROCESS_DESC,

  NOTES)

    VALUES (@p_OUT_ID,        -- REQ_ID - varchar(15)

    'INSERT',        -- PROCESS_ID - varchar(10)

    @p_MAKER_ID,        -- CHECKER_ID - varchar(15)

    GETDATE(), -- APPROVE_DT - datetime

    N'Thêm mới phiếu xuất vật liệu thành công', N'Thêm mới phiếu xuất vật liệu'      -- PROCESS_DESC - nvarchar(1000)

    )

        ---- CHECK BƯỚC QUY TRÌNH

         INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],ROLE_USER,[STATUS],BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, CHECKER_ID)

		 VALUES(@p_OUT_ID,'ADDNEW','NVTT','C',@p_BRN_ID,'',@p_DEPT_ID, @p_MAKER_ID)

  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_Upd

    @p_OUT_ID VARCHAR(15) = NULL,

    @p_TRN_Date VARCHAR(25) = NULL,

    @p_TRN_TIME VARCHAR(50) = NULL,

    @p_BRN_ID VARCHAR(15) = NULL,

    @p_DEPT_ID VARCHAR(15) = NULL,

    @p_QTY DECIMAL(18,2) = NULL,

    @p_PRICE NUMERIC(18, 0) = NULL,

    @p_TOTAL_AMT NUMERIC(18, 2) = NULL,

    @p_NOTES NVARCHAR(1000),

    @p_OUT_DESC NVARCHAR(500) = NULL,

    @p_AUTH_STATUS VARCHAR(1) = NULL,

    @p_MAKER_ID VARCHAR(100) = NULL,

    @p_CREATE_DT VARCHAR(25) = NULL,

    @p_CHECKER_ID VARCHAR(100) = NULL,

    @p_APPROVE_DT VARCHAR(25) = NULL,

    @p_KT_AUTH_STATUS VARCHAR(1) = NULL,

    @p_KT_MAKER_ID VARCHAR(100) = NULL,

    @p_KT_CREATE_DT VARCHAR(25) = NULL,

    @p_KT_CHECKER_ID VARCHAR(100) = NULL,

    @p_KT_APPROVE_DT VARCHAR(25) = NULL,

    @p_RECORD_STATUS VARCHAR(1) = NULL,

    @p_XmlData XML = NULL,

	  @p_CORE_NOTE nvarchar(500) = NULL,

	  @p_WARE_HOUSE VARCHAR(15) = NULL,

    @p_MW_REQ_ID VARCHAR(50)=NULL, 

    @p_PROMOTION_ID VARCHAR(50) = NULL, 

    @p_WARE_ID VARCHAR(50)=NULL,@p_REQ_ACOUNT VARCHAR(5)=NULL, @p_REQ_USER VARCHAR(150) = NULL

AS

DECLARE

    --DVSD

    @OUT_ID VARCHAR(15) = NULL,

    @MAST_BAL_ID VARCHAR(15) = NULL,

    @CUST_NAME NVARCHAR(200) = NULL,

    @QTY DECIMAL(18,2) = NULL,

	  @QTY_OLD DECIMAL(18,2) = NULL,

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

	  @EVENT_NAME NVARCHAR(1000) = NULL, --LUCTV BO SUNG 15-02-2019 TEN SU KIEN TREN LUOI DS XUAT VAT LIEU

	  @IS_BCT VARCHAR(1) = 'N',-- LUCTV BO SUNG 05-04-2019 TEN SU KIEN TREN LUOI DS XUAT VAT LIEU

	  @COST_ACC VARCHAR(50) = NULL,

    @UNIT_RECEIVE VARCHAR(50)= NULL,

    @DEP_RECEIVE VARCHAR(50)= NULL,

    @UNIT_CHARGE VARCHAR(50)= NULL,

    @DEP_CHARGE VARCHAR(50)= NULL,

    @UNIT_PAY VARCHAR(50)= NULL,

    @CUSTOMER_NAME NVARCHAR(100)= NULL,

    @CUSTOMER_ID VARCHAR(100)= NULL,

    @CUSTOMER_LOCATION NVARCHAR(1000)= NULL

DECLARE @INDEX INT =0

DECLARE @PRICE_CODE VARCHAR(30)

DECLARE @hdoc INT;

DECLARE @QUANTITY_ALLOCATION DECIMAL(18,2), @ALLOCATED DECIMAL(18,2), @MATERIAL_ID VARCHAR(50)

EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XmlData;

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

        QTY_OLD DECIMAL(18,2),

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

		    EVENT_NAME NVARCHAR(1000),

		    IS_BCT VARCHAR(1),-- LUCTV BO SUNG 05-04-2019 TEN SU KIEN TREN LUOI DS XUAT VAT LIEU

		    COST_ACC VARCHAR(50),

        UNIT_RECEIVE VARCHAR(50),

        DEP_RECEIVE VARCHAR(50),

        UNIT_CHARGE VARCHAR(50),

        DEP_CHARGE VARCHAR(50),

        UNIT_PAY VARCHAR(50),

        CUSTOMER_NAME VARCHAR(100),

        CUSTOMER_ID VARCHAR(100),

        CUSTOMER_LOCATION VARCHAR(1000)

    );

OPEN XmlData;

BEGIN TRANSACTION;

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

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' OUT_ID, N'Loại kho đang để trống. Vui lòng chọn và thử lại' ErrorDesc

			RETURN '-1'

		END	

IF(EXISTS(SELECT * FROM MW_OUT mo WHERE OUT_ID = @p_OUT_ID AND mo.STATUS = 'REQ_ACOUNT'))

BEGIN

	UPDATE MW_OUT SET REQ_ACOUNT=@p_REQ_ACOUNT WHERE OUT_ID = @p_OUT_ID

      DECLARE @CUR_PROCESS VARCHAR(20) = (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,'OUT_KT',N'Giao dịch viên xử lý','C','GDV',@p_BRN_ID,@CUR_PROCESS,@p_DEPT_ID, 'Approve',GETDATE(), N'Trưởng đơn vị phê duyệt')

END

ELSE

BEGIN

--    IF (SELECT mo.AUTH_STATUS FROM MW_OUT mo WHERE OUT_ID = @p_OUT_ID) = 'R'

--    BEGIN

--    	SET @p_AUTH_STATUS = 'E'

--    END ELSE 

    SET @p_AUTH_STATUS = 'E'

	UPDATE [dbo].[MW_OUT]

  SET 

    [QTY] = @p_QTY,

	  AUTH_STATUS=@p_AUTH_STATUS,

    [PRICE] = @p_PRICE,

    [TOTAL_AMT] = @p_TOTAL_AMT,

    [NOTES] = @p_NOTES,

	  MAKER_ID=@p_MAKER_ID,

	  [CORE_NOTE] = @p_CORE_NOTE,

	  WARE_HOUSE=  @p_WARE_HOUSE,DEPT_ID = @p_DEPT_ID,REQ_ACOUNT = @p_REQ_ACOUNT,

    MW_REQ_ID =  @p_MW_REQ_ID, PROMOTION_ID = @p_PROMOTION_ID, WARE_ID = @p_WARE_ID, REQ_USER = @p_REQ_USER

WHERE [OUT_ID] = @p_OUT_ID;

IF @@Error <> 0

    GOTO ABORT;

--Insert XmlData

DECLARE @l_COST_ACC VARCHAR(50);

DELETE FROM MW_OUT_DT WHERE OUT_ID = @p_OUT_ID;

FETCH NEXT FROM XmlData

INTO @OUT_ID, @MAST_BAL_ID, @CUST_NAME, @QTY, @QTY_OLD,@QTY_REAL_OLD,@PRICE,@TOTAL_AMT, @NOTES,@TO_BRN_ID, @TO_DEPT_ID, @EVENT_NAME,@IS_BCT, @COST_ACC,@UNIT_RECEIVE,@DEP_RECEIVE,@UNIT_CHARGE,@DEP_CHARGE,@UNIT_PAY,@CUSTOMER_NAME,@CUSTOMER_ID,@CUSTOMER_LOCATION

WHILE @@FETCH_STATUS = 0

BEGIN

  --Thuantm TẠO PHIẾU XUẤT KIỂM TRA SỐ LƯỢNG CẤP PHÁT PHIẾU YÊU CẦU NẾU số lượng xuất vượt quá số lượng cấp phát thì chặn.

    SELECT @MATERIAL_ID=MATERIAL_ID FROM MW_MAST_BAL mmb WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID

    SELECT @QUANTITY_ALLOCATION=mrd.QUANTITY_ALLOCATION, @ALLOCATED=mrd.ALLOCATED FROM MW_REQ_DT mrd WHERE mrd.REQ_CODE = @p_MW_REQ_ID AND mrd.MATERIAL_ID = @MATERIAL_ID

	SET @INDEX = @INDEX +1

	SET @PRICE_CODE =(SELECT PRICE_CODE FROM MW_MAST_PRICE WHERE PRICE_ID =(SELECT PRICE_ID FROM MW_MAST_BAL WHERE MAST_BAL_ID=@MAST_BAL_ID))

	SET @l_COST_ACC = (SELECT TOP 1 COST_ACC = CASE WHEN MT.GROUP_ID = 'MWG000000016073' and LEN(@COST_ACC) > 0 then @COST_ACC

														WHEN @COST_ACC = null then MT.EXP_ACCTNO

														WHEN @COST_ACC = '' then MT.EXP_ACCTNO

														ELSE MT.EXP_ACCTNO

														END 

						FROM dbo.MW_MAST_BAL A

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

						WHERE A.MAST_BAL_ID = @MAST_BAL_ID)

---- CHẶN SỐ LƯỢNG XUẤT LỚN HƠN PYC ----

DECLARE @CUR_QTY_PYC DECIMAL

IF(@p_MW_REQ_ID IS NOT NULL AND @p_MW_REQ_ID <> '')

BEGIN

	SELECT @CUR_QTY_PYC = MRD.QUANTITY_REQ FROM MW_REQ_DT MRD WHERE MRD.REQ_ID = @p_MW_REQ_ID AND MRD.BRANCH_USE = @UNIT_RECEIVE AND ISNULL(MRD.DEP_USE, '') = ISNULL(@DEP_RECEIVE, '') AND MRD.MATERIAL_ID = @MATERIAL_ID

    IF(@CUR_QTY_PYC < @QTY)

    BEGIN

        ROLLBACK TRANSACTION

        CLOSE XmlData;

        DEALLOCATE XmlData;

        SELECT  '-1' AS Result ,'' OUT_ID ,N'Dòng ' + CONVERT(VARCHAR(5), @INDEX) + N': Số lượng xuất lớn hơn số lượng được yêu cầu.' ErrorDesc

        RETURN '-1'

    END

END

--	IF(@COST_ACC IS NULL OR @COST_ACC = '')

--		BEGIN

--			ROLLBACK TRANSACTION

--			CLOSE XmlData;

--			DEALLOCATE XmlData;

--			SELECT '-1' as Result, '' OUT_ID, N'Danh sách đơn vị/ khách hàng nhận, Dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': Tài khoản chi phí không được để trống' 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;

    INSERT INTO [dbo].[MW_OUT_DT]

    (

        [OUT_DT_ID],

        [OUT_ID],

        [MAST_BAL_ID],

        [CUST_NAME],

        [QTY],

		    [QTY_OLD],

        QTY_REAL_OLD,

        [PRICE],

        [TOTAL_AMT],

        [NOTES],

		    [TO_BRN_ID],

		    [TO_DEPT_ID],[EVENT_NAME],[IS_BCT],[COST_ACC],UNIT_RECEIVE,DEP_RECEIVE,UNIT_CHARGE,DEP_CHARGE,UNIT_PAY,CUSTOMER_NAME,CUSTOMER_ID,CUSTOMER_LOCATION

    )

    SELECT @p_OUT_DT_ID,

           @p_OUT_ID,

           @MAST_BAL_ID,

           @CUST_NAME,

           @QTY,

		       (SELECT mmb.QTY_BALANCE FROM MW_MAST_BAL mmb WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID),

           (SELECT mmb.QTY_REAL FROM MW_MAST_BAL mmb WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID),

           @PRICE,

           @TOTAL_AMT,

           @NOTES,

		       @TO_BRN_ID,

		       @TO_DEPT_ID,@EVENT_NAME,@IS_BCT,@COST_ACC,@UNIT_RECEIVE,@DEP_RECEIVE,@UNIT_CHARGE,@DEP_CHARGE,@UNIT_PAY,@CUSTOMER_NAME,@CUSTOMER_ID,@CUSTOMER_LOCATION

    IF @@Error <> 0

        GOTO ABORT;

    FETCH NEXT FROM XmlData

    INTO @OUT_ID,

         @MAST_BAL_ID,

         @CUST_NAME,

         @QTY,

		     @QTY_OLD,

         @QTY_REAL_OLD,

         @PRICE,

         @TOTAL_AMT,

         @NOTES,

		     @TO_BRN_ID,

		     @TO_DEPT_ID,@EVENT_NAME,@IS_BCT,@COST_ACC,@UNIT_RECEIVE,@DEP_RECEIVE,@UNIT_CHARGE,@DEP_CHARGE,@UNIT_PAY,@CUSTOMER_NAME,@CUSTOMER_ID,@CUSTOMER_LOCATION

END;

CLOSE XmlData;

DEALLOCATE XmlData;

		-- doanptt 07/06/2022: XÓA CÁC PROCESS UPDATE CŨ

		--DELETE dbo.MW_PROCESS WHERE REQ_ID = @OUT_ID AND PROCESS_ID = 'UPDATE_HC'

		INSERT INTO dbo.MW_PROCESS

		(

			REQ_ID,

			PROCESS_ID,

			CHECKER_ID,

			APPROVE_DT,

			PROCESS_DESC,NOTES

		)

		VALUES

		(   @OUT_ID,        -- REQ_ID - varchar(15)

			'UPD_HC',        -- PROCESS_ID - varchar(10)

			@p_MAKER_ID,        -- CHECKER_ID - varchar(15)

			GETDATE(), -- APPROVE_DT - datetime

			N'Cập nhật phiếu xuất vật liệu thành công' ,

			N'Cập nhật phiếu xuất vật liệu'      -- PROCESS_DESC - nvarchar(1000)

		)

        ---- CHECK BƯỚC QUY TRÌNH

--         INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, CHECKER_ID)

--		 VALUES(@p_OUT_ID,'ADDNEW','C',@p_BRN_ID,'',@p_DEPT_ID, @p_MAKER_ID)

---

UPDATE MW_OUT

SET TOTAL_AMT = (SELECT SUM(TOTAL_AMT) FROM MW_OUT_DT WHERE OUT_ID =@p_OUT_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;