Search Results hr_pattern_exceptions_pk

Overview

HR_PATTERN_EXCEPTIONS is a transactional table in the HR schema of Oracle E-Business Suite (validated in ETRM 12.1.1 and 12.2.2) belonging to the PER – Human Resources product family. Per the documented description, it stores "an exception to a repeating pattern," meaning it captures discrete deviations from otherwise regular recurring schedules defined elsewhere in the application. Repeating patterns in EBS commonly underpin work schedules, shift patterns, availability windows, and similar recurring configurations. The exceptions recorded here are the individual override records that suspend, shorten, extend, or otherwise alter the normal pattern for a defined window of time.

From a data-modeling perspective, the mined heuristic classification is satellite-leaning. That is, HR_PATTERN_EXCEPTIONS behaves more like a descriptive satellite attached to a parent business entity than a standalone hub or a pure link. It carries a foreign key reference to its parent pattern, which supports treating it as dependent descriptive detail hanging off HR_PATTERNS rather than an independent business key. This classification is a suggestion derived from the foreign key topology and should be validated against actual ETL and reporting requirements.

Key Information Stored

The table is documented with 12 physical columns, of which the following are most significant:

• EXCEPTION_ID — The surrogate primary key, enforced by the unique index HR_PATTERN_EXCEPTIONS_PK. It uniquely identifies each exception row and is the target of downstream references.
• PATTERN_ID — Foreign key to HR_PATTERNS, identifying the repeating pattern to which this exception applies.
• EXCEPTION_NAME — The descriptive name of the exception; part of the business-key candidate.
• EXCEPTION_START_TIME — The datetime at which the exception takes effect; also part of the business-key candidate.
• EXCEPTION_END_TIME — The datetime at which the exception ceases to apply, defining the override window.
• EXCEPTION_CATEGORY — A classification value distinguishing the type or nature of the exception.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the EBS framework to detect concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — Standard EBS WHO columns recording audit and user context.

Two unique indexes exist. HR_PATTERN_EXCEPTIONS_PK (EXCEPTION_ID) is the surrogate primary key. The business-key candidate is HR_PAE_UK01 (PATTERN_ID, EXCEPTION_NAME, EXCEPTION_START_TIME), which uniquely identifies an exception within a pattern by name and start time. In the search context "hr_pae_uk01," this is the unique constraint name users encounter in error messages or index listings.

Common Use Cases and Queries

Typical usage centers on validating, auditing, and reporting overrides to recurring patterns. Common queries include listing all active exceptions for a pattern within a time window, correlating exceptions with their usages, and reconciling pattern schedules against their exceptions.

• Inventorying exceptions for a given pattern: SELECT EXCEPTION_ID, EXCEPTION_NAME, EXCEPTION_START_TIME, EXCEPTION_END_TIME, EXCEPTION_CATEGORY FROM HR.HR_PATTERN_EXCEPTIONS WHERE PATTERN_ID = :pattern_id ORDER BY EXCEPTION_START_TIME;
• Finding an exception by its business key: SELECT * FROM HR.HR_PATTERN_EXCEPTIONS WHERE PATTERN_ID = :pattern_id AND EXCEPTION_NAME = :name AND EXCEPTION_START_TIME = :start_time;
• Identifying currently effective exceptions: WHERE SYSDATE BETWEEN EXCEPTION_START_TIME AND EXCEPTION_END_TIME.
• Detecting exceptions that carry no usage linkage, which may indicate stale or orphaned configuration.

These patterns support configuration validation, data-migration reconciliation, and operational reporting on scheduling deviations.

Related Objects

• HR_PATTERNS — Parent of the pattern definition; joined via HR_PATTERN_EXCEPTIONS.PATTERN_ID = HR_PATTERNS.PATTERN_ID.
• HR_EXCEPTION_USAGES — Child table referencing this table; joined via HR_EXCEPTION_USAGES.EXCEPTION_ID = HR_PATTERN_EXCEPTIONS.EXCEPTION_ID.

These documented foreign key relationships establish HR_PATTERN_EXCEPTIONS as a dependent detail between its parent pattern and its usage records, consistent with the satellite-leaning classification.