Search Results pay_element_types_f_tl_pk




Overview
Explore in AI Chat

Overview

PAY_ELEMENT_TYPES_F_TL is the translation (TL) table for the PAY_ELEMENT_TYPES_F base table in the Oracle E-Business Suite Payroll (PAY) module, owned by the HR schema. It stores the language-specific, translated text attributes of element definitions — the earning, deduction, and informational components that drive payroll processing. Because elements are user-facing concepts appearing on payslips, reports, and self-service pages, their names and descriptions must be presented in the session language of each user. The base table holds the primary, source-language definition, while PAY_ELEMENT_TYPES_F_TL carries one row per element per installed language, enabling multilingual payroll deployments.

The object carries the Data Vault classification of standalone (heuristic, mined from the foreign-key structure). From a modeling perspective, this suggests it behaves as a reference/lookup construct rather than a hub, link, or satellite in the classical Data Vault sense — the row identity is the composite of the element and its language, with no independent business key of its own. It is therefore best treated as a dependent translation attribute set keyed to the parent element definition.

Key Information Stored

The primary key PAY_ELEMENT_TYPES_F_TL_PK is composed of ELEMENT_TYPE_ID and LANGUAGE, with the documented unique index in 12.2.2 additionally including ZD_EDITION_NAME (the editioning column introduced by Online Patching). The most significant columns are:

  • ELEMENT_TYPE_ID — surrogate identifier of the parent element definition; joins back to PAY_ELEMENT_TYPES_F. Part of the primary key.
  • LANGUAGE — the language code of the translated row; the second component of the primary key.
  • ELEMENT_NAME — the translated name of the element as displayed to users in reports, payslips, and forms.
  • REPORTING_NAME — the translated name used specifically on statutory and management reporting outputs.
  • DESCRIPTION — the translated long description explaining the element's purpose.
  • SOURCE_LANG — the language from which the translation was derived, indicating the source of the base text.
  • ZD_EDITION_NAME — editioning discriminator supporting Online Patching in 12.2.2.

The audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE) mirror the standard EBS WHO columns and record row provenance. Note that the surrogate primary key here is the composite (ELEMENT_TYPE_ID, LANGUAGE) rather than a standalone generated sequence value; the parent business entity is the element itself.

Common Use Cases and Queries

The predominant use is joining the translation table to its base table to retrieve language-specific element labels. A typical reporting query selects the user's language row:

  • Localized element listing: SELECT b.ELEMENT_TYPE_ID, t.ELEMENT_NAME, t.DESCRIPTION FROM PAY_ELEMENT_TYPES_F b, PAY_ELEMENT_TYPES_F_TL t WHERE b.ELEMENT_TYPE_ID = t.ELEMENT_TYPE_ID AND t.LANGUAGE = USERENV('LANG')
  • Payslip and reporting labels: retrieve REPORTING_NAME for elements included in a payroll run for statutory output.
  • Translation completeness audit: compare row counts and non-null values per LANGUAGE against the base table to detect missing or stale translations.
  • Multilingual reconciliation: self-join on ELEMENT_TYPE_ID across two LANGUAGE values to compare terminology between locales.

Because the table is read-heavy and updated only when element text changes, it is safe for high-volume reporting joins, though use of the primary key columns in predicates is recommended.

Related Objects

  • PAY_ELEMENT_TYPES_F — the base (non-translated) element definition table; joined on ELEMENT_TYPE_ID.
  • PAY_ELEMENT_TYPES_VL — the translated view typically layered over the _F and _TL pair for simplified access.
  • PAY_ELEMENT_TYPES_TL — the corresponding translation-table synonym/pairing companion in the TL family.
  • PAY_ELEMENT_CLASSIFICATIONS — categorizes elements, keyed by ELEMENT_TYPE_ID.
  • PAY_ELEMENT_LINKS_F — associates elements with input values, referencing the same ELEMENT_TYPE_ID.
  • PAY_RUN_RESULTS — payroll processing results referencing element types for reporting labels.
  • PAY_BALANCES — balance definitions linked to elements for accumulation and reporting.
  • FND_LANGUAGES — defines the valid LANGUAGE codes present in the translation table.