Trace details for a concurrent request along with the log and output file location details

Script to get Trace details for a concurrent request along with the log file and output file location details

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,

  'File Name: '

  || execname.execution_file_name

  || execname.subroutine_name,

  'Status : '

  || DECODE (phase_code, 'R', 'Running')

  || '-'

  || DECODE (status_code, 'R', 'Normal'),

  'SID Serial: '

  || ses.sid

  || ','

  || ses.serial#,

  'Module : '

  || ses.module ,

  TO_CHAR(req.actual_start_date,'DD-MON-YYYY HH24:MI:SS')

Request_start_date ,

  TO_CHAR(req.actual_completion_date,'DD-MON-YYYY HH24:MI:SS')

Request_completion_date ,

  req.logfile_name ,

  req.outfile_name ,

  req.argument_text parameters_passed

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           = :p_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

;