Search Results hr_pattern_constructions_pk




Overview
Explore in AI Chat

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:

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.