Search Results opi_ids_push_log_n3

Overview

OPI.OPI_IDS_PUSH_LOG is a staging and summary table owned by the OPI schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It serves the Enterprise Data Warehouse (EDW) inventory daily status program. Daily inventory activity is extracted from the source instance OLTP tables into this table before being transferred to the source-side staging table OPI_EDW_INV_DAILY_STAT_FSTG. The table tracks beginning and ending values of all inventory activity occurring on any given day, providing a daily snapshot of onhand, intransit, and WIP inventory quantities and values.

The physical storage resides in the APPS_TS_SUMMARY tablespace with PCT Free of 10, reflecting its role as a summary-level object rather than a transaction-detail store. The documented schema contains 67 columns, with a single-row-per-combination grain keyed by IDS_KEY. Mined foreign key metadata shows a reference to CST_COST_GROUPS through COST_GROUP_ID.

Heuristic Data Vault classification suggests this object models as a satellite. Its columns are overwhelmingly descriptive measures and attributes (quantities, values, flags) attached to a business key composed of date, organization, item, subinventory, locator, cost group, and lot. It behaves as a hub-anchored satellite rather than a link, since it captures state and activity measures rather than resolving many-to-many relationships between hubs.

Key Information Stored

The surrogate primary key is IDS_KEY, a VARCHAR2(240) column defined as the unique primary key via OPI_IDS_PUSH_LOG_PK. It is a concatenation of date, organization id, inventory item id, subinventory code, locator id, cost group id, and lot number, making it also the principal business-key candidate. No separate single-column unique business key exists; uniqueness is enforced only through this composite-style key.

• ORGANIZATION_ID, INVENTORY_ITEM_ID, TRX_DATE, SUBINVENTORY_CODE, LOCATOR_ID, LOT_NUMBER, COST_GROUP_ID — the grain-determining identifiers.
• AVG_ONH_QTY, AVG_INT_QTY, AVG_WIP_QTY — average of beginning and ending quantities for onhand, intransit, and WIP inventory.
• AVG_ONH_VAL_B, AVG_INT_VAL_B, AVG_WIP_VAL_B — corresponding average values in functional currency.
• BEG_ONH_QTY, BEG_INT_QTY, BEG_WIP_QTY — beginning balances; paired END_* columns hold ending balances.
• BASE_CURRENCY_CODE — functional currency of the source instance.
• BASE_UOM, EDW_BASE_UOM, EDW_CONV_RATE, EDW_UOM_CONV_RATE — unit-of-measure and conversion attributes governing the EDW load; EDW_UOM_CONV_RATE is the conversion rate applied to the item's unit of measure.
• PUSH_FLAG — controls transfer to the downstream staging table; indexed by OPI_IDS_PUSH_LOG_N3.
• PERIOD_FLAG, NETTABLE_FLAG, ITEM_STATUS, ITEM_TYPE, REVISION, COMMODITY_CODE — classification and eligibility attributes.

Common Use Cases and Queries

The table supports EDW inventory reporting, period-end valuation, and daily activity reconciliation. A typical extraction query selects unconsumed rows for transfer to the downstream staging table:

SELECT IDS_KEY, ORGANIZATION_ID, INVENTORY_ITEM_ID, TRX_DATE, AVG_ONH_QTY, AVG_ONH_VAL_B, EDW_UOM_CONV_RATE FROM OPI.OPI_IDS_PUSH_LOG WHERE PUSH_FLAG = 'N';

Reporting by organization and day benefits from index OPI_IDS_PUSH_LOG_N2 (ORGANIZATION_ID, TRX_DATE), while item-level analysis uses OPI_IDS_PUSH_LOG_N1 (INVENTORY_ITEM_ID, ORGANIZATION_ID, TRX_DATE). The search term "edw_uom_conv_rate" maps directly to the EDW_UOM_CONV_RATE column, commonly used to reconcile source unit-of-measure quantities against EDW-normalized values during load validation.

Related Objects

• CST_COST_GROUPS — referenced by COST_GROUP_ID; cost group classification of inventory.
• OPI_EDW_INV_DAILY_STAT_FSTG — downstream source-side staging table receiving pushed rows.
• MTL_SYSTEM_ITEMS_B / MTL_PARAMETERS — source OLTP tables for item and organization attributes.
• MTL_ONHAND_QUANTITIES_DETAIL — origin of onhand quantity and locator data.
• WIP_DISCRETE_JOBS / WIP_TRANSACTIONS — origin of WIP quantity and value measures.
• OPI_EDW_INV_DAILY_STAT — the target daily status summary consumed by EDW reporting.