Concurrent requests information scripts

The first script will report information about your concurrent requests, such as if it's scheduled, running or completed, which users run them, request IDs, their arguments.
For example, to find all runs of Workflow Background Process program:

    SELECT F.REQUEST_ID,
         A.USER_NAME,
         DECODE (TO_CHAR (F.ACTUAL_START_DATE, 'DD/MM/YYYY HH24:MI:SS'),
                 NULL, 'PENDING',
                 TO_CHAR (F.ACTUAL_START_DATE, 'DD/MM/YYYY HH24:MI:SS'))
            DATE_STARTED,
         DECODE (
            DECODE (
               TRUNC (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE),
               0, NULL,
                  TRUNC (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE)
               || ' Days'
               || ' + ')
            || TO_CHAR (
                  TRUNC (SYSDATE)
                  + NUMTODSINTERVAL (
                       (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE) * 86400,
                       'second'),
                  'HH24:MI:SS'),
            ' Days + ', 'RUNNING',
            DECODE (
               TRUNC (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE),
               0, NULL,
                  TRUNC (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE)
               || ' Days'
               || ' + ')
            || TO_CHAR (
                  TRUNC (SYSDATE)
                  + NUMTODSINTERVAL (
                       (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE) * 86400,
                       'second'),
                  'HH24:MI:SS'))
            REQ_DURATION,
         F.COMPLETION_TEXT,
         P.CONCURRENT_PROGRAM_NAME CONC_PROGRAM,
         F.ARGUMENT_TEXT
    FROM APPLSYS.FND_CONCURRENT_REQUESTS F,
         APPLSYS.FND_USER A,
         APPLSYS.FND_CONCURRENT_PROGRAMS P,
         APPLSYS.FND_CONCURRENT_PROGRAMS_TL PT
   WHERE     A.USER_ID(+) = F.REQUESTED_BY
         AND F.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
         AND P.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_ID
         AND PT.USER_CONCURRENT_PROGRAM_NAME = 'Workflow Background Process'
         AND PT.LANGUAGE = 'US'
ORDER BY F.REQUESTED_START_DATE DESC;

REQUEST_ID	USER_NAME	DATE_STARTED	REQ_DURATION	COMPLETION_TEXT	CONC_PROGRAM	ARGUMENT_TEXT
39363160	COLL_SETUP	PENDING	RUNNING		FNDWFBG	XXSTRYRE, , , N, Y, Y
39363194	COLL_SETUP	09/07/2012 12:00:03	00:00:09	Normal completion	FNDWFBG	XXSTRYRE, , , Y, N, N
39362136	SYSADMIN	09/07/2012 11:36:10	RUNNING		FNDWFBG	OEOL, , , Y, N, N
...	...	...	...	...	...	...
38021590	SYSADMIN	09/05/2012 10:25:03	4 Days + 23:06:07		FNDWFBG	OEOL, , , Y, N, N

If DATE_STARTED has a value of PENDING, then this concurrent request has not started yet. Ignore, in this case, the RUNNING value of REQ_DURATION.

You may filter the result by using specific columns in the WHERE clause.

PHASE_CODE and STATUS_CODE of APPLSYS.FND_CONCURRENT_REQUESTS table.

PHASE_CODE
VALUE	MEANING
C	Completed
I	Inactive
P	Pending
R	Running

STATUS_CODE
VALUE	MEANING
A	Waiting
B	Resuming
C	Normal
D	Cancelled
E	Error
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

CONCURRENT_PROGRAM_NAME of APPLSYS.FND_CONCURRENT_PROGRAMS table, if you want to use its short name (FNDWFBG for our case).
ACTUAL_START_DATE of APPLSYS.FND_CONCURRENT_REQUESTS to limit the result by date.
USER_NAME of APPLSYS.FND_USER to check runs of specific users.

The second script will report the running/pending concurrent requests, to which concurrent manager are assigned, the node they are running their process IDs, log and output files, and database session information.

  SELECT
         REQ.REQUEST_ID "REQUEST ID",
         USR.USER_NAME "USER",
         QU.USER_CONCURRENT_QUEUE_NAME "CONC. MANAGER",
         REQ.REQUEST_DESCRIPTION "NAME",
         REQ.CONCURRENT_PROGRAM_NAME "SHORT NAME",
         REQ.ARGUMENT_TEXT "ARGUMENTS",
         DECODE (REQ.PHASE_CODE,
                 'R', 'RUNNING',
                 'P', 'PENDING',
                 'I', 'INCACTIVE')
            "PHASE",
         REQ.REQUEST_DATE "DATE SUBMITTED",
         REQ.REQUESTED_START_DATE "REQUESTED START DATE",
         REQ.ACTUAL_START_DATE "DATE STARTED",
         REQ.LOGFILE_NODE_NAME "NODE",
         REQ.LOGFILE_NAME "LOG",
         REQ.OUTFILE_NAME "OUTPUT",
         REQ.OS_PROCESS_ID "NODE PROCESS",
         REQ.ORACLE_PROCESS_ID "DB PROCESS",
         SS.INST_ID "DB INSTANCE",
         SS.SID "DB SID",
         SS.STATUS "DB SESSION STATUS"
    FROM APPS.FND_CONCURRENT_WORKER_REQUESTS REQ,
         APPLSYS.FND_USER USR,
         GV$SESSION SS,
         APPS.FND_CONCURRENT_QUEUES_VL QU
   WHERE     USR.USER_ID = REQ.REQUESTED_BY
         AND REQ.ORACLE_SESSION_ID = SS.AUDSID(+)
         AND REQ.CONCURRENT_QUEUE_ID = QU.CONCURRENT_QUEUE_ID
ORDER BY REQUEST_ID DESC;