Results for “ego_mtl_sy_items_ext_tl”

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

Overview

EGO_MTL_SY_ITEMS_EXT_TL is a translation (TL) table in the EGO schema, the Advanced Product Catalog module of Oracle E-Business Suite. Its documented purpose is to store user-defined (flexfield-style) attribute values for item extensions that are language-dependent. Where the base table EGO_MTL_SY_ITEMS_EXT_B stores the language-independent definition of an item extension row, this _TL counterpart carries the translatable user-defined attribute values keyed by LANGUAGE, allowing the same extension row to present different attribute text per installed language.

The table is owned by EGO and is documented as VALID in both the 12.1.1 and 12.2.2 ETRM repositories. Its 12.2.2 physical schema comprises 59 columns. The heuristic Data Vault classification mined from the FK structure is standalone; in modeling terms it is best treated as a satellite of an item-extension hub/link rather than as a hub or link itself, since it is a descriptive, non-key-bearing child that hangs off the extension identifier.

Key Information Stored

The documented unique index EGO_MTL_SY_ITEMS_EXT_TL_U1 (LANGUAGE, EXTENSION_ID) establishes the composite business key: a single language version per extension row. The primary key constraint EGO_MTL_SY_ITEMS_EXT_TL_PK is defined on EXTENSION_ID, the surrogate identifier of the underlying extension.

• EXTENSION_ID — surrogate primary key; correlates to the base extension row and to the EGO_MTL_SY_ITEMS_EXT_B side.
• LANGUAGE / SOURCE_LANG — the installed language of the translated attribute values and the source language from which they were derived; LANGUAGE participates in the unique index.
• ORGANIZATION_ID — the inventory organization context for the extension values.
• INVENTORY_ITEM_ID — the inventory item to which the extension attribute set applies.
• ITEM_CATALOG_GROUP_ID — the catalog group (item catalog group) governing the attribute set; documented as a foreign key to MTL_ITEM_CATALOG_GROUPS_B.
• ATTR_GROUP_ID / DATA_LEVEL_ID — the attribute group and data level that define which attribute set the row belongs to.
• REVISION_ID — revision context for the extension attribute values.
• PK1_VALUE … PK5_VALUE — up to five key segment values used to identify the target entity instance for the extension row.
• TL_EXT_ATTR1 … TL_EXT_ATTR40 — the forty translatable extended attribute value columns holding the actual user-defined attribute content.
• Standard audit columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Who-columns maintained by EBS.

Common Use Cases and Queries

Typical usage is to retrieve translated user-defined attribute values for an item when the catalog is displayed in a specific language, or to report attribute content per organization and item.

• Join the _TL row to its base extension row and the item master by EXTENSION_ID, INVENTORY_ITEM_ID and ORGANIZATION_ID.
• Filter to a specific language using LANGUAGE = USERENV('LANG') or an explicit language code, exploiting the U1 index on (LANGUAGE, EXTENSION_ID).
• Resolve catalog group semantics by joining ITEM_CATALOG_GROUP_ID to MTL_ITEM_CATALOG_GROUPS_B.
• Reporting: pivot TL_EXT_ATTR1..40 into descriptive columns for item attribute reporting, or audit who last updated a translated attribute value using the audit columns.

A representative pattern resolves the translated attribute for an item and language by selecting FROM EGO_MTL_SY_ITEMS_EXT_TL WHERE INVENTORY_ITEM_ID = :item AND LANGUAGE = :lang, joining to EGO_MTL_SY_ITEMS_EXT_B on EXTENSION_ID.

Related Objects

• MTL_ITEM_CATALOG_GROUPS_B — referenced by this table's ITEM_CATALOG_GROUP_ID foreign key; supplies catalog group definition.
• EGO_MTL_SY_ITEMS_EXT_B — the base (language-independent) extension table paired with this _TL table via EXTENSION_ID.
• MTL_SYSTEM_ITEMS_B — item master referenced through INVENTORY_ITEM_ID.
• EGO_ITEM_CATALOG_GROUPS / EGO_ATTR_GROUPS — attribute group and catalog group metadata referenced via ATTR_GROUP_ID and ITEM_CATALOG_GROUP_ID.
• EGO_MTL_SY_ITEMS_EXT_TL_PK / EGO_MTL_SY_ITEMS_EXT_TL_U1 — the primary key constraint and unique business-key index on the table itself.