Search Results hr_api_user_hook

Overview

The HR_API_USER_HOOK_REPORTS table is a repository object owned by the HR schema within the PER (Human Resources) product family of Oracle E-Business Suite. Its documented purpose is narrow and specific: it holds error information generated by the HR_API_USER_HOOK mechanism whenever such errors occur. In the absence of user hook errors, the table retains no information, functioning effectively as a diagnostic or exception log rather than a transactional business entity.

The HR_API_USER_HOOK construct is the extensibility framework through which customers attach custom PL/SQL logic to the standard HR API processing flow. When that custom code raises an error or fails validation, the runtime captures the resulting diagnostic text into this table so that it can be surfaced to the calling process or investigated after the fact. This design keeps failure diagnostics isolated from the main transactional tables, ensuring that routine HR API operations are not polluted by exception data.

Under the heuristic Data Vault classification supplied in the metadata, this object is modeled as standalone. No foreign key relationships were mined from its structure, which supports treating it as an independent diagnostic satellite keyed by its own session identifier rather than as a hub or link participating in a broader integration model. The classification is a suggestion for analytical modeling; the table does not enforce relational dependencies on other HR entities.

Key Information Stored

The table is documented with four physical columns in the 12.2.2 ETRM schema. The most significant of these are:

• SESSION_ID — Identifies the API session or invocation context under which the user hook error was raised. This is the primary grouping attribute for correlating multiple error lines produced by a single call.
• LINE — A sequence number that orders the individual error or informational lines captured within a given session. Together with SESSION_ID it forms the first component of the primary key.
• TEXT — The message payload holding the actual error information emitted by the user hook. In practice this carries the Oracle error text or the custom message raised by the customer's hook code.
• ZD_EDITION_NAME — The editioning column introduced by the EBS 12.2 online patching architecture. It participates in the unique index and is transparent to runtime queries under normal edition context.

The surrogate primary key is defined by HR_API_USER_HOOK_REPORTS_PK, documented on the column combination (SESSION_ID, LINE). The unique index HR_API_USER_HOOK_REPORTS_PK extends this to (SESSION_ID, LINE, ZD_EDITION_NAME), reflecting the edition-aware variant of the same business key. Because the key is composed entirely of natural identifying attributes, no separate artificial surrogate column exists; SESSION_ID and LINE together serve as both the business key and the physical access path.

Common Use Cases and Queries

The principal use case is post-mortem diagnosis of failed HR API calls where custom user hook logic is in use. Support and technical teams query the table after a failed hire, assignment update, or termination to retrieve the exact error text recorded for the failing session.

SELECT session_id, line, text
FROM   hr.hr_api_user_hook_reports
WHERE  session_id = :p_session_id
ORDER  BY line;

A second scenario is identifying which user hooks are failing most frequently across a period, useful when validating a customization before production promotion. Because the table stores only failures, any row returned indicates a defect or environment mismatch worth investigation.

SELECT session_id, COUNT(*) error_lines
FROM   hr.hr_api_user_hook_reports
GROUP  BY session_id
ORDER  BY error_lines DESC;

Reporting usage is deliberately lightweight. Administrators may join SESSION_ID back to their own audit or logging tables to associate each error block with the user, concurrent request, or batch job that triggered the API. Because the table is cleared or remains empty on success, it should never be treated as a complete audit trail of API activity.

Related Objects

The metadata documents no foreign key relationships, and the heuristic classification confirms the object is standalone. Dependency is therefore logical rather than enforced. The most significant related objects are:

• HR_API_USER_HOOK — The user hook procedure package that generates the error text written into the TEXT column.
• HR_API_USER_HOOK_REPORTS_PK — The primary key constraint and unique index defining the (SESSION_ID, LINE, ZD_EDITION_NAME) access path.
• HR_API_HOOK_BUSINESS_GROUP — Configuration table controlling which business groups and hooks are active, and therefore which sessions can produce report rows.
• HR_API_HOOKS — Metadata describing the available hook points invoked during API processing.
• PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F — The primary HR entities whose API maintenance commonly triggers user hook execution.
• FND_CONCURRENT_REQUESTS — Frequently joined indirectly to associate error sessions with the concurrent program that invoked the API.

Because no database-level joins are enforced, any correlation between SESSION_ID and external logging must be established through application-level conventions.