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)

Justin Thorp 2,265 on at

Like (0)

Report

Hi,

Is the payment unposted? Check:

PM10201

PM10300

PM10400

Also, if you goto Inquiry - Purchasing - Trx by Vendor, find the invoice and drill on the amount remaining...what shows here? Please send a screenshot.

Thanks,

Justin

Was this reply helpful? Yes No
Suggested answer

soma 24,410 on at

Like (0)

Report

Run the below query and you can see the applied details for those two payments.

SELECT * FROM PM10200 WHERE APFRDCNM in ('XXXX','YYYY')

SELECT * FROM PM30300 WHERE APFRDCNM in ('XXXX','YYYY')

Note: Replace XXXX with your payment number 1 and YYYY with payment number 2.

Hope this helps!!!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

The duplicate payments were in PM10201, but not the other tables. I deleted them from PM10201, ran CheckLinks, logged out and then back in, but the duplicate payment is still on the HATB report.

I don't have an option to upload a screenshot, what is your email?

The Inquiry->Purchasing->Transactions by Vendor listed the original amount and nothing in the unapplied amount and no discount available, but it didn't show any transactions against it.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

No payments on the AP HATB report are in table PM10200

The duplicate payments are not in table PM30300. The valid payments are there, but the APFRDCNM is '00000000000000000107 ', which is not in the HATB. The APTVCHNM is in the HATB, is that what you meant on the second?

Was this reply helpful? Yes No
soma 24,410 on at

Like (0)

Report

Click the use rich formatting option under the reply box and attach the image of HATB and vendor inquiry window for those payments.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

From HATB: Voucher 00001 is the one that needs to disappear.

From Purchasing->Inquiry->Transaction by Vendor:

Double Click on a line:

That was from before I deleted the duplicate payments from PM10100. I try to open Payables Transaction Inquiry by Vendor and get: The stored procedure createSQLTempTable returned the following results: DBMS: 12, Microsoft GP: 0.

When the problem first occurred, the duplicate payments were in PM10100, PM10201, PM30300. I removed them in test, but the duplicate payments still show up in the HATB.

Was this reply helpful? Yes No
Suggested answer

soma 24,410 on at

Like (0)

Report

We are not able to see the attached screen shots.

You also need to check few more tables while removing payments from SQL. Better you can contact your GP partner and check with this issue.

Note: Deleting the payment from backend is not recommended solution until knows the appropriate tables.

Hope this helps!!!

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

Like (2)

Report

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
Community Member on at

Like (0)

Report

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
Community Member on at

Like (0)

Report

Was this reply helpful? Yes No