Search from 700 + Posts

Jan 16, 2013

Query to connect PER_PEOPLE_F and FND_USER in Oracle Applications


Below is Simple Query to connect PER_PEOPLE_F  and FND_USER in Oracle Applications and print following details
  1. User ID
  2. User name
  3. Email Address
  4. Contact #
for Oracle Application user 



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

If fnd_global.user_id is not returning any value set the context by executing
FND_GLOBAL.apps-initalize or replace it with user_id



No comments:

Post a Comment