Results for “pay_au_term_annual_v”

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

Overview

PAY_AU_TERM_ANNUAL_V is an Australian localisation view owned by the APPS schema in Oracle E-Business Suite Payroll (PAY). It presents a denormalised, reporting-friendly projection of annual leave on termination entries processed through Australian payroll legislation. The view isolates all element entry records belonging to the element named "ANNUAL LEAVE ON TERMINATION" and flattens the four associated input values — HOURS, PAYMENT, LOADING, and OTHER — into discrete columns, along with their unit of measure. Its primary function is to support termination reporting, payment reconciliation, and downstream integration where a single record per element entry is preferable to navigating the normalised PAY_ELEMENT_ENTRIES_F and PAY_ELEMENT_ENTRY_VALUES_F structures.

Because the view is localisation-specific and its element name predicate is hard-coded, it should be treated strictly as a read-only reporting object. It is not intended for data maintenance; all DML must be directed to the underlying base tables.

Underlying Base Objects

The view is defined over six documented base objects, all referenced through APPS synonyms:

• PAY_ELEMENT_ENTRIES_F — the driving base table, aliased PEE. Supplies the element entry identifier, element link, assignment, effective dates, object version number, and rowid.
• PAY_ELEMENT_TYPES_F — aliased PET. Restricts rows to the element name "ANNUAL LEAVE ON TERMINATION" and provides the processing type used by the termination function.
• PAY_ELEMENT_ENTRY_VALUES_F — instantiated four times (EEV0–EEV3), each joined to the entry to retrieve one specific input value's screen entry value.
• PAY_INPUT_VALUES_F — instantiated four times (PIV0–PIV3), supplying input value metadata such as the unit of measure and enforcing the NAME predicates for HOURS, PAYMENT, LOADING, and OTHER.
• FND_SESSIONS — aliased SES. Correlates the query to the current user session and its effective date, ensuring the element type and entry dates are effective for that session.
• PAY_AU_TERMINATIONS — a PL/pgSQL package whose PROCESSED function is invoked within the SELECT list to derive a single-character termination status indicator.

The view text carries an ORDERED hint, forcing the join sequence PET, SES, PIV0–PIV3, EEV0, PEE, EEV1–EEV3. This ordering is significant for performance, as it drives the element type lookup first and then resolves the four input value joins before reaching the entry values.

Key Columns

• ROWID / OBJECT_VERSION_NUMBER — identify the underlying element entry row and support optimistic locking semantics.
• ELEMENT_ENTRY_ID / ELEMENT_LINK_ID / ASSIGNMENT_ID — the principal keys linking the entry to its assignment and element link.
• ELEMENT_TYPE_ID — the element type for the annual leave on termination element.
• CREATOR_TYPE / ENTRY_TYPE — indicate how the entry was created and the type of entry.
• INPUT_VALUE_ID, SCREEN_ENTRY_VALUE, UOM (×4) — repeated for HOURS, PAYMENT, LOADING, and OTHER, exposing each input value's identifier, entered value, and unit of measure in a single row.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-tracked effective range of the element entry.
• Termination indicator — the SUBSTR of PAY_AU_TERMINATIONS.PROCESSED, a single-character status flag used to determine whether the entry has been processed on termination.

Common Use Cases and Queries

Typical scenarios include termination payment auditing, reconciliation of hours, payment components, and loadings, and extraction feeds to third-party payroll or finance systems.

• Per-assignment termination leave detail:

SELECT assignment_id, effective_start_date, effective_end_date,
       hours, payment, loading, other
FROM   apps.pay_au_term_annual_v
WHERE  assignment_id = :p_assignment_id
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

• Aggregated termination liability by assignment:

SELECT assignment_id,
       SUM(hours)   AS total_hours,
       SUM(payment) AS total_payment,
       SUM(loading) AS total_loading
FROM   apps.pay_au_term_annual_v
GROUP  BY assignment_id;

• Filtering by processing status: joining or filtering on the derived termination flag allows users to isolate entries already processed on termination from those still pending.

Because the view is session-filtered via FND_SESSIONS and constrained to a single element name, queries should generally be run within an initialized EBS session and scoped to specific assignments or effective dates to avoid full scans of PAY_ELEMENT_ENTRIES_F and its four entry-value joins.