Search Results hr_api_batch_message_lines_pk

Overview

HR.HR_API_BATCH_MESSAGE_LINES is a transactional log table in the Oracle E-Business Suite Human Resources schema that captures the results and messages generated during batch API processing. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and is registered in FND Design Data under the product short name PER as PER.HR_API_BATCH_MESSAGE_LINES. Its purpose is diagnostic and audit-oriented: each row records the outcome of a single API invocation executed inside a batch run, preserving both success/failure status and the associated error diagnostics returned by the PL/SQL engine.

From a dimensional modeling perspective, the mined relationship structure classifies this object heuristically as standalone. The only documented foreign key, SECURITY_GROUP_ID to FND_SECURITY_GROUPS, is a multi-tenant security reference rather than a true business entity relationship, so the table does not naturally resolve into a hub, link, or satellite pattern. It is best treated as a fact-like event log anchored by its surrogate key, where each row is an atomic processing event rather than a description of a master entity. Modelers who wish to impose a Data Vault structure would typically treat it as a satellite of a batch run hub, with the batch run number acting as the business key.

Key Information Stored

The table contains nine documented columns, of which the most significant for querying and reporting are the following:

• LINE_ID — System-generated numeric primary key. This is the surrogate identifier and the single column of the unique index HR_API_BATCH_MESSAGE_LINES_PK, which is the table's only documented uniqueness constraint. No separate business-key candidate is defined in the metadata.
• API_NAME — VARCHAR2(61) holding the name of the API that was invoked for the line.
• BATCH_RUN_NUMBER — Numeric value identifying which batch run the line belongs to. This is the grouping column and the only indexed non-primary-key access path, via the nonunique index HR_API_BATCH_MESSAGE_LINES_N1.
• STATUS — VARCHAR2(30) indicating whether the API call succeeded or failed.
• ERROR_MESSAGE — VARCHAR2(512) holding the SQLERRM text when STATUS is Fail.
• ERROR_NUMBER — Numeric SQLCODE captured when STATUS is Fail.
• EXTENDED_ERROR_MESSAGE — VARCHAR2(2000) carrying the extended diagnostic text when STATUS is Fail.
• SOURCE_ROW_INFORMATION — VARCHAR2(2000) free text that uniquely identifies the source row information, allowing the failing record to be traced back to the originating data.
• SECURITY_GROUP_ID — NUMBER(15) foreign key to FND_SECURITY_GROUPS, enforcing the multi-org/multi-tenant access boundary.

Because BATCH_RUN_NUMBER is indexed but LINE_ID is the sole unique key, a given batch run may produce many lines—one per API invocation—and the primary key, not the batch run, guarantees row uniqueness.

Common Use Cases and Queries

The most frequent operational scenario is post-batch error triage: after a batch API job completes, support staff retrieve the failure rows for the run to determine which source records were rejected and why. A canonical query filters on the batch run and status:

• SELECT LINE_ID, API_NAME, STATUS, ERROR_NUMBER, ERROR_MESSAGE, EXTENDED_ERROR_MESSAGE, SOURCE_ROW_INFORMATION FROM HR.HR_API_BATCH_MESSAGE_LINES WHERE BATCH_RUN_NUMBER = :batch_run AND STATUS = 'Fail';
• SELECT BATCH_RUN_NUMBER, STATUS, COUNT(*) FROM HR.HR_API_BATCH_MESSAGE_LINES GROUP BY BATCH_RUN_NUMBER, STATUS ORDER BY BATCH_RUN_NUMBER;

The first pattern relies on the HR_API_BATCH_MESSAGE_LINES_N1 index for efficient access; the second supports reconciliation and success-rate reporting. Joining SOURCE_ROW_INFORMATION to the originating staging or interface table allows a rejected source row to be corrected and resubmitted. Because the table can grow large across historical runs, purge or archive strategies keyed on BATCH_RUN_NUMBER are commonly applied to the APPS_TS_TX_DATA tablespace.

Related Objects

The documented dependency set for HR_API_BATCH_MESSAGE_LINES is deliberately narrow:

• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID; the only documented foreign key relationship and the basis for secure access control in multi-tenant deployments.
• APPS.HR_API_BATCH_MESSAGE_LINES — the APPS synonym that application code and reports use to query the HR-owned table.
• PUBLIC.HR_API_BATCH_MESSAGE_LINES — the public synonym exposing the table more broadly for ad hoc querying.

No other database objects are documented as referencing this table, and it does not itself reference any object other than through the security group column. The batch API framework that writes to it—those PER and HR batch-processing packages that accept a batch run number—is the primary producer of rows, while the primary key HR_API_BATCH_MESSAGE_LINES_PK and the supporting nonunique index HR_API_BATCH_MESSAGE_LINES_N1 are the objects that govern access to it.