SQL view to show what check paid a Purchase Order

October 17, 2008 by Victoria Yudin 14 Comments


There is no easy way in Dynamics GP to see what check or credit memo was applied to a particular Purchase Order. Here is a SQL view that can be used in SmartList Builder or any other reporting tool you choose to see this information. For other SQL scripts please visit my GP Reports Page.

~~~~~

CREATE VIEW view_WhatCheckPaidMyPO
AS

/*****************************************************************
view_WhatCheckPaidMyPO
Created on Oct 17, 2008 by Victoria Yudin, Flexible Solutions, Inc. 
For updates see https://victoriayudin.com/gp-reports/
Does not take Multicurrency into account.
Will return multiple lines for PO's resulting in multiple invoices
     or ones paid by multiple checks, credit memos or returns.
Updated Sep 11, 2009 to change columns returned to be
     more 'Crystal friendly' and clean up some formatting.
*****************************************************************/

SELECT     PO.PONUMBER PO_Number,
           RH.POPRCTNM POP_Receipt_Number,
           RH.VCHRNMBR Voucher_Number,
           RH.VENDORID Vendor_ID,
           coalesce(PM.APFRDCNM,'') Payment_Number,
           coalesce(PM.DOCDATE,'1/1/1900') Payment_Date,
           coalesce(PM.APFRMAPLYAMT,0) Payment_Amount,
           CASE PM.DOCTYPE
                WHEN 4 THEN 'Return'
                WHEN 5 THEN 'Credit Memo'
                WHEN 6 THEN 'Payment'
                ELSE 'Not Paid'
                END Payment_Type

FROM POP30300 RH

     LEFT OUTER JOIN
           (SELECT VENDORID, APTVCHNM, APFRDCNM, DOCTYPE,
                       DOCDATE, APFRMAPLYAMT
                FROM PM10200
            UNION ALL
            SELECT VENDORID, APTVCHNM, APFRDCNM, DOCTYPE,
                       DOCDATE, APFRMAPLYAMT
                FROM PM30300) PM
            ON RH.VCHRNMBR = PM.APTVCHNM AND RH.VENDORID = PM.VENDORID

     LEFT OUTER JOIN
           (SELECT PONUMBER, POPRCTNM, POPTYPE FROM POP10500
            WHERE POPTYPE IN (2,3)
            GROUP BY PONUMBER, POPRCTNM, POPTYPE) PO
            ON PO.POPRCTNM = RH.POPRCTNM

WHERE RH.POPTYPE IN (2,3)

/**the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions**/
GO
GRANT SELECT ON view_WhatCheckPaidMyPO TO DYNGRP

~~~~~

Disclaimer: I only tested this on limited data, if you find an issue or have a suggestion for improving this view, please let me know and I will post the update here for everyone to benefit.

GP Reports code, GP SQL scripts, Payables SQL code
, , , ,

14 Responses to “SQL view to show what check paid a Purchase Order”

  1. Rennecia Reply March 4, 2020 at 2:00 pm

    Hi Victoria, I checked out your script. It seems to be duplicating the data if the Receipt had multiple lines.

    Like

    • Victoria Yudin Reply March 4, 2020 at 2:03 pm

      Hi Rennecia,

      I am not looking at individual lines, but this will return multiple lines for PO’s resulting in multiple invoices or ones paid by multiple checks, credit memos or returns. could that be what you’re seeing?

      -Victoria

      Like

  2. Derek L Reply July 10, 2013 at 2:18 pm

    Hi Victoria,

    We’ve been spinning our wheels trying to put together an “encumbrance report” showing unpaid POs on GL codes. We’ve currently delivered a report that does just that, but our methodology was to tie GL codes to payments, determine if they are against a PO (by presence of a PO receipt number), and then subtract from the PO totals on that GL code. It seems to work, but now they’d now like to be able to “drill down” to individual PO line items and we’re stumped. Any thoughts?

    Like

    • Victoria Yudin Reply July 11, 2013 at 2:47 pm

      Hi Derek,

      The only thing that I can think of would be to look at the GL account on each POP line to see if you can use that, but I am not sure if that would work. Have you looked into using PO Commitments for this? I have not used it and am not sure if it would help, but it might…

      -Victoria

      Like

      • Derek L Reply July 11, 2013 at 3:05 pm

        Yeah, we’re beginning to doubt it can be done since we can’t actually find where the information is tied together in GP. We may just enable them to drill to PO level, which we think that we can do.

        We aren’t using the PO Commitments module. That would make things too easy, wouldn’t it?

        Thanks for your quick response!

        Like

  3. Tom Myers Reply March 7, 2011 at 11:49 am

    Victoria,
    Anyway to link this view to show not only the receipt for the invoice match, but the shipment receipt also?
    tom

    Like

    • Victoria Yudin Reply March 7, 2011 at 5:27 pm

      Tom,

      Do you mean you want to show a line for each receipt the invoice was matched to? That’s not really the goal of this view…but I believe you should be able to do that using the POP10500 table. I will put POP reports on my ‘wish list’ for future blog posts.

      -Victoria

      Like

  4. Santosh Reply April 22, 2010 at 1:50 am

    Hi Victoria,

    You have developed various query for AR, AP, GL, Inventory and lastly FA. Thanks for that. I have used all your quries and put into smartlist using smartlist builder.

    Do you have any plan to develop such type of query for POP transaction?

    Something like POP Analysis report. Which should show Purchase order and its related transaction like shipment, invoice, return transaction ( Shipment return/ Invoice retun etc).

    This will definatly help to know the status of PO also help to now how much is balance in Purchase Order to GRNI or invoice.

    Hope you have understand what I am thinking. If you have such ready query please share with me.

    Regards,
    Santosh

    Like

    • Victoria Yudin Reply April 22, 2010 at 6:03 am

      Santosh,

      Thanks for frequenting my blog! I don’t have anything like what you’re asking for available to post. I will put this on the list of requests for future posts.

      -Victoria

      Like

      • FAC Reply April 23, 2010 at 1:57 pm

        Hi Victoria

        maybe this helps a little

        I did this to view the status of one PO that’s why the Variable

        the result is just like making an inquiry of purchase orders

        hope that helps

        DECLARE @PONUMBER NVARCHAR(25) SET @PONUMBER = ‘ODC000001055’
        SELECT ‘Purchase Order’ AS POPTYPE, DOCDATE, CASE POSTATUS WHEN 1 THEN ‘New’ WHEN 2 THEN ‘Released’ WHEN 3 THEN ‘Change Order’ WHEN 4 THEN ‘Received’ WHEN 5 THEN ‘Closed’ WHEN 6 THEN ‘Cancelled’ END POSTATUS,
        PONUMBER, ” AS POPRCTNM, CURNCYID
        FROM POP10100 where PONUMBER = @PONUMBER
        UNION ALL
        SELECT DISTINCT CASE POPTYPE WHEN 1 THEN ‘Shipment’ WHEN 2 THEN ‘Invoice’ WHEN 3 THEN ‘Shipment/Invoice’ WHEN 4 THEN ‘Return’ WHEN 5 THEN ‘Return w/Credit’ WHEN 6 THEN ‘Inventory Return’ WHEN 7 THEN ‘Inventory Return w/Credit’ END POPTYPE,
        RECEIPTDATE, ” POSTATUS, PONUMBER, POP30300.POPRCTNM, CURNCYID
        FROM POP30300, POP30310
        where POP30300.POPRCTNM = POP30310.POPRCTNM
        AND PONUMBER = @PONUMBER

        Like

  5. Fernando Reply September 10, 2009 at 7:07 pm

    Hi Victoria
    thanks for all the posts you are really helpfull

    could you help me with this?
    well i’am from Peru my GP is in spanish i hope i translate ok the options

    in reports / purchasing / check information
    in the option checks by vendor

    i get a report of all checks of that vendor
    but i need that query in sql because of a flat file generation that i have to do to the IRS of peru (in peru is called SUNAT jeje)

    I did a sql-trace to that report
    and i see that it gets data from PM30200
    but also from PM20000

    all data from PM30200 is in the report of GP
    but only some of PM20000 is in that report
    but i don’t get why just some of PM20000 is in the report and why others not

    i hope you unterstood and help me figured out what the cryteria of the report is

    pardon my english

    thanks a lot

    Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | Dynamics GP SQL View to Show What Check Paid a PODynamics GP SQL View to Show What Check Paid a PO - DynamicAccounting.net - April 21, 2016

    […] on October 17, 2008 by Mark Polino Ooooo! Victoria Yudin has a SQL view that will show what check paid a Purchase Order! Make sure you check it […]

    Like

Leave a comment