Oracle EBS (E-Business Suite) Concurrent Request Monitoring Queries
1. Check Currently Running Concurrent Requests
-- Display details of currently running concurrent requests
SET PAGES 1000 LINES 1000 PAGESIZE 1000;
COL program FOR a80;
SELECT
a.request_id,
DECODE(a.phase_code, 'R', 'Running', 'Normal') AS Phase,
TO_CHAR(a.actual_start_date, 'DD-MON-RRRR HH24:MI:SS') AS start_time,
SUBSTR(b.user_concurrent_program_name, 1, 80) AS program
FROM
apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs_tl b
WHERE
a.concurrent_program_id = b.concurrent_program_id
AND a.status_code = 'R'
ORDER BY
start_time;
2. Check Running Requests With SID and Session Info
-- Show session and OS details of currently running concurrent requests
SET LINES 200
SET PAGES 1000
COL spid FOR a10
COL process FOR a10
COL pgmname FOR a50
COL username FOR a15
COL args FOR a50
COL sid FOR 9999
SELECT
fcr.request_id,
fcpt.user_concurrent_program_name AS pgmname,
TO_CHAR(fcr.actual_start_date, 'DD-MON-YY HH24:MI:SS') AS stdate,
s.sid,
s.serial#,
s.status,
TO_CHAR(s.logon_time, 'DD-MON-YY HH24:MI:SS') AS logon,
ROUND(s.last_call_et / 60) AS LCT, -- Last Call Time in minutes
fu.user_name AS username
FROM
applsys.fnd_concurrent_requests fcr,
applsys.fnd_concurrent_programs_tl fcpt,
gv$session s,
gv$process p,
applsys.fnd_user fu
WHERE
fcr.phase_code = 'R'
AND fcr.status_code IN ('R','T','W')
AND s.process (+) = fcr.os_process_id
AND s.inst_id = p.inst_id (+)
AND s.paddr = p.addr (+)
AND p.spid = fcr.oracle_process_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.program_application_id = fcpt.application_id
AND fcpt.language = USERENV('LANG')
AND fcr.requested_by = fu.user_id
AND fcpt.user_concurrent_program_name NOT IN ('Report Set', 'Request Set Stage')
ORDER BY
logon DESC;
3. Running Requests with Argument Details
-- Shows running requests along with their input arguments
SET LINES 200
SET PAGES 1000
COL program FOR a40
COL user_name FOR a20
SELECT
a.request_id,
DECODE(a.phase_code, 'R', 'Running', 'Normal') AS Phase,
TO_CHAR(a.actual_start_date, 'DD-MON-RRRR HH24:MI:SS') AS start_time,
SUBSTR(b.user_concurrent_program_name, 1, 40) AS program,
SUBSTR(a.argument_text, 1, 60) AS argument,
c.user_name
FROM
apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs_tl b,
apps.fnd_user c
WHERE
a.concurrent_program_id = b.concurrent_program_id
AND a.status_code = 'R'
AND a.requested_by = c.user_id
ORDER BY
start_time;
4. Historical Completed Requests for a Specific Program
-- Show completed requests for a given program name within the last 7 days
SET LINES 200
SET PAGES 1000
COL user_name FOR a20
COL argument_text FOR a60
SELECT
a.request_id,
TO_CHAR(a.actual_start_date, 'DD-MON HH24:MI') AS start_date,
TO_CHAR(a.actual_completion_date, 'DD-MON HH24:MI') AS end_date,
ROUND(((a.actual_completion_date - a.actual_start_date) * 24 * 60 * 60 / 60), 2) AS process_time_mins,
a.phase_code AS P,
a.status_code AS S,
a.argument_text,
d.user_name
FROM
apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b,
apps.fnd_concurrent_programs_tl c,
apps.fnd_user d
WHERE
a.concurrent_program_id = b.concurrent_program_id
AND b.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = d.user_id
AND TRUNC(a.actual_completion_date) >= TRUNC(SYSDATE - 7)
AND c.user_concurrent_program_name LIKE '%&Concurrent_program_name%'
ORDER BY
start_date ASC;
5. Last 300 Days Completion Status for a Specific Program
-- Query to list executions of a specific program within the last 300 days with arguments
SELECT
TO_CHAR(actual_start_date, 'DD-MON-YY HH24:MI:SS') AS start_date,
TO_CHAR(actual_completion_date, 'DD-MON-YY HH24:MI:SS') AS end_date,
phase_code,
status_code,
ROUND(((actual_completion_date - actual_start_date) * 24 * 60 * 60 / 60), 2) AS process_time_mins,
request_id,
TO_CHAR(request_date, 'DD-MON-YY HH24:MI:SS') AS request_date,
requestor,
argument_text,
program
FROM
xxfmit.FM_SUMMARY_DETAILS
WHERE
program LIKE '%FM GSTR-2 Report Format-1%'
AND TRUNC(actual_completion_date) >= TRUNC(SYSDATE - 300)
AND argument_text LIKE '%27AAACF2630E1Z5, , NOV-22%'
ORDER BY
request_id DESC;
