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

    @p_TRN_Date datetime = NULL,

    @p_GROUP_ID varchar(15) = NULL,

    @p_MATERIAL_NAME nvarchar(1000) = NULL,

    @p_MATERIAL_DESC nvarchar(MAX) = NULL,

    @p_MAKER_ID_KT varchar(15) = NULL,

    @p_MATERIAL_ID varchar(15) = NULL,

    @p_CHECKER_ID_KT varchar(15) = NULL,

    @p_BRANCH_ID varchar(15) = NULL,

    @p_DEPT_ID varchar(15) = NULL,

    @p_CREATE_DT_KT datetime = NULL,

    @p_AUTH_STATUS_KT varchar(1) = NULL,

    @p_QTY int = NULL,

    @p_PO_CODE nvarchar(200) = NULL,

    @p_NOTES nvarchar(1000) = NULL,

    @p_RECORD_STATUS varchar(1) = 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_APPROVE_DT_KT datetime = NULL,

    @p_TRN_TIME varchar(50) = NULL,

    @p_BUY_PRICE NUMERIC(18, 0) = NULL,

    @p_TOTAL_AMT numeric(18, 2) = NULL,

    @p_VAT decimal(18, 2) = NULL,

    @p_PRICE_VAT decimal(18, 0) = NULL,

    @p_IS_PROMO varchar(1) = NULL,

	@p_BRANCH_CREATE  varchar(15)  = NULL,

	@p_INVOICE_NO  nvarchar(200)	   = NULL,

	@p_INVOICE_DT  datetime		   = NULL,

	@p_DIVISION_ID  varchar(15)	   = NULL,

	@p_ENTRY_BOOKED  varchar(1)	   = NULL,

	@p_CORE_NOTE nvarchar(500)	   = NULL,

	@p_IN_CODE nvarchar(200)	   = NULL,

	@p_WARE_HOUSE VARCHAR(15) = NULL,

	@p_XmlData XML = NULL

AS

		DECLARE @sErrorCode VARCHAR(20) = ''

		DECLARE @l_IN_ID VARCHAR(15)

		DECLARE @l_IN_MASTER_ID VARCHAR(15)

		-- LAY GROUP LEVEL - NẾU LÀ 1 THÌ CHẶN

		DECLARE @GROUP_LEVEL VARCHAR(15)

		--- KHAI BAO BIEN CURSOR

		DECLARE @IN_ID VARCHAR(15) = NULL,

				@GROUP_ID VARCHAR(15) = NULL,

				@MATERIAL_ID VARCHAR(15) = NULL,

				@MATERIAL_NAME NVARCHAR(200) = NULL,

				@QTY INT = NULL,

				@VAT NUMERIC(18, 0) = NULL,

				@TOTAL_AMT NUMERIC(18, 2) = NULL,

				@NOTES NVARCHAR(1000) = NULL

				DECLARE @hdoc INT;

		---

		EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XmlData;

		DECLARE XmlData CURSOR FOR

		SELECT *

		FROM

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

			WITH (	IN_ID VARCHAR(15),

					GROUP_ID VARCHAR(15),

					MATERIAL_ID VARCHAR(15),

					MATERIAL_NAME NVARCHAR(200),

					QTY INT,

					VAT NUMERIC(18, 0),

					TOTAL_AMT NUMERIC(18, 2),

					NOTES NVARCHAR(1000));

		OPEN XmlData;

		BEGIN TRANSACTION

		-- INSERT MASTER

		EXEC [MW_IN_CODE_Gen] @p_BRANCH_CREATE,@p_WARE_HOUSE, @p_IN_CODE OUT

		EXEC SYS_CodeMasters_Gen 'MW_IN_MASTER', @l_IN_MASTER_ID out

		IF @l_IN_MASTER_ID='' OR @l_IN_MASTER_ID IS NULL GOTO ABORT

		INSERT INTO dbo.MW_IN_MASTER

		(

		    IN_ID,

		    TRN_Date,

		    GROUP_ID,

		    MATERIAL_NAME,

		    MATERIAL_DESC,

		    MAKER_ID_KT,

		    MATERIAL_ID,

		    CHECKER_ID_KT,

		    BRANCH_ID,

		    DEPT_ID,

		    CREATE_DT_KT,

		    AUTH_STATUS_KT,

		    QTY,

		    PO_CODE,

		    NOTES,

		    RECORD_STATUS,

		    AUTH_STATUS,

		    MAKER_ID,

		    CREATE_DT,

		    CHECKER_ID,

		    APPROVE_DT,

		    APPROVE_DT_KT,

		    TRN_TIME,

		    BUY_PRICE,

		    TOTAL_AMT,

		    VAT,

		    PRICE_VAT,

		    IS_PROMO,

			BRANCH_CREATE,

			INVOICE_NO,

			INVOICE_DT,

			DIVISION_ID,

			ENTRY_BOOKED,

			CORE_NOTE,IN_CODE,WARE_HOUSE

		)

		VALUES

		(   

			@l_IN_MASTER_ID,

		    @p_TRN_Date,

		    @p_GROUP_ID,

		    @p_MATERIAL_NAME,

		    @p_MATERIAL_DESC,

		    @p_MAKER_ID_KT,

		    @p_MATERIAL_ID,

		    @p_CHECKER_ID_KT,

		    @p_BRANCH_ID,

		    @p_DEPT_ID,

			CONVERT(DATETIME,@p_CREATE_DT_KT, 103)

		    ,

		    @p_AUTH_STATUS_KT,

		    @p_QTY,

		    @p_PO_CODE,

		    @p_NOTES,

		    @p_RECORD_STATUS,

		    @p_AUTH_STATUS,

		    @p_MAKER_ID,

		    CONVERT(DATETIME,@p_CREATE_DT, 103),

		    @p_CHECKER_ID,

		    CONVERT(DATETIME,@p_APPROVE_DT, 103),

		    CONVERT(DATETIME,@p_APPROVE_DT_KT, 103),

		    @p_TRN_TIME,

		    @p_BUY_PRICE,

		    @p_TOTAL_AMT,

		    @p_VAT,

		    @p_PRICE_VAT,

		    @p_IS_PROMO,

			@p_BRANCH_CREATE,

			@p_INVOICE_NO,

			@p_INVOICE_DT,

			@p_DIVISION_ID,

			@p_ENTRY_BOOKED,

			@p_CORE_NOTE,@p_IN_CODE,@p_WARE_HOUSE

		    )

		-- INSERT DETAIL

		FETCH NEXT FROM XmlData

		INTO @IN_ID,@GROUP_ID ,@MATERIAL_ID ,@MATERIAL_NAME,@QTY,@VAT,@TOTAL_AMT,@NOTES

		WHILE @@FETCH_STATUS = 0

		BEGIN

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

		EXEC SYS_CodeMasters_Gen 'MW_IN', @IN_ID out

		IF @IN_ID='' OR @IN_ID IS NULL GOTO ABORT

		--SET @p_TOTAL_AMT = @p_BUY_PRICE

		--SET @p_BUY_PRICE = ROUND(@p_TOTAL_AMT/@p_QTY,0)

		SET @GROUP_LEVEL =(SELECT TOP 1 GROUP_LEVEL FROM MW_GROUP WHERE GROUP_ID =@GROUP_ID)

		IF(@GROUP_LEVEL='1')

		BEGIN 

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' IN_ID, N'Bạn không thể nhập kho vật liệu với mã nhóm cấp 1 (THẺ hoặc HC). Vui lòng chọn các nhóm vật liệu khác hai nhóm này' ErrorDesc

			RETURN '-1'	

		END

		INSERT INTO dbo.MW_IN

		(

		    IN_MASTER_ID,IN_ID,TRN_Date, GROUP_ID,MATERIAL_NAME,

		    MATERIAL_DESC,MAKER_ID_KT,MATERIAL_ID,CHECKER_ID_KT, BRANCH_ID,

		    DEPT_ID,CREATE_DT_KT,AUTH_STATUS_KT,QTY,PO_CODE,NOTES,RECORD_STATUS,

		    AUTH_STATUS,MAKER_ID,CREATE_DT,CHECKER_ID,APPROVE_DT,APPROVE_DT_KT,TRN_TIME,

		    BUY_PRICE,TOTAL_AMT,VAT,PRICE_VAT,IS_PROMO,BRANCH_CREATE,INVOICE_NO,

			INVOICE_DT,DIVISION_ID,ENTRY_BOOKED,CORE_NOTE

		)

		VALUES

		(   @l_IN_MASTER_ID,

			@IN_ID,

		    @p_TRN_Date,

		    @GROUP_ID,@MATERIAL_NAME, @NOTES, @p_MAKER_ID_KT, @MATERIAL_ID,@p_CHECKER_ID_KT, @p_BRANCH_ID,@p_DEPT_ID,

			CONVERT(DATETIME,@p_CREATE_DT_KT, 103) , @p_AUTH_STATUS_KT, @QTY,@p_PO_CODE, @NOTES,@p_RECORD_STATUS,@p_AUTH_STATUS, @p_MAKER_ID,

		    CONVERT(DATETIME,@p_CREATE_DT, 103),@p_CHECKER_ID,

		    CONVERT(DATETIME,@p_APPROVE_DT, 103),

		    CONVERT(DATETIME,@p_APPROVE_DT_KT, 103),@p_TRN_TIME, @p_BUY_PRICE,

		    @TOTAL_AMT, @VAT,@TOTAL_AMT*@VAT/100,

		    @p_IS_PROMO,@p_BRANCH_CREATE,@p_INVOICE_NO,

			@p_INVOICE_DT,@p_DIVISION_ID,@p_ENTRY_BOOKED,@p_CORE_NOTE

		    )

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

		IF @@Error <> 0

        GOTO ABORT;

		FETCH NEXT FROM XmlData

		INTO @IN_ID,@GROUP_ID ,@MATERIAL_ID ,@MATERIAL_NAME,@QTY,@VAT,@TOTAL_AMT,@NOTES

		END

		CLOSE XmlData;

		DEALLOCATE XmlData;

COMMIT TRANSACTION

SELECT '0' as Result, @l_IN_MASTER_ID  IN_ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		 CLOSE XmlData;

		 DEALLOCATE XmlData;

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' IN_ID, '' ErrorDesc

		RETURN '-1'

End