ALTER   PROCEDURE [dbo].[TR_REQ_PAY_INVOICE_UPD_Up]
@p_UP_ID VARCHAR(15) = NULL,
@p_REQ_PAY_ID	varchar(15)= NULL,
@p_REQ_PAY_CODE	varchar(50)	= NULL,
@p_MAKER_ID varchar(15) = null,
@p_MAKER_ID_KT varchar(15) = null,
@p_EDITOR_ID VARCHAR(20) = NULL,
@p_XMP_TEMP XML = NULL
AS
--Validation is here
	DECLARE @MAKER_ID_CREATE_INVOICE VARCHAR(15),@l_REQ_PAY_ID VARCHAR(15)
	SET @MAKER_ID_CREATE_INVOICE =(SELECT MAKER_ID FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
	SET @p_MAKER_ID_KT =(SELECT MAKER_ID_KT FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
	SET @l_REQ_PAY_ID = (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE REQ_PAY_CODE =@p_REQ_PAY_CODE)
	DECLARE @ENTRY_PAIR varchar(20),@DR_CR varchar(20),@ACCT VARCHAR(50), @ACCT_NAME VARCHAR(50), @AMT decimal(18,0),@CURRENCY VARCHAR(15), @EXC_RATE DECIMAL(18,0),
		@BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15),@TRN_DESC nvarchar(1000)
	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(50) = NULL,@TAX_NO nvarchar(15) = NULL,@GOODS_NAME nvarchar(500) = 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),@TOTAL_AMT_KT DECIMAL(18,2),@VAT_KT DECIMAL(18,2),@TYPE_VAT VARCHAR(15),@TYPE_FUNC VARCHAR(15),
		@MAKER_ID VARCHAR(20), @CREATE_DT VARCHAR(20), @CREATE_DT_KT VARCHAR(20), @MAKER_ID_KT VARCHAR(20), @AUTH_STATUS_KT VARCHAR(20)
	DECLARE @hdoc INT;
	EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP;
	DECLARE XmlInvoice CURSOR FOR
	SELECT *
	FROM
	OPENXML(@hdoc, '/Root/XmlData', 2)
	WITH(REQ_PAY_ID VARCHAR(15),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(50),TAX_NO nvarchar(15),GOODS_NAME nvarchar(500) ,
		 PRICE decimal(18,2),TAX decimal(18, 2),VAT decimal(18,2),NOTE NVARCHAR(MAX),VAT_RATE decimal(18,2),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),
		 PRICE_KT DECIMAL(18,2),VAT_KT DECIMAL(18,2),TOTAL_AMT_KT DECIMAL(18,2),TYPE_VAT VARCHAR(15),TYPE_FUNC VARCHAR(15),
		 MAKER_ID VARCHAR(20), CREATE_DT VARCHAR(20), CREATE_DT_KT VARCHAR(20), MAKER_ID_KT VARCHAR(20), AUTH_STATUS_KT VARCHAR(20))
		OPEN XmlInvoice;
	DECLARE @INDEX INT  =0
	BEGIN TRANSACTION
			-- VALIDATE
		IF(EXISTS (SELECT * FROM TR_REQ_PAY_INVOICE_UPD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND UP_ID<>@p_UP_ID AND AUTH_STATUS <>'A'))
		BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' REQ_PAY_ID, N'Đang tồn tại giao dịch điều chỉnh chứng từ có mã ' + @p_REQ_PAY_CODE +N' đang chờ duyệt'ErrorDesc
				RETURN '-1'
		END
		IF(@TYPE_FUNC='KT')
			BEGIN
				IF(EXISTS(SELECT * 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 REQ_PAY_ID <>@p_REQ_PAY_ID))
				BEGIN
						ROLLBACK TRANSACTION
						SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': Số hóa đơn '+ @INVOICE_NO +N' đã tồn tại trong hệ thống' ErrorDesc
				RETURN '-1'
				END		
			END

		-- CAP NHAT MASTER
		SET @l_REQ_PAY_ID =(SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE REQ_PAY_CODE = @p_REQ_PAY_CODE)
		UPDATE TR_REQ_PAY_INVOICE_UPD SET REQ_PAY_ID=@l_REQ_PAY_ID, MAKER_ID=@p_EDITOR_ID
		WHERE UP_ID= @p_UP_ID
		---
		DELETE FROM TR_REQ_PAY_INVOICE_UPD_DT WHERE UPD_ID=@p_UP_ID
		FETCH NEXT FROM XmlInvoice  INTO @p_REQ_PAY_ID, @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, 
		@MAKER_ID, @CREATE_DT, @CREATE_DT_KT, @MAKER_ID_KT, @AUTH_STATUS_KT
		WHILE @@fetch_status=0 BEGIN
			INSERT INTO TR_REQ_PAY_INVOICE_UPD_DT(UPD_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_UP_ID,@l_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, @MAKER_ID_CREATE_INVOICE,CONVERT(DATE,@CREATE_DT,103), @p_EDITOR_ID,'U',NULL,NULL,CONVERT(DATE,@CREATE_DT_KT,103),@p_MAKER_ID_KT,'U',NULL,NULL,'1',
			@INVOICE_NO_SIGN,@VAT_RATE,@CURRENCY,@RATE,@PRICE_KT , @VAT_KT,@TOTAL_AMT_KT,@TYPE_VAT,@TYPE_FUNC)
		IF @@error<>0 GOTO ABORT;
		FETCH NEXT FROM XmlInvoice INTO @p_REQ_PAY_ID, @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, 
		@MAKER_ID, @CREATE_DT, @CREATE_DT_KT, @MAKER_ID_KT, @AUTH_STATUS_KT
		END;
		CLOSE XmlInvoice;
		DEALLOCATE XmlInvoice;
		--- Luu log chinh sua
		INSERT INTO TR_REQ_PAY_INVOICE_LOG SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID
		-----
	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