Results for “service_code_id”

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

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.