Oracle Apps Knowledge Sharing

Query to extract active users and responsibilities assigned in Oracle Apps R12

In this post, we have given a query which helps you to download list of active users and responsibilities assigned to them. This is generally needed for audit.

Key Tables:

FND_USER

FND_USER_RESP_GROUPS_DIRECT

FND_USER_RESP_GROUPS_INDIRECT

FND_RESPONSIBILITY_VL

FND_APPLICATION

FND_APPLICATION_TL

Tested Instance: R12.2.4

Script: 

  SELECT b.user_name,
         b.user_id,
         b.start_date,
         b.end_date,        
         b.description,
         ftl.application_name,
         c.responsibility_name,
         a.start_date responsibility_start_date,
         a.end_date responsibility_end_date,
         a.description
    FROM fnd_user_resp_groups_direct a,
         fnd_user b,
         fnd_responsibility_vl c,        
         fnd_application fa,
         fnd_application_tl ftl
   WHERE a.user_id = b.user_id
     AND a.responsibility_id = c.responsibility_id
     AND fa.application_id = a.responsibility_application_id
     AND fa.application_id = ftl.application_id
     AND ftl.language   = USERENV('LANG')
     AND SYSDATE BETWEEN a.start_date AND NVL(a.end_date,SYSDATE + 1)
     AND SYSDATE BETWEEN b.start_date AND NVL(b.end_date,SYSDATE + 1)
     AND SYSDATE BETWEEN c.start_date AND NVL(c.end_date,SYSDATE + 1)
     AND b.user_id > 1100 -- Excludes standard users like SYSADMIN,GUEST etc
 UNION ALL
 SELECT DISTINCT b.user_name,
         b.user_id,
         b.start_date,
         b.end_date,        
         b.description,
         ftl.application_name,
         c.responsibility_name,
         a.start_date responsibility_start_date,
         a.end_date responsibility_end_date,
         a.description
    FROM fnd_user_resp_groups_indirect a,
         fnd_user b,
         fnd_responsibility_vl c,        
         fnd_application fa,
         fnd_application_tl ftl
   WHERE a.user_id = b.user_id
     AND a.responsibility_id = c.responsibility_id
     AND fa.application_id = a.responsibility_application_id
     AND fa.application_id = ftl.application_id
     AND ftl.language   = USERENV('LANG')
     AND SYSDATE BETWEEN a.start_date AND NVL(a.end_date,SYSDATE + 1)
     AND SYSDATE BETWEEN b.start_date AND NVL(b.end_date,SYSDATE + 1)
     AND SYSDATE BETWEEN c.start_date AND NVL(c.end_date,SYSDATE + 1)
     AND b.user_id > 1100 -- Excludes standard users like SYSADMIN,GUEST etc
    ORDER BY user_name