Search Results mtl_uom_conversions




Overview
Explore in AI Chat

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:

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: