Search Results hr_stu_exceptions




Overview
Explore in AI Chat

Overview

HR_STU_EXCEPTIONS is a table in the HR schema of Oracle E-Business Suite, owned by the PER (Human Resources) product. Its documented purpose is to capture error conditions that arise during the startup data process. In practical terms, it functions as a diagnostic or audit repository where the HR startup/seed process records rows it could not process, transform, or validate. Administrators and technical consultants consult this table when the startup data process completes with warnings or errors, since it isolates the offending records and their associated messages.

The ETRM metadata classifies this object, using a heuristic mined from its FK structure, as standalone — meaning it is not modeled as a Data Vault hub, link, or satellite because no foreign key relationships were detected. This classification is a modeling suggestion rather than a functional constraint: the table behaves as a self-contained exception log keyed by the affected table and record identity.

Key Information Stored

The documented physical schema in ETRM 12.2.2 consists of four columns. The most significant are:

  • TABLE_NAME — Identifies the database table in which the startup process encountered the error condition. This is the primary context column and, together with the surrogate identifier, forms part of the composite primary key.
  • SURROGATE_ID — The surrogate identifier of the offending row in the referenced table. Combined with TABLE_NAME, it pinpoints the exact record that failed processing.
  • EXCEPTION_TEXT — The descriptive text of the error or exception raised by the startup data process. This is the diagnostic payload of the row and completes the composite primary key.
  • TRUE_KEY — An additional documented column, typically carrying the business (true) key value of the affected record, allowing the exception to be traced back to a natural key rather than only the surrogate.

The surrogate primary key of the table is defined by the unique index HR_STU_EXCEPTIONS_PK, whose columns are (TABLE_NAME, SURROGATE_ID, EXCEPTION_TEXT). Because the same three columns form both the PK and the only documented unique index, the business-key candidate and the surrogate identity coincide here; the table does not expose a separate single-column sequence-based primary key in the documented metadata.

Common Use Cases and Queries

Typical scenarios center on triage of the HR startup data process: identifying which tables produced the most exceptions, locating the specific row behind an error, and confirming whether remediation succeeded. Representative SQL patterns include:

  • Counting exceptions by source table: SELECT table_name, COUNT(*) FROM hr.hr_stu_exceptions GROUP BY table_name ORDER BY 2 DESC;
  • Retrieving all messages for one table: SELECT surrogate_id, true_key, exception_text FROM hr.hr_stu_exceptions WHERE table_name = :p_table;
  • Locating a specific failing record: SELECT exception_text FROM hr.hr_stu_exceptions WHERE table_name = :p_table AND surrogate_id = :p_id;
  • Post-fix verification: after re-running the startup data process, re-query the table to confirm the count has dropped or the target row no longer appears.

Reporting use cases include exception dashboards for HR implementations, pre- and post-upgrade validation of seeded HR data, and extraction of exception history for support escalation to Oracle. Because rows are keyed by table and surrogate identifier, joins back to the original tables are performed dynamically on TABLE_NAME and SURROGATE_ID rather than through static foreign keys.

Related Objects

The metadata documents no foreign keys, consistent with the standalone Data Vault classification. The most significant related objects are therefore the HR startup and seed tables whose errors this log records, joined by the (TABLE_NAME, SURROGATE_ID) pair, for example HR_ALL_ORGANIZATION_UNITS, PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, HR_ORGANIZATION_UNITS, and the various HR_STU_* staging tables produced by the startup data process. Supporting objects include the primary key index HR_STU_EXCEPTIONS_PK and the PER schema APIs used to re-run or re-validate the startup data process once exceptions are corrected.