To query work schedules in Oracle Fusion, you use the Work Schedules work area or the REST API to retrieve schedule definitions and assignments. This article explains the key tables, query methods, and common troubleshooting steps.
What Is a Work Schedule in Oracle Fusion?
In Oracle Fusion Human Capital Management (HCM), a work schedule defines the standard working hours and days for an employee or a group. It includes details like the start and end times, break durations, and the pattern of workdays across a week. Schedules are used for time entry, absence accruals, and payroll calculations.
There are two main types: standard schedules (fixed weekly patterns) and flexible schedules (variable hours or shifts). Each schedule is stored with a unique name and an effective date range.
Where to Find Work Schedule Data
The primary tables for work schedules are:
PER_WORK_SCHEDULES– stores the schedule header (name, type, effective dates).PER_WORK_SCHEDULE_LINES– stores the daily or weekly patterns (day of week, start time, end time, hours).PER_ASSIGNMENTS_WORK_SCHEDULE– links a person assignment to a schedule.
You can query these via SQL if you have direct database access, or use the REST API endpoints like /workSchedules for a more structured approach.
How to Run a Work Schedule Query in Fusion
There are three common ways to query work schedules:
- Using the Work Schedules work area – Navigate to My Client Groups > Work Schedules. Use the search fields to find a schedule by name or effective date.
- Using the REST API – Call
GET /workScheduleswith query parameters likeq=name="Standard Week"oreffectiveDate=2025-01-01. The response is in JSON/XML. - Using SQL – If you have access to the HCM database, write a query joining the tables above. For example:
SELECT w.name, l.day_number, l.start_time, l.end_time FROM PER_WORK_SCHEDULES w, PER_WORK_SCHEDULE_LINES l WHERE w.schedule_id = l.schedule_id AND w.name = 'Standard Week';
Each method has its own use case: the work area is best for ad-hoc checks, the API for integration, and SQL for reporting.
Choosing the Right Query Method
Your choice depends on your role and need:
- HR Specialist – Use the work area to view or edit schedules without technical skills.
- Developer – Use the REST API to embed schedule data into external applications.
- Reporting Analyst – Use SQL to extract large datasets for analytics.
Consider the data volume and frequency. For real-time lookups, the API is efficient. For batch reports, SQL is better.
Common Issues and Troubleshooting
Users often encounter these problems:
- No results found – Check the effective date range. Schedules are date-effective, so a past or future date may return nothing.
- Duplicate schedules – Multiple versions can exist. Use the latest effective start date.
- Assignment not linked – The employee may not have a schedule assigned. Check
PER_ASSIGNMENTS_WORK_SCHEDULE. - API returns 404 – Ensure the REST service is enabled and you have the correct endpoint.
For API issues, verify your authentication and the base URL. For SQL issues, confirm the table names match your schema version.
Work Schedule Query Template
Use this SQL template to extract schedule details for a specific employee or all schedules:
| Field | Example | Description |
|---|---|---|
| Schedule Name | Standard Week | Name of the schedule |
| Day Number | 1 (Monday) | Day of week (1-7) |
| Start Time | 09:00 | Start of workday |
| End Time | 17:00 | End of workday |
| Effective Date | 2025-01-01 | Start date of schedule version |
Sample filled-in (fictional): For employee 1001, schedule ‘Standard Week’ has Monday start 09:00, end 17:00, effective 2025-01-01.
Frequently Asked Questions
Can I query work schedules using REST API?
Yes, Oracle Fusion provides REST endpoints for work schedules. Use GET /workSchedules with appropriate filters. You need to have the HCM REST service enabled and proper privileges.
How do I find an employee’s assigned schedule?
Query the assignment table: SELECT schedule_id FROM PER_ASSIGNMENTS_WORK_SCHEDULE WHERE assignment_id = :id. Then join to the schedule tables to get details.
What if a schedule has multiple effective dates?
Use the effective date that matches your reporting period. For example, to see the schedule as of today, filter effectiveStartDate <= TRUNC(SYSDATE) AND effectiveEndDate >= TRUNC(SYSDATE).
Can I update a schedule via SQL?
Direct SQL updates are not recommended because Fusion uses a data model with validation. Use the work area or REST API to change schedules to ensure data integrity.
Why does my query return no rows?
Check the effective date range and the schedule name spelling. Also verify that the schedule is not expired. Use wildcards in the name if unsure.
Safety and Best Practices
When querying work schedules, always test your queries in a sandbox environment first. Avoid modifying data directly in production. For complex reporting, consider using Oracle Transactional Business Intelligence (OTBI) instead of raw SQL. If you need to handle sensitive employee data, ensure you comply with your organization’s data privacy policies.