Search Results pay_element_sets_tl_pk

Overview

PAY_ELEMENT_SETS_TL is the translation table for element sets within the Oracle Payroll (PAY) module. An element set is a named grouping of payroll elements — such as earnings, deductions, or balance-affecting items — that is used to drive eligibility for a payroll run, a balance calculation, a report, or a legislative process. Because element sets carry user-facing descriptive text, Oracle E-Business Suite stores that text in a dedicated translation (TL) table so the same element set definition can be presented in multiple languages. PAY_ELEMENT_SETS_TL holds the language-specific ELEMENT_SET_NAME for each element set row defined in the base transactional table.

The object resides in the HR schema and is documented as VALID in ETRM for release 12.2.2, with corresponding availability in 12.1.1. Its heuristic Data Vault classification, mined from the foreign-key structure, is standalone; this suggests that, for analytical or warehouse modeling, the table is best treated as a descriptive satellite attached to the element set entity rather than as an independent hub or a link between two business entities. In practice it functions as a language-dependent attribute store keyed to the element set identifier and language.

Key Information Stored

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

• ELEMENT_SET_ID — surrogate identifier of the parent element set; the principal join column to the base element set table.
• LANGUAGE — the language code for the translated name, forming part of the composite key.
• SOURCE_LANG — the language from which the translation originates, supporting the standard EBS TL pattern.
• ELEMENT_SET_NAME — the translated display name of the element set, the primary business-facing attribute of this table.
• ZD_EDITION_NAME — the editioning column used by EBS online patching (12.2.x) to identify the active edition of the row.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE — which record who created and last modified each translation row and when.

The documented primary key is PAY_ELEMENT_SETS_TL_PK on (ELEMENT_SET_ID, LANGUAGE). A separate unique index, PAY_ELEMENT_SETS_TL_PK (also referred to as a business-key candidate), extends the key to (ELEMENT_SET_ID, LANGUAGE, ZD_EDITION_NAME), reflecting the editioning requirement of the 12.2.2 schema. The surrogate key is therefore ELEMENT_SET_ID combined with LANGUAGE; the business-key candidate adds the edition discriminator to guarantee uniqueness across editions.

Common Use Cases and Queries

This table is most often joined to PAY_ELEMENT_SETS_F (or its translated base) to present element set names in the session language. A typical query retrieves the localized name for each element set:

• Reporting on element set membership for payroll runs, filtered by ELEMENT_SET_NAME.
• Joining to base element set and element set–element mapping tables to resolve names for a given ELEMENT_SET_ID.
• Filtering by LANGUAGE (or USERENV('LANG')) to return only the active language row.
• Excluding superseded editions by filtering ZD_EDITION_NAME = 'SET1' in 12.2.x environments.
• Auditing recent translation changes via LAST_UPDATE_DATE and LAST_UPDATED_BY.

A representative join pattern is: SELECT f.element_set_id, t.element_set_name FROM pay_element_sets_f f, pay_element_sets_tl t WHERE f.element_set_id = t.element_set_id AND t.language = USERENV('LANG');

Related Objects

The table links primarily to element set definition and membership objects:

• PAY_ELEMENT_SETS_F — base element set definition, joined on ELEMENT_SET_ID.
• PAY_ELEMENT_SETS — the underlying element set entity view/table.
• PAY_ELEMENT_SET_ELEMENTS — maps elements into a set, joined on ELEMENT_SET_ID.
• PAY_ELEMENT_TYPES_F / PAY_ELEMENT_TYPES_TL — the elements themselves, referenced through the membership table.
• FND_LANGUAGES — validates the LANGUAGE values used here.

These relationships are consistent with the standalone classification: the table depends on the element set entity but does not by itself establish a link between two independent business entities.