Find Request Set Name, Conc Manager given a request ID

Given a request id, how does one find out the program name and the manager running the request? Most of the Metalink notes and blogs provide the answer to this question but for one aspect. What is that? Read on to find out.

What the SQL queries available on the web and Metalink lack is that they provide the concurrent program name for a single request given the request id. What about for a request set? Do they provide the answer? No, they don't.

I have tried here to cover this aspect too. So, here goes.

Note: This query works for all request ids, not only for request ids for request sets.

select request_id,fcpv.CONCURRENT_PROGRAM_NAME "PROGRAM_NAME",
nvl (fcr.description,fcpv.user_concurrent_program_name) "USER_PROGRAM_NAME",
decode(fcpv.REQUEST_SET_FLAG,'N','SINGLE_REQUEST','Y','REQUEST_SET') "PROGRAM_TYPE",
fcqv.CONCURRENT_QUEUE_NAME, fcqv.USER_CONCURRENT_QUEUE_NAME
from
fnd_concurrent_requests fcr, fnd_concurrent_programs_vl fcpv, fnd_concurrent_processes fcp, fnd_concurrent_queues_vl fcqv
where
fcr.concurrent_program_id = fcpv.concurrent_program_id (+)
and fcr.controlling_manager = fcp.CONCURRENT_PROCESS_ID
and fcp.concurrent_queue_id = fcqv.concurrent_queue_id
and fcr.request_id = &request_id;