Search Results fnd_lookup_types_tl_u2

Overview

FND_LOOKUP_TYPES_TL is the translation table for Oracle E-Business Suite lookup types. It resides in the APPLSYS schema and is owned by the FND product (FND Design Data: FND.FND_LOOKUP_TYPES_TL). Its role is to store the language-dependent descriptive text — the user-facing meaning and description — associated with each lookup type defined in the base table FND_LOOKUP_TYPES. In a multilingual EBS deployment, the base table holds the structural identity of a lookup type, while this TL table holds the translated narrative text that end users see in the Oracle Forms interface and in concurrent program parameters.

Each row corresponds to one lookup type in one language, scoped by security group and by the application identifier of the view through which the lookup type is exposed. The object is stored in the APPS_TS_SEED tablespace, reflecting its classification as seed data that is shipped with the application and maintained by Oracle or by the implementing organization. Translation is governed by the SOURCE_LANG mechanism: a row whose LANGUAGE differs from SOURCE_LANG will mirror subsequent edits made to the source-language row until a translation is explicitly entered.

From a Data Vault modeling perspective, the metadata heuristic classifies this object as standalone, meaning no foreign keys were mined from its structure. The natural modeling suggestion would therefore be a hub for lookup type identity combined with a satellite carrying the descriptive attributes and language. However, the absence of enforced RI means this classification should be treated as a guide rather than an authoritative modeling decision.

Key Information Stored

The business identity of a row is composed of the primary key FND_LOOKUP_TYPES_TL_PK (LOOKUP_TYPE, SECURITY_GROUP_ID, VIEW_APPLICATION_ID, LANGUAGE). The most important columns are:

• LOOKUP_TYPE — VARCHAR2(30), the quick code lookup type name; functionally a foreign key to FND_LOOKUP_TYPES.LOOKUP_TYPE.
• SECURITY_GROUP_ID — NUMBER(15), the security group under which the lookup type is visible.
• VIEW_APPLICATION_ID — NUMBER(15), identifies the application exposing the lookup type through its _LOOKUPS view.
• LANGUAGE — the language of the row's translated text.
• SOURCE_LANG — the language the text mirrors when no translation exists.
• MEANING — VARCHAR2(80), the user-facing name of the lookup type.
• DESCRIPTION — VARCHAR2(240), descriptive text for the lookup type.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.

Two unique indexes act as business-key candidates. FND_LOOKUP_TYPES_TL_U1 (LOOKUP_TYPE, VIEW_APPLICATION_ID, SECURITY_GROUP_ID, LANGUAGE, ZD_EDITION_NAME) enforces language-level uniqueness per lookup type. FND_LOOKUP_TYPES_TL_U2 (MEANING, VIEW_APPLICATION_ID, SECURITY_GROUP_ID, LANGUAGE, ZD_EDITION_NAME) — the index referenced by the search term fnd_lookup_types_tl_u2 — enforces uniqueness on the translated meaning within a language, preventing two lookup types in the same view and security group from presenting an identical translated name.

Common Use Cases and Queries

Typical reporting and diagnostic scenarios include extracting all translations of a given lookup type, locating the source-language row, verifying uniqueness of translated meanings, and auditing recent changes to seeded text.

• Retrieve all languages for one lookup type:
  SELECT language, meaning, description FROM apps.fnd_lookup_types_tl WHERE lookup_type = :p_type AND view_application_id = :p_app ORDER BY language;
• Identify untranslated rows where the translation simply mirrors the source:
  SELECT lookup_type, meaning FROM apps.fnd_lookup_types_tl WHERE language <> source_lang AND meaning = (SELECT meaning FROM apps.fnd_lookup_types_tl b WHERE b.lookup_type = t.lookup_type AND b.language = t.source_lang ...);
• Audit changes during a migration window:
  SELECT lookup_type, language, last_updated_by, last_update_date FROM apps.fnd_lookup_types_tl WHERE last_update_date >= :p_start;
• Detect potential U2 conflicts by grouping on meaning, view_application_id, security_group_id and language.

Because the table is seed data, query results are commonly used to build translation work lists prior to loading through the Application Object Library maintenance forms or the FNDLOAD utility.

Related Objects

• FND_LOOKUP_TYPES — the base table; joined on LOOKUP_TYPE.
• FND_LOOKUP_VALUES_TL — the parallel translation table for lookup values, sharing the same language and source-language semantics.
• FND_LOOKUP_TYPES_TL (synonym under APPS) — the application-facing synonym by which the table is normally queried.
• FND_LOOKUP_TYPES_VL — the MLS view that joins the base table with this TL table to present translated rows.
• FND_LOOKUPS — the maintained view exposing combined lookup type and value information.
• FND_LOOKUP_TYPES_TL_U1 / _U2 — the unique indexes whose constraints govern valid data entry.
• FNDLOAD — the loader utility that extracts and uploads lookup type translations between environments.