Oracle EBS (E-Business Suite) Concurrent Request Monitoring Queries

 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;