Has anybody figured out a way to do this? I want an AP aging report, but don’t show me what is currently due, 30 days past due, etc. Instead, show me what was paid on time, what was paid 30 days past due, and so on, for each supplier, within a given date range. I was thinking I can write a BAQ that gets each invoice amount w/due date and match that up with the payments to calculate the buckets but I don’t know if there is an easier way or a report that already exists that I don’t know about.
So instead of showing your current balances at 30/60/90 days past due, you want to see all-time what you paid on time and 30, 60, 90, 180 days past due?
Right
I’d have to noodle that a little bit. It’s much easier to just look at what’s currently outstanding… haha. Do you know where the payments show up? I am not very familiar with the AP side of things. I see a pay schedule table that links to the AP Header info… I can see how you’d get when the payments were due, but I don’t see where to see the corresponding payments that were made.
Yeah I am struggling with that part too. There’s erp.CheckHed and erp.APTran but nothing is quite matching up the way I expect it to.
It sounds like you’re on the right track! I’m not aware of an existing report. The APInvHed table has a ChangeDate field, which I think will be the last payment date in most cases because that’s the last time the record would be updated (but definitely double check that with a sample dataset).
Add a filter to the BAQ such that APInvHed.DocInvoiceBal = 0.
If the ChangeDate field doesn’t work out, then you’ll want to add the APTran table, joined on InvoiceNum. APTran.TranDate compared with APInvHed.DueDate would give you the number of days for the buckets.
APInvHed and APTran seem to be the best bet.
May need to put the APTran as a subquery where you sum the payments by invoice and pull in the maximum date to see how many days late.
After you model that one, you could do the same on the AR side too.
1 Like
I wrote a BAQ to be able to run a historical AR aging and ended up using the Tran GLC table filtered to related to file of InvcHead and CashDtl with GLAcctContect of Receivables.
I think if you use those criteria, you should be able to pull all of the invoices and cash receipts, then calculate the days between them and bucket them by the groups you want.