Search Results mtl_system_items_tl_n1

Overview

INV.MTL_SYSTEM_ITEMS_TL is the translation (MLS) table that stores language-specific descriptions for inventory items in Oracle E-Business Suite 12.1.1 and 12.2.2. Multilingual support for item descriptions is implemented as a pair of tables: MTL_SYSTEM_ITEMS_B holds the base (non-translatable) item attributes, while MTL_SYSTEM_ITEMS_TL holds the translated DESCRIPTION and LONG_DESCRIPTION values for each installed language. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, is owned by the INV schema, and carries FND Design Data reference INV.MTL_SYSTEM_ITEMS_TL.

From a dimensional modeling perspective, the heuristic Data Vault classification for this object is satellite-leaning. It should be modeled as a satellite attached to the item hub represented by MTL_SYSTEM_ITEMS_B, since it carries descriptive, language-qualified attributes rather than independent business entities or relationship links. The primary key MTL_SYSTEM_ITEMS_TL_PK (INVENTORY_ITEM_ID, ORGANIZATION_ID, LANGUAGE) reinforces this: the table is wholly dependent on the parent item record for its identity and cannot exist as a standalone hub.

Key Information Stored

The table contains 11 documented columns. The most significant are:

• INVENTORY_ITEM_ID (NUMBER) — Inventory item identifier; part of the composite primary key and the foreign key to MTL_SYSTEM_ITEMS_B.
• ORGANIZATION_ID (NUMBER) — Organization identifier; also part of the primary key, since item definitions are organization-scoped.
• LANGUAGE (VARCHAR2) — The language code of the translation row; completes the primary key.
• SOURCE_LANG (VARCHAR2) — Source language column used by the MLS framework.
• DESCRIPTION (VARCHAR2, 240) — The translated item description, the principal business payload of this table.
• LONG_DESCRIPTION (VARCHAR2, 4000) — The translated long description for the item.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard Who columns tracking audit metadata.

The surrogate/composite primary key is (INVENTORY_ITEM_ID, ORGANIZATION_ID, LANGUAGE). The unique index MTL_SYSTEM_ITEMS_TL_U1 mirrors these three columns on APPS_TS_TX_IDX and serves as the business-key candidate. A non-unique index, MTL_SYSTEM_ITEMS_TL_N1, covers (ORGANIZATION_ID, LANGUAGE, DESCRIPTION) to accelerate filtered description lookups.

Common Use Cases and Queries

Typical uses include multilingual item search, translated pick lists, and reporting where descriptions must be presented in the user's session language. A practical join pattern retrieves the translated description alongside base item attributes:

• SELECT b.INVENTORY_ITEM_ID, b.ORGANIZATION_ID, t.LANGUAGE, t.DESCRIPTION, t.LONG_DESCRIPTION FROM MTL_SYSTEM_ITEMS_B b JOIN MTL_SYSTEM_ITEMS_TL t ON b.INVENTORY_ITEM_ID = t.INVENTORY_ITEM_ID AND b.ORGANIZATION_ID = t.ORGANIZATION_ID WHERE t.LANGUAGE = USERENV('LANG');
• Filter by description using the N1 index: SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID, DESCRIPTION FROM INV.MTL_SYSTEM_ITEMS_TL WHERE ORGANIZATION_ID = :org AND LANGUAGE = :lang AND DESCRIPTION LIKE :pattern;
• Audit translated content changes via LAST_UPDATE_DATE and LAST_UPDATED_BY.

Because a row exists per language, queries must always constrain LANGUAGE (or SOURCE_LANG) to avoid duplicate item rows in reports.

Related Objects

• MTL_SYSTEM_ITEMS_B — Parent base table; joined on INVENTORY_ITEM_ID and ORGANIZATION_ID. The documented foreign key points to this table.
• MTL_SYSTEM_ITEMS_TL — Referenced by the MTL_SYSTEM_ITEMS_TL synonym and the EGO_MTL_SYSTEM_ITEMS_TL_TR1 trigger, which maintains translation integrity for EGO (product information) flows.
• MTL_SYSTEM_ITEMS_TL_TA_IBE — Audit/history companion for iStore (IBE) translation updates.
• OE_ITEMS_MV — Materialized view used by Order Management that depends on the translated item data.
• EGO_MTL_SYSTEM_ITEMS_TL_TR1 — Trigger referencing this table to synchronize translated item descriptions with the EGO item master.

These dependencies confirm that MTL_SYSTEM_ITEMS_TL functions as the canonical translation store for item descriptions across INV, EGO, and OE reporting paths.