Search Results pay_element_types_tl

Overview

HR.PAY_ELEMENT_TYPES_F_TL is the translation (TL) table for Oracle Payroll element types in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the language-specific, user-facing text associated with the base element type defined in HR.PAY_ELEMENT_TYPES_F, including the element's display name, reporting name, and description. Each physical row represents one language rendition of one element type, keyed by ELEMENT_TYPE_ID and a LANGUAGE code. The object is owned by the HR schema and is registered in FND Design Data as PAY.PAY_ELEMENT_TYPES_F_TL, with a status of VALID. Its role is strictly descriptive: it holds translated labels used throughout payroll setup, element entry, and reporting, while the operational definition of the element (classification, processing rules, links to input values) remains in the _F base table.

From a Data Vault modeling perspective, the heuristic classification supplied in the metadata is standalone. Following dimensional or Data Vault conventions, this table is best treated as a satellite attached to the PAY_ELEMENT_TYPES_F hub, since it carries descriptive, time-stamped attributes (who columns plus text) keyed by the parent element type identifier rather than participating in independent business relationships. It should not be modeled as its own hub or link.

Key Information Stored

• ELEMENT_TYPE_ID — Numeric, system-generated primary key inherited from PAY_ELEMENT_TYPES_F. It identifies the parent element type and forms the leading column of the composite primary key.
• LANGUAGE — The language code of the translation. Together with ELEMENT_TYPE_ID, it is the business-key candidate that enforces uniqueness per language.
• ELEMENT_NAME — The translated long user name for the element type, up to 80 characters. This is the label users see when selecting elements.
• REPORTING_NAME — A translated short name used in statutory and management reporting where space is constrained.
• DESCRIPTION — A translated description of up to 240 characters explaining the element's purpose.
• SOURCE_LANG — The source language from which the translation was derived, supporting the standard EBS translation model.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Who columns capturing the last modification event and the responsible user or login.
• CREATED_BY, CREATION_DATE — Standard Who columns capturing row creation audit information.
• ZD_EDITION_NAME — The edition identifier used by EBS online patching (Edition-Based Redefinition) in 12.2.x; it is part of the unique index PAY_ELEMENT_TYPES_F_TL_PK.

The documented primary key is PAY_ELEMENT_TYPES_F_TL_PK over (ELEMENT_TYPE_ID, LANGUAGE), extended in 12.2.2 to include ZD_EDITION_NAME. A non-unique secondary index, PAY_ELEMENT_TYPES_F_TL_N2, covers (LANGUAGE, ELEMENT_NAME) to support name-based lookups within a language. The surrogate identifier remains ELEMENT_TYPE_ID; LANGUAGE is the discriminator that makes the business key unique.

Common Use Cases and Queries

Typical usage centers on retrieving localized element labels for concurrent programs, BI Publisher reports, and custom OAF or Forms extensions. A common pattern joins the translation table to its base table to render elements in the session language. For example:

• Listing all element types with their translated names for a given language:

SELECT t.ELEMENT_TYPE_ID, t.ELEMENT_NAME, t.REPORTING_NAME
FROM   HR.PAY_ELEMENT_TYPES_F_TL t
WHERE  t.LANGUAGE = USERENV('LANG');

• Resolving a specific element's display text for reporting:

SELECT f.ELEMENT_TYPE_NAME, t.REPORTING_NAME
FROM   HR.PAY_ELEMENT_TYPES_F_TL t,
       HR.PAY_ELEMENT_TYPES_F    f
WHERE  t.ELEMENT_TYPE_ID = f.ELEMENT_TYPE_ID
AND    t.LANGUAGE = 'US';

• Auditing recently changed translations using the Who columns (LAST_UPDATE_DATE, LAST_UPDATED_BY).
• Validating translation completeness by comparing row counts per LANGUAGE against the base table.

Because the object is flagged standalone and does not itself reference other database objects, queries should always drive localization through the LANGUAGE column and join back to PAY_ELEMENT_TYPES_F for operational attributes.

Related Objects

• HR.PAY_ELEMENT_TYPES_F — The base table holding element definitions; joined on ELEMENT_TYPE_ID and the parent of the translated text.
• HR.PAY_ELEMENT_TYPES_F_TL_PK — The unique index enforcing (ELEMENT_TYPE_ID, LANGUAGE, ZD_EDITION_NAME).
• HR.PAY_ELEMENT_TYPES_F_TL_N2 — The non-unique index on (LANGUAGE, ELEMENT_NAME) used for name searches.
• HR.PAY_ELEMENT_TYPES_F_VL / _VL — The translated view variants exposed to forms and reports, which read from this table.
• HR.PAY_INPUT_VALUES_F — Element input value definitions that reference element types and whose labels are resolved alongside translation data.
• HR.PAY_ELEMENT_LINKS_F — Element link records tied to element types, commonly joined when presenting linked elements in a chosen language.
• FND_USER — Referenced through LAST_UPDATED_BY and CREATED_BY Who columns.

These relationships make PAY_ELEMENT_TYPES_F_TL the authoritative source for localized element terminology across payroll configuration, element entry, and downstream reporting in Oracle EBS.