DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

ALTER PROCEDURE [dbo].[MW_OUT_Ins] @p_TRN_Date DATE=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 DATETIME=NULL, @p_CHECKER_ID VARCHAR(15) =NULL, @p_APPROVE_DT DATETIME=NULL,

    @p_KT_AUTH_STATUS VARCHAR(1) =NULL, @p_KT_MAKER_ID VARCHAR(15) =NULL, @p_KT_CREATE_DT DATETIME=NULL, @p_KT_CHECKER_ID VARCHAR(15) =NULL,

    @p_KT_APPROVE_DT DATETIME=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

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

	@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, @PRICE_ID VARCHAR(25)

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

    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),COST_ACC VARCHAR(50))

OPEN XmlData;

BEGIN TRANSACTION;

DECLARE @p_OUT_ID VARCHAR(15);

EXEC [MW_OUT_CODE_Gen] @p_BRANCH_CREATE,@p_WARE_HOUSE, @p_OUT_CODE OUT;

EXEC SYS_CodeMasters_Gen 'MW_OUT', @p_OUT_ID OUT;

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

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], [KT_AUTH_STATUS], [KT_MAKER_ID], [KT_CREATE_DT], [KT_CHECKER_ID], [KT_APPROVE_DT], [RECORD_STATUS], [CORE_NOTE],

[BRANCH_CREATE],[WARE_HOUSE],[OUT_CODE])

SELECT @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, 'U',

    @p_MAKER_ID, CONVERT(DATETIME, GETDATE(), 0), NULL, NULL, 'U', NULL, NULL, NULL, NULL, '1', @p_CORE_NOTE, @p_BRANCH_CREATE,@p_WARE_HOUSE,@p_OUT_CODE;

IF @@error<>0 GOTO ABORT;

--Insert XmlData

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 TOP 1 PRICE_CODE FROM MW_MAST_PRICE WHERE PRICE_ID =(SELECT PRICE_ID FROM MW_MAST_BAL WHERE MAST_BAL_ID=@MAST_BAL_ID))

	--LUCTV 12 01 2021 BO SUNG RANG BUOC NEU CHUA DUOC XAC NHAN LO VAT LIEU THI KHONG CHO PHÉP ĐIỀU CHUYỂN VẬT LIỆU

	SET @PRICE_ID = (SELECT TOP 1 PRICE_ID FROM MW_MAST_BAL WHERE MAST_BAL_ID=@MAST_BAL_ID)

	IF(EXISTS(SELECT * FROM MW_TRANSFER_DT WHERE MAST_BAL_ID IN (SELECT MAST_BAL_ID FROM MW_MAST_BAL WHERE PRICE_ID =@PRICE_ID) AND TO_BRN_ID =@p_BRANCH_CREATE AND TRANSFER_ID IN (SELECT TRANSFER_ID FROM MW_TRANSFER WHERE KT_AUTH_STATUS ='A')))

	BEGIN

		IF(NOT EXISTS(SELECT * FROM MW_TRANSFER_CONF WHERE BRANCH_RECIVE =@p_BRANCH_CREATE AND TRANSFER_ID IN 

		(SELECT TRANSFER_ID FROM MW_TRANSFER_DT WHERE MAST_BAL_ID IN  (SELECT MAST_BAL_ID FROM MW_MAST_BAL WHERE PRICE_ID =@PRICE_ID) AND TO_BRN_ID =@p_BRANCH_CREATE) ))

		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' chưa được xác nhận điều chuyển. Vui lòng xác nhận điều chuyển trước khi sử dụng' ErrorDesc;

			RETURN '-1';

			END

	END

	--LUCTV: 30-11-2018 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 IN (SELECT OUT_ID FROM MW_OUT WHERE KT_AUTH_STATUS<>'A' AND RECORD_STATUS='1' AND OUT_ID <> @p_OUT_ID)))

	--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ã là '+@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

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

    --INSERT INTO [dbo].[MW_OUT_DT] ([OUT_DT_ID], [OUT_ID], [MAST_BAL_ID], [FR_BRN_ID], [FR_DEPT_ID], [FR_PRICE_ID], [TO_BRN_ID], [TO_DEPT_ID], [QTY], [QTY_OLD], [PRICE], [TOTAL_AMT], [NOTES])

    --VALUES( @p_OUT_DT_ID, @p_OUT_ID, @MAST_BAL_ID, @FR_BRN_ID, @FR_DEPT_ID, @FR_PRICE_ID, @TO_BRN_ID, @TO_DEPT_ID, @QTY, @QTY_OLD, @PRICE, @TOTAL_AMT, @NOTES)

    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;