Results for “isc_dr_mttr_01_mv”

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

Overview

ISC_DR_MTTR_01_MV is a materialized view owned by the APPS schema in Oracle E-Business Suite, catalogued under the FND – Application Object Library product. The object belongs to the Depot Repair reporting layer of the Oracle Enterprise Asset Management and Service/Depot Repair family. Its name decomposes into "ISC" (Service Contracts/Depot Repair analytics family), "DR" (Depot Repair), and "MTTR" (Mean Time To Repair), with the "_01_MV" suffix indicating the first materialized view variant in the aggregate series. The view consolidates repair turnaround metrics across time hierarchies, organizations, customers, products, and repair types so that operational reporting does not have to scan transactional repair order tables directly.

The ETRM metadata records 26 physical columns in the 12.1.1 schema and explicitly notes that the object is "not implemented in this database" in the captured environment, a common situation for seeded materialized views that are created only when the Depot Repair reporting concurrent programs are run or when the relevant product license is active. Under the heuristic Data Vault classification supplied in the metadata, the object is treated as standalone — it carries no FK-derived links to other hubs or satellites beyond a single reference to CSD_REPAIR_TYPES_B. In Data Vault terms it is best modeled as a satellite-like aggregate: a pre-computed fact structure keyed by a composite business key rather than a surrogate hub, with the repair type reference acting as the sole conformed dimension link.

Key Information Stored

The unique index I_SNAP$_ISC_DR_MTTR_01_MV defines the effective business key of the materialized view. It is a ten-column composite key, using SYS_OP_MAP_NONNULL to permit null-tolerant uniqueness across REPAIR_ORGANIZATION_ID, REPAIR_TYPE_ID, QTR_ID, CUSTOMER_ID, PRODUCT_CATEGORY_ID, ITEM_ORG_ID, MONTH_ID, WEEK_ID, DAY_ID, and MV_GRP_ID. Because the object is a summary view rather than a transactional entity, there is no separate single-column surrogate primary key; the composite index functions as the grain definition.

The most significant columns fall into three groups:

• Dimensional keys: REPAIR_ORGANIZATION_ID, ITEM_ORG_ID, CUSTOMER_ID, PRODUCT_CATEGORY_ID, and REPAIR_TYPE_ID identify where, for whom, and what was repaired.
• Time hierarchy keys: QTR_ID, MONTH_ID, WEEK_ID, DAY_ID, TIME_ID, and PERIOD_TYPE_ID allow the same aggregate to be sliced by calendar granularity; AGGREGATION_FLAG and MV_GRP_ID distinguish which rows belong to which aggregation level.
• Measures: TIME_TO_REPAIR, the ten banded buckets TIME_TO_REPAIR_B1 through TIME_TO_REPAIR_B10, MV_TIME_TO_REPAIR_COUNT, and RO_COUNT together provide both central-tendency and distributional views of repair duration, with RO_COUNT giving the underlying repair order population per grain.

Common Use Cases and Queries

The view primarily supports Depot Repair dashboards and Service operational reports: MTTR trending by organization, repair type performance versus customer commitments, and product category failure analysis. Refresh is typically performed by the Depot Repair "Repair Turnaround Statistics" concurrent request, which repopulates the aggregate from base repair order and repair line data.

A representative query aggregates MTTR by quarter and repair type for a given repair organization:

• SELECT qtr_id, repair_type_id, SUM(ro_count) ro_count, AVG(time_to_repair) mttr FROM isc_dr_mttr_01_mv WHERE repair_organization_id = :p_org AND aggregation_flag = 'Y' GROUP BY qtr_id, repair_type_id ORDER BY qtr_id;
• A distribution query uses the banded columns to render a histogram: SELECT repair_type_id, SUM(time_to_repair_b1) b1, SUM(time_to_repair_b2) b2, ... SUM(time_to_repair_b10) b10 FROM isc_dr_mttr_01_mv GROUP BY repair_type_id;
• A customer-facing report joins through the repair type to descriptive text: SELECT m.customer_id, t.name, SUM(m.time_to_repair) FROM isc_dr_mttr_01_mv m, csd_repair_types_b t WHERE m.repair_type_id = t.repair_type_id GROUP BY m.customer_id, t.name;

Because this is a materialized view, reports should be aware of refresh timing, and ad hoc queries against it will not reflect repairs posted after the last refresh.

Related Objects

The single documented foreign key relationship is ISC_DR_MTTR_01_MV.REPAIR_TYPE_ID → CSD_REPAIR_TYPES_B, so CSD_REPAIR_TYPES_B is the primary conformed dimension joined for repair type descriptions. Beyond that, the aggregate draws its source rows from the Depot Repair transactional base, principally the repair order and repair line tables such as CSD_REPAIRS and CSD_REPAIR_LINES, and their organization and item validation against MTL_PARAMETERS, MTL_SYSTEM_ITEMS_B, and HR_ALL_ORGANIZATION_UNITS for repair and inventory organizations. Time keys reference the FND calendar dimension structures used by the Service analytics family. Customer identity resolves through the Oracle Receivables customer tables (RA_CUSTOMERS / HZ_CUST_ACCOUNTS) via CUSTOMER_ID, and product category resolves through MTL_CATEGORIES_B. Because the metadata classifies the object as standalone, no additional FK-enforced links exist; the remaining relationships are logical joins maintained by the concurrent program that populates the view rather than by database constraints.