Search Results pay_element_types_x




Overview
Explore in AI Chat

Overview

PAY_ELEMENT_TYPES_X is a date-effective (X) view owned by the APPS schema within the Payroll (PAY) product module of Oracle E-Business Suite. It exposes element type definitions — the reusable building blocks that determine how an element processes, what inputs and outputs it accepts, which currency it uses, and how entries are generated during a payroll run. Because it is a "date-effective" view, it presents rows as of the effective date range defined by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, allowing the same logical element type to have multiple historically versioned definitions.

The view is a critical data source for payroll reporting, integrations, interfaces, and extensions that need to read element type metadata (rather than the current-only, singular view). It is widely referenced in payroll extracts, element migration scripts, and statutory reporting where the historical effective definition of an element must be resolved.

Underlying Base Objects

The ETRM 12.2.2 metadata documents that PAY_ELEMENT_TYPES_X is defined over two base objects through synonyms:

  • PAY_ELEMENT_TYPES_F (SYNONYM) — the date-effective (F) table holding element type attribute rows including effective dates, flags, currencies, and DFF/legislation attributes.
  • PAY_ELEMENT_TYPES_F_TL (SYNONYM) — the translation (TL) table providing language-specific element name, reporting name, and description.

The view text joins these on ELEMENT_TYPE_ID and effective date boundaries, exposing the denormalized result (PET.* plus PETTL.ELEMENT_NAME, PETTL.DESCRIPTION, PETTL.REPORTING_NAME). The relationship is therefore a straightforward one-to-many correspondence between the effective definition rows and their translated names, filtered so that callers see a coherent, date-effective view akin to Oracle's standard _X view pattern (comparable to HR/Payroll _F/_X pairs).

Key Columns

Common Use Cases and Queries

Typical scenarios include resolving currently active element types for a business group, driving payroll interfaces, generating element catalogs, and validating migration scripts.

A query returning currently effective element types for a business group:

  • SELECT element_type_id, element_name, classification_id, input_currency_code FROM apps.pay_element_types_x WHERE business_group_id = :p_bg AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

A query listing element behavior flags for reconciliation to a prior effective date:

  • SELECT element_type_id, element_name, process_in_run_flag, multiply_value_flag FROM apps.pay_element_types_x WHERE legislation_code = :p_leg AND :p_eff_date BETWEEN effective_start_date AND effective_end_date ORDER BY processing_priority;

A query joining to a payroll run or element entries to enrich reporting with translated names:

  • SELECT x.element_name, x.reporting_name, x.legislation_subgroup FROM apps.pay_element_types_x x, apps.pay_element_entries_f e WHERE e.element_type_id = x.element_type_id AND SYSDATE BETWEEN x.effective_start_date AND x.effective_end_date;

Because the view is date-effective, all queries should constrain on effective dates to avoid returning multiple historical versions of the same ELEMENT_TYPE_ID.