Tech Trainings

Oracle Cloud ERP – Query to get HRMS Person Name, Email, Phone, User Account Details

December 10, 2020 by admin

select papf.person_id, pu.username, ppnf.display_name, pp.phone_number, pea.email_address, pu.start_date, pu.end_date, papf.effective_start_date, papf.effective_end_date, pu.active_flag, pu.suspended
    from per_all_people_f    papf
       , per_person_names_f  ppnf
       , per_phones          pp
       , per_email_addresses pea
       , per_users pu
    WHERE  SYSDATE BETWEEN NVL(papf.effective_start_date, SYSDATE) AND NVL(papf.effective_end_date, SYSDATE)
    AND    pea.email_address_id(+) = papf.primary_email_id
    AND    SYSDATE BETWEEN NVL(pea.date_from(+), SYSDATE) AND NVL(pea.date_to(+), SYSDATE)
    AND    pp.phone_id (+)         = papf.primary_phone_id
    AND    SYSDATE BETWEEN NVL(pp.date_from(+), SYSDATE) AND NVL(pp.date_to(+), SYSDATE)
    and    ppnf.person_id(+) = papf.person_id
    and    ppnf.name_type(+) = 'GLOBAL'
    and    SYSDATE BETWEEN NVL(ppnf.effective_start_date(+), SYSDATE) AND NVL(ppnf.effective_end_date(+), SYSDATE)
    and    papf.person_id = pu.person_id(+)
    and    sysdate between nvl(pu.start_date(+), sysdate) and nvl(pu.end_date(+), sysdate)
    and    pu.active_flag(+) = 'Y'
    and    sysdate between nvl(papf.effective_start_date, sysdate) and nvl(papf.effective_end_date, sysdate)

Comments |0|