Search Results ams_hierarchies_all_tl_pk

Overview

The AMS_HIERARCHIES_ALL_TL table is a core data object within the Oracle E-Business Suite Marketing (AMS) module. It functions as a translation table, specifically designed to store multilingual content for descriptive attributes related to marketing hierarchies. Its primary role is to support the internationalization of the application by holding translated versions of user-facing text, such as hierarchy names and descriptions, for the base data stored in its parent table, AMS_HIERARCHIES_ALL_B. This separation of translatable columns from the base transactional data is a standard architectural pattern in Oracle EBS, enabling a single installation to support multiple languages concurrently.

Key Information Stored

This table stores the language-specific translations for columns that require localization. The structure is defined by a composite primary key that uniquely identifies each translated record. The key columns are HIERARCHY_ID, which links back to the base hierarchy record, and LANGUAGE, which identifies the translation language code (e.g., 'US' for American English, 'KO' for Korean). The most critical translated columns typically include HIERARCHY_NAME and DESCRIPTION, which hold the user-readable name and explanatory text for a marketing hierarchy in a given language. Other potential translatable attributes, such as SOURCE_LANG, are also stored to manage the translation process. The table's existence is crucial for generating locale-appropriate reports, user interfaces, and campaign definitions.

Common Use Cases and Queries

The primary use case is retrieving hierarchy information in a user's session language for display within forms, reports, and list of values (LOVs). A standard query involves joining this table with its base table while filtering on the desired language. For example, to fetch all active hierarchy names in the current session language for reporting purposes, a developer might use:

• SELECT b.HIERARCHY_ID, tl.HIERARCHY_NAME, tl.DESCRIPTION
• FROM AMS_HIERARCHIES_ALL_B b,
• AMS_HIERARCHIES_ALL_TL tl
• WHERE b.HIERARCHY_ID = tl.HIERARCHY_ID
• AND tl.LANGUAGE = USERENV('LANG')
• AND b.ENABLED_FLAG = 'Y';

Another common scenario is data migration or setup, where translated seed data for standard hierarchies must be populated into this table for each supported language following the insertion of base records into AMS_HIERARCHIES_ALL_B.

Related Objects

The table has a direct and essential relationship with its base table, as defined by the provided metadata. The relationship is a foreign key dependency where AMS_HIERARCHIES_ALL_TL.HIERARCHY_ID references AMS_HIERARCHIES_ALL_B. This enforces referential integrity, ensuring every translation record corresponds to a valid base hierarchy. The primary key for this table is AMS_HIERARCHIES_ALL_TL_PK on the columns (HIERARCHY_ID, LANGUAGE). In application logic, this table is primarily accessed through Oracle's standard multilingual views (often prefixed or suffixed with '_TL' or '_VL'), which automatically handle language filtering, rather than through direct SQL against the base table.