Search Results isc_dbi_cpm_001_mv




Overview
Explore in AI Chat

Overview

The ISC_DBI_CPM_001_MV object is a materialized view owned by the APPS schema in Oracle E-Business Suite, historically associated with the ISC – Supply Chain Intelligence product family. The ETRM metadata explicitly flags this product area as obsolete, and the object itself is documented as not implemented in the reference database. Consequently, ISC_DBI_CPM_001_MV should be treated as a legacy Daily Business Intelligence (DBI) construct: a pre-aggregated snapshot used by Supply Chain Intelligence dashboards to deliver fast analytic responses over transactional supply chain data without querying base tables directly.

The view carries 24 documented columns across 12.1.1 and exposes a composite unique index, I_SNAP$_ISC_DBI_CPM_001_MV, built with SYS_OP_MAP_NONNULL wrappers—a signature of materialized-view refresh infrastructure that guarantees uniqueness even when individual key columns are null. From a Data Vault modeling perspective (a heuristic classification derived from the FK structure), the object behaves as a satellite: it holds descriptive and aggregated measures keyed against dimensional identifiers rather than acting as an integrating hub or link. This classification is a modeling suggestion only, not a documented design contract.

Key Information Stored

The materialized view captures a customer/item/period snapshot, most likely at a monthly or fiscal granularity, with paired keys and measures:

  • ENT_YEAR_ID – the fiscal or entitlement year identifier anchoring the time dimension of each snapshot row.
  • GRP_ID – grouping identifier used to partition or categorize the aggregate set.
  • CUSTOMER_ID – the customer party referenced by the row; paired with the boolean-style CUSTOMER_FLAG.
  • CLASS_CODE – classification code segmenting the customer or item population.
  • PRODUCT_ID – the product dimension key and the only documented foreign key target, referencing FND_DM_PRODUCTS. Note that FND_DM_PRODUCTS is a nonexistent or legacy placeholder, which further confirms the object's obsolete status.
  • ITEM_CATEGORY_ID and ITEM_CAT_FLAG – the item category key and its associated classification flag.
  • INVENTORY_ITEM_ID and ORGANIZATION_ID – the inventory item and inventory organization keys, tying the snapshot to specific supply chain contexts.
  • SERVICE_ITEM_ID – the service item or serviceable product reference.
  • ACTIVE_G, ACTIVE_NEW_G, ACTIVE_RENEW_G and their C_ prefixed counterparts – cumulative and count measures (totals, new activations, renewals, and cross-period or calculated variants).
  • ACTIVE_G1, ACTIVE_NEW_G1, ACTIVE_RENEW_G1 and C_ACTIVE_G1, C_ACTIVE_NEW_G1, C_ACTIVE_RENEW_G1 – a second measure set, typically representing a prior period or comparison baseline.
  • C_TOTAL – the consolidated count total used as the headline metric.

The column set contains no single surrogate primary key column; uniqueness is enforced only through the multi-column unique index, making the composite of ENT_YEAR_ID, CLASS_CODE, CUSTOMER_ID, SERVICE_ITEM_ID, ITEM_CATEGORY_ID, PRODUCT_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, CUSTOMER_FLAG, ITEM_CAT_FLAG, and GRP_ID the de facto business key. Any analytic join against this object must use that combination to avoid fan-out.

Common Use Cases and Queries

Because the object is not implemented, direct querying is typically only relevant in historical or migrated environments. Typical patterns include period-over-period comparison of active, new, and renewed counts:

  • Comparing current-period measures (ACTIVE_G) against the baseline set (ACTIVE_G1) to derive growth percentages by customer or item category.
  • Rolling up C_TOTAL by ORGANIZATION_ID or CLASS_CODE for Supply Chain Intelligence dashboard tiles.
  • Drilling from PRODUCT_ID into the product dimension hierarchy.
  • Filtering on CUSTOMER_FLAG and ITEM_CAT_FLAG to separate customer-oriented and item-oriented refresh slices.

A representative pattern:

SELECT class_code, organization_id, SUM(c_total) AS total_cnt, SUM(c_active_g1) AS prior_cnt FROM isc_dbi_cpm_001_mv WHERE ent_year_id = &year AND customer_flag = 'Y' GROUP BY class_code, organization_id;

Reporting should prefer public DBI interfaces or base transactional tables over this materialized view in supported releases.

Related Objects

  • FND_DM_PRODUCTS – the sole documented FK target, joined on ISC_DBI_CPM_001_MV.PRODUCT_ID = FND_DM_PRODUCTS.PRODUCT_ID.
  • I_SNAP$_ISC_DBI_CPM_001_MV – the unique index implementing the snapshot's business key and used for refresh de-duplication.
  • DBI materialized-view refresh jobs – concurrent programs that populated the snapshot from underlying staging and fact tables.
  • Supply Chain Intelligence dashboard definitions – BIP or JSP report metadata that consumed this view.
  • Related ISC_DBI snapshot views sharing the ISC_DBI_CPM_ naming prefix and the same customer/item dimension pattern.

Given its obsolete classification, implementations on 12.1.1 or 12.2.2 should verify whether the object exists via DBA_MVIEWS before relying on it, and should otherwise substitute current DBI or OTBI data sources in its place.