Search Results fa_lookups_tl_u1

Overview

FA.FA_LOOKUPS_TL is the translation (TL) table for the Oracle Assets lookup repository. It holds the language‑specific, translatable columns associated with the base table FA_LOOKUPS_B, storing the MEANING and DESCRIPTION text for each QuickCode value in whatever languages have been installed and enabled in the EBS instance. The object resides in the FA schema, is registered under FND Design Data as OFA.FA_LOOKUPS_TL, and is defined with a status of VALID in both Oracle EBS 12.1.1 and 12.2.2. Physically it is stored in the APPS_TS_SEED tablespace with a PCTFREE of 10, consistent with a low‑volume, seed‑data lookup table that is populated at installation and only occasionally extended by configuration.

FA_LOOKUPS_B and FA_LOOKUPS_TL together form the Oracle Assets implementation of the Oracle Multilingual Standards pattern: a base table carrying language‑independent attributes and a TL table carrying one row per language per lookup entry. From a data‑vault modeling perspective, the metadata classifies this object as standalone (no foreign key relationships are documented for it). A reasonable modeling suggestion would be to treat FA_LOOKUPS_B as the reference or "hub" of the lookup, and FA_LOOKUPS_TL as a language‑scoped descriptive satellite, since it carries no independent business identity of its own.

Key Information Stored

The table is composed of twelve documented columns. The most significant are:

• LOOKUP_TYPE (VARCHAR2 30) — identifies the list of QuickCode values to which the row belongs, i.e., the lookup category.
• LOOKUP_CODE (VARCHAR2 30) — identifies the specific QuickCode value within that type.
• LANGUAGE — the defined language of the row's translated columns; every value in FA_LOOKUPS_B physically requires one TL row per installed language.
• SOURCE_LANG — the actual language the translated columns were originally entered in; a value differing from LANGUAGE indicates a row that is the "source" for that translation.
• MEANING (VARCHAR2 80) — the translatable, user‑facing meaning of the lookup code.
• DESCRIPTION (VARCHAR2 80) — the translatable detailed description of the lookup code.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard Who columns maintained by EBS for auditing and concurrency.
• ZD_EDITION_NAME (VARCHAR2 30) — the edition‑tracking column used by the EBS online patching / editioning model introduced in 12.2.

The documented primary key is FA_LOOKUPS_TL_PK on (LOOKUP_TYPE, LOOKUP_CODE, LANGUAGE). The unique index FA_LOOKUPS_TL_U1, which the user searched for, covers (LOOKUP_TYPE, LOOKUP_CODE, LANGUAGE, ZD_EDITION_NAME) in the APPS_TS_SEED tablespace. The difference between the two is deliberate: the PK enforces uniqueness of a lookup per language, while U1 additionally incorporates the edition name so that the 12.2 Online Patching mechanism can hold pre‑ and post‑patch editions side by side without violating the primary key.

Common Use Cases and Queries

The most common reporting requirement is to resolve a stored lookup code into its display meaning for a specific language. A typical query joins FA_LOOKUPS_B (for the language‑independent attributes) to FA_LOOKUPS_TL and filters on LANGUAGE:

SELECT b.lookup_type, b.lookup_code, t.meaning, t.description
FROM fa.fa_lookups_b b, fa.fa_lookups_tl t
WHERE b.lookup_type = t.lookup_type
AND b.lookup_code = t.lookup_code
AND t.language = USERENV('LANG')
AND b.lookup_type = :p_type;

Other uses include validating that every installed language has a translation row (identifying gaps with a NOT EXISTS against fnd_languages), auditing customised QuickCode values in an implementation, and diagnosing duplicate‑key errors that surface against FA_LOOKUPS_TL_U1 during patching. Administrators also use the table to script bulk updates of MEANING and DESCRIPTION across languages when terminology is standardised across an enterprise.

Related Objects

• FA.FA_LOOKUPS_B — the base table; joins on LOOKUP_TYPE and LOOKUP_CODE. Holds the language‑independent attributes and the canonical definition of each lookup.
• FA_LOOKUPS_TL# — the internal edition view used by Online Patching in 12.2; documented as referencing FA_LOOKUPS_TL.
• FND_LANGUAGES — the EBS language registry; joins on LANGUAGE to list only installed and active translations.
• FND_LOOKUP_TYPES / FND_LOOKUP_VALUES — the analogous application‑level lookup tables maintained by the FND schema; many Oracle Assets lookups are surfaced through FND views even when seeded into FA_LOOKUPS.
• FA_LOOKUPS_VL — the view that presents base and translated columns together for a session's language, and is the object most Reports and OA Framework pages query instead of the TL table directly.

Because the metadata documents no inbound or outbound foreign keys, FA_LOOKUPS_TL should be treated as a reference data object rather than a transactional entity; queries against it are predominantly read‑only lookups resolved by LOOKUP_TYPE, LOOKUP_CODE and LANGUAGE.