Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP (Archived)

Invoice Payment History Report

(0) ShareShare
ReportReport
Posted on by 105

I am trying to generate a report that shows some invoice detail along with the payment info that applies to the invoice, such as the check or checks that went to pay for the invoice and the dates and amounts etc.

This sounds like it would be an easy thing to create since I can get this info on a per document basis from Inquiry. But I need this in a report form, ie for auditing purposes or to show a vendor their invoices that I have paid.

I am trying to use SmartList Builder because it is easy to create something that I can export to Excel, manipulate, then save as a pipe-delimited file. But it is not immediately clear the relationships between the tables PM30200 and PM30300 and how they are used to provide the functionality in the Inquiry window.

I am surprised that it is not a "canned" report since this is extremely useful info. I also can't imagine that I am the first to try and get this info in a report form.

Can anyone enlighten me? What am I missing?

 Thanks.

*This post is locked for comments

  • Re: Invoice Payment History Report

    Hello,

    Thank you for using Microsoft Online Communities.  I understand that you would like an invoice payment history report.  The best way to accomplish this would be to use the information from the PM30300 table(PM Apply to History File).

    This table will hold all apply records for payments, credit memos and returns.  When you look at this table you will want to check the APTVCHNM column.  This column will hold the voucher number of the invoice.  So if you were run a select statement on this table for this column it will return all lines that are related to the invoice.  You can then look at the voucher number to determine what the payment was that was applied.

    If you have any other questions please let me know.

    Best regards,

    Brad G

    Microsoft Dynamics GP

    We hope you get value from our new forums platform! Tell us what you think

    social.microsoft.com/.../threads

    This posting is provided AS IS with no warranties, and confers no rights

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Invoice Payment History Report

    CheckStub Look Up by Strophe enables users to view check stub details from the payables transaction by vendor inquiry screen and reprint the check stub information any time in the future after printing computer checks. This module will also provide GP bank rec information, Check and Document detail links, and the ability to mass reprint check stubs in both detail and summary for a group of vendors.

    Short Online Demo below:

     

  • Richard Schultz Profile Picture
    Richard Schultz 1,085 on at
    Re: Invoice Payment History Report

     Craigmeister,

    The PM30200 is the Payables "Header" table; it contains once-only information from an invoice (like Date, invoice number, Vendor, etc.).  The PM 30300 is the Payables "Detail" table; it contains the lines of the invoice (with info like Line number, quantity, Item, & so on).  Every invoice (aka "voucher" in the PM world) has a header and detail section, so every invoice will have an entry in the PM30200 table (one) and in the PM30300 table (one or more).  If memory serves, PM30200 also stores the checks (amount, date, number, etc).

    Now, there is also a PM10200 & 10300 set of tables.  The difference between that set of tables and the ones you noted is that the ones starting with a "3" are "History" tables; that is, the data in them is not used for transacting business anymore.  GP knows this because they have been fully paid.  When you cut a check that completely pays an invoice, GP runs some stored procedures on the database that move the invoice (and check, if fully-applied) from the PM1-series to the PM3-series.

    Here's where it gets odd.  If you have PARTLY paid an invoice, it will remain in the PM1-series, because it hasn't been fully paid.  However, the check (if fully applied to a voucher) WILL move to the PM3-series; accordingly, any query that looks at both checks cut AND invoices paid needs to include both sets of tables.

    Of course, the fun doesn't end there.  There is another set of tables (numbers escape me right now, but I can dig them up if you like) that stores the "offset" transactions; that is, which check was applied to which invoice.  These tables exist in the 1-series (aka "open" or "work" records) and the 3-series (aka "closed" or "history" records).  To tie a check to a voucher, you'll need to include those tables.

    If you remain stuck, let me know, and I can try to put that structure together for you in a SQL query.  I'm a bit under the gun right now, so I may not be very quick, but I'll try to get it for you in a few days.  You can reach me at rschultz<at>100wattsolutions<dot>com.

  • Frank Hamelly | MVP, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 46,220 Super User 2024 Season 2 on at
    Re: Invoice Payment History Report

    Craigmeister,

    Check Victoria Yudin's blog post here http://victoriayudin.com/2008/10/22/sql-view-ap-apply-in-gp/ and see if this helps.

    Best regards,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,711 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,458 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans