Search Results hr_pattern_purpose_usages_pk

Overview

The HR_PATTERN_PURPOSE_USAGES table resides in the HR schema and is owned by the PER (Human Resources) product module. In Oracle EBS 12.1.1 and 12.2.2, it functions as a reference and validation table that defines the valid combinations of entities and pattern purposes used by the HR pattern engine. Each row establishes an approved pairing of an entity name with a pattern purpose, optionally scoped by hierarchy level, and acts as the gatekeeper that determines which pattern-purpose configurations the application will accept for downstream processing.

Because it is referenced by HR_CALENDAR_USAGES through the PURPOSE_USAGE_ID column, this table behaves as an authoritative source of purpose-usage definitions consumed by functional calendar and pattern logic. Under a heuristic Data Vault classification mined from its foreign-key structure, the table leans toward a hub role: it stores a distinct, uniquely identified set of business keys (the purpose usage itself) that other tables reference. This classification should be treated as a modeling suggestion rather than a mandated design, since the table also carries descriptive attributes that resemble satellite content.

Key Information Stored

The table contains ten documented columns. The most significant are summarized below:

• PURPOSE_USAGE_ID — The surrogate primary key, enforced by HR_PATTERN_PURPOSE_USAGES_PK. It uniquely identifies each valid entity/pattern-purpose pairing and is the column referenced by dependent tables.
• ENTITY_NAME — The name of the entity to which the pattern purpose applies. Together with PATTERN_PURPOSE it forms the business-key candidate HR_PATTERN_PURPOSE_USAGES_UK1.
• PATTERN_PURPOSE — The purpose code or label assigned to the pattern. It participates in two unique constraints: HR_PATTERN_PURPOSE_USAGES_UK1 (with ENTITY_NAME) and HR_PATTERN_PURPOSE_USAGES_UK2 (with HIERARCHY_LEVEL).
• HIERARCHY_LEVEL — The hierarchy level at which the pattern purpose applies, allowing the same purpose to be qualified by organizational or structural depth.
• OBJECT_VERSION_NUMBER — Supports optimistic locking, incremented on each update to prevent concurrent modification conflicts.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and LAST_UPDATE_DATE provide standard Oracle EBS WHO-column auditability, capturing who created, who last updated, the login context, and the corresponding timestamps.

The distinction between the surrogate key and the business keys matters during integration and data conversion: external systems typically supply ENTITY_NAME, PATTERN_PURPOSE, and HIERARCHY_LEVEL, and the surrogate PURPOSE_USAGE_ID is generated internally.

Common Use Cases and Queries

Report developers and integrations query this table to enumerate the valid pattern-purpose combinations available for a given entity, to validate incoming configuration data, or to resolve the PURPOSE_USAGE_ID needed for calendar usage setup. A representative query to list valid usages for an entity is:

• SELECT purpose_usage_id, entity_name, pattern_purpose, hierarchy_level FROM hr_pattern_purpose_usages WHERE entity_name = :p_entity ORDER BY pattern_purpose, hierarchy_level;

A join to dependent usages confirms where a purpose is actually consumed:

• SELECT p.entity_name, p.pattern_purpose, c.purpose_usage_id FROM hr_pattern_purpose_usages p, hr_calendar_usages c WHERE p.purpose_usage_id = c.purpose_usage_id;

Because the table is small and reference-oriented, it is commonly extracted in full for data-warehouse dimension loading or cached in application lookup logic during batch processing.

Related Objects

The principal dependent object is HR_CALENDAR_USAGES, which references HR_PATTERN_PURPOSE_USAGES via HR_CALENDAR_USAGES.PURPOSE_USAGE_ID, making this table a parent in that relationship. The table is also associated with the unique indexes HR_PATTERN_PURPOSE_USAGES_PK, HR_PATTERN_PURPOSE_USAGES_UK1, and HR_PATTERN_PURPOSE_USAGES_UK2, which enforce integrity over the surrogate and business keys. Additional related objects include the standard HR WHO audit infrastructure and the Oracle EBS pattern and calendar configuration APIs and forms that consume ENTITY_NAME and PATTERN_PURPOSE values to drive valid lookups. Together, these relationships confirm the table's role as a validation hub for pattern purposes within the PER module.