Search Results pay_element_class_usages




Overview
Explore in AI Chat

Overview

PAY_ELEMENT_CLASS_USAGES is an APPS-owned reporting view within the Oracle Payroll (PAY) product family. It exposes the date-effective rows of the underlying element class usage entity, restricted to the row that is valid as of the current application session's effective date. In Oracle EBS 12.1.1 and 12.2.2, the view is defined in the APPS schema and is marked VALID. It is the standard read interface through which payroll configuration, reporting, and integration components resolve which element classifications are associated with a given payroll run type at a point in time.

Because payroll setup objects such as element classifications and run types are date-tracked, consumers must never read the _F (date-effective) table directly without applying an effective-date filter. PAY_ELEMENT_CLASS_USAGES encapsulates that filter logic by comparing each row's EFFECTIVE_START_DATE and EFFECTIVE_END_DATE against the FND_SESSIONS.EFFECTIVE_DATE for the current session. This makes it the preferred object for concurrent programs, Oracle Reports, OBIEE/Hyperion extracts, and inbound/outbound interfaces that must reflect the user's effective date context.

Underlying Base Objects

The view is defined over two documented referenced objects:

  • PAY_ELEMENT_CLASS_USAGES_F (SYNONYM) — the date-effective base table holding every logical version of an element class usage row, keyed by ELEMENT_CLASS_USAGE_ID and bounded by EFFECTIVE_START_DATE / EFFECTIVE_END_DATE.
  • FND_SESSIONS (SYNONYM) — the Applications session table, queried twice via correlated subqueries on SESSION_ID = USERENV('SESSIONID') to obtain the session's effective date.

The inner query (aliased ECU in the documented view text) joins these through two scalar subqueries: one requiring ECU.EFFECTIVE_START_DATE <= the session effective date, and one requiring ECU.EFFECTIVE_END_DATE >= the same effective date. Only the single intersecting version of each element class usage is therefore visible. Since both predicates are strict against the session date, an element class usage whose effective window does not contain the session date — whether expired or future-dated — is excluded.

Key Columns

  • ELEMENT_CLASS_USAGE_ID — Primary identifier of the element class usage record; the join key back to PAY_ELEMENT_CLASS_USAGES_F.
  • RUN_TYPE_ID — Foreign key to the payroll run type with which the element classification is being used.
  • CLASSIFICATION_ID — Foreign key to the element classification (e.g., earnings, deductions, information) being associated.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — The date-effective window of the returned version.
  • BUSINESS_GROUP_ID — Business group that owns the row; critical for multi-organization security filtering.
  • LEGISLATION_CODE — Legislation under which the usage is defined, used to scope payroll rules by country.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard WHO audit columns.
  • OBJECT_VERSION_NUMBER — Optimistic locking token, present for consistency with the base table but not meaningful to read-only reporting.

Common Use Cases and Queries

Typical uses include validating run type/element classification combinations, driving payslip or element eligibility extracts, and reconciling payroll setup between environments. The view removes the need to duplicate effective-date logic, though queries should still filter on BUSINESS_GROUP_ID to respect security.

Example — list active element class usages for a legislation:

  • SELECT element_class_usage_id, run_type_id, classification_id, effective_start_date, effective_end_date, legislation_code FROM pay_element_class_usages WHERE legislation_code = :p_legislation ORDER BY run_type_id, classification_id;

Example — trace the base table version behind a returned row:

  • SELECT f.* FROM pay_element_class_usages_f f, pay_element_class_usages v WHERE f.element_class_usage_id = v.element_class_usage_id AND v.run_type_id = :p_run_type;

Example — count usages per classification for audit:

  • SELECT classification_id, COUNT(*) FROM pay_element_class_usages WHERE business_group_id = :p_bg GROUP BY classification_id;

Because results depend on FND_SESSIONS.EFFECTIVE_DATE, queries executed outside a Forms/OLTP session (for example, via SQL*Plus or a third-party tool) may return no rows unless a session context exists. In such cases, supply an explicit date against PAY_ELEMENT_CLASS_USAGES_F instead.