Search Results pay_au_term_etp_v




Overview
Explore in AI Chat

Overview

PAY_AU_TERM_ETP_V is a read-only database view owned by the APPS schema and defined in the PAY (Payroll) product family. It is classified in the ETRM repository as an Australian localisation view, meaning it exists to satisfy statutory and operational reporting requirements specific to Australian payroll processing — principally the treatment of Employment Termination Payments (ETPs). The view denormalises element entry data for termination-related elements into a flattened, single-row-per-entry shape, resolving the individual input values of each element entry into discrete numbered columns (INPUT_VALUE_ID and SCREEN_ENTRY_VALUE pairs from EEV0 through EEV9, plus additional override columns).

Because the view exposes both EFFECTIVE_START_DATE and EFFECTIVE_END_DATE directly from PAY_ELEMENT_ENTRIES_F, it is a frequent target for users searching on "effective_end_date". Date-effective filtering is the primary mechanism by which consumers restrict the view to the termination records that were active on a given date, which is essential given that payroll element entries are date-tracked and multiple historical versions of the same entry may coexist.

Underlying Base Objects

The ETRM metadata records the following referenced base objects: FND_SESSIONS (synonym), PAY_AU_TERMINATIONS (package), PAY_ELEMENT_ENTRIES_F (synonym), PAY_ELEMENT_ENTRY_VALUES_F (synonym), PAY_ELEMENT_TYPES_F (synonym), and PAY_INPUT_VALUES_F (synonym). The view's driving table is PAY_ELEMENT_ENTRIES_F aliased PEE. It is joined to PAY_ELEMENT_TYPES_F (PET) on the element type, and to PAY_INPUT_VALUES_F (PIV0–PIV11) to resolve input value definitions and their units of measure. PAY_ELEMENT_ENTRY_VALUES_F is joined repeatedly (EEV0–EEV9) to pivot each input value's screen entry value into its own column. FND_SESSIONS (SES) is cross-joined to supply the session effective date, which is passed into the PAY_AU_TERMINATIONS package. That package supplies the PROCESSED and OVERRIDE_ELIG functions invoked in the select list; the OVERRIDE_ELIG calls carry the comment "BUG# 5956223", reflecting a documented fix lineage in this localisation logic.

Key Columns

  • ELEMENT_ENTRY_ID / ELEMENT_LINK_ID — Primary and foreign keys identifying the element entry and its element link context.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effective boundaries inherited from PAY_ELEMENT_ENTRIES_F. These govern which version of a termination entry is visible and are the standard filter columns for as-of-date reporting.
  • OBJECT_VERSION_NUMBER — Optimistic locking version, useful for integration consumers.
  • ASSIGNMENT_ID — Links the termination entry to the employee assignment.
  • ELEMENT_TYPE_ID / ENTRY_TYPE / CREATOR_TYPE — Identify the element being processed and its origin.
  • INPUT_VALUE_ID / SCREEN_ENTRY_VALUE (EEV0–EEV9) — The pivoted input values of the entry, with associated UOM columns from the matching PAY_INPUT_VALUES_F rows.
  • PROCESSED and OVERRIDE_ELIG flags — Derived values returned by PAY_AU_TERMINATIONS indicating processing state and eligibility override status.
  • EEV9 date conversion — One input value is explicitly converted with TO_DATE(..., 'YYYY/MM/DD HH24:MI:SS'), exposing a date-typed termination attribute.

Common Use Cases and Queries

Typical consumers include Australian payroll reconciliation reports, termination payment extracts, and third-party payroll interfaces that need the individual ETP input values for an assignment within a date range.

A representative query filtering on the effective end date:

  • SELECT element_entry_id, assignment_id, effective_start_date, effective_end_date, element_type_id, processed_flag FROM apps.pay_au_term_etp_v WHERE effective_end_date = TO_DATE('31-DEC-4712','DD-MON-YYYY');
  • SELECT assignment_id, element_entry_id, effective_start_date, effective_end_date FROM apps.pay_au_term_etp_v WHERE assignment_id = :p_assignment_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

Because the view references FND_SESSIONS to derive the session effective date, the results of the PAY_AU_TERMINATIONS function calls depend on the invoking session context. Queries should therefore be executed through the standard EBS session (for example, via a concurrent program or OA Framework page) rather than an ad hoc SQL session, to ensure the effective date used by the package functions matches the reporting date the user intends.