Wednesday, June 26, 2013

How to select user and responsibility details associated to an Application(FND) user

-- Query to derive responsibility/responsibilities associated to an Application(FND) user/users

Parameters:-

1. Application(FND) User Name
2. Responsibility Name
 
  SELECT u.user_name,
              u.start_date user_start_date,
              u.end_date user_end_date,
              u.email_address user_email_address,
              r.responsibility_name,
              g.start_date resp_assign_start_date,
              g.end_date resp_assign_end_date,
              g.user_id,
              r.responsibility_id
    FROM apps.fnd_user u,
             apps.fnd_user_resp_groups g,
             apps.fnd_responsibility_tl r
   WHERE     1 = 1
        AND r.responsibility_id = g.responsibility_id
        AND g.user_id = u.user_id
        AND u.user_name = NVL(:p_user_name, u.user_name)
        AND r.responsibility_name = NVL(:p_responsibility_name, r.responsibility_name)
ORDER BY u.user_name, r.responsibility_name DESC;