Search Results opi_pmi_ids_temp_pk

Overview

OPI_PMI_IDS_TEMP is a transient staging table owned by the OPI (Operations Intelligence) schema in Oracle E-Business Suite. It supports the Operations Intelligence product's inventory and material movement analytics by holding pre-aggregated, period-scoped transaction quantities keyed by organization, warehouse, and item dimensions. Because the name carries the "_TEMP" suffix, the object is designed for short-lived retention: rows are populated during an ETL or concurrent-program run, consumed by downstream reporting or cost-calculation logic, and typically truncated after the load completes. In release 12.1.1 the documented physical definition exposes 23 columns, and it remains valid in 12.2.2.

Applying a Data Vault classification heuristic to the mined key and foreign-key structure, this object resolves as a standalone entity. It carries neither inbound nor outbound foreign-key constraints in the documented relationship data. This suggests it is best modeled as an isolated staging construct rather than as a hub, link, or satellite in a normalized Data Vault design; if it were integrated into such a model, its composite natural key would serve as the basis for a link to inventory and item hubs rather than as a persistent satellite.

Key Information Stored

The table's grain is defined by the primary key constraint OPI_PMI_IDS_TEMP_PK, a composite unique index spanning seven columns: CO_CODE, ORGN_CODE, WHSE_CODE, LOCATION, ITEM_ID, LOT_ID, and TRX_DATE. This composite effectively serves as the business-key candidate; there is no separate single-column surrogate key documented. Each combination therefore identifies one period/transaction-date record for a specific item and lot at a specific storage location.

• CO_CODE – operating company / ledger-level identifier.
• ORGN_CODE – inventory organization owning the transaction.
• WHSE_CODE and LOCATION – warehouse and subinventory/locator position.
• ITEM_ID – inventory item master reference.
• LOT_ID – lot or batch control reference.
• TRX_DATE – transaction date anchoring the period bucket.
• BEG_ONH_QTY / END_ONH_QTY / AVG_ONH_QTY – opening, closing, and average on-hand quantities for the period.
• TOTAL_REC_QTY / TOT_ISSUES_QTY / TOT_CUST_SHIP_QTY / PO_DEL_QTY – receipt, issue, customer shipment, and PO delivery movement totals.
• INV_ADJ_QTY / WIP_ISSUE_QTY / WIP_COMP_QTY – inventory adjustment and Work in Process issue/completion quantities.
• FROM_ORG_QTY / TO_ORG_QTY – inter-organization transfer quantities in and out.
• PERIOD_FLAG / OPERATION_CODE – period state indicator and transaction classification.
• CREATION_DATE / LAST_UPDATE_DATE – standard EBS audit columns for row lifecycle.

Common Use Cases and Queries

Typical use is inventory movement and reconciliation reporting across a period. A representative query retrieves on-hand and flow quantities for a given organization and item range:

• SELECT orgn_code, item_id, lot_id, SUM(beg_onh_qty), SUM(end_onh_qty) FROM opi.opi_pmi_ids_temp WHERE trx_date BETWEEN :from_date AND :to_date GROUP BY orgn_code, item_id, lot_id;
• Reconciliation of receipts versus issues: compare TOTAL_REC_QTY against TOT_ISSUES_QTY and INV_ADJ_QTY by warehouse to identify unexplained variances.
• Inter-organization transfer audit using FROM_ORG_QTY and TO_ORG_QTY to confirm balanced movements.
• Period-close validation filtering on PERIOD_FLAG to confirm the staging set is complete before cost or analytics programs consume it.

Because rows are temporary, queries should be scoped tightly by date and organization to avoid scanning data mid-load.

Related Objects

The documented metadata records no foreign keys, so relationships are inferred from the shared business columns. The most significant associated objects are:

• MTL_SYSTEM_ITEMS_B – joined on ITEM_ID to resolve item descriptions.
• MTL_ITEM_LOCATIONS / MTL_PARAMETERS – joined on ORGN_CODE and LOCATION for warehouse context.
• MTL_LOT_NUMBERS – joined on LOT_ID for lot attributes.
• MTL_ONHAND_QUANTITIES – source of on-hand balances compared against BEG_ONH_QTY and END_ONH_QTY.
• MTL_MATERIAL_TRANSACTIONS – underlying transaction detail feeding the aggregated quantity columns.
• WIP_DISCRETE_JOBS / WIP_TRANSACTIONS – support the WIP_ISSUE_QTY and WIP_COMP_QTY figures.
• OPI_PMI_IDS and related OPI staging counterparts – conventional sibling objects in the Operations Intelligence temp family sharing the same key structure.