DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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 INT = 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(15) = NULL,

    @p_CREATE_DT VARCHAR(25) = NULL,

    @p_CHECKER_ID VARCHAR(15) = NULL,

    @p_APPROVE_DT VARCHAR(25) = NULL,

    @p_KT_AUTH_STATUS VARCHAR(1) = NULL,

    @p_KT_MAKER_ID VARCHAR(15) = NULL,

    @p_KT_CREATE_DT VARCHAR(25) = NULL,

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

AS

DECLARE

    --DVSD

    @OUT_ID VARCHAR(15) = NULL,

    @MAST_BAL_ID VARCHAR(15) = NULL,

    @CUST_NAME NVARCHAR(200) = NULL,

    @QTY INT = NULL,

	@QTY_OLD INT = 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

DECLARE @INDEX INT =0

DECLARE @PRICE_CODE VARCHAR(30)

DECLARE @hdoc INT;

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

        --FR_BRN_ID varchar(15) ,

        --FR_DEPT_ID varchar(15) ,

        --FR_PRICE_ID varchar(15) ,

        --TO_BRN_ID varchar(15) ,

        --TO_DEPT_ID varchar(15) ,

        CUST_NAME NVARCHAR(200),

        QTY INT,

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

		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)

    );

OPEN XmlData;

BEGIN TRANSACTION;

UPDATE [dbo].[MW_OUT]

SET 

    [QTY] = @p_QTY,

	AUTH_STATUS='U',

    [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

WHERE [OUT_ID] = @p_OUT_ID;

IF @@Error <> 0

    GOTO ABORT;

--Insert XmlData

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,@PRICE,@TOTAL_AMT, @NOTES,@TO_BRN_ID, @TO_DEPT_ID, @EVENT_NAME,@IS_BCT, @COST_ACC

WHILE @@FETCH_STATUS = 0

BEGIN

		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 = NULL OR @COST_ACC = '')

		BEGIN

			ROLLBACK TRANSACTION

			CLOSE XmlData;

			DEALLOCATE XmlData;

			SELECT '-1' as Result, '' REQ_PAY_ID, N'Danh sách lô vật liệu, Dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': Tài khoản chi phí không được để trống' ErrorDesc

			RETURN '-1'

		END

	--LUCTV: 03-1-2019 BO SUNG HAM KIEM TRA LO VAT LIEU NEU DANG DUOC XUAT SU DUNG THI PHAI CHO DUYET XONG MOI TAO MOI XUAT SD

	--IF(EXISTS(SELECT * FROM MW_OUT_DT  WHERE MAST_BAL_ID =@MAST_BAL_ID AND OUT_ID<>@p_OUT_ID AND OUT_ID IN (SELECT OUT_ID FROM MW_OUT WHERE KT_AUTH_STATUS<>'A' AND RECORD_STATUS='1')))

	--BEGIN

	--	CLOSE XmlData;

	--	DEALLOCATE XmlData;

	--	ROLLBACK TRANSACTION;

	--	SELECT '-1' AS Result, '' OUT_ID, N'Dòng '+ CONVERT(VARCHAR(15),@INDEX) +N': Lô vật liệu có mã '+@PRICE_CODE +N' đang được xuất sử dụng. Vui lòng duyệt thông tin trước khi tạo đợt mới' ErrorDesc;

	--	RETURN '-1';

	--END

	----LUCTV: 08-03-2019 BO SUNG HAM KIEM TRA LO VAT LIEU NEU DANG DUOC DIEU CHUYEN THI PHAI CHO DUYET XONG MOI TAO MOI XUAT SD

	--IF(EXISTS(SELECT * FROM MW_TRANSFER_DT  WHERE MAST_BAL_ID =@MAST_BAL_ID AND TRANSFER_ID IN (SELECT TRANSFER_ID FROM MW_TRANSFER WHERE KT_AUTH_STATUS<>'A' AND RECORD_STATUS='1')))

	--BEGIN

	--	CLOSE XmlData;

	--	DEALLOCATE XmlData;

	--	ROLLBACK TRANSACTION;

	--	SELECT '-1' AS Result, '' OUT_ID, N'Dòng '+ CONVERT(VARCHAR(15),@INDEX) +N': Lô vật liệu có mã '+@PRICE_CODE +N' đang được điều chuyển. Vui lòng duyệt thông tin trước khi tạo đợt mới' ErrorDesc;

	--	RETURN '-1';

	--END

	----LUCTV: 08-03-2019 BO SUNG HAM KIEM TRA LO VAT LIEU NEU DANG DUOC THANH LY THI PHAI CHO DUYET XONG MOI TAO MOI XUAT SD

	--IF(EXISTS(SELECT * FROM MW_LIQ_DT  WHERE MAST_BAL_ID =@MAST_BAL_ID AND LIQ_ID IN (SELECT LIQ_ID FROM MW_LIQ_MASTER WHERE KT_AUTH_STATUS<>'A' AND RECORD_STATUS='1')))

	--BEGIN

	--	CLOSE XmlData;

	--	DEALLOCATE XmlData;

	--	ROLLBACK TRANSACTION;

	--	SELECT '-1' AS Result, '' OUT_ID, N'Dòng '+ CONVERT(VARCHAR(15),@INDEX) +N': Lô vật liệu có mã '+@PRICE_CODE +N' đang được thanh lý. Vui lòng duyệt thông tin trước khi tạo đợt mới' ErrorDesc;

	--	RETURN '-1';

	--END

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

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

        [PRICE],

        [TOTAL_AMT],

        [NOTES],

		[TO_BRN_ID],

		[TO_DEPT_ID],[EVENT_NAME],[IS_BCT],[COST_ACC]

    )

    SELECT @p_OUT_DT_ID,

           @p_OUT_ID,

           @MAST_BAL_ID,

           @CUST_NAME,

           @QTY,

		   @QTY_OLD,

           @PRICE,

           @TOTAL_AMT,

           @NOTES,

		   @TO_BRN_ID,

		   @TO_DEPT_ID,@EVENT_NAME,@IS_BCT,@COST_ACC

    IF @@Error <> 0

        GOTO ABORT;

    FETCH NEXT FROM XmlData

    INTO @OUT_ID,

         @MAST_BAL_ID,

         @CUST_NAME,

         @QTY,

		 @QTY_OLD,

         @PRICE,

         @TOTAL_AMT,

         @NOTES,

		 @TO_BRN_ID,

		 @TO_DEPT_ID,@EVENT_NAME,@IS_BCT,@COST_ACC

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

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;