Search Results pay_element_types




Overview
Explore in AI Chat

Overview

The PAY_ELEMENT_TYPES view is a date-effective reporting object owned by the APPS schema within the Payroll (PAY) product module of Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented status is VALID, and its stated description is simply "Date-effective view." Element types represent the reusable definitions of payroll earnings, deductions, and other pay components that drive payroll processing, costing, and legislative reporting. The view exposes these definitions through a single query surface that resolves date-effective (DATETRACK) and translatable (TL) data across multiple underlying synonyms, allowing developers, report authors, and integrators to read the current or historic definition of any element type using familiar APPS naming conventions rather than joining the _F and _F_TL tables directly. Because it is a view rather than a base table, it is commonly used in custom BI Publisher reports, Oracle Discoverer workbooks, OAF pages, and interface extracts that need element metadata without performing date-effective joins manually.

Underlying Base Objects

Per the ETRM metadata for 12.2.2, the view references three base objects, each exposed to APPS as a synonym: FND_SESSIONS, PAY_ELEMENT_TYPES_F, and PAY_ELEMENT_TYPES_F_TL. In the view text, the alias PETF refers to PAY_ELEMENT_TYPES_F — the date-effective (DATETRACK) table that stores the element type definition including business group, legislation, classification, formula, currency codes, processing flags, and the ATTRIBUTE1–20 and ELEMENT_INFORMATION1–N descriptive flexfield columns. The alias PETFTL refers to PAY_ELEMENT_TYPES_F_TL — the translatable (_TL) table that supplies the language-dependent attributes ELEMENT_NAME, DESCRIPTION, and REPORTING_NAME, plus a second reference to ELEMENT_NAME returned under the alias SYSTEM_NAME. This symmetric join produces both the user-maintained name and a system-referenced copy of the name in the same result row. FND_SESSIONS is the standard Oracle Applications session table used by date-effective views to resolve the Effective Date context and the current session language, yielding the correct row from the _F table and the matching language row from the _TL table.

Key Columns

The view exposes the primary key ELEMENT_TYPE_ID together with EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, which together satisfy date-effective queries. Contextual columns include BUSINESS_GROUP_ID, LEGISLATION_CODE, LEGISLATION_SUBGROUP, and CLASSIFICATION_ID (with a related BENEFIT_CLASSIFICATION_ID). Processing behaviour is controlled through FORMULA_ID, INPUT_CURRENCY_CODE, OUTPUT_CURRENCY_CODE, PROCESSING_TYPE, PROCESSING_PRIORITY, PROCESS_IN_RUN_FLAG, POST_TERMINATION_RULE, MULTIPLE_ENTRIES_ALLOWED_FLAG, MULTIPLY_VALUE_FLAG, STANDARD_LINK_FLAG, ADJUSTMENT_ONLY_FLAG, INDIRECT_ONLY_FLAG, ADDITIONAL_ENTRY_ALLOWED_FLAG, and CLOSED_FOR_ENTRY_FLAG. Qualifying eligibility is stored in QUALIFYING_AGE, QUALIFYING_LENGTH_OF_SERVICE, and QUALIFYING_UNITS. Display attributes ELEMENT_NAME and SYSTEM_NAME (both from PETFTL.ELEMENT_NAME), DESCRIPTION, and REPORTING_NAME provide the labeling, while COMMENT_ID links to the FND_DOCUMENTS comment repository. Standard WHO columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE) and the ATTRIBUTE and ELEMENT_INFORMATION flexfield segments complete the projection.

Common Use Cases and Queries

  • Listing active element types for a business group and legislation.
  • Feeding element metadata into a payroll reconciliation or costing report.
  • Identifying elements that permit manual entry or multiple entries during an assignment validation.
  • Extracting the reporting name for legislative or statutory reporting.

Typical query:

SELECT element_type_id, element_name, reporting_name,
       classification_id, processing_priority
FROM   apps.pay_element_types
WHERE  business_group_id = :p_bg_id
AND    legislation_code  = :p_leg_code;

For historic reporting, add AND TRUNC(:p_effective_date) BETWEEN effective_start_date AND effective_end_date. Because the view already resolves FND_SESSIONS, callers should set the effective date context (e.g., via FND_SESSION or the fnd_global APIs) so the _F and _TL rows align correctly.