The blog provides useful SQL Queries for Identifying the Concurrent Request Log File details and Concurrent Request Trace File details

SQL Query to retrieve Concurrent Request Log File

SELECT  'LOGFILE_NAME=' || logfile_name
FROM    FND_CONCURRENT_REQUESTS
WHERE   request_id = &req

SELECT  'OUTFILE_NAME=' || outfile_name
FROM    FND_CONCURRENT_REQUESTS
WHERE   request_id = &req

SQL Query to retrieve Concurrent Request Log File on a specific Concurrent Manager Node 

SELECT  'LOGFILE_NODE_NAME=' || logfile_node_name
FROM    FND_CONCURRENT_REQUESTS
WHERE   request_id = &req

SELECT  'OUTFILE_NODE_NAME=' || outfile_node_name 
FROM    FND_CONCURRENT_REQUESTS
WHERE   request_id = &req

SQL Query to retrieve Concurrent Request Trace File 

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 80
SET PAGES 22
SET HEAD OFFSELECT '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
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
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;