Active Responsibility List with Active User Count

If you need to create an ad-hoc report showing all the currently active Oracle responsibilities with all active users that are using them, below is a simple query that can help you.

SELECT

       fat.application_name         "Application Name",

       frv.responsibility_name      "Active Responsibility Name",

       COUNT(fu.user_name)          "Active User Count"

  FROM

       fnd_user                     fu,

       fnd_user_resp_groups_direct  furgd,

       fnd_responsibility_vl        frv,

       fnd_application_tl           fat

 WHERE

       1=1

   --

   AND furgd.end_date IS NULL

   --

   AND TRUNC(SYSDATE) BETWEEN

          TRUNC(furgd.start_date) AND TRUNC(NVL(furgd.end_date, SYSDATE+1))

   AND TRUNC(SYSDATE) BETWEEN

          TRUNC(frv.start_date)   AND TRUNC(NVL(frv.end_date, SYSDATE+1))

   AND TRUNC(SYSDATE) BETWEEN

          TRUNC(fu.start_date)    AND TRUNC(NVL(fu.end_date, SYSDATE+1))

   --

   AND fat.application_id       =  frv.application_id

   --

   AND furgd.responsibility_id  =  frv.responsibility_id

   AND furgd.user_id            =  fu.user_id

   --

 GROUP BY fat.application_name, frv.responsibility_name

 ORDER BY fat.application_name, frv.responsibility_name;