01
About this report
One row per top-level customer. Open invoice balances are split by days past due into Current, 1-30, 31-60, 61-90 and Over 90. Unapplied credit memos and payments sit in their own negative column, and Balance is the net. Amounts are in base currency at each transaction's own exchange rate, so they can differ slightly from a revalued aging.
Click a bucket or the balance to open Open receivables filtered to that customer and bucket. The documents listed add up to the figure you clicked. Sort by Over 90 days to see collection risk first.
02
Sample preview
Fictional figures in the shape you will see. Yours come from your account.
| Customer | Current | 1-30 days | 31-60 days | 61-90 days | Over 90 days | Unapplied credits | Balance |
|---|---|---|---|---|---|---|---|
| Harbor & Pine Co. | 48,200 | 12,500 | 0 | 0 | 0 | 0 | 60,700 |
| Brightline Dental Group | 21,400 | 0 | 8,750 | 0 | 0 | -1,200 | 28,950 |
| Summit Analytics LLC | 27,150.50 | 0 | 0 | 0 | 0 | 0 | 27,150.50 |
| Coastal Freightways | 0 | 0 | 0 | 4,300 | 9,120 | 0 | 13,420 |
| Maple Ridge Clinics | 9,800 | 3,100 | 0 | 0 | 0 | -450 | 12,450 |
03
The SuiteQL
This is the query each report runs. Read it, copy it, or change it after import.
SELECT o.customer__id,
NVL(c.altname, c.entityid) AS customer,
SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) <= 0 THEN o.open_amount ELSE 0 END) AS current_due,
SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) BETWEEN 1 AND 30 THEN o.open_amount ELSE 0 END) AS days_1_30,
SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) BETWEEN 31 AND 60 THEN o.open_amount ELSE 0 END) AS days_31_60,
SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) BETWEEN 61 AND 90 THEN o.open_amount ELSE 0 END) AS days_61_90,
SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) > 90 THEN o.open_amount ELSE 0 END) AS over_90,
SUM(CASE WHEN o.open_amount < 0 THEN o.open_amount ELSE 0 END) AS unapplied,
SUM(o.open_amount) AS balance
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
GROUP BY o.customer__id, NVL(c.altname, c.entityid)
ORDER BY balance DESCSELECT 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