Results for “item_cat_flag”

50+ results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

ISC_DBI_CPM_000_MV is a materialized view owned by the APPS schema in Oracle E-Business Suite, registered under the FND — Application Object Library product. Despite its physical classification as a TABLE in the ETRM metadata, the "_MV" suffix and the presence of a SYS_OP_MAP_NONNULL-based unique index (I_SNAP$_ISC_DBI_CPM_000_MV) confirm it is a snapshot materialized view, typically used to accelerate the Oracle Daily Business Intelligence (DBI) customer and product margin reporting layer. It carries 40 columns and serves as a pre-aggregated analytical structure rather than a transactional table.

The subject area centers on subscription and service lifecycle activity — activations, renewals, and expirations — sliced by customer, item category, product, organization, and multiple time dimensions. The heuristic Data Vault classification mined from its FK structure is standalone, meaning the object is not modeled as a classic hub, link, or satellite; it behaves as a self-contained, denormalized reporting aggregate whose single documented foreign key points outward to a product dimension.

Key Information Stored

The most significant columns cluster into three functional groups.

The ETRM metadata does not document a single-column surrogate primary key; the only unique index is the composite I_SNAP$_ISC_DBI_CPM_000_MV across CLASS_CODE, CUSTOMER_ID, ITEM_CATEGORY_ID, PRODUCT_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, SERVICE_ITEM_ID, ENT_QTR_ID, ENT_PERIOD_ID, WEEK_ID, REPORT_DATE_JULIAN, CUSTOMER_FLAG, ITEM_CAT_FLAG, and GRP_ID. These 14 attributes constitute the business-key candidate for the snapshot.

Common Use Cases and Queries

Because this is a DBI snapshot, queries are read-only and typically drive margin dashboards and period comparisons. A representative pattern joining the documented FK:

  • SELECT m.customer_id, m.product_id, p.product_name, m.ent_period_id, SUM(m.activated_g) activated, SUM(m.expired_g) expired FROM apps.isc_dbi_cpm_000_mv m JOIN apps.fnd_dm_products p ON m.product_id = p.product_id WHERE m.report_date_julian BETWEEN :start_julian AND :end_julian GROUP BY m.customer_id, m.product_id, p.product_name, m.ent_period_id;
  • Trend analysis of net activations (ACTIVATED_G minus EXPIRED_G) by fiscal quarter using ENT_QTR_ID.
  • Customer-level margin reporting filtered by CUSTOMER_FLAG to isolate flagged accounts.

Refresh is controlled by the DBI concurrent program schedule; because the object is a materialized view, ad hoc DML is not supported and all access must occur through SELECT statements.

Related Objects

The only explicit foreign key documented in the ETRM metadata is ISC_DBI_CPM_000_MV.PRODUCT_ID → FND_DM_PRODUCTS.PRODUCT_ID, the product dimension that supplies descriptive attributes for margin reporting. Additional dependencies, inferred from the DBI schema naming pattern and column semantics, include the fact and dimension tables feeding the snapshot: ISC_DBI_CPM_FACT and ISC_DBI_CPM_MV (base margin fact and view), FND_DM_PRODUCTS and its category hierarchy tables for ITEM_CATEGORY_ID resolution, the customer dimension (FND_DM_CUSTOMERS or equivalent CUSTOMER_ID source), and the time dimension supplying ENT_PERIOD_ID, ENT_QTR_ID, and WEEK_ID. The SYS_OP_MAP_NONNULL index structure is generated by the snapshot refresh process and is not itself a joinable object.