Monday, July 5, 2010

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.

7 comments:

Mariano Gomez said...

You missed one: the transaction will still be in GL if it was posted. Removing the transaction from RM and SOP won't remove the GL distributions, so it's best to use the backout JE feature in GL to get rid of these.

MG.-

Steve Endow said...

Hi Mariano,

Well, the caveat that I mention with this specific procedure is that the RM transaction has been voided (or would need to be voided), so the GL distributions are reversed during that process.

There might be some element that I'm missing, but based on some tests, it looks like the void completely reverses all of the SOP distributions.

Thanks,

Steve

Mariano Gomez said...

Including the inventory and cost of goods portion? I thought when a SOP invoice got posted to RM it only carried over the RM related portion (SLS, RM, TAX, etc.) and that the inventory and cost of goods all went straight to GL, hence the reason for the "This invoice was posted in Sales Order Processing.." message.

Now I am intrigued :-)

Steve Endow said...

Well, in my scenario, the client uses non-inventory items, so the inventory implications is one of the caveats that I mention that might not work perfectly--the inventory quantities would presumably need to be returned via SOP.

But, regarding the GL impact, I just did a test in TWO, and I'm showing that the GL JE for the voided RM transaction does reverse the Inventory and COGS.

So it seems that the GL is taken care of, and it would only be inventory that needs cleanup for clients that use inventory items?

web developer said...

HI Mariano,

i am facing some issues in eConnect for SOP Invoice.
1. I want to migrate SOP open invoice(Fully Unaaply and Partially apply)
2.Here we have take information for header in SOP30200 and Line from SOP30300
3.If i migrate these details will migrated into SOP10100 as a header and SOP10200 as a line.(there we can post)
4. The problem is i am taking information from history table, the econnect wont allow the distribution type 14 and 15 because these two posting accounts
5. How can i avoid the posting account--DISTTYPE(14 & 15) while migrating SOP Invoice? Is there any way? Thanks in Advance

Unknown said...

Hello GP Gurus,
I would like to pick your brains & this was the best blog, related to my problem.

1. When I print my Document Inquiry Report (or open order report) there are at least (56) SO numbers listed at the beginning.

2. The Customer ID field is blank, as well as the customer name and the date is 0/0/000. Plus all money totals are zero.

3. I have tried voiding these open orders, but it says I cannot void something that has posted.

4. I can find these jobs in the system, but all customer info fields are blank.

I guess my question is, is there a way of flushing these jobs out?

Unknown said...

Thanks, Steve, I was just able to use this process to clean up a similar situation. Worked great!