Search Results allowed_units_lookup_code




Overview
Explore in AI Chat

Overview

APPS.OKX_ITEM_UOMS_V is an Oracle E-Business Suite view that exposes the complete set of units of measure (UOMs) permitted for a given inventory item within a specific inventory organization. It resolves the relationship between an item's ALLOWED_UNITS_LOOKUP_CODE attribute on MTL_SYSTEM_ITEMS_B and the conversion definitions held in the UOM conversion and class conversion tables, returning one row per valid, enabled UOM. The view is commonly used in Order Management, purchasing, and pricing extensions where the application must validate or list the UOMs a user is permitted to select for an item.

The object is owned by APPS and is available in both 12.1.1 and 12.2.2. The view text is identical across both releases, so reports and integrations built against it remain portable during an upgrade from 12.1.3 to 12.2.x. Because it is a database view rather than a table, it presents no storage of its own; all rows are derived at query time, ensuring that UOM eligibility always reflects current item and conversion setup.

Underlying Base Objects

The view is defined over five documented base objects, each referenced through an APPS synonym: MTL_SYSTEM_ITEMS_B, MTL_UNITS_OF_MEASURE, MTL_UNITS_OF_MEASURE_TL, MTL_UOM_CLASS_CONVERSIONS, and MTL_UOM_CONVERSIONS.

  • MTL_SYSTEM_ITEMS_B (aliased MTLITM1) supplies the item identity, organization, primary UOM, and the ALLOWED_UNITS_LOOKUP_CODE, which drives the branching logic of the view.
  • MTL_UOM_CONVERSIONS (MTLUCV) provides both item-specific and global (INVENTORY_ITEM_ID = 0) conversion rows, filtered so that disabled conversions are excluded.
  • MTL_UOM_CLASS_CONVERSIONS (MTLUCC) supplies class-level conversions for the item, again filtered on DISABLE_DATE.
  • MTL_UNITS_OF_MEASURE and MTL_UNITS_OF_MEASURE_TL (MTLUOM2) provide the UOM code, the language-specific unit of measure name, the UOM class, and the description.

The join is a multi-branch predicate: when ALLOWED_UNITS_LOOKUP_CODE is 1 or 3, item-specific conversions apply; when it is 2 or 3, class-level conversions apply; and a global base UOM row is always eligible when its class matches the item's primary UOM class. The view joins MTLUOM2.UOM_CODE to MTLUCV.UOM_CODE and filters MTLUOM2.LANGUAGE to the session language via USERENV('LANG'), so descriptions are returned in the user's locale.

Key Columns

  • ORGANIZATION_ID — the inventory organization in which the item is defined.
  • INVENTORY_ITEM_ID — the item identifier, joining to MTL_SYSTEM_ITEMS_B.
  • UOM_TYPE — the item's ALLOWED_UNITS_LOOKUP_CODE, exposed under this alias. Values 1, 2, and 3 control whether item-specific, class-level, or both conversion sets are honored.
  • UOM_CODE — the unit of measure code, the principal value used in validation.
  • UNIT_OF_MEASURE and UNIT_OF_MEASURE_TL — the translated unit of measure name from the _TL table.
  • UOM_CLASS — the UOM class to which the unit belongs.
  • DESCRIPTION — the language-specific description of the UOM.

Common Use Cases and Queries

The most frequent use is generating a validated list of values for an item's orderable or purchasable UOMs. A typical query filters by organization and item:

  • SELECT uom_code, unit_of_measure FROM apps.okx_item_uoms_v WHERE organization_id = :org AND inventory_item_id = :item ORDER BY uom_code;
  • SELECT uom_code FROM apps.okx_item_uoms_v WHERE inventory_item_id = :item AND uom_type IN (1,3); — isolates item-specific allowed units.
  • Validation logic in order entry or receiving extensions can check EXISTS (SELECT 1 FROM apps.okx_item_uoms_v WHERE inventory_item_id = :item AND uom_code = :entered_uom) before accepting a transaction UOM.

Because the view already excludes disabled conversions and honors the session language, it is preferable to re-implementing the conversion logic manually. Note that it returns UOMs, not conversion rates; those must be obtained from MTL_UOM_CONVERSIONS separately when a rate is required.