In 2006, a client requested a way to fully erase a posted SOP invoice from Dynamics GP. They imported invoices with non-inventory items from their operational system, aligning invoice numbers with project numbers. An employee, finding an error in an imported invoice, voided its receivables transaction after posting instead of deleting and reimporting it. This action prevented the use of the same invoice number for the corrected invoice.

While alternatives to reusing the invoice number existed, they would impact future reporting and reconciliation. This led to a search for a solution to fully remove the voided invoice and its associated number. While some colleagues suggested it was impossible without SQL manipulation, a multi-step method was discovered.

This method, revisited recently for an integration project, effectively removed invoice numbers in a test database. However, it’s crucial to note that this method hasn’t been reviewed since 2006, and more efficient alternatives may exist. The effectiveness of this approach with inventory items is uncertain, and it should not be considered a universal solution.

This procedure involves multiple precise steps to prevent data loss and should be performed individually for each document, not across a range of customers. It’s essential to understand that this method permanently removes data from GP.

Given the risks, creating a full backup of your GP databases is strongly advised before attempting this process. Testing on a separate database to verify steps and data values is also recommended.

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

This guide outlines removing a posted and subsequently voided sales order invoice through Receivables Posted Transactions Maintenance (Transactions -> Sales -> Posted Transactions).

Confirm the transaction details within the Receivables Transaction Inquiry window (Inquiry -> Sales -> Transactions by Customer).

The invoice should appear as “OPEN*” and “Voided”, denoted by the asterisk next to “Open”.

Access the Customer Maintenance window (Cards -> Sales -> Customer) and locate the relevant customer record. Click on the “Options” button.

Ensure all checkboxes within the “Maintain History” section are selected. This directs Great Plains to transfer these transactions from “Open” to “History” for record-keeping instead of deleting them.

The “Paid Transaction Removal” process will transition the document status from “OPEN” to “HISTORY”.

Open the “Paid Sales Transaction Removal” process (Tools -> Routines -> Sales -> Paid Transaction Removal). Note: This process functions at the customer level, not for individual transactions.

1. Input the “Customer ID” in both the “From” and “To” fields.
2. Retain the “Class ID” setting as “All”.
3. In the “Remove” section, ensure the “Void” checkbox is marked.
4. Other checkboxes can be selected as needed, but aren’t mandatory.
5. Leave “Balance Forward Consolidation” unchecked.
6. Check “Print Register”.
7. Click the “Process” button.
8. A confirmation dialog box labeled “Remove Transactions” will display; click “Yes”.
9. Print the register (onscreen or to a printer) to validate successful document removal.

Upon completion, the document status should shift from “OPEN” to “HISTORY”.

With the voided document now in “History,” the “Receivables” segment of the transaction can be removed from the database.

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

1. Set the “Range” to “Document Number”.
2. Input the sales invoice number in both the “From” and “To” fields. Do not use a customer number here.
3. Click the “Insert” button.
4. Select all checkboxes located at the bottom left of the window.
5. Click the “Process” button.

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

Finally, remove the “Sales” component of the transaction from the database. It’s worth noting that despite the receivables document disappearing from the Receivables Transaction Inquiry window, the sales document may still be visible in the Sales Order Processing Document Inquiry (Inquiry -> Sales -> Sales Documents).

To eliminate the sales document, navigate to Tools -> Utilities -> Sales -> Remove Sales History.

Select “Document Number” for the “Range”, input the document number in both the “From” and “To” fields, then click “Insert”. Avoid using a customer number for the range. Ensure all checkboxes at the window’s bottom are checked. Click the “Process” button.

Clicking “Redisplay” within the SOP Document Inquiry window should show that the document is no longer listed.

This process removes all document records from the GP database. However, if sales tax is enabled, additional sales tax entries might remain. This step necessitates action by a Database Administrator.

Execute the following SQL query on the relevant company database:

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

(Replace ‘XXXX’ with the voided invoice’s RO/document number)

This should return two records per SOP line item:

If these records correspond to the voided transaction, manually delete them using the following SQL command:

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

After this, the invoice number can be reused.