Oct 14, 2013

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;


No comments: