Active Employee Roster Query

 Active Employee Roster Query

SELECT 

    -- Person Identifiers

    papf.person_number,

    ppnf.full_name,

    ppnf.first_name,

    ppnf.last_name,

    -- Employment Details

    paam.assignment_number,

    paam.assignment_status_type,

    ppos.date_start                AS hire_date,

    ppos.actual_termination_date,

    -- Job,Department Details

    pjft.name                      AS job_name,

    houtl.name                     AS department_name,

    -- Assignment Metadata

    paam.effective_start_date      AS assignment_start_date,

    paam.effective_end_date        AS assignment_end_date

FROM 

    per_all_people_f               papf,

    per_person_names_f             ppnf,

    per_all_assignments_m          paam,

    per_periods_of_service         ppos,

    per_jobs_f_tl                  pjft,

    hr_organization_units_f_tl     houtl

WHERE 1=1

    -- Person to Name

    AND papf.person_id = ppnf.person_id

    AND ppnf.name_type = 'GLOBAL'

    -- Person to Assignment

    AND papf.person_id = paam.person_id

    -- Person to Work Relationship

    AND papf.person_id = ppos.person_id

    AND paam.period_of_service_id = ppos.period_of_service_id

    -- Assignment to Job Translation (Outer Join)

    AND paam.job_id = pjft.job_id(+)

    AND pjft.language(+) = 'US'

    --  Assignment to Department Translation (Outer Join)

    AND paam.organization_id = houtl.organization_id(+)

    AND houtl.language(+) = 'US'

    -- Active Employees Only (Assignment Status)

    AND paam.assignment_status_type = 'ACTIVE'

    AND paam.primary_flag = 'Y'         

    AND paam.assignment_type = 'E'      

    -- Filter for records active as of TODAY

    AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

    AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

    AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

    AND TRUNC(SYSDATE) BETWEEN pjft.effective_start_date(+) AND pjft.effective_end_date(+)

    AND TRUNC(SYSDATE) BETWEEN houtl.effective_start_date(+) AND houtl.effective_end_date(+)

ORDER BY 

    papf.person_number