Results for “fm_form_mst_tl”

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

Overview

FM_FORM_MST_TL is the translation (language) table for the formula header master record in Oracle EBS Process Manufacturing Product Development (GMD). In Oracle EBS, master tables that store user-facing descriptive text typically split into a language-independent base table (FM_FORM_MST_B for the base record) and a language-dependent translation table (FM_FORM_MST_TL), which carries the translated descriptions keyed by language. FM_FORM_MST_TL holds the formula description text that appears on the formula header, enabling multilingual support for formula names and descriptions across the enterprise.

The metadata classifies this object heuristically as a Data Vault satellite. From a modeling perspective, this is appropriate: the table's grain is one row per (FORMULA_ID, LANGUAGE), and it stores descriptive, dependent attributes (the translated descriptions) that describe a business key owned elsewhere. It is not a hub — the formula identity is not originated here — and it is not a link, as it does not resolve a many-to-many relationship between distinct business entities. It attaches descriptive context to the formula hub.

Key Information Stored

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

• FORMULA_ID — Foreign key to the formula header (the business entity). Part of the composite primary key.
• LANGUAGE — The language code for the translation row. Part of the composite primary key and the discriminator that makes this a translation table.
• SOURCE_LANG — The language from which the translation was derived, supporting Oracle's translation-source tracking for the seeded base language.
• FORMULA_DESC1#1 — The translated formula description line 1. The #1 suffix is an Oracle Applications convention denoting a language-dependent (translatable) column, one variant per installed language.
• FORMULA_DESC2#1 — The translated formula description line 2, following the same language-dependent convention.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Oracle EBS audit columns (WHO columns) recording row creation and last modification.

The surrogate/business key is defined by the unique index FM_FORM_MST_TL_PK on (FORMULA_ID, LANGUAGE). This composite key — not a single-column surrogate — is the true uniqueness constraint and the target of the user's search. Because the key combines the owning entity and the language, it functions as both the primary key and the natural business key, guaranteeing exactly one translated description per formula per language.

Common Use Cases and Queries

Typical usage centers on multilingual formula reporting and translation completeness checks. A common pattern joins the translation table to the base table to retrieve descriptions in a chosen language:

SELECT m.formula_id, m.formula_no, t.formula_desc1#1
FROM   gmd.fm_form_mst_b m,
       gmd.fm_form_mst_tl t
WHERE  m.formula_id = t.formula_id
AND    t.language = USERENV('LANG');

Reporting scenarios include validating that every formula has a translation row for each active language (translation-gap analysis), auditing who last changed a translated description using the WHO columns, and driving formula lookup lists in the user's session language. Analysts also query FM_FORM_MST_TL_PK directly to confirm the uniqueness of formula/language combinations before data loads.

Related Objects

• FM_FORM_MST_B — The base (language-independent) formula header table; join on FORMULA_ID. This is the primary parent of the translation rows.
• FM_FORM_MST_TL_PK — The unique index enforcing (FORMULA_ID, LANGUAGE).
• FM_FORM_MST — The runtime view/ synonym typically exposing base and translation together.
• FM_FORM_MST_TL and its audit columns interact with FND_LANGUAGES for valid LANGUAGE values.
• Formula-material and formula-routing children such as the formula detail tables also key on FORMULA_ID, tying translated headers to line-level data.

Because the table is classified as standalone with no external FK dependencies beyond its parent, its relationships are governed through FORMULA_ID and LANGUAGE rather than through additional referential constraints.