Search Results hr_pattern_purpose_usages_uk1

Overview

HR.HR_PATTERN_PURPOSE_USAGES is a reference and validation table in the Oracle E-Business Suite Human Resources (HR) schema, registered under FND Design Data as PER.HR_PATTERN_PURPOSE_USAGES. It stores the predefined set of valid combinations of schedulable entities and pattern purposes used by the work pattern framework. Its role is to constrain which purpose values may be associated with which entity when work patterns are defined and inherited across an enterprise hierarchy. For example, the purpose Resource Schedule may be valid only with the entities Location and Schedulable Resource, whereas the purpose Work Pattern may be valid with Organization, Person, and Assignment. Each row specifies exactly one valid pairing of a purpose with an entity, together with the hierarchy level at which that pairing applies.

Under the Data Vault classification heuristic derived from its foreign-key structure, this object is characterized as hub-leaning. In modeling terms, the unique combination of attribute columns (entity name and pattern purpose) functions as a natural business key, making the table suitable for treatment as a hub or reference hub rather than a transactional link or satellite.

Key Information Stored

The table is documented with ten columns in Oracle EBS 12.2.2 and resides in the APPS_TS_TX_DATA tablespace with PCTFREE 20. The most significant columns are:

• PURPOSE_USAGE_ID (NUMBER) — System-generated surrogate primary key, enforced by HR_PATTERN_PURPOSE_USAGES_PK. This is the column referenced by dependent tables.
• ENTITY_NAME (VARCHAR2(30), mandatory) — The schedulable entity to which the purpose applies, validated against the SCHEDULABLE ENTITY lookup.
• PATTERN_PURPOSE (VARCHAR2(30), mandatory) — The purpose of a pattern that is valid with the entity, validated against the PATTERN PURPOSE lookup.
• HIERARCHY_LEVEL (NUMBER) — The level at which the entity/purpose combination applies, reflecting that patterns may be inherited from higher-level entities.
• OBJECT_VERSION_NUMBER (NUMBER) — Optimistic locking counter incremented on each row update.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — Standard WHO audit columns present on all transactional and reference tables in the HR schema.

Two composite unique indexes document additional business-key candidates: HR_PATTERN_PURPOSE_USAGES_UK1 on (ENTITY_NAME, PATTERN_PURPOSE), which guarantees that no entity/purpose pairing is duplicated, and HR_PATTERN_PURPOSE_USAGES_UK2 on (PATTERN_PURPOSE, HIERARCHY_LEVEL), which governs the inheritance rule per purpose per level. The user query hr_pattern_purpose_usages_uk2 refers directly to this second unique index.

Common Use Cases and Queries

Because it drives validation and lookup behavior, the table is typically consulted when resolving the valid purposes for a given entity, when building LOV queries for pattern definition screens, or when auditing the hierarchy levels at which a purpose is permitted. A typical retrieval joins the lookup meanings:

• Discover all purposes valid for a given entity: SELECT pattern_purpose FROM hr_pattern_purpose_usages WHERE entity_name = :p_entity_name;
• Confirm the hierarchy levels permitted for a purpose: SELECT hierarchy_level FROM hr_pattern_purpose_usages WHERE pattern_purpose = :p_purpose ORDER BY hierarchy_level;
• Join to HR_CALENDAR_USAGES to see which usage records rely on a given purpose definition: SELECT cu.* FROM hr_calendar_usages cu, hr_pattern_purpose_usages ppu WHERE cu.purpose_usage_id = ppu.purpose_usage_id;
• Integrity reporting for orphaned usages: identify rows in HR_CALENDAR_USAGES whose PURPOSE_USAGE_ID has no matching definition.

Related Objects

The principal documented dependency is the foreign key from HR_CALENDAR_USAGES.PURPOSE_USAGE_ID to HR_PATTERN_PURPOSE_USAGES.PURPOSE_USAGE_ID. Related objects of significance include:

• HR.HR_CALENDAR_USAGES — Child table referencing PURPOSE_USAGE_ID; stores actual usages of purposes against calendars.
• HR.HR_PATTERN_PURPOSE_USAGES_PK — Primary-key index on PURPOSE_USAGE_ID.
• HR_PATTERN_PURPOSE_USAGES_UK1 / UK2 — Unique indexes enforcing business keys.
• FND Lookups — SCHEDULABLE ENTITY and PATTERN PURPOSE lookup types validating ENTITY_NAME and PATTERN_PURPOSE.
• PER Work Pattern Setup / HR Pattern Definition forms — Application layer that reads this table to restrict user entry.