Search Results customer_flag




Overview
Explore in AI Chat

Overview

ISC_DBI_CPM_001_MV is a materialized-view-backed summary table owned by the APPS schema in Oracle E-Business Suite, physically stored in the APPS_TS_SUMMARY tablespace with a PCTFREE of 10. It belongs to the Contracts Intelligence (ISC) product family and specifically supports the Contracts Profitability Management (CPM) daily business intelligence dashboards used by service and contract organizations. The object is registered in FND Design Data as ISC.ISC_DBI_CPM_001_MV, confirming that it is a seeded, delivered object rather than a customer extension. It is populated by Oracle's concurrent refresh programs and is intended for read-only analytical access.

The table aggregates contract balance metrics by enterprise year, customer, product, item category, and inventory organization. Currency-denominated amounts are stored in more than one currency context, distinguished by column suffix and by the _G convention, which typically denotes Global (functional ledger) currency.

From a Data Vault modeling perspective, the heuristic classification of this object is standalone, with no parent-key relationships to other hubs or links beyond an inferred reference to FND_DM_PRODUCTS. In dimensional terms it behaves as a pre-joined fact plus conformed-dimension snapshot rather than a normalized hub, link, or satellite. Teams applying Data Vault should treat it as a denormalized reporting mart instead of a source-system entity.

Key Information Stored

The table contains 24 columns. The most significant are:

  • ENT_YEAR_ID — the enterprise year identifier; the primary time grain of the summary.
  • CUSTOMER_ID — the customer (party) identifier for contract counterparties.
  • CLASS_CODE — customer classification code, up to 30 characters, enabling customer-segment rollups.
  • ITEM_CATEGORY_ID — product category identifier for item hierarchy analysis.
  • PRODUCT_ID — item organization identifier; the only column with a documented foreign key, referencing FND_DM_PRODUCTS.
  • SERVICE_ITEM_ID — service item organization identifier.
  • INVENTORY_ITEM_ID and ORGANIZATION_ID — inventory-level identifiers for item and warehouse reporting.
  • CUSTOMER_FLAG — a grouping ID column for the Customer dimension, used by the refresh engine to signal which grouping level a row represents.
  • ITEM_CAT_FLAG — the equivalent grouping ID column for the Item dimension.
  • GRP_ID — an additional grouping ID column.
  • ACTIVE_G, ACTIVE_NEW_G, ACTIVE_RENEW_G — balances of active, newly acquired, and renewed contracts as of enterprise year end, in global currency.
  • C_ACTIVE_G, C_ACTIVE_NEW_G, C_ACTIVE_RENEW_G — the corresponding customer-side or current-period variants of the same measures.
  • ACTIVE_G1, ACTIVE_NEW_G1, ACTIVE_RENEW_G1 and their C_-prefixed counterparts, plus C_TOTAL — alternate measure sets, typically for a second currency context or comparison period.

A single unique function-based index, I_SNAP$_ISC_DBI_CPM_001_MV, spans all eleven grouping and key columns using SYS_OP_MAP_NONNULL so that nulls participate in uniqueness enforcement. These eleven columns constitute the composite business-key candidate. Four non-unique indexes (N1 through N4) support the most common filter combinations, each anchored on ENT_YEAR_ID, ITEM_CAT_FLAG, and CUSTOMER_FLAG.

Common Use Cases and Queries

This table is queried almost exclusively for contract revenue and backlog reporting. A typical query sums global-currency active contract balance by customer classification for a given year:

SELECT c.class_code, SUM(c.active_g) act, SUM(c.active_new_g) new_act
FROM apps.isc_dbi_cpm_001_mv c
WHERE c.ent_year_id = :year
AND c.customer_flag = :grp_level
GROUP BY c.class_code;

Because the grouping ID columns signal the aggregation level, report authors must filter CUSTOMER_FLAG, ITEM_CAT_FLAG, and GRP_ID to the desired level to avoid double counting rolls-up rows. Index N1 (ENT_YEAR_ID, ITEM_CAT_FLAG, CUSTOMER_FLAG, CUSTOMER_ID, ITEM_CATEGORY_ID) best serves customer-by-category analyses; N2 and N4 serve product-oriented drill-downs. Renewal versus new business comparisons use the ACTIVE_NEW_G and ACTIVE_RENEW_G columns, while multi-currency or prior-period comparisons use the G1 and C_ measure families and C_TOTAL. In 12.2.2 the table is refreshed by the same DBI concurrent programs as in 12.1.1; no structural difference is documented for the column list.

Related Objects

  • FND_DM_PRODUCTS — the sole documented foreign key target, joined through PRODUCT_ID, providing item organization and product hierarchy attributes.
  • APPS.ISC_DBI_CPM_002_MV (and sibling CPM summary objects) — peer daily-business-intelligence marts refreshed together for contract profitability.
  • ISC_DBI_CPM_001_SQ / associated refresh program units — the packaged logic that populates the materialized view.
  • CUSTOMER_ID → HZ_CUST_ACCOUNTS / HZ_PARTIES — inferred joins for customer name and classification resolution.
  • INVENTORY_ITEM_ID → MTL_SYSTEM_ITEMS_B and ORGANIZATION_ID → MTL_PARAMETERS — inferred joins for item and organization descriptions.
  • ITEM_CATEGORY_ID → MTL_CATEGORIES_B — inferred join for category naming in reports.
  • GL_LEDGERS / GL_CURRENCIES — inferred reference for interpreting the _G and G1 currency contexts.