Search Results mtl_uom_conversions_view




Overview
Explore in AI Chat

Overview

MTL_UOM_CONVERSIONS_VIEW is an APPS-owned database view in the Oracle E-Business Suite Inventory (INV) module. It is defined with VALID status and is available in both the 12.1.1 and 12.2.2 releases. Its documented purpose is to present unit of measure (UOM) conversion rates between an item's primary unit of measure and another unit of measure. Rather than exposing the raw conversion rows stored in the underlying conversion tables, the view resolves and computes the effective conversion factor between the primary UOM and each associated UOM based on item-specific, class-level, and standard conversion data.

Because the view operates at the intersection of items, organizations, UOM classes, and conversion definitions, it plays a central role in reporting and integration scenarios that require a normalized, query-ready representation of item UOM relationships. Report developers, interface builders, and conversion programs query it to avoid re-implementing the complex resolution logic embedded in the view text.

Underlying Base Objects

The view is defined over four documented base objects, resolved through synonyms in the APPS schema:

  • MTL_SYSTEM_ITEMS_B (synonym) — supplies the item identity, owning organization, primary unit of measure, and primary UOM code.
  • MTL_UNITS_OF_MEASURE_VL (view) — referenced twice to resolve the UOM code of the primary unit of measure and the UOM class of the target unit of measure.
  • MTL_UOM_CONVERSIONS (synonym) — the central conversion table, joined three times to obtain item-specific conversion rates and the standard (item-independent) conversion rate.
  • MTL_UOM_CLASS_CONVERSIONS (synonym) — provides class-to-class conversion rates where the primary and target UOMs belong to different UOM classes.

The joins are largely outer joins on item and UOM code, which allows the view to return conversion information even when item-specific overrides are absent, falling back to standard and class-level rates.

Key Columns

The view exposes the following significant columns:

The computed conversion factor is produced by the view's DECODE expression, which multiplies an intra-class factor (1 when both UOMs share a class, otherwise the class conversion rate) by the secondary conversion rate, then divides by the effective primary conversion rate after considering disable dates and item-level overrides.

Common Use Cases and Queries

Typical scenarios include reporting all UOMs available for an item with their resolved conversion factors, validating that an interface supplies conversions consistent with the item definition, and searching by UOM class to group items. A representative query is:

SELECT inventory_item_id, organization_id, primary_uom_code, uom_class, uom_code
FROM apps.mtl_uom_conversions_view
WHERE inventory_item_id = :item_id
AND organization_id = :org_id;

To filter by UOM class, as suggested by the search term "uom_class":

SELECT inventory_item_id, primary_uom_code, uom_class, unit_of_measure
FROM apps.mtl_uom_conversions_view
WHERE uom_class = :uom_class;

Because the view performs significant join and decode work, queries should always be constrained by item, organization, or UOM class to maintain acceptable performance.