Results for “mv_expense_cost_g_cnt”

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

Overview

ISC_DR_COSTS_02_MV is a materialized view registered under the FND — Application Object Library product schema within Oracle E-Business Suite. Its name and column composition indicate that it belongs to the Oracle Depot Repair (ISC) cost aggregation layer, providing pre-computed repair cost summaries across organizations, product categories, customers, and repair types, segmented by time period and aggregation flag.

The view is owned by APPS and, at the documented 12.1.1 physical schema, contains 21 columns. According to the ETRM metadata, the object is not implemented in the reference database at which the extract was taken, meaning the definition exists in the repository but no runtime instance is present. Its FK structure is deliberately minimal — a single reference to CSD_REPAIR_TYPES_B through REPAIR_TYPE_ID — and there are no incoming foreign keys. The heuristic Data Vault classification mined from the FK topology is therefore standalone, closest in modeling terms to a satellite-like analytical snapshot rather than a hub or link. This classification should be treated as a modeling suggestion derived from the referential shape, not as an authoritative Data Vault designation.

The materialized view's design supports Oracle's SQL summary-management rewrite mechanism: the presence of count columns (MV_*_CNT) alongside generalized and slightly-generalized aggregates (G and SG suffixes) is characteristic of a materialized view intended to satisfy queries at multiple aggregation levels.

Key Information Stored

The most significant columns fall into four functional groups.

• Business dimension keys: REPAIR_ORGANIZATION_ID, PRODUCT_CATEGORY_ID, CUSTOMER_ID, REPAIR_TYPE_ID, GRP_ID, TIME_ID, and PERIOD_TYPE_ID form the composite grain of the summary. REPAIR_TYPE_ID is the only column carrying a declared foreign key, resolving to CSD_REPAIR_TYPES_B.
• Aggregation control: AGGREGATION_FLAG indicates the level of pre-computed rollup represented by a given row, allowing a single materialized view to serve both detailed and summarized queries.
• Cost measures: MATERIAL_COST_G, MATERIAL_COST_SG, LABOR_COST_G, LABOR_COST_SG, EXPENSE_COST_G, and EXPENSE_COST_SG store material, labor, and expense costs at generalized (G) and slightly-generalized (SG) precision.
• Count measures: MV_MATERIAL_COST_G_CNT, MV_MATERIAL_COST_SG_CNT, MV_LABOR_COST_G_CNT, MV_LABOR_COST_SG_CNT, MV_EXPENSE_COST_G_CNT, MV_EXPENSE_COST_SG_CNT, and MV_CNT hold row counts backing each aggregate, which are required for correct recomputation when only partial aggregation is available.

No single surrogate primary key column is documented. The closest documented business-key candidate is the unique index I_SNAP$_ISC_DR_COSTS_02_MV, defined over SYS_OP_MAP_NONNULL wrappers on TIME_ID, PERIOD_TYPE_ID, PRODUCT_CATEGORY_ID, REPAIR_ORGANIZATION_ID, CUSTOMER_ID, REPAIR_TYPE_ID, and GRP_ID. This index enforces uniqueness across the seven-dimensional grain while tolerating nulls in any of those keys.

Common Use Cases and Queries

The primary use case is cost-of-repair analysis by depot organization, customer, product category, and repair type over time. A typical query filters on PERIOD_TYPE_ID and TIME_ID and rolls up generalized cost columns:

• SELECT repair_organization_id, product_category_id, SUM(MATERIAL_COST_G + LABOR_COST_G + EXPENSE_COST_G) total_cost FROM isc_dr_costs_02_mv WHERE time_id BETWEEN :start AND :end GROUP BY repair_organization_id, product_category_id;
• Trend reporting across PERIOD_TYPE_ID using MV_CNT to weight averages and detect low-volume periods.
• Customer profitability analysis joining CUSTOMER_ID to the customer master.
• Repair-type benchmarking joining REPAIR_TYPE_ID to CSD_REPAIR_TYPES_B for descriptive attributes.

Because the view is a materialized view, queries should be checked against the current refresh mode; stale data is possible between refreshes, and the MV_*_CNT columns must be used whenever partial aggregation is queried to avoid double counting.

Related Objects

Documented relationship data identifies a single foreign key. The most significant related objects are:

• CSD_REPAIR_TYPES_B — referenced by REPAIR_TYPE_ID; the primary source of repair-type descriptions.
• CSD_REPAIR_TYPES_TL — translated repair-type names for reporting joins.
• CS_INCIDENTS_ALL_B / depot repair work-order tables — the transactional source from which cost measures are aggregated.
• MTL_SYSTEM_ITEMS_B and item category tables — resolve PRODUCT_CATEGORY_ID.
• HZ_CUST_ACCOUNTS — resolves CUSTOMER_ID to customer name and account number.
• CS_ORGANIZATIONS / HR_OPERATING_UNITS — resolves REPAIR_ORGANIZATION_ID.
• ISC_DR_COSTS_MV family variants — sibling materialized views covering alternate aggregation grains.

Absence of incoming foreign keys confirms that no other object depends on this view; it is consumed only by ad hoc reporting, BI Publisher datasets, and custom summary queries.