Search Results hr_patterns




Overview
Explore in AI Chat

Overview

HR_PATTERNS is a core Human Resources table in the Oracle E-Business Suite PER (Human Resources) module, owned by the HR schema. It stores the definition of a repeating time pattern — for example a work pattern, an SSP (Statutory Sick Pay) qualifying pattern, or a resource schedule. Each row represents a single reusable pattern header that describes when a recurring cycle begins and how it is named, forming the master reference against which calendars, pattern constructions, exceptions, and purposes are built.

Its role is that of a foundational definition table: rather than storing individual scheduled days or shifts directly, HR_PATTERNS holds the identity and starting anchor of a pattern, and delegates the detailed composition to child tables. This design allows one pattern to be reused across multiple calendars, resource schedules, and eligibility rules without duplication.

The FK structure indicates that HR_PATTERNS functions as a hub in a Data Vault modeling sense — it is the central parent that many dependent (satellite/link-like) records reference through PATTERN_ID, while PATTERN_NAME serves as the natural business key. The table status is VALID in the documented environment.

Key Information Stored

The physical schema documents ten columns, of which the following are most significant:

  • PATTERN_ID — Surrogate primary key, enforced by HR_PATTERNS_PK. This is the value carried into all dependent tables as the foreign key PATTERN_ID.
  • PATTERN_NAME — The business-key candidate, enforced by the unique index HR_PATTERNS_UK1. This gives the pattern a user-recognisable identity.
  • PATTERN_START_WEEKDAY — Defines the weekday on which the repeating cycle begins, anchoring the pattern in the working week.
  • PATTERN_START_TIME — Records the start time of the pattern, used where a resource or work schedule is time-sensitive.
  • OBJECT_VERSION_NUMBER — Optimistic locking column supporting concurrent update control.
  • CREATED_BY, CREATION_DATE — Standard WHO audit columns recording creation metadata.
  • LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — Standard WHO audit columns recording the last modification and the session that made it.

The surrogate key (PATTERN_ID) is distinct from the business key (PATTERN_NAME); integrations and lookups should resolve names to IDs before joining to dependent tables.

Common Use Cases and Queries

Typical scenarios include retrieving a pattern by name, listing patterns used by a calendar, and reporting on exceptions raised against a pattern.

  • Resolve a pattern ID from its name: SELECT pattern_id, pattern_name FROM hr_patterns WHERE pattern_name = :name;
  • Find calendars that consume a given pattern: SELECT c.* FROM hr_calendars c WHERE c.pattern_id = :pattern_id;
  • List components making up a composite pattern: SELECT * FROM hr_pattern_constructions WHERE pattern_id = :pattern_id;
  • Report exceptions: SELECT * FROM hr_pattern_exceptions WHERE pattern_id = :pattern_id;
  • Identify a pattern's purpose: SELECT * FROM hr_pattern_purposes WHERE pattern_id = :pattern_id;

Reporting is frequently used to audit which work or SSP qualifying patterns are active, which calendars depend on them, and where pricing (QP) constructs reference the same pattern.

Related Objects

The following objects reference HR_PATTERNS through the PATTERN_ID relationship:

Together these relationships confirm HR_PATTERNS as the central hub for all repeating time-pattern definitions across HR and pricing modules.