Rem
Rem $Header: groups_ui_pkgbody.sql 06-jun-2007.04:02:43 denath Exp $
Rem
Rem groups_ui_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      groups_ui_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_group_alerts_info for perf. Added Index hint on c2 table.
Rem    denath      06/06/07 - Backport denath_bug-6019788 from main
Rem    acgopala    04/07/07 - bug 5979009 adding get_applicable_member_targets procedure
Rem    acgopala    03/28/07 - bug 5594879
Rem    schoudha    11/06/06 - Bug No 5639585
Rem    bram        09/17/06 - Backport bram_bug-5478798 from main
Rem    bram        04/10/06 - Fix perf Bug -5105621
Rem    bram        07/12/06 - Backport bram_fix_5105621 from main 
Rem    bram        07/12/06 - Backport bram_bug-5137855 from main 
Rem    bram        08/30/06 - Solving perf problems in get_group_members_info
Rem    vkarpura    04/17/06 - project no# 20566 
Rem    vkarpura    04/17/06 - project no# 20566 
Rem    rpatti      02/23/06 - Backport rpatti_scnd-fix-4608945 from main 
Rem    bram        04/10/06 - Fix perf Bug -5105621
Rem    bram        04/06/06 - Bug-5137855
Rem    bram        04/06/06 - Bug-5137855
Rem    rpatti      02/16/06 - fix for group charts perf bug 4608945 
Rem    rpatti      09/14/05 - fix perf bug 4608945 
Rem    dtsao       08/30/05 - Rewrite get_group_alerts_info API for PERF issue 
Rem    paachary    08/28/05 - Bug 4573076
Rem    bram        08/23/05 - Bug-4563915
Rem    nigupta     08/18/05 - bug 4561266 
Rem    dtsao       08/03/05 - modify get_chart_data to return metric unit 
Rem    nigupta     08/01/05 - bug 4526651 
Rem    vkarpura    07/26/05 - Fix count check before looping 
Rem    nigupta     07/25/05 - bug 4327402 
Rem    dtsao       07/22/05 - rewrite get_avail_columns_list function for 9.2 
Rem                           compatiblility 
Rem    nigupta     07/08/05 - use mgmt_topology.update_background_image 
Rem                           instead of em_topology.update_background_image 
Rem    pratagar    06/27/05 - Type Display Name 
Rem    dtsao       05/18/05 - Let apply_default_chart can accept aggregate 
Rem                           target type 
Rem    dtsao       05/16/05 - add proc to get group tgt types 
Rem    dtsao       05/13/05 - continue
Rem    dtsao       05/12/05 - continue
Rem    dtsao       05/12/05 - continue
Rem    dtsao       05/11/05 - type and instance specific UI initialization 
Rem                           apis 
Rem    dtsao       05/10/05 - Do not count non-up targets when doing roll-up 
Rem                           for alert segment 
Rem    dtsao       04/29/05 - continue
Rem    dtsao       04/28/05 - add group type init 
Rem    dtsao       03/28/05 - Fix apply_default_chart 
Rem    dtsao       03/22/05 - Add apply_default_chart procedure 
Rem    groyal      02/16/05 - Remove reference to obsolete policy tables in get_target_list_details, get_summary_info
Rem    dtsao       03/03/05 - Add priv check when composite target init 
Rem    dtsao       03/03/05 - Do not count unavil in alert 
Rem    nigupta     03/07/05 - bug 4226062 
Rem    dtsao       03/02/05 - Fix add default chart 
Rem    nigupta     02/28/05 - changes in rollup procedure 
Rem    dtsao       02/23/05 - Enhance add_default_chart 
Rem    niramach    02/14/05 - Exempt->suppress conversions. 
Rem    dtsao       02/02/05 - Bug 4083753 
Rem    nigupta     02/01/05 - continue add rollup procedure 
Rem    dtsao       01/31/05 - Bug 4139907 
Rem    ramalhot    01/17/05 - assoc def name changed from member to contains 
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    nigupta     01/21/05 - add rollup procedure 
Rem    nigupta     01/06/05 - modify get_system_def to get topology related 
Rem                           data for edit/create page 
Rem    vagarwal    01/05/05 - fix bugs 3927901, 3630585 in get_alerts_info 
Rem    ramalhot    12/30/04 - update_charts modified to use em_group package 
Rem    dtsao       12/27/04 - Add name & type columns 
Rem    nigupta     12/16/04 - show only related to associations for system 
Rem                           editing page 
Rem    nigupta     12/13/04 - use only relates_to links for topology 
Rem    vagarwal    12/10/04 - continue
Rem    vagarwal    12/08/04 - fix alerts sql 
Rem    yaofeng     11/15/04 - add get_applicable_mem_tgt_types 
Rem    yaofeng     11/11/04 - customize columns 
Rem    rpatti      11/05/04 - continue
Rem    rpatti      11/03/04 - add column cust. support 
Rem    streddy     11/08/04 - Pass in the redundancy group type 
Rem    streddy     11/04/04 - Added default chart API 
Rem    jpyang      11/02/04 - redundancy group status segment
Rem    nigupta     11/02/04 - bug in get_members_and_associations
Rem    nigupta     10/29/04 - in get_group_assoc_def both targets should be
Rem                           members
Rem    vagarwal    10/27/04 - continue
Rem    rpatti      10/26/04 - continue
Rem    rpatti      10/25/04 - continue
Rem    yaofeng     10/15/04 - Search
Rem    nigupta     10/13/04 - return host in get_target_list_details 
Rem    rpatti      10/22/04 - continue
Rem    rpatti      10/13/04 - add create/edit APIs 
Rem    vagarwal    09/30/04 - 
Rem    nigupta     09/21/04 - add get_summary_info 
Rem    nigupta     09/20/04 - move expand_node, get_members_and_associations 
Rem                           and get_target_list_details from 
Rem                           group_mntr_pkgbody 
Rem    vagarwal    09/14/04 - vagarwal_groups_0823
Rem    vagarwal    09/02/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY em_groups_ui AS

  G_FALSE CONSTANT INTEGER := 0;
  G_TRUE CONSTANT INTEGER := 1;

FUNCTION get_targets(comp_target_name IN VARCHAR2,
                     comp_target_type IN VARCHAR2,
                     member_target_type IN VARCHAR2 DEFAULT NULL,
                     member_target_name IN VARCHAR2 DEFAULT NULL)
RETURN SMP_EMD_TGT_OBJECT_TBL
PIPELINED IS
  l_tgt_cur cursorType;
  l_tgt SMP_EMD_TGT_OBJECT := 
    SMP_EMD_TGT_OBJECT(NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
  IF (member_target_type IS NULL) THEN
    OPEN l_tgt_cur FOR 
      SELECT mt.target_name, mt.target_type,
             mt.target_guid, mt.target_name as display_name,
             mt.type_display_name,  mt.host_name
        FROM mgmt_flat_target_assoc a, mgmt_targets ct, mgmt_targets mt
       WHERE ct.target_name = comp_target_name
         AND ct.target_type = comp_target_type
         AND ct.target_guid = a.source_target_guid
         AND a.is_membership = 1
         AND mt.target_guid = a.assoc_target_guid
         AND mt.target_name like '%'||member_target_name||'%';
  ELSE
    OPEN l_tgt_cur FOR 
      SELECT mt.target_name, mt.target_type,
             mt.target_guid, mt.target_name as display_name,
             mt.type_display_name,  mt.host_name
        FROM mgmt_flat_target_assoc a, mgmt_targets ct, mgmt_targets mt
       WHERE ct.target_name = comp_target_name
         AND ct.target_type = comp_target_type
         AND ct.target_guid = a.source_target_guid
         AND a.is_membership = 1
         AND mt.target_guid = a.assoc_target_guid
         AND mt.target_name like '%'||member_target_name||'%'
         AND mt.target_type = member_target_type;
  END IF;
  LOOP
    FETCH l_tgt_cur INTO l_tgt.target_name, l_tgt.target_type,
                         l_tgt.target_guid, l_tgt.display_name,
                         l_tgt.type_display_name, l_tgt.host_name;
    EXIT WHEN l_tgt_cur%NOTFOUND;
    PIPE ROW(l_tgt);
  END LOOP;
  CLOSE l_tgt_cur;
  RETURN;
END;

FUNCTION expand_aggregate_targets (p_targets_to_expand IN SMP_EMD_NVPAIR_ARRAY)
RETURN SMP_EMD_NVPAIR_ARRAY
IS
  l_result_list SMP_EMD_NVPAIR_ARRAY;
  l_expanded_list SMP_EMD_NVPAIR_ARRAY :=  SMP_EMD_NVPAIR_ARRAY();
BEGIN
    l_result_list := SMP_EMD_NVPAIR_ARRAY();

    IF (p_targets_to_expand IS NULL)
    THEN return l_result_list;
    END IF;

    IF (p_targets_to_expand.COUNT = 0)
    THEN return l_result_list;
    END IF;

    FOR i in p_targets_to_expand.FIRST .. p_targets_to_expand.LAST
    LOOP
        l_result_list.extend(1);
        l_result_list(l_result_list.LAST) := p_targets_to_expand(i);

        if (mgmt_target.is_aggregate_type(p_targets_to_expand(i).value) = 1)
        THEN
            SELECT SMP_EMD_NVPAIR(td.target_name, td.target_type)
              BULK COLLECT INTO l_expanded_list
              FROM mgmt_flat_target_assoc members, mgmt_targets ts, mgmt_targets td
             WHERE ts.target_name = p_targets_to_expand(i).name
               AND ts.target_type = p_targets_to_expand(i).value
               AND members.source_target_guid=ts.target_guid
               AND members.is_membership = 1
               AND members.assoc_target_guid = td.target_guid;

          IF (l_expanded_list.COUNT > 0)
          THEN
           BEGIN
            FOR j in l_expanded_list.FIRST .. l_expanded_list.LAST
            LOOP
                l_result_list.extend(1);
                l_result_list(l_result_list.LAST) := l_expanded_list(j);
            END LOOP;
           END;
          END IF;
        END IF;
    END LOOP;
    return l_result_list;
END expand_aggregate_targets;

-----------------------------------------------------------------------
-- Internal procedures
-----------------------------------------------------------------------
-- update custom chart info
PROCEDURE update_charts
(
                target_name_in    IN  VARCHAR2,
                target_type_in    IN  VARCHAR2,
                edit_mode_in      IN  INTEGER,
                charts_list_in    IN  MGMT_GRP_CHART_ARRAY
)
IS 
BEGIN
  IF (edit_mode_in = G_TRUE) THEN 
    em_group.modify_charts(target_name_in, target_type_in,charts_list_in);
  ELSIF (edit_mode_in = G_FALSE)THEN
    em_group.create_charts(target_name_in, target_type_in,charts_list_in);
  END IF;

END update_charts;

-- update custom column info
PROCEDURE update_columns
(
                target_name_in    IN  VARCHAR2,
                target_type_in    IN  VARCHAR2,
                edit_mode_in      IN  INTEGER,
                columns_list_in   IN  MGMT_GRP_COLUMN_ARRAY
)
IS 
  l_target_guid MGMT_TARGETS.target_guid%TYPE;
BEGIN

  -- get the target guid
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);

  -- if this is edit
  IF (edit_mode_in = G_TRUE)
  THEN
    -- delete all previous column customization data
    DELETE
      FROM MGMT_GROUP_CUSTOM_COLUMNS
     WHERE composite_target_guid = l_target_guid;
  END IF;

  -- save the column info
  IF (columns_list_in IS NOT NULL AND columns_list_in.COUNT > 0)
  THEN
      INSERT INTO MGMT_GROUP_CUSTOM_COLUMNS
        (composite_target_guid, column_type, member_metric_guid,
         property_name, target_type, abbreviation, display_order)
      SELECT DISTINCT l_target_guid, c.column_type, c.metric_guid,
             c.property_name, c.target_type, c.abbreviation, 
             c.display_order
        FROM TABLE(CAST(columns_list_in AS MGMT_GRP_COLUMN_ARRAY)) c
       ORDER BY c.display_order;
  END IF; 
END update_columns;

-- update ui preferences
PROCEDURE update_prefs
(
                target_name_in    IN  VARCHAR2,
                target_type_in    IN  VARCHAR2,
                edit_mode_in      IN  INTEGER,
                prefs_list_in     IN  SMP_EMD_NVPAIR_ARRAY
)
IS 
  l_target_guid MGMT_TARGETS.target_guid%TYPE;
BEGIN

  -- get the target guid
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);

  -- if this is edit
  IF (edit_mode_in = G_TRUE)
  THEN
    -- delete all previous customization data
    DELETE
      FROM MGMT_TARGET_PROPERTIES p
     WHERE p.target_guid = l_target_guid
       AND p.property_name IN 
           (SELECT name
             FROM TABLE(CAST(prefs_list_in AS SMP_EMD_NVPAIR_ARRAY)));
  END IF;

  -- save the pref info
  IF (prefs_list_in IS NOT NULL AND prefs_list_in.COUNT > 0)
  THEN
      INSERT INTO MGMT_TARGET_PROPERTIES
        (target_guid, property_name, property_value)
      SELECT l_target_guid, up.name, up.value
        FROM TABLE(CAST(prefs_list_in AS SMP_EMD_NVPAIR_ARRAY)) up;
  END IF; 

END update_prefs;

PROCEDURE get_group_members_def(
                target_guid_in  IN   RAW,
                members_out     OUT  cursorType)
IS
BEGIN
  OPEN members_out FOR
    SELECT tgt.target_name, tgt.target_type,
           NVL(tgt.type_display_name, tgt.target_type) AS type_display_name,
           tgt.timezone_region, tgt.target_guid
      FROM mgmt_target_assocs mem, mgmt_targets tgt
     WHERE mem.assoc_guid = MGMT_ASSOC.g_contains_guid
       AND mem.source_target_guid = target_guid_in
       AND mem.assoc_target_guid = tgt.target_guid;

END get_group_members_def;

--
PROCEDURE get_group_charts_def(
                target_guid_in      IN  RAW,
                charts_list_out     OUT  cursorType,
                charts_sel_tgt_out  OUT  cursorType
) IS
BEGIN
  OPEN charts_list_out FOR
    SELECT DISTINCT c.comp_chart_guid, c.chart_type,
           c.display_order, c.targets_count, m.target_type,
           m.metric_guid, m.metric_name, m.metric_column,
           m.column_label_nlsid, 
           NVL(min_column, 0) AS min_column, 
           NVL(max_column, 0) AS max_column,
           NVL(avg_column, 0) AS avg_column,
           NVL(sum_column, 0) AS sum_column,
           NVL(stdev_column, 0) AS stdev_column,
           m.column_label
      FROM MGMT_GROUP_CHART c, MGMT_METRICS m,
           MGMT_GROUP_SUMMARY_CHART_DEF sd
     WHERE c.comp_target_guid = target_guid_in
       AND c.member_metric_guid = m.metric_guid
       AND c.comp_chart_guid = sd.comp_chart_guid (+)
     ORDER BY c.display_order;
                                                                               
  OPEN charts_sel_tgt_out FOR
    SELECT s.comp_chart_guid, t.target_name, t.target_type,
           t.target_guid
      FROM MGMT_GROUP_CHART c, MGMT_GROUP_CHART_SELTARGETS s,
           MGMT_TARGETS t
     WHERE c.comp_target_guid = target_guid_in
       AND s.comp_chart_guid = c.comp_chart_guid
       AND t.target_guid = s.target_guid;

END get_group_charts_def;

PROCEDURE get_group_columns_def(
                target_guid_in      IN  RAW,
                columns_list_out    OUT  cursorType
) IS
BEGIN
  OPEN columns_list_out FOR
    WITH cols AS
          (
            SELECT DISTINCT composite_target_guid, 
                            column_type, 
                            member_metric_guid,
                            property_name, 
                            target_type, 
                            abbreviation, 
                            display_order,
                            view_type
              FROM MGMT_GROUP_CUSTOM_COLUMNS
             WHERE composite_target_guid = target_guid_in
          ),
         selcols AS
          (
            SELECT DISTINCT cols.display_order, cols.abbreviation, 
                    m.target_type, cols.column_type,
                    m.metric_guid, m.metric_name, m.metric_column,
                    m.column_label, m.column_label_nlsid, 
                    NULL AS "property_name",
                    m.short_name AS "property_display_name", 
                    m.short_name_nlsid AS "property_display_nlsid"
               FROM cols, MGMT_METRICS m
              WHERE cols.column_type = em_groups_ui.G_COLUMN_TYPE_METRIC
                AND m.metric_guid = cols.member_metric_guid
                AND m.num_keys = 0
                AND m.usage_type = 0
                AND m.metric_type = 0
                AND NOT (m.metric_name='Response' AND m.metric_column='Status')
              UNION ALL 
             SELECT DISTINCT cols.display_order, cols.abbreviation, 
                    cols.target_type, cols.column_type,
                    NULL AS "metric_guid", NULL AS "metric_name", 
                    NULL AS "metric_column", NULL AS "column_label",
                    NULL AS "column_label_nlsid", p.property_name,
                    p.property_display_name, p.property_display_nlsid
               FROM cols, MGMT_TARGET_PROP_DEFS p
              WHERE cols.column_type = em_groups_ui.G_COLUMN_TYPE_PROPERTY
                AND p.target_type = cols.target_type
                AND p.property_name = cols.property_name
                AND p.hidden_flag = 0
                AND p.credential_flag = 0
              UNION ALL
             SELECT DISTINCT cols.display_order, cols.abbreviation,
                    NULL AS "target_type", cols.column_type,
                    NULL AS "metric_guid", NULL AS "metric_name",
                    NULL AS "metric_column", NULL AS "column_label",
                    NULL AS "column_label_nlsid", cols.property_name,
                    g.property_display_name,
                    g.property_display_nlsid
               FROM cols, MGMT_ALL_TARGET_PROPS g
              WHERE cols.property_name=g.property_name
                AND cols.column_type = em_groups_ui.G_COLUMN_TYPE_GEN_PROPERTY
              UNION ALL
             SELECT DISTINCT cols.display_order, cols.abbreviation, 
                    NULL AS "target_type", cols.column_type,
                    NULL AS "metric_guid", NULL AS "metric_name",
                    NULL AS "metric_column", NULL AS "column_label",
                    NULL AS "column_label_nlsid", cols.property_name,
                    NULL AS "property_display_name", 
                    NULL AS "property_display_nlsid"
               FROM cols
              WHERE cols.column_type = em_groups_ui.G_COLUMN_TYPE_GENERAL
          )
    SELECT DISTINCT display_order, 
                    abbreviation,
                    target_type,
                    column_type,
                    metric_guid,
                    metric_name, 
                    column_type, 
                    metric_column,
                    column_label, 
                    column_label_nlsid, 
                    "property_name", 
                    "property_display_name",
                    "property_display_nlsid"
      FROM selcols
     ORDER BY display_order;
                                                                               
END get_group_columns_def;

PROCEDURE get_group_prefs_def(
                target_guid_in    IN   RAW,
                prefs_list_out    OUT  cursorType
) IS
BEGIN

  OPEN prefs_list_out FOR
    SELECT property_name, property_value 
      FROM MGMT_TARGET_PROPERTIES
     WHERE target_guid = target_guid_in;
                                                                               
END get_group_prefs_def;

PROCEDURE get_group_assocs_def(
                target_guid_in  IN   RAW,
                assoc_list_out  OUT  cursorType)
IS
BEGIN
    OPEN assoc_list_out FOR
      WITH members AS
      (
        SELECT assoc_target_guid AS target_guid
          FROM mgmt_target_assocs 
         WHERE assoc_guid = MGMT_ASSOC.g_contains_guid
           AND source_target_guid = target_guid_in
      )
      SELECT a.source_target_guid, a.assoc_target_guid,
             st.target_name AS source_target_name,
             st.target_type AS source_target_type,
             at.target_name AS assoc_target_name,
             at.target_type AS assoc_target_type,
             a.assoc_guid, d.association_type,
             DECODE (a.scope_target_guid, target_guid_in, 1, 0) 
               AS system_scope
        FROM mgmt_target_assocs a, mgmt_target_assoc_defs d,
             mgmt_targets st, mgmt_targets at
       WHERE (
             a.source_target_guid IN
               (SELECT target_guid FROM members)
              AND 
              a.assoc_target_guid IN
               (SELECT target_guid FROM members) 
             )
         AND (
               a.scope_target_guid = target_guid_in
               OR
               a.scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID
             )
         AND d.assoc_guid = a.assoc_guid 
         AND a.source_target_guid = st.target_guid
         AND a.assoc_target_guid = at.target_guid
         AND d.association_type = MGMT_ASSOC.ASSOC_TYPE_RELATES_TO;

END get_group_assocs_def;

--
-- FUNCTION
--
-- PURPOSE:
--  The function returns a string that describes a given metric
--
-- INPUT:
--  metric_guid_in: Metric guid for a target/metric/metric column combo
--
-- OUTPUT:
--  the string represents the metric constructed out the default
--   target type and metric column labels.
--   It doesn't account for internationalization.
--   It is used to uniquely identify a metric - but also to provide
--    a key value readable by end users.
--
FUNCTION get_metric_label
             (metric_guid_in IN RAW)
RETURN VARCHAR2
IS
  l_type mgmt_metrics.target_type%TYPE;
  l_column_label mgmt_metrics.column_label%TYPE;
  l_type_display_name mgmt_target_types.type_display_name%TYPE;

BEGIN

  BEGIN
    SELECT DISTINCT target_type, column_label
      INTO l_type, l_column_label
      FROM mgmt_metrics
     WHERE metric_guid = metric_guid_in;

    BEGIN
      SELECT type_display_name
        INTO l_type_display_name
        FROM mgmt_target_types
       WHERE target_type = l_type;
    EXCEPTION
      WHEN OTHERS THEN
        l_type_display_name := l_type;
    END;
  
    RETURN l_type_display_name || ' ' || l_column_label;

  EXCEPTION
    WHEN OTHERS THEN
      l_type_display_name := l_type;
  END;

  RETURN NULL;

END get_metric_label;

-----------------------------------------------------------------------
-- End Internal procedures
-----------------------------------------------------------------------

--
FUNCTION get_avg_metric_label
             (metric_guid_in IN RAW)
RETURN VARCHAR2
IS
BEGIN
  RETURN 'Average: ' || get_metric_label(metric_guid_in);
END get_avg_metric_label;
--

--
FUNCTION get_min_metric_label
             (metric_guid_in IN RAW)
RETURN VARCHAR2
IS
BEGIN
  RETURN 'Maximum: ' || get_metric_label(metric_guid_in);
END get_min_metric_label;
--

--
FUNCTION get_max_metric_label
             (metric_guid_in IN RAW)
RETURN VARCHAR2
IS
BEGIN
  RETURN 'Minimum: ' || get_metric_label(metric_guid_in);
END get_max_metric_label;
--

--
FUNCTION get_sum_metric_label
             (metric_guid_in IN RAW)
RETURN VARCHAR2
IS
BEGIN
  RETURN 'Sum: ' || get_metric_label(metric_guid_in);
END get_sum_metric_label;
--

--
FUNCTION get_stdev_metric_label
             (metric_guid_in IN RAW)
RETURN VARCHAR2
IS
BEGIN
  RETURN 'Std. Deviation: ' || get_metric_label(metric_guid_in);
END get_stdev_metric_label;
--

--
PROCEDURE add_default_chart(target_type_in   IN VARCHAR2,
                            chart_type_in    IN NUMBER,
                            member_type_in   IN VARCHAR2,
                            member_metric_in IN VARCHAR2,
                            member_column_in IN VARCHAR2,
                            targets_count_in IN NUMBER DEFAULT 2,
                            min_column_in    IN NUMBER DEFAULT 0,
                            max_column_in    IN NUMBER DEFAULT 0,
                            avg_column_in    IN NUMBER DEFAULT 0,
                            sum_column_in    IN NUMBER DEFAULT 0,
                            stdev_column_in  IN NUMBER DEFAULT 0
) 
IS
  l_member_metric_guid MGMT_METRICS.metric_guid%TYPE;
  l_display_order NUMBER(2) := 0;
BEGIN

  IF (NOT ( target_type_in = mgmt_global.ANY_TARGET_TYPE)
      AND ( mgmt_target.is_aggregate_type(target_type_in) = 0))
  THEN
      raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
        'Invalid target type ' || target_type_in ||
        ' for chart type ' ||   chart_type_in ||
        '; member target type = ' || member_type_in ||
        '; metric = ' || member_metric_in ||'/' || member_column_in);
  END IF;

  IF NOT ((chart_type_in = mgmt_group.G_CHART_PEAK_TARGETS) OR
          (chart_type_in = mgmt_group.G_CHART_HIGHEST_TARGETS) OR
          (chart_type_in = mgmt_group.G_CHART_LOWEST_TARGETS) OR
          (chart_type_in = mgmt_group.G_CHART_SUMMARY_METRIC))
  THEN
      raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
        'Invalid chart type ' || chart_type_in ||
        'for target type ' ||   target_type_in ||
        '; member target type = ' || member_type_in ||
        '; metric = ' || member_metric_in ||'/' || member_column_in);
  END IF;

  IF (((chart_type_in = mgmt_group.G_CHART_PEAK_TARGETS) OR
       (chart_type_in = mgmt_group.G_CHART_HIGHEST_TARGETS) OR
       (chart_type_in = mgmt_group.G_CHART_LOWEST_TARGETS)) AND
       (targets_count_in < 1))
  THEN
      raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
        'Invalid target count for chart type ' || chart_type_in ||
        'for target type ' ||   target_type_in ||
        '; member target type = ' || member_type_in ||
        '; metric = ' || member_metric_in ||'/' || member_column_in);
  END IF;

  IF (chart_type_in = mgmt_group.G_CHART_SUMMARY_METRIC AND
      NOT (min_column_in = 1 OR max_column_in =1 OR
           avg_column_in = 1 OR sum_column_in = 1 OR
           stdev_column_in = 1))
  THEN
      raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
        'No column selected for default summary chart type '||
         target_type_in ||
        '; member target type = ' || member_type_in ||
        '; metric = ' || member_metric_in ||'/' || member_column_in);
  END IF;

  -- get the last display order for the given composite target 
  --  type and member type combo. We will then just add a row after 
  --  this.


  BEGIN
    SELECT MAX(display_order)
      INTO l_display_order
      FROM MGMT_GROUP_DEFAULT_CHART
     WHERE comp_target_type = target_type_in
       AND member_target_type = member_type_in
     GROUP BY comp_target_type, member_target_type;

     l_display_order := l_display_order + 1;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_display_order := 0; -- we default to 0 anyway
  END;

  BEGIN
   INSERT INTO MGMT_GROUP_DEFAULT_CHART
      (comp_target_type, chart_type, member_target_type, 
       member_metric_name, member_metric_column,
       targets_count, min_column, max_column, avg_column,
       sum_column, stdev_column, display_order)
   VALUES
     (target_type_in, chart_type_in,
      member_type_in, member_metric_in,
      member_column_in, targets_count_in, 
      min_column_in, max_column_in, avg_column_in,
      sum_column_in, stdev_column_in, l_display_order);
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      raise_application_error(mgmt_global.DUPLICATE_RECORD_ERR,
        'Default chart already exists for target type ' || target_type_in ||
        '; member target type = ' || member_type_in ||
        '; metric = ' || member_metric_in ||'/' || member_column_in);
  END;

END add_default_chart;


PROCEDURE apply_default_charts(p_aggr_target_name IN VARCHAR2,
                       p_aggr_target_type IN VARCHAR2,
                       p_targets_to_add IN SMP_EMD_NVPAIR_ARRAY,
                       p_targets_to_remove IN SMP_EMD_NVPAIR_ARRAY
)
IS
l_aggregate_guid MGMT_TARGETS.target_guid%TYPE;
l_new_type_list SMP_EMD_STRING_ARRAY;
l_removed_type_list SMP_EMD_STRING_ARRAY;
l_added_type_list SMP_EMD_STRING_ARRAY;
l_targets_to_add SMP_EMD_NVPAIR_ARRAY;
l_targets_to_remove SMP_EMD_NVPAIR_ARRAY;
l_counter INTEGER;
l_type VARCHAR2(256);
count1 NUMBER;
count2 NUMBER;
l_chart_guid_list MGMT_USER_GUID_ARRAY;
l_chart_guid mgmt_group_chart.comp_chart_guid%TYPE;
l_metric_guid mgmt_metrics.metric_guid%TYPE;
l_member_metric_guid mgmt_metrics.metric_guid%TYPE;
l_has_deleted BOOLEAN;
TYPE chart_set IS TABLE OF MGMT_GROUP_CHART%ROWTYPE;
chart_rec chart_set;
TYPE default_chart_set IS TABLE OF MGMT_GROUP_DEFAULT_CHART%ROWTYPE;
default_chart_rec default_chart_set;
display_order1 NUMBER;
BEGIN

--check if this is a valid aggregate type
  IF (mgmt_target.is_aggregate_type(p_aggr_target_type) = 0)
  THEN
      raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
        'Invalid target type ' || p_aggr_target_type);
  END IF;
  
  BEGIN
        SELECT target_guid
        INTO   l_aggregate_guid
        FROM   MGMT_TARGETS
        WHERE  target_name=p_aggr_target_name
        AND    target_type=p_aggr_target_type;
        
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
        raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
        'The specified target ' || p_aggr_target_name || ' does not exist');
  END;
  
  l_targets_to_add := expand_aggregate_targets(p_targets_to_add);
  l_targets_to_remove := expand_aggregate_targets(p_targets_to_remove);

  
-- Generate the target type list that needs to be removed
-- A type should be removed if it does not exist in new target list
  SELECT DISTINCT value
  BULK COLLECT INTO l_removed_type_list
  FROM TABLE(CAST(l_targets_to_remove AS SMP_EMD_NVPAIR_ARRAY))
  WHERE value NOT IN
    ( SELECT DISTINCT target_type 
        FROM mgmt_flat_target_assoc members, mgmt_targets t
       WHERE members.source_target_guid=l_aggregate_guid
         AND members.is_membership = 1
         AND members.assoc_target_guid = t.target_guid
    );
  
-- Generate the target type list that needs to be added
-- A type should not be added even if it in add_target if
--  1. This target type exists in delete target list, or
--  2. The number of target of this type is different between 
--     add_list and new_list
  SELECT DISTINCT value
  BULK COLLECT INTO l_added_type_list
  FROM TABLE(CAST(l_targets_to_add AS SMP_EMD_NVPAIR_ARRAY))
  WHERE value NOT IN
    ( SELECT DISTINCT value 
        FROM TABLE(CAST(l_targets_to_remove AS SMP_EMD_NVPAIR_ARRAY))
    );

  IF (l_removed_type_list IS NULL) THEN
    l_removed_type_list := SMP_EMD_STRING_ARRAY();
  END IF;
  
  IF (l_added_type_list IS NULL) THEN
      l_added_type_list := SMP_EMD_STRING_ARRAY();
  END IF;

-- Now looping to check the added_type_list  
  IF (l_added_type_list.COUNT > 0)
  THEN
    l_counter := l_added_type_list.FIRST;
    WHILE l_counter IS NOT NULL
    LOOP
      l_type := l_added_type_list(l_counter);
      SELECT COUNT(DISTINCT target_name)
        INTO count1
        FROM mgmt_flat_target_assoc members, mgmt_targets t
       WHERE members.source_target_guid=l_aggregate_guid
         AND members.is_membership = 1
         AND members.assoc_target_guid = t.target_guid
         AND target_type = l_type;
        
      SELECT COUNT(DISTINCT name)
        INTO count2
        FROM TABLE(CAST(l_targets_to_add AS SMP_EMD_NVPAIR_ARRAY))
       WHERE value = l_type;
        
      IF (count1 <> count2) THEN
         l_added_type_list.DELETE(l_counter);
      END IF;
      l_counter := l_added_type_list.NEXT(l_counter);
    END LOOP;
  END IF;
  
--DELETE all charts which has target_type in l_removed_type_list
-- TO DECIDE: Is there a better way to do so?
  l_has_deleted := FALSE;
  IF (l_removed_type_list.COUNT > 0) THEN
    l_counter := l_removed_type_list.FIRST;
    WHILE l_counter IS NOT NULL
    LOOP
      l_type := l_removed_type_list(l_counter);
         SELECT DISTINCT c.comp_chart_guid
         BULK COLLECT INTO l_chart_guid_list
           FROM MGMT_GROUP_CHART c, MGMT_METRICS m
          WHERE c.comp_target_guid = l_aggregate_guid
            AND c.member_metric_guid = m.metric_guid
            AND m.target_type = l_type;
           
      IF (  (l_chart_guid_list IS NOT NULL)
         AND(l_chart_guid_list.COUNT > 0) ) THEN
         count1 := l_chart_guid_list.FIRST;
         WHILE count1 IS NOT NULL 
         LOOP
            l_has_deleted := TRUE;
            DELETE FROM MGMT_GROUP_CHART
            WHERE COMP_CHART_GUID = l_chart_guid_list(count1);
            
            --Actually There should be no any default select targets chart
            DELETE FROM MGMT_GROUP_CHART_SELTARGETS
            WHERE COMP_CHART_GUID = l_chart_guid_list(count1);
            
            DELETE FROM MGMT_GROUP_SUMMARY_CHART_DEF
            WHERE COMP_CHART_GUID = l_chart_guid_list(count1);
            
            count1 := l_chart_guid_list.NEXT(count1);
         END LOOP;
      END IF;
      l_counter := l_removed_type_list.NEXT(l_counter);
    END LOOP;
  END IF;
  
-- Now we need to handle add part. But before that, if any chart has
-- been deleted, we need to reorg display order first.
-- TO DECIDE: If reorg is necessary? Display order could be sparsed?
  IF (l_has_deleted = TRUE) THEN
    display_order1 := 1;

    SELECT * 
    BULK COLLECT INTO chart_rec
    FROM MGMT_GROUP_CHART
    WHERE COMP_TARGET_GUID = l_aggregate_guid
    ORDER BY DISPLAY_ORDER;
    
    --Change the display order
    IF ( (chart_rec IS NOT NULL)
     AND (chart_rec.COUNT > 0) )THEN
      FOR i in chart_rec.FIRST .. chart_rec.LAST
      LOOP
        chart_rec(i).DISPLAY_ORDER := display_order1;
        display_order1 := display_order1 + 1;
      END LOOP;
      
      --update into repos
      FOR i in chart_rec.FIRST .. chart_rec.LAST
      LOOP
        UPDATE MGMT_GROUP_CHART SET ROW = chart_rec(i)
        WHERE COMP_CHART_GUID = chart_rec(i).COMP_CHART_GUID;
      END LOOP;
    END IF;
  END IF;
  
-- Begin to add new default charts
  IF (l_added_type_list.COUNT > 0) THEN
    --summary metric guid
    l_metric_guid := MGMT_METRIC.get_metric_guid(p_aggr_target_type, em_group.SUM_METRIC,' ');
    count1 := l_added_type_list.FIRST;
    WHILE count1 IS NOT NULL
    LOOP
      SELECT *
      BULK COLLECT INTO default_chart_rec
        FROM MGMT_GROUP_DEFAULT_CHART
       WHERE ( (comp_target_type = p_aggr_target_type)
          OR (comp_target_type = mgmt_global.ANY_TARGET_TYPE))
         AND MEMBER_TARGET_TYPE = l_added_type_list(count1)
     ORDER BY DISPLAY_ORDER;
    
     IF ( (default_chart_rec IS NOT NULL)
      AND (default_chart_rec.COUNT > 0) )THEN
        FOR i in default_chart_rec.FIRST .. default_chart_rec.LAST
        LOOP
          l_member_metric_guid := MGMT_METRIC.get_metric_guid(
                default_chart_rec(i).MEMBER_TARGET_TYPE,
                default_chart_rec(i).MEMBER_METRIC_NAME,
                default_chart_rec(i).MEMBER_METRIC_COLUMN);
          INSERT INTO MGMT_GROUP_CHART
                 (comp_target_guid, chart_type, member_metric_guid, 
                 display_order, targets_count)
                 VALUES
                 (l_aggregate_guid, default_chart_rec(i).chart_type,l_member_metric_guid,
                  display_order1, default_chart_rec(i).targets_count)
                 RETURNING comp_chart_guid
                 INTO l_chart_guid;

          display_order1 := display_order1 + 1;
             
          --handle summary metric chart
          IF (default_chart_rec(i).chart_type = MGMT_GROUP.CHART_SUMMARY_METRIC) THEN
            INSERT INTO MGMT_GROUP_SUMMARY_CHART_DEF
                   (comp_chart_guid, comp_target_guid, comp_metric_guid, min_column,
                    max_column, avg_column, sum_column, stdev_column)
                   VALUES
                   (l_chart_guid, l_aggregate_guid, l_metric_guid, 
                    default_chart_rec(i).min_column,
                    default_chart_rec(i).max_column, default_chart_rec(i).avg_column, 
                    default_chart_rec(i).sum_column, default_chart_rec(i).stdev_column);
          END IF;
        END LOOP;
      END IF;
      count1 := l_added_type_list.NEXT(count1);
    END LOOP;
  END IF;
END apply_default_charts;

PROCEDURE apply_target_ui_defaults(
    p_target_name         IN mgmt_targets.target_name%TYPE,
    p_target_type         IN mgmt_targets.target_type%TYPE,
    p_targets_to_add      IN SMP_EMD_NVPAIR_ARRAY,
    p_targets_to_remove   IN SMP_EMD_NVPAIR_ARRAY
)
IS
BEGIN

    IF ( mgmt_group.is_group(p_target_type) = 0 AND
         mgmt_group.is_redundancy_group(p_target_type) = 0 AND
         mgmt_system.is_system(p_target_type) = 0) 
    THEN
        raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
            'Invalid target type ' || p_target_type || 
            ' supported only for is_group, is_redundancy_group and is_system types' );
    END IF;

    -- Generic UI defaults (applicable to groups, systems and red grps)
    apply_default_charts(p_target_name,
        p_target_type,
        p_targets_to_add,
        p_targets_to_remove);

    -- Note: If type specific code needs to be added (eg. for redundancy
    -- groups only), please introduce a separate proc specific for that type

END apply_target_ui_defaults;

PROCEDURE register_summary_metric(target_type_in IN VARCHAR2)
IS
  l_metric_col_list MGMT_METRIC_COLUMN_ARRAY :=  MGMT_METRIC_COLUMN_ARRAY();
  l_metric_exists NUMBER;
BEGIN
  SELECT count(*) INTO l_metric_exists
    FROM mgmt_metrics
   WHERE target_type=target_type_in
     AND metric_name=G_SUM_METRIC;

  IF (l_metric_exists = 0)
  THEN
    -- Register the summary chart support metric
    l_metric_col_list.extend(2);
    l_metric_col_list(1) := MGMT_METRIC_COLUMN_OBJ.new(
                             p_column_name => G_SUM_METRIC_KEY,
                             p_column_label => G_SUM_METRIC_KEY,
                             p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
                             p_is_key => MGMT_GLOBAL.G_TRUE);

    l_metric_col_list(2) := MGMT_METRIC_COLUMN_OBJ.new(
                             p_column_name => G_SUM_METRIC_COLUMN,
                             p_column_label => G_SUM_METRIC_COLUMN,
                             p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER);

    mgmt_metric.create_metric(p_target_type => target_type_in,
                            p_metric_name => G_SUM_METRIC,
                            p_metric_label => G_SUM_METRIC,
                            p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_REPOS_TABLE,
                            p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_PLSQL,
                            p_source =>      'EMD_COMPOSITE_TARGETS.EVALUATE_SUMMARY_METRICS',
                            p_metric_column_list => l_metric_col_list,
                            p_is_transposed => 1,
                            p_is_repository => 1);

    l_metric_col_list.delete;
  END IF;

END register_summary_metric;

PROCEDURE setup_type_ui_metadata(p_target_type_in IN VARCHAR2)
IS
BEGIN

    IF ( mgmt_group.is_group(p_target_type_in) = 0 AND
         mgmt_group.is_redundancy_group(p_target_type_in) = 0 AND
         mgmt_system.is_system(p_target_type_in) = 0) 
    THEN
        raise_application_error(mgmt_global.INVALID_PARAMS_ERR,
            'Invalid target type ' || p_target_type_in || 
            ' supported only for is_group, is_redundancy_group and is_system types' );
    END IF;
    -- Generic type UI metadata
    register_summary_metric(p_target_type_in);

    -- Note: If type specific metadata needs to be added, 
    -- please introduce a separate proc specific for that type

END setup_type_ui_metadata;

PROCEDURE initialize_group_type(target_type_in IN VARCHAR2)
IS
  l_prop_value MGMT_TYPE_PROPERTIES.property_value%TYPE := '0';
  l_metric_col_list MGMT_METRIC_COLUMN_ARRAY :=  MGMT_METRIC_COLUMN_ARRAY();
BEGIN

  register_summary_metric(target_type_in);

  -- is this a type with "is_cluster" flag on?
  -- if so it has response metric
  l_prop_value := mgmt_target.get_type_property(
                    target_type_in,
                    MGMT_GLOBAL.G_IS_CLUSTER_PROP,
                    '0');

  IF l_prop_value = '1'
  THEN
        l_metric_col_list.extend(1);
        l_metric_col_list(1) := MGMT_METRIC_COLUMN_OBJ.NEW(
                          p_column_name => mgmt_global.G_AVAIL_METRIC_COLUMN,
                          p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_REPOS_EVENT,
                          p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_SEVEVAL_PLSQL,
                          p_source      => 'mgmt_severity_helper.OR_AVAIL_EVAL_PROC');
                                                                                
        mgmt_metric.create_metric(p_target_type => target_type_in,
                                  p_metric_name => mgmt_global.G_AVAIL_METRIC_NAME,
                                  p_metric_type => mgmt_global.G_METRIC_TYPE_REPOS_TABLE,
                                  p_is_repository => MGMT_GLOBAL.G_TRUE,
                                  p_source_type => mgmt_global.G_METRIC_SOURCE_SEVEVAL_PLSQL,
                                  p_source      => 'mgmt_severity_helper.OR_AVAIL_EVAL_PROC',
                                  p_metric_column_list => l_metric_col_list);

  END IF;

  -- add metadata for instance properties for dashboard support

END initialize_group_type;
                                                                               
--
-- API to be called from UI to create/edit group
--
PROCEDURE update_group
(
                target_name_in    IN  VARCHAR2,
                target_type_in    IN  VARCHAR2,
                edit_mode_in      IN  INTEGER,
                timezone_rgn_in   IN  VARCHAR2,
                member_targets_in IN  SMP_EMD_NVPAIR_ARRAY,
                charts_list_in    IN  MGMT_GRP_CHART_ARRAY,
                columns_list_in   IN  MGMT_GRP_COLUMN_ARRAY,
                prefs_in          IN  SMP_EMD_NVPAIR_ARRAY
)
IS 

  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_old_members SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();

BEGIN
  -- create/modify the group definiton and membership
  IF (edit_mode_in = G_FALSE)
  THEN
    BEGIN
      mgmt_target.add_group(
          p_group_name => target_name_in, 
          p_group_type => target_type_in, 
          p_member_targets => member_targets_in,
          p_timezone_rgn => timezone_rgn_in);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
          RAISE mgmt_global.target_already_exists;
    END;
  ELSE
    -- get the target guid
    l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);

    -- get all current members in the table except those
    -- in the new members list passed in
    SELECT SMP_EMD_NVPAIR(tgt.target_name, tgt.target_type)
      BULK COLLECT INTO l_old_members
      FROM mgmt_target_assocs mem, mgmt_targets tgt
     WHERE mem.assoc_guid = MGMT_ASSOC.g_contains_guid
       AND mem.source_target_guid = l_target_guid
       AND mem.assoc_target_guid NOT IN
           ( SELECT target_guid
               FROM mgmt_targets t,
                    TABLE(CAST(member_targets_in AS SMP_EMD_NVPAIR_ARRAY)) m
              WHERE m.name = t.target_name
                AND m.value = t.target_type)
       AND mem.assoc_target_guid = tgt.target_guid;

    mgmt_target.modify_group(
          p_group_name => target_name_in, 
          p_group_type => target_type_in, 
          p_targets_to_add => member_targets_in,
          p_targets_to_remove => l_old_members);
  END IF;

  IF (edit_mode_in = G_FALSE)
  THEN
    -- get the target guid
    l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);
  END IF;

  update_charts(target_name_in, target_type_in, edit_mode_in, charts_list_in);
  update_columns(target_name_in, target_type_in, edit_mode_in, columns_list_in);
  update_prefs(target_name_in, target_type_in, edit_mode_in, prefs_in);

END update_group;
     
--
-- API to be called from UI to create/edit  a redundancy group
--
PROCEDURE update_redundancy_group
(
                target_name_in    IN  VARCHAR2,
                target_type_in    IN  VARCHAR2,
                edit_mode_in      IN  INTEGER,
                timezone_rgn_in   IN  VARCHAR2,
                member_targets_in IN  SMP_EMD_NVPAIR_ARRAY,
                charts_list_in    IN  MGMT_GRP_CHART_ARRAY,
                columns_list_in   IN  MGMT_GRP_COLUMN_ARRAY,
                prefs_in          IN  SMP_EMD_NVPAIR_ARRAY
)
IS 

  l_target_guid MGMT_TARGETS.target_guid%TYPE;

BEGIN
  -- create/modify the group definiton and membership
  IF (edit_mode_in = G_FALSE)
  THEN
    BEGIN
     --  DOESN'T CURRENTLY SUPPORT CUSTOM TYPES
     --  OR TIMEZONE REGION OR OWNER
      mgmt_group.create_redundancy_group(
          p_rgroup_name => target_name_in, 
          p_rgroup_type => target_type_in, 
          p_member_targets => member_targets_in,
          p_rgroup_owner => null,
          p_timezone_rgn => timezone_rgn_in);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
          RAISE mgmt_global.target_already_exists;
    END;
  ELSE
    mgmt_group.modify_redundancy_group(
          p_rgroup_name => target_name_in, 
          p_rgroup_type => target_type_in, 
          p_member_targets => member_targets_in);
  END IF;

  -- get the target guid
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);

  update_charts(target_name_in, target_type_in, edit_mode_in, charts_list_in);
  update_columns(target_name_in, target_type_in, edit_mode_in, columns_list_in);
  update_prefs(target_name_in, target_type_in, edit_mode_in, prefs_in);

END update_redundancy_group;
     
--
--
-- API to be called from UI to create/edit a system
--
PROCEDURE update_system
(
                target_name_in    IN  VARCHAR2,
                target_type_in    IN  VARCHAR2,
                edit_mode_in      IN  INTEGER,
                timezone_rgn_in   IN  VARCHAR2,
                member_targets_in IN  SMP_EMD_NVPAIR_ARRAY,
                assoc_list_in     IN  MGMT_NVPAIR_TUPLE_LIST,
                charts_list_in    IN  MGMT_GRP_CHART_ARRAY,
                columns_list_in   IN  MGMT_GRP_COLUMN_ARRAY,
                prefs_in          IN  SMP_EMD_NVPAIR_ARRAY,
                topo_prefs_in     IN  SMP_EMD_NVPAIR_ARRAY,
                node_pos_in       IN  TOPO_NODE_POS_ARRAY,
                image_name_in     IN  mgmt_topo_page_bg_image.image_name%TYPE,
                image_data_in     IN  mgmt_topo_page_bg_image.image_data%TYPE
)
IS 

  l_target_guid MGMT_TARGETS.target_guid%TYPE;

BEGIN
  -- create/modify the group definiton and membership
  IF (edit_mode_in = G_FALSE)
  THEN
    BEGIN
      mgmt_system.create_system(
          p_system_name => target_name_in, 
          p_system_type => target_type_in, 
          p_members => member_targets_in,
          p_timezone_region => timezone_rgn_in);

      -- add the associations
      mgmt_assoc.create_target_assocs(
          p_assoc_def_name => mgmt_assoc.ASSOC_TYPE_RELATES_TO,
          p_scope_target_name => target_name_in,
          p_scope_target_type => target_type_in,
          p_assoc_list => assoc_list_in,
          p_is_editable => mgmt_assoc.ASSOC_EDITABLE);

    EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
          RAISE mgmt_global.target_already_exists;
    END;
  ELSE
    mgmt_system.modify_system(
          p_system_name => target_name_in, 
          p_system_type => target_type_in, 
          p_new_members => member_targets_in);

      -- change the associations
      mgmt_assoc.set_target_assocs(
          p_assoc_def_name => mgmt_assoc.ASSOC_TYPE_RELATES_TO,
          p_scope_target_name => target_name_in,
          p_scope_target_type => target_type_in,
          p_assoc_list => assoc_list_in,
          p_is_editable => mgmt_assoc.ASSOC_EDITABLE);

      --Add System Tracking for Auto created Systems
     syst_hist.RECORD_CHANGES(
            new SMP_EMD_NVPAIR(target_name_in, target_type_in), 
            member_targets_in); 
  END IF;

  
  -- get the target guid
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);

  update_charts(target_name_in, target_type_in, edit_mode_in, charts_list_in);
  update_columns(target_name_in, target_type_in, edit_mode_in, columns_list_in);
  update_prefs(target_name_in, target_type_in, edit_mode_in, prefs_in);
  em_topology.update_graph_properties('grpsTopoPageType', l_target_guid, 
                                      edit_mode_in, topo_prefs_in);
  em_topology.update_node_positions('grpsTopoPageType', l_target_guid,
                                    edit_mode_in, node_pos_in);
  mgmt_topology.update_background_image('grpsTopoPageType', l_target_guid,
                            edit_mode_in, image_name_in, image_data_in);

END update_system;

-- Get Group Definition
PROCEDURE get_group_def(
                target_name_in      IN   VARCHAR2,
                target_type_in      IN   VARCHAR2,
                timezone_rgn_out    OUT  VARCHAR2,
                members_out         OUT  cursorType,
                charts_list_out     OUT  cursorType,
                charts_sel_tgt_out  OUT  cursorType,
                columns_list_out    OUT  cursorType,
                prefs_list_out      OUT  cursorType
) IS

l_target_guid MGMT_TARGETS.target_guid%TYPE;
l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();

BEGIN

  BEGIN
    SELECT target_guid, timezone_region
      INTO l_target_guid, timezone_rgn_out
      FROM mgmt_targets
     WHERE target_name = target_name_in AND
           target_type = target_type_in;
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      raise MGMT_GLOBAL.target_does_not_exist;
  END;

    -- Ensure that the current user has modify group privilege
    IF MGMT_USER.has_priv(l_current_user, MGMT_USER.OPERATOR_TARGET,
                          target_name_in, target_type_in) = 0 THEN
        raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
        'The current user does not have enough privileges to perform this operation');
    END IF;  
  get_group_members_def (l_target_guid, members_out);

  get_group_charts_def(l_target_guid, charts_list_out, charts_sel_tgt_out);

  get_group_columns_def(l_target_guid, columns_list_out);

  get_group_prefs_def(l_target_guid, prefs_list_out);

END get_group_def;
  
-- Get a system definition
PROCEDURE get_system_def(
                target_name_in      IN   VARCHAR2,
                target_type_in      IN   VARCHAR2,
                timezone_rgn_out    OUT  VARCHAR2,
                members_out         OUT  cursorType,
                assoc_list_out      OUT  cursorType,
                topo_prefs_list_out OUT  cursorType,
                node_pos_list_out   OUT  cursorType,
                image_data_out      OUT  cursorType,
                charts_list_out     OUT  cursorType,
                charts_sel_tgt_out  OUT  cursorType,
                columns_list_out    OUT  cursorType,
                prefs_list_out      OUT  cursorType
) IS
                                                                               
l_target_guid MGMT_TARGETS.target_guid%TYPE;
l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();                                                                               
BEGIN
                                                                               
  BEGIN
    SELECT target_guid, timezone_region
      INTO l_target_guid, timezone_rgn_out
      FROM mgmt_targets
     WHERE target_name = target_name_in AND
           target_type = target_type_in;
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      raise MGMT_GLOBAL.target_does_not_exist;
  END;

    -- Ensure that the current user has modify group privilege
    IF MGMT_USER.has_priv(l_current_user, MGMT_USER.OPERATOR_TARGET,
                          target_name_in, target_type_in) = 0 THEN
        raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
        'The current user does not have enough privileges to perform this operation');
    END IF;                                                                               
  get_group_members_def (l_target_guid, members_out);
                                                                               
  get_group_charts_def(l_target_guid, charts_list_out, charts_sel_tgt_out);

  get_group_assocs_def (l_target_guid, assoc_list_out);

  get_group_columns_def(l_target_guid, columns_list_out);

  get_group_prefs_def(l_target_guid, prefs_list_out);

  em_topology.get_graph_properties('grpsTopoPageType', l_target_guid,
                                   topo_prefs_list_out);

  em_topology.get_node_positions('grpsTopoPageType', l_target_guid, 
                                 node_pos_list_out);

  em_topology.get_background_image('grpsTopoPageType', l_target_guid, 
                                   image_data_out);

END get_system_def;

--
--   This procedure returns the list of default charts
--   applicable for a list of member types
PROCEDURE get_default_charts_list (
                target_type_in  IN  VARCHAR2,
                members_in      IN  SMP_EMD_NVPAIR_ARRAY,
                charts_list_out OUT cursorType
) IS
  l_types SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
BEGIN
  
  SELECT DISTINCT target_type
    BULK COLLECT INTO l_types
    FROM
         (
          SELECT member_target_type target_type
            FROM MGMT_FLAT_TARGET_MEMBERSHIPS m, 
                 TABLE(CAST(members_in AS SMP_EMD_NVPAIR_ARRAY)) t
           WHERE m.composite_target_name = t.name
             AND m.composite_target_type = t.value
          UNION ALL
          SELECT t.value target_type
            FROM TABLE(CAST(members_in AS SMP_EMD_NVPAIR_ARRAY)) t
         );

  OPEN charts_list_out FOR
    SELECT SYS_GUID(), chart_type, display_order,
           targets_count, target_type, metric_guid,
           metric_name, metric_column, 
           column_label_nlsid, min_column,
           max_column, avg_column, sum_column, stdev_column,column_label
      FROM (
             SELECT DISTINCT c.chart_type,
                    c.display_order, c.targets_count, m.target_type,
                    m.metric_guid, m.metric_name, m.metric_column,
                    m.column_label_nlsid, 
                    NVL(min_column, 0) AS min_column, 
                    NVL(max_column, 0) AS max_column,
                    NVL(avg_column, 0) AS avg_column,
                    NVL(sum_column, 0) AS sum_column,
                    NVL(stdev_column, 0) AS stdev_column,
                    c.comp_target_type,m.column_label
               FROM MGMT_GROUP_DEFAULT_CHART c, MGMT_METRICS m,
                    TABLE(CAST(l_types AS SMP_EMD_STRING_ARRAY)) t
              WHERE (c.comp_target_type = target_type_in
                 OR c.comp_target_type = mgmt_global.ANY_TARGET_TYPE)   
                AND c.member_target_type = t.column_value
                AND m.target_type = c.member_target_type
                AND m.metric_name = c.member_metric_name
                AND m.metric_column = c.member_metric_column
              ORDER BY c.comp_target_type DESC, m.target_type, c.display_order
          );
                                                                               
END get_default_charts_list;

                                                                                
PROCEDURE get_avail_columns_list (
                target_type_in  IN  VARCHAR2,
                members_in      IN  SMP_EMD_NVPAIR_ARRAY,
                columns_list_out     OUT  cursorType
)
IS
  l_types SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
BEGIN

  SELECT DISTINCT target_type
    BULK COLLECT INTO l_types
    FROM
         (
          SELECT member_target_type target_type
            FROM MGMT_FLAT_TARGET_MEMBERSHIPS m,
                 TABLE(CAST(members_in AS SMP_EMD_NVPAIR_ARRAY)) t
           WHERE m.composite_target_name = t.name
             AND m.composite_target_type = t.value
          UNION ALL
          SELECT t.value target_type
            FROM TABLE(CAST(members_in AS SMP_EMD_NVPAIR_ARRAY)) t
         );

  OPEN columns_list_out FOR
  SELECT DISTINCT metrics.target_type, 
         em_groups_ui.G_COLUMN_TYPE_METRIC AS "column_type",
         metrics.metric_guid, metrics.metric_name, metrics.metric_column,
         metrics.column_label, metrics.column_label_nlsid,
         NULL AS "property_name",
         metrics.short_name AS "property_display_name",
         metrics.short_name_nlsid AS "property_display_nlsid"
    FROM (
      SELECT DISTINCT m.target_type, m.metric_name, m.metric_column,
             m.column_label, m.column_label_nlsid, m.metric_guid,
             m.short_name, m.short_name_nlsid
        FROM mgmt_metrics m, (SELECT t.column_value AS target_type
                              FROM TABLE(CAST(l_types AS SMP_EMD_STRING_ARRAY)) t
                             )types
       WHERE m.target_type = types.target_type
         AND metric_type = 0
         AND num_keys = 0
         AND usage_type = 0
         AND NOT (metric_name='Response' AND metric_column='Status')
           )metrics 
   UNION ALL
  SELECT props.target_type,
         em_groups_ui.G_COLUMN_TYPE_PROPERTY AS "column_type",
         NULL AS "metric_guid", NULL AS "metric_name",
         NULL AS "metric_column",
         NULL AS "column_label",
         NULL AS "column_label_nlsid", props.property_name,
         props.property_display_name, props.property_display_nlsid
    FROM (
      SELECT p.property_name, p.target_type,
             p.property_display_name, p.property_display_nlsid
        FROM MGMT_TARGET_PROP_DEFS p, (
                                   SELECT t.column_value AS target_type
                                     FROM TABLE(CAST(l_types AS SMP_EMD_STRING_ARRAY)) t
                                     )types
       WHERE p.target_type = types.target_type
         AND p.hidden_flag = 0
         AND p.credential_flag = 0
         )props 
   UNION ALL
  SELECT NULL AS "target_type",
         em_groups_ui.G_COLUMN_TYPE_GEN_PROPERTY AS "column_type",
         NULL AS "metric_guid", NULL AS "metric_name",
         NULL AS "metric_column",
         NULL AS "column_label",
         NULL AS "column_label_nlsid", g.property_name,
         g.property_display_name, g.property_display_nlsid
    FROM MGMT_ALL_TARGET_PROPS g
   WHERE g.hidden_flag = 0
     AND g.credential_flag = 0
   UNION ALL
  SELECT NULL AS "target_type",
         em_groups_ui.G_COLUMN_TYPE_GENERAL AS "column_type",
         NULL AS "metric_guid", NULL AS "metric_name",
         NULL AS "metric_column",
         NULL AS "column_label",
         NULL AS "column_label_nlsid", 
         emd_pref.MGMT_USER_PREF_STATUS_COLUMN AS "property_name",
         emd_pref.MGMT_USER_PREF_STATUS_COLUMN AS "property_display_name",
         NULL AS property_display_nlsid
    FROM dual
   UNION ALL
  SELECT NULL AS "target_type",
         em_groups_ui.G_COLUMN_TYPE_GENERAL AS "column_type",
         NULL AS "metric_guid", NULL AS "metric_name",
         NULL AS "metric_column",
         NULL AS "column_label",
         NULL AS "column_label_nlsid",
         emd_pref.MGMT_USER_PREF_ALERTS_COL AS "property_name",
         emd_pref.MGMT_USER_PREF_ALERTS_COL AS "property_display_name",
         NULL AS property_display_nlsid
    FROM dual
   UNION ALL
  SELECT NULL AS "target_type",
         em_groups_ui.G_COLUMN_TYPE_GENERAL AS "column_type",
         NULL AS "metric_guid", NULL AS "metric_name",
         NULL AS "metric_column",
         NULL AS "column_label",
         NULL AS "column_label_nlsid",
         emd_pref.MGMT_USER_PREF_TYPE_COL AS "property_name",
         emd_pref.MGMT_USER_PREF_TYPE_COL AS "property_display_name",
         NULL AS property_display_nlsid
    FROM dual
   UNION ALL
  SELECT NULL AS "target_type",
         em_groups_ui.G_COLUMN_TYPE_GENERAL AS "column_type",
         NULL AS "metric_guid", NULL AS "metric_name",
         NULL AS "metric_column",
         NULL AS "column_label",
         NULL AS "column_label_nlsid",
         emd_pref.MGMT_USER_PREF_POL_VIOL_COL AS "property_name",
         emd_pref.MGMT_USER_PREF_POL_VIOL_COL AS "property_display_name",
         NULL AS property_display_nlsid
    FROM dual
   UNION ALL
  SELECT NULL AS "target_type",
         em_groups_ui.G_COLUMN_TYPE_GENERAL AS "column_type",
         NULL AS "metric_guid", NULL AS "metric_name",
         NULL AS "metric_column",
         NULL AS "column_label",
         NULL AS "column_label_nlsid",
         emd_pref.MGMT_USER_PREF_POL_COMP_COLUMN AS "property_name",
         emd_pref.MGMT_USER_PREF_POL_COMP_COLUMN AS "property_display_name",
         NULL AS property_display_nlsid
    FROM dual;

END get_avail_columns_list;

PROCEDURE get_applicable_mem_tgt_types (
                members_in      IN  SMP_EMD_NVPAIR_ARRAY,
                types_out      OUT  cursorType
) IS
BEGIN
  OPEN types_out FOR
  SELECT DISTINCT target_type
    FROM
         (
          SELECT member_target_type target_type
            FROM MGMT_FLAT_TARGET_MEMBERSHIPS m,
                 TABLE(CAST(members_in AS SMP_EMD_NVPAIR_ARRAY)) t
           WHERE m.composite_target_name = t.name
             AND m.composite_target_type = t.value
          UNION ALL
          SELECT t.value target_type 
            FROM TABLE(CAST(members_in AS SMP_EMD_NVPAIR_ARRAY)) t
         );

END get_applicable_mem_tgt_types;

PROCEDURE get_applicable_member_targets (
                members_in      IN  SMP_EMD_NVPAIR_ARRAY,
                targets_out      OUT  cursorType
) IS
BEGIN
  OPEN targets_out FOR
  SELECT target_name,target_type
    FROM
         (
          SELECT member_target_name target_name,
                 member_target_type target_type
            FROM MGMT_FLAT_TARGET_MEMBERSHIPS m,
                 TABLE(CAST(members_in AS SMP_EMD_NVPAIR_ARRAY)) t
           WHERE m.composite_target_name = t.name
             AND m.composite_target_type = t.value
          UNION 
          SELECT t.name target_name,
                 t.value target_type 
            FROM TABLE(CAST(members_in AS SMP_EMD_NVPAIR_ARRAY)) t
         );

END get_applicable_member_targets;
------------------------------------------------------------------------
--  Homepage Related API
------------------------------------------------------------------------   

-- PROCEDURE: get_group_alerts_info
--
-- PURPOSE:
--   This procedure returns the alerts for the specified group.
--   Used by the Alerts segment.
--
-- IN Parameters:
--   target_name_in: The group target name
--   target_type_in: The group target type. 
--
-- OUT Parameters:
--   severity_cur_out: The cursor containing target severities information
--   metric_error_cur_out: The cursor containing metric errors information
--
PROCEDURE get_group_alerts_info(  target_name_in VARCHAR2,
                           target_type_in IN VARCHAR2,
                           severity_cur_out OUT cursorType,
                           metric_error_cur_out OUT cursorType)
IS
  l_target_guid mgmt_targets.target_guid%TYPE;

BEGIN

  /*
      Store the target guid to improve performance.
  */
  l_target_guid := mgmt_target.get_target_guid(
                         target_name_in,
                         target_type_in);

    -- severity
    OPEN severity_cur_out FOR
        SELECT 
               c.violation_level AS severity_code, 
               COUNT(c.violation_level) AS totcount,
               SUM(DECODE(SIGN(SYSDATE-1-c.collection_timestamp),1,0,1))  AS count
        FROM  mgmt_current_violation c,
              mgmt_current_availability avail
        WHERE EXISTS 
              (SELECT 1
                 FROM mgmt_flat_target_memberships t
                WHERE composite_target_guid = l_target_guid
                  AND t.member_target_guid = c.target_guid)
          AND c.violation_type IN (0,2)
          AND avail.target_guid = c.target_guid
          AND avail.current_status = mgmt_global.G_STATUS_UP
        GROUP BY c.violation_level;

    -- metric error
    -- note: if there is no record, the cursor still contains one row
    -- with count = 0
    OPEN metric_error_cur_out FOR
      SELECT COUNT(*) AS count
        FROM mgmt$group_flat_memberships members, mgmt_current_metric_errors err
       WHERE members.composite_target_guid=l_target_guid
       AND members.member_target_guid = err.target_guid;

END get_group_alerts_info;

-- PROCEDURE: get_group_availability_info
--
-- PURPOSE:
--   This procedure returns the availability information  for the specified group
--   Used by the groups status segment
--
-- IN Parameters:
--   target_name_in: The group target name
--   target_type_in: The group target type. 
--
-- OUT Parameters:
--   availability_cur_out: The cursor containing member targets availability 
--   information. group targets are excluded.
--
PROCEDURE get_group_availability_info(  target_name_in VARCHAR2,
                           target_type_in IN VARCHAR2,
                           avail_cur_out OUT cursorType
                           )
IS
  l_target_guid mgmt_targets.target_guid%TYPE;

BEGIN

  /*
      Store the target guid to improve performance.
  */
  SELECT target_guid
	 INTO l_target_guid
	 FROM mgmt_targets t
         WHERE t.target_name = target_name_in
         AND t.target_type = target_type_in;

    -- availability info
    OPEN avail_cur_out FOR
      SELECT NVL(avail.current_status, MGMT_GLOBAL.G_STATUS_UNKNOWN) AS current_status,
             COUNT(*) AS count
        FROM  mgmt_flat_target_memberships members, mgmt_targets tgt, mgmt_current_availability avail
        WHERE members.composite_target_guid=l_target_guid
        AND members.member_target_guid = tgt.target_guid
        AND tgt.is_group = 0
        AND members.member_target_guid = avail.target_guid (+)
       GROUP BY avail.current_status;

END get_group_availability_info;

-- PROCEDURE: get_red_group_avail_info 
--
-- PURPOSE:
--   This procedure returns the availability information  for the specified group
--   Used by the groups status segment
-- 
-- IN Parameters:
--   target_name_in: The group target name
--   target_type_in: The group target type. 
--
-- OUT Parameters:
--   availability_cur_out: The cursor containing member targets availability 
--   information. group targets are excluded.
--   status_section_out: The cursor containing status, up since, availability
--   %, and member type information.
--
PROCEDURE get_red_group_avail_info(  target_name_in VARCHAR2,
                               target_type_in IN VARCHAR2,
                               avail_cur_out OUT cursorType,
                               status_section_out OUT cursorType) IS
  l_target_guid mgmt_targets.target_guid%TYPE;
  l_member_type VARCHAR2(128);
  
BEGIN
  l_target_guid := mgmt_target.get_target_guid(
                         target_name_in,
                         target_type_in);

  get_group_availability_info(target_name_in, target_type_in, avail_cur_out);
 
  BEGIN
    SELECT target_type INTO l_member_type
      FROM mgmt_target_assocs a, mgmt_targets t
     WHERE a.source_target_guid = l_target_guid
       AND a.assoc_target_guid = t.target_guid
       AND a.assoc_guid = MGMT_ASSOC.g_contains_guid
       AND rownum < 2;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
       l_member_type := '';
  END;

  OPEN status_section_out FOR
    SELECT
      NVL(avail.current_status, 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,
      l_member_type AS member_type
    FROM mgmt_targets tgt,
         mgmt_current_availability avail
    WHERE l_target_guid = tgt.target_guid AND
          tgt.target_guid = avail.target_guid(+);
END;

-- PROCEDURE: get_group_members_info
--
-- PURPOSE:
--   This procedure returns the membership information  for the specified group
--
-- IN Parameters:
--   target_name_in: The group target name
--   target_type_in: The group target type. 
--   member_target_type_in: If set, only member target of this type would be ret
--   urned.
--   member_target_name_in: If set, only member target has name like this would --   be returned.
--
-- OUT Parameters:
--   target_types_cur_out:  The cursor containing target types of member targets
--   members_details_out:   Cursor containing member information
--
PROCEDURE get_group_members_info(
              target_name_in        IN  VARCHAR2,
              target_type_in        IN  VARCHAR2,
              member_target_type_in IN  VARCHAR2 DEFAULT NULL,
              member_target_name_in IN  VARCHAR2 DEFAULT NULL,
              target_types_out      OUT cursorType,
              member_targets_out    OUT cursorType,
              columns_info_out      OUT cursorType,
              status_info_out       OUT cursorType,
              alerts_info_out       OUT cursorType,
              policy_info_out       OUT cursorType,
              prop_columns_out      OUT cursorType,
              metric_values_out     OUT cursorType,
              metric_sev_out        OUT cursorType
) IS
    l_generic_cols   SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
    l_status_prop    INTEGER := 0;
    l_alerts_prop    INTEGER := 0;
    l_policy_viol_prop    INTEGER := 0;
    l_policy_comp_prop    INTEGER := 0;
    l_comp_guid      RAW(16);
    columnCount NUMBER;
    l_SMP_EMD_TGT_OBJECT_TBL SMP_EMD_TGT_OBJECT_TBL:=SMP_EMD_TGT_OBJECT_TBL();
BEGIN
  l_comp_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);

  -- find the target types the user can see
  OPEN target_types_out FOR
    SELECT DISTINCT target_type, target_type type_display_name
      FROM mgmt_flat_target_assoc members, mgmt_targets t
      WHERE members.source_target_guid=l_comp_guid
        AND members.is_membership = 1
        AND members.assoc_target_guid = t.target_guid;

  -- customized column info 
  get_group_columns_def(l_comp_guid, columns_info_out);
  
  --Store Targets List to Improve performance
   SELECT get_targets(target_name_in,target_type_in,member_target_type_in, member_target_name_in)
     INTO l_SMP_EMD_TGT_OBJECT_TBL  
     FROM DUAL;
     
  -- member targets info 
  OPEN member_targets_out FOR
      SELECT tgt.target_guid,
             tgt.target_name, 
             tgt.target_type,
             tgt.type_display_name
        FROM TABLE(CAST(l_SMP_EMD_TGT_OBJECT_TBL as SMP_EMD_TGT_OBJECT_TBL)) tgt;

  -- check if the generic columns are there
  BEGIN 
    SELECT 1 
      INTO l_status_prop
      FROM mgmt_group_custom_columns
     WHERE composite_target_guid=l_comp_guid
       AND property_name = emd_pref.MGMT_USER_PREF_STATUS_COLUMN;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_status_prop := 0;
    WHEN OTHERS THEN
      RAISE;
  END;
    
  BEGIN 
    SELECT 1 
      INTO l_alerts_prop
      FROM mgmt_group_custom_columns
     WHERE composite_target_guid=l_comp_guid
       AND property_name = emd_pref.MGMT_USER_PREF_ALERTS_COL;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_alerts_prop := 0;
    WHEN OTHERS THEN
      RAISE;
  END;
    
  BEGIN 
    SELECT 1 
      INTO l_policy_viol_prop
      FROM mgmt_group_custom_columns
     WHERE composite_target_guid=l_comp_guid
       AND property_name = emd_pref.MGMT_USER_PREF_POL_VIOL_COL;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_policy_viol_prop := 0;
    WHEN OTHERS THEN
      RAISE;
  END;
    
  BEGIN 
    SELECT 1 
      INTO l_policy_comp_prop
      FROM mgmt_group_custom_columns
     WHERE composite_target_guid=l_comp_guid
       AND property_name = emd_pref.MGMT_USER_PREF_POL_COMP_COLUMN;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_policy_comp_prop := 0;
    WHEN OTHERS THEN
      RAISE;
  END;
  
  --Temp code for default columns
  BEGIN
    SELECT count(*)
    INTO columnCount
    FROM mgmt_group_custom_columns
    WHERE composite_target_guid=l_comp_guid;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      columnCount := 0;
    WHEN OTHERS THEN
      RAISE;
  END;
  
  IF columnCount <= 5 THEN
     l_status_prop := 1;
     l_alerts_prop := 1;
     l_policy_viol_prop := 1;
  END IF;

  OPEN status_info_out FOR
    WITH met AS
      (
        SELECT DISTINCT target_type, metric_name
  	    FROM mgmt_metrics
 		   WHERE metric_name = 'Response' 
  	     AND metric_column = 'Status'
      )
    SELECT tgt.target_guid, 
           DECODE(avail.current_status, 
           NULL, 
           DECODE(met.metric_name, NULL, -1, 2), 
           avail.current_status) AS current_status
      FROM TABLE(CAST(l_SMP_EMD_TGT_OBJECT_TBL as SMP_EMD_TGT_OBJECT_TBL)) tgt,
           mgmt_current_availability avail, met
     WHERE tgt.target_guid = avail.target_guid (+)
       AND tgt.target_type = met.target_type (+);
   
   OPEN alerts_info_out FOR 
   -- Removing the following one line to fix Bug no 5639585
   -- The following does not work in 9.2.0.8.0 RDBMS label
   -- Hence changing to incorporate performance too
   WITH tgt as
   (
   --   SELECT * FROM TABLE(CAST(l_SMP_EMD_TGT_OBJECT_TBL as SMP_EMD_TGT_OBJECT_TBL))
     SELECT mfta.assoc_target_guid target_guid
       FROM mgmt_flat_Target_assoc  mfta
     WHERE mfta.is_membership = 1
       AND mfta.source_target_guid = l_comp_guid
   )         
    SELECT target_guid, SUM(warning_count)as warning_count, SUM(critical_count) as critical_count
    FROM   (
             SELECT tgt.target_guid target_guid, 
                    DECODE(sev.severity_code,mgmt_global.g_severity_warning,1,0) warning_count,
                    DECODE(sev.severity_code,mgmt_global.g_severity_critical,1,0) critical_count
             FROM   tgt, mgmt_current_severity sev
             WHERE  tgt.target_guid = sev.target_guid (+) 
            UNION ALL
            SELECT  tgt.target_guid target_guid, 
                    decode(c.violation_level,mgmt_global.g_severity_warning,1,0) warning_count, 
                    decode(c.violation_level,mgmt_global.g_severity_critical,1,0) critical_count
             FROM   tgt, mgmt_flat_target_assoc f, 
                    mgmt_current_violation c, 
                    mgmt_current_availability a
            WHERE  f.is_membership     = 1
              AND  tgt.target_guid       = f.source_target_guid
              AND  f.assoc_target_guid = c.target_guid
              AND  NOT c.violation_type=1
              AND  c.target_guid       = a.target_guid
              AND  a.current_status    = mgmt_global.g_status_up
       )GROUP BY target_guid;
         
    OPEN policy_info_out FOR
      SELECT self.target_guid, 
             NVL(self.information_count,0) + NVL(comptgt.information_count,0) AS information_count, 
             NVL(self.warning_count,0) + NVL(comptgt.warning_count,0) AS warning_count, 
             NVL(self.critical_count,0) + NVL(comptgt.critical_count,0) AS critical_count, 
             self.compliance_score AS compliance_score 
      FROM
        (
          SELECT alltgt.target_guid,
                 alltgt.information_count+realtgt.information_count AS information_count,
                 alltgt.warning_count+realtgt.warning_count AS warning_count,
                 alltgt.critical_count+realtgt.critical_count AS critical_count,
                 alltgt.compliance_score+realtgt.compliance_score AS compliance_score
          FROM
                (SELECT tgt.target_guid,
                    SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,e.non_exempt_violations_logged,0)) information_count,
                    SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,e.non_exempt_violations_logged,0)) warning_count,
                    SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,e.non_exempt_violations_logged,0)) critical_count,
                    ROUND(AVG(compliance_score)) as compliance_score
                FROM (SELECT mfta.assoc_target_guid target_guid
                      FROM mgmt_flat_Target_assoc  mfta
                      WHERE mfta.is_membership = 1
                        AND mfta.source_target_guid = l_comp_guid) tgt,
                     mgmt_policies r,
                     mgmt_policy_assoc_eval_summ e
                WHERE e.target_guid=tgt.target_guid
                  AND e.policy_guid=r.policy_guid
                  AND r.policy_type = 2
                GROUP BY tgt.target_guid) realtgt,
                (SELECT target_guid,
                    0 AS information_count, 
                    0 AS warning_count, 
                    0 AS critical_count, 
                    0 AS compliance_score
                FROM (SELECT mfta.assoc_target_guid target_guid
                      FROM mgmt_flat_Target_assoc  mfta
                      WHERE mfta.is_membership = 1
                        AND mfta.source_target_guid = l_comp_guid)) alltgt
          WHERE alltgt.target_guid = realtgt.target_guid (+) ) self,
        (SELECT /*+ INDEX(e)*/ tgt2.target_guid, 
                SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,e.non_exempt_violations_logged,0)) information_count,
                SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,e.non_exempt_violations_logged,0)) warning_count,
                SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,e.non_exempt_violations_logged,0)) critical_count
         FROM (SELECT mfta.assoc_target_guid target_guid
                      FROM mgmt_flat_Target_assoc  mfta
                      WHERE mfta.is_membership = 1
                        AND mfta.source_target_guid = l_comp_guid) tgt2,
                mgmt_policies r,
                mgmt_policy_assoc_eval_summ e,
                mgmt_flat_target_assoc tm
         WHERE  tm.source_target_guid = tgt2.target_guid
                AND tm.assoc_target_guid = e.target_guid (+)
                AND tm.is_membership = 1
                AND e.policy_guid=r.policy_guid
                AND r.policy_type = 2
         GROUP BY tgt2.target_guid) comptgt
    WHERE (l_policy_viol_prop = 1 OR l_policy_comp_prop = 1)
      AND self.target_guid = comptgt.target_guid (+);

  OPEN prop_columns_out FOR
    SELECT DISTINCT tgt.target_guid, prop.property_name, prop.property_value
      FROM MGMT_TARGET_PROPERTIES prop, MGMT_GROUP_CUSTOM_COLUMNS prefs,
           TABLE(CAST(l_SMP_EMD_TGT_OBJECT_TBL AS SMP_EMD_TGT_OBJECT_TBL)) tgt
     WHERE prefs.composite_target_guid = l_comp_guid
       AND (prefs.column_type = em_groups_ui.G_COLUMN_TYPE_PROPERTY OR
            prefs.column_type = em_groups_ui.G_COLUMN_TYPE_GEN_PROPERTY)
       AND prefs.property_name = prop.property_name
       AND tgt.target_guid = prop.target_guid;

  OPEN metric_values_out FOR
   SELECT curmet.target_guid, curmet.metric_guid,
           ROUND(curmet.value,2) AS value
      FROM mgmt_current_metrics curmet,
           (SELECT /*+ INDEX(mfta MGMT_FLAT_TARGET_ASSOC_IDX01) */ mfta.assoc_target_guid target_guid
            FROM mgmt_flat_Target_assoc  mfta
            WHERE mfta.is_membership = 1
              AND mfta.source_target_guid = l_comp_guid ) tgt
     WHERE curmet.metric_guid IN
             (SELECT member_metric_guid
                FROM MGMT_GROUP_CUSTOM_COLUMNS
               WHERE composite_target_guid = l_comp_guid
                 AND column_type = em_groups_ui.G_COLUMN_TYPE_METRIC
             )
       AND tgt.target_guid = curmet.target_guid;

  OPEN metric_sev_out FOR
 SELECT pa.object_guid AS target_guid, 
          pa.policy_guid AS metric_guid,
           NVL(sev.severity_code, MGMT_GLOBAL.G_SEVERITY_CLEAR) severity_code
     FROM mgmt_policy_assoc pa, mgmt_current_severity sev,
          (SELECT assoc_target_guid target_guid
            FROM mgmt_flat_Target_assoc 
            WHERE is_membership = 1
              AND source_target_guid = l_comp_guid ) tgt
     WHERE pa.object_guid = tgt.target_guid
       AND pa.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET
       AND pa.policy_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC
       AND pa.policy_guid IN
             (SELECT member_metric_guid
                FROM MGMT_GROUP_CUSTOM_COLUMNS
               WHERE composite_target_guid = l_comp_guid
                 AND column_type =  em_groups_ui.G_COLUMN_TYPE_METRIC
             )
       AND pa.object_guid = sev.target_guid (+)
       AND pa.policy_guid = sev.metric_guid (+);

END get_group_members_info;

--
-- Return the list of chart definition for a group
-- used to render the charts
PROCEDURE get_group_charts_list(
                target_name_in      IN   VARCHAR2,
                target_type_in      IN   VARCHAR2,
                charts_list_out     OUT  cursorType
) IS

  l_target_guid MGMT_TARGETS.target_guid%TYPE;
BEGIN

  l_target_guid := MGMT_TARGET.get_target_guid(
                    target_name_in, target_type_in);

  OPEN charts_list_out FOR
    SELECT DISTINCT c.comp_chart_guid, c.chart_type,
           c.display_order, c.targets_count, m.target_type,
           m.metric_guid, m.metric_name, m.metric_column,
           m.column_label_nlsid,m.column_label
      FROM MGMT_GROUP_CHART c, MGMT_METRICS m
     WHERE c.comp_target_guid = l_target_guid
       AND c.member_metric_guid = m.metric_guid
     ORDER BY c.display_order;
                                                                               
END get_group_charts_list;

-- Return the chart timeseries
PROCEDURE get_chart_data(
              target_name_in     IN  VARCHAR2,
              target_type_in     IN  VARCHAR2,
              chart_guid_in      IN  RAW,
              time_period_in     IN  VARCHAR2,
              timeseries_out     OUT cursorType,
              metric_unit_out    OUT cursorType)
IS
  l_target_guid   MGMT_TARGETS.target_guid%TYPE;
  l_comp_tz_region MGMT_TARGETS.timezone_region%TYPE;
  l_member_metric_guid MGMT_GROUP_CHART.member_metric_guid%TYPE;
  l_chart_type MGMT_GROUP_CHART.chart_type%TYPE;
  l_sum_metric_guid    MGMT_METRICS.metric_guid%TYPE;
  l_targets_count MGMT_GROUP_CHART.targets_count%TYPE;
  l_avg_column    MGMT_GROUP_SUMMARY_CHART_DEF.avg_column%TYPE := 0;
  l_min_column    MGMT_GROUP_SUMMARY_CHART_DEF.min_column%TYPE := 0;
  l_max_column    MGMT_GROUP_SUMMARY_CHART_DEF.max_column%TYPE := 0;
  l_sum_column    MGMT_GROUP_SUMMARY_CHART_DEF.sum_column%TYPE := 0;
  l_stdev_column  MGMT_GROUP_SUMMARY_CHART_DEF.stdev_column%TYPE := 0;
  l_mem_targets   MGMT_GRP_CHART_DISP_TZ_ARRAY := MGMT_GRP_CHART_DISP_TZ_ARRAY();
  i               INTEGER := 1;
  l_systimestamp TIMESTAMP WITH TIME ZONE;
  l_sysdate      DATE;

  l_days         NUMBER;
                                                                               
BEGIN
  -- get the composite target GUID and group timezone region
  BEGIN
    SELECT target_guid, timezone_region
      INTO l_target_guid, l_comp_tz_region
      FROM mgmt_targets
     WHERE target_name = target_name_in AND
           target_type = target_type_in;
                                                                               
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      raise MGMT_GLOBAL.target_does_not_exist;
  END;

  --Get metric units
  OPEN metric_unit_out FOR
  SELECT DISTINCT m.UNIT_NLSID
    FROM MGMT_METRICS m, MGMT_GROUP_CHART c
   WHERE c.comp_chart_guid = chart_guid_in
     AND c.member_metric_guid = m.metric_guid
     AND m.UNIT_NLSID is not null;

  -- TODO: ERROR handling...
  SELECT c.chart_type, c.member_metric_guid, c.targets_count,
         NVL(s.avg_column, 0), NVL(s.min_column, 0),
         NVL(s.max_column, 0), NVL(s.sum_column, 0),
         NVL(s.stdev_column, 0)
    INTO l_chart_type, l_member_metric_guid, l_targets_count,
         l_avg_column, l_min_column, l_max_column, 
         l_sum_column, l_stdev_column
    FROM MGMT_GROUP_CHART c, MGMT_GROUP_SUMMARY_CHART_DEF s
   WHERE c.comp_chart_guid = chart_guid_in 
     AND c.comp_chart_guid = s.comp_chart_guid (+);

  -- get current time stamp
  l_systimestamp := SYSTIMESTAMP;
  l_sysdate :=  CAST(l_systimestamp at time zone l_comp_tz_region as DATE);

  l_targets_count := l_targets_count + 1;

  IF time_period_in = 'byWeek'
  THEN
     l_days := 7;
  ELSE
     IF time_period_in = 'byMonth'
     THEN
        l_days := 31;
     ELSE
        l_days := 1;
     END IF;
  END IF;

  IF (l_chart_type = EM_GROUPS_UI.G_CHART_SELECTED_TARGETS)
  THEN
    SELECT MGMT_GRP_CHART_DISP_TZ_OBJ(
           sel.target_guid, l_member_metric_guid,
           ' ', t.target_name, t.timezone_region,
           CAST(l_systimestamp at time zone t.timezone_region as DATE)-l_days,
           CAST(l_systimestamp at time zone t.timezone_region as DATE)-l_sysdate )
      BULK COLLECT INTO l_mem_targets
      FROM MGMT_GROUP_CHART_SELTARGETS sel, MGMT_TARGETS t
     WHERE sel.comp_chart_guid = chart_guid_in
       AND t.target_guid = sel.target_guid;
  ELSIF (l_chart_type = EM_GROUPS_UI.G_CHART_PEAK_TARGETS)
  THEN
    -- targets list depends on the timeperiod selected
    IF time_period_in = 'byWeek'
    THEN
      --  WEEK TIMESERIES.......
        SELECT MGMT_GRP_CHART_DISP_TZ_OBJ(target_guid, l_member_metric_guid,
                                       ' ', target_name, timezone_region,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_days,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_sysdate)
          BULK COLLECT INTO l_mem_targets
        FROM   (SELECT MIN(tgt.target_name) target_name,
                       tgt.target_guid,
                       MIN(tgt.timezone_region) timezone_region
                FROM   mgmt_flat_target_assoc mem, mgmt_targets tgt,
                       mgmt_metrics_1day data
                WHERE  mem.source_target_guid = l_target_guid
                  AND  mem.is_membership = 1
                  AND  tgt.target_guid   = mem.assoc_target_guid
                  AND  data.target_guid  = tgt.target_guid
                  AND  data.metric_guid  = l_member_metric_guid
                  AND  data.key_value    = ' '
                  AND  data.rollup_timestamp >=
                           CAST(l_systimestamp at time zone tgt.timezone_region as DATE) - l_days
                GROUP BY tgt.target_guid
                ORDER BY MAX(data.value_maximum) DESC
               )
        WHERE  rownum < l_targets_count;
    ELSIF time_period_in = 'byMonth'
    THEN
      --  MONTH TIMESERIES.......
        SELECT MGMT_GRP_CHART_DISP_TZ_OBJ(target_guid, l_member_metric_guid,
                                       ' ', target_name, timezone_region,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_days,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_sysdate)
          BULK COLLECT INTO l_mem_targets
        FROM   (SELECT MIN(tgt.target_name) target_name,
                       tgt.target_guid,
                       MIN(tgt.timezone_region) timezone_region
                FROM   mgmt_flat_target_assoc mem, mgmt_targets tgt,
                       mgmt_metrics_1day data
                WHERE  mem.source_target_guid = l_target_guid
                  AND  mem.is_membership = 1
                  AND  tgt.target_guid   = mem.assoc_target_guid
                  AND  data.target_guid  = tgt.target_guid
                  AND  data.metric_guid  = l_member_metric_guid
                  AND  data.key_value    = ' '
                  AND  data.rollup_timestamp >=
                           CAST(l_systimestamp at time zone tgt.timezone_region as DATE) - l_days
                GROUP BY tgt.target_guid
                ORDER BY MAX(data.value_maximum) DESC
               )
        WHERE  rownum < l_targets_count;
    ELSE
      --  DAY TIMESERIES.......
        SELECT MGMT_GRP_CHART_DISP_TZ_OBJ(target_guid, l_member_metric_guid,
                                       ' ', target_name, timezone_region,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_days,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_sysdate)
          BULK COLLECT INTO l_mem_targets
        FROM   (SELECT MIN(tgt.target_name) target_name,
                       tgt.target_guid,
                       MIN(tgt.timezone_region) timezone_region
                FROM   mgmt_flat_target_assoc mem, mgmt_targets tgt,
                       mgmt_metrics_1hour data
                WHERE  mem.source_target_guid = l_target_guid
                  AND  mem.is_membership = 1
                  AND  tgt.target_guid   = mem.assoc_target_guid
                  AND  data.target_guid  = tgt.target_guid
                  AND  data.metric_guid  = l_member_metric_guid
                  AND  data.key_value    = ' '
                  AND  data.rollup_timestamp >=
                           CAST(l_systimestamp at time zone tgt.timezone_region as DATE) - l_days
                GROUP BY tgt.target_guid
                ORDER BY MAX(data.value_average) DESC
               )
        WHERE  rownum < l_targets_count;
    END IF;

  ELSIF (l_chart_type = EM_GROUPS_UI.G_CHART_HIGHEST_TARGETS)
  THEN

    -- get the Time series
    IF time_period_in = 'byWeek'
    THEN
      --  WEEK TIMESERIES.......
        SELECT MGMT_GRP_CHART_DISP_TZ_OBJ(target_guid, l_member_metric_guid,
                                       ' ', target_name, timezone_region,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_days,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_sysdate)
          BULK COLLECT INTO l_mem_targets
        FROM   (SELECT MIN(tgt.target_name) target_name,
                       tgt.target_guid,  
                       MIN(tgt.timezone_region) timezone_region
                FROM   mgmt_flat_target_assoc mem, mgmt_targets tgt, 
                       mgmt_metrics_1day data
                WHERE  mem.source_target_guid = l_target_guid
                  AND  mem.is_membership = 1
                  AND  tgt.target_guid   = mem.assoc_target_guid
                  AND  data.target_guid  = tgt.target_guid
                  AND  data.metric_guid  = l_member_metric_guid
                  AND  data.key_value    = ' '
                  AND  data.rollup_timestamp >= 
                           CAST(l_systimestamp at time zone tgt.timezone_region as DATE) - l_days
                GROUP BY tgt.target_guid
                ORDER BY AVG(data.value_average) DESC
               )
        WHERE  rownum < l_targets_count;
    ELSIF time_period_in = 'byMonth'
    THEN
      --  MONTH TIMESERIES.......
        SELECT MGMT_GRP_CHART_DISP_TZ_OBJ(target_guid, l_member_metric_guid,
                                       ' ', target_name, timezone_region,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_days,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_sysdate)
          BULK COLLECT INTO l_mem_targets
        FROM   (SELECT MIN(tgt.target_name) target_name,
                       tgt.target_guid,  
                       MIN(tgt.timezone_region) timezone_region
                FROM   mgmt_flat_target_assoc mem, mgmt_targets tgt, 
                       mgmt_metrics_1day data
                WHERE  mem.source_target_guid = l_target_guid
                  AND  mem.is_membership = 1
                  AND  tgt.target_guid   = mem.assoc_target_guid
                  AND  data.target_guid  = tgt.target_guid
                  AND  data.metric_guid  = l_member_metric_guid
                  AND  data.key_value    = ' '
                  AND  data.rollup_timestamp >= 
                           CAST(l_systimestamp at time zone tgt.timezone_region as DATE) - l_days
                GROUP BY tgt.target_guid
                ORDER BY AVG(data.value_average) DESC
               )
        WHERE  rownum < l_targets_count;

    ELSE
      --  DAY TIMESERIES.......
        SELECT MGMT_GRP_CHART_DISP_TZ_OBJ(target_guid, l_member_metric_guid,
                                       ' ', target_name, timezone_region,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_days,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_sysdate)
          BULK COLLECT INTO l_mem_targets
        FROM   (SELECT MIN(tgt.target_name) target_name,
                       tgt.target_guid,  
                       MIN(tgt.timezone_region) timezone_region
                FROM   mgmt_flat_target_assoc mem, mgmt_targets tgt, 
                       mgmt_metrics_1hour data
                WHERE  mem.source_target_guid = l_target_guid 
                  AND  mem.is_membership = 1
                  AND  tgt.target_guid   = mem.assoc_target_guid
                  AND  data.target_guid  = tgt.target_guid 
                  AND  data.metric_guid  = l_member_metric_guid
                  AND  data.key_value    = ' '
                  AND  data.rollup_timestamp >= 
                           CAST(l_systimestamp at time zone tgt.timezone_region as DATE) - l_days
                GROUP BY tgt.target_guid
                ORDER BY AVG(data.value_average) DESC
               )
        WHERE  rownum < l_targets_count;
    END IF;

  ELSIF (l_chart_type = EM_GROUPS_UI.G_CHART_LOWEST_TARGETS)
  THEN 
    -- get the Time series
    IF time_period_in = 'byWeek'
    THEN
      --  WEEK TIMESERIES.......
        SELECT MGMT_GRP_CHART_DISP_TZ_OBJ(target_guid, l_member_metric_guid,
                                       ' ', target_name, timezone_region,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_days,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_sysdate)
          BULK COLLECT INTO l_mem_targets
        FROM   (SELECT MIN(tgt.target_name) target_name,
                       tgt.target_guid,  
                       MIN(tgt.timezone_region) timezone_region
                FROM   mgmt_flat_target_assoc mem, mgmt_targets tgt, 
                       mgmt_metrics_1day data
                WHERE  mem.source_target_guid = l_target_guid
                  AND  mem.is_membership = 1
                  AND  tgt.target_guid   = mem.assoc_target_guid
                  AND  data.target_guid  = tgt.target_guid
                  AND  data.metric_guid  = l_member_metric_guid
                  AND  data.key_value    = ' '
                  AND  data.rollup_timestamp >= 
                           CAST(l_systimestamp at time zone tgt.timezone_region as DATE) - l_days
                GROUP BY tgt.target_guid
                ORDER BY AVG(data.value_average) 
               )
        WHERE  rownum < l_targets_count;
    ELSIF time_period_in = 'byMonth'
    THEN
      --  MONTH TIMESERIES.......
        SELECT MGMT_GRP_CHART_DISP_TZ_OBJ(target_guid, l_member_metric_guid,
                                       ' ', target_name, timezone_region,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_days,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_sysdate)
          BULK COLLECT INTO l_mem_targets
        FROM   (SELECT MIN(tgt.target_name) target_name,
                       tgt.target_guid,  
                       MIN(tgt.timezone_region) timezone_region
                FROM   mgmt_flat_target_assoc mem, mgmt_targets tgt, 
                       mgmt_metrics_1day data
                WHERE  mem.source_target_guid = l_target_guid
                  AND  mem.is_membership = 1
                  AND  tgt.target_guid   = mem.assoc_target_guid
                  AND  data.target_guid  = tgt.target_guid
                  AND  data.metric_guid  = l_member_metric_guid
                  AND  data.key_value    = ' '
                  AND  data.rollup_timestamp >= 
                           CAST(l_systimestamp at time zone tgt.timezone_region as DATE) - l_days
                GROUP BY tgt.target_guid
                ORDER BY AVG(data.value_average) 
               )
        WHERE  rownum < l_targets_count;

    ELSE
      --  DAY TIMESERIES.......
        SELECT MGMT_GRP_CHART_DISP_TZ_OBJ(target_guid, l_member_metric_guid,
                                       ' ', target_name, timezone_region,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_days,
                                       CAST(l_systimestamp at time zone timezone_region as DATE)-l_sysdate)
          BULK COLLECT INTO l_mem_targets
        FROM   (SELECT MIN(tgt.target_name) target_name,
                       tgt.target_guid,  
                       MIN(tgt.timezone_region) timezone_region
                FROM   mgmt_flat_target_assoc mem, mgmt_targets tgt, 
                       mgmt_metrics_1hour data
                WHERE  mem.source_target_guid = l_target_guid 
                  AND  mem.is_membership = 1
                  AND  tgt.target_guid   = mem.assoc_target_guid
                  AND  data.target_guid  = tgt.target_guid 
                  AND  data.metric_guid  = l_member_metric_guid
                  AND  data.key_value    = ' '
                  AND  data.rollup_timestamp >= 
                           CAST(l_systimestamp at time zone tgt.timezone_region as DATE) - l_days
                GROUP BY tgt.target_guid
                ORDER BY AVG(data.value_average) 
               )
        WHERE  rownum < l_targets_count;
    END IF;
  ELSIF (l_chart_type = EM_GROUPS_UI.G_CHART_SUMMARY_METRIC)
  THEN
    -- get the guid for the metric level
    l_sum_metric_guid := MGMT_METRIC.get_metric_guid(
                       target_type_in, G_SUM_METRIC, G_SUM_METRIC_COLUMN);

    i := 1;
    IF (l_avg_column = 1)
    THEN
      l_mem_targets.extend(1);
      l_mem_targets(i) := MGMT_GRP_CHART_DISP_TZ_OBJ
                           (l_target_guid, l_sum_metric_guid,
                            get_avg_metric_label(l_member_metric_guid),
                            'AVG', l_comp_tz_region, l_sysdate-l_days,0);

      i := i+1;
    END IF;
    IF (l_max_column = 1)
    THEN
      l_mem_targets.extend(1);
      l_mem_targets(i) := MGMT_GRP_CHART_DISP_TZ_OBJ
                           (l_target_guid, l_sum_metric_guid,
                            get_max_metric_label(l_member_metric_guid),
                            'MAX', l_comp_tz_region, l_sysdate-l_days,0);
      i := i+1;
    END IF;
    IF (l_min_column = 1)
    THEN
      l_mem_targets.extend(1);
      l_mem_targets(i) := MGMT_GRP_CHART_DISP_TZ_OBJ
                           (l_target_guid, l_sum_metric_guid,
                            get_min_metric_label(l_member_metric_guid),
                            'MIN', l_comp_tz_region, l_sysdate-l_days,0);
      i := i+1;
    END IF;
    IF (l_sum_column = 1)
    THEN
      l_mem_targets.extend(1);
      l_mem_targets(i) := MGMT_GRP_CHART_DISP_TZ_OBJ
                           (l_target_guid, l_sum_metric_guid,
                            get_sum_metric_label(l_member_metric_guid),
                            'SUM', l_comp_tz_region, l_sysdate-l_days,0);
      i := i+1;
    END IF;
    IF (l_stdev_column = 1)
    THEN
      l_mem_targets.extend(1);
      l_mem_targets(i) := MGMT_GRP_CHART_DISP_TZ_OBJ
                           (l_target_guid, l_sum_metric_guid,
                            get_stdev_metric_label(l_member_metric_guid),
                            'STDEV', l_comp_tz_region, l_sysdate-l_days,0);
      i := i+1;
    END IF;
  END IF;

  IF (l_chart_type = EM_GROUPS_UI.G_CHART_SUMMARY_METRIC)
  THEN
    -- get the Time series
    IF time_period_in = 'byWeek'
    THEN
      --  WEEK TIMESERIES.......
      OPEN timeseries_out FOR
         SELECT tgt.target_name, data.rollup_timestamp, data.value_average
           FROM TABLE(CAST(l_mem_targets AS MGMT_GRP_CHART_DISP_TZ_ARRAY)) tgt,
                mgmt_metrics_1hour data
          WHERE data.target_guid = l_target_guid
            AND data.metric_guid = l_sum_metric_guid
            AND data.key_value   = tgt.key_value
            AND data.rollup_timestamp >= l_sysdate - l_days
          ORDER BY 2;
    ELSIF time_period_in = 'byMonth'
    THEN
      --  MONTH TIMESERIES.......
      OPEN timeseries_out FOR
         SELECT tgt.target_name, data.rollup_timestamp, data.value_average
           FROM TABLE(CAST(l_mem_targets AS MGMT_GRP_CHART_DISP_TZ_ARRAY)) tgt,
                mgmt_metrics_1day data
          WHERE data.target_guid = l_target_guid
            AND data.metric_guid = l_sum_metric_guid
            AND data.key_value   = tgt.key_value
            AND data.rollup_timestamp >= l_sysdate - l_days
          ORDER BY 2;
    ELSE
      --  DAY TIMESERIES.......
      OPEN timeseries_out FOR
         SELECT tgt.target_name, data.collection_timestamp, data.value
           FROM TABLE(CAST(l_mem_targets AS MGMT_GRP_CHART_DISP_TZ_ARRAY)) tgt,
                mgmt_metrics_raw data
          WHERE data.target_guid = l_target_guid
            AND data.metric_guid = l_sum_metric_guid
            AND data.key_value   = tgt.key_value
            AND data.collection_timestamp >= l_sysdate - l_days
          ORDER BY 2;
    END IF;
  ELSE -------------- Not Summary charts --------------
    -- get the Time series
    IF time_period_in = 'byWeek'
    THEN
      --  WEEK TIMESERIES.......
      OPEN timeseries_out FOR
         SELECT /*+ ORDERED INDEX(data) USE_NL(tgt data) */ tgt.target_name,
                data.rollup_timestamp+tgt.time_delta, 
                data.value_average
           FROM TABLE(CAST(l_mem_targets AS MGMT_GRP_CHART_DISP_TZ_ARRAY)) tgt,
                mgmt_metrics_1hour data
          WHERE data.target_guid = tgt.target_guid
            AND data.metric_guid = tgt.metric_guid
            AND data.key_value = ' '
            AND data.rollup_timestamp >= tgt.time_stamp
          ORDER BY 2;
    ELSIF time_period_in = 'byMonth'
    THEN
      --  MONTH TIMESERIES.......
      OPEN timeseries_out FOR
         SELECT /*+ ORDERED INDEX(data) USE_NL(tgt data) */ tgt.target_name,
                data.rollup_timestamp+tgt.time_delta, 
                data.value_average
           FROM TABLE(CAST(l_mem_targets AS MGMT_GRP_CHART_DISP_TZ_ARRAY)) tgt,
                mgmt_metrics_1day data
          WHERE data.target_guid = tgt.target_guid
            AND data.metric_guid = tgt.metric_guid
            AND data.key_value = ' '
            AND data.rollup_timestamp >= tgt.time_stamp
          ORDER BY 2;
    ELSE
      --  DAY TIMESERIES.......
      OPEN timeseries_out FOR
         SELECT /*+ ORDERED INDEX(data) USE_NL(tgt data) */ tgt.target_name,
                data.collection_timestamp+tgt.time_delta, 
                data.value
           FROM TABLE(CAST(l_mem_targets AS MGMT_GRP_CHART_DISP_TZ_ARRAY)) tgt,
                mgmt_metrics_raw data
          WHERE data.target_guid = tgt.target_guid
            AND data.metric_guid = tgt.metric_guid
            AND data.key_value = ' '
            AND data.collection_timestamp >= tgt.time_stamp
          ORDER BY 2;
    END IF;
  END IF;

END get_chart_data;

------------------------------------------------------------------------
--  Topology Related API
------------------------------------------------------------------------   

-- Gets the members and associations between the members of a target
PROCEDURE get_members_and_associations
  (p_target_name IN mgmt_targets.target_name%TYPE,
   p_target_type IN mgmt_targets.target_type%TYPE,
   members_out OUT cursorType,
   associations_out OUT cursorType)
IS 
    l_guid_array MGMT_TARGET_GUID_ARRAY := MGMT_TARGET_GUID_ARRAY();
    l_target_guid mgmt_targets.target_guid%TYPE;

    i NUMBER := 1;
    
    CURSOR c_members IS 
        SELECT t1.assoc_target_guid target_guid
        FROM mgmt_target_assocs t1, mgmt_targets t2, mgmt_target_assoc_defs t3
        WHERE t1.source_target_guid = t2.target_guid
          AND t2.target_name = p_target_name
          AND t2.target_type = p_target_type
          AND t1.assoc_guid = t3.assoc_guid
          AND t3.association_type = 'contains';
BEGIN
    FOR guid IN c_members LOOP
        l_guid_array.EXTEND;
        l_guid_array(i) := guid.target_guid;
        i := i+1;
    END LOOP;

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

    OPEN associations_out FOR 
        SELECT t1.source_target_guid, t1.assoc_target_guid, t2.association_type
        FROM mgmt_target_assocs t1, mgmt_target_assoc_defs t2 
        WHERE t1.assoc_guid = t2.assoc_guid AND 
              (t1.source_target_guid IN 
                (SELECT column_value 
                 FROM TABLE(CAST(l_guid_array AS MGMT_TARGET_GUID_ARRAY))) AND
               t1.assoc_target_guid IN
                 (SELECT column_value 
                  FROM TABLE(CAST(l_guid_array AS MGMT_TARGET_GUID_ARRAY))) AND 
               (t1.scope_target_guid = l_target_guid OR
               t1.scope_target_guid = MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID) AND
               (t2.association_type = MGMT_ASSOC.ASSOC_TYPE_RELATES_TO)); 
   
    OPEN members_out FOR
        SELECT column_value target_guid
        FROM TABLE(CAST(l_guid_array AS MGMT_TARGET_GUID_ARRAY));
        
END get_members_and_associations;
  

-- todo: update the comments for curstor in pkgdef
-- todo: this query (adapted from mgmt_target.get_target_list_view_summary) is 
-- VERY INEFFICIENT. It computes the alerts, policy violations for all the 
-- targets and then filters out..need to structure it differently
PROCEDURE get_target_list_details
  (target_guid_list_in IN MGMT_TARGET_GUID_ARRAY,
   details_out OUT cursorType)
IS

BEGIN
  
  OPEN details_out FOR
    SELECT tgt.target_name,
           tgt.target_type,
           tgt.display_name,
           tgt.type_display_name,
           DECODE(avail_status.cur_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail_status.cur_status) cur_status,
           NVL(alltgt.warning_count,0)+NVL(comptgt.warning_count,0) AS warning_count,
           NVL(alltgt.critical_count,0)+NVL(comptgt.critical_count,0) AS critical_count,
           -1 AS policy_vc,
           tgt.target_guid,
           tgt.host_name
      FROM mgmt_targets tgt,
           (SELECT cur_status,
                   target_guid
              FROM (SELECT avail.current_status as cur_status,
                           avail.target_guid target_guid
                      FROM mgmt_current_availability avail,
                           mgmt_targets tgt
                     WHERE tgt.target_guid=avail.target_guid
                    UNION ALL
                   (SELECT DECODE(SUM(DECODE(avail.current_status,1,0,1)),0,1,0) cur_status,
                           mem.composite_target_guid target_guid
                      FROM mgmt_current_availability avail,
                           mgmt_flat_target_memberships mem
                     WHERE avail.target_guid=mem.member_target_guid
                       AND mem.composite_target_guid NOT IN
                           (SELECT target_guid FROM mgmt_current_availability)
                  GROUP BY mem.composite_target_guid))) avail_status,
           (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,
                   target_guid tguid
              FROM mgmt_current_severity
          GROUP BY target_guid ) alltgt,
           (SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                   SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                   tm.composite_target_guid tguid
              FROM mgmt_flat_target_memberships tm,
                   mgmt_current_severity cs
             WHERE tm.member_target_guid = cs.target_guid
          GROUP BY tm.composite_target_guid) comptgt,
          TABLE(CAST(target_guid_list_in AS MGMT_TARGET_GUID_ARRAY)) guids
      WHERE tgt.target_guid = guids.column_value
        AND tgt.target_guid = avail_status.target_guid(+)
        AND tgt.target_guid = comptgt.tguid(+)
        AND tgt.target_guid = alltgt.tguid(+)
      ORDER BY tgt.type_display_name, tgt.target_name;
  
END get_target_list_details;


PROCEDURE expand_node
  (parent_guid_in IN mgmt_targets.target_guid%TYPE,
   sys_tgt_name_in IN mgmt_targets.target_name%TYPE,
   sys_tgt_type_in IN mgmt_targets.target_type%TYPE,
   existing_guids_in IN MGMT_TARGET_GUID_ARRAY,
   updated_guids_out OUT cursorType,
   associations_out OUT cursorType,
   outgoing_link_count_out OUT cursorType)
IS
    l_sys_tgt_guid mgmt_targets.target_guid%TYPE;
    l_member_guids MGMT_TARGET_GUID_ARRAY := MGMT_TARGET_GUID_ARRAY();
    l_children_guids MGMT_TARGET_GUID_ARRAY := MGMT_TARGET_GUID_ARRAY();
    l_updated_guids MGMT_TARGET_GUID_ARRAY := MGMT_TARGET_GUID_ARRAY();
    i NUMBER := 1;
    l_is_member BOOLEAN := FALSE;

    CURSOR c_members(p_sys_tgt_guid mgmt_targets.target_guid%TYPE) IS 
        SELECT t1.assoc_target_guid target_guid
        FROM mgmt_target_assocs t1, mgmt_target_assoc_defs t2
        WHERE t1.source_target_guid = p_sys_tgt_guid
          AND t1.assoc_guid = t2.assoc_guid
          AND t2.association_type = 'contains';

    CURSOR c_children (p_sys_tgt_guid mgmt_targets.target_guid%TYPE) IS
        SELECT t1.assoc_target_guid target_guid
        FROM mgmt_target_assocs t1, mgmt_target_assoc_defs t2
        WHERE t1.source_target_guid = parent_guid_in 
          AND t1.assoc_guid = t2.assoc_guid   
          AND (t1.scope_target_guid = p_sys_tgt_guid OR 
               t1.scope_target_guid = MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID) 
          AND t2.association_type = MGMT_ASSOC.ASSOC_TYPE_RELATES_TO;

BEGIN
    l_sys_tgt_guid := mgmt_target.get_target_guid(sys_tgt_name_in,
                                                  sys_tgt_type_in);

    FOR guid IN c_members(l_sys_tgt_guid) LOOP
        l_member_guids.EXTEND;
        l_member_guids(i) := guid.target_guid;
        IF l_member_guids(i) = parent_guid_in THEN
            l_is_member := TRUE;
        END IF; 
        i := i+1;
    END LOOP;

    IF NOT l_is_member THEN
        -- need to do something here
        i := 1;
    END IF;

    i := 1;
    FOR child_guid IN c_children(l_sys_tgt_guid) LOOP
        l_children_guids.EXTEND;
        l_children_guids(i) := child_guid.target_guid;
        i := i + 1;
    END LOOP;  

    i := 1;
    FOR guid IN  
        (((SELECT column_value target_guid
          FROM TABLE(CAST(existing_guids_in AS MGMT_TARGET_GUID_ARRAY))) 
        UNION
        (SELECT column_value target_guid
         FROM TABLE(CAST(l_children_guids AS MGMT_TARGET_GUID_ARRAY))))
        INTERSECT
        (SELECT column_value target_guid
         FROM TABLE(CAST(l_member_guids AS MGMT_TARGET_GUID_ARRAY)))) LOOP
        l_updated_guids.EXTEND;
        l_updated_guids(i) := guid.target_guid;
        i := i + 1;
    END LOOP;   
/*    
    i := 1;
    FOR guid IN c_updated LOOP
        l_updated_guids.EXTEND;
        l_updated_guids(i) := guid.target_guid;
        i := i + 1;
    END LOOP;
*/    
    OPEN associations_out FOR 
        SELECT t1.source_target_guid, t1.assoc_target_guid, t2.association_type
        FROM mgmt_target_assocs t1, mgmt_target_assoc_defs t2 
        WHERE t1.assoc_guid = t2.assoc_guid  
          AND t1.source_target_guid IN 
                (SELECT column_value 
                 FROM TABLE(CAST(l_updated_guids AS MGMT_TARGET_GUID_ARRAY))) 
          AND t1.assoc_target_guid IN
                (SELECT column_value 
                 FROM TABLE(CAST(l_updated_guids AS MGMT_TARGET_GUID_ARRAY)))
          AND (t1.scope_target_guid = l_sys_tgt_guid OR
               t1.scope_target_guid = MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID)
          AND t2.association_type = MGMT_ASSOC.ASSOC_TYPE_RELATES_TO; 

    OPEN updated_guids_out FOR
        SELECT column_value target_guid
        FROM TABLE(CAST(l_updated_guids AS MGMT_TARGET_GUID_ARRAY));

    OPEN outgoing_link_count_out FOR 
        SELECT source_target_guid, count(*) out_links
        FROM mgmt_target_assocs t1, mgmt_target_assoc_defs t2 
        WHERE t1.assoc_guid = t2.assoc_guid
          AND t1.source_target_guid IN 
                (SELECT column_value
                 FROM TABLE(CAST(l_updated_guids AS MGMT_TARGET_GUID_ARRAY)))
          AND t1.assoc_target_guid IN 
                (SELECT column_value
                 FROM TABLE(CAST(l_member_guids AS MGMT_TARGET_GUID_ARRAY)))
          AND (t1.scope_target_guid = l_sys_tgt_guid OR
               t1.scope_target_guid = MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID)
          AND t2.association_type = MGMT_ASSOC.ASSOC_TYPE_RELATES_TO     
        GROUP BY source_target_guid;

END expand_node;

-- rolls up various values like alerts, policy violations, etc. for an 
-- aggregate target.
PROCEDURE rollup
    (aggr_target_name_in IN mgmt_targets.target_name%TYPE,
     aggr_target_type_in IN mgmt_targets.target_type%TYPE,
     member_details IN NUMBER,
     rolled_up_details_out OUT cursorType)
IS 
    TYPE TARGET_DETAILS_TABLE IS TABLE OF TARGET_DETAILS INDEX BY VARCHAR(64);
    TYPE ROLLED_UP_DETAILS_TABLE IS TABLE OF ROLLED_UP_DETAILS INDEX BY 
        VARCHAR(64);
    members_own_details TARGET_DETAILS_TABLE;
    members_rolled_up_details ROLLED_UP_DETAILS_TABLE;
    members_rolled_up_details_arr ROLLED_UP_DETAILS_ARRAY;
    parent_child_array MGMT_AGGR_MEMBERS_ARRAY;
    
    l_agg_target_guid mgmt_targets.target_guid%TYPE;
  
    i NUMBER := 1;
    iterator VARCHAR2(64);
    
    FUNCTION do_union
        (guids1 IN GUID_ARRAY,
         guids2 IN GUID_ARRAY)
    RETURN GUID_ARRAY
    IS
        i NUMBER := 1;
        union_guids GUID_ARRAY := GUID_ARRAY();
    BEGIN
        FOR iter IN
        ( 
            SELECT column_value AS guid
            FROM TABLE(CAST(guids1 AS GUID_ARRAY))
            UNION
            SELECT column_value AS guid
            FROM TABLE(CAST(guids2 AS GUID_ARRAY))
        )
        LOOP
            union_guids.extend;
            union_guids(i) := iter.guid;
            i := i + 1;
        END LOOP;

        return union_guids;
    END do_union;

    PROCEDURE compute_rolled_up_details
        (target_guid_in IN mgmt_targets.target_guid%TYPE)
    IS
        children_guids GUID_ARRAY;
        num_children NUMBER := 0;
        guid mgmt_targets.target_guid%TYPE;
        status NUMBER;
    BEGIN
        IF (members_rolled_up_details(target_guid_in) IS NOT NULL) THEN
            -- the rolled up values for this target have been initialized 
            -- earlier
            return;
        END IF;
    
        -- Get the guids for all the direct children of this target
        FOR children IN
        ( 
            SELECT assoc_target_guid
              FROM (TABLE(CAST(parent_child_array AS MGMT_AGGR_MEMBERS_ARRAY)))
             WHERE source_target_guid = target_guid_in
        )
        LOOP 
            IF (num_children = 0) THEN
                children_guids := GUID_ARRAY();
            END IF;
            num_children := num_children + 1;
            children_guids.extend(1);
            children_guids(num_children) := children.assoc_target_guid;
        END LOOP;

        -- To initialize, copy the target's own values into rolled up values 
        -- table
        members_rolled_up_details(target_guid_in) :=
            ROLLED_UP_DETAILS(members_own_details(target_guid_in).target_guid,
                             members_own_details(target_guid_in).target_name,
                             members_own_details(target_guid_in).target_type,
                             0, -- number of members
                             0, -- number of members up
                             0, -- number of members down
                             0, -- number of members blacked out
                             0, -- number of members in the unknown state
                             members_own_details(target_guid_in).warning_alerts,
                             members_own_details(target_guid_in).critical_alerts,
                             members_own_details(target_guid_in).information_pv,
                             members_own_details(target_guid_in).warning_pv,
                             members_own_details(target_guid_in).critical_pv,
                             members_own_details(target_guid_in).current_status,
                             members_own_details(target_guid_in).host_name,
                             GUID_ARRAY(),
                             GUID_ARRAY());
    
        -- For rolling up the number of alerts and policy violations for a 
        -- target, the target itself is always used. Hence add it to the array 
        -- which contains the guids of the targets to be used in rolling up
        -- alerts and policy violations
        members_rolled_up_details(target_guid_in).alerts_pv_guids.extend;    
        members_rolled_up_details(target_guid_in).alerts_pv_guids(1) :=
            target_guid_in;
        
        IF (children_guids IS NULL) THEN
            -- This is a non aggregate target. 
            members_rolled_up_details(target_guid_in).mem_count_guids.extend;
            members_rolled_up_details(target_guid_in).mem_count_guids(1) :=
                target_guid_in;
        ELSIF (members_own_details(target_guid_in).response_defined != 0) THEN
            -- This is an aggregate target and has response metric defined. It
            -- itself will be used in rolling up the number of members.
            members_rolled_up_details(target_guid_in).mem_count_guids.extend;
            members_rolled_up_details(target_guid_in).mem_count_guids(1) :=
                target_guid_in;
        END IF;
                
        IF (children_guids IS NULL) THEN
            -- If this is a non aggregate target, we are done for this target
            return;                                   
        END IF;  
        
        -- For aggregate targets, we need to use the values of the children 
        -- targets to compute the rolled up values.
        FOR c IN 1..children_guids.COUNT
        LOOP
            -- recursively compute the rolled up values for a child
            compute_rolled_up_details(children_guids(c));
        END LOOP;

        -- To get the set of targets to be used in rolling up alerts and 
        -- policy violations do a union of the corresponding set for each of
        -- the direct children
        FOR c IN 1..children_guids.COUNT
        LOOP
        --    members_rolled_up_details(target_guid_in).alerts_pv_guids := 
        --        SET(members_rolled_up_details(target_guid_in).alerts_pv_guids)
        --        MULTISET UNION DISTINCT
        --        SET(members_rolled_up_details(children_guids(c)).alerts_pv_guids);
              members_rolled_up_details(target_guid_in).alerts_pv_guids :=
                 do_union(
                 members_rolled_up_details(target_guid_in).alerts_pv_guids, 
                 members_rolled_up_details(children_guids(c)).alerts_pv_guids);
        END LOOP;
        
        -- Do a union of the corresponding sets for each of its children to get
        -- the set of targets to be used for calculating member count.
        FOR c IN 1..children_guids.COUNT
        LOOP
        --    members_rolled_up_details(target_guid_in).mem_count_guids :=
        --        SET(members_rolled_up_details(target_guid_in).mem_count_guids) 
        --        MULTISET UNION DISTINCT
        --        SET(members_rolled_up_details(children_guids(c)).mem_count_guids);
            members_rolled_up_details(target_guid_in).mem_count_guids:=
                do_union(
                members_rolled_up_details(target_guid_in).mem_count_guids,
                members_rolled_up_details(children_guids(c)).mem_count_guids);
        END LOOP;
        
        
        -- Once the sets of targets to be used for rolling up have been prepared
        -- use those sets to roll up the values.
        
        FOR i IN 1..members_rolled_up_details(target_guid_in).alerts_pv_guids.COUNT
        LOOP
            guid := members_rolled_up_details(target_guid_in).alerts_pv_guids(i);
            -- The alerts and policy violations for the target itself have 
            -- already been copied over to members_rolled_up_details from
            -- members_own_details
            IF (guid != target_guid_in)  
            THEN
                -- A target's alerts are counted only if it is up
                IF (members_own_details(guid).current_status = MGMT_GLOBAL.G_STATUS_UP)
                THEN 
                    members_rolled_up_details(target_guid_in).warning_alerts :=
                        members_rolled_up_details(target_guid_in).warning_alerts +
                        members_own_details(guid).warning_alerts;
            
                    members_rolled_up_details(target_guid_in).critical_alerts :=
                        members_rolled_up_details(target_guid_in).critical_alerts +
                        members_own_details(guid).critical_alerts;
                END IF;

                members_rolled_up_details(target_guid_in).information_pv :=
                    members_rolled_up_details(target_guid_in).information_pv +
                    members_own_details(guid).information_pv;
                
                members_rolled_up_details(target_guid_in).warning_pv :=
                    members_rolled_up_details(target_guid_in).warning_pv +
                    members_own_details(guid).warning_pv;
            
                members_rolled_up_details(target_guid_in).critical_pv :=
                    members_rolled_up_details(target_guid_in).critical_pv +
                    members_own_details(guid).critical_pv;
            END IF;
        END LOOP;
        
        FOR i IN 1..members_rolled_up_details(target_guid_in).mem_count_guids.COUNT
        LOOP
            guid := members_rolled_up_details(target_guid_in).mem_count_guids(i);
            
            -- The target itself will not be counted in counting the number of
            -- members for itself, but it will be counted for counting the 
            -- number of members for another aggregate target of which it is a 
            -- member.
            IF (guid != target_guid_in) 
            THEN
                members_rolled_up_details(target_guid_in).num_members := 
                    members_rolled_up_details(target_guid_in).num_members + 1;
                
                status := members_own_details(guid).current_status;
                IF (status = MGMT_GLOBAL.G_STATUS_UP) THEN 
                    members_rolled_up_details(target_guid_in).num_up := 
                        members_rolled_up_details(target_guid_in).num_up + 1;
                ELSIF (status = MGMT_GLOBAL.G_STATUS_DOWN) THEN
                    members_rolled_up_details(target_guid_in).num_down := 
                        members_rolled_up_details(target_guid_in).num_down + 1;
                ELSIF (status = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
                    members_rolled_up_details(target_guid_in).num_blacked_out :=
                        members_rolled_up_details(target_guid_in).num_blacked_out + 1;
                ELSE
                    members_rolled_up_details(target_guid_in).num_unknown :=
                        members_rolled_up_details(target_guid_in).num_unknown + 1;
                END IF;
            END IF;    
        END LOOP;
    END compute_rolled_up_details; 
BEGIN
  -- Get the guid for the aggregate target
  l_agg_target_guid := mgmt_target.get_target_guid(aggr_target_name_in,
                                                   aggr_target_type_in);

  FOR members IN 
  (
    -- Get all the parent-child relationships in the membership hierarchy of 
    -- this aggregate target.
    SELECT t1.target_name AS source_target_name,
           t1.target_type AS source_target_type,
           t1.target_guid AS source_target_guid,
           t2.target_name AS assoc_target_name,
           t2.target_type AS assoc_target_type,
           t2.target_guid AS assoc_target_guid
      FROM mgmt_targets t1, mgmt_targets t2, 
           mgmt_target_assocs ta, mgmt_target_assoc_defs defs 
     WHERE (
             ta.source_target_guid = l_agg_target_guid 
             OR ta.source_target_guid IN 
               (
                 -- Get all the aggregate targets that are in the membership
                 -- hierarchy for this aggregate target, ie targets which
                 -- are either directly members of the given aggregate target
                 -- or which are members of some other aggregate targets which
                 -- are members of the given aggregate target.
                 SELECT assoc_target_guid
                   FROM mgmt_flat_target_assoc
                  WHERE source_target_guid = l_agg_target_guid
                    AND is_membership = 1
                    AND assoc_target_guid IN
                        (
                          -- Get all the aggregate targets
                          SELECT DISTINCT source_target_guid
                            FROM mgmt_flat_target_assoc
                           WHERE is_membership = 1
                        )
               )
           )
       AND t1.target_guid = ta.source_target_guid
       AND t2.target_guid = ta.assoc_target_guid
       AND ta.assoc_guid = defs.assoc_guid
       AND defs.association_type = mgmt_assoc.ASSOC_TYPE_CONTAINS
  )
  LOOP 
    IF (i = 1) THEN
      parent_child_array := MGMT_AGGR_MEMBERS_ARRAY();
    END IF;

    parent_child_array.extend(1);
    parent_child_array(i) := MGMT_AGGR_MEMBERS(members.source_target_name,
                                              members.source_target_type,
                                              members.source_target_guid,
                                              members.assoc_target_name,
                                              members.assoc_target_type,
                                              members.assoc_target_guid); 
   
    i := i + 1;
  END LOOP;  

  FOR member_details IN 
  (
    -- Get the details for each target in the membership hierarchy of the 
    -- given aggregate target. The details are for each target itself only,
    -- ie if a particular target is an aggregate target, then the details are 
    -- not rolled up details. 
    SELECT members.assoc_target_guid AS target_guid,
           members.assoc_target_name AS target_name,
           members.assoc_target_type AS target_type,
           NVL(alerts.warning_count, 0) AS warning_alerts,
           NVL(alerts.critical_count, 0) AS critical_alerts,
           NVL(policy.information_count, 0) AS information_pv,
           NVL(policy.warning_count, 0) AS warning_pv,
           NVL(policy.critical_count, 0) AS critical_pv,
           avail.current_status AS current_status,
--           NVL(response.response_defined, 0) AS response_defined,
           tgt.host_name AS host_name
      FROM (
             SELECT *
               FROM TABLE(CAST(parent_child_array AS MGMT_AGGR_MEMBERS_ARRAY))
             UNION ALL
             SELECT aggr_target_name_in, aggr_target_type_in, l_agg_target_guid,
                    aggr_target_name_in, aggr_target_type_in, l_agg_target_guid 
               FROM DUAL
           ) members,
           mgmt_targets tgt,
           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,
                    cs.target_guid 
               FROM mgmt_current_severity cs
               WHERE cs.severity_type != MGMT_GLOBAL.G_SEVERITY_TYPE_AVAILABILITY
           GROUP BY target_guid
           ) alerts,
           (
             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,
                    cv.target_guid
               FROM mgmt_current_violation cv
              WHERE violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY 
                AND ( exempt_code = MGMT_GLOBAL.G_SUPPRESS_NONE OR
                      ( exempt_code = MGMT_GLOBAL.G_SUPPRESS_DATE AND
                        exempt_until <= SYSDATE 
                      )
                    )
           GROUP BY target_guid
           ) policy
--           (
--             SELECT target_guid, count(*) response_defined
--               FROM mgmt$target_type
--              WHERE metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
--                AND metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN 
--           GROUP BY target_guid   
--           ) response
     WHERE members.assoc_target_guid = alerts.target_guid(+)
       AND members.assoc_target_guid = policy.target_guid(+)
       AND members.assoc_target_guid = avail.target_guid(+)
--       AND members.assoc_target_guid = response.target_guid(+)
       AND members.assoc_target_guid = tgt.target_guid(+)
  )
  LOOP
    members_own_details(member_details.target_guid) := 
        TARGET_DETAILS(member_details.target_guid,
                      member_details.target_name,
                      member_details.target_type,
                      member_details.warning_alerts,
                      member_details.critical_alerts,
                      member_details.information_pv,
                      member_details.warning_pv,
                      member_details.critical_pv,
                      member_details.current_status,
                      member_details.host_name,
                      0, -- to be removed if we dont use metrics_defined
                      0  -- set the response defined to be 0. It is set 
                         -- to the correct value in the next IF statement
                      );
                      
    IF (member_details.current_status IS NOT NULL) THEN
      -- The response metric is defined for this target
      members_own_details(member_details.target_guid).response_defined := 1;
    END IF;
    
    members_rolled_up_details(member_details.target_guid) := null;                  
  END LOOP;

  -- Now, compute the rolled up details.
  compute_rolled_up_details(l_agg_target_guid);

  IF (member_details = 1) THEN
    -- we need to get the details for the aggregate target as well as all its
    -- members 
    i := 1;
    iterator := members_rolled_up_details.FIRST;
    WHILE iterator IS NOT NULL
    LOOP
      IF (i = 1) THEN
        members_rolled_up_details_arr := ROLLED_UP_DETAILS_ARRAY();
      END IF;
      
      members_rolled_up_details_arr.extend(1);
      members_rolled_up_details_arr(i) := members_rolled_up_details(iterator);
      
      iterator := members_rolled_up_details.NEXT(iterator);  
      i := i + 1;
    END LOOP;       
  
    OPEN rolled_up_details_out FOR
      SELECT target_guid, target_name, target_type,
             num_members, num_up, num_down, num_blacked_out, num_unknown,
             warning_alerts, critical_alerts,
             information_pv, warning_pv, critical_pv,
             NVL(current_status, -1) AS current_status, 
             host_name
        FROM TABLE(CAST(members_rolled_up_details_arr AS ROLLED_UP_DETAILS_ARRAY)); 
  ELSE 
    -- We need to get the details only for the aggregate target
    members_rolled_up_details_arr := ROLLED_UP_DETAILS_ARRAY();
    members_rolled_up_details_arr.extend(1);
    members_rolled_up_details_arr(1) := 
      members_rolled_up_details(l_agg_target_guid);
    OPEN rolled_up_details_out FOR 
      SELECT target_guid, target_name, target_type,
             num_members, num_up, num_down, num_blacked_out, num_unknown,
             warning_alerts, critical_alerts,
             information_pv, warning_pv, critical_pv,
             NVL(current_status, -1) AS current_status,
             host_name
        FROM TABLE(CAST(members_rolled_up_details_arr AS ROLLED_UP_DETAILS_ARRAY));   
  END IF;  
  
END rollup;
  

PROCEDURE get_summary_info
  (target_name_in IN mgmt_targets.target_name%TYPE,
   target_type_in IN mgmt_targets.target_type%TYPE,
   critical_alerts_out OUT NUMBER,
   warning_alerts_out OUT NUMBER,
   policy_vc_out OUT NUMBER,
   num_targets_out OUT NUMBER)
IS

BEGIN
    SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)), 
           SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0))
      INTO warning_alerts_out, critical_alerts_out
      FROM mgmt_flat_target_memberships ftm, mgmt_current_severity cs
     WHERE ftm.composite_target_name = target_name_in
       AND ftm.composite_target_type = target_type_in
       AND ftm.member_target_guid = cs.target_guid;
     
    policy_vc_out := -1;
      
    SELECT COUNT(*)
      INTO num_targets_out
      FROM mgmt_flat_target_memberships 
     WHERE composite_target_name = target_name_in
       AND composite_target_type = target_type_in;
 
END get_summary_info;

PROCEDURE GET_GROUP_MEMBER_TARGET_TYPES(p_aggr_name IN VARCHAR2,
    p_aggr_type IN VARCHAR2 , member_types_out OUT cursorType) IS
l_target_guid RAW(16);
BEGIN
  l_target_guid := mgmt_target.get_target_guid(p_aggr_name, p_aggr_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_aggr_name || ' (' || p_aggr_type || ')');
  END IF;
  OPEN member_types_out FOR
      SELECT DISTINCT member_target_type
        FROM mgmt_flat_target_memberships
       WHERE composite_target_type = p_aggr_type
         AND composite_target_name = p_aggr_name;
END GET_GROUP_MEMBER_TARGET_TYPES;

-- 
-- Pass the removed targets and new member list, this procedure return member
--  target type that exists before removing but not in the new list
--
PROCEDURE get_removed_type
(
                p_targets_to_remove IN SMP_EMD_NVPAIR_ARRAY,
                member_targets_in IN  SMP_EMD_NVPAIR_ARRAY,
	        removed_types_out    OUT  cursorType
) 
IS 
l_targets_to_remove SMP_EMD_NVPAIR_ARRAY;
l_new_member_targets SMP_EMD_NVPAIR_ARRAY;
BEGIN
  l_targets_to_remove := expand_aggregate_targets(p_targets_to_remove);
  l_new_member_targets := expand_aggregate_targets(member_targets_in);

-- Generate the target type list that needs to be removed
-- A type should be removed if it does not exist in new target list
  OPEN removed_types_out FOR
  SELECT DISTINCT value as member_type
  FROM TABLE(CAST(l_targets_to_remove AS SMP_EMD_NVPAIR_ARRAY))
  WHERE value NOT IN
    ( SELECT DISTINCT value
        FROM TABLE(CAST(l_new_member_targets AS SMP_EMD_NVPAIR_ARRAY))
    );
END get_removed_type;

end em_groups_ui;
/

show errors;