Search Results opi_wms_009_mv

Overview

OPI_WMS_009_MV is a materialized view registered in the APPS schema within the Oracle E-Business Suite environment (12.1.1 and 12.2.2). It is owned by the FND – Application Object Library product family, which indicates that the object is delivered as part of the shared application foundation layer rather than a specific operational module. Functionally it belongs to the Oracle Process Manufacturing / Warehouse Management (OPM-WMS) reporting footprint, where objects carrying the OPI_WMS_* prefix are used to pre-aggregate warehouse activity data for downstream analytics, dashboards, and exception monitoring.

The view exposes a denormalized, time-bucketed projection of warehouse operation plans. The documented physical schema in the ETRM 12.1.1 repository contains 17 columns, and the presence of a functional unique index (I_SNAP$_OPI_WMS_009_MV) built on SYS_OP_MAP_NONNULL expressions confirms that the object is consumed as a snapshot for materialized-view refresh and fast refresh lookups. Following the heuristic Data Vault classification derived from its foreign-key structure, the object is best modeled as a standalone entity — it does not act as a conventional hub, link, or satellite because its only documented outbound relationship points to WMS_OP_PLANS_B and the remaining columns function as descriptive aggregation attributes rather than foreign-key joins.

Key Information Stored

The 17 documented columns support a multi-dimensional aggregation of warehouse exceptions across organizational and calendar hierarchies. The most significant columns are:

• ORGANIZATION_ID — the inventory organization that owns the aggregated warehouse activity; part of the business-key candidate.
• OPERATION_PLAN_ID — the parent warehouse operation plan; this is the sole documented foreign key, referencing WMS_OP_PLANS_B.
• SUBINVENTORY_CODE — the subinventory within the organization at which the activity occurred.
• INV_CATEGORY_ID and ITEM_ORG_ID — the inventory category and item-organization combination used to slice results by material grouping.
• REASON_ID — the reason code associated with the recorded exception.
• AGG_LEVEL and GRP_ID — control parameters that indicate the granularity of the aggregation and the grouping set applied.
• TIME_ID, PERIOD_TYPE_ID, QTR_ID, MONTH_ID, WEEK_ID, DAY_ID — the calendar hierarchy columns that allow the snapshot to be reported by day, week, month, quarter, or period type.
• EXCEPTIONS, EXCEPTIONS_CNT, TOTAL_CNT — the measured values: the exception detail, the count of exceptions, and the total count against which exceptions are compared.

The surrogate primary key is implicit in the materialized view snapshot; the business-key candidates are the twelve SYS_OP_MAP_NONNULL columns defined in the unique index I_SNAP$_OPI_WMS_009_MV, which together guarantee uniqueness across organization, plan, reason, subinventory, category, item, and every level of the time hierarchy.

Common Use Cases and Queries

The primary use case is exception-rate reporting for warehouse operations. Typical queries aggregate EXCEPTIONS_CNT against TOTAL_CNT to derive a compliance or exception percentage, filtered by organization and time bucket. For example:

• Exception percentage by subinventory for a given month: SELECT subinventory_code, SUM(exceptions_cnt)/SUM(total_cnt) FROM opi_wms_009_mv WHERE organization_id = :org AND month_id = :mth GROUP BY subinventory_code;
• Trend analysis across the calendar hierarchy using DAY_ID, WEEK_ID, MONTH_ID, and QTR_ID to roll up exception counts over time.
• Drill-down by item category or reason code to isolate the drivers of warehouse exceptions.
• Feeding BI Publisher or OBIEE dashboards that refresh from the materialized view rather than querying base transaction tables directly.

Because the object is a materialized view, refresh scheduling and staleness must be considered before relying on it for real-time operational decisions.

Related Objects

The most significant related object is the parent plan table referenced by the single documented foreign key:

• WMS_OP_PLANS_B — joined via OPI_WMS_009_MV.OPERATION_PLAN_ID = WMS_OP_PLANS_B.OPERATION_PLAN_ID; supplies plan header attributes absent from the snapshot.
• OPI_WMS_009 — the underlying base query or view that the MV is built from.
• WMS_OP_PLANS_TL — the translation table for plan descriptions, useful when surfacing plan names in reports.
• MTL_SYSTEM_ITEMS_B — joined via ITEM_ORG_ID for item descriptions.
• MTL_CATEGORIES_B — joined via INV_CATEGORY_ID for category names.
• MTL_SECONDARY_INVENTORIES — joined via SUBINVENTORY_CODE for subinventory attributes.
• FND_GLOBAL — supplies the session organization context used to filter ORGANIZATION_ID.

These objects collectively support the join paths required to render the snapshot into a fully described operational report.