PM Historical Trial Balance

(0) Share

Report

Posted on by Community Member

I want a SQL query that will list all outstanding payable invoices and their AP control accounts, as of a given date. Would anyone be able to help me with such a query please?

Thanks,

Deesom

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Tim Foster 8,515 on at

Like (0)

Report

RE: PM Historical Trial Balance

Go here:

victoriayudin.com/.../payables-sql-views

Tim

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

RE: PM Historical Trial Balance

Deesom, the challenge with this question is that you are going to ask for more that what is only in the PM Transaction Open table.

We just spent many hours with a client trying to determine the very question and we have now over 10 different queries that we will run depending on the 'ask' that is happening.

It is a matter of linking the PM30300 Distribution information and the PM30600 PM Historical Transactions along with the GL00100 for the Account details. Then figuring out the dates is another matter altogether.

We are now able to look into the History and Open tables and tell what Invoice is OPEN / Partially Paid / Fully Paid for a specific Vendor over the course of 'all history'. Our next tackle with this project will be to start to date filter the findings.

While not a complete answer, maybe this will give you some sort of idea of what you have to start to look for. If you want to discuss the details, post the SQL Query that you have started with, and maybe I or others will some more feedback.

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

RE: PM Historical Trial Balance

Tim I love the work that Victoria has on this site, and I have / am using many of the scripts here as a basis for other things that we are doing, I am not sure which one of these documents might address the question here - not that they would not help - they are again as stated a great place to start.

Thanks for great scripts Victoria, I have used them a ton to figure out issues.

Was this reply helpful? Yes No
Victoria Yudin 22,768 on at

Like (0)

Report

RE: PM Historical Trial Balance

Thanks Bill! And thanks Tim for the shout out! :-)

Bill, you are correct. I currently do not have any scripts for the AP historical aged trial balance posted on my blog. Although I have created HATB reports in the past, as you pointed out, these often need to be customized to the specific situation, environment and needs. It has been my experience that creating one of these can be quite complex. If anyone is interested in a custom report created for them, please contact me and we can discuss options.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: PM Historical Trial Balance

My thanks to Bill Campbell and Tim Foster for their timely and informative responses. Even though I don't have the answers I want, your guidance will certainly give me a fair idea of the possible approach I should pursue to resolve my issue. Thanks also to Victoria for her collection of postings.

Regards,

Deesom.

Was this reply helpful? Yes No