Search Results isc_dr_service_code_mv




Overview
Explore in AI Chat

Overview

ISC_DR_SERVICE_CODE_MV is a materialized view container registered in the APPS schema under the FND - Application Object Library product grouping within Oracle E-Business Suite 12.1.1 and 12.2.2. The object functions as a pre-aggregated reporting structure for Service (the "ISC" prefix denotes Service Contracts/Service module lineage, while the "DR" segment reflects a demand/repair summary category) metrics, specifically counting repair service-code activity. It summarizes transactional activity against service codes and repair types across multiple time hierarchies (quarter, month, week, day) and organizational dimensions (item organization, repair organization, product category, customer).

The heuristic Data Vault classification mined from the foreign-key structure is standalone. That is, the entity does not participate in the surrogate-key hub or link chains that characterize a normalized Data Vault model; it is instead a denormalized reporting aggregate. Modelers should treat it as a derived, non-authoritative snapshot rather than a system-of-record table.

Key Information Stored

The physical schema contains 16 columns. The principal business-key candidates are expressed through the unique index I_SNAP$_ISC_DR_SERVICE_COD, which combines eleven non-nullable dimensions:

The measures are RO_COUNT (repair-order count) and MV_COUNT (materialized-view aggregate count), complemented by AGGREGATION_FLAG, which indicates whether the row is a base-level record or a rolled-up total. There is no independent surrogate primary key beyond the composite unique-index construct; the eleven-column business key defines row uniqueness.

Common Use Cases and Queries

This object is used primarily for service-desk and depot-repair analytics: trending repair volumes by service code, benchmarking repair organizations, and rolling up demand by customer or product category.

A typical trend query filters to a single service code and month grain:

  • SELECT month_id, repair_organization_id, SUM(ro_count) FROM isc_dr_service_code_mv WHERE service_code_id = :p_code AND period_type_id = :p_month GROUP BY month_id, repair_organization_id;

Because it is a materialized view, refresh scheduling (fast vs. complete) and staleness must be considered before using it for operational reporting. For near-real-time needs, source tables CSD_SERVICE_CODES_B and CSD_REPAIR_TYPES_B should be queried directly. Bulk extracts for BI publishers should filter on AGGREGATION_FLAG to avoid double-counting rolled-up rows.

Related Objects

  • CSD_SERVICE_CODES_B — joined via SERVICE_CODE_ID; supplies code descriptions.
  • CSD_REPAIR_TYPES_B — joined via REPAIR_TYPE_ID; supplies repair-type names.
  • CSD_SERVICE_CODES_TL — translation table for service-code display values.
  • CSD_REPAIR_TYPES_TL — translation table for repair-type display values.
  • HR_ALL_ORGANIZATION_UNITS — resolves REPAIR_ORGANIZATION_ID and ITEM_ORG_ID.
  • MTL_CATEGORIES_B — resolves PRODUCT_CATEGORY_ID.
  • HZ_CUST_ACCOUNTS — resolves CUSTOMER_ID.

These joins form the standard dimensional view used when reporting service-code repair demand across the Service module.