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