Wednesday 10 June 2015

Excluded menu and function in Responsibility Query in oracle apps

This query can give details of a Oracle Application Responsibility and list of menus and functions that are excluded from that responsibility.

select res.responsibility_name
     , app.application_name
     , res.responsibility_key
     , res.description
     , res.start_date
     , res.end_date
     , dat.data_group_name
     , apd.application_name
     , mnu.menu_name
     , req.request_group_name
     , apr.application_name
     , decode(exc.rule_type,'F','Function','M', 'Menu', rule_type) Exclusion_Type
     , decode(exc.rule_type,'F',(select function_name || ',' || description
                                   from fnd_form_functions_vl fnc
                                  where fnc.function_id = exc.action_id
                                )
                           ,'M',(select menu_name || ',' || description
                                   from fnd_menus_vl imn
                                   where imn.menu_id = exc.action_id
                                )
                           , to_char(exc.action_id)
             ) Excluded_Menu_Or_func
  from apps.fnd_responsibility_vl res
     , apps.fnd_application_vl    app
     , apps.fnd_data_groups       dat
     , apps.fnd_menus_vl          mnu
     , apps.fnd_request_groups    req
     , apps.fnd_application_vl    apd
     , apps.fnd_application_vl    apr
     , apps.fnd_resp_functions    exc
 where res.application_id            = app.application_id
   and res.data_group_id             = dat.data_group_id
   and res.data_group_application_id = apd.application_id
   and res.menu_id                   = mnu.menu_id
   and req.request_group_id          = res.request_group_id
   and req.application_id            = res.group_application_id
   and apr.application_id            = req.application_id
   and exc.application_id            = res.application_id
   and exc.responsibility_id         = res.responsibility_id ;

No comments:

Post a Comment