Search Results mtl_uom_class_conversions_pk

Overview

MTL_UOM_CLASS_CONVERSIONS is an Inventory (INV) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores conversion relationships between base units of measure belonging to two different unit-of-measure classes. Where the standard MTL_UOM_CONVERSIONS table governs intra-class conversions (for example, converting inches to feet within the Length class), MTL_UOM_CLASS_CONVERSIONS handles the cross-class or inter-class conversion definitions required when a single inventory item must be transacted or reported in units drawn from distinct classes, such as converting a Weight base unit to a Volume or Count base unit for a specific item. The table is owned by the INV schema and is documented as standalone from a foreign-key perspective, meaning that no enforced foreign keys bind it to parent transactional tables; relationships to items and unit-of-measure definitions are maintained logically through the item and UOM identifier columns.

Under a heuristic Data Vault classification mined from the FK structure, the table is best modeled as a standalone reference or lookup entity rather than a true Data Vault hub, link, or satellite. It functions as a business-rule configuration table against which the Inventory conversion engine evaluates multi-class unit conversions.

Key Information Stored

The documented physical schema exposes eighteen columns. The most significant are:

• INVENTORY_ITEM_ID – the inventory item to which the cross-class conversion applies; this is the lead column of the primary key and of three unique business-key candidates.
• FROM_UNIT_OF_MEASURE / FROM_UOM_CODE / FROM_UOM_CLASS – the source unit of measure, its short code, and the class to which the source unit belongs.
• TO_UNIT_OF_MEASURE / TO_UOM_CODE / TO_UOM_CLASS – the destination unit of measure, its short code, and its class. The pair of FROM and TO identifiers, together with the item, defines the conversion direction.
• CONVERSION_RATE – the multiplicative factor applied to translate a quantity expressed in the FROM unit into the equivalent quantity in the TO unit.
• DISABLE_DATE – the date on which the conversion definition is effectively deactivated, enabling date-effective, versioned behavior.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN – the standard Oracle EBS who-audit columns capturing creation and last-modification metadata.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE – concurrent-program context columns recording which request and program last touched the row.

The surrogate primary key is MTL_UOM_CLASS_CONVERSIONS_PK, defined on (INVENTORY_ITEM_ID, FROM_UNIT_OF_MEASURE, TO_UNIT_OF_MEASURE). Three unique indexes act as business-key candidates: MTL_UOM_CLASS_CONVERSIONS_U1 (INVENTORY_ITEM_ID, TO_UNIT_OF_MEASURE), MTL_UOM_CLASS_CONVERSIONS_U2 (INVENTORY_ITEM_ID, TO_UOM_CODE), and MTL_UOM_CLASS_CONVERSIONS_U3 (TO_UOM_CLASS, INVENTORY_ITEM_ID). These constraints imply that, for a given item, the destination unit, its code, and its class are each unique, preserving deterministic conversion direction.

Common Use Cases and Queries

Typical applications include validating item-level conversion setup, investigating conversion-rate discrepancies during receiving, shipping, and inventory movement, and building reconciliation reports for units of measure spanning multiple classes. A straightforward lookup for a given item and destination unit follows:

• SELECT inventory_item_id, from_uom_code, to_uom_code, conversion_rate, disable_date FROM inv.mtl_uom_class_conversions WHERE inventory_item_id = :item_id AND to_uom_code = :uom_code;
• To find only active definitions: filter with WHERE disable_date IS NULL OR disable_date > SYSDATE.
• To audit recently modified configuration: SELECT * FROM inv.mtl_uom_class_conversions WHERE last_update_date >= TRUNC(SYSDATE) - 7;
• To profile multi-class items, group by item and count destination classes to surface items whose conversions bridge more than two classes.

Related Objects

Although the metadata classifies this table as standalone, its logical dependencies include:

• MTL_SYSTEM_ITEMS_B – joined on INVENTORY_ITEM_ID to resolve item attributes and primary UOM.
• MTL_UNITS_OF_MEASURE – joined on TO_UNIT_OF_MEASURE, FROM_UNIT_OF_MEASURE, or the corresponding UOM_CODE columns to obtain unit descriptions and class assignments.
• MTL_UOM_CLASSES – joined on TO_UOM_CLASS and FROM_UOM_CLASS to describe the classes involved.
• MTL_UOM_CONVERSIONS – the companion table holding intra-class conversions, commonly queried alongside this one to build a complete conversion picture.
• INV_CONVERT / INV_UM_CONVERT – the PL/SQL conversion APIs that resolve rates at runtime, consuming these definitions.

Together these objects support correct, item-specific cross-class unit conversion throughout Inventory and Order Management processing.