PID and SID details of a Concurrent Request

-- ----------------------------------------------------------------------------------------------------------------
-- Purpose : Getting the Process ID, session and terminal details for the Concurrent request.
-- Date      : 30.May.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------------------

SELECT req.request_id ,
prog.USER_CONCURRENT_PROGRAM_NAME Conc_prg_Name,
fu.USER_NAME requested_by,
fr.RESPONSIBILITY_KEY responsibility,
ses.LOGON_TIME,
ses.sid,
ses.serial#,
proc.SPID,
proc.pid,
ses.USERNAME "User",
ses.STATUS,
ses.sql_hash_value sql_hash,
proc.USERNAME osuser,
ses.MACHINE,
nvl(ses.TERMINAL,'Not Available') Terminal,
ses.MODULE,
ses.ACTION,
ses.MODULE_HASH,
nvl(ses.PROGRAM,'Not Available') Program
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_responsibility fr,fnd_user fu,
fnd_executables execname
where
1=1
and req.request_id = :request_id
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and req.RESPONSIBILITY_ID = fr.RESPONSIBILITY_ID
and fu.USER_ID = req.REQUESTED_BY
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

-- --------------------------------------------------------------------------------