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

ALTER PROCEDURE [dbo].[MW_IN_KT_Upd]

	@p_IN_ID varchar(15),

    @p_TRN_Date VARCHAR(25) = 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 VARCHAR(25) = NULL,

    @p_AUTH_STATUS_KT varchar(1) = NULL,

    @p_QTY DECIMAL(18, 2) = 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 VARCHAR(25) = NULL,

    @p_CHECKER_ID varchar(15) = NULL,

    @p_APPROVE_DT VARCHAR(25) = NULL,

    @p_APPROVE_DT_KT VARCHAR(25) = NULL,

    @p_TRN_TIME varchar(50) = NULL,

    @p_BUY_PRICE DECIMAL(18, 2) = NULL,

    @p_TOTAL_AMT DECIMAL(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		VARCHAR(25)		   = NULL,

	@p_DIVISION_ID			VARCHAR(15)	   = NULL,

	@p_ENTRY_BOOKED		 varchar(1)	   = NULL,

	@p_CORE_NOTE nvarchar(500)	   = NULL,

	@p_IN_CODE NVARCHAR(100) = NULL,

	@p_WARE_HOUSE VARCHAR(15) = NULL,

	@p_PO_ID varchar(20) = NULL,

	@p_XmlData XML = NULL

AS

		DECLARE @sErrorCode VARCHAR(20) = ''

		DECLARE @l_IN_ID VARCHAR(15)

		DECLARE @l_IN_DT_ID 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 DECIMAL(18, 2) = NULL,

				@VAT DECIMAL(18, 2) = NULL,

				@TOTAL_AMT DECIMAL(18, 2) = NULL,

				@NOTES NVARCHAR(1000) = NULL,

				@IS_PROMO VARCHAR(1) = NULL,

				@PRICE_VAT DECIMAL(18, 2) = NULL,

				@MATERIAL_ACCTNO VARCHAR(25)

		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 DECIMAL(18, 2),

					VAT DECIMAL(18, 2),

					TOTAL_AMT DECIMAL(18, 2),

					NOTES NVARCHAR(1000),

					IS_PROMO VARCHAR(1),

					PRICE_VAT DECIMAL(18, 2),

					MATERIAL_ACCTNO VARCHAR(25));

		OPEN XmlData;

		BEGIN TRANSACTION

		--LUCTV: NGAY 07 -06 2019. BO SUNG RANG BUOC NEU DANG TRA VE CHO HANH CHINH THI KHONG DUOC PHEP CAP NHAT THONG TIN

		DELETE FROM MW_IN WHERE IN_MASTER_ID =@p_IN_ID

		IF( (SELECT AUTH_STATUS FROM MW_IN_MASTER WHERE IN_ID = @p_IN_ID)<>'A')

		BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' as Result, N'Thông tin nhập mới vật liệu đang bị trả về bộ phận hành chính. Vui lòng chờ bộ phận hành chính kiểm duyệt' ErrorDesc

				RETURN '-1'

		END

		-- UPDATE MASTER

		UPDATE [dbo].[MW_IN_MASTER]

		SET 

		--[TOTAL_AMT] = @p_BUY_PRICE,

		--[BUY_PRICE] = @p_BUY_PRICE/@p_QTY,

		--[TRN_Date] = @p_TRN_Date,

		--[GROUP_ID] = @p_GROUP_ID,

		--[MATERIAL_NAME] = @p_MATERIAL_NAME,

		[MATERIAL_DESC] = @p_MATERIAL_DESC,

		[MAKER_ID_KT] = @p_MAKER_ID_KT,

		--[MATERIAL_ID] = @p_MATERIAL_ID,

		[CHECKER_ID_KT] = @p_CHECKER_ID_KT,

		[BRANCH_ID] = @p_BRANCH_ID,

		[DEPT_ID] = @p_DEPT_ID,

		[CREATE_DT_KT] = CONVERT(DATETIME,@p_CREATE_DT_KT, 103) ,

		[AUTH_STATUS_KT] = 'U',

		--[QTY] = @p_QTY,

		[PO_CODE] = @p_PO_CODE,

		[PO_ID] = @p_PO_ID,

		[NOTES] = @p_NOTES,

		[RECORD_STATUS] = @p_RECORD_STATUS,

		--[AUTH_STATUS] = @p_AUTH_STATUS,

		--[MAKER_ID] = @p_MAKER_ID,

		--[CREATE_DT] = @p_CREATE_DT,

		--[CHECKER_ID] = @p_CHECKER_ID,

		--[APPROVE_DT] = @p_APPROVE_DT,

		--[APPROVE_DT_KT] = @p_APPROVE_DT_KT,

		--[TRN_TIME] = @p_TRN_TIME,

		--[VAT] = @p_VAT,

		--[PRICE_VAT] = @p_PRICE_VAT,

		[IS_PROMO] = @p_IS_PROMO,

		[BRANCH_CREATE]  = @p_BRANCH_CREATE ,

		[INVOICE_NO]		= @p_INVOICE_NO	   ,

		[INVOICE_DT]		= CONVERT(DATETIME,@p_INVOICE_DT, 103) 	   ,

		[DIVISION_ID]		= @p_DIVISION_ID	,

		[ENTRY_BOOKED]	= @p_ENTRY_BOOKED,

		[CORE_NOTE] =@p_CORE_NOTE,WARE_HOUSE =@p_WARE_HOUSE

		WHERE IN_ID = @p_IN_ID

		-----------

		UPDATE MW_IN_MASTER

		SET @p_PO_CODE =@p_PO_CODE, [MAKER_ID] = @p_MAKER_ID,INVOICE_NO= @p_INVOICE_NO,[NOTES] = @p_NOTES,

		[INVOICE_DT]=  CONVERT(DATETIME,@p_INVOICE_DT, 103)	, @p_PO_ID = @p_PO_ID

		WHERE IN_ID =@p_IN_ID

		-- INSERT DETAIL

		FETCH NEXT FROM XmlData

		INTO @IN_ID,@GROUP_ID ,@MATERIAL_ID ,@MATERIAL_NAME,@QTY,@VAT,@TOTAL_AMT,@NOTES,@IS_PROMO,@PRICE_VAT, @MATERIAL_ACCTNO

		WHILE @@FETCH_STATUS = 0

		BEGIN

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

		IF(@PRICE_VAT IS NULL)

		BEGIN

		SET @PRICE_VAT = 0;

		END

		EXEC SYS_CodeMasters_Gen 'MW_IN', @l_IN_DT_ID out

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

		--SET @p_TOTAL_AMT = @p_BUY_PRICE

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

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

		)

		VALUES

		(   @p_IN_ID,

			@l_IN_DT_ID,

		    CONVERT(DATETIME,@p_TRN_Date, 103),

		    @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, ROUND(@TOTAL_AMT/@QTY,0),

		    @TOTAL_AMT, @VAT,@PRICE_VAT,

		    @IS_PROMO,@p_BRANCH_CREATE,@p_INVOICE_NO,

			CONVERT(DATETIME,@p_INVOICE_DT, 103),@p_DIVISION_ID,@p_ENTRY_BOOKED,@p_CORE_NOTE, @p_PO_ID, @MATERIAL_ACCTNO

		    )

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

		IF @@Error <> 0

        GOTO ABORT;

		FETCH NEXT FROM XmlData

		INTO @IN_ID,@GROUP_ID ,@MATERIAL_ID ,@MATERIAL_NAME,@QTY,@VAT,@TOTAL_AMT,@NOTES,@IS_PROMO,@PRICE_VAT, @MATERIAL_ACCTNO

		END

		CLOSE XmlData;

		DEALLOCATE XmlData;

		-- doanptt 07/06/2022: Thêm process

		DELETE dbo.PL_PROCESS WHERE REQ_ID = @p_IN_ID AND PROCESS_ID = 'UPDATE_KT'

		INSERT INTO dbo.PL_PROCESS

		(

			REQ_ID,

			PROCESS_ID,

			CHECKER_ID,

			APPROVE_DT,

			PROCESS_DESC,NOTES

		)

		VALUES

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

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

			@p_MAKER_ID,        -- CHECKER_ID - varchar(15)

			GETDATE(), -- APPROVE_DT - datetime

			N'Cập nhật phiếu nhập mới vật liệu kế toán thành công' ,

			N'Cập nhật phiếu nhập mới vật liệu kế toán'      -- PROCESS_DESC - nvarchar(1000)

		)

COMMIT TRANSACTION

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

RETURN '0'

ABORT:

BEGIN

		 CLOSE XmlData;

		 DEALLOCATE XmlData;

		ROLLBACK TRANSACTION

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

		RETURN '-1'

End