Oracle HCM SQL Toolkit – Part 4: Work Schedule Assignment and Daily Availability in Oracle Cloud HCM

Oracle HCM SQL Toolkit – Part 4

Work Schedule Assignment and Daily Availability in Oracle Cloud HCM

In the earlier parts of this series, we looked at useful SQL patterns for understanding Oracle HCM data, employee populations, and technical troubleshooting.

In this fourth installment, we will focus on a very common Time and Labor / Workforce Management requirement:

How do I identify an employee’s assigned work schedule, and how do I derive the actual daily availability generated from that schedule?

These are two related but different questions.

The first query identifies the work schedule assigned to the employee assignment.

The second uses Oracle’s availability API to return the daily availability / shift details generated from the schedule configuration.

This distinction is important because a schedule assignment tells us which schedule is assigned, while the availability API tells us what that schedule translates into for a specific date range.

---

Query 1 – Employee Work Schedule Assignment

The following query returns the active work schedule assigned to an employee or contingent worker.

SELECT
    papf.person_number,
    zss.schedule_name,
    psa.resource_type,
    TO_CHAR(psa.start_date, 'MM/DD/YYYY') AS start_date,
    TO_CHAR(psa.end_date, 'MM/DD/YYYY') AS end_date,
    psa.primary_flag,
    hou.name AS legal_employer
FROM
    per_schedule_assignments psa,
    per_all_assignments_m paam,
    hr_all_organization_units hou,
    zmm_sr_schedules_tl zss,
    per_all_people_f papf
WHERE 1 = 1
  AND psa.resource_id = paam.assignment_id
  AND psa.resource_type = 'ASSIGN'
  AND paam.assignment_status_type = 'ACTIVE'
  AND paam.primary_flag = 'Y'
  AND paam.assignment_type IN ('E', 'C')
  AND paam.system_person_type IN ('EMP', 'CWK')
  AND paam.person_id = papf.person_id
  AND TRUNC(SYSDATE) BETWEEN hou.effective_start_date
                         AND hou.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date
                         AND papf.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date
                         AND paam.effective_end_date
  AND psa.schedule_id = zss.schedule_id
  AND papf.person_number = '53002239'
  AND paam.legal_entity_id = hou.organization_id
  AND zss.language = 'US';

A typical output may look like:

Person Number	Schedule Name	Start Date	End Date	Primary	Legal Employer
53002239	US Standard 8x5	01/01/2026	12/31/4712	Y	US Legal Employer

---

Why Schedule Assignment Alone Is Not Enough

One of the common misconceptions when querying schedules is assuming that the assigned schedule itself contains the employee's daily shift start and end times.

It does not always work that way.

Think of the schedule assignment as:

Employee Assignment
↓
Assigned Work Schedule
↓
Schedule Pattern / Calendar
↓
Daily Availability

The first query tells us:

Employee 53002239 is assigned to schedule X.

But if we need to answer:

What time is this employee expected to work on October 5?

we need to derive the availability generated from that schedule.

That is where the second query becomes very useful.

---

Query 2 – Deriving Daily Availability

Oracle provides the PER_AVAILABILITY_DETAILS.GET_AVAILABILITY_DETAILS table function for deriving availability for a resource over a specified date range.

SELECT
    availability.*,
    assignment.assignment_id
FROM
    per_all_assignments_f assignment,
    TABLE(
        per_availability_details.get_availability_details(
            p_resource_type      => 'ASSIGN',
            p_resource_id        => assignment.assignment_id,
            p_period_start       => TO_DATE(
                                        '2026-10-01 00:00',
                                        'yyyy-mm-dd hh24:mi'
                                    ),
            p_period_end         => TO_DATE(
                                        '2026-10-10 00:00',
                                        'yyyy-mm-dd hh24:mi'
                                    ),
            p_include_calevents  => 'Y'
        )
    ) availability
WHERE assignment.person_id IN (
        SELECT person_id
        FROM per_all_people_f
        WHERE person_number = '53002239'
        and trunc(sysdate) between effective_start_date and effective_end_date
      )
  AND assignment.assignment_type IN ('E', 'C')
  AND assignment.assignment_status_type = 'ACTIVE'
  AND TRUNC(availability.shift_start)
      BETWEEN assignment.effective_start_date
          AND assignment.effective_end_date;

Schedule Assignment tells you WHAT schedule the employee has. Availability tells you WHEN the employee is expected to work.

---

Oracle HCM SQL Toolkit – Part 4 Recap

We explored how to identify an employee’s assigned work schedule and derive date-specific daily availability using Oracle Cloud HCM schedule tables and the PER_AVAILABILITY_DETAILS API.

Oracle HCM SQL Toolkit – Part 3: ESS Scheduled Process Details and Status

Oracle HCM SQL Toolkit – Part 3: ESS Scheduled Process Details and Status

Welcome to Part 3 of the Oracle HCM SQL Toolkit, a continuing series of practical Oracle HCM SQL queries every consultant should keep handy.

In this post, we move to another area that comes up frequently in Oracle HCM troubleshooting:

Scheduled Processes and ESS request history.

Oracle HCM performs a significant amount of background processing through Enterprise Scheduler Service (ESS).

When investigating a Scheduled Process, it is often useful to know:

• What all processes are scheduled, and you don’t have access to see from the UI
• What process was submitted?
• Who submitted it?
• When was it submitted?
• When did execution actually start?
• When did it finish?
• What is its current status?
• What is the technical process name?

The Query

SELECT
    erh.requestid AS "Process ID",
    SUBSTR(
        erh.definition,
        INSTR(erh.definition, '/', -1) + 1
    ) AS "Process Name",
    erh.username AS "Submitted By",
    TO_CHAR(
        erh.submission,
        'YYYY-MM-DD HH24:MI:SS'
    ) AS "Submission Time",
    TO_CHAR(
        erh.processstart,
        'YYYY-MM-DD HH24:MI:SS'
    ) AS "Start Time",
    TO_CHAR(
        erh.processend,
        'YYYY-MM-DD HH24:MI:SS'
    ) AS "End Time",
    DECODE(
        erh.state,
        1,  'Wait',
        2,  'Ready',
        3,  'Running',
        4,  'Completed',
        5,  'Blocked',
        6,  'Hold',
        7,  'Canceling',
        9,  'Canceled',
        10, 'Error',
        11, 'Warning',
        12, 'Succeeded',
        erh.state
    ) AS "Status",
    erh.definition AS "Full Definition Path"
FROM
    fusion.ess_request_history erh
WHERE 1 = 1
  AND erh.state IN (1,6)
ORDER BY
    erh.submission DESC;

In this example, the query returns requests in Wait and Hold status because of:

erh.state IN (1,6)

ESS State Values

State	Status
1	Wait
2	Ready
3	Running
4	Completed
5	Blocked
6	Hold
7	Canceling
9	Canceled
10	Error
11	Warning
12	Succeeded

Error and Warning Requests

AND erh.state IN (10,11)

Currently Running Requests

AND erh.state = 3

Technical Process Name vs. Scheduled Process Display Name

One particularly useful detail when working with ESS is that the internal technical process name may be different from the Scheduled Process name that functional users recognize in the application.

Technical Process Name	Scheduled Process / Display Name
TCDEventInternalizingProcessJob	Generate Time Cards from Time Collection Devices
HrcCommunicationManager	Manage Communication Responses
DataSecurityAclRefresh	Compute Users ACL by Events
SyncRolesJob	Retrieve Latest LDAP Changes
ACRPLENR	Update Accrual Plan Enrollments
ACRPRC	Calculate Accruals and Balances
BENDSGEL	Reevaluate Designee Eligibility
BENMNGLE	Evaluate Life Event Participation
FlowEssJobDefn	HCM Flow Secured
PurgeSpreadsheetLoaderHistoryJob	Delete HCM Spreadsheet Data Loader Stage Table Data
PurgeExtractPayrollActionsJob	Purge Extracts Archive Data
PurgeExtractsPartitionsAuto	Purge Extracts Archive Partitions
CloudMetricsNumberOfNamedUsers	Generate Cloud Usage Metrics
TimeComplianceRulesCheckJob	Generate Time Exceptions from Compliance Rules
BENCLENR	Close Enrollment
BENCLLPSLE	Collapse Life Events
BENTEMOD	Evaluate Temporal Event Participation
BENASLFE	Assign Corrective Potential Life Event
BENMNGBENREL	Assign and Update Benefits Relationships
HcmAtomFeedPurgeJob	Purge Atom Feed Entries from Oracle Fusion Schema
PurgeObsoletedSmartActions	Purge Obsoleted Smart Action Records
HcmAlertRunPurgeJob	Purge Alert Processing and Log Entries
GenericFeatureUpgrade	Generic Feature Upgrade
SendCommunication	Send Communication
HcmPurgeSensitiveDataAccessAuditJob	Purge Sensitive Data Access Audit
GroupsEvalJob	Evaluate Group Membership
SynchronizeManagerHierarchyJob	Synchronize Person Assignments from Position

for example-

The technical ESS process name is:

TimeComplianceRulesCheckJob

You can filter ESS history using:

AND erh.definition LIKE '%TimeComplianceRulesCheckJob%'

and review when requests were submitted, when they started, how long they ran, whether requests remained waiting, and whether they ended with errors or warnings.

Oracle HCM SQL Toolkit – Part 3 Recap

This query provides a useful view of:

Scheduled Process → Technical ESS Job → Status and Execution Details

It is particularly useful when troubleshooting background processing or trying to connect the functional Scheduled Process name with the underlying ESS technical process.

Oracle HCM SQL Toolkit – Part 2: Person External Application Identifier Details

Oracle HCM SQL Toolkit – Part 2: Person External Application Identifier Details

Welcome to Part 2 of the Oracle HCM SQL Toolkit, a continuing series of practical SQL queries that Oracle HCM consultants can keep handy for day-to-day work.

In this post, we will look at another frequently useful area:

External Application Identifiers associated with a person.

Oracle HCM frequently integrates with external applications, and those applications may use identifiers that are completely different from the employee's Oracle HCM Person Number.

Knowing how to retrieve these identifiers can be extremely useful when troubleshooting integrations and reconciling employee data between systems.

The Query

SELECT
    ppn.full_name,
    papf.person_number,
    pext.ext_identifier_type,
    flkp.meaning,
    pext.ext_identifier_number,
    pext.date_from,
    pext.date_to
FROM
    per_ext_app_identifiers pext,
    per_all_people_f papf,
    fnd_common_lookups flkp,
    per_person_names_f ppn
WHERE
    TRUNC(SYSDATE) BETWEEN papf.effective_start_date
                       AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppn.effective_start_date
                       AND ppn.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pext.date_from
                       AND NVL(
                           pext.date_to,
                           TO_DATE('31/12/4712', 'DD/MM/YYYY')
                       )
AND pext.person_id = papf.person_id
AND pext.ext_identifier_type = flkp.lookup_code
AND flkp.lookup_type = 'ORA_PER_EXT_IDENTIFIER_TYPES'
AND ppn.person_id = papf.person_id
AND ppn.name_type = 'GLOBAL';

Sample Output

Full Name	Person Number	Identifier Type	Meaning	External Identifier
John Smith	100234	BENEFITS_ID	Benefits Vendor ID	BEN29823
Jane Doe	100987	LMS_ID	Learning System ID	LMS83928

Why External Identifiers Matter

Oracle HCM may identify an employee using:

Person Number = 100245

but another application might identify the same employee differently.

Oracle HCM Person Number : 100245
Benefits Vendor ID       : BEN87943
Learning System ID       : LMS45672
Identity System ID       : IAM003482

External Application Identifiers provide a mechanism for maintaining these external references against a person.

Where This Is Useful

This becomes especially helpful when Oracle HCM integrates with:

• Benefits providers
• Payroll vendors
• Learning applications
• Identity-management systems
• Time applications
• Legacy HR systems
• Custom enterprise applications

Identifier Type and Meaning

The technical identifier type comes from:

pext.ext_identifier_type

The query joins this value to:

FND_COMMON_LOOKUPS

using:

flkp.lookup_type = 'ORA_PER_EXT_IDENTIFIER_TYPES'

This gives us both the technical identifier code and the user-readable meaning.

A Common Integration Troubleshooting Scenario

Imagine a support ticket says:

Employee 100245 is missing from the benefits vendor system.

Before investigating middleware or extract logic, one simple check is:

Does employee 100245 have the expected Benefits Vendor external identifier?

If the query returns:

Person Number       : 100245
Identifier Type     : BENEFITS_ID
External Identifier : BEN87943

you now have the value that can be reconciled against the external application.

Oracle HCM SQL Toolkit – Part 2 Recap

This query provides a useful view of:

Person → External Identifier Type → External Identifier

It is particularly valuable when troubleshooting integrations or reconciling worker information between Oracle HCM and another application.

Oracle HCM SQL Toolkit – Part 1: Country, Legal Employer and Employee Count

Oracle HCM SQL Toolkit – Part 1: Country, Legal Employer and Employee Count

Over time, every Oracle HCM consultant builds a personal collection of SQL queries that become part of their everyday toolkit.

Some queries are useful for troubleshooting. Others help with reporting, validation, integrations, payroll, or simply understanding how an Oracle HCM environment is structured.

This series, Oracle HCM SQL Toolkit, is intended to build a practical library of those frequently used queries.

In Part 1, we will look at a simple but very useful query:

How many Legal Employers are configured, which countries do they belong to, and how many active employees are associated with each Legal Employer?

The Query

SELECT
    ple.name AS legal_employer,
    xep.le_information_context AS country_code,
    (
        SELECT COUNT(paaf.assignment_id)
        FROM per_all_assignments_f paaf
        WHERE paaf.legal_entity_id = ple.organization_id
          AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date
                                 AND paaf.effective_end_date
          AND paaf.primary_flag = 'Y'
          AND paaf.assignment_type = 'E'
          AND paaf.assignment_status_type_id = 1
    ) AS employee_count
FROM xle_entity_profiles xep
JOIN per_legal_employers ple
    ON xep.legal_entity_id = ple.legal_entity_id
WHERE xep.legal_employer_flag = 'Y'
  AND ple.status = 'A'
--AND xep.le_information_context = 'US'
ORDER BY ple.name;

Sample Output

Legal Employer	Country Code	Employee Count
Vision US LLC	US	12,450
Vision Canada Ltd	CA	2,180
Vision UK Ltd	GB	3,925
Vision India Pvt Ltd	IN	8,740

Why Keep This Query Handy?

This query provides a quick view of the organization's HCM footprint.

• Which Legal Employers exist
• Which country each Legal Employer belongs to
• Employee population by Legal Employer
• Which Legal Employers have large populations
• Whether any Legal Employers have unexpectedly small populations

It is useful during enterprise-structure discussions, Payroll analysis, reporting, data validation, troubleshooting, and general environment discovery.

Oracle HCM SQL Toolkit – Part 1 Recap

This query provides a simple view of:

Country → Legal Employer → Employee Count

It is easy to understand, easy to modify, and useful across many Oracle HCM functional and technical scenarios.

Oracle Cloud HCM: Recovering HDL Files and Troubleshooting Import Errors Using SQL

Introduction

One of the biggest challenges during Oracle Cloud HCM support is troubleshooting an HDL load after it has already been processed.

A common scenario looks like this:

• An HDL was loaded several weeks or months ago.
• The original .dat file is no longer available.
• The consultant who loaded it has left the project.
• A business user reports incorrect data.
• You need to determine exactly what was loaded before preparing a correction HDL.

Since HDL imports are processed through Oracle's internal Import and Load Data framework, many consultants assume there is no way to retrieve the original file contents.

Fortunately, Oracle stores the imported HDL data in several HCM Data Loader tables. With a few SQL queries, you can reconstruct the original HDL file, review each physical line, and analyze errors generated during processing.

In this article, we will look at two practical SQL techniques:

• Reconstructing previously loaded HDL files
• Viewing HDL errors at every processing stage

---

Business Scenario

Imagine receiving the following request from Payroll:

An employee's salary was loaded incorrectly three months ago. We don't have the original HDL file anymore, but we need to understand what was imported before creating a correction.

Without the original HDL file, most teams begin recreating the data manually.

Instead, Oracle already stores every imported HDL line inside the database. With the appropriate SQL query, you can rebuild the original .dat file almost exactly as it was loaded.

---

Recovering Previously Loaded HDL Files

Oracle stores every HDL file line in the HRC_DL_FILE_LINES table.

Using the Data Set and Business Object tables, we can reconstruct the HDL file.

The following SQL returns every line in the HDL file in the original sequence.

SELECT
    hdds.ucm_content_id,
    hdds.data_set_name,
    hddsbo.data_file_name,
    hdfl.seq_num AS line_sequence,
    hdfl.text AS hdl_line_text
FROM hrc_dl_data_sets hdds,
     hrc_dl_data_set_bus_objs hddsbo,
     hrc_dl_file_lines hdfl
WHERE 1 = 1
  AND hdds.data_set_id = hddsbo.data_set_id
  AND hddsbo.data_set_bus_obj_id = hdfl.data_set_bus_obj_id
  -- AND hdds.ucm_content_id = 'UCMFA06411541'
  AND hdds.data_set_name = 'Salary_6278_06302026.zip'
ORDER BY
    hddsbo.data_file_name,
    hdfl.seq_num;

---

Searching by UCM Content ID

If you know the uploaded UCM document ID instead of the zip file name, modify the WHERE clause.

WHERE hdds.ucm_content_id = 'UCMFA06411541'

This is particularly useful when reviewing historical HDL loads from production.

---

Typical Output

UCM Content ID	Data File	Sequence	HDL Line
UCMFA06411541	Salary.dat	1	METADATA...
UCMFA06411541	Salary.dat	2	MERGE...
UCMFA06411541	Salary.dat	3	MERGE...

Because the results are returned in sequence order, they can easily be copied into a new .dat file when preparing correction HDL.

---

Why This Query Is Useful

This query is useful in many implementation and support situations, such as:

• Original HDL file lost
• Reviewing historical data loads
• Building correction HDL files
• Comparing production versus test loads
• Investigating unexpected data changes
• Performing audit activities

Instead of recreating HDL manually, you can extract what Oracle already imported.

---

HDL Error Processing

Recovering the HDL file is only half of the story.

The next challenge is understanding where the import failed.

Many consultants only review the Import and Load Data UI. However, Oracle stores detailed messages throughout every stage of HDL processing.

Understanding these stages makes troubleshooting significantly easier.

---

HDL Processing Stages

Oracle validates HDL through several layers before data reaches the application.

1. Zip File
2. Data File
3. Import Validation
4. Metadata
5. Hierarchy
6. Logical Object
7. Physical Row
8. Service Processing

Each stage validates a different portion of the HDL. Knowing where an error occurs often tells you exactly what needs to be corrected.

---

Understanding Each Error Type

1. Zip File Errors

These occur before Oracle even reads the HDL.

• Invalid ZIP archive
• Corrupted upload
• Unsupported file format

2. Data File Errors

Oracle validates the contents of each .dat file.

• Missing data file
• Invalid encoding
• File structure issues

3. Import Validation Errors

These occur while Oracle imports individual HDL lines.

• Invalid attribute values
• Invalid dates
• Missing required fields

4. Metadata Errors

Oracle validates every METADATA line.

• Misspelled attribute names
• Incorrect business object
• Unsupported HDL version

5. Hierarchy Errors

Oracle validates parent-child relationships.

• Child record loaded before parent
• Invalid hierarchy sequence
• Missing parent object

6. Logical Object Errors

Logical validation occurs after hierarchy validation.

• Duplicate logical objects
• Missing unique keys
• Invalid HDL object relationships

7. Physical Row Errors

These errors occur at the individual MERGE line level.

• Duplicate source keys
• Invalid effective dates
• Business rule violations

8. Service Errors

These are generated during the final application service call.

• Object validation failures
• Payroll business rule errors
• Security violations
• Unexpected application exceptions

---

A Single Query for Complete Error Analysis

Rather than searching multiple tables individually, you can use a consolidated SQL query that retrieves HDL errors from every processing stage.

The query categorizes messages into:

• Zip File
• Data File
• Import
• Metadata
• Hierarchy
• Logical Object
• Physical Row
• Service Error

It also returns useful details such as:

• Message Type
• Error Message
• Stack Trace
• File Name
• Line Number
• Metadata Line
• HDL File Line
• Request ID
• UCM Content ID

Having all error information in one report makes it much easier to determine exactly where the HDL processing failed.

SELECT 1 orderby
, 'Zip File' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, to_number(null) seq_num
, '' ui_user_key
, '' metadata
, '' file_line
, ds.request_id
, ds.data_set_name
, '' data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_sets ds
WHERE l.message_source_table_name = 'HRC_DL_DATA_SETS'
AND l.message_source_line_id = ds.data_set_id

UNION

SELECT 2 orderby
, 'Data File' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, to_number('') seq_num
, '' ui_user_key
, '' metadata
, '' file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
WHERE l.message_source_table_name = 'HRC_DL_DATA_SET_BUS_OBJS'
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id

UNION

SELECT 3 orderby
, 'Import' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, '' ui_user_key
, '' metadata
, fl.text file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = 'HRC_DL_FILE_LINES'
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND fl.line_id = l.message_source_line_id

UNION

SELECT 4 orderby
, 'METADATA' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, '' ui_user_key
, fl.text metadata
, '' file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_file_headers fh
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = 'HRC_DL_FILE_HEADERS'
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND fh.header_id = l.message_source_line_id
AND fl.line_id = fh.line_id

UNION

SELECT 5 orderby
, 'Hierarchy' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, '' ui_user_key
, (SELECT hl.text
   FROM hrc_dl_file_lines hl
      , hrc_dl_file_headers fh
  WHERE fh.header_id = fr.header_id
    AND hl.line_id = fh.line_id) metadata
, fl.text file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_file_rows fr
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = 'HRC_DL_FILE_ROWS'
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND fr.row_id = l.message_source_line_id
AND fl.line_id = fr.line_id

UNION

SELECT 6 orderby
, 'Logical Object' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, ll.ui_user_key ui_user_key
, (SELECT hl.text
   FROM hrc_dl_file_lines hl
      , hrc_dl_file_headers fh
  WHERE fh.header_id = fr.header_id
    AND hl.line_id = fh.line_id) metadata
, fl.text file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_logical_lines ll
, fusion.hrc_dl_file_rows fr
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = 'HRC_DL_LOGICAL_LINES'
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND ll.logical_line_id = l.message_source_line_id
AND fr.logical_line_id = ll.logical_line_id
AND fl.line_id = fr.line_id

UNION

SELECT 7 orderby
, 'Physical Row' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, pl.ui_user_key || ' ' || pl.ui_date_from || ' ' || pl.ui_date_to ui_user_key
, (SELECT hl.text
   FROM hrc_dl_file_lines hl
      , hrc_dl_file_headers fh
  WHERE fh.header_id = fr.header_id
    AND hl.line_id = fh.line_id) metadata
, fl.text file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_physical_lines pl
, fusion.hrc_dl_file_rows fr
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = 'HRC_DL_PHYSICAL_LINES'
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND pl.physical_line_id = l.message_source_line_id
AND fr.row_id = pl.row_id
AND fl.line_id = fr.line_id

UNION

SELECT DISTINCT 8 orderby
, 'Service Error' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, to_number(null)
, ''
, '' metadata
, '' file_line
, ds.request_id
, ''
, '' data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_service_requests sr
, fusion.hrc_dl_data_sets ds
WHERE l.message_source_table_name = 'HRC_DL_SERVICE_REQUESTS'
AND l.message_source_line_id = sr.service_call_id;

---

Common Production Support Scenarios

These SQL queries are particularly useful for:

• Recovering lost HDL files
• Investigating failed HDL loads
• Payroll support
• Production issue analysis
• Data conversion validation
• Building correction HDL files
• Audit requests
• Comparing environments

---

Best Practices

When supporting HDL imports, consider the following recommendations:

• Keep the original HDL zip file whenever possible.
• Record the HDL Data Set Name and UCM Content ID for every production deployment.
• Use UCM Content ID as the primary reference when troubleshooting historical loads.
• Review the earliest error in the processing hierarchy before investigating downstream errors.
• Validate correction HDL files in a lower environment before deploying to production.

---

Final Thoughts

Oracle HCM Data Loader stores significantly more information than many consultants realize. By querying the HDL repository tables, implementation teams can recover historical HDL files, reconstruct lost .dat files, and troubleshoot errors at every stage of the load process.

These SQL techniques are very useful during production support, especially when the original HDL files are unavailable or when historical data changes need to be investigated.

Rather than relying only on the Import and Load Data user interface, leveraging the underlying HDL tables provides deeper insight into both the data that was loaded and the exact point where processing failed.

---

Important Tip

One of the first things I recommend after every production HDL deployment is recording the Data Set Name, UCM Content ID, and Request ID.

Even if the original ZIP file is lost months later, these identifiers allow you to reconstruct the HDL contents and investigate errors directly from the HCM Data Loader repository tables, making future support significantly easier.

Oracle Cloud Payroll: Creating US Federal Tax Cards Using HCM Data Loader (HDL)

Introduction

One of the most common activities during the implementation of Oracle Cloud Payroll is creating employee tax cards. Whether you're performing an initial data conversion, onboarding new employees, or migrating from a legacy payroll system, maintaining tax withholding information accurately is essential for correct payroll tax calculations.

Although tax cards can be created individually through the Oracle Cloud user interface, implementation teams often need to load thousands of employee tax elections during a payroll conversion. Oracle Cloud Payroll provides HCM Data Loader (HDL) business objects that allow tax cards to be created efficiently, consistently, and in bulk.

A common misconception is that creating a US Federal Tax Card only requires loading Federal withholding elections. A US Tax Card is composed of multiple HDL business objects that work together to establish the employee's complete payroll taxation configuration.

In this article, we'll explore the complete hierarchy of a US Federal Tax Card, understand the evolution of Oracle's Federal Tax HDL business objects, and walk through a practical HDL example for creating a Federal Tax Card.

---

Understanding the US Tax Card Hierarchy

A US Tax Card consists of several related HDL business objects that represent different portions of an employee's taxation information.

The overall hierarchy looks like this:

Tax Withholding
│
├── US Taxation
│   │
│   └── US Taxation Base
│
└── Federal Taxes
    │
    ├── FederalTaxesBase
    ├── FederalTaxes2020
    └── FederalTaxes2023

Each component has a specific purpose:

HDL Object	Purpose
TaxWithholding	Creates the parent Tax Withholding card
USTaxation	Associates the employee with a Tax Reporting Unit (TRU)
USTaxationBase	Stores work location and statutory taxation details
FederalTaxes	Creates the Federal Taxes calculation component
FederalTaxesBase / FederalTaxes2020 / FederalTaxes2023	Stores the employee's Federal withholding elections

Understanding this hierarchy makes it much easier to troubleshoot HDL errors and build reliable conversion files.

---

Understanding the Federal Tax HDL Objects

One of the most confusing areas for Oracle Payroll consultants is determining which Federal Tax HDL object should be used.

Oracle has introduced multiple versions of the Federal Tax component over time to align with IRS Form W-4 changes.

Today you'll commonly encounter three business objects:

• FederalTaxesBase
• FederalTaxes2020
• FederalTaxes2023

Although they all configure Federal withholding, each represents a different IRS withholding model.

FederalTaxesBase – Pre-2020 IRS W-4

Prior to 2020, employees completed the traditional IRS Form W-4 using withholding allowances.

The primary election options included:

• Filing Status
• Number of Allowances
• Additional Federal Tax Amount
• Federal Tax Exemption

Oracle supports this withholding model using the FederalTaxesBase HDL business object.

Typical attributes include:

• Filing Status
• Allowances
• Additional Tax Amount
• Exempt from Federal Income Tax
• Medicare
• Social Security
• Federal Unemployment
• Federal Income Tax

This object is primarily used when converting employees whose tax elections were established using the pre-2020 IRS W-4.

FederalTaxes2020 – IRS Form W-4 Redesign

Beginning in 2020, the IRS completely redesigned Form W-4.

The most significant change was the elimination of withholding allowances.

Instead of allowances, employees now provide:

• Filing Status
• Multiple Jobs indicator
• Dependents
• Other Income
• Deductions
• Additional Withholding

Oracle introduced the FederalTaxes2020 HDL business object to support this redesigned withholding model.

Unlike the previous version, withholding calculations are based on dollar amounts rather than allowances.

FederalTaxes2023 – Current Federal Tax Model

Oracle later introduced FederalTaxes2023 to support additional legislative updates and enhancements to the Federal withholding model.

The overall structure remains similar to FederalTaxes2020 while supporting newer attributes such as:

• Nonresident Alien Indicator
• Updated Federal withholding calculations
• Additional payroll processing options

For most new Oracle Cloud Payroll implementations today, FederalTaxes2023 is the recommended business object.

---

Comparing the Federal Tax HDL Objects

HDL Object	IRS Model	Typical Attributes
FederalTaxesBase	Pre-2020 W-4	Filing Status, Allowances, Additional Tax, Exempt
FederalTaxes2020	2020 W-4	Multiple Jobs, Dependents, Other Income, Deductions
FederalTaxes2023	Current	Same as 2020 plus Nonresident Alien and newer enhancements

---

Which Federal Tax HDL Object Should You Use?

FederalTaxesBase

Use when converting legacy employees whose tax elections were created using the traditional IRS W-4 with withholding allowances.

FederalTaxes2020

Use when employee withholding elections follow the redesigned IRS 2020 W-4.

FederalTaxes2023

Recommended for new Oracle Cloud Payroll implementations using current Oracle releases.

Note: The available HDL business objects depend on your Oracle Cloud Payroll release. Always verify the supported business objects in the Oracle HCM documentation for your environment.

---

Step 1: Create the Tax Withholding Card

The first step is creating the employee's Tax Withholding card.

METADATA|TaxWithholding|EffectiveStartDate|EffectiveEndDate|LegislativeDataGroupName|DirCardDefinitionName|CardSequence|AssignmentNumber
MERGE|TaxWithholding|2025/05/01|4712/12/31|US Legislative Data Group|Tax Withholding|1|E100755

This creates the parent tax card that will contain all Federal and State taxation information.

---

Step 2: Create US Taxation

Next, associate the employee with the appropriate Tax Reporting Unit.

METADATA|USTaxation|EffectiveStartDate|EffectiveEndDate|LegislativeDataGroupName|CardSequence|
AssignmentNumber|AssociationTaxReportingUnitName|AssociationAssignmentNumber|TaxReportingUnit
MERGE|USTaxation|2025/05/01|4712/12/31|US Legislative Data Group|1|E100755|ABC LLC|E100755|ABC LLC

This establishes the taxation relationship between the employee and the Tax Reporting Unit.

---

Step 3: Load US Taxation Base

The USTaxationBase component stores work location and statutory taxation information.

METADATA|USTaxationBase|EffectiveStartDate|EffectiveEndDate|LegislativeDataGroupName|CardSequence|AssignmentNumber|
AssociationTaxReportingUnitName|AssociationAssignmentNumber|TaxReportingUnit|PrimaryWorkAddress|StateforDisabilityCalculation|StateforUnemploymentCalculation|StatutoryEmployee|StateforFamilyandMedicalLeaveCalculation|StateforLongTermCareCalculation
MERGE|USTaxationBase|2025/05/01|4712/12/31|US Legislative Data Group|1|E100755|Abc LLC|E100755|Abc LLC|113 street TN|TN|TN|N||

Typical information maintained includes:

• Primary Work Address
• State Unemployment
• State Disability
• Family Medical Leave
• Long-Term Care
• Statutory Employee indicator

These attributes influence various payroll tax calculations depending on legislative requirements.

---

Step 4: Create the Federal Taxes Component

Before loading withholding elections, create the Federal Taxes calculation component.

METADATA|FederalTaxes|EffectiveStartDate|EffectiveEndDate|LegislativeDataGroupName|CardSequence|AssignmentNumber
MERGE|FederalTaxes|2025/05/01|4712/12/31|US Legislative Data Group|1|E100755

This creates the Federal Taxes section of the employee's tax card.

---

Step 5: Load Federal Withholding Elections

Finally, load the employee's withholding elections.

For modern Oracle Cloud Payroll implementations, this is typically accomplished using FederalTaxes2023.

METADATA|FederalTaxes2023|EffectiveStartDate|EffectiveEndDate|LegislativeDataGroupName|CardSequence|AssignmentNumber|
FilingStatus|MultipleJobs|QualifyingDependentsAmount|OtherDependentsAmount|TotalDependentsAmount|OtherIncomeAmount|DeductionsAmount|ExtraWithholding|ExemptfromFederalIncomeTaxWithholding|NonresidentAlien|IRSLockinDate|Medicare|FederalUnemployment|SocialSecurity|FederalIncomeTax|EnforceFederalIncomeTaxLookbackRule|TaxEnforcementLevel|RegularAmount|RegularRate|SupplementalAmount|SupplementalRate|CumulativeTaxation
MERGE|FederalTaxes2023|2025/05/01|4712/12/31|US Legislative Data Group|1|E100755|4||5|6|11|5|5|||N|||||||PSU|||||

Typical information includes:

• Filing Status
• Multiple Jobs
• Qualifying Dependents
• Other Dependents
• Other Income
• Deductions
• Additional Withholding
• Federal Tax Exemption
• Nonresident Alien indicator

Once loaded, these elections become part of the employee's Federal Tax Card and are used during payroll processing.

---

Recommended HDL File Split

You can split the HDL load into multiple TaxWithholding.dat files to make the load sequence easier to manage:

• File 1: TaxWithholding
• File 2: USTaxation and USTaxationBase
• File 3: FederalTaxes and FederalTaxes2023

This approach helps validate each parent-child dependency before loading the next section.

---

Common Business Scenarios

This HDL solution is commonly used for:

• Initial Oracle Payroll implementations
• Payroll data conversions
• Mergers and acquisitions
• Employee onboarding integrations
• Bulk tax election updates
• Payroll system migrations

---

Benefits of Using HDL

Using HDL to create tax cards provides several advantages:

• Supports large-scale employee conversions
• Eliminates repetitive manual data entry
• Improves data consistency
• Easily repeatable across environments
• Supports version-controlled deployment
• Reduces implementation effort

---

Best Practices

When loading US Tax Cards:

• Create the Tax Withholding card before child components.
• Use the same CardSequence throughout the hierarchy.
• Verify the employee assignment exists.
• Confirm the Tax Reporting Unit is valid.
• Validate Federal withholding elections before loading.
• Run QuickPay after loading to verify tax calculations.
• Test the complete hierarchy in a lower environment before production deployment.

---

Common Implementation Pitfalls

Implementation teams frequently encounter issues such as:

• Loading child objects before creating the Tax Withholding card.
• Using inconsistent CardSequence values.
• Invalid Tax Reporting Unit names.
• Incorrect Legislative Data Group.
• Effective dates that do not align across HDL objects.
• Using the wrong Federal Tax HDL object for the employee's W-4 model.

Understanding the dependency between the HDL objects helps avoid many of these issues.

---

Final Thoughts

Although creating a US Federal Tax Card through HDL may initially appear complex, it becomes much more manageable once you understand the overall hierarchy and the role of each HDL business object.

Rather than thinking of the Federal Tax Card as a single HDL file, think of it as a collection of related components that together define an employee's payroll taxation configuration.

By understanding when to use FederalTaxesBase, FederalTaxes2020, and FederalTaxes2023, implementation teams can build cleaner HDL files, simplify payroll data conversions, and ensure employees' Federal withholding elections are configured accurately.

---

Important Tip

One of the most common implementation mistakes is attempting to load FederalTaxes2023 directly without first creating the parent TaxWithholding and FederalTaxes components.

Oracle expects the complete tax card hierarchy to exist before child components are loaded. Following a parent-to-child loading sequence not only avoids dependency errors but also results in cleaner, more maintainable HDL files during payroll implementations and future support activities.

Oracle Cloud Payroll: Managing Payroll Assignments and Timecard Required Flag Using HDL

Introduction

During Oracle Cloud Payroll implementations, assigning employees to the correct payroll is one of the foundational configuration activities.

In addition to payroll assignments, organizations often need to maintain payroll processing attributes such as the Timecard Required Flag, particularly when integrating with Oracle Time and Labor (OTL) or third-party timekeeping applications.

While both tasks can be performed through the Oracle Cloud user interface, Oracle provides standard HCM Data Loader (HDL) business objects that enable these activities to be completed efficiently in bulk.

In this article, we will look at two commonly used HDL business objects:

• AssignedPayroll – Assign an employee to a payroll definition.
• PayrollAssignmentDetails – Maintain payroll assignment attributes such as the Timecard Required Flag.

Using these HDL objects together provides a scalable approach for payroll implementations, data conversions, and ongoing payroll maintenance.

---

Step 1: Assign the Employee to a Payroll

The first step is assigning the employee to the appropriate payroll definition.

Oracle provides the AssignedPayroll HDL business object for this purpose.

Sample HDL

METADATA|AssignedPayroll|EffectiveStartDate|AssignmentNumber|PayrollDefinitionCode|LegislativeDataGroupName|
StartDate|TimecardRequiredFlag|OvertimePeriodCode|LastStandardProcessDate|FinalCloseDate
MERGE|AssignedPayroll|2025/05/01|E100755|Active|US Legislative Data Group|2025/05/01|Y|||

This HDL:

• Assigns the employee to a payroll
• Creates the payroll relationship
• Can initialize payroll processing attributes during assignment

Typical implementation scenarios include:

• Initial employee conversion
• New hire conversions
• Payroll migration projects
• Organizational restructures
• Payroll reassignment

---

Step 2: Maintain Payroll Assignment Details

Once the payroll assignment exists, payroll-specific attributes can be updated independently using the PayrollAssignmentDetails HDL business object.

This is especially useful when the payroll assignment already exists but payroll processing settings need to change.

Sample HDL

METADATA|PayrollAssignmentDetails|LegislativeDataGroupName|AssignmentNumber|EffectiveStartDate|EffectiveEndDate|TimecardRequiredFlag|OvertimePeriodCode
MERGE|PayrollAssignmentDetails|US Legislative Data Group|E100755|2025/05/01|4712/12/31|Y|

Unlike AssignedPayroll, this HDL does not assign the employee to a payroll.

Instead, it maintains payroll assignment attributes for an existing payroll assignment.

---

Understanding the Timecard Required Flag

One of the most commonly maintained attributes is:

TimecardRequiredFlag

Supported values:

Value	Description
Y	Timecards are required before payroll processing
N	Timecards are not required

The appropriate configuration depends on the organization’s payroll and timekeeping design.

---

Typical Business Scenarios

Oracle Time and Labor (OTL)

Organizations using Oracle Time and Labor typically configure:

TimecardRequiredFlag = Y

Since Oracle maintains employee timecards, payroll expects time entries to be available for payroll processing.

Third-Party Time Systems

Organizations using applications such as:

• UKG
• Kronos
• Workday Time
• ADP Workforce Manager

may maintain employee time externally.

Depending on the integration design, only overtime or payroll-relevant transactions may be interfaced into Oracle Payroll.

In these cases, organizations often configure:

TimecardRequiredFlag = N

This allows the external application to remain the system of record for time while Oracle Payroll processes only the required payroll transactions.

Worker Classification Changes

Organizations frequently update this flag when employees move between:

• Hourly Nonexempt
• Salaried Nonexempt
• Exempt

HDL allows these updates to be performed for large employee populations in a consistent and repeatable manner.

---

When Should Each HDL Be Used?

Business Requirement	AssignedPayroll	PayrollAssignmentDetails
Assign employees to payroll	Yes	No
Change payroll assignment	Yes	No
Update Timecard Required Flag	Only if you want to set it consistently during payroll assignment	Yes
Update overtime period	Only if you want to set it consistently during payroll assignment	Yes
Ongoing payroll maintenance	No	Yes

As a best practice:

• Use AssignedPayroll when creating or changing payroll assignments.
• Use PayrollAssignmentDetails for ongoing maintenance of payroll processing attributes.

---

Benefits of Using HDL

Using these HDL business objects provides several advantages:

• Supports mass employee updates
• Eliminates repetitive manual configuration
• Simplifies payroll data conversion
• Promotes consistent payroll configuration
• Can be repeated across Development, Test, and Production environments
• Useful for payroll implementations and reorganizations

---

Rollback Support

These objects don’t support rollback. You need to prepare separate delete HDL if required.

---

Best Practices

When using these HDL objects:

• Ensure the employee assignment already exists.
• Verify the Payroll Definition belongs to the correct Legislative Data Group.
• Use effective dates that align with payroll processing periods.
• Configure the Timecard Required Flag based on your organization’s payroll and timekeeping strategy.
• Validate payroll assignments after loading using Oracle Payroll pages or OTBI reports.
• Test both Y and N scenarios before mass loading.
• Maintain a backup copy of the source HDL file and load results for audit purposes.

---

Final Thoughts

Oracle Cloud Payroll provides separate HDL business objects for assigning employees to payrolls and maintaining payroll processing attributes.

Understanding the distinction between AssignedPayroll and PayrollAssignmentDetails allows implementation teams to build cleaner, more maintainable payroll data conversion and support processes.

For new payroll assignments, AssignedPayroll establishes the payroll relationship.

For ongoing maintenance activities, such as updating the Timecard Required Flag or overtime processing settings, PayrollAssignmentDetails offers a straightforward and scalable solution.

Leveraging both HDL objects appropriately helps reduce manual effort, supports large-scale payroll maintenance, and ensures consistent payroll configuration across the enterprise.