Search Results hz_imp_batch_details_u1

Overview

The AR.HZ_IMP_BATCH_DETAILS table is a technical staging and audit table within the Oracle E-Business Suite Trading Community Architecture (TCA) Registry. It stores detail information about the import of records from the interface tables into the TCA Registry, operating as a companion to the batch summary object. Each row captures a single execution of an import batch, recording how many times the batch has run, the current import status, and the identifier of the concurrent request that drove the import process. The object resides in the APPS schema, is owned by AR, and is defined with FND Design Data reference AR.HZ_IMP_BATCH_DETAILS. Its status is VALID in both Oracle EBS 12.1.1 and 12.2.2.

From a dimensional modeling perspective, the metadata classifies this object as satellite-leaning. This is a modeling suggestion rather than a physical constraint: the table records descriptive, time-versioned attributes of a batch run, keyed by the batch itself. It is best understood as a satellite of the batch hub, where BATCH_ID represents the parent key and RUN_NUMBER provides the run-level granularity. Because a single logical batch can be executed multiple times, a strictly type-one satellite model must account for the repetition implied by the composite key.

Key Information Stored

The primary key HZ_IMP_BATCH_DETAILS_PK is defined on the composite of BATCH_ID and RUN_NUMBER. A matching unique index, HZ_IMP_BATCH_DETAILS_U1 — the object the user searched for — enforces the same uniqueness constraint on (BATCH_ID, RUN_NUMBER) and resides in the APPS_TS_TX_IDX tablespace. This composite is therefore the business-key candidate for the object, distinguishing each execution of a batch.

• BATCH_ID — Interface batch identifier; the foreign key to HZ_IMP_BATCH_SUMMARY and the leading column of the unique index.
• RUN_NUMBER — The number of times the batch has run; the second column of the composite key.
• IMPORT_REQ_ID — Import request identifier for the run.
• DUP_BATCH_ID — Duplicate batch identifier; a foreign key to HZ_DUP_BATCH, used when a batch is flagged as a duplicate.
• IMPORT_STATUS — Free-text import status for the batch run.
• MAIN_CONC_REQ_ID — Request identifier for the central concurrent program, useful for tracing the parent import job.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Standard concurrent program Who columns that identify the driving batch program.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Who audit columns.

Note that only BATCH_ID and RUN_NUMBER are mandatory in the documented definition; the remaining columns carry no mandatory flag.

Common Use Cases and Queries

The most frequent use is diagnostic: determining the outcome of a TCA import batch and correlating it with the concurrent request that produced it. A typical pattern retrieves the latest run per batch:

• Status monitoring — select BATCH_ID, RUN_NUMBER, IMPORT_STATUS, and IMPORT_REQ_ID for a given batch to review the sequence of runs and their outcomes.
• Request reconciliation — join IMPORT_REQ_ID or MAIN_CONC_REQ_ID to FND_CONCURRENT_REQUESTS to obtain the phase, status, and completion time of the import job.
• Duplicate investigation — use DUP_BATCH_ID to trace batches flagged as duplicates back to HZ_DUP_BATCH.
• Re-run analysis — group by BATCH_ID and take MAX(RUN_NUMBER) to find the current state of each batch, or count runs to detect repeated failures.

A foundational query is the documented select against the table; production SQL typically restricts it by date range on CREATION_DATE or by a specific BATCH_ID to avoid full scans.

Related Objects

• AR.HZ_IMP_BATCH_SUMMARY — the parent object. HZ_IMP_BATCH_DETAILS.BATCH_ID references this table, forming the primary join path.
• HZ_DUP_BATCH — referenced by DUP_BATCH_ID for duplicate batch identification.
• FND_CONCURRENT_REQUESTS — joined via IMPORT_REQ_ID, MAIN_CONC_REQ_ID, or REQUEST_ID to inspect the import job.
• HZ_IMP_BATCH_DETAILS_U1 — the unique index on (BATCH_ID, RUN_NUMBER); the searched identifier.
• APPS-level TCA import packages and concurrent programs that populate this table during interface table processing.