Search Results gmd_recipes_tl




Overview
Explore in AI Chat

Overview

GMD_RECIPES_TL is the translation (multi-language) table for Oracle Process Manufacturing recipes within the GMD – Process Manufacturing Product Development module. Recipes are the master manufacturing instructions in OPM that define the sequence of operations, ingredients, and quantities required to produce a batch of product. Because recipe descriptions must be presented in the user's session language, Oracle maintains the language-independent attributes in the base table GMD_RECIPES_B and stores the translated descriptive text in GMD_RECIPES_TL.

The table is owned by the GMD schema and is valid in both Oracle EBS 12.1.1 and 12.2.2. Its documented physical schema in ETRM 12.2.2 contains nine columns. From a Data Vault modeling perspective, the heuristic classification is standalone: the object depends on the GMD_RECIPES_B hub through RECIPE_ID and carries language-dependent descriptive attributes, so it is most naturally modeled as a satellite of the recipe hub, qualified by LANGUAGE.

Key Information Stored

The documented columns and their roles are as follows:

  • RECIPE_ID – Identifier of the parent recipe; the foreign reference to the base recipe entity and half of the primary key.
  • LANGUAGE – The NLS language code for which the translation row applies; the second half of the primary key.
  • RECIPE_DESCRIPTION#1 – The translated recipe description. The "#1" suffix is the standard OPM mechanism for storing the first translated text attribute of the entity.
  • SOURCE_LANG – The language in which the description was originally authored, used by the translation framework to indicate the source of the value.
  • CREATED_BY – User who created the translation row.
  • CREATION_DATE – Date and time the translation row was created.
  • LAST_UPDATED_BY – User who most recently changed the translation.
  • LAST_UPDATE_DATE – Date and time of the most recent change; the standard WHO column used for change detection and incremental extraction.
  • LAST_UPDATE_LOGIN – Login identifier associated with the most recent update, supporting audit and session-level reporting.

The primary key GMD_RECIPES_TL_PK is composite, defined on (RECIPE_ID, LANGUAGE). This unique index is also the business-key candidate: a recipe may have at most one translated description row per language. RECIPE_ID functions as the surrogate-to-business link to the recipe hub, while LANGUAGE discriminates the satellite rows.

Common Use Cases and Queries

Typical scenarios include verifying translation coverage before a multi-language rollout, extracting localized recipe descriptions for reports and labels, and auditing which recipes lack a row for a target language. A join back to the base table is almost always required, since GMD_RECIPES_TL holds no operational attributes beyond the description.

List all translations for a given recipe:

  • SELECT RECIPE_ID, LANGUAGE, SOURCE_LANG, RECIPE_DESCRIPTION#1 FROM GMD.GMD_RECIPES_TL WHERE RECIPE_ID = :p_recipe_id;

Find recipes missing a specific language translation, a common data-quality check:

  • SELECT b.RECIPE_ID FROM GMD.GMD_RECIPES_B b WHERE NOT EXISTS (SELECT 1 FROM GMD.GMD_RECIPES_TL t WHERE t.RECIPE_ID = b.RECIPE_ID AND t.LANGUAGE = 'US');

Incremental extraction for a data warehouse can key off LAST_UPDATE_DATE:

  • SELECT RECIPE_ID, LANGUAGE, RECIPE_DESCRIPTION#1, LAST_UPDATE_DATE FROM GMD.GMD_RECIPES_TL WHERE LAST_UPDATE_DATE >= :p_since;

Related Objects

The translation table is tightly coupled to the following objects:

  • GMD_RECIPES_B – Base (language-independent) recipe table; join on RECIPE_ID and supply the LANGUAGE predicate from the session or report parameter.
  • GMD_RECIPES_TL_PK – Composite unique index on (RECIPE_ID, LANGUAGE) that enforces one translation per recipe per language.
  • GMD_OPERATIONS_TL / GMD_RECIPE_OPERATIONS – Operation-level translations and recipe-operation links that combine with the recipe description when building a complete multilingual recipe document.
  • FND_LANGUAGES – Reference table for valid LANGUAGE and SOURCE_LANG codes.
  • OPM Recipe APIs / OPM Product Development forms – Application code that inserts and maintains translation rows through the OPM recipe maintenance UI.

Because the object is classified as standalone with no outgoing foreign keys documented, joins to parent entities should be driven explicitly from GMD_RECIPES_B on RECIPE_ID rather than inferred through database constraints.