Search Results fnd_lookup_types_tl




Overview
Explore in AI Chat

Overview

FND_LOOKUP_TYPES_TL is the translation table for FND_LOOKUP_TYPES in Oracle E-Business Suite, owned by the APPLSYS schema within the FND — Application Object Library product. Lookup types define the extensible code-value lists (lookups) that drive validation, list-of-values behavior, and reference data display throughout EBS. Because EBS supports multiple installed languages, user-facing attributes such as Meaning and Description cannot reside on the base table alone; they are externalized here, one row per language.

The base FND_LOOKUP_TYPES table stores language-independent lookup type definitions (lookup type name, application context, custom flag, and so on), while FND_LOOKUP_TYPES_TL carries the translated MEANING and DESCRIPTION text keyed by LANGUAGE. This separation is the standard EBS MLS (Multi-Lingual Support) pattern. From a heuristic Data Vault modeling perspective, the ETRM metadata classifies this object as standalone, with no documented foreign key relationships. In Data Vault terms, the table is best modeled as a satellite, whose parent hub key would be the lookup type identifier (LOOKUP_TYPE, VIEW_APPLICATION_ID, SECURITY_GROUP_ID), with LANGUAGE acting as the driving key for the descriptive payload. The base table and its _TL companion together represent the hub-satellite split.

Key Information Stored

The table contains 13 documented columns. The most significant are:

  • LOOKUP_TYPE — the internal name of the lookup type (for example, a value set or reference list identifier). Part of the primary key.
  • VIEW_APPLICATION_ID — the application that owns the lookup type; participates in both the primary key and the unique business keys.
  • SECURITY_GROUP_ID — the security group context, historically significant in earlier EBS releases and still present in the key structure.
  • LANGUAGE — the NLS language code for the translated row; the discriminator that makes this an MLS table. Part of the primary key.
  • MEANING — the translated, user-visible name of the lookup type, displayed to end users in the session language.
  • DESCRIPTION — the translated explanation of the lookup type's purpose.
  • SOURCE_LANG — the language in which the source text was originally authored, used by the translation framework.
  • ZD_EDITION_NAME — the edition-based redefinition column supporting online patching in 12.2.x; part of the unique indexes.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns recording who created or last modified the row and when.

Two unique indexes are documented as business-key candidates. FND_LOOKUP_TYPES_TL_U1 covers (LOOKUP_TYPE, VIEW_APPLICATION_ID, SECURITY_GROUP_ID, LANGUAGE, ZD_EDITION_NAME) and FND_LOOKUP_TYPES_TL_U2 covers (MEANING, VIEW_APPLICATION_ID, SECURITY_GROUP_ID, LANGUAGE, ZD_EDITION_NAME). The primary key FND_LOOKUP_TYPES_TL_PK is defined over (LOOKUP_TYPE, SECURITY_GROUP_ID, VIEW_APPLICATION_ID, LANGUAGE), as listed in the ETRM metadata.

Common Use Cases and Queries

Consultants and developers query this table whenever they need the display text for a lookup type rather than its internal code. Typical scenarios include building reports that show localized lookup definitions, validating that seeded lookups carry translations in all installed languages, and comparing translations between environments after a patch.

A standard query returning the visible name of a lookup type reads:

  • SELECT LOOKUP_TYPE, VIEW_APPLICATION_ID, MEANING, DESCRIPTION, LANGUAGE FROM FND_LOOKUP_TYPES_TL WHERE LOOKUP_TYPE = :p_type AND LANGUAGE = USERENV('LANG');

Reporting use cases include auditing seeded versus user-defined lookup types, identifying rows where MEANING is null for a given LANGUAGE (missing translation), and joining to the base table for a complete metadata inventory:

  • SELECT b.LOOKUP_TYPE, b.CUSTOM_FLAG, t.MEANING FROM FND_LOOKUP_TYPES b, FND_LOOKUP_TYPES_TL t WHERE b.LOOKUP_TYPE = t.LOOKUP_TYPE AND b.VIEW_APPLICATION_ID = t.VIEW_APPLICATION_ID AND t.LANGUAGE = USERENV('LANG');

Analysts also use the table to confirm translation coverage across the languages enabled at a site, counting distinct LANGUAGE values per lookup type.

Related Objects

The following objects are most significant when working with FND_LOOKUP_TYPES_TL:

  • FND_LOOKUP_TYPES — the base table; join on LOOKUP_TYPE and VIEW_APPLICATION_ID to combine language-independent definition attributes with translated text.
  • FND_LOOKUP_VALUES — the lookup values that belong to a lookup type; its translation companion FND_LOOKUP_VALUES_TL follows the same MLS pattern.
  • FNDLOOKUP_VALUES_VL / FND_LOOKUP_TYPES_VL — the MLS views that automatically join the base and _TL tables using the current session language, providing the preferred access path for forms and reports.
  • FND_LANGUAGES — the language registry that supplies the valid LANGUAGE codes used in this table.
  • FND_APPLICATION — the application registry referenced by VIEW_APPLICATION_ID.
  • FND_LOOKUP_TYPES_TL_PK and the U1/U2 unique indexes — enforce key integrity and business-key uniqueness, and are documented in the ETRM physical schema.

Because the ETRM relationship data classifies this object as standalone, no foreign keys are documented; joins are maintained logically through the shared key columns rather than enforced referential constraints. Any custom query or report should therefore respect the MLS join on LANGUAGE to avoid duplicate-language rows.