Search Results opi_inv_val_sum_mv




Overview
Explore in AI Chat

Overview

OPI_INV_VAL_SUM_MV is a materialized view owned by the APPS schema in Oracle E-Business Suite, registered under the FND – Application Object Library product. It functions as a pre-aggregated inventory valuation summary, consolidating on-hand, in-transit, and work-in-process (WIP) values across organization, subinventory, category, and time dimensions. In 12.1.1 the object exposes 44 physical columns. Its presence as a materialized view indicates it supports reporting performance, allowing inventory valuation inquiries and analytical dashboards to avoid querying transactional inventory tables directly.

From a heuristic Data Vault modeling perspective, the mined FK structure classifies this object as standalone. This suggests it does not function as a classic hub, link, or satellite in a normalized Data Vault sense; rather, it behaves as a self-contained aggregate/snapshot structure. It may be treated as a derived reporting object that summarizes facts without distributing normalized business keys into separate hub tables.

Key Information Stored

The materialized view stores dimensional identifiers and additive value/quantity measures. Its unique business-key candidate is defined by the index I_SNAP$_OPI_INV_VAL_SUM_MV, which spans the non-null forms of ORGANIZATION_ID, SOURCE, SUBINVENTORY_CODE, INV_CATEGORY_ID, UOM_CODE, ITEM_ORG_ID, YEAR_ID, QTR_ID, MONTH_ID, WEEK_ID, DAY_ID, AGGREGATION_LEVEL_FLAG, and GRP_ID. Unlike a single surrogate primary key, this composite unique index effectively serves as the grain definition for the aggregate.

The most significant columns include:

  • ORGANIZATION_ID — inventory organization context.
  • SUBINVENTORY_CODE — subinventory-level breakdown.
  • ITEM_ORG_ID — item and organization reference.
  • INV_CATEGORY_ID — inventory category dimension.
  • SOURCE and GRP_ID — source system and grouping identifiers.
  • AGGREGATION_LEVEL_FLAG — indicates the aggregation grain of the row.
  • YEAR_ID, QTR_ID, MONTH_ID, WEEK_ID, DAY_ID — time dimension hierarchy.
  • ONHAND_VALUE_G / ONHAND_VALUE_B / ONHAND_VALUE_SG — on-hand values expressed in different currencies or bases, with corresponding COUNT_OVG, COUNT_OVB, and COUNT_OVSG count measures.
  • INTRANSIT_VALUE_* / INTRANSIT_QTY — in-transit inventory values and quantities.
  • WIP_VALUE_* — work-in-process valuation amounts.
  • INV_TOTAL_VALUE_* — total inventory valuation roll-ups.
  • COUNT_TOTAL — total record or item count for the aggregate.

Common Use Cases and Queries

Typical usage centers on inventory valuation reporting by period and organization. A common pattern filters on the time dimension and organization:

  • SELECT organization_id, subinventory_code, SUM(onhand_value_g), SUM(intransit_value_g), SUM(wip_value_g), SUM(inv_total_value_g) FROM opi_inv_val_sum_mv WHERE year_id = :year AND month_id = :month GROUP BY organization_id, subinventory_code;
  • Comparing on-hand versus in-transit and WIP valuations for a subinventory or category.
  • Trending inventory value across months using YEAR_ID, QTR_ID, MONTH_ID, WEEK_ID, and DAY_ID.
  • Reconciling aggregated totals back to transactional inventory balances.

Because it is a materialized view, queries should account for refresh timing; the data may lag source transactions depending on the refresh schedule.

Related Objects

The documented FK relationship ties YEAR_ID to JAI_FA_AST_YEARS. Beyond this, the materialized view depends conceptually on inventory organization, item, category, and period objects referenced by its dimensional columns. Its key relationships include:

These relationships should be validated against the source views underlying the materialized view, as the ETRM metadata records only the YEAR_ID foreign key explicitly.