Search Results mtl_uom_conversions_u1

Overview

INV.MTL_UOM_CONVERSIONS is a transactional configuration table in the Oracle E-Business Suite Inventory (INV) schema. It stores conversion rates between non-base units of measure and their base unit, at the item level. The non-base units of measure themselves are stored in this table, and each row defines how a specific non-base unit converts to the base unit of its associated unit-of-measure class for a given inventory item. It is a foundational object for quantity handling: purchasing, receiving, inventory transactions, order management, and shipping all rely on accurate item-unit conversion factors to translate transactional quantities into the base unit of measure maintained in MTL_SYSTEM_ITEMS.

From a Data Vault modeling perspective, the heuristic classification of this object is standalone, meaning it is not formally mined as a hub, link, or satellite through foreign-key structure. In practice it behaves as an item-level reference or bridge-style configuration table, but the documented relationship metadata does not assign a hub, link, or satellite role. This classification should be treated as a modeling suggestion rather than a definitive architectural fact.

Key Information Stored

The table is keyed by a composite primary key, MTL_UOM_CONVERSIONS_PK, defined on (UNIT_OF_MEASURE, INVENTORY_ITEM_ID). In addition to the primary key, the documented metadata identifies two unique business-key candidates: MTL_UOM_CONVERSIONS_U1 on (INVENTORY_ITEM_ID, UNIT_OF_MEASURE) and MTL_UOM_CONVERSIONS_U2 on (INVENTORY_ITEM_ID, UOM_CODE). Note that the primary key and U1 share the same columns in a different order, while U2 introduces UOM_CODE, the unit of measure short name, as an alternate business identifier.

• INVENTORY_ITEM_ID — the inventory item identifier; ties the conversion to a specific item.
• UNIT_OF_MEASURE — the non-base unit of measure long name.
• UOM_CODE — the unit of measure short name (the U2 index candidate).
• UOM_CLASS — the class of the conversion unit of measure, derived from MTL_UNITS_OF_MEASURE.
• CONVERSION_RATE — the rate from the conversion unit to the base unit of the class.
• DEFAULT_CONVERSION_FLAG — 'Y' when the conversion is associated with a specific item and matches a previously defined standard conversion with the same rate.
• DISABLE_DATE — the date after which the conversion is no longer valid for transactions.
• DIMENSION_UOM, LENGTH, WIDTH, HEIGHT — dimensional attributes for the item-UOM combination.
• Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE for audit and concurrent program context.

Common Use Cases and Queries

Typical usage is resolving the conversion rate for an item and unit of measure, or auditing conversions that have active or disabled status. A common query pattern filters out disabled conversions and joins to MTL_SYSTEM_ITEMS for the base unit:

• Look up the current rate: SELECT CONVERSION_RATE FROM MTL_UOM_CONVERSIONS WHERE INVENTORY_ITEM_ID = :item AND UOM_CODE = :uom AND (DISABLE_DATE IS NULL OR DISABLE_DATE > SYSDATE);
• Audit item-level overrides: filter on DEFAULT_CONVERSION_FLAG = 'Y' to isolate item conversions that mirror standard conversions.
• Reporting: list all conversions by UOM_CLASS to analyze coverage across an item catalog, or review DISABLE_DATE ranges for conversion history.
• Dimensional reporting on LENGTH, WIDTH, HEIGHT, and DIMENSION_UOM for freight and packaging calculations.

Related Objects

The most significant objects interacting with MTL_UOM_CONVERSIONS include:

• MTL_UNITS_OF_MEASURE — source of the unit-of-measure definition and class used to derive UOM_CLASS.
• MTL_SYSTEM_ITEMS — the item master; joins on INVENTORY_ITEM_ID and supplies the base unit of measure.
• MTL_SYSTEM_ITEMS_B / _TL — base and translated item definitions linked by INVENTORY_ITEM_ID.
• MTL_UOM_CLASS_CONVERSIONS — class-level standard conversions referenced by DEFAULT_CONVERSION_FLAG logic.
• INV_ITEM_UOM_PUB / INV_UOM_PUB — public APIs used to create and maintain item-unit conversions.
• MTL_TRANSACTIONS and MTL_MATERIAL_TRANSACTIONS — transactional tables where conversion rates govern quantity translation.