Search Results per_std_holiday_absences_pk




Overview
Explore in AI Chat

Overview

PER_STD_HOLIDAY_ABSENCES is a Human Resources (PER) module table in the Oracle EBS schema owned by HR. As documented in ETRM 12.1.1 / 12.2.2, it records when statutory holidays are taken by employees. Each row captures an individual absence event tied to a specific person and a predefined standard holiday, allowing the application to track the actual date a holiday was observed, whether it was deferred, or whether the entitlement expired. This is essential for HR statutory holiday administration, particularly in legislated environments where entitlement, carry-over, and expiry rules are audited.

From a dimensional modeling perspective, the heuristic Data Vault classification derived from the foreign key structure suggests this object is satellite-leaning. It behaves as a descriptive satellite attached to the PER_STANDARD_HOLIDAYS reference table, storing event-level attributes (dates, reasons, expiry status) that change independently of the parent holiday definition. Its primary key, PER_STD_HOLIDAY_ABSENCES_PK, is defined on STD_HOLIDAY_ABSENCES_ID alone per the ETRM excerpt, while the unique index additionally includes ZD_EDITION_NAME, reflecting the editioning feature introduced in later 12.2.x releases.

Key Information Stored

The table contains 35 documented columns. The most significant include:

  • STD_HOLIDAY_ABSENCES_ID — Surrogate primary key and unique identifier for each absence record.
  • PERSON_ID — Foreign reference to the employee or person taking the statutory holiday.
  • STANDARD_HOLIDAY_ID — Foreign key to PER_STANDARD_HOLIDAYS, identifying the holiday definition affected.
  • DATE_NOT_TAKEN — Date on which the holiday was not taken, supporting deferral or forfeiture tracking.
  • ACTUAL_DATE_TAKEN — The real calendar date the holiday was observed.
  • REASON — Free-text or coded explanation for non-standard handling of the holiday.
  • EXPIRED — Flag indicating whether the holiday entitlement lapsed.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle framework.
  • ZD_EDITION_NAME — Editioning column present in the unique index; business-key candidate alongside the primary key.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 — Descriptive flexfield (DFF) storage for client-specific extensions.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns for change tracking.

Common Use Cases and Queries

Typical reporting scenarios include identifying employees who did not take a scheduled statutory holiday, auditing expired entitlements, and reconciling actual versus planned holiday dates for payroll or legislative compliance. A representative query joins the table to PER_STANDARD_HOLIDAYS to resolve holiday names:

  • SELECT a.STD_HOLIDAY_ABSENCES_ID, a.PERSON_ID, h.HOLIDAY_NAME, a.ACTUAL_DATE_TAKEN, a.EXPIRED FROM PER_STD_HOLIDAY_ABSENCES a JOIN PER_STANDARD_HOLIDAYS h ON a.STANDARD_HOLIDAY_ID = h.STANDARD_HOLIDAY_ID;
  • Filtering on EXPIRED = 'Y' to report lapsed holiday entitlement.
  • Filtering on DATE_NOT_TAKEN IS NOT NULL to identify deferred or forfeited holidays.
  • Extracting DFF attribute values (ATTRIBUTE1–ATTRIBUTE20) for client-specific statutory reporting.

Related Objects

The most significant related objects are:

  • PER_STANDARD_HOLIDAYS — Parent reference table; joined via STANDARD_HOLIDAY_ID.
  • PER_ALL_PEOPLE_F — Standard HR person view; joined via PERSON_ID to resolve employee details.
  • PER_STD_HOLIDAY_ABSENCES_PK — Primary key index on STD_HOLIDAY_ABSENCES_ID (with ZD_EDITION_NAME).
  • HR schema editioning views — ZD_EDITION_NAME supports edition-based redefinition.
  • PER DFF definitions — Enabled on ATTRIBUTE_CATEGORY and ATTRIBUTE1–20.