Search Results service_code_id




Overview
Explore in AI Chat

Overview

CSD_SC_FREQUENCY_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite. It is delivered as part of the CSD (Depot Repair) product family and is documented as VALID in both ETRM 12.1.1 and 12.2.2. The view exists to supply the FREQUENCY value displayed against a Service Code on the Depot Repair Evaluation page (often referenced internally as the Service Code Evaluation or scoring page).

Its single purpose is to expose a calculated ratio rather than to store data: it returns the proportion of repair orders that were associated exclusively with a given service code relative to the total number of repair orders in which the associated service item appeared. Because this ratio is central to service-code scoring and ranking, the view functions as a lightweight reporting and integration interface. Report developers, form personalization authors, and interface builders can query CSD_SC_FREQUENCY_V instead of recomputing the ratio themselves, guaranteeing that the same figure shown in the Evaluation page is reproduced elsewhere. The user search term "service_code_id" maps directly to the SERVICE_CODE_ID column exposed by this view, which is the key used to join the frequency result back to service code definition tables.

Underlying Base Objects

The view is defined over exactly one documented base object: the materialized view CSD_SC_FREQ_SUM_MV. The defining query aliases that object as SC_MV and projects six columns, with the FREQUENCY column computed as (SC_ONLY_RO_COUNT / SC_MV.TOTAL_RO_COUNT).

Because CSD_SC_FREQ_SUM_MV is a materialized view, the aggregated counts it holds are refreshed on a defined schedule controlled by the Depot Repair concurrent program setup, not recalculated on every execution. This has two practical consequences. First, the FREQUENCY values returned by CSD_SC_FREQUENCY_V reflect the state of the materialized view at its last refresh, not necessarily live transactional data. Second, queries against this view are inexpensive relative to querying the underlying repair order and service code transaction tables directly, since the heavy aggregation work has already been performed.

Key Columns

  • RO_INVENTORY_ITEM_ID — Inventory item identifier of the repair order item, used to scope the frequency statistic to a specific repairable item.
  • TOTAL_RO_COUNT — Total number of repair orders counted for the item in the materialized view; this is the denominator of the frequency calculation.
  • SERVICE_CODE_ID — Surrogate key of the service code. This is the column most commonly used as a search or join predicate, and it links the frequency figure back to the service code definition.
  • SERVICE_ITEM_ID — Identifier of the service item associated with the service code, providing the granularity at which counts are accumulated.
  • SC_ONLY_RO_COUNT — Number of repair orders whose service code set consisted solely of this service code; this is the numerator of the frequency calculation.
  • FREQUENCY — Derived ratio SC_ONLY_RO_COUNT divided by TOTAL_RO_COUNT, expressing how often the service code appears alone. Higher values indicate a more exclusive association with the item.

Common Use Cases and Queries

Typical uses include validating why a service code scores as it does on the Evaluation page, building custom reports that rank service codes by exclusivity for a given inventory item, and diagnosing discrepancies when the Evaluation page and a custom report disagree.

To retrieve all service codes for a specific repair order item:

  • SELECT service_code_id, service_item_id, sc_only_ro_count, total_ro_count, frequency FROM apps.csd_sc_frequency_v WHERE ro_inventory_item_id = :item_id ORDER BY frequency DESC;

To look up the frequency record for a single known service code:

  • SELECT ro_inventory_item_id, frequency, total_ro_count FROM apps.csd_sc_frequency_v WHERE service_code_id = :service_code_id;

To surface only high-confidence, exclusive service codes while avoiding division-by-zero risk in downstream calculations, filter on the pre-computed column:

  • SELECT service_code_id, frequency FROM apps.csd_sc_frequency_v WHERE total_ro_count > 0 AND frequency >= 0.5;

Because FREQUENCY is already computed in the view, no division logic is required in these queries. Where the denominator TOTAL_RO_COUNT is zero, the ratio must be treated as undefined; consumers should guard against this case as shown above.