Results for “hr_exception_usages_uk2”

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

Overview

HR_EXCEPTION_USAGES is a table in the HR (PER — Human Resources) schema of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It identifies the usage of defined exceptions to a repeating pattern. In Oracle HRMS, calendars and their repeating work patterns are used to schedule shifts, workday definitions, and coverage plans. A pattern (for example, a two-week rotation) may need exceptions — a specific holiday, a training day, or an altered shift — and HR_EXCEPTION_USAGES records where such an exception is actually applied.

The ETRM metadata classifies this object heuristically as a link table in a Data Vault sense. This classification is a modeling suggestion rather than a physical property: the table sits between three parents and resolves a many-to-many style association, which is the classic role of a link entity. Physically, the table is nothing more than a relationship carrier, holding the foreign keys that pin an exception to a specific calendar and calendar usage.

Key Information Stored

• EXCEPTION_USAGE_ID — surrogate primary key, enforced by HR_EXCEPTION_USAGES_PK. No business meaning beyond uniqueness.
• EXCEPTION_ID — foreign key to HR_PATTERN_EXCEPTIONS, identifying which defined exception is being used.
• CALENDAR_ID — foreign key to HR_CALENDARS, identifying the calendar against which the exception applies.
• CALENDAR_USAGE_ID — foreign key to HR_CALENDAR_USAGES, narrowing the application to a particular usage of that calendar.
• EXCEPTION_REASON — free-text or coded reason describing why the exception was applied.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/Forms framework to prevent concurrent update conflicts.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — standard WHO columns for audit and change tracking.

Two unique indexes act as business-key candidates. HR_EXCEPTION_USAGES_UK1 covers (EXCEPTION_ID, CALENDAR_ID), and HR_EXCEPTION_USAGES_UK2 covers (EXCEPTION_ID, CALENDAR_USAGE_ID). Together with the surrogate PK, these enforce that a given exception is not used twice for the same calendar or the same calendar usage.

Common Use Cases and Queries

Typical usage includes reporting which exceptions have been applied to a calendar, checking for duplicate or conflicting exception assignments, and tracing a pattern exception back to the calendars it affects.

• List all exceptions applied to a calendar:

  SELECT e.exception_id, e.exception_reason, p.name
FROM hr_exception_usages e, hr_pattern_exceptions p
WHERE e.exception_id = p.exception_id
AND e.calendar_id = :calendar_id;

• Find every calendar using a specific exception:

  SELECT u.calendar_id, c.name
FROM hr_exception_usages u, hr_calendars c
WHERE u.calendar_id = c.calendar_id
AND u.exception_id = :exception_id;

• Detect exceptions tied to a particular calendar usage:

  SELECT * FROM hr_exception_usages
WHERE calendar_usage_id = :usage_id;

• Audit recently created exception usages:

  SELECT exception_usage_id, exception_id, calendar_id, creation_date
FROM hr_exception_usages
WHERE creation_date > SYSDATE - 30;

Related Objects

• HR_CALENDARS — joined on CALENDAR_ID; the parent calendar definition.
• HR_CALENDAR_USAGES — joined on CALENDAR_USAGE_ID; the specific usage of a calendar.
• HR_PATTERN_EXCEPTIONS — joined on EXCEPTION_ID; the defined exception being used.
• HR_EXCEPTION_USAGES_PK / UK1 / UK2 — the primary and unique indexes enforcing identity and business uniqueness.

Because HR_EXCEPTION_USAGES is a link table, it is typically queried in join with its three foreign-key parents rather than in isolation. Application logic in the PER module manages exception application through the calendar and pattern maintenance forms, which populate this table automatically.