SQL - Retrieving Current Position and Assignment Names for Active Employees

SELECT
    papf.person_number,
    paam.assignment_name AS current_assignment_name,
    hapt.name AS current_position_name
FROM
    fusion.per_all_people_f papf
JOIN
    fusion.per_all_assignments_m paam
    ON papf.person_id = paam.person_id
JOIN
    fusion.hr_all_positions_f hap
    ON paam.position_id = hap.position_id
    AND TRUNC(SYSDATE) BETWEEN hap.effective_start_date AND hap.effective_end_date
JOIN
    fusion.hr_all_positions_f_tl hapt
    ON hap.position_id = hapt.position_id
    AND hapt.language = 'US'
WHERE
    paam.assignment_type = 'E' -- Employee assignments
    AND paam.primary_flag = 'Y'
    AND paam.assignment_status_type = 'ACTIVE'
    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;

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.