Results for “mtl_system_items_tl”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MTL_SYSTEM_ITEMS_TL is the translation table for item master records in Oracle E-Business Suite Inventory (INV). It stores language-specific descriptive text — item description and long description — for rows defined in the base table MTL_SYSTEM_ITEMS_B. The "_TL" suffix follows the standard Oracle multi-language naming convention, indicating that the table participates in the Oracle Translation (MLS) framework and holds one row per item per installed language.

The table is owned by the INV schema and is classified as VALID in the ETRM repository for releases 12.1.1 and 12.2.2. Because MTL_SYSTEM_ITEMS_B is itself organization-scoped, item identity in EBS is expressed as the combination of INVENTORY_ITEM_ID and ORGANIZATION_ID; the translation table carries both of these keys, so multilingual descriptive content is partitioned by organization as well as by language.

From a data-modeling perspective, the heuristic Data Vault classification supplied in the metadata is satellite-leaning. This is a reasonable interpretation: MTL_SYSTEM_ITEMS_TL depends on the parent item hub (or link, where the item-to-organization relationship is modeled) and carries descriptive, language-keyed attributes rather than introducing new business keys of its own.

Key Information Stored

The documented schema exposes 11 columns. The most significant are listed below.

• INVENTORY_ITEM_ID — Surrogate identifier of the item, shared with MTL_SYSTEM_ITEMS_B. Part of the composite primary key.
• ORGANIZATION_ID — The inventory organization in which the item is defined. Part of the composite primary key and required to resolve the item correctly.
• LANGUAGE — The installed language code for which the row's descriptive text applies. Completes the composite primary key.
• SOURCE_LANG — The language in which the row was originally authored, used by the MLS framework to determine translation status.
• DESCRIPTION — The short, language-specific item description displayed throughout EBS forms, lists of values and reports.
• LONG_DESCRIPTION — The extended textual description of the item, typically maintained in the item master's descriptive text areas.
• LAST_UPDATE_DATE / LAST_UPDATED_BY / LAST_UPDATE_LOGIN — Standard EBS audit columns capturing when and by whom the translation row was last changed.
• CREATION_DATE / CREATED_BY — Standard EBS audit columns recording the row's initial creation.

The primary key MTL_SYSTEM_ITEMS_TL_PK is defined on (INVENTORY_ITEM_ID, ORGANIZATION_ID, LANGUAGE). A unique index, MTL_SYSTEM_ITEMS_TL_U1, is documented on the same three columns and therefore serves as the business-key candidate for the entity. Because the surrogate identifier is composite rather than a single generated value, the practical distinction between the PK and the unique index is nominal; both enforce the same natural-key uniqueness. The foreign key on (INVENTORY_ITEM_ID, ORGANIZATION_ID) references MTL_SYSTEM_ITEMS_B, ensuring that translations can exist only for items that have a base record in that organization.

Common Use Cases and Queries

MTL_SYSTEM_ITEMS_TL is most commonly accessed when reporting item descriptions in a specific language, or when joining item attributes to descriptive text. A typical pattern joins the translation row to its base table and filters on both the organization and the session or report language:

• Item description lookup: SELECT b.segment1, t.description FROM mtl_system_items_b b, mtl_system_items_tl t WHERE b.inventory_item_id = t.inventory_item_id AND b.organization_id = t.organization_id AND t.language = USERENV('LANG') — the standard pattern for displaying an item description in the user's language.
• Multilingual reporting: Restricting to a particular LANGUAGE to produce reports in a fixed corporate language, independent of session settings.
• Translation completeness: Detecting rows where SOURCE_LANG differs from LANGUAGE, which can indicate untranslated or stale descriptive text.
• Data migration and interfaces: Populating descriptions when loading items via open interfaces, where the interface must supply the correct language-keyed row.
• Extracts to downstream systems: Joining item master attributes with localized descriptions for data warehouses and e-commerce catalogs.

Related Objects

• MTL_SYSTEM_ITEMS_B — The base (non-translated) item master table. The foreign key on (INVENTORY_ITEM_ID, ORGANIZATION_ID) is directed at this table; virtually all item queries join to it.
• MTL_SYSTEM_ITEMS_VL — The MLS view that combines base and translated columns, and the object most application code queries instead of the _TL table directly.
• MTL_SYSTEM_ITEMS_KFV — The key-flexfield view for items, used for descriptive flexfields and concatenated segment displays.
• MTL_ITEM_CATEGORIES — Associates items with categories and is frequently joined for reporting that includes localized item descriptions.
• MTL_ONHAND_QUANTITIES — Inventory on-hand balances, joined to translations when on-hand reports require descriptive labels.
• MTL_TRANSACTIONS_INTERFACE / MTL_MATERIAL_TRANSACTIONS — Transaction processing tables linked to the item master for movement and receipt reporting.
• FND_LANGUAGES — The language reference table that defines valid values for LANGUAGE and SOURCE_LANG.
• EGO_ITEM_PUB / EGO_ITEM_GRP — The item public APIs used to create and maintain item data, which maintain the translation rows behind the scenes.