-- FILE:   fnd_cp_ovlp_bymgr.sql
--
-- AUTHOR: Andy Rivenes, arivenes@appsdba.com, www.appsdba.com
--         Copyright (C) 2006 AppsDBA Consulting
--
-- DATE:   01/17/2006
--
-- DESCRIPTION:
--         Query to show concurrent program overlap by concurrent manager
--         for a given hour.
--
-- MODIFICATIONS:
--         09/18/2006, A. Rivenes, General cleanup and added more comments.
--         10/16/2006, A. Rivenes, Updated date formats to catch all overlaps
--           and simplified interval calculations with Jerry Ireland's suggestions.
--         10/18/2006, A. Rivenes, Further simplified with more of Jerry's
--           suggestions.
--
-- 
-- 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 SERVEROUTPUT on;
--
SET HEAD off;
ACCEPT var_date  PROMPT 'ENTER the interval date (format: MM/DD/YYYY) > ' ;
ACCEPT var_hr    PROMPT 'ENTER the interval hour (format: HH24) > ' ;
ACCEPT var_mgr   DEFAULT '%' PROMPT 'ENTER the CM queue (format: INVMGR, leave blank for all) > ' ;
PROMPT ;
SET HEAD on;
--
SPOOL fnd_ovlp_bymgr.txt;
--
DECLARE
-- Cursor 1:
--
-- Determine queues with jobs that were active queues during any part 
-- of the interval period
--
CURSOR cm_queue_cur(var_intvl VARCHAR, var_queue VARCHAR) IS
  SELECT
    DISTINCT q.concurrent_queue_name CMQUEUE
  FROM
    applsys.fnd_concurrent_requests r,
    applsys.fnd_concurrent_processes p,
    applsys.fnd_concurrent_queues q
  WHERE
    ( r.ACTUAL_START_DATE <= TO_DATE(var_intvl||':59:59','MM/DD/YYYY HH24:MI:SS')
      AND r.ACTUAL_COMPLETION_DATE >= TO_DATE(var_intvl||':00:00','MM/DD/YYYY HH24:MI:SS') )
    AND q.concurrent_queue_name LIKE var_queue
    AND R.controlling_manager = P.concurrent_process_id
    AND p.concurrent_queue_id = q.concurrent_queue_id
    AND p.queue_application_id = q.application_id
  ORDER BY
    q.concurrent_queue_name;
--
--
-- Cursor 2:
--
-- Get all requests by queue_name, program and run time
-- Use input parameters for date and queue
--
--   STIM - Start time
--   SMIN - Start minute
--   RMIN - Run minutes (interval duration)
--
CURSOR cm_runtime_cur(var_intvl VARCHAR, var_queue VARCHAR) IS
  SELECT
    q.concurrent_queue_name CMQUEUE,
    DECODE(cptl.user_concurrent_program_name,
      'Report Set', r.description,
      cptl.user_concurrent_program_name) CMPROG,
    r.ACTUAL_START_DATE STIM,
    TO_CHAR(GREATEST(r.ACTUAL_START_DATE,TO_DATE(var_intvl||':00:00','MM/DD/YYYY HH24:MI:SS')),'MI') SMIN,
    CEIL((LEAST(r.ACTUAL_COMPLETION_DATE,TO_DATE(var_intvl||':59:59','MM/DD/YYYY HH24:MI:SS'))
      - GREATEST(r.ACTUAL_START_DATE,TO_DATE(var_intvl||':00:00','MM/DD/YYYY HH24:MI:SS')))*60*24) RMIN
  FROM
    applsys.fnd_concurrent_requests r,
    applsys.fnd_concurrent_processes p,
    applsys.fnd_concurrent_queues q,
    applsys.fnd_concurrent_programs_tl cptl
  WHERE
    ( r.ACTUAL_START_DATE <= TO_DATE(var_intvl||':59:59','MM/DD/YYYY HH24:MI:SS')
      AND r.ACTUAL_COMPLETION_DATE >= TO_DATE(var_intvl||':00:00','MM/DD/YYYY HH24:MI:SS') )
    AND q.concurrent_queue_name = var_queue
    AND R.controlling_manager = P.concurrent_process_id
    AND p.concurrent_queue_id = q.concurrent_queue_id
    AND p.queue_application_id = q.application_id
    AND r.program_application_id = cptl.application_id
    AND r.concurrent_program_id = cptl.concurrent_program_id
  ORDER BY
    STIM,
    RMIN;
  --
  var_runtime VARCHAR2(61);
  var_filler CHAR(1) := '+';
  ctr INTEGER;
  --
BEGIN
  --
  DBMS_OUTPUT.ENABLE(1000000);
  --
  -- Loop for each active queue during requested interval (e.g. hour)
  --
  FOR cm_queue_rec IN cm_queue_cur('&&var_date'||' '||'&&var_hr','&&var_mgr') LOOP
    --
    DBMS_OUTPUT.PUT_LINE('Processing requests for concurrent queue: '||cm_queue_rec.CMQUEUE);
    DBMS_OUTPUT.PUT_LINE(CHR(13));
    DBMS_OUTPUT.PUT_LINE('Time                                      1         2         3         4         5');
    DBMS_OUTPUT.PUT_LINE('Min:                            012345678901234567890123456789012345678901234567890123456789');
    --
    -- Create overlap view for each queue
    --
    FOR cm_runtime_rec IN cm_runtime_cur('&&var_date'||' '||'&&var_hr', cm_queue_rec.CMQUEUE) LOOP
      var_runtime := '';
      --
      -- Blank pad until start minute
      --
      FOR ctr IN 0..TO_NUMBER(cm_runtime_rec.SMIN) LOOP
        var_runtime := var_runtime||' ';
      END LOOP;
      --
      -- Mark run time
      --
      FOR ctr IN 1..TO_NUMBER(cm_runtime_rec.RMIN) LOOP
        var_runtime := var_runtime||var_filler;
      END LOOP;
      --
      -- Output record
      --
      DBMS_OUTPUT.PUT_LINE(RPAD(cm_runtime_rec.CMPROG,30,' ')||' '||var_runtime);
    END LOOP;
    --
    DBMS_OUTPUT.PUT_LINE(CHR(13));
  END LOOP;
END;
/
--
SPOOL off;