Announcements
Hi all
I have couple of payments laying unapplied. I applied to invoices from Sales>transactions>apply sales documents. After applying when I go to inquiry>Transaction by customer, this payment still appear in open transactions. Please suggest how to move to History.
Best Regards
Rashid Anwar
*This post is locked for comments
I believe the design has to do with documents available when generating AR Customer Statements.
If a document is paid and no longer should appear on a customer statement then move it to history.
I had a client who was frustrated that paid invoices continued to appear on customer statements. They were not aware of the move to history process.
Hi,
Darwish is correct in his explanation of how everything works, but you are beyond that. I think you want to remove the errant document and be done with it. If you have already tried the suggestions in the user interface and none of them worked, you may have to resort to file surgery to correct the problem. I would approach your issue the same way as I do payables. Believe me, if I could take care of it through the user interface, I would. Sometimes you have to go behind the scenes to fix bad transactions. The fact that RM works differently is correct, but it doesn't matter if your goal is to remove the transaction completely. The data will just be in a different set of tables.
Kind regards,
Leslie
Thank you Leslie but what about Ola Darwish comment above.
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
Hi Rashid,
Unfortunately, AR is not similar to AP in that regard. In AP as soon as the invoice is fully paid or the payment is fully applied, the status changes to history. In The AR module, the invoice will remain open after it is fully applied. The only way to move it to history is to run paid Transaction Removal.
Its under Sales>>Routines>>paid transaction removal
Before doing that you have to make sure that on the customer card, Maintain history checkboxes are all marked. Also it’s a good practice to take a backup.
There is one downside of running paid transaction removal. You will not be able to void anything that moved to history.
Thanks
Ola
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
