Search Results msc_uom_class_conversions




Overview
Explore in AI Chat

Overview

MSC_UOM_CLASS_CONVERSIONS is a table in the MSC schema belonging to the Advanced Supply Chain Planning (ASCP) product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores unit of measure conversion definitions that relate the base units of two different UOM classes, as opposed to the more common intra-class conversions that Oracle Inventory maintains for a single class. The table exists primarily to support planning engine calculations where the source and destination units belong to separate classes and therefore require a class-to-class bridge ratio.

The table is documented with 36 columns and carries two unique indexes: MSC_UOM_CLASS_CONVERSIONS_U1 on (INVENTORY_ITEM_ID, FROM_UNIT_OF_MEASURE, TO_UNIT_OF_MEASURE), and MSC_UOM_CLASS_CONVERSIONS_U2 on (INVENTORY_ITEM_ID, TO_UOM_CODE). Based on heuristic Data Vault classification derived from its foreign key structure, this object is best modeled as a link table. It resolves the many-to-many relationship between inventory items and units of measure, associating a from-side UOM with a to-side UOM through a stored conversion rate.

Key Information Stored

The declared primary key constraint is MSC_UOM_CLASS_CONVERSIONS_PK, comprising INVENTORY_ITEM_ID, FROM_UNIT_OF_MEASURE, and TO_UNIT_OF_MEASURE. The unique index U1 mirrors these same three columns, making them the strongest documented business-key candidate. A second unique index, U2, on INVENTORY_ITEM_ID and TO_UOM_CODE provides an alternate semantic key that treats the destination UOM as the driving attribute.

  • INVENTORY_ITEM_ID — the item context to which the conversion applies; conversions in this table are item-specific rather than global.
  • FROM_UNIT_OF_MEASURE and TO_UNIT_OF_MEASURE — the two UOMs being related; both are foreign keys into MSC_UNITS_OF_MEASURE.
  • FROM_UOM_CODE and TO_UOM_CODE — the abbreviated codes for the respective UOMs, used in reporting and in the U2 unique key.
  • FROM_UOM_CLASS and TO_UOM_CLASS — the classes to which the from-side and to-side UOMs belong; these define whether the record is truly cross-class.
  • CONVERSION_RATE — the multiplicative ratio applied when translating a quantity expressed in the from-unit into the to-unit.
  • DISABLE_DATE — the date on or after which the conversion is no longer considered active by the planning processes.
  • SR_INSTANCE_ID — identifies the source application instance from which the record was collected or refreshed.
  • REFRESH_NUMBER — the collection or refresh cycle identifier associated with the record.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard audit columns present on most MSC tables.
  • REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context identifying which process wrote the row.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the DFF (descriptive flexfield) columns reserved for customer-specific extensions.

Common Use Cases and Queries

The typical consumer of this table is the ASCP planning engine, which reads conversion rates to normalize demand, supply, and on-hand quantities that arrive in mismatched UOM classes. Reporting and diagnostic queries focus on verifying that a conversion exists, that it is not disabled, and that the rate produces a sensible value.

  • Listing all active cross-class conversions for a given item:
SELECT inventory_item_id, from_unit_of_measure, to_unit_of_measure,
       from_uom_class, to_uom_class, conversion_rate
FROM   msc_uom_class_conversions
WHERE  inventory_item_id = :item_id
AND   (disable_date IS NULL OR disable_date > SYSDATE);
  • Validating that both sides resolve to known units in MSC_UNITS_OF_MEASURE, to catch orphaned or stale rows after collection.
  • Reconciling ASCP-collected rates against the source EBS inventory conversion definitions when planning quantities appear incorrect.
  • Filtering by SR_INSTANCE_ID and REFRESH_NUMBER to isolate the most recent collection run during troubleshooting.
  • Auditing the PROGRAM_ID and REQUEST_ID columns to confirm which concurrent program last populated a disputed record.

Related Objects

The documented foreign keys tie this table to the UOM master data maintained in ASCP. The following objects are the most significant in the surrounding model:

  • MSC_UNITS_OF_MEASURE — referenced twice, once by FROM_UNIT_OF_MEASURE and once by TO_UNIT_OF_MEASURE; it is the primary parent of this link table and supplies the descriptive attributes of each unit.
  • MSC_UOM_CLASS_CONVERSIONS_PK — the primary key constraint enforcing uniqueness on INVENTORY_ITEM_ID, FROM_UNIT_OF_MEASURE, and TO_UNIT_OF_MEASURE.
  • MSC_UOM_CLASS_CONVERSIONS_U1 and MSC_UOM_CLASS_CONVERSIONS_U2 — unique indexes that enforce the two documented business-key candidates.
  • MSC_ITEM_ATTRIBUTES / MSC_SYSTEM_ITEMS — item master tables whose INVENTORY_ITEM_ID values supply the item context for every conversion row.
  • MTL_UNITS_OF_MEASURE and MTL_UOM_CONVERSIONS — the source EBS inventory tables from which ASCP conversion data is ultimately collected.
  • MSC_UOM_CONVERSIONS — the intra-class companion table; cross-class relationships are recorded here instead.
  • MSC_UOM_CLASS_ASSOCIATIONS — defines which units belong to which UOM class, supporting the class columns in this table.