Oracle X-Ray: every OneStream balance, traced to the invoice

Quick Facts

• Industry: Oilfield services group (about 70 legal entities, 16 Oracle ledgers, many currencies)
• Role: Designer and developer, OneStream / Oracle EBS integration
• Timeline: 2026, built and verified in phases from June to October
• Team: Designer and developer on the build, with the client's finance and tax teams as the requesters and testers
• Status: Delivered and verified. Drill-back signed off for UAT; reconciliation connectors tie to the penny.
• Impact: Auditors and controllers go from a OneStream balance to the invoice and payment in about 3 clicks, with no Oracle login and no IT ticket, and every drill level ties to the penny.

---

Overview

The client is a global oilfield services group consolidating and reconciling its books in OneStream, with Oracle E-Business Suite as the system of record. OneStream showed one summarized number per account, entity and month. That is what a consolidation platform should show, but it left every close with the same question from auditors and finance: what is this number made of?

Oracle X-Ray answers that question from inside OneStream. Right-click a balance and you see the journal lines behind it, then the invoice, payment or receipt behind each line. For intercompany balances you also see what the other company recorded, even when it sits in another region, ledger and currency. For tax accounts, a dedicated drill shows where the other side of every tax entry went: cash, taxes payable or deferred tax. Nothing is ever written back to Oracle.

The Problem

Five things made the close slower than it needed to be:

• Numbers without a story. Answering "what is this balance made of?" meant logging into Oracle and searching by hand, every close, for every question.
• Intercompany mismatches. When two sister companies recorded different amounts for the same deal, someone had to find the other side's entry, often in another region and currency. That meant email chains between teams.
• Tax entries with no other side. The tax team owns only the tax accounts, so a tax debit on its own never said whether it had been paid in cash, accrued to taxes payable or deferred. Finding out meant rebuilding the journal by hand.
• Reconciliations with no reliable source side. Oracle's subledgers (payables, receivables, assets, inventory) do not carry full general-ledger coding, so they could not be lined up against GL balances automatically.
• Incomplete or misleading extracts. Early pulls missed whole account groups such as inventory and fixed-asset activity, loaded the wrong entities for a workflow, or reported some cash accounts in the wrong currency.

Process

The Trial Balance load was fixed first, because a drill is only as good as the balance it starts from. The work then ran in two phases.

Phase 1: Build the drill-back

The drill reads the cell that was clicked, translates its OneStream dimensions back into Oracle's account-coding segments, and walks the trail from there. Every feature was preceded by probe queries on live data and tied out by hand against independent GL figures before sign-off.

Three engineering moments are worth telling in plain words:

• A security wall, proven and routed around. The standard path from accounting entries to source documents was blocked by Oracle's row-level security for the integration user. Diagnostics proved it was security and not a faulty join. The fix was an alternate route through the distribution-link table, which reached the invoice, payment and receipt without needing any new database grants.
• A false match, caught and fixed. A miscellaneous cash receipt was matching an unrelated old invoice because identifiers collide across document types. A document-type guard fixed it for every document type, not just the one that exposed it.
• A drill removed on evidence. A planned intercompany-document drill was dropped after the data showed the client books no transactions of that kind. Shipping a drill that could only ever answer "No" would have been noise.

Tax Offset Detail was added at the tax team's request on top of the same journal-line drill, and verified against a month of journal legs: 426 lines tied, 0 mismatches.

Phase 2: Subledger sources and the verification kit

Four connectors were built to supply the independent "source" side of OneStream's Account Reconciliations, and a repeatable kit was built to prove them against the GL for any period.

The Five Connectors

One connector loads the general ledger, and four more feed the "source" side of OneStream's Account Reconciliations, so each GL balance has something independent to reconcile against.

1. Trial Balance Connector

Each workflow loads exactly the entities assigned to it, guarded by a fixed list of known ledgers, and the connector refuses to load unfiltered. A reconciliation-currency map makes foreign-currency cash accounts load in their bank-account currency, with the bank-account master data as the source of truth. Status: in use, and the entity-scoping and currency fixes are validated.

2. Accounts Payable Connector

Payables is sourced from the accounting entries Oracle actually posted rather than from default account setup, and that change is what made it tie. Trade payables tie to the penny, including the two largest balances at around $9–13M each. The few differences were under $150 on million-dollar balances, each explained line by line as a manual journal or an FX revaluation.

3. Accounts Receivable Connector

Open trade receivables plus unbilled receivables, by entity and account, in functional currency. Clean trade entities tied to the penny three months running. The FX-revaluation differences on one foreign-currency entity were categorised rather than left unexplained.

4. Fixed Assets Connector

Re-sourced from the GL so that every cost, activity and accumulated-depreciation account appears. About 52 accounts now show balances, up from about 15, and the rebuilt pull reproduces the GL account for account.

5. Inventory Connector

Covers all four inventory subledgers (on-hand, receiving, in-transit and work-in-process) and reports the accounts Oracle actually posts to, under average costing. Inventory is a live snapshot with no history, so it is extracted at period close.

The Drill-Backs, Level by Level

Drills are offered from the right-click drill menu on any loaded balance, in three chains: standard, intercompany and tax.

• Standard: OneStream balance → Transaction Detail (this year's journal lines for that exact account) → Source Document (invoice, payment or receipt)
• Intercompany: OneStream balance → Intercompany Detail (pinned to the partner clicked) → Counterparty GL Entry (any ledger, any currency) → Counterparty Source Document
• Tax: Tax account balance → Tax Offset Detail (where the other side of each entry landed)

Standard · Level 1 — Transaction Detail

Shows the year-to-date journal lines behind the exact account combination that was clicked. Income-statement accounts show the year's activity, which resets each January. Balance-sheet accounts open with an Opening Balance row, so the grid always adds up to the balance that was clicked.

Standard · Level 2 — Source Document

Follows Oracle's subledger-accounting trail to the original document and returns the full balanced entry, every debit and credit leg.

• Payables: vendor, invoice number, invoice date, accounting date, payment status, payment date and check number.
• Receivables: customer, sales-invoice number, receipt number and receipt date.
• Payments and receipts resolve to their own check or receipt record.
• A manual journal with no source document shows a clear "no subledger source" message instead of an empty grid.

All 7 traceability fields requested by finance were proven populated on live data.

Intercompany · Level 1 — Intercompany Detail

Shows the intercompany journal lines pinned to the exact partner that was clicked, with no leakage from other partners.

Intercompany · Level 2 — Counterparty GL Entry

Finds the partner's mirror entry by swapping the company and partner segments, searching across all ledgers and regions because the partner usually sits in another one. Amounts match on transaction currency, and the accounted amounts differ only by the partner's FX.

Intercompany · Level 3 — Counterparty Source Document

Ends at the partner's own vendor bill or customer invoice. Entities loaded from spreadsheets rather than Oracle return a clear "not available for this partner" message.

Tax · Tax Offset Detail

Built at the tax team's request. Tax owns only the tax accounts, so a tax debit alone does not say whether it went to cash (paid), taxes payable (accrued) or deferred tax (timing). This drill adds an Offset Account column listing the accounts on the opposite side of each journal. Verified against a month of journal legs: 426 lines tied, 0 mismatches.

Verification and Diagnostics

Verification Kit

Compares GL against subledger by entity and account for any period, and gives every difference a category: manual journal, FX revaluation, timing, or a real break. An as-of reconstruction rebuilt about 90% of a receivables balance from later activity and still hit the GL exactly, three months running. A journal-source decomposition explains any GL balance by where it came from: Payables, Receivables, Spreadsheet, Manual or Revaluation. Three months across eight entities ran in about 7 minutes with zero query failures.

Diagnostics and Discovery

Read-only investigations that turned data questions into clear findings: cash reconciliation currency, inventory configuration, fixed-asset depreciation, and a regional GL-versus-subledger variance investigation. In that last one, every difference was explained to the penny and no data fix was needed.

Results

Soft outcomes:

• Self-service audit trail. Auditors and controllers no longer need an Oracle login or a ticket to IT to explain a number.
• Tax visibility. The tax team sees where each tax entry's other side landed (paid, accrued or deferred) without rebuilding journals.
• Reconciliations worth preparing. The few payables differences were under $150 on million-dollar balances, each explained line by line. A regional variance investigation explained every unexplained difference to the penny and needed no data fix: they were genuine reconciling items plus a run-timing effect.
• Setup problems found before they hit the books. A single misconfigured accounting class sent several hundred million dollars of work-in-process value outside the inventory accounts. Over $100M of accumulated depreciation had been entered by manual journal rather than through the asset system, which was raised as a control risk. Three foreign-currency cash accounts were silently loading in the wrong currency (fixed), and an extra entity in one workflow load was removed.
• Process rules made explicit. Inventory is a live snapshot in Oracle with no history, so it has to be extracted at period close. Receivables reconciliations must use the at-close import, because a later refresh drops invoices collected since then.

Learnings

What worked. Diagnosing before building. Probing live data first is what exposed the security wall, the false match and the drill that should not exist, and tying each result out against independent GL figures meant "signed off" meant something. Reading the data before trusting the design kept the drills small and the answers exact.

What I'd do differently. Fix the foundation and the drill in the same pass. The early extracts missed accounts and loaded the wrong entities, and every drill built on them inherited those gaps until the Trial Balance connector was corrected. A short audit of the base load before the first drill would have saved a round of rework.

Skill developed. Designing for a finance reader rather than a database reader. Each drill level ends in something an auditor recognises (an invoice, a payment, a partner's own entry) and says plainly when there is nothing to show. Pairing that with read-only, evidence-first engineering is now how I scope any drill-back or reconciliation work.