Rem drv: <create type="pkgbodys" pos="service/service_pkgbody.sql+"/>
Rem
Rem $Header: aggservice_ui_pkgbody.sql 06-jun-2007.04:02:49 denath Exp $
Rem
Rem aggservice_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      aggservice_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    denath      04/30/07 - Bug fix 6019788.Modified query in
Rem                           GET_SERVICES_FOR_SYSTEM for perf.
Rem    denath      06/06/07 - Backport denath_bug-6019788 from main
Rem    mfidanbo    04/11/07 - add support for returning availability delay
Rem    andyao      11/06/06 - fix query for non promoted metrics
Rem    eporter     08/01/06 - Backport eporter_bug-5404472 from main
Rem    eporter     07/25/06 - Bug 5404472 
Rem    eporter     06/09/06 - Bug 5264151: Cannot see external alerts 
Rem    eporter     07/19/06 - Backport Business functions
Rem    eporter     07/19/06 - Backport jpyang_bug-4871802 (needed by my code)
Rem    mfidanbo    07/07/06 - Backport mfidanbo_bug-4622522 from main 
Rem    jpyang      11/27/05 - Backport jpyang_bug-4685891 from main 
Rem    jpyang      11/01/05 - fix
Rem    jpyang      09/23/05 - timezone support in chart 
Rem    jpyang      08/22/05 - fix notrans query for GET_METRICS_LIST 
Rem    jpyang      07/08/05 - more type_meta_ver fix 
Rem    jpyang      07/11/05 - use column_label for service charts 
Rem    jpyang      06/30/05 - get_metrics_list performance 
Rem    jpyang      06/29/05 - optimize get_metric_list 
Rem    jpyang      06/22/05 - support timezone 
Rem    mfidanbo    06/08/05 - fix api 
Rem    mfidanbo    04/13/05 - return more information with alerts cursor 
Rem    jpyang      03/29/05 - fix modify_aggservice 
Rem    gan         03/18/05 - repo coll uses table metric
Rem    aholser     03/09/05 - allow operator setting for perf metrics 
Rem    jpyang      03/04/05 - get_metrics_list -> add thresholds 
Rem    jpyang      03/03/05 - handle create duplicate service 
Rem    jpyang      02/22/05 - add test based 
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    vagarwal    12/22/04 - return a single row cursor instead of empty 
Rem                           cursor for targets that do not have availability 
Rem    vagarwal    12/15/04 - modify avail_all_assoc_targets for groups 
Rem    jpyang      12/01/04 - fix edit sql 
Rem    jpyang      11/23/04 - support nontrans for chart 
Rem    jpyang      11/08/04 - perf/usage tab show/hide support 
Rem    jpyang      10/21/04 - avail history hgrid 
Rem    jpyang      10/15/04 - charts sql 
Rem    jpyang      10/07/04 - homepage chart sql 
Rem    vagarwal    10/03/04 - change proc name to get_services_for_system 
Rem    vagarwal    09/30/04 - add proc to get svc for a system, 
Rem    jpyang      09/29/04 - home pages 
Rem    jpyang      09/20/04 - add timezone 
Rem    jpyang      09/08/04 - support edit
Rem    jpyang      09/02/04 - jpyang_services_ui
Rem    jpyang      08/31/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY EM_AGGSVC_UI AS

---------------------PRIVATE PROCEDURES/FUNCTIONS ---------------------------
-- return performance cursor
CURSOR metric_cur (p_target_guid IN RAW, p_target_type IN VARCHAR2,
                   p_metric_category IN VARCHAR2) IS
     SELECT met.metric_guid, met.metric_name, met.metric_column, 
            '' as coll_name, th.warning_operator,
            th.warning_threshold, th.critical_operator,
            th.critical_threshold
       FROM ( SELECT m.metric_guid, m.metric_name, m.metric_column,
                     t.target_guid
                FROM mgmt_targets t,
                     mgmt_metrics m,
                     mgmt_category_map map
               WHERE t.target_guid = hextoraw(p_target_guid)
                 AND t.target_type = m.target_type
                 AND t.type_meta_ver = m.type_meta_ver
                 AND t.target_type = map.target_type
                 AND t.type_meta_ver = map.type_meta_ver
                 AND (t.category_prop_1 = m.category_prop_1
                       OR m.category_prop_1 = ' ')
                 AND (t.category_prop_2 = m.category_prop_2
                       OR m.category_prop_2 = ' ')
                 AND (t.category_prop_3 = m.category_prop_3
                       OR m.category_prop_3 = ' ')
                 AND (t.category_prop_4 = m.category_prop_4
                       OR m.category_prop_4 = ' ')
                 AND (t.category_prop_5 = m.category_prop_5
                       OR m.category_prop_5 = ' ')
                 AND m.is_transposed = 0
                 AND m.metric_type not in (MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
                                           MGMT_GLOBAL.G_METRIC_TYPE_REPOS_TABLE)
                 AND map.object_guid = m.metric_guid
                 AND map.object_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC
                 AND map.class_name = MGMT_GLOBAL.G_CATEGORY_CLASS_SERVICE
                 AND map.category_name = p_metric_category) met,
            mgmt_metric_thresholds th
        WHERE met.target_guid = th.target_guid (+)
          AND met.metric_guid = th.metric_guid (+)    
     UNION ALL
     SELECT m.metric_guid, m.metric_name, m.metric_column,
            m.coll_name, th.warning_operator,
            th.warning_threshold, th.critical_operator,
            th.critical_threshold
       FROM mgmt_metric_thresholds th,
            (SELECT t.target_guid,
                    cm.metric_name,
                    cm.metric_column,
                    cm.metric_guid,
                    c.coll_name,
                    cm.metric_label_nlsid,
                    cm.column_label_nlsid
               FROM mgmt_metrics cm,
                    mgmt_metrics tm,
                    mgmt_category_map map,
                    mgmt_metric_collections c,
                    mgmt_targets t
              WHERE t.target_guid = hextoraw(p_target_guid)
                AND t.target_type = cm.target_type
                AND t.type_meta_ver = cm.type_meta_ver
                AND t.type_meta_ver = tm.type_meta_ver
                AND t.type_meta_ver = map.type_meta_ver
                AND (t.category_prop_1 = cm.category_prop_1
                 OR cm.category_prop_1 = ' ')
                AND (t.category_prop_2 = cm.category_prop_2
                 OR cm.category_prop_2 = ' ')
                AND (t.category_prop_3 = cm.category_prop_3
                 OR cm.category_prop_3 = ' ')
                AND (t.category_prop_4 = cm.category_prop_4
                 OR cm.category_prop_4 = ' ')
                AND (t.category_prop_5 = cm.category_prop_5
                 OR cm.category_prop_5 = ' ')
                AND cm.is_transposed = 1
                AND cm.key_order = 0
                AND cm.metric_column <> ' '
                AND map.object_guid = cm.metric_guid
                AND map.object_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC
                AND map.class_name = MGMT_GLOBAL.G_CATEGORY_CLASS_SERVICE 
                AND map.category_name = p_metric_category
                AND tm.metric_name = cm.metric_name
                AND tm.metric_column = ' '
                AND c.target_guid = hextoraw(p_target_guid)
                AND c.metric_guid = tm.metric_guid)
             m
      WHERE m.target_guid = th.target_guid (+)
        AND m.metric_guid = th.metric_guid (+)
        AND m.coll_name = th.coll_name(+);

CURSOR metric_cur_edit  (p_target_guid IN RAW, p_target_type IN VARCHAR2,
                   p_metric_category IN VARCHAR2) IS
     SELECT met.metric_guid, met.metric_name, met.metric_column,
            '' as coll_name,
            th.warning_operator, th.warning_threshold, 
            th.critical_operator, th.critical_threshold,
            '' as eval_func
       FROM (SELECT m.metric_guid, m.metric_name, m.metric_column,
                    t.target_guid
               FROM mgmt_targets t,
                    mgmt_metrics m,
                    mgmt_category_map map
              WHERE t.target_guid = p_target_guid
                AND t.target_type = m.target_type
                AND t.type_meta_ver = m.type_meta_ver
                AND (t.category_prop_1 = m.category_prop_1
                     OR m.category_prop_1 = ' ')
                AND (t.category_prop_2 = m.category_prop_2
                     OR m.category_prop_2 = ' ')
                AND (t.category_prop_3 = m.category_prop_3
                     OR m.category_prop_3 = ' ')
                AND (t.category_prop_4 = m.category_prop_4
                     OR m.category_prop_4 = ' ')
                AND (t.category_prop_5 = m.category_prop_5
                      OR m.category_prop_5 = ' ') 
                AND m.is_transposed = 0
                AND m.metric_type not in (mgmt_global.G_METRIC_TYPE_TABLE,
                                          mgmt_global.G_METRIC_TYPE_REPOS_TABLE)
                AND map.object_guid = m.metric_guid
                AND map.object_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC
                AND map.class_name = MGMT_GLOBAL.G_CATEGORY_CLASS_SERVICE
                AND map.category_name = p_metric_category) met,
               mgmt_metric_thresholds th
      WHERE met.target_guid = th.target_guid (+)
        AND met.metric_guid = th.metric_guid (+)
     UNION ALL
     SELECT m.metric_guid, m.metric_name, m.metric_column,
            d.key_value coll_name,
            th.warning_operator, th.warning_threshold, 
            th.critical_operator, th.critical_threshold,
            d.eval_func as eval_func
       FROM mgmt_targets t,
            mgmt_category_map map,
            mgmt_metric_dependency d,
            mgmt_metric_thresholds th,
            mgmt_metrics m
      WHERE t.target_guid = p_target_guid
        AND t.target_type = m.target_type
        AND t.type_meta_ver = m.type_meta_ver
        AND (t.category_prop_1 = m.category_prop_1
             OR m.category_prop_1 = ' ')
        AND (t.category_prop_2 = m.category_prop_2
             OR m.category_prop_2 = ' ')
        AND (t.category_prop_3 = m.category_prop_3
             OR m.category_prop_3 = ' ')
        AND (t.category_prop_4 = m.category_prop_4
             OR m.category_prop_4 = ' ')
        AND (t.category_prop_5 = m.category_prop_5
              OR m.category_prop_5 = ' ') 
        AND m.is_transposed = 1
        AND map.object_guid = m.metric_guid
        AND map.object_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC
        AND map.class_name = MGMT_GLOBAL.G_CATEGORY_CLASS_SERVICE
        AND map.category_name = p_metric_category
        AND d.target_guid = t.target_guid
        AND d.metric_guid = m.metric_guid
        AND d.target_guid = th.target_guid (+)
        AND d.metric_guid = th.metric_guid (+)
        AND d.key_value   = th.coll_name (+);


-- return dependencies metrics cursor
CURSOR dep_metric_cur (p_key_value IN VARCHAR2, 
                       p_category_name IN VARCHAR2,
                       p_target_guid IN RAW,
                       p_metric_guid IN RAW) IS
    SELECT DISTINCT t.target_name, t.target_type, m.metric_name, m.metric_column, dep.dep_key_value
    FROM mgmt_metric_dependency_details dep,
         mgmt_metrics m,
         mgmt_targets t,
         mgmt_category_map map
	WHERE dep.dep_target_guid = t.target_guid
	AND dep.dep_metric_guid = m.metric_guid
    AND dep.key_value = p_key_value
    AND m.type_meta_ver = t.type_meta_ver
    AND dep.target_guid = p_target_guid
    AND dep.metric_guid = p_metric_guid
    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 = ' ')
    AND map.object_guid = m.metric_guid
    AND map.object_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC 
    AND map.class_name = MGMT_GLOBAL.G_CATEGORY_CLASS_SERVICE 
    AND map.category_name = p_category_name;


PROCEDURE MODIFY_AGGSERVICE_METRICS(p_aggsvc_name      IN VARCHAR2,
                                 p_target_type      IN VARCHAR2,
                                 p_metrics          IN AGGSERVICE_METRIC_ARRAY,
                                 p_target_guid      IN RAW,
                                 p_service_category IN VARCHAR2,
                                 p_metric_name      IN VARCHAR2,
                                 p_value_col_name   IN VARCHAR2) IS
l_metrics      AGGSERVICE_METRIC_ARRAY := AGGSERVICE_METRIC_ARRAY();
l_metric       AGGSERVICE_METRIC;
l_metric_names SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
l_metric_name  VARCHAR2(256);
l_thresholds   MGMT_THRESHOLDS;

BEGIN
    -- find collection to be modified
    SELECT AGGSERVICE_METRIC(p.metric_name, p.metric_column, p.coll_name, 
                p.component_metrics, p.warning_threshold,
        	p.critical_threshold, p.operator, p.show_in_home_page, p.eval_function)
        BULK COLLECT INTO l_metrics
        FROM TABLE(CAST(p_metrics AS AGGSERVICE_METRIC_ARRAY)) p
    WHERE p.coll_name IN 
        (SELECT coll_name
            FROM mgmt_targets t,
                 mgmt_metric_collections c,
                 mgmt_category_map map
           WHERE t.target_guid = p_target_guid
             AND t.target_guid = c.target_guid
             AND t.type_meta_ver = map.type_meta_ver
             AND map.target_type = p_target_type
             AND map.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE
             AND map.category_name = p_service_category
             AND map.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
             AND map.object_guid = c.metric_guid);
             
    if (emdw_log.p_is_debug_set) THEN
        emdw_log.debug('modify aggregate service : modified ' || p_service_category ||
                       ' metrics length : ' || l_metrics.COUNT, MODULE_NAME);
    end if;

    IF l_metrics IS NOT NULL AND l_metrics.COUNT > 0 
    THEN
        FOR i IN l_metrics.FIRST .. l_metrics.LAST
        LOOP
          l_metric := l_metrics(i);

          EM_REP_METRIC.modify_repo_metric_collection(
                MGMT_METRIC_INSTANCE.NEW(
                    p_aggsvc_name,
                    p_target_type,
                    l_metric.metric_name,
                    l_metric.metric_column,
                    l_metric.coll_name), 
                    l_metric.eval_function,
                    l_metric.component_metrics);  
                
          IF (l_metric.show_in_home_page = 'true')
          THEN
            IF(p_metric_name = mgmt_service.G_USAGE_METRIC_NAME) THEN
              MGMT_SERVICE.set_homepage_usage_chart(
                p_aggsvc_name,
                p_target_type,
                l_metric.metric_name, 
                l_metric.metric_column,
                l_metric.coll_name);    
            ELSIF (p_metric_name = mgmt_service.G_PERFORMANCE_METRIC_NAME) THEN
              MGMT_SERVICE.set_homepage_perf_chart(
                p_aggsvc_name,
                p_target_type,
                l_metric.metric_name, 
                l_metric.metric_column,
                l_metric.coll_name);                      
            END IF;
          END IF;

          set_thresholds(p_aggsvc_name, p_target_type,
              l_metric);

        END LOOP;        
    END IF;    

 -- find collection to be added
 SELECT AGGSERVICE_METRIC(p.metric_name, p.metric_column, p.coll_name, 
                p.component_metrics, p.warning_threshold,
		p.critical_threshold, p.operator, p.show_in_home_page, p.eval_function) 
        BULK COLLECT INTO l_metrics
        FROM TABLE(CAST(p_metrics AS AGGSERVICE_METRIC_ARRAY)) p
    WHERE p.coll_name NOT IN 
        (SELECT coll_name
            FROM mgmt_targets t,
                 mgmt_metric_collections c,
                 mgmt_category_map map
           WHERE t.target_guid = p_target_guid
             AND t.target_guid = c.target_guid
             AND t.type_meta_ver = map.type_meta_ver
             AND map.target_type = p_target_type
             AND map.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE
             AND map.category_name = p_service_category
             AND map.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
             AND c.metric_guid = map.object_guid);
             
    if (emdw_log.p_is_debug_set) THEN
        emdw_log.debug('modify aggregate service : new ' || p_service_category || 
                       ' metrics length : ' || l_metrics.COUNT, MODULE_NAME);
    end if;
          
    IF l_metrics IS NOT NULL AND l_metrics.COUNT > 0 
    THEN
        FOR i IN l_metrics.FIRST .. l_metrics.LAST
        LOOP
          l_metric := l_metrics(i);
          EM_REP_METRIC.add_repo_metric_collection(
                MGMT_METRIC_INSTANCE.NEW(
                    p_aggsvc_name,
                    p_target_type,
                    p_metric_name,
                    p_value_col_name,
                    l_metric.coll_name), 
                    l_metric.eval_function,
                    l_metric.component_metrics);                

          IF (l_metric.show_in_home_page = 'true')
          THEN
            IF(p_metric_name = mgmt_service.G_USAGE_METRIC_NAME) THEN
              MGMT_SERVICE.set_homepage_usage_chart(
                p_aggsvc_name,
                p_target_type,
                p_metric_name,
                p_value_col_name,
                l_metric.coll_name);    
            ELSIF (p_metric_name = mgmt_service.G_PERFORMANCE_METRIC_NAME) THEN
              MGMT_SERVICE.set_homepage_perf_chart(
                p_aggsvc_name,
                p_target_type,
                p_metric_name,
                p_value_col_name,
                l_metric.coll_name);                      
            END IF;                  
          END IF;

          set_thresholds(p_aggsvc_name, p_target_type,
              l_metric);

        END LOOP;        
    END IF;
     
    -- find collection to be deleted
    -- find metrics (metric name, metric column)
    SELECT c.coll_name
        BULK COLLECT INTO l_metric_names
        FROM 
            mgmt_metric_collections c,
            mgmt_targets t,
            mgmt_category_map map
    WHERE c.coll_name NOT IN
        (SELECT coll_name
                FROM TABLE(CAST(p_metrics AS AGGSERVICE_METRIC_ARRAY)))
     AND t.target_guid = p_target_guid
     AND c.target_guid = t.target_guid
     AND t.target_type = p_target_type
     AND t.type_meta_ver = t.type_meta_ver
     AND map.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE
     AND map.category_name = p_service_category
     AND map.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
     AND c.metric_guid = map.object_guid;

    if (emdw_log.p_is_debug_set) THEN
        emdw_log.debug('modify aggregate service : deleted ' || p_service_category || 
                   ' metrics length : ' || l_metric_names.COUNT, MODULE_NAME);
    end if;
    
    IF l_metric_names IS NOT NULL AND l_metric_names.COUNT > 0 
    THEN
        FOR i IN l_metric_names.FIRST .. l_metric_names.LAST
        LOOP
            l_metric_name := l_metric_names(i);
            EM_REP_METRIC.delete_repo_metric_collection(
                MGMT_METRIC_INSTANCE.NEW(
                    p_aggsvc_name,
                    p_target_type,
                    p_metric_name,
                    p_value_col_name,
                    l_metric_name));
          -- delete threshold
          mgmt_monitoring.delete_target_metric_config(
                    p_target_type,
                    p_aggsvc_name,
                    p_metric_name,
                    p_value_col_name,
                    l_metric_name);
        END LOOP;
    END IF;

END MODIFY_AGGSERVICE_METRICS;

---------------------END PRIVATE PROCEDURES/FUNCTIONS--------------------------

PROCEDURE SAVE_AGGSERVICE(p_aggsvc_name IN VARCHAR2,
                        p_target_type IN VARCHAR2,
                        p_subservices IN SMP_EMD_NVPAIR_ARRAY,
                        p_avail_eval_func IN VARCHAR2,
                        p_timezone IN VARCHAR2,
                        p_perfmetrics IN AGGSERVICE_METRIC_ARRAY,
                        p_usagemetrics IN AGGSERVICE_METRIC_ARRAY,
                        p_businessmetrics IN AGGSERVICE_METRIC_ARRAY,
                        p_mode IN VARCHAR2) IS
l_avail_eval_func VARCHAR(100);
l_target_guid RAW(16);

BEGIN

    IF p_mode = 'CREATE'
    THEN
        add_aggservice(p_aggsvc_name, p_target_type, p_subservices, 
           p_avail_eval_func, p_timezone, p_perfmetrics, p_usagemetrics);
        if (emdw_log.p_is_debug_set) THEN
            emdw_log.debug('create aggregate service: ' || p_aggsvc_name, MODULE_NAME);
        end if; 
    ELSE
        if (emdw_log.p_is_debug_set) THEN
            emdw_log.debug('edit aggregate service: ' || p_aggsvc_name, MODULE_NAME);
        end if;

        modify_aggservice(p_aggsvc_name, p_target_type, p_subservices, 
            p_avail_eval_func, p_timezone, p_perfmetrics, p_usagemetrics,
            p_businessmetrics);

    END IF;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX 
      THEN  
        RAISE mgmt_global.target_already_exists;

    
END SAVE_AGGSERVICE;

PROCEDURE ADD_AGGSERVICE(p_aggsvc_name IN VARCHAR2,
                        p_target_type IN VARCHAR2,
                        p_subservices IN SMP_EMD_NVPAIR_ARRAY,
                        p_avail_eval_func IN VARCHAR2,
                        p_timezone IN VARCHAR2,
                        p_perfmetrics IN AGGSERVICE_METRIC_ARRAY,
                        p_usagemetrics IN AGGSERVICE_METRIC_ARRAY) IS
l_avail_eval_func VARCHAR(100);
l_perf_metric AGGSERVICE_METRIC;
l_perf_thresholds MGMT_THRESHOLDS;
l_usage_metric AGGSERVICE_METRIC;
l_usage_thresholds MGMT_THRESHOLDS;
l_target_guid RAW(16);

BEGIN
    -- TODO: include timezone
    IF p_timezone IS NOT NULL
    THEN
      MGMT_SERVICE.create_aggregate_service(p_aggsvc_name, 
                                          p_target_type,
                                          p_subservices,
                                          p_avail_eval_func,
                                          p_timezone);
    ELSE
      MGMT_SERVICE.create_aggregate_service(p_aggsvc_name,  
                                          p_target_type,
                                          p_subservices,
                                          p_avail_eval_func);
    END IF;

    SELECT target_guid INTO l_target_guid
     FROM mgmt_targets
     WHERE target_name = p_aggsvc_name AND target_type = p_target_type;

    -- add performance metrics
    IF p_perfmetrics IS NOT NULL THEN
        FOR i IN p_perfmetrics.FIRST .. p_perfmetrics.LAST
        LOOP
           l_perf_metric := p_perfmetrics(i);
           IF l_perf_metric IS NOT NULL THEN
               BEGIN
                  EM_REP_METRIC.add_repo_metric_collection(
                        MGMT_METRIC_INSTANCE.NEW(
                            p_aggsvc_name, 
                            p_target_type,
                            MGMT_SERVICE.G_PERFORMANCE_METRIC_NAME,
                            MGMT_SERVICE.G_PERFORMANCE_VALUE_COL_NAME,
                            l_perf_metric.coll_name), 
                            l_perf_metric.eval_function,
                            l_perf_metric.component_metrics); 
                  
                  -- if this metric is used in homepage chart
                  IF (l_perf_metric.show_in_home_page = 'true')
                  THEN
                    MGMT_SERVICE.set_homepage_perf_chart(
                        p_aggsvc_name,
                        p_target_type,
                        MGMT_SERVICE.G_PERFORMANCE_METRIC_NAME,
                        MGMT_SERVICE.G_PERFORMANCE_VALUE_COL_NAME,
                        l_perf_metric.coll_name);                      
                  END IF;

                  set_thresholds(p_aggsvc_name, p_target_type,
                      l_perf_metric);
               END;
           END IF; 
        END LOOP;
    END IF; 
 
   
    -- add usage metrics
    IF p_usagemetrics IS NOT NULL THEN
        FOR i IN p_usagemetrics.FIRST .. p_usagemetrics.LAST
        LOOP
           l_usage_metric := p_usagemetrics(i);
           IF l_usage_metric IS NOT NULL THEN
               BEGIN
                  EM_REP_METRIC.add_repo_metric_collection(
                        MGMT_METRIC_INSTANCE.NEW(
                            p_aggsvc_name,
                            p_target_type,
                            MGMT_SERVICE.G_USAGE_METRIC_NAME,
                            MGMT_SERVICE.G_USAGE_VALUE_COL_NAME,
                            l_usage_metric.coll_name), 
                            l_usage_metric.eval_function,
                            l_usage_metric.component_metrics);

                  IF (l_usage_metric.show_in_home_page = 'true')
                  THEN
                    MGMT_SERVICE.set_homepage_usage_chart(
                        p_aggsvc_name,
                        p_target_type,
                        MGMT_SERVICE.G_USAGE_METRIC_NAME,
                        MGMT_SERVICE.G_USAGE_VALUE_COL_NAME,
                        l_usage_metric.coll_name);                      
                  END IF;

                  -- sets thresholds
                  set_thresholds(p_aggsvc_name, p_target_type,
                      l_usage_metric);
               END;
           END IF;
        END LOOP;
    END IF;
END ADD_AGGSERVICE;


PROCEDURE MODIFY_AGGSERVICE(p_aggsvc_name IN VARCHAR2,
                        p_target_type IN VARCHAR2,
                        p_subservices IN SMP_EMD_NVPAIR_ARRAY,
                        p_avail_eval_func IN VARCHAR2,
                        p_timezone IN VARCHAR2,
                        p_perfmetrics IN AGGSERVICE_METRIC_ARRAY,
                        p_usagemetrics IN AGGSERVICE_METRIC_ARRAY,
                        p_businessmetrics IN AGGSERVICE_METRIC_ARRAY) IS
l_target_guid RAW(16);
l_delete_subservices SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
l_add_subservices SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
l_bus_sessions SMP_EMD_STRING_ARRAY;

BEGIN
    SELECT target_guid INTO l_target_guid
     FROM mgmt_targets
     WHERE target_name = p_aggsvc_name AND target_type = p_target_type;

    -- gets the subservices list to be deleted
    SELECT SMP_EMD_NVPAIR(tgt.target_name, tgt.target_type)
      BULK COLLECT INTO l_delete_subservices
      FROM mgmt_target_assocs a, 
           mgmt_targets tgt
     WHERE a.source_target_guid = l_target_guid
       AND a.assoc_target_guid NOT IN
           ( SELECT target_guid
               FROM mgmt_targets t,
                    TABLE(CAST(p_subservices AS SMP_EMD_NVPAIR_ARRAY)) m
              WHERE m.name = t.target_name
                AND m.value = t.target_type )
       AND a.assoc_target_guid = tgt.target_guid
       AND a.assoc_guid = mgmt_assoc.g_contains_guid;
    
    if (emdw_log.p_is_debug_set) THEN
	emdw_log.debug('aggregate_service : deleted list length : ' || 
	    l_delete_subservices.COUNT || ' p_subservices : ' || p_subservices.COUNT, MODULE_NAME);
    end if;

    -- gets the subservices list to be added
    SELECT SMP_EMD_NVPAIR(tgt.target_name, tgt.target_type)
      BULK COLLECT INTO l_add_subservices
      FROM mgmt_targets tgt,
           ( SELECT target_guid
              FROM mgmt_targets t,
                   TABLE(CAST(p_subservices AS SMP_EMD_NVPAIR_ARRAY)) s
              WHERE s.name = t.target_name 
                AND s.value = t.target_type) m 
      WHERE m.target_guid = tgt.target_guid
        AND m.target_guid NOT IN 
            ( SELECT assoc_target_guid 
                FROM mgmt_target_assocs
               WHERE source_target_guid = l_target_guid 
                 AND assoc_guid = mgmt_assoc.g_contains_guid);   

    if (emdw_log.p_is_debug_set) THEN
        emdw_log.debug('aggregate_service : add list length : ' ||
            l_add_subservices.COUNT || ' p_subservices : ' || p_subservices.COUNT, MODULE_NAME);
    end if;

    MGMT_SERVICE.modify_aggregate_service(p_aggsvc_name, 
                                        p_target_type,
                                        l_add_subservices,
                                        l_delete_subservices,
                                        p_avail_eval_func);

    -- update metric collections
    MODIFY_AGGSERVICE_METRICS(p_aggsvc_name, p_target_type, p_perfmetrics,
                           l_target_guid,
                           mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE,
                           mgmt_service.G_PERFORMANCE_METRIC_NAME,
                           mgmt_service.G_PERFORMANCE_VALUE_COL_NAME);
    MODIFY_AGGSERVICE_METRICS(p_aggsvc_name, p_target_type, p_usagemetrics,
                           l_target_guid,
                           mgmt_global.G_SERVICE_CATEGORY_USAGE,
                           mgmt_service.G_USAGE_METRIC_NAME,
                           mgmt_service.G_USAGE_VALUE_COL_NAME);
    MODIFY_AGGSERVICE_METRICS(p_aggsvc_name, p_target_type, p_businessmetrics,
                           l_target_guid,
                           mgmt_global.G_SERVICE_CATEGORY_BUSINESS,
                           mgmt_service.G_BUSINESS_METRIC_NAME,
                           mgmt_service.G_BUSINESS_VALUE_COL_NAME);

    IF ( gensvc.has_promoted_metrics (p_aggsvc_name, 
                               p_target_type, 
                               mgmt_global.G_SERVICE_CATEGORY_BUSINESS)) THEN
                                       
      IF (NOT em_bam_service.is_business_data_enabled(p_target_name => p_aggsvc_name,
                                                      p_target_type => p_target_type)) THEN
        em_bam_service.enable_business_data(p_target_name => p_aggsvc_name, 
                                            p_target_type => p_target_type);
      END IF;
    ELSE
      IF (em_bam_service.is_business_data_enabled(p_target_name => p_aggsvc_name,
                                                  p_target_type => p_target_type)) THEN
        l_bus_sessions := em_bam_service.get_target_datasessions(p_aggsvc_name, p_target_type);
        IF (l_bus_sessions IS NULL) OR (l_bus_sessions.COUNT = 0) THEN
          em_bam_service.disable_business_data(p_target_name => p_aggsvc_name, 
                                               p_target_type => p_target_type);
        END IF;
      END IF;
    END IF;
END MODIFY_AGGSERVICE;

PROCEDURE GET_AGGSERVICE(p_aggsvc_name IN VARCHAR2,
            p_target_type IN VARCHAR2,
            p_subservices OUT SMP_EMD_NVPAIR_ARRAY,
            p_avail_eval_func OUT VARCHAR2,
            p_timezone OUT VARCHAR2,
            p_perfmetrics OUT AGGSERVICE_METRIC_ARRAY,
            p_usagemetrics OUT AGGSERVICE_METRIC_ARRAY,
            p_businessmetrics OUT AGGSERVICE_METRIC_ARRAY,
            p_timezone_list OUT SMP_EMD_STRING_ARRAY) IS
BEGIN
--  this is for edit mode
    MGMT_SERVICE.get_aggregate_service_info(p_aggsvc_name, 
        p_target_type,
        p_subservices,
        p_avail_eval_func,
        p_timezone);

    p_perfmetrics := GET_SERVICE_METRICS(p_aggsvc_name, p_target_type, 'perfType', EDIT_MODE);
    p_usagemetrics := GET_SERVICE_METRICS(p_aggsvc_name, p_target_type, 'usageType', EDIT_MODE);
    p_businessmetrics := GET_SERVICE_METRICS(p_aggsvc_name, p_target_type, 'businessType', EDIT_MODE);

    SELECT timezone_region BULK COLLECT INTO p_timezone_list
        FROM mgmt_targets
        WHERE target_guid IN
            (SELECT t.target_guid 
                FROM TABLE(CAST(p_subservices AS SMP_EMD_NVPAIR_ARRAY)) p,
                    mgmt_targets t
                WHERE t.target_name = p.name
                  AND t.target_type = p.value);
        
END GET_AGGSERVICE;

PROCEDURE GET_SERVICES_FOR_SYSTEM(
    p_target_name IN  VARCHAR2,
    p_target_type IN  VARCHAR2,
    p_services    OUT CURSOR_TYPE) 
IS
     l_target_guid  mgmt_targets.target_guid%TYPE;
BEGIN
        
    l_target_guid := mgmt_target.get_target_guid( 
                         p_target_name,
                         p_target_type);

    -- this query is adapted from the services folder query in 
    -- emd_mntr.get_targets_summary6
    -- todo:a separate proc will be provided to obtain serviceInfo for a 
    -- given service
    OPEN p_services  FOR
    WITH tgt AS
           (
           SELECT target_name as display_name, target_type, target_guid,
                  target_name , type_display_name,
                  category_prop_1, category_prop_2, category_prop_3, 
                  category_prop_4, category_prop_5, type_meta_ver
             FROM mgmt_targets
            WHERE target_guid IN
                      ( SELECT a.source_target_guid from 
                               mgmt_target_assocs a, 
                               mgmt_targets t 
                        WHERE a.assoc_target_guid = l_target_guid
                        AND   a.assoc_guid = mgmt_assoc.g_runs_on_guid
                        AND   t.target_guid = a.source_target_guid
                      )
           ),
         met AS
           (
           SELECT  
m.metric_guid, m.target_type, m.type_meta_ver, m.category_prop_1, 
m.category_prop_2, m.category_prop_3, m.category_prop_4, m.category_prop_5,
c.category_name
             FROM mgmt_metrics m,
                  mgmt_category_map c
            WHERE c.object_guid = m.metric_guid
              AND c.target_type = m.target_type
              AND c.type_meta_ver = m.type_meta_ver
              AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
              AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE
              AND c.category_name IN (mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE,mgmt_global.G_SERVICE_CATEGORY_USAGE)
           )
      SELECT /*+ INDEX(tgt) INDEX(avail) */
             tgt.target_name,
             tgt.target_type,
             tgt.target_name as display_name,
             tgt.type_display_name,
             NVL(avail.current_status,MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status,
             tgt.target_guid,
             NVL(allperfusage.perf_warning_count,0) AS perf_warning_count,
             NVL(allperfusage.perf_critical_count,0) AS perf_critical_count,
             NVL(allperfusage.usage_warning_count,0) AS usage_warning_count,
             NVL(allperfusage.usage_critical_count,0) AS usage_critical_count,             
             NVL(allpolicy.information_count, 0) AS policy_information_count,
             NVL(allpolicy.warning_count, 0) AS policy_warning_count,
             NVL(allpolicy.critical_count, 0) AS policy_critical_count
        FROM tgt,
             mgmt_current_availability avail,
             (SELECT 
                     SUM(DECODE(met.category_name||severity_code,mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE||MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) perf_warning_count,
                     SUM(DECODE(met.category_name||severity_code,mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE||MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) perf_critical_count,
                     SUM(DECODE(met.category_name||severity_code,mgmt_global.G_SERVICE_CATEGORY_USAGE||MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) usage_warning_count,
                     SUM(DECODE(met.category_name||severity_code,mgmt_global.G_SERVICE_CATEGORY_USAGE||MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) usage_critical_count,
                     s.target_guid tguid
                FROM 
                     tgt,
                     mgmt_current_severity s,
                     met
                WHERE met.metric_guid = s.metric_guid
                  AND tgt.target_type   = met.target_type
                  AND tgt.type_meta_ver = met.type_meta_ver
                  AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ')
                  AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ')
                  AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ')
                  AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ')
                  AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ')
                  AND tgt.target_guid = s.target_guid
                  AND met.category_name IN (mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE, mgmt_global.G_SERVICE_CATEGORY_USAGE)
               GROUP BY s.target_guid ) allperfusage,
             (SELECT 
                     SUM(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,1,0)) information_count,
                     SUM(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                     SUM(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                     c.target_guid tguid
                FROM 
                      tgt,
                      mgmt_current_violation c
                WHERE c.violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY AND
                      tgt.target_guid  = c.target_guid AND
                      (c.exempt_code = 0 OR
                      (c.exempt_code = 2 and c.exempt_until <= SYSDATE))                
               GROUP BY c.target_guid ) allpolicy
       WHERE tgt.target_guid = avail.target_guid (+)
         AND tgt.target_guid = allperfusage.tguid (+)
         AND tgt.target_guid = allpolicy.tguid(+)
        ORDER BY tgt.target_name;

END GET_SERVICES_FOR_SYSTEM;

PROCEDURE GET_SERVICE_METRICS(p_target_name IN VARCHAR2,
            p_target_type  IN VARCHAR2,
            p_metric_type  IN VARCHAR2,
            p_metrics_list OUT AGGSERVICE_METRIC_ARRAY) IS
BEGIN
   p_metrics_list := get_service_metrics(p_target_name, p_target_type, 
                                        p_metric_type);
END GET_SERVICE_METRICS;

FUNCTION GET_SERVICE_METRICS(p_target_name IN VARCHAR2,
            p_target_type IN VARCHAR2,
            p_metric_type IN VARCHAR2,
            p_mode        IN VARCHAR2 DEFAULT NULL)
     RETURN AGGSERVICE_METRIC_ARRAY IS
p_metrics_list     AGGSERVICE_METRIC_ARRAY;
l_target_guid      RAW(16);
l_comp_metrics     MGMT_METRIC_INSTANCE_ARRAY := MGMT_METRIC_INSTANCE_ARRAY();
l_service_category VARCHAR2(32);
BEGIN 
    p_metrics_list := AGGSERVICE_METRIC_ARRAY();

    IF (p_metric_type IS NOT NULL)
    THEN
        IF (p_metric_type='perfType')
        THEN
            l_service_category := MGMT_GLOBAL.G_SERVICE_CATEGORY_PERFORMANCE;
        ELSIF (p_metric_type='usageType')
        THEN
            l_service_category := MGMT_GLOBAL.G_SERVICE_CATEGORY_USAGE;
        ELSIF (p_metric_type='businessType')
        THEN
            l_service_category := MGMT_GLOBAL.G_SERVICE_CATEGORY_BUSINESS;
        END IF;
    END IF;

    SELECT target_guid INTO l_target_guid
      FROM mgmt_targets
     WHERE target_name = p_target_name AND target_type = p_target_type;
     
    IF p_mode = EDIT_MODE
    THEN
    FOR sub_rec IN metric_cur_edit(l_target_guid, p_target_type, l_service_category)

    LOOP
        l_comp_metrics := MGMT_METRIC_INSTANCE_ARRAY(); 
        FOR sub_rec1 IN dep_metric_cur(sub_rec.coll_name, l_service_category, l_target_guid, sub_rec.metric_guid)
        LOOP
          l_comp_metrics.extend;
          l_comp_metrics(dep_metric_cur%ROWCOUNT) :=
            MGMT_METRIC_INSTANCE(sub_rec1.target_name, sub_rec1.target_type,
                sub_rec1.metric_name, sub_rec1.metric_column, sub_rec1.dep_key_value);
        END LOOP;
        
        p_metrics_list.extend;
        p_metrics_list(metric_cur_edit%ROWCOUNT) :=
            AGGSERVICE_METRIC(sub_rec.metric_name, sub_rec.metric_column, 
                sub_rec.coll_name, l_comp_metrics,
                sub_rec.warning_threshold, sub_rec.critical_threshold,
                sub_rec.warning_operator, 'false', sub_rec.eval_func);     
    END LOOP;
    ELSE
    FOR sub_rec IN metric_cur(l_target_guid, p_target_type, l_service_category)
    LOOP
        l_comp_metrics := MGMT_METRIC_INSTANCE_ARRAY(); 
        p_metrics_list.extend;
        p_metrics_list(metric_cur%ROWCOUNT) :=
            AGGSERVICE_METRIC(sub_rec.metric_name, sub_rec.metric_column, 
                sub_rec.coll_name, l_comp_metrics,
                sub_rec.warning_threshold, sub_rec.critical_threshold,
                sub_rec.warning_operator, 'false', '');     
    END LOOP;
    END IF;
  RETURN p_metrics_list;
END GET_SERVICE_METRICS;

PROCEDURE GET_SERVICE_GENERAL_INFO(p_target_name IN VARCHAR2,
        p_target_type IN VARCHAR2,
        gen_info_out OUT CURSOR_TYPE) IS

l_target_guid RAW(16);
BEGIN
  l_target_guid := mgmt_target.get_target_guid(p_target_name, p_target_type);

  OPEN gen_info_out FOR
    SELECT
      NVL(mgmt_target.get_avail_current_status(tgt.target_guid), MGMT_GLOBAL.G_STATUS_UNKNOWN) AS cur_status,
      mgmt_target.get_current_status_timestamp(l_target_guid) AS since_timestamp, 
      mgmt_target.get_avail_pct_value(tgt.target_guid, 1) AS avail_pct,
      mgmt_avail.get_target_end_marker_time(l_target_guid) AS avail_delay,
      NVL(perfsev.sev_code, MGMT_GLOBAL.G_SEVERITY_CLEAR) AS perf_sev_code,
      NVL(usagesev.sev_code, MGMT_GLOBAL.G_SEVERITY_CLEAR) AS usage_sev_code,
      NVL(businesssev.sev_code, MGMT_GLOBAL.G_SEVERITY_CLEAR) AS business_sev_code,
      NVL(allpolicy.information_count, 0) AS pol_info_count,
      NVL(allpolicy.warning_count, 0) AS pol_warning_count,
      NVL(allpolicy.critical_count, 0) AS pol_critical_count
    FROM mgmt_targets tgt,
         (SELECT MAX(severity_code) AS sev_code
            FROM mgmt_current_severity s,
                 mgmt_metrics m 
           WHERE m.metric_guid IN
              (SELECT metric_guid
                FROM mgmt_metrics m,
                     mgmt_category_map c
               WHERE  c.object_guid = m.metric_guid
                 AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
                 AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE
                 AND c.category_name = mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE)
             AND s.target_guid = l_target_guid
             AND s.metric_guid = m.metric_guid) perfsev,
         (SELECT MAX(severity_code) AS sev_code
            FROM mgmt_current_severity s,
                 mgmt_metrics m 
           WHERE m.metric_guid IN
              (SELECT metric_guid
                FROM mgmt_metrics m,
                     mgmt_category_map c
               WHERE  c.object_guid = m.metric_guid
                 AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
                 AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE
                 AND c.category_name = mgmt_global.G_SERVICE_CATEGORY_USAGE)
            AND s.target_guid = l_target_guid
            AND s.metric_guid = m.metric_guid) usagesev,
         (SELECT MAX(severity_code) AS sev_code
            FROM mgmt_current_severity s,
                 mgmt_metrics m 
           WHERE m.metric_guid IN
              (SELECT metric_guid
                FROM mgmt_metrics m,
                     mgmt_category_map c
               WHERE  c.object_guid = m.metric_guid
                 AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
                 AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE
                 AND c.category_name = mgmt_global.G_SERVICE_CATEGORY_BUSINESS)
            AND s.target_guid = l_target_guid
            AND s.metric_guid = m.metric_guid) businesssev,
        (SELECT SUM(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,1,0)) information_count,
                SUM(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                SUM(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count
                FROM mgmt_current_violation
                WHERE violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY 
                  AND
                      (exempt_code = 0 OR
                       (exempt_code = 2 and exempt_until <= SYSDATE)) 
                  AND target_guid = l_target_guid) allpolicy  
    WHERE tgt.target_guid = l_target_guid;
	
END GET_SERVICE_GENERAL_INFO;

PROCEDURE GET_SERVICE_ALERTS_INFO(p_target_name IN VARCHAR2,
    p_target_type IN VARCHAR2,
    p_alerts_category IN VARCHAR2 DEFAULT NULL,
    alerts_info_out OUT CURSOR_TYPE) IS
    
l_target_guid RAW(16);

BEGIN
  l_target_guid := mgmt_target.get_target_guid(p_target_name, p_target_type);

  IF (l_target_guid IS NULL) 
  THEN
        raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
                            MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR_M ||
                            p_target_name || ' (' || p_target_type ||
                            ')');
  END IF;
  IF (p_alerts_category IS NULL OR p_alerts_category = 'ALL') THEN
  OPEN alerts_info_out FOR
    SELECT tgt.target_name,
           tgt.target_type,
           tgt.type_display_name,
           sev.severity_code,
           sev.collection_timestamp,
           sev.message,
           sev.message_nlsid, 
           sev.message_params,
           sev.severity_guid
      FROM mgmt_targets tgt,
           (SELECT assoc_target_guid as guid
              FROM mgmt_target_assocs
             WHERE source_target_guid = l_target_guid
               AND assoc_guid = mgmt_assoc.g_contains_guid
              UNION
                 SELECT l_target_guid FROM dual) all_guids,
            mgmt_metrics m,
            mgmt_current_severity sev
      WHERE tgt.target_guid = all_guids.guid
        AND tgt.target_type   = m.target_type
        AND tgt.type_meta_ver = m.type_meta_ver
        AND (m.category_prop_1 = tgt.category_prop_1 OR m.category_prop_1 = ' ')
        AND (m.category_prop_2 = tgt.category_prop_2 OR m.category_prop_2 = ' ')
        AND (m.category_prop_3 = tgt.category_prop_3 OR m.category_prop_3 = ' ')
        AND (m.category_prop_4 = tgt.category_prop_4 OR m.category_prop_4 = ' ')
        AND (m.category_prop_5 = tgt.category_prop_5 OR m.category_prop_5 = ' ')
        AND sev.target_guid = tgt.target_guid
        AND sev.metric_guid = m.metric_guid
        AND m.remote != 1
        AND m.metric_name != 'test_response';
  ELSE
  OPEN alerts_info_out FOR
    SELECT tgt.target_name,
           tgt.target_type,
           tgt.type_display_name,
           sev.severity_code,
           sev.collection_timestamp,
           sev.message,
           sev.message_nlsid, 
           sev.message_params,
           sev.severity_guid
      FROM mgmt_targets tgt,
           (SELECT assoc_target_guid as guid
              FROM mgmt_target_assocs
             WHERE source_target_guid = l_target_guid
               AND assoc_guid = mgmt_assoc.g_contains_guid
              UNION
                 SELECT l_target_guid FROM dual) all_guids,
            mgmt_metrics m,
            mgmt_category_map c,
            mgmt_current_severity sev
      WHERE tgt.target_guid = all_guids.guid
        AND tgt.target_type   = m.target_type
        AND tgt.target_type = c.target_type
        AND tgt.type_meta_ver = m.type_meta_ver
        AND tgt.type_meta_ver = c.type_meta_ver
        AND (m.category_prop_1 = tgt.category_prop_1 OR m.category_prop_1 = ' ')
        AND (m.category_prop_2 = tgt.category_prop_2 OR m.category_prop_2 = ' ')
        AND (m.category_prop_3 = tgt.category_prop_3 OR m.category_prop_3 = ' ')
        AND (m.category_prop_4 = tgt.category_prop_4 OR m.category_prop_4 = ' ')
        AND (m.category_prop_5 = tgt.category_prop_5 OR m.category_prop_5 = ' ')
        AND m.metric_guid = c.object_guid
        AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
        AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE
        AND (c.category_name = p_alerts_category)
        AND sev.target_guid = tgt.target_guid
        AND sev.metric_guid = m.metric_guid;    
  END IF;

END GET_SERVICE_ALERTS_INFO;

PROCEDURE GET_SUBSERVICES_LIST (p_target_name IN VARCHAR2,
    p_target_type IN VARCHAR2,
    subservices_list_out OUT CURSOR_TYPE) IS
    
l_target_guid RAW(16);
l_targets SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();

BEGIN
  l_target_guid := mgmt_target.get_target_guid(p_target_name, p_target_type);

  IF (l_target_guid IS NULL) 
  THEN
        raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
                            MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR_M ||
                            p_target_name || ' (' || p_target_type ||
                            ')');
  END IF;

  SELECT SMP_EMD_NVPAIR(target_name, target_type) BULK COLLECT
    INTO l_targets
    FROM mgmt_targets 
    WHERE target_guid IN
      (SELECT assoc_target_guid
         FROM mgmt_target_assocs
        WHERE source_target_guid = l_target_guid
          AND assoc_guid = mgmt_assoc.g_contains_guid
        UNION
          SELECT l_target_guid FROM dual);
   subservices_list_out := GET_SERVICES_INFO(l_targets);       
    
END GET_SUBSERVICES_LIST;
    
FUNCTION GET_SERVICES_INFO(p_target_list IN SMP_EMD_NVPAIR_ARRAY)
    RETURN CURSOR_TYPE IS

l_service_info_out CURSOR_TYPE;
BEGIN
  OPEN l_service_info_out FOR
      SELECT /*+ INDEX(tgt) INDEX(avail) */
             tgt.target_name,
             tgt.target_type,
             tgt.target_name as display_name,
             tgt.type_display_name,
             NVL(avail.current_status,MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status,
             tgtProp.property_value is_aggregate_service,
             tgt.target_guid,
             NVL(allperf.warning_count,0) AS perf_warning_count,
             NVL(allperf.critical_count,0) AS perf_critical_count,
             NVL(allusage.warning_count,0) AS usage_warning_count,
             NVL(allusage.critical_count,0) AS usage_critical_count,             
             NVL(allpolicy.information_count, 0) AS policy_information_count,
             NVL(allpolicy.warning_count, 0) AS policy_warning_count,
             NVL(allpolicy.critical_count, 0) AS policy_critical_count,
             sys.target_name AS system_target_name,
             sys.target_type AS system_target_type,
             NVL(sysavail.up_count, 0) AS sys_up_count,
             NVL(sysavail.down_count, 0) AS sys_down_count,
             NVL(sysalert.warning_count, 0) AS sys_warning_count,
             NVL(sysalert.critical_count, 0) AS sys_critical_count
        FROM (SELECT target_name, target_type, target_guid,
                      target_name as display_name, type_display_name
                 FROM mgmt_targets t,
                      TABLE(CAST(p_target_list AS SMP_EMD_NVPAIR_ARRAY)) p
                 WHERE t.target_name = p.name
                   AND t.target_type = p.value) tgt,
             (SELECT property_value, target_type
                FROM mgmt_type_properties
                WHERE property_name = 'is_aggregate_service') tgtProp,
             mgmt_current_availability avail,
             (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                     SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                     t.target_guid tguid
	            FROM mgmt_current_severity s,
                     mgmt_metrics m,
					 mgmt_targets t,
					 mgmt_category_map c
                WHERE m.metric_guid = s.metric_guid
			  	  AND t.target_guid = s.target_guid
				  AND t.target_type   = m.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 = ' ') 		
				  AND m.metric_guid = c.object_guid
        		  AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
        		  AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE
        		  AND c.category_name = mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE
                  GROUP BY t.target_guid) allperf,				  
             (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                     SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                     t.target_guid tguid
	            FROM mgmt_current_severity s,
                     mgmt_metrics m,
					 mgmt_targets t,
					 mgmt_category_map c
                WHERE m.metric_guid = s.metric_guid
			  	  AND t.target_guid = s.target_guid
				  AND t.target_type   = m.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 = ' ') 		
				  AND m.metric_guid = c.object_guid
        		  AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
        		  AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE
        		  AND c.category_name = mgmt_global.G_SERVICE_CATEGORY_USAGE
                  GROUP BY t.target_guid) allusage,				  
             (SELECT SUM(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,1,0)) information_count,
                     SUM(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                     SUM(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                     target_guid tguid
                FROM mgmt_current_violation
                WHERE violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY AND
                      (exempt_code = 0 OR
                       (exempt_code = 2 and exempt_until <= SYSDATE))                
               GROUP BY target_guid ) allpolicy,  
             (SELECT t.target_name, t.target_type, a.source_target_guid tguid
                FROM mgmt_target_assocs a, 
                     mgmt_targets t
               WHERE a.assoc_guid = MGMT_ASSOC.g_runs_on_guid
                 AND t.target_guid = a.assoc_target_guid) sys,
             (SELECT SUM(DECODE(current_status, MGMT_GLOBAL.G_STATUS_UP,1,0)) up_count,
                     SUM(DECODE(current_status, MGMT_GLOBAL.G_STATUS_DOWN,1,0)) down_count,
                     a.source_target_guid tguid
                FROM mgmt_current_availability c,
                     mgmt_target_assocs a
               WHERE c.target_guid = a.assoc_target_guid
                 AND a.assoc_guid = MGMT_ASSOC.g_depends_on_guid
                GROUP BY a.source_target_guid) sysavail,
             (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                     SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                     a.source_target_guid tguid
               FROM mgmt_current_severity s,
                     mgmt_target_assocs a
                WHERE a.assoc_guid = MGMT_ASSOC.g_depends_on_guid
                  AND s.target_guid = a.assoc_target_guid
                GROUP BY a.source_target_guid) sysalert
       WHERE tgt.target_guid = avail.target_guid (+)
         AND tgt.target_guid = allperf.tguid (+)
         AND tgt.target_guid = allusage.tguid (+)
         AND tgt.target_guid = allpolicy.tguid(+)
         AND tgt.target_guid = sys.tguid(+)
         AND tgt.target_guid = sysavail.tguid(+)    
         AND tgt.target_guid = sysalert.tguid(+)
         AND tgt.target_type = tgtProp.target_type(+)
        ORDER BY tgt.target_name;

  RETURN l_service_info_out;
END GET_SERVICES_INFO;

PROCEDURE GET_DEFAULT_HOMEPAGE_METRICS(p_target_name IN VARCHAR2,
    p_target_type IN VARCHAR2,
    perf_metric_info OUT CURSOR_TYPE,
    usage_metric_info OUT CURSOR_TYPE) IS
    
l_target_guid RAW(16);
l_metric_name VARCHAR2(128);
l_metric_column VARCHAR2(128);
l_key_value VARCHAR2(128);

BEGIN
  l_target_guid := mgmt_target.get_target_guid(p_target_name, p_target_type);

  IF (l_target_guid IS NULL) 
  THEN
        raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
                            MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR_M ||
                            p_target_name || ' (' || p_target_type ||
                            ')');
  END IF;

  MGMT_SERVICE.get_homepage_perf_chart(p_target_name, p_target_type,
    l_metric_name, l_metric_column, l_key_value);
  OPEN perf_metric_info FOR
    SELECT 
      l_metric_name metric_name,
      l_metric_column metric_column,
      l_key_value  key_value
    FROM dual;
    
  MGMT_SERVICE.get_homepage_usage_chart(p_target_name, p_target_type,
    l_metric_name, l_metric_column, l_key_value);

  OPEN usage_metric_info FOR
    SELECT 
      l_metric_name metric_name,
      l_metric_column metric_column,
      l_key_value key_value
    FROM dual;
    
END GET_DEFAULT_HOMEPAGE_METRICS;


PROCEDURE GET_METRICS_LIST (p_target_name IN VARCHAR2,
    p_target_type IN VARCHAR2,
    p_metric_category IN VARCHAR2,
    metrics_list_out OUT CURSOR_TYPE,
    target_sysdate OUT DATE)
IS

l_target_guid RAW(16);
l_type_meta_ver VARCHAR2(8);

BEGIN

  l_target_guid := mgmt_target.get_target_guid(p_target_name, p_target_type);

  IF (l_target_guid IS NULL) 
  THEN
        raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
                            MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR_M ||
                            p_target_name || ' (' || p_target_type ||
                            ')');
  END IF;

  target_sysdate := mgmt_target.sysdate_target(l_target_guid);

  if (emdw_log.p_is_debug_set) THEN
      emdw_log.debug('get_metrics_list:' || l_target_guid || ' : ' || p_metric_category, MODULE_NAME);
  end if;

    OPEN metrics_list_out FOR
     SELECT met.metric_guid, met.metric_name, met.metric_column, 
            met.metric_label_nlsid,
            met.column_label_nlsid,
            '' as coll_name, met.target_guid,
            th.warning_threshold,
            th.critical_threshold,
            met.column_label
       FROM (select m.metric_guid, m.metric_name, 
                    m.metric_column, m.metric_label_nlsid,
	            m.column_label_nlsid, t.target_guid,
                    m.column_label 
               from mgmt_targets t,
            	    mgmt_metrics m,
            	    mgmt_category_map map
              WHERE t.target_guid = hextoraw(l_target_guid)
                AND t.target_type = m.target_type
                AND t.type_meta_ver = m.type_meta_ver
                AND t.target_type = map.target_type
                AND t.type_meta_ver = map.type_meta_ver
                AND (t.category_prop_1 = m.category_prop_1
                     OR m.category_prop_1 = ' ')
                AND (t.category_prop_2 = m.category_prop_2
                     OR m.category_prop_2 = ' ')
                AND (t.category_prop_3 = m.category_prop_3
                     OR m.category_prop_3 = ' ')
                AND (t.category_prop_4 = m.category_prop_4
                     OR m.category_prop_4 = ' ')
                AND (t.category_prop_5 = m.category_prop_5
                     OR m.category_prop_5 = ' ')
                AND m.is_transposed = 0
                AND m.metric_type not in (MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
                                          MGMT_GLOBAL.G_METRIC_TYPE_REPOS_TABLE)
                AND map.object_guid = m.metric_guid
                AND map.object_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC
                AND map.class_name = MGMT_GLOBAL.G_CATEGORY_CLASS_SERVICE
                AND map.category_name = p_metric_category) met,
             mgmt_metric_thresholds th
       WHERE met.target_guid = th.target_guid (+) 
         AND met.metric_guid = th.metric_guid (+)
     UNION ALL
     SELECT m.metric_guid, m.metric_name, m.metric_column,
            m.metric_label_nlsid, m.column_label_nlsid,
            m.coll_name, m.target_guid,
            th.warning_threshold,
            th.critical_threshold,
            m.column_label
       FROM mgmt_metric_thresholds th,
            (SELECT t.target_guid,
                   cm.metric_name,
                    cm.metric_column,
                    cm.metric_guid,
                    c.coll_name,
                    cm.metric_label_nlsid,
                    cm.column_label_nlsid,
                    cm.column_label
               FROM mgmt_metrics cm,
                    mgmt_metrics tm,
                    mgmt_category_map map,
                    mgmt_metric_collections c,
                    mgmt_targets t
              WHERE t.target_guid = hextoraw(l_target_guid)
                AND t.target_type = cm.target_type
                AND t.type_meta_ver = cm.type_meta_ver
                AND t.type_meta_ver = tm.type_meta_ver
                AND t.type_meta_ver = map.type_meta_ver
                AND (t.category_prop_1 = cm.category_prop_1
                 OR cm.category_prop_1 = ' ')
                AND (t.category_prop_2 = cm.category_prop_2
                 OR cm.category_prop_2 = ' ')
                AND (t.category_prop_3 = cm.category_prop_3
                 OR cm.category_prop_3 = ' ')
                AND (t.category_prop_4 = cm.category_prop_4
                 OR cm.category_prop_4 = ' ')
                AND (t.category_prop_5 = cm.category_prop_5
                 OR cm.category_prop_5 = ' ')
                AND cm.is_transposed = 1
                AND cm.key_order = 0
                AND cm.metric_column <> ' '
                AND map.object_guid = cm.metric_guid
                AND map.object_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC 
                AND map.class_name = MGMT_GLOBAL.G_CATEGORY_CLASS_SERVICE 
                AND map.category_name = p_metric_category 
                AND tm.metric_name = cm.metric_name
                AND tm.metric_column = ' '
                AND c.target_guid = hextoraw(l_target_guid)
                AND c.metric_guid = tm.metric_guid)
             m
      WHERE m.target_guid = th.target_guid (+)
        AND m.metric_guid = th.metric_guid (+)
        AND m.coll_name = th.coll_name(+);
 

END GET_METRICS_LIST;

PROCEDURE GET_AVAIL_ALL_ASSOC_TARGETS (p_target_name IN VARCHAR2,
    p_target_type IN VARCHAR2,
    top_level_target_name IN VARCHAR2,
    top_level_target_type IN VARCHAR2,
    top_level_tz IN VARCHAR2,
    start_date_in IN DATE,
    end_date_in IN DATE,
    num_of_days_in IN NUMBER,
    assoc_type_in IN VARCHAR2,
    start_time_out OUT DATE,
    end_time_out OUT DATE,
    tz_out OUT VARCHAR2,
    avail_states_self_out OUT CURSOR_TYPE,
    avail_states_assoc_out OUT CURSOR_TYPE
 ) IS

l_current_status_out NUMBER;
l_avail_pct_out NUMBER;
l_status_since_out DATE;
l_perf_state_cursor CURSOR_TYPE;
l_avail_selector VARCHAR2(64);

BEGIN
    BEGIN
      -- this function should get the top level target and pass it to gensvc_view functions
      GENSVC_VIEW.get_avail_single_target(p_target_name, p_target_type, top_level_target_name, top_level_target_type, top_level_tz,
      start_date_in, end_date_in, num_of_days_in, start_time_out, end_time_out, tz_out,
        l_current_status_out, l_avail_pct_out, l_status_since_out, avail_states_self_out, 
        l_perf_state_cursor,l_avail_selector); 
      EXCEPTION WHEN OTHERS THEN
          -- open a single row cursor 
          OPEN avail_states_self_out FOR
              SELECT t.target_guid as target_guid,
                     t.target_name as target_name,
                     t.target_type as target_type,
                     avail.current_status as current_status,
                     avail.severity_guid as severity_guid,
                     avail.current_status as historical_status,
                     avail.end_collection_timestamp as duration,
                     avail.start_collection_timestamp as start_timestamp,
                     avail.end_collection_timestamp as end_timestamp,
                     '0' as test_based,
                     '1' as comp_targets_count
                FROM mgmt_availability avail, mgmt_targets t
               WHERE t.target_name = p_target_name
                 AND t.target_type = p_target_type
                 AND t.target_guid = avail.target_guid(+);
    END;

    IF ( l_perf_state_cursor%ISOPEN ) THEN
       CLOSE l_perf_state_cursor;
    END IF;


    GENSVC_VIEW.get_avail_all_assoc_targets(p_target_name, p_target_type, top_level_target_name, top_level_target_type, top_level_tz,
      start_date_in, end_date_in, num_of_days_in, assoc_type_in, start_time_out,
      end_time_out, tz_out, avail_states_assoc_out, l_perf_state_cursor);

    IF ( l_perf_state_cursor%ISOPEN ) THEN
       CLOSE l_perf_state_cursor;
    END IF;

 
END GET_AVAIL_ALL_ASSOC_TARGETS;

PROCEDURE HAS_PERF_USAGE_BY_TYPE(p_target_type IN VARCHAR2,
        p_type_meta_ver IN VARCHAR2 DEFAULT NULL,
        perf_metrics_out OUT SMP_EMD_NVPAIR_ARRAY,
        usage_metrics_out OUT SMP_EMD_NVPAIR_ARRAY) AS
BEGIN
  BEGIN
   SELECT SMP_EMD_NVPAIR(metric_name, metric_column)
     BULK COLLECT INTO perf_metrics_out
     FROM  mgmt_metrics m,
           mgmt_category_map c
     WHERE c.target_type = p_target_type
       AND (c.type_meta_ver = p_type_meta_ver OR
            p_type_meta_ver IS NULL)
       AND c.object_guid = m.metric_guid
       AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
       AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE
       AND c.category_name = mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE
       AND m.metric_name = MGMT_SERVICE.G_PERFORMANCE_METRIC_NAME
       AND m.metric_column = MGMT_SERVICE.G_PERFORMANCE_VALUE_COL_NAME; 
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
        perf_metrics_out := NULL;
  END;

  BEGIN
   SELECT SMP_EMD_NVPAIR(metric_name, metric_column)
     BULK COLLECT INTO usage_metrics_out
     FROM  mgmt_metrics m,
           mgmt_category_map c
     WHERE c.target_type = p_target_type
       AND (c.type_meta_ver = p_type_meta_ver OR
            p_type_meta_ver IS NULL)
       AND c.object_guid = m.metric_guid
       AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
       AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE
       AND c.category_name = mgmt_global.G_SERVICE_CATEGORY_USAGE
       AND m.metric_name = MGMT_SERVICE.G_USAGE_METRIC_NAME
       AND m.metric_column = MGMT_SERVICE.G_USAGE_VALUE_COL_NAME;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
        usage_metrics_out := NULL;
  END;

END HAS_PERF_USAGE_BY_TYPE;

PROCEDURE SET_THRESHOLDS(
    p_target_name IN VARCHAR2,
    p_target_type IN VARCHAR2,
    p_metric AGGSERVICE_METRIC
)
IS
l_policy_key_val_list MGMT_POLICY_KEY_VAL_ARRAY := null;

BEGIN
  l_policy_key_val_list := MGMT_POLICY_KEY_VAL_ARRAY();
  l_policy_key_val_list.extend;

  l_policy_key_val_list(1) :=
  MGMT_POLICY_KEY_VAL.NEW(
    p_key_value => MGMT_POLICY_KEY_COL_COND_ARRAY(
      MGMT_POLICY_KEY_COL_COND.NEW(
        p_key_value => p_metric.coll_name,
        p_has_wildcard => MGMT_GLOBAL.G_FALSE
      )
    ),
    p_condition_operator => p_metric.operator,
    p_param_values => MGMT_POLICY_PARAM_VAL_ARRAY(
      MGMT_POLICY_PARAM_VAL.NEW(
        p_param_name => ' ',
        p_crit_threshold => p_metric.critical_threshold,
        p_warn_threshold => p_metric.warning_threshold)
    ));
          
    mgmt_monitoring.save_target_metric_config(p_target_type, p_target_name, 
          p_metric.metric_name, p_metric.metric_column,
          p_metric.coll_name, 1, l_policy_key_val_list );
END;

PROCEDURE GET_NON_PROMOTED_METRICS_LIST (p_target_name IN VARCHAR2,
                                 p_target_type IN VARCHAR2,
                                 p_metric_category IN VARCHAR2,
                                 metrics_list_out OUT CURSOR_TYPE)
IS
l_target_guid RAW(16);
l_key_value_count INTEGER;
l_key_values SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
BEGIN

    l_target_guid := mgmt_target.get_target_guid(p_target_name, p_target_type);

    FOR sub_rec IN metric_cur(l_target_guid, p_target_type, p_metric_category)
    LOOP
      SELECT COUNT(key_value) 
        INTO l_key_value_count
        FROM mgmt_metric_dependency
       WHERE target_guid = l_target_guid
         AND sub_rec.coll_name = key_value;

      IF (l_key_value_count = 0) THEN
        l_key_values.extend(1);
        l_key_values(l_key_values.COUNT) := sub_rec.coll_name;
      END IF;

    END LOOP;

    OPEN metrics_list_out FOR
     SELECT COLUMN_VALUE as key_value FROM TABLE(CAST(l_key_values AS SMP_EMD_STRING_ARRAY));

END GET_NON_PROMOTED_METRICS_LIST;


PROCEDURE GET_PROMOTED_METRICS_LIST (p_target_name IN VARCHAR2,
                                     p_target_type IN VARCHAR2,
                                     p_metric_category IN VARCHAR2,
                                     metrics_list_out OUT CURSOR_TYPE)
IS
BEGIN

    OPEN metrics_list_out FOR
  SELECT met_dep.key_value as key_value
    FROM mgmt_metrics met, mgmt_targets tgt, mgmt_category_map cat_map,
         mgmt_metric_dependency met_dep 
   WHERE tgt.target_name = p_target_name
     AND tgt.target_type = p_target_type
     AND cat_map.target_type = tgt.target_type
     AND (cat_map.type_meta_ver = tgt.type_meta_ver OR tgt.type_meta_ver IS NULL)
     AND cat_map.object_guid = met.metric_guid
     AND cat_map.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC
     AND cat_map.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE 
     AND cat_map.category_name = p_metric_category
     AND tgt.type_meta_ver = met.type_meta_ver
     AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ')
     AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ')
     AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ')
     AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ')
     AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ')
     AND met_dep.target_guid = tgt.target_guid
     AND met_dep.metric_guid = met.metric_guid;

END GET_PROMOTED_METRICS_LIST;


PROCEDURE ALLOW_BUSINESS_PROMOTION(p_target_name IN VARCHAR2, 
                                   p_target_type IN VARCHAR2,
                                   allow_promotion OUT INTEGER)
IS
  l_svc_id         MGMT_TARGETS.target_guid%TYPE;
  target_sysdate   DATE;
BEGIN

  allow_promotion := 1;

  IF ( p_target_name IS NULL ) OR ( p_target_type IS NULL ) THEN
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'One of Service Target name, Target type is Null');
  END IF;
  
  IF EM_BAM_SERVICE.IS_BUSINESS_DATA_ENABLED(p_target_name, p_target_type) THEN
    return;
  END IF;

  l_svc_id := mgmt_service.get_service_guid( p_target_name,
                                             p_target_type);
  BEGIN
    -- see if there are any memebers that are
    -- 1. services and 
    -- 2. have business metrics.
    SELECT 1 INTO allow_promotion FROM dual where EXISTS(
    SELECT 1
      FROM 
       (
              SELECT tgt.target_guid, tgt.target_name, 
                     tgt.target_type, 1 as is_key
                FROM mgmt_target_assocs, mgmt_targets tgt
               WHERE source_target_guid = l_svc_id
                 AND assoc_guid = MGMT_ASSOC.g_depends_on_guid
                 AND tgt.target_guid = assoc_target_guid
       ) members,
       mgmt_type_properties type_props,
       mgmt_target_properties props
     WHERE type_props.target_type = members.target_type
       AND type_props.property_value = '1'
       AND (type_props.property_name = MGMT_GLOBAL.G_IS_SERVICE_PROP OR
            type_props.property_name = MGMT_GLOBAL.G_IS_AGGREGATE_SERVICE_PROP)
       AND members.target_guid = props.target_guid
       AND props.property_name = EM_SERVICE.G_SVC_HAS_BUSINESS
       AND props.property_value = 'true');
    return;
      
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       allow_promotion := 0;
   END;

END ALLOW_BUSINESS_PROMOTION;

END EM_AGGSVC_UI;
/
show errors;