Search Results ids_key

Overview

OPI_IDS_PUSH_LOG is a staging and audit table belonging to the OPI (Operations Intelligence) product, an obsolete module in Oracle EBS 12.1.1 and 12.2.2. The table records the push of inventory and costing snapshot data into the Operations Intelligence data warehouse or reporting layer. Each row represents a summarized inventory position for a given item, organization, subinventory, locator, lot, revision, and transaction date, along with beginning, ending, average, and transactional quantity and value balances.

The ETRM metadata notes that the object is "Not implemented in this database," indicating that in many environments the table exists only as a dormant schema artifact. Where it is populated, it functions as a historical log of periodic inventory and cost balance extracts, with a PUSH_FLAG and PERIOD_FLAG controlling which records have been transmitted to downstream targets. The heuristic Data Vault classification is standalone, meaning the table has no incoming or outgoing hub/link relationships in the mined FK structure. From a modeling perspective it is best treated as a satellite-style fact staging table keyed by a single surrogate identifier, rather than a hub or link.

Key Information Stored

The documented physical schema contains 67 columns. The most significant are:

• IDS_KEY — the surrogate primary key (OPI_IDS_PUSH_LOG_PK) and the column commonly referenced in searches for "ids_key." It uniquely identifies each pushed inventory snapshot row.
• ORGANIZATION_ID, INVENTORY_ITEM_ID, SUBINVENTORY_CODE, LOCATOR_ID, REVISION, LOT_NUMBER, ITEM_STATUS, ITEM_TYPE — the inventory dimension business keys defining the item position.
• COST_GROUP_ID — foreign key to CST_COST_GROUPS, the primary documented relationship.
• COMMODITY_CODE, BASE_UOM, BASE_CURRENCY_CODE, EDW_BASE_UOM, EDW_CONV_RATE, EDW_UOM_CONV_RATE — costing and unit-of-measure conversion attributes for the warehouse extract.
• BEG_*/AVG_*/END_* (ONH, INT, WIP quantity and value columns) — beginning, average, and ending on-hand, intransit, and WIP balances.
• TOT_ISSUES_*, TOTAL_REC_*, TOT_CUST_SHIP_*, PO_DEL_*, INV_ADJ_*, FROM_ORG_*, TO_ORG_*, WIP_* — transactional quantity and value movement buckets.
• TRX_DATE, PERIOD_FLAG, PUSH_FLAG, NETTABLE_FLAG — period, push status, and nettable indicators.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID — concurrent program lineage; plus the standard WHO audit columns CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY.

Only IDS_KEY is documented as a single-column PK; no separate unique business-key index is recorded, so composite inventory dimensions serve as the effective business identity.

Common Use Cases and Queries

Typical uses include reconciling Operations Intelligence extracts against the source inventory balances, auditing which records have been pushed, and reconstructing historical inventory value by period. A representative query:

• Locate un-pushed rows: SELECT IDS_KEY, ORGANIZATION_ID, INVENTORY_ITEM_ID FROM OPI_IDS_PUSH_LOG WHERE PUSH_FLAG = 'N' AND PERIOD_FLAG = 'Y';
• Join to cost groups: SELECT p.IDS_KEY, c.COST_GROUP_ID, p.END_ONH_VAL_B FROM OPI_IDS_PUSH_LOG p, CST_COST_GROUPS c WHERE p.COST_GROUP_ID = c.COST_GROUP_ID;
• Trailing a specific extract: filter on REQUEST_ID or TRX_DATE to audit a single concurrent program run.

Related Objects

The documented foreign-key relationship links this table to:

• CST_COST_GROUPS — joined on COST_GROUP_ID; the sole documented FK reference, classifying the cost group for each pushed row.
• MTL_SYSTEM_ITEMS_B — implied via INVENTORY_ITEM_ID and ORGANIZATION_ID.
• MTL_ITEM_LOCATIONS / MTL_LOT_NUMBERS — implied via LOCATOR_ID and LOT_NUMBER.
• MTL_SECONDARY_INVENTORIES — implied via SUBINVENTORY_CODE.
• CST_PERIOD_CLOSE_SUMMARY or equivalent costing period tables — related through TRX_DATE and PERIOD_FLAG.
• OPI_IDS_PUSH_LOG_PK — the named primary-key constraint over IDS_KEY.

Because the module is obsolete, dependency and referential integrity are minimal, and the table should be treated as a historical staging artifact rather than an active transactional source.