Extract the trace Files for Concurrent program

Extract the trace Files for Concurrent program

Modules Involved: Application object library(AOL)
Purpose         : To extract the trace Files for Concurrent program
Description     : This query needs the parameter Request_Id.
****************************************************************** */
select 'Request id:'||REQUEST_ID as "Request_Id",
'Trace id: '||ORACLE_PROCESS_ID as "Trace Id",
'Trace Flag: '||REQ.ENABLE_TRACE as "Trace Flag",
'Trace Name: '||DEST.value||'/'||LOWER(DBNM.value)||'_ora_'||ORACLE_PROCESS_ID||'.trc' as "Trace Path",
'Prog. Name: '||PROG.USER_CONCURRENT_PROGRAM_NAME as "Concurrent Program Name",
'File Name: '||EXECNAME.EXECUTION_FILE_NAME|| EXECNAME.SUBROUTINE_NAME as "File name",
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal') as "Status",
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
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