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
--    https://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.

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

  1. Hi Victoria, on a SQL view, is there a way to bring the in the Checkbook ID attached to the invoice while the invoice is in Work or Open Status? Just to be clear, this would be an invoice that is entered or posted with no payment applied to it (Not in ‘History’). To my understanding, the Checkbook ID is selected while entering a payment trx on the Batch Entry Window. Could that Checkbook ID selected on the Batch Entry window be brought into a SQL view? I’ve been searching through tables and I’m seeing that the checkbook ID is not captured until the payment processing starts.

    Like

    • Hi Mwalimu, you mention “the Checkbook ID attached to the invoice” – what exactly does this mean? Where have you attached a Checkbook ID to an invoice prior to paying it?

      -Victoria

      Like

      • Hi, my apologies, what I meant is; when a batch is made on the Batch Entry window in the purchase module, a checkbook ID is selected. Transactions are then created inside the batch. is the Checkbook ID that is selected on the Batch entry window tracked in tables for Invoices that are unpaid? I hope this is clearer

        Like

        • Thanks Mwalimu, that helps.

          The Checkbook ID entered on the batch window is stored in the SY00500 while the batch is not posted. However, that is simply the DEFAULT Checkbook ID that will be used IF you happen to enter payments in that batch. Even with this being the default, you can still change the Checkbook ID on any payment you enter in the batch. A Checkbook ID is not actually associated with any non-payment transaction in any way.

          Once the batch is posted, the default Checkbook ID that was on the batch (and in the SY00500 table) is not stored anywhere and is not associated with any transactions that were in that batch. Even if there were payments entered in the batch, those would have the actual Checkbook ID entered for them saved, not the default Checkbook ID from the batch.

          Hope that helps with your query.
          -Victoria

          Like

  2. Hi Victoria,

    I am trying to recreate the AP Aging as of 12/31/2019. I tried following the links to Siva’s blogs but they are no longer valid. I also ran the SQL in your 2011 response to Erin, but that only puts the payables in the aging buckets, based on a date (I replaced getdate() with a variable holding the value ’12/31/2019′), for currently open payables. It does not look at the historical table and back out payments made after 12/31/2019 and it does not exclude invoices with a docdate > 12/31/2019. In my mind, I have an idea how I can use the Apply tables to recreate what the aging would have look like at 12/31/2019, but my brain keeps freezing up when I sit down to write the code. I am new to GP and am just learning the data of each table and their relationships. Any advice is much appreciated to get me over my writer’s block.

    Thanks,

    Tony

    Like

    • Hi Tony,

      Reproducing this in SQL is extremely difficult, it took me over 40 hours with my knowledge of the GP tables, relationships, data flow and logic. It’s more complicated than just the apply logic, as there are also voids, discounts, and other stuff to include.

      And even after that it’s not always 100% what everyone wants. Because of that I do not give this code out for free and I customize it each time. I do offer this code for sale if what I have meets your needs – let me know if you are interested and I will email you directly about this.

      Thanks,
      -Victoria

      Like

  3. This SQL View is cool. However, I want to add one more column in it which should be sum of ( 61 to 90 Days + 91 and Over ). How can I do that? Can you share updated SQL View with required new column?
    or if I have created a smartlist and want to add a calculated field showing sum of these 2 columns, how can I do that? (Currently I am unable to do that from smartlist builder, add calculated field, as it gives me an error of incorrect syntax near ‘.61’.)

    Like

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

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

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

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

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

    Like

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

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

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

    Like

  12. We manually look each of the invoices up…

    Like

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

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

  15. 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 to Taiwo Cancel 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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: