Concurrent Request Info

----------------------------------
-Concurrent Request Details
-Prompts for Request Id
-cr_info.sql
-usage : @cr_info
-----------------------------------

column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 100
set pages 22
set head off

SELECT '****************************************************************************************
Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'Execution Method  :' ||DECODE (execname.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
'N/A'
),'Executable Name  : '||execname.execution_file_name|| execname.subroutine_name,
'Phase : '||DECODE (req.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
'N/A'
)||' | Status : '||DECODE (req.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
'N/A'
),
'PID :'||proc.spid || ' | SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module,
'Actual Start Date :'||to_char(req.actual_start_date,'DD-MON-YYYY HH24:MI:SS')||' | Actual Completion Date :'||to_char(req.actual_completion_date,'DD-MON-YYYY HH:MI:SS'),
'Duration Hours :'||floor(((req.actual_completion_date-req.actual_start_date)*24*60*60)/3600)||
' Min :'||floor((((req.actual_completion_date-req.actual_start_date)*24*60*60) -
floor(((req.actual_completion_date-req.actual_start_date)*24*60*60)/3600)*3600)/60)||
' Sec :'||round((((req.actual_completion_date-req.actual_start_date)*24*60*60) -
floor(((req.actual_completion_date-req.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((req.actual_completion_date-req.actual_start_date)*24*60*60) -
floor(((req.actual_completion_date-req.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )),
'Log File Name : '||req.logfile_name,
'Out File Name : '||req.outfile_name,
'****************************************************************************************'
from apps.fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog,
apps.fnd_executables execname
where 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 prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id
/