Search Results element_reporting_name




Overview
Explore in AI Chat

Overview

APPS.PAY_AU_ASG_LEAVE_TAKEN_V is a consolidated reporting view in the Oracle EBS Payroll (PAY) schema that presents leave and absence information for Australian (AU) assignments. It is a union-all aggregation view that merges the output of seven sibling views — PAY_AU_ASG_LEAVE_TAKEN_V1 through PAY_AU_ASG_LEAVE_TAKEN_V7 — into a single, unified result set. The view's role is to provide a common access point for reporting and integration purposes where absence hours, associated payments, and the relevant action type for a leave event must be retrieved across assignments and payroll time periods.

Within the Australian localization of Oracle Payroll, various leave-taken queries exist to address different combinations of categorization, absence type, and payment handling. Rather than requiring report authors and integrators to know which specific sub-view applies to a given case, this parent view normalizes all seven variants into one consistent projection, ordered by element reporting name and start date.

Underlying Base Objects

The ETRM 12.2.2 metadata documents the following referenced base objects:

The view text is composed as a nested query: an inner inline view unions the seven sub-views, and the outer query projects and orders the combined rows. Note that the definition mixes UNION (which eliminates duplicate rows) for V1, V2, V3, V5, and V6, and UNION ALL (which preserves duplicates) for V4 and V7. This distinction is significant — the V4 and V7 branches are assumed to produce disjoint or intentionally non-deduplicated data, while the remaining branches are subject to implicit distinct processing.

The reference to PAY_AU_SOE_PKG indicates that the underlying logic of these views depends on the Australian Statement of Earnings package, which encapsulates the business rules for calculating absence and payment amounts.

Key Columns

  • ROW_ID — A row identifier enabling unique referencing of each leave record within the result set.
  • ASSIGNMENT_ID — Foreign key to the assignment of the employee to whom the leave record belongs; the primary join key to PER_ALL_ASSIGNMENTS_F and related assignment entities.
  • TIME_PERIOD_ID — Identifies the payroll time period associated with the leave, linking to the time period definitions used in payroll processing.
  • ELEMENT_REPORTING_NAME — The reporting name of the payroll element representing the leave or absence; the primary sort key and the descriptor most meaningful to report consumers.
  • START_DATE — The start date of the leave period; the secondary sort key.
  • END_DATE — The end date of the leave period.
  • ABSENCE_HOURS — The number of hours of absence recorded for the leave event.
  • PAYMENT — The payment amount associated with the leave event.
  • ACTION_TYPE — Indicates the type of action or transaction reflected by the record (for example, the nature of the leave entry or adjustment).

Common Use Cases and Queries

This view is typically used for Australian payroll reporting, absence analysis, and downstream integration extracts. A representative query retrieving leave taken for a specific assignment is:

  • SELECT assignment_id, element_reporting_name, start_date, end_date, absence_hours, payment, action_type FROM apps.pay_au_asg_leave_taken_v WHERE assignment_id = :p_assignment_id ORDER BY start_date;

For period-based reporting, filtering on time period allows reconciliation against payroll runs:

  • SELECT element_reporting_name, SUM(absence_hours) absence_hours, SUM(payment) payment FROM apps.pay_au_asg_leave_taken_v WHERE time_period_id = :p_time_period_id GROUP BY element_reporting_name;

Because the view already applies an ORDER BY on ELEMENT_REPORTING_NAME and START_DATE, consumers requiring deterministic sequence can rely on that ordering, while those aggregating should apply their own GROUP BY. Since the top-level view performs set operations over seven sub-views, care should be taken in performance-sensitive extracts; filtering by ASSIGNMENT_ID or TIME_PERIOD_ID is recommended to limit the volume processed.