September 1, 2010 
This Dynamics GP SQL view originated from a request on the Dynamics GP customer forum, but is also something that I can see being useful in a variety of situations. It returns all posted Payables invoices that came from the Purchase Order Processing module with details of the items that were received on each invoice as well as the GL journal entry number for each POP receipt and invoice.
~~~~~
CREATE VIEW view_Payables_POP_Invoices AS /******************************************************************* view_Payables_POP_Invoices Created Sep 1, 2010 by Victoria Yudin - Flexible Solutions, Inc. For updates visit https://victoriayudin.com/gp-reports/ Returns Payables Invoices only Updated Feb 10, 2011 to include GL journal entry number Updated Jun 20, 2016 to add date invoice was paid *******************************************************************/ SELECT PM.VCHRNMBR Voucher_Number, PM.VENDORID Vendor_ID, POP.VENDNAME Vendor_Name, PM.DOCDATE Invoice_Date, PM.DUEDATE Due_Date, PM.DOCNUMBR Invoice_Number, PM.DOCAMNT Invoice_Amount, PM.CURTRXAM Unpaid_Amount, CASE PM.VOIDED WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END Voided, POP.POPRCTNM POP_Receipt_Number, CASE POP.POPTYPE WHEN 2 THEN 'Invoice' WHEN 3 THEN 'Shipment/Invoice' END Receipt_Type, POP.receiptdate Receipt_Date, I.RCPTLNNM Receipt_Line_Num, I.PONUMBER PO_Number, I.ITEMNMBR Item_Number, I.ITEMDESC Item_Description, R.QTYINVCD Quantity_Invoiced, R.UOFM U_of_M, I.UNITCOST Unit_Cost, I.EXTDCOST Extended_Cost, I.LOCNCODE Site_ID, CASE I.NONINVEN WHEN 0 THEN 'Inventory Item' WHEN 1 THEN 'Non-Inventory Item' END Item_Type, G.JRNENTRY Journal_Entry_Number, PM.DINVPDOF Date_Invoice_Was_Paid FROM (SELECT VCHRNMBR,VENDORID, DOCDATE, DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DOCTYPE, VOIDED, DINVPDOF FROM PM20000 UNION ALL SELECT VCHRNMBR,VENDORID, DOCDATE, DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DOCTYPE, VOIDED, DINVPDOF FROM PM30200) PM -- PM trx INNER JOIN POP30300 POP -- POP header ON PM.VENDORID = POP.VENDORID AND PM.DOCNUMBR = POP.VNDDOCNM INNER JOIN POP30310 I -- POP detail ON I.POPRCTNM = POP.POPRCTNM INNER JOIN POP10500 R -- receipt details ON R.POPRCTNM = I.POPRCTNM AND R.RCPTLNNM = I.RCPTLNNM LEFT OUTER JOIN (SELECT DISTINCT SOURCDOC, JRNENTRY, ORDOCNUM, ORMSTRID FROM GL20000 UNION SELECT DISTINCT SOURCDOC, JRNENTRY, ORDOCNUM, ORMSTRID FROM GL30000 UNION SELECT DISTINCT SOURCDOC, a.JRNENTRY, ORDOCNUM, ORMSTRID FROM GL10001 a INNER JOIN GL10000 b ON a.JRNENTRY = b.JRNENTRY) G --GL entries ON G.ORDOCNUM = R.POPRCTNM AND G.ORMSTRID = R.VENDORID AND G.SOURCDOC in ('POIVC','RECVG') WHERE PM.DOCTYPE = 1 -- invoices only AND POP.POPTYPE in (2,3) -- invoices only /** 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_Payables_POP_Invoices TO DYNGRP
~~~~~
For more Dynamics GP SQL scripts take a look at the GP Reports page on this blog.
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Victoria Yudin 
Hi Victoria, thanks for this, it was very helpful! However, I have come across an issue with the relationships between tables and I couldn’t quite get it right so I’m hoping you are able to shed some light on it! Any input would be much appreciated!
To give you some context, my aim is to get extract all Payments per VENDORID, then group them by the PO Number and get the sum of the amount. My initial SQL was this:
SELECT
P3.PORDNMBR,
SUM(ISNULL(P2.APFRMAPLYAMT, P1.DOCAMNT)) AS TOTAL_PAYMENT
FROM
PM30200 P1
LEFT OUTER JOIN PM30300 P2 ON P2.APFRDCNM = P1.DOCNUMBR AND P2.VENDORID = P1.VENDORID
LEFT OUTER JOIN PM30200 P3 ON P3.DOCNUMBR = P2.APTODCNM AND P3.VENDORID = P1.VENDORID
WHERE
P1.DOCTYPE IN (5, 6) — Only calculate the sum of entries with doctype (5, 6 = Payment)
AND P1.VOIDED = 0
AND P1.VENDORID = ‘XXX’
GROUP BY
P3.PORDNMBR
However, what I found was that the PORDNMBR isn’t reliable for extracting the PO Number as it doesn’t always contain the data we want – sometimes it has “*Multiple”, which indicates that the invoice has more than one payment or credit applied to it.
So then, I stumbled across this article of yours, which seems to be very similar to what I want – except for the fact that I’m looking at Payments rather than invoices. Following your script above, I came up with the following:
SELECT
P5.PONUMBER,
SUM(ISNULL(P2.APFRMAPLYAMT, P1.DOCAMNT)) AS TOTAL_PAYMENT
FROM
PM30200 P1
INNER JOIN PM30300 P2 ON P2.APFRDCNM = P1.DOCNUMBR AND P2.VENDORID = P1.VENDORID
INNER JOIN PM30200 P3 ON P3.DOCNUMBR = P2.APTODCNM AND P3.VENDORID = P1.VENDORID
— Get the correct PO Number for each Payment.
INNER JOIN POP30300 P4 ON P4.VENDORID = P1.VENDORID AND P4.VNDDOCNM = P3.DOCNUMBR
INNER JOIN POP30310 P5 ON P5.POPRCTNM = P4.POPRCTNM
WHERE
P1.DOCTYPE IN (5, 6) — Only calculate the sum of entries with doctype (5, 6 = Payment)
AND P1.VOIDED = 0
AND P1.VENDORID = ‘XXX’
GROUP BY
P5.PONUMBER
POP30300 joins nicely with PM30200 via the VNDDOCNM and DOCNUMBR – this has a direct 1-1 relationship. However, the problem I have is when joining the POP30310 via the POPRCTNM. If I just look at the POP30310 with the relevant POPRCTNM values, it will something like this:
POPRCTNM RCPTLNNM PONUMBER
AC2 16384 XXX-1-1
AC3 16384 XXX-1-1
AC1 16384 XXX-1-1
AC4 16384 XXX-1-2
AC3 32768 XXX-1-2
AC5 16384 XXX-1-2
AC5 32768 XXX-1-3
AC6 16384 XXX-1-3
AC6 32768 XXX-1-4
AC6 49152 XXX-1-5
From the results above, the PO Number XXX-1-1 will be have the sum of values for AC2, AC3 and AC1 to which ever values it’s connected to the Payment tables which isn’t correct.
Do you know if there is a way around this – or the bigger question is if this is even possible to do?
I look forward to your response.
LikeLike
Gonzalo,
Did you try this code instead? https://victoriayudin.com/2008/10/17/sql-view-what-check-paid-purchase-order/
That might work better.
-Victoria
LikeLike
Victoria, thanks for this – managed to figure out the problem I had.
OK, for the sake of argument, say in PM30300, I have my 1 line of payment of 30,000 and I want to know which is the related PO Number for this in the POP tables – my problem was I was always thinking there is only 1 PO number consisting this payment. It may be linked to 3 POs for instance of 15,000 , 10,000 and 5,000 with all different PO numbers.
My problem was that because of my incorrect assumption, I would be getting a sum of 90,000 in my SQL query rather than 30,000 for that payment. What I really needed to do so subtract the correct values against the 30,000 and luckily these are stored in POP10500 within the QTYINVCD column or for the POP30310 in the EXTDCOST column.
Thanks again for your response and of course for your various SQL queries!
LikeLike
Victoria … such helpful posts! Wanted to let you know I had to take the underscore out of ‘Document_Type’ for the view above. When using this SQL view to create a new SmartList with SmartList Designer (GP 2013 SP2), GP does some additional manipulation of the SQL script (looked at T-SQL Query tab) and it caused an issue for the field Document)Type only (go figure). Try it! Reporting such bugs can be a pain with Microsoft, so not sure I’ll be following up on this, but wanted to let you and your fans know.
P.S. I made sure your credit was given in the SQL view …
LikeLike
Hi Denni,
Thank you very much for letting me know! I am hearing all sorts of things like this about the new SmartList Designer. I have to be honest – beyond just testing a few things and looking quickly at it, I don’t use it yet. All my customers that need custom SmartLists already have SmartList Builder, which is much more stable and doesn’t have these quirks. I am sure MS will eventually get SmartList Designer up to par, but for now, I think I am sticking with SLB. 🙂
-Victoria
LikeLike
Thanks for your very useful website Victoria.
I am looking for a PO report that looks at invoice numbers that are assigned to PO’s – basically, looking at the data from the other side, compared with your query above.
The goal is to manage PO’s in order to ensure that there is no duplication.
I have an existing PO status report but I just need to add the invoice number to it. So far this has proved very difficult, mainly because POP30310 stores the PO Number but not the Invoice Number and POP30300 stores the Invoice Number and not the PO Number.
Is there a better way of approaching this?
This is what I have so far:
select
‘PO10110/10100’ AS [Source],
POWH.[DOCDATE] as ‘Doc Date’,
POWH.[HOLD] as ‘Hold?’,
POWH.[Flags] as ‘Flags?’,
CASE
WHEN POWL.NONINVEN = 1 THEN ‘NON INV’
WHEN POWL.NONINVEN = 0 THEN ‘INV’
ELSE ‘OTHER’
END
AS [INV?],
RTRIM(POWL.[PONUMBER]) as ‘PO Number’,
rtrim(POWL.[ITEMNMBR]) as ‘Item Number’,
rtrim(POWL.ITEMDESC) as ‘ItemDesc’,
POWL.LineNumber,
rtrim(POWL.[VENDORID]) as ‘Vendor ID’,
rtrim(POWL.[LOCNCODE]) as ‘Location Code’,
POWL.[QTYORDER] as ‘QTY Ordered’,
POWL.[QTYCANCE] as ‘QTY Cancelled’,
POWL.[ORUNTCST] as ‘Orig Unit Cost’,
POWL.[UNITCOST] as ‘Unit Cost’,
[POL_Status]=
case
when POWL.POLNESTA=1 then ‘New’
when POWL.POLNESTA=2 then ‘Released’
when POWL.POLNESTA=3 then ‘Change Order’
when POWL.POLNESTA=4 then ‘Received’
when POWL.POLNESTA=5 then ‘Closed’
when POWL.POLNESTA=6 then ‘Canceled’
end,
POWL.[REQDATE] as ‘Req Date’,
POWL.[PRMDATE] as ‘Promised Date’,
POWL.[PRMSHPDTE] as ‘Promised Ship Date’,
POWL.[ORIGPRMDATE] as ‘Orig Promised Date’,
POWL.[FSTRCPTDT] as ‘First Rec Date’,
POWL.[LSTRCPTDT] as ‘Last Rec Date’,
POWL.RELEASE as ‘Release’,
rtrim(POWL.[CURNCYID]) as ‘Currency ID’,
POWL.XCHGRATE as ExRate,
POWL.[OREXTCST] as ‘Orig Cost’,
POWL.[EXTDCOST] as ‘Cost’,
POWH.[DUEDATE] as ‘Due Date’,
rtrim(POWL.[REQSTDBY]) as ‘Notes (REQSTDBY)’,
rtrim(POWL.[COMMNTID]) as ‘Notes (COMMNTID)’,
RTRIM(COMMENT.[COMMNTID]) AS CommentID,
(COMMENT.[COMMENT_1]) AS Comment1,
(COMMENT.[COMMENT_2]) AS Comment2,
(COMMENT.[COMMENT_3]) AS Comment3,
(COMMENT.[COMMENT_4]) AS Comment4,
(COMMENT.[CMMTTEXT]) AS CommentTXT
FROM
[POP10110] POWL –Purchase Order Work Line
LEFT OUTER JOIN
[POP10100] as POWH –Purchase Order Work Header
ON
POWL.[PONUMBER] = POWH.[PONUMBER]
LEFT OUTER JOIN
[IV00101] as IV
ON POWL.[ITEMNMBR] = IV.[ITEMNMBR]
LEFT OUTER JOIN
POP10150 COMMENT
ON COMMENT.POPNUMBE = POWH.PONUMBER
WHERE POWH.STATGRP 0 –NOT VOIDED
UNION ALL
select
‘PO30110/30100’ AS [Source],
POHH.[DOCDATE] as ‘Doc Date’,
0 as [Hold?],
POHH.[Flags] as ‘Flags?’,
CASE
WHEN POHL.NONINVEN = 1 THEN ‘NON INV’
WHEN POHL.NONINVEN = 0 THEN ‘INV’
ELSE ‘OTHER’
END
AS [INV?],
RTRIM(POHL.[PONUMBER]) as ‘PO Number’,
rtrim(POHL.[ITEMNMBR]) as ‘Item Number’,
rtrim(POHL.ITEMDESC) as ‘ItemDesc’,
POHL.LineNumber,
rtrim(POHL.[VENDORID]) as ‘Vendor ID’,
rtrim(POHL.[LOCNCODE]) as ‘Location Code’,
POHL.[QTYORDER] as ‘QTY Ordered’,
POHL.[QTYCANCE] as ‘QTY Cancelled’,
POHL.[ORUNTCST] as ‘Orig Unit Cost’,
POHL.[UNITCOST] as ‘Unit Cost’,
[POL_Status]=
case
when POHL.POLNESTA=1 then ‘New’
when POHL.POLNESTA=2 then ‘Released’
when POHL.POLNESTA=3 then ‘Change Order’
when POHL.POLNESTA=4 then ‘Received’
when POHL.POLNESTA=5 then ‘Closed’
when POHL.POLNESTA=6 then ‘Canceled’
end,
POHL.[REQDATE] as ‘Req Date’,
POHL.[PRMDATE] as ‘Promised Date’,
POHL.[PRMSHPDTE] as ‘Promised Ship Date’,
POHL.[ORIGPRMDATE] as ‘Orig Promised Date’,
POHL.[FSTRCPTDT] as ‘First Rec Date’,
POHL.[LSTRCPTDT] as ‘Last Rec Date’,
POHL.RELEASE as ‘Release’,
NULL as [Currency ID],
NULL as [ExRate],
POHL.[OREXTCST] as ‘Orig Cost’,
POHL.[EXTDCOST] as ‘Cost’,
POHH.[DUEDATE] as ‘Due Date’,
rtrim(POHL.[REQSTDBY]) as ‘Notes (REQSTDBY)’,
rtrim(POHL.[COMMNTID]) as ‘Notes (COMMNTID)’,
RTRIM(COMMENT.COMMNTID) AS CommentID,
(COMMENT.COMMENT_1) AS Comment1,
(COMMENT.COMMENT_2) AS Comment2,
(COMMENT.COMMENT_3) AS Comment3,
(COMMENT.COMMENT_4) AS Comment4,
(COMMENT.CMMTTEXT) AS CommentTXT
FROM
[POP30110] POHL –Purchase Order History Line
LEFT OUTER JOIN
[POP30100] as POHH –Purchase Order History Header
ON
POHL.[PONUMBER] = POHH.[PONUMBER]
LEFT OUTER JOIN
[IV00101] as IV
ON POHL.[ITEMNMBR] = IV.[ITEMNMBR]
LEFT OUTER JOIN
POP10150 COMMENT
ON COMMENT.POPNUMBE = POHH.PONUMBER
WHERE POHH.STATGRP 0 –NOT VOIDED
LikeLike
Hi Andrew,
I think you need to do a 2-step link. First from the PO to the receipt, then from the receipt to the Invoice. Maybe something like:
left outer join
POP10500 PR — POP Receipt
on POWL.PONUMBER = PR.PONUMBER
and POWL.ORD = PR.POLNENUM
and PR.POPTYPE in (2,3)
left outer join
POP30300 INV –POP Invoices
on INV.POPTYPE in (2,3)
and PR.POPRCTNM = INV.POPRCTNM
The invoice would then be INV.VNDDOCNM. I tried it on a few of our POs and it worked, hopefully this gets you closer to what you need. Please keep in mind that if you have multiple or partial POs being received on the same receipt and multiple or partial receipts being invoices on the same invoice, you may easily get duplication of data because of this type of linking.
-Victoria
LikeLike
Hi Victoria – another great script. Are you able to enter in the Account Number and Account Name as well? I’ve tried but had no luck. Thx!
LikeLike
Jay, you could combine this script with my GL Distributions for AP Transactions script to accomplish that. If you have a specific question about it, let me know. I also offer custom report (or SQL code) creation, let me know if you’re interested in something like that.
-Victoria
LikeLike
Hi Victoria,
I love your blog!! I need to report on the tax information of the payables invoice such as the Tax Stae and the percentage. Cna you shed some light on what table I should query?
Thanks!
Anna
LikeLike
Anna,
I would start with table TX30000.
-Victoria
LikeLike
When I look at the vendor yearly summary Inquiry, it doesn’t tie out. I ran the above query for a vendor for all of 2012 and looked the vendor yearly summary inquiry for 2012 and there are differences in the amount billed. I assume that I am not understanding something.
LikeLike
Jason,
This is a view for payables invoices that originated from the POP module. Typically there will be some invoices (and other transactions, like credit memos) entered directly in the Payables module, so this view will only give you a subset of what will be shown on the Vendor Yearly Summary Inquiry window. Also, you want to be careful on that window, as the data may differ depending on what you have in the Summary View field.
Hope that helps.
-Victoria
LikeLike
Thanks again!!
LikeLike
So, using this above if I want to get the amount paid for the invoice coming from a PO, can I just take PM.DOCAMNT Invoice_Amount – M.CURTRXAM Unpaid_Amount?
Thanks
LikeLike
Sherry,
Yes, you’ve got the right idea. The actual code would be
PM.DOCAMNT - PM.CURTRXAM-Victoria
LikeLike
Thanks so much for the help! I am trying to help a client report on all payments made regardless of whether a PO was created or not. I have a whole module using the POP tables. In a new report based on purely payments, we are using the PM30200 table for the core transaction. The only peice missing was the item number for the transaction. He would like to report on that also so I used parts of this query to join the POP30300 and POP30310 table using the joing conditions you listed to the original PM30200. I only need the ids for the transactions because it is a staging table for an OLAP database.
Thanks in advance!!
LikeLike
Sherry,
When I hear requests like this I get worried, because GP does not have a way to link data like this, there is no direct/clear link between items in POP and payments in payables. As soon as you have one receipt or invoice for multiple or partial PO’s, or a credit (or return) applied to an invoice, or a partial payment, any logic that shows a link between payments and line items will not be 100%. That said…theoretically you can at least start with a list of all payments and how they were applied (something like my Payment Apply Detail view)…and then have a detail/drilldown or link to the PO(s) if the invoice is linked to a PO. You might also find my What Check Paid a Purchase Order view useful.
-Victoria
LikeLike
INNER JOIN POP30300 POP — POP header
ON PM.VENDORID = POP.VENDORID
AND PM.DOCNUMBR = POP.VNDDOCNM
The second clause in the statement from the query above only finds a few matches in my data. If I remove both and do the join on the VCHRNMBR, i have many more matches. Can you possibly explain why a pm.DocNumbr wouldn’t match a pop.vnddocnm and is my change creating bad data?
Thanks
LikeLike
Hi Sherry,
This view is meant to only return payables invoices that originated in the Purchase Order Processing module. It’s entirely possible that you only have a few transactions in your system that match this criteria because you’re not really using the POP module and most or your invoices originate directly in the Payables module. The line you’re looking to take out is critical to the logic and will return incorrect data, as well as duplicate data, so I do not recommend this.
Can you let me know what you’re looking to report on and perhaps I can suggest a different approach?
-Victoria
LikeLike
thanks for such a nice script
LikeLike
Hi Victoria,
This SQL Query is great and saved me a lot of time as I am pretty new to GP tables. The question is, I also need to include Unposted transactions in this report and I figured I can join PM10000 table as well. But the problem is I don’t see the Unposted transactions in that table at all. Is there any other table which I can use?
LikeLike
Hi Sharath,
When a POP invoice is posted in GP, it automatically goes to the PM20000 table, it never hits the PM10000 – so that’s not going to be useful for this.
Just to make sure I understand – you want to include unposted POP invoices? In that case, you are can look in the POP10300 and POP10310 tables.
If you’re looking for something else, please write back with more details.
-Victoria
LikeLike
That’s exactly what I wanted. I was able to use those tables and create a view. I also referred to “What Check Paid a Purchase Order” and was able to get the Payment Number of Historical transactions. Thanks a ton. I have now got a good understanding of GP database because of your blogs.
LikeLike
Victoria Excellent! This helps save time researching thank you! Is there a way to link this to the GL? What I mean by that is it would be awesome to have a view that balances back to the GL transactions, but instead of listing one invoice amount in the GL account, to replace that one amount with the details(one or more lines of the PO detail) so that users could review the details in Excel say a pivot table instead of linking over to GP and clicking through a bunch of screens to drill down for each transaction. Does that make sense?
Thank you
LikeLike
Hi Stephen,
It does make sense, however, I don’t think this can be easily done…
Part of the problem in doing what you’re describing is that GP does not track GL distributions per line item. For example, if you have 5 line items, 2 with one GL inventory account, the other 3 with a different GL inventory account, you will see 2 lines for the inventory accounts in the GL, not 5.
Another problem with showing this would be the fact that for every GL entry you will have other accounts involved. So for one payables transaction, you may have 10 line items on the PO(s) and 6 GL distributions and no true way to relate them. In that example, doing this on one view, you would get 60 lines. So I am not sure that this would be the best approach.
You may need to delve into the business requirements a little more to see what exactly users are trying to accompish…it may be that they actually have a few different separate needs that will be best met by several ‘smaller’ reports.
-Victoria
LikeLike
Hi Victoria
Like Stephen, we have been asked by our user community to create a SmartList that would return the following information:
Purchase Order Number, Receipt Number, Vendor ID, Vendor Name, Voucher Number, Invoice Number and GL Journal Entry #
The users do not seem too concerned about the GL distributions or amount fields because their primary goal is to get back the Vendor Document (Invoice) Number and the GL journal entry number. It would be ideal if we could have a way to return the GL journal entry number from the GL header tables and included in your …Payables invoices originating from POP… view.
Thank you
LikeLike
Hi Kristie,
I just updated the code to include the GL entry number. I did some testing and it seems to work with my data, please try it and let me know if this works for you.
-Victoria
LikeLike
Victoria – I cannot thank you enough for the GL additions you made to this view. First pass at the data would indicate it’s exactly what the users want. I’m waiting on the users’ feedback for confirmation, but wanted to let you know it is deployed and looks to be what they asked for.
You’re a gem!
LikeLike
Victoria, thanks for this SQL. It’s great to have something that makes “sense” of the POP invoices and have a join. One question. Is there any tips or tricks info you are aware of regarding configuration of “go to” buttons in Smart List Builder?
LikeLike
Hi Chuck,
I do not have any tips or tricks for the Go To buttons, sorry. You might have better luck asking a broader audience on the GP Customer Forum.
-Victoria
LikeLike
Victoria,
Another home run.
Leslie
LikeLike
This is a great one! I couldn’t resist trying it out. I created this video showing what I did: http://www.youtube.com/watch?v=y2m2NE4w4-s
LikeLike
Steve,
This is awesome – thank you so much!
-Victoria
LikeLike