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.
- 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.
- 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.
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!
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
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
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
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
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
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
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
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
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
Victoria,
SSRS is probably what we will use. Thank you for your assistance.
Rita
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?
Meli,
Posted checks? Or unposted checks?
-Victoria
Victoria,
Thanks for the queires do you have any queries where the output can be tied out to the Aging report
Farman
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
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
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
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!
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
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.
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
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
Nisha,
This might help: http://www.box.net/shared/1bcc214vjr.
-Victoria
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!
Hi Kat,
I do not have any scripts for this, nor would I recommend using SQL scripts to upload any transactional data.
-Victoria
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?
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
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!
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
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