Results for “exception_reason”

50+ results




Overview
Explore in AI Chat

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

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.