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