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 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,
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