PERSONNUMBER_ASSIGNMENTID_WORKTERMSASSIGNMENTID_PERIODOFSERVICEID

Query without Person Number Filter

WITH RankedAssignments AS (
    SELECT 
        papf.person_number AS PersonNumber,
        paam.assignment_id AS AssignmentId, 
        paam.work_terms_assignment_id AS WorkTermsAssignmentId,
        ppos.period_of_service_id AS PeriodOfServiceId,
        ROW_NUMBER() OVER (PARTITION BY papf.person_number ORDER BY paam.effective_start_date DESC) AS rn
    FROM
        per_all_people_f papf
        JOIN per_person_names_f ppnf ON ppnf.person_id = papf.person_id
        JOIN per_periods_of_service ppos ON ppos.person_id = papf.person_id
        JOIN per_all_assignments_m paam ON paam.period_of_service_id = ppos.period_of_service_id
        JOIN per_all_assignments_m workterm ON workterm.assignment_id = paam.work_terms_assignment_id
    WHERE
        SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND ppnf.name_type = 'GLOBAL'
        AND SYSDATE BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
        AND paam.assignment_status_type IN ('ACTIVE', 'Active - Payroll Eligible')
        AND SYSDATE BETWEEN paam.effective_start_date AND paam.effective_end_date
        AND paam.primary_assignment_flag = 'Y'
        AND SYSDATE BETWEEN workterm.effective_start_date AND workterm.effective_end_date
        AND papf.person_number IN ('XXXXXX')
)
SELECT PersonNumber, AssignmentId, WorkTermsAssignmentId, PeriodOfServiceId
FROM RankedAssignments
WHERE rn = 1

Query with Person Number Filter (Add the multiple person number at the same time as a parameter)

WITH SplitPersonNumbers AS (
    SELECT REGEXP_SUBSTR(:person_numbers, '[^,]+', 1, LEVEL) AS person_number
    FROM DUAL
    CONNECT BY LEVEL <= REGEXP_COUNT(:person_numbers, ',') + 1
),
RankedAssignments AS (
    SELECT 
        papf.person_number AS PersonNumber,
        paam.assignment_id AS AssignmentId, 
        paam.work_terms_assignment_id AS WorkTermsAssignmentId,
        ppos.period_of_service_id AS PeriodOfServiceId,
        ROW_NUMBER() OVER (PARTITION BY papf.person_number ORDER BY paam.effective_start_date DESC) AS rn
    FROM
        per_all_people_f papf
        JOIN per_person_names_f ppnf ON ppnf.person_id = papf.person_id
        JOIN per_periods_of_service ppos ON ppos.person_id = papf.person_id
        JOIN per_all_assignments_m paam ON paam.period_of_service_id = ppos.period_of_service_id
        JOIN per_all_assignments_m workterm ON workterm.assignment_id = paam.work_terms_assignment_id
        JOIN SplitPersonNumbers spn ON spn.person_number = papf.person_number -- Use split person numbers
    WHERE
        SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
        AND ppnf.name_type = 'GLOBAL'
        AND SYSDATE BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
        AND paam.assignment_status_type IN ('ACTIVE', 'Active - Payroll Eligible')
        AND SYSDATE BETWEEN paam.effective_start_date AND paam.effective_end_date
        AND paam.primary_assignment_flag = 'Y'
        AND SYSDATE BETWEEN workterm.effective_start_date AND workterm.effective_end_date
)
SELECT PersonNumber, AssignmentId, WorkTermsAssignmentId, PeriodOfServiceId
FROM RankedAssignments
WHERE rn = 1

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