Query Employee Assignment Number Details

WITH person_list AS (
    SELECT 
        TRIM(REGEXP_SUBSTR(:PersonNumbers, '[^ ]+', 1, LEVEL)) AS PERSON_NUMBER,
        LEVEL AS sort_order
    FROM dual
    CONNECT BY REGEXP_SUBSTR(:PersonNumbers, '[^ ]+', 1, LEVEL) IS NOT NULL
)
SELECT 
    papf.PERSON_NUMBER AS "Person Number",
    paam.ASSIGNMENT_STATUS_TYPE AS "Assignment Status",
    paam.ASSIGNMENT_NUMBER AS "Assignment Number"
FROM 
    PER_ALL_ASSIGNMENTS_M paam
JOIN 
    PER_ALL_PEOPLE_F papf 
    ON paam.PERSON_ID = papf.PERSON_ID
JOIN 
    person_list pl 
    ON papf.PERSON_NUMBER = pl.PERSON_NUMBER
WHERE 
    paam.EFFECTIVE_LATEST_CHANGE = 'Y'
    AND paam.EFFECTIVE_END_DATE > SYSDATE
    AND papf.EFFECTIVE_END_DATE > SYSDATE
    AND papf.EFFECTIVE_START_DATE <= SYSDATE
    AND paam.ASSIGNMENT_TYPE = 'E'  -- Only Employees
ORDER BY 
    pl.sort_order

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