Search Results inv_daily_status_pk




Overview
Explore in AI Chat

Overview

The table OPI.OPI_EDW_INV_DAILY_STAT_F is the Inventory Daily Status Fact table within the Oracle Operations Intelligence (OPI) product, delivered under the E-Business Suite 12.1.1 and 12.2.2 releases. It forms part of the OPI Enterprise Data Warehouse (EDW) star schema, storing periodic snapshots of inventory position and movement activity aggregated across organizations, items, locators, lots, and accounting periods. Each row captures a daily state of on-hand, intransit, and WIP balances alongside quantity and value measures expressed in both base and global currency, enabling trend, velocity, and valuation analytics without repeatedly querying transactional Inventory tables.

Under a heuristic Data Vault classification mined from its foreign key structure, this object is modeled as standalone, meaning it does not exhibit the strict hub/link/satellite relationships typically found in a normalized Data Vault. It operates instead as a classic Kimball-style fact table whose grain is defined by the combination of business keys and the period/date dimensions it references (PRD_DATE_FK_KEY, TRX_DATE_FK_KEY).

Key Information Stored

The physical schema documented for ETRM 12.1.1 contains 102 columns. The most analytically significant columns fall into three families: identity and keys, quantity measures, and value measures.

Common Use Cases and Queries

Typical reporting queries aggregate quantity or value measures by period, organization, or item, using the surrogate key for uniqueness. A common pattern joining the daily status key to a dimensional lookup is:

SELECT s.INV_DAILY_STATUS_PK, s.INV_ORG_FK_KEY,
       s.END_ONH_QTY, s.END_ONH_VAL_B
FROM   OPI.OPI_EDW_INV_DAILY_STAT_F s
WHERE  s.PRD_DATE_FK_KEY = :period_key
AND    s.END_ONH_QTY > 0;

Analysts use this table for inventory turn and velocity trending (comparing BEG_ONH_QTY to END_ONH_QTY over successive periods), on-hand valuation reports distinguishing BASE versus GLOBAL currency, WIP aging (BEG_WIP_QTY versus END_WIP_QTY), and intransit exposure analysis (AVG_INT_VAL_B). Because the "inv_daily_status_pk" search term maps directly to the key columns, lookups by that key are the most direct access path and are backed by the two unique indexes.

Related Objects

  • OPI_EDW_INV_DAILY_STATUS_F_U2 — unique index enforcing the business key (INV_DAILY_STATUS_PK, INV_DAILY_STATUS_PK_KEY).
  • OPI_EDW_INV_DAILY_STAT_F_PK — primary key index on INV_DAILY_STATUS_PK_KEY.
  • Dimension tables referenced through INV_ORG_FK_KEY, ITEM_ORG_FK_KEY, PRD_DATE_FK_KEY, TRX_DATE_FK_KEY, and BASE_CURRENCY_FK_KEY within the OPI schema.
  • Base Oracle Inventory transactional tables (MTL_ONHAND_QUANTITIES, MTL_MATERIAL_TRANSACTIONS) that feed this fact table.
  • OPI ETL program units that populate OPI_EDW_INV_DAILY_STAT_F from source inventory tables.