Currently running Requests with details

set lines 180
set pages 41
col user_name for a15 trunc
col sess for a13 head "Sid,serial#"
col os_process_id for a10
col db_pid for a8
col mt_pid for a8
col dte head "Start Date"
col parent for a10 head "Parent Req"
col inst_id for 99 head "Inst"
col redo_sz_mb for a12
col duration for a11
col concurrent_queue_name for a24 trunc


SELECT fu.user_name ,
S.request_id,decode(s.is_sub_request,'N',null,s.PARENT_REQUEST_ID) parent,
decode(s.status_code,'W','Paused',s.oracle_process_id) db_pid,
decode(s.status_code,'W','Paused',nvl(s.os_process_id,gs.process)) mt_pid,
-- gs.inst_id,
decode(s.status_code,'W','Paused',gs.sid||','||gs.serial#) sess,
P.concurrent_program_name,
fcq.concurrent_queue_name,
to_char(S.actual_start_date,'DD/MM/RR HH24:MI:SS')dte,
lpad(decode(extract(DAY FROM numtodsinterval(sysdate-actual_start_date,'DAY')),
0,null,
rpad(extract(DAY FROM numtodsinterval(sysdate-actual_start_date,'DAY')),2)||'d ')||
lpad(extract(HOUR FROM numtodsinterval(sysdate-actual_start_date,'DAY')),2)||':'||
lpad(extract(MINUTE FROM numtodsinterval(sysdate-actual_start_date,'DAY')),2,'0')||':'||
lpad(round(extract(SECOND from numtodsinterval(sysdate-actual_start_date,'DAY')),0),2,'0'),11) Duration,
to_char(c.value/1024/1024,'999B999.00')  Redo_Sz_Mb
FROM apps.fnd_user fu,
apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_processes fcpr,
apps.fnd_concurrent_programs P,
    gv$session gs,
gv$process gp,
-- gv$statname b,
gv$sesstat c,
apps.fnd_concurrent_requests S
WHERE P.concurrent_program_id = S.concurrent_program_id
and c.STATISTIC#(+) = 194
--and b.inst_id(+) = c.inst_id
and c.sid (+) = gs.sid
and c.inst_id(+) = gs.inst_id
--and b.name(+)  = 'redo size'
and gp.spid(+)= s.oracle_process_id
and gp.addr=gs.paddr(+)
and gp.inst_id = gs.inst_id(+)
and s.controlling_manager = fcpr.concurrent_process_id
and fcpr.concurrent_queue_id = fcq.CONCURRENT_QUEUE_ID
and fcq.APPLICATION_ID = fcpr.QUEUE_APPLICATION_ID
AND fu.user_id = S.requested_by
AND P.application_id = S.program_application_id
AND S.phase_code = 'R'
AND upper(s.LOGFILE_NODE_NAME)= upper(nvl(substr(gs.machine,1,instr(gs.machine,'.')-1),s.LOGFILE_NODE_NAME))
order by 2;