GP 10, windows 7
The Purchasing->Historical Aged Trial Balance report shows two payments (different voucher numbers) applied to a document. I can't find the first payment voucher number in PM00400, PM10100, PM20000, PM20100, PM30200, PM30300, PM30600, PM80500, PM80600, MCO20105
It has to exist somewhere, or else it wouldn't show up in the AP HATB report. CheckLinks gives no errors.
*This post is locked for comments
Solution:
1) Check Links for Purchasing had been run from previous attempt to resolve this issue.
2) Used Dave Musgrave's script to search for string in all database user tables, located at blogs.msdn.com/.../updated-spsearchonalldb-sql-stored-procedure-to-search-an-entire-database.aspx
3) Deleted the duplicate payments from the tables they were in:
delete from EURO2..[PM10100] where [VCHRNMBR] < '000025';
delete from EURO2..[PM10201] where [PMNTNMBR] < '000025';
delete from EURO2..[PM30300] where [VCHRNMBR] < '000025';
4) Ran CheckLinks to verify database integrity
Thank you everyone for your help in getting to this point!
I would log a technical support incident with Microsoft and engage them to help you clean up the problem record(s) using SQL Server Management Studio > SQL Query Analyzer.
I have done similar projects on numerous past occasions, which are typically made necessary by data not being deleted when transferred to other tables (you typically wind up with a record in a work and open table, or open and history).
I strongly recommend creating/refreshing a test database for this process, testing the fix and documenting it prior to executing it in the Live database.
Did Checklinks on all Purchasing and Tools->Utilities->Finanacial->Reconcile
gave no errors.
Historical Aged Trial Balance still shows the extra payment.
I ran the Purchasing Reconcile Payables Accounts Utility and Summary shows that the balances are off by more than the duplicate payment amount. If I try to limit it to fiscal or calendar year, it doesn't return any information.
It looks like you have a duplicate payment record with no Document Number, Date, Description, etc. I noticed you ran check links earlier; have you tried run the Purchasing Reconcile Payables Accounts Utility? If you run the utilities, and can't get a resolution, then it is time to consider using SQL to eliminate what appears to be remnants of transactions in your Payables tables. I would strongly suggest engaging Technical Support to explore all your options and get this just right.
Here are updates of the Transaction by Vendor Inquiry dialogue
Distributions button:
From the Apply button:
The HATB report output is unchanged.
I restored the database and ran the spSeachOnAllDB query.
The duplicate payment is listed in PM10100, PM10201, PM30300 and HATB.
I need to remove the duplicate payment without triggering a journal entry.
This didn't return any results for the payment voucher number that is showing up on the HATB report. It did return results for the voucher and document number the payment is applied to, as well as the other applied payment voucher number.
Use David's stored procedure "sp_searchonalldb" to find the documents.
It will bring you the table name and field name where the specific document sits.
You can download the script from
blogs.msdn.com/.../spsearchonalldb-sql-stored-procedure-to-search-an-entire-database.aspx
execute the proc in your company database and run it
exec sp_searchonalldb 'your doc no'
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (