01
About this report
Every posted invoice with an unpaid balance and a due date before today. Each row gives the customer billed, the invoice and due dates, days overdue, terms, and the unpaid amount in the invoice currency and in base currency at the invoice's rate.
Use it for a weekly collections review or as a dashboard portlet. Filter on Customer before a call. The invoice number opens the invoice.
02
Sample preview
Fictional figures in the shape you will see. Yours come from your account.
| Invoice | Customer | Invoice date | Due date | Days overdue | Terms | Currency | Unpaid (invoice currency) | Unpaid |
|---|---|---|---|---|---|---|---|---|
| INV20511 | Coastal Freightways | 2026-05-02 | 2026-06-01 | 115 | Net 30 | Canadian Dollar | 12,490 | 9,120 |
| INV20688 | Coastal Freightways | 2026-06-20 | 2026-07-20 | 66 | Net 30 | Canadian Dollar | 5,890 | 4,300 |
| INV20790 | Brightline Dental Group | 2026-07-11 | 2026-08-10 | 45 | Net 30 | US Dollar | 8,750 | 8,750 |
| INV20877 | Harbor & Pine Co. : Retail rollout | 2026-08-15 | 2026-09-14 | 10 | Net 30 | US Dollar | 12,500 | 12,500 |
03
The SuiteQL
This is the query each report runs. Read it, copy it, or change it after import.
SELECT t.id AS invoice__id,
t.tranid AS invoice,
NVL(e.altname, e.entityid) AS customer,
TO_CHAR(t.trandate, 'YYYY-MM-DD') AS invoice_date,
TO_CHAR(t.duedate, 'YYYY-MM-DD') AS due_date,
TRUNC(SYSDATE) - TRUNC(t.duedate) AS days_overdue,
BUILTIN.DF(t.terms) AS terms,
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
AND t.duedate < TRUNC(SYSDATE)
ORDER BY t.duedate04