Search Results csd_sc_freq_sum_mv




Overview
Explore in AI Chat

Overview

CSD_SC_FREQ_SUM_MV is an Oracle E-Business Suite materialized view owned by the APPS schema within the CSD – Depot Repair product family. Despite being registered as a TABLE object in the ETRM data dictionary (a common artifact of how Oracle stores materialized views as physical segments), the naming convention and the presence of SYS_OP_MAP_NONNULL-based unique indexes confirm its nature as a precomputed summary structure. It aggregates depot repair service activity by inventory item and service code, providing a denormalized frequency summary that avoids costly runtime aggregation against the base repair order tables. In EBS 12.1.1 and 12.2.2, this object supports depot repair analytics, service contract reporting, and return-order trend analysis.

The metadata classifies this object heuristically as standalone within the Data Vault model. Because no downstream foreign keys reference it and its relationships are one-directional, it is best modeled as a satellite of the service code and inventory item hubs, carrying pre-aggregated measures rather than introducing new business keys. Organizations adopting Data Vault or dimensional modeling should treat it as a derived fact-like structure keyed to the underlying operational entities.

Key Information Stored

The documented physical schema contains six columns. The most significant are:

  • RO_INVENTORY_ITEM_ID — the repair-order inventory item identifier; a primary grouping dimension for the summary.
  • SERVICE_CODE_ID — the foreign key to CSD_SERVICE_CODES_B, tying each summary row to a defined depot repair service code.
  • SERVICE_ITEM_ID — the service item identifier, distinguishing the specific serviceable component.
  • TOTAL_RO_COUNT — total repair orders associated with the item and service code combination.
  • SC_ONLY_RO_COUNT — count of repair orders attributable solely to the service code, enabling differentiation between item-specific and code-driven activity.
  • COUNT(*) — the aggregate row counter assisting the materialized view refresh and query optimizer.

No traditional single-column surrogate primary key is documented. Instead, the unique index I_SNAP$_CSD_SC_FREQ_SUM_MV spans RO_INVENTORY_ITEM_ID, TOTAL_RO_COUNT, SERVICE_CODE_ID, and SERVICE_ITEM_ID using SYS_OP_MAP_NONNULL, which enforces uniqueness while permitting NULL combinations. This composite index is the de facto business-key candidate and supports the materialized view's fast-refresh capability.

Common Use Cases and Queries

This object is typically queried for depot repair throughput reporting. A representative pattern retrieves high-volume service codes for a given item:

  • SELECT service_code_id, SUM(total_ro_count) FROM csd_sc_freq_sum_mv GROUP BY service_code_id;
  • Joining to CSD_SERVICE_CODES_B on SERVICE_CODE_ID to resolve human-readable service code names for dashboards.
  • Ranking inventory items by repair-order frequency to identify high-failure components.
  • Comparing TOTAL_RO_COUNT against SC_ONLY_RO_COUNT to assess whether repair volume is item-driven or service-code driven.

Because it is a materialized view, results reflect the most recent refresh cycle. Reports requiring real-time accuracy should validate refresh status via DBA_MVIEWS or DBA_MVIEW_REFRESH_TIMES before consuming data.

Related Objects

The most significant related objects, grounded in the documented foreign key and product context, include:

  • CSD_SERVICE_CODES_B — referenced via CSD_SC_FREQ_SUM_MV.SERVICE_CODE_ID → CSD_SERVICE_CODES_B, providing service code definitions.
  • CSD_SERVICE_CODES_TL — the translation table supplying language-specific service code descriptions.
  • CSD_REPAIRS and CSD_REPAIR_ORDERS — transactional sources from which the frequency counts are derived.
  • MTL_SYSTEM_ITEMS_B — resolves RO_INVENTORY_ITEM_ID and SERVICE_ITEM_ID to item attributes.
  • CSD_SERVICE_ITEMS — supports reconciliation of the SERVICE_ITEM_ID dimension.

These associations allow the summary to be expanded into full operational reporting without direct access to high-volume repair transaction tables.