RESPONSIBILITY NAME WITH MENUS DETAILS IN ORACLE APPS

-- CHECK FOR A RESPONSIBILITY NAME WITH MENUS, etc.

  SELECT frt.responsibility_id "ID"
       , frt.responsibility_name "Responsibility Name"
       , (SELECT DISTINCT COUNT (*)
                 FROM applsys.fnd_user fu
                        , apps.fnd_user_resp_groups_direct furg
                    WHERE furg.user_id = fu.user_id
                      AND frt.responsibility_id = furg.responsibility_id
                      AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE
                      AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE
                      AND fr.end_date IS NULL) user_ct
       , fa.application_id "Resp Appl ID"
       , fa.application_short_name "Application"
       , fat.application_name "Application Name"
       , fr.responsibility_key "Responsibility Key"
       , frt.description "Responsibility Description"
       , fr.start_date "Start Date"
       , DECODE (fr.version, '4', 'Oracle Applications', 'W', 'Oracle Self Service Web Applications') "Available From"
       , fdg.data_group_name "Data Group Name"
       , fat2.application_name "Data Group Application"
       , fmt.user_menu_name "Menu"
       , frg.request_group_name "Request Group Name"
       , fat3.application_name "Request Group Application"
       , fr.creation_date "Responsibility Creation Date"
       , fu.description "Responsibility Created By"
    FROM applsys.fnd_responsibility_tl frt
       , applsys.fnd_responsibility fr
       , applsys.fnd_application fa
       , applsys.fnd_application_tl fat
       , applsys.fnd_application_tl fat2
       , applsys.fnd_application_tl fat3
       , applsys.fnd_data_groups fdg
       , applsys.fnd_menus_tl fmt
       , applsys.fnd_request_groups frg
       , applsys.fnd_user fu
   WHERE fr.responsibility_id = frt.responsibility_id
     AND fr.application_id = fat.application_id
     AND fa.application_id = fat.application_id
     AND fr.data_group_application_id = fat2.application_id(+)
     AND fr.group_application_id = fat3.application_id(+)
     AND fr.data_group_id = fdg.data_group_id(+)
     AND fr.menu_id = fmt.menu_id(+)
     AND fr.request_group_id = frg.request_group_id(+)
     AND fr.end_date IS NULL
     AND frt.responsibility_name = 'System Administrator'
ORDER BY 2;

-- SIMPLE CHECK FOR A RESPONSIBILITY NAME

  SELECT frt.responsibility_name r_name
       , frt.responsibility_id id
       , frt.application_id app_id
       , fr.responsibility_key key
       , fa.application_short_name app_short
       , fat.application_name app_full
       , fr.start_date
       , fr.end_date
       , frt.description
       , frt.creation_date created_on
    FROM applsys.fnd_responsibility_tl frt
       , applsys.fnd_responsibility fr
       , applsys.fnd_application fa
       , applsys.fnd_application_tl fat
   WHERE fr.responsibility_id = frt.responsibility_id
     AND fa.application_id = fat.application_id
     AND fr.application_id = fat.application_id
     AND frt.responsibility_name = 'System Administrator'
     AND NVL (fr.end_date, SYSDATE + 1) > SYSDATE
ORDER BY frt.responsibility_name;