SQL Script to pick responsibilities and other details

Responsibility and menu exclusions
----------------------------------------------------------------------------
SELECT DISTINCT frt.responsibility_name ,
  (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1
  WHERE application_id=fr.application_id
  AND fat1.language   ='US'
  ) Resp_Application_Name,
  frt.description ,
  fr.responsibility_key ,
  fm.menu_name ,
  fdg.data_group_name ,
 (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1,apps.fnd_responsibility_vl frvl
  WHERE fat1.application_id=frvl.data_group_application_id
  and frvl.responsibility_id=fr.responsibility_id
  AND fat1.language   ='US'
  ) Data_grp__Application_Name,
  frg.request_group_name ,
  (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1
  WHERE application_id=frg.application_id
  AND fat1.language   ='US'
  ) Req_grp__Application_Name
  ,frf.rule_type Exclusions_Type
   ,fff.user_function_name Exclusions
   ,fff.function_name function_name
  ,fr.start_date
  ,fr.end_date
FROM apps.fnd_responsibility_tl frt ,
  apps.fnd_application_tl fat,
  apps.fnd_responsibility fr,
  apps.fnd_menus fm,
  apps.fnd_data_groups fdg,
  apps.fnd_request_groups frg,
  apps.fnd_form_functions_vl fff,
  apps.fnd_resp_functions frf
WHERE 1                      =1
AND fat.application_id       =fr.application_id
AND fr.responsibility_id     =frt.responsibility_id
AND fm.menu_id               =fr.menu_id
AND fdg.data_group_id        =fr.data_group_id
AND frg.request_group_id(+)  =fr.request_group_id
AND frf.responsibility_id(+) =fr.responsibility_id
AND frf.action_id            =fff.function_id(+)
AND frt.responsibility_name LIKE '%XXXtest%'
AND rule_type='F'
UNION ALL
SELECT DISTINCT frt.responsibility_name ,
  (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1
  WHERE application_id=fr.application_id
  AND fat1.language   ='US'
  ) Resp_Application_Name,
  frt.description ,
  fr.responsibility_key ,
  fm.menu_name ,
  fdg.data_group_name ,
 (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1,apps.fnd_responsibility_vl frvl
  WHERE fat1.application_id=frvl.data_group_application_id
  and frvl.responsibility_id=fr.responsibility_id
  AND fat1.language   ='US'
  ) Data_grp__Application_Name,
  frg.request_group_name ,
  (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1
  WHERE application_id=frg.application_id
  AND fat1.language   ='US'
  ) Req_grp__Application_Name
  ,frf.rule_type Exclusions_Type
  ,FMV.MENU_NAME Exclusions
  ,null function_name
  ,fr.start_date
  ,fr.end_date
FROM apps.fnd_responsibility_tl frt ,
  apps.fnd_application_tl fat,
  apps.fnd_responsibility fr,
  apps.fnd_menus fm,
  apps.fnd_data_groups fdg,
  apps.fnd_request_groups frg,
  apps.fnd_resp_functions frf,
  APPS.FND_MENUS_VL FMV
WHERE 1                      =1
AND fat.application_id       =fr.application_id
AND fr.responsibility_id     =frt.responsibility_id
AND fm.menu_id               =fr.menu_id
AND fdg.data_group_id        =fr.data_group_id
AND frg.request_group_id(+)  =fr.request_group_id
AND frf.responsibility_id(+) =fr.responsibility_id
AND frf.action_id            =FMV.MENU_id(+)
AND frt.responsibility_name LIKE '%XXXtest%'
AND rule_type='M'
 UNION ALL
SELECT DISTINCT frt.responsibility_name ,
  (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1
  WHERE application_id=fr.application_id
  AND fat1.language   ='US'
  ) Resp_Application_Name,
  frt.description ,
  fr.responsibility_key ,
  fm.menu_name ,
  fdg.data_group_name ,
 (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1,apps.fnd_responsibility_vl frvl
  WHERE fat1.application_id=frvl.data_group_application_id
  and frvl.responsibility_id=fr.responsibility_id
  AND fat1.language   ='US'
  ) Data_grp__Application_Name,
  frg.request_group_name ,
  (SELECT DISTINCT application_name
  FROM apps.fnd_application_tl fat1
  WHERE application_id=frg.application_id
  AND fat1.language   ='US'
  ) Req_grp__Application_Name
  ,NULL Exclusions_Type
  ,null function_name
  ,NULL Exclusions
  ,fr.start_date
  ,fr.end_date
FROM apps.fnd_responsibility_tl frt ,
  apps.fnd_application_tl fat,
  apps.fnd_responsibility fr,
  apps.fnd_menus fm,
  apps.fnd_data_groups fdg,
  apps.fnd_request_groups frg  
WHERE 1                      =1
AND fat.application_id       =fr.application_id
AND fr.responsibility_id     =frt.responsibility_id
AND fm.menu_id               =fr.menu_id
AND fdg.data_group_id        =fr.data_group_id
AND frg.request_group_id(+)  =fr.request_group_id
AND fr.responsibility_id NOT IN(SELECT responsibility_id FROM  APPS.fnd_resp_functions)
AND frt.responsibility_name LIKE '%XXXtest%'

------------------------------------------------------------------------------------------------------

Responsibility and Profiles

select distinct
 b.user_profile_option_name "Profile option  Name"
 , a.profile_option_name "Short Name"
 , decode(to_char(c.level_id),'10001','Site'
 ,'10002','Application'
 ,'10003','Responsibility'
 ,'10004','User'
 ,'Unknown') "Level"
 , decode(to_char(c.level_id),'10001','Site'
 ,'10002',nvl(h.application_short_name,to_char(c.level_value))
 ,'10003',nvl(g.responsibility_name,to_char(c.level_value))
 ,'10004',nvl(e.user_name,to_char(c.level_value))
 ,'Unknown') "Level Value"
 , c.PROFILE_OPTION_VALUE "Profile Value"
 --, c.profile_option_id "Profile ID"
 --, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
 --, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
 from
 apps.fnd_profile_options a
 , apps.FND_PROFILE_OPTIONS_VL b
 , apps.FND_PROFILE_OPTION_VALUES c
 --, apps.FND_USER d
 , apps.FND_USER e
 , apps.FND_RESPONSIBILITY_VL g
 , apps.FND_APPLICATION h
 where 1=1
 --a.application_id = nvl(401, a.application_id)
 --and a.profile_option_name = nvl('INV', a.profile_option_name)
 --b.user_profile_option_name like '&ProfileName' -- 'AFLOG_ENABLED'
 and a.profile_option_name = b.profile_option_name
 and a.profile_option_id = c.profile_option_id
 and a.application_id = c.application_id
 --and c.last_updated_by = d.user_id(+)
 and c.level_value = e.user_id(+)
 and c.level_value = g.responsibility_id(+)
 and c.level_value = h.application_id(+)
 and g.responsibility_name LIKE '%XXXtest%'--='Bills of Material PXR DE Eching' --
 and c.level_id='10003'