Here is the sample SQL Query for Oracle Fusion HCM. These queries will help to retrieve the basic information related to the employee assignment information.
This is the basic query, use this table accordingly and modify as per your requirements.
SQL Query :
SELECT
a.assignment_number,
p.person_number,
p.full_name,
a.job_id,
j.job_name,
a.position_id,
pos.position_name,
a.department_id,
d.department_name,
a.location_id,
loc.location_code AS location_name,
a.hire_date,
a.assignment_status_type
FROM
per_all_assignments_f a
JOIN
per_all_people_f p ON a.person_id = p.person_id
JOIN
per_jobs j ON a.job_id = j.job_id
JOIN
per_positions pos ON a.position_id = pos.position_id
JOIN
hr_organization_units d ON a.department_id = d.organization_id
JOIN
hr_locations loc ON a.location_id = loc.location_id
WHERE
SYSDATE BETWEEN a.effective_start_date AND a.effective_end_date;
