Query To Get Responsibility Name, Request Group and Request Set Name and Concurrent Program Details.

SELECT   frv.responsibility_name,
        frg.request_group_name,
        frgu.request_unit_type,
        frgu.request_unit_id,
        fcpt.user_concurrent_program_name,
        null request_set_name
FROM    fnd_responsibility_vl frv
        ,fnd_request_groups frg
        ,FND_REQUEST_GROUP_UNITS frgu
        ,fnd_concurrent_programs fcp
        ,fnd_concurrent_programs_tl fcpt
WHERE  frv.responsibility_name =nvl(:RESP_NAME,frv.responsibility_name)
AND    frv.request_group_id= frg.request_group_id
AND    frgu.request_group_id= frg.request_group_id
AND    fcpt.application_id= frgu.unit_application_id
AND    fcp.concurrent_program_id= frgu.request_unit_id
AND    fcpt.concurrent_program_id=fcp.concurrent_program_id
and frg.application_id in (101,140,260,222,200)

union

SELECT   frv.responsibility_name,
        frg.request_group_name,
        frgu.request_unit_type,
        frgu.request_unit_id,
        fcp.user_concurrent_program_name
        ,frsv.request_set_name request_set_name
FROM    fnd_responsibility_vl frv
        ,fnd_request_groups frg
        ,FND_REQUEST_GROUP_UNITS frgu
        ,FND_REQUEST_SETS_VL frsv
        ,FND_REQUEST_SET_PROGRAMS frsp
        ,fnd_concurrent_programs_tl fcp
WHERE  frv.responsibility_name = nvl(:RESP_NAME,frv.responsibility_name)
AND    frv.request_group_id= frgu.request_group_id
AND    frgu.request_group_id= frg.request_group_id
AND frsv.request_set_id= frgu.request_unit_id
and frsp.request_set_id= frsv.request_set_id
and frsp.concurrent_program_id= fcp.concurrent_program_id
and frg.application_id in (101,140,260,222,200)
;