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_Ins]

@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 @l_REQ_PAY_ID VARCHAR(15)

	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

		--VALIDATION

		IF(EXISTS (SELECT * FROM TR_REQ_PAY_INVOICE_UPD WHERE REQ_PAY_ID =@p_REQ_PAY_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

		-- INSERT MASTER

		DECLARE @p_UPD_ID VARCHAR(15);

		EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_INVOICE_UPD', @p_UPD_ID OUT;

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

		INSERT INTO TR_REQ_PAY_INVOICE_UPD (UP_ID,REQ_PAY_ID,MAKER_ID,CREATE_DT,CHECKER_ID,APPROVE_DT,AUTH_STATUS) VALUES (@p_UPD_ID,@l_REQ_PAY_ID,@p_EDITOR_ID,GETDATE(),NULL,NULL,'U')

		---

		--DELETE FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID=@p_REQ_PAY_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

			SET @INDEX = @INDEX +1

			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

			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,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, EDITOR_ID) 

			VALUES (@p_UPD_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,CONVERT(DATE,@CREATE_DT,103),'U',NULL,NULL,CONVERT(DATE,@CREATE_DT_KT,103),@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, @p_EDITOR_ID)

		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, @p_UPD_ID UP_ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		--CLOSE XmlInvoice;

		--DEALLOCATE XmlInvoice;

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

		RETURN '-1'

End