SQL QUERY FOR PAYABLE OUTSTANDING TRANSACTIONS WITH INVOICES THEY'RE APPLIED TO

(0) Share

Report

Posted on by Community Member

Hi all,

I'm trying to get a report for outstanding payable transactions (ie. checks, cash) with the invoices they were applied to and the distributions that posted from the invoices. Any idea if there's an available SQL script for that? I've looked but couldn't find one. I tried joining the various tables (PM30200, PM30300, PM30600, CM30200) but the data comes out wrong. The report I'm looking for should also contain the description on the invoice.

Any help would be greatly appreciated!

Odiop

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

MattPaulen 6,912 on at

Like (2)

Report

Have you checked Victoria Yudin's site? I'm not sure if there is something with exactly what you need but it should be a good starting point. She's got one that has the apply info and one that has the distributions so maybe utilizing the two will help.

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

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

Like (1)

Report

Matt that is a great suggestion. Sometimes we spend a ton of time looking for that 'last' piece of data on a single report, when two might just do fine. If I had the details on one and then needed to get into the distribution, I might find that an acceptable solution.

Often times as you likely encountered it when you try to link that one more transaction laden table into a view that messes up everything else.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

I agree with Matt and Bill. Victoria Yudin has tons and tons of SQL views posted on her site. Also look at the existing views in the GP company database.

If you want to post a description of each column you want on your report I'd be happy to take a swing at it when I have time.

Was this reply helpful? Yes No
Suggested answer

Victoria Yudin 22,769 on at

Like (0)

Report

Thanks for all the shout outs guys! :-)

I do have a view on my blog that combines the Payables apply detail with the GL distributions of the invoices:

victoriayudin.com/.../sql-view-for-payables-apply-detail-and-gl-distributions-in-dynamics-gp

Hope that helps.

Was this reply helpful? Yes No