Search Results secondary_uom_code




Overview
Explore in AI Chat

Overview

MTL_ITEM_LOT_UOM_CONV_ERV is an APPS-owned reporting view in the Oracle E-Business Suite Inventory (INV) module that consolidates unit-of-measure conversion information for lot-controlled items. The view serves as a unified audit and reference source that combines two distinct conversion contexts: lot-specific UOM class conversions and item-level intra-class conversions. Its name reflects this dual purpose, as the "ERV" suffix and UNION-based construction indicate an event or enterprise reporting view designed to flatten conversion data from multiple transactional and master-level tables into a single consistent result set.

The view is particularly relevant to implementations that use Oracle Process Manufacturing (OPM) lot conversion functionality alongside standard discrete inventory lot control. It exposes conversion rates, audit trails, reason codes, and batch associations, making it suitable for compliance reporting, conversion audit reviews, and integration with external quality or manufacturing systems. A notable column exposed by this view is SECONDARY_UOM_CODE, which appears alongside PRIMARY_UOM_CODE from the item master, supporting dual-unit-of-measure inventory environments.

Underlying Base Objects

The view is defined over a UNION of two queries. The first query joins MTL_LOT_UOM_CLASS_CONVERSIONS, MTL_LOT_CONV_AUDIT, MTL_SYSTEM_ITEMS_KFV, MTL_PARAMETERS, HR_ALL_ORGANIZATION_UNITS, MFG_LOOKUPS, MTL_TRANSACTION_REASONS, and GME_BATCH_HEADER. This branch returns lot-level conversion records with their full audit history, producing a CONVERSION_TYPE of 'LOT'.

The second query draws from MTL_UOM_CLASS_CONVERSIONS and MTL_UOM_CONVERSIONS together with MTL_SYSTEM_ITEMS_KFV and MTL_PARAMETERS, returning item-level intra-class conversions with a CONVERSION_TYPE of 'ITEM-INTRA'. Documented base objects include GME_BATCH_HEADER, HR_ALL_ORGANIZATION_UNITS, MFG_LOOKUPS, MTL_LOT_CONV_AUDIT, MTL_LOT_UOM_CLASS_CONVERSIONS, MTL_PARAMETERS, MTL_SYSTEM_ITEMS_KFV, MTL_TRANSACTION_REASONS, MTL_UNITS_OF_MEASURE, MTL_UOM_CLASS_CONVERSIONS, and MTL_UOM_CONVERSIONS.

Key Columns

Common Use Cases and Queries

Typical uses include auditing lot conversion changes, reconciling secondary UOM data, and feeding quality or MES integrations. The following query lists lot-level conversions for a secondary-UOM item:

SELECT event_id, conversion_type, item_number, lot_number, from_uom_code, to_uom_code, conversion_rate, primary_uom_code, secondary_uom_code FROM apps.mtl_item_lot_uom_conv_erv WHERE conversion_type = 'LOT' AND secondary_uom_code IS NOT NULL AND inventory_item_id = :item_id;

A second scenario retrieves the audit trail with reason and batch context:

SELECT event_id, update_type, reason_name, batch_no, creation_date, last_update_date FROM apps.mtl_item_lot_uom_conv_erv WHERE organization_id = :org_id AND creation_date >= :from_date ORDER BY creation_date DESC;