SQL view for current Payables aging detail in Dynamics GP

I 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 open (unpaid) payables transaction. 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 and change the labels and number of days.

Additional resources:

• Payables SQL views
• Payables module commonly used tables
• Other Dynamics GP reporting links

create view view_Current_Payables_Aging_Detail
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ 
-- view_Current_Payables_Aging_Detail
-- Apr 24, 2011 - Victoria Yudin, Flexible Solutions, Inc.
-- Shows current AP aging
-- Functional currency only
-- Aging by due date
-- For updates please see:
--	 http://victoriayudin.com/gp-reports/
-- For other payables SQL scripts: 
--	 http://victoriayudin.com/gp-reports/payables-sql-views/
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
VM.VendORID Vendor_ID, 
VM.VendNAME Vendor_Name,
VM.VNDCLSID Vendor_Class, 
VM.PYMTRMID Vendor_Terms,
case P.DOCTYPE
  when 1 then 'Invoice'
  when 2 then 'Finance Charge'
  when 3 then 'Misc Charge'
  when 4 then 'Return'
  when 5 then 'Credit Memo'
  when 6 then 'Payment'
  end Document_Type,
P.DOCNUMBR Document_Number,
P.DOCDATE Document_Date,
P.PSTGDATE GL_Posting_Date,
P.DUEDATE Due_Date,

P.TRXDSCRN [Description],

case
  when P.DOCTYPE < 4 then P.DOCAMNT
  else P.DOCAMNT * -1
  end Document_Amount,

case
  when P.DOCTYPE < 4 then P.CURTRXAM
  else P.CURTRXAM * -1
  end Unapplied_Amount,

case
  when datediff(d, P.DUEDATE, getdate()) < 31
       and P.DOCTYPE < 4 then P.CURTRXAM
  when datediff(d, P.DUEDATE, getdate()) < 31           
       and P.DOCTYPE > 3 then P.CURTRXAM * -1
  else 0
  end [Current],

case
  when datediff(d, P.DUEDATE, getdate()) between 31 and 60
       and P.DOCTYPE < 4 then P.CURTRXAM    
  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],

case
  when datediff(d, P.DUEDATE, getdate()) between 61 and 90
       and P.DOCTYPE < 4 then P.CURTRXAM    
  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],

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  --vendor master
inner join PM20000 P  --open payables
   on P.VENDORID = VM.VENDORID

where P.CURTRXAM <> 0 and P.VOIDED = 0

-- add permissions for DYNGRP
GO
grant select on view_Current_Payables_Aging_Detail 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.