Search Results opi_inv_cca_sum_mv

Overview

OPI_INV_CCA_SUM_MV is an APPS-owned materialized view registered as a table within the FND – Application Object Library product group in Oracle EBS 12.1.1 and 12.2.2. It stores pre-aggregated cycle count analysis data. Cycle counting is the periodic physical verification of on-hand inventory against system balances, and this object consolidates the results of that activity into summary rows so that inventory accuracy, adjustment activity, and hit/miss performance can be reported without scanning transactional detail. The "MV" suffix and the presence of an I_SNAP$_ unique index confirm that the object is a materialized view built with a refresh mechanism, rather than a base transactional table.

The FK evidence classifies this object as standalone in Data Vault terms: it references other tables but is not itself referenced by dependent child objects. In a Data Vault model this pattern suggests a satellite-like structure that carries descriptive measures keyed to dimensions such as organization, item, subinventory, category, and calendar period, rather than a hub or link. The absence of inbound foreign keys reinforces its role as a reporting aggregation target rather than a master data source.

Key Information Stored

The object contains 68 documented columns, organized around a composite aggregation key and a large block of paired count/quantity measures.

• Dimension keys: ORGANIZATION_ID, SUBINVENTORY_CODE, INV_CATEGORY_ID, ITEM_ORG_ID, UOM_CODE, CYCLE_COUNT_HEADER_ID, and CYCLE_COUNT_CLASS_ID establish the scope of each aggregated row.
• Calendar dimensions: TIME_ID, PERIOD_TYPE_ID, YEAR_ID, QTR_ID, MONTH_ID, WEEK_ID, and DAY_ID support period-based trending.
• Aggregation control: AGGREGATION_LEVEL_FLAG, SOURCE, and GRP_ID identify the grain at which the summary was produced.
• Performance counters: NUMBER_OF_TOTAL_ENTRIES, NUMBER_OF_HITS, NUMBER_OF_MISSES, and NUMBER_OF_EXACT_MATCHES provide cycle count accuracy metrics.
• Adjustment measures: NUMBER_OF_ADJUSTMENTS, POSITIVE_ADJUSTMENT_QTY, NEGATIVE_ADJUSTMENT_QTY, GROSS_ADJUSTMENT_QTY, and NET_ADJUSTMENT_QTY quantify correction activity.
• Valuation measures: POSITIVE_ADJUSTMENT_VAL_B (base currency), _VAL_G (group currency), and _VAL_SG (secondary group currency) variants extend across positive, negative, gross, net, and system inventory categories.
• System inventory: SYSTEM_INVENTORY_QTY and its valuation counterparts capture the book balance at the time of the count.

The unique index I_SNAP$_OPI_INV_CCA_SUM_MV, built with SYS_OP_MAP_NONNULL over fifteen columns including ORGANIZATION_ID, SOURCE, SUBINVENTORY_CODE, INV_CATEGORY_ID, CYCLE_COUNT_HEADER_ID, and the calendar descriptors, serves as the effective business key. No separate surrogate primary key column is documented; the composite index is the primary means of row uniqueness.

Common Use Cases and Queries

The primary use case is cycle count accuracy and adjustment reporting. Analysts compare summary rows across periods to measure count performance by organization, subinventory, or category. A representative query filters by calendar period and organization:

• SELECT organization_id, subinventory_code, inv_category_id, period_type_id,
•        NUMBER_OF_HITS, NUMBER_OF_TOTAL_ENTRIES,
•        NUMBER_OF_ADJUSTMENTS, NET_ADJUSTMENT_QTY, NET_ADJUSTMENT_VAL_B
•   FROM opi_inv_cca_sum_mv
•  WHERE organization_id = :org_id
•    AND month_id = :month_id
•    AND aggregation_level_flag = :level;

Hit-rate derivation (NUMBER_OF_HITS divided by NUMBER_OF_TOTAL_ENTRIES) and adjustment-to-inventory ratios against SYSTEM_INVENTORY_VAL_B are typical KPI calculations. Because the object is pre-aggregated, these queries avoid the cost of joining transactional cycle count and adjustment tables, making it suitable for dashboards, exception reports, and period-over-period variance analysis using the YEAR_ID, QTR_ID, MONTH_ID, and WEEK_ID descriptors.

Related Objects

The documented foreign keys and the aligned column names establish the following significant relationships:

• MTL_CYCLE_COUNT_HEADERS — joined via OPI_INV_CCA_SUM_MV.CYCLE_COUNT_HEADER_ID → MTL_CYCLE_COUNT_HEADERS.CYCLE_COUNT_HEADER_ID; supplies the count schedule context.
• JAI_FA_AST_YEARS — joined via OPI_INV_CCA_SUM_MV.YEAR_ID → JAI_FA_AST_YEARS; the fiscal year calendar source.
• MTL_CYCLE_COUNT_CLASSES — the class definition implied by CYCLE_COUNT_CLASS_ID.
• MTL_SYSTEM_ITEMS_B / MTL_ITEM_LOCATIONS — resolved through ITEM_ORG_ID, INV_CATEGORY_ID, and SUBINVENTORY_CODE for descriptive attributes.
• MTL_SECONDARY_INVENTORIES — the subinventory master referenced by SUBINVENTORY_CODE.
• ORG_ORGANIZATION_DEFINITIONS — the organization master for ORGANIZATION_ID.
• MTL_CC_ENTRIES / MTL_ADJUSTMENTS — the transactional sources from which the materialized view is refreshed.

Because the object is classified as standalone with no inbound foreign keys, no dependent tables reference it directly; consumers should treat it as a terminal reporting layer.