Stock on hand, as of any date
The auditor asks a simple question: how many units were on hand at year end? The system that moves the stock cannot answer it, because on-hand quantity is stored for one date only, and that date is today.
◆ What goes wrongthe plain version, before any technical detail.
Inside Oracle Fusion, on-hand quantity lives in a table that holds the current state: what is in each location right now. Every issue and receipt updates it in place. Nothing files away what the number was yesterday, or at the March close, or the day the count was taken.
So there is no delivered report for stock as of a past date. The question recurs on Oracle's own customer forums year after year, and an Oracle support note exists purely to explain the workaround, which says everything about how often support is asked. The workaround is reconstruction: start from current on-hand and roll transactions backward to the date you want.
◆ Where the delivered paths stoprollback arithmetic, and an eleven-month window in the paid product.
| Path | What it gives | Where it stops |
|---|---|---|
| Live on-hand table | Current quantity by item, organization, and subinventory. Accurate, for today. | One date. The past is overwritten in place. |
| Rollback query | Current on-hand, minus receipts and plus issues since the target date, from transaction history. | Hand-built, slow at scale, and quietly wrong once transactions are backdated across the target date. |
| Paid analytics | Oracle's analytics product keeps month-end historic inventory balances. | Vendor-documented limits: roughly the past eleven months of month ends, no support for backdated transactions in historic balances, and a full pipeline reset to repair them. |
The pattern is the same one this catalog documents for receivables aging as of any date: the transactional system holds now, reconstruction is fragile, and the vendor's analytics layer holds a short window of month ends. A year-end audit question can outlive all three.
◆ The owned answera daily snapshot, with replay only for the seam.
The owned build takes one snapshot of on-hand per day into a fact table you keep forever. From the day the pipeline starts, any as-of question is a filter. For dates before the pipeline existed, one reconstruction backfills the history, run once, validated once, and labeled as rebuilt.
| Reconstruction check, item AS-1140 | Units |
|---|---|
| On hand today | 1,240 |
| Receipts since the as-of date | 180 |
| Issues since the as-of date | 145 |
| On hand at the as-of date | 1,205 |
The arithmetic is the whole method: today's 1,240, minus the 180 that arrived afterward, plus the 145 that left afterward, is 1,205 then. The snapshot table makes that a stored fact instead of a nightly recomputation, and backdated transactions become a rebuild trigger instead of silent error. Sample values are illustrative, never client data.
-- on-hand as of any date, from the owned daily snapshot
SELECT item_number, organization_code,
SUM(on_hand_qty) AS on_hand
FROM fct_inventory_day
WHERE snapshot_date = DATE '2025-12-31'
GROUP BY 1, 2
ORDER BY 1, 2
- ERP
- Enterprise resource planning, the system of record for operations and finance.
- on-hand quantity
- Units physically in stock in a location, as the system currently believes.
- subinventory
- A storage subdivision of an inventory organization.
- rollback
- Reconstructing a past quantity by reversing later transactions out of the current one.
- backdating
- Entering a transaction dated in the past, after later transactions already posted.
- snapshot
- A dated copy of a table state, kept so the past stays queryable.
- backfill
- A one-time rebuild of history from before a pipeline existed.
- as-of date
- The past date a question is asked about, as the data stood then.