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_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