--  FILE:   fnd_cp_time.sql
--
--  AUTHOR: Andy Rivenes, arivenes@appsdba.com, www.appsdba.com
--          Copyright (C) 1999-2005 AppsDBA Consulting
--
--  DATE:   unknown
--
--  DESCRIPTION:
--          Query to display long running concurrent requests.
--          
--  MODIFICATIONS:
--          A. Rivenes, 08/09/1999, Changed elapsed time to minutes.
--          A. Rivenes, 12/08/2005, Added specific start date, changed formatting.
--   
-- 
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
--        
--
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
/