Search Results hr_pattern_purposes




Overview
Explore in AI Chat

Overview

HR_PATTERN_PURPOSES is a Human Resources (PER) table in the Oracle E-Business Suite 12.1.1 and 12.2.2 schemas. It stores the potential purposes associated with a parent pattern, such as "Work Pattern" or "Resource schedule." In the ETRM data model, this table functions as a dependent child of HR_PATTERNS, providing the classification dimension that describes what a given pattern is used for. Because the pattern purpose is the meaningful descriptor while the pattern itself carries the temporal and structural definition, the object supports workforce scheduling, resource planning, and shift-pattern configuration.

Under the heuristic Data Vault classification provided in the metadata, HR_PATTERN_PURPOSES is satellite-leaning. This suggests that, in a Data Vault model, it would most naturally be modeled as a satellite attached to a HR_PATTERNS hub, with PATTERN_PURPOSE_ID acting as the row identifier and PATTERN_ID acting as the link to the parent hub. The absence of downstream foreign keys reinforces this interpretation: the table describes the parent rather than participating in additional business relationships.

Key Information Stored

The documented physical schema contains nine columns. Among the most important:

  • PATTERN_PURPOSE_ID — the surrogate primary key, uniquely identifying each purpose row. This is enforced by the index HR_PATTERN_PURPOSES_PK.
  • PATTERN_PURPOSE — the descriptive purpose value, such as "Work Pattern" or "Resource schedule." It is part of the business-key candidate HR_PATTERN_PURPOSES_UK1 (PATTERN_PURPOSE, PATTERN_ID), alongside the alternate index HR_PAP_UK01.
  • PATTERN_ID — the foreign key to HR_PATTERNS, establishing the parent pattern to which the purpose applies. It is the second component of the unique business key.
  • OBJECT_VERSION_NUMBER — the optimistic-locking column used by the Oracle Applications Framework to detect concurrent updates.
  • CREATED_BY, CREATION_DATE — standard who-columns recording the creating user and timestamp.
  • LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — standard who-columns capturing the most recent modification user, login session, and timestamp.

The distinction between the surrogate key (PATTERN_PURPOSE_ID) and the business-key candidate (PATTERN_PURPOSE, PATTERN_ID) is important for data migration and integration: users should not assume that PATTERN_PURPOSE_ID values are stable across environments, whereas the combination of purpose and pattern should remain semantically consistent.

Common Use Cases and Queries

Typical reporting scenarios include listing all purposes defined for a given pattern, identifying patterns by purpose category, and auditing configuration changes. A representative query joins the child to its parent:

  • SELECT p.pattern_id, p.pattern_purpose FROM hr_pattern_purposes p WHERE p.pattern_id = :pattern_id;
  • SELECT pp.pattern_purpose, COUNT(*) FROM hr_pattern_purposes pp GROUP BY pp.pattern_purpose;
  • SELECT pp.pattern_id, pp.pattern_purpose, h.pattern_name FROM hr_pattern_purposes pp JOIN hr_patterns h ON h.pattern_id = pp.pattern_id WHERE pp.pattern_purpose = 'Work Pattern';

Because the table holds a small reference-style set of values, it is also useful for validation lookups during data loading to ensure the purpose supplied matches an existing definition for the pattern. Auditors frequently query the who-columns to establish whether a purpose assignment was later modified, using LAST_UPDATED_DATE and LAST_UPDATED_BY.

Related Objects

The most significant related object is the parent table HR_PATTERNS, joined via PATTERN_ID to PATTERN_PURPOSE_ID ownership. Key relationships and dependents include:

  • HR_PATTERNS — parent; joined on HR_PATTERN_PURPOSES.PATTERN_ID = HR_PATTERNS.PATTERN_ID.
  • HR_PATTERN_PURPOSES_PK — unique index on PATTERN_PURPOSE_ID.
  • HR_PATTERN_PURPOSES_UK1 / HR_PAP_UK01 — unique indexes on PATTERN_PURPOSE and PATTERN_ID.
  • Scheduling and resource-planning views or concurrent programs in the PER module that surface pattern purposes to end users.
  • Standard EBS who-column conventions shared with other PER tables, enabling consistent audit reporting across the HR schema.

Because the table has no documented child foreign keys, it should be treated as a leaf descriptor of HR_PATTERNS rather than a shared reference table, and changes to its contents should be governed through the parent pattern configuration rather than independently.