Search Results allowed_units_lookup_code

Overview

OKX_ITEM_UOMS_V is a seed data view owned by the APPS schema within the OKX – Contracts Integration product family in Oracle E-Business Suite. Its documented purpose is to expose item unit of measure codes for use by the Contracts Integration module and related reporting. The view is registered as VALID in the ETRM repository across both 12.1.1 and 12.2.2 environments, and its name follows the OKX naming convention (OKX_ITEM_UOMS_V), indicating it is part of the standard seed data set delivered with the Contracts Integration application.

In practical terms, the view functions as a filtered, language-aware listing of the UOMs that are either directly assigned to an inventory item or reachable through the item's primary UOM class hierarchy. It is not a transactional view; it is a reference view that answers the question, "which units of measure may legitimately be used with this item?" for the current session language.

Underlying Base Objects

The view is defined over the following base objects (documented as synonyms in the APPS schema): MTL_SYSTEM_ITEMS_B, MTL_UNITS_OF_MEASURE, MTL_UNITS_OF_MEASURE_TL, MTL_UOM_CLASS_CONVERSIONS, and MTL_UOM_CONVERSIONS. The SQL joins MTL_SYSTEM_ITEMS_B (aliased MTLITM1) to MTL_UOM_CONVERSIONS (MTLUCV) on inventory item, and to MTL_UNITS_OF_MEASURE_TL (MTLUOM2) on UOM code, while MTL_UOM_CLASS_CONVERSIONS supplies the cross-class conversion paths. The join is gated by the item's ALLOWED_UNITS_LOOKUP_CODE (values 1, 2, or 3), which controls whether item-specific, class-based, or both conversion sources are considered, and by NVL(MTLUCV.DISABLE_DATE, SYSDATE+1) > SYSDATE, ensuring only non-expired conversions appear.

The MTL_UOM_CLASS_CONVERSIONS and MTL_UOM_CONVERSIONS relationships explain why a given item can expand to many UOM rows: different UOM classes linked via conversions are all included, provided their disable date has not passed.

Key Columns

• ORGANIZATION_ID – the inventory organization owning the item.
• INVENTORY_ITEM_ID – the item identifier.
• UOM_TYPE – derived from ALLOWED_UNITS_LOOKUP_CODE, indicating whether the item's allowable UOMs come from item-specific, class-based, or combined lookups.
• UOM_CODE – the unit of measure code.
• UNIT_OF_MEASURE – the UOM as stored in MTL_UNITS_OF_MEASURE_TL.
• UNIT_OF_MEASURE_TL – the translatable UOM description sourced from the _TL table.
• UOM_CLASS – the class to which the UOM belongs (e.g., weight, volume, quantity).
• DESCRIPTION – the descriptive text for the UOM.

The language filter MTLUOM2.LANGUAGE = USERENV('LANG') means results are always returned in the session's current language, which is important for multi-language implementations.

Common Use Cases and Queries

Typical uses include validating which UOMs may be selected on a contract or item, populating LOVs in OKX integration UI, and reporting on the effective UOM set per item. A representative query:

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

To list all UOMs available for a specific item in a target organization, bind the item and org. To validate a single UOM:

SELECT COUNT(*) FROM apps.okx_item_uoms_v
WHERE inventory_item_id = :item_id AND uom_code = :uom_code;

Because the view already filters on DISABLE_DATE and language, callers should not re-apply those predicates unless overriding behavior is explicitly desired.