Results for “isc_dr_costs_01_mv”

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

Overview

ISC_DR_COSTS_01_MV is a materialized view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It resides in the FND – Application Object Library product grouping, though its name and column structure indicate it serves the Service Contracts / Depot Repair cost analytics domain (ISC prefix, DR = Depot Repair). The object stores pre-aggregated repair cost data across multiple time hierarchies and organizational dimensions, making it a reporting-oriented summary structure rather than a transactional table.

Physically the object exposes 26 columns and is documented as VALID. Its unique index, I_SNAP$_ISC_DR_COSTS_01_MV, uses SYS_OP_MAP_NONNULL on ten dimension columns, which is the standard signature of an Oracle materialized view snapshot index generated for fast refresh with null handling. A modeling heuristic classifies this object as standalone in Data Vault terms — it functions as a self-contained aggregate rather than a canonical hub, link, or satellite. Practitioners modeling this data for a warehouse would typically suggest treating it as a pre-computed satellite-style fact aggregate rather than a normalized entity.

Key Information Stored

The grain of the materialized view is defined by its dimension columns, which combine to form the business-key candidate captured in the unique index:

• REPAIR_ORGANIZATION_ID – the operating unit / repair organization owning the cost record.
• REPAIR_TYPE_ID – foreign key to CSD_REPAIR_TYPES_B, identifying the repair classification.
• CUSTOMER_ID – the customer associated with the repair activity.
• PRODUCT_CATEGORY_ID and ITEM_ORG_ID – product classification and item master organization context.
• GRP_ID – a grouping identifier used for roll-up reporting.
• TIME_ID, PERIOD_TYPE_ID, QTR_ID, PERIOD_ID, WEEK_ID, DAY_ID – the time hierarchy supporting quarter, period, week, and day level analysis.
• AGGREGATION_FLAG – indicates the aggregation level or roll-up state of the row.

The measure columns form matched pairs of general-ledger and secondary-ledger amounts, plus non-null count companions used for refresh and averaging:

• MATERIAL_COST_G, LABOR_COST_G, EXPENSE_COST_G – general ledger material, labor, and expense costs.
• MATERIAL_COST_SG, LABOR_COST_SG, EXPENSE_COST_SG – the corresponding secondary ledger (reporting currency / GAAP) amounts.
• MV_MATERIAL_COST_G_CNT, MV_LABOR_COST_G_CNT, MV_EXPENSE_COST_G_CNT and their SG counterparts, plus MV_CNT – row counts maintained by the materialized view for incremental refresh and weighted aggregation.

No surrogate primary key column is documented; identity is enforced solely through the composite unique index.

Common Use Cases and Queries

Typical consumption is in cost dashboards and depot repair margin analysis. A standard pattern aggregates the ledger measures by repair organization and quarter:

• SELECT REPAIR_ORGANIZATION_ID, QTR_ID, SUM(MATERIAL_COST_G + LABOR_COST_G + EXPENSE_COST_G) TOTAL_COST FROM APPS.ISC_DR_COSTS_01_MV WHERE AGGREGATION_FLAG = :flag GROUP BY REPAIR_ORGANIZATION_ID, QTR_ID;
• Comparative general-ledger versus secondary-ledger reporting: sum _G and _SG columns side by side to surface currency and ledger differences.
• Trend analysis by PERIOD_ID, WEEK_ID, or DAY_ID joined to a time dimension for period-over-period variance.
• Customer and product category profitability by grouping on CUSTOMER_ID and PRODUCT_CATEGORY_ID.
• Refresh diagnostics: inspect the MV_*_CNT columns to confirm row counts and detect stale or missing aggregate buckets after a refresh cycle.

Related Objects

The documented foreign key relationship is from REPAIR_TYPE_ID to CSD_REPAIR_TYPES_B. This is the principal join for resolving repair type names and descriptions in reports. Surrounding objects commonly referenced with this materialized view include:

• CSD_REPAIR_TYPES_B / CSD_REPAIR_TYPES_TL – repair type base and translation tables.
• CS_* service contracts tables for contract context when reconciling repair costs to contract entitlements.
• ISC_DR_COSTS_* companion materialized views (other aggregation series) that share the same dimension keys.
• PER_TIME / GL period dimension tables used to resolve QTR_ID, PERIOD_ID, WEEK_ID, and DAY_ID.
• HZ_CUST_ACCOUNTS for customer naming behind CUSTOMER_ID.

Because the object is a materialized view, it carries no user-maintained DML API; maintenance is performed through scheduled refresh, and direct queries should treat all columns as read-only.