Search Results hr_pattern_constructions_fk1

Overview

HR.HR_PATTERN_CONSTRUCTIONS is a transactional table in the Oracle E-Business Suite HR schema that stores the individual building blocks of repeating time-based patterns used across Oracle Time and Labor, Oracle Human Resources, and Oracle Payroll. Each row represents a single period of time within a pattern, declaring the availability (for example, "AT WORK" or "OFF DUTY") that applies to that period. The SEQUENCE_NO column establishes the order of rows within the parent pattern, allowing the application to reconstruct the chronological flow of a shift, rotation, or schedule template.

A key characteristic of this table is its dual construction model. A pattern period may be defined either by referencing a predefined pattern bit, in which case the row's AVAILABILITY applies to that period only, or by referencing another existing pattern through COMPONENT_PATTERN_ID. When a component pattern is used, its constituent periods are substituted into the parent pattern at the position identified by SEQUENCE_NO, and no availability value is stored on the row because it is inherited from the component pattern. This design allows complex, nested patterns to be assembled from simpler, reusable definitions.

From a Data Vault modeling perspective, the FK topology — with three outgoing foreign keys to HR_PATTERN_BITS and HR_PATTERNS and a business key of SEQUENCE_NO plus PATTERN_ID — suggests classification as a link entity, capturing the associations between patterns, pattern bits, and their ordered sequencing.

Key Information Stored

The table contains twelve documented columns. The most significant include:

• PATTERN_CONSTRUCTION_ID — System-generated numeric primary key and surrogate identifier for the row. Enforced by index HR_PATTERN_CONSTRUCTIONS_PK.
• SEQUENCE_NO — The ordinal position of this construction row within its parent pattern; drives the ordering applied when the pattern is rendered.
• PATTERN_ID — Foreign key to HR_PATTERNS identifying the parent pattern to which this row belongs.
• PATTERN_BIT_ID — Foreign key to HR_PATTERN_BITS; when populated, defines the availability for the period from a predefined bit.
• COMPONENT_PATTERN_ID — Self-referencing foreign key to HR_PATTERNS; when populated, the referenced pattern is substituted at this point in the parent.
• AVAILABILITY — VARCHAR2(30) describing the availability that applies for the duration of the associated pattern bit. It must be null when a component pattern is used.
• OBJECT_VERSION_NUMBER — Optimistic locking column for concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns recording row creation and modification history.

The documented unique business key is HR_PATTERN_CONSTRUCTIONS_UK1 on (SEQUENCE_NO, PATTERN_ID), guaranteeing that no two rows share the same ordinal within a given pattern. Non-unique indexes FK1, FK2, and FK3 support lookups by PATTERN_BIT_ID, PATTERN_ID, and COMPONENT_PATTERN_ID respectively. The table resides in tablespace APPS_TS_TX_DATA with indexes in APPS_TS_TX_IDX.

Common Use Cases and Queries

Typical applications include reconstructing a shift pattern for display, validating that a pattern is fully defined before it is published, and tracing where a component pattern has been reused. A common query lists a pattern's periods in sequence, joining to HR_PATTERN_BITS to resolve the availability and to HR_PATTERNS to identify any nested component patterns:

• Pattern expansion: SELECT pc.sequence_no, pc.availability, pc.pattern_bit_id, pc.component_pattern_id FROM hr_pattern_constructions pc WHERE pc.pattern_id = :p_pattern_id ORDER BY pc.sequence_no;
• Reuse audit: SELECT pattern_id, sequence_no FROM hr_pattern_constructions WHERE component_pattern_id = :p_component_id ORDER BY pattern_id, sequence_no;
• Validation of orphan rows: Query for rows where both PATTERN_BIT_ID and COMPONENT_PATTERN_ID are null, or where AVAILABILITY is populated alongside a component pattern reference, both of which indicate malformed constructions.

Reporting often aggregates counts of construction rows per pattern to detect incomplete definitions, and sequence gaps are queried to detect broken ordering within a pattern.

Related Objects

The following objects are most significant to HR_PATTERN_CONSTRUCTIONS, based on the documented FK relationships:

• HR.HR_PATTERNS — Referenced twice: via PATTERN_ID (the parent pattern) and via COMPONENT_PATTERN_ID (the nested pattern substitution).
• HR.HR_PATTERN_BITS — Referenced via PATTERN_BIT_ID, supplying the availability definition for a period.
• HR_PATTERN_CONSTRUCTIONS_PK / UK1 indexes — Structural dependencies enforcing uniqueness of the surrogate and business keys.
• FK indexes HR_PATTERN_CONSTRUCTIONS_FK1, FK2, FK3 — Support efficient joins from the parent tables back to this child table.
• PER.HR_PATTERN_CONSTRUCTIONS — The FND Design Data registration that governs the table's definition and installation.

Together these objects form the pattern-definition subsystem used by scheduling and time-collection features in EBS 12.1.1 and 12.2.2.