Search Results hr_pattern_constructions_pk

Overview

HR_PATTERN_CONSTRUCTIONS is a Human Resources (PER) schema table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the decomposition of a repeating work pattern into its individual "bits" of time. Work patterns in EBS—used by scheduling, availability, and workforce management functionality—are hierarchical by nature: a named pattern is composed of one or more pattern bits arranged in a defined sequence, and any given construction row represents one such arrangement step. Where HR_PATTERNS defines the repeating pattern itself, HR_PATTERN_CONSTRUCTIONS records how that pattern is assembled from its constituent pieces, preserving sequence and availability semantics.

From a Data Vault modeling perspective, the metadata's heuristic classification for this table is link. This reflects its structure: the table primarily resolves many-to-many and hierarchical relationships between pattern definitions (HR_PATTERNS, including a self-referencing component pattern), individual pattern bits (HR_PATTERN_BITS), and the descriptive attributes (sequence, availability) that qualify each association. Modeling it as a link with an associated satellite for the availability attribute is a reasonable design suggestion, but the authoritative source remains the PER transactional model.

Key Information Stored

The table exposes 12 documented columns. The most significant include:

• PATTERN_CONSTRUCTION_ID — the surrogate primary key, enforced by HR_PATTERN_CONSTRUCTIONS_PK. This is a system-generated identifier with no business meaning.
• SEQUENCE_NO and PATTERN_ID — together forming the business-key candidate unique index HR_PAC_UK_01 (also documented as HR_PATTERN_CONSTRUCTIONS_UK1). SEQUENCE_NO establishes the ordinal position of a construction entry within its parent pattern, while PATTERN_ID identifies the owning pattern.
• PATTERN_BIT_ID — foreign key to HR_PATTERN_BITS, identifying the individual time bit used at this construction step.
• COMPONENT_PATTERN_ID — foreign key to HR_PATTERNS, allowing a pattern to be composed of another pattern (nested or referenced patterns), which gives the structure its hierarchical character.
• AVAILABILITY — the descriptive attribute indicating the availability state associated with the construction entry.
• OBJECT_VERSION_NUMBER — supports optimistic locking for concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS WHO columns capturing row audit and audit-trail information.

Common Use Cases and Queries

Typical usage centers on reconstructing a pattern's full definition in sequence, resolving which bits or nested patterns occupy each position, and reporting on availability. A standard query joins the table to its parents and children:

• List all construction entries for a pattern in order: SELECT pc.sequence_no, pc.pattern_bit_id, pc.component_pattern_id, pc.availability FROM hr_pattern_constructions pc WHERE pc.pattern_id = :pattern_id ORDER BY pc.sequence_no;
• Join to HR_PATTERN_BITS to resolve the actual time bits: ... JOIN hr_pattern_bits pb ON pb.pattern_bit_id = pc.pattern_bit_id;
• Detect nested patterns by joining COMPONENT_PATTERN_ID back to HR_PATTERNS.
• Audit changed constructions using LAST_UPDATE_DATE and LAST_UPDATED_BY for reconciliation or reporting extracts.

Reporting use cases include schedule validation, availability analysis, and data migration verification, where the (SEQUENCE_NO, PATTERN_ID) uniqueness guarantees a deterministic ordering per pattern.

Related Objects

• HR_PATTERNS — referenced twice: via PATTERN_ID (owning pattern) and COMPONENT_PATTERN_ID (nested pattern).
• HR_PATTERN_BITS — referenced via PATTERN_BIT_ID, providing the individual time bits.
• HR_PATTERN_CONSTRUCTIONS_PK and HR_PAC_UK_01 / HR_PATTERN_CONSTRUCTIONS_UK1 — unique indexes enforcing the surrogate and business keys.
• PER scheduling and availability APIs that consume pattern definitions indirectly through HR_PATTERNS.
• WHO audit columns linking to standard FND user/login reference data.