Below is Simple Query to connect PER_PEOPLE_F and FND_USER in Oracle Applications and print following details
SELECT fu.user_id, fu.user_name,
(select hr.email_address from PER_PEOPLE_F hr
where fu.employee_id = hr.person_id
and nvl(hr.effective_start_date, sysdate-1) < sysdate
and nvl(hr.effective_END_date, sysdate+1) > sysdate) email_address,
(select pho.phone_number from PER_PEOPLE_F hr, per_phones pho
where fu.employee_id = hr.person_id
and hr.person_id = pho.parent_id(+)
AND pho.phone_type(+) = 'W1'
AND pho.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND sysdate between pho.date_from(+)
and nvl(pho.date_to(+), sysdate)
and nvl(hr.effective_start_date, sysdate-1) < sysdate
and nvl(hr.effective_END_date, sysdate+1) > sysdate) phone_number
from fnd_user fu
where fu.user_id = fnd_global.user_id
- User ID
- User name
- Email Address
- Contact #
SELECT fu.user_id, fu.user_name,
(select hr.email_address from PER_PEOPLE_F hr
where fu.employee_id = hr.person_id
and nvl(hr.effective_start_date, sysdate-1) < sysdate
and nvl(hr.effective_END_date, sysdate+1) > sysdate) email_address,
(select pho.phone_number from PER_PEOPLE_F hr, per_phones pho
where fu.employee_id = hr.person_id
and hr.person_id = pho.parent_id(+)
AND pho.phone_type(+) = 'W1'
AND pho.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND sysdate between pho.date_from(+)
and nvl(pho.date_to(+), sysdate)
and nvl(hr.effective_start_date, sysdate-1) < sysdate
and nvl(hr.effective_END_date, sysdate+1) > sysdate) phone_number
from fnd_user fu
where fu.user_id = fnd_global.user_id
No comments:
Post a Comment