Rem
Rem $Header: host_ui_pkgbody.sql 16-apr-2008.07:59:13 mnihalan Exp $
Rem
Rem host_ui_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      host_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    mnihalan    04/16/08 - Backport mnihalan_bug-6919845 from main
Rem    qsong       07/29/07 - XbranchMerge qsong_bug-6279839 from main
Rem    aptrived    07/23/07 - Bug#6251154, correcting timezone problem in
Rem                           uptime calculation
Rem    aptrived    03/07/06 - Bug#4742179, Fixing Boottime 
Rem    jpyang      07/15/05 - increase col size 
Rem    ajayshar    07/14/05 - Bug-4448722- CSA Collector status pending
Rem    jpyang      05/09/05 - add met.usage_type = 
Rem                           MGMT_GLOBAL.G_USAGE_VIEW_COLLECT 
Rem    pbantis     04/05/05 - Update getTypedGroupMembersInfo() for policies. 
Rem    groyal      02/16/05 - Remove reference to obsolete policy tables in getTypedGroupMembersInfo
Rem    niramach    02/04/05 - exempt->suppress conversions. 
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    mningomb    12/10/04 - Cutover Critical patch advisories policy to 10.2 
Rem                           policy framework 
Rem    jochen      12/10/04 - Fix diskgroup calculation for ASM 
Rem    mfidanbo    12/09/04 - 
Rem    jochen      11/08/04 - Check metric version for ASM query 
Rem    groyal      11/02/04 - Update getHostTargetListInfo 
Rem    asawant     09/13/04 - Adding more user prefered columns 
Rem    ramalhot    08/25/04 - cutover to new assoc tables 
Rem    rpatti      12/13/03 - fix timezone 
Rem    ancheng     11/10/03 - more fix to bug 3122422 
Rem    ancheng     10/31/03 - bug 3122422
Rem    groyal      10/30/03 - Move cpf policy to different category 
Rem    shuberma    09/26/03 - Use group_flat_membership 
Rem    jpyang      09/04/03 - add agent name to host targets list 
Rem    jpyang      08/27/03 - add target_guid to host target list 
Rem    xuliu       09/05/03 - fix after review 
Rem    xuliu       08/29/03 - cluster stuff 
Rem    kcheng      08/13/03 -  get os info for host pages
Rem    groyal      08/20/03 - Fix policy support in getTypedGroupMembersInfo 
Rem    groyal      08/07/03 - Add support for Critical Patch Advisories 
Rem    lhan        07/18/03 - handle host with multiple ASM instances
Rem    groyal      07/08/03 - Add policy violations support
Rem    lhan        06/18/03 - 
Rem    lhan        06/17/03 - Add osm diskgroups query
Rem    rpatti      04/11/03 - us target timezone region
Rem    jpyang      03/28/03 - nls support
Rem    ancheng     02/20/03 - targets and metrics version support
Rem    ancheng     02/13/03 - target version fix
Rem    ancheng     12/19/02 - target version support
Rem    kcheng      11/27/02 - merge raghu's exception fix in 4.0.0 to main
Rem    rpatti      01/09/03 - support def summary metrics
Rem    agor        11/15/02 - fix 2670996
Rem    jsoule      10/31/02 - add new alias variable for clarity
Rem    jsoule      10/31/02 - use alias when querying selected metrics
Rem    kcheng      10/22/02 - update getHostSiteMapInfo
Rem    njuillar    10/14/02 - Get rid of Host Group availability concept
Rem    kcheng      08/15/02 - add last_load_time to getHostTargetListInfo
Rem    skini       07/12/02 - Change in target_name column size
Rem    rpatti      06/26/02 - rpatti_support_netapp_plsql_pkg
Rem    rpatti      06/26/02 - Moved to a new file
Rem    rpatti      06/19/02 - fix os version
Rem    rpatti      06/13/02 - support new avail states
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Created
Rem    rpatti      04/10/02 - show detailed availability numbers
Rem    tjaiswal    04/01/02 - Misc group fixes
Rem    rpatti      02/25/02 - use function to get current user
Rem    rpatti      01/24/02 - fix build
Rem    kcheng      01/22/02 - split host page
Rem    rpatti      01/21/02 - more optimizations
Rem    rpatti      01/18/02 - more changes
Rem    rpatti      01/17/02 - config values moved to properties
Rem    rpatti      01/09/02 - make sure username is uppercase
Rem    rpatti      01/08/02 - suport customization of metrics
Rem    rpatti      01/07/02 - perf changes
Rem    rpatti      01/03/02 - separate out summary metric query
Rem    edemembe    01/08/02 - Metrics are now target independent
Rem    rpatti      12/26/01 - inventory pages for host group
Rem    ancheng     12/18/01 - change getHostSiteMapInfo
Rem    agor        12/17/01 - get target Oracle Home
Rem    ancheng     12/13/01 - modify getHostSiteMapInfo
Rem    rpatti      12/12/01 - Merged rpatti_host_groups_1
Rem    rpatti      12/11/01 - Created
Rem


CREATE OR REPLACE PACKAGE BODY emd_mntr_host AS

/*
 * set_default_metric_preferences - set the default metrics to show up in the
 *                                  summary for a given user
 *
 *   p_user_name     - the user being modified
 *   p_callback_type - type of modification; may be one of:
 *                       MGMT_USER.USER_CREATED_CALLBACK
 *                       MGMT_USER.USER_DROPPED_CALLBACK
 */
PROCEDURE set_default_subtab_preferences(p_user_name     IN VARCHAR2,
                                         p_callback_type IN NUMBER)
AS

  TYPE guid_list IS TABLE OF RAW(16);

  -- metrics for summary UI
  k_metrics CONSTANT guid_list := guid_list
    (HEXTORAW('0C71A1AFAC2D7199013837DA35522C08'),  -- Cpu Util %
     HEXTORAW('86821B5F0CE858D6E4A7F7390E88B73C'),  -- Memory Util %
     HEXTORAW('B2F6335B27BDD06BE55F69AC5A4FC2E7')); -- Total Disk IO

BEGIN
  IF p_callback_type = MGMT_USER.USER_CREATED_CALLBACK THEN
    FOR l_metric_guid_i IN 1..k_metrics.COUNT
    LOOP
      INSERT INTO mgmt_user_subtab_col_prefs
        (user_name, subtab_name, column_id_guid, column_type, display_order)
      VALUES
        (p_user_name, mgmt_global.g_host_target_type,
         k_metrics(l_metric_guid_i), 0, 4 + l_metric_guid_i);
    END LOOP;

    -- Hosts default generic columns
    INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, 
                column_id, column_type, display_order)
         VALUES (p_user_name,
                 mgmt_global.g_host_target_type,
                 emd_pref.mgmt_user_pref_avail_col,
                 emd_pref.mgmt_user_pref_generic_col_tp, 1);
    INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, 
                column_id, column_type, display_order)
         VALUES (p_user_name,
                 mgmt_global.g_host_target_type,
                 emd_pref.mgmt_user_pref_alerts_col,
                 emd_pref.mgmt_user_pref_generic_col_tp, 2);
    INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, 
                column_id, column_type, display_order)
         VALUES (p_user_name,
                 mgmt_global.g_host_target_type,
                 emd_pref.mgmt_user_pref_pol_viol_col,
                 emd_pref.mgmt_user_pref_generic_col_tp, 3);
    INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, 
                column_id, column_type, display_order)
         VALUES (p_user_name,
                 mgmt_global.g_host_target_type,
                 emd_pref.mgmt_user_pref_pol_comp_column,
                 emd_pref.mgmt_user_pref_generic_col_tp, 4);
  
    COMMIT;
  END IF;

END set_default_subtab_preferences;


PROCEDURE getHostSiteMapInfo(
                                target_name_in IN VARCHAR2,
                                target_type_in IN VARCHAR2,
                                type_display_name_out OUT VARCHAR2,
                                avail_status_out OUT INTEGER,
                                avail_pct_out OUT NUMBER,
                                time_zone_out OUT VARCHAR2,
                                os_info_out OUT VARCHAR2,
                                hw_info_out OUT VARCHAR2,
                                start_timestamp_out OUT Date,
                                config_values_out OUT cursorType,
                                osm_target_name_out OUT VARCHAR2,
                                ofs_total_out OUT NUMBER,
                                osm_diskgroups_total_out OUT NUMBER,
                                os_name_out OUT VARCHAR2,
                                cpu_count_out OUT NUMBER,
                                memory_size_out OUT NUMBER,
                                disk_space_out OUT NUMBER,
                                cluster_name_out OUT VARCHAR2,
                                cluster_display_type_out OUT VARCHAR2
  ) IS

    l_target_guid mgmt_targets.target_guid%TYPE;
    l_target_severities MGMT_SEVERITY_ARRAY := MGMT_SEVERITY_ARRAY();
    
    l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
    l_osm_target_guid mgmt_targets.target_guid%TYPE;
    l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
    l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
    l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
    l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
    l_category_prop_5 mgmt_targets.category_prop_5%TYPE;
    l_host_tz VARCHAR2(64); -- Host timezone
    l_host_time_tz timestamp with time zone; -- Host time with timezone
    l_repo_tz VARCHAR2(64); -- Repository timezone
    l_repo_time_tz timestamp with time zone; -- Repository sys time with timezone
    l_days NUMBER;
    l_hours NUMBER;
    l_boot_info VARCHAR2(64);
  BEGIN

    BEGIN
      SELECT target_guid, type_display_name, category_prop_1
        INTO l_target_guid, type_display_name_out, os_name_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;

    avail_status_out := mgmt_target.get_avail_current_status(l_target_guid);
    avail_pct_out := ROUND(mgmt_target.get_avail_pct_value(l_target_guid, 1), 2);
    start_timestamp_out := mgmt_target.get_current_status_timestamp(l_target_guid);

    BEGIN 
      SELECT NVL(
               to_char(
                 from_tz(CAST(last_load_time AS TIMESTAMP), 
                         timezone_region), 
                 'TZD'),
               'GMT ' || to_char(
                           from_tz(CAST(last_load_time AS TIMESTAMP),
                                        timezone_region), 
                           'TZR'))
         INTO time_zone_out
         FROM mgmt_targets
      WHERE target_name = target_name_in AND 
             target_type = target_type_in;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       time_zone_out := NULL;
   END;


  BEGIN
    SELECT os.name || ' '|| os.base_version 
         || decode(os.update_level, null,'',' '|| os.update_level) 
         || decode ( os.address_length_in_bits, null, '', ' 
            (' || + os.address_length_in_bits || ')')
       INTO os_info_out
    FROM mgmt_hc_os_summary os, mgmt_ecm_snapshot s 
    WHERE os.snapshot_guid = s.snapshot_guid 
         AND s.target_name=target_name_in
           AND s.target_type=target_type_in
           AND s.is_current = 'Y';
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       os_info_out := NULL;
   END;

   BEGIN
      SELECT hw.system_config, cpu_count, memory_size_in_mb, local_disk_space_in_gb
         INTO hw_info_out, cpu_count_out, memory_size_out, disk_space_out
      FROM mgmt_hc_hardware_master hw , mgmt_ecm_snapshot s 
      WHERE hw.snapshot_guid = s.snapshot_guid 
         AND s.target_name=target_name_in
         AND s.target_type=target_type_in
         AND s.is_current = 'Y';
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       hw_info_out := NULL;
   END;

   BEGIN
      SELECT target_name, target_guid, type_meta_ver, category_prop_1, 
             category_prop_2, category_prop_3, category_prop_4, category_prop_5
         INTO osm_target_name_out, l_osm_target_guid, l_type_meta_ver, 
              l_category_prop_1, l_category_prop_2, l_category_prop_3, 
              l_category_prop_4, l_category_prop_5
      FROM mgmt_targets 
      WHERE target_type = 'osm_instance' 
        AND host_name = target_name_in
	AND rownum=1;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       osm_target_name_out := null;
   END;

   -- OFS related space usage
   IF osm_target_name_out IS NULL OR lengthb(osm_target_name_out) = 0
   THEN
      ofs_total_out := -1;
   ELSE
      BEGIN
       SELECT sum(ofs_size.value)
	       INTO ofs_total_out
         FROM 
	   (SELECT d.key_value as key, d.value as value
              FROM mgmt$metric_current d
             WHERE d.metric_name = 'Single_Instance_OFS_Summary'
               AND d.target_type = 'osm_instance'
               AND d.metric_column = 'ofs_size_gb'
               AND d.target_guid = l_osm_target_guid) ofs_size,
           (SELECT d.key_value as key
             FROM mgmt$metric_current d
             WHERE d.metric_name = 'OFS_State'
               AND d.target_type = 'osm_instance'
               AND d.metric_column = 'ofs_mount_state'
               AND d.value = 'MOUNTED'
               AND d.target_guid = l_osm_target_guid) ofs_state
        WHERE ofs_size.key= ofs_state.key;

        IF ofs_total_out IS NULL THEN
          ofs_total_out := -1;
        END IF;
     END;
   END IF;


   IF osm_target_name_out IS NULL OR lengthb(osm_target_name_out) = 0
   THEN
      osm_diskgroups_total_out := -1;
   ELSE
      BEGIN
       SELECT sum(d.value/1024) 
	   INTO osm_diskgroups_total_out
        FROM mgmt_metrics m, mgmt_current_metrics d
        WHERE m.metric_name = 'DiskGroup_Usage' 
          AND m.metric_column = 'total_mb' 
          AND m.target_type = 'osm_instance'
          AND m.type_meta_ver = l_type_meta_ver
          AND (m.category_prop_1 = l_category_prop_1 OR m.category_prop_1 = ' ')
          AND (m.category_prop_2 = l_category_prop_2 OR m.category_prop_2 = ' ')
          AND (m.category_prop_3 = l_category_prop_3 OR m.category_prop_3 = ' ')
          AND (m.category_prop_4 = l_category_prop_4 OR m.category_prop_4 = ' ')
          AND (m.category_prop_5 = l_category_prop_5 OR m.category_prop_5 = ' ')
          AND m.metric_guid = d.metric_guid 
          AND d.target_guid = l_osm_target_guid;
          
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
         osm_diskgroups_total_out := -1;
     END;
   END IF;

  BEGIN
   BEGIN
    SELECT t.timezone_region INTO l_host_tz
      FROM mgmt_targets t
        WHERE t.target_guid = l_target_guid;

    SELECT TO_TIMESTAMP_TZ((SELECT prop.property_value
                              FROM mgmt_target_properties prop
                                WHERE prop.target_guid = l_target_guid
                                  AND prop.property_name = G_BOOTTIME)
                            || ' '
                            || TZ_OFFSET(l_host_tz),
                            G_DATE_TIME_TIMEZONE_FORMAT)
      INTO l_host_time_tz
        FROM dual;

    SELECT sessiontimezone INTO l_repo_tz FROM dual;

    SELECT TO_TIMESTAMP_TZ(TO_CHAR(sysdate, G_DATE_TIME_TIME_FORMAT)
                           || ' ' || TZ_OFFSET(l_repo_tz),
                           G_DATE_TIME_TIMEZONE_FORMAT)
      INTO l_repo_time_tz
        FROM dual;

    -- Find time difference from normalized time
    SELECT FLOOR(TO_NUMBER(CAST(SYS_EXTRACT_UTC(l_repo_time_tz) AS DATE)
                         - CAST(SYS_EXTRACT_UTC(l_host_time_tz) AS DATE))
                 * 24)
      INTO l_hours
        FROM dual;

    l_days := floor(l_hours / 24);
    l_hours := l_hours - (l_days * 24);
    l_boot_info := l_days || ' ' || l_hours || ' '
                   || TO_CHAR(l_host_time_tz,G_DATE_TIME_TIMEZONE_FORMAT);
    EXCEPTION
     WHEN OTHERS THEN
     BEGIN
      SELECT prop.property_value
       INTO l_boot_info
        FROM mgmt_target_properties prop
        WHERE prop.target_guid = l_target_guid
          AND prop.property_name = G_BOOTTIME;
     EXCEPTION
      WHEN NO_DATA_FOUND THEN
       l_boot_info := '';
     END;
    END;

    OPEN config_values_out FOR
      SELECT prop.property_name, prop.property_value
        FROM mgmt_target_properties prop
       WHERE prop.target_guid = l_target_guid
          AND prop.property_name <> G_BOOTTIME
     UNION
      SELECT G_BOOTTIME, l_boot_info
        FROM dual;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN
    OPEN config_values_out FOR
      SELECT prop.property_name, prop.property_value
        FROM mgmt_target_properties prop
       WHERE prop.target_guid = l_target_guid;
   WHEN OTHERS THEN
     RAISE;
  END;

    BEGIN 
      SELECT ct.target_name, ct.type_display_name 
        INTO cluster_name_out, cluster_display_type_out
        FROM mgmt_target_assocs mem, mgmt_targets ct, mgmt_targets mt
       WHERE mem.assoc_guid = MGMT_ASSOC.g_contains_guid
         AND mem.assoc_target_guid = l_target_guid
         AND ct.target_guid = mem.source_target_guid
         AND mt.target_guid = mem.assoc_target_guid
         AND mt.target_type = target_type_in 
         AND ct.target_type = 'cluster';
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       cluster_name_out := NULL;
       cluster_display_type_out := NULL;
   END;

END getHostSiteMapInfo;

--
-- FUNCTION: get_target_avail_status
--
-- PURPOSE:
--   This function returns the availability current status of the given target
--   guid. If the status is MGMT_GLOBAL.G_STATUS_UNKNOWN, it returns
--   100 if availability is not defined for the target. Given target_guid
--   should have threshold defined on {Response Metric/Status Column}
--   to have availability statistic.
--
--

FUNCTION get_target_avail_status
  (target_guid_in  IN  mgmt_targets.target_guid%TYPE)
  RETURN NUMBER
IS
  current_status_val NUMBER;
  availability_threshold_exists NUMBER := 0;

BEGIN

  current_status_val := mgmt_target.get_avail_current_status(target_guid_in);

  IF current_status_val != MGMT_GLOBAL.G_STATUS_UNKNOWN THEN
    RETURN current_status_val;
  END IF;

  SELECT COUNT(*) 
    INTO availability_threshold_exists
    FROM MGMT_METRIC_THRESHOLDS th, 
         MGMT_TARGETS t, 
         MGMT_METRICS m
  WHERE  th.target_guid = target_guid_in
  AND    th.target_guid = t.target_guid
  AND    th.metric_guid = m.metric_guid
  AND    t.target_type = m.target_type
  AND    m.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
  AND    m.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
  AND    t.type_meta_ver = m.type_meta_ver 
  AND    (m.category_prop_1 = ' ' or m.category_prop_1 = t.category_prop_1) 
  AND    (m.category_prop_2 = ' ' or m.category_prop_2 = t.category_prop_2) 
  AND    (m.category_prop_3  = ' ' or m.category_prop_3 = t.category_prop_3) 
  AND    (m.category_prop_4 = ' ' or m.category_prop_4 = t.category_prop_4) 
  AND    (m.category_prop_5=' ' or m.category_prop_5 = t.category_prop_5);

  IF availability_threshold_exists = 0 THEN
    RETURN 100;
  END IF;

  RETURN MGMT_GLOBAL.G_STATUS_UNKNOWN;

END get_target_avail_status;

PROCEDURE getHostTargetListInfo(
                                target_name_in IN VARCHAR2,
                                target_type_in IN VARCHAR2,
                                type_display_name_out OUT VARCHAR2,
                                target_summary_out OUT cursorType
  ) IS

    l_host_name MGMT_TARGETS.target_name%TYPE;
    l_target_guid mgmt_targets.target_guid%TYPE;

  BEGIN

    BEGIN
      SELECT host_name, type_display_name, target_guid
        INTO l_host_name, type_display_name_out, l_target_guid
       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;

      -- now for the given host name find all the targets related data
      OPEN target_summary_out FOR
        SELECT tgt.target_name,
               tgt.target_type,
               tgt.type_display_name,
               ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid, 1), 0) AS avail_pct,
               get_target_avail_status(tgt.target_guid) AS current_status,
               NVL(sev_detail.warning_count, 0) AS warning_count,
               tgt.last_load_time,
               NVL(sev_detail.critical_count, 0) AS critical_count,
               p.property_value AS target_oraclehome,
               NVL(policy.informational_count, 0) AS policy_informational_count,             
               NVL(policy.warning_count, 0) AS policy_warning_count,
               NVL(policy.critical_count, 0) AS policy_critical_count,
               NVL(policy.compliance_score, -1) as target_compliance_score,               
               DECODE(advisory.home_location, NULL, 0, 1) as has_advisory,
               tgt.target_guid,
               tgt.emd_url
          FROM mgmt_targets tgt, mgmt_target_properties p,
               (SELECT sev.target_guid,
                       SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count,
                       SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count
                  FROM mgmt_current_severity sev, mgmt_targets t
                 WHERE t.host_name = l_host_name
                   AND sev.target_guid = t.target_guid
                 GROUP BY sev.target_guid ) sev_detail,                 
              (SELECT e.target_guid,
                      SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,e.non_exempt_violations_logged,0)) as informational_count,
                      SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,e.non_exempt_violations_logged,0)) as warning_count,
                      SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,e.non_exempt_violations_logged,0)) as critical_count,
                      ROUND(AVG(e.compliance_score)) as compliance_score
                FROM mgmt_targets tgt, mgmt_policy_assoc_eval_summ e, mgmt_policies r
                WHERE tgt.host_name = l_host_name
                  AND e.target_guid = tgt.target_guid
                  AND r.policy_guid = e.policy_guid 
                  AND r.policy_type = MGMT_GLOBAL.G_TYPE_POLICY              
               GROUP BY e.target_guid ) policy,                       
               (SELECT unique hrv.column_str_value as home_location
                FROM
                    mgmt_policies p,
                    mgmt_current_violation v,
                    mgmt_violation_context hrv
                 WHERE  p.author = 'ORACLE'
                    AND p.target_type = 'host'
                    AND p.policy_name = 'Critical Patch Advisories for Oracle Homes'
                    AND p.policy_guid = v.policy_guid
                    AND v.target_guid = l_target_guid
                    AND v.policy_guid = hrv.policy_guid 
                    AND v.target_guid = hrv.target_guid 
                    AND v.key_value = hrv.key_value
                    AND hrv.column_name = 'HOME_LOCATION' ) advisory                 
         WHERE tgt.target_guid = sev_detail.target_guid (+)
           AND tgt.target_guid=policy.target_guid (+)         
           AND tgt.host_name=l_host_name
           AND tgt.target_guid!=l_target_guid
           AND tgt.target_guid=p.target_guid (+)
           AND p.property_name(+)='OracleHome'
           AND p.property_value = advisory.home_location(+)
         ORDER BY tgt.type_display_name, tgt.target_name;

END getHostTargetListInfo;

PROCEDURE getHostSummaryInfo(
                                target_name_in IN VARCHAR2,
                                target_type_in IN VARCHAR2,
                                type_display_name_out OUT VARCHAR2,
                                last_load_time_out OUT DATE,
                                emd_url_out OUT VARCHAR2,
                                thresholds_out OUT cursorType,
                                severities_out OUT cursorType,
                                os_name_out OUT VARCHAR2
  ) IS

    l_target_guid mgmt_targets.target_guid%TYPE;
    l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
    l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
    l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
    l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
    l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
    l_category_prop_5 mgmt_targets.category_prop_5%TYPE;

  BEGIN

    BEGIN
      SELECT target_guid, type_display_name,
             last_load_time, emd_url,
             type_meta_ver, category_prop_1, category_prop_1, category_prop_2, 
             category_prop_3, category_prop_4, category_prop_5
        INTO l_target_guid, type_display_name_out,
             last_load_time_out, emd_url_out,
             l_type_meta_ver, l_category_prop_1, os_name_out, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5
       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;

    OPEN severities_out FOR
      SELECT m.metric_name, m.metric_column, s.severity_code
        FROM mgmt_current_severity s, mgmt_metrics m
       WHERE s.target_guid = l_target_guid
         AND m.target_type = 'host'
         AND m.metric_name = 'Load'
         AND m.metric_guid = s.metric_guid
         AND m.type_meta_ver = l_type_meta_ver
         AND (m.category_prop_1 = l_category_prop_1 OR m.category_prop_1 = ' ')
         AND (m.category_prop_2 = l_category_prop_2 OR m.category_prop_2 = ' ')
         AND (m.category_prop_3 = l_category_prop_3 OR m.category_prop_3 = ' ')
         AND (m.category_prop_4 = l_category_prop_4 OR m.category_prop_4 = ' ')
         AND (m.category_prop_5 = l_category_prop_5 OR m.category_prop_5 = ' ');

    OPEN thresholds_out FOR
      SELECT m.metric_name, m.metric_column,
             t.warning_threshold, t.critical_threshold
        FROM mgmt_metric_thresholds t,  mgmt_metrics m
       WHERE t.target_guid = l_target_guid
         AND m.target_type = 'host'
         AND m.metric_name = 'Load'
         AND t.metric_guid = m.metric_guid
         AND m.type_meta_ver = l_type_meta_ver
         AND (m.category_prop_1 = l_category_prop_1 OR m.category_prop_1 = ' ')
         AND (m.category_prop_2 = l_category_prop_2 OR m.category_prop_2 = ' ')
         AND (m.category_prop_3 = l_category_prop_3 OR m.category_prop_3 = ' ')
         AND (m.category_prop_4 = l_category_prop_4 OR m.category_prop_4 = ' ')
         AND (m.category_prop_5 = l_category_prop_5 OR m.category_prop_5 = ' ');

END getHostSummaryInfo;

PROCEDURE getTypedGroupMembersInfo(
                                target_name_in IN VARCHAR2,
                                target_type_in IN VARCHAR2,
                                member_type_in IN VARCHAR2,
                                type_display_name_out OUT VARCHAR2,
                                summary_cols_out OUT cursorType,
                                summary_vals_out OUT cursorType,
                                summary_sevs_out OUT cursorType,
                                target_summary_out OUT cursorType
) IS

    l_target_guid mgmt_targets.target_guid%TYPE;
    l_user_name VARCHAR2(256);
    l_user_alias VARCHAR2(256);
    l_summary_col_cnt NUMBER(3) := 0;

   BEGIN

    -- get the current user name from sys context
    l_user_name := UPPER(mgmt_user.get_current_em_user());

    -- find the alias for the current user
    l_user_alias := mgmt_user.get_subtab_prefs_alias(l_user_name, member_type_in);

    BEGIN
      SELECT target_guid, type_display_name
        INTO l_target_guid, type_display_name_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;

    SELECT COUNT(*)
      INTO l_summary_col_cnt
      FROM mgmt_user_subtab_col_prefs 
      WHERE user_name = l_user_alias AND
            column_type = 0 AND
            subtab_name = member_type_in;

    -- if the user has no preferences - show all
    IF l_summary_col_cnt = 0
    THEN
      OPEN summary_cols_out FOR
        SELECT DISTINCT met.metric_name, met.metric_column, 
               met.short_name, met.short_name_nlsid
          FROM mgmt_metrics met
         WHERE met.target_type = member_type_in
           AND met.metric_type = 0
           AND met.is_for_summary = 1
           AND met.num_keys = 0
           AND met.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
           AND NOT (met.metric_name = 'Response' and met.metric_column = 'Status')
           AND met.metric_name IN
           ( SELECT metric_name
               FROM mgmt_metrics
               WHERE target_type = member_type_in 
                   AND metric_type = 2
                   AND metric_column = ' '
           )
         ORDER BY met.short_name;

      -- of all the summary metrics - return severity (including clear)
      -- for any that actually have thresholds 
      OPEN summary_sevs_out FOR
        SELECT thr.target_guid, met.short_name, met.short_name_nlsid,
               NVL(sev.severity_code, MGMT_GLOBAL.G_SEVERITY_CLEAR) AS severity_code
          FROM mgmt_target_assocs mem,
               (SELECT DISTINCT metric_guid, short_name, short_name_nlsid
                  FROM mgmt_metrics
                 WHERE target_type = member_type_in
                   AND metric_type = 0
                   AND num_keys = 0
                   AND is_for_summary = 1
                   AND usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
                   AND NOT (metric_name = 'Response' and metric_column = 'Status')
                   AND metric_name IN
                   ( SELECT metric_name
                       FROM mgmt_metrics
                       WHERE target_type = member_type_in
                           AND metric_type = 2
                           AND metric_column = ' '
                   )
               ) met,
               mgmt_metric_thresholds thr, mgmt_current_severity sev
         WHERE mem.source_target_guid = l_target_guid
           AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
           AND thr.target_guid = mem.assoc_target_guid
           AND thr.metric_guid = met.metric_guid
           AND thr.target_guid = sev.target_guid (+)
           AND thr.metric_guid = sev.metric_guid (+);

      OPEN summary_vals_out FOR
        SELECT curmet.target_guid, met.short_name, met.short_name_nlsid,
               ROUND(curmet.value, 2)
          FROM mgmt_target_assocs mem,
               (SELECT DISTINCT metric_guid, short_name, short_name_nlsid
                  FROM mgmt_metrics
                 WHERE target_type = member_type_in
                   AND metric_type = 0
                   AND num_keys = 0
                   AND is_for_summary = 1
                   AND usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
                   AND NOT (metric_name = 'Response' and metric_column = 'Status')
                   AND metric_name IN
                   ( SELECT metric_name
                       FROM mgmt_metrics
                       WHERE target_type = member_type_in
                           AND metric_type = 2
                           AND metric_column = ' '
                   )
               ) met,
               mgmt_current_metrics curmet
         WHERE mem.source_target_guid = l_target_guid
           AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
           AND curmet.target_guid = mem.assoc_target_guid
           AND curmet.metric_guid = met.metric_guid;
    ELSE -- the user has some preferences
      OPEN summary_cols_out FOR
        SELECT DISTINCT met.metric_name, met.metric_column, met.short_name, 
               met.short_name_nlsid, prefs.display_order
          FROM mgmt_user_subtab_col_prefs prefs, mgmt_metrics met
         WHERE prefs.user_name = l_user_alias
           AND prefs.subtab_name = member_type_in
           AND prefs.column_id_guid = met.metric_guid
           AND prefs.column_type = 0
           AND met.metric_type = 0
           AND met.num_keys = 0
           AND met.is_for_summary = 1
           AND met.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
           AND NOT (met.metric_name = 'Response' and met.metric_column = 'Status')
           AND met.metric_name IN
           ( SELECT metric_name
               FROM mgmt_metrics
               WHERE target_type = member_type_in
                   AND metric_type = 2
                   AND metric_column = ' '
           )

         ORDER BY prefs.display_order;

      OPEN summary_sevs_out FOR
        SELECT thr.target_guid, met.short_name, met.short_name_nlsid,
               NVL(sev.severity_code, MGMT_GLOBAL.G_SEVERITY_CLEAR) AS severity_code
          FROM mgmt_target_assocs mem,
               (SELECT DISTINCT m.metric_guid, m.short_name, m.short_name_nlsid
                  FROM mgmt_user_subtab_col_prefs p, mgmt_metrics m
                 WHERE p.user_name = l_user_alias
                   AND p.subtab_name = member_type_in
                   AND p.column_id_guid = m.metric_guid
                   AND p.column_type = 0
                   AND m.metric_type = 0
                   AND m.num_keys = 0
                   AND m.is_for_summary = 1
                   AND m.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
                   AND NOT (m.metric_name = 'Response' and m.metric_column = 'Status')
                   AND m.metric_name IN
                   ( SELECT metric_name
                       FROM mgmt_metrics
                       WHERE target_type = member_type_in
                         AND metric_type = 2
                         AND metric_column = ' '
                   )
               ) met,
               mgmt_metric_thresholds thr, mgmt_current_severity sev
         WHERE mem.source_target_guid = l_target_guid
           AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
           AND thr.target_guid = mem.assoc_target_guid
           AND thr.metric_guid = met.metric_guid
           AND thr.target_guid = sev.target_guid (+)
           AND thr.metric_guid = sev.metric_guid (+);
  
      OPEN summary_vals_out FOR
        SELECT curmet.target_guid, met.short_name, met.short_name_nlsid, ROUND(curmet.value, 2)
          FROM mgmt_target_assocs mem,
               (SELECT DISTINCT m.metric_guid, m.short_name, m.short_name_nlsid
                  FROM mgmt_user_subtab_col_prefs p, mgmt_metrics m
                 WHERE p.user_name = l_user_alias
                   AND p.subtab_name = member_type_in
                   AND p.column_id_guid = m.metric_guid
                   AND p.column_type = 0
                   AND m.metric_type = 0
                   ANd m.num_keys = 0
                   AND m.is_for_summary = 1
                   AND m.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
                   AND NOT (m.metric_name = 'Response' and m.metric_column = 'Status')
                   AND m.metric_name IN
                   ( SELECT metric_name
                       FROM mgmt_metrics
                      WHERE target_type = member_type_in
                        AND metric_type = 2
                        AND metric_column = ' '
                   )
               ) met,
               mgmt_current_metrics curmet
         WHERE mem.source_target_guid = l_target_guid
           AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
           AND curmet.target_guid = mem.assoc_target_guid
           AND curmet.metric_guid = met.metric_guid;
    END IF;
  
      OPEN target_summary_out FOR
        SELECT tgt.target_name,
               tgt.target_type,
               tgt.target_guid,
               tgt.type_display_name,
               mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status,
               NVL(sev_detail.warning_count, 0) AS warning_count,
               NVL(sev_detail.critical_count, 0) AS critical_count,
               tgt.host_name,
               NVL(policy_detail.information_count, 0) AS policy_information_count,
               NVL(policy_detail.warning_count, 0) AS policy_warning_count,
               NVL(policy_detail.critical_count, 0) AS policy_critical_count,
               NVL(policy_detail.compliance_score, -1) AS target_compliance_score 
          FROM mgmt_target_assocs mem, mgmt_targets tgt,
               (SELECT sev.target_guid,
                       SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count,
                       SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count
                  FROM mgmt_current_severity sev, 
                       mgmt_target_assocs mem
                 WHERE mem.source_target_guid = l_target_guid
                   AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
                   AND sev.target_guid = mem.assoc_target_guid
                 GROUP BY sev.target_guid ) sev_detail,
               (SELECT
                  e.target_guid as tguid,
                  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
                  mgmt_flat_target_assoc mem,
                  mgmt_policy_assoc_eval_summ e,
                  mgmt_policies r
                WHERE mem.source_target_guid = l_target_guid
                    AND mem.is_membership=1
                    AND e.target_guid=mem.assoc_target_guid
                    AND r.policy_guid = e.policy_guid
                    AND r.policy_type = MGMT_GLOBAL.G_TYPE_POLICY
                 GROUP BY e.target_guid) policy_detail
         WHERE mem.source_target_guid = l_target_guid
           AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
           AND tgt.target_guid = mem.assoc_target_guid
           AND tgt.target_guid = sev_detail.target_guid (+)           
           AND tgt.target_guid = policy_detail.tguid(+)
         ORDER BY tgt.type_display_name, tgt.target_name;
END getTypedGroupMembersInfo;

/**
 This procedure takes as input a target type and returns a list of summary 
 metrics, non-hidden properties, and generic columns that a user may display
 in the sub-tab for the target type in question. The current choice setting
 is also returned.
 */
PROCEDURE getSubTabPreferrences (
              p_target_type_in     IN  VARCHAR2,
              p_user_prefs_cur_out OUT cursorType,
              p_def_prefs_cur_out  OUT cursorType)
IS
l_user_alias VARCHAR2(256);
l_pref_count NUMBER(3);
BEGIN

  -- find the alias for the current user name (from sys context)
  l_user_alias :=
    mgmt_user.get_subtab_prefs_alias(mgmt_user.get_current_em_user(),
                                     p_target_type_in);

  -- Is this a composite subtab or a simple target subtab?
  IF (p_target_type_in != emd_pref.mgmt_user_pref_all_targets) AND
     (p_target_type_in != emd_pref.mgmt_user_pref_groups) AND
     (p_target_type_in != emd_pref.mgmt_user_pref_systems) AND
     (p_target_type_in != emd_pref.mgmt_user_pref_services) AND
     (p_target_type_in != emd_pref.mgmt_user_pref_webapps) THEN
    -- Simple Target subtab

    SELECT COUNT(*) INTO l_pref_count
      FROM mgmt_user_subtab_col_prefs
     WHERE user_name = l_user_alias
       AND subtab_name = p_target_type_in;

    -- Does this target_type have preferences
    IF (l_pref_count > 0) THEN
      -- Returns all the preferred columns for a given alias and tgt_type 
      OPEN p_user_prefs_cur_out FOR
        WITH prefs AS
          (
            SELECT column_type, display_order, column_id, column_id_guid
              FROM mgmt_user_subtab_col_prefs
             WHERE user_name = l_user_alias
               AND subtab_name = p_target_type_in
          )
          SELECT DISTINCT column_id_guid, 
                 NULL AS "COLUMN_ID",
                 short_name AS "COLUMN_ID_DISP",
                 short_name_nlsid AS "COLUMN_ID_NLSID",
                 column_type, display_order
            FROM mgmt_metrics met, prefs
           WHERE met.target_type = p_target_type_in
             AND met.metric_type = 0
             AND met.num_keys = 0
             AND met.is_for_summary = 1
             AND met.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
             AND NOT (met.metric_name = 'Response' and met.metric_column = 'Status')
             AND met.metric_name IN
             ( SELECT metric_name
                 FROM mgmt_metrics
                 WHERE target_type = p_target_type_in 
                     AND metric_type = 2
                     AND metric_column = ' '
             )
             AND prefs.column_type = 0
             AND met.metric_guid = prefs.column_id_guid
           UNION
          SELECT DISTINCT NULL AS "COLUMN_ID_GUID",
                 column_id,
                 property_display_name AS "COLUMN_ID_DISP",
                 property_display_nlsid AS "COLUMN_ID_NLSID",
                 column_type, display_order
            FROM mgmt_target_prop_defs props, prefs
           WHERE props.target_type = p_target_type_in
             AND props.hidden_flag = 0
             AND props.credential_flag = 0
             AND prefs.column_type = 1
             AND props.property_name = prefs.column_id
           UNION
          SELECT NULL AS "COLUMN_ID_GUID",
                 column_id,
                 column_id AS "COLUMN_ID_DISP",
                 NULL AS "COLUMN_ID_NLSID",
                 column_type, display_order
            FROM prefs
           WHERE prefs.column_type = 2
           ORDER BY DISPLAY_ORDER;
    
      -- Returns all the unselected columns for a given alias and target
      OPEN p_def_prefs_cur_out FOR
        WITH props AS
          (
            SELECT *
              FROM mgmt_target_prop_defs
             WHERE target_type = p_target_type_in
               AND hidden_flag = 0
               AND credential_flag = 0
          ),
        metrics AS
          (
            SELECT *
              FROM mgmt_metrics
             WHERE metric_type = 0
               AND target_type = p_target_type_in
               AND usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
               AND num_keys = 0 
               AND is_for_summary = 1
               AND NOT (metric_name = 'Response' and metric_column = 'Status')
               AND metric_name IN
               ( SELECT metric_name
                   FROM mgmt_metrics
                   WHERE target_type = p_target_type_in 
                       AND metric_type = 2
                       AND metric_column = ' '
               )
          ),
        user_prefs AS
          (
            SELECT column_type, column_id, column_id_guid
              FROM mgmt_user_subtab_col_prefs
             WHERE user_name = l_user_alias
               AND subtab_name = p_target_type_in
          ),
        all_prefs AS
          (
            SELECT 2 AS "COLUMN_TYPE", column_id, NULL AS "COLUMN_ID_GUID"
              FROM mgmt_user_subtab_col_prefs
             WHERE user_name = mgmt_user.wildcard_user_name_alias
               AND subtab_name = p_target_type_in
               AND column_type = 2
             UNION
            SELECT DISTINCT 1 AS "COLUMN_TYPE", property_name AS "COLUMN_ID",
                   NULL AS "COLUMN_ID_GUID"
              FROM props
             UNION
            SELECT DISTINCT 0 AS "COLUMN_TYPE", NULL AS "COLUMN_ID",
                   metric_guid AS "COLUMN_ID_GUID"
              FROM metrics
          ),
        result_prefs AS
          (
            SELECT * FROM all_prefs
             MINUS
            SELECT * FROM user_prefs
          )
        SELECT column_id_guid, column_id, column_id AS "COLUMN_ID_DISP",
               NULL AS "COLUMN_ID_NLSID", column_type
          FROM result_prefs
         WHERE column_type = 2
         UNION
        SELECT column_id_guid, column_id,
               property_display_name AS "COLUMN_ID_DISP",
               property_display_nlsid AS "COLUMN_ID_NLSID",
               column_type
          FROM props, result_prefs
         WHERE props.property_name = column_id
         UNION
        SELECT column_id_guid, column_id, short_name AS "COLUMN_ID_DISP",
               short_name_nlsid AS "COLUMN_ID_NLSID", column_type
          FROM metrics, result_prefs
         WHERE metric_guid = column_id_guid;

    ELSE -- Target type does not have def prefs (support older integ. folders)

      OPEN p_user_prefs_cur_out FOR
        SELECT column_id_guid, column_id, column_id_disp,
               column_id_nlsid, column_type, ROWNUM AS "DISPLAY_ORDER"
      FROM
      (
        SELECT NULL AS "COLUMN_ID_GUID",
               emd_pref.MGMT_USER_PREF_AVAIL_COL AS "COLUMN_ID",
               emd_pref.MGMT_USER_PREF_AVAIL_COL AS "COLUMN_ID_DISP",
               NULL AS "COLUMN_ID_NLSID",
               2 AS "COLUMN_TYPE", 0 AS "COL_ORDER"
          FROM dual
         UNION ALL
        SELECT NULL AS "COLUMN_ID_GUID",
               emd_pref.MGMT_USER_PREF_ALERTS_COL AS "COLUMN_ID",
               emd_pref.MGMT_USER_PREF_ALERTS_COL AS "COLUMN_ID_DISP",
               NULL AS "COLUMN_ID_NLSID",
               2 AS "COLUMN_TYPE", 0 AS "COL_ORDER"
          FROM dual
         UNION ALL
        SELECT DISTINCT metric_guid AS "COLUMN_ID_GUID",
               NULL AS "COLUMN_ID",
               short_name AS "COLUMN_ID_DISP",
               short_name_nlsid AS "COLUMN_ID_NLSID",
               0 AS "COLUMN_TYPE", 2 AS "COL_ORDER"
          FROM mgmt_metrics met 
         WHERE met.metric_type = 0
           ANd met.num_keys = 0
           AND met.is_for_summary = 1
           AND met.target_type = p_target_type_in
           AND met.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
           AND NOT (met.metric_name = 'Response' and met.metric_column = 'Status')
           AND met.metric_name IN
           ( SELECT metric_name
               FROM mgmt_metrics
               WHERE target_type = p_target_type_in 
                   AND metric_type = 2
                   AND metric_column = ' '
           )
         ORDER BY "COL_ORDER", "COLUMN_ID_DISP"
       );

      -- Open a cursor with no data...
      OPEN p_def_prefs_cur_out FOR
        SELECT NULL AS "COLUMN_ID_GUID", NULL AS "COLUMN_ID", 
               NULL AS "COLUMN_ID_DISP", NULL AS "COLUMN_ID_NLSID", 
               NULL AS "COLUMN_TYPE", NULL AS "DISPLAY_ORDER"
          FROM dual
         WHERE ROWNUM < 1;

    END IF;
    
  ELSE -- Composite Target (i.e. All Targets, Groups)
    -- This cursor returns all the preferred columns for a given alias
    -- NOTE: Composite folders only have global props and generic columns (i.e.
    -- availability, alerts, etc.)
    OPEN p_user_prefs_cur_out FOR
      WITH prefs AS
        (
          SELECT column_type, column_id, display_order
            FROM mgmt_user_subtab_col_prefs
           WHERE user_name = l_user_alias
             AND subtab_name = p_target_type_in
        )
        SELECT DISTINCT NULL AS "COLUMN_ID_GUID",
               column_id,
               property_display_name AS "COLUMN_ID_DISP",
               property_display_nlsid AS "COLUMN_ID_NLSID",
               column_type, display_order
          FROM mgmt_all_target_props props, prefs
         WHERE props.hidden_flag = 0
           AND props.credential_flag = 0
           AND props.property_name = prefs.column_id
           AND prefs.column_type = 1
           AND prefs.column_id = props.property_name
         UNION
        SELECT NULL AS "COLUMN_ID_GUID",
               column_id,
               NULL AS "COLUMN_ID_DISP",
               NULL AS "COLUMN_ID_NLSID",
               column_type, display_order
          FROM prefs
         WHERE column_type = 2
         ORDER BY display_order;

    -- Returns all the unselected columns for a given alias and composite folder
    OPEN p_def_prefs_cur_out FOR
      WITH props AS
        (
          SELECT *
            FROM mgmt_all_target_props
           WHERE hidden_flag = 0
             AND credential_flag = 0
        ),
      user_prefs AS
        (
          SELECT column_id, column_type
            FROM mgmt_user_subtab_col_prefs
           WHERE user_name = l_user_alias
             AND subtab_name = p_target_type_in
        ),
      def_prefs AS
        (
          SELECT property_name AS "COLUMN_ID", 1 AS "COLUMN_TYPE"
            FROM props
           UNION
          SELECT column_id, column_type
            FROM mgmt_user_subtab_col_prefs
           WHERE user_name = mgmt_user.wildcard_user_name_alias
             AND subtab_name = p_target_type_in
        ),
      remain_prefs AS
        (
           SELECT * FROM def_prefs
            MINUS
           SELECT * FROM user_prefs
        )
      SELECT DISTINCT NULL AS "COLUMN_ID_GUID",
             column_id,
             property_display_name AS "COLUMN_ID_DISP",
             property_display_nlsid AS "COLUMN_ID_NLSID",
             column_type
        FROM props, remain_prefs prefs
       WHERE property_name = column_id
         AND column_type = 1
       UNION
      SELECT NULL AS "COLUMN_ID_GUID",
             column_id,
             NULL AS "COLUMN_ID_DISP",
             NULL AS "COLUMN_ID_NLSID",
             column_type
        FROM remain_prefs
       WHERE column_type = 2;
  END IF;

END getSubTabPreferrences;

/**
  Insert the preferences of the user - for metrics to be shown 
  in the dense UI for a given type
 */
PROCEDURE saveSummaryMetricsInfo (
              p_target_type_in     IN  VARCHAR2,
              p_sel_metrics_in     IN  MGMT_SUBTAB_PREF_ARRAY)
IS
  l_user_name VARCHAR2(256);

BEGIN

  -- get the current user name from sys context
  l_user_name := mgmt_user.get_current_em_user();

  -- delete any old preferences for the user
  DELETE FROM mgmt_user_subtab_col_prefs
    WHERE user_name = l_user_name AND
          subtab_name = p_target_type_in;

  -- insert the new preferences
  INSERT INTO mgmt_user_subtab_col_prefs
    (user_name, subtab_name, column_id_guid, column_id, column_type, 
     display_order)
    SELECT l_user_name, p_target_type_in, col.column_id_guid, col.column_id,
           col.column_type, col.display_order
      FROM TABLE(CAST(p_sel_metrics_in AS MGMT_SUBTAB_PREF_ARRAY)) col;

END saveSummaryMetricsInfo;

                        

PROCEDURE getHostPerfInfo(
              target_name_in     IN  VARCHAR2,
              target_type_in     IN  VARCHAR2,
              metric_name_in     IN  VARCHAR2,
              metric_column_in   IN  VARCHAR2,
              target_names_out   OUT cursorType
) IS

  l_target_guid        mgmt_targets.target_guid%TYPE;
  l_metric_guid        mgmt_metrics.metric_guid%TYPE;
  l_systimestamp       TIMESTAMP WITH TIME ZONE;

BEGIN

  dbms_output.put_line( 'getHostPerfInfo():IN');

  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);
  l_systimestamp := SYSTIMESTAMP;

  SELECT DISTINCT metric_guid
    INTO l_metric_guid
    FROM mgmt_metrics 
   WHERE target_type='host'
     AND metric_name=metric_name_in
     AND metric_column=metric_column_in;

  OPEN target_names_out FOR
    SELECT target_name
    FROM   (SELECT /*+ ORDERED INDEX(tgt) INDEX(data) USE_NL(data) */
                   MIN(tgt.target_name) target_name
            FROM   mgmt_target_assocs mem, mgmt_targets tgt,
                   mgmt_metrics_1hour data
            WHERE  mem.source_target_guid = l_target_guid
              AND  mem.assoc_guid = MGMT_ASSOC.g_contains_guid
              AND  tgt.target_guid   = mem.assoc_target_guid
              AND  data.target_guid = tgt.target_guid
              AND  data.metric_guid  = l_metric_guid
              AND  data.key_value = ' '
              AND  data.rollup_timestamp >= 
                     CAST(l_systimestamp at time zone tgt.timezone_region as DATE) -7
            GROUP BY data.target_guid
            ORDER BY AVG(data.value_average) DESC
           )
    WHERE  rownum < 4;

  dbms_output.put_line( 'getHostPerfInfo():OUT');

END getHostPerfInfo;

END emd_mntr_host;
/

show errors;