Results for “mtl_cross_references_tl_u1”

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

Overview

The INV.MTL_CROSS_REFERENCES_TL table is the translation (TL) component of the multilingual schema (MLS) implementation for cross-references in Oracle E-Business Suite. It stores the translated Description column for cross-reference records, enabling organizations operating in multiple languages to maintain localized descriptions of the relationships between items, item revisions, and their associated identifiers across the inventory and order management domains.

Cross-references in Oracle EBS define relationships such as substitute items, related items, customer item numbers, supplier item numbers, and competitor item mappings. The MLS architecture splits this data across two paired tables: MTL_CROSS_REFERENCES_B (the base table holding language-independent columns such as CROSS_REFERENCE_ID, type codes, and identifiers) and MTL_CROSS_REFERENCES_TL (the translation table holding the language-dependent DESCRIPTION). The _TL table is physically distinct and does not itself store the parent keys beyond CROSS_REFERENCE_ID, which it inherits from the base table.

With respect to Data Vault modeling heuristics, this table is classified as a standalone structure. From a dimensional or Data Vault perspective, it is best modeled as a satellite of the cross-reference hub (the conceptual business key being CROSS_REFERENCE_ID). The table carries descriptive, language-qualified attributes and standard audit ("Who") columns rather than foreign-key relationships to other hubs, consistent with a satellite role.

Key Information Stored

The table contains nine documented columns. The most operationally significant are:

• CROSS_REFERENCE_ID (NUMBER) — The cross-reference identifier. This is the surrogate key linking to the base table and forms part of the primary key.
• LANGUAGE (VARCHAR2) — The language code for the translated row, forming the second component of the primary key.
• SOURCE_LANG (VARCHAR2) — Indicates the source language from which the translation derives, used by MLS to determine whether the row is the original or a translated variant.
• DESCRIPTION (VARCHAR2, length 240) — The translated description of the cross-reference; this is the principal business attribute the table exists to hold.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Oracle "Who" audit columns recording row creation and last modification metadata.

The documented primary key is MTL_CROSS_REFERENCES_TL_PK (CROSS_REFERENCE_ID, LANGUAGE). A separate unique index, MTL_CROSS_REFERENCES_TL_U1, is defined on the same (CROSS_REFERENCE_ID, LANGUAGE) column pair in tablespace APPS_TS_TX_IDX. In this schema the primary-key columns effectively also serve as the business-key candidate, since the combination of cross-reference and language naturally qualifies a single translated description. The table resides in tablespace APPS_TS_TX_DATA.

Common Use Cases and Queries

The typical access pattern joins the translation table to its base table to retrieve a localized description alongside the language-independent cross-reference attributes. A representative query filters by language and cross-reference:

SELECT b.cross_reference_id,
       b.cross_reference_type,
       t.language,
       t.description
FROM   inv.mtl_cross_references_b b,
       inv.mtl_cross_references_tl t
WHERE  b.cross_reference_id = t.cross_reference_id
AND    t.language = USERENV('LANG');

Common scenarios include: multilingual item cross-reference reporting where users expect descriptions in their session language; data migration or interface loads that must populate both the _B and _TL tables in tandem; and integrity audits verifying that every base row has at least one matching translation row. Because the primary key is (CROSS_REFERENCE_ID, LANGUAGE), uniqueness checks and merge logic should always key on both columns. Reporting tools and materialized views (such as OE_ITEMS_MV) exploit the join to present translated descriptions in order-management contexts.

Related Objects

• INV.MTL_CROSS_REFERENCES_B — The base table holding language-independent cross-reference data; joined on CROSS_REFERENCE_ID.
• INV.MTL_CROSS_REFERENCES_TL# — The underlying table object referenced within the INV schema, supporting the MLS infrastructure.
• APPS.OE_ITEMS_MV — A materialized view that depends on this table, used to expose item data including translated cross-reference descriptions.
• APPS schema objects — The APPS synonym layer that exposes MTL_CROSS_REFERENCES_TL for application-tier SQL and forms.
• MTL_SYSTEM_ITEMS_B / _TL — Related MLS tables describing items to which cross-references are attached.

The table does not reference any database object per its dependency record; it is referenced by OE_ITEMS_MV and the internal MTL_CROSS_REFERENCES_TL# object. This standalone yet satellite-like role confirms its function as a pure descriptive store keyed to the cross-reference identifier and language.