Oracle Cloud ERP – Query to get HRMS Person Name, Email, Phone, User Account Details
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|
Category: OracleSaaS