Dynamics Community Forum Thread Details

Payments have been applied to invoices but still not moved to history in Receivable Management

Rashid,

I'll start this by telling you this was a PM problem, but it sounds very much like the problem you're facing. I'll also say that this procedure should not be attempted by a newbie. However, if you are VERY comfortable with SQL AND the GP data model, you might try this on a sample database. I call this kind of thing ‘file surgery’ and it is something only an experienced person should attempt.

It was time consuming, so be prepared to pay a few hours of consulting time if you (as you should) engage a skilled consultant to complete it.

My goal was to delete anything related to a stuck payment so that they could enter a proper payment and clean up the books. Since I didn’t know exactly which tables were affected, I searched the whole database to make sure I found all of the transactions wherever they landed. For instance, you may have records in a third-party module like Mekorma.

1. Everyone should log out of the production company and stay logged out until you’re finished. You can take it offline if you want, but that’s not usually acceptable unless it’s absolutely necessary, like if you cannot perform your fix while anyone is logged in.

2. In preparation, I backed up production and restored the data into a test company. This is the MOST IMPORTANT task in the entire process.

3. After the backup, I searched all of the tables in the database for the following:

a. The Voucher number of the invoice.

b. The Document number of the invoice.

c. The Voucher number of the payment.

d. The Document number of the payment.

4. Next, I looked through the results of my search and deleted any record that I knew didn't have anything to do with my transaction(s). For example, if some record in a SOP table had a Master Number that matched one of my search values. It's pretty easy to scan down the search results and pick out the ones that don't relate. If you don’t know, don't delete it. You’ll discover the answer later.

5. I ended up with a total of 41 tables that included relevant records. I copied the results to a new Excel spreadsheet. I converted the columns that would be copied over to Text. I changed them to Text so that Excel wouldn't truncate any of my values.

6. Next, I used Excel to build a select statement that would return the table and column that contained my search values. Then I evaluated the tables to weed out irrelevant records. Some of the tables will be listed more than once if both documents are referred to in the same table. For example, if you have a record in the Apply table, you will get two hits on the same record because each search value shows up in a single record, just in different fields.

7. I evaluated the results of my select statement and determined which records needed to be deleted or adjusted. Some of them were fine, like the general ledger transaction resulting from posting the invoice. Remember - my goal was to delete all records related to the stuck payment.

8. My evaluation resulted in 22 records that needed to be deleted or adjusted and 18 records that were fine.

9. I went back to my Excel spreadsheet and created a ‘delete’ query for those records needing to be deleted, and an ‘update’ statement for the records that just needed to be changed.

10. I searched the database again to confirm the results were what I expected.

11. In the end, only 9 unique tables were affected. I needed to delete or adjust 22 records, but they were all in just 9 tables. My client did not have any third-party modules.

12. I then launched GP and ran Reconcile and Check Links on the modules that included any of my 9 tables. In my case, it was Finnancial and Purchasing. If your database is small enough you could run it on everything. I like to do that, but it takes too long on a large database. Run Check Links until it returns no errors.

13. Now was the moment of truth. I went the Edit Check Batch window in Purchasing and selected the invoices impacted by stuck payments; I needed to make sure they were free to be paid. Success!

14. I backed up my test company.

15. I refreshed my test company with the production database once more and went through the process again.

16. I compared the first test company’s dataset totals to the current test company’s database to make sure the data was still correct. I just compare totals in the data tables rather than print reports. It’s WAY faster than printing a bunch of reports, and it’s easier to check more tables this way.

17. It worked again, so I moved to production and went through process on production.

18. I compared the production dataset to the test company’s to confirm that the data was correct.

19. Success again!

20. I then backed up both the Test company and the Production company databases.

21. Done!

Arguably, I could have just restored my completed test company over production, but I don’t like doing that. It’s common for clients to be very unhappy if they are locked out of production. They swear they will only print reports and run inquires – but that’s a promise that is often forgotten by somebody.

If I do this at night or over the weekend, I’d lock them out. That way, if somebody did try to log in they wouldn’t be able to. Don’t forget that the datasets might not be equal if an errant transaction was entered. It’s relatively easy to track down what was done, and it’s a step you have to take if your test totals do not match.

Like Harry Lee says “it’s better to take time than chances”.

That’s it. I’ve had to do this several times over the last 20 years, and now I’m sharing it with you all. Enjoy!

Kind regards,

Leslie

Quick Links

Leaderboard