Results for “substitution_description”

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

Overview

GMD_ITEM_SUBSTITUTION_HDR_TL is the translation table for the Item Substitution header entity within the Oracle Process Manufacturing (OPM) Product Development module (GMD). In Oracle EBS 12.1.1 and 12.2.2, item substitution allows planners, formulators, and production personnel to define approved replacement items for a given inventory or formula item so that demand can be satisfied when the primary item is unavailable. The substitution header holds the controlling definition of a substitution relationship, while this _TL table stores the language-dependent descriptive text associated with that header.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone. This suggests it may be modeled as its own satellite or reference structure rather than as a dependent child of a larger hub/link construct, since the mined foreign-key structure did not surface a clear parent dependency. The object is flagged as VALID in the GMD schema and is owned by GMD.

Key Information Stored

The table is narrow by design, with nine documented physical columns. The most significant are:

• SUBSTITUTION_ID — Identifier of the parent substitution header record. This is the operational foreign key linking the translation row back to the base GMD_ITEM_SUBSTITUTION_HDR table.
• LANGUAGE — The language code for the translated content, for example US for American English.
• SUBSTITUTION_DESCRIPTION — The translated, user-facing description of the substitution. This is the principal payload of the table and the reason it exists separately from the header.
• SOURCE_LANG — The language from which the description was originally derived, supporting translation workflows in multi-language implementations.
• CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns capturing who created and last maintained each translated row, and when.

The primary key is documented as GMD_ITEM_SUBSTITUTION_HDR_TL_P on (LANGUAGE, SUBSTITUTION_ID), which uniquely identifies a translation row for a given language and header. A unique index, GMD_ITEM_SUBSTITUTION_HDR_TL_U, is maintained on (SUBSTITUTION_ID, LANGUAGE), providing a business-key candidate that guarantees one translation per header per language regardless of column order in the constraint definition.

Common Use Cases and Queries

The table is primarily queried when reports or extensions must display substitution descriptions in a specific language. A typical query joins the header to its translation:

• Retrieve translated descriptions: SELECT h.substitution_id, t.substitution_description FROM gmd_item_substitution_hdr h, gmd_item_substitution_hdr_tl t WHERE h.substitution_id = t.substitution_id AND t.language = USERENV('LANG');
• Audit translated rows: query by LANGUAGE to confirm coverage across supported languages.
• Identify untranslated headers: outer-join the header to the _TL table and filter on NULL SUBSTITUTION_DESCRIPTION.

Common reporting scenarios include substitution master listings, language coverage audits for global rollouts, and integration extracts feeding downstream planning or labeling systems.

Related Objects

The following objects are most relevant, with join columns noted:

• GMD_ITEM_SUBSTITUTION_HDR — the base header table; join on SUBSTITUTION_ID.
• GMD_ITEM_SUBSTITUTION_DTL — detail lines defining the substitute items; related through the header's SUBSTITUTION_ID.
• GMD_ITEM_SUBSTITUTION_HDR_TL_P / _U — primary key and unique index structures enforcing integrity.
• FND_LANGUAGES — reference table for valid LANGUAGE codes and translated language names.
• MTL_SYSTEM_ITEMS_B / MTL_SYSTEM_ITEMS_TL — item master tables referenced by the substitution detail lines.

Together these objects form the substitution definition and translation layer within the OPM Product Development module.