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

ALTER PROCEDURE [dbo].[TR_REQ_PAY_INVOICE_VAT_Upd]

@p_REQ_PAY_ID	VARCHAR(15)= NULL,

@p_MAKER_ID	VARCHAR(15)	= NULL,

@p_MAKER_ID_KT	VARCHAR(15)	= NULL,

@p_REQ_TYPE_CURRENCY NVARCHAR(50) = 'VND',

@p_RATE	DECIMAL(18,2) = '1',

@p_XMP_TEMP_VAT_INVOICE XML = NULL

AS

	DECLARE @INDEX_VAT INT =0

	DECLARE @VAT DECIMAL(18, 0) =NULL, @TRANS_NO NVARCHAR(50)=NULL, @TRANS_DT VARCHAR(20)=NULL,@INVOICE_SIGN NVARCHAR(50) = NULL,@INVOICE_NO_SIGN NVARCHAR(50)=NULL,

		@INVOICE_NO NVARCHAR(50) = NULL,@INVOICE_DT VARCHAR(20) = NULL,@SELLER NVARCHAR(250) = NULL,@TAX_NO NVARCHAR(15) = NULL,@GOODS_NAME NVARCHAR(250) = NULL,

		@PRICE DECIMAL(18, 0) = NULL,@TAX DECIMAL(18, 0) = NULL,@NOTES NVARCHAR(MAX) = NULL,@VAT_RATE DECIMAL(18,2),

		@ACC_NO VARCHAR(25),@ACC_NAME NVARCHAR(250),@ISSUED_BY NVARCHAR(250),@ISSUED_DT VARCHAR(20),@RATE DECIMAL(18,2),@PRICE_KT DECIMAL(18,2) =0,@TOTAL_AMT_KT DECIMAL(18,2) =0,@VAT_KT DECIMAL(18,2) =0,

		@TYPE_VAT VARCHAR(15),@TYPE_FUNC VARCHAR(15), @IS_TRANSFER_EXTERNAL VARCHAR(15), @CURRENCY VARCHAR(15)

	DECLARE @hdocVATInvoice INT;

	EXEC sp_xml_preparedocument @hdocVATInvoice OUTPUT, @p_XMP_TEMP_VAT_INVOICE;

BEGIN TRANSACTION

-- BEGIN HOA DON CO VAT

	IF(@p_XMP_TEMP_VAT_INVOICE IS NOT NULL)

	BEGIN

		-- hoa don co VAT

		DECLARE XmlVATInvoice CURSOR FOR

		SELECT * FROM OPENXML(@hdocVATInvoice, '/Root/XmlVATInvoice', 2)

		WITH(TRANS_NO nvarchar(50),TRANS_DT VARCHAR(20),INVOICE_SIGN nvarchar(50),INVOICE_NO_SIGN nvarchar(50),

				INVOICE_NO nvarchar(50),INVOICE_DT VARCHAR(20) ,SELLER nvarchar(250),TAX_NO nvarchar(15),GOODS_NAME nvarchar(250) ,

				PRICE decimal(18,0),TAX decimal(18, 0),VAT decimal(18,0),NOTE NVARCHAR(MAX),VAT_RATE decimal(18,2),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),PRICE_KT DECIMAL(18,0),VAT_KT DECIMAL(18,2),TOTAL_AMT_KT DECIMAL(18,2),TYPE_VAT VARCHAR(15), TYPE_FUNC VARCHAR(15))

		OPEN XmlVATInvoice

		-- CAP NHAT HOA DON CO VAT

		FETCH NEXT FROM XmlVATInvoice  INTO @TRANS_NO , @TRANS_DT  ,@INVOICE_SIGN ,@INVOICE_NO_SIGN,@INVOICE_NO ,@INVOICE_DT ,@SELLER ,

		@TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES,@VAT_RATE,@CURRENCY,@RATE,@PRICE_KT ,@VAT_KT,@TOTAL_AMT_KT,@TYPE_VAT,@TYPE_FUNC

		WHILE @@fetch_status=0 BEGIN

			SET @INDEX_VAT = @INDEX_VAT+1

			--IF(@TYPE_FUNC ='KT')

			--BEGIN

				IF(EXISTS(SELECT REQ_PAYDT_ID FROM TR_REQ_PAY_INVOICE 

									WHERE INVOICE_NO =@INVOICE_NO 

										AND INVOICE_NO_SIGN = @INVOICE_NO_SIGN 

										AND INVOICE_SIGN =@INVOICE_SIGN 

										AND TAX_NO =@TAX_NO 

										AND TAX =@TAX 

										AND TYPE_FUNC = 'KT' 

										AND AUTH_STATUS <>'D' 

										AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS NOT IN ('E','D') AND MAKER_ID NOT IN (SELECT TLNANME FROM TL_USER WHERE RoleName ='DISABLE' OR AUTH_STATUS ='U'))

							)

					)

				BEGIN

					ROLLBACK TRANSACTION

					CLOSE XmlVATInvoice;

					DEALLOCATE XmlVATInvoice;

					SELECT '-1' as Result, '' REQ_PAY_ID,N'Thông tin hóa đơn đính kèm có VAT, dòng ' + CONVERT(VARCHAR(5),@INDEX_VAT)+N': Số hóa đơn '+ @INVOICE_NO +N' đã tồn tại trong hệ thống' ErrorDesc

					RETURN '-1'

				END	

			--END

			--doanptt 20/04/2022

			/*

			IF(NOT EXISTS(SELECT * FROM OPENXML(@hdoc, '/Root/XmlDataEntries', 2) WITH(DR_CR varchar(20),ACCT VARCHAR(50), ACCT_NAME VARCHAR(500), AMT decimal(18,2))  WHERE ACCT = '353200002'))

			BEGIN

				ROLLBACK TRANSACTION

				CLOSE XmlVATInvoice;	

				DEALLOCATE XmlVATInvoice;

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới hóa đơn đính kèm có VAT, nhưng lưới hạch toán không có tài khoản nợ 353200002. Vui lòng thêm tài khoản nợ 353200002 trước khi gửi phê duyệt'  ErrorDesc

				RETURN '-1'

			END

			*/

			DECLARE @p_REQ_INV_VAT_ID VARCHAR(15);

			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_INVOICE', @p_REQ_INV_VAT_ID OUT;

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

			INSERT INTO TR_REQ_PAY_INVOICE(REQ_PAYDT_ID,REQ_PAY_ID,TRANS_NO,TRANS_DT,INVOICE_SIGN,INVOICE_NO,INVOICE_DT,SELLER,TAX_NO,GOODS_NAME,PRICE,TAX,VAT,NOTE, MAKER_ID,CREATE_DT,EDITOR_ID,

			AUTH_STATUS,CHECKER_ID,APPROVE_DT, CREATE_DT_KT,MAKER_ID_KT,AUTH_STATUS_KT,CHECKER_ID_KT,APPROVE_DT_KT,RECORD_STATUS,INVOICE_NO_SIGN,VAT_RATE,CURRENCY,RATE,PRICE_KT ,VAT_KT,

			TOTAL_AMT_KT,TYPE_VAT,TYPE_FUNC) 

			VALUES (@p_REQ_INV_VAT_ID,@p_REQ_PAY_ID , @TRANS_NO , CONVERT(DATE,@TRANS_DT,103)  ,@INVOICE_SIGN ,@INVOICE_NO ,CONVERT(DATE,@INVOICE_DT,103) ,@SELLER ,@TAX_NO ,@GOODS_NAME ,@PRICE ,

			@TAX,@VAT,@NOTES, @p_MAKER_ID,GETDATE(),NULL,'U',NULL,NULL,GETDATE(),@p_MAKER_ID_KT,'U',NULL,NULL,'1',@INVOICE_NO_SIGN,@VAT_RATE, @p_REQ_TYPE_CURRENCY, @p_RATE,@PRICE_KT ,@VAT_KT,@TOTAL_AMT_KT,

			@TYPE_VAT,'KT')

		IF @@error<>0 GOTO ABORT;

		FETCH NEXT FROM XmlVATInvoice 

		INTO @TRANS_NO , @TRANS_DT,@INVOICE_SIGN,@INVOICE_NO_SIGN,@INVOICE_NO ,@INVOICE_DT ,@SELLER ,@TAX_NO ,@GOODS_NAME ,@PRICE ,

		@TAX,@VAT,@NOTES,@VAT_RATE,@CURRENCY,@RATE,@PRICE_KT ,@VAT_KT,@TOTAL_AMT_KT,@TYPE_VAT,@TYPE_FUNC

		END;

		CLOSE XmlVATInvoice;

		DEALLOCATE XmlVATInvoice;

	END

-- END HOA DON CO VAT

	COMMIT TRANSACTION

SELECT '0' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		CLOSE XmlData;

		DEALLOCATE XmlData;

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

		RETURN '-1'

End