Rem
Rem $Header: ip_webapp_pkgbody.sql 17-jul-2006.02:22:05 pmaddi Exp $
Rem
Rem ip_webapp_pkgbody.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ip_webapp_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    pmaddi      06/08/06 - Fixing the bug for forms based applications 
Rem    pmaddi      07/17/06 - Backport pmaddi_bug-5153991 from main 
Rem    pkmohapa    05/30/06 - Changing the depends_on association to 
Rem                           supports_eum_on for bug 5027443 
Rem    pkmohapa    07/11/06 - Backport pkmohapa_bug-5027443 from main 
Rem    gankrish    07/11/06 - Backport saramasa_bug-5027421 from main 
Rem    saramasa    05/03/06 - Trunc date diff, to fix bug# 5027421 
Rem    saramasa    09/13/05 - Fix for bug# 4570468 columns seq is specified 
Rem    saramasa    09/13/05 - Fix for bug# 4570468 
Rem    saramasa    09/13/05 - Fix for bug# 4570468 columns seq is specified 
Rem    pmaddi      07/29/05 - header removed 
Rem    pmaddi      07/28/05 - Modifying step groups and stepd function to make 
Rem                           them static sql 
Rem    pmaddi      07/22/05 - Modyoing the transaction performance queries to 
Rem                           not use dyanamic sql. 
Rem    pmaddi      07/15/05 - Fixing transaction performance queries issue (bug-4493151)
Rem    saramasa    07/12/05 - sql voilations fix 
Rem    saramasa    07/05/05 - Metric Column Names SQL Injection bug fix 
Rem                           4435525 
Rem    lkoneti     06/30/05 - To add changes for Time Zone 
Rem    lkoneti     06/27/05 - Make changes for Time Zone 
Rem    lkoneti     06/27/05 - After making TimeZone changes 
Rem    lkoneti     06/26/05 - For TimeZone Change 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    ratadiko    05/18/05 - ratadiko_bug-4374591
Rem    saramasa    05/10/05 - Created
Rem

CREATE OR REPLACE PACKAGE BODY ip_webapp AS

 PROCEDURE get_metric_list( target_guid_in IN MGMT_TARGETS.target_guid%TYPE,
                            metric_type_in IN VARCHAR2,
                            metric_cursor_out  OUT CURSORTYPE) IS
 BEGIN
   em_dashboard_service.get_metric_list( target_guid_in, metric_type_in, 
        metric_cursor_out);
 END get_metric_list;
 
PROCEDURE get_metric_thresholds(v_target_guid IN VARCHAR2,
                           v_metric_guid IN VARCHAR2,
                           v_key_value IN VARCHAR2,
                           v_warning_threshold OUT NUMBER,
                           v_critical_threshold OUT NUMBER) IS
l_row_count NUMBER;
BEGIN   

  SELECT COUNT(*)
     INTO l_row_count
     FROM mgmt_metric_thresholds
    WHERE target_guid=v_target_guid
      AND metric_guid=v_metric_guid
      AND key_value=v_key_value;


  IF (l_row_count = 1) THEN

     SELECT warning_threshold, critical_threshold
       INTO v_warning_threshold,  v_critical_threshold
       FROM mgmt_metric_thresholds
      where target_guid=v_target_guid
        AND key_value=v_key_value
        AND metric_guid=v_metric_guid;
   END IF;
END get_metric_thresholds;

PROCEDURE get_metric_data(v_metric_data_cur OUT CURSORTYPE,
                           v_target_guid IN VARCHAR2,
                           v_metric_guid IN VARCHAR2,
                           v_key_value IN VARCHAR2,
                           v_start_date IN DATE,
                           v_end_date IN DATE,
                           v_timezone IN VARCHAR2) IS
   
l_start_date DATE;
l_end_date DATE;
l_diff NUMBER;
l_tgt_timezone MGMT_TARGETS.timezone_region%TYPE;
l_tgt_sysdate DATE;

BEGIN

  SELECT timezone_region
    INTO l_tgt_timezone
    FROM MGMT_TARGETS
   WHERE target_guid=v_target_guid;
 
 l_start_date := MGMT_GLOBAL.ADJUST_TZ(v_start_date,  v_timezone, l_tgt_timezone);

 l_end_date := MGMT_GLOBAL.ADJUST_TZ(v_end_date, v_timezone, l_tgt_timezone);

 l_tgt_sysdate := MGMT_GLOBAL.SYSDATE_TZRGN(l_tgt_timezone);
 l_diff := l_end_date - l_start_date;

 IF (l_diff <= 1) THEN
   OPEN v_metric_data_cur FOR
     SELECT MGMT_GLOBAL.ADJUST_TZ(collection_timestamp,l_tgt_timezone,v_timezone), value
        FROM mgmt_metrics_raw
      WHERE target_guid=v_target_guid
      AND metric_guid=v_metric_guid
      AND key_value=v_key_value
      AND collection_timestamp BETWEEN l_start_date AND l_end_date;

 ELSIF (l_diff <= 15) THEN
   OPEN v_metric_data_cur FOR
   SELECT MGMT_GLOBAL.ADJUST_TZ(rollup_timestamp,l_tgt_timezone,v_timezone), value_average
     from mgmt_metrics_1hour
    WHERE target_guid=v_target_guid
      AND metric_guid=v_metric_guid
      AND key_value=v_key_value
      AND rollup_timestamp BETWEEN l_start_date AND l_end_date;
  ELSE
     OPEN v_metric_data_cur FOR
     SELECT MGMT_GLOBAL.ADJUST_TZ(rollup_timestamp,l_tgt_timezone,v_timezone), value_average
       from mgmt_metrics_1day
      WHERE target_guid=v_target_guid
        AND metric_guid=v_metric_guid
        AND key_value=v_key_value
        AND rollup_timestamp BETWEEN l_start_date AND l_end_date;
  END IF;

 END get_metric_data;
 
 PROCEDURE get_avail_for_cigar_chart (v_target_guid IN RAW,
                                      v_start_date IN VARCHAR2,
                                      v_end_date IN VARCHAR2, 
                                      v_timezone IN VARCHAR2,
                                      v_status_det_cur OUT CURSORTYPE) IS
 BEGIN
   em_dashboard_service.get_avail_for_cigar_chart(v_target_guid,
                                      v_start_date,
                                      v_end_date, 
                                      v_timezone,
                                      v_status_det_cur);
 END get_avail_for_cigar_chart;
 
 PROCEDURE get_avail_for_pie_chart (v_target_guid IN RAW,
                                    v_start_date IN VARCHAR2,
                                    v_end_date IN VARCHAR2,
                                    v_timezone in VARCHAR2,
                                    v_status_dur_cur OUT CURSORTYPE) IS
 BEGIN
   em_dashboard_service.get_avail_for_pie_chart(v_target_guid,
                                    v_start_date,
                                    v_end_date,
                                    v_timezone,
                                    v_status_dur_cur);                                     
 END get_avail_for_pie_chart;

--Purpose of this function is to concat all the steps in a step group for the tranaction.
function concat_steps(stpgrp_name in VARCHAR,
											target_guid_in IN RAW,
											txn_guid_in IN RAW,
											stp_grp_guid_in IN RAW)
											return varchar2 is
	l_str  varchar2(2000) default null;
	l_sep  varchar2(2) default null;
  begin
      for x in ( select steps.name name from mgmt_bcn_step_defn steps,mgmt_bcn_stepgroup_steps stlink
      where 
      stlink.target_guid = target_guid_in
      AND steps.target_guid = stlink.target_guid
      AND stlink.txn_guid = txn_guid_in
      AND stlink.txn_guid = steps.txn_guid
      AND stepgroup_guid = stp_grp_guid_in
      AND steps.step_guid = stlink.step_guid
			AND steps.parent_step_guid is null
      order by steps.name asc
      ) loop
          l_str := l_str || l_sep || x.name;
	  l_sep := ', ';
      end loop;
      return stpgrp_name || ' ('|| l_str || ')';
  END concat_steps;


--This function returns the lowest url report specific metrics .

PROCEDURE get_slowurl_data( slowurl_cursor_out OUT CURSORTYPE ,
			    target_guid_in IN RAW ,
                            start_date_in IN date ,
                            end_date_in IN date ,
                            view_by_type_in IN VARCHAR ,
                            min_hits_in IN VARCHAR ,
                            orderby_in  IN VARCHAR ,
                            custom_date_type_in IN NUMBER ) is

sqlQuery                varchar2( 3000 ) ;
days                    number ;
tableName               varchar2(30) ;
l_target_date           date;

BEGIN    
    days := end_date_in  - start_date_in ;

    days := trunc(days); --To Fix the bug 5027421

    tablenAME := 'MGMT$E2E_Raw' ;

    if( (days>=1) and (days<=7)  ) then
      tableName := 'MGMT$E2E_hourly' ;
    end if ;
    if( days>7 ) then
      tableName := 'MGMT$E2E_1Day' ;
    end if;

      sqlQuery := 'SELECT uri URL , sum(hit_count) HITS , round( sum(total_time)/sum(hit_count) , 2 ) PROCESSING_TIME_PER_HIT , ' ;

    if (('hits' =  view_by_type_in ) or
        ('uri' = view_by_type_in )) then
          sqlQuery := sqlQuery|| ' round(sum(servlet_time)/sum(hit_count),2) SERVLET_TIME,round(sum(jsp_time)/sum(hit_count),2) JSP_TIME,round(sum(ejb_time)/sum(hit_count),2) EJB_TIME,round(sum(jdbc_time)/sum(hit_count),2) JDBC_TIME ' ;
    end if ;
    if ('servlet' =  view_by_type_in ) then
          sqlQuery := sqlQuery|| ' round(sum(servlet_time)/sum(hit_count),2) SERVLET_TIME,round(sum(servlet_time)/sum(hit_count),2) SERVLET_TIME,round(sum(jsp_time)/sum(hit_count),2) JSP_TIME,round(sum(ejb_time)/sum(hit_count),2) EJB_TIME,round(sum(jdbc_time)/sum(hit_count),2) JDBC_TIME ' ;
    end if ;
    if ('jsp' =  view_by_type_in) then
          sqlQuery := sqlQuery|| ' round(sum(jsp_time)/sum(hit_count),2) JSP_TIME,round(sum(servlet_time)/sum(hit_count),2) SERVLET_TIME,round(sum(jsp_time)/sum(hit_count),2) JSP_TIME,round(sum(ejb_time)/sum(hit_count),2) EJB_TIME,round(sum(jdbc_time)/sum(hit_count),2) JDBC_TIME ' ;
    end if ;
    if(  'jdbc' =  view_by_type_in ) then
          sqlQuery := sqlQuery|| ' round(sum(jdbc_time)/sum(hit_count),2) JDBC_TIME,round(sum(servlet_time)/sum(hit_count),2) SERVLET_TIME,round(sum(jsp_time)/sum(hit_count),2) JSP_TIME,round(sum(ejb_time)/sum(hit_count),2) EJB_TIME,round(sum(jdbc_time)/sum(hit_count),2) JDBC_TIME' ;
    end if ;
    if(  'ejb' =  view_by_type_in ) then 
         sqlQuery := sqlQuery|| 'round(sum(ejb_time)/sum(hit_count),2) EJB_TIME,round(sum(servlet_time)/sum(hit_count),2) SERVLET_TIME,round(sum(jsp_time)/sum(hit_count),2) JSP_TIME,round(sum(ejb_time)/sum(hit_count),2) EJB_TIME,round(sum(jdbc_time)/sum(hit_count),2) JDBC_TIME' ;
    end if ;


    sqlQuery := sqlQuery|| ' FROM '||tableName ;
    sqlQuery := sqlQuery|| ' WHERE hit_count> :min_hits_in AND target_guid= :target_guid_in ' ;
    sqlQuery := sqlQuery|| ' AND rollup_timestamp between :start_date_in and :end_date_in ' ;
    sqlQuery := sqlQuery|| ' GROUP BY uri ORDER BY :orderby_in DESC ' ;

                 open   slowurl_cursor_out for
                                sqlQuery
                using min_hits_in,target_guid_in,start_date_in,end_date_in,orderby_in ;

END get_slowurl_data ;

--This function is for
PROCEDURE getRowsByObjAndStat(results_out OUT cursorType ,
                            target_guid_in IN RAW ,
                            start_date_in IN date ,
                            end_date_in IN date ,
                            custom_date_type_in IN NUMBER,
                            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 ) IS

  target_names SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
  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;

  IF (length(trim(target_name_in)) > 0)
  THEN
      target_names.extend;
      target_names(1) := target_name_in;
  END IF;

  getRowsByObjAndStatLoc(results_out, target_guid_in , start_date_in , end_date_in , custom_date_type_in ,target_names, target_type_in, page_type_in, metric_name_in, obj_type_in, stat_type_in, filter_name_in, filter_min_hits_in);

END getRowsByObjAndStat;





--this function is for
PROCEDURE getRowsByObjAndStatLoc( results_out OUT cursorType ,
                            target_guid_in IN RAW ,
                            start_date_in IN date ,
                            end_date_in IN date ,
                            custom_date_type_in IN NUMBER ,
                            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 ) IS



    currIndex INTEGER;
    i INTEGER;

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

    sqlquery VARCHAR(10000);
    inloadquery VARCHAR(4000);
    coloadquery VARCHAR2(4000);

    website_guid RAW(16);

    days       number ;
    l_target_date date;

BEGIN


    currIndex := 1;
    i := 1;


    days := end_date_in  - start_date_in ;

    days := trunc(days); --To Fix the bug 5027421
    
     IF  days>=7 THEN
        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
        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.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.VISITOR_SUBNET_NUM, 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.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.VISITOR_SUBNET_NUM, 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;

    -- 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
        --'select null,null,null,null,null,null,null,null,null,null,null,null,'||minHitsVal ||' from dual; union all'
        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 >=:startDate '||
                    ' and rollup_timestamp < :endDate'  ||
                    ' 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 = :metric_name_in'||                    
                    ' and ct.target_guid = :target_guid_in';

        IF (filter_name_in is not null)
        THEN
            sqlquery := sqlquery||' and '||columnNameString||' like :filter_name_in';
        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 start_date_in, end_date_in, metric_name_in, target_guid_in, filter_name_in;
       ELSE
	  OPEN results_out FOR sqlquery 
	       USING start_date_in, end_date_in, metric_name_in, target_guid_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 >= :startDate'||
                  ' and rollup_timestamp < :endDate'  ||
                    ' 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 = :metric_name_in'|| 
                    ' and ct.target_guid = :target_guid_in';

        IF (filter_name_in is not null)
        THEN
            coloadquery := coloadquery||' and '||columnNameString||' like :filter_name_in';
        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 :startDate AND :endDate '||
            ' AND ct2.target_guid = :target_guid_in';

        inloadquery := inloadquery||' GROUP BY ct2.target_guid, ct2.target_name, il.page_url';
        --'select null,null,null,null,null,null,null,null,null,null,null,null,null,'||minHitsVal ||' from dual union all '
        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), null '||
            '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 ';
             sqlquery := sqlquery||' order by 1 ';
        ELSIF (stat_type_in = 'showPerfTotalTime')
        THEN
             sqlquery := sqlquery||' order by 1 ';
            --sqlquery := sqlquery||' order by nvl(total_time, 0) desc ';
        ELSIF (stat_type_in = 'showServTime')
        THEN
             sqlquery := sqlquery||' order by 1 ';
            --sqlquery := sqlquery||' order by nvl(svr_time, 0) desc ';
        ELSE
             sqlquery := sqlquery||' order by 1 ';
            --sqlquery := sqlquery||' order by nvl(hits, 0) desc';
        END IF;

	IF (filter_name_in is not null) THEN
	   OPEN results_out FOR sqlquery 
		USING start_date_in, end_date_in, metric_name_in, target_guid_in, filter_name_in,
			start_date_in, end_date_in, target_guid_in, start_date_in, end_date_in, 
			metric_name_in, target_guid_in, filter_name_in, start_date_in, end_date_in, 
			target_guid_in;
	ELSE
	   OPEN results_out FOR sqlquery 
		USING start_date_in, end_date_in, metric_name_in, target_guid_in, 
		      start_date_in, end_date_in, target_guid_in, start_date_in, end_date_in, 
		      metric_name_in, target_guid_in, start_date_in, end_date_in, target_guid_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 = :target_guid_in'||
                        ' and d1.target_guid = m1.assoc_target_guid '||
                        ' AND d1.rollup_timestamp >= :startDate'||
            ' and d1.rollup_timestamp < :endDate'  ||
                        ' ) 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 = :target_guid_in'||
                       ') r '||
                    ' where rollup_timestamp >= :startDate'||
          ' and rollup_timestamp < :endDate '||
                    ' 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 d.metric_name = :metric_name_in'||
                    ' and ct.target_guid = :target_guid_in';

            IF (filter_name_in is not null)
            THEN
                sqlquery := sqlquery||' and '||columnNameString||' like :filter_name_in';
            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 >= :startDate'||
                    ' and rollup_timestamp < :endDate' ||
                    ' 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 = :metric_name_in '||
                    ' and ct.target_guid = :target_guid_in ';                    

            IF (filter_name_in is not null)
            THEN
                  sqlquery := sqlquery||' and '||columnNameString||' like :filter_name_in';
            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 ';
            sqlquery := sqlquery||' order by 1 ';
        ELSIF (stat_type_in = 'showPerfTotalTime')
        THEN
            --sqlquery := sqlquery||' order by sum(response_time_average*hits) desc ';
            sqlquery := sqlquery||' order by 1 ';
        ELSIF (stat_type_in = 'showServTime')
        THEN
            --sqlquery := sqlquery||' order by sum(server_time_average*hits)/sum(hits) desc ';
            sqlquery := sqlquery||' order by 1 ';
        ELSE
            --sqlquery := sqlquery||' order by sum(hits) desc';
            sqlquery := sqlquery||' order by 1 ';
        END IF;
        IF obj_type_in = 'byReg'
        THEN
          IF (filter_name_in is not null)
          THEN
            OPEN results_out FOR sqlquery 
		USING target_guid_in, start_date_in, end_date_in, target_guid_in, 
		      start_date_in, end_date_in, metric_name_in, target_guid_in,
		      filter_name_in;
          ELSE
            OPEN results_out FOR sqlquery 
		USING target_guid_in, start_date_in, end_date_in, target_guid_in, 
		      start_date_in, end_date_in, metric_name_in, target_guid_in;
          END IF;
        ELSE
          IF (filter_name_in is not null)
          THEN
            OPEN results_out FOR sqlquery
		USING start_date_in, end_date_in, metric_name_in, target_guid_in, 
		   filter_name_in;
          ELSE
            OPEN results_out FOR sqlquery
		USING start_date_in, end_date_in, metric_name_in, target_guid_in;
	    END IF;
	  END IF;

     END IF;
		  
END getRowsByObjAndStatLoc;
                    
                      
--this function is for 
PROCEDURE getResponseTimeseriesForQuries(results_out OUT cursorType,
                            target_guid_in IN RAW ,
                            start_date_in IN date ,
                            end_date_in IN date ,
                            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 SMP_EMD_LONG_STRING_ARRAY,
                            custom_date_type_in IN NUMBER) IS

    days       number ;
    columnNameString VARCHAR2(40);
    tableNameString VARCHAR(4000);
    urlTableName VARCHAR2(4000);
    domainTableName VARCHAR2(4000);
    regionTableName VARCHAR2(4000);
    ipTableName VARCHAR2(4000);
    l_target_date date;
    sqlquery VARCHAR(3000);
    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;

    days := end_date_in  - start_date_in ;

    days := trunc(days); --To Fix the bug 5027421

    IF  days>7 THEN
         urlTableName := 'MGMT_RT_URL_1DAY';
         domainTableName := 'MGMT_RT_DOMAIN_1DAY';
         regionTableName := 'MGMT_RT_DOMAIN_1DAY';
         ipTableName := 'MGMT_RT_IP_1DAY';
    ELSE
        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.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.VISITOR_SUBNET_NUM, 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.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.VISITOR_SUBNET_NUM, 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) '||
                        --', sum(hits) '||
                        ' from '||urlTableName||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                        ' where rollup_timestamp >= :startDate ' ||
                        'and rollup_timestamp < :endDate ' || 
                            ' and d.target_guid = m.assoc_target_guid '||
                            ' and '||columnNameString||' in (SELECT * FROM TABLE(CAST(:query_name_in AS SMP_EMD_LONG_STRING_ARRAY)))'||
                            ' 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 = :metric_name_in '||                            
                            ' and ct.target_guid = :target_guid_in'||
                            ' group by m.source_target_guid, '||columnNameString||', rollup_timestamp '||
                            ' order by rollup_timestamp ';
                    
			 ELSIF (obj_type_in = 'byFormUrl')
		    THEN
				  columnNameString := 'URL_FILENAME';
				  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) '||
                        --', sum(hits) '||
                        ' from '||urlTableName||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                        ' where rollup_timestamp >= :startDate ' ||
                        'and rollup_timestamp < :endDate ' || 
                            ' and d.target_guid = m.assoc_target_guid '||
                            ' and '||columnNameString||' in (SELECT * FROM TABLE(CAST(:query_name_in AS SMP_EMD_LONG_STRING_ARRAY)))'||
                            ' 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 = :metric_name_in '||                            
                            ' and ct.target_guid = :target_guid_in'||
                            ' 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) '||
                    --', sum(hits) '||
                    ' from '||tableNameString||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                    ' where rollup_timestamp >= :startDate '||
              ' and rollup_timestamp < :endDate '|| 
                        ' 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 = :metric_name_in '||                            
                    ' and ct.target_guid = :target_guid_in'||
                    ' 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) '||
                --', sum(hits) '||
                ' from '||urlTableName||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_targets mt, mgmt_target_assoc_defs def '||
                ' where rollup_timestamp >= :startDate '||
                  ' and rollup_timestamp < :endDate '|| 
                    ' and d.target_guid = m.assoc_target_guid '||
                    ' and '||columnNameString||' in (SELECT * FROM TABLE(CAST(:query_name_in AS SMP_EMD_LONG_STRING_ARRAY)))'||
                    ' 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 = :metric_name_in '||                            
                    ' and ct.target_guid = :target_guid_in'||
                    ' 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) '||
                --', sum(hits) '||
                ' from '||tableNameString||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def, '||
                ' mgmt_rt_regions r'||
                ' where rollup_timestamp >=  :startDate '||
            ' and rollup_timestamp < :endDate '|| 
                ' and r.target_guid = m.source_target_guid '||
                ' and '||columnNameString||' in (SELECT * FROM TABLE(CAST(:query_name_in AS SMP_EMD_LONG_STRING_ARRAY)))'||
                ' 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 = :metric_name_in '||                            
                ' and ct.target_guid = :target_guid_in'||
                ' 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) '||
                --', sum(hits) '||
                ' from '||tableNameString||' d, mgmt_target_assocs m, mgmt_targets ct, mgmt_target_assoc_defs def '||
                ' where rollup_timestamp >= :startDate '||
              ' and rollup_timestamp < :endDate ' || 
                ' and d.target_guid = m.assoc_target_guid '||
                ' and '||columnNameString||' in (SELECT * FROM TABLE(CAST(:query_name_in AS SMP_EMD_LONG_STRING_ARRAY)))'||
                ' and d.metric_name = :metric_name_in '||                            
                ' and ct.target_guid = :target_guid_in'||
                ' 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 '||
                ' 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 start_date_in, end_date_in, metric_name_in, target_guid_in;
    ELSE
        OPEN results_out FOR sqlquery USING start_date_in, end_date_in, query_name_in, metric_name_in, target_guid_in;
    END IF;

END;
    

-- This function is for website watch list

PROCEDURE getWebappWatchListData(watchlist_cursor OUT cursorType,
                                target_guid_in IN RAW,
                                start_date_in IN date,
                                end_date_in IN date,
                                custom_date_type_in IN NUMBER,
                                targetName           IN VARCHAR2,
                                targetType           IN VARCHAR2,              
                                metric_name          IN VARCHAR2
						  ) IS

  v_valid_time      NUMBER := 60 * 60 * 1000;
  v_show_chronos    NUMBER(1) ;
  v_page_type       VARCHAR2(32);
  v_metric_name     VARCHAR2(32);
  v_err_code        NUMBER(3);
  watchlist_data       MGMT_MNTR_USER_STATS_ARRAY;
  result               NUMBER(3);
  err_desc             VARCHAR2(512);
  homepage_url_prop    VARCHAR2(512);

 days       number;
 l_target_date date;
 errorType     number(3);


BEGIN
    errorType := 0;
    days := end_date_in  - start_date_in ;

    days := trunc(days); --To Fix the bug 5027421
  IF( days <= 1 ) THEN
      v_page_type :=  'byDay';
  ELSIF( days >1 and days<=7 )then
       v_page_type :=  'byWeek';
  ELSIF( days > 7 )then
        v_page_type :=  'byMonth';
  end if;

  IF ( ( targetName is null ) OR ( targetType is null ) ) THEN
    result := EMD_BCNTXN.p_bcn_err_badparams;
  END IF;

  v_metric_name := metric_name;


  IF ( v_metric_name is null ) THEN
    v_metric_name := 'latency';
  END IF;   
  BEGIN	   
     EMD_MNTR_USER.GETWATCHlISTSTATS ( targetName, targetType,
                 v_page_type, v_metric_name, v_show_chronos,
                 watchlist_data );
    BEGIN
       SELECT property_value INTO homepage_url_prop
         FROM mgmt_target_properties
         WHERE property_name = 'homepageUrl'
          AND target_guid = ( SELECT target_guid
                                 FROM mgmt_targets
                                 WHERE target_name = targetName
                                   AND target_type = targetType);
          --AND target_guid = target_guid_in;       
     EXCEPTION --
       WHEN NO_DATA_FOUND THEN   
         errorType := 2;
         result := EMD_BCNTXN.p_err_homepage_prop_notfound;      
     END;  
   EXCEPTION 
   WHEN OTHERS THEN  --FIRST LEVEL 
    errorType := 1;--Main error
    result := EMD_BCNTXN.p_bcn_err_oraerr;
    err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50);    
    MGMT_LOG.LOG_ERROR('website_homepage', NULL, 'getWebappWatchListData: db error: ' || 'target_name = '|| targetName || '; target_type = ' ||targetType );
END;
--Get the final results
 IF(errorType <= 1) THEN
   OPEN watchlist_cursor FOR 
    SELECT null,null, null,null,null,null,null,null, homepage_url_prop, result, err_desc FROM dual
      UNION ALL
    SELECT x.queryName,x.displayName,to_number(x.avgVal),to_number(x.servAvgVal),to_number(x.dbAvgVal),to_number(x.hitsVal),to_number(x.incompleteHitsVal),
         to_number(x.incompleteAvgVal),null,null,null FROM TABLE(CAST( watchlist_data AS MGMT_MNTR_USER_STATS_ARRAY)) x, dual;
  ELSE
    OPEN watchlist_cursor FOR 
     SELECT null,null, null,null,null,null,null,null, homepage_url_prop, result, err_desc FROM dual;
  END if;

END getWebappWatchListData;                




--This function returns the url performance report specific metrics .

PROCEDURE get_urlperf_data( urlperf_cursor_out OUT CURSORTYPE ,
			    target_guid_in IN RAW ,
                            start_date_in IN date ,
                            end_date_in IN date ,
                            uri_in IN VARCHAR ,
                            custom_date_type_in IN NUMBER ) is

sqlQuery                varchar2( 3000 ) ;
days                    number ;
tableName               varchar2(30) ;
l_target_date date;
BEGIN    
    days := end_date_in  - start_date_in ;

    days := trunc(days); --To Fix the bug 5027421

    if( days<=1) then
       tableName := 'MGMT$E2E_Raw' ;
    elsif( (days>1) and (days<=7)  ) then
      tableName := 'MGMT$E2E_hourly' ;
    elsif ( days>7 ) then
      tableName := 'MGMT$E2E_1Day' ;
    end if;
sqlQuery := 'SELECT rollup_timestamp, servlet_time SERVLET_TIME, jsp_time JSP_TIME, ejb_time EJB_TIME' ; 
sqlQuery := sqlQuery|| ',jdbc_time JDBC_TIME ,total_time TOTAL_TIME ' ; 
sqlQuery := sqlQuery|| ',hit_count HITS'  ;
sqlQuery := sqlQuery|| ' FROM ' || tableName || ' WHERE target_guid= :target_guid_in' ;
sqlQuery := sqlQuery|| ' AND uri= :uri_in' ;
sqlQuery := sqlQuery|| ' AND rollup_timestamp between :start_date_in and :end_date_in' ;
sqlQuery := sqlQuery|| ' ORDER BY 1 ASC' ;

                 open   urlperf_cursor_out for
                                sqlQuery
                using target_guid_in, uri_in, start_date_in, end_date_in ;

END get_urlperf_data ;


--This function returns the step group metrics .
PROCEDURE get_stepgroup_data(grp_detail_cursor_out OUT CURSORTYPE,
												target_guid_in IN RAW,
												txn_guid_in IN RAW,
												start_date_in IN DATE,
												end_date_in IN DATE,
												custom_date_type_in IN NUMBER,
												test_type_in IN VARCHAR,
												metric_name_in IN VARCHAR,
												metric_columns_in IN SMP_EMD_STRING_ARRAY
												)
IS
    l_target_date DATE;
    days_in NUMBER;
BEGIN
    IF (custom_date_type_in = 1) 
    THEN
        days_in := end_date_in  - start_date_in;
    ELSE
        SELECT mgmt_target.sysdate_target(target_guid_in) INTO l_target_date FROM dual;
        days_in := l_target_date - start_date_in;
    END IF;

    days_in := trunc(days_in); --To Fix the bug 5027421

    IF (days_in<=1) 
    THEN
        -- Open Cursor for less than 1 day from raw table
        OPEN grp_detail_cursor_out FOR
            SELECT grp_name, bcn_name, metric_column, ROUND(AVG(value),2) value
						FROM
							(SELECT metric_column , key_value, value
							FROM
                ( SELECT 
                    metric_column, metric_guid
                  FROM 
                    mgmt_metrics mm, mgmt_targets mt
                  WHERE 
                    metric_name = metric_name_in
                    AND metric_column IN (SELECT * FROM TABLE(CAST(metric_columns_in AS SMP_EMD_STRING_ARRAY)))
                    AND mt.target_guid = target_guid_in  
                    AND mm.target_type = mt.target_type
                    AND mt.type_meta_ver = mm.type_meta_ver
                    AND (mt.category_prop_1 = mm.category_prop_1 OR mm.category_prop_1 = ' ')
                    AND (mt.category_prop_2 = mm.category_prop_2 OR mm.category_prop_2 = ' ')
                    AND (mt.category_prop_3 = mm.category_prop_3 OR mm.category_prop_3 = ' ')
                    AND (mt.category_prop_4 = mm.category_prop_4 OR mm.category_prop_4 = ' ')
                    AND (mt.category_prop_5 = mm.category_prop_5 OR mm.category_prop_5 = ' ')
                ) met,						
								( SELECT key_value, value, metric_guid
										FROM
											mgmt_metrics_raw
										WHERE target_guid = target_guid_in
											AND collection_timestamp  BETWEEN start_date_in AND end_date_in
											AND value IS NOT NULL											
									) data								
									WHERE
									data.metric_guid(+) = met.metric_guid
								)met_data,							
                ( SELECT 
                    ck.composite_key, bcn_name, txn_name, grp_name
                  FROM
                    mgmt_metrics_composite_keys ck, 
                    ( 
										SELECT bcn_name , txn_name, 
											ip_webapp.concat_steps(stgrp.name, txn_bcns.target_guid, 
																txn_bcns.txn_guid, stepgroup_guid) grp_name, stgrp.name key_part3
										FROM
												(SELECT txns.target_guid, bcn_name, txns.name txn_name, txn_guid
												FROM
															(SELECT b.target_guid, mt1.target_name as bcn_name
															FROM 
																mgmt_bcn_target b, mgmt_targets mt1													
															WHERE 
																 b.target_guid = target_guid_in
																AND b.beacon_target_guid = mt1.target_guid) bcns, 
														mgmt_bcn_txn_defn txns
												WHERE 
												bcns.target_guid = txns.target_guid
												AND txns.txn_guid = txn_guid_in
												AND txn_type = test_type_in
												)txn_bcns, 
											mgmt_bcn_stepgroup_defn stgrp
											WHERE 
											stgrp.target_guid = txn_bcns.target_guid
											AND stgrp.txn_guid = txn_bcns.txn_guid											
                    ) txn_grp_bcns
										WHERE
										ck.target_guid = target_guid_in
										AND ck.key_part1_value = txn_grp_bcns.txn_name												
										AND ck.key_part2_value = txn_grp_bcns.bcn_name
										AND ck.key_part3_value = txn_grp_bcns.key_part3
										AND (ck.key_part4_value IS NULL OR ck.key_part4_value = ' ')
										AND (ck.key_part5_value IS NULL OR ck.key_part5_value = ' ')                 
                ) key						
            WHERE
							met_data.key_value(+) = key.composite_key							
	        GROUP BY 
          grp_name, bcn_name, metric_column;

    ELSIF (days_in > 1 AND days_in <= 7) 
    THEN
        -- Open Cursor for less than 1 week from houly table
        OPEN grp_detail_cursor_out FOR 
            SELECT 
                grp_name, bcn_name, metric_column, ROUND(AVG(value),2) value
						FROM
							(SELECT metric_column , key_value, value
							FROM
                ( SELECT 
                    metric_column, metric_guid
                  FROM 
                    mgmt_metrics mm, mgmt_targets mt
                  WHERE 
                    metric_name = metric_name_in
                    AND metric_column IN (SELECT * FROM TABLE(CAST(metric_columns_in AS SMP_EMD_STRING_ARRAY))) 
                    AND mt.target_guid = target_guid_in  
                    AND mm.target_type = mt.target_type
                    AND mt.type_meta_ver = mm.type_meta_ver
                    AND (mt.category_prop_1 = mm.category_prop_1 OR mm.category_prop_1 = ' ')
                    AND (mt.category_prop_2 = mm.category_prop_2 OR mm.category_prop_2 = ' ')
                    AND (mt.category_prop_3 = mm.category_prop_3 OR mm.category_prop_3 = ' ')
                    AND (mt.category_prop_4 = mm.category_prop_4 OR mm.category_prop_4 = ' ')
                    AND (mt.category_prop_5 = mm.category_prop_5 OR mm.category_prop_5 = ' ')
                ) met,						
								( SELECT key_value, value_average value, metric_guid
										FROM
											mgmt_metrics_1hour
										WHERE target_guid = target_guid_in
											AND rollup_timestamp  BETWEEN start_date_in AND end_date_in
											AND value_average IS NOT NULL											
									) data								
									WHERE
									data.metric_guid(+) = met.metric_guid
								)met_data,							
                ( SELECT 
                    ck.composite_key, bcn_name, txn_name, grp_name
                  FROM
                    mgmt_metrics_composite_keys ck, 
                    ( 
										SELECT bcn_name , txn_name, 
											ip_webapp.concat_steps(stgrp.name, txn_bcns.target_guid, 
																txn_bcns.txn_guid, stepgroup_guid) grp_name, stgrp.name key_part3
										FROM
												(SELECT txns.target_guid, bcn_name, txns.name txn_name, txn_guid
												FROM
															(SELECT b.target_guid, mt1.target_name as bcn_name
															FROM 
																mgmt_bcn_target b, mgmt_targets mt1													
															WHERE 
																 b.target_guid = target_guid_in
																AND b.beacon_target_guid = mt1.target_guid) bcns, 
														mgmt_bcn_txn_defn txns
												WHERE 
												bcns.target_guid = txns.target_guid
												AND txns.txn_guid = txn_guid_in
												AND txn_type = test_type_in
												)txn_bcns, 
											mgmt_bcn_stepgroup_defn stgrp
											WHERE 
											stgrp.target_guid = txn_bcns.target_guid
											AND stgrp.txn_guid = txn_bcns.txn_guid											
                    ) txn_grp_bcns
										WHERE
										ck.target_guid = target_guid_in
										AND ck.key_part1_value = txn_grp_bcns.txn_name												
										AND ck.key_part2_value = txn_grp_bcns.bcn_name
										AND ck.key_part3_value = txn_grp_bcns.key_part3
										AND (ck.key_part4_value IS NULL OR ck.key_part4_value = ' ')
										AND (ck.key_part5_value IS NULL OR ck.key_part5_value = ' ')                 
                ) key						
            WHERE
							met_data.key_value(+) = key.composite_key							
	        GROUP BY 
          grp_name, bcn_name, metric_column;

    ELSE
    -- Open Cursor for more than 1 week from daily table
		OPEN grp_detail_cursor_out FOR 
            SELECT 
                grp_name, bcn_name, metric_column, ROUND(AVG(value),2) value
						FROM
							(SELECT metric_column , key_value, value
							FROM
                ( SELECT 
                    metric_column, column_label, column_label_nlsid, metric_guid
                  FROM 
                    mgmt_metrics mm, mgmt_targets mt
                  WHERE 
                    metric_name = metric_name_in
                    AND metric_column IN (SELECT * FROM TABLE(CAST(metric_columns_in AS SMP_EMD_STRING_ARRAY))) 
                    AND mt.target_guid = target_guid_in  
                    AND mm.target_type = mt.target_type
                    AND mt.type_meta_ver = mm.type_meta_ver
                    AND (mt.category_prop_1 = mm.category_prop_1 OR mm.category_prop_1 = ' ')
                    AND (mt.category_prop_2 = mm.category_prop_2 OR mm.category_prop_2 = ' ')
                    AND (mt.category_prop_3 = mm.category_prop_3 OR mm.category_prop_3 = ' ')
                    AND (mt.category_prop_4 = mm.category_prop_4 OR mm.category_prop_4 = ' ')
                    AND (mt.category_prop_5 = mm.category_prop_5 OR mm.category_prop_5 = ' ')
                ) met,						
								( SELECT key_value, value_average value, metric_guid
										FROM
											mgmt_metrics_1day
										WHERE target_guid = target_guid_in
											AND rollup_timestamp  BETWEEN start_date_in AND end_date_in
											AND value_average IS NOT NULL											
									) data								
									WHERE
									data.metric_guid(+) = met.metric_guid
								)met_data,							
                ( SELECT 
                    ck.composite_key, bcn_name, txn_name, grp_name
                  FROM
                    mgmt_metrics_composite_keys ck, 
                    ( 
										SELECT bcn_name , txn_name, 
											ip_webapp.concat_steps(stgrp.name, txn_bcns.target_guid, 
																txn_bcns.txn_guid, stepgroup_guid) grp_name, stgrp.name key_part3
										FROM
												(SELECT txns.target_guid, bcn_name, txns.name txn_name, txn_guid
												FROM
															(SELECT b.target_guid, mt1.target_name as bcn_name
															FROM 
																mgmt_bcn_target b, mgmt_targets mt1													
															WHERE 
																 b.target_guid = target_guid_in
																AND b.beacon_target_guid = mt1.target_guid) bcns, 
														mgmt_bcn_txn_defn txns
												WHERE 
												bcns.target_guid = txns.target_guid
												AND txns.txn_guid = txn_guid_in
												AND txn_type = test_type_in
												)txn_bcns, 
											mgmt_bcn_stepgroup_defn stgrp
											WHERE 
											stgrp.target_guid = txn_bcns.target_guid
											AND stgrp.txn_guid = txn_bcns.txn_guid											
                    ) txn_grp_bcns
										WHERE
										ck.target_guid = target_guid_in
										AND ck.key_part1_value = txn_grp_bcns.txn_name												
										AND ck.key_part2_value = txn_grp_bcns.bcn_name
										AND ck.key_part3_value = txn_grp_bcns.key_part3
										AND (ck.key_part4_value IS NULL OR ck.key_part4_value = ' ')
										AND (ck.key_part5_value IS NULL OR ck.key_part5_value = ' ')                 
                ) key						
            WHERE
							met_data.key_value(+) = key.composite_key							
	        GROUP BY 
          grp_name, bcn_name, metric_column;

    END IF;

END get_stepgroup_data;

--This function returns the step metrics .
PROCEDURE get_step_data(step_detail_cursor_out OUT CURSORTYPE,
												target_guid_in IN RAW,
												txn_guid_in IN RAW,
												start_date_in IN DATE,
												end_date_in IN DATE,
												custom_date_type_in IN NUMBER,
												test_type_in IN VARCHAR,
												metric_name_in IN VARCHAR,
												metric_columns_in IN SMP_EMD_STRING_ARRAY
											)
IS
    l_target_date DATE;
    days_in NUMBER;
BEGIN
    IF (custom_date_type_in = 1) 
    THEN
        days_in := end_date_in  - start_date_in;
    ELSE
        SELECT mgmt_target.sysdate_target(target_guid_in) INTO l_target_date FROM dual;
        days_in := l_target_date - start_date_in;
    END IF;

    days_in := trunc(days_in); --To Fix the bug 5027421

    IF (days_in<=1) 
    THEN
        -- Open Cursor for less than 1 day from raw table
        OPEN step_detail_cursor_out FOR
            SELECT step_name, bcn_name, metric_column, ROUND(AVG(value),2) value
						FROM
							(SELECT metric_column, value, key_value
								FROM
									(SELECT key_value, value, metric_guid
										FROM mgmt_metrics_raw
										WHERE
											target_guid = target_guid_in
											AND collection_timestamp  BETWEEN start_date_in AND end_date_in
											AND value IS NOT NULL                    
									) data,
									( SELECT metric_column, metric_guid
										FROM 
											mgmt_metrics mm, mgmt_targets mt
										WHERE 
											mt.target_guid = target_guid_in  
											AND metric_name = metric_name_in
											AND metric_column IN (SELECT * FROM TABLE(CAST(metric_columns_in AS SMP_EMD_STRING_ARRAY))) 
											AND mm.target_type = mt.target_type
											AND mt.type_meta_ver = mm.type_meta_ver
											AND (mt.category_prop_1 = mm.category_prop_1 OR mm.category_prop_1 = ' ')
											AND (mt.category_prop_2 = mm.category_prop_2 OR mm.category_prop_2 = ' ')
											AND (mt.category_prop_3 = mm.category_prop_3 OR mm.category_prop_3 = ' ')
											AND (mt.category_prop_4 = mm.category_prop_4 OR mm.category_prop_4 = ' ')
											AND (mt.category_prop_5 = mm.category_prop_5 OR mm.category_prop_5 = ' ')
									) met                
									WHERE
									data.metric_guid(+) = met.metric_guid
							)met_data,
               (SELECT ck.composite_key, bcn_name , txn_name, step_name
                  FROM  mgmt_metrics_composite_keys ck, 
                    (SELECT bcn_name , txn_name, steps.name step_name
										FROM (SELECT txns.target_guid, bcn_name, txns.name txn_name, txn_guid
													FROM
															(SELECT b.target_guid, mt1.target_name as bcn_name
															FROM mgmt_bcn_target b, mgmt_targets mt1
															WHERE 
																 b.target_guid = target_guid_in
																AND b.beacon_target_guid = mt1.target_guid) bcns, 
														mgmt_bcn_txn_defn txns
												WHERE 
												bcns.target_guid = txns.target_guid
												AND txns.txn_guid = txn_guid_in
												AND txn_type = test_type_in
												)txn_bcns, 
											mgmt_bcn_step_defn steps
											WHERE 
											steps.target_guid = txn_bcns.target_guid
											AND steps.txn_guid = txn_bcns.txn_guid
											AND steps.parent_step_guid is null
												--AND steps.target_guid = b.target_guid
                    ) txn_stp_bcns
										WHERE
										ck.target_guid = target_guid_in
										AND ck.key_part1_value = txn_stp_bcns.txn_name												
										AND ck.key_part2_value = txn_stp_bcns.bcn_name
										AND ck.key_part3_value = txn_stp_bcns.step_name
										AND (ck.key_part4_value IS NULL OR ck.key_part4_value = ' ')
										AND (ck.key_part5_value IS NULL OR ck.key_part5_value = ' ')
                    --AND steps.target_guid = ck.target_guid
										--AND txns.target_guid = ck.target_guid
								    --AND txns.txn_guid = txn_guid_in
										--AND steps.txn_guid = txns.txn_guid
										--AND steps.target_guid = txns.target_guid
								    --AND txn_type = test_type_in
                ) key
            WHERE
                met_data.key_value(+) = key.composite_key                
	        GROUP BY step_name, bcn_name, metric_column;
           -- ORDER BY 
            --    step_name, bcn_name ASC;

    ELSIF (days_in > 1 AND days_in <= 7) 
    THEN
        -- Open Cursor for less than 1 week from houly table
        OPEN step_detail_cursor_out FOR 
            SELECT step_name, bcn_name, metric_column, ROUND(AVG(value),2) value
						FROM
							(SELECT metric_column, value, key_value
								FROM
									(SELECT key_value, value_average value, metric_guid
										FROM mgmt_metrics_1hour
										WHERE
											target_guid = target_guid_in
											AND rollup_timestamp  BETWEEN start_date_in AND end_date_in
											AND value_average IS NOT NULL                    
									) data,
									( SELECT metric_column, column_label, column_label_nlsid, metric_guid
										FROM 
											mgmt_metrics mm, mgmt_targets mt
										WHERE 
											mt.target_guid = target_guid_in  
											AND metric_name = metric_name_in
											AND metric_column IN (SELECT * FROM TABLE(CAST(metric_columns_in AS SMP_EMD_STRING_ARRAY))) 
											AND mm.target_type = mt.target_type
											AND mt.type_meta_ver = mm.type_meta_ver
											AND (mt.category_prop_1 = mm.category_prop_1 OR mm.category_prop_1 = ' ')
											AND (mt.category_prop_2 = mm.category_prop_2 OR mm.category_prop_2 = ' ')
											AND (mt.category_prop_3 = mm.category_prop_3 OR mm.category_prop_3 = ' ')
											AND (mt.category_prop_4 = mm.category_prop_4 OR mm.category_prop_4 = ' ')
											AND (mt.category_prop_5 = mm.category_prop_5 OR mm.category_prop_5 = ' ')
									) met                
									WHERE
									data.metric_guid(+) = met.metric_guid
							)met_data,
               (SELECT ck.composite_key, bcn_name , txn_name, step_name
                  FROM  mgmt_metrics_composite_keys ck, 
                    (SELECT bcn_name , txn_name, steps.name step_name
										FROM (SELECT txns.target_guid, bcn_name, txns.name txn_name, txn_guid
													FROM
															(SELECT b.target_guid, mt1.target_name as bcn_name
															FROM mgmt_bcn_target b, mgmt_targets mt1
															WHERE 
																 b.target_guid = target_guid_in
																AND b.beacon_target_guid = mt1.target_guid) bcns, 
														mgmt_bcn_txn_defn txns
												WHERE 
												bcns.target_guid = txns.target_guid
												AND txns.txn_guid = txn_guid_in
												AND txn_type = test_type_in
												)txn_bcns, 
											mgmt_bcn_step_defn steps
											WHERE 
											steps.target_guid = txn_bcns.target_guid
											AND steps.txn_guid = txn_bcns.txn_guid
											AND steps.parent_step_guid is null												
                    ) txn_stp_bcns
										WHERE
										ck.target_guid = target_guid_in
										AND ck.key_part1_value = txn_stp_bcns.txn_name												
										AND ck.key_part2_value = txn_stp_bcns.bcn_name
										AND ck.key_part3_value = txn_stp_bcns.step_name
										AND (ck.key_part4_value IS NULL OR ck.key_part4_value = ' ')
										AND (ck.key_part5_value IS NULL OR ck.key_part5_value = ' ')                    
                ) key
            WHERE
                met_data.key_value(+) = key.composite_key                
	        GROUP BY step_name, bcn_name, metric_column;

    ELSE
        -- Open Cursor for more than 1 week from daily table
		OPEN step_detail_cursor_out FOR 
            SELECT step_name, bcn_name, metric_column, ROUND(AVG(value),2) value
						FROM
							(SELECT metric_column, value, key_value
								FROM
									(SELECT key_value, value_average value, metric_guid
										FROM mgmt_metrics_1day
										WHERE
											target_guid = target_guid_in
											AND rollup_timestamp  BETWEEN start_date_in AND end_date_in
											AND value_average IS NOT NULL                    
									) data,
									( SELECT metric_column, metric_guid
										FROM 
											mgmt_metrics mm, mgmt_targets mt
										WHERE 
											mt.target_guid = target_guid_in  
											AND metric_name = metric_name_in
											AND metric_column IN (SELECT * FROM TABLE(CAST(metric_columns_in AS SMP_EMD_STRING_ARRAY))) 
											AND mm.target_type = mt.target_type
											AND mt.type_meta_ver = mm.type_meta_ver
											AND (mt.category_prop_1 = mm.category_prop_1 OR mm.category_prop_1 = ' ')
											AND (mt.category_prop_2 = mm.category_prop_2 OR mm.category_prop_2 = ' ')
											AND (mt.category_prop_3 = mm.category_prop_3 OR mm.category_prop_3 = ' ')
											AND (mt.category_prop_4 = mm.category_prop_4 OR mm.category_prop_4 = ' ')
											AND (mt.category_prop_5 = mm.category_prop_5 OR mm.category_prop_5 = ' ')
									) met                
									WHERE
									data.metric_guid(+) = met.metric_guid
							)met_data,
               (SELECT ck.composite_key, bcn_name , txn_name, step_name
                  FROM  mgmt_metrics_composite_keys ck, 
                    (SELECT bcn_name , txn_name, steps.name step_name
										FROM (SELECT txns.target_guid, bcn_name, txns.name txn_name, txn_guid
													FROM
															(SELECT b.target_guid, mt1.target_name as bcn_name
															FROM mgmt_bcn_target b, mgmt_targets mt1
															WHERE 
																 b.target_guid = target_guid_in
																AND b.beacon_target_guid = mt1.target_guid) bcns, 
														mgmt_bcn_txn_defn txns
												WHERE 
												bcns.target_guid = txns.target_guid
												AND txns.txn_guid = txn_guid_in
												AND txn_type = test_type_in
												)txn_bcns, 
											mgmt_bcn_step_defn steps
											WHERE 
											steps.target_guid = txn_bcns.target_guid
											AND steps.txn_guid = txn_bcns.txn_guid
											AND steps.parent_step_guid is null												
                    ) txn_stp_bcns
										WHERE
										ck.target_guid = target_guid_in
										AND ck.key_part1_value = txn_stp_bcns.txn_name												
										AND ck.key_part2_value = txn_stp_bcns.bcn_name
										AND ck.key_part3_value = txn_stp_bcns.step_name
										AND (ck.key_part4_value IS NULL OR ck.key_part4_value = ' ')
										AND (ck.key_part5_value IS NULL OR ck.key_part5_value = ' ')                    
                ) key
            WHERE
                met_data.key_value(+) = key.composite_key                
	        GROUP BY step_name, bcn_name, metric_column;
    END IF;

END get_step_data;

--This function returns the top/slowest url metrics .
PROCEDURE get_analyze_data_by_url(
	      filter_data_out            OUT CURSORTYPE,
              target_name_in             IN  VARCHAR2,
              target_type_in             IN  VARCHAR2,
              fixed_url_axis_in          IN  VARCHAR2,
              filter_axis_in             IN  VARCHAR2,
              min_coll_time_in           IN  DATE,
              max_coll_time_in           IN  DATE
              )
IS

i PLS_INTEGER;
j PLS_INTEGER;

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

v_min_coll_time DATE;
v_max_coll_time DATE;

url_filename VARCHAR2(1024);

url_filenames VARCHAR(1024);

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

BEGIN

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

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

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


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

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

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

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

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

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

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

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

    WHEN 'domain' THEN

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

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

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

        GROUP BY url_filename, visitor_domain ORDER BY avg_elapsed_time DESC)

      WHERE rownum <= analyze_max_rows;

    WHEN 'visitor' THEN

      OPEN filter_data_out FOR

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

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

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

        GROUP BY url_filename, visitor_node ORDER BY avg_elapsed_time DESC)

       WHERE rownum <= analyze_max_rows;


    WHEN 'region' THEN
      OPEN filter_data_out FOR

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

        FROM mgmt_rt_metrics_raw m, mgmt_rt_regions r

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

        AND

        r.target_guid = website_target_guid

        AND

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

        AND

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

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

    WHEN 'webcache' THEN

      OPEN filter_data_out FOR

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

        FROM mgmt_rt_metrics_raw m, mgmt_targets t WHERE elapsed_time < latency_filter AND

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

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

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

       WHERE rownum <= analyze_max_rows;

  END CASE;

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

end ip_webapp;
/
show errors;