--  FILE:   fnd_pgm_RP.sql
--
--  AUTHOR: Andy Rivenes, arivenes@appsdba.com, www.appsdba.com
--          Copyright (C) 2005 AppsDBA Consulting
--
--  DATE:   12/05/2005
--
--  DESCRIPTION:
--          Query to display long running concurrent requests in a
--          resource profile type format.
--          
--  MODIFICATIONS:
-- 
-- 
-- 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 TRIMSpool on;
SET PAGES 9999;
SET HEAD on;
--
SET HEAD off;
ACCEPT enddt  PROMPT 'ENTER the date (ex: 01-DEC-05) > ' ;
PROMPT ;
SET HEAD on;
--
SPOOL fnd_pgm_RP.txt;
--
PROMPT Concurrent Program Profile for &&enddt ;
--
COLUMN pn           HEADING 'Program|Name'      FORMAT A50 WORD_WRAPPED;
COLUMN qn           HEADING 'Queue|Name'        FORMAT A20;
COLUMN cnt          HEADING 'Total|Jobs'        FORMAT 999,990;
COLUMN tott         HEADING 'Total|Time(Min)'   FORMAT 9990.99;
COLUMN mint         HEADING 'Min|Time(Min)'     FORMAT 9990.99;
COLUMN avgt         HEADING 'Avg|Time(Min)'     FORMAT 9990.99;
COLUMN maxt         HEADING 'Max|Time(Min)'     FORMAT 9990.99;
--
--BREAK ON REPORT
--COMPUTE SUM LABEL "Total Requests" OF cnt ON REPORT
--
BREAK ON qn SKIP 1 ON REPORT
COMPUTE SUM LABEL "Queue Totals" OF cnt ON qn
COMPUTE SUM LABEL "Total Requests" OF cnt ON REPORT
--
SELECT DECODE(cptl.user_concurrent_program_name,
              'Report Set', r.description,
              cptl.user_concurrent_program_name) pn,
       q.concurrent_queue_name qn,
       COUNT(r.REQUEST_ID) cnt,
       SUM(ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2)) tott,
       MIN(ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2)) mint,
       AVG(ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2)) avgt,
       MAX(ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2)) maxt
  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')
 GROUP BY
       DECODE(cptl.user_concurrent_program_name,
              'Report Set', r.description,
              cptl.user_concurrent_program_name),
       q.concurrent_queue_name
 ORDER BY 2, 3
/