Page History

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Asamco BV, Alexander Toufexis
-- Create date: 2019-05-15
-- Description:	Change invoice date
-- =============================================
CREATE PROCEDURE _as_ChangeInvoiceDate 
	-- Add the parameters for the stored procedure here
	@InvoiceNumber varchar(100) = NULL,
	@AuditNumber varchar(100) = NULL,
	@DocType int = 0, --only needed in case of an @InvoiceNumber
	@NewTxDate datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	
	--checks if not both are null
	IF (@InvoiceNumber is null AND @AuditNumber is null)
		BEGIN
			RaisError('Both parameters are NULL!',17,1)
            RETURN -1
		END

	DECLARE @InvNumID bigint
	IF (@InvoiceNumber IS NOT NULL)
		BEGIN
			SELECT @InvNumID = (SELECT TOP 1 AutoIndex FROM InvNum WHERE InvNumber = @InvoiceNumber AND DocType = @DocType )
			SELECT @AuditNumber =
				CASE --get from AR or AP table, dependent on docType
					WHEN @DocType IN (0,1,4,6) THEN (SELECT cAuditNumber FROM PostAR WHERE InvNumKey = @InvNumID)
					WHEN @DocType IN (2,3,5,7) THEN (SELECT cAuditNumber FROM PostAP WHERE InvNumKey = @InvNumID)
					END

		END
	ELSE 
		BEGIN --lookup based on audit number
			SELECT @InvNumID =
				CASE --get from AR or AP table, dependent on docType
					WHEN @DocType IN (0,1,4,6) THEN (SELECT InvNumKey FROM PostAR WHERE cAuditNumber = @AuditNumber)
					WHEN @DocType IN (2,3,5,7) THEN (SELECT InvNumKey FROM PostAP WHERE cAuditNumber = @AuditNumber)
					END
		END

	--Check if all relevant fields are found:
	IF (@InvNumID IS NULL OR @AuditNumber IS NULL)
		BEGIN
			RaisError('Values not found. Cancelling procedure!',17,1)
			RETURN -1
		END


	--now update the dates!
		--PostAR/AP
		IF (@DocType IN (0,1,4,6))
			BEGIN
				UPDATE PostAR SET TxDate = @NewTxDate WHERE cAuditNumber = @AuditNumber
			END
		ELSE
			BEGIN
				UPDATE PostAP SET TxDate = @NewTxDate WHERE cAuditNumber = @AuditNumber
			END
		--PostGL
		UPDATE PostGL SET TxDate = @NewTxDate WHERE cAuditNumber = @AuditNumber
		--PostST
		UPDATE PostST SET TxDate = @NewTxDate WHERE cAuditNumber = @AuditNumber
		--invNum
		UPDATE InvNum SET InvDate = @NewTxDate WHERE AutoIndex = @InvNumID

		DECLARE @msg varchar(max)
		SELECT @msg = 'Updated to new TxDate for audit number: ' + @AuditNumber + ' and invnumID: ' + CAST(@InvNumID as varchar(100))+  ' (invnumber: ' + COALESCE(@InvoiceNumber,'no invno provided') + ')';
		PRINT @msg;
END
GO