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 reciept and invoice.
~~~~~
CREATE VIEW view_Payables_POP_Invoices AS /******************************************************************* view_Payables_POP_Invoices Created on Sep 1, 2010 by Victoria Yudin - Flexible Solutions, Inc. For updates visit http://victoriayudin.com/gp-reports/ Returns Payables Invoices only Updated on Feb 10, 2011 to include GL journal entry number *******************************************************************/ 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 FROM (SELECT VCHRNMBR,VENDORID, DOCDATE, DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DOCTYPE, VOIDED FROM PM20000 UNION ALL SELECT VCHRNMBR,VENDORID, DOCDATE, DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DOCTYPE, VOIDED 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 -- recceipt 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.
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.
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
Thanks again!!
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
Sherry,
Yes, you’ve got the right idea. The actual code would be
PM.DOCAMNT - PM.CURTRXAM-Victoria
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!!
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
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
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
thanks for such a nice script
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?
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
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.
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
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
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
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
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!
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?
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
Victoria,
Another home run.
Leslie
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
Steve,
This is awesome – thank you so much!
-Victoria