Rem
Rem $Header: chronos_prmapping_pkgbody.sql 11-sep-2006.09:23:16 jureyes Exp $
Rem
Rem chronos_prmapping_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      chronos_prmapping_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    jureyes     09/11/06 - Backport jureyes_bug-5487000 from main
Rem    jureyes     08/10/06 - Backport jureyes_bug5186918 from main
Rem    jureyes     02/17/06 - Backport jureyes_bug-5005456 from main 
Rem    jureyes     01/30/06 - Backport jureyes_bug-4962183 from main 
Rem    yxie        12/12/05 - Backport yxie_bug-4725344 from main 
Rem    jureyes     08/29/06 - Fix bug 5487000 (problem with sql statements with
Rem                           more than 4,000 characters).
Rem    jureyes     07/24/06 - Fix bug 5186918 (enable logging for 10.1.3.1 
Rem                           oc4js) 
Rem    jureyes     02/08/06 - Fix bug 5005456 (disable selection in the table 
Rem                           for 10.1.3 OC4Js). 
Rem    jureyes     01/25/06 - CHange name of function GET_EUMJ2EE_DATA (bug 
Rem                           4962183) 
Rem    yxie        12/06/05 - Fix bug 4725344: convert all sysdate to target timezone
Rem    jureyes     11/29/05 - Fix timing in Sql Queries (not use sysdate). 
Rem    jureyes     07/12/05 - Fix "slowest requests by" in the "Request 
Rem                           Performance" page. 
Rem    yxie        06/27/05 - make 7-day view gets from daily rollup table
Rem    yxie        06/06/05 - changes depends_on to supports_eum_on 
Rem    yxie        05/12/05 - fix security issue 
Rem    jureyes     05/06/05 - Add procedure that fetches multiple sql requests 
Rem                           at a time to reduce trips to database. 
Rem    yxie        04/15/05 - changing contains to depends_on 
Rem    yxie        03/23/05 - reconstruct eum/e2e request url mapping query 
Rem    eporter     03/14/05 - Use PR Mapping bootstrap table 
Rem    jureyes     03/04/05 - 
Rem    eporter     02/24/05 - Use new rollup tables 
Rem    jureyes     02/02/05 - fix EUMJ2EE query 
Rem    yxie        10/11/04 - 
Rem    yxie        09/27/04 - yxie_request_performances
Rem    yxie        09/09/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY EMD_CHRONOS_PRMAPPING AS

-------------------------------------------------------------
-- CHRONOS_GET_PAGE_REQUESTS_DATA
-------------------------------------------------------------
PROCEDURE CHRONOS_GET_PAGE_REQUESTS_DATA(
                          webapp_name_in          IN VARCHAR2,
                          webapp_type_in          IN VARCHAR2,
                          page_url_in             IN VARCHAR2,
                          delta_days_in           IN NUMBER,
                          results_out             OUT p_cursor_type)
IS

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

BEGIN
  
  SELECT target_guid
    INTO websiteGuid
    FROM MGMT_TARGETS
   WHERE target_name = webapp_name_in
     AND target_type = webapp_type_in;

  endTime := MGMT_TARGET.SYSDATE_TARGET(websiteGuid);
  startTime := endTime-delta_days_in;

  IF(delta_days_in < 7) THEN
    OPEN results_out FOR
      SELECT request_url, 
             SUM(num_cache_hits), 
             DECODE(SUM(num_cache_hits),
                    0, 0,
                    SUM(cache_hits_avg_svr_time*num_cache_hits) / 
                        SUM(num_cache_hits)), 
             SUM(num_non_cache_hits), 
             DECODE(SUM(num_non_cache_hits),
                    0, 0,
                    SUM(non_cache_hits_avg_svr_time*num_non_cache_hits) /
                        SUM(num_non_cache_hits))
        FROM em$rt_pr_mapping pr, mgmt_targets mt, 
             mgmt_target_assocs mta
        WHERE mta.assoc_target_guid = pr.target_guid
              AND mta.source_target_guid = mt.target_guid
              AND mt.target_name = webapp_name_in
              AND mt.target_type = webapp_type_in
              AND mta.assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid
              AND pr.page_url = page_url_in
              AND pr.rollup_timestamp > startTime 
              AND pr.rollup_timestamp <= endTime
        GROUP BY request_url
        ORDER BY request_url;
  ELSE
    OPEN results_out FOR
      SELECT request_url, 
             SUM(num_cache_hits), 
             DECODE(SUM(num_cache_hits),
                    0, 0,
                    SUM(cache_hits_avg_svr_time*num_cache_hits) / 
                        SUM(num_cache_hits)), 
             SUM(num_non_cache_hits), 
             DECODE(SUM(num_non_cache_hits),
                    0, 0,
                    SUM(non_cache_hits_avg_svr_time*num_non_cache_hits) /
                        SUM(num_non_cache_hits))
        FROM mgmt_rt_pr_mapping_1day pr, mgmt_targets mt, 
             mgmt_target_assocs mta
        WHERE mta.assoc_target_guid = pr.target_guid
              AND mta.source_target_guid = mt.target_guid
              AND mt.target_name = webapp_name_in
              AND mt.target_type = webapp_type_in
              AND mta.assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid
              AND pr.page_url = page_url_in
              AND pr.rollup_timestamp > startTime
              AND pr.rollup_timestamp <= endTime
        GROUP BY request_url
        ORDER BY request_url;
  END IF;

END CHRONOS_GET_PAGE_REQUESTS_DATA;

-------------------------------------------------------------
-- CHRONOS_GET_REQUESTS_DATA
-------------------------------------------------------------
PROCEDURE CHRONOS_GET_REQUESTS_DATA(
                          webapp_name_in          IN VARCHAR2,
                          webapp_type_in          IN VARCHAR2,
                          request_urls_in         IN SMP_EMD_STRING_ARRAY,
                          delta_days_in           IN NUMBER,
                          results_out             OUT p_cursor_type)
IS
  pr_mapping_table    VARCHAR(64);
  e2e_table           VARCHAR(64);
  prquery             VARCHAR(2000);
  e2equery            VARCHAR(2000);
  mappingquery        VARCHAR(5000);  
  startTime           DATE;
  endTime             DATE;
  websiteGuid         RAW(16);

BEGIN

  IF(delta_days_in < 7) THEN
    pr_mapping_table := 'em$rt_pr_mapping';
  ELSE
    pr_mapping_table := 'mgmt_rt_pr_mapping_1day';
  END IF;

  IF (delta_days_in < 7) THEN
    e2e_table := 'mgmt_e2e_summary_1hour';
  ELSE
    e2e_table := 'mgmt_e2e_summary_1day';
  END IF;

  SELECT target_guid
    INTO websiteGuid
    FROM MGMT_TARGETS
   WHERE target_name = webapp_name_in
     AND target_type = webapp_type_in;

  endTime := MGMT_TARGET.SYSDATE_TARGET(websiteGuid);
  startTime := endTime-delta_days_in;

  prquery :=
    'SELECT request_url, '||
    '       SUM(num_cache_hits) cacheHits, '||
    '       DECODE(SUM(num_cache_hits), 0, 0, '||
    '              SUM(cache_hits_avg_svr_time*num_cache_hits) / SUM(num_cache_hits)) cacheTime, '||
    '       SUM(num_non_cache_hits) noncacheHits, '||
    '       DECODE(SUM(num_non_cache_hits), 0, 0, '||
    '                 SUM(non_cache_hits_avg_svr_time*num_non_cache_hits) / SUM(num_non_cache_hits)) noncacheTime '||
    'FROM '||pr_mapping_table||' pr, mgmt_targets mt, '||
    '        mgmt_target_assocs mta, mgmt_target_assoc_defs mtad '||
    'WHERE request_url IN (SELECT * FROM TABLE(CAST(:c1 AS SMP_EMD_STRING_ARRAY))) '||
    '      AND mta.assoc_target_guid = pr.target_guid '||
    '      AND mta.source_target_guid = mt.target_guid '||
    '      AND mt.target_name = :c2 '||
    '      AND mt.target_type = :c3 '||
    '      AND mta.assoc_guid = mtad.assoc_guid '||
    '      AND mtad.assoc_def_name = ''supports_eum_on'''|| 
    '      AND pr.rollup_timestamp > :c4 '||
    '      AND pr.rollup_timestamp <= :c5 '||
    'GROUP BY request_url';

  e2equery :=
    'SELECT uri, '||
    '       SUM(hit_count) totalHits, '||
    '       SUM(total_time) totalTime, '||
    '       SUM(servlet_time) servletTime, '||
    '       SUM(jsp_time) jspTime, '||
    '       SUM(ejb_time) ejbTime, '||
    '       SUM(jdbc_time) jdbcTime '||
    'FROM '||e2e_table||' ee, mgmt_targets mt2, '||
    '        mgmt_target_assocs mta2, mgmt_target_assoc_defs mtad2 '||
    'WHERE uri IN (SELECT * FROM TABLE(CAST(:c6 AS SMP_EMD_STRING_ARRAY))) '||
    '      AND mta2.assoc_target_guid = ee.target_guid '||
    '      AND mta2.source_target_guid = mt2.target_guid '||
    '      AND mt2.target_name = :c7 '||
    '      AND mt2.target_type = :c8 '||
    '      AND mta2.assoc_guid = mtad2.assoc_guid '||
    '      AND mtad2.assoc_def_name = ''supports_e2e_on'''|| 
    '      AND ee.rollup_timestamp > :c9 '||
    '      AND ee.rollup_timestamp <= :c10 '||
    'GROUP BY uri';

  mappingquery :=
    'SELECT request_url, cacheHits, cacheTime, noncacheHits, nonCacheTime, '||
    '       totalHits, totalTime, servletTime, jspTime, ejbTime, jdbcTime '||
    'FROM '||
    '  ('||prquery||') LEFT OUTER JOIN ('||e2equery||') '||
    'ON request_url = uri '||
    'ORDER BY request_url';

  OPEN results_out FOR mappingquery USING request_urls_in, webapp_name_in, webapp_type_in, startTime, endTime, request_urls_in, webapp_name_in, webapp_type_in, startTime, endTime;

END CHRONOS_GET_REQUESTS_DATA;

-------------------------------------------------------------
-- CHRONOS_GET_EUMJ2EE_DATA
-------------------------------------------------------------

PROCEDURE CHRONOS_GET_REQUEST_PERF_DATA(  p_webcache_guids_in IN SMP_EMD_STRING_ARRAY,
				     p_oc4j_guids_in IN SMP_EMD_STRING_ARRAY,
                                     p_target_name IN VARCHAR2, 
                                     p_target_type IN VARCHAR2,
                                     p_tier IN VARCHAR2,
                                     p_time_ctx IN VARCHAR2,                                     
				     p_min_hits IN NUMBER,
                                     p_max_rows IN NUMBER,
                                     p_results_out OUT p_cursor_type) 
IS

  l_oc4js MGMT_TARGET_GUID_ARRAY;
  l_webcaches MGMT_TARGET_GUID_ARRAY;
  l_min_hits NUMBER;
  l_max_rows NUMBER;
  l_e2e_table_name VARCHAR2(128);
  l_eum_table_name VARCHAR2(128);
  l_order_clause VARCHAR2(512);
  l_filter_col VARCHAR2(128);
  l_inner_stmt VARCHAR2(4000);
  l_stmt VARCHAR2(4000);
  l_num_days NUMBER;
  l_tmpnum NUMBER;
  l_target_date date;

BEGIN

SELECT MGMT_TARGET.SYSDATE_TARGET(mt.target_guid) INTO l_target_date
     FROM MGMT_TARGETS mt 
     WHERE mt.target_name=p_target_name AND mt.target_type=p_target_type;

  -- build target guid arrays
  l_oc4js := MGMT_TARGET_GUID_ARRAY();
  IF (p_oc4j_guids_in IS NOT NULL) AND (p_oc4j_guids_in.COUNT > 1) THEN
    FOR l_tmpnum IN 2..p_oc4j_guids_in.COUNT LOOP
      l_oc4js.EXTEND(1);
      l_oc4js(l_tmpnum-1) := HEXTORAW(p_oc4j_guids_in(l_tmpnum));
    END LOOP;
  END IF;

 -- build target guid arrays
  l_webcaches := MGMT_TARGET_GUID_ARRAY();
  IF (p_webcache_guids_in IS NOT NULL) AND (p_webcache_guids_in.COUNT > 1) THEN
    FOR l_tmpnum IN 2..p_webcache_guids_in.COUNT LOOP
      l_webcaches.EXTEND(1);
      l_webcaches(l_tmpnum-1) := HEXTORAW(p_webcache_guids_in(l_tmpnum));
    END LOOP;
  END IF;

  -- process time ctx
  IF p_time_ctx = 'byWeek' THEN 
    l_num_days := 7;
    l_e2e_table_name :=  'mgmt_e2e_summary_1day';
    l_eum_table_name :=  'mgmt_rt_pr_mapping_1day';
  ELSIF p_time_ctx = 'byMonth' THEN
    l_num_days := 31;
    l_e2e_table_name :=  'mgmt_e2e_summary_1day';
    l_eum_table_name :=  'mgmt_rt_pr_mapping_1day';
  ELSE
    -- default time ctx is byDay
    l_num_days := 1;
    l_e2e_table_name :=  'mgmt_e2e_summary_1hour';   
    l_eum_table_name :=  'em$rt_pr_mapping'; 
  END IF;

  -- process min_hits
  l_min_hits := p_min_hits;
  IF l_min_hits < 0 THEN
    l_min_hits := 0;
  END IF;

  -- process max rows (min is 10)
  l_max_rows := p_max_rows;
  IF l_max_rows < 10 THEN
    l_max_rows := 10;
  END IF;

  -- build the sql statement
  l_inner_stmt := 
                  '((SELECT NVL(e2e.request_url, eum.request_url) as request_url, ' ||
                         'NVL(j2ee_hits, 0) as j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                         'num_cache_hits, NVL(num_non_cache_hits, 0) as num_non_cache_hits, ' ||
                         'noncache_time, ' ||
                         'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                         'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time ' ||        
                    'FROM ( ' ||
                         'SELECT uri as request_url, ' ||
                                'SUM(hit_count) j2ee_hits, ' ||
                                'SUM(total_time) j2ee_total_time, ' ||
                                'SUM(jdbc_time) jdbc_time, ' ||
                                'SUM(servlet_count) servlet_total_count, ' ||
                                'SUM(servlet_time) servlet_total_time, ' ||
                                'SUM(jsp_count) jsp_total_count, ' || 
                                'SUM(jsp_time) jsp_total_time, ' ||
                                'SUM(ejb_count) ejb_total_count, ' ||
                                'SUM(ejb_time) ejb_total_time, ' ||
                                'SUM(servlet_time+jsp_time+ejb_time) j2ee_time '||
                           'FROM ' || l_e2e_table_name || ' ' || 
                          'WHERE target_guid IN ' ||
                                '( SELECT * FROM TABLE(CAST(:c1 AS MGMT_TARGET_GUID_ARRAY)) ) ' ||
                            'AND rollup_timestamp > ( :c2 - :c3 ) ' ||
                            'AND rollup_timestamp <= :c4 ' ||
                          'GROUP BY URI ' ||
                         ') e2e, ' ||
                         '( ' ||
		         'SELECT request_url, SUM(num_cache_hits) num_cache_hits, ' ||
                                'SUM(num_non_cache_hits) num_non_cache_hits, ' ||
                                'SUM(non_cache_hits_avg_svr_time*num_non_cache_hits) noncache_time ' ||
                          'FROM ' || l_eum_table_name || ' ' ||
                          'WHERE target_guid IN ' ||
                                '( SELECT * FROM TABLE(CAST(:c5 AS MGMT_TARGET_GUID_ARRAY)) ) ' ||
		           'AND rollup_timestamp <= :c6 ' ||
			   'AND rollup_timestamp > ( :c7 - :c8 ) ' ||
		          'GROUP BY request_url ' ||
                         ') eum ' ||
                   'WHERE e2e.request_url = eum.request_url(+)) ' ||
                'UNION ' ||
                  '(SELECT NVL(e2e.request_url, eum.request_url) as request_url, ' ||
                         'NVL(j2ee_hits,0) as j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                         'num_cache_hits, NVL(num_non_cache_hits, 0) as num_non_cache_hits, ' ||
                         'noncache_time,  ' ||
                         'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                         'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time ' ||
                    'FROM ( ' ||
                         'SELECT uri as request_url, ' ||
                                'SUM(hit_count) j2ee_hits, ' ||
                                'SUM(total_time) j2ee_total_time, ' ||
                                'SUM(jdbc_time) jdbc_time, ' ||
                                'SUM(servlet_count) servlet_total_count, ' ||
                                'SUM(servlet_time) servlet_total_time, ' ||
                                'SUM(jsp_count) jsp_total_count, ' ||
                                'SUM(jsp_time) jsp_total_time, ' ||
                                'SUM(ejb_count) ejb_total_count, ' ||
                                'SUM(ejb_time) ejb_total_time, ' ||
                                'SUM(servlet_time+jsp_time+ejb_time) j2ee_time '||
                           'FROM ' || l_e2e_table_name || ' ' ||
                          'WHERE target_guid IN ' ||
                                '( SELECT * FROM TABLE(CAST(:c9 AS MGMT_TARGET_GUID_ARRAY)) ) ' ||
                            'AND rollup_timestamp > ( :c10 - :c11 ) ' ||
                            'AND rollup_timestamp <= :c12 ' ||  
                          'GROUP BY URI ' ||
                         ') e2e, ' ||
                         '( ' ||
		         'SELECT request_url, SUM(num_cache_hits) num_cache_hits, ' ||
                                'SUM(num_non_cache_hits) num_non_cache_hits, ' ||
                                'SUM(non_cache_hits_avg_svr_time*num_non_cache_hits) noncache_time ' ||
                           'FROM ' || l_eum_table_name || ' ' ||
                          'WHERE target_guid IN ' ||
                                '( SELECT * FROM TABLE(CAST(:c13 AS MGMT_TARGET_GUID_ARRAY)) ) ' ||
		            'AND rollup_timestamp <= :c14 ' ||
			    'AND rollup_timestamp > ( :c15 - :c16 ) ' ||
		          'GROUP BY request_url ' ||
                         ') eum ' ||
                   'WHERE e2e.request_url(+) = eum.request_url) ) inner_stmt ';

  IF p_tier = 'WEBSERVER' THEN
    l_stmt := 
           'SELECT request_url, j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                  'num_cache_hits, num_non_cache_hits, noncache_time,  ' ||
                  'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                  'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time ' ||
             'FROM ' ||
             '( SELECT request_url, j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                      'num_cache_hits, num_non_cache_hits, noncache_time,  ' ||
                      'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                      'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time, ' ||
                      'DECODE(num_non_cache_hits, 0, 0, noncache_time/num_non_cache_hits) AS order_col ' ||
                 'FROM ' || l_inner_stmt || ' ' ||
                'WHERE num_non_cache_hits >= :c17 ' ||        
                'ORDER BY order_col desc ) ' || 
            'WHERE rownum <= :c18';
  ELSIF p_tier = 'DATABASE' THEN
    l_stmt := 
           'SELECT request_url, j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                  'num_cache_hits, num_non_cache_hits, noncache_time,  ' ||
                  'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                  'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time ' ||
             'FROM ' ||
             '( SELECT request_url, j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                      'num_cache_hits, num_non_cache_hits, noncache_time,  ' ||
                      'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                      'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time, ' ||
                      'DECODE(j2ee_hits, 0, 0, jdbc_time/j2ee_hits) AS order_col ' ||
                 'FROM ' || l_inner_stmt || ' ' ||
                'WHERE j2ee_hits >= :c17 ' ||        
                'ORDER BY order_col desc ) ' || 
            'WHERE rownum <= :c18';
  ELSE
    l_stmt := 
           'SELECT request_url, j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                  'num_cache_hits, num_non_cache_hits, noncache_time,  ' ||
                  'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                  'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time ' ||
             'FROM ' ||
             '( SELECT request_url, j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                      'num_cache_hits, num_non_cache_hits, noncache_time,  ' ||
                      'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                      'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time, ' ||
                      'DECODE(j2ee_hits, 0, 0, (j2ee_total_time-jdbc_time)/j2ee_hits) AS order_col ' ||
                 'FROM ' || l_inner_stmt || ' ' ||
                'WHERE j2ee_hits >= :c17 ' ||        
                'ORDER BY order_col desc ) ' || 
            'WHERE rownum <= :c18';
  END IF; 

  -- open the cursor
  OPEN p_results_out FOR l_stmt USING l_oc4js, l_target_date, l_num_days, l_target_date, l_webcaches, l_target_date, l_target_date, l_num_days, 
                                      l_oc4js, l_target_date, l_num_days, l_target_date, l_webcaches,l_target_date, l_target_date, l_num_days,
                                      l_min_hits, l_max_rows;


END CHRONOS_GET_REQUEST_PERF_DATA;


-- Old Implementation of CHRONOS_GET_EUMJ2EE_DATA
-- This implementation will be obsolete and must be removed
-------------------------------------------------------------
-- CHRONOS_GET_EUMJ2EE_DATA
-------------------------------------------------------------

PROCEDURE CHRONOS_GET_EUMJ2EE_DATA(  p_webcache_guids_in IN SMP_EMD_STRING_ARRAY,
				     p_oc4j_guids_in IN SMP_EMD_STRING_ARRAY,                                                   p_tier IN VARCHAR2,
                                     p_time_ctx IN VARCHAR2,                             			                p_min_hits IN NUMBER,
                                     p_max_rows IN NUMBER,
                                     p_results_out OUT p_cursor_type) 
IS

  l_oc4js MGMT_TARGET_GUID_ARRAY;
  l_webcaches MGMT_TARGET_GUID_ARRAY;
  l_min_hits NUMBER;
  l_max_rows NUMBER;
  l_e2e_table_name VARCHAR2(128);
  l_eum_table_name VARCHAR2(128);
  l_order_clause VARCHAR2(512);
  l_filter_col VARCHAR2(128);
  l_inner_stmt VARCHAR2(4000);
  l_stmt VARCHAR2(4000);
  l_num_days NUMBER;
  l_tmpnum NUMBER;



BEGIN

  -- build target guid arrays
  l_oc4js := MGMT_TARGET_GUID_ARRAY();
  IF (p_oc4j_guids_in IS NOT NULL) AND (p_oc4j_guids_in.COUNT > 1) THEN
    FOR l_tmpnum IN 2..p_oc4j_guids_in.COUNT LOOP
      l_oc4js.EXTEND(1);
      l_oc4js(l_tmpnum-1) := HEXTORAW(p_oc4j_guids_in(l_tmpnum));
    END LOOP;
  END IF;

 -- build target guid arrays
  l_webcaches := MGMT_TARGET_GUID_ARRAY();
  IF (p_webcache_guids_in IS NOT NULL) AND (p_webcache_guids_in.COUNT > 1) THEN
    FOR l_tmpnum IN 2..p_webcache_guids_in.COUNT LOOP
      l_webcaches.EXTEND(1);
      l_webcaches(l_tmpnum-1) := HEXTORAW(p_webcache_guids_in(l_tmpnum));
    END LOOP;
  END IF;

  -- process time ctx
  IF p_time_ctx = 'byWeek' THEN 
    l_num_days := 7;
    l_e2e_table_name :=  'mgmt_e2e_summary_1day';
    l_eum_table_name :=  'mgmt_rt_pr_mapping_1day';
  ELSIF p_time_ctx = 'byMonth' THEN
    l_num_days := 31;
    l_e2e_table_name :=  'mgmt_e2e_summary_1day';
    l_eum_table_name :=  'mgmt_rt_pr_mapping_1day';
  ELSE
    -- default time ctx is byDay
    l_num_days := 1;
    l_e2e_table_name :=  'mgmt_e2e_summary_1hour';   
    l_eum_table_name :=  'em$rt_pr_mapping'; 
  END IF;

  -- process min_hits
  l_min_hits := p_min_hits;
  IF l_min_hits < 0 THEN
    l_min_hits := 0;
  END IF;

  -- process max rows (min is 10)
  l_max_rows := p_max_rows;
  IF l_max_rows < 10 THEN
    l_max_rows := 10;
  END IF;

  -- build the sql statement
  l_inner_stmt := 
                  '((SELECT NVL(e2e.request_url, eum.request_url) as request_url, ' ||
                         'NVL(j2ee_hits, 0) as j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                         'num_cache_hits, NVL(num_non_cache_hits, 0) as num_non_cache_hits, ' ||
                         'noncache_time, ' ||
                         'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                         'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time ' ||        
                    'FROM ( ' ||
                         'SELECT uri as request_url, ' ||
                                'SUM(hit_count) j2ee_hits, ' ||
                                'SUM(total_time) j2ee_total_time, ' ||
                                'SUM(jdbc_time) jdbc_time, ' ||
                                'SUM(servlet_count) servlet_total_count, ' ||
                                'SUM(servlet_time) servlet_total_time, ' ||
                                'SUM(jsp_count) jsp_total_count, ' || 
                                'SUM(jsp_time) jsp_total_time, ' ||
                                'SUM(ejb_count) ejb_total_count, ' ||
                                'SUM(ejb_time) ejb_total_time, ' ||
                                'SUM(servlet_time+jsp_time+ejb_time) j2ee_time '||
                           'FROM ' || l_e2e_table_name || ' ' || 
                          'WHERE target_guid IN ' ||
                                '( SELECT * FROM TABLE(CAST(:c1 AS MGMT_TARGET_GUID_ARRAY)) ) ' ||
                            'AND rollup_timestamp >= ( SYSDATE - :c2 ) ' ||
                          'GROUP BY URI ' ||
                         ') e2e, ' ||
                         '( ' ||
		         'SELECT request_url, SUM(num_cache_hits) num_cache_hits, ' ||
                                'SUM(num_non_cache_hits) num_non_cache_hits, ' ||
                                'SUM(non_cache_hits_avg_svr_time*num_non_cache_hits) noncache_time ' ||
                          'FROM ' || l_eum_table_name || ' ' ||
                          'WHERE target_guid IN ' ||
                                '( SELECT * FROM TABLE(CAST(:c3 AS MGMT_TARGET_GUID_ARRAY)) ) ' ||
		           'AND rollup_timestamp <= SYSDATE ' ||
			   'AND rollup_timestamp >= ( SYSDATE - :c4 ) ' ||
		          'GROUP BY request_url ' ||
                         ') eum ' ||
                   'WHERE e2e.request_url = eum.request_url(+)) ' ||
                'UNION ' ||
                  '(SELECT NVL(e2e.request_url, eum.request_url) as request_url, ' ||
                         'NVL(j2ee_hits,0) as j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                         'num_cache_hits, NVL(num_non_cache_hits, 0) as num_non_cache_hits, ' ||
                         'noncache_time,  ' ||
                         'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                         'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time ' ||
                    'FROM ( ' ||
                         'SELECT uri as request_url, ' ||
                                'SUM(hit_count) j2ee_hits, ' ||
                                'SUM(total_time) j2ee_total_time, ' ||
                                'SUM(jdbc_time) jdbc_time, ' ||
                                'SUM(servlet_count) servlet_total_count, ' ||
                                'SUM(servlet_time) servlet_total_time, ' ||
                                'SUM(jsp_count) jsp_total_count, ' ||
                                'SUM(jsp_time) jsp_total_time, ' ||
                                'SUM(ejb_count) ejb_total_count, ' ||
                                'SUM(ejb_time) ejb_total_time, ' ||
                                'SUM(servlet_time+jsp_time+ejb_time) j2ee_time '||
                           'FROM ' || l_e2e_table_name || ' ' ||
                          'WHERE target_guid IN ' ||
                                '( SELECT * FROM TABLE(CAST(:c5 AS MGMT_TARGET_GUID_ARRAY)) ) ' ||
                            'AND rollup_timestamp >= ( SYSDATE - :c6 ) ' ||
                          'GROUP BY URI ' ||
                         ') e2e, ' ||
                         '( ' ||
		         'SELECT request_url, SUM(num_cache_hits) num_cache_hits, ' ||
                                'SUM(num_non_cache_hits) num_non_cache_hits, ' ||
                                'SUM(non_cache_hits_avg_svr_time*num_non_cache_hits) noncache_time ' ||
                           'FROM ' || l_eum_table_name || ' ' ||
                          'WHERE target_guid IN ' ||
                                '( SELECT * FROM TABLE(CAST(:c7 AS MGMT_TARGET_GUID_ARRAY)) ) ' ||
		            'AND rollup_timestamp <= SYSDATE ' ||
			    'AND rollup_timestamp >= ( SYSDATE - :c8 ) ' ||
		          'GROUP BY request_url ' ||
                         ') eum ' ||
                   'WHERE e2e.request_url(+) = eum.request_url) ) inner_stmt ';

  IF p_tier = 'WEBSERVER' THEN
    l_stmt := 
           'SELECT request_url, j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                  'num_cache_hits, num_non_cache_hits, noncache_time,  ' ||
                  'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                  'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time ' ||
             'FROM ' ||
             '( SELECT request_url, j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                      'num_cache_hits, num_non_cache_hits, noncache_time,  ' ||
                      'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                      'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time, ' ||
                      'DECODE(num_non_cache_hits, 0, 0, noncache_time/num_non_cache_hits) AS order_col ' ||
                 'FROM ' || l_inner_stmt || ' ' ||
                'WHERE num_non_cache_hits >= :c9 ' ||        
                'ORDER BY order_col desc ) ' || 
            'WHERE rownum <= :c10';
  ELSIF p_tier = 'DATABASE' THEN
    l_stmt := 
           'SELECT request_url, j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                  'num_cache_hits, num_non_cache_hits, noncache_time,  ' ||
                  'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                  'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time ' ||
             'FROM ' ||
             '( SELECT request_url, j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                      'num_cache_hits, num_non_cache_hits, noncache_time,  ' ||
                      'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                      'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time, ' ||
                      'DECODE(j2ee_hits, 0, 0, jdbc_time/j2ee_hits) AS order_col ' ||
                 'FROM ' || l_inner_stmt || ' ' ||
                'WHERE j2ee_hits >= :c9 ' ||        
                'ORDER BY order_col desc ) ' || 
            'WHERE rownum <= :c10';
  ELSE
    l_stmt := 
           'SELECT request_url, j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                  'num_cache_hits, num_non_cache_hits, noncache_time,  ' ||
                  'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                  'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time ' ||
             'FROM ' ||
             '( SELECT request_url, j2ee_hits, j2ee_total_time, jdbc_time, ' ||
                      'num_cache_hits, num_non_cache_hits, noncache_time,  ' ||
                      'servlet_total_count, servlet_total_time, jsp_total_count, ' ||
                      'jsp_total_time, ejb_total_count, ejb_total_time, j2ee_time, ' ||
                      'DECODE(j2ee_hits, 0, 0, (j2ee_total_time-jdbc_time)/j2ee_hits) AS order_col ' ||
                 'FROM ' || l_inner_stmt || ' ' ||
                'WHERE j2ee_hits >= :c9 ' ||        
                'ORDER BY order_col desc ) ' || 
            'WHERE rownum <= :c10';
  END IF; 

  -- open the cursor
  OPEN p_results_out FOR l_stmt USING l_oc4js, l_num_days, l_webcaches, l_num_days,
                                      l_oc4js, l_num_days, l_webcaches,l_num_days,
                                      l_min_hits, l_max_rows;


END CHRONOS_GET_EUMJ2EE_DATA;

-------------------------------------------------------------
-- E2E_GET_SQL_TEXT_DATA
-------------------------------------------------------------
PROCEDURE E2E_GET_SQL_TEXT_DATA(sql_guids_in IN SMP_EMD_STRING_ARRAY,
                                    p_results_out OUT p_cursor_type)
IS
  q_stmt VARCHAR2(4000);

BEGIN

  q_stmt:= 
        'SELECT DISTINCT stmt_guid, stmt_text, part_no FROM mgmt_e2e_sql_stmt ' ||
        'WHERE  RAWTOHEX(stmt_guid) IN (SELECT * from TABLE(CAST(:x1 AS SMP_EMD_STRING_ARRAY))) ' ||
        'ORDER BY stmt_guid, part_no';


  OPEN p_results_out FOR q_stmt USING sql_guids_in;


END E2E_GET_SQL_TEXT_DATA;


-------------------------------------------------------------
-- E2E_GET_COMPONENT_VERSIONS
-------------------------------------------------------------
PROCEDURE E2E_GET_COMPONENT_VERSIONS(comp_names_in IN SMP_EMD_STRING_ARRAY,
                                    p_type_in IN VARCHAR2, 
                                    p_property_in IN VARCHAR2,
                                    p_results_out OUT p_cursor_type)
IS

BEGIN

OPEN p_results_out FOR           
SELECT tgt.target_name, mtp.property_value
  FROM mgmt_target_properties mtp,
              (SELECT target_guid, target_name 
                 from mgmt_targets  
                where target_type = p_type_in 
                  and target_name in 
                       (SELECT * FROM TABLE(CAST(comp_names_in AS SMP_EMD_STRING_ARRAY)))
              ) tgt
       where mtp.property_name = p_property_in 
         and mtp.target_guid = tgt.target_guid;


END E2E_GET_COMPONENT_VERSIONS;



END EMD_CHRONOS_PRMAPPING;
/
SHOW ERRORS;