Search Results msc_uom_conversions




Overview
Explore in AI Chat

Overview

MSC_UOM_CONVERSIONS is a table in the MSC schema (Advanced Supply Chain Planning) in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the different non-base unit of measure conversions known to the planning engine. In effect, it is the planning-side cache of item-level UOM conversion factors — the rates that allow planned quantities expressed in a transactional or purchased unit of measure to be normalized into the base unit used by the plan. It is populated and refreshed by the MSC collection programs that pull item and UOM setup from Inventory (INV) into the MSC staging tables.

From a Data Vault modeling perspective, the mined foreign-key structure classifies this object as satellite-leaning. The composite key of inventory item plus unit of measure behaves as a parent hub reference, while the conversion rate, effective/disable dates, and descriptive attributes hang off it as satellite-style descriptive data. This is a heuristic suggestion only; the physical table is a conventional EBS denormalized staging table.

Key Information Stored

The table carries 34 documented columns. The most significant are:

The surrogate/primary key is MSC_UOM_CONVERSIONS_PK, defined on (INVENTORY_ITEM_ID, UNIT_OF_MEASURE). Two unique indexes act as business-key candidates: MSC_UOM_CONVERSIONS_U1 on (INVENTORY_ITEM_ID, UNIT_OF_MEASURE) and MSC_UOM_CONVERSIONS_U2 on (INVENTORY_ITEM_ID, UOM_CODE).

Common Use Cases and Queries

Typical uses include validating that planned item quantities convert correctly, diagnosing planning quantity discrepancies attributable to missing or wrong conversion rates, and reporting which items have non-base UOM definitions. Common patterns:

  • List all conversions for an item: SELECT unit_of_measure, conversion_rate FROM msc_uom_conversions WHERE inventory_item_id = :p_item_id;
  • Find rows missing a usable rate: SELECT * FROM msc_uom_conversions WHERE conversion_rate IS NULL OR conversion_rate = 0;
  • Reconcile against the source Inventory definition: SELECT m.inventory_item_id, m.unit_of_measure, m.conversion_rate, s.conversion_rate AS inv_rate FROM msc_uom_conversions m, mtl_uom_conversions s WHERE m.inventory_item_id = s.inventory_item_id AND m.unit_of_measure = s.uom_code;
  • Identify currently effective conversions: WHERE disable_date IS NULL OR disable_date > SYSDATE.
  • Audit which collection run refreshed a row: filter on REFRESH_NUMBER or REQUEST_ID.

Related Objects

The most significant related objects are: