Rem
Rem $Header: sdk_metric_helper_pkgbody.sql 13-may-2008.13:39:48 gan Exp $
Rem
Rem service_helper_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      service_helper_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    gan         05/13/08 - XbranchMerge gan_bug-7010926 from main
Rem    gan         09/07/07 - Backport gan_bug-6336199 from main
Rem    gan         08/07/06 - Backport gan_bug-5076584 from main
Rem    gan         08/02/06 - Backport gan_bug-5412753 from main
Rem    gan         10/03/05 - return SKIP 
Rem    gan         09/13/05 - metric error if no dependency 
Rem    gan         06/27/05 - fix collection loop 
Rem    gan         04/15/05 - num_keys may change across ver 
Rem    gan         03/17/05 - use table matric guid 
Rem    gan         03/14/05 - rewrite data retrieval for interpolation 
Rem    gan         03/10/05 - remove unused avail table 
Rem    gan         03/02/05 - disabled collection 
Rem    gan         01/19/05 - add unique 
Rem    gan         01/10/05 - remove MEDIAN 
Rem    gan         12/29/04 - all key support 
Rem    gan         11/30/04 - use Load_Timestamp as bootstrap 
Rem    gan         11/24/04 - DBTIMEZONE
Rem    gan         11/19/04 - get interval 
Rem    gan         11/12/04 - no data for non-up svc 
Rem    gan         11/11/04 - refactor common code 
Rem    gan         11/04/04 - add stddev 
Rem    gan         11/02/04 - interpolation 
Rem    gan         10/28/04 - code review change 
Rem    gan         10/25/04 - fix return for min_dep_svc_coll 
Rem    gan         10/20/04 - MIN, MAX, AVG 
Rem    streddy     10/07/04 - Undo the workaround in wrapper code 
Rem    streddy     10/04/04 - Check for null dependencies 
Rem    streddy     10/03/04 - Move get_key_by_key_num to internal package 
Rem    gan         09/29/04 - change avail check 
Rem    gan         09/22/04 - use mgmt_svc_coll_data type 
Rem    gan         09/21/04 - metric rollup 
Rem    streddy     08/16/04 - streddy_agr_svc_apis
Rem    gan         08/03/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY MGMT_METRIC_HELPER AS

G_EVAL_INTERPOLATION_DATA CONSTANT NUMBER(1) := 1;
G_EVAL_HISTORICAL_DATA     CONSTANT NUMBER(1) := 2;
G_GMT mgmt_targets.timezone_region%TYPE := 'GMT';
G_MAX_HISTORY_WINDOW_PARAM CONSTANT VARCHAR2(64) 
                  := 'REPO_METRIC_MAX_HISTORY';
G_DEFAULT_WINDOW CONSTANT NUMBER(1) := 6;

P_DB_TIMEZONE VARCHAR(256) := NULL;
P_DEP_COUNT_ALL_KEY constant NUMBER(4) :=9999;

TYPE AVAIL_ARRAY is TABLE OF mgmt_availability.current_status%TYPE;
TYPE DATE_ARRAY is TABLE OF DATE;
TYPE NUMBER_ARRAY IS TABLE OF NUMBER;
TYPE COLL_DATA_CURTYPE is REF CURSOR;
------------Private Procedures---------------------------

FUNCTION get_backward_param
RETURN NUMBER
IS
l_param_value NUMBER;
BEGIN
  BEGIN
  SELECT TO_NUMBER(parameter_value) INTO l_param_value
    FROM mgmt_parameters
   WHERE parameter_name = G_MAX_HISTORY_WINDOW_PARAM;
  EXCEPTION
    WHEN no_data_found THEN
       INSERT INTO mgmt_parameters(parameter_name, parameter_value, parameter_comment,
                                  internal_flag)
        VALUES(G_MAX_HISTORY_WINDOW_PARAM, G_DEFAULT_WINDOW, 
               'Maximum window to calculate, unit is hour', 1);
       l_param_value := G_DEFAULT_WINDOW;
    WHEN others THEN
       l_param_value := G_DEFAULT_WINDOW;
  END;
  return l_param_value;
END get_backward_param;

FUNCTION last_coll_time_utc(p_service_guid IN RAW,
                            p_metric_guid IN RAW,
                            p_key_value IN VARCHAR2)
RETURN DATE
IS
l_last_time DATE;
l_timezone_region mgmt_targets.timezone_region%TYPE;
l_temp_timestamp TIMESTAMP WITH TIME ZONE;
l_temp_hour NUMBER;
l_temp_min NUMBER;
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
     emdw_log.debug('last_coll_time_utc: Enter', G_MODULE);
   END IF;

   SELECT timezone_region INTO l_timezone_region 
   FROM mgmt_targets
   WHERE target_guid = p_service_guid;

   BEGIN
      SELECT collection_timestamp INTO l_last_time
      FROM mgmt_current_metrics
      WHERE target_guid = p_service_guid
        AND metric_guid = p_metric_guid
        AND key_value = p_key_value;
      l_last_time := mgmt_global.to_utc(l_last_time, l_timezone_region);
      IF EMDW_LOG.p_is_debug_set THEN
         emdw_log.debug('last_coll_time_utc: get from mgmt_current_metrics',
                        g_module);
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
        SELECT load_timestamp INTO l_last_time
        FROM mgmt_targets
        WHERE target_guid = p_service_guid;
        -- last_updated_time is repository time
      IF p_db_timezone is null THEN
         l_temp_timestamp := SYSTIMESTAMP;
         l_temp_hour := extract(TIMEZONE_HOUR from l_temp_timestamp);
         l_temp_min := extract(TIMEZONE_MINUTE from l_temp_timestamp);
         p_db_timezone := ''||l_temp_hour||':'||l_temp_min;
      END IF;

      l_last_time := mgmt_global.to_utc(l_last_time, p_db_timezone);
      IF EMDW_LOG.p_is_debug_set THEN
         emdw_log.debug('last_coll_time_utc: get from mgmt_targets, ' ||
                        ' db timezone is ' || p_db_timezone,
                        g_module);
      END IF;
   END;

   IF EMDW_LOG.P_IS_DEBUG_SET 
   THEN
      EMDW_LOG.DEBUG('last_coll_time_utc:For service ' || p_service_guid 
                    || ' is ' 
                    || TO_CHAR(l_last_time, 'YYYY-MM-DD HH24:MI:SS')                              , G_MODULE) ; 
   END IF ;

   IF emdw_log.P_IS_DEBUG_SET THEN
     emdw_log.debug('last_coll_time_utc: Exit', G_MODULE);
   END IF;

   RETURN l_last_time;
  
END last_coll_time_utc;

-- get the mininum of dependent's collection time
-- exclude the dependent which is not up.
FUNCTION min_dep_svc_coll_time_utc(p_service_guid IN RAW,
                               p_metric_guid IN RAW,
                               p_key_value IN VARCHAR2)
RETURN DATE
IS
l_min_time DATE;
l_remote_metric NUMBER;
BEGIN
   IF EMDW_LOG.P_IS_DEBUG_SET 
   THEN
      EMDW_LOG.DEBUG('min_dep_svc_coll_time: Enter', G_MODULE) ; 
   END IF ;
   -- exclude the dep metrics whose collection is disabled.

   SELECT sum(m.remote) INTO l_remote_metric
     FROM mgmt_metrics m, 
          mgmt_metric_dependency_details dd
    WHERE dd.target_guid = p_service_guid
      AND dd.metric_guid = p_metric_guid
      AND dd.key_value = p_key_value
      AND dd.dep_metric_guid = m.metric_guid;
   -- [TODO: if test based promotion is using the right coll name, remove the
   --        if condition ] 
   IF l_remote_metric > 0 THEN
     IF emdw_log.P_IS_DEBUG_SET THEN
        emdw_log.debug('min_dep_svc_coll_time: is remote metric', G_MODULE);
     END IF;

     SELECT min(mgmt_global.to_utc(c.collection_timestamp, t.timezone_region))
       INTO l_min_time
       FROM mgmt_targets t, mgmt_current_metrics c, 
            mgmt_metric_dependency_details d, mgmt_current_availability a
      WHERE d.target_guid = p_service_guid
        AND d.metric_guid = p_metric_guid
        AND d.key_value = p_key_value
        AND d.dep_target_guid = t.target_guid
        AND d.dep_target_guid = c.target_guid
        AND d.dep_metric_guid = c.metric_guid
        AND (d.dep_key_value = c.key_value
             OR d.dep_key_value = MGMT_METRIC_HELPER.G_DEP_ALL_KEY_VALUE)
        AND a.target_guid = d.dep_target_guid
        AND a.current_status = mgmt_global.G_STATUS_UP;
   ELSE
     SELECT min(mgmt_global.to_utc(c.collection_timestamp, t.timezone_region))
       INTO l_min_time
       FROM mgmt_targets t, mgmt_current_metrics c, 
            mgmt_metric_dependency_details d, mgmt_current_availability a,
            mgmt_collections coll
      WHERE d.target_guid = p_service_guid
        AND d.metric_guid = p_metric_guid
        AND d.key_value = p_key_value
        AND d.dep_target_guid = t.target_guid
        AND d.dep_target_guid = c.target_guid
        AND d.dep_metric_guid = c.metric_guid
        AND (d.dep_key_value = c.key_value
             OR d.dep_key_value = MGMT_METRIC_HELPER.G_DEP_ALL_KEY_VALUE)
        AND a.target_guid = d.dep_target_guid
        AND a.current_status = mgmt_global.G_STATUS_UP
        AND d.dep_target_guid = coll.object_guid
        AND coll.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET
        AND d.dep_coll_name = coll.coll_name
        AND coll.is_enabled = 1;
   END IF; 


   IF EMDW_LOG.P_IS_DEBUG_SET 
   THEN
      IF l_min_time IS NOT NULL THEN
        EMDW_LOG.DEBUG('min_dep_svc_coll_time: for service ' || p_service_guid 
                    || ' is ' 
                    || TO_CHAR(l_min_time, 'YYYY-MM-DD HH24:MI:SS')                                 , G_MODULE) ; 
      ELSE
        EMDW_LOG.DEBUG('min_dep_svc_coll_time: for service ' || p_service_guid 
                    || ' is  NULL, all dependends are not UP' 
                    , G_MODULE) ; 

      END IF;
   END IF ;

   IF EMDW_LOG.P_IS_DEBUG_SET 
   THEN
      EMDW_LOG.DEBUG('min_dep_svc_coll_time: Exit', G_MODULE) ; 
   END IF ;

   return l_min_time;

END min_dep_svc_coll_time_utc;

-- repo coll calculation interval in minute
FUNCTION get_repo_coll_interval(p_target_guid IN RAW,
                                p_metric_guid IN RAW,
                                p_coll_name IN VARCHAR2)
RETURN NUMBER
IS
l_interval NUMBER;
BEGIN
  BEGIN
     SELECT min_dep_interval INTO l_interval
       FROM mgmt_metric_dependency
      WHERE target_guid = p_target_guid
        AND metric_guid = p_metric_guid
        AND key_value = p_coll_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      raise_application_error(mgmt_global.INVALID_PARAMS_ERR, 'Can not find  '
             || ' metric dependency. Target ' || p_target_guid || ' Metric ' ||
             p_metric_guid || ' Key value ' || p_coll_name);
  END;

  IF EMDW_LOG.P_IS_DEBUG_SET THEN
    EMDW_LOG.DEBUG('get_repo_coll_interval: for coll ' 
                  || p_coll_name || ' is ' || l_interval, G_MODULE);
  END IF;

  RETURN l_interval;
END get_repo_coll_interval;



-- return dependents count
FUNCTION get_dependents_count(p_target_guid IN RAW,
                              p_metric_guid IN RAW,
                              p_key_value IN VARCHAR2)
RETURN NUMBER
IS
l_dep_count NUMBER;
l_dep_key mgmt_metric_dependency_details.dep_key_value%TYPE;
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
     emdw_log.debug('get_dependens_count: Enter', G_MODULE);
   END IF;

   SELECT count(*) INTO l_dep_count
     FROM mgmt_metric_dependency_details
    WHERE target_guid = p_target_guid
      AND metric_guid = p_metric_guid
      AND key_value = p_key_value;
   
   IF l_dep_count = 1 THEN
      SELECT dep_key_value INTO l_dep_key
        FROM mgmt_metric_dependency_details
       WHERE target_guid = p_target_guid
         AND metric_guid = p_metric_guid
         AND key_value = p_key_value;
      IF l_dep_key = MGMT_METRIC_HELPER.G_DEP_ALL_KEY_VALUE THEN
         l_dep_count := P_DEP_COUNT_ALL_KEY;
      END IF; 
   END IF;
    
   IF l_dep_count = 0 THEN
        raise_application_error(MGMT_GLOBAL.INVALID_METRIC_DEP_ERR, 
                            MGMT_GLOBAL.INVALID_METRIC_DEP_ERR_M ||
                            ' Target ' || p_target_guid || ' metric ' ||
                            p_metric_guid || ' key ' || p_key_value || 
                           ' has 0 dependent, but collection is not disabled');
   END IF;

   IF emdw_log.P_IS_DEBUG_SET THEN
     emdw_log.debug('get_dependens_count: dependents count for target '
                  || p_target_guid || ' metric '
                  || p_metric_guid || ' key value '
                  || p_key_value || ' is ' || l_dep_count, G_MODULE);
   END IF;

   IF emdw_log.P_IS_DEBUG_SET THEN
     emdw_log.debug('get_dependens_count: Exit', G_MODULE);
   END IF;

   RETURN l_dep_count;
END get_dependents_count;


PROCEDURE  check_avail_pack_results(p_target_guid IN RAW,
                                    p_metric_guid IN RAW,
                                    p_coll_name VARCHAR2,
                                    p_svc_key_value IN VARCHAR2,
                                    p_num_keys IN NUMBER, 
                                    p_datas IN  MGMT_SVC_COLL_DATA_ARRAY,
                                    p_values OUT  MGMT_METRIC_VALUE_ARRAY)
AS
l_svc_date DATE;
l_values MGMT_METRIC_VALUE_ARRAY := MGMT_METRIC_VALUE_ARRAY();
l_key_column mgmt_metrics.key_column%TYPE;
l_metric_column mgmt_metrics.metric_column%TYPE;
l_avails AVAIL_ARRAY := AVAIL_ARRAY();
l_datas MGMT_SVC_COLL_DATA_ARRAY := p_datas;
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
     emdw_log.debug('check_avail_pack_results Array: Enter', G_MODULE);
   END IF;

    -- [TODO] The following exception is temp
    -- with multiple key, the p_coll_name is composite key guid,
    -- need to use mgmt_metric.key_order to find all the key column names
    -- and mgmt_target_composite_keys to get all key values
    IF p_num_keys > 1 THEN
      raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
               'Metric with multiple keys are not supported now');
    END IF;


    SELECT unique key_column, metric_column INTO l_key_column, l_metric_column
      FROM mgmt_metrics
     WHERE metric_guid = p_metric_guid;

    SELECT current_status BULK COLLECT INTO l_avails 
      FROM mgmt_availability a, 
           TABLE(CAST(l_datas as MGMT_SVC_COLL_DATA_ARRAY)) d
     WHERE a.target_guid = p_target_guid
       AND a.start_collection_timestamp 
                <= d.collection_timestamp
       AND (a.end_collection_timestamp IS NULL 
           OR a.end_collection_timestamp 
               >  d.collection_timestamp)
     ORDER BY d.collection_timestamp;
    
     IF EMDW_LOG.P_IS_DEBUG_SET THEN
           EMDW_LOG.debug('check_avail_pack_result:get avail at  ' 
                     || l_avails.COUNT || ' time points ',
                      G_MODULE);
     END IF;

     FOR indx IN 1..l_datas.COUNT
     LOOP
       l_svc_date := l_datas(indx).collection_timestamp;

       IF l_datas(indx).value IS NOT NULL THEN
           --l_values.EXTEND;
           IF l_avails(indx) = mgmt_global.G_STATUS_UP THEN
            l_values.EXTEND;
            IF p_num_keys > 0 THEN 
              l_values(l_values.LAST) := MGMT_METRIC_VALUE_OBJ.new(p_target_guid, 
                                     0, NULL, 
                                    MGMT_NAMEVALUE_ARRAY(
                                      mgmt_namevalue_obj.new(l_key_column,
                                                             p_svc_key_value),
                                      mgmt_namevalue_obj.new(l_metric_column,
                                                          l_datas(indx).value)),
                                   l_svc_date);
            ELSE
              l_values(l_values.LAST) := MGMT_METRIC_VALUE_OBJ.new(p_target_guid, 
                                     0, NULL, 
                                    MGMT_NAMEVALUE_ARRAY(
                                      mgmt_namevalue_obj.new(l_metric_column,
                                                          l_datas(indx).value)),
                                   l_svc_date);
            END IF;
            IF EMDW_LOG.P_IS_DEBUG_SET THEN
             EMDW_LOG.DEBUG('check_avail_pack_results:Coll ' || p_coll_name ||
                            ' at time ' ||
                           TO_CHAR(l_svc_date, 'YYYY-MM-DD HH24:MI:SS')
                           || ' with value ' || l_datas(indx).value
                           || ' key ' 
                           || p_svc_key_value,
                           G_MODULE);
            END IF;
          ELSE
           IF EMDW_LOG.P_IS_DEBUG_SET THEN
             EMDW_LOG.DEBUG('check_avail_pack_results:Coll ' || p_coll_name 
                   || ' key ' || p_svc_key_value || ' at time ' || 
                   TO_CHAR(l_svc_date, 'YYYY-MM-DD HH24:MI:SS') 
                   || ' is skipped, the target avail is ' || l_avails(indx),
                   G_MODULE);
           END IF;
          END IF; -- lavail = G_STATUS_UP
      ELSE
           IF EMDW_LOG.P_IS_DEBUG_SET THEN
            EMDW_LOG.DEBUG('check_avail_pack_results:Coll ' || p_coll_name 
                   || ' key ' || p_svc_key_value || ' at time ' || 
                   TO_CHAR(l_svc_date, 'YYYY-MM-DD HH24:MI:SS') 
                   || ' does not have data ',
                   G_MODULE);
           END IF;
      END IF; -- p_data_values(indx) IS NOT NULL
    END LOOP; -- FOR LOOP
 
   IF l_values.COUNT = 0 THEN
      l_values.extend(1);
      l_values(1) := MGMT_METRIC_VALUE_OBJ.new(p_target_guid, 
                                    EM_METRIC_EVAL.G_METRIC_EVAL_SKIP,
                                    'No data', 
                                    NULL,
                                    NULL);
   END IF;
   p_values := l_values;

   IF emdw_log.P_IS_DEBUG_SET THEN
     emdw_log.debug('check_avail_pack_results Array: Enter', G_MODULE);
   END IF;

END check_avail_pack_results;

--check svc avail and pack results, time is adjusted from UTC to 
--svc time region.
PROCEDURE  check_avail_pack_results(p_target_guid IN RAW,
                                    p_metric_guid IN RAW,
                                    p_coll_name VARCHAR2,
                                    p_svc_key_value IN VARCHAR2,
                                    p_num_keys IN NUMBER, 
                                    p_data_cur IN COLL_DATA_CURTYPE,
                                    p_values OUT MGMT_METRIC_VALUE_ARRAY,
                                    p_target_tz 
                                        IN mgmt_targets.timezone_region%TYPE,
                                    p_dep_tz 
                                        IN mgmt_targets.timezone_region%TYPE)
AS
l_datas MGMT_SVC_COLL_DATA_ARRAY;
l_cur_fetch_row NUMBER := 0;
l_values MGMT_METRIC_VALUE_ARRAY := MGMT_METRIC_VALUE_ARRAY();
l_values_ret MGMT_METRIC_VALUE_ARRAY := MGMT_METRIC_VALUE_ARRAY();
l_batch_size NUMBER := 100;
BEGIN
    IF emdw_log.P_IS_DEBUG_SET THEN
     emdw_log.debug('check_avail_pack_results Cursor: Enter', G_MODULE);
    END IF;
    
    LOOP 
      FETCH p_data_cur BULK COLLECT INTO l_datas LIMIT l_batch_size;
      IF l_datas IS NULL OR l_datas.COUNT = 0 THEN
         EXIT;
      END IF;

      IF EMDW_LOG.P_IS_DEBUG_SET THEN
           EMDW_LOG.debug('check_avail_pack_result:Fetched from cursor ' 
                     || p_data_cur%ROWCOUNT || ' rows ',
                      G_MODULE);
      END IF;

      -- convert time from dep target to the target
      FOR indx IN 1..l_datas.COUNT 
      LOOP
         l_datas(indx).collection_timestamp := 
                      CAST(FROM_TZ(CAST( l_datas(indx).collection_timestamp
                                         as TIMESTAMP), 
                                         p_dep_tz) 
                           AT TIME ZONE p_target_tz 
		           AS DATE);

      END LOOP;
      check_avail_pack_results(p_target_guid,
                                    p_metric_guid,
                                    p_coll_name,
                                    p_svc_key_value,
                                    p_num_keys, 
                                    l_datas,
                                    l_values);
      FOR indx IN 1..l_values.COUNT
      LOOP
        IF l_values(indx).status <> EM_METRIC_EVAL.G_METRIC_EVAL_SKIP
        THEN
           l_values_ret.extend;
           l_values_ret(l_values_ret.COUNT) := l_values(indx);
        END IF;
      END LOOP;

      l_datas.DELETE;
   END LOOP; -- CUR LOOP


   IF l_values_ret.COUNT = 0 THEN
      l_values_ret.extend(1);
      l_values_ret(1) := MGMT_METRIC_VALUE_OBJ.new(p_target_guid, 
                                    EM_METRIC_EVAL.G_METRIC_EVAL_SKIP,
                                    'No data', 
                                    NULL,
                                    NULL);
   END IF;

   p_values := l_values_ret;

   IF emdw_log.P_IS_DEBUG_SET THEN
     emdw_log.debug('check_avail_pack_results Cursor: Exit', G_MODULE);
   END IF;

END check_avail_pack_results;

-- if the metric doesn't have key, return ' ', otherwise return p_coll_name
PROCEDURE get_key_by_key_num(p_target_guid IN RAW,
                            p_metric_guid IN RAW,
                            p_coll_name IN VARCHAR2,
                            p_num_keys OUT NUMBER,
                            p_key_value OUT VARCHAR2)
AS
BEGIN
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('get_key_by_key_num: Enter,target ' || p_target_guid
                   || ' metric ' || p_metric_guid
                   || ' coll name ' || p_coll_name
                   , G_MODULE);
  END IF;

  SELECT DISTINCT num_keys INTO p_num_keys
  FROM mgmt_metrics m, mgmt_targets t
 WHERE t.target_guid = p_target_guid
   AND m.metric_guid = p_metric_guid
   AND m.target_type = t.target_type
   AND m.type_meta_ver = t.type_meta_ver
   AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
   AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
   AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
   AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
   AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ');

  IF p_num_keys = 0  THEN
     p_key_value := ' ';
  ELSE
     p_key_value := p_coll_name;
  END IF;

  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('get_key_by_key_num: Exit, '
                   || ' number of key ' || p_num_keys
                   || ' key value ' || p_key_value, G_MODULE);
  END IF;
END get_key_by_key_num;

FUNCTION interpolate_datas(p_first_value IN NUMBER,
                          p_first_time IN DATE,
                          p_second_value IN NUMBER,
                          p_second_time IN DATE,
                          p_coll_time IN DATE)
RETURN NUMBER
AS
l_ret_value NUMBER;
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('interpolate_datas: Enter'
                     , G_MODULE);
   END IF;
    
   l_ret_value := p_first_value + (p_coll_time-p_first_time) 
                    *(p_second_value - p_first_value)/(p_second_time-p_first_time);
   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('interpolate_datas: Exit'
                     , G_MODULE);
   END IF;
   return l_ret_value;
END interpolate_datas;

FUNCTION sum_deps_values(p_values IN mgmt_svc_coll_data_array)
RETURN NUMBER
AS
l_ret_value NUMBER;
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('sum_deps_values: Enter'
                     , G_MODULE);
   END IF;

   SELECT SUM(value) INTO l_ret_value
     FROM TABLE(CAST(p_values as mgmt_svc_coll_data_array));

   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('sum_deps_values: Exit'
                     || ' value is ' || l_ret_value
                     , G_MODULE);
   END IF;

   return l_ret_value;
END sum_deps_values;


FUNCTION stddev_deps_values(p_values IN mgmt_svc_coll_data_array)
RETURN NUMBER
AS
l_ret_value NUMBER;

BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('stddev_deps_values: Enter'
                     , G_MODULE);
   END IF;

   SELECT STDDEV(value) INTO l_ret_value
     FROM TABLE(CAST(p_values as mgmt_svc_coll_data_array));

   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('stddev_deps_values: Exit'
                     || ' value is ' || l_ret_value
                     , G_MODULE);
   END IF;

   return l_ret_value;
END stddev_deps_values;

-- handle single data based on the closeness to coll timestamp
PROCEDURE handle_single_data(p_target_guid IN RAW,
                             p_metric_guid IN RAW,
                             p_key_value IN VARCHAR2,
                             p_data_coll_timestamp IN DATE,
                             p_data_value IN NUMBER,
                             p_repo_coll_interval IN NUMBER,
                             p_coll_time_svc_region IN DATE,
                             p_dep_values IN OUT NOCOPY mgmt_svc_coll_data_array)
AS
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('handle_single_data:Enter'
                     , G_MODULE);
   END IF;

   IF (p_data_coll_timestamp >= p_coll_time_svc_region 
       AND p_data_coll_timestamp <= p_coll_time_svc_region 
                                + p_repo_coll_interval/(2*24*60))
      OR
      (p_data_coll_timestamp <= p_coll_time_svc_region
       AND p_data_coll_timestamp >= p_coll_time_svc_region
                                 - p_repo_coll_interval/(2*24*60))
   THEN
       p_dep_values.extend;
       p_dep_values(p_dep_values.COUNT) := 
                          mgmt_svc_coll_data(NULL, NULL, NULL,
                                             NULL, p_data_value);
      IF emdw_log.p_is_debug_set THEN
           emdw_log.debug('handle_single_data: '
                           || ' for target ' || p_target_guid
                           || ' metric ' || p_metric_guid
                           || ' key value ' || p_key_value
                           || ' the only data value is ' 
                           || p_data_value
                           || ' at service time '
                           || TO_CHAR(p_coll_time_svc_region,
                                      'YYYY-MM-DD HH24:MI:SS'),
                           G_MODULE);
       END IF;
   ELSE
      IF emdw_log.p_is_debug_set THEN
           emdw_log.debug('handle_single_data: '
                           || ' for target ' || p_target_guid
                           || ' metric ' || p_metric_guid
                           || ' key value ' || p_key_value
                           || ' data value ' 
                           || p_data_value
                           || ' at time '
                           || TO_CHAR(p_data_coll_timestamp,
                                      'YYYY-MM-DD HH24:MI:SS')
                           || ' is too far away from collection time '
                           || TO_CHAR(p_coll_time_svc_region,
                                      'YYYY-MM-DD HH24:MI:SS'),
                           G_MODULE);
      END IF;
   END IF;
   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('handle_single_data: Exit'
                     , G_MODULE);
   END IF;

END handle_single_data;

PROCEDURE fetch_and_interpolate_data(p_target_guid IN RAW,
                                    p_metric_guid IN RAW,
                                    p_svc_key_value IN VARCHAR2,
                                    p_coll_timestamp IN DATE,
                                    p_repo_coll_interval IN NUMBER,
                                    p_agg_func IN VARCHAR2,
                                    p_ret_value OUT mgmt_svc_coll_data)
AS
l_timezone_region mgmt_targets.timezone_region%TYPE;
l_coll_time_svc_region DATE;
l_max_ts DATE;
l_min_ts DATE;
l_dep_min_max_datas MGMT_SVC_COLL_DATA_ARRAY;
l_temp_value NUMBER;
l_dep_values mgmt_svc_coll_data_array := mgmt_svc_coll_data_array();
l_svc_ret_value NUMBER;
l_dep_keys mgmt_medium_string_table := mgmt_medium_string_table();
l_dep_coll_time DATE;
l_dep_coll_time_h DATE;
l_dep_coll_time_l DATE;
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('fetch_and_interpolate_data: Enter'
                     || ' target ' || p_target_guid
                     || ' metric ' || p_metric_guid
                     || ' key value ' || p_svc_key_value
                     || ' timestamp (UTC) ' 
                     || TO_CHAR(p_coll_timestamp, 'YYYY-MM-DD HH24:MI:SS')
                     , G_MODULE);
   END IF;

   SELECT timezone_region INTO l_timezone_region
     FROM mgmt_targets
    WHERE target_guid = p_target_guid;
   
   l_coll_time_svc_region := CAST(FROM_TZ(CAST(p_coll_timestamp as TIMESTAMP), 
                                         G_GMT) 
                                  AT TIME ZONE l_timezone_region 
		                  AS DATE);

   FOR rec IN (
        SELECT d.dep_target_guid, d.dep_metric_guid, 
               d.dep_key_value, t.timezone_region 
          FROM mgmt_metric_dependency_details d,
               mgmt_targets t
         WHERE d.target_guid = p_target_guid
           AND d.metric_guid = p_metric_guid
           AND d.key_value = p_svc_key_value
           AND d.dep_target_guid = t.target_guid)
   LOOP
     l_dep_coll_time := CAST(FROM_TZ(CAST(p_coll_timestamp as TIMESTAMP), 
                                          G_GMT) 
                             AT TIME ZONE rec.timezone_region
		        AS DATE);
     l_dep_coll_time_l := CAST(FROM_TZ(CAST(p_coll_timestamp 
                                            -2*p_repo_coll_interval /(24*60)
                                            as TIMESTAMP), 
                                          G_GMT) 
                             AT TIME ZONE rec.timezone_region
		        AS DATE);
     l_dep_coll_time_h := CAST(FROM_TZ(CAST(p_coll_timestamp 
                                            +2*p_repo_coll_interval /(24*60)
                                            as TIMESTAMP), 
                                          G_GMT) 
                             AT TIME ZONE rec.timezone_region
		        AS DATE);

     IF rec.dep_key_value != MGMT_METRIC_HELPER.G_DEP_ALL_KEY_VALUE THEN
        l_dep_keys.extend(1);
        l_dep_keys(1) := rec.dep_key_value;
     ELSE
        select unique key_value 
          bulk collect into l_dep_keys
          from mgmt_current_metrics
         where target_guid = rec.dep_target_guid
           and metric_guid = rec.dep_metric_guid;
     END IF;

     FOR indx IN 1..l_dep_keys.COUNT
     LOOP
       SELECT MAX(COLLECTION_TIMESTAMP) 
         INTO l_max_ts
         FROM MGMT_METRICS_RAW R2 
        WHERE r2.target_guid = rec.dep_target_guid
          AND r2.metric_guid = rec.dep_metric_guid
          AND r2.key_value   = l_dep_keys(indx)
          AND r2.collection_timestamp
              <= l_dep_coll_time
          AND r2.collection_timestamp  
              >= l_dep_coll_time_l;

       SELECT MIN(COLLECTION_TIMESTAMP) 
         INTO l_min_ts
         FROM MGMT_METRICS_RAW R2 
        WHERE r2.target_guid = rec.dep_target_guid
          AND r2.metric_guid = rec.dep_metric_guid
          AND r2.key_value   = l_dep_keys(indx)
          AND r2.collection_timestamp
              >= l_dep_coll_time 
          AND r2.collection_timestamp  
              <= l_dep_coll_time_h;
     
       select mgmt_svc_coll_data(target_guid, metric_guid, key_value,
                                 collection_timestamp,
                                 value)
         bulk collect into l_dep_min_max_datas
         from mgmt_metrics_raw
        where target_guid = rec.dep_target_guid
          and metric_guid = rec.dep_metric_guid
          and key_value = l_dep_keys(indx)
          and  (collection_timestamp = l_max_ts or collection_timestamp = l_min_ts)
        order by collection_timestamp asc;
     
        IF l_dep_min_max_datas.COUNT = 0 THEN
           null;
        ELSIF l_dep_min_max_datas.COUNT = 1 THEN
           handle_single_data(l_dep_min_max_datas(1).target_guid ,
                             l_dep_min_max_datas(1).metric_guid ,
                             l_dep_min_max_datas(1).key_value ,
                             CAST(FROM_TZ(
                                   CAST(l_dep_min_max_datas(1).collection_timestamp as TIMESTAMP), 
                                   rec.timezone_region) 
                                   AT TIME ZONE l_timezone_region
		                   AS DATE),
                             l_dep_min_max_datas(1).value ,
                             p_repo_coll_interval ,
                             l_coll_time_svc_region ,
                             l_dep_values );
        ELSE
           l_temp_value :=
              interpolate_datas(l_dep_min_max_datas(1).value,
                                CAST(FROM_TZ(
                                   CAST(l_dep_min_max_datas(1).collection_timestamp as TIMESTAMP), 
                                   rec.timezone_region) 
                                   AT TIME ZONE l_timezone_region
		                   AS DATE),
                                l_dep_min_max_datas(2).value,
                                CAST(FROM_TZ(
                                   CAST(l_dep_min_max_datas(2).collection_timestamp as TIMESTAMP), 
                                   rec.timezone_region) 
                                   AT TIME ZONE l_timezone_region
		                   AS DATE),
                                l_coll_time_svc_region);
           l_dep_values.extend;
           l_dep_values(l_dep_values.COUNT) := mgmt_svc_coll_data(NULL, NULL, NULL,
                                                               NULL, l_temp_value);
           IF emdw_log.p_is_debug_set THEN
              emdw_log.debug('fetch_and_interpolate_data:interpolate data. '
                           || ' for target ' || l_dep_min_max_datas(1).target_guid
                           || ' metric ' || l_dep_min_max_datas(1).metric_guid
                           || ' key value ' || l_dep_min_max_datas(1).key_value
                           || ' the interpolate value is ' 
                           || l_temp_value
                           || ' at service time '
                           || TO_CHAR(l_coll_time_svc_region,
                                      'YYYY-MM-DD HH24:MI:SS'),
                           G_MODULE);
           END IF;
       END IF;
     END LOOP;
   END LOOP; 

   --Apply aggregate funciton to the number array l_dep_values
   IF l_dep_values.COUNT = 0 THEN
      p_ret_value := NULL;
      IF emdw_log.p_is_debug_set THEN
         emdw_log.debug('fetch_and_interpolate_data: No data for target '
                         || p_target_guid
                         || ' metric ' || p_metric_guid
                         || ' key ' || p_svc_key_value
                         || ' at time '
                         || TO_CHAR(l_coll_time_svc_region, 
                                       'YYYY-MM-DD, HH24:MI:SS')
                         , G_MODULE);
      END IF;
   ELSE
      IF p_agg_func = 'SUM' THEN
        l_svc_ret_value := sum_deps_values(l_dep_values);
      ELSIF p_agg_func = 'STDDEV' THEN
        l_svc_ret_value := stddev_deps_values(l_dep_values);
      ELSE
        raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
               'Aggregate function ' || p_agg_func || ' is not supported');
      END IF; 
      p_ret_value := mgmt_svc_coll_data(p_target_guid, p_metric_guid,
                                        p_svc_key_value, l_coll_time_svc_region,
                                        l_svc_ret_value);
      IF emdw_log.p_is_debug_set THEN
         emdw_log.debug('fetch_and_interpolate_data: Data for target '
                         || p_target_guid
                         || ' metric ' || p_metric_guid
                         || ' key ' || p_svc_key_value
                         || ' at time '
                         || TO_CHAR(l_coll_time_svc_region, 
                                       'YYYY-MM-DD, HH24:MI:SS')
                         || ' value is ' || l_svc_ret_value
                         || ' aggregate func is ' || p_agg_func
                         , G_MODULE);
      END IF;
   END IF;
   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('fetch_and_interpolate_data: Exit', G_MODULE);
   END IF;

END fetch_and_interpolate_data;

PROCEDURE  aggregate_historical_data(p_target_guid IN RAW,
                                     p_metric_guid IN RAW,
                                     p_coll_name IN VARCHAR2,
                                     l_svc_key_value IN VARCHAR2,
                                     l_start_timestamp IN DATE,
                                     l_end_timestamp IN DATE,
                                     p_agg_func IN VARCHAR2,
                                     l_temp_value OUT mgmt_svc_coll_data)
AS
l_dyn_sql VARCHAR2(32000);
l_timezone_region mgmt_targets.timezone_region%TYPE;
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('aggregate_historical_data: Enter', G_MODULE);
   END IF;

   SELECT timezone_region INTO l_timezone_region
     FROM mgmt_targets
    WHERE target_guid = p_target_guid;

   l_dyn_sql :=   'BEGIN '
                  || 'SELECT MGMT_SVC_COLL_DATA(:v_target_guid, '
                  || '                     :v_metric_guid, '
                  || '                     :v_coll_name, '
                  || '                     :v_coll_timestamp, '
                  ||                   p_agg_func||'(value)) '
                  || ' INTO :v_data '
                  || ' FROM  '
                  || '        ( SELECT d.dep_target_guid, '
                  || '                 d.dep_metric_guid, '
                  || '                 d.dep_key_value, '
                  || '                 CAST(FROM_TZ(CAST(:v_start as TIMESTAMP), ' 
                  || '                     ''GMT'') '
                  || '                     AT TIME ZONE t.timezone_region ' 
		  || '                     AS DATE) min, '
                  || '                 CAST(FROM_TZ(CAST(:v_stop as TIMESTAMP), ' 
                  || '                     ''GMT'') '
                  || '                     AT TIME ZONE t.timezone_region ' 
		  || '                    AS DATE) max '
                  || '           FROM mgmt_metric_dependency m, '
                  || '                mgmt_metric_dependency_details d,  '
                  || '                mgmt_targets t '
                  || '          WHERE m.target_guid = :v_target_guid '
                  || '            AND m.metric_guid = :v_metric_guid '
                  || '            AND m.key_value = :v_key_value '
                  || '            AND m.target_guid = d.target_guid '
                  || '            AND m.metric_guid = d.metric_guid '
                  || '            AND m.key_value = d.key_value '
                  || '            AND d.dep_target_guid = t.target_guid '
                  || '        ) c, '
                  || '       mgmt_metrics_raw r '
                  || ' WHERE c.dep_target_guid = r.target_guid '
                  || '   AND c.dep_metric_guid = r.metric_guid '
                  || '   AND (c.dep_key_value = r.key_value '
                  || '        OR c.dep_key_value = MGMT_METRIC_HELPER.G_DEP_ALL_KEY_VALUE) '
                  || '   AND r.collection_timestamp > c.min '
                  || '   AND r.collection_timestamp <= c.max; '
                  || 'END; ';
   BEGIN
      EXECUTE IMMEDIATE l_dyn_sql
            USING IN p_target_guid,
                  IN p_metric_guid,
                  IN p_coll_name,
                  IN CAST(FROM_TZ(CAST(l_end_timestamp as TIMESTAMP), G_GMT) AT TIME ZONE l_timezone_region AS DATE),
                  OUT l_temp_value,
                  IN  l_start_timestamp,
                  IN  l_end_timestamp,
                  IN  l_svc_key_value;
   EXCEPTION
      WHEN OTHERS THEN
             emdw_log.error('aggregate_historical_data:Dynamic sql got error ' 
                        || SQLERRM || ' SQL is ' || l_dyn_sql
                        || ' Collection timestamps is ' 
                        || to_char(l_end_timestamp, 
                                  'YYYY-MM-DD HH24:MI:SS'),
                          G_MODULE);
             raise;
   END;
   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('aggregate_historical_data: Exit', G_MODULE);
   END IF;
END aggregate_historical_data;


PROCEDURE internal_eval(p_target_guid IN RAW,
                        p_metric_guid IN RAW,
                        p_coll_name IN VARCHAR2,
                        p_agg_func IN VARCHAR2,
                        p_calculation_mode IN NUMBER,
                        p_values OUT mgmt_metric_value_array)
AS
l_values MGMT_METRIC_VALUE_ARRAY;
l_dep_count NUMBER;
l_temp_value MGMT_SVC_COLL_DATA;
l_start_timestamp DATE;
l_end_timestamp DATE;
l_last_coll_timestamp DATE;
l_min_dep_timestamp DATE;
l_repo_coll_interval NUMBER;
l_datas MGMT_SVC_COLL_DATA_ARRAY := MGMT_SVC_COLL_DATA_ARRAY();
l_num_keys mgmt_metrics.num_keys%TYPE;
l_svc_key_value mgmt_metric_dependency.key_value%TYPE;
l_backward_window NUMBER;
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('internal_eval: Enter', G_MODULE);
   END IF;

   get_key_by_key_num(p_target_guid, p_metric_guid, 
                      p_coll_name, l_num_keys, l_svc_key_value);    
   l_dep_count := get_dependents_count(p_target_guid,
                              p_metric_guid, p_coll_name);
   -- l_dep_count can not be 0
   IF l_dep_count = 1 THEN
      copy_value(p_target_guid, p_metric_guid, p_coll_name, l_values);
      p_values := l_values;
      IF EMDW_LOG.P_IS_DEBUG_SET THEN
        EMDW_LOG.DEBUG('internal_eval: call COPY for target ' 
                       || p_target_guid
                       || ' metric ' || p_metric_guid || ' key ' 
                       || l_svc_key_value || ' collection name '
                       || p_coll_name , G_MODULE);
      END IF;
   ELSE
      l_last_coll_timestamp := last_coll_time_utc(p_target_guid, 
                                                  p_metric_guid, 
                                                  l_svc_key_value);

      l_repo_coll_interval := get_repo_coll_interval(p_target_guid,
                                                     p_metric_guid,
                                                     p_coll_name);

      l_min_dep_timestamp := min_dep_svc_coll_time_utc(p_target_guid,
                                                       p_metric_guid,
                                                       l_svc_key_value);
      IF l_min_dep_timestamp IS NULL THEN
        p_values :=  mgmt_metric_value_array();
        p_values.extend(1);
        p_values(1) := MGMT_METRIC_VALUE_OBJ.new(p_target_guid, 
                                    EM_METRIC_EVAL.G_METRIC_EVAL_SKIP,
                                    'No data', 
                                    NULL,
                                    NULL);

        RETURN;
      END IF;
      
      -- only calculate for last 6hours
      l_backward_window := get_backward_param;
      IF l_min_dep_timestamp - l_last_coll_timestamp > l_backward_window/24 THEN
        l_start_timestamp := l_min_dep_timestamp - l_backward_window/24;
      ELSE 
        l_start_timestamp := l_last_coll_timestamp;
      END IF;

      LOOP
        IF l_start_timestamp >= l_min_dep_timestamp THEN
           EXIT;
        END IF;

        l_end_timestamp := l_start_timestamp + l_repo_coll_interval/(24*60);
        IF l_end_timestamp > l_min_dep_timestamp THEN
           l_end_timestamp := l_min_dep_timestamp;
        END IF;
       
        IF p_calculation_mode = G_EVAL_INTERPOLATION_DATA THEN 
            fetch_and_interpolate_data(p_target_guid, 
                                       p_metric_guid, 
                                       l_svc_key_value,
                                       l_end_timestamp, 
                                       l_repo_coll_interval, 
                                       p_agg_func,
                                       l_temp_value);
        ELSE
           aggregate_historical_data(p_target_guid,
                                     p_metric_guid,
                                     p_coll_name,
                                     l_svc_key_value,
                                     l_start_timestamp,
                                     l_end_timestamp,
                                     p_agg_func,
                                     l_temp_value);
        END IF;
        
        IF l_temp_value IS NOT NULL THEN
           l_datas.EXTEND;
           l_datas(l_datas.COUNT) := l_temp_value;
           IF emdw_log.P_IS_DEBUG_SET THEN
              emdw_log.debug('eval_interpplation_value: Eval ' || p_agg_func 
                        || ' for target ' || p_target_guid
                        || ' metric ' || p_metric_guid
                        || ' collection ' || p_coll_name
                        || ' at '
                        || TO_CHAR(l_end_timestamp, 'YYYY-MM-DD HH24:MI:SS')
                        || ' Value is ' || l_temp_value.value
                        , G_MODULE);
           END IF;

           l_temp_value := NULL;
        END IF;
        l_start_timestamp := l_start_timestamp + l_repo_coll_interval/(24*60);
      END LOOP;

      --check svc avail and pack results
      check_avail_pack_results(p_target_guid, p_metric_guid, p_coll_name,
                           l_svc_key_value, l_num_keys, l_datas, p_values);

   END IF;
   IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('internal_eval: Exit', G_MODULE);
   END IF;

END internal_eval;

------------End Private Procedures-----------------------

-- called from other helper functions when there is only one dependent
PROCEDURE copy_value(p_target_guid IN RAW,
                         p_metric_guid IN RAW,
                         p_coll_name IN VARCHAR2,
                         p_values OUT mgmt_metric_value_array)
AS
l_dep_target_guid RAW(16);
l_dep_metric_guid RAW(16);
l_dep_key_value mgmt_metric_dependency_details.dep_key_value%TYPE;
l_last_svc_coll_time DATE;
l_datas MGMT_SVC_COLL_DATA_ARRAY;
l_key_column mgmt_metrics.key_column%TYPE;
l_metric_column mgmt_metrics.metric_column%TYPE;
l_num_keys mgmt_metrics.num_keys%TYPE;
l_svc_key_value mgmt_metric_dependency.key_value%TYPE;
l_data_cur COLL_DATA_CURTYPE;
l_timezone_region mgmt_targets.timezone_region%TYPE;
l_backward_window NUMBER;
l_sysdate_utc DATE;
l_dep_timezone mgmt_targets.timezone_region%TYPE;
l_last_coll_dep_zone DATE;
BEGIN
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('copy_value: Enter ' ||
                   ' target ' || p_target_guid
                   || ' metric ' || p_metric_guid
                   || ' coll name ' || p_coll_name
                   , G_MODULE);
  END IF;

  get_key_by_key_num(p_target_guid,
              p_metric_guid, p_coll_name, l_num_keys, l_svc_key_value);    

  IF EMDW_LOG.P_IS_DEBUG_SET THEN
     EMDW_LOG.debug('copy_value :: ' || p_coll_name ||
                    '/' || p_target_guid || '/' || p_metric_guid ||
                     ' - key value resolved to ' || l_svc_key_value,
                     G_MODULE);
  END IF;

  SELECT timezone_region INTO l_timezone_region
    FROM mgmt_targets
   WHERE target_guid = p_target_guid;

  -- take advantage of the fact there is only one dependent
  BEGIN 
    SELECT dep_target_guid, dep_metric_guid, dep_key_value, timezone_region 
      INTO l_dep_target_guid, l_dep_metric_guid, l_dep_key_value,
           l_dep_timezone
    FROM mgmt_metric_dependency_details dd, mgmt_targets t
    WHERE dd.target_guid = p_target_guid
      AND   dd.metric_guid = p_metric_guid
      AND  dd.key_value = l_svc_key_value
      AND dd.dep_target_guid = t.target_guid;
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        raise_application_error(MGMT_GLOBAL.INVALID_METRIC_DEP_ERR, 
                            MGMT_GLOBAL.INVALID_METRIC_DEP_ERR_M ||
                            ' Target ' || p_target_guid || ' metric ' ||
                            p_metric_guid || ' coll  ' || p_coll_name ||
                            ' key ' || l_svc_key_value || 
                           ' has more than 1 dependent, ' ||
                           ' but COPY eval function is used');
    WHEN NO_DATA_FOUND THEN
        raise_application_error(MGMT_GLOBAL.INVALID_METRIC_DEP_ERR, 
                            MGMT_GLOBAL.INVALID_METRIC_DEP_ERR_M ||
                            ' Target ' || p_target_guid || ' metric ' ||
                            p_metric_guid || ' coll ' || p_coll_name ||
                            ' key ' || l_svc_key_value || 
                           ' has 0 dependent, but collection is not disabled');

    
  END;


  IF l_dep_key_value = MGMT_METRIC_HELPER.G_DEP_ALL_KEY_VALUE THEN
        raise_application_error(MGMT_GLOBAL.INVALID_METRIC_DEP_ERR, 
                            MGMT_GLOBAL.INVALID_METRIC_DEP_ERR_M ||
                            ' Target ' || p_target_guid || ' metric ' ||
                            p_metric_guid || ' coll ' || p_coll_name ||
                            ' key ' || l_svc_key_value || 
                           ' uses ALL KEY for dependent while eval function '
                           || ' is COPY.');
  END IF;

  l_last_svc_coll_time := last_coll_time_utc(p_target_guid, p_metric_guid,
                             l_svc_key_value);
  l_backward_window := get_backward_param;
  l_sysdate_utc := cast(sys_extract_utc(systimestamp) as DATE);

  IF l_sysdate_utc - l_last_svc_coll_time > l_backward_window/24 THEN
     l_last_svc_coll_time := l_sysdate_utc - l_backward_window/24;
  END IF;

  l_last_coll_dep_zone := CAST(FROM_TZ(CAST(l_last_svc_coll_time
                                              as TIMESTAMP), 
                                          G_GMT) 
                                 AT TIME ZONE l_dep_timezone 
		                 AS DATE);

  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('last coll time in dep zone ' 
               || to_char(l_last_coll_dep_zone, 'YYYY-MM-DD HH24:MI:SS'),  G_MODULE);
  END IF;

  OPEN l_data_cur FOR 
  SELECT MGMT_SVC_COLL_DATA(p_target_guid,
                            p_metric_guid,
                            p_coll_name,
                            r.collection_timestamp,
                            r.value)
  FROM mgmt_metrics_raw r
  WHERE r.target_guid = l_dep_target_guid
   AND r.metric_guid = l_dep_metric_guid
   AND r.key_value = l_dep_key_value
   AND r.collection_timestamp > l_last_coll_dep_zone
   ORDER BY r.collection_timestamp;

  check_avail_pack_results(p_target_guid, p_metric_guid, p_coll_name,
                           l_svc_key_value, l_num_keys, l_data_cur, p_values,
                           l_timezone_region,
                           l_dep_timezone);


  IF l_data_cur%ISOPEN THEN
    CLOSE l_data_cur;
  END IF;

  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('copy_value: Exit', G_MODULE);
  END IF;

END copy_value;

--
-- PROCEDURE: eval_min_valuecollections_tables.sql
-- PURPOSE: calculate the min value of dependent metrics 
--       for the aggregate service's metric
-- IN PARAMETER:collections_tables.sql
--     p_target_guid; service guid
--     p_metrics_guid, metric guid
-- OUT PARAMETER:
--      p_values, caculated metric values
PROCEDURE eval_min_value(p_target_guid IN RAW,
                         p_metric_guid IN RAW,
                         p_coll_name IN VARCHAR2,
                         p_values OUT MGMT_METRIC_VALUE_ARRAY)
AS
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('eval_min_value: Enter' ||
                   ' target ' || p_target_guid
                   || ' metric ' || p_metric_guid
                   || ' coll name ' || p_coll_name
                   , G_MODULE);
   END IF;

   internal_eval(p_target_guid,
                 p_metric_guid,
                 p_coll_name,
                 'MIN',
                 G_EVAL_HISTORICAL_DATA,
                 p_values);

   IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('eval_min_value: Exit', G_MODULE);
   END IF;

END;

--
-- PROCEDURE: eval_max_value
-- PURPOSE: calculate the mav value of dependent metrics for the aggregate service's metric
-- IN PARAMETER:
--     p_target_guid; service guid
--     p_metrics_guid, metric guid
-- OUT PARAMETER:
--       p_values, caculated metric values
PROCEDURE eval_max_value(p_target_guid IN RAW,
                         p_metric_guid IN RAW,
                         p_coll_name IN VARCHAR2,
                         p_values OUT MGMT_METRIC_VALUE_ARRAY)
AS
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('eval_max_value: Enter' ||
                   ' target ' || p_target_guid
                   || ' metric ' || p_metric_guid
                   || ' coll name ' || p_coll_name
                   , G_MODULE);
   END IF;

   internal_eval(p_target_guid,
                 p_metric_guid,
                 p_coll_name,
                 'MAX',
                 G_EVAL_HISTORICAL_DATA,
                 p_values);

   IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('eval_max_value: Exit', G_MODULE);
   END IF;
END;
--
-- PROCEDURE: eval_avg_value
-- PURPOSE: calculate the avg value of dependent metrics 
--   for the aggregate service's metric
-- IN PARAMETER:
--     p_target_guid; service guid
--     p_metrics_guid, metric guid
-- OUT PARAMETER:
--       p_values, caculated metric values
PROCEDURE eval_avg_value(p_target_guid IN RAW,
                         p_metric_guid IN RAW,
                         p_coll_name IN VARCHAR2,
                         p_values OUT MGMT_METRIC_VALUE_ARRAY)
AS
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('eval_avg_value: Enter' ||
                   ' target ' || p_target_guid
                   || ' metric ' || p_metric_guid
                   || ' coll name ' || p_coll_name
                   , G_MODULE);
   END IF;

   internal_eval(p_target_guid,
                 p_metric_guid,
                 p_coll_name,
                 'AVG',
                 G_EVAL_HISTORICAL_DATA,
                 p_values);

   IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('eval_avg_value: Exit', G_MODULE);
   END IF;
END;
--
-- PROCEDURE: eval_sum_value
-- PURPOSE: calculate the sum value of dependent metrics for the aggregate service's metric
-- IN PARAMETER:
--     p_target_guid; service guid
--     p_metrics_guid, metric guid
-- OUT PARAMETER:
--       p_values, caculated metric values
PROCEDURE eval_sum_value(p_target_guid IN RAW,
                         p_metric_guid IN RAW,
                         p_coll_name IN VARCHAR2,
                         p_values OUT MGMT_METRIC_VALUE_ARRAY)
AS
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('eval_sum_value: Enter' ||
                   ' target ' || p_target_guid
                   || ' metric ' || p_metric_guid
                   || ' coll name ' || p_coll_name
                   , G_MODULE);
   END IF;

   internal_eval(p_target_guid,
                 p_metric_guid,
                 p_coll_name,
                 'SUM',
                 G_EVAL_INTERPOLATION_DATA,
                 p_values);

   IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('eval_sum_value: Exit', G_MODULE);
   END IF;
END;

PROCEDURE eval_stddev_value(p_target_guid IN RAW,
                         p_metric_guid IN RAW,
                         p_coll_name IN VARCHAR2,
                         p_values OUT MGMT_METRIC_VALUE_ARRAY)
AS
BEGIN
   IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('eval_stddev_value: Enter' ||
                   ' target ' || p_target_guid
                   || ' metric ' || p_metric_guid
                   || ' coll name ' || p_coll_name
                   , G_MODULE);
   END IF;

   internal_eval(p_target_guid,
                 p_metric_guid,
                 p_coll_name,
                 'STDDEV',
                 G_EVAL_INTERPOLATION_DATA,
                 p_values);

   IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('eval_stddev_value: Exit', G_MODULE);
   END IF;

END eval_stddev_value;

-- Wrapper eval proc for data repo collections
PROCEDURE wrapper_eval_func(p_target_guid IN RAW,
                            p_metric_guid IN RAW,
                            p_coll_name IN VARCHAR2,
                            p_values OUT MGMT_METRIC_VALUE_ARRAY)
IS
  l_eval_func VARCHAR2(256);
  l_num_keys NUMBER;
  l_dep_count NUMBER := 0;
  -- delete this after the base bug is fixed
  l_index NUMBER;
  l_temp_values MGMT_METRIC_VALUE_ARRAY;

  l_key_value mgmt_metric_dependency.key_value%TYPE;
  l_column_metric_guid mgmt_metric_dependency.metric_guid%TYPE;

BEGIN

  l_key_value := em_coll_util.get_coll_item_property(
                 p_target_guid,
                 mgmt_global.g_object_type_target,
                 p_metric_guid,
                 p_coll_name,
                 em_rep_metric.G_KEY_VALUE_PROP);
  l_column_metric_guid := HEXTORAW(em_coll_util.get_coll_item_property(
                 p_target_guid,
                 mgmt_global.g_object_type_target,
                 p_metric_guid,
                 p_coll_name,
                 em_rep_metric.G_COLUME_GUID_PROP));

  IF EMDW_LOG.P_IS_DEBUG_SET THEN
     EMDW_LOG.debug('wrapper_eval_func :: ' || p_coll_name ||
                    '/' || p_target_guid || '/' || p_metric_guid ||
                     ' - key value resolved to ' || l_key_value ||
                     ' - column metric resolved to ' || l_column_metric_guid,
                     G_MODULE);
  END IF;


  BEGIN
    SELECT eval_func
      INTO l_eval_func
      FROM mgmt_metric_dependency
     WHERE target_guid = p_target_guid
       AND metric_guid = l_column_metric_guid
       AND key_value   = NVL(l_key_value, ' ');
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
          raise_application_error(MGMT_GLOBAL.MISSING_EVAL_FUNC_ERR,
                            MGMT_GLOBAL.MISSING_EVAL_FUNC_ERR_M ||
                            ' Target ' || p_target_guid || ' metric ' ||
                            p_metric_guid || ' column metric ' 
                            || l_column_metric_guid || ' key ' || l_key_value);
   END;


   SELECT count(*)
    INTO l_dep_count
    FROM mgmt_metric_dependency_details
   WHERE target_guid = p_target_guid
     AND metric_guid = l_column_metric_guid
     AND key_value   = NVL(l_key_value, ' ');


  -- Don't call eval-proc if there are no dependencies
  IF (l_dep_count = 0) THEN
    IF EMDW_LOG.P_IS_DEBUG_SET THEN
        EMDW_LOG.debug('wrapper_eval_func :: ' || p_coll_name ||
                       '/' || p_target_guid || '/' || p_metric_guid ||
                       '/column ' || l_column_metric_guid || ' key ' ||
                       l_key_value ||
                       ' No metric dependencies found, returning no data',
                       G_MODULE);
    END IF;
--  collection framwork does not work for metric collection error
    raise_application_error(mgmt_global.INVALID_METRIC_DEP_ERR, 
                                    'Metric configuration for '
                                     || l_key_value || ' is missing  -');
/*  
    p_values :=  mgmt_metric_value_array();
    p_values.extend(1);
    p_values(1) := MGMT_METRIC_VALUE_OBJ.new(p_target_guid, 
                                    EM_METRIC_EVAL.G_METRIC_EVAL_ERROR,
                                    'Metric configuration for '
                                     || l_key_value || ' is missing',
                                    NULL,
                                    NULL);
*/
    return;
  END IF;


  -- Execute the callback now
  EXECUTE IMMEDIATE
    'BEGIN ' || DBMS_ASSERT.qualified_sql_name(l_eval_func)
             || '(:1, :2, :3, :4); END; '
     USING IN p_target_guid, IN l_column_metric_guid, IN l_key_value,
     OUT p_values;

END wrapper_eval_func;

END;
/
show errors;