AP aging as of any date
The auditor wants the aging as of December 31. The report only knows today, and Oracle’s snapshots miss what was backdated. Keep the dated facts, and any as-of date is one parameter away.
1 Invoice facts with their accounted dates, extracted whole. Nothing summarized, nothing snapshot.
2 Payment facts with their applied dates, the other half of every schedule.
3 One query: outstanding as of date d is what was invoiced on or before d minus what was paid on or before d, per schedule, per supplier. Its Airflow DAG loads nightly with sanity checks; a failed check stops the load, not the close.
4 Both extracts land side by side in the top tier of your cloud box, the raw layer called bronze, and load straight down into the star.
5 The warehouse star is just a database in your own cloud, amounts joined to lookups, security joined on ledger. Every seeded payables report is documented in our catalog, open it here.
6 December 31 becomes a parameter. A backdated invoice lands in its correct past bucket because the bucket is computed, not photographed, and the historical aging ties to the control account for that date.
deploys Everything ships from Git. One Oracle quirk to respect: uploading report artifacts resets folder permissions, so every deploy ends by reasserting them.
The record, in Oracle’s own pages: the Customer Connect thread where an SR was answered with a past as-of date is not possible while the auditors waited, the FDI known-issues pages documenting that backdated transactions are missed by the incremental run with a full reload as the workaround, and the FDI FAQ conceding the aging balance cannot be reconciled to the trial balance and prior-period entries live only in the current snapshot.
answer 9,847,200 invoiced − 5,726,900 paid = 4,120,300 outstanding · equals GL · 4 backdated invoices in their correct buckets
◆ The record, in full— what Oracle answered, and what its own platform documents.
| The evidence | What it says |
|---|---|
| The SR answer | A customer asked for AP aging as of a past date and Oracle’s support answer was that it is not possible. Their reply carries the whole problem: this fundamental reconciliation is not supported, they cannot be the first to need it, and the auditors are asking now. |
| Snapshots in Oracle’s own platform | Fusion Data Intelligence ages from snapshots, and its known-issues pages document that backdated transactions are not updated correctly in the incremental run. The published workaround is a full reload of the aging functional area, daily if reporting needs it. |
| The opening that does not match | The same pages document the opening amount of a period failing to match the prior period’s closing in the aging subject areas, which is the symptom an auditor finds first. |
| The concession | Oracle’s FAQ answers why the aging balance cannot be reconciled to the Payables trial balance, and why backdated transactions are absent from historical snapshots: prior-period entries land only in the current snapshot. A snapshot cannot rewind. |
◆ The as-of computation, mechanics— why computed aging cannot have the snapshot disease.
| Piece | How it works |
|---|---|
| The two dated facts | Every invoice schedule with its accounted date, every payment application with its applied date, extracted whole through the compliant bulk path. The dates are the asset: nothing is aggregated away, so nothing has to be photographed. |
| The as-of rule | Outstanding as of d equals invoiced on or before d minus paid on or before d, per schedule. Buckets follow from the due date the same way. A backdated invoice enters the right past bucket automatically, because the bucket is arithmetic, not history. |
| The tie, built in | The same star holds the GL journal lines for the control account, so the historical aging total ties to the ledger for that date, the reconciliation the SR customer owed their auditors. One warehouse, both answers. |
| Gates before anyone looks | Row counts against the manifest, duplicate keys, null suppliers, and today’s total against yesterday’s plus the day’s activity. A failed gate stops the load and pages the pipeline owner. |
| Where it runs | A database in your own cloud, GCP, Azure, or AWS, security joined on ledger. The read-only console answers the as-of question in a sentence, as shown above, and the receivables side works identically. |
◆ The December 31 walk-through— the auditor’s number, rebuilt and tied.
March, audit season. The request: supplier aging as of December 31, tied to the ledger. The old way is an SR, a shrug, and a spreadsheet archaeology project across three people’s exports. The owned way is one parameter: invoices accounted through December 31 total 9,847,200, payments applied through December 31 total 5,726,900, outstanding 4,120,300, per supplier, per bucket, and the same star’s GL lines for the control account total 4,120,300 for the same date. Four invoices backdated in January land in their correct December buckets because the buckets are computed from dates, not copied from a snapshot that had already been taken. The auditor gets the schedule, the tie, and the drill path, and nobody reloads anything.
Published clean-room: the pattern is shown, no client’s SQL, structures, or naming appears here, and never will.
- as of
- The balance rebuilt for a chosen past date. A parameter here.
- snapshot
- A copy taken on a day. It cannot learn what was backdated later.
- AP · GL
- Accounts payable, the general ledger. The aging must tie between them.
- FDI
- Fusion Data Intelligence, Oracle’s analytics platform. Snapshot-based aging.
- SR
- A support request to Oracle. The one on this page got a no.
- control account
- The GL account the subledger posts into. The tie lives here.
- BICC
- BI Cloud Connector, Oracle’s bulk export service for Fusion data.
- DAG
- A scheduled chain of jobs. Airflow runs the nightly load as one.
- DQ
- Data-quality checks. Dupes, counts, nulls, caught before the load.
- star schema
- One table of amounts joined to small lookup tables. The fast shape.
- ⋈
- A join. Security joins the amounts on ledger.