Search Results opi_wms_008_mv

Overview

OPI_WMS_008_MV is a materialized view registered in the APPS schema under the FND - Application Object Library product family in Oracle EBS 12.1.1 and 12.2.2. It functions as an aggregated warehouse execution snapshot, consolidating operational plan performance metrics across organization, subinventory, item category, and hierarchical time dimensions. The object exposes 22 documented columns and is defined with a single unique index, I_SNAP$_OPI_WMS_008_MV, indicating a snapshot-based materialized view created with the SYS_OP_MAP_NONNULL function to enforce uniqueness on nullable business keys.

From a heuristic Data Vault modeling perspective, this object behaves as a satellite rather than a hub or link. Its grain is a composite of dimensional identifiers (organization, subinventory, category, item, plan, aggregation level, group, and time), and its measures are additive counters and elapsed-time metrics. The foreign key relationship to WMS_OP_PLANS_B.OPERATION_PLAN_ID anchors the satellite to the warehouse operation plan hub, while the remaining key columns serve as descriptive dimensional context.

Key Information Stored

The most significant columns fall into three functional groups. The dimensional key set comprises ORGANIZATION_ID, SUBINVENTORY_CODE, INV_CATEGORY_ID, ITEM_ORG_ID, OPERATION_PLAN_ID, AGG_LEVEL, and GRP_ID. These columns, combined with the time identifiers TIME_ID, PERIOD_TYPE_ID, QTR_ID, MONTH_ID, WEEK_ID, and DAY_ID, form the composite business key enforced by the unique index I_SNAP$_OPI_WMS_008_MV. The presence of SYS_OP_MAP_NONNULL in the index definition confirms that null values are permitted in these columns and are treated as distinct for uniqueness enforcement.

The time hierarchy columns (QTR_ID, MONTH_ID, WEEK_ID, DAY_ID) allow drill-down reporting by calendar period, while PERIOD_TYPE_ID and TIME_ID establish the base time dimension. The plan reference column OPERATION_PLAN_ID is the only documented foreign key, pointing to WMS_OP_PLANS_B. It links each aggregated row to its parent operation plan.

The measure columns capture execution performance: EXECUTIONS, EXEC_WITH_EXCEPTIONS, and EXCEPTIONS provide absolute counts, while EXECUTIONS_CNT, EXEC_WITH_EXCEPTIONS_CNT, EXCEPTIONS_CNT, and TOTAL_CNT supply denominator counts for rate calculations. ELAPSED_TIME and ELAPSED_TIME_CNT support average-duration analysis. Notably, the metadata does not document a surrogate primary key column; uniqueness is maintained solely through the composite business index.

Common Use Cases and Queries

Typical reporting scenarios include warehouse execution dashboards, exception-rate trending, and plan-versus-actual throughput analysis. A representative query aggregates execution success rates by organization and month:

• SELECT organization_id, month_id, SUM(executions_cnt) total_exec, SUM(executions) exec_ok, SUM(executions) / NULLIF(SUM(executions_cnt),0) success_rate FROM opi_wms_008_mv WHERE operation_plan_id = :plan GROUP BY organization_id, month_id ORDER BY organization_id, month_id;
• Exception trending by subinventory: SELECT subinventory_code, day_id, SUM(exceptions) exceptions FROM opi_wms_008_mv WHERE organization_id = :org AND day_id BETWEEN :from_day AND :to_day GROUP BY subinventory_code, day_id;
• Average elapsed time per execution at the item-category level: SELECT inv_category_id, item_org_id, SUM(elapsed_time) / NULLIF(SUM(elapsed_time_cnt),0) avg_elapsed FROM opi_wms_008_mv GROUP BY inv_category_id, item_org_id;

Because this is a materialized view, refresh scheduling and staleness must be considered; report consumers should verify the last refresh timestamp through DBA_MVIEWS or ALL_MVIEWS before relying on the data for real-time decisions.

Related Objects

The documented foreign key establishes WMS_OP_PLANS_B as the primary parent table, joined on OPERATION_PLAN_ID. The surrounding warehouse management schema typically includes related objects that share the same dimensional grain and are frequently joined in reporting:

• WMS_OP_PLANS_B — the operation plan master; join on OPERATION_PLAN_ID.
• WMS_OP_PLANS_TL — translated plan descriptions for multilingual reporting.
• MTL_SYSTEM_ITEMS_B — item master, joined via ITEM_ORG_ID and inventory item references.
• MTL_ITEM_CATEGORIES — category assignments supporting INV_CATEGORY_ID filtering.
• MTL_SECONDARY_INVENTORIES — subinventory definitions keyed by ORGANIZATION_ID and SUBINVENTORY_CODE.
• ORG_ORGANIZATION_DEFINITIONS — organization context for ORGANIZATION_ID.
• GL_CALENDAR relationships via TIME_ID / PERIOD_TYPE_ID for fiscal period alignment.
• Other OPI_WMS_###_MV snapshots in the same product family, which share the AGG_LEVEL and GRP_ID aggregation framework.

Because the object is a standalone-classified satellite with a single documented parent, dependency analysis should focus on its materialized view definition and refresh job rather than on outbound foreign keys, none of which are documented.