Search Results msc_uom_conversions_pk

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:

• INVENTORY_ITEM_ID — the inventory item to which the conversion belongs; part of the primary key.
• UNIT_OF_MEASURE — the non-base unit of measure for which a conversion is defined; part of the primary key and a foreign key to MSC_UNITS_OF_MEASURE.
• UOM_CODE — the coded representation of the unit of measure, used in the second unique index.
• UOM_CLASS — the classification group (e.g., quantity, weight, volume) used to validate conversions.
• CONVERSION_RATE — the numeric factor converting the non-base UOM to the base UOM.
• DISABLE_DATE — the date the conversion ceases to be effective.
• DEFAULT_CONVERSION_FLAGS — flags indicating default conversion behaviour.
• SR_INSTANCE_ID — source instance identifier for multi-source/collection support.
• REFRESH_NUMBER — the collection refresh cycle that produced the row.
• LAST_UPDATE_DATE, CREATION_DATE, LAST_UPDATED_BY, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS WHO columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard EBS DFF (descriptive flexfield) attribute columns.

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:

• MSC_UNITS_OF_MEASURE — referenced via MSC_UOM_CONVERSIONS.UNIT_OF_MEASURE; the parent UOM lookup.
• MSC_SYSTEM_ITEMS / MSC_ITEM_ORG_ASSIGNMENTS — provide the item master context for INVENTORY_ITEM_ID.
• MTL_UOM_CONVERSIONS and MTL_UNITS_OF_MEASURE — the Inventory source tables from which these rows are collected.
• MTL_SYSTEM_ITEMS_B — item definition source for the collected item set.
• MSC_PLANNED_ORDERS and MSC_SUPPLIES — planning demand/supply tables that consume the converted quantities.
• MSC_ITEM_CATEGORIES — supplementary item context used in reports.