Search Results inv_period_status_pk_key

Overview

OPI_EDW_IPS_NET_CHG is an Oracle Operations Intelligence (OPI) staging and analytics table that holds net change metrics for inventory positions across organizations, subinventories, and periods. In Oracle EBS 12.1.1 and 12.2.2, this object belongs to the OPI product family, which was Oracle's embedded operational intelligence layer used to feed the Enterprise Data Warehouse (EDW) with pre-aggregated supply chain and inventory movement data. The table is designed to capture period-over-period inventory net change, comparing beginning and ending balances for on-hand, in-transit, and WIP quantities and values.

The ETRM metadata explicitly classifies this object as "Not implemented in this database," and the OPI module itself is flagged as Obsolete in 12.1.1/12.2.2. It exists in the documented physical schema of the OPI owner with 99 columns in ETRM 12.1.1. Under the heuristic Data Vault classification mined from FK structure, OPI_EDW_IPS_NET_CHG is modeled as a standalone object—it does not participate as a dependent satellite tied to a parent hub via standard FK linkage beyond the ROW_ID reference. The single documented foreign key (ROW_ID → CS_SYSTEMS_ALL_B_TEMP) is unusual and suggests the column is used loosely as a system/temp reference rather than a true referential constraint.

Key Information Stored

The table's 99 columns are organized around inventory quantity and value pairs, each duplicated in "basic" (_B) and "global" (_G) currency contexts. The most significant columns are:

• ROW_ID — surrogate identifier; documented as the FK column referencing CS_SYSTEMS_ALL_B_TEMP.
• INV_ORG_FK_KEY — inventory organization dimension key.
• ITEM_ORG_FK_KEY — item/organization relationship key.
• INV_PERIOD_STATUS_PK / INV_PERIOD_STATUS_PK_KEY — period status surrogate and business key for the accounting/period dimension.
• BEG_ONH_QTY, BEG_WIP_QTY, BEG_INT_QTY — beginning balances for on-hand, WIP, and in-transit.
• END_ONH_QTY, END_WIP_QTY, END_INT_QTY — ending balances for the same three states.
• AVG_ONH_QTY, AVG_WIP_QTY, AVG_INT_QTY — period averages.
• TOTAL_REC_QTY, TOT_ISSUES_QTY, TOT_CUST_SHIP_QTY, PO_DEL_QTY, INV_ADJ_QTY — movement drivers (receipts, issues, customer shipments, PO deliveries, adjustments).
• FROM_ORG_QTY, TO_ORG_QTY — inter-org transfer quantities.
• WIP_ASSY_QTY, WIP_COMP_QTY, WIP_ISSUE_QTY — WIP completion, assembly, and issue movements.
• BASE_CURRENCY_FK_KEY, BASE_UOM_FK_KEY, COST_GROUP, ITEM_TYPE, ITEM_STATUS — dimensional context.
• USER_ATTRIBUTE1–15, USER_MEASURE1–5, USER_FK1–5 — extensibility columns.

Business-key candidates are not formally documented as unique indexes; the surrogate-integer FK-style keys (like INV_PERIOD_STATUS_PK_KEY) act as the closest business identifiers.

Common Use Cases and Queries

Typical usage centers on period-over-period inventory variance reporting:

• Reconciling beginning vs. ending on-hand balances for an inventory organization and period, computing net change:

SELECT INV_ORG_FK_KEY, INV_PERIOD_STATUS_PK_KEY, SUM(END_ONH_QTY - BEG_ONH_QTY) AS NET_ONH_CHG FROM OPI.OPI_EDW_IPS_NET_CHG GROUP BY INV_ORG_FK_KEY, INV_PERIOD_STATUS_PK_KEY;

• Explaining movement by driver: comparing TOTAL_REC_QTY, TOT_ISSUES_QTY, and INV_ADJ_QTY to validate the bridge between BEG_ONH_QTY and END_ONH_QTY.
• WIP analysis: evaluating WIP_ASSY_QTY and WIP_COMP_QTY against BEG_WIP_QTY/END_WIP_QTY for manufacturing efficiency reporting.
• Currency-aware analytics: comparing _B and _G value columns to detect FX impact.

Because the module is obsolete, most customers query equivalent data directly from MTL_TRANSACTIONS, MTL_ONHAND_QUANTITIES, and the inventory period-close tables rather than relying on this staging object.

Related Objects

Given the standalone classification, direct relationships are limited. The key documented and inferred related objects are:

• CS_SYSTEMS_ALL_B_TEMP — joined via OPI_EDW_IPS_NET_CHG.ROW_ID.
• MTL_SYSTEM_ITEMS_B / MTL_ITEM_ORG — source for ITEM_ORG_FK_KEY.
• ORG_ORGANIZATION_DEFINITIONS — source for INV_ORG_FK_KEY.
• GL_PERIOD_STATUSES / ORG_ACCT_PERIODS — source for INV_PERIOD_STATUS_PK.
• MTL_ONHAND_QUANTITIES — source of BEG_ONH_QTY/END_ONH_QTY.
• MTL_TRANSACTIONS — source of movement metrics (receipts, issues, adjustments).
• WIP_DISCRETE_JOBS / WIP_ENTITIES — source for WIP_ASSY_QTY, WIP_COMP_QTY, WIP_ISSUE_QTY.
• RCV_SHIPMENT_LINES — source of PO_DEL_QTY.

As with all OPI EDW staging tables, referential integrity is enforced logically rather than via database constraints, reflecting its role as a dimensional-model extraction target.