Search 800 + 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



4 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
    Replies
    1. Great Article Cloud Computing Projects

      Networking Projects

      Final Year Projects for CSE

      JavaScript Training in Chennai

      JavaScript Training in Chennai

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

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

    payrollsolutionexperts
    Article submission sites

    ReplyDelete
  3. Thanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle Fusion HCM . Actually, I was looking for the same information on internet for
    Oracle Fusion HCM Interview Questions and Answers and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject.

    ReplyDelete