Search Results pay_upgrade_definitions_tl




Overview
Explore in AI Chat

Overview

PAY_UPGRADE_DEFINITIONS_TL is the translation (language) table that stores the multilingual display text for the base table PAY_UPGRADE_DEFINITIONS in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It resides in the HR schema and belongs to the PAY (Payroll) product family. The "_TL" suffix identifies it as a translated table: the base row carries non-translatable attributes and the surrogate key, while one or more _TL rows carry the language-dependent NAME and DESCRIPTION values for each supported language installed at the site.

In EBS, upgrade definitions describe the payroll-related upgrade or transformation setup used when migrating data, definitions, or configuration between releases or legislative versions. The _TL table simply makes the human-readable label and narrative of those upgrade definitions available to the user interface and reports in each enabled language.

The ETRM metadata classifies this object heuristically as standalone within the Data Vault model, with no foreign-key-derived link relationships mined from the FK structure. As a modeling suggestion, this reflects the typical _TL pattern: the table behaves as a satellite-like, language-qualified companion to the base upgrade-definition entity, distinguished by the LANGUAGE discriminator rather than by a separate hub or link. All validation and referential integrity to the base table are maintained at the base-table level and through the EBS language views, not through declared foreign keys on this table.

Key Information Stored

The documented physical schema for 12.2.2 lists twelve columns; the significant ones for querying and interpretation are:

  • UPGRADE_DEFINITION_ID — the surrogate identifier shared with the base table PAY_UPGRADE_DEFINITIONS; it links each translated row to its parent definition.
  • LANGUAGE — the language code for the translated text on the row; combined with UPGRADE_DEFINITION_ID it forms the primary key column list of PAY_UPGRADE_DEFINITIONS_TL_PK.
  • SOURCE_LANG — the language from which the translated text was derived, used by the translation-maintenance process.
  • NAME — the translated short name of the upgrade definition, shown in lists and lookups.
  • DESCRIPTION — the translated longer description of the upgrade definition.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — standard EBS WHO-column audit attributes recording who created and last changed each translated row and when.
  • ADDITIONAL_INFO — supplementary translated context associated with the definition.
  • ZD_EDITION_NAME — the editioning column present in 12.2.x; the documented unique index PAY_UPGRADE_DEFINITIONS_TL_PK is defined as (UPGRADE_DEFINITION_ID, LANGUAGE, ZD_EDITION_NAME), which serves as the business-key candidate and accommodates Edition-Based Redefinition.

The surrogate primary key is thus UPGRADE_DEFINITION_ID plus LANGUAGE, while the wider unique index including ZD_EDITION_NAME reflects the 12.2 editioning model.

Common Use Cases and Queries

Typical use is retrieving translated labels for upgrade definitions in reports, interfaces, and the concurrent-program or setup UIs, restricted to the session language or the base language.

  • Fetch the definition name and description in a specific language: 
    SELECT t.upgrade_definition_id, t.language, t.name, t.description
FROM   hr.pay_upgrade_definitions_tl t
WHERE  t.language = USERENV('LANG')
AND    t.upgrade_definition_id = :p_id;
  • List all translations available for a definition: 
    SELECT language, name, description
FROM   hr.pay_upgrade_definitions_tl
WHERE  upgrade_definition_id = :p_id
ORDER  BY language;
  • Find definitions with no row in the session language (fallback detection) by outer-joining to the translation table and filtering on NULL.
  • Reporting on translation completeness by comparing translation row counts against active languages in FND_LANGUAGES.

Because the _TL table only carries text, it should normally be joined to PAY_UPGRADE_DEFINITIONS on UPGRADE_DEFINITION_ID to obtain the full business record.

Related Objects

  • PAY_UPGRADE_DEFINITIONS — base table; join on UPGRADE_DEFINITION_ID to obtain non-translatable attributes.
  • PAY_UPGRADE_DEFINITIONS_TL_PK — the primary-key unique index enforcing uniqueness of UPGRADE_DEFINITION_ID, LANGUAGE (and ZD_EDITION_NAME).
  • FND_LANGUAGES — language definitions referenced implicitly by the LANGUAGE column.
  • HR_ALL_ORGANIZATION_UNITS / FND_SESSION_LANGUAGES — used to resolve the session or base language for translated lookups.
  • _TL language views — EBS exposes a language view over the base and _TL tables so consumers query a single logical entity.
  • PAY_UPGRADE_* setup and validation tables that reference UPGRADE_DEFINITION_ID as the parent key.