Search Results hr_patterns_uk1

Overview

HR.HR_PATTERNS is a foundational Oracle E-Business Suite table that stores repeating patterns of time periods used across multiple application modules. In the EBS 12.1.1 and 12.2.2 data models, the table resides in the HR schema (FND Design Data: PER.HR_PATTERNS) and is created in the APPS_TS_TX_DATA tablespace with a PCT Free of 20. It defines the master templates that describe how a sequence of time periods recurs — for example, a shift pattern assigned to a group of workers, a work schedule cycle, or a timetable governing resource availability. Downstream constructs such as calendars, pattern constructions, and pricing qualifiers reference this table to realize actual working-time rules.

Under a heuristic Data Vault classification derived from its foreign key structure, HR_PATTERNS behaves as a hub: it holds the unique business identity of each pattern and is referenced by many dependent (satellite-like) tables. This classification should be treated as a modeling suggestion rather than a documented constraint.

Key Information Stored

The table contains ten documented columns. The most significant are:

• PATTERN_ID — System-generated numeric primary key (HR_PATTERNS_PK). This is the surrogate key referenced by all foreign-key relationships.
• PATTERN_NAME — VARCHAR2(80), the unique business identifier for the pattern. Enforced by the unique index HR_PATTERNS_UK1 (also documented as HR_PAT_UK01). This is the primary business-key candidate and the column users most often search by (e.g., the search term "hr_patterns_uk1" maps to this unique index).
• PATTERN_START_WEEKDAY — VARCHAR2(30), the day of the week on which the pattern must begin.
• PATTERN_START_TIME — The time of day (format HH:MI) at which the pattern starts.
• OBJECT_VERSION_NUMBER — System-managed optimistic locking column, incremented by one on each row update.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — Standard "Who" audit columns maintained by the framework.

The surrogate key (PATTERN_ID) is distinct from the business key (PATTERN_NAME); the unique index ensures no two patterns share a name.

Common Use Cases and Queries

The table is queried during workforce scheduling, calendar generation, payroll time calculation, and pricing/qualifier configuration. A common lookup resolves a pattern's surrogate key from its business name:

• Retrieve a pattern's start definitions: SELECT PATTERN_ID, PATTERN_NAME, PATTERN_START_WEEKDAY, PATTERN_START_TIME FROM HR.HR_PATTERNS WHERE PATTERN_NAME = :p_name;
• Join to dependent construction rows to list component periods: SELECT p.PATTERN_NAME, c.* FROM HR.HR_PATTERNS p, HR.HR_PATTERN_CONSTRUCTIONS c WHERE p.PATTERN_ID = c.PATTERN_ID;
• Reporting on audit activity: filter on LAST_UPDATE_DATE to identify recently modified schedule templates.
• Validating uniqueness before inserts: query by PATTERN_NAME to honor HR_PATTERNS_UK1.

Related Objects

Multiple tables carry PATTERN_ID foreign keys into this hub, reflecting its broad reuse:

• HR_CALENDARS — references HR_PATTERNS.PATTERN_ID to bind calendars to a pattern.
• HR_PATTERN_CONSTRUCTIONS — references both PATTERN_ID and COMPONENT_PATTERN_ID (self-referencing composition).
• HR_PATTERN_EXCEPTIONS — date-specific deviations from a pattern.
• HR_PATTERN_PURPOSES — usage classifications for each pattern.
• QP_PATTERNS, QP_LIST_LINES, QP_ARCH_LIST_LINES, QP_ATTRIBUTE_GROUPS, QP_PATTERN_PHASES — Oracle Advanced Pricing tables that reuse patterns for qualifier and modifier scheduling.

The HR_PATTERNS# object is the underlying editioning/trigger variant. All references should be joined via PATTERN_ID.