Search Results pattern_purpose_id




Overview
Explore in AI Chat

Overview

HR.HR_PATTERN_PURPOSES is a transactional table in the Oracle E-Business Suite Human Resources (HR) schema that records the valid purposes for which a repeating pattern defined in HR_PATTERNS may be used. As the ETRM documentation states, a repeating pattern may be applied to various purposes, and because the purpose affects the validation rules applied during pattern creation, it is necessary to explicitly register the valid purposes associated with each pattern. This table therefore acts as a controlling catalog that constrains how a given pattern can be consumed by downstream scheduling, absence, and workload functionality.

Within the EBS 12.1.1 and 12.2.2 data model, the object is owned by the HR schema and maps to FND Design Data PER.HR_PATTERN_PURPOSES. It resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 20, consistent with typical transactional data objects. From a Data Vault modeling perspective, the mined relationship structure classifies this object as satellite-leaning: it hangs off the HR_PATTERNS parent through a foreign key and carries descriptive attributes keyed to that parent, which is characteristic of a satellite attached to a hub. This is a heuristic suggestion rather than a documented Data Vault implementation.

Key Information Stored

The table contains nine documented columns. The most important are:

  • PATTERN_PURPOSE_ID — System-generated numeric surrogate primary key, enforced by the HR_PATTERN_PURPOSES_PK unique index. This is the column most frequently searched as hr_pattern_purposes_pk.
  • PATTERN_PURPOSE — VARCHAR2(30) value describing the purpose for which the parent pattern may be used. Validated against the PATTERN PURPOSE lookup type. This column is part of the business-key candidate.
  • PATTERN_ID — Numeric foreign key to HR_PATTERNS, identifying the parent repeating pattern. This column is also part of the business-key candidate.
  • OBJECT_VERSION_NUMBER — Optimistic locking counter that increments by one on each row update.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — Standard Who columns capturing audit and session context for row creation and maintenance.

The surrogate primary key is PATTERN_PURPOSE_ID. The business-key candidate is the unique index HR_PATTERN_PURPOSES_UK1, defined on the combination (PATTERN_PURPOSE, PATTERN_ID). Note that the relationship metadata also cites HR_PAP_UK01 as the primary key over (PATTERN_PURPOSE, PATTERN_ID); the physical unique index documented in ETRM 12.2.2 is HR_PATTERN_PURPOSES_UK1. A non-unique foreign key index, HR_PATTERN_PURPOSES_FK1, supports the PATTERN_ID join to HR_PATTERNS.

Common Use Cases and Queries

Typical use cases include validating whether a pattern supports a requested purpose before scheduling logic is applied, reporting on which purposes are enabled for a pattern, and auditing changes to pattern configuration.

A retrieval query follows the documented form:

  • SELECT PATTERN_PURPOSE_ID, PATTERN_PURPOSE, PATTERN_ID, OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE FROM HR.HR_PATTERN_PURPOSES;

To list the purposes for a specific pattern:

  • SELECT PATTERN_PURPOSE FROM HR.HR_PATTERN_PURPOSES WHERE PATTERN_ID = :pattern_id;

To validate a business key before insert or update:

  • SELECT PATTERN_PURPOSE_ID FROM HR.HR_PATTERN_PURPOSES WHERE PATTERN_PURPOSE = :purpose AND PATTERN_ID = :pattern_id;

For reporting, joining to HR_PATTERNS via PATTERN_ID provides pattern metadata alongside the purpose, supporting configuration and migration audits.

Related Objects

  • HR.HR_PATTERNS — Parent object referenced by the PATTERN_ID foreign key; the primary related table.
  • APPS.HR_PATTERN_PURPOSES — APPS-synonym view providing the application-layer access point.
  • HR_PATTERN_PURPOSES_WHO — Who-column maintenance view documented as referencing this table.
  • HR_PATTERN_PURPOSES_PK, HR_PATTERN_PURPOSES_UK1, HR_PATTERN_PURPOSES_FK1 — Supporting indexes on the surrogate key, business key, and foreign key respectively.
  • PATTERN PURPOSE lookup — Lookup type validating the PATTERN_PURPOSE column.

The object references no database objects other than HR_PATTERNS and is referenced by the APPS synonyms and Who view noted in the ETRM dependency listing.