EmployeeInformation

SELECT
    ppf.person_number
  , ppf.PERSON_ID
  , ppn.first_name
  , ppn.last_name
  , ppf.EFFECTIVE_START_DATE hire_date
  , pea.EMAIL_ADDRESS
  , PJF.NAME   JOB_NAME
  , haou2.NAME Ledger
  , PU.USERNAME
  , (
        SELECT
            ppnf.full_name
        FROM
            per_person_names_f ppnf
        WHERE
            ppnf.person_id     = pasf.manager_id
            AND ppnf.name_type = 'GLOBAL'
    )
                                                                                                                                                                                   supervisorname
  , glcc.segment1 ||'.' || glcc.segment2 ||'.' || glcc.segment3 ||'.' || glcc.segment4 ||'.' || glcc.segment5 ||'.' || glcc.segment6 ||'.' || glcc.segment7 ||'.' || glcc.segment8 defaultexpaccount
    -- ,(SELECT listagg(prdv.role_name, ',') within GROUP (
    -- ORDER BY role_name) AS role_name
    -- FROM per_user_roles pur,
     -- per_users puq,
     -- per_roles_dn_vl prdv
    -- WHERE pur.user_id = puq.user_id
    -- AND pur.role_id   = prdv.role_id
    -- AND puq.user_id   =pu.user_id
    -- AND rownum        <10
    -- GROUP BY pur.user_id
    -- ) role_name
FROM
    per_all_people_f             ppf
  , per_person_names_f           ppn
  , PER_ALL_ASSIGNMENTS_M        paam
  , per_assignment_supervisors_f pasf
  , gl_code_combinations         glcc
  , PER_EMAIL_ADDRESSES          pea
  , hr_all_organization_units    haou2
  , PER_JOBS                     pjf
  , PER_USERS                    PU
WHERE
    1                                          = 1
    AND ppf.person_id                          = ppn.person_id
    AND ppf.person_id                          = paam.person_id(+)
    AND ppf.person_id                          = pasf.person_id(+)
    AND NVL( pasf.manager_type,'LINE_MANAGER') = 'LINE_MANAGER'
    AND ppf.person_id                          = pea.person_id(+)
    AND PAAM.JOB_ID                            = PJF.JOB_ID(+)
    AND PAAM.default_code_comb_id              = glcc.code_combination_id(+)
    AND PAAM.BUSINESS_UNIT_ID                  = haou2.organization_id(+)
    AND PPF.PERSON_ID                          = PU.PERSON_ID(+)
    AND ppn.name_type                          = 'GLOBAL'
    AND TRUNC (SYSDATE) BETWEEN TRUNC (PAAM.effective_start_date) AND TRUNC (PAAM.effective_end_date)
    AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL(pasf.effective_start_date,sysdate)) AND TRUNC(NVL(pasf.effective_end_date,sysdate))
    AND TRUNC (SYSDATE) BETWEEN TRUNC (ppn.effective_start_date) AND TRUNC (ppn.effective_end_date)
    AND TRUNC (SYSDATE) BETWEEN TRUNC (ppf.effective_start_date) AND TRUNC (ppf.effective_end_date)
    AND PAAM.ASSIGNMENT_TYPE IN ('E'
                               ,'C')
    And upper(ppn.first_name)    like nvl(upper(:IP_First_Name),upper(ppn.first_name))
    And upper(ppn.last_name)     like nvl(upper(:IP_Last_Name),upper(ppn.last_name))
    And upper(ppf.person_number) like nvl(upper(:IP_Person_number),upper(ppf.person_number))
    And upper(PU.USERNAME)       like nvl(upper(:IP_Username),upper(PU.USERNAME))
    And upper(pea.EMAIL_ADDRESS) like nvl(upper(:IP_EmailAddress),upper(pea.EMAIL_ADDRESS))
ORDER BY
    ppf.person_number