SQL - Get the Person Number based on Assignment Id

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

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

    paam.assignment_id = :assignment_id

    AND papf.effective_start_date <= SYSDATE AND papf.effective_end_date >= SYSDATE

    AND ppnf.name_type = 'GLOBAL'

    AND ppnf.effective_start_date <= SYSDATE AND ppnf.effective_end_date >= SYSDATE

    AND paam.assignment_status_type IN ('ACTIVE', 'Active - Payroll Eligible')

    AND paam.effective_start_date <= SYSDATE AND paam.effective_end_date >= SYSDATE

    AND paam.primary_assignment_flag = 'Y'

    AND workterm.effective_start_date <= SYSDATE AND workterm.effective_end_date >= SYSDATE

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