/**********************************************************************************************************/
/*                                                                                                        */
/* Procedure:   reponse_time.sql                                                                          */
/* Description: Provides the information for the response time chart                                      */
/*                                                                                                        */
/* Copyright (C) 2005 G Piper                                                                             */
/*                                                                                                        */
/* Version:    	2.0.1                                                                                     */
/*                                                                                                        */
/* Required:	  Requres the OAM Collection program FNDOAMCOL to be running.                             */
/*                                                                                                        */
/*                                                                                                        */
/* Written by:  Gary Piper PIPER-Rx                                                                       */
/*                                                                                                        */
/*                                                                                                        */
/* THIS PLSQL CODE IS NOT SUPPORTED BY EITHER PIPER-RX OR QUEST SOFTWARE                                  */
/*                                                                                                        */
/* DISCLAIMER                                                                                             */
/* Please refer to our Disclaimer at www.piper-rx.com                                                     */
/*                                                                                                        */
/* This program is free software: you can redistribute it and/or modify                                   */
/* it under the terms of the GNU Affero 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 Affero General Public License for more details.                                                    */
/*                                                                                                        */
/* a copy of the GNU Affero General Public Licenses provided is part of the                               */
/* down load Terms and Conditions available on our site www.piper-rx.com or                               */
/* you can refere to www.gnu.org/licenses/gpl.html                                                        */
/*                                                                                                        */
/*                                                                                                        */
/*                  *************************************************                                     */
/*                  ***          WARNING  WARNING WARNING         ***                                     */
/*                  ***      NEVER IMPLEMENT WITHOUT TESTING      ***                                     */
/*                  ***           USE AT YOUR OWN RISK            ***                                     */
/*                  *************************************************                                     */
/*                                                                                                        */
/**********************************************************************************************************/

set linesize 400
set serveroutput on size 1000000

DECLARE

type trtimeSec is table of integer index by binary_integer;
     rtimeSec trtimeSec;

type trtimeCount is table of integer index by binary_integer;
     rtimeCount trtimeCount;

type trtimeAvg is table of integer index by binary_integer;
     rtimeAvg trtimeAvg;  
  
sample_counter    number:= 47;
history_days      number := 7;

v_date            date;

avg_run_time_sec  number;
std_dev_1         number;
std_dev_2         number;
std_dev_3         number;
 
output_line       varchar(300):= ''; -- Main data line
output_line2      varchar(300):= ''; -- Average
output_line3      varchar(300):= ''; -- 1 STD DEV
output_line4      varchar(300):= ''; -- 2 STD DEV
output_line5      varchar(300):= ''; -- 3 STD DEV

-- *****************************
-- **         CURSOR          **
-- *****************************  
CURSOR data_curs is

SELECT trunc(fcr.actual_start_date) actual_start_date,
       to_char(fcr.actual_start_date, 'HH24:MI') formatted_date,
       trunc((fcr.actual_start_date - trunc(v_date)) *24 *60 ) min_since_midnight,
       (fcr.actual_completion_date - fcr.actual_start_date)*24*60*60 run_time_sec
  FROM applsys.fnd_concurrent_requests fcr
 WHERE fcr.program_application_id = 0     -- FNDOAMCOL
   and fcr.concurrent_program_id  = 44598 -- FNDOAMCOL
   and trunc(fcr.actual_start_date) =  (v_date)
   and fcr.actual_completion_date is not null;

v_data_curs data_curs%rowtype;

-- *****************************
-- **         BEGIN           **
-- *****************************
BEGIN

FOR i in 0..(history_days - 1) LOOP

SELECT trunc(sysdate - i) 
  INTO v_date
  FROM dual;
  
-- INITIALISE THE TABLE --
FOR i in 0..sample_counter LOOP
	rtimeCount(i) := '0';
  rtimeSec(i) := '0';
  rtimeAvg(i) := '0';
END LOOP;

output_line  := v_date||',';
output_line2 := 'Average'||',';
output_line3 := '1 * Std Dev'||',';
output_line4 := '2 * Std Dev'||',';
output_line5 := '3 * Std Dev'||',';

FOR v_data_curs in data_curs LOOP  

    FOR i in 0..sample_counter LOOP

        IF ((v_data_curs.min_since_midnight >= (i * 30))
             and v_data_curs.min_since_midnight < ((i+1) * 30)) THEN

           rtimeCount(i) := rtimeCount(i) + 1;
           rtimeSec(i)   := rtimeSec(i) + v_data_curs.run_time_sec;

        END IF;

   END LOOP;
   
END LOOP;

-- Calculate average run times for each run in the 30 min period
FOR i in 0..sample_counter LOOP

    IF ( rtimeCount(i) > 0 ) THEN

        rtimeAvg(i):= round((rtimeSec(i) / rtimeCount(i)),0);

    ELSE

        rtimeAvg(i):= rtimeSec(i);

    END IF;

END LOOP;

-- Build the output line
FOR i in 0..sample_counter LOOP

    output_line := output_line || rtimeAvg(i)||',';

END LOOP;

dbms_output.put_line(output_line);

END LOOP;


-- Calculate Averages and STD DEV

SELECT round(avg(((fcr.actual_completion_date - fcr.actual_start_date)*24*60*60)),0),
       round(stddev(((fcr.actual_completion_date - fcr.actual_start_date)*24*60*60)),0)
  INTO avg_run_time_sec,
       std_dev_1
  FROM applsys.fnd_concurrent_requests fcr
 WHERE fcr.program_application_id = 0     -- FNDOAMCOL
   and fcr.concurrent_program_id  = 44598 -- FNDOAMCOL
   and (sysdate  - trunc(fcr.actual_start_date)) <=  history_days;
   

FOR i in 0..sample_counter LOOP

output_line2 := output_line2 || avg_run_time_sec||',';
output_line3 := output_line3 || std_dev_1||',';
output_line4 := output_line4 || std_dev_1 *2||',';
output_line5 := output_line5 || std_dev_1 *3||',';

END LOOP;

dbms_output.put_line(output_line2);
dbms_output.put_line(output_line3);
dbms_output.put_line(output_line4);
dbms_output.put_line(output_line5);

END;