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.