An Oracle Fusion HCM employee assignment query needs a date and an employment scope. The current primary assignment, all concurrent assignments and an assignment at a historical date are different questions. Define the one you need before joining or expanding employment records.
Resolve the worker by person number, then inspect its work relationships. The employee-detail guide explains the worker identifiers. A work relationship provides the employment context, including the legal employer. An assignment supplies the associated job and organizational details.
A dated collection request can use GET /hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber='100123'&expand=workRelationships.assignments&effectiveDate=2026-10-31&limit=5. The person number is synthetic. Oracle documents this parent-to-assignment expansion in its past and future worker example. Inspect nested collection limits before treating the result as complete.
For a focused child read, use the resolved work relationship's assignments collection. Keep the returned assignment key and assignment number separately. A resource key identifies the API item; an assignment number is a business reference that is useful in reports and discussions with HR.
Inspect the primary flags in their documented scope. A relationship can have its own primary assignment, and the worker can have employment in another relationship. Selecting every row whose label contains primary without understanding the flag can return an unexpected population.
Read department and job details from the selected dated assignment. Retrieve legal-employer context from the associated relationship rather than copying it from an unrelated employment record. Keep the relationship and assignment identifiers in the export so repeated business labels remain distinguishable.
To compare two dates, retrieve the same employee at each date and match records by the appropriate stable identifiers. A request for one effective date returns a point-in-time view; it is not automatically a complete history export. Use the documented historical capabilities of the selected resource for a full audit question.
This CLHAPI assignment-query adaptation returns employee and contingent-worker assignments for synthetic person number 100123 on 31 October 2026. It removes the original primary-only filter to include concurrent assignments. Replace the person number and every date together.
SELECT p.person_number,
pnf.display_name,
p.person_id,
asg.period_of_service_id,
asg.assignment_id,
asg.assignment_number,
asg.assignment_type,
asg.assignment_status_type,
asg.primary_flag,
asg.organization_id,
asg.job_id,
asg.effective_start_date,
asg.effective_end_date
FROM per_all_people_f p
JOIN per_person_names_f pnf
ON pnf.person_id = p.person_id
AND DATE '2026-10-31' BETWEEN pnf.effective_start_date
AND pnf.effective_end_date
AND pnf.name_type = 'GLOBAL'
LEFT JOIN per_all_assignments_m asg
ON asg.person_id = p.person_id
AND DATE '2026-10-31' BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.effective_latest_change = 'Y'
AND asg.assignment_type IN ('E', 'C')
WHERE p.person_number = '100123'
AND DATE '2026-10-31' BETWEEN p.effective_start_date
AND p.effective_end_date
ORDER BY p.person_number, asg.period_of_service_id, asg.assignment_id;Oracle's assignment table reference distinguishes work terms from assignments and allows multiple changes in a day. EFFECTIVE_LATEST_CHANGE = 'Y' selects the latest change for the effective interval. This is a dated snapshot, not a full history export. A null assignment can mean that the person has no matching assignment in this scope.
Fusion Toolkit's worker-resolve-employment recipe resolves the worker and relationship before reading assignments at an explicit date. Review its completeness evidence and refine an ambiguous relationship filter. The manager lookup guide continues from the selected assignment. This workflow was reviewed on 8 November 2026.
You can use an LLM with the Fusion Toolkit AI Administrator skill and ask, "Show employee 100123's assignments and department as of 31 October 2026, and identify the primary assignment." It can query supported records in plain language without requiring you to write SQL or REST requests. Provide the date and employee number.