From GL balance to invoice, one click
The auditor points at a number and asks what is behind it. Fusion’s answer is a corridor of clicks or a hand-wired report, and the path dies at export. Keep the keys, and the drill is one click, either direction.
1 GL journal lines with their import references, Oracle’s own link between a journal and what fed it. Most extracts throw it away; this one keeps it.
2 SLA journal lines with the identifiers of their source transactions: invoice, receipt, payment, asset.
3 The drill spine joins the hops once: balance to journal line to accounting line to source. One hard gate at load: broken links must be zero.
4 Everything lands in bronze, the raw tier of your cloud, and loads into the star.
5 The warehouse star is just a database in your own cloud, amounts joined to lookups, security joined on ledger. The conformed Oracle model is public, drill it here.
6 Run it in Power BI, Tableau, ThoughtSpot, or a custom app on the same star; ours is the console. A balance opens into its journal lines, a line opens onto its invoice, and an invoice traces up to every balance it touched. The path survives export, because the keys travel with the data.
deploys Everything ships from Git: a new subledger source is one reviewed commit, not another round of report wiring.
The record, in Oracle’s own pages: the Customer Connect thread where users shown the Smart View path asked for the subledger transactions in one or two clicks because the corridor was too long to demonstrate, the thread where a team rebuilding an R12-style GL detail report with AP subledger detail ended up asking strangers for custom SQL, and Oracle’s own setup guide for GL-to-subledger drill in BI, which documents the hand-wired navigation targets and states plainly that the popup choice of target will not happen automatically.
answer 5210, april: 1,284,300.00 · 214 journal lines · each drills to its invoice, receipt, or asset in one click
◆ The drill, rendered— three hops, one key each, ending on the invoice.
| balance | account 01-5210-620 · April 2026 · 1,284,300.00 · 214 journal lines behind it |
| ↓ click one line | |
| journal line | journal AP-APR-118 · line 42 · 18,450.00 · source: Payables · import reference kept |
| ↓ click the source key | |
| invoice | Alder Metals LLC · INV-20841 · accounted 2026-07-08 · 18,450.00 · matched to PO-7731 |
| and back | the same keys run upward: INV-20841 traces to every balance it touched, in one query |
Illustrative account and figures; the shape is the deliverable. The journal line and invoice carry the same 18,450.00 because the spine joins on keys, not amounts. Three hops in Fusion’s corridor; one query here.
◆ The record, in full— the corridor, the wiring, and where the path dies.
| The evidence | What it says |
|---|---|
| The Smart View corridor | The path exists: a balance drills to Inquire on Detail Balances, then to the journal, then to the subledger transaction. The team that demonstrated it to their business was asked for the same answer in one or two clicks, because a corridor walked one transaction at a time is a demo, not a working practice. |
| The OTBI wiring | Oracle’s own setup guide for GL-to-subledger drill in BI is a manual: build the subledger analysis, filter it on the journal identifier, set the column interaction, add a navigation target per subledger. When a journal could come from more than one subledger, a popup asks the user to pick the right target, and the guide states that this will not happen automatically. |
| The R12 expectation | Teams arriving from the old on-premises world expect a GL detail report that carries the subledger detail inline. The thread rebuilding one ends with a request for custom SQL from strangers, which is the honest measure of how far the seeded path falls short. |
| The mechanism | The drill lives inside the tool, not inside the data. Smart View’s path is a UI feature; OTBI’s is report wiring. Export the numbers to a warehouse or a spreadsheet and the path is gone, precisely when an auditor wants to walk it. |
◆ The owned drill, mechanics— the keys, the gate, and both directions.
| Piece | How it works |
|---|---|
| The keys Oracle keeps | Fusion already stores the links: a journal-to-source reference table on the GL side, and source transaction identifiers on every subledger accounting line. The build extracts them alongside the amounts instead of aggregating them away. |
| The spine, joined once | Balance to journal line, journal line to accounting line, accounting line to invoice, receipt, payment, or asset. The joins live in the model, so every report, every console answer, and every export inherits the same path. |
| Both directions | The same keys run upward. Given an invoice, the spine returns every journal and balance it touched, which turns a supplier dispute or a correction from an investigation into a query. |
| Gates before anyone looks | Broken links must be zero: no journal line without its accounting line, no accounting line pointing at a source that is not there. Row counts against the manifest, duplicate keys, the ledger tie. A failed gate stops the load, not the audit. |
| Survives export | Because the path is columns, not tool behavior, it exists wherever the data goes: the warehouse, the console, a spreadsheet an auditor takes away. The corridor ends at the tool’s door; the spine does not. |
◆ Where the keys live, in Oracle’s own reference— the two documented grains the spine is built from.
| Hop | Documented source and grain |
|---|---|
| Journal → source link | General Ledger – Journals Real Time: Oracle states this data at the grain of GL_IMPORT_REFERENCES, the journal-to-source link table itself, falling back to journal lines only for entries that never came from a subledger. The drill key is the documented grain. |
| Accounting → transaction | Subledger Accounting – Journals Real Time, at the grain of XLA_AE_LINES, carrying the account combination, the accounting class, and the transaction attributes of the source document underneath each entry. |
| The manual alternative | Oracle’s navigation-target setup guide: the same drill, wired by hand, report pair by report pair, with a manual popup choice. The spine replaces the whole procedure with two kept columns. |
| Multiple entities | Ledger rides on every hop, so the drill respects the security join by construction: a user drills only through the ledgers they hold, on the way down and on the way back up. |
◆ The audit-sample walk-through— fifty balances, fifty paths, one afternoon.
Fieldwork week. The auditors select fifty balances and ask for the transaction behind each one, with the trail. The old way is fifty corridor walks with screenshots at every hop and a popup per ambiguous journal, spread across whoever holds the right roles. The owned way is one query per item: each balance returns its journal lines, each line carries its source key, and each source arrives with its identifiers, INV-20841, 18,450.00, accounted July 8, matched to its purchase order. The trail is not a stack of screenshots; it is the columns themselves, and the broken-links gate that ran last night is the standing proof that no path on the list dead-ends. Fifty items, one afternoon, and the reverse question, which balances did this invoice touch, costs exactly one more query.
Published clean-room: the pattern is shown, no client’s SQL, structures, or naming appears here, and never will.
- drill
- Opening a number into the records behind it. A query here, a corridor there.
- import references
- Oracle’s link table between a journal and the subledger work that fed it.
- SLA
- Subledger Accounting, the bridge between a transaction and its journal entry.
- navigation target
- OTBI’s hand-wired drill link, built per report pair, chosen by popup.
- Smart View
- Oracle’s Excel add-in. Its drill path works, one transaction per walk.
- source keys
- The identifiers of the invoice, receipt, payment, or asset under an entry.
- broken link
- A key pointing at a row that is not there. Must be zero before anyone looks.
- 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.
- star schema
- Facts joined to small lookup tables. The shape where paths are columns.
- ⋈
- A join. Three of them make the whole drill.