Search Results ic_item_mst_tl




Overview
Explore in AI Chat

Overview

IC_ITEM_MST_TL is the translations table for the Oracle Process Manufacturing (OPM) item master within the GMI schema. In Oracle EBS 12.1.1 and 12.2.2, it stores multilingual item descriptions that correspond to the base item records held in IC_ITEM_MST_B. The table exists specifically so that OPM items can carry language-specific descriptions without duplicating the entire item master record for each installed language. It is owned by GMI — Process Manufacturing Inventory — and is documented as VALID in ETRM 12.2.2.

From a data modeling perspective, the metadata's heuristic Data Vault classification places this object as a standalone entity. In practice, however, its composite primary key of ITEM_ID plus LANGUAGE and its descriptive payload (translated item descriptions) make it behave like a satellite attached to the IC_ITEM_MST_B hub: the ITEM_ID is the hub key, LANGUAGE is the driving multi-active attribute, and the description columns carry the context-dependent descriptive load. Readers designing a vault model around OPM should treat IC_ITEM_MST_TL as a multi-active satellite keyed on ITEM_ID and LANGUAGE.

Key Information Stored

The documented physical schema contains 10 columns. The most significant are:

  • ITEM_ID — The numeric surrogate identifier of the OPM item. It joins back to the base item master and forms half of the composite primary key.
  • LANGUAGE — The NLS language code for which the descriptions apply. Together with ITEM_ID it constitutes the unique business key, enforced by index IC_ITEM_MST_TL_PK.
  • SOURCE_LANG — Indicates the source or base language of the record, distinguishing translations from the original-language entry.
  • ITEM_DESC1 and ITEM_DESC2 — The translated item descriptions. These are the primary payload columns and the reason the table exists.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Oracle who-columns providing audit and concurrency information for each translation row.

The surrogate primary key is the combination (ITEM_ID, LANGUAGE) rather than a single generated sequence, so there is no separate numeric row identifier. The same two columns also serve as the documented business-key candidate. There is no independent foreign key structure recorded in the metadata, reinforcing the standalone classification.

Common Use Cases and Queries

The table is most often queried when reporting item descriptions in a user's session language, when auditing which translations exist for a given item, or when validating completeness of translated descriptions before a multilingual rollout. A typical pattern joins the translations table to the base item master on ITEM_ID, filtering by LANGUAGE or by the session language, for example:

  • Retrieve a description for one item and language: SELECT ITEM_DESC1 FROM GMI.IC_ITEM_MST_TL WHERE ITEM_ID = :p_item_id AND LANGUAGE = USERENV('LANG');
  • Find items missing a translation in a target language: SELECT b.ITEM_ID FROM GMI.IC_ITEM_MST_B b WHERE NOT EXISTS (SELECT 1 FROM GMI.IC_ITEM_MST_TL t WHERE t.ITEM_ID = b.ITEM_ID AND t.LANGUAGE = 'FR');
  • List all translations for an item: SELECT LANGUAGE, SOURCE_LANG, ITEM_DESC1 FROM GMI.IC_ITEM_MST_TL WHERE ITEM_ID = :p_item_id ORDER BY LANGUAGE;
  • Audit recent translation changes: SELECT ITEM_ID, LANGUAGE, LAST_UPDATED_BY, LAST_UPDATE_DATE FROM GMI.IC_ITEM_MST_TL WHERE LAST_UPDATE_DATE >= :p_since;

These queries support multilingual item catalogs, label printing, and regulatory submissions that require descriptions in a designated language.

Related Objects

The principal relationships are:

  • IC_ITEM_MST_B — The base OPM item master. It is the parent of this translations table; rows join on ITEM_ID and share the item's identity.
  • IC_ITEM_MST_TL_PK — The unique index over (ITEM_ID, LANGUAGE) that enforces the one-translation-per-item-per-language rule.
  • IC_ITEM_MST_VL — The commonly exposed view layer combining base and translation data for language-aware item lookups.
  • IC_ITEM_MST_B / IC_ITEM_MST_TL owner GMI — Both reside in the GMI schema, so cross-schema access requires appropriate grants or synonyms.
  • MTL_SYSTEM_ITEMS_B — The discrete/inventory item master. Where OPM items are mapped to inventory items, descriptions may be reconciled between the two models.

Because the object is classified as standalone with no documented foreign keys, joins to it are driven by the ITEM_ID business relationship rather than enforced referential constraints.