ePay Invoice deletion

ePay Invoice deletion.sql

--------------------------------------------------------------------------------------------------
-- To remove a normal Invoice from the ePay database, you need to delete from three tables:
-- Invoice, InvoiceEntry, and InvoicePayment. However, our users might not know the exact 
-- InvoiceNumber(s) you need, so you might need to recover them based on the customer and
-- "Voucher Number" (which is BatchNumber in the database).
--------------------------------------------------------------------------------------------------

DECLARE @InvoiceID uniqueidentifier, @InvoiceNumber nvarchar(30), @BatchNumber nvarchar(25), @Amount decimal (19,2), @CustomerNumber nvarchar(25), @CustomerName nvarchar(65);

DECLARE curInvoiceID CURSOR LOCAL FAST_FORWARD FOR
	-- Try just running this cursor query to see what this is going to do.
	SELECT i.InvoiceID
		, i.InvoiceNumber, c.CustomerNumber, c.Name AS "CustomerName", i.BatchNumber, i.Amount, i.CreatedOn, i.Comments
	FROM dbo.Invoice i
	JOIN dbo.Customer c ON c.CustomerID = i.CustomerID
	-- Obviously, change this WHERE clause to return the invoices you need to delete.
	WHERE (
		i.InvoiceNumber in ('235415', '234772', '234597')  -- InvoiceNumber shows as "TRANSACTION #" in some screens
		--( c.CustomerNumber='100674' AND i.BatchNumber IN ('200001267') )  -- BatchNumber shows as "Voucher Number" in some screens
		--OR ( c.CustomerNumber='101052' AND i.BatchNumber IN ('180012256', '180012257', '180012258') )
	)
	ORDER BY c.CustomerNumber, i.BatchNumber
	;

OPEN curInvoiceID;
FETCH NEXT FROM curInvoiceID INTO @InvoiceID, @InvoiceNumber, @CustomerNumber, @CustomerName, @BatchNumber, @Amount
WHILE @@FETCH_STATUS = 0 BEGIN
	PRINT N'Found an invoice for customer ' + @CustomerName + N' (Account ' + @CustomerNumber + N')'
		+ N' for voucher/batch number ' + @BatchNumber
		+ N' for amount ' + CAST(@Amount AS nvarchar(21))
		+ N' . It is Invoice Number ' + @InvoiceNumber + '.'

	BEGIN TRAN;

	DELETE FROM dbo.InvoicePayment WHERE InvoiceID = @InvoiceID
	PRINT 'Deleted ' + LTRIM(STR(@@ROWCOUNT)) + ' record(s) from table [InvoicePayment] where InvoiceID = ' +  CAST(@InvoiceID AS nvarchar(255));
	DELETE FROM dbo.InvoiceEntry WHERE invoiceid = @InvoiceID
	PRINT 'Deleted ' + LTRIM(STR(@@ROWCOUNT)) + ' record(s) from table [InvoiceEntry] where InvoiceID = ' +  CAST(@InvoiceID AS nvarchar(255));
	DELETE FROM dbo.Invoice WHERE InvoiceID = @InvoiceID
	PRINT 'Deleted ' + LTRIM(STR(@@ROWCOUNT)) + ' record(s) from table [Invoice] where InvoiceID = ' +  CAST(@InvoiceID AS nvarchar(255));

	-- You need to COMMIT instead of ROLLBACK to make the changes permanent.
	ROLLBACK TRAN; PRINT '(rolled back - change the ROLLBACK to COMMIT to make permanent)';
	--COMMIT TRAN; PRINT 'Changes commited.';

	PRINT '';
	FETCH NEXT FROM curInvoiceID INTO @InvoiceID, @InvoiceNumber, @CustomerNumber, @CustomerName, @BatchNumber, @Amount;
END;

CLOSE curInvoiceID; 
DEALLOCATE curInvoiceID;


--------------------------------------------------------------------------------------------------
-- Although credit memos look the same as Invoices in many places in the GUI, they are stored 
-- differently. According to SKG support, Payment is the (only?!?) table we need to delete from
-- for a credit memo...
--------------------------------------------------------------------------------------------------
-- C-001268

SELECT top 10 p.PaymentNumber, p.Amount, p.BatchNumber
	, c.CustomerNumber, c.Name AS "CustomerName"
	, p.PaymentID, p.CreatedOn--, p.CustomerID 
FROM Payment p
JOIN Customer c ON c.CustomerID = p.CustomerID
WHERE c.CustomerNumber='100613' AND p.Amount IN (7303.77, 1139.81, 24990.00)

BEGIN TRAN;
	delete from Payment where PaymentID = '37117F2B-03C4-4FF0-87A2-F35C43960107' ;
ROLLBACK TRAN; PRINT '(rolled back - change the ROLLBACK to COMMIT to make permanent)';
--COMMIT TRAN; PRINT 'Changes commited.';