Search Results mtl_units_of_measure_tl




Overview
Explore in AI Chat

Overview

MTL_UNITS_OF_MEASURE_TL is a translatable (TL) table in the Oracle Inventory (INV) schema that stores the language-specific descriptive text for unit of measure definitions in Oracle EBS 12.1.1 and 12.2.2. It holds the user-facing description and name attributes of a unit of measure in each installed language, while the base definition — the UOM code, class, and conversion characteristics — resides in the companion non-translated table MTL_UNITS_OF_MEASURE. The TL architecture allows a single UOM to carry multiple language rows distinguished by the LANGUAGE column, with SOURCE_LANG identifying the original installation language for translation comparison.

From a data modeling perspective, the metadata classifies this object as standalone based on its foreign key structure, and it carries no outbound foreign keys to other tables. A Data Vault modeling suggestion would therefore treat it as a reference or descriptive satellite attached to a UOM business key (UNIT_OF_MEASURE / UOM_CODE plus LANGUAGE), rather than as a hub or link in its own right. In ETRM terms it functions as a descriptive satellite supplying multi-language attributes.

Key Information Stored

  • UNIT_OF_MEASURE — The internal numeric identifier of the unit of measure; part of the surrogate primary key MTL_UNITS_OF_MEASURE_TL_PK alongside LANGUAGE.
  • UOM_CODE — The short alphanumeric business code for the UOM (for example EA, KG, L). Combined with LANGUAGE and ZD_EDITION_NAME it forms the unique business key candidate MTL_UNITS_OF_MEASURE_TL_U2.
  • LANGUAGE — The NLS language identifier for the row; the second component of both the primary key and the business-key unique index.
  • UOM_CLASS — The classification grouping the UOM belongs to (quantity, weight, volume, time, and so on), used to control conversion eligibility.
  • BASE_UOM_FLAG — Indicates whether the row defines the base unit for its class.
  • UNIT_OF_MEASURE_TL — The translatable display name of the unit of measure in the row's language.
  • DESCRIPTION — The language-specific descriptive text for the unit of measure.
  • DISABLE_DATE — The date on which the unit of measure translation was disabled or superseded.
  • SOURCE_LANG — The language of the original base record, used by the translation framework to detect untranslated rows.
  • ZD_EDITION_NAME — The editioning column supporting Online Patching in 12.2.x; also part of unique indexes U1 and U2.
  • Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN capture row history.
  • Concurrent request columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE identify the process that last touched the row.
  • ATTRIBUTE1 through ATTRIBUTE15 and ATTRIBUTE_CATEGORY — The standard Oracle EBS descriptive flexfield columns.

The surrogate primary key is MTL_UNITS_OF_MEASURE_TL_PK (UNIT_OF_MEASURE, LANGUAGE); the business-key candidates are U1 (UNIT_OF_MEASURE, LANGUAGE, ZD_EDITION_NAME) and U2 (UOM_CODE, LANGUAGE, ZD_EDITION_NAME).

Common Use Cases and Queries

Reporting and integration tasks routinely join this table to the base table to resolve both the code and its translated description. A typical query retrieves all English-language UOM definitions:

  • Lookup by code: SELECT unit_of_measure, uom_code, unit_of_measure_tl, description FROM mtl_units_of_measure_tl WHERE uom_code = :code AND language = USERENV('LANG');
  • Translation completeness: compare SOURCE_LANG rows against rows in other languages to detect missing translations.
  • Reporting joins: combine with MTL_SYSTEM_ITEMS_B to present item primary UOM descriptions in user-facing reports.
  • Conversion validation: join to MTL_UOM_CONVERSIONS to confirm that UOM codes selected for conversions exist and are active (DISABLE_DATE IS NULL).

Related Objects

  • MTL_UNITS_OF_MEASURE — The base, non-translated UOM table; join on UNIT_OF_MEASURE to obtain class and conversion attributes.
  • MTL_UOM_CONVERSIONS — Stores conversion factors between UOMs; joins on UOM_CODE / UNIT_OF_MEASURE.
  • MTL_SYSTEM_ITEMS_B — References the primary unit of measure for each inventory item; joins on PRIMARY_UOM_CODE.
  • MTL_ITEM_LOCATIONS / inventory quantity tables — Display UOM codes in transactional and on-hand reporting.
  • FND_LANGUAGES — Provides the valid LANGUAGE values and their translated names.
  • Standard UOM APIs — INV_UOM_PUB and related PL/SQL packages consume and validate UOM definitions against these tables.

Because the table is standalone with no outbound foreign keys, integration logic typically keys off UOM_CODE and LANGUAGE rather than a surrogate reference, and any schema changes should account for the online-patching ZD_EDITION_NAME column in 12.2.x.