Dynamics GP Payables SQL Views
- All Posted Transactions Open and historical posted payables transactions.
- Apply Information Shows invoices and what checks or credits were applied to them.
- Current Payables Aging Detail Shows one row per open transactions with the default GP aging buckets aged by due date.
- Current Payables Aging Summary Shows one row per vendor with a balance with default GP aging buckets aged by due date.
- GL Distributions for AP Transactions Distributions for all payables transactions – includes posted and unposted.
- Last Activity Shows all vendors with a last activity and created dates.
- Multicompany Open Invoices A working example of how to create a view combining payables transactions from 3 GP companies. Use this with SmartList Builder.
- Multicompany Open Invoices (for Crystal Reports) Same as above, but with no spaces in the field names. Use this with Crystal Reports.
- Payables invoices originating from POP All posted Payables invoices that originated from POP with details on the items purchased.
- Payment Apply Detail Shows payments and what invoices they were applied to.
- Payment Apply Detail and GL Distributions Shows payments, what transactions they were applied to and the GL distributions of the applied to transactions.
- Posted Payments All posted payments.
- Top X Percent of Vendor Data SQL code to show top X percent of vendors invoices or payments for a specified date range.
- Total Payments to 1099 Vendors in the Prior Year Shows one line per 1099 vendor with the total paid to them in the prior calendar year.
- Unposted Checks Shows all unposted checks with the apply detail.
- Vendor Monthly Totals Shows calendar year and month totals for all vendor transactions.
- Vendor Totals by Year Shows calendar year totals for all vendor transactions – results are one row per vendor with the years in columns.
- Vendor Yearly Totals Shows calendar year totals for all vendor transactions – results are one row per vendor per year with the columns being the different possible yearly totals.
- What Check Paid a Purchase Order View showing what payables check number paid a Purchase Order.
For additional reporting tips and SQL code, please visit my GP Reports page.
Code from others that I have found helpful:
- Shawn Dorward – 1099 Year End Edit – from Shawn Dorward, shows 1099 summary information by vendor by year
Victoria Yudin 
Hi Victoria, would you happen to have any payables distribution views containing Analytical Accounting ?
LikeLike
Hi Curtis,
Sorry, I do not. 😦
AA is one of my least favorite modules and I almost never have to work with it (thankfully), so I do not have much code written for it.
-Victoria
LikeLike
Do you happen to have a query that combines POP and Payables together that could give me the activity for a month that I could compare to what actually hit the General Ledger? I have the GL side down, but there are a lot of scenarios with POP that i am not sure i am getting them all. Thanks in advance.
LikeLike
Hi Robyn,
Precisely because there are so many scenarios, this is typically something that is better when created specifically for you, If you’re interested in getting some custom reporting created for this, let me know. Or if you are doing this yourself and have some specific questions, I can try to help.
-Victoria
LikeLike
Hi Victoria,
Great site, great views⦠crazy helpful.
Would you have a view/query that calculates the âRemaining to shipâ quantity:
Any help or guidance would be great!
Thank you,
George
908.892.8947
Northeast Business Systems, Inc.
A Microsoft Dynamics GP Consultancy for more than 25 years
LikeLike
Hi George,
For what kinds of transactions?
-Victoria
LikeLike
Hi Victoria,
Purchase orders⦠Remaining to ship, as a result of PO shipment receipts or PO shipment/Invoice receipts.
Does that make sense? Does that help?
Thanks again for any guidance/help,
George
908.892.8947
Northeast Business Systems, Inc.
A Microsoft Dynamics GP Consultancy for more than 25 years
LikeLike
Hi George,
Unfortunately, I don’t have anything like this readily available. You would need to take the quantities on the PO from the POP10110 table and combine with the quantities received – I typically like to use table POP10500 for this.
-Victoria
LikeLike
I’m looking for a table/field/query that will provide the date an invoice was paid in full and has the invoice amount along with the total paid amount. Does this exist?
LikeLike
Hi Christie,
The date the invoice was paid in full is stored in the DINVPDOF field of the PM30200 table. Invoices will only be in the PM30200 table if they have been fully paid or voided. If an invoice if not fully paid, it will be in the PM20000 table. If it is partially paid, the payment amount will be the difference between the DOCAMNT and the CURTRXAM.
Hope that helps,
-Victoria
LikeLike
Hi Victoria,
I would like to thank you for your Excellent Blog and the material you have provided. It has proved to be an extremely easy task figuring out GP for a beginner like myself. I was wondering, is there any way for me to get the unposted transactions (which I believe are in a specific batch) and the total $ amount associated with the same.
Thank you for your response in advance.
-Ish
LikeLike
Hi Natish,
Unposted Payables transactions can be in 3 different tables, each of these tables will have a batch ID and the transaction amounts:
PM10000 – Work/Unposted Transactions
PM10300 – Work/Unposted Payments
PM10400 – Work/Unposted Manual Payments
-Victoria
LikeLike
Is there a query that will look at all companies (we have 5 separate companies in GP) and report checks paid (company, check #, payee, amount, date)?
LikeLike
Hi Ian,
For 5 companies it’s probably easier to create your own query with the 5 companies hardcoded with a union on the data. Here is an example of doing that for invoices with 3 companies: https://victoriayudin.com/2008/11/08/sql-view-to-show-open-ap-invoices-from-multiple-companies-in-gp/.
-Victoria
LikeLike
Thank you so much, Victoria! You ROCK!!
LikeLike
Is there an AP aging listing as of a point-in-time? For example, I want to know what my listing looked like as of 12/31/16 yet the sql above is pulling from the open table only. I’m wondering if I can pull from all tables and include “where” pstdate is < 2017-01-01. Thanks!
LikeLike
Hi Andrew,
A point in time aging (usually referred to as HATB, or historical aged trial balance) is not stored anywhere in GP and you cannot get it by pulling from all tables and using a cut off date. Because of how documents are applied in GP and how this data is stored in the tables, it’s much more complicated.
If you are interested, this is something I can do as a consulting project. Let me know if you would like to talk about this in more detail.
-Victoria
LikeLike
Hi Victoria,
I am looking for the SQL query to build a Payable Historical Aged Trial Balance. Any ideas?
Thanks
LikeLike
Hi Andrew,
I do have a query for this, but it’s something that took a huge amount of effort to create, so I am not going to be posting for free. I also find that most companies want something slightly different, so I typically find out what is needed and then tailor the code to the specifications for a fee. If you are interested in this, please let me know.
Thanks,
-Victoria
LikeLike
Is there a report where I can do an historical aging that also grabs the distribution codes?
LikeLike
Hi Kenneth,
This is not something I have posted on my blog. It can be created as a custom report for you. Please let me know if you are interested.
-Victoria
LikeLike
Hello,
I’m new to GP and love the references, thanks for that! It is my first week on the job and I’m looking to get an accounts payable invoice history file. I’m looking for the fields; total invoice value, invoice number, vendor number, check number, invoice date, entry date and check/payment date.
From the list of sql views which ones could I start with to put together this custom view?
Thank you,
-Joe
LikeLike
Hi Joe,
I would start with the Apply Information view. That should have almost everything you need.
-Victoria
LikeLike
Thanks for pointing me in the right direction.
LikeLike
Victoria,
I am new to GP and need a query that I can find all open invoices (per vendor) and the details for those invoices. We are implementing a Card Payables system here and I need to send a file over with this information.
Any help would be great!
Thanks!
LikeLike
Hi Simon,
Not sure what kind of detail you’re looking for – I am thinking one of my views on this page already has this information…you just need to find the right one.
-Victoria
LikeLike
Hi Victoria,
Do you know of any existing view/query to get a list of payable batch names by status?
LikeLike
Hi Chris,
What specifically are you looking to do? Batches are not stored in GP once posted, and you can reuse the same batch ID over and over, so if you’re looking to see historical information about batches, that’s not realistic in more cases. If you’re looking for unposted batches, you can see all batches in each GP company in the SY00500 table. You can determine what transaction types are in each batch by a combination of SERIES and BCHSOURC. I have lists of batch statuses on my Company/System Tables page. Hope that helps.
-Victoria
LikeLike
Hi Victoria, We built a win-form/econnect application to import vendor invoices from another system. I wanted to check unposted batches and prevent the same batch name from being used if there was an unposted batch with that name. I’ll check the SY00500 table to get a list of unposted payable batches.
Thanks.
LikeLike
Hi Victoria,
I have some customers that are asking for an AP HATB report in which they could filter by AP account number (to tie to the GL). I don’t know if this could be done with a SQL View or if this needs to be a custom report but I was wondering if you knew of a 3rd party that offers something similar or if this is a request you have gotten and how you resolved it. Any advice you could offer is appreciated. Thanks!
-Amanda
LikeLike
Hi Amanda,
I have created reports similar to this as consulting projects in the past. It’s very difficult to talk about this generically – every company I have worked with on this wants something different. If you are interested in discussing this in more detail, let me know and I will email you directly.
-Victoria
LikeLike
i need the same thing. Payables HATB by Project. just a different roll-up (I think), but I’m getting lost trying to tie the tables from PM back to POP (where the JobNumber is)
LikeLike
btw….this whole HATB for payables is like a Russian nesting doll….a stored proc inside a stored proc, inside a….you get the idea.
it appears the base SP’s are:
pmPrintHATBGetDocuments
pmPrintHATBGetApplyHistory
pmPrintHATBGetApplyWorkOpen
and primarily these use the PM 30300 for history and PM10200 for work open. I just can’t find how to join in a table that has the darn JobNumber.
LikeLike
LOL George!
Love the Russian nesting doll analogy. I personally prefer to write my own stored procedure instead of trying to use what’s in GP already for the HATB. I find the GP ones overly complicated for what I typically want to do.
If you’re interested in talking about this some more, let me know and I will email you directly to set up a time to chat.
-Victoria
LikeLike
Hi Victoria,
I’m trying to get a detail list of what prints on the 1099 forms. 1099s are based on what is paid in the calendar year. I know it uses PM30200 but what is confusing me is that I want all payments (doctype=6) and I want the 1099amnt for 2013 calendar year. But the ten99amnt is stored with the voucher not the check. Since they are in the same file I’m not sure how to link back and get the 1099amnt from the voucher when I’m really pulling check data. I can’t take the entire check amount because sometimes there is something paid on the check that is not 1099 so I really need that 1099amnt field. I tried editing your ‘view_payables_apply_detail’ view but since it is pulling just the payments I can’t get that field since it’s on the voucher. Any hints how to get a full detail report of what is in summary 1099 #?
Thanks,
Laura
LikeLike
Hi Laura,
I just updated this view to include 1099 information – you should be able to just filter on payment dates here. 🙂
-Victoria
LikeLike
Hi Victoria….
I’m using your all ..transactions query… and the data is in history at PM 2000 & PM 30200..right? ( hope that’s right as I’m working off my faulty memory of tables.
LikeLike
Hi Jack,
The check number will be the Document Number for payments made with a check. And the amount will be the Document Amount. Please let me know if I am misunderstanding what you’re asking for.
-Victoria
LikeLike
Hi Victoria,
Thank you so much for posting these queries. They are most helpful. I’ve learned a lot
I was wondering if you could show us how to add 2 more fields to this great query
in history check number and amount..
thanks in advance!
Jack
LikeLike
Hi Jack,
Which query did you want to add the check number and amount to?
-Victoria
LikeLike
Victoria,
Is there any way to tie a cost from an Accounts Payable entry to a customer invoice in Accounts Receivable?
LikeLike
Karen,
Not typically. I know we’ve created customizations for our customers in the past to do things like this, but the answer will really depend on exactly what your requirements are.
-Victoria
LikeLike
Victoria,
I was looking at your Vendor Totals by Year SQL view. That view provides most of the information that was requested by one of my PM users. However, they would also like to add the Vendor Class ID (VNDCLSID) and the Payment Terms ID. Can you help with the code to add those two parameters to your Vendor Totals by Year SQL?
Thank you,
Henry
LikeLike
Henry,
I have added those to the view.
-Victoria
LikeLike
Hi Victoria,
Have you ever received a request to produce a report or SQL view that shows all items included on a Remittance or Check stub? The SQL view that you have available works awesome to put in a Check Number and see the Invoices being paid off but I would like to also see the Credit Memos that are paying off Invoices that were included on the Remittance at the time the check is printed. Any guidance you can provide would be very helpful. Thanks!
LikeLike
Hi Amanda,
While I have had this request many times, this is not possible until GP 2013 because GP does not store this information. Starting with GP 2013, you should be able to get this out-of-the-box. If you’re on an earlier version of GP, the only way to find this information is to keep a copy of the check stub/remittance.
-Victoria
LikeLike
Hi Victoria, we are on Dynamics 2018 and I’m trying to pull exactly this info for a bank file feed. It’s simple enough to pull the invoices on a check via PM30200 and PM30300 but I’m not finding where they’ve stored invoices that were completely covered by a credit memo. There must be some tie back to that check voucher number, right? If you could point me in the right direction, I’d be most appreciative! Thanks!
LikeLike
Hi Victoria – I am looking for a historical aged trial balance report that would include specific modifications. Is this something you already have code for or is it something that you could be commissioned to do?
Thank you,
Erin
LikeLike
Hi Erin,
It’s not something I have code published for, but I have done a number of historical trial balance reports, and is something I can definitely help with as a consulting project. I will email you with my contact information so we can talk about this in more detail.
Thanks,
-Victoria
LikeLike
Hi Victoria,
Thank you so much for posting these queries. They are most helpful. I just have a question regarding the Vendor Monthly Totals. When I run this for a specific period I cant’ seem to tie it to the PM Transaction History Summary Report in GP. Can you please tell me what this query is pulling from or what amounts it is including? I need to reconcile it so I’m sure it’s pulling all that I need.
Thank you!
Rita
LikeLike
Hi Rita,
The results for my Vendor Monthly Totals code should correspond to the Vendor Period Summary Inquiry window (Inquiry | Purchasing | Vendor Period) with the Display selected as Calendar. Looking at the tables used on the PM Transaction History Summary Report, I would actually not expect those numbers to match for active vendors, mainly because the GP report is only looking at the transaction detail for historical transactions and not including posted but unpaid transactions. So unless you’re looking at very old data or a vendor that has nothing but historical transactions, these results should not match.
Another reason that the results could be off is that my code is pulling from summary tables, which might be incorrect. Once in a while, when a posting process gets stuck, the summary tables do not get updated properly. To fix this you can use the Reconcile Utility in payables.
-Victoria
LikeLike
Thank you very much for your quick response.
I’m looking for the easiest way to run a report that provides payables invoices by month by vendor, not including payments. Subtotaled by Vendor if possible. Can you suggest which report may provide that?
Thank you
LikeLike
Rita,
I don’t think I have anything exactly like that. And if you want a list of invoices plus a subtotal for each vendor, then you will need to use a reporting tool of some kind to display it that way. Or are you talking about only showing the subtotals per vendor, without the details?
-Victoria
LikeLike
Victoria,
I would need the transactions, then the subtotal per vendor. I’m thinking a reporting tool is going to be needed. I’m going to Convergence next week. I’ll look for something. Do you have any suggestion? Again, thank you very much for your assistance.
Rita
LikeLike
Rita,
In my experience the 2 most used tools for this kind of reporting are SQL Server Reporting Services (SSRS) and Crystal Reports. Both have pros and cons, depending on many different factors beyond the need for just one report like this. If you have specific questions, let me know and I will try to help. If you’re looking for someone to help create this for you and/or teach you how to do it, that is something my company offers.
-Victoria
LikeLike
Victoria,
SSRS is probably what we will use. Thank you for your assistance.
Rita
LikeLike
I love Crystal Reports. best reporting tool out there….I wish Microsoft would have bought them and not made the funky report builder app they have now.
LikeLike
George,
Amen!
-Victoria
LikeLike
Hi Victoria,
Thank you for all your helpful queries and break downs. I do have a question…
I need to create a sql view to show all invoices for a batch of checks. How can I go about?
LikeLike
Meli,
Posted checks? Or unposted checks?
-Victoria
LikeLike
Victoria,
Thanks for the queires do you have any queries where the output can be tied out to the Aging report
Farman
LikeLike
Farman,
The Current Payables Aging Summary view should tie to the current open aging. Since there are many different reports and options for each, you need to be careful about what you are selecting and comparing.
-Victoria
LikeLike
Hi Victoria,
I changed the tax type for a particular vendor class from ‘Not a 1099 Vendor’ to ‘Miscellaneous’. I now need to populate the 1099 amounts in the ‘Cards > Purchasing > 1099 Details’ module for hundreds of vendors. I’m guessing the only tables needed are PM00200 and PM00202, joined by VENDORID, and populating TEN99ALIF with AMTPDLIF for the aforementioned tax type. Will this work and if so, will this be sufficient for the printing of 1099’s as well?
We are running GP 10.0.
Please let me know if I’m close or way off.
Thank you!
– Bob
LikeLike
Hi Bob,
I have done done something like this myself, so I would not feel comfortable telling someone else it’s ok. Have you tried posting on the GP Community forum to see if anyone has done this? Another option may be the 1099 Modifier tool – it’s not free, but it is more secure than changing data directly in the tables.
-Victoria
LikeLike
Hi, Looking for a script that would change an AP document number in all applicable tables after it is posted and paid. Assume it hits MANY tables. Do you have such a script?
Thanks!
LikeLike
Hi Lisa,
Sorry, I do not have a script for this. You are right that it would hit MANY tables and not just in the AP module. I would be very careful getting a script from someone for something like this, especially if you have additional modules or 3rd party products that integrate with AP.
-Victoria
LikeLike
Hi Victoria,
I have an issue with the Payables Transaction Edit List. The batch total does not match the purchase amounts. The purchase amount is the correct number. My question is where does GP obtain the batch total amount (which is located at the top on the Payables Transaction Edit List report). What could account for the difference? Thank you.
LikeLike
Kevin,
Batch totals can be incorrect for many reasons (like multiple people entering or viewing transactions in a batch at the same time, or imports). This should not normally cause a problem unless you have turned on batch verification in Posting Setup. When you post a batch, each transaction is looked at individually, batch totals are not used for any processing or calculations.
-Victoria
LikeLike
Hi Victoria,
I am trying to write a SQL script for AP Historical Trial Balance. We have over 30 companies in GP and would like to run 1 consolidated TB report. Do you have SQL script which can accomplish this?
I believe I’ve almost there(98% of the vendors balance match), its the final tweeking thats getting really difficult.
Any help is will really appreciated.
Thanks,
Nisha
LikeLike
Nisha,
This might help: http://www.box.net/shared/1bcc214vjr.
-Victoria
LikeLike
What tables does the Purchasing->Reports->Historical Aging Trial Balance-Detail (GP 10) pull from? The Report is showing payment voucher numbers that do not show up in the above scripts for HATB, All Posted Transactions, All Posted Payments.
LikeLike
I am not sure what scripts you’re looking at specifically, but the Historical Aged Trial Balance report in GP should be using: PM20000, PM30200, PM10200 and PM30300. Also PM00200, but only for the vendor info.
-Victoria
LikeLike
Hi Victoria,
Do you have any SQL scripts that would allow me to upload Payables Receivings Transactions? I would love if we could purchase either Smart Connect or the Scribe product but it’s just not in our budget right now.
Thanks!
LikeLike
Hi Kat,
I do not have any scripts for this, nor would I recommend using SQL scripts to upload any transactional data. 😦
-Victoria
LikeLike
Victoria – some of your stuff is SO close to what I want – maybe I am just missing the one I DO want. Essentially I want to pull GL entires for 2010 (or a selectable year) and for an account range – then have that link up to which Purchase Orders the payments go with so that I can provide the users with how much has been spent against which POs (and then other info about the PO & Vendor of course) – I was looking at linking GL20000 with POP10500 using the RCTnums… thoughts?
LikeLike
Jim,
If all you want is information about PO’s and which were paid, etc., why involve the GL at all? Have you looked at my What Check Paid a Purchase Order view? Perhaps that’s a good starting point?
If that’s not what you’re after, then you will have to give me more detail. At first, don’t worry so much about what tables to use or how to link them – what’s more important is understanding exactly what the purpose of the report is and what columns of data are needed.
-Victoria
LikeLike
WONDERFUL start – Thanks!
our GL accounts are set up so that Capitol accounts are in one range – departments in another range (in actuality another part of the key) – and am thinking a bit long term with a view / query & an SSRS report. with a number of parameters.
SO – I am thinking I will link your query/view up with the POP30310 to get the detail and the account index – then I have detail totals AND can link all the way back to the GL – how far off am I? Granted I would need to rename a few of your fields since they are more header than detail, but it looks like a good idea for someone that does not do a bunch in the PO tables… I appreciate the thoughts!
LikeLike
Hi Jason,
Unfortunately, GP does not have any way of applying a payment to a Purchase Order. This is definitely something that I have seen asked before, but there is just no way to do it out-of-the-box. Payments can only be applies to posted Payables transactions. If you need the system to actually reflect this in the PO total, you’re going to need to look at a customization. If you simply need to be able to see that there was a payment, or print it on a report, perhaps a custom PO report would be enough.
-Victoria
LikeLike
Hi Victoria,
I had one question that any tool is there to upload bulk payable transaction into Dynamic GP.
LikeLike
Helna,
Yes, there are a number of tools, actually. The top 2 are Integration Manager and eConnect.
-Victoria
LikeLike
Victoria,
Hello. You have great information on your site.
I have one question. We are trying to apply a partial payment to a purchase order. We pay most of our vendors 10% when they start the work and 90% when they ship it. We need to be able to apply that 10% payment to the purchase order when we pay it and have it deduct off the total outstanding PO total. Is there a way to do this?
Any information that you can give me would be greatly appreaciated.
Thanks,
Jason
LikeLike