Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Complete Invoice detail and Payment detail needed - AP

Complete Invoice detail and Payment detail needed - AP

Complete Invoice detail and Payment detail needed - AP

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

RE: Complete Invoice detail and Payment detail needed - AP

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:
blog: http://victoriayudin.com

RE: Complete Invoice detail and Payment detail needed - AP

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close