Completely Removing a Posted SOP Invoice From Dynamics GP

Back in 2006, I had a client that called me asking how they could completely remove a posted SOP invoice from Dynamics GP.

They imported SOP invoices with non-inventory line items from their operations system to GP, and each invoice number matched the project number from their operations system, keeping things nice and tidy.  Apparently an employee found a mistake in one of the imported invoices, and instead of deleting the invoice and reimporting it before posting, the employee voided the receivables transaction associated with the invoice after it had been posted.  Because the voided transaction was using the invoice number in GP, they were unable to re-import the corrected invoice with the same invoice number.  There were probably a few alternatives to re-using the same invoice, but those would cause some problems for them down the road when it came to their reporting and reconciliation.

I recall asking a few colleagues how the voided invoice (and its number) could be completely removed from GP, and I think one or two people told me that it couldn't be done (or they didn't know how), perhaps short of SQL deletes and updates.  But after checking with a few gurus and poking around some rarely used GP windows, I came up with this relatively long, somewhat tedious, but apparently effective method.

I'm posting this because I'm working on an integration and recently had to remove some invoice numbers from my test database for testing.  I was able to use this process to remove the invoice numbers and complete my testing. 

One disclaimer is that I haven't looked at this procedure since 2006, and haven't bothered to see if there is a better, more efficient, or different way to do it.  I suspect there might be a different or simpler way, and welcome any comments with suggestions.  I didn't find any problems with this approach, and the client didn't seem to encounter any issues, but I am guessing that there could be some negative consequences to this approach for certain clients depending on how they use GP and SOP invoices (i.e. if they use inventory items, unlike my client).  So this is by no means a universal guaranteed solution.

And finally, there are a few caveats about this process.  First, it involves several steps that must all be performed properly to avoid deleting data accidentally.  Second, it requires that you perform the process on each document you wish to remove--it shouldn't simply be run for a customer or range of customers.  Third, it involves removing data completely from GP, as in permanently deleted.

Because of these risks, I strongly recommend that you make a complete backup of your GP databases before attempting this, and also make sure to test the process on a test database first so that you confirm all of the steps and data values.

=============================================

This procedure provides instructions on how to completely remove a sales order invoice that was posted and then voided through the Receivables Posted Transactions Maintenance.  (Transactions -> Sales -> Posted Transactions)

Verify the transaction under the Receivables Transaction Inquiry window (Inquiry -> Sales -> Transactions by Customer)

The invoice should be listed as an OPEN* and Voided transaction (as indicated by the Asterisk next to Open).

Open the Customer Maintenance window (Cards ? Sales ? Customer) and pull up the customer's record.

Click on the Options button.

 Make sure that all checkboxes are checked in the Maintain History section.  This specifies that Great Plains will move these transactions from Open to History and keep them rather than delete them.

The Paid Transaction Removal process will move the document from an OPEN status to HISTORY status.

Open the Paid Sales Transaction Removal process (Tools -> Routines -> Sales -> Paid Transaction Removal).  Note that this process can only be run on the Customer, and not on a single transaction.

1.    Select the Customer ID in the From and To fields
2.    Leave Class ID set to All
3.    In the Remove section, the Void checkbox must be checked
4.    The other checkboxes can be checked if desired, but are not necessary
5.    Leave Balance Forward Consolidation unchecked
6.    Check Print Register
7.    Click on the Process button
8.    A "Remove Transactions" confirmation dialog will appear—click Yes
9.  Print the register to screen (and printer if desired) to confirm that the documents were properly removed


When this process is complete the document should have been moved from OPEN to HISTORY.

Now that the voided document has been moved to history, the Receivables portion of the transaction can be removed from the database.

Open the Remove Receivables Transaction History window (Tools -> Utilities -> Sales -> Remove Transaction History).

1.    Change the Range to Document Number
2.    Enter the sales invoice number in the From and To fields.  Do NOT use a customer number.
3.    Click on the Insert Button
4.    Check all checkboxes at the bottom left corner of the window
5.    Click on the Process button


The document will no longer be listed in the Transaction Inquiry window.

The final step is to remove the Sales portion of the transaction from the database.

You can see that even though the receivables document no longer appears in the Receivables Transaction Inquiry window, you can still see the sales document in the Sales Order Processing Document Inquiry (Inquiry -> Sales -> Sales Documents)

To remove the sales document, go to Tools -> Utilities -> Sales -> Remove Sales History.

Choose a Range of Document Number, enter the document number in the From and To fields and click on the Insert button.

Do NOT use a customer number for the range.

Make sure that all checkboxes are checked at the bottom of the window.

Click on the Process button


Click on Redisplay in the SOP Document Inquiry window and the document should no longer be listed:

The above process removes all of the document records from the GP database; however, if sales tax is used, there are a few ancillary sales tax records that are not removed during this process.

This step must be performed by a Database Administrator.

Run the following select statement on the company database in question:

SELECT * FROM TX30000 WHERE DOCTYPE = 3 AND SERIES = 1 AND DOCNUMBR = 'XXXX'

(change XXXX to the RO number / document number for the voided invoice)

You should see two records for each SOP line item:

If these records match the voided transaction, they need to be manually deleted.

DELETE FROM TX30000 WHERE DOCTYPE = 3 AND SERIES = 1 AND DOCNUMBR = 'XXXX'


After this step is completed, you will be allowed to re-use the invoice number again.