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.
Hi Victoria, I checked out your script. It seems to be duplicating the data if the Receipt had multiple lines.
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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!
LikeLike
Victoria,
Anyway to link this view to show not only the receipt for the invoice match, but the shipment receipt also?
tom
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
FAC,
Thanks for sharing this code with us.
-Victoria
LikeLike
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
LikeLike
Fernando,
I just posted a view for all payables payments, hopefully that will help you.
-Victoria
LikeLike