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



2 comments:

  1. You're so interesting and Fantastic; so nice to find someone with some original thoughts on this subject seriously.Many thanks for starting this up.
    Oracle Fusion financial

    ReplyDelete
  2. Great post! This is very useful for me and gain more information, Thanks for sharing with us.

    payrollsolutionexperts
    Article submission sites

    ReplyDelete