Oracle Fusion AP Aging Report
How much the company owes suppliers, how overdue each balance is, and which suppliers and invoices drive the past-due total — every figure traceable to the source invoice and its GL distribution.
What the seeded report gives you
Payables Aging Report (BI Publisher) · OTBI subject area "Payables Invoices – Transactions Real Time". Columns: Business Unit, Supplier & Site, Invoice Number/Date/Due Date, Aging Bucket (fixed), Amount Remaining, Currency.
Where it falls short
- Aging buckets are fixed in the data model — not a runtime parameter.
- No drill from a bucket total to the invoice line, distribution, or GL account.
- One business unit per run; no consolidated multi-BU position in one currency.
- No PO-match or hold/dispute status to separate owed from in-dispute.
- No expected-payment-date view; export row caps; no retained trend.
What finance actually needs
- Runtime-configurable buckets chosen by the user.
- Full drill: bucket → invoice → line → distribution → GL account.
- Hold / dispute / PO-match status columns.
- Multi-BU consolidation in one reporting currency.
- Expected-payment-date + DPO; nightly snapshot reconciled to source.
Related Closely related to the Payables Invoice Aging Report — same open-AP question; this is the classic 'AP Aging' name finance teams search for.
Sample build of the Oracle Fusion AP Aging Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
| Supplier | Current | 1–30 | 31–60 | 61–90 | 90+ | Total Open |
|---|---|---|---|---|---|---|
| Acme Logistics | 120,000 | 45,000 | 12,000 | 0 | 0 | 177,000 |
| Globex Materials | 88,500 | 0 | 0 | 22,000 | 8,400 | 118,900 |
| Initech Services | 0 | 31,000 | 0 | 0 | 14,200 | 45,200 |
| Umbrella Supply | 210,400 | 60,000 | 18,500 | 5,000 | 0 | 293,900 |
| Total | 418,900 | 136,000 | 30,500 | 27,000 | 22,600 | 635,000 |
The report buckets every open payment schedule by days past due against the due date in AP_PAYMENT_SCHEDULES_ALL, then rolls the open balance up by supplier. Current sits at $6.46M; the $1.96M past-due tail is concentrated in the 31-60 bucket.
Twelve suppliers carry balances in the 90+ bucket totaling $310K, but three of them have invoices on payment hold — so the headline overdue figure overstates what is actually payable until those holds clear.
The 90+ concentration traces to held invoices in AP_HOLDS_ALL. Release or write off the holds, then re-age so the past-due total reflects real payment obligations only.
This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.
Data sources
Show / hide SQL
SELECT s.vendor_name AS supplier, i.invoice_num AS invoice_number,
i.invoice_date, ps.due_date, i.invoice_currency_code AS currency,
ps.amount_remaining,
CASE WHEN TRUNC(:as_of_date) <= ps.due_date THEN 'Current'
WHEN TRUNC(:as_of_date)-ps.due_date <= 30 THEN '1-30'
WHEN TRUNC(:as_of_date)-ps.due_date <= 60 THEN '31-60'
WHEN TRUNC(:as_of_date)-ps.due_date <= 90 THEN '61-90'
ELSE '90+' END AS aging_bucket
FROM ap_invoices_all i
JOIN ap_payment_schedules_all ps ON ps.invoice_id = i.invoice_id
JOIN poz_suppliers s ON s.supplier_id = i.vendor_id
WHERE ps.amount_remaining <> 0 AND i.org_id = :business_unit
ORDER BY supplier, ps.due_date;The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per invoice payment schedule.
| Element | Type | Definition |
|---|---|---|
| dim_supplier | dimension | supplier · site |
| dim_business_unit | dimension | operating unit |
| dim_date | dimension | invoice & due |
| dim_gl_account | dimension | account combo |
| amount_remaining | measure | open balance |
| aging_bucket | measure | derived, configurable |
| dpo | measure | days payable outstanding |
Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.
| Table | Reporting columns | Subject areas |
|---|---|---|
| AP_INVOICES_ALL | 63 | 15 |
| AP_PAYMENT_SCHEDULES_ALL | 21 | 2 |
| POZ_SUPPLIERS | 145 | 75 |
| AP_INVOICE_DISTRIBUTIONS_ALL | 59 | 11 |
The same question across systems.
| Concept | Oracle Fusion | SAP S/4HANA | Workday |
|---|---|---|---|
| Report | Payables Aging Report | Vendor line items (FBL1N) → aged | Find Supplier Invoices / Aging |
| Open balance | AP_PAYMENT_SCHEDULES_ALL | ACDOCA + BSIK | Supplier Invoice object |
| Supplier master | POZ_SUPPLIERS | LFA1 / BUT000 | Supplier (resource) |
| Extraction | BICC | CDS / OData | RaaS / Prism |