Search Results pay_element_class_tl_pk




Overview
Explore in AI Chat

Overview

The PAY_ELEMENT_CLASSIFICATIONS_TL table resides in the HR schema and is owned by the Oracle Payroll (PAY) product module. It stores translated (language-specific) definitions for element classifications used throughout the Oracle E-Business Suite payroll engine. In Oracle EBS, an element classification is a high-level grouping that determines how an element behaves during payroll processing — for example, whether it is an earnings, deduction, information, or balance-initialization type. Because Oracle EBS is a multi-language application, the base (untranslated) rows for these classifications are held in a corresponding non-TL table (PAY_ELEMENT_CLASSIFICATIONS), while the _TL table holds the translated text attributes such as classification name and description for each installed language.

Based on the heuristic Data Vault classification mined from the foreign-key structure, this object is modeled as a standalone table. In Data Vault terms, it behaves effectively as a satellite-like reference of descriptive text, decoupled from any parent link. This is a modeling suggestion rather than a physical constraint; the table can be treated independently when designing reporting or ETL pipelines.

Key Information Stored

The table is a translation table, so its primary key combines the business object identity with the language context. The documented unique index PAY_ELEMENT_CLASS_TL_PK covers CLASSIFICATION_ID, LANGUAGE, and ZD_EDITION_NAME. The most significant columns are:

  • CLASSIFICATION_ID — Surrogate/business key that identifies the element classification. Joins back to the base table and other payroll objects.
  • LANGUAGE — Language code (e.g., US, FR) that makes each row language-specific; part of the primary key.
  • CLASSIFICATION_NAME — The translated display name of the classification.
  • DESCRIPTION — The translated textual description of the classification.
  • SOURCE_LANG — The source language from which the row was derived (e.g., the base language US).
  • ZD_EDITION_NAME — Editioning column used by Oracle EBS 12.2's online patching (edition-based redefinition); part of the unique index.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns tracking who changed the row and when.
  • CREATED_BY, CREATION_DATE — Standard WHO columns recording row creation.

The CLASSIFICATION_ID is the surrogate identifier, while the combination of CLASSIFICATION_ID, LANGUAGE, and ZD_EDITION_NAME forms the business-key candidate enforced by the unique index.

Common Use Cases and Queries

Typical uses include building multilingual payroll reports and validating that a classification has a translation in the recipient's language.

  • Retrieving the translated name for a classification in a chosen language: 
    SELECT classification_id, classification_name, description
FROM   hr.pay_element_classifications_tl
WHERE  language = USERENV('LANG');
  • Joining to the base table to see both the code and its translation: 
    SELECT b.classification_id, t.language, t.classification_name
FROM   hr.pay_element_classifications b,
       hr.pay_element_classifications_tl t
WHERE  b.classification_id = t.classification_id
AND    t.language = 'US';
  • Auditing missing translations for a given language by comparing distinct CLASSIFICATION_ID values in the base table against those present in the TL table.
  • Incrementally extracting only changed rows using LAST_UPDATE_DATE for data warehouse loads.

Related Objects

The translation table depends on the base PAY_ELEMENT_CLASSIFICATIONS table through the shared CLASSIFICATION_ID. Classifications themselves are referenced by payroll elements, so significant related objects include PAY_ELEMENT_TYPES_F and PAY_ELEMENT_TYPES_TL (via element classification), PAY_ELEMENTS and its _TL companion, and the classification-to-use mapping tables used by the payroll run. The base PAY_ELEMENT_CLASSIFICATIONS table and its translated companion are queried together in most reporting scenarios. Views such as the Payroll element setup forms and the PAY_ELEMENT_CLASSIFICATIONS_VL style views (where defined) provide language-joined access, and the standard WHO audit columns link to FND_USER for user attribution.