Friday, March 16, 2018

Find Active Users with responsibilities assigned in R122


Below Query can be used to find all active users and all the responsibilities assigned to them in Oracle EBusiness Suite R122


SELECT   fu.user_name, fu.start_date user_start_date,fu.description,
         fu.end_date user_end_date, per.GLOBAL_NAME employee_name,
         per.effective_start_date employee_start_date,
         per.effective_end_date employee_end_date, resp.responsibility_name,
         resp.start_date responsibility_start_date,
         resp.end_date responsibility_end_date
    FROM apps.fnd_user fu,
         (SELECT *
            FROM (SELECT x.*,
                         ROW_NUMBER () OVER (PARTITION BY person_id ORDER BY effective_end_date DESC)
                                                                            r
                    FROM apps.per_all_people_f x)
           WHERE r = 1) per,
         (SELECT fr.responsibility_name, fur.user_id, fur.start_date,
                 fur.end_date
            FROM apps.fnd_user_resp_groups_direct fur,
                 apps.fnd_responsibility_tl fr
           WHERE fur.responsibility_id = fr.responsibility_id
          UNION
          SELECT fr.responsibility_name, fur.user_id, fur.start_date,
                 fur.end_date
            FROM apps.fnd_user_resp_groups_indirect fur,
                 apps.fnd_responsibility_tl fr
           WHERE fur.responsibility_id = fr.responsibility_id) resp
   WHERE fu.employee_id = per.person_id(+)
     AND resp.user_id = fu.user_id
  --   and fu.user_name in ('XYZ','PQR')
 --    and fu.description like '%XYZ%'
     and  nvl(fu.end_date, sysdate+1) > Sysdate order by fu.user_name

3 comments: