Search Results fnd_flex_hierarchies_tl_pk

Overview

The FND_FLEX_HIERARCHIES_TL table is a core Application Object Library (FND) table within Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It functions as a translation table, storing language-specific descriptive text for flexfield hierarchies defined in the base table, FND_FLEX_HIERARCHIES. These hierarchies are used to organize and relate values within key flexfields, such as the Accounting Flexfield or Item Categories, into parent-child relationships for reporting and data validation. The primary role of this table is to support the multilingual capabilities of EBS by enabling the display of hierarchy names and descriptions in a user's preferred language, as defined by the application's NLS (National Language Support) settings.

Key Information Stored

The table's structure is designed to link translated text to a specific hierarchy within a specific value set. Its primary key (FND_FLEX_HIERARCHIES_TL_PK) uniquely identifies a record through the combination of FLEX_VALUE_SET_ID, HIERARCHY_ID, and LANGUAGE. A unique key (FND_FLEX_HIERARCHIES_TL_UK1) also enforces uniqueness on the hierarchy name per language and value set. The most critical columns include HIERARCHY_ID (linking to the base table), FLEX_VALUE_SET_ID (identifying the associated value set), and LANGUAGE (the language code, e.g., 'US' for American English). The table holds the translated HIERARCHY_NAME and DESCRIPTION for the corresponding hierarchy. The CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN columns provide standard EBS audit trail information.

Common Use Cases and Queries

This table is primarily accessed by the application's flexfield engine when rendering hierarchy information in a user session. Common technical and reporting use cases involve retrieving translated hierarchy details for a given value set or troubleshooting missing translations. A typical query to fetch all Spanish translations for hierarchies in a specific Accounting Flexfield value set would join to the base table.

• Sample Query: SELECT tl.hierarchy_name, tl.description, h.hierarchy_code FROM apps.fnd_flex_hierarchies_tl tl, apps.fnd_flex_hierarchies h WHERE tl.flex_value_set_id = :value_set_id AND tl.language = 'ES' AND tl.hierarchy_id = h.hierarchy_id AND tl.flex_value_set_id = h.flex_value_set_id;
• Reporting: Reports that list flexfield hierarchies for documentation or setup validation often query this table to present user-friendly names in the correct locale.
• Data Fix: Scripts to correct or populate missing translations for a newly added language will target this table, ensuring the HIERARCHY_NAME is unique within the FLEX_VALUE_SET_ID and LANGUAGE combination.

Related Objects

FND_FLEX_HIERARCHIES_TL has a direct and essential relationship with its base table, FND_FLEX_HIERARCHIES, via a foreign key constraint on the columns FLEX_VALUE_SET_ID and HIERARCHY_ID. This enforces referential integrity, ensuring every translation record corresponds to a valid base hierarchy. The table is also intrinsically linked to the FND_FLEX_VALUE_SETS table, as the FLEX_VALUE_SET_ID column references a specific flexfield value set. While not explicitly listed in the provided metadata, other related objects include the FND_FLEX_HIERARCHIES_B table (if the base table uses a _B/_TL architecture) and public APIs such as FND_FLEX_HIERARCHY_API, which developers should use for programmatic creation or modification of hierarchy data instead of direct DML on this table.