briefcase invoice

SQL view for current Payables aging in Dynamics GP


Over the past few years I have had several requests for a summary current Payables aging report that can be easily exported into Excel. Yes, you can play with the Report Writer aging report to take out the headers and make it export to Excel, but sometimes there are other reasons for wanting a report outside of Report Writer.

Below is a script to create a view for this. It is only looking at functional currency and will return one row per vendor with a balance. I am hard-coding the aging using the default aging setup installed with GP, which is aging by due date and using the following buckets:

  • Current
  • 31 to 60 Days
  • 61 to 90 Days
  • 91 and Over

If you would like to use different aging buckets, just follow the examples in my code.

create view view_Current_Payables_Aging_Summary
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Current_Payables_Aging_Summary
-- Created Sep 30, 2011 by Victoria Yudin,
--    Flexible Solutions, Inc.
-- For updates please see
--    http://victoriayudin.com/gp-reports/
-- Shows current AP aging
-- Functional currency only
-- Updated on Jan 25, 2012 to fix aging buckets to use
--    due dates
-- Updated Apr 25, 2013 to fix aging buckets for credit docs
-- Updated Sep 26, 2013 to add vendor credit limit
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

SELECT
VM.VENDORID Vendor_ID, 
VM.VENDNAME Vendor_Name,
VM.VNDCLSID Vendor_Class, 
VM.PYMTRMID Vendor_Terms,
VM.CRLMTDLR Credit_Limit,
sum (CASE
 WHEN P.DOCTYPE < 4 THEN P.CURTRXAM
 ELSE P.CURTRXAM * -1
 END) Unapplied_Amount,
sum (CASE
 WHEN DATEDIFF(d, P.DUEDATE, getdate()) < 31
      and P.DOCTYPE < 4 THEN P.CURTRXAM
 WHEN DATEDIFF(d, P.DOCDATE, getdate()) < 31
      and P.DOCTYPE > 3 THEN P.CURTRXAM * -1
 ELSE 0
 END) [Current],
sum (CASE
 WHEN DATEDIFF(d, P.DUEDATE, getdate()) between 31 and 60
      and P.DOCTYPE < 4 THEN P.CURTRXAM
 WHEN DATEDIFF(d, P.DOCDATE, getdate()) between 31 and 60
      and P.DOCTYPE > 3 THEN P.CURTRXAM * -1
 ELSE 0
 END) [31_to_60_Days],
sum (CASE
 WHEN DATEDIFF(d, P.DUEDATE, getdate()) between 61 and 90
      and P.DOCTYPE < 4 THEN P.CURTRXAM
 WHEN DATEDIFF(d, P.DOCDATE, getdate()) between 61 and 90
      and P.DOCTYPE > 3 THEN P.CURTRXAM * -1
 ELSE 0
 END) [61_to_90_Days],
sum (CASE
 WHEN DATEDIFF(d, P.DUEDATE, getdate()) > 90
      and P.DOCTYPE < 4 THEN P.CURTRXAM
 WHEN DATEDIFF(d, P.DOCDATE, getdate()) > 90
      and P.DOCTYPE > 3 THEN P.CURTRXAM * -1
 ELSE 0
 END) [91_and_Over]

FROM PM00200 VM  --vendor master
INNER JOIN PM20000 P  --open payables
   ON P.VENDORID = VM.VENDORID

WHERE P.CURTRXAM <> 0 AND VOIDED = 0

GROUP BY VM.VENDORID, VM.VENDNAME, VM.PYMTRMID, 
   VM.VNDCLSID, VM.CRLMTDLR

-- add permissions for DYNGRP
GO
GRANT SELECT ON view_Current_Payables_Aging_Summary TO DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

41 Responses to “SQL view for current Payables aging in Dynamics GP”

  1. Victoria, is there any way to take into account scheduled payments on the aging view? I was under the impression that GP would move the invoice to history, but on the view it looks like it is showing up as unpaid, maybe I’m missing something? Technically, it isn’t paid, but I think that they just want it to not show past due since they have made a payment contract with the vendor so it should actually show as current rather then past due.

    Like

    • Hi Rob,

      This code does not take Scheduled Payments into account. So it will actually show incorrect results if you have Scheduled Payments. I have to say, this is the first request I have gotten for including Scheduled Payments in any of the payables views…I will put in on the request list. If you wanted to do this yourself, you would need to add some code to treat DOCTYPE 7 differently.

      -Victoria

      Like

  2. Victoria,

    Do you know of this same report that would also have the details with it? The invoices that need to be paid in which make up these totals? I know there is an AP Trail Balance Report Writer report for this, but my users are having problems exporting this to Excel and it’s not exactly all that ‘pretty’ as we all know. This summary was great and I was able to get it in Excel Report Builder and it really did the job as far as a summary. If I could get details, that would rock!

    Thanks,
    John Olley

    Like

  3. Thanks Victoria for this. However I would like to ask if this can be modified to give a weekly aging instead of 30, 60,90. Can it be modified to give the amount due by vendor for every friday until the end of the year?

    Thanks for any direction you can give me on this

    Like

    • Erica,

      It sounds like you’re actually looking more for a cash forecast rather than an aging? I have not done something like that before, but I would think it’s doable in a similar way to what I have here. You would have to change the date calculations to what you need and create additional aging buckets as needed. Take a look at my blog post on coding dates in SQL to help you with the formulas. There are a number of examples there for specific days of the week. For what you’re asking, though, it might be best to find the last Friday (or first Friday) you want, then keep adding or subtracting 1 week at a time.

      One issue you might run into with the logic as you have described it is that you would have a constantly changing number of columns in your results. For example, today, there might be about 18 weeks left in the year, but in the middle of December you would only get 2 or 3 columns in your results. For aging purposes, you might instead decide to always go X weeks out and everything else would be in an ‘over X weeks’ aging bucket?

      Another thing to consider is the naming of your columns – if you are going to be using this in SmartList, it would have to be something generic like ‘1 week’, ‘2 weeks’, etc. In a different reporting tool you could actually change the name of the columns dynamically based on either a field you add to the view or a calculation in the report.

      Hope that helps to give you some ideas.

      -Victoria

      Like

  4. Thanks for the knowledge shared Victoria on this reporting. I was able to run the query in SQL and it was successful. i would like you to guide me on how to publish this report into smartlist using smartlist builder. Thanks

    Like

  5. Is there a similar script to create a Receivables aging?

    Like

  6. Thank you Victoria – I really appreciate your assistance. I have one more question – what would I need to add in order to pull historical data and run historically as of different dates?

    Like

  7. This looks like it could work – Thank you for your assistance!

    Like

    • Hi Erin,

      Try something like this, it will only work for the PAY distribution and only if there is one PAY distribution per transaction, but it will give you an idea:

      SELECT
      VM.VENDORID Vendor_ID,
      VM.VENDNAME Vendor_Name,
      VM.VNDCLSID Vendor_Class,
      VM.PYMTRMID Vendor_Terms,
      G.ACTNUMST Account_Number,
      sum(CASE
      WHEN P.DOCTYPE < 4 THEN P.CURTRXAM
      ELSE P.CURTRXAM * -1
      END) Unapplied_Amount,
      sum(CASE
      WHEN DATEDIFF(d, P.DUEDATE, getdate()) < 31
      AND P.DOCTYPE < 4 THEN P.CURTRXAM
      WHEN DATEDIFF(d, P.DOCDATE, getdate()) > 3
      THEN P.CURTRXAM * -1
      ELSE 0
      END) [Current],
      sum(CASE
      WHEN DATEDIFF(d, P.DUEDATE, getdate())
      between 31 and 60
      AND P.DOCTYPE > 3
      THEN P.CURTRXAM * -1
      ELSE 0
      END) [31_to_60_Days],
      sum(CASE
      WHEN DATEDIFF(d, P.DUEDATE, getdate())
      between 61 and 90
      AND P.DOCTYPE > 3
      THEN P.CURTRXAM * -1
      ELSE 0
      END) [61_to_90_Days],
      sum(CASE
      WHEN DATEDIFF(d, P.DUEDATE, getdate()) > 90
      AND P.DOCTYPE < 4 THEN P.CURTRXAM
      WHEN DATEDIFF(d, P.DUEDATE, getdate()) > 90
      AND P.DOCTYPE > 3 THEN P.CURTRXAM * -1
      ELSE 0
      END) [91_and_Over]
      FROM PM00200 VM
      INNER JOIN PM20000 P
      ON P.VENDORID = VM.VENDORID
      LEFT OUTER JOIN PM10100 D
      ON P.VCHRNMBR = D.VCHRNMBR
      AND P.CNTRLTYP = D.CNTRLTYP
      AND D.DISTTYPE = 2
      LEFT OUTER JOIN
      GL00105 G ON D.DSTINDX = G.ACTINDX
      WHERE P.CURTRXAM <> 0 AND VOIDED = 0
      GROUP BY VM.VENDORID, VM.VENDNAME,
      VM.PYMTRMID, VM.VNDCLSID, G.ACTNUMST

      Like

  8. We look at the invoice distribution accounts in order to determine the fund.

    Like

  9. We manually look each of the invoices up…

    Like

  10. We have used GP for only a year and have been manually calculating this from the detail AP. Currently we do not have a report that can break this down between funds.

    Like

  11. I work for a municipality and we use fund accounting, therefore we have multiple balance sheets within one company. How can you break out the aged payables between funds?

    Like

  12. How do you use/run the view?

    Like

Trackbacks/Pingbacks

  1. SQL view for current Payables aging detail in Dynamics GP - Victoria Yudin - Microsoft Dynamics GP - Microsoft Dynamics Community - May 9, 2013

    […] have received a few requests for a detailed version my Current Payables Aging Summary view. Here it is. This code is only looking at functional currency and will return one row per […]

    Like

  2. SQL view for current Payables aging detail in Dynamics GP - Victoria Yudin - Microsoft Dynamics GP - Microsoft Dynamics Community - April 24, 2013

    […] have received a few requests for a detailed version my Current Payables Aging Summary view. Here it is. This code is only looking at functional currency and will return one row per […]

    Like

  3. SQL view for current Payables aging detail in Dynamics GP – 4/24, Victoria Yudin | Partner Compete - April 24, 2013

    […] have received a few requests for a detailed version my Current Payables Aging Summary view. Here it is. This code is only looking at functional currency and will return one row per […]

    Like

  4. SQL view for current Payables aging detail in Dynamics GP | Victoria Yudin - April 24, 2013

    […] have received a few requests for a detailed version my Current Payables Aging Summary view. Here it is. This code is only looking at functional currency and will return one row per […]

    Like

  5. Everything Dynamics GP #36 - About Dynamics, Development and Life - GP Technical Blogs - Microsoft Dynamics Community - October 3, 2011

    […] SQL view for current Payables aging in Dynamics GP (Victoria Yudin) […]

    Like

  6. SQL view for current Payables aging in Dynamics GP | | Interesting Findings & Knowledge Sharing - September 30, 2011

    […] site: SQL view for current Payables aging in Dynamics GP VN:F [1.9.10_1130]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,625 other followers

%d bloggers like this: