Search Results sc_mv

Overview

APPS.CSD_SC_FREQUENCY_V is a reporting view in Oracle E-Business Suite that exposes calculated frequency metrics derived from Service Contract coverage data. The object is defined over the materialized view CSD_SC_FREQ_SUM_MV and presents, for each service item and service code combination, a ratio that expresses how often a serviceable inventory item has been covered by a service contract exclusively, relative to the total count of repair orders or service events recorded for that item. In ETRM 12.2.2, the view is owned by the APPS schema and is intended for read-only consumption by reporting, analytics, and integration components. The "sc_mv" search term that surfaces this object reflects its direct dependency on the CSD_SC_FREQ_SUM_MV materialized aggregate, which is the underlying summarization layer. Because the view performs no filtering, grouping, or joins beyond wrapping the materialized view, it functions as a stable, simplified projection that shields downstream consumers from the physical column naming and structure of the summary table.

Underlying Base Objects

The ETRM metadata documents exactly one referenced base object for this view: CSD_SC_FREQ_SUM_MV, classified as a table. The view is a straightforward pass-through with a computed column. The SELECT aliases the materialized view as SC_MV and projects its columns directly, adding one derived expression, FREQUENCY, defined as SC_ONLY_RO_COUNT divided by TOTAL_RO_COUNT. No additional joins, predicates, or aggregation occur at the view level; all summarization is performed by the materialized view, which is typically refreshed on a scheduled basis to keep the frequency statistics current. Consequently, the freshness of any data returned by CSD_SC_FREQUENCY_V is governed by the refresh policy of CSD_SC_FREQ_SUM_MV, not by the view itself.

Key Columns

• RO_INVENTORY_ITEM_ID — The inventory item identifier representing the repair order or serviceable item for which coverage frequency is measured.
• SERVICE_ITEM_ID — The identifier of the service item associated with the frequency record.
• SERVICE_CODE_ID — The service code that classifies the type of service being analyzed.
• TOTAL_RO_COUNT — The total count of repair orders or service events recorded for the item and service combination.
• SC_ONLY_RO_COUNT — The subset count of repair orders that were covered exclusively by service contracts.
• FREQUENCY — A calculated ratio equal to SC_ONLY_RO_COUNT divided by TOTAL_RO_COUNT, expressing the proportion of total activity attributable to service contract coverage.

Consumers should note that FREQUENCY is a division of two counts and will raise a division-by-zero condition if TOTAL_RO_COUNT is zero; callers should guard against this in custom SQL.

Common Use Cases and Queries

The view is typically used to analyze service contract penetration and to prioritize items or service codes where exclusive contract coverage is high or low. A representative query retrieves the highest-frequency combinations for a service code:

SELECT ro_inventory_item_id, service_item_id, service_code_id, total_ro_count, sc_only_ro_count, frequency FROM apps.csd_sc_frequency_v WHERE service_code_id = :p_service_code_id AND total_ro_count > 0 ORDER BY frequency DESC;

Another common pattern aggregates frequency across service codes to compare coverage trends:

SELECT service_code_id, AVG(frequency) avg_frequency, SUM(total_ro_count) total_events FROM apps.csd_sc_frequency_v GROUP BY service_code_id;

Because the underlying object is a materialized view, queries against CSD_SC_FREQUENCY_V are efficient for dashboards, extract programs, and integration interfaces that require pre-aggregated frequency metrics rather than live transactional counts.