Search Results mtl_lot_uom_class_conv_u1




Overview
Explore in AI Chat

Overview

INV.MTL_LOT_UOM_CLASS_CONVERSIONS is a seed-data table that stores lot-specific unit-of-measure conversions in Oracle E-Business Suite Release 12.1.1 and 12.2.2. Whereas standard UOM conversions in MTL_UOM_CONVERSIONS apply globally to an item or a UOM class, this table narrows the conversion scope to an individual lot. It allows an organization to define a conversion rate that applies only to a specific combination of organization, inventory item, lot number, source UOM, and target UOM — for example, when a particular lot of material has a density, concentration, or potency that differs from the item's standard assumption.

The table resides in the APPS_TS_SEED tablespace, consistent with its role as reference or setup data rather than high-volume transactional data. Many rows are seeded by Oracle, but implementations may also insert custom lot-level conversions. Its columns track the two UOM identities (code, class, and descriptive unit of measure), the conversion rate, and a disable date that sunsets the conversion without physically deleting the row. Standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) provide auditability. A foreign key to GMD_EVENT_SPEC_DISP links a conversion to a quality specification disposition, tying lot conversion behavior to quality results. The metadata's heuristic Data Vault classification is standalone — no parent hub or link is implied by the FK structure, so it is best modeled as an independent reference satellite keyed by its own surrogate.

Key Information Stored

The surrogate primary key is CONVERSION_ID (NUMBER), enforced by the unique index MTL_LOT_UOM_CLASS_CONV_PK. The business-key candidate is defined by the unique index MTL_LOT_UOM_CLASS_CONV_U1 across ORGANIZATION_ID, INVENTORY_ITEM_ID, LOT_NUMBER, FROM_UOM_CODE, and TO_UOM_CODE — meaning a given lot may have only one active conversion between any ordered pair of UOM codes within an organization and item. The most significant columns are:

  • CONVERSION_ID — surrogate identifier and primary key; the target of any child references.
  • ORGANIZATION_ID — the inventory organization in which the conversion is valid.
  • INVENTORY_ITEM_ID — surrogate key of the item the lot belongs to.
  • LOT_NUMBER — the specific lot (VARCHAR2(80)) to which the conversion applies.
  • FROM_UOM_CODE / TO_UOM_CODE — the source and target unit-of-measure codes that make up the conversion pair.
  • FROM_UOM_CLASS / TO_UOM_CLASS — the UOM classes of the respective codes, used to validate that the conversion crosses compatible classes.
  • FROM_UNIT_OF_MEASURE / TO_UNIT_OF_MEASURE — descriptive unit-of-measure names aligned to the codes.
  • CONVERSION_RATE — the multiplier applied when converting from the source to the target UOM for this lot.
  • DISABLE_DATE — the date on which the conversion ceases to be effective, supporting soft retirement.
  • EVENT_SPEC_DISP_ID — quality specification disposition reference (FK to GMD_EVENT_SPEC_DISP).
  • Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and the request/program columns for audit and concurrent-program traceability.

Common Use Cases and Queries

Typical scenarios include resolving the correct conversion when receiving, issuing, or transacting a specific lot whose UOM relationship deviates from the item standard; validating that an entered lot conversion is unique per item/organization/UOM pair; and reporting which lot conversions are currently active versus disabled. A representative query retrieves the active conversion for a lot:

  • SELECT conversion_id, organization_id, inventory_item_id, lot_number, from_uom_code, to_uom_code, conversion_rate FROM inv.mtl_lot_uom_class_conversions WHERE organization_id = :org AND inventory_item_id = :item AND lot_number = :lot AND from_uom_code = :from_uom AND to_uom_code = :to_uom AND (disable_date IS NULL OR disable_date > SYSDATE).
  • Uniqueness check before insert: query the same business-key columns without the rate predicate to detect a conflicting row.
  • Expiry reporting: filter on DISABLE_DATE to list conversions scheduled for retirement.

Related Objects

The FK relationship documented in the metadata ties the table to quality disposition data, while operational joins are driven by the business key. Significant related objects include: