Rem
Rem $Header: chronos_mntr_pkgbody.sql 17-jul-2006.19:45:42 yxie Exp $
Rem
Rem chronos_mntr_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      chronos_mntr_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    yxie        07/17/06 - XbranchMerge yxie_eum_views from main
Rem    yxie        12/12/05 - Backport yxie_bug-4725344 from main 
Rem    yxie        11/11/05 - Fix bug 4725344: convert all sysdate to target timezone
Rem    eporter     08/22/05 - Only count unprocessed samples from last 24 hours 
Rem    yxie        07/05/05 - fix bug 4473366 
Rem    yxie        06/27/05 - make 7-day view gets data from daily rollup table
Rem    yxie        06/06/05 - changes depends_on to supports_eum_on 
Rem    yxie        05/13/05 - fix sql injection security issue 
Rem    yxie        04/25/05 - fix bug 4332185 and 4332159
Rem    yxie        04/15/05 - changing contains to depends_on 
Rem    yxie        05/04/05 - Fix bug 4299150
Rem    eporter     03/14/05 - Incomplete Loads bootstrapping 
Rem    eporter     02/28/05 - Change incomplete loads table to rollup tables 
Rem    pmaddi      02/22/05 - assoc def name changed from member to contains 
Rem                           for getResponseTimeseriesForQuries 
Rem    ratadiko    02/03/05 - Adding procedure for EUM Graphs. 
Rem    eporter     01/20/05 - getRowsByObjAndStat: return totalUsageTime in sec
Rem    ramalhot    01/17/05 - assoc def name changed from member to contains 
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    yxie        12/12/04 - Get webserver type from getOverallWebfarmPerf
Rem    eporter     12/01/04 - Fix error in getNumUnprocessedHits 
Rem    yxie        11/18/04 - Fix bug get data for byWeek from hourly table
Rem    yxie        11/18/04 - Fix bug in getResponseDist by setting sqlquery to a greater limit
Rem    yxie        10/23/04 - Fixed DBAvgTime bug in getStatsObj
Rem    adosani     09/30/04 - Additions for WebApp Oracle Forms Target Type
Rem    yxie        10/07/04 - Fixed tvmeci difs in getRowsByObjAndStat getHitDetails and getOverallWebfarmPerf
Rem    ramalhot    10/05/04 -  g_contains_guid removed from dynamic sql 
Rem    yxie        09/28/04 - add incomplete loads information to url data
Rem    eporter     09/17/04 - Add getNumUnprocessedHits function 
Rem    eporter     09/09/04 - adding bootstrapping code 
Rem    yxie        09/05/04 - Bug fix region domain name case sensitive
Rem    ramalhot    08/30/04 - cutover to new assoc tables 
Rem    dcawley     07/07/04 - Increase user name size 
Rem    snakai      11/11/03 - add support for metric versioning 
Rem    snakai      09/26/03 - fix region queries 
Rem    jriel       05/13/03 - add server timing to enduser data
Rem    rmarripa    03/26/03 - check for user privileges
Rem    asawant     11/26/02 - Fixing SQL security threat
Rem    rmarripa    06/14/02 - remove rep url sqls/procedures
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Restructured.
Rem    lgloyd      02/15/02 - fix join with base mgmt_metrics tables, need target_guid
Rem    lgloyd      02/12/02 - retrive composite target name from byObjStatType query
Rem    rmarripa    01/24/02 - Retrieve CHRONOS_ENABLE_FLAG from MGMT_RT_TARGET_PROPERTIES.
Rem    lgloyd      02/05/02 - add weighted performance to URL Metrics
Rem    smishra     02/01/02 - fixed bad column name introduced during metric cutover.
Rem    rmarripa    01/16/02 - Retrieve thresholds from mgmt_metric_thresholds.
Rem    rmarripa    01/08/02 - Round the stddev values.
Rem    rmarripa    01/07/02 - Fix the standard deviation value.
Rem    edemembe    01/08/02 - Metrics are now target independent
Rem    rmarripa    12/17/01 - Remove the current average from the MGMT_MNTR_USER_STATS_OBJ
Rem    edemembe    12/26/01 - Removing TARGET_NAME,TARGET_TYPE,etc
Rem    rmarripa    12/15/01 - Add check for future data in all queries.
Rem    njuillar    12/13/01 - Merged njuillar_split_mntr_package
Rem    njuillar    12/12/01 - Created
Rem

CREATE OR REPLACE PACKAGE body emd_mntr_user AS



--
-- getWatchListStats - get stats for watch list items
--
--  Inputs:
--    target_name_in
--    target_type_in
--    page_type_in
--    metric_name_in
--
--  Outputs:
--
--    show_chronos_out - set to 1 if chronos ui should be shown, otherwise 0
--
--    objarray_out an array of data containing watch list items and 
--      performance stats.  Items returned include:
--          queryName 
--          displayName 
--          avgVal
--          hitsVal
--          minVal
--          maxVal
--          stddevVal
--          varianceVal
--          critThreshold
--          warnThreshold
--          currentAvg
--          urlLink
--

PROCEDURE getWatchListStats(target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_name_in IN VARCHAR2,
                            show_chronos_out OUT INTEGER,
                            objarray_out OUT MGMT_MNTR_USER_STATS_ARRAY) IS

    obj MGMT_MNTR_USER_STATS_OBJ;
    currIndex INTEGER;
    strChronosDataExists VARCHAR2(5);
    statsFound INTEGER;

BEGIN

    show_chronos_out := 0;
    objarray_out := MGMT_MNTR_USER_STATS_ARRAY();

    -- Check to see if we should show the chronos UI for this target.
    --
    strChronosDataExists := emd_mntr_user.chronosDataExists(target_name_in,
      target_type_in, page_type_in, metric_name_in);

    IF (strChronosDataExists = 'false')
    THEN
      return;
    END IF;

    -- show the chronos ui
    --
    show_chronos_out := 1;


    BEGIN
      -- Loop through all of the watch list urls and the most favorite
      -- url and get the stats for each.  We loop over each one individually
      -- so that a very selective query on the large data table can be made.
      --
      -- This looping avoids an outer join on the data table which would
      -- be very expensive, since we don't know if there is data available for
      -- watch list items until we look. We always want to display watch list
      -- items regardless of whether data exists or not.
      --
      -- Additionally, we don't expect the watch list to be very long, 
      -- so there should not be many iterations in this loop.
      --

      currIndex := 1;
   
      -- Dummy column OrderColumn is added to the select list to 
      -- get the representative URL always on top of the result set.
      for record in (
        SELECT  t.target_guid "TARGET_GUID", 
            u.display_name "DISPLAY_NAME",
            u.url_filename "URL_FILENAME"
        FROM MGMT_RT_URLS u, mgmt_targets t
        WHERE u.target_guid = t.target_guid
          and t.target_name = target_name_in and t.target_type = target_type_in
      ) loop

        statsFound := getUrlStatsObj(page_type_in, metric_name_in, 
          record.target_guid, record.url_filename, record.display_name, obj);

        IF (obj is not null)
        THEN
          objarray_out.extend;
          objarray_out(currIndex) := obj;
          currIndex := currIndex + 1;
        END IF;

      END loop;

    EXCEPTION
        WHEN OTHERS THEN RAISE;
    END;
END;


--
-- getRowsByObjAndStat - get stats by object type and stat type
--
--  Inputs:
--    target_name_in - (target_name or "" for all targets of given type)
--    target_type_in
--    page_type_in  (byDay, byWeek, byMonth)
--    metric_name_in (latency)
--    obj_type_in (byUrl, byDom, byReg, byIp)
--    stat_type_in (showPerf, showLoad, showPerfTotalTime)
--    filter_name_in - filter string or null
--    filter_min_hits_in - min hits filter or 0 
--    filter_min_hits_out - min hits filter - adjusted if necessary
--
--  Outputs:
--
--    Cursor for rows of stats.  Items returned include:
--          displayName 
--          urlFilename 
--          url_link 
--          hitsVal 
--          avgVal (sec) 
--          avgServVal (sec)
--          avgDBVal (sec)
--          minVal (sec) 
--          maxVal (sec) 
--          stddevVal (sec) 
--          totalUsageTimeVal (min)
--          target_name  (useful when querying for all targets of a given type)
--
--
PROCEDURE getRowsByObjAndStat(target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_name_in IN VARCHAR2,
                            obj_type_in IN VARCHAR2,
                            stat_type_in IN VARCHAR2,
                            filter_name_in IN VARCHAR2,
                            filter_min_hits_in IN INTEGER,
                            filter_min_hits_out OUT INTEGER,
                            results_out OUT cursorType) IS

    l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();

    currIndex INTEGER;
    i INTEGER;

    minHitsVal INTEGER;
    columnNameString VARCHAR2(40);
    tableNameString VARCHAR(2000);
    urlTableName VARCHAR2(2000);
    domainTableName VARCHAR2(2000);
    regionTableName VARCHAR2(2000);
    ipTableName VARCHAR2(2000);
    incompleteLoadsTableName VARCHAR2(64);

    sqlquery VARCHAR(32000);
    inloadquery VARCHAR(32000);
    coloadquery VARCHAR(32000);

    website_guid RAW(16);

    startTime DATE;
    endTime DATE;

BEGIN
    IF ( MGMT_USER.has_priv(l_current_user, MGMT_USER.VIEW_TARGET,target_name_in, target_type_in) = 
           MGMT_USER.USER_DOES_NOT_HAVE_PRIV ) THEN
        RAISE MGMT_GLOBAL.insufficient_privileges;
    END IF;

    currIndex := 1;
    i := 1;

  SELECT target_guid
    INTO website_guid
    FROM MGMT_TARGETS
   WHERE target_name = target_name_in
     AND target_type = target_type_in;

    endTime := MGMT_TARGET.SYSDATE_TARGET(website_guid);

    IF page_type_in = 'byMonth'
    THEN
        startTime := endTime - 31;
        urlTableName := 'MGMT_RT_URL_1DAY';
        domainTableName := 'MGMT_RT_DOMAIN_1DAY';
        regionTableName := 'MGMT_RT_DOMAIN_1DAY';
        ipTableName := 'MGMT_RT_IP_1DAY';
        incompleteLoadsTableName := 'MGMT_RT_INCOMPLETE_LOADS_1DAY';
    ELSE
      IF page_type_in = 'byWeek'
      THEN
        startTime := endTime - 7;
        urlTableName := 'MGMT_RT_URL_1DAY';
        domainTableName := 'MGMT_RT_DOMAIN_1DAY';
        regionTableName := 'MGMT_RT_DOMAIN_1DAY';
        ipTableName := 'MGMT_RT_IP_1DAY';
        incompleteLoadsTableName := 'MGMT_RT_INCOMPLETE_LOADS_1DAY';
      ELSE
        startTime := endTime - 1;
        urlTableName := 
          '(SELECT b.target_guid, b.metric_name, b.url_filename, b.url_link, '||
          '        b.rollup_timestamp, b.hits, b.response_time_average, '||
          '        b.response_time_minimum, b.response_time_maximum, '||
          '        b.response_time_sdev, b.response_time_variance, '||
          '        b.server_time_average, b.server_time_minimum, '||
          '        b.server_time_maximum, b.server_time_sdev, '||
          '        b.server_time_variance, b.db_time_average, b.db_time_minimum, '||
          '        b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
          ' FROM MGMT_RT_URL_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE b.target_guid = mtrt.target_guid ' ||
               'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_URL_1HOUR'' ' ||
          'UNION ' || 
          'SELECT h.target_guid, h.metric_name, h.url_filename, h.url_link, '||
          '       h.rollup_timestamp, h.hits, h.response_time_average, '||
          '       h.response_time_minimum, h.response_time_maximum, '||
          '       h.response_time_sdev, h.response_time_variance, '||
          '       h.server_time_average, h.server_time_minimum, '||
          '       h.server_time_maximum, h.server_time_sdev, '||
          '       h.server_time_variance, h.db_time_average, h.db_time_minimum, '||
          '       h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
          ' FROM MGMT_RT_URL_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE h.target_guid = mtrt.target_guid ' ||
               'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_URL_1HOUR'')';
        domainTableName := 
          '(SELECT b.target_guid, b.metric_name, b.visitor_domain, b.visitor_subnet, '||
          '        b.visitor_subnet_num, b.rollup_timestamp, b.hits, '||
          '        b.response_time_average, b.response_time_minimum, '||
          '        b.response_time_maximum, b.response_time_sdev, b.response_time_variance, '||
          '        b.server_time_average, b.server_time_minimum, b.server_time_maximum, '||
          '        b.server_time_sdev, b.server_time_variance, b.db_time_average, '||
          '        b.db_time_minimum, b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
          ' FROM MGMT_RT_DOMAIN_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE b.target_guid = mtrt.target_guid ' ||
               'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_DOMAIN_1HOUR'' ' ||
          'UNION ' || 
          'SELECT h.target_guid, h.metric_name, h.visitor_domain, h.visitor_subnet, '||
          '       h.visitor_subnet_num, h.rollup_timestamp, h.hits, '||
          '       h.response_time_average, h.response_time_minimum, '||
          '       h.response_time_maximum, h.response_time_sdev, h.response_time_variance, '||
          '       h.server_time_average, h.server_time_minimum, h.server_time_maximum, '||
          '       h.server_time_sdev, h.server_time_variance, h.db_time_average, '||
          '       h.db_time_minimum, h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
          ' FROM MGMT_RT_DOMAIN_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE h.target_guid = mtrt.target_guid ' ||
               'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_DOMAIN_1HOUR'')';
        regionTableName := domainTableName;
        ipTableName := 
          '(SELECT b.target_guid, b.metric_name, b.visitor_node, b.rollup_timestamp, '||
          '        b.hits, b.response_time_average, b.response_time_minimum, '||
          '        b.response_time_maximum, b.response_time_sdev, b.response_time_variance, '||
          '        b.server_time_average, b.server_time_minimum,  b.server_time_maximum, '||
          '        b.server_time_sdev, b.server_time_variance, b.db_time_average, '||
          '        b.db_time_minimum, b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
          ' FROM MGMT_RT_IP_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE b.target_guid = mtrt.target_guid ' ||
               'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_IP_1HOUR'' ' ||
          'UNION ' || 
          'SELECT h.target_guid, h.metric_name, h.visitor_node, h.rollup_timestamp, '||
          '       h.hits, h.response_time_average, h.response_time_minimum, '||
          '       h.response_time_maximum, h.response_time_sdev, h.response_time_variance, '||
          '       h.server_time_average, h.server_time_minimum,  h.server_time_maximum, '||
          '       h.server_time_sdev, h.server_time_variance, h.db_time_average, '||
          '       h.db_time_minimum, h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
          ' FROM MGMT_RT_IP_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE h.target_guid = mtrt.target_guid ' ||
               'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_IP_1HOUR'')';
        incompleteLoadsTableName := 'EM$RT_INCOMPLETE_LOADS';
      END IF;
    END IF;
      
    -- check to see if a min hits filter has been specified.  If so, use
    -- it, otherwise use the default min hits filter
    --

    minHitsVal := filter_min_hits_in;
    IF (minHitsVal = 0)
    THEN
        BEGIN
          select to_number(nvl(parameter_value, 0))
          into minHitsVal
          from mgmt_parameters
          where parameter_name = 'mgmt_rt_min_hits';
        EXCEPTION
           WHEN NO_DATA_FOUND
           THEN
               minHitsVal := 0;
           WHEN OTHERS THEN RAISE;
        END;
    END IF;
    filter_min_hits_out := minHitsVal;

    -- perform the appropriate query
    --
    IF (obj_type_in = 'byFormUrl')
    THEN
        columnNameString := 'URL_FILENAME'; 
        --
        -- Slowest/Most Hits by URL
        --
        sqlquery := 
            'select '||
                ' nvl(max((select display_name from mgmt_rt_urls u '||
                    ' where u.target_guid = m.source_target_guid '||
                    ' and u.url_filename = d.url_filename)), d.url_filename), '||
                ' d.url_filename, '||
                ' max(url_link), '||
                ' sum(hits), '||
                ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||   
                ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||   
                ' round(min(response_time_minimum)/1000,2), '||
                ' round(max(response_time_maximum)/1000,2), '||
        ' decode( '||
          ' sum(hits), '||
          ' 0, 0, '||
          ' 1, 0, '||
          ' round( sqrt( '||
            '   (sum(hits) * '||
            '   (sum(decode(hits, '||
              '     0, 0, '||
              '     (((response_time_variance * hits *  '||
        '     (hits - 1)) + '||
              '     power((hits * response_time_average), 2))  '||
        '     / hits)))) -  '||
        '   power(sum(response_time_average * hits), 2)) /  '||
        ' (sum(hits) * (sum(hits) - 1)) '||
          ' )/1000, 2) '||
          ' ), '||
                ' round(sum(response_time_average*hits)/1000,2), '||
                ' ct.target_name '||
                ' from '||urlTableName||' d, mgmt_target_assocs m,mgmt_targets ct,mgmt_target_assoc_defs def '||
                ' where rollup_timestamp > :1 '||
                    ' and rollup_timestamp <= :2 '||
                    ' and m.assoc_guid =def.assoc_guid '||
                    ' and def.assoc_def_name = ''supports_eum_on''' ||
                    ' and def.scope_target_type = '' ''' ||
                    ' and ct.target_guid = m.source_target_guid '||
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and d.metric_name = :3'||
                    ' and ct.target_type = :4'||
                    ' and ct.target_name = :5';

        IF (filter_name_in is not null)
        THEN
            sqlquery := sqlquery||' and '||columnNameString||' like :6';
        END IF;

        sqlquery := sqlquery||' group by m.source_target_guid, ct.target_name, '||columnNameString;

        IF (stat_type_in = 'showPerf')
        THEN
            sqlquery := sqlquery||' order by sum(response_time_average*hits)/sum(hits) desc ';
        ELSIF (stat_type_in = 'showPerfTotalTime')
        THEN
            sqlquery := sqlquery||' order by sum(response_time_average*hits) desc ';
        ELSIF (stat_type_in = 'showServTime')
        THEN
            sqlquery := sqlquery||' order by sum(server_time_average*hits)/sum(hits) desc ';        
        ELSE
            sqlquery := sqlquery||' order by sum(hits) desc';
        END IF;

--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 0, 254));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 255, 254));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 509, 254));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 763, 254));
        IF (filter_name_in is not null)
        THEN
          OPEN results_out FOR sqlquery USING startTime, endTime, metric_name_in, target_type_in, target_name_in, filter_name_in;
        ELSE
          OPEN results_out FOR sqlquery USING startTime, endTime, metric_name_in, target_type_in, target_name_in;
        END IF;

    ELSIF (obj_type_in = 'byUrl')
    THEN
        columnNameString := 'URL_FILENAME'; 
        --
        -- Slowest/Most Hits by URL
        --
        coloadquery := 
            'select '||
                ' nvl(max((select display_name from mgmt_rt_urls u '||
                    ' where u.target_guid = ct.target_guid '||
                    ' and u.url_filename = d.url_filename)), d.url_filename) display_name, '||
                ' d.url_filename url_filename, '||
                ' max(url_link) full_url, '||
                ' sum(hits) hits, '||
                ' round(sum(response_time_average*hits)/sum(hits)/1000,2) avg_time, '||
                ' round(sum(server_time_average*hits)/sum(hits)/1000,2) svr_time, '||      
                ' round(min(response_time_minimum)/1000,2) min_time, '||
                ' round(max(response_time_maximum)/1000,2) max_time, '||
        ' decode( '||
          ' sum(hits), '||
          ' 0, 0, '||
          ' 1, 0, '||
          ' round( sqrt( '||
            '   (sum(hits) * '||
            '   (sum(decode(hits, '||
              '     0, 0, '||
              '     (((response_time_variance * hits *  '||
        '     (hits - 1)) + '||
              '     power((hits * response_time_average), 2))  '||
        '     / hits)))) -  '||
        '   power(sum(response_time_average * hits), 2)) /  '||
        ' (sum(hits) * (sum(hits) - 1)) '||
          ' )/1000, 2) '||
          ' ) std_dev, '||
                ' round(sum(response_time_average*hits)/1000,2) total_time, '||
                ' ct.target_name composite_target_name, '||
                ' ct.target_guid composite_target_guid '||
                ' from '||urlTableName||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                ' where rollup_timestamp > :1 '||
                    ' and rollup_timestamp <= :2 '||
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and ct.target_guid = m.source_target_guid '||
                    ' and m.assoc_guid =def.assoc_guid '||
                    ' and def.assoc_def_name = ''supports_eum_on''' ||
                    ' and def.scope_target_type = '' ''' ||
                    ' and d.metric_name = :3'||
                    ' and ct.target_type = :4'||
                    ' and ct.target_name = :5';

        IF (filter_name_in is not null)
        THEN
            coloadquery := coloadquery||' and '||columnNameString||' like :6';
        END IF;

        coloadquery := coloadquery||' group by ct.target_guid, ct.target_name, '||columnNameString;


        inloadquery := 
          'SELECT page_url, SUM(num_incomplete_loads) num_incomplete_loads, '||
            'DECODE(SUM(num_incomplete_loads), 0, 0, '||
            'round(SUM(avg_server_time * num_incomplete_loads)/SUM(num_incomplete_loads)/1000,2)) avg_server_time, '||
            'ct2.target_name composite_target_name, '||
            'ct2.target_guid composite_target_guid '||
            'FROM ' || incompleteLoadsTableName || ' il, mgmt_target_assocs m2, mgmt_targets ct2, mgmt_target_assoc_defs def2 '||
            'WHERE il.target_guid = m2.assoc_target_guid'||
            ' AND ct2.target_guid = m2.source_target_guid'||
            ' AND m2.assoc_guid = def2.assoc_guid '||
            ' AND def2.assoc_def_name = ''supports_eum_on''' ||
            ' AND def2.scope_target_type = '' ''' ||
            ' AND il.rollup_timestamp > :7 '||
            ' AND il.rollup_timestamp <= :8 '||
            ' AND ct2.target_type = :9'||
            ' AND ct2.target_name = :10';

        inloadquery := inloadquery||' GROUP BY ct2.target_guid, ct2.target_name, il.page_url';


        sqlquery := 
            'SELECT nvl(display_name, url_filename), url_filename, nvl(full_url, url_filename), nvl(hits, 0), '||
              'nvl(avg_time, 0), nvl(svr_time, 0), nvl(min_time, 0), nvl(max_time, 0), '||
              'nvl(std_dev, 0), nvl(total_time, 0), composite_target_name, '||
              'nvl(num_incomplete_loads, 0), nvl(avg_server_time, 0) '||
            'FROM ('||
              'SELECT display_name, url_filename, full_url, hits, avg_time, '||
                      'svr_time, min_time, max_time, std_dev, '||
                      'total_time, da1.composite_target_name, num_incomplete_loads, '||
                      'avg_server_time '||
                'FROM ('||coloadquery||') da1 LEFT OUTER JOIN ('||inloadquery||') iq1 '||
                'ON da1.url_filename = iq1.page_url '||
                'AND da1.composite_target_guid = iq1.composite_target_guid '||
              'UNION '||
              'SELECT display_name, page_url, full_url, hits, avg_time, '||
                      'svr_time, min_time, max_time, std_dev, '||
                      'total_time, da2.composite_target_name, num_incomplete_loads, '||
                      'avg_server_time '||
                'FROM ('||coloadquery||') da2 RIGHT OUTER JOIN ('||inloadquery||') iq2 '||
                'ON da2.url_filename = iq2.page_url '||
                'AND da2.composite_target_guid = iq2.composite_target_guid)';

        IF (stat_type_in = 'showPerf')
        THEN
            sqlquery := sqlquery||' order by nvl(avg_time, 0) desc, nvl(avg_server_time, 0) desc ';
        ELSIF (stat_type_in = 'showPerfTotalTime')
        THEN
            sqlquery := sqlquery||' order by nvl(total_time, 0) desc ';
        ELSIF (stat_type_in = 'showServTime')
        THEN
            sqlquery := sqlquery||' order by nvl(svr_time, 0) desc ';        
        ELSE
            sqlquery := sqlquery||' order by nvl(hits, 0) desc';
        END IF;

        IF (filter_name_in is not null)
        THEN
          OPEN results_out FOR sqlquery USING startTime, endTime, metric_name_in, target_type_in, target_name_in, filter_name_in, startTime, endTime, target_type_in, target_name_in, startTime, endTime, metric_name_in, target_type_in, target_name_in, filter_name_in, startTime, endTime, target_type_in, target_name_in;
        ELSE
          OPEN results_out FOR sqlquery USING startTime, endTime, metric_name_in, target_type_in, target_name_in, startTime, endTime, target_type_in, target_name_in, startTime, endTime, metric_name_in, target_type_in, target_name_in, startTime, endTime, target_type_in, target_name_in;
        END IF;


    ELSE
        IF (obj_type_in = 'byDom')
        THEN
            columnNameString := 'VISITOR_DOMAIN';
            tableNameString := domainTableName;
        ELSIF obj_type_in = 'byIp'
        THEN
            columnNameString := 'VISITOR_NODE';
            tableNameString := ipTableName;
        ELSIF obj_type_in = 'byReg'
        THEN
            columnNameString := 'REGION_NAME';
            tableNameString := regionTableName;
        END IF;

        --
        -- Slowest/Most Hits
        --
        IF obj_type_in = 'byReg'
        THEN
          sqlquery := 
                'select '||columnNameString||', '||
                    ' sum(hits), '||
                    ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(min(response_time_minimum)/1000,2), '||
                    ' round(max(response_time_maximum)/1000,2), '||
                  ' decode( '||
                  '   sum(hits), '||
                '   0, 0, '||
                  '   1, 0, '||
                  '   round( sqrt( '||
                  '       (sum(hits) * '||
                  '       (sum(decode(hits, '||
                  '           0, 0, '||
                '           (((response_time_variance * hits *  '||
              '           (hits - 1)) + '||
                  '           power((hits * response_time_average), 2))  '||
                  '           / hits)))) -  '||
                  '       power(sum(response_time_average * hits), 2)) /  '||
                  '   (sum(hits) * (sum(hits) - 1)) '||
                  '   )/1000,2 ) '||
                  ' ), '||
                    ' round(sum(response_time_average*hits)/1000,2), '||
                    ' ct.target_name '||
                    ' from '||tableNameString||' d, mgmt_target_assocs m,mgmt_targets ct,mgmt_target_assoc_defs def, '||
                      ' (SELECT UNIQUE visitor_subnet_num, visitor_domain, r.region_name, r.target_guid FROM '||
                        ' (SELECT UNIQUE visitor_subnet_num, visitor_domain, id from '||tableNameString||' d1, mgmt_rt_region_entries e , mgmt_target_assocs m1 '||
                        ' where ((e.min_ip >= 0 AND d1.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr(''.''||d1.visitor_domain, -LENGTH(e.domain)-1)) = (''.''||UPPER(e.domain))) ) '||
                        ' and m1.source_target_guid = :1 '||
                        ' and d1.target_guid = m1.assoc_target_guid '||
                        ' AND d1.rollup_timestamp > :2 '||
                        ' and d1.rollup_timestamp <= :3 '||
                        ' ) vsn, '||
                      ' mgmt_rt_regions r, mgmt_rt_region_mapping mp '|| 
                      ' where r.region_guid = mp.region_guid '||
                      ' and vsn.id = mp.id '||
                      ' and r.target_guid = :4 '||
                       ') r '||
                    ' where rollup_timestamp > :5 '||
                    ' and rollup_timestamp <= :6 '||
                    ' and m.assoc_guid = def.assoc_guid '||
                    ' and def.assoc_def_name = ''supports_eum_on''' ||
                    ' and def.scope_target_type = '' ''' ||
                    ' and ct.target_guid = m.source_target_guid '||
                    ' and r.target_guid = m.source_target_guid '||
                    ' and (d.visitor_subnet_num = r.visitor_subnet_num '||
                    ' and d.visitor_domain = r.visitor_domain) '||
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and ct.target_guid = :7'||
                    ' and d.metric_name = :8';

            IF (filter_name_in is not null)
            THEN                
                sqlquery := sqlquery||' and '||columnNameString||' like :9';
            END IF;

            sqlquery := sqlquery||' group by m.source_target_guid, ct.target_name, '||columnNameString;
 
        ELSE
            sqlquery := 
                'select '||columnNameString||', '||
                    ' sum(hits), '||
                    ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(min(response_time_minimum)/1000,2), '||
                    ' round(max(response_time_maximum)/1000,2), '||
                  ' decode( '||
                  '   sum(hits), '||
                  '   0, 0, '||
                  '   1, 0, '||
                  '   round( sqrt( '||
                  '       (sum(hits) * '||
                  '       (sum(decode(hits, '||
                  '           0, 0, '||
                  '           (((response_time_variance * hits *  '||
                  '           (hits - 1)) + '||
                  '           power((hits * response_time_average), 2))  '||
                  '           / hits)))) -  '||
                  '       power(sum(response_time_average * hits), 2)) /  '||
                  '   (sum(hits) * (sum(hits) - 1)) '||
                  '   )/1000, 2) '||
                  ' ), '||
                    ' round(sum(response_time_average*hits)/1000,2), '||
                    ' ct.target_name '||
                    ' from '||tableNameString||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                    ' where rollup_timestamp > :1 '||
                    ' and rollup_timestamp <= :2 '||
                    ' and m.assoc_guid = def.assoc_guid '||
                    ' and def.assoc_def_name = ''supports_eum_on''' ||
                    ' and def.scope_target_type = '' ''' ||
                    ' and ct.target_guid = m.source_target_guid '||
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and d.metric_name = :3 '||
                    ' and ct.target_type = :4 '||
                    ' and ct.target_name = :5';

            IF (filter_name_in is not null)
            THEN
                sqlquery := sqlquery||' and '||columnNameString||' like :6';
            END IF;

            sqlquery := sqlquery||' group by m.source_target_guid, ct.target_name, '||columnNameString;

        END IF;

        IF (stat_type_in = 'showPerf')
        THEN
            sqlquery := sqlquery||' order by sum(response_time_average*hits)/sum(hits) desc ';
        ELSIF (stat_type_in = 'showPerfTotalTime')
        THEN
            sqlquery := sqlquery||' order by sum(response_time_average*hits) desc ';
        ELSIF (stat_type_in = 'showServTime')
        THEN
            sqlquery := sqlquery||' order by sum(server_time_average*hits)/sum(hits) desc ';        
        ELSE
            sqlquery := sqlquery||' order by sum(hits) desc';
        END IF;

--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 0, 254));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 255, 254));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 509, 254));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 763, 254));
        IF obj_type_in = 'byReg'
        THEN
          IF (filter_name_in is not null)
          THEN 
            OPEN results_out FOR sqlquery USING website_guid, startTime, endTime, website_guid, startTime, endTime, website_guid, metric_name_in, filter_name_in;
          ELSE
            OPEN results_out FOR sqlquery USING website_guid, startTime, endTime, website_guid, startTime, endTime, website_guid, metric_name_in;
          END IF;
        ELSE
          IF (filter_name_in is not null)
          THEN
            OPEN results_out FOR sqlquery USING startTime, endTime, metric_name_in, 
                  target_type_in, target_name_in, filter_name_in; 
          ELSE
            OPEN results_out FOR sqlquery USING startTime, endTime, metric_name_in, 
                  target_type_in, target_name_in; 
          END IF;
        END IF;

   END IF;

END;

--
-- Obsolete method, please do not use this
-- 
-- getRowsByObjAndStat - get stats by object type and stat type
--
--  Inputs:
--    target_names_in - (target_names or empty list for all targets of type)
--    target_type_in
--    page_type_in  (byDay, byWeek, byMonth)
--    metric_name_in (latency)
--    obj_type_in (byUrl, byDom, byReg, byIp)
--    stat_type_in (showPerf, showLoad, showPerfTotalTime)
--    filter_name_in - filter string or null
--    filter_min_hits_in - min hits filter or 0 
--    filter_min_hits_out - min hits filter - adjusted if necessary
--
--  Outputs:
--
--    Cursor for rows of stats.  Items returned include:
--          displayName 
--          urlFilename 
--          url_link 
--          hitsVal 
--          avgVal (sec) 
--          avgServVal (sec)
--          avgDBVal (sec)
--          minVal (sec) 
--          maxVal (sec) 
--          stddevVal (sec) 
--          totalUsageTimeVal (sec)
--          target_name  (useful when querying for all targets of a given type)
--
--
PROCEDURE getRowsByObjAndStat(target_names_in IN SMP_EMD_STRING_ARRAY,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_name_in IN VARCHAR2,
                            obj_type_in IN VARCHAR2,
                            stat_type_in IN VARCHAR2,
                            filter_name_in IN VARCHAR2,
                            filter_min_hits_in IN INTEGER,
                            filter_min_hits_out OUT INTEGER,
                            results_out OUT cursorType) IS

    currIndex INTEGER;
    i INTEGER;

    minHitsVal INTEGER;
    timePeriod VARCHAR2(20);
    columnNameString VARCHAR2(40);
    tableNameString VARCHAR(2000);
    urlTableName VARCHAR2(2000);
    domainTableName VARCHAR2(2000);
    regionTableName VARCHAR2(2000);
    ipTableName VARCHAR2(2000);
    incompleteLoadsTableName VARCHAR2(64);

    sqlquery VARCHAR(32000);
    inloadquery VARCHAR(32000);
    coloadquery VARCHAR(32000);

    website_guid RAW(16);
    website_guids_list SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();

BEGIN

    currIndex := 1;
    i := 1;

    IF page_type_in = 'byMonth'
    THEN
        timePeriod := '(sysdate - 31)';
        urlTableName := 'MGMT_RT_URL_1DAY';
        domainTableName := 'MGMT_RT_DOMAIN_1DAY';
        regionTableName := 'MGMT_RT_DOMAIN_1DAY';
        ipTableName := 'MGMT_RT_IP_1DAY';
        incompleteLoadsTableName := 'MGMT_RT_INCOMPLETE_LOADS_1DAY';
    ELSE
      IF page_type_in = 'byWeek'
      THEN
        timePeriod := '(sysdate - 7)';
        urlTableName := 'MGMT_RT_URL_1DAY';
        domainTableName := 'MGMT_RT_DOMAIN_1DAY';
        regionTableName := 'MGMT_RT_DOMAIN_1DAY';
        ipTableName := 'MGMT_RT_IP_1DAY';
        incompleteLoadsTableName := 'MGMT_RT_INCOMPLETE_LOADS_1DAY';
      ELSE
        timePeriod := '(sysdate - 1)';
        urlTableName := 
          '(SELECT b.target_guid, b.metric_name, b.url_filename, b.url_link, '||
          '        b.rollup_timestamp, b.hits, b.response_time_average, '||
          '        b.response_time_minimum, b.response_time_maximum, '||
          '        b.response_time_sdev, b.response_time_variance, '||
          '        b.server_time_average, b.server_time_minimum, '||
          '        b.server_time_maximum, b.server_time_sdev, '||
          '        b.server_time_variance, b.db_time_average, b.db_time_minimum, '||
          '        b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
          ' FROM MGMT_RT_URL_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE b.target_guid = mtrt.target_guid ' ||
               'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_URL_1HOUR'' ' ||
          'UNION ' || 
          'SELECT h.target_guid, h.metric_name, h.url_filename, h.url_link, '||
          '       h.rollup_timestamp, h.hits, h.response_time_average, '||
          '       h.response_time_minimum, h.response_time_maximum, '||
          '       h.response_time_sdev, h.response_time_variance, '||
          '       h.server_time_average, h.server_time_minimum, '||
          '       h.server_time_maximum, h.server_time_sdev, '||
          '       h.server_time_variance, h.db_time_average, h.db_time_minimum, '||
          '       h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
          ' FROM MGMT_RT_URL_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE h.target_guid = mtrt.target_guid ' ||
               'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_URL_1HOUR'')';
        domainTableName := 
          '(SELECT b.target_guid, b.metric_name, b.visitor_domain, b.visitor_subnet, '||
          '        b.visitor_subnet_num, b.rollup_timestamp, b.hits, '||
          '        b.response_time_average, b.response_time_minimum, '||
          '        b.response_time_maximum, b.response_time_sdev, b.response_time_variance, '||
          '        b.server_time_average, b.server_time_minimum, b.server_time_maximum, '||
          '        b.server_time_sdev, b.server_time_variance, b.db_time_average, '||
          '        b.db_time_minimum, b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
          ' FROM MGMT_RT_DOMAIN_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE b.target_guid = mtrt.target_guid ' ||
               'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_DOMAIN_1HOUR'' ' ||
          'UNION ' || 
          'SELECT h.target_guid, h.metric_name, h.visitor_domain, h.visitor_subnet, '||
          '       h.visitor_subnet_num, h.rollup_timestamp, h.hits, '||
          '       h.response_time_average, h.response_time_minimum, '||
          '       h.response_time_maximum, h.response_time_sdev, h.response_time_variance, '||
          '       h.server_time_average, h.server_time_minimum, h.server_time_maximum, '||
          '       h.server_time_sdev, h.server_time_variance, h.db_time_average, '||
          '       h.db_time_minimum, h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
          ' FROM MGMT_RT_DOMAIN_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE h.target_guid = mtrt.target_guid ' ||
               'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_DOMAIN_1HOUR'')';
        regionTableName := domainTableName;
        ipTableName := 
          '(SELECT b.target_guid, b.metric_name, b.visitor_node, b.rollup_timestamp, '||
          '        b.hits, b.response_time_average, b.response_time_minimum, '||
          '        b.response_time_maximum, b.response_time_sdev, b.response_time_variance, '||
          '        b.server_time_average, b.server_time_minimum,  b.server_time_maximum, '||
          '        b.server_time_sdev, b.server_time_variance, b.db_time_average, '||
          '        b.db_time_minimum, b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
          ' FROM MGMT_RT_IP_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE b.target_guid = mtrt.target_guid ' ||
               'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_IP_1HOUR'' ' ||
          'UNION ' || 
          'SELECT h.target_guid, h.metric_name, h.visitor_node, h.rollup_timestamp, '||
          '       h.hits, h.response_time_average, h.response_time_minimum, '||
          '       h.response_time_maximum, h.response_time_sdev, h.response_time_variance, '||
          '       h.server_time_average, h.server_time_minimum,  h.server_time_maximum, '||
          '       h.server_time_sdev, h.server_time_variance, h.db_time_average, '||
          '       h.db_time_minimum, h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
          ' FROM MGMT_RT_IP_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE h.target_guid = mtrt.target_guid ' ||
               'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_IP_1HOUR'')';
        incompleteLoadsTableName := 'EM$RT_INCOMPLETE_LOADS';
      END IF;
    END IF;
      
    -- check to see if a min hits filter has been specified.  If so, use
    -- it, otherwise use the default min hits filter
    --

    minHitsVal := filter_min_hits_in;
    IF (minHitsVal = 0)
    THEN
        BEGIN
          select to_number(nvl(parameter_value, 0))
          into minHitsVal
          from mgmt_parameters
          where parameter_name = 'mgmt_rt_min_hits';
        EXCEPTION
           WHEN NO_DATA_FOUND
           THEN
               minHitsVal := 0;
           WHEN OTHERS THEN RAISE;
        END;
    END IF;
    filter_min_hits_out := minHitsVal;

    -- perform the appropriate query
    --
    IF (obj_type_in = 'byFormUrl')
    THEN
        columnNameString := 'URL_FILENAME'; 
        --
        -- Slowest/Most Hits by URL
        --
        sqlquery := 
            'select '||
                ' nvl(max((select display_name from mgmt_rt_urls u '||
                    ' where u.target_guid = m.source_target_guid '||
                    ' and u.url_filename = d.url_filename)), d.url_filename), '||
                ' d.url_filename, '||
                ' max(url_link), '||
                ' sum(hits), '||
                ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||   
                ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||   
                ' round(min(response_time_minimum)/1000,2), '||
                ' round(max(response_time_maximum)/1000,2), '||
        ' decode( '||
          ' sum(hits), '||
          ' 0, 0, '||
          ' 1, 0, '||
          ' round( sqrt( '||
            '   (sum(hits) * '||
            '   (sum(decode(hits, '||
              '     0, 0, '||
              '     (((response_time_variance * hits *  '||
        '     (hits - 1)) + '||
              '     power((hits * response_time_average), 2))  '||
        '     / hits)))) -  '||
        '   power(sum(response_time_average * hits), 2)) /  '||
        ' (sum(hits) * (sum(hits) - 1)) '||
          ' )/1000, 2) '||
          ' ), '||
                ' round(sum(response_time_average*hits)/1000,2), '||
                ' ct.target_name '||
                ' from '||urlTableName||' d, mgmt_target_assocs m,mgmt_targets ct,mgmt_target_assoc_defs def '||
                ' where rollup_timestamp > '||timePeriod||
                    ' and rollup_timestamp <= sysdate '||
                    ' and m.assoc_guid =def.assoc_guid '||
                    ' and def.assoc_def_name = ''supports_eum_on''' ||
                    ' and def.scope_target_type = '' ''' ||
                    ' and ct.target_guid = m.source_target_guid '||
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and d.metric_name = :1'||
                    ' and ct.target_type = :2'||
                    ' and ct.target_name IN (SELECT * FROM TABLE(CAST(:3 AS SMP_EMD_STRING_ARRAY)))';

        IF (filter_name_in is not null)
        THEN
            sqlquery := sqlquery||' and '||columnNameString||' like :4';
        END IF;

        sqlquery := sqlquery||' group by m.source_target_guid, ct.target_name, '||columnNameString;

        IF (stat_type_in = 'showPerf')
        THEN
            sqlquery := sqlquery||' order by sum(response_time_average*hits)/sum(hits) desc ';
        ELSIF (stat_type_in = 'showPerfTotalTime')
        THEN
            sqlquery := sqlquery||' order by sum(response_time_average*hits) desc ';
        ELSIF (stat_type_in = 'showServTime')
        THEN
            sqlquery := sqlquery||' order by sum(server_time_average*hits)/sum(hits) desc ';        
        ELSE
            sqlquery := sqlquery||' order by sum(hits) desc';
        END IF;

--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 0, 254));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 255, 254));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 509, 254));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 763, 254));
        IF (filter_name_in is not null)
        THEN
          OPEN results_out FOR sqlquery USING metric_name_in, target_type_in, target_names_in, filter_name_in;
        ELSE
          OPEN results_out FOR sqlquery USING metric_name_in, target_type_in, target_names_in;
        END IF;

    ELSIF (obj_type_in = 'byUrl')
    THEN
        columnNameString := 'URL_FILENAME'; 
        --
        -- Slowest/Most Hits by URL
        --
        coloadquery := 
            'select '||
                ' nvl(max((select display_name from mgmt_rt_urls u '||
                    ' where u.target_guid = ct.target_guid '||
                    ' and u.url_filename = d.url_filename)), d.url_filename) display_name, '||
                ' d.url_filename url_filename, '||
                ' max(url_link) full_url, '||
                ' sum(hits) hits, '||
                ' round(sum(response_time_average*hits)/sum(hits)/1000,2) avg_time, '||
                ' round(sum(server_time_average*hits)/sum(hits)/1000,2) svr_time, '||      
                ' round(min(response_time_minimum)/1000,2) min_time, '||
                ' round(max(response_time_maximum)/1000,2) max_time, '||
        ' decode( '||
          ' sum(hits), '||
          ' 0, 0, '||
          ' 1, 0, '||
          ' round( sqrt( '||
            '   (sum(hits) * '||
            '   (sum(decode(hits, '||
              '     0, 0, '||
              '     (((response_time_variance * hits *  '||
        '     (hits - 1)) + '||
              '     power((hits * response_time_average), 2))  '||
        '     / hits)))) -  '||
        '   power(sum(response_time_average * hits), 2)) /  '||
        ' (sum(hits) * (sum(hits) - 1)) '||
          ' )/1000, 2) '||
          ' ) std_dev, '||
                ' round(sum(response_time_average*hits)/1000,2) total_time, '||
                ' ct.target_name composite_target_name, '||
                ' ct.target_guid composite_target_guid '||
                ' from '||urlTableName||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                ' where rollup_timestamp > '||timePeriod||
                    ' and rollup_timestamp <= sysdate '||
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and ct.target_guid = m.source_target_guid '||
                    ' and m.assoc_guid =def.assoc_guid '||
                    ' and def.assoc_def_name = ''supports_eum_on''' ||
                    ' and def.scope_target_type = '' ''' ||
                    ' and d.metric_name = :1'||
                    ' and ct.target_type = :2'||
                    ' and ct.target_name IN (SELECT * FROM TABLE(CAST(:3 AS SMP_EMD_STRING_ARRAY)))';

        IF (filter_name_in is not null)
        THEN
            coloadquery := coloadquery||' and '||columnNameString||' like :4';
        END IF;

        coloadquery := coloadquery||' group by ct.target_guid, ct.target_name, '||columnNameString;


        inloadquery := 
          'SELECT page_url, SUM(num_incomplete_loads) num_incomplete_loads, '||
            'DECODE(SUM(num_incomplete_loads), 0, 0, '||
            'round(SUM(avg_server_time * num_incomplete_loads)/SUM(num_incomplete_loads)/1000,2)) avg_server_time, '||
            'ct2.target_name composite_target_name, '||
            'ct2.target_guid composite_target_guid '||
            'FROM ' || incompleteLoadsTableName || ' il, mgmt_target_assocs m2, mgmt_targets ct2, mgmt_target_assoc_defs def2 '||
            'WHERE il.target_guid = m2.assoc_target_guid'||
            ' AND ct2.target_guid = m2.source_target_guid'||
            ' AND m2.assoc_guid = def2.assoc_guid '||
            ' AND def2.assoc_def_name = ''supports_eum_on''' ||
            ' AND def2.scope_target_type = '' ''' ||
            ' AND il.rollup_timestamp BETWEEN '||timePeriod||' AND sysdate '||
            ' AND ct2.target_type = :5'||
            ' AND ct2.target_name IN (SELECT * FROM TABLE(CAST(:6 AS SMP_EMD_STRING_ARRAY)))';

        inloadquery := inloadquery||' GROUP BY ct2.target_guid, ct2.target_name, il.page_url';


        sqlquery := 
            'SELECT nvl(display_name, url_filename), url_filename, nvl(full_url, url_filename), nvl(hits, 0), '||
              'nvl(avg_time, 0), nvl(svr_time, 0), nvl(min_time, 0), nvl(max_time, 0), '||
              'nvl(std_dev, 0), nvl(total_time, 0), composite_target_name, '||
              'nvl(num_incomplete_loads, 0), nvl(avg_server_time, 0) '||
            'FROM ('||
              'SELECT display_name, url_filename, full_url, hits, avg_time, '||
                      'svr_time, min_time, max_time, std_dev, '||
                      'total_time, da1.composite_target_name, num_incomplete_loads, '||
                      'avg_server_time '||
                'FROM ('||coloadquery||') da1 LEFT OUTER JOIN ('||inloadquery||') iq1 '||
                'ON da1.url_filename = iq1.page_url '||
                'AND da1.composite_target_guid = iq1.composite_target_guid '||
              'UNION '||
              'SELECT display_name, page_url, full_url, hits, avg_time, '||
                      'svr_time, min_time, max_time, std_dev, '||
                      'total_time, da2.composite_target_name, num_incomplete_loads, '||
                      'avg_server_time '||
                'FROM ('||coloadquery||') da2 RIGHT OUTER JOIN ('||inloadquery||') iq2 '||
                'ON da2.url_filename = iq2.page_url '||
                'AND da2.composite_target_guid = iq2.composite_target_guid)';

        IF (stat_type_in = 'showPerf')
        THEN
            sqlquery := sqlquery||' order by nvl(avg_time, 0) desc, nvl(avg_server_time, 0) desc ';
        ELSIF (stat_type_in = 'showPerfTotalTime')
        THEN
            sqlquery := sqlquery||' order by nvl(total_time, 0) desc ';
        ELSIF (stat_type_in = 'showServTime')
        THEN
            sqlquery := sqlquery||' order by nvl(svr_time, 0) desc ';        
        ELSE
            sqlquery := sqlquery||' order by nvl(hits, 0) desc';
        END IF;

        IF (filter_name_in is not null)
        THEN
          OPEN results_out FOR sqlquery USING metric_name_in, target_type_in, target_names_in, filter_name_in, target_type_in, target_names_in, metric_name_in, target_type_in, target_names_in, filter_name_in, target_type_in, target_names_in;
        ELSE
          OPEN results_out FOR sqlquery USING metric_name_in, target_type_in, target_names_in, target_type_in, target_names_in, metric_name_in, target_type_in, target_names_in, target_type_in, target_names_in;
        END IF;


    ELSE
        IF (obj_type_in = 'byDom')
        THEN
            columnNameString := 'VISITOR_DOMAIN';
            tableNameString := domainTableName;
        ELSIF obj_type_in = 'byIp'
        THEN
            columnNameString := 'VISITOR_NODE';
            tableNameString := ipTableName;
        ELSIF obj_type_in = 'byReg'
        THEN
            columnNameString := 'REGION_NAME';
            tableNameString := regionTableName;
        END IF;

        --
        -- Slowest/Most Hits
        --
        IF obj_type_in = 'byReg'
        THEN

            FOR j in 1..target_names_in.COUNT
            LOOP
              --There is a test where it is in here twice
              SELECT target_guid
                INTO website_guid
                FROM mgmt_targets
                WHERE target_type = target_type_in
                  AND target_name = target_names_in(j);

                website_guids_list.extend();
                website_guids_list(1) := website_guid;
            END LOOP;

            sqlquery := 
                'select '||columnNameString||', '||
                    ' sum(hits), '||
                    ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(min(response_time_minimum)/1000,2), '||
                    ' round(max(response_time_maximum)/1000,2), '||
                  ' decode( '||
                  '   sum(hits), '||
                '   0, 0, '||
                  '   1, 0, '||
                  '   round( sqrt( '||
                  '       (sum(hits) * '||
                  '       (sum(decode(hits, '||
                  '           0, 0, '||
                '           (((response_time_variance * hits *  '||
              '           (hits - 1)) + '||
                  '           power((hits * response_time_average), 2))  '||
                  '           / hits)))) -  '||
                  '       power(sum(response_time_average * hits), 2)) /  '||
                  '   (sum(hits) * (sum(hits) - 1)) '||
                  '   )/1000,2 ) '||
                  ' ), '||
                    ' round(sum(response_time_average*hits)/1000,2), '||
                    ' ct.target_name '||
                    ' from '||tableNameString||' d, mgmt_target_assocs m,mgmt_targets ct,mgmt_target_assoc_defs def, '||
                      ' (SELECT UNIQUE visitor_subnet_num, visitor_domain, r.region_name, r.target_guid FROM '||
                        ' (SELECT UNIQUE visitor_subnet_num, visitor_domain, id from '||tableNameString||' d1, mgmt_rt_region_entries e , mgmt_target_assocs m1 '||
                        ' where ((e.min_ip >= 0 AND d1.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr(''.''||d1.visitor_domain, -LENGTH(e.domain)-1)) = (''.''||UPPER(e.domain))) ) '||
                        ' and m1.source_target_guid IN (SELECT * FROM TABLE(CAST(:1 AS SMP_EMD_STRING_ARRAY))) '||
                        ' and d1.target_guid = m1.assoc_target_guid '||
                        ' AND d1.rollup_timestamp > '||timePeriod||
            ' and d1.rollup_timestamp <= sysdate '||
                        ' ) vsn, '||
                      ' mgmt_rt_regions r, mgmt_rt_region_mapping mp '|| 
                      ' where r.region_guid = mp.region_guid '||
                      ' and vsn.id = mp.id '||
                      ' and r.target_guid IN (SELECT * FROM TABLE(CAST(:2 AS SMP_EMD_STRING_ARRAY))) '||
                       ') r '||
                    ' where rollup_timestamp > '||timePeriod||
          ' and rollup_timestamp <= sysdate '||
                    ' and m.assoc_guid = def.assoc_guid '||
                    ' and def.assoc_def_name = ''supports_eum_on''' ||
                    ' and def.scope_target_type = '' ''' ||
                    ' and ct.target_guid = m.source_target_guid '||
                    ' and r.target_guid = m.source_target_guid '||
                    ' and (d.visitor_subnet_num = r.visitor_subnet_num '||
                    ' and d.visitor_domain = r.visitor_domain) '||
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and ct.target_guid IN (SELECT * FROM TABLE(CAST(:3 AS SMP_EMD_STRING_ARRAY)))'||
                    ' and d.metric_name = :4';

            IF (filter_name_in is not null)
            THEN                
                sqlquery := sqlquery||' and '||columnNameString||' like :5';
            END IF;

            sqlquery := sqlquery||' group by m.source_target_guid, ct.target_name, '||columnNameString;
 
        ELSE
            sqlquery := 
                'select '||columnNameString||', '||
                    ' sum(hits), '||
                    ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(min(response_time_minimum)/1000,2), '||
                    ' round(max(response_time_maximum)/1000,2), '||
                  ' decode( '||
                  '   sum(hits), '||
                  '   0, 0, '||
                  '   1, 0, '||
                  '   round( sqrt( '||
                  '       (sum(hits) * '||
                  '       (sum(decode(hits, '||
                  '           0, 0, '||
                  '           (((response_time_variance * hits *  '||
                  '           (hits - 1)) + '||
                  '           power((hits * response_time_average), 2))  '||
                  '           / hits)))) -  '||
                  '       power(sum(response_time_average * hits), 2)) /  '||
                  '   (sum(hits) * (sum(hits) - 1)) '||
                  '   )/1000, 2) '||
                  ' ), '||
                    ' round(sum(response_time_average*hits)/1000,2), '||
                    ' ct.target_name '||
                    ' from '||tableNameString||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                    ' where rollup_timestamp > '||timePeriod||
                    ' and rollup_timestamp <= sysdate '||
                    ' and m.assoc_guid = def.assoc_guid '||
                    ' and def.assoc_def_name = ''supports_eum_on''' ||
                    ' and def.scope_target_type = '' ''' ||
                    ' and ct.target_guid = m.source_target_guid '||
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and d.metric_name = :1 '||
                    ' and ct.target_type = :2 '||
                    ' and ct.target_name IN (SELECT * FROM TABLE(CAST(:3 AS SMP_EMD_STRING_ARRAY)))';

            IF (filter_name_in is not null)
            THEN
                sqlquery := sqlquery||' and '||columnNameString||' like :4';
            END IF;

            sqlquery := sqlquery||' group by m.source_target_guid, ct.target_name, '||columnNameString;

        END IF;

        IF (stat_type_in = 'showPerf')
        THEN
            sqlquery := sqlquery||' order by sum(response_time_average*hits)/sum(hits) desc ';
        ELSIF (stat_type_in = 'showPerfTotalTime')
        THEN
            sqlquery := sqlquery||' order by sum(response_time_average*hits) desc ';
        ELSIF (stat_type_in = 'showServTime')
        THEN
            sqlquery := sqlquery||' order by sum(server_time_average*hits)/sum(hits) desc ';        
        ELSE
            sqlquery := sqlquery||' order by sum(hits) desc';
        END IF;

--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 0, 254));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 255, 254));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 509, 254));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlquery, 763, 254));
        IF obj_type_in = 'byReg'
        THEN
          IF (filter_name_in is not null)
          THEN 
            OPEN results_out FOR sqlquery USING website_guids_list, website_guids_list, website_guids_list, metric_name_in, filter_name_in;
          ELSE
            OPEN results_out FOR sqlquery USING website_guids_list, website_guids_list, website_guids_list, metric_name_in;
          END IF;
        ELSE
          IF (filter_name_in is not null)
          THEN
            OPEN results_out FOR sqlquery USING metric_name_in, 
                  target_type_in, target_names_in, filter_name_in; 
          ELSE
            OPEN results_out FOR sqlquery USING metric_name_in, 
                  target_type_in, target_names_in; 
          END IF;
        END IF;

   END IF;

END;


--
-- getUrlStatsObj -  returns statistical information about a given URL
--
--      If no stats are available, returns object with 'n/a' as values
--      and returns stats_found_ret set to 0
--
--  Inputs:
--    page_type_in
--    metric_name_in
--    target_guid_in
--    url_filename_in
--    display_name_in
--
--  Return:
--    no_stats_found - set to 1 if no stats were found, otherwise 0
--    Object containing statistical information about an URL:
--          queryName 
--          displayName 
--          avgVal
--          hitsVal
--          minVal
--          maxVal
--          stddevVal
--          varianceVal
--          critThreshold
--          warnThreshold
--          currentAvg
--          urlLink
--          mostFavorite
--
FUNCTION getUrlStatsObj(
            page_type_in IN VARCHAR2,
            metric_name_in IN VARCHAR2,
            target_guid_in IN RAW,
            url_filename_in IN VARCHAR2,
            display_name_in IN VARCHAR2,
            obj_out OUT MGMT_MNTR_USER_STATS_OBJ)
                return INTEGER IS

    stats_found_ret   INTEGER;   

    sel_cv        cursorType;
    il_sel_cv     cursorType;

    sqlquery      VARCHAR(5000);
    ilsqlquery    VARCHAR(1000);

    startTime     DATE;
    endTime       DATE;
    tableName     VARCHAR(2000);
    incompleteLoadsTableName VARCHAR2(64);

    curLink       VARCHAR2(1280);
    curAvg        VARCHAR2(20);
    curServAvg    VARCHAR2(20);
    curDBAvg      VARCHAR2(20);
    curMin        VARCHAR2(20);
    curMax        VARCHAR2(20);
    curStddev     VARCHAR2(20);
    curVariance   VARCHAR2(20);
    curHits       VARCHAR2(20);
    curCrit       VARCHAR2(20);
    curWarn       VARCHAR2(20);
    curMostFavorite VARCHAR(5);
    curIncompHits VARCHAR2(20);
    curIncompAvg  VARCHAR2(20);
    
    l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();

BEGIN
    IF ( MGMT_USER.has_priv(l_current_user, MGMT_USER.VIEW_TARGET,target_guid_in) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV ) THEN
        RAISE MGMT_GLOBAL.insufficient_privileges;
    END IF;


    stats_found_ret := 0;
    curMostFavorite := 'false';

    BEGIN

        endTime := MGMT_TARGET.SYSDATE_TARGET(target_guid_in);

        IF page_type_in = 'byMonth'
        THEN
            startTime := endTime - 31;
            tableName := 'MGMT_RT_URL_1DAY';
            incompleteLoadsTableName := 'MGMT_RT_INCOMPLETE_LOADS_1DAY';
        ELSE
            IF page_type_in = 'byWeek'  
            THEN
              startTime := endTime - 7;
              tableName := 'MGMT_RT_URL_1DAY';
              incompleteLoadsTableName := 'MGMT_RT_INCOMPLETE_LOADS_1DAY';   
            ELSE
              startTime := endTime - 1;
              TableName := 
                '(SELECT b.target_guid, b.metric_name, b.url_filename, b.url_link, '||
                '        b.rollup_timestamp, b.hits, b.response_time_average, '||
                '        b.response_time_minimum, b.response_time_maximum, '||
                '        b.response_time_sdev, b.response_time_variance, '||
                '        b.server_time_average, b.server_time_minimum, '||
                '        b.server_time_maximum, b.server_time_sdev, '||
                '        b.server_time_variance, b.db_time_average, b.db_time_minimum, '||
                '        b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
                'FROM MGMT_RT_URL_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
                || 'WHERE b.target_guid = mtrt.target_guid ' ||
                     'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
                     'AND mtrt.rollup_table_name = ''MGMT_RT_URL_1HOUR'' ' ||
                'UNION ' || 
                'SELECT h.target_guid, h.metric_name, h.url_filename, h.url_link, '||
                '       h.rollup_timestamp, h.hits, h.response_time_average, '||
                '       h.response_time_minimum, h.response_time_maximum, '||
                '       h.response_time_sdev, h.response_time_variance, '||
                '       h.server_time_average, h.server_time_minimum, '||
                '       h.server_time_maximum, h.server_time_sdev, '||
                '       h.server_time_variance, h.db_time_average, h.db_time_minimum, '||
                '       h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
                'FROM MGMT_RT_URL_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
                || 'WHERE h.target_guid = mtrt.target_guid ' ||
                     'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
                     'AND mtrt.rollup_table_name = ''MGMT_RT_URL_1HOUR'')';
              incompleteLoadsTableName := 'EM$RT_INCOMPLETE_LOADS';
            END IF;
        END IF;

        sqlquery := 'select nvl(max(d.url_link),'' ''), '||
            ' round(sum(response_time_average * hits) / SUM(hits)/1000,2), '||
            ' round(sum(server_time_average * hits) / SUM(hits)/1000,2), '||
            ' round(sum(db_time_average * hits) / SUM(hits)/1000,2), '||
            ' round(min(response_time_minimum)/1000,2), '||
            ' round(max(response_time_maximum)/1000,2), '||
            ' decode( '||
            '   sum(hits), '||
            '   0, 0, '||
            '   1, 0, '||
            '   round( sqrt( '||
            '       (sum(hits) * '||
            '       (sum(decode(hits, '||
            '           0, 0, '||
            '           (((response_time_variance * hits *  '||
            '           (hits - 1)) + '||
            '           power((hits * response_time_average), 2))  '||
            '           / hits)))) -  '||
            '       power(sum(response_time_average * hits), 2)) /  '||
            '     (sum(hits) * (sum(hits) - 1)) '||
            '   )/1000, 2 ) '||
            ' ), '||
        ' decode('||
        ' sum(hits),'||
        ' 0, 0,'||
        ' 1, 0,'||
        ' round( (sum(hits) *'||
          ' (sum(decode(hits,'||
          '   0, 0,'||
          '   (((response_time_variance * hits * '||
      '   (hits - 1)) +'||
            '   power((hits * response_time_average), 2)) '||
      '   / hits)) )) - '||
      ' power(sum(response_time_average * hits), 2)) / '||
      ' (sum(hits) * (sum(hits) - 1))'||
        ' /1000, 2 ) ), '||
            ' sum(hits), '||
            ' to_char(0), to_char(0) '||
            ' from '||tableName||' d, mgmt_target_assocs m, mgmt_target_assoc_defs def '||
            ' where d.target_guid = m.assoc_target_guid '||
              ' and m.source_target_guid = :1 '||
              ' and d.url_filename = :2 '||
              ' and d.metric_name = :3 '||
              ' and m.assoc_guid = def.assoc_guid '||
              ' and def.assoc_def_name = ''supports_eum_on''' ||
              ' and def.scope_target_type = '' ''' ||
              ' and d.rollup_timestamp > :4 '||
              ' and d.rollup_timestamp <= :5 '||
              ' group by m.source_target_guid ';

        OPEN sel_cv FOR sqlquery USING target_guid_in, url_filename_in, metric_name_in, startTime, endTime;

        FETCH sel_cv INTO curLink, curAvg, curServAvg, curDBAvg, curMin, curMax, curStddev,
            curVariance, curHits, curCrit, curWarn ;

        CLOSE sel_cv;

        ilsqlquery :=
          'select SUM(num_incomplete_loads), '||
            'DECODE(SUM(num_incomplete_loads), 0, 0, '||
            'round(SUM(avg_server_time * num_incomplete_loads)/SUM(num_incomplete_loads)/1000,2)) '||
              'from ' || incompleteLoadsTableName || ' il, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
              'where il.target_guid = m.assoc_target_guid '||
              'and ct.target_guid = :1 '||
              'and ct.target_guid = m.source_target_guid '||
              'and m.assoc_guid = def.assoc_guid '||
              'and def.assoc_def_name = ''supports_eum_on''' ||
              'and def.scope_target_type = '' ''' ||
              'and il.page_url = :2 '||
              'and il.rollup_timestamp > :3 '||
              'and il.rollup_timestamp <= :4 '||
              'group by ct.target_guid ';

        OPEN il_sel_cv FOR ilsqlquery USING target_guid_in, url_filename_in, startTime, endTime;
        FETCH il_sel_cv INTO curIncompHits, curIncompAvg;       
        CLOSE il_sel_cv;


        IF (curAvg is null)
        THEN
            curAvg := '0';
        END IF;
        IF (curServAvg is null)
        THEN
            curServAvg := '0';
        END IF;
        IF (curDBAvg is null)
        THEN
            curDBAvg := '0';
        END IF;
        IF (curMin is null)
        THEN
            curMin := '0';
        END IF;
        IF (curMax is null)
        THEN
            curMax := '0';
        END IF;
        IF (curStddev is null)
        THEN
            curStddev := '0';
        END IF;
        IF (curVariance is null)
        THEN
            curVariance := '0';
        END IF;
        IF (curHits is null)
        THEN
            curHits := '0';
        END IF;
        IF (curCrit is null)
        THEN
            curCrit := '0';
        END IF;
        IF (curWarn is null)
        THEN
            curWarn := '0';
        END IF;
        IF(curIncompHits is null)
        THEN
            curIncompHits := '0';
        END IF;
        IF (curIncompAvg is null)
        THEN
            curIncompAvg := '0';
        END IF;

        obj_out := MGMT_MNTR_USER_STATS_OBJ(url_filename_in, display_name_in, curAvg, curServAvg, curDBAvg,
          curHits, curMin, curMax, curStddev, curVariance,
          curCrit, curWarn, curLink, curMostFavorite, curIncompHits, curIncompAvg);

        stats_found_ret := 1;

    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
          obj_out := MGMT_MNTR_USER_STATS_OBJ(url_filename_in, display_name_in, '0', '0', '0',
              '0', '0', '0', '0', '0',
              '0', '0', ' ', 'false', '0', '0');
        IF sel_cv%ISOPEN
        THEN
           CLOSE sel_cv;
        END IF;

    WHEN OTHERS THEN
      IF sel_cv%ISOPEN
         THEN
           CLOSE sel_cv;
      END IF;
      RAISE;

    END;

    return stats_found_ret;
END;

--
-- getMetricsResponseTimeseries - gets Response metrics timeseries for a given target
--
--  Inputs:
--    target_name_in
--    target_type_in
--    page_type_in
--    metric_name_in
--    display_name_in
--          an URL or a Watch List display name for an URL
--
--  Outputs:
--
--    metric_warning_threshold_out 
--    metric_critical_threshold_out
--    results_out - cursor containing result set of timeseries query
--
--

PROCEDURE getMetricsResponseTimeseries(target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_warning_threshold_out OUT VARCHAR2,
                            metric_critical_threshold_out OUT VARCHAR2,
                            results_out OUT cursorType) IS

    sqlquery      VARCHAR(3000);
    startTime     DATE;
    endTime       DATE;
    tableName     VARCHAR(64);
    targetGuid    RAW(16);

BEGIN

    -- Initialize out parameters
    --
    metric_warning_threshold_out := ' ';
    metric_critical_threshold_out := ' ';

    SELECT target_guid
      INTO targetGuid
      FROM MGMT_TARGETS
     WHERE target_name = target_name_in
       AND target_type = target_type_in;

    endTime := MGMT_TARGET.SYSDATE_TARGET(targetGuid);

    IF page_type_in = 'byWeek'
    THEN
        startTime := endTime - 7;
        tableName := 'MGMT_METRICS_1HOUR';
    ELSIF page_type_in = 'byMonth'
    THEN
        startTime := endTime - 31;
        tableName := 'MGMT_METRICS_1DAY';
    ELSE  
        startTime := endTime - 1;
        tableName := 'MGMT_METRICS_1HOUR';
    END IF;
    
    -- get the Time series
    --
    sqlquery := 'select column_label, '||
            ' rollup_timestamp, '||
            ' round(value_average,2) '||
        ' from '||tableName||' d, mgmt_metrics mt, mgmt_targets t '||
        ' where d.rollup_timestamp > :1 '||
            ' and d.rollup_timestamp <= :2 '||
            ' and d.metric_guid = mt.metric_guid '||
            ' and d.target_guid = t.target_guid '||            
            ' and mt.metric_name = ''Response'''||
            ' and t.target_name = :3 '||
            ' and t.target_type = :4 '||
            ' and mt.metric_column = ''Timing'''||
            ' and mt.metric_column is not null '||
            ' and mt.metric_type = 0 '||
            ' and d.value_average > 0 '||
            ' and t.target_type = mt.target_type '||
            ' and t.type_meta_ver = mt.type_meta_ver '||
            ' and (t.category_prop_1 = mt.category_prop_1 or mt.category_prop_1 = '' '') '||
            ' and (t.category_prop_2 = mt.category_prop_2 or mt.category_prop_2 = '' '') '||
            ' and (t.category_prop_3 = mt.category_prop_3 or mt.category_prop_3 = '' '') '||
            ' and (t.category_prop_4 = mt.category_prop_4 or mt.category_prop_4 = '' '') '||
            ' and (t.category_prop_5 = mt.category_prop_5 or mt.category_prop_5 = '' '') '||
        ' order by metric_column, rollup_timestamp';

    OPEN results_out FOR sqlquery USING startTime, endTime, target_name_in, target_type_in;

    -- get the thresholds for the http timing fetchlet
    -- this is our primary metric for display
    --
    select mt.warning_threshold, 
        mt.critical_threshold 
    into 
        metric_warning_threshold_out,
        metric_critical_threshold_out
    from mgmt_metric_thresholds mt, mgmt_targets t, mgmt_metrics m
    where t.target_name = target_name_in
        and t.target_type = target_type_in
        and m.metric_name = 'Response'
        and m.metric_column = 'Timing'
        and m.metric_type = 0
        and t.target_type = m.target_type
        and t.type_meta_ver = m.type_meta_ver
        and (t.category_prop_1 = m.category_prop_1 or m.category_prop_1 = ' ')
        and (t.category_prop_2 = m.category_prop_2 or m.category_prop_2 = ' ')
        and (t.category_prop_3 = m.category_prop_3 or m.category_prop_3 = ' ')
        and (t.category_prop_4 = m.category_prop_4 or m.category_prop_4 = ' ')
        and (t.category_prop_5 = m.category_prop_5 or m.category_prop_5 = ' ')
  and mt.metric_guid = m.metric_guid
  and mt.target_guid = t.target_guid; 

END;


--
-- getResponseTimeseries - gets timeseries cursor for a given query_name
--      The cursor returns: name, timestamp, response time, num hits columns
--  
--  Inputs:
--    target_name_in
--    target_type_in
--    page_type_in
--    metric_name_in
--    obj_type_in
--    stat_type_in
--    query_name_in
--
--  Outputs:
--
--    results_out - cursor containing result set of timeseries query
--
--

PROCEDURE getResponseTimeseries(target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_name_in IN VARCHAR2,
                            obj_type_in IN VARCHAR2,
                            query_name_in IN VARCHAR2,
                            results_out OUT cursorType) IS


    columnNameString VARCHAR2(40);
    tableNameString VARCHAR(2000);
    urlTableName VARCHAR2(2000);
    domainTableName VARCHAR2(2000);
    regionTableName VARCHAR2(2000);
    ipTableName VARCHAR2(2000);

    startTime DATE;
    endTime DATE;
    targetGuid RAW(16);
    
    sqlquery VARCHAR(5000);
    queryName   VARCHAR2(1024);
    l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();

BEGIN
    IF ( MGMT_USER.has_priv(l_current_user, MGMT_USER.VIEW_TARGET,target_name_in, target_type_in) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV ) THEN
        RAISE MGMT_GLOBAL.insufficient_privileges;
    END IF;

    queryName := query_name_in;

    SELECT target_guid
      INTO targetGuid
      FROM MGMT_TARGETS
     WHERE target_name = target_name_in
       AND target_type = target_type_in;

    endTime := MGMT_TARGET.SYSDATE_TARGET(targetGuid);

    IF page_type_in = 'byMonth'
    THEN
        startTime := endTime - 31;
        urlTableName := 'MGMT_RT_URL_1DAY';
        domainTableName := 'MGMT_RT_DOMAIN_1DAY';
        regionTableName := 'MGMT_RT_DOMAIN_1DAY';
        ipTableName := 'MGMT_RT_IP_1DAY';
    ELSE
        IF page_type_in = 'byWeek'
        THEN
            startTime := endTime - 7;
        ELSE
            startTime := endTime - 1;
        END IF;
        urlTableName := 
          '(SELECT b.target_guid, b.metric_name, b.url_filename, b.url_link, '||
          '        b.rollup_timestamp, b.hits, b.response_time_average, '||
          '        b.response_time_minimum, b.response_time_maximum, '||
          '        b.response_time_sdev, b.response_time_variance, '||
          '        b.server_time_average, b.server_time_minimum, '||
          '        b.server_time_maximum, b.server_time_sdev, '||
          '        b.server_time_variance, b.db_time_average, b.db_time_minimum, '||
          '        b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
          'FROM MGMT_RT_URL_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE b.target_guid = mtrt.target_guid ' ||
               'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_URL_1HOUR'' ' ||
          'UNION ' || 
          'SELECT h.target_guid, h.metric_name, h.url_filename, h.url_link, '||
          '       h.rollup_timestamp, h.hits, h.response_time_average, '||
          '       h.response_time_minimum, h.response_time_maximum, '||
          '       h.response_time_sdev, h.response_time_variance, '||
          '       h.server_time_average, h.server_time_minimum, '||
          '       h.server_time_maximum, h.server_time_sdev, '||
          '       h.server_time_variance, h.db_time_average, h.db_time_minimum, '||
          '       h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
          'FROM MGMT_RT_URL_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE h.target_guid = mtrt.target_guid ' ||
               'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_URL_1HOUR'')';
        domainTableName := 
          '(SELECT b.target_guid, b.metric_name, b.visitor_domain, b.visitor_subnet, '||
          '        b.visitor_subnet_num, b.rollup_timestamp, b.hits, '||
          '        b.response_time_average, b.response_time_minimum, '||
          '        b.response_time_maximum, b.response_time_sdev, b.response_time_variance, '||
          '        b.server_time_average, b.server_time_minimum, b.server_time_maximum, '||
          '        b.server_time_sdev, b.server_time_variance, b.db_time_average, '||
          '        b.db_time_minimum, b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
          'FROM MGMT_RT_DOMAIN_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE b.target_guid = mtrt.target_guid ' ||
               'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_DOMAIN_1HOUR'' ' ||
          'UNION ' || 
          'SELECT h.target_guid, h.metric_name, h.visitor_domain, h.visitor_subnet, '||
          '       h.visitor_subnet_num, h.rollup_timestamp, h.hits, '||
          '       h.response_time_average, h.response_time_minimum, '||
          '       h.response_time_maximum, h.response_time_sdev, h.response_time_variance, '||
          '       h.server_time_average, h.server_time_minimum, h.server_time_maximum, '||
          '       h.server_time_sdev, h.server_time_variance, h.db_time_average, '||
          '       h.db_time_minimum, h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
          'FROM MGMT_RT_DOMAIN_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE h.target_guid = mtrt.target_guid ' ||
               'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_DOMAIN_1HOUR'')';
        regionTableName := domainTableName;
        ipTableName := 
          '(SELECT b.target_guid, b.metric_name, b.visitor_node, b.rollup_timestamp, '||
          '        b.hits, b.response_time_average, b.response_time_minimum, '||
          '        b.response_time_maximum, b.response_time_sdev, b.response_time_variance, '||
          '        b.server_time_average, b.server_time_minimum,  b.server_time_maximum, '||
          '        b.server_time_sdev, b.server_time_variance, b.db_time_average, '||
          '        b.db_time_minimum, b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
          'FROM MGMT_RT_IP_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE b.target_guid = mtrt.target_guid ' ||
               'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_IP_1HOUR'' ' ||
          'UNION ' || 
          'SELECT h.target_guid, h.metric_name, h.visitor_node, h.rollup_timestamp, '||
          '       h.hits, h.response_time_average, h.response_time_minimum, '||
          '       h.response_time_maximum, h.response_time_sdev, h.response_time_variance, '||
          '       h.server_time_average, h.server_time_minimum,  h.server_time_maximum, '||
          '       h.server_time_sdev, h.server_time_variance, h.db_time_average, '||
          '       h.db_time_minimum, h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
          'FROM MGMT_RT_IP_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE h.target_guid = mtrt.target_guid ' ||
               'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_IP_1HOUR'')';
    END IF;

    -- perform the appropriate query
    --
    IF (obj_type_in = 'byUrl')
    THEN
        columnNameString := 'URL_FILENAME'; 
        --
        -- URL Timeseries
        --
        BEGIN
            select url_filename 
                into queryName
                from mgmt_rt_urls u, mgmt_targets t
                where t.target_name = target_name_in
                and t.target_type = target_type_in
                and t.target_guid = u.target_guid
                and u.display_name = query_name_in;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            queryName := query_name_in;
        WHEN OTHERS THEN
          RAISE;
        END;

        --
        -- Timeseries query
        --
            sqlquery := 
                'select '||
                    ' nvl(max((select display_name from mgmt_rt_urls u '||
                        ' where u.target_guid = m.source_target_guid '||
                        ' and u.url_filename = d.url_filename)), d.url_filename), '||
                    ' rollup_timestamp, '||
                    ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||                    
                    ' sum(hits) '||
                    ' from '||urlTableName||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                    ' where rollup_timestamp > :1 '||
                        ' and rollup_timestamp <= :2 '||
                        ' and d.target_guid = m.assoc_target_guid '||
                        ' and '||columnNameString||' = :3 '||
                        ' and m.assoc_guid = def.assoc_guid '||
                        ' and def.assoc_def_name = ''supports_eum_on''' ||
                        ' and def.scope_target_type = '' ''' ||
                        ' and ct.target_guid = m.source_target_guid ' ||
                        ' and d.metric_name = :4 '||
                        ' and ct.target_name = :5 '||
                        ' and ct.target_type = :6 '||
                        ' group by m.source_target_guid, '||columnNameString||', rollup_timestamp '||
                        ' order by rollup_timestamp ';

    ELSIF obj_type_in = 'byWebserver'
    THEN
        tableNameString := urlTableName;

        sqlquery := 
            'select (select target_name from mgmt_targets where target_guid = d.target_guid), '||
                ' rollup_timestamp, '||
                ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||
                ' sum(hits) '||
                ' from '||tableNameString||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                ' where rollup_timestamp > :1 '||
                    ' and rollup_timestamp <= :2 '||
                    ' and m.assoc_guid = def.assoc_guid '||
                    ' and def.assoc_def_name = ''supports_eum_on''' ||
                    ' and def.scope_target_type = '' ''' ||
                    ' and ct.target_guid = m.source_target_guid '||
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and d.metric_name = :3 '||
                    ' and ct.target_name = :4 '||
                    ' and ct.target_type = :5 '||
                    ' group by d.target_guid, rollup_timestamp '||
                    ' order by rollup_timestamp ';

    ELSIF obj_type_in = 'byWebserverbyUrl'
    THEN
        tableNameString := urlTableName;
        columnNameString := 'URL_FILENAME'; 

        BEGIN
            select url_filename 
                into queryName
                from mgmt_rt_urls u, mgmt_targets t
                where t.target_name = target_name_in
                and t.target_type = target_type_in
                and t.target_guid = u.target_guid
                and u.display_name = query_name_in;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            queryName := query_name_in;
        WHEN OTHERS THEN
          RAISE;
        END;

        sqlquery := 
            'select mt.target_name, '||
                ' rollup_timestamp, '||
                ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||
                ' sum(hits) '||
                ' from '||urlTableName||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_targets mt, mgmt_target_assoc_defs def '||
                ' where rollup_timestamp > :1 '||
                    ' and rollup_timestamp <= :2 '||
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and '||columnNameString||' = :3 '||
                    ' and m.assoc_guid = def.assoc_guid '||
                    ' and def.assoc_def_name = ''supports_eum_on''' ||
                    ' and def.scope_target_type = '' ''' ||
                    ' and ct.target_guid = m.source_target_guid '||
                    ' and mt.target_guid = m.assoc_target_guid '||
                    ' and d.metric_name = :4 '||
                    ' and ct.target_name = :5 '||
                    ' and ct.target_type = :6 '||
                    ' group by d.target_guid, mt.target_name, '||columnNameString||', rollup_timestamp '||
                    ' order by rollup_timestamp ';

    ELSIF obj_type_in = 'byReg'
    THEN
        columnNameString := 'REGION_NAME';
        tableNameString := regionTableName;
        sqlquery := 
            'select '||columnNameString||', '||
                ' rollup_timestamp, '||
                ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||
                ' sum(hits) '||
                ' from '||tableNameString||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def, '||
                ' mgmt_rt_regions r'||
                ' where rollup_timestamp > :1 '||
                ' and rollup_timestamp <= :2 '||
                ' and r.target_guid = m.source_target_guid '||
                ' and '||columnNameString||' = :3 '||
                ' 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_subnet_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 d.target_guid = m.assoc_target_guid '||
                ' and m.assoc_guid = def.assoc_guid '||
                ' and def.assoc_def_name = ''supports_eum_on''' ||
                ' and def.scope_target_type = '' ''' ||
                ' and ct.target_guid = m.source_target_guid '||                
                ' and d.metric_name = :4 '||
                ' and ct.target_name = :5 '||
                ' and ct.target_type = :6 '||
                ' group by m.source_target_guid, '||columnNameString||', rollup_timestamp '||
                ' order by rollup_timestamp ';
    ELSE
        IF (obj_type_in = 'byDom')
        THEN
            columnNameString := 'VISITOR_DOMAIN';
            tableNameString := domainTableName;
        ELSIF obj_type_in = 'byIp'
        THEN
            columnNameString := 'VISITOR_NODE';
            tableNameString := ipTableName;
        END IF;


        --
        -- Timeseries query
        --
        sqlquery := 
            'select '||columnNameString||', '||
                ' rollup_timestamp, '||
                ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||                
                ' sum(hits) '||
                ' from '||tableNameString||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                ' where rollup_timestamp > :1 '||
                ' and rollup_timestamp <= :2 '||
                ' and d.target_guid = m.assoc_target_guid '||
                ' and '||columnNameString||' = :3 '||
                ' and d.metric_name = :4 '||
                ' and m.assoc_guid = def.assoc_guid '||
                ' and def.assoc_def_name = ''supports_eum_on''' ||
                ' and def.scope_target_type = '' ''' ||
                ' and ct.target_guid = m.source_target_guid '||
                ' and ct.target_name = :5 '||
                ' and ct.target_type = :6 '||
                ' group by m.source_target_guid, '||columnNameString||', rollup_timestamp '||
                ' order by rollup_timestamp ';

    END IF;

    IF obj_type_in = 'byWebserver'
    THEN
       OPEN results_out FOR sqlquery USING startTime, endTime, metric_name_in, target_name_in, target_type_in;
    ELSE
       OPEN results_out FOR sqlquery USING startTime, endTime, queryName, metric_name_in, 
                target_name_in, target_type_in;
    END IF;

END;

PROCEDURE getResponseTimeseriesForQuries(target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_name_in IN VARCHAR2,
                            obj_type_in IN VARCHAR2,
                            query_name_in IN VARCHAR2,
                            results_out OUT cursorType) IS


    columnNameString VARCHAR2(40);
    tableNameString VARCHAR(2000);
    urlTableName VARCHAR2(2000);
    domainTableName VARCHAR2(2000);
    regionTableName VARCHAR2(2000);
    ipTableName VARCHAR2(2000);
    
    startTime DATE;
    endTime DATE;
    targetGuid RAW(16);

    sqlquery VARCHAR(5000);
    queryName   VARCHAR2(1024);
    l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();

BEGIN
    IF ( MGMT_USER.has_priv(l_current_user, MGMT_USER.VIEW_TARGET,target_name_in, target_type_in) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV ) THEN
        RAISE MGMT_GLOBAL.insufficient_privileges;
    END IF;

    queryName := query_name_in;
    EMDW_LOG.DEBUG(queryName, 'asdf') ;

    SELECT target_guid
      INTO targetGuid
      FROM MGMT_TARGETS
     WHERE target_name = target_name_in
       AND target_type = target_type_in;

    endTime := MGMT_TARGET.SYSDATE_TARGET(targetGuid);

    IF page_type_in = 'byMonth'
    THEN
        startTime := endTime - 31;
        urlTableName := 'MGMT_RT_URL_1DAY';
        domainTableName := 'MGMT_RT_DOMAIN_1DAY';
        regionTableName := 'MGMT_RT_DOMAIN_1DAY';
        ipTableName := 'MGMT_RT_IP_1DAY';
    ELSE
        IF page_type_in = 'byWeek'
        THEN
          startTime := endTime - 7;
        ELSE          
          startTime := endTime - 1;
        END IF;
        urlTableName := 
          '(SELECT b.target_guid, b.metric_name, b.url_filename, b.url_link, '||
          '        b.rollup_timestamp, b.hits, b.response_time_average, '||
          '        b.response_time_minimum, b.response_time_maximum, '||
          '        b.response_time_sdev, b.response_time_variance, '||
          '        b.server_time_average, b.server_time_minimum, '||
          '        b.server_time_maximum, b.server_time_sdev, '||
          '        b.server_time_variance, b.db_time_average, b.db_time_minimum, '||
          '        b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
          'FROM MGMT_RT_URL_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE b.target_guid = mtrt.target_guid ' ||
               'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_URL_1HOUR'' ' ||
          'UNION ' || 
          'SELECT h.target_guid, h.metric_name, h.url_filename, h.url_link, '||
          '       h.rollup_timestamp, h.hits, h.response_time_average, '||
          '       h.response_time_minimum, h.response_time_maximum, '||
          '       h.response_time_sdev, h.response_time_variance, '||
          '       h.server_time_average, h.server_time_minimum, '||
          '       h.server_time_maximum, h.server_time_sdev, '||
          '       h.server_time_variance, h.db_time_average, h.db_time_minimum, '||
          '       h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
          'FROM MGMT_RT_URL_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE h.target_guid = mtrt.target_guid ' ||
               'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_URL_1HOUR'')';
        domainTableName := 
          '(SELECT b.target_guid, b.metric_name, b.visitor_domain, b.visitor_subnet, '||
          '        b.visitor_subnet_num, b.rollup_timestamp, b.hits, '||
          '        b.response_time_average, b.response_time_minimum, '||
          '        b.response_time_maximum, b.response_time_sdev, b.response_time_variance, '||
          '        b.server_time_average, b.server_time_minimum, b.server_time_maximum, '||
          '        b.server_time_sdev, b.server_time_variance, b.db_time_average, '||
          '        b.db_time_minimum, b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
          'FROM MGMT_RT_DOMAIN_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE b.target_guid = mtrt.target_guid ' ||
               'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_DOMAIN_1HOUR'' ' ||
          'UNION ' || 
          'SELECT h.target_guid, h.metric_name, h.visitor_domain, h.visitor_subnet, '||
          '       h.visitor_subnet_num, h.rollup_timestamp, h.hits, '||
          '       h.response_time_average, h.response_time_minimum, '||
          '       h.response_time_maximum, h.response_time_sdev, h.response_time_variance, '||
          '       h.server_time_average, h.server_time_minimum, h.server_time_maximum, '||
          '       h.server_time_sdev, h.server_time_variance, h.db_time_average, '||
          '       h.db_time_minimum, h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
          'FROM MGMT_RT_DOMAIN_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE h.target_guid = mtrt.target_guid ' ||
               'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_DOMAIN_1HOUR'')';
        regionTableName := domainTableName;
        ipTableName := 
          '(SELECT b.target_guid, b.metric_name, b.visitor_node, b.rollup_timestamp, '||
          '        b.hits, b.response_time_average, b.response_time_minimum, '||
          '        b.response_time_maximum, b.response_time_sdev, b.response_time_variance, '||
          '        b.server_time_average, b.server_time_minimum,  b.server_time_maximum, '||
          '        b.server_time_sdev, b.server_time_variance, b.db_time_average, '||
          '        b.db_time_minimum, b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
          'FROM MGMT_RT_IP_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE b.target_guid = mtrt.target_guid ' ||
               'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_IP_1HOUR'' ' ||
          'UNION ' || 
          'SELECT h.target_guid, h.metric_name, h.visitor_node, h.rollup_timestamp, '||
          '       h.hits, h.response_time_average, h.response_time_minimum, '||
          '       h.response_time_maximum, h.response_time_sdev, h.response_time_variance, '||
          '       h.server_time_average, h.server_time_minimum,  h.server_time_maximum, '||
          '       h.server_time_sdev, h.server_time_variance, h.db_time_average, '||
          '       h.db_time_minimum, h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
          'FROM MGMT_RT_IP_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
          || 'WHERE h.target_guid = mtrt.target_guid ' ||
               'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
               'AND mtrt.rollup_table_name = ''MGMT_RT_IP_1HOUR'')';
    END IF;

    -- perform the appropriate query
    --
    IF (obj_type_in = 'byUrl')
    THEN
        columnNameString := 'URL_FILENAME'; 
        --
        -- URL Timeseries
        --
        --
        -- Timeseries query
        --
            sqlquery := 
                'select '||
                    ' nvl(max((select display_name from mgmt_rt_urls u '||
                        ' where u.target_guid = m.source_target_guid '||
                        ' and u.url_filename = d.url_filename)), d.url_filename), '||
                    ' rollup_timestamp, '||
                    ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                    ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||                    
                    ' sum(hits) '||
                    ' from '||urlTableName||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                    ' where rollup_timestamp > :1 '||
                        ' and rollup_timestamp <= :2 '||
                        ' and d.target_guid = m.assoc_target_guid '||
                        ' and '||columnNameString||' in :3 '||
                        ' and m.assoc_guid = def.assoc_guid '||
                        ' and def.assoc_def_name = ''supports_eum_on''' ||
                        ' and def.scope_target_type = '' ''' ||
                        ' and ct.target_guid = m.source_target_guid ' ||
                        ' and d.metric_name = :4 '||
                        ' and ct.target_name = :5 '||
                        ' and ct.target_type = :6 '||
                        ' group by m.source_target_guid, '||columnNameString||', rollup_timestamp '||
                        ' order by rollup_timestamp ';

    EMDW_LOG.DEBUG(sqlquery,
                   'asdf') ;
    ELSIF obj_type_in = 'byWebserver'
    THEN
        tableNameString := urlTableName;

        sqlquery := 
            'select (select target_name from mgmt_targets where target_guid = d.target_guid), '||
                ' rollup_timestamp, '||
                ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||
                ' sum(hits) '||
                ' from '||tableNameString||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                ' where rollup_timestamp > :1 '||
                    ' and rollup_timestamp <= :2 '||
                    ' and m.assoc_guid = def.assoc_guid '||
                    ' and def.assoc_def_name = ''supports_eum_on''' ||
                    ' and def.scope_target_type = '' ''' ||
                    ' and ct.target_guid = m.source_target_guid '||
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and d.metric_name = :3 '||
                    ' and ct.target_name = :4 '||
                    ' and ct.target_type = :5 '||
                    ' group by d.target_guid, rollup_timestamp '||
                    ' order by rollup_timestamp ';

    ELSIF obj_type_in = 'byWebserverbyUrl'
    THEN
        tableNameString := urlTableName;
        columnNameString := 'URL_FILENAME'; 


        sqlquery := 
            'select mt.target_name, '||
                ' rollup_timestamp, '||
                ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||
                ' sum(hits) '||
                ' from '||urlTableName||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_targets mt, mgmt_target_assoc_defs def '||
                ' where rollup_timestamp > :1 '||
                    ' and rollup_timestamp <= :2 '||
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and '||columnNameString||' in :3 '||
                    ' and m.assoc_guid = def.assoc_guid '||
                    ' and def.assoc_def_name = ''supports_eum_on''' ||
                    ' and def.scope_target_type = '' ''' ||
                    ' and ct.target_guid = m.source_target_guid '||
                    ' and mt.target_guid = m.assoc_target_guid '||
                    ' and d.metric_name = :4 '||
                    ' and ct.target_name = :5 '||
                    ' and ct.target_type = :6 '||
                    ' group by d.target_guid, mt.target_name, '||columnNameString||', rollup_timestamp '||
                    ' order by rollup_timestamp ';

    ELSIF obj_type_in = 'byReg'
    THEN
        columnNameString := 'REGION_NAME';
        tableNameString := regionTableName;
        sqlquery := 
            'select '||columnNameString||', '||
                ' rollup_timestamp, '||
                ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||
                ' sum(hits) '||
                ' from '||tableNameString||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def, '||
                ' mgmt_rt_regions r'||
                ' where rollup_timestamp > :1 '||
                ' and rollup_timestamp <= :2 '||
                ' and r.target_guid = m.source_target_guid '||
                ' and '||columnNameString||' in :3 '||
                ' 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_subnet_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 d.target_guid = m.assoc_target_guid '||
                ' and m.assoc_guid = def.assoc_guid '||
                ' and def.assoc_def_name = ''supports_eum_on''' ||
                ' and def.scope_target_type = '' ''' ||
                ' and ct.target_guid = m.source_target_guid '||                
                ' and d.metric_name = :4 '||
                ' and ct.target_name = :5 '||
                ' and ct.target_type = :6 '||
                ' group by m.source_target_guid, '||columnNameString||', rollup_timestamp '||
                ' order by rollup_timestamp ';
    ELSE
        IF (obj_type_in = 'byDom')
        THEN
            columnNameString := 'VISITOR_DOMAIN';
            tableNameString := domainTableName;
        ELSIF obj_type_in = 'byIp'
        THEN
            columnNameString := 'VISITOR_NODE';
            tableNameString := ipTableName;
        END IF;


        --
        -- Timeseries query
        --
        sqlquery := 
            'select '||columnNameString||', '||
                ' rollup_timestamp, '||
                ' round(sum(response_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(server_time_average*hits)/sum(hits)/1000,2), '||
                ' round(sum(db_time_average*hits)/sum(hits)/1000,2), '||                
                ' sum(hits) '||
                ' from '||tableNameString||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                ' where rollup_timestamp > :1 '||
                ' and rollup_timestamp <= :2 '||
                ' and d.target_guid = m.assoc_target_guid '||
                ' and '||columnNameString||' in :3 '||
                ' and d.metric_name = :4 '||
                ' and m.assoc_guid = def.assoc_guid '||
                ' and def.assoc_def_name = ''supports_eum_on''' ||
                ' and def.scope_target_type = '' ''' ||
                ' and ct.target_guid = m.source_target_guid '||
                ' and ct.target_name = :5 '||
                ' and ct.target_type = :6 '||
                ' group by m.source_target_guid, '||columnNameString||', rollup_timestamp '||
                ' order by rollup_timestamp ';

    END IF;

    IF obj_type_in = 'byWebserver'
    THEN
       OPEN results_out FOR sqlquery USING startTime, endTime, metric_name_in, target_name_in, target_type_in;
    ELSE
       OPEN results_out FOR sqlquery USING startTime, endTime, query_name_in, metric_name_in, 
                target_name_in, target_type_in;
    END IF;

END;
--
-- getResponseDist - gets response distribution cursor for a given query_name
--      The cursor returns: response seconds, num hits columns
--  
--  Inputs:
--    target_name_in
--    target_type_in
--    page_type_in
--    metric_name_in
--    obj_type_in
--    stat_type_in
--    query_name_in
--    dist_type_in
--
--  Outputs:
--
--    results_out - cursor containing result set of timeseries query
--
--

PROCEDURE getResponseDist(target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_name_in IN VARCHAR2,
                            obj_type_in IN VARCHAR2,
                            query_name_in IN VARCHAR2,
                            dist_type_in IN NUMBER,
                            results_out OUT cursorType) IS

    columnNameString VARCHAR2(40);
    tableNameString VARCHAR(1024);
    urlTableName VARCHAR2(1024);
    domainTableName VARCHAR2(1024);
    regionTableName VARCHAR2(1024);
    ipTableName VARCHAR2(1024);
    
    sqlquery VARCHAR(5000);
    queryName   VARCHAR2(1024);
    l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();

    startTime DATE;
    endTime DATE;
    targetGuid RAW(16);

BEGIN

    IF ( MGMT_USER.has_priv(l_current_user, MGMT_USER.VIEW_TARGET,target_name_in, target_type_in) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV ) THEN
        RAISE MGMT_GLOBAL.insufficient_privileges;
    END IF;

    queryName := query_name_in;

    SELECT target_guid
      INTO targetGuid
      FROM MGMT_TARGETS
     WHERE target_name = target_name_in
       AND target_type = target_type_in;

    endTime := MGMT_TARGET.SYSDATE_TARGET(targetGuid);

    IF page_type_in = 'byMonth'
    THEN
        startTime := endTime - 31;
        urlTableName := 'MGMT_RT_URL_DIST_1DAY';
        domainTableName := 'MGMT_RT_DOMAIN_DIST_1DAY';
        regionTableName := 'MGMT_RT_DOMAIN_DIST_1DAY';
        ipTableName := 'MGMT_RT_IP_DIST_1DAY';
    ELSE
        IF page_type_in = 'byWeek'  
        THEN
          startTime := endTime - 7;
          urlTableName := 'MGMT_RT_URL_DIST_1DAY';
          domainTableName := 'MGMT_RT_DOMAIN_DIST_1DAY';
          regionTableName := 'MGMT_RT_DOMAIN_DIST_1DAY';
          ipTableName := 'MGMT_RT_IP_DIST_1DAY';
        ELSE
          startTime := endTime - 1;
          urlTableName := 
            '(SELECT b.target_guid, b.metric_name, b.url_filename, b.rollup_timestamp, '||
            '        b.hits, b.num_seconds, b.dist_value_type '||
            'FROM MGMT_RT_URL_DIST_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
            || 'WHERE b.target_guid = mtrt.target_guid ' ||
                 'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
                 'AND mtrt.rollup_table_name = ''MGMT_RT_URL_DIST_1HOUR'' ' ||
            'UNION ' || 
            'SELECT h.target_guid, h.metric_name, h.url_filename, h.rollup_timestamp, '||
            '       h.hits, h.num_seconds, h.dist_value_type '||
            'FROM MGMT_RT_URL_DIST_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
            || 'WHERE h.target_guid = mtrt.target_guid ' ||
                 'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
                 'AND mtrt.rollup_table_name = ''MGMT_RT_URL_DIST_1HOUR'')';
          domainTableName := 
            '(SELECT b.target_guid, b.metric_name, b.visitor_domain, b.visitor_subnet, '||
            '        b.visitor_subnet_num, b.rollup_timestamp, b.hits, b.num_seconds, '||
            '        b.dist_value_type '||
            'FROM MGMT_RT_DOMAIN_DIST_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
            || 'WHERE b.target_guid = mtrt.target_guid ' ||
                 'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
                 'AND mtrt.rollup_table_name = ''MGMT_RT_DOMAIN_DIST_1HOUR'' ' ||
            'UNION  ' || 
            'SELECT h.target_guid, h.metric_name, h.visitor_domain, h.visitor_subnet, '||
            '       h.visitor_subnet_num, h.rollup_timestamp, h.hits, h.num_seconds, '||
            '       h.dist_value_type '||
            'FROM MGMT_RT_DOMAIN_DIST_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
            || 'WHERE h.target_guid = mtrt.target_guid ' ||
                 'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
                 'AND mtrt.rollup_table_name = ''MGMT_RT_DOMAIN_DIST_1HOUR'')';
          regionTableName := domainTableName;
          ipTableName := 
            '(SELECT b.target_guid, b.metric_name, b.visitor_node, b.rollup_timestamp, '||
            '        b.hits, b.num_seconds, b.dist_value_type '||
            'FROM MGMT_RT_IP_DIST_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
            || 'WHERE b.target_guid = mtrt.target_guid ' ||
                 'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
                 'AND mtrt.rollup_table_name = ''MGMT_RT_IP_DIST_1HOUR'' ' ||
            'UNION ' || 
            'SELECT h.target_guid, h.metric_name, h.visitor_node, h.rollup_timestamp, '||
            '       h.hits, h.num_seconds, h.dist_value_type '||
            'FROM MGMT_RT_IP_DIST_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
            || 'WHERE h.target_guid = mtrt.target_guid ' ||
                 'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
                 'AND mtrt.rollup_table_name = ''MGMT_RT_IP_DIST_1HOUR'')';
        END IF;
    END IF;

    -- perform the appropriate query
    --
    IF (obj_type_in = 'byReg')
    THEN
        columnNameString := 'REGION_NAME';
        tableNameString := regionTableName;

        sqlquery := 'select '||
            ' decode(num_seconds,0,''0'',1,''1'',2,''2'',3,''3'',4,''4'',5,''5'',6,''6'',7,''7'', '||
            ' 8,''8'',9,''9'',10,''10'',11,''11'', 12,''12'',13,''13'',14,''14'',15,''15'', '||
            ' 16, ''16-20'',17,''16-20'',18,''16-20'',19,''16-20'',20,''16-20'', '||
            ' 21, ''21-25'',22,''21-25'',23,''21-25'',24,''21-25'',25,''21-25'', '||
            ' 26, ''26-30'',27,''26-30'',28,''26-30'',29,''26-30'',30,''26-30'', '||
            ' 31, ''31-35'',32,''31-35'',33,''31-35'',34,''31-35'',35,''31-35'', '||
            ' 36, ''36-40'',37,''36-40'',38,''36-40'',39,''36-40'',40,''36-40'', '||
            ' 41, ''41-50'',42,''41-50'',43,''41-50'',44,''41-50'',45,''41-50'', '||
            ' 46, ''41-50'',47,''41-50'',48,''41-50'',49,''41-50'',50,''41-50'', '||
            ' 51, ''51-60'',52,''51-60'',53,''51-60'',54,''51-60'',55,''51-60'', '||
            ' 56, ''51-60'',57,''51-60'',58,''51-60'',59,''51-60'',60,''51-60'', '||
            '''61+''), '||
            ' sum(hits) '||
            ' from '||tableNameString||' d, mgmt_target_assocs m,mgmt_targets ct, mgmt_target_assoc_defs def,'||
            ' mgmt_rt_regions r'||
            ' where rollup_timestamp > :1 '||
            ' and rollup_timestamp <= :2 '||
            ' and r.target_guid = m.source_target_guid '||
            ' and '||columnNameString||' = :3 '||
            ' 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_subnet_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 d.target_guid = m.assoc_target_guid '||
            ' and d.metric_name = :4 '||
            ' and m.assoc_guid = def.assoc_guid '||
            ' and def.ASSOC_DEF_name = ''supports_eum_on''' ||
            ' and def.scope_target_type = '' ''' ||
            ' and ct.target_guid = m.source_target_guid '||
            ' and ct.target_name = :5 '||
            ' and ct.target_type = :6 '||
            ' and d.dist_value_type = :7 '||
            ' group by m.source_target_guid, '||columnNameString||', metric_name, '||
            ' decode(num_seconds,0,''0'',1,''1'',2,''2'',3,''3'',4,''4'',5,''5'',6,''6'',7,''7'', '||
            ' 8,''8'',9,''9'',10,''10'',11,''11'', 12,''12'',13,''13'',14,''14'',15,''15'', '||
            ' 16, ''16-20'',17,''16-20'',18,''16-20'',19,''16-20'',20,''16-20'', '||
            ' 21, ''21-25'',22,''21-25'',23,''21-25'',24,''21-25'',25,''21-25'', '||
            ' 26, ''26-30'',27,''26-30'',28,''26-30'',29,''26-30'',30,''26-30'', '||
            ' 31, ''31-35'',32,''31-35'',33,''31-35'',34,''31-35'',35,''31-35'', '||
            ' 36, ''36-40'',37,''36-40'',38,''36-40'',39,''36-40'',40,''36-40'', '||
            ' 41, ''41-50'',42,''41-50'',43,''41-50'',44,''41-50'',45,''41-50'', '||
            ' 46, ''41-50'',47,''41-50'',48,''41-50'',49,''41-50'',50,''41-50'', '||
            ' 51, ''51-60'',52,''51-60'',53,''51-60'',54,''51-60'',55,''51-60'', '||
            ' 56, ''51-60'',57,''51-60'',58,''51-60'',59,''51-60'',60,''51-60'', '||
            '''61+''),'||
            ' decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, '||
                  ' 11,11,12,12,13,13,14,14,15,15, '||
                  ' 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, '||
                  ' 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, '||
                  ' 36,36,37,36,38,36,39,36,40,36, '||
                  ' 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, '||
                  ' 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, '||
                  ' 61) '||
            ' order by '||
            ' decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, '||
                  ' 11,11,12,12,13,13,14,14,15,15, '||
                  ' 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, '||
                  ' 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, '||
                  ' 36,36,37,36,38,36,39,36,40,36, '||
                  ' 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, '||
                  ' 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, '||
                  ' 61) ';

    ELSE
        IF (obj_type_in = 'byUrl')
        THEN
            columnNameString := 'URL_FILENAME'; 
            tableNameString := urlTableName;

            BEGIN
                select url_filename 
                    into queryName
                    from mgmt_rt_urls u, mgmt_targets t
                    where t.target_name = target_name_in
                    and t.target_type = target_type_in
                    and t.target_guid = u.target_guid
                    and u.display_name = query_name_in;
            EXCEPTION
            WHEN NO_DATA_FOUND THEN
                queryName := query_name_in;
            WHEN OTHERS THEN
              RAISE;
            END;
        ELSIF (obj_type_in = 'byDom')
        THEN
            columnNameString := 'VISITOR_DOMAIN';
            tableNameString := domainTableName;
        ELSIF obj_type_in = 'byIp'
        THEN
            columnNameString := 'VISITOR_NODE';
            tableNameString := ipTableName;
        END IF;

        sqlquery := 'select '||
            ' decode(num_seconds,0,''0'',1,''1'',2,''2'',3,''3'',4,''4'',5,''5'',6,''6'',7,''7'', '||
            ' 8,''8'',9,''9'',10,''10'',11,''11'', 12,''12'',13,''13'',14,''14'',15,''15'', '||
            ' 16, ''16-20'',17,''16-20'',18,''16-20'',19,''16-20'',20,''16-20'', '||
            ' 21, ''21-25'',22,''21-25'',23,''21-25'',24,''21-25'',25,''21-25'', '||
            ' 26, ''26-30'',27,''26-30'',28,''26-30'',29,''26-30'',30,''26-30'', '||
            ' 31, ''31-35'',32,''31-35'',33,''31-35'',34,''31-35'',35,''31-35'', '||
            ' 36, ''36-40'',37,''36-40'',38,''36-40'',39,''36-40'',40,''36-40'', '||
            ' 41, ''41-50'',42,''41-50'',43,''41-50'',44,''41-50'',45,''41-50'', '||
            ' 46, ''41-50'',47,''41-50'',48,''41-50'',49,''41-50'',50,''41-50'', '||
            ' 51, ''51-60'',52,''51-60'',53,''51-60'',54,''51-60'',55,''51-60'', '||
            ' 56, ''51-60'',57,''51-60'',58,''51-60'',59,''51-60'',60,''51-60'', '||
            '''61+''), '||
            ' sum(hits) '||
            ' from '||tableNameString||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
            ' where rollup_timestamp > :1 '||
            ' and rollup_timestamp <= :2 '||
            ' and d.target_guid = m.assoc_target_guid '||
            ' and '||columnNameString||' = :3 '||
            ' and d.metric_name = :4 '||
            ' and m.assoc_guid = def.assoc_guid '||
            ' and def.assoc_def_name = ''supports_eum_on''' ||
            ' and def.scope_target_type = '' ''' ||
            ' and ct.target_guid = m.source_target_guid '||
            ' and ct.target_name = :5 '||
            ' and ct.target_type = :6 '||
            ' and d.dist_value_type = :7 '||
            ' group by m.source_target_guid, '||columnNameString||', metric_name, '||
            ' decode(num_seconds,0,''0'',1,''1'',2,''2'',3,''3'',4,''4'',5,''5'',6,''6'',7,''7'', '||
            ' 8,''8'',9,''9'',10,''10'',11,''11'', 12,''12'',13,''13'',14,''14'',15,''15'', '||
            ' 16, ''16-20'',17,''16-20'',18,''16-20'',19,''16-20'',20,''16-20'', '||
            ' 21, ''21-25'',22,''21-25'',23,''21-25'',24,''21-25'',25,''21-25'', '||
            ' 26, ''26-30'',27,''26-30'',28,''26-30'',29,''26-30'',30,''26-30'', '||
            ' 31, ''31-35'',32,''31-35'',33,''31-35'',34,''31-35'',35,''31-35'', '||
            ' 36, ''36-40'',37,''36-40'',38,''36-40'',39,''36-40'',40,''36-40'', '||
            ' 41, ''41-50'',42,''41-50'',43,''41-50'',44,''41-50'',45,''41-50'', '||
            ' 46, ''41-50'',47,''41-50'',48,''41-50'',49,''41-50'',50,''41-50'', '||
            ' 51, ''51-60'',52,''51-60'',53,''51-60'',54,''51-60'',55,''51-60'', '||
            ' 56, ''51-60'',57,''51-60'',58,''51-60'',59,''51-60'',60,''51-60'', '||
            '''61+''),'||
            ' decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, '||
                  ' 11,11,12,12,13,13,14,14,15,15, '||
                  ' 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, '||
                  ' 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, '||
                  ' 36,36,37,36,38,36,39,36,40,36, '||
                  ' 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, '||
                  ' 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, '||
                  ' 61) '||
            ' order by '||
            ' decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, '||
                  ' 11,11,12,12,13,13,14,14,15,15, '||
                  ' 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, '||
                  ' 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, '||
                  ' 36,36,37,36,38,36,39,36,40,36, '||
                  ' 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, '||
                  ' 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, '||
                  ' 61) ';


    END IF;

    OPEN results_out FOR sqlquery USING startTime, endTime, queryName, metric_name_in, 
              target_name_in, target_type_in, dist_type_in;

END;



--
-- getStatsObj - gets stats on the given object
--  
--      If no stats are available, returns object with 'n/a' as values
--      and returns stats_found_ret set to 0
--
--  Inputs:
--    page_type_in
--    metric_name_in
--    target_guid_in
--    obj_type_in
--    query_name_in
--
--  Return:
--    Object containing statistical information about an object:
--          displayName
--          queryName 
--          avgVal
--          hitsVal
--          minVal
--          maxVal
--          stddevVal
--          varianceVal
--          critThreshold
--          warnThreshold
--          currentAvg
--          urlLink
--
--
--  Outputs:
--
--    results_out - cursor containing result set of timeseries query
--
--


PROCEDURE getStatsObj(target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_name_in IN VARCHAR2,
                            obj_type_in IN VARCHAR2,
                            query_name_in IN VARCHAR2,
                            obj_out OUT MGMT_MNTR_USER_STATS_OBJ) IS

    sel_cv        cursorType;
    sqlquery      VARCHAR(5000);

    queryName     VARCHAR2(1024);
    tableName     VARCHAR(2000);
    columnNameString VARCHAR2(40);
    domainTableName VARCHAR2(2000);
    regionTableName VARCHAR2(2000);
    ipTableName VARCHAR2(2000);

    curLink       VARCHAR2(1280);
    curAvg        VARCHAR2(20);
    curServAvg    VARCHAR2(20);
    curDBAvg      VARCHAR2(20);
    curMin        VARCHAR2(20);
    curMax        VARCHAR2(20);
    curStddev     VARCHAR2(20);
    curVariance   VARCHAR2(20);
    curHits       VARCHAR2(20);
    curCrit       VARCHAR2(20);
    curWarn       VARCHAR2(20);
    curMostFavorite VARCHAR(5);

    statsFound      INTEGER;
    targetGuid      RAW(16);
    l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();

    startTime DATE;
    endTime DATE;

BEGIN

    IF ( MGMT_USER.has_priv(l_current_user, MGMT_USER.VIEW_TARGET,target_name_in, target_type_in) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV ) THEN
        RAISE MGMT_GLOBAL.insufficient_privileges;
    END IF;

    queryName := query_name_in;

    -- default values
    --
    curLink := ' ';
    curAvg := '0';
    curServAvg := '0';
    curDBAvg := '0';
    curMin := '0';
    curMax := '0';
    curStddev := '0';
    curVariance := '0';
    curHits := '0';
    curCrit := '0';
    curWarn := '0';
    curMostFavorite := 'false';

    SELECT target_guid
      INTO targetGuid
      FROM MGMT_TARGETS
     WHERE target_name = target_name_in
       AND target_type = target_type_in;

    endTime := MGMT_TARGET.SYSDATE_TARGET(targetGuid);

    IF page_type_in = 'byMonth'
    THEN
        startTime := endTime - 31;
        domainTableName := 'MGMT_RT_DOMAIN_1DAY';
        regionTableName := 'MGMT_RT_DOMAIN_1DAY';
        ipTableName := 'MGMT_RT_IP_1DAY';
    ELSE
        IF page_type_in = 'byWeek'
        THEN
          startTime := endTime - 7;
          domainTableName := 'MGMT_RT_DOMAIN_1DAY';
          regionTableName := 'MGMT_RT_DOMAIN_1DAY';
          ipTableName := 'MGMT_RT_IP_1DAY';
        ELSE
          startTime := endTime - 1;
          domainTableName := 
           '(SELECT b.target_guid, b.metric_name, b.visitor_domain, b.visitor_subnet, '||
            '        b.visitor_subnet_num, b.rollup_timestamp, b.hits, '||
            '        b.response_time_average, b.response_time_minimum, '||
            '        b.response_time_maximum, b.response_time_sdev, b.response_time_variance, '||
            '        b.server_time_average, b.server_time_minimum, b.server_time_maximum, '||
            '        b.server_time_sdev, b.server_time_variance, b.db_time_average, '||
            '        b.db_time_minimum, b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
            'FROM MGMT_RT_DOMAIN_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
            || 'WHERE b.target_guid = mtrt.target_guid ' ||
                 'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
                 'AND mtrt.rollup_table_name = ''MGMT_RT_DOMAIN_1HOUR'' ' ||
            'UNION ' || 
            'SELECT h.target_guid, h.metric_name, h.visitor_domain, h.visitor_subnet, '||
            '       h.visitor_subnet_num, h.rollup_timestamp, h.hits, '||
            '       h.response_time_average, h.response_time_minimum, '||
            '       h.response_time_maximum, h.response_time_sdev, h.response_time_variance, '||
            '       h.server_time_average, h.server_time_minimum, h.server_time_maximum, '||
            '       h.server_time_sdev, h.server_time_variance, h.db_time_average, '||
            '       h.db_time_minimum, h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
            'FROM MGMT_RT_DOMAIN_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
            || 'WHERE h.target_guid = mtrt.target_guid ' ||
                 'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
                 'AND mtrt.rollup_table_name = ''MGMT_RT_DOMAIN_1HOUR'')';
          regionTableName := domainTableName;
          ipTableName := 
            '(SELECT b.target_guid, b.metric_name, b.visitor_node, b.rollup_timestamp, '||
            '        b.hits, b.response_time_average, b.response_time_minimum, '||
            '        b.response_time_maximum, b.response_time_sdev, b.response_time_variance, '||
            '        b.server_time_average, b.server_time_minimum,  b.server_time_maximum, '||
            '        b.server_time_sdev, b.server_time_variance, b.db_time_average, '||
            '        b.db_time_minimum, b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
            'FROM MGMT_RT_IP_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
            || 'WHERE b.target_guid = mtrt.target_guid ' ||
                 'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
                 'AND mtrt.rollup_table_name = ''MGMT_RT_IP_1HOUR'' ' ||
            'UNION ' || 
            'SELECT h.target_guid, h.metric_name, h.visitor_node, h.rollup_timestamp, '||
            '       h.hits, h.response_time_average, h.response_time_minimum, '||
            '       h.response_time_maximum, h.response_time_sdev, h.response_time_variance, '||
            '       h.server_time_average, h.server_time_minimum,  h.server_time_maximum, '||
            '       h.server_time_sdev, h.server_time_variance, h.db_time_average, '||
            '       h.db_time_minimum, h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
            'FROM MGMT_RT_IP_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
            || 'WHERE h.target_guid = mtrt.target_guid ' ||
                 'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
                 'AND mtrt.rollup_table_name = ''MGMT_RT_IP_1HOUR'')';
        END IF;
    END IF;

    IF obj_type_in = 'byUrl'
    THEN
        BEGIN
            select url_filename
                into queryName
                from mgmt_rt_urls u
                where u.target_guid = targetGuid
                and u.display_name = query_name_in;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            queryName := query_name_in;
        WHEN OTHERS THEN
          RAISE;
        END;

        statsFound := getUrlStatsObj(page_type_in, metric_name_in,
            targetGuid, queryName, query_name_in, obj_out);

    ELSE

        IF obj_type_in = 'byDom'
        THEN
            tableName := domainTableName;
            columnNameString := 'VISITOR_DOMAIN';

            sqlquery := 'select '||
                ' round(sum(response_time_average * hits) / SUM(hits)/1000,2), '||
                ' round(sum(server_time_average * hits) / SUM(hits)/1000,2), '||
                ' round(sum(db_time_average * hits) / SUM(hits)/1000,2), '||
                ' round(min(response_time_minimum)/1000,2), '||
                ' round(max(response_time_maximum)/1000,2), '||
              ' decode( '||
              '   sum(hits), '||
            '   0, 0, '||
            '   1, 0, '||
              '   round( sqrt( '||
              '       (sum(hits) * '||
              '       (sum(decode(hits, '||
              '           0, 0, '||
              '           (((response_time_variance * hits *  '||
              '           (hits - 1)) + '||
              '           power((hits * response_time_average), 2))  '||
              '           / hits)))) -  '||
              '       power(sum(response_time_average * hits), 2)) /  '||
              '       (sum(hits) * (sum(hits) - 1)) '||
              '   )/1000, 2 ) '||
              ' ), '||
              ' decode('||
              '   sum(hits),'||
              '   0, 0,'||
              '   1, 0,'||
              '   round( (sum(hits) *'||
              '   (sum(decode(hits,'||
              '       0, 0,'||
              '       (((response_time_variance * hits * '||
              '       (hits - 1)) +'||
              '       power((hits * response_time_average), 2)) '||
              '       / hits)) )) - '||
              '   power(sum(response_time_average * hits), 2)) / '||
              '   (sum(hits) * (sum(hits) - 1))'||
              ' /1000, 2 ) ), '||
                ' sum(hits) '||
                ' from '||tableName||' d, mgmt_target_assocs m,mgmt_target_assoc_defs def '||
                ' where rollup_timestamp > :1 '||
                ' and rollup_timestamp <= :2 '||
                ' and m.source_target_guid = :3 '||
                ' and d.target_guid = m.assoc_target_guid '||
                ' and m.assoc_guid = def.assoc_guid ' ||
                ' and def.assoc_def_name = ''supports_eum_on''' ||
                ' and def.scope_target_type = '' ''' ||
                ' and d.metric_name = :4 '||
                ' and '||columnNameString||' = :5 '||
                ' group by m.source_target_guid, '||columnNameString;

        ELSIF obj_type_in = 'byReg'
        THEN
            columnNameString := 'REGION_NAME';
            tableName := regionTableName;

            sqlquery := 'select '||
                ' round(sum(response_time_average * hits) / SUM(hits)/1000,2), '||
                ' round(sum(server_time_average * hits) / SUM(hits)/1000,2), '||
                ' round(sum(db_time_average * hits) / SUM(hits)/1000,2), '||
                ' round(min(response_time_minimum)/1000,2), '||
                ' round(max(response_time_maximum)/1000,2), '||
              ' decode( '||
              '   sum(hits), '||
            '   0, 0, '||
            '   1, 0, '||
              '   round( sqrt( '||
              '       (sum(hits) * '||
              '       (sum(decode(hits, '||
              '           0, 0, '||
              '           (((response_time_variance * hits *  '||
              '           (hits - 1)) + '||
              '           power((hits * response_time_average), 2))  '||
              '           / hits)))) -  '||
              '       power(sum(response_time_average * hits), 2)) /  '||
              '       (sum(hits) * (sum(hits) - 1)) '||
              '   )/1000, 2 )  '||
              ' ), '||
              ' decode('||
              '   sum(hits),'||
              '   0, 0,'||
              '   1, 0,'||
              '   round( (sum(hits) *'||
              '   (sum(decode(hits,'||
              '       0, 0,'||
              '       (((response_time_variance * hits * '||
              '       (hits - 1)) +'||
              '       power((hits * response_time_average), 2)) '||
              '       / hits)) )) - '||
              '   power(sum(response_time_average * hits), 2)) / '||
              '   (sum(hits) * (sum(hits) - 1))'||
              ' /1000, 2 ) ), '||
                ' sum(hits) '||
                ' from '||tableName||' d, mgmt_target_assocs m, mgmt_target_assoc_defs def,  '||
                ' mgmt_rt_regions r'||
                ' where rollup_timestamp > :1 '||
                ' and rollup_timestamp <= :2 '||
                ' and m.source_target_guid = :3 '||
                ' and d.target_guid = m.assoc_target_guid '||
                ' and m.assoc_guid = def.assoc_guid '||
                ' and def.assoc_def_name = ''supports_eum_on''' ||
                ' and def.scope_target_type = '' ''' ||
                ' 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_subnet_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 r.target_guid = m.source_target_guid '||
                ' and d.metric_name = :4 '||
                ' and '||columnNameString||' = :5 '||
                ' group by m.source_target_guid, '||columnNameString;

        ELSIF obj_type_in = 'byIp'
        THEN
            tableName := ipTableName;
            columnNameString := 'VISITOR_NODE';

            sqlquery := 'select '||
                ' round(sum(response_time_average * hits) / SUM(hits)/1000,2), '||
                ' round(sum(server_time_average * hits) / SUM(hits)/1000,2), '||
                ' round(sum(db_time_average * hits) / SUM(hits)/1000,2), '||
                ' round(min(response_time_minimum)/1000,2), '||
                ' round(max(response_time_maximum)/1000,2), '||
              ' decode( '||
              '   sum(hits), '||
            '   0, 0, '||
            '   1, 0, '||
              '   round( sqrt( '||
              '       (sum(hits) * '||
              '       (sum(decode(hits, '||
              '           0, 0, '||
              '           (((response_time_variance * hits *  '||
              '           (hits - 1)) + '||
              '           power((hits * response_time_average), 2))  '||
              '           / hits)))) -  '||
              '       power(sum(response_time_average * hits), 2)) /  '||
              '       (sum(hits) * (sum(hits) - 1)) '||
              '   )/1000, 2 ) '||
              ' ), '||
              ' decode('||
              '   sum(hits),'||
              '   0, 0,'||
              '   1, 0,'||
              '   round( (sum(hits) *'||
              '   (sum(decode(hits,'||
              '       0, 0,'||
              '       (((response_time_variance * hits * '||
              '       (hits - 1)) +'||
              '       power((hits * response_time_average), 2)) '||
              '       / hits)) )) - '||
              '   power(sum(response_time_average * hits), 2)) / '||
              '   (sum(hits) * (sum(hits) - 1))'||
              ' /1000, 2 ) ), '||
                ' sum(hits) '||
                ' from '||tableName||' d, mgmt_target_assocs m, mgmt_target_assoc_defs def'||
                ' where rollup_timestamp > :1 '||
                ' and rollup_timestamp <= :2 '||
                ' and m.source_target_guid = :3 '||
                ' and m.assoc_guid = def.assoc_guid '||
                ' and def.assoc_def_name = ''supports_eum_on''' ||
                ' and def.scope_target_type = '' ''' ||
                ' and d.target_guid = m.assoc_target_guid '||
                ' and d.metric_name = :4 '||
                ' and '||columnNameString||' = :5 '||
                ' group by m.source_target_guid, '||columnNameString;

        END IF;

        BEGIN
          OPEN sel_cv FOR sqlquery USING startTime, endTime, targetGuid, metric_name_in, queryName;

          FETCH sel_cv INTO curAvg, curServAvg, curDBAvg, curMin, curMax, curStddev, curVariance, curHits;

          CLOSE sel_cv;

          obj_out := MGMT_MNTR_USER_STATS_OBJ(query_name_in, query_name_in, curAvg, curServAvg, curDBAvg,
            curHits, curMin, curMax, curStddev, curVariance,
            curCrit, curWarn, curLink, 'false', null, null );

        EXCEPTION
        WHEN OTHERS THEN
          IF sel_cv%ISOPEN
             THEN
               CLOSE sel_cv;
          END IF;
          RAISE;

        END;

    END IF;

END;


--
-- getHitDetails - gets hit details cursor for a given query_name
--
--  Inputs:
--    target_name_in
--    target_type_in
--    page_type_in
--    metric_name_in
--    obj_type_in
--    sort_order_in (slowFirst, recentFirst)
--    query_name_in
--
--  Outputs:
--
--    results_out - cursor containing result set of query
--
--

PROCEDURE getHitDetails(target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_name_in IN VARCHAR2,
                            obj_type_in IN VARCHAR2,
                            sort_order_in IN VARCHAR2,
                            query_name_in IN VARCHAR2,
                            results_out OUT cursorType) IS

    columnNameString VARCHAR2(40);
    displayNameString VARCHAR2(300);
    
    startTime DATE;
    endTime DATE;
    targetGuid RAW(16);

    sqlquery VARCHAR(3000);
    queryName   VARCHAR2(1024);
    maxElapsedTimeVal INTEGER;
    l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();

BEGIN

    IF ( MGMT_USER.has_priv(l_current_user, MGMT_USER.VIEW_TARGET,target_name_in, target_type_in) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV ) THEN
        RAISE MGMT_GLOBAL.insufficient_privileges;
    END IF;

    queryName := query_name_in;

    SELECT target_guid
      INTO targetGuid
      FROM MGMT_TARGETS
     WHERE target_name = target_name_in
       AND target_type = target_type_in;

    endTime := MGMT_TARGET.SYSDATE_TARGET(targetGuid);

    IF page_type_in = 'byWeek'
    THEN
        startTime := endTime - 7;
    ELSIF page_type_in = 'byMonth'
    THEN
        startTime := endTime - 31;
    ELSE
        startTime := endTime - 1;
    END IF;

    BEGIN
        -- get the default max elapsed time value
        --
        select to_number(nvl(property_value, 0))
            into maxElapsedTimeVal
        from mgmt_rt_target_properties p, mgmt_targets t
        where p.target_guid = t.target_guid
            and t.target_name = target_name_in
            and t.target_type = target_type_in
            and p.property_name = 'mgmt_rt_max_elapsed_time';
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            maxElapsedTimeVal := 0;
        WHEN OTHERS THEN RAISE;
    END;
           
    IF (maxElapsedTimeVal = 0)
    THEN
        BEGIN
          select to_number(nvl(parameter_value, 0))
          into maxElapsedTimeVal
          from mgmt_parameters
          where parameter_name = 'mgmt_rt_max_elapsed_time';
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                maxElapsedTimeVal := 0;
            WHEN OTHERS THEN RAISE;
        END;
    END IF;

    -- default display name subselect, overridden below if a different
    -- display name is desired
    --
    displayNameString := 'nvl((select u.display_name from mgmt_rt_urls u '||
        ' where ct.target_guid = u.target_guid and '||
        ' u.url_filename = d.url_filename), d.url_filename) ';
    
    -- perform the appropriate query
    --
    IF  obj_type_in = 'byReg'
    THEN
        columnNameString := 'REGION_NAME';

        --
        -- Query
        --
        sqlquery := 'select '||displayNameString||', '||
            ' round(d.elapsed_time/1000,2), '||
            ' 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, '||
            ' url_filename, url_base||url_filename '||
            ' from mgmt_rt_metrics_raw d, mgmt_target_assocs m,mgmt_targets ct, mgmt_target_assoc_defs def, '||
            ' mgmt_rt_regions r'||
            ' where collection_timestamp > :1 '||
            ' and collection_timestamp <= :2 '||
            ' and r.target_guid = m.source_target_guid '||
            ' and d.target_guid = m.assoc_target_guid '||
            ' and m.assoc_guid = def.assoc_guid '||
            ' and def.assoc_def_name = ''supports_eum_on''' ||
            ' and def.scope_target_type = '' ''' ||
            ' and ct.target_guid = m.source_target_guid '||
            ' 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))))' ||
            ' and '||columnNameString||' = :3 '||
            ' and d.metric_name = :4 '||
            ' and d.elapsed_time <= :5 '||
            ' and ct.target_name = :6 '||
            ' and ct.target_type = :7 ';


    ELSE
        IF (obj_type_in = 'byUrl')
        THEN
            columnNameString := 'URL_FILENAME'; 
            displayNameString := 'VISITOR_NODE'; 

            BEGIN
                select url_filename 
                    into queryName
                    from mgmt_rt_urls u, mgmt_targets t
                    where t.target_name = target_name_in
                    and t.target_type = target_type_in
                    and t.target_guid = u.target_guid
                    and u.display_name = query_name_in;
            EXCEPTION
            WHEN NO_DATA_FOUND THEN
                queryName := query_name_in;
            WHEN OTHERS THEN
              RAISE;
            END;
        ELSIF obj_type_in = 'byDom'
        THEN
            columnNameString := 'VISITOR_DOMAIN';
        ELSIF obj_type_in = 'byIp'
        THEN
            columnNameString := 'VISITOR_NODE';
        END IF;

        --
        -- Query
        --
        sqlquery := 'select '||displayNameString||', '||
                ' round(d.elapsed_time/1000,2) 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, '||
                ' url_filename, url_base||url_filename '||
                ' from mgmt_rt_metrics_raw d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                ' where collection_timestamp > :1 '||
                ' and collection_timestamp <= :2 '||
                ' and d.target_guid = m.assoc_target_guid '||
                ' and '||columnNameString||' = :3 '||
                ' and d.metric_name = :4 '||
                ' and d.elapsed_time <= :5 '||
                ' and m.assoc_guid = def.assoc_guid '||
                ' and def.assoc_def_name = ''supports_eum_on''' ||
                ' and def.scope_target_type = '' ''' ||
                ' and ct.target_guid = m.source_target_guid '||
                ' and ct.target_name = :6 '||
                ' and ct.target_type = :7 ';

    END IF;

    IF sort_order_in = 'slowFirst'
    THEN
        sqlquery := sqlquery||' order by elapsed_time desc ';
    ELSE 
        -- recentFirst
        sqlquery := sqlquery||' order by collection_timestamp desc ';
    END IF;

    OPEN results_out FOR sqlquery USING startTime, endTime, queryName, metric_name_in, 
        maxElapsedTimeVal, target_name_in, target_type_in;

END;


--
-- getOverallWebfarmPerf - gets overall performance of webservers within a webfarm
--  
--  Inputs:
--    target_name_in
--    target_type_in
--    page_type_in
--    metric_name_in
--
--  Outputs:
--
--    results_out - cursor containing result set of query
--
PROCEDURE getOverallWebfarmPerf(target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            page_type_in IN VARCHAR2,
                            metric_name_in IN VARCHAR2,
                            results_out OUT cursorType) IS

    tableName     VARCHAR(2000);
    sqlquery      VARCHAR(5000);
    l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();

    startTime DATE;
    endTime DATE;
    targetGuid RAW(16);

BEGIN

        IF ( MGMT_USER.has_priv(l_current_user, MGMT_USER.VIEW_TARGET,target_name_in, target_type_in) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV ) THEN
            RAISE MGMT_GLOBAL.insufficient_privileges;
        END IF;

        SELECT target_guid
          INTO targetGuid
          FROM MGMT_TARGETS
         WHERE target_name = target_name_in
           AND target_type = target_type_in;

        endTime := MGMT_TARGET.SYSDATE_TARGET(targetGuid);

        IF page_type_in = 'byMonth'
        THEN
            startTime := endTime - 31;
            tableName := 'MGMT_RT_URL_1DAY';
        ELSE
            IF page_type_in = 'byWeek'
            THEN
              startTime := endTime - 7;
              tableName := 'MGMT_RT_URL_1DAY';
            ELSE
              startTime := endTime - 1;
              TableName := 
                
                '(SELECT b.target_guid, b.metric_name, b.url_filename, b.url_link, '||
                '        b.rollup_timestamp, b.hits, b.response_time_average, '||
                '        b.response_time_minimum, b.response_time_maximum, '||
                '        b.response_time_sdev, b.response_time_variance, '||
                '        b.server_time_average, b.server_time_minimum, '||
                '        b.server_time_maximum, b.server_time_sdev, '||
                '        b.server_time_variance, b.db_time_average, b.db_time_minimum, '||
                '        b.db_time_maximum, b.db_time_sdev, b.db_time_variance '||
                'FROM MGMT_RT_URL_BOOTSTRAP b, MGMT_TARGET_ROLLUP_TIMES mtrt '
                || 'WHERE b.target_guid = mtrt.target_guid ' ||
                     'AND b.rollup_timestamp > mtrt.rollup_timestamp ' ||
                     'AND mtrt.rollup_table_name = ''MGMT_RT_URL_1HOUR'' ' ||
                'UNION ' || 
                'SELECT h.target_guid, h.metric_name, h.url_filename, h.url_link, '||
                '       h.rollup_timestamp, h.hits, h.response_time_average, '||
                '       h.response_time_minimum, h.response_time_maximum, '||
                '       h.response_time_sdev, h.response_time_variance, '||
                '       h.server_time_average, h.server_time_minimum, '||
                '       h.server_time_maximum, h.server_time_sdev, '||
                '       h.server_time_variance, h.db_time_average, h.db_time_minimum, '||
                '       h.db_time_maximum, h.db_time_sdev, h.db_time_variance '||
                'FROM MGMT_RT_URL_1HOUR h, MGMT_TARGET_ROLLUP_TIMES mtrt '
                || 'WHERE h.target_guid = mtrt.target_guid ' ||
                     'AND h.rollup_timestamp <= mtrt.rollup_timestamp ' ||
                     'AND mtrt.rollup_table_name = ''MGMT_RT_URL_1HOUR'')';
            END IF;
        END IF;


    sqlquery := ' select '||
        ' (select target_name from mgmt_targets where target_guid = d.target_guid), '||
        ' (select target_type from mgmt_targets where target_guid = d.target_guid), '||
        ' round(sum(response_time_average*hits)/sum(hits)/1000,2), ' ||
        ' round(sum(server_time_average*hits)/sum(hits)/1000,2), ' ||
        ' round(sum(db_time_average*hits)/sum(hits)/1000,2), ' ||
        ' sum(hits) '||
        ' from '||tableName||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
        ' where rollup_timestamp > :1 '||
        ' and rollup_timestamp <= :2 '||
        ' and d.metric_name = :3 '||
        ' and d.target_guid = m.assoc_target_guid '||
        ' and m.assoc_guid = def.assoc_guid '||
        ' and def.assoc_def_name = ''supports_eum_on''' ||
        ' and def.scope_target_type = '' ''' ||
        ' and ct.target_guid = m.source_target_guid '||
        ' and ct.target_name = :4 '||
        ' and ct.target_type = :5 '||
        ' group by d.target_guid';

    OPEN results_out FOR sqlquery USING startTime, endTime, metric_name_in, 
        target_name_in, target_type_in;


END;

function chronosDataExists(
                            targetName VARCHAR2,
                            targetType VARCHAR2,
                            pageType VARCHAR2,
                            metricName VARCHAR2) return VARCHAR2 IS

--    dataExists VARCHAR2(6);
    chronosExists VARCHAR2(80);

    begin

      begin
        chronosExists := null;
        select m.property_value into chronosExists
           from mgmt_rt_target_properties m
           where m.target_guid = (select target_guid from mgmt_targets t
                                     where t.target_name = targetName
                                         and t.target_type = targetType )
             and m.property_name='CHRONOS_ENABLE_HOMEPAGE';

      EXCEPTION
        WHEN NO_DATA_FOUND THEN
         chronosExists := 'true';
      end;

      -- This condition is to treat invalid property values in
      -- mgmt_target_properties for the property CHRONOS_ENABLE_HOMEPAGE.
      if ( ( chronosExists != 'true' ) and ( chronosExists != 'false' ) )then
        chronosExists := 'true';
      end if;

    return chronosExists;


--    dataExists := 'false';
--
--    for record in (select decode(count(*), 0, 'false', 1, 'true', 'false') "VALUE"
--      from em$rt_url_details_1day dd
--      where dd.COMPOSITE_TARGET_GUID = (select target_guid from mgmt_targets
--          where target_name = targetName and target_type = targetType)
--          and dd.metric_name = metricName
--      and rownum < 2) loop
--
--      dataExists := record.value;
--          exit;
--
--    end loop;
--
--    if (dataExists = 'false') then
--
--      for record in (select decode(count(*), 0, 'false', 1, 'true', 'false') "VALUE"
--        from em$rt_url_details_31day dd
--      where dd.COMPOSITE_TARGET_GUID = (select target_guid from mgmt_targets
--          where target_name = targetName and target_type = targetType)
--          and dd.metric_name = metricName
--        and rownum < 2) loop
--
--      dataExists := record.value;
--          exit;
--      end loop;
--    end if;
--
--    return dataExists;

    end;


--
-- getNumUnprocessedHits - gets number of hits that can be rolled up
--  
--  Inputs:
--    target_name_in
--    target_type_in
--
--  Outputs:
--
--    unprocessed_hits - How many hits are in the database that haven't been 
--                       rolled up by the bootstrap or normal rollup.
--
PROCEDURE getNumUnprocessedHits(target_name_in IN VARCHAR2,
                                target_type_in IN VARCHAR2,
                                unprocessed_hits OUT INTEGER) IS

  CURSOR member_guids (targetName VARCHAR2,
                       targetType VARCHAR2) IS
    SELECT m.assoc_target_guid
    FROM mgmt_target_assocs m, mgmt_targets ct
    WHERE ct.target_name = targetName
      AND ct.target_type = targetType
      AND ct.target_guid = m.source_target_guid
      AND m.assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid;

  v_target_guid            p_target_guid_list_type;
  v_last_rollup            DATE; -- the time of the last rollup
  v_last_bootstrap_rollup  DATE;
  v_cur_hits               INTEGER;

  v_start_time DATE;
  v_end_time DATE;
  v_webapp_guid RAW(16);

BEGIN

  unprocessed_hits := 0;

  OPEN member_guids(target_name_in, target_type_in);

  FETCH member_guids BULK COLLECT INTO v_target_guid;
  --EXIT WHEN NOT v_target_guid.EXISTS(1);

  IF(NOT v_target_guid.EXISTS(1)) THEN
    RETURN;
  END IF;

  SELECT target_guid
    INTO v_webapp_guid
    FROM MGMT_TARGETS
   WHERE target_name = target_name_in
     AND target_type = target_type_in;

  v_end_time := MGMT_TARGET.SYSDATE_TARGET(v_webapp_guid);
  v_start_time := v_end_time - 1;


  -- Loop through the targets returned from the target_cursor.
  FOR i IN v_target_guid.FIRST..v_target_guid.LAST 
  LOOP
    SELECT rollup_timestamp
    INTO v_last_rollup
    FROM MGMT_TARGET_ROLLUP_TIMES
    WHERE target_guid = v_target_guid(i)
      AND rollup_table_name = 'MGMT_RT_URL_1HOUR';

    SELECT rollup_timestamp 
    INTO v_last_bootstrap_rollup
    FROM MGMT_RT_BOOTSTRAP_TIMES
    WHERE target_guid = v_target_guid(i);
      
    BEGIN  
      SELECT COUNT(target_guid)
      INTO v_cur_hits
      FROM MGMT_RT_METRICS_RAW
      WHERE target_guid =  v_target_guid(i)
        AND TRUNC(collection_timestamp, 'HH24') >  v_last_rollup
        AND       collection_timestamp          >  v_last_bootstrap_rollup
        AND collection_timestamp BETWEEN v_start_time AND v_end_time;
    EXCEPTION 
      WHEN NO_DATA_FOUND THEN
        v_cur_hits := 0;
      WHEN OTHERS THEN RAISE;
    END;

    unprocessed_hits := unprocessed_hits + v_cur_hits;

  END LOOP;

  IF member_guids%ISOPEN THEN
    CLOSE member_guids;
  END IF;
  
END getNumUnprocessedHits;



FUNCTION ipStr2Num( ip IN VARCHAR2) RETURN NUMBER IS
  dot1  number;
  dot2  number;
  dot3  number;
  ip_num number;
BEGIN
  dot1 := instr(ip, '.');
  dot2 := instr(ip, '.',  dot1+1);
  dot3 := instr(ip, '.',  dot2+1);
  ip_num := to_number(substr(ip, 1, dot1-1))           * 16777216 + 
            to_number(substr(ip, dot1+1, dot2-dot1-1)) * 65536 +
            to_number(substr(ip, dot2+1, dot3-dot2-1)) * 256 + 
            to_number(substr(ip, dot3+1, length(ip) - dot3));
  RETURN ip_num;

EXCEPTION 
  WHEN OTHERS THEN
    RETURN -1;
END;

FUNCTION ipSubnet2Num( ip IN VARCHAR2) RETURN NUMBER IS
  dot1  number;
  dot2  number;
  dot3  number;
  ip_num number;
BEGIN
  dot1 := instr(ip, '.');
  dot2 := instr(ip, '.',  dot1+1);
  dot3 := instr(ip, '.',  dot2+1);
  IF(dot3 = 0) THEN
    dot3 := LENGTH(ip) + 1;
  END IF;
  ip_num := to_number(substr(ip, 1, dot1-1))           * 16777216 + 
            to_number(substr(ip, dot1+1, dot2-dot1-1)) * 65536 +
            to_number(substr(ip, dot2+1, dot3-dot2-1)) * 256;
  RETURN ip_num;

EXCEPTION 
  WHEN OTHERS THEN
    RETURN -1;
END;

FUNCTION addRegionEntry(region_guid_in IN RAW, 
                        domain_in      IN VARCHAR2, 
                        min_ip_in      IN NUMBER,
                        max_ip_in      IN NUMBER) return INTEGER IS
v_id NUMBER;
v_tmp_id NUMBER;
i  NUMBER;

BEGIN

  IF( (region_guid_in IS NULL) OR (domain_in IS NULL) OR (min_ip_in IS NULL) OR
      (max_ip_in IS NULL)) THEN
    RETURN -1;
  END IF;

  BEGIN
    SELECT id 
      INTO v_id 
      FROM MGMT_RT_REGION_ENTRIES 
      WHERE domain = domain_in;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
        v_id := NULL;
    WHEN OTHERS THEN RAISE;
  END;

  IF(v_id IS NULL) THEN

    IF(min_ip_in >= 0) THEN  -- if the entry is a subnet
      v_id := min_ip_in;
    ELSE
      v_id := 0;
      FOR i IN 1..LENGTH(domain_in)
      LOOP
        -- run a simple hash function on the string
        v_id := v_id + i * ASCII(SUBSTR(domain_in, i, 1));
      END LOOP;

    END IF;

    -- It is very possible that the id that I have so far is not unique because
    -- two MIN_IPs are the same or two strings hash to the same thing
    v_id := v_id - 1;
    v_tmp_id := v_id;
    WHILE (v_tmp_id IS NOT NULL)
    LOOP
      v_id := v_id + 1;
      BEGIN
        SELECT id 
          INTO v_tmp_id
          FROM MGMT_RT_REGION_ENTRIES
          WHERE id = v_id;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          v_tmp_id := NULL;
        WHEN OTHERS THEN RAISE;
      END;
    END LOOP;
      
    INSERT INTO MGMT_RT_REGION_ENTRIES (id, min_ip, max_ip, domain, ref_count) 
      VALUES (v_id, min_ip_in, max_ip_in, domain_in, 1);

  ELSE
    -- the domain already exists in the mapping table, increase reference count
    UPDATE MGMT_RT_REGION_ENTRIES
      SET ref_count = (ref_count + 1)
      WHERE id = v_id;
  END IF;

  -- insert it into the mapping table
  INSERT INTO MGMT_RT_REGION_MAPPING (id, region_guid)
    VALUES(v_id, region_guid_in);
  COMMIT;

  RETURN 0;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RETURN -2;
END addRegionEntry;

PROCEDURE getWebAppType(target_name_in IN VARCHAR2,
                        target_type_in IN VARCHAR2,
                        webapp_type_out OUT VARCHAR2) IS
     
  CURSOR prop_value_cursor IS
              SELECT a.property_value
                FROM mgmt_targets t, mgmt_target_properties a
                WHERE t.target_guid = a.target_guid
                  AND t.target_name = target_name_in
                  AND t.target_type = target_type_in
                  AND a.property_name = 'app_type'
                    AND property_type = 'INSTANCE';
                  
  BEGIN
  
    OPEN prop_value_cursor;
    IF (prop_value_cursor%NOTFOUND)
    THEN
      webapp_type_out := null;
    ELSE
      FETCH prop_value_cursor INTO webapp_type_out;
    END IF;    
    
  END;


PROCEDURE isFormsEUM (target_name_in IN VARCHAR2,
                      target_type_in IN VARCHAR2,
                      is_forms_eum_out OUT INTEGER) IS
  BEGIN

    is_forms_eum_out := 0;

    SELECT count(*)
      INTO is_forms_eum_out
      FROM MGMT_TYPE_PROPERTIES
     WHERE target_type = target_type_in
       AND property_name = EUM_VIEW_TYPE_PROP
       AND property_value = 'forms';

    IF (is_forms_eum_out > 0) THEN
      is_forms_eum_out := 1;
    ELSE
      -- Check special case for website, if the app_type property is for Forms
      SELECT count(tp.target_guid)
        INTO is_forms_eum_out
        FROM MGMT_TARGET_PROPERTIES tp, MGMT_TARGETS t
       WHERE t.target_name = target_name_in
         AND t.target_type = target_type_in
         AND target_type_in = mgmt_global.G_WEBSITE_TARGET_TYPE
         AND t.target_guid = tp.target_guid
         AND tp.property_name = 'app_type'
         AND tp.property_value IN ('FORMS', 'is_form', 'forms');
      
      IF (is_forms_eum_out > 0) THEN
        is_forms_eum_out := 1;
      END IF;

    END IF;
    
  END;


END emd_mntr_user;
/
show errors;