To Get the request to which the conc program is attached.

select e.* from fnd_concurrent_programs_tl a,
fnd_concurrent_programs b ,
fnd_request_group_units c,
fnd_responsibility d,
fnd_responsibility_tl e
where a.user_CONCURRENT_PROGRAM_NAME =  :P_CONC_PROGRAM_NAME
and a.CONCURRENT_PROGRAM_ID= b.CONCURRENT_PROGRAM_ID
and b.CONCURRENT_PROGRAM_ID = c.REQUEST_UNIT_ID
and c.request_group_id = d.request_group_id
and c.application_id = d.application_id
and d.RESPONSIBILITY_ID = e.RESPONSIBILITY_ID
and e.LANGUAGE = 'US'
and a.LANGUAGE = 'US'