Results for “hr_pattern_exceptions_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

HR_PATTERN_EXCEPTIONS_V is a valid, APPS-owned database view within the Oracle E-Business Suite Release 12.1.1 and 12.2.2 environments, belonging to the PER (Human Resources) product family. According to ETRM documentation, the view is "used to support user interface" functionality, meaning it serves as a denormalized, read-only projection that the Oracle HRMS Forms and OAF-based pages consume when rendering pattern exception data. Rather than presenting raw transactional rows, the view assembles descriptive context — pattern names, lookup meanings, and version identifiers — alongside the underlying exception records, allowing the presentation layer to display user-friendly values without additional round trips.

Because it is a view and not a table, HR_PATTERN_EXCEPTIONS_V does not store data independently. It is a reporting and integration-friendly access point that abstracts the join logic between the exception entity, its parent pattern, and the lookup that classifies the exception category. For developers conducting custom reporting, the view is a convenient single source that already resolves the foreign-key relationships that would otherwise require manual joins in a custom query.

Underlying Base Objects

The view text joins three primary sources, as documented in the ETRM excerpt:

• HR_PATTERN_EXCEPTIONS (aliased PATEX) — the driving base table containing the exception records.
• HR_PATTERNS (aliased PAT) — joined on PATTERN_ID to retrieve the parent pattern's descriptive name.
• HR_LOOKUPS (aliased LOOK) — outer-joined on EXCEPTION_CATEGORY = LOOKUP_CODE with LOOKUP_TYPE fixed to 'EXCEPTION_CATEGORY'.

The ETRM metadata also lists HR_API (PACKAGE) among the referenced base objects, reflecting the standard Oracle HRMS practice of routing inserts, updates, and deletes through the HR_API business logic layer rather than permitting direct DML against the underlying tables. This underscores that the view is intended strictly for query and display purposes; any data maintenance must be performed through the supported HRMS APIs.

Key Columns

The view exposes the following documented columns:

• EXCEPTION_ID — primary identifier for the pattern exception record.
• PATTERN_ID — foreign key to the parent pattern in HR_PATTERNS.
• EXCEPTION_CATEGORY — the lookup code classifying the exception.
• EXCEPTION_START_TIME — the effective start time of the exception, and the specific column most users query when searching for "exception_start_time."
• EXCEPTION_END_TIME — the corresponding end time, delimiting the exception window.
• EXCEPTION_NAME — the user-assigned name of the exception.
• PATTERN_NAME — the descriptive name of the parent pattern, resolved through the join to HR_PATTERNS.
• EXCEPTION_CATEGORY_MEANING — the human-readable meaning derived from HR_LOOKUPS.MEANING (the raw view text labels the column MEANING).
• OBJECT_VERSION_NUMBER — the optimistic locking token used by the OAF framework to detect concurrent updates.

Common Use Cases and Queries

Typical scenarios include auditing pattern exceptions within a date window, reporting exception categories by their display meaning, and supporting integrations that need pattern context without re-implementing the join logic.

To filter on the exception start time, a straightforward query is:

• SELECT exception_id, pattern_name, exception_name, exception_start_time, exception_end_time, exception_category_meaning FROM apps.hr_pattern_exceptions_v WHERE exception_start_time >= :p_from_date ORDER BY exception_start_time;

To list all exceptions for a specific pattern by name, one may query:

• SELECT exception_name, exception_start_time, exception_end_time, exception_category_meaning FROM apps.hr_pattern_exceptions_v WHERE pattern_name = :p_pattern_name;

Because HR_LOOKUPS is outer-joined, rows with a null or unclassified EXCEPTION_CATEGORY still surface, with the meaning column returning null — an important consideration when building reports that must not silently drop uncategorized exceptions.