Complete Invoice detail and Payment detail needed - AP

[jcw12000]

Hi Guys

I am new to the GP system.

What I need is a SQL query or report that will give me all AP invoices (paid and unpaid) with all its detail incl tax and payment detail. It would be nice if I could get the corresponding GL detail of the TAX. All the detail needs to be in one line. Only header detail is needed.

Anyone have something like this????

 

[VictoriaYudin]

I probably have parts of this in various code I have posted here:

http://victoriayudin.com/gp-reports/payables-sql-views/.

I don't think I have anything that will give you all the information you're mentioning, but you should be able to get pretty close and then add as needed. I would probably recommend starting with this one:

http://victoriayudin.com/2009/10/12/sql-view-for-all-posted-payables-transactions-in-dynamics-gp/.

Getting all the information on one line could be difficult because depending on what you want to show there may be multiple one-to-many relationships. For example:

1. GL detail of the tax - there could be multiple GL accounts per Tax Schedule, so you would need to decide how to show this on one line. If the answer is 'we always have one account' and you decide to code it that way, make sure to code it in such a way that it will not break if 2 years down the road this assumption is not true anymore.

2. Payment detail - multiple payments/credits/returns can be applied to one invoice. If all you want to show is a total applied column, or even separate it out by total payment, total credits, total returns...that would work. However, if you want to show check numbers or credit memo numbers, you would need to decide how to deal with the possibility of multiple payments/credits/returns applied to one invoice.

Victoria Yudin
Dynamics GP MVP 2005 - 2011
Use Crystal Reports and SSRS with GP:

http://www.GPReportsViewer.com/gpreports.html

blog:

http://victoriayudin.com

 

[jcw12000]

It is fine to show an invoice transaction more then once if there are more than one payment for the invoice transaction. I only want to show the input tax of the invoice transaction which in my case is only one GL account