Search Results mtl_uom_conversions_pk

Overview

MTL_UOM_CONVERSIONS is the Inventory (INV) schema table that stores unit of measure conversion definitions in Oracle E-Business Suite 12.1.1 and 12.2.2. It supports both default, class-level conversions and item-specific overrides, allowing the application to translate quantities expressed in one unit of measure into another for the same item. The table is a core component of Oracle Inventory's UOM infrastructure and is consulted during transactions such as receiving, shipping, miscellaneous issue and receipt, cycle counting, and on-hand reporting, where a user-entered quantity must be normalized to the item's primary unit of measure.

The table is documented as VALID in the INV product and is physically implemented with twenty columns. The documented primary key, MTL_UOM_CONVERSIONS_PK, is defined on (UNIT_OF_MEASURE, INVENTORY_ITEM_ID), and two unique indexes act as business-key candidates: MTL_UOM_CONVERSIONS_U1 (INVENTORY_ITEM_ID, UNIT_OF_MEASURE) and MTL_UOM_CONVERSIONS_U2 (INVENTORY_ITEM_ID, UOM_CODE). The metadata carries no foreign-key relationship data and classifies the object, as a heuristic Data Vault modeling suggestion, as standalone; it therefore behaves as an entity in its own right rather than as a dependent satellite of a parent hub.

Key Information Stored

Each row defines a conversion applicable either to a specific inventory item or, when INVENTORY_ITEM_ID is null or a default indicator is set, as a class-level default. The most significant columns are:

• UNIT_OF_MEASURE — the target (or "to") unit of measure of the conversion.
• UOM_CODE — the source (or "from") unit of measure.
• UOM_CLASS — the UOM class to which the conversion belongs, used to scope default conversions.
• INVENTORY_ITEM_ID — the item for which the conversion is defined; null or a default row applies more broadly.
• CONVERSION_RATE — the numeric multiplier applied to convert from UOM_CODE into UNIT_OF_MEASURE.
• DEFAULT_CONVERSION_FLAG — indicates whether the row is a default conversion rather than an item-specific one.
• DISABLE_DATE — the date on which the conversion ceases to be effective.
• LENGTH, WIDTH, HEIGHT, DIMENSION_UOM — dimensional attributes used for volumetric and freight calculations.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context that last modified the row.

The composite primary key (UNIT_OF_MEASURE, INVENTORY_ITEM_ID) is the physical row identifier, while MTL_UOM_CONVERSIONS_U1 and U2 establish alternate uniqueness on the item/UOM pairings used by the application.

Common Use Cases and Queries

Typical reporting and validation scenarios include retrieving the conversion rate for an item between two UOMs, auditing item-specific overrides versus class defaults, and identifying conversions that have been disabled. A representative query for an item-specific conversion is:

SELECT conversion_rate FROM mtl_uom_conversions WHERE inventory_item_id = :item_id AND uom_code = :from_uom AND unit_of_measure = :to_uom AND (disable_date IS NULL OR disable_date > SYSDATE);

Default conversions are located by filtering on UOM_CLASS and DEFAULT_CONVERSION_FLAG, or by selecting rows where INVENTORY_ITEM_ID is null. Analysts also join the table to item master tables to validate that every stocked item has a conversion path to its primary UOM, and use CONVERSION_RATE in conjunction with LENGTH, WIDTH, HEIGHT, and DIMENSION_UOM to compute volume-based conversions.

Related Objects

Because the metadata records no declared foreign keys, relationships are functional rather than enforced. The most significant related objects include:

• MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID to obtain item attributes and the primary UOM.
• MTL_UNITS_OF_MEASURE — joined on UNIT_OF_MEASURE / UOM_CODE to resolve UOM descriptions.
• MTL_UOM_CLASSES — joined on UOM_CLASS to resolve class-level defaults.
• MTL_TRANSACTIONS_INTERFACE / MTL_MATERIAL_TRANSACTIONS — transaction entries validated against the conversion rates defined here.
• MTL_ONHAND_QUANTITIES / MTL_ONHAND_QUANTITIES_DETAIL — on-hand balances converted using the rates in this table.
• INV_UOM_CONVERSION APIs and the Oracle Inventory UOM conversion public API — used to insert, update, or disable conversions programmatically.