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
