01
About this report
One row per posted invoice with an unpaid balance, and per credit memo or customer payment with an unapplied amount, shown as a negative. Each row gives the document, type, top-level customer, date, due date, days overdue, aging bucket, and the open amount in the transaction currency and in base currency at the transaction's rate.
Opened from AR aging by customer, it arrives filtered to one customer and bucket. On its own, filter on Aging bucket or sort by Days overdue to build a collections call list.
02
Sample preview
Fictional figures in the shape you will see. Yours come from your account.
| Document | Type | Customer | Date | Due date | Days overdue | Aging bucket | Currency | Open (transaction currency) | Open amount |
|---|---|---|---|---|---|---|---|---|---|
| INV20931 | Invoice | Harbor & Pine Co. | 2026-09-19 | 2026-10-19 | -25 | Current | US Dollar | 48,200 | 48,200 |
| INV20877 | Invoice | Harbor & Pine Co. | 2026-08-15 | 2026-09-14 | 10 | 1-30 | US Dollar | 12,500 | 12,500 |
| INV20790 | Invoice | Brightline Dental Group | 2026-07-11 | 2026-08-10 | 45 | 31-60 | US Dollar | 8,750 | 8,750 |
| PYMT08812 | Payment | Brightline Dental Group | 2026-09-02 | 2026-09-02 | Unapplied credit | US Dollar | -1,200 | -1,200 | |
| INV20511 | Invoice | Coastal Freightways | 2026-05-02 | 2026-06-01 | 115 | Over 90 | Canadian Dollar | 12,490 | 9,120 |
03
The SuiteQL
This is the query each report runs. Read it, copy it, or change it after import.
SELECT o.document__id,
o.document,
o.type,
NVL(c.altname, c.entityid) AS customer,
TO_CHAR(o.tran_date, 'YYYY-MM-DD') AS tran_date,
TO_CHAR(o.due_date, 'YYYY-MM-DD') AS due_date,
CASE WHEN o.open_amount < 0 THEN NULL ELSE TRUNC(SYSDATE) - TRUNC(o.due_date) END AS days_overdue,
CASE WHEN o.open_amount < 0 THEN 'Unapplied credit'
WHEN TRUNC(SYSDATE) - TRUNC(o.due_date) <= 0 THEN 'Current'
WHEN TRUNC(SYSDATE) - TRUNC(o.due_date) <= 30 THEN '1-30'
WHEN TRUNC(SYSDATE) - TRUNC(o.due_date) <= 60 THEN '31-60'
WHEN TRUNC(SYSDATE) - TRUNC(o.due_date) <= 90 THEN '61-90'
ELSE 'Over 90' END AS aging_bucket,
o.currency,
o.open_fx,
o.open_amount
FROM (SELECT e.toplevelparent AS customer__id,
t.id AS document__id,
t.tranid AS document,
BUILTIN.DF(t.type) AS type,
t.trandate AS tran_date,
NVL(t.duedate, t.trandate) AS due_date,
BUILTIN.DF(t.currency) AS currency,
t.foreignamountunpaid AS open_fx,
t.foreignamountunpaid * t.exchangerate AS open_amount
FROM transaction t
JOIN entity e ON e.id = t.entity
WHERE t.type = 'CustInvc'
AND t.posting = 'T'
AND t.foreignamountunpaid > 0
UNION ALL
SELECT e.toplevelparent,
t.id,
t.tranid,
BUILTIN.DF(t.type),
t.trandate,
t.trandate,
BUILTIN.DF(t.currency),
-t.foreignpaymentamountunused,
-t.foreignpaymentamountunused * t.exchangerate
FROM transaction t
JOIN entity e ON e.id = t.entity
WHERE t.type IN ('CustCred', 'CustPymt')
AND t.foreignpaymentamountunused > 0) o
JOIN entity c ON c.id = o.customer__id
ORDER BY customer, o.due_date04