Results for “csd_most_comm_mtl_used_v”

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

Overview

CSD_MOST_COMM_MTL_USED_V is a reporting view owned by the APPS schema within the Oracle E-Business Suite Depot Repair (CSD) module. It is documented as a VALID database view and serves as the data source for the "Most Common Material Used" bin, an analytical component that surfaces the materials most frequently consumed during repair order execution. The view consolidates aggregate repair-order activity into a single result set that ranks materials by usage frequency and quantity, enabling planners and depot managers to identify high-turnover parts.

The view is read-only by design; it exposes derived metrics rather than transactional rows, and it is intended for query and reporting rather than for direct DML. In EBS 12.1.1 and 12.2.2, the definition is unchanged, and the object remains a lightweight join over two materialized views, making it efficient for embedded dashboard queries.

Underlying Base Objects

The ETRM metadata identifies two referenced base objects:

• CSD_RO_PER_ITEM_MV (TABLE) — supplies the total repair-order count per inventory item (TOTAL_RO_COUNT).
• CSD_WIP_MTL_USED_MV (TABLE) — supplies the per-item material consumption counts and quantities (PART_ONLY_RO_COUNT, TOTAL_PART_QUANTITY, and related attributes).

The view performs an inner join on the RO_INVENTORY_ITEM_ID column, correlating each repaired inventory item with its consumption history. Because both sources are materialized views, the data reflects the most recent refresh cycle rather than live transactional state; this is an important consideration when interpreting currency of results.

Key Columns

• RO_INVENTORY_ITEM_ID — the inventory item identifier for the repair order context; the join key between the two materialized views.
• PART_ONLY_RO_COUNT — count of repair orders in which the part was consumed.
• TOTAL_RO_COUNT — total count of repair orders for the item, used as the denominator for frequency.
• FREQUENCY — the ratio PART_ONLY_RO_COUNT / TOTAL_RO_COUNT, expressing how often the part appears relative to total repair activity.
• PART_INVENTORY_ITEM_ID — the inventory item identifier of the part itself.
• PART_PRIMARY_UOM_CODE — the primary unit of measure for the part.
• TOTAL_PART_QUANTITY and PART_ONLY_RO_QUANTITY — aggregate and repair-order-specific consumed quantities.
• AVG_CONSUMPTION — the ratio TOTAL_PART_QUANTITY / PART_ONLY_RO_QUANTITY, indicating average quantity consumed per repair order.

Together, these columns support ranking of materials by frequency and by average consumption, the two primary analytical dimensions of the bin.

Common Use Cases and Queries

Typical uses include depot-repair demand planning, spare-parts stocking decisions, and identifying parts that drive the majority of repair volume. A representative query returns the highest-frequency materials:

SELECT RANK() OVER (ORDER BY FREQUENCY DESC) RNK,
       PART_INVENTORY_ITEM_ID,
       FREQUENCY,
       AVG_CONSUMPTION,
       TOTAL_PART_QUANTITY
FROM   APPS.CSD_MOST_COMM_MTL_USED_V
WHERE   FREQUENCY IS NOT NULL
ORDER BY FREQUENCY DESC;

A second scenario filters by a specific item to inspect its consumption profile:

SELECT RO_INVENTORY_ITEM_ID, PART_PRIMARY_UOM_CODE,
       TOTAL_RO_COUNT, TOTAL_PART_QUANTITY, AVG_CONSUMPTION
FROM   APPS.CSD_MOST_COMM_MTL_USED_V
WHERE   RO_INVENTORY_ITEM_ID = :p_item_id;

Because both numerators and denominators originate from materialized views, queries should be scheduled after the relevant refreshes to avoid stale ratios or division anomalies. The view is best consumed as an aggregate input to dashboards, reports, and inventory-planning processes rather than as a transaction-level source.