Query to display long running concurrent requests

--


--

SET LINESIZE 200;

SET PAGES 9999;

SET HEAD ON;

--

SET HEAD off;

ACCEPT enddt PROMPT 'ENTER the date (ex: 01-AUG-99) > ' ;

PROMPT ;

SET HEAD on;

--

SPOOL fnd_cp_time.txt;

--

COLUMN request_id HEADING 'Request ID' FORMAT 99999999;

COLUMN pn HEADING 'Program
Name' FORMAT A40;

COLUMN qn HEADING 'Queue
Name' FORMAT A20;

COLUMN strttime HEADING 'Start
Time' FORMAT A17;

COLUMN rtime HEADING 'Elapsed
(Min)' FORMAT 9990.99;

--

SELECT r.REQUEST_ID,

DECODE(cptl.user_concurrent_program_name,

'Report Set', substr(r.description,1,40),

SUBSTR(cptl.user_concurrent_program_name,1,40)) pn,

q.concurrent_queue_name qn,

TO_CHAR(r.ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') strttime,

ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2) rtime

FROM fnd_concurrent_requests r,

fnd_concurrent_processes p,

fnd_concurrent_programs cp,

fnd_concurrent_programs_tl cptl,

fnd_concurrent_queues q

WHERE p.concurrent_queue_id = q.concurrent_queue_id

AND p.queue_application_id = q.application_id

AND r.controlling_manager = p.concurrent_process_id

AND r.phase_code = 'C'

AND r.program_application_id = cp.application_id

AND r.concurrent_program_id = cp.concurrent_program_id

AND cp.application_id = cptl.application_id

AND cp.concurrent_program_id = cptl.concurrent_program_id

AND TRUNC(ACTUAL_START_DATE) = TO_DATE(UPPER('&&enddt'),'DD-MON-YY')

ORDER BY 4

/