01
About this report
One row per posted vendor bill with an unpaid balance, and per vendor credit or vendor payment with an unapplied amount, shown as a negative. Each row gives the document, type, vendor, date, due date, days overdue, aging bucket, and the open amount in the transaction currency and in base currency.
Opened from AP aging by vendor, it arrives filtered to one vendor and bucket. On its own it works as a payment-run list. Filter Aging bucket to what is due and sort by Due date.
02
Sample preview
Fictional figures in the shape you will see. Yours come from your account.
| Document | Type | Vendor | Date | Due date | Days overdue | Aging bucket | Currency | Open (transaction currency) | Open amount |
|---|---|---|---|---|---|---|---|---|---|
| B-7781 | Bill | Ridgeline Contractors | 2026-07-02 | 2026-08-01 | 54 | 31-60 | US Dollar | 2,250 | 2,250 |
| B-7902 | Bill | Ridgeline Contractors | 2026-08-10 | 2026-09-09 | 15 | 1-30 | US Dollar | 4,500 | 4,500 |
| VC-0211 | Bill Credit | Ridgeline Contractors | 2026-08-22 | 2026-08-22 | Unapplied credit | US Dollar | -500 | -500 | |
| INV-44120 | Bill | Cloudway Hosting | 2026-09-15 | 2026-10-15 | -21 | Current | US Dollar | 14,200 | 14,200 |
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(v.altname, v.entityid) AS vendor,
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 t.entity AS vendor__id,
t.id AS document__id,
NVL(t.tranid, t.transactionnumber) 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
WHERE t.type = 'VendBill'
AND t.posting = 'T'
AND t.foreignamountunpaid > 0
UNION ALL
SELECT t.entity,
t.id,
NVL(t.tranid, t.transactionnumber),
BUILTIN.DF(t.type),
t.trandate,
t.trandate,
BUILTIN.DF(t.currency),
-t.foreignpaymentamountunused,
-t.foreignpaymentamountunused * t.exchangerate
FROM transaction t
WHERE t.type IN ('VendCred', 'VendPymt')
AND t.foreignpaymentamountunused > 0) o
JOIN entity v ON v.id = o.vendor__id
ORDER BY o.due_date04