What tables feed AP Historical Aged Trial Balance? HATB showing Payments that don't exist in tables

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (17)

Answers (1)

Verified answer

Community Member on at

Like (0)

Report

RE: What tables feed AP Historical Aged Trial Balance? HATB showing Payments that don't exist in tables

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!

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

RE: What tables feed AP Historical Aged Trial Balance? HATB showing Payments that don't exist in tables

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: What tables feed AP Historical Aged Trial Balance? HATB showing Payments that don't exist in tables

Did Checklinks on all Purchasing and Tools->Utilities->Finanacial->Reconcile

gave no errors.

Historical Aged Trial Balance still shows the extra payment.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: What tables feed AP Historical Aged Trial Balance? HATB showing Payments that don't exist in tables

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.

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

RE: What tables feed AP Historical Aged Trial Balance? HATB showing Payments that don't exist in tables

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: What tables feed AP Historical Aged Trial Balance? HATB showing Payments that don't exist in tables

Here are updates of the Transaction by Vendor Inquiry dialogue

Distributions button:

From the Apply button:

The HATB report output is unchanged.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: What tables feed AP Historical Aged Trial Balance? HATB showing Payments that don't exist in tables

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: What tables feed AP Historical Aged Trial Balance? HATB showing Payments that don't exist in tables

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: What tables feed AP Historical Aged Trial Balance? HATB showing Payments that don't exist in tables

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.

Was this reply helpful? Yes No
Tom Mathew 1,750 on at

Like (2)

Report

RE: What tables feed AP Historical Aged Trial Balance? HATB showing Payments that don't exist in tables

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'

Was this reply helpful? Yes No