Oracle concurrent requests
                                                 Last update (2013-07-31 09:33:00)
                                                                                                                   Date added (2008-02-18 15:20:25)

Summary
To get a list of oracle concurrent requests (running, terminated, errored, completed) use the following query.
SELECT REQUEST_ID, a.user_name, f.ACTUAL_START_DATE DATE_STARTED, f.ACTUAL_COMPLETION_DATE DATE_FINISHED, 
/*DECODE(TRUNC(ACTUAL_COMPLETION_DATE  - ACTUAL_START_DATE), 0, NULL, TRUNC(ACTUAL_COMPLETION_DATE - f.ACTUAL_START_DATE) || ' Days' || ' + ') || 
TO_CHAR(TO_DATE(TRUNC(MOD(ACTUAL_COMPLETION_DATE-f.ACTUAL_START_DATE,1) * 86400), 'SSSSS'), 'HH24:MI:SS') REQ_DURATION,*/
NVL(COMPLETION_TEXT, 'RUNNING') COMPLETION_TEXT,
p.CONCURRENT_PROGRAM_NAME CONC_PROGRAM, ARGUMENT_TEXT 
FROM applsys.fnd_concurrent_requests f, applsys.fnd_user a, applsys.fnd_concurrent_programs p 
WHERE a.user_id (+) = f.REQUESTED_BY 
AND f.CONCURRENT_PROGRAM_ID  = p.CONCURRENT_PROGRAM_ID 
AND TRUNC(f.ACTUAL_START_DATE) > TRUNC(SYSDATE-30)
--AND request_id = 1491647
--AND phase_code = 'C' 
--AND status_code = 'C' 
--AND p.CONCURRENT_PROGRAM_NAME LIKE '%XLAACCUP%'
--AND a.user_name like 'ACHASAPI'
--AND ARGUMENT_TEXT LIKE '%TPA_Prov_IN%'
--AND completion_text = 'Normal completion'
ORDER BY requested_start_date DESC;
You can filter for example the Pending with phase_code = 'P'
With the same way the Terminated requests by the user are with status_code = 'X'
The Terminated by error are with status_code='E'

Especially for the Running requests you can relate them with the oracle database session id.
--Running requests related with oracle sessions
SELECT REQUEST_ID, a.user_name, f.ACTUAL_START_DATE DATE_STARTED, 
DECODE(TRUNC(sysdate  - ACTUAL_START_DATE), 0, NULL, TRUNC(sysdate - f.ACTUAL_START_DATE) || ' Days' || ' + ') || 
TO_CHAR(TO_DATE(TRUNC(MOD(sysdate-f.ACTUAL_START_DATE,1) * 86400), 'SSSSS'), 'HH24:MI:SS') REQ_DURATION, 
s.SID, s.SERIAL#, s.status, p.SPID, s.MODULE,
p.CONCURRENT_PROGRAM_NAME CONC_PROGRAM, ARGUMENT_TEXT 
FROM applsys.fnd_concurrent_requests f, applsys.fnd_user a, applsys.fnd_concurrent_programs p, 
v$process p, v$session s
WHERE a.user_id (+) = f.REQUESTED_BY 
AND f.CONCURRENT_PROGRAM_ID  = p.CONCURRENT_PROGRAM_ID 
AND TRUNC(f.ACTUAL_START_DATE) > TRUNC(SYSDATE-30)
--AND request_id = 1491647
AND phase_code = 'R' 
AND s.paddr = p.addr
AND f.ORACLE_PROCESS_ID = p.spid 
--AND status_code = 'C' 
--AND p.CONCURRENT_PROGRAM_NAME LIKE '%XLAACCUP%'
--AND a.user_name like 'ACHASAPI'
--AND ARGUMENT_TEXT LIKE '%TPA_Prov_IN%'
--AND completion_text = 'Normal completion'
ORDER BY requested_start_date DESC;
The table APPLSYS.FND_CONCURRENT_REQUESTS has the concurrent requests.
The explanation for columns STATUS_CODE and PHASE_CODE are:
STATUS_CODE column:
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

PHASE_CODE column:
C Completed
I Inactive
P Pending
R Running
In case you want to kill the oracle session related with the request with the alter system kill session command, then after the kill session, you will continue to see it Running from the applications screen.

You can update the FND_CONCURRENT_REQUESTS table to make it looks like completed (Canceled).
UPDATE APPLSYS.FND_CONCURRENT_REQUESTS 
SET STATUS_CODE='D', 
PHASE_CODE='C' 
WHERE REQUEST_ID = :request_id;
You can do the same with the Terminated requests
SELECT REQUEST_ID FROM APPLSYS.FND_CONCURRENT_REQUESTS
WHERE STATUS_CODE='T' AND PHASE_CODE='R'; 
Reviews
Filters
Search