Search Results per_nl_absence_changes




Overview
Explore in AI Chat

Overview

The PER_NL_ABSENCE_CHANGES table is a Dutch localizations table residing in the HR schema and shipped as part of the Oracle E-Business Suite PER – Human Resources product family. Its documented purpose is to capture "Dutch Specific Absence Changes," meaning it stores the audit trail of modifications made to absence and sickness records that are governed by Netherlands statutory reporting requirements (commonly associated with the UWV and the WAZO / Poortwachter framework for sickness absence tracking). Because the table records the delta between successive states of an absence record — the date a change occurred, the type of update, and the reported indicator — it functions as a change-history ledger rather than a master record.

From a heuristic Data Vault modeling perspective, the ETRM metadata classifies this table as standalone. In practice this suggests a satellite treatment: the table carries descriptive, time-stamped attributes about an absence record, referencing the parent PER_ABSENCE_ATTENDANCES entity through a foreign key. It is not a hub (it holds no independently generated business key) and not a link (it resolves no many-to-many relationship). The single documented foreign key — ABSENCE_ATTENDANCE_ID → PER_ABSENCE_ATTENDANCES — anchors each change row to exactly one absence record, consistent with satellite semantics.

Key Information Stored

The documented physical schema for release 12.2.2 contains eight columns. The most significant are:

  • ABSENCE_ATTENDANCE_ID — Foreign key to PER_ABSENCE_ATTENDANCES. This is the join key back to the parent absence record and the primary business relationship for every row.
  • PERSON_ID — Identifies the employee (assignment holder) whose sickness or absence record was modified. Enables person-centric reporting without traversing the parent table in all cases.
  • DATE_CHANGED — The timestamp at which the change to the absence record was captured. This is the effective-dating column that gives the table its historical character.
  • UPDATE_TYPE — Categorizes the nature of the change (for example, an initial entry, a correction, or an adjustment), supporting audit classification.
  • REPORTED_INDICATOR — A flag denoting whether the change has been reported (typically to the Dutch authorities), central to compliance monitoring.
  • SICKNESS_START_DATE — Records the start date of the sickness period as known at the time of the change.
  • PERCENTAGE_SICK — The degree of sickness (partial or full incapacity) associated with the change, relevant to the Dutch partial-sickness regime.
  • RECOVERY_DATE — The date of recovery recorded with the change, closing out a sickness period.

The metadata does not document a surrogate primary key or a named unique index beyond the foreign key reference. Where no single surrogate key is exposed, the practical row identity is typically a composite of ABSENCE_ATTENDANCE_ID, DATE_CHANGED, and UPDATE_TYPE; these should be treated as business-key candidates when reconciling history rows.

Common Use Cases and Queries

Typical scenarios include Dutch statutory sickness reporting, absence audit trails, and reconciliation between the current absence record and its historical changes.

A join back to the parent absence record:

SELECT ac.absence_attendance_id,
       ac.date_changed,
       ac.update_type,
       ac.sickness_start_date,
       ac.percentage_sick,
       ac.recovery_date
FROM   per_nl_absence_changes ac,
       per_absence_attendances pa
WHERE  ac.absence_attendance_id = pa.absence_attendance_id
AND    ac.person_id = :person_id
ORDER BY ac.date_changed;

A compliance query surfacing changes not yet reported:

SELECT person_id, absence_attendance_id, date_changed
FROM   per_nl_absence_changes
WHERE  reported_indicator = 'N'
ORDER BY date_changed;

Related Objects

  • PER_ABSENCE_ATTENDANCES — Parent absence record; joined on ABSENCE_ATTENDANCE_ID. The primary relationship.
  • PER_ALL_PEOPLE_F — Employee master; joined on PERSON_ID for name and assignment context.
  • PER_ALL_ASSIGNMENTS_F — Assignment-level detail for organizational reporting on sickness changes.
  • PER_NL_ABSENCE_CHANGES (self) — No self-join is documented, but successive DATE_CHANGED rows enable state-diff analysis.
  • PAY_ELEMENT_ENTRIES_F — Where Dutch sickness changes drive payroll absence element entries.

Because the object is standalone in the Data Vault sense, integrations should always resolve the parent PER_ABSENCE_ATTENDANCES row before interpreting a change, and reporting logic should select the most recent DATE_CHANGED row to obtain the current state.