Oracle HCM HDL Integration Error Debugger — 3-Table Debug Chain

1. HDL Architecture & Failure Modes

Oracle HCM Data Loader (HDL) processes .dat files through a multi-stage pipeline. When a load fails, Oracle stores the failure context in three tables that form a debug chain. Most support calls fail to use all three — leading to incomplete diagnosis and repeated submission failures.

Upload .dat File

→

HRC_INTEGRATION_BATCHES

→

HRC_INTEGRATION_DETAILS

→

HRC_INTEGRATION_ERROR_MESSAGES

→

Business Object Tables

Each layer tells you something different:

HRC_INTEGRATION_BATCHES: Was the file accepted at all? Batch-level status (COMPLETED, FAILED, PROCESSING). This is the first table to check — if the batch never made it here, the upload itself failed (network, file format, authentication).
HRC_INTEGRATION_DETAILS: Row-level processing results. Each record in your .dat file generates one or more rows here. This is where you find out which records failed vs. succeeded.
HRC_INTEGRATION_ERROR_MESSAGES: The actual error text, error code, and attribute-level context for each failed row. This is the table most people miss — they stop at DETAILS but the actionable message is here.

⚠️

Licensing note: These tables are accessible via Oracle HCM Cloud database views through HCM Extracts or direct OTBI subject area "Integration" queries. Direct SQL access requires a BI or HCM Reporting role. In OTBI, use the HCM Integration subject area.

2. Table 1: HRC_INTEGRATION_BATCHES

The entry point of every HDL load. One row per .dat file submitted.

Column	Type	Description
`INTEGRATION_BATCH_ID`	NUMBER	Primary key — join key to DETAILS table
`INTEGRATION_BATCH_NAME`	VARCHAR2	Name you assigned on upload, or system-generated
`BATCH_STATUS`	VARCHAR2	COMPLETED, FAILED, PROCESSING, WAITING, CANCELLED
`OBJECT_NAME`	VARCHAR2	HDL business object: Worker, Assignment, Salary, etc.
`STARTED_ON`	TIMESTAMP	When processing began
`COMPLETED_ON`	TIMESTAMP	When processing finished (null if still running)
`TOTAL_RECORDS`	NUMBER	Total rows in the submitted file
`SUCCESSFUL_RECORDS`	NUMBER	Rows that loaded successfully
`FAILED_RECORDS`	NUMBER	Rows that failed — if > 0, drill into DETAILS
`SOURCE_FILE_NAME`	VARCHAR2	Original .dat filename
`CREATED_BY`	VARCHAR2	User who submitted the load

3. Table 2: HRC_INTEGRATION_DETAILS

Row-level processing status. One row per record in the .dat file.

Column	Type	Description
`INTEGRATION_DETAIL_ID`	NUMBER	PK — join key to ERROR_MESSAGES
`INTEGRATION_BATCH_ID`	NUMBER	FK to HRC_INTEGRATION_BATCHES
`RECORD_STATUS`	VARCHAR2	SUCCESS, FAILED, WARNING, SKIPPED
`SOURCE_RECORD_NUMBER`	NUMBER	Line number in the .dat file — use to locate the bad row
`PERSON_NUMBER`	VARCHAR2	Person number if applicable (null for org/position loads)
`EFFECTIVE_START_DATE`	DATE	Effective date of the record being loaded
`ACTION_TYPE`	VARCHAR2	MERGE, DELETE, UPDATE — from the .dat METADATA line

4. Table 3: HRC_INTEGRATION_ERROR_MESSAGES

The actual error messages. This is the table most implementers skip — but it's the only place you get the specific attribute, error code, and human-readable fix guidance.

Column	Type	Description
`MESSAGE_ID`	NUMBER	PK
`INTEGRATION_DETAIL_ID`	NUMBER	FK to HRC_INTEGRATION_DETAILS
`MESSAGE_TYPE`	VARCHAR2	ERROR, WARNING, INFORMATION
`MESSAGE_TEXT`	VARCHAR2	Full error text — this is what you want
`MESSAGE_NAME`	VARCHAR2	Oracle message code (e.g. HR_WORKER_NOT_FOUND)
`ATTRIBUTE_NAME`	VARCHAR2	The specific column in the .dat file that caused the error
`ATTRIBUTE_VALUE`	VARCHAR2	The value that was rejected

5. Query 1: Batch Status Dashboard

First query to run after any HDL submission. Shows all batches from the last 7 days with success/failure counts and processing time.

Query 1 — HDL Batch Status Dashboard

-- HDL batch status for the last 7 days
SELECT
    hib.integration_batch_id,
    hib.integration_batch_name,
    hib.object_name,
    hib.batch_status,
    hib.total_records,
    hib.successful_records,
    hib.failed_records,
    hib.started_on,
    hib.completed_on,
    ROUND(EXTRACT(SECOND FROM
        (hib.completed_on - hib.started_on) DAY(3) TO SECOND(0)
    ) / 60, 1)                              AS processing_minutes,
    hib.source_file_name,
    hib.created_by
FROM
    hrc_integration_batches  hib
WHERE
    hib.started_on >= SYSDATE - 7
ORDER BY
    hib.started_on DESC;

6. Query 2: All Failed Rows with Error Messages

The core debug query. Pass in a batch ID (from Query 1) to get every failed row with its error text, the bad attribute, and the bad value — all in one result set.

Query 2 — All Failed Rows with Full Error Context

-- Full error detail for a specific batch
-- Replace :batch_id with the INTEGRATION_BATCH_ID from Query 1
SELECT
    hid.source_record_number,
    hid.person_number,
    hid.effective_start_date,
    hid.action_type,
    hid.record_status,
    hiem.message_type,
    hiem.message_name,
    hiem.message_text,
    hiem.attribute_name,
    hiem.attribute_value
FROM
    hrc_integration_batches     hib
    JOIN hrc_integration_details     hid  ON  hid.integration_batch_id = hib.integration_batch_id
    JOIN hrc_integration_error_messages hiem
                                         ON  hiem.integration_detail_id = hid.integration_detail_id
WHERE
    hib.integration_batch_id = :batch_id
    AND hid.record_status   = 'FAILED'
    AND hiem.message_type   = 'ERROR'
ORDER BY
    hid.source_record_number,
    hiem.message_id;

7. Query 3: Error Message Frequency Summary

When a batch has thousands of failures, you don't need every row — you need the top error types. This query groups by MESSAGE_NAME so you can fix the most common root cause first.

Query 3 — Error Frequency by Message Code (Triage View)

-- Top errors by frequency for a given batch
SELECT
    hiem.message_name,
    hiem.attribute_name,
    COUNT(*)                      AS occurrences,
    MAX(hiem.message_text)         AS sample_message_text,
    LISTAGG(
        SUBSTR(hiem.attribute_value, 1, 50), ', '
    ) WITHIN GROUP (ORDER BY hiem.message_id)
                                  AS sample_bad_values
FROM
    hrc_integration_batches          hib
    JOIN hrc_integration_details     hid  ON  hid.integration_batch_id = hib.integration_batch_id
    JOIN hrc_integration_error_messages hiem
                                         ON  hiem.integration_detail_id = hid.integration_detail_id
WHERE
    hib.integration_batch_id = :batch_id
    AND hid.record_status   = 'FAILED'
    AND hiem.message_type   = 'ERROR'
GROUP BY
    hiem.message_name,
    hiem.attribute_name
ORDER BY
    occurrences DESC;

8. Query 4: Partially Loaded Batches

Finds batches where some records succeeded and some failed — the most dangerous state, because a partial load can leave your data model in an inconsistent state (a Worker object loaded but the associated Assignment failed).

Query 4 — Partially Loaded Batches (Data Integrity Risk)

-- Batches with both successes and failures — review before resubmit
SELECT
    hib.integration_batch_id,
    hib.integration_batch_name,
    hib.object_name,
    hib.batch_status,
    hib.total_records,
    hib.successful_records,
    hib.failed_records,
    ROUND(hib.successful_records / NULLIF(hib.total_records, 0) * 100, 1)
                                    AS success_rate_pct,
    hib.started_on,
    hib.created_by
FROM
    hrc_integration_batches  hib
WHERE
    hib.failed_records     > 0
    AND hib.successful_records > 0
    AND hib.started_on     >= SYSDATE - 30
ORDER BY
    hib.started_on DESC;

🚨

Critical: Never resubmit a full batch after a partial success. Extract only the failed rows (using SOURCE_RECORD_NUMBER from Query 2), fix them, and submit a corrective file. Resubmitting the full batch will MERGE duplicate successful records.

9. Query 5: Failed Row with Person Context

When a worker's assignment fails, you need to see their current state in Oracle to understand why — are they active? Do they have a work relationship? This query joins the error row back to PER_ALL_PEOPLE_F to give you full person context alongside the error.

Query 5 — Failed HDL Rows with Current Person State

-- Joins HDL failures back to current person data for root cause analysis
SELECT
    hid.source_record_number,
    hid.person_number,
    hid.effective_start_date                  AS hdl_effective_date,
    hiem.message_name,
    hiem.message_text,
    hiem.attribute_name,
    hiem.attribute_value,
    -- Current state from Oracle
    papf.display_name,
    papf.date_of_birth,
    paam.assignment_status_type               AS current_assignment_status,
    paam.assignment_type                      AS current_assignment_type,
    ppos.date_start                           AS work_rel_start_date,
    ppos.actual_termination_date              AS term_date
FROM
    hrc_integration_batches          hib
    JOIN hrc_integration_details     hid  ON  hid.integration_batch_id  = hib.integration_batch_id
    JOIN hrc_integration_error_messages hiem
                                         ON  hiem.integration_detail_id = hid.integration_detail_id
    LEFT JOIN per_all_people_f       papf ON  papf.person_number         = hid.person_number
                                         AND  SYSDATE BETWEEN papf.effective_start_date
                                                              AND papf.effective_end_date
    LEFT JOIN per_all_assignments_m  paam ON  paam.person_id             = papf.person_id
                                         AND  SYSDATE BETWEEN paam.effective_start_date
                                                              AND paam.effective_end_date
                                         AND  paam.primary_flag          = 'Y'
                                         AND  paam.effective_latest_change = 'Y'
    LEFT JOIN per_periods_of_service ppos ON  ppos.person_id             = papf.person_id
                                         AND  ppos.actual_termination_date IS NULL
WHERE
    hib.integration_batch_id = :batch_id
    AND hid.record_status   = 'FAILED'
    AND hiem.message_type   = 'ERROR'
ORDER BY
    hid.source_record_number;

10. Query 6: Integration History by Object

Shows HDL load history for a specific business object over the last 90 days. Use this to identify load frequency, failure trends, and which users are submitting the most failed loads — often points to a broken upstream process.

Query 6 — Integration Load History by Business Object

-- History of all loads for a given HDL object type
-- :object_name = 'Worker', 'Assignment', 'Salary', 'PersonAddress', etc.
SELECT
    TRUNC(hib.started_on, 'MM')              AS load_month,
    hib.created_by,
    hib.batch_status,
    COUNT(*)                                 AS batch_count,
    SUM(hib.total_records)                   AS total_records,
    SUM(hib.successful_records)              AS total_success,
    SUM(hib.failed_records)                  AS total_failed,
    ROUND(AVG(
        EXTRACT(SECOND FROM
            (hib.completed_on - hib.started_on) DAY(3) TO SECOND(0))
    ) / 60, 1)                              AS avg_processing_min
FROM
    hrc_integration_batches  hib
WHERE
    hib.object_name  = :object_name
    AND hib.started_on >= SYSDATE - 90
GROUP BY
    TRUNC(hib.started_on, 'MM'),
    hib.created_by,
    hib.batch_status
ORDER BY
    load_month DESC,
    total_failed DESC;

11. Common HDL Error Patterns & Root Causes

MESSAGE_NAME	Cause	Fix
`HR_WORKER_NOT_FOUND`	`PersonNumber` in .dat file doesn't exist in Oracle, or the effective date predates the person's hire date	Verify person number with a `PER_ALL_PEOPLE_F` lookup; check `DATE_START` in `PER_PERIODS_OF_SERVICE`
`HR_ASSIGNMENT_NOT_FOUND`	Assignment ID referenced in the payload doesn't exist or belongs to a different person	Use person number + effective date to re-derive assignment; avoid hardcoded assignment IDs
`HR_WORK_RELATIONSHIP_ACTIVE`	Attempting to create a new work relationship when an active one already exists	Terminate the existing work relationship first, or use a MERGE action to update it
`HR_INVALID_LOOKUP_CODE`	A lookup code value doesn't exist in the target environment (common in env migrations)	Check `FND_LOOKUP_VALUES` for the specific lookup type; add missing lookup codes via Manage Lookups
`HR_DATE_OVERLAP`	Effective date range overlaps an existing date-effective row	Either correct-as-of (update the existing row) or end-date the prior row before inserting a new one
`PAY_ORG_PAY_METHOD_NOT_FOUND`	Payroll definition or payment method referenced in a payroll assignment doesn't exist	Load the payroll definition via Manage Payroll Definitions first, then resubmit the assignment
`HR_POSITION_NOT_FOUND`	Position referenced in the assignment row doesn't exist or is end-dated before the effective start date	Verify position status and effective dates in `HR_ALL_POSITIONS_F`
`HR_REQUISITION_NOT_FOUND`	For recruiting loads: offer letter or requisition ID doesn't match	Only applicable for ORC data; verify via `IRC_REQUISITIONS`

Debug Decision Tree

Run Query 1 to find the batch ID and confirm it failed (not just PROCESSING).
Run Query 3 on that batch ID to get the top error types by frequency. Pick the top error.
Run Query 2 filtered to that MESSAGE_NAME to get specific rows and bad attribute values.
For person-related errors, run Query 5 to see the current Oracle state for those person numbers.
Check for Query 4 (partial success) before fixing — if some rows loaded, extract only the failed rows for the corrective file.
After fix, verify the resubmission using Query 6 to confirm improved success rates over time.

✅

Pro tip: Add INTEGRATION_BATCH_ID to your HDL submission logging. When support asks "what batch ID was that?", you'll have it. Store it in a load log table with the submission timestamp, object name, and submitting user — one row per batch.

Need the Full Oracle HCM SQL Library?

Beyond HDL debugging — the SQL Query Library has 7 production queries for active employees, headcount, turnover, absence balances, and salary reports. All with proper date-effective joins.

View SQL Query Library →

Oracle HDL Integration Error Debugger

Table of Contents