Results for “pay_element_type_usages”

22 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The PAY_ELEMENT_TYPE_USAGES view is a date-effective reporting object within the Oracle E-Business Suite Payroll (PAY) product module. It is owned by the APPS schema and holds a status of VALID in the documented ETRM metadata for releases 12.1.1 and 12.2.2. The view presents the association between a payroll element type and the payroll run types for which that element is eligible, exposing whether the element is included or excluded from a given run type. Because element eligibility directly influences which payments and deductions are processed during a payroll run, this view is a foundational reference for both functional reporting and technical integration.

Its principal distinction from the underlying base table is its date-effective behavior. The view returns only those rows whose effective date range envelops the current application session's effective date, as determined at query time by FND_SESSIONS. This design removes the burden of manually filtering by effective dates from report authors and interface developers, and ensures that point-in-time payroll configuration is presented consistently with the way the payroll engine itself resolves element eligibility.

Underlying Base Objects

The view is defined over two documented base objects. The primary source is the synonym PAY_ELEMENT_TYPE_USAGES_F, which is the date-effective ("_F") table that stores the full row history, including effective start and end dates for each element type usage record. The second referenced object is the synonym FND_SESSIONS, which supplies the current session's effective date.

The view text filters the base table with two correlated subqueries: rows are retained only where EFFECTIVE_START_DATE is less than or equal to the session effective date and EFFECTIVE_END_DATE is greater than or equal to that same session effective date. The session effective date is obtained by matching FND_SESSIONS.SESSION_ID to USERENV('SESSIONID'). In effect, PAY_ELEMENT_TYPE_USAGES is a thin, session-aware projection of PAY_ELEMENT_TYPE_USAGES_F that always yields the currently valid version of each usage record.

Key Columns

  • ELEMENT_TYPE_USAGE_ID — Primary identifier for the usage record.
  • RUN_TYPE_ID — Identifies the payroll run type to which the element type usage applies.
  • ELEMENT_TYPE_ID — Identifies the element type being associated with the run type.
  • INCLUSION_FLAG — Indicates whether the element type is included in or excluded from the specified run type.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — The date-effective boundaries of the record; the view returns only rows valid for the session date.
  • BUSINESS_GROUP_ID — The business group that owns the record, providing a key partitioning attribute for multi-organization reporting.
  • LEGISLATION_CODE — The legislation under which the usage is defined, supporting country-specific payroll configurations.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Oracle who-audit columns.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the applications framework.

Common Use Cases and Queries

The view is commonly used to validate which run types will process a given element type, to audit element eligibility configuration, and to feed downstream extracts that must reflect the configuration in effect on a particular date. A typical query returning the usages for a specific element type is:

  • SELECT etu.element_type_id, etu.run_type_id, etu.inclusion_flag, etu.effective_start_date, etu.effective_end_date FROM apps.pay_element_type_usages etu WHERE etu.element_type_id = :p_element_type_id ORDER BY etu.run_type_id;
  • SELECT etu.element_type_id, etu.run_type_id, etu.inclusion_flag FROM apps.pay_element_type_usages etu WHERE etu.business_group_id = :p_bg_id AND etu.legislation_code = :p_legislation AND etu.inclusion_flag = 'Y';

For historical reporting, users should query the base date-effective table PAY_ELEMENT_TYPE_USAGES_F directly, since the view deliberately scopes results to the session effective date. When integration extracts must reproduce a payroll run exactly, correlating this view with the run's effective date via an explicit session context is the recommended approach.