Results for “per_absence_attendances_v3”

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

Overview

APPS.PER_ABSENCE_ATTENDANCES_V3 is a reporting and integration view in the Oracle E-Business Suite PER — Human Resources product. In Oracle EBS 12.1.1 and 12.2.2 the object is delivered with a status of VALID and is owned by the APPS schema. Its documented purpose is to expose the history of periods of absence for employees. Rather than reading the transactional absence table in isolation, the view resolves the meaningful context of each absence record: the absence type name, the person's name and employee number, the authorising person's name and employee number, the reason meaning, and the absence category meaning. This makes the view suitable for concurrent programs, BI Publisher reports, Discoverer workbooks, OBIEE/OTBI extracts, and custom PL/SQL integrations that require human-readable absence data without re-implementing the lookup joins. Because the view resolves effective-dated people records through PER_ALL_PEOPLE_F, it reflects the person attributes valid at the relevant date rather than a static snapshot.

Underlying Base Objects

The documented metadata lists the referenced base objects as HR_API (package), HR_LOOKUPS (view), PER_ABSENCE_ATTENDANCES (synonym), PER_ABS_ATTENDANCE_REASONS (synonym), PER_ABSENCE_ATTENDANCE_TYPES_VL (view), and PER_ALL_PEOPLE_F (synonym). The view's defining SQL joins PER_ABSENCE_ATTENDANCES (aliased ABA) as the driving table to PER_ABSENCE_ATTENDANCE_TYPES (ABT) on ABSENCE_ATTENDANCE_TYPE_ID, to PER_ALL_PEOPLE_F (PEP) for the authorising person and again (PEP2) for the replacement person, and to PER_ABS_ATTENDANCE_REASONS (ABR) for the absence reason. Two HR_LOOKUPS instances resolve the reason meaning (lookup type ABSENCE_REASON) and the absence category meaning (lookup type ABSENCE_CATEGORY). All joins to the people, reason, and lookup tables are outer joins ((+)), so absence rows are retained even when no authorising person, replacement person, reason, or category is populated. The person joins are further constrained by effective-date logic comparing DATE_NOTIFICATION (falling back to DATE_START) against the person record's effective start and end dates.

Key Columns

• ABSENCE_ATTENDANCE_ID — primary identifier of the absence record.
• BUSINESS_GROUP_ID — the HR business group owning the record.
• ABSENCE_ATTENDANCE_TYPE_ID — links to the absence type; the view also returns the type NAME.
• PERSON_ID, AUTHORISING_PERSON_ID, REPLACEMENT_PERSON_ID — the employee, the authoriser, and any replacement.
• ABS_ATTENDANCE_REASON_ID, with resolved MEANING from ABSENCE_REASON.
• PERIOD_OF_INCAPACITY_ID — links the absence to statutory sick pay (SSP) incapacity data.
• SSP1_ISSUED — indicates whether an SSP1 form (statutory sick pay record) has been issued for the absence. This is the column referenced by the search term "ssp1_issued".
• ABSENCE_DAYS, ABSENCE_HOURS — quantified duration.
• DATE_START, TIME_START, DATE_END, TIME_END — actual period boundaries.
• DATE_PROJECTED_START, TIME_PROJECTED_START, DATE_PROJECTED_END, TIME_PROJECTED_END — expected period boundaries.
• DATE_NOTIFICATION — date the absence was reported.
• OCCURRENCE — occurrence number used in absence-monitoring rules.
• FULL_NAME, EMPLOYEE_NUMBER — for the authorising and replacement persons.
• ATTRIBUTE1–ATTRIBUTE20 and ATTRIBUTE_CATEGORY — descriptive flexfield values.
• Audit columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE.

Common Use Cases and Queries

Typical scenarios include sickness and absence reporting, SSP1 issuance audits, absence-reason analysis, and integration feeds into HR or payroll systems. The following query lists absences for a person where an SSP1 has been issued:

SELECT absence_attendance_id,
       full_name,
       employee_number,
       name            AS absence_type,
       date_start,
       date_end,
       absence_days,
       ssp1_issued
FROM   apps.per_absence_attendances_v3
WHERE  person_id = :p_person_id
AND    ssp1_issued = 'Y'
ORDER BY date_start DESC;

Reason and category summaries are prepared by the view itself, so aggregation requires no additional joins:

SELECT meaning,
       COUNT(*)        AS absence_count,
       SUM(absence_days) AS total_days
FROM   apps.per_absence_attendances_v3
WHERE  date_start BETWEEN :p_from AND :p_to
GROUP BY meaning
ORDER BY total_days DESC;

For statutory reporting, filter on period_of_incapacity_id and ssp1_issued, and use date_notification to satisfy the effective-dated person join. Because all optional joins are outer joins, records missing a reason or authoriser remain visible; consumers should therefore treat meaning and the authorising person columns as potentially null.