Rem
Rem $Header: chronos_analyze_pkgbody.sql 03-jun-2008.12:16:38 jashukla Exp $
Rem
Rem chronos_analyze_pkgbody.sql
Rem
Rem Copyright (c) 2003, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      chronos_analyze_pkgbody.sql - package body file for chronos analyze feature 
Rem
Rem    DESCRIPTION
Rem      package body for chronos analyze feature 
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jashukla    06/03/08 - Bug 7144619 remove internal identifiers
Rem    yxie        06/06/05 - changes depends_on to supports_eum_on 
Rem    eporter     05/20/05 - Bug 4358373: SQL injection on Advanced Analyze 
Rem    pmaddi      04/07/05 - Get URL name for WebCache Type 
Rem    yxie        04/15/05 - changing contains to depends_on
Rem    eporter     04/13/05 - Remove dependencies on target names 
Rem    eporter     04/10/05 - Do not only use webcaches 
Rem    eporter     03/08/05 - Fix bug when multiple domains are selected 
Rem    eporter     02/14/05 - Change Anvanced Analyze implementation
Rem    eporter     02/02/05 - Change Advanced Analyze parameters 
Rem    ratadiko    02/02/05 - Adding a wrapper over CHRONOS_GET_ANALYZE_DATA 
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    eporter     12/14/04 - Allow custom time ranges 
Rem    eporter     12/03/04 - Bug 3849061 - Change region implementation 
Rem    adosani     10/13/04 - Additions for Oracle Forms Webapp Target Type 
Rem    jsamal      09/29/04 - 
Rem    eporter     09/28/04 - Add advanced analyze function 
Rem    ramalhot    08/25/04 - cutover to new assoc tables 
Rem    jsamal      07/16/04 - Checking out to implement the new feature. 
Rem    snakai      09/26/03 - fix region queries 
Rem    rmarripa    09/26/03 - return dates for collection timestamp 
Rem    rmarripa    09/22/03 - case insensitive search 
Rem    rmarripa    09/18/03 - fix analyze max rows 
Rem    snakhoda    09/17/03 - limit rows returned by analyze query 
Rem    snakhoda    09/17/03 - add search capability 
Rem    snakhoda    09/11/03 - code review changes
Rem    snakhoda    09/10/03 - fix region and visitor matching bugs 
Rem    snakhoda    08/20/03 - snakhoda_bug-3081195_main 
Rem    snakhoda    08/08/03 - changes to regions and webcache filtering
Rem    mashukla    08/07/03 - Created
Rem


CREATE OR REPLACE Package body EMD_CHRONOS_ANALYZE
AS

PROCEDURE GET_ANALYZE_DATA_BY_URL(
              target_name_in             IN  VARCHAR2,
              target_type_in             IN  VARCHAR2,
              fixed_url_axis_in          IN  VARCHAR2,
              filter_axis_in             IN  VARCHAR2,
              max_coll_time_in           IN  DATE,
              min_coll_time_in           IN  DATE,
              filter_data_out            OUT p_cursor_type)
IS

i PLS_INTEGER;
j PLS_INTEGER;

website_target_guid RAW(16);
analyze_max_rows PLS_INTEGER;
srch_str_length PLS_INTEGER;
srch_str_upper VARCHAR2(1024);

v_min_coll_time DATE;
v_max_coll_time DATE;

url_filename VARCHAR2(1024);

url_filenames VARCHAR(1024);

fixed_url_axis_members MgmtChrnsAnlzFxdAxesMembers := null;
new_str VARCHAR2(50);
cursor_data p_cursor_type;

BEGIN

--
-- select website target guid for target name
--
SELECT target_guid INTO website_target_guid FROM MGMT_TARGETS 
 WHERE target_name=target_name_in and target_type=target_type_in;

dbms_output.put_line('target_guid ' || website_target_guid);
--
-- get max number of rows to return
--
BEGIN
  SELECT to_number(nvl(parameter_value,0))
     INTO analyze_max_rows 
    FROM MGMT_PARAMETERS
    WHERE parameter_name = 'mgmt_rt_analyze_max_rows';
EXCEPTION
  WHEN OTHERS THEN
    analyze_max_rows := max_rows;
END;

if(analyze_max_rows <= 0) then
   analyze_max_rows := max_rows;
end if;


--
-- copy url fixed axis elements into selectable array of varchars
--

IF (fixed_url_axis_in is not null) THEN
  i := 1;
  fixed_url_axis_members := MgmtChrnsAnlzFxdAxesMembers();
  url_filenames := fixed_url_axis_in;
  WHILE LENGTH(url_filenames) > 0
  LOOP
    IF INSTR(url_filenames, ',') = 0 THEN
      url_filename := url_filenames;
      url_filenames := '';
    ELSE
      url_filename := SUBSTR(url_filenames, 1, INSTR(url_filenames, ',') -1);
      url_filenames := SUBSTR(url_filenames, INSTR(url_filenames, ',') +1, LENGTH(url_filenames));
    END IF;
    fixed_url_axis_members.extend;
    fixed_url_axis_members(i):=url_filename;
    i := i + 1;
  END LOOP;
END IF;

open cursor_data for
          SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers));

loop
fetch cursor_data into new_str;
exit when cursor_data%notfound;
dbms_output.put_line(new_str);
end loop;

v_min_coll_time := min_coll_time_in;
v_max_coll_time := max_coll_time_in;
--IF (v_min_coll_time IS NULL) THEN
--  v_min_coll_time := TO_DATE('1000', 'yyyy');
--END IF;

--IF (v_max_coll_time IS NULL) THEN
--  v_max_coll_time := TO_DATE('3000', 'yyyy');
--END IF;

--
-- extract min and max collection timestamps from raw table for all
-- webcaches in webapp
--

-- 
-- Now multiple cursors one for each dimension
--
CASE filter_axis_in

    WHEN 'domain' THEN

dbms_output.put_line('in domain' || analyze_max_rows);
      OPEN filter_data_out FOR
      SELECT * FROM
        (SELECT url_filename, visitor_domain,count(r.collection_timestamp),ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time,
        ROUND(max(elapsed_time)/1000,2),ROUND(stddev(elapsed_time)/1000,2),
        ROUND(avg(server_latency_time)/1000,2),  
        ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2), 
        ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
        ROUND(stddev(database_time)/1000,2)

        FROM mgmt_rt_metrics_raw r WHERE 
        
        elapsed_time < latency_filter AND
       r.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        url_filename in 
           (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))

        GROUP BY url_filename, visitor_domain ORDER BY avg_elapsed_time DESC)

      WHERE rownum <= analyze_max_rows;

    WHEN 'visitor' THEN

      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT url_filename, visitor_node,count(r.collection_timestamp), ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time,
        ROUND(max(elapsed_time)/1000,2),ROUND(stddev(elapsed_time)/1000,2),
        ROUND(avg(server_latency_time)/1000,2),  
        ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2), 
        ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
        ROUND(stddev(database_time)/1000,2)
        
        FROM mgmt_rt_metrics_raw r

        WHERE elapsed_time < latency_filter AND
       r.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

     
        url_filename in 
           (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))

        GROUP BY url_filename, visitor_node ORDER BY avg_elapsed_time DESC)

       WHERE rownum <= analyze_max_rows;


    WHEN 'region' THEN
      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT url_filename, r.region_name,count(m.collection_timestamp),ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time,
        ROUND(max(elapsed_time)/1000,2),ROUND(stddev(elapsed_time)/1000,2),
        ROUND(avg(server_latency_time)/1000,2),
        ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2),
        ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
        ROUND(stddev(database_time)/1000,2)

        FROM mgmt_rt_metrics_raw m, mgmt_rt_regions r

        WHERE elapsed_time < latency_filter AND
       m.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time 

        AND

        r.target_guid = website_target_guid

        AND

        (EXISTS ( SELECT 1
              FROM dual
             WHERE 
              r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))

        AND

        url_filename in 
           (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))

        GROUP BY url_filename, r.region_name ORDER BY avg_elapsed_time DESC)
      
       WHERE rownum <= analyze_max_rows;

    WHEN 'webcache' THEN

      OPEN filter_data_out FOR

       SELECT * FROM
        (SELECT url_filename, t.target_name,count(m.collection_timestamp), ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time,
        ROUND(max(elapsed_time)/1000,2),ROUND(stddev(elapsed_time)/1000,2),
        ROUND(avg(server_latency_time)/1000,2), 
        ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2),
        ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
        ROUND(stddev(database_time)/1000,2)

        FROM mgmt_rt_metrics_raw m, mgmt_targets t WHERE elapsed_time < latency_filter AND

        m.target_guid=t.target_guid AND      
       m.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        url_filename in 
          (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))
     
        and t.target_guid = m.target_guid

        
        GROUP BY url_filename, t.target_name ORDER BY avg_elapsed_time DESC)

       WHERE rownum <= analyze_max_rows;

  END CASE;

--
-- everything went successfully
--
  RETURN;
--
-- exception happened so return failure code
--
EXCEPTION
  WHEN OTHERS
   THEN
dbms_output.put_line(SUBSTR(SQLERRM, 1, err_maxlen));
        RETURN;
END GET_ANALYZE_DATA_BY_URL;

PROCEDURE CHRONOS_GET_ANALYZE_DATA(
              target_name_in             IN  VARCHAR2,
              target_type_in             IN  VARCHAR2,
              fixed_domain_axis_in       IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              fixed_webcache_axis_in     IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              fixed_region_axis_in       IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              fixed_url_axis_in          IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              fixed_visitor_axis_in      IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              filter_axis_in             IN  VARCHAR2,
              filter_axis_contains       IN  VARCHAR2,
              analyze_sample             IN  VARCHAR2,
              max_coll_time_in           IN  DATE,
              min_coll_time_in           IN  DATE,
              max_rows_out               OUT INTEGER,
              max_coll_time_out          OUT DATE,
              min_coll_time_out          OUT DATE,
              filter_data_out            OUT p_cursor_type,
              status_code                OUT INTEGER,
              err_msg                    OUT VARCHAR2)
IS

i PLS_INTEGER;
j PLS_INTEGER;

website_target_guid RAW(16);
analyze_max_rows PLS_INTEGER;
srch_str_length PLS_INTEGER;
srch_str_upper VARCHAR2(1024);

v_min_coll_time DATE;
v_max_coll_time DATE;

fixed_domain_axis_members MgmtChrnsAnlzFxdAxesMembers := null;
fixed_url_axis_members MgmtChrnsAnlzFxdAxesMembers := null;
fixed_visitor_axis_members MgmtChrnsAnlzFxdAxesMembers := null;
fixed_region_axis_members MgmtChrnsAnlzFxdAxesMembers := null;
fixed_webcache_axis_members MgmtChrnsAnlzFxdAxesMembers := null;
webapp_webcache_members MgmtChrnsAnlzFxdAxesMembers := null;
--
-- cursor to extract all webcache for website target passed in
--
CURSOR webcache_members_cursor(l_target_name_in VARCHAR2,l_target_type_in VARCHAR2) IS
  SELECT rawtohex(assoc_target_guid)
    FROM MGMT_TARGET_ASSOCS, MGMT_TARGETS ct, MGMT_TARGETS mt
   WHERE ct.target_name=l_target_name_in 
     and ct.target_type=l_target_type_in 
     and assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid
     and source_target_guid = ct.target_guid
     and mt.target_guid = assoc_target_guid;
--
-- cursor to extract webcache guids for webcache target names passed in for fixed axis
--
CURSOR webcache_guids_cursor(l_fixed_webcache_axis_members MgmtChrnsAnlzFxdAxesMembers) IS
  SELECT rawtohex(target_guid) FROM MGMT_TARGETS WHERE target_name in 
     (SELECT * FROM TABLE(CAST(l_fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers))) ;
BEGIN

--
-- select website target guid for target name
--
SELECT target_guid INTO website_target_guid FROM MGMT_TARGETS 
 WHERE target_name=target_name_in and target_type=target_type_in;

--
-- get max number of rows to return
--
BEGIN
  SELECT to_number(nvl(parameter_value,0))
     INTO analyze_max_rows 
    FROM MGMT_PARAMETERS
    WHERE parameter_name = 'mgmt_rt_analyze_max_rows';
EXCEPTION
  WHEN OTHERS THEN
    analyze_max_rows := max_rows;
END;

if(analyze_max_rows <= 0) then
   analyze_max_rows := max_rows;
end if;

--
-- copy domain fixed axis elements into selectable array of varchars
--
IF (fixed_domain_axis_in is not null) THEN
  j:=fixed_domain_axis_in.COUNT;
  IF (j=0) THEN
    fixed_domain_axis_members := null;
  ELSE
    fixed_domain_axis_members := MgmtChrnsAnlzFxdAxesMembers();
    FOR i IN 1..j LOOP 
      fixed_domain_axis_members.extend;
      fixed_domain_axis_members(i):=fixed_domain_axis_in(i).value;
    END LOOP;
  END IF;
END IF; 

--
-- copy visitor fixed axis elements into selectable array of varchars
--
IF (fixed_visitor_axis_in is not null) THEN
  j:=fixed_visitor_axis_in.COUNT;
  IF (j=0) THEN
    fixed_visitor_axis_members:=null;
  ELSE
    fixed_visitor_axis_members := MgmtChrnsAnlzFxdAxesMembers();
    FOR i IN 1..j LOOP
      fixed_visitor_axis_members.extend;
      fixed_visitor_axis_members(i):=fixed_visitor_axis_in(i).value;
    END LOOP;
  END IF;
END IF;

--
-- copy url fixed axis elements into selectable array of varchars
--
IF (fixed_url_axis_in is not null) THEN
  j:=fixed_url_axis_in.COUNT;
  IF (j=0) THEN
    fixed_url_axis_members:=null;
  ELSE
    fixed_url_axis_members := MgmtChrnsAnlzFxdAxesMembers();
    FOR i IN 1..j LOOP
      fixed_url_axis_members.extend;
      fixed_url_axis_members(i):=fixed_url_axis_in(i).value;
    END LOOP;
  END IF;
END IF;

--
-- copy webcache fixed axis elements into selectable array of varchars
-- get their guids as strings instead of raw guids from names
--
IF (fixed_webcache_axis_in is not null) THEN
   j:=fixed_webcache_axis_in.COUNT;
   IF (j=0) THEN
     fixed_webcache_axis_members := null;
   ELSE
     webapp_webcache_members := MgmtChrnsAnlzFxdAxesMembers();
     FOR i IN 1..j LOOP
       webapp_webcache_members.extend; 
       webapp_webcache_members(i):=fixed_webcache_axis_in(i).value;
     END LOOP;

     OPEN webcache_guids_cursor(webapp_webcache_members);
     FETCH webcache_guids_cursor BULK COLLECT INTO fixed_webcache_axis_members;
     CLOSE webcache_guids_cursor;
     webapp_webcache_members.delete;
     if (fixed_webcache_axis_members is null or fixed_webcache_axis_members.COUNT=0) then
       err_msg:='NO WEBCACHES FOUND FOR WEBSITE';
       status_code:=success_code;
       RETURN;
     END IF;
   END IF; 
END IF ;

--
-- copy region fixed axis elements into selectable array of varchars
--
IF (fixed_region_axis_in is not null) THEN
  j:=fixed_region_axis_in.COUNT;
  IF (j=0) THEN
    fixed_region_axis_members:=null;
  ELSE
    fixed_region_axis_members := MgmtChrnsAnlzFxdAxesMembers();
    FOR i IN 1..j LOOP
      fixed_region_axis_members.extend;
      fixed_region_axis_members(i):=fixed_region_axis_in(i).value;
    END LOOP;
  END IF;
END IF;

--
-- if no fixed webcache axis elements then use all webcaches that
-- are part of webapp target
--
IF (fixed_webcache_axis_members is null) THEN

  OPEN webcache_members_cursor(target_name_in,target_type_in);
  FETCH webcache_members_cursor BULK COLLECT into webapp_webcache_members;
  CLOSE webcache_members_cursor;

  IF (webapp_webcache_members is null or webapp_webcache_members.COUNT=0) THEN
    err_msg := 'NO WEB CACHES IN WEB APPLICATION TARGET';
    status_code:= success_code;
    RETURN;
  ELSE
    fixed_webcache_axis_members := MgmtChrnsAnlzFxdAxesMembers();
    j := webapp_webcache_members.COUNT;
    FOR i in 1..j LOOP
      fixed_webcache_axis_members.extend;
      fixed_webcache_axis_members(i) := webapp_webcache_members(i);
    END LOOP;
  END IF;
END IF;

v_min_coll_time := min_coll_time_in;
v_max_coll_time := max_coll_time_in;
--IF (v_min_coll_time IS NULL) THEN
--  v_min_coll_time := TO_DATE('1000', 'yyyy');
--END IF;

--IF (v_max_coll_time IS NULL) THEN
--  v_max_coll_time := TO_DATE('3000', 'yyyy');
--END IF;

--
-- extract min and max collection timestamps from raw table for all
-- webcaches in webapp
--
SELECT min(collection_timestamp), max(collection_timestamp)
  INTO min_coll_time_out, max_coll_time_out 
  FROM MGMT_RT_METRICS_RAW 
  WHERE rawtohex(target_guid) in  
     (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)));

srch_str_length := NVL(LENGTH(filter_axis_contains), 0 );
srch_str_upper := UPPER(filter_axis_contains);


-- 
-- Now multiple cursors one for each dimension
--
CASE filter_axis_in

  WHEN 'url' THEN
   IF (analyze_sample = 'false') THEN

      OPEN filter_data_out FOR 

      SELECT * FROM
       (SELECT url_filename,count(r.collection_timestamp),
       ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time, ROUND(max(elapsed_time)/1000,2), 
       ROUND(stddev(elapsed_time)/1000,2), ROUND(avg(server_latency_time)/1000,2),
       ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2),
       ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
       ROUND(stddev(database_time)/1000,2)

       FROM mgmt_rt_metrics_raw r WHERE elapsed_time < latency_filter AND 
       r.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        ((fixed_domain_axis_members IS NULL) or (fixed_domain_axis_members IS NOT NULL AND
        visitor_domain in 
        (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_webcache_axis_members IS NULL) 
         or 
        (fixed_webcache_axis_members is not null and rawtohex(target_guid) in 
        (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND 
     
        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))
      
        AND

        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
             (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND (( srch_str_length = 0) OR (url_filename LIKE '%' || filter_axis_contains || '%'))
        
        GROUP BY url_filename ORDER BY avg_elapsed_time DESC)

       WHERE rownum <= analyze_max_rows;

    ELSIF (analyze_sample = 'true') THEN 
      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT visitor_node, URL_FILENAME,
          ROUND(elapsed_time/1000,2) avg_elapsed_time,
          round(d.server_latency_time/1000,2),
          round(d.database_time/1000,2),
          submit_action_timestamp,
          collection_timestamp, 
          os_name, 
          os_version,  
          browser_name, 
          browser_version
        FROM mgmt_rt_metrics_raw d

        WHERE elapsed_time < latency_filter AND
       d.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        ((fixed_domain_axis_members is null) OR 
        (fixed_domain_axis_members is not null and visitor_domain in 
          (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_webcache_axis_members is null) OR 
        (fixed_webcache_axis_members is not null and rawtohex(target_guid) in 
         (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND
     
        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
          (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND

        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND (( srch_str_length = 0) OR (url_filename LIKE '%' || filter_axis_contains || '%'))

        ORDER BY collection_timestamp DESC)
       WHERE rownum <= analyze_max_rows;
  ELSE

      OPEN filter_data_out FOR 

      SELECT * FROM
       (SELECT count(r.collection_timestamp),
       ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time, ROUND(max(elapsed_time)/1000,2), 
       ROUND(stddev(elapsed_time)/1000,2), ROUND(avg(server_latency_time)/1000,2),
       ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2),
       ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
       ROUND(stddev(database_time)/1000,2)

       FROM mgmt_rt_metrics_raw r WHERE elapsed_time < latency_filter AND 
       r.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        ((fixed_domain_axis_members IS NULL) or (fixed_domain_axis_members IS NOT NULL AND
        visitor_domain in 
        (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_webcache_axis_members IS NULL) 
         or 
        (fixed_webcache_axis_members is not null and rawtohex(target_guid) in 
        (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND 
     
        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))
      
        AND

        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
             (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND (( srch_str_length = 0) OR (url_filename LIKE '%' || filter_axis_contains || '%')) ) 

       WHERE rownum <= analyze_max_rows;

  END IF;


    WHEN 'domain' THEN
    IF (analyze_sample = 'false') THEN

      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT visitor_domain,count(r.collection_timestamp),ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time,
        ROUND(max(elapsed_time)/1000,2),ROUND(stddev(elapsed_time)/1000,2),
        ROUND(avg(server_latency_time)/1000,2),  
        ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2), 
        ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
        ROUND(stddev(database_time)/1000,2)

        FROM mgmt_rt_metrics_raw r WHERE 
        
        elapsed_time < latency_filter AND
       r.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
           (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_webcache_axis_members is null) OR 
        (fixed_webcache_axis_members is not null and rawtohex(target_guid) in  
          (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND 
     
        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))
       
        AND

        ((fixed_domain_axis_members IS NULL) or (fixed_domain_axis_members IS NOT NULL AND
         visitor_domain in 
        (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

       AND (( srch_str_length = 0) OR (UPPER(visitor_domain) LIKE '%' || srch_str_upper || '%'))
        
        GROUP BY visitor_domain ORDER BY avg_elapsed_time DESC)

      WHERE rownum <= analyze_max_rows;
    ELSIF (analyze_sample = 'true') THEN
      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT visitor_node, URL_FILENAME,
          ROUND(elapsed_time/1000,2) avg_elapsed_time,
          round(d.server_latency_time/1000,2), 
          round(d.database_time/1000,2), 
          submit_action_timestamp,
          collection_timestamp, 
          os_name, 
          os_version,  
          browser_name, 
          browser_version
        FROM mgmt_rt_metrics_raw d

        WHERE elapsed_time < latency_filter AND
       d.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        ((fixed_domain_axis_members is null) OR 
        (fixed_domain_axis_members is not null and visitor_domain in 
          (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_webcache_axis_members is null) OR 
        (fixed_webcache_axis_members is not null and rawtohex(target_guid) in 
         (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND
     
        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
          (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND

        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

       AND (( srch_str_length = 0) OR (UPPER(visitor_domain) LIKE '%' || srch_str_upper || '%'))
        
        ORDER BY collection_timestamp DESC)
       WHERE rownum <= analyze_max_rows;
  ELSE

      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT count(r.collection_timestamp),ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time,
        ROUND(max(elapsed_time)/1000,2),ROUND(stddev(elapsed_time)/1000,2),
        ROUND(avg(server_latency_time)/1000,2),  
        ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2),
       ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
       ROUND(stddev(database_time)/1000,2)

        FROM mgmt_rt_metrics_raw r WHERE 
        
        elapsed_time < latency_filter AND
       r.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
           (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_webcache_axis_members is null) OR 
        (fixed_webcache_axis_members is not null and rawtohex(target_guid) in  
          (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND 
     
        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))
       
        AND

        ((fixed_domain_axis_members IS NULL) or (fixed_domain_axis_members IS NOT NULL AND
         visitor_domain in 
        (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

       AND (( srch_str_length = 0) OR (UPPER(visitor_domain) LIKE '%' || srch_str_upper || '%')))

      WHERE rownum <= analyze_max_rows;
   

  END IF;

    WHEN 'visitor' THEN

  IF (analyze_sample = 'false') THEN


      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT visitor_node,count(r.collection_timestamp), ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time,
        ROUND(max(elapsed_time)/1000,2),ROUND(stddev(elapsed_time)/1000,2),
        ROUND(avg(server_latency_time)/1000,2),  
        ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2), 
        ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
        ROUND(stddev(database_time)/1000,2)
        
        FROM mgmt_rt_metrics_raw r

        WHERE elapsed_time < latency_filter AND
       r.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        ((fixed_domain_axis_members is null) OR 
        (fixed_domain_axis_members is not null and visitor_domain in 
          (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_webcache_axis_members is null) OR 
        (fixed_webcache_axis_members is not null and rawtohex(target_guid) in 
         (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND
     
        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
          (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND

        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND ( ( srch_str_length = 0) OR (UPPER(visitor_node) LIKE '%' || srch_str_upper|| '%'))
        
        GROUP BY visitor_node ORDER BY avg_elapsed_time DESC)

       WHERE rownum <= analyze_max_rows;

    ELSIF (analyze_sample = 'true') THEN


      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT visitor_node,  URL_FILENAME,
          ROUND(elapsed_time/1000,2) avg_elapsed_time,
          round(d.server_latency_time/1000,2),
          round(d.database_time/1000,2),
          submit_action_timestamp,
          collection_timestamp, 
          os_name, 
          os_version,  
          browser_name, 
          browser_version
        FROM mgmt_rt_metrics_raw d

        WHERE elapsed_time < latency_filter AND
       d.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        ((fixed_domain_axis_members is null) OR 
        (fixed_domain_axis_members is not null and visitor_domain in 
          (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_webcache_axis_members is null) OR 
        (fixed_webcache_axis_members is not null and rawtohex(target_guid) in 
         (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND
     
        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
          (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND

        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND ( ( srch_str_length = 0) OR (UPPER(visitor_node) LIKE '%' || srch_str_upper|| '%'))
        
        ORDER BY collection_timestamp DESC)
       WHERE rownum <= analyze_max_rows;
  ELSE
      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT count(r.collection_timestamp), ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time,
        ROUND(max(elapsed_time)/1000,2),ROUND(stddev(elapsed_time)/1000,2),
        ROUND(avg(server_latency_time)/1000,2),  
        ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2), 
        ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
        ROUND(stddev(database_time)/1000,2)
        
        FROM mgmt_rt_metrics_raw r

        WHERE elapsed_time < latency_filter AND
       r.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        ((fixed_domain_axis_members is null) OR 
        (fixed_domain_axis_members is not null and visitor_domain in 
          (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_webcache_axis_members is null) OR 
        (fixed_webcache_axis_members is not null and rawtohex(target_guid) in 
         (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND
     
        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
          (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND

        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND ( ( srch_str_length = 0) OR (UPPER(visitor_node) LIKE '%' || srch_str_upper|| '%')))

       WHERE rownum <= analyze_max_rows;

  END IF;

    WHEN 'region' THEN
    IF (analyze_sample = 'false') THEN
      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT r.region_name,count(m.collection_timestamp),ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time,
        ROUND(max(elapsed_time)/1000,2),ROUND(stddev(elapsed_time)/1000,2),
        ROUND(avg(server_latency_time)/1000,2),
        ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2),
        ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
        ROUND(stddev(database_time)/1000,2)

        FROM mgmt_rt_metrics_raw m, mgmt_rt_regions r

        WHERE elapsed_time < latency_filter AND
       m.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time 

        AND

        r.target_guid = website_target_guid

        AND

        (EXISTS ( SELECT 1
              FROM dual
             WHERE 
              r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))

        AND

        ((fixed_domain_axis_members is null) OR 
        (fixed_domain_axis_members is not null and visitor_domain in 
          (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_webcache_axis_members is null) OR 
        (fixed_webcache_axis_members is not null and rawtohex(m.target_guid) in 
           (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
     
        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
           (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
        (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))
       
        AND

        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND ( ( srch_str_length = 0)  OR  (r.region_name LIKE '%' || filter_axis_contains || '%'))
        
        GROUP BY r.region_name ORDER BY avg_elapsed_time DESC)
      
       WHERE rownum <= analyze_max_rows;
    ELSIF (analyze_sample = 'true') THEN

      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT 
          visitor_node, URL_FILENAME,
          ROUND(d.elapsed_time/1000,2) avg_elapsed_time,
          round(d.server_latency_time/1000,2),
          round(d.database_time/1000,2),
          d.submit_action_timestamp,
          d.collection_timestamp, 
          d.os_name, 
          d.os_version,  
          d.browser_name, 
          d.browser_version
        FROM mgmt_rt_metrics_raw d, mgmt_rt_regions r

        WHERE d.elapsed_time < latency_filter AND
       d.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time
  
        AND

              r.target_guid = website_target_guid

              AND

              (EXISTS ( SELECT 1
                          FROM dual
                         WHERE
                          r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))

              AND

        ((fixed_domain_axis_members is null) OR 
        (fixed_domain_axis_members is not null and visitor_domain in 
          (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_webcache_axis_members is null) OR 
        (fixed_webcache_axis_members is not null and rawtohex(d.target_guid) in 
         (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND
     
        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
          (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND

        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND ( ( srch_str_length = 0)  OR  (r.region_name LIKE '%' || filter_axis_contains || '%'))
        
        ORDER BY collection_timestamp DESC)
       WHERE rownum <= analyze_max_rows;
  ELSE
      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT count(m.collection_timestamp),ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time,
        ROUND(max(elapsed_time)/1000,2),ROUND(stddev(elapsed_time)/1000,2),
        ROUND(avg(server_latency_time)/1000,2),
        ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2), 
        ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
        ROUND(stddev(database_time)/1000,2)

        FROM mgmt_rt_metrics_raw m, mgmt_rt_regions r

        WHERE elapsed_time < latency_filter AND
       m.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time

        AND

        r.target_guid = website_target_guid

        AND

        (EXISTS ( SELECT 1
              FROM dual
             WHERE 
              r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))

        AND

        ((fixed_domain_axis_members is null) OR 
        (fixed_domain_axis_members is not null and visitor_domain in 
          (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_webcache_axis_members is null) OR 
        (fixed_webcache_axis_members is not null and rawtohex(m.target_guid) in 
           (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
     
        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
           (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
        (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))
       
        AND

        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND ( ( srch_str_length = 0)  OR  (r.region_name LIKE '%' || filter_axis_contains || '%')))
      
       WHERE rownum <= analyze_max_rows;
   
  END IF;

    WHEN 'webcache' THEN
    IF (analyze_sample = 'false') THEN


      OPEN filter_data_out FOR

       SELECT * FROM
        (SELECT t.target_name,count(m.collection_timestamp), ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time,
        ROUND(max(elapsed_time)/1000,2),ROUND(stddev(elapsed_time)/1000,2),
        ROUND(avg(server_latency_time)/1000,2), 
        ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2),
        ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
        ROUND(stddev(database_time)/1000,2)

        FROM mgmt_rt_metrics_raw m, mgmt_targets t WHERE elapsed_time < latency_filter AND

        m.target_guid=t.target_guid AND      
       m.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        ((fixed_domain_axis_members is null) OR 
        (fixed_domain_axis_members is not null and visitor_domain in 
         (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 

        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
          (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))
     
        AND 
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND 
     
        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND

        rawtohex(m.target_guid) in 
           (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))
     

        and t.target_guid = m.target_guid

        AND (( srch_str_length = 0)  OR (t.target_name LIKE '%' || filter_axis_contains || '%'))
        
        GROUP BY t.target_name ORDER BY avg_elapsed_time DESC)

       WHERE rownum <= analyze_max_rows;

    ELSIF (analyze_sample = 'true') THEN

      OPEN filter_data_out FOR

      SELECT * FROM
        (SELECT 
          visitor_node, URL_FILENAME,
          ROUND(d.elapsed_time/1000,2) avg_elapsed_time,
          round(d.server_latency_time/1000,2),
          round(d.database_time/1000,2),
          d.submit_action_timestamp,
          d.collection_timestamp, 
          d.os_name, 
          d.os_version,  
          d.browser_name, 
          d.browser_version
        FROM mgmt_rt_metrics_raw d,mgmt_targets t

        WHERE d.elapsed_time < latency_filter 
  
        AND
       d.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        d.target_guid=t.target_guid

              AND

        ((fixed_domain_axis_members is null) OR 
        (fixed_domain_axis_members is not null and visitor_domain in 
          (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 
     
        ((fixed_webcache_axis_members is null) OR 
        (fixed_webcache_axis_members is not null and rawtohex(d.target_guid) in 
         (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND
     
        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
          (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND

        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND (( srch_str_length = 0)  OR (t.target_name LIKE '%' || filter_axis_contains || '%'))
        
        ORDER BY collection_timestamp DESC)
       WHERE rownum <= analyze_max_rows;
  ELSE
      OPEN filter_data_out FOR

       SELECT * FROM
        (SELECT count(m.collection_timestamp), ROUND(avg(elapsed_time)/1000,2) avg_elapsed_time,
        ROUND(max(elapsed_time)/1000,2),ROUND(stddev(elapsed_time)/1000,2),
        ROUND(avg(server_latency_time)/1000,2), 
        ROUND(max(server_latency_time)/1000,2), ROUND(stddev(server_latency_time)/1000,2), 
        ROUND(avg(database_time)/1000,2), ROUND(max(database_time)/1000,2),
        ROUND(stddev(database_time)/1000,2)

        FROM mgmt_rt_metrics_raw m, mgmt_targets t WHERE elapsed_time < latency_filter AND
       m.collection_timestamp BETWEEN v_min_coll_time AND v_max_coll_time AND

        m.target_guid=t.target_guid AND      

        ((fixed_domain_axis_members is null) OR 
        (fixed_domain_axis_members is not null and visitor_domain in 
         (SELECT * FROM TABLE(CAST(fixed_domain_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND 

        ((fixed_url_axis_members is null) OR 
        (fixed_url_axis_members is not null and url_filename in 
          (SELECT * FROM TABLE(CAST(fixed_url_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))
     
        AND 
     
        ((fixed_region_axis_members IS NULL) 
         OR 
         ( (fixed_region_axis_members IS NOT NULL)
         AND
         (EXISTS ( SELECT 1
               FROM mgmt_rt_regions mrg
              WHERE (mrg.target_guid = website_target_guid)
                AND (mrg.region_name IN
                   (SELECT * FROM TABLE(CAST(fixed_region_axis_members AS
                                 MgmtChrnsAnlzFxdAxesMembers))))
                AND mrg.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
      ))
        ))

        AND 
     
        ((fixed_visitor_axis_members is null) OR 
        (fixed_visitor_axis_members is not null and visitor_node in 
           (SELECT * FROM TABLE(CAST(fixed_visitor_axis_members as MgmtChrnsAnlzFxdAxesMembers)))))

        AND

        rawtohex(m.target_guid) in 
           (SELECT * FROM TABLE(CAST(fixed_webcache_axis_members as MgmtChrnsAnlzFxdAxesMembers)))
     

        and t.target_guid = m.target_guid

        AND (( srch_str_length = 0)  OR (t.target_name LIKE '%' || filter_axis_contains || '%')))

       WHERE rownum <= analyze_max_rows;
  END IF;
  END CASE;

--
-- everything went successfully
--
  max_rows_out := analyze_max_rows;
  status_code:=success_code;
  RETURN;
--
-- exception happened so return failure code
--
EXCEPTION
  WHEN OTHERS
   THEN
        err_msg := SUBSTR(SQLERRM, 1, err_maxlen);
        status_code:= failure_code;
        RETURN;
END CHRONOS_GET_ANALYZE_DATA;

-- CHRONOS_GET_ADV_ANALYZE_DATA
-- 
-- Inputs:
--
-- target_name_in   web application name
-- target_type_in   web application type
-- filter_axis_in   the column we will select and group by
-- url_filename_in  if not null, only entries with this filename are selected
-- visitor_node_in  if not null, only entries with this host name are selected
-- visitor_ip_in    if not null, only entries with this ip are selected
-- visitor_domain_in if not null, only entries in this domain are selected
--                   the domain can be a ip subnet like 128.2.67 or a domain
--                   like us.oracle.com
-- regions_in        if not null and not of size 0, only entries in these 
--                   regions will be returned
-- webservers_in     if not null and not of size 0, only entries in these 
--                   webservers will be returned
-- browser_list_in   if not null, a comparison like 'in (''MSIE'')'
-- os_list_in        if not null, a comparison like 'in (''Windows'')'
-- min_coll_time_in    if not null, only selects entries >= this time
-- max_coll_time_in    if not null, only selects entries <= this time
-- 
-- Outputs:
-- max_rows_out      the maximum number of rows that will be returned
-- max_coll_time_out specifies the maximum collection time from the data
-- min_coll_time_out specifies the maximum collection time from the data
-- filter_data_out   a cursor with the data
-- status_code       return status, 0 = failure, 1 = success
-- err_msg           sql error in case of failure
-- 
PROCEDURE CHRONOS_GET_ADV_ANALYZE_DATA(
              target_name_in             IN  VARCHAR2,
              target_type_in             IN  VARCHAR2,
              analyze_type_in            IN  VARCHAR2,
              filter_axis_in             IN  VARCHAR2,
              fixed_url_axis_in          IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              fixed_visitor_axis_in      IN  MGMT_CHRONOS_ANALYZE_ARRAY, 
              fixed_domain_axis_in       IN  MGMT_CHRONOS_ANALYZE_ARRAY, 
              fixed_region_axis_in       IN  MGMT_CHRONOS_ANALYZE_ARRAY,  
              fixed_webserver_axis_in    IN  MGMT_CHRONOS_ANALYZE_ARRAY, 
              fixed_browser_axis_in      IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              fixed_os_axis_in           IN  MGMT_CHRONOS_ANALYZE_ARRAY, 
              search_str_in              IN  VARCHAR2, 
              min_coll_time_in           IN  DATE,
              max_coll_time_in           IN  DATE, 
              max_rows_out               OUT INTEGER,
              max_coll_time_out          OUT DATE,
              min_coll_time_out          OUT DATE,
              filter_data_out            OUT p_cursor_type,
              status_code                OUT INTEGER, 
              err_msg                    OUT VARCHAR2)
IS

  website_target_guid             RAW(16);
  server_target_guid              RAW(16);
  cur_region_guid                 RAW(16);
  website_target_guid_list        p_target_guid_list_type;
  analyze_max_rows                PLS_INTEGER;
  filter_column                   VARCHAR2(32);
  srch_str_upper                  VARCHAR2(1024);

  sqlquery                        VARCHAR(3000);
  i                               PLS_INTEGER;
  j                               PLS_INTEGER;
  len                             PLS_INTEGER;
  -- I need my own variables for collection time in case the input dates are 
  -- null.  I'm using these as bind variables to my dynamic sql query.
  min_coll_time                   DATE := TO_DATE('01-01-1000', 'MM-DD-YYYY');
  max_coll_time                   DATE := TO_DATE('12-31-4000', 'MM-DD-YYYY');
  fixed_url_axis_members          MgmtChrnsAnlzFxdAxesMembers := null;
  -- domains are like us.oracle.com
  fixed_domain_axis_members       MgmtChrnsAnlzFxdAxesMembers := null;
  -- subnets are like 144.25
  fixed_subnet_axis_members       MgmtChrnsAnlzFxdAxesMembers := null;
  fixed_vis_ip_axis_members       MgmtChrnsAnlzFxdAxesMembers := null;
  fixed_vis_node_axis_members     MgmtChrnsAnlzFxdAxesMembers := null;
  fixed_region_axis_members       MgmtChrnsAnlzFxdAxesMembers := null;
  fixed_webserver_axis_members    MgmtChrnsAnlzFxdAxesMembers := null;
  webapp_webserver_members        MgmtChrnsAnlzFxdAxesMembers := null;
  region_members                  MgmtChrnsAnlzFxdAxesMembers := null;
  browser_use_not_in              PLS_INTEGER := 0;
  browser_members                 MgmtChrnsAnlzFxdAxesMembers := null;
  os_use_not_in                   PLS_INTEGER := 0;
  os_members                      MgmtChrnsAnlzFxdAxesMembers := null;

--
-- cursor to extract all webservers for website target passed in
--
CURSOR webserver_members_cursor IS
  SELECT rawtohex(assoc_target_guid)
    FROM MGMT_TARGET_ASSOCS, MGMT_TARGETS ct, MGMT_TARGETS mt
   WHERE ct.target_name=target_name_in 
     and ct.target_type=target_type_in 
     and assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid
     and source_target_guid = ct.target_guid
     and mt.target_guid = assoc_target_guid;

--
-- cursor to extract webcache guids for webcache target names passed in for fixed axis
--
CURSOR webserver_guids_cursor(l_fixed_webserver_axis_members MgmtChrnsAnlzFxdAxesMembers) IS
  SELECT rawtohex(target_guid)  FROM MGMT_TARGETS WHERE target_name in 
     (SELECT * FROM TABLE(CAST(l_fixed_webserver_axis_members as MgmtChrnsAnlzFxdAxesMembers)));

--
-- cursor to extract region guids for region names passed in for fixed axis
--
CURSOR region_guids_cursor(l_fixed_region_axis_members MgmtChrnsAnlzFxdAxesMembers, l_website_target_guid RAW) IS
  SELECT rawtohex(region_guid) FROM MGMT_RT_REGIONS WHERE region_name IN
     (SELECT * FROM TABLE(CAST(l_fixed_region_axis_members as MgmtChrnsAnlzFxdAxesMembers)))
     AND target_guid = l_website_target_guid;

BEGIN

  --
  -- select website target guid for target name
  --
  SELECT target_guid INTO website_target_guid FROM MGMT_TARGETS 
    WHERE target_name=target_name_in and target_type=target_type_in;

  --
  -- get max number of rows to return
  --
  BEGIN
    SELECT to_number(nvl(parameter_value,0))
      INTO analyze_max_rows 
      FROM MGMT_PARAMETERS
      WHERE parameter_name = 'mgmt_rt_analyze_max_rows';
  EXCEPTION
    WHEN OTHERS THEN
      analyze_max_rows := max_rows;
  END;

  if(analyze_max_rows <= 0) then
     analyze_max_rows := max_rows;
  end if;

--
-- copy url fixed axis elements into selectable array of varchars
--
IF (fixed_url_axis_in is not null) THEN
  j:=fixed_url_axis_in.COUNT;
  IF (j > 0) THEN
    fixed_url_axis_members := MgmtChrnsAnlzFxdAxesMembers();
    FOR i IN 1..j LOOP
      fixed_url_axis_members.extend;
      fixed_url_axis_members(i):=fixed_url_axis_in(i).value;
    END LOOP;
  END IF;
END IF;

--
-- copy visitor fixed axis elements into selectable array of varchars
--
IF (fixed_visitor_axis_in is not null) THEN
  j:=fixed_visitor_axis_in.COUNT;
    FOR i IN 1..j LOOP 
      -- it is a visitor_ip like 144.25.16.34
      IF(fixed_visitor_axis_in(i).value_type = 1) THEN
	IF(fixed_vis_ip_axis_members IS NULL) THEN 
	  fixed_vis_ip_axis_members := MgmtChrnsAnlzFxdAxesMembers();
	END IF;
        fixed_vis_ip_axis_members.extend;
        fixed_vis_ip_axis_members(fixed_vis_ip_axis_members.COUNT):=fixed_visitor_axis_in(i).value;
      END IF;
      -- it is a visitor_node like host.example.com
      IF(fixed_visitor_axis_in(i).value_type = 0) THEN
	IF(fixed_vis_node_axis_members IS NULL) THEN
	  fixed_vis_node_axis_members := MgmtChrnsAnlzFxdAxesMembers();
	END IF;
        fixed_vis_node_axis_members.extend;
        fixed_vis_node_axis_members(fixed_vis_node_axis_members.COUNT):=fixed_visitor_axis_in(i).value;
      END IF;
    END LOOP;
END IF; 

--
-- copy domain fixed axis elements into selectable array of varchars
--
IF (fixed_domain_axis_in is not null) THEN
  j:=fixed_domain_axis_in.COUNT;
    FOR i IN 1..j LOOP 
      -- it is a subnet like 144.25
      IF(fixed_domain_axis_in(i).value_type = 1) THEN
	IF(fixed_subnet_axis_members IS NULL) THEN
	  fixed_subnet_axis_members := MgmtChrnsAnlzFxdAxesMembers();
	END IF;
        fixed_subnet_axis_members.extend;
        fixed_subnet_axis_members(fixed_subnet_axis_members.COUNT):=fixed_domain_axis_in(i).value||'.%';
      END IF;
      -- it is a domain like example.com
      IF(fixed_domain_axis_in(i).value_type = 0) THEN
	IF(fixed_domain_axis_members IS NULL) THEN
	  fixed_domain_axis_members := MgmtChrnsAnlzFxdAxesMembers();
	END IF;
        fixed_domain_axis_members.extend;
        fixed_domain_axis_members(fixed_domain_axis_members.COUNT):='%.'||fixed_domain_axis_in(i).value;
      END IF;
    END LOOP;
END IF; 

IF (fixed_region_axis_in is not null) THEN
  j:=fixed_region_axis_in.COUNT;
  IF (j=0) THEN
    fixed_region_axis_members := null;
  ELSE
    region_members := MgmtChrnsAnlzFxdAxesMembers();
    FOR i IN 1..j LOOP
      region_members.extend; 
      region_members(i):=fixed_region_axis_in(i).value;
    END LOOP;

    OPEN region_guids_cursor(region_members, website_target_guid);
    FETCH region_guids_cursor BULK COLLECT INTO fixed_region_axis_members;
    CLOSE region_guids_cursor;
  END IF;
END IF;

--
-- copy webserver fixed axis elements into selectable array of varchars
-- get their guids as strings instead of raw guids from names
--
IF (fixed_webserver_axis_in is not null) THEN
   j:=fixed_webserver_axis_in.COUNT;
   IF (j=0) THEN
     fixed_webserver_axis_members := null;
   ELSE
     webapp_webserver_members := MgmtChrnsAnlzFxdAxesMembers();
     FOR i IN 1..j LOOP
       webapp_webserver_members.extend; 
       webapp_webserver_members(i):=fixed_webserver_axis_in(i).value;
     END LOOP;

     OPEN webserver_guids_cursor(webapp_webserver_members);
     FETCH webserver_guids_cursor BULK COLLECT INTO fixed_webserver_axis_members;
     CLOSE webserver_guids_cursor;
     webapp_webserver_members.delete;
     if (fixed_webserver_axis_members is null or fixed_webserver_axis_members.COUNT=0) then
       err_msg:='NO WEBSERVERS FOUND FOR WEBSITE';
       status_code:=failure_code;
       RETURN;
     END IF;
   END IF; 
END IF;

--
-- if no fixed webserver axis elements then use all webservers that
-- are part of webapp target
--
IF (fixed_webserver_axis_members is null) THEN

  OPEN webserver_members_cursor;
  FETCH webserver_members_cursor BULK COLLECT into webapp_webserver_members;
  CLOSE webserver_members_cursor;

  IF (webapp_webserver_members is null or webapp_webserver_members.COUNT=0) THEN
    err_msg := 'NO WEB SERVERS IN WEB APPLICATION TARGET';
    status_code:= failure_code;
    RETURN;
  ELSE
    fixed_webserver_axis_members := MgmtChrnsAnlzFxdAxesMembers();
    j := webapp_webserver_members.COUNT;
    FOR i in 1..j LOOP
      fixed_webserver_axis_members.extend;
      fixed_webserver_axis_members(i) := webapp_webserver_members(i);
    END LOOP;
  END IF;
END IF;

  IF (fixed_browser_axis_in is not null) THEN
    j:=fixed_browser_axis_in.COUNT;
    IF (j=0) THEN
      browser_members := null;
    ELSE
      browser_members := MgmtChrnsAnlzFxdAxesMembers();
      browser_use_not_in := fixed_browser_axis_in(1).value_type;
      FOR i IN 1..j LOOP
        browser_members.extend; 
        browser_members(i):=fixed_browser_axis_in(i).value;
      END LOOP;
    END IF;
  END IF;

  IF (fixed_os_axis_in is not null) THEN
    j:=fixed_os_axis_in.COUNT;
    IF (j=0) THEN
      os_members := null;
    ELSE
      os_members := MgmtChrnsAnlzFxdAxesMembers();
      os_use_not_in := fixed_os_axis_in(1).value_type;
      FOR i IN 1..j LOOP
        os_members.extend; 
        os_members(i):=fixed_os_axis_in(i).value;
      END LOOP;
    END IF;
  END IF;
  
  srch_str_upper := UPPER(search_str_in);
  -- make sure we don't have a 0 length string
  IF(NVL(LENGTH(srch_str_upper), 0) = 0) THEN 
    srch_str_upper := NULL;
  END IF;

  IF (min_coll_time_in IS NOT NULL) THEN
    min_coll_time := min_coll_time_in;
  END IF;
  IF (max_coll_time_in IS NOT NULL) THEN
    max_coll_time := max_coll_time_in;
  END IF;

  -- select the filter column based on the filter_axis_in
  CASE filter_axis_in
    WHEN 'url' THEN
      filter_column := 'd.url_filename';
    WHEN 'domain' THEN
      filter_column := 'd.visitor_domain';
    WHEN 'visitor' THEN
      filter_column := 'd.visitor_node';
    WHEN 'region' THEN
      filter_column := 'r.region_name';
    WHEN 'webcache' THEN
      filter_column := 't.target_name';
    WHEN 'browser' THEN
      filter_column := 'd.browser_name';
    WHEN 'browserOS' THEN
      filter_column := 'd.os_name';
    ELSE
      filter_column := 'd.url_filename';
  END CASE;
  
  sqlquery := 'SELECT * FROM (SELECT ';
  IF(analyze_type_in = 'samples') THEN
    sqlquery := sqlquery ||'d.visitor_node, d.url_filename, ' ||
      'ROUND(d.elapsed_time/1000,2) avg_elapsed_time, ' ||
      'ROUND(d.server_latency_time/1000,2), ' ||
      'ROUND(d.database_time/1000,2), ' ||      
      'd.submit_action_timestamp, ' ||
      'd.collection_timestamp, ' ||
      'd.os_name, ' ||
      'd.os_version, ' ||
      'd.browser_name, ' ||
      'd.browser_version ';
  ELSE
    IF(analyze_type_in = 'grouped') THEN
      sqlquery := sqlquery || filter_column || ', ';
    END IF;
    sqlquery := sqlquery ||
      'count(d.collection_timestamp), ' ||
      'ROUND(avg(d.elapsed_time)/1000,2) avg_elapsed_time, ' ||
      'ROUND(max(d.elapsed_time)/1000,2), ' || 
      'ROUND(stddev(d.elapsed_time)/1000,2), '||
      'ROUND(avg(d.server_latency_time)/1000,2), ' ||
      'ROUND(max(d.server_latency_time)/1000,2), ' ||
      'ROUND(stddev(d.server_latency_time)/1000,2), ' ||
      'ROUND(avg(d.database_time)/1000,2), ' ||
      'ROUND(max(d.database_time)/1000,2), ' ||
      'ROUND(stddev(d.database_time)/1000,2) ';
  END IF;

  sqlquery := sqlquery || 'FROM mgmt_rt_metrics_raw d ';

  -- only add tables if I need them
  IF (filter_axis_in = 'webcache') THEN
    sqlquery := sqlquery || ', mgmt_targets t ';
  ELSIF (filter_axis_in = 'region') THEN
    sqlquery := sqlquery || ', mgmt_rt_regions r ';
  END IF;
  sqlquery := sqlquery || 'WHERE d.elapsed_time < :c1 '; 
 
  IF (filter_axis_in = 'webcache') THEN
    sqlquery := sqlquery || 'AND d.target_guid = t.target_guid ';
  ELSIF (filter_axis_in = 'region') THEN
    sqlquery := sqlquery || 'AND r.target_guid = :cg ' ||
                ' AND r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr(''.''||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER(''.''||e.domain))))';
  END IF;
    
  -- only specify regions if the user selected specific ones
  sqlquery := sqlquery || 'AND ' ||
        '((:cr IS NULL) OR (:cr IS NOT NULL ' ||
        'AND EXISTS (SELECT 1 /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr(''.''||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER(''.''||e.domain))) AND mp.region_guid IN(SELECT * FROM TABLE(CAST(:cr as MgmtChrnsAnlzFxdAxesMembers)))))) ';

  --
  -- If the user specifies webservers, only take data from those webservers.
  -- If no webservers were specified, get data from all member webservers.
  --
  sqlquery := sqlquery || 'AND d.target_guid IN (SELECT * FROM TABLE(CAST(:cw as MgmtChrnsAnlzFxdAxesMembers))) ';

  -- Code for the URLs
  sqlquery := sqlquery || 'AND ' ||
        '((:c2 is null) OR ' ||
        '(:c2 is not null and url_filename in ' ||
        '(SELECT * FROM TABLE(CAST(:c2 as MgmtChrnsAnlzFxdAxesMembers))))) ';

  -- Code for the Visitors
  sqlquery := sqlquery || 'AND ' ||
        '((:c3 is null) OR ' ||
        '(:c3 is not null and visitor_ip in ' ||
          '(SELECT * FROM TABLE(CAST(:c3 as MgmtChrnsAnlzFxdAxesMembers))))) ';
  sqlquery := sqlquery || 'AND ' ||
        '((:c4 is null) OR ' ||
        '(:c4 is not null and visitor_node in ' ||
          '(SELECT * FROM TABLE(CAST(:c4 as MgmtChrnsAnlzFxdAxesMembers))))) ';


  -- select only the raw samples that either end with the visitor_domain_in for
  -- the case where it is a domain like example.com or it begins with a 
  -- subnet like 128.2.67 
  sqlquery := sqlquery || 'AND ' ||
        '((:c5 is null) OR ' ||
        '(:c5 is not null and EXISTS ' ||
          '(SELECT 1 FROM TABLE(CAST(:c5 as MgmtChrnsAnlzFxdAxesMembers)) '||
             'WHERE d.visitor_ip LIKE column_value))) ';
  sqlquery := sqlquery || 'AND ' ||
        '((:c6 is null) OR ' ||
        '(:c6 is not null and EXISTS ' ||
          '(SELECT 1 FROM TABLE(CAST(:c6 as MgmtChrnsAnlzFxdAxesMembers)) '||
             'WHERE d.visitor_node LIKE column_value))) ';

  -- Code for the Browser Software
  sqlquery := sqlquery || 'AND ((:c2 is null) OR ' ||
        '(:c2 is not null and d.browser_name ';
  IF(browser_use_not_in = 1) THEN
    sqlquery := sqlquery || 'not ';
  END IF;
  sqlquery := sqlquery ||'in (SELECT * FROM TABLE(CAST(:c2 as MgmtChrnsAnlzFxdAxesMembers))))) ';

  -- Code for the Browser OS
  sqlquery := sqlquery || 'AND ((:c2 is null) OR ' ||
        '(:c2 is not null and d.os_name ';
  IF(os_use_not_in = 1) THEN
    sqlquery := sqlquery || 'not ';
  END IF;
  sqlquery := sqlquery ||'in (SELECT * FROM TABLE(CAST(:c2 as MgmtChrnsAnlzFxdAxesMembers))))) ';

  -- Code for the Search
  sqlquery := sqlquery || 'AND ((:s IS NULL) OR ((:s IS NOT NULL) AND ';
    -- On the samples page, we show the name and version.
    -- Allow the user to search on both columns at once.
    IF(analyze_type_in = 'samples' AND filter_axis_in = 'browser') THEN
      sqlquery := sqlquery || 
        'UPPER(d.browser_name||'' ''||d.browser_version) LIKE :s ';
    ELSIF(analyze_type_in = 'samples' AND filter_axis_in = 'browserOS') THEN
      sqlquery := sqlquery || 
        'UPPER(d.os_name||'' ''||d.os_version) LIKE :s ';
    ELSE
      sqlquery := sqlquery || 'UPPER(' || filter_column || ') LIKE :s ';
    END IF;
  sqlquery := sqlquery || ')) ';

  -- Code for the User-Defined Dates
  sqlquery := sqlquery || 'AND d.collection_timestamp BETWEEN :c8 AND :c9 ';

  IF(analyze_type_in = 'samples') THEN
    sqlquery := sqlquery || 'ORDER BY d.collection_timestamp DESC';
  ELSIF(analyze_type_in = 'grouped') THEN
    sqlquery := sqlquery ||'GROUP BY ' || filter_column ||
                          ' ORDER BY avg_elapsed_time DESC';
  END IF;
  sqlquery := sqlquery ||') WHERE rownum <= :x';

  IF (filter_axis_in = 'region') THEN
    -- In this case, the second parameter is the website_target_guid
    OPEN filter_data_out FOR sqlquery 
      USING latency_filter, website_target_guid, fixed_region_axis_members, fixed_region_axis_members, fixed_region_axis_members, fixed_webserver_axis_members, fixed_url_axis_members, fixed_url_axis_members, fixed_url_axis_members, fixed_vis_ip_axis_members, fixed_vis_ip_axis_members, fixed_vis_ip_axis_members, fixed_vis_node_axis_members, fixed_vis_node_axis_members, fixed_vis_node_axis_members, fixed_subnet_axis_members, fixed_subnet_axis_members, fixed_subnet_axis_members, fixed_domain_axis_members, fixed_domain_axis_members, fixed_domain_axis_members, browser_members, browser_members, browser_members, os_members, os_members, os_members, srch_str_upper, srch_str_upper, srch_str_upper, min_coll_time, max_coll_time, analyze_max_rows;
  ELSE
    OPEN filter_data_out FOR sqlquery 
      USING latency_filter, fixed_region_axis_members, fixed_region_axis_members, fixed_region_axis_members, fixed_webserver_axis_members, fixed_url_axis_members, fixed_url_axis_members, fixed_url_axis_members, fixed_vis_ip_axis_members, fixed_vis_ip_axis_members, fixed_vis_ip_axis_members, fixed_vis_node_axis_members, fixed_vis_node_axis_members, fixed_vis_node_axis_members, fixed_subnet_axis_members, fixed_subnet_axis_members, fixed_subnet_axis_members, fixed_domain_axis_members, fixed_domain_axis_members, fixed_domain_axis_members, browser_members, browser_members, browser_members, os_members, os_members, os_members, srch_str_upper, srch_str_upper, srch_str_upper, min_coll_time, max_coll_time, analyze_max_rows;
  END IF;

  --
  -- extract min and max collection timestamps from raw table for all
  -- webcaches in webapp
  --
  SELECT min(collection_timestamp), max(collection_timestamp)
    INTO min_coll_time_out, max_coll_time_out 
    FROM MGMT_RT_METRICS_RAW d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct
    WHERE ct.target_name = target_name_in 
      AND ct.target_type = target_type_in
      AND m.assoc_target_guid = d.target_guid
      AND m.assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid
      AND ct.target_guid = m.source_target_guid;

--
-- everything went successfully
--
  max_rows_out := analyze_max_rows;
  status_code  := success_code;
  RETURN;

--
-- exception happened so return failure code
--
EXCEPTION
  WHEN OTHERS
   THEN
        err_msg := SUBSTR(SQLERRM, 1, err_maxlen);
        status_code:= failure_code;
        RETURN;

END CHRONOS_GET_ADV_ANALYZE_DATA;

END EMD_CHRONOS_ANALYZE;
/
show errors;