Victoria Yudin

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 http://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.