Find Request Group, Responsibility based on concurrent program name,executable name.

Sometimes I ran into task to identify Oracle APPS responsibility or request group to which certain concurrent program is attached. Going through Oracle APPS forms is not really a straight path.

Below are few SQL scripts helping to identify that.

Find concurrent programs attached to certain responsibility.
select * from fnd_concurrent_programs_tl
where concurrent_program_id in (
select request_unit_id from fnd_request_group_units
where request_group_id in (
select request_group_id from fnd_request_groups where request_group_id in (
select request_group_id from fnd_responsibility where responsibility_key = 'YOUR RESPONSIBILITY KEY')
))
Find all responsibilities and concurrent program names based on the executable Name


select --r.rowid rrowid,p.rowid prowid,e.rowid erowid,fcp.rowid frowid,
e.executable_name,responsibility_key,user_concurrent_program_name
from fnd_responsibility r,
fnd_concurrent_programs_tl p,
fnd_request_group_units u,
fnd_executables e,
fnd_concurrent_programs fcp
where r.request_group_id=u.request_group_id
and u.request_unit_id = p.concurrent_program_id
and executable_name like '%SEARCH EXECUTABLE PATTERN%'
and fcp.executable_id = e.executable_id
and user_concurrent_program_name like '%SEARCH PATTERN%'
and fcp.concurrent_program_id = p.concurrent_program_id



Find all responsibilities having the "search concurrent program" attached to the request group
------->
select responsibility_key,user_concurrent_program_name
from fnd_responsibility r,
fnd_concurrent_programs_tl p,
fnd_request_group_units u
where r.request_group_id=u.request_group_id
and u.request_unit_id = p.concurrent_program_id
and user_concurrent_program_name like '%SEARCH PATTERN%'
---------------<
Find concurrent Program Names based on Responsibility Key Value

select * from fnd_concurrent_programs_tl
where concurrent_program_id in (
select request_unit_id from fnd_request_group_units
where request_group_id in (
select request_group_id from fnd_request_groups where request_group_id in (
select request_group_id from fnd_responsibility where responsibility_key = 'Your Responsibility Key Search Pattern')
))