Trace details for a concurrent request along with the log 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
;