Rem
Rem $Header: group_comp_mntr_pkgbody.sql 27-jun-2005.01:43:34 pratagar Exp $
Rem
Rem group_comp_mntr_pkgbody.sql
Rem
Rem Copyright (c) 2003, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      group_comp_mntr_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    pratagar    06/27/05 - Type Display Name 
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    ramalhot    08/30/04 - cutover to new assoc tables 
Rem    tjaiswal    10/16/03 - tjaiswal_ocs_comp_bugs 
Rem    tjaiswal    10/06/03 - Add support for system ui across components 
Rem    tjaiswal    09/16/03 - Support for webapp metrics 
Rem    tjaiswal    05/05/03 - Created
Rem

CREATE OR REPLACE PACKAGE BODY group_comp_mntr AS

  COMPOSITE_TYPE CONSTANT VARCHAR(10) := 'composite';

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

  METRIC_TYPE_NUMBER CONSTANT INTEGER := 0;
  METRIC_TYPE_STRING CONSTANT INTEGER := 1;
  METRIC_TYPE_TABLE CONSTANT INTEGER := 2;
  METRIC_TYPE_EVAL CONSTANT INTEGER := 5;

  STATS_RELATED CONSTANT NUMBER(1) := 0;

  G_SEVERITY_CLEAR CONSTANT INTEGER := 15;


-- returns 1 for should rollup, 0 for should not rollup
FUNCTION should_rollup_sev
  (old_sev_in IN NUMBER,
   new_sev_in IN NUMBER )
RETURN NUMBER IS

BEGIN
  
  IF( old_sev_in < 0 OR new_sev_in = 0 )
  THEN
    RETURN 0;
  END IF;
  
  -- clear < warning < critical
  
  -- if the old severity value is critical, return false ...
  IF( old_sev_in = 25 )
  THEN
    RETURN 0;
  END IF;

  IF( old_sev_in = 15 )
  THEN
      -- if the new severity is the same as old, return false
      IF( new_sev_in = 15 )
      THEN
          RETURN 0;
      ELSE          
        -- if the new severity is any other value, return true
        RETURN 1;                                        
      END IF;                      
  END IF;

  IF( old_sev_in = 20 )
  THEN
      -- if the new severity is the same as old or clear, return false
      IF( new_sev_in = 15 OR
          new_sev_in = 20 )
      THEN          
          RETURN 0;
      ELSE
        -- if the new severity is any other value, return true
        RETURN 1;                                        
      END IF;            
  END IF;

  RETURN 0;
      
END should_rollup_sev;

-- returns 1 for should rollup, 0 for should not rollup
FUNCTION should_rollup_status
  (old_status_in IN NUMBER,
   new_status_in IN NUMBER )
RETURN NUMBER IS

BEGIN
  
  -- if an incorrect value is passed in, return false 
  IF( old_status_in < 0 OR new_status_in < 0 )
  THEN
      RETURN 0;
  END IF;      
      
  -- order of severity of target availability states: least to worst
  -- up < unknown < unreachable < blackout < error < agent down < down
  
  -- if the old status value is down, return false ...
  IF( old_status_in = MGMT_GLOBAL.G_STATUS_DOWN )
  THEN
      RETURN 0;
  END IF;      
      
  IF( old_status_in = MGMT_GLOBAL.G_STATUS_UP )
  THEN
      -- if the new status is the same as old, return false
      IF( new_status_in = MGMT_GLOBAL.G_STATUS_UP )
      THEN
          RETURN 0;
      ELSE            
        -- if the new status is any other value, return true
        RETURN 1;          
      END IF;      
  END IF;      

  IF( old_status_in = MGMT_GLOBAL.G_STATUS_UNKNOWN )
  THEN
      -- if the new status is the same as old or up, return false
      IF( new_status_in = MGMT_GLOBAL.G_STATUS_UP OR
          new_status_in = MGMT_GLOBAL.G_STATUS_UNKNOWN )
      THEN
          RETURN 0;
      ELSE            
        -- if the new status is any other value, return true
        RETURN 1;          
      END IF;      
  END IF;      

  IF( old_status_in = MGMT_GLOBAL.G_STATUS_UNREACHABLE )
  THEN
      -- if the new status is the same as old or up or unreachable, return false
      IF( new_status_in = MGMT_GLOBAL.G_STATUS_UP OR
          new_status_in = MGMT_GLOBAL.G_STATUS_UNKNOWN OR
          new_status_in = MGMT_GLOBAL.G_STATUS_UNREACHABLE )
      THEN
          RETURN 0;
      ELSE            
        -- if the new status is any other value, return true
        RETURN 1;          
      END IF;      
      
  END IF;      

  IF( old_status_in = MGMT_GLOBAL.G_STATUS_BLACKOUT )
  THEN
      -- if the new status is the same as old or up or unreachable or blackout, return false
      IF( new_status_in = MGMT_GLOBAL.G_STATUS_UP OR
          new_status_in = MGMT_GLOBAL.G_STATUS_UNKNOWN OR
          new_status_in = MGMT_GLOBAL.G_STATUS_UNREACHABLE OR
          new_status_in = MGMT_GLOBAL.G_STATUS_BLACKOUT )
      THEN
          RETURN 0;
      ELSE            
        -- if the new status is any other value, return true
        RETURN 1;          
      END IF;      
      
  END IF;      

  IF( old_status_in = MGMT_GLOBAL.G_STATUS_ERROR )
  THEN
      -- if the new status is the same as old or up or unreachable or blackout or error, return false
      IF( new_status_in = MGMT_GLOBAL.G_STATUS_UP OR
          new_status_in = MGMT_GLOBAL.G_STATUS_UNKNOWN OR
          new_status_in = MGMT_GLOBAL.G_STATUS_UNREACHABLE OR
          new_status_in = MGMT_GLOBAL.G_STATUS_BLACKOUT OR
          new_status_in = MGMT_GLOBAL.G_STATUS_ERROR )
      THEN
          RETURN 0;
      ELSE            
        -- if the new status is any other value, return true
        RETURN 1;          
      END IF;      
      
  END IF;      

  IF( old_status_in = MGMT_GLOBAL.G_STATUS_AGENT_DOWN )
  THEN
      -- if the new status is the same as old or up or unreachable or blackout or error or agent down, return false
      IF( new_status_in = MGMT_GLOBAL.G_STATUS_UP OR
          new_status_in = MGMT_GLOBAL.G_STATUS_UNKNOWN OR
          new_status_in = MGMT_GLOBAL.G_STATUS_UNREACHABLE OR
          new_status_in = MGMT_GLOBAL.G_STATUS_BLACKOUT OR
          new_status_in = MGMT_GLOBAL.G_STATUS_ERROR OR
          new_status_in = MGMT_GLOBAL.G_STATUS_AGENT_DOWN )
      THEN
          RETURN 0;
      ELSE            
        -- if the new status is any other value, return true
        RETURN 1;          
      END IF;      
      
  END IF;      

  RETURN 0;
      
END should_rollup_status;

-- returns MGMT_GLOBAL.G_TRUE if atleast 1 website metric exists, 
-- MGMT_GLOBAL.G_FALSE otherwise
FUNCTION website_met_exists
  (met_obj_in IN METRICS_OBJ_ARRAY)
RETURN NUMBER IS

BEGIN
  
  -- if the array is empty, there are no website metrics, so return false
  IF( met_obj_in IS NULL OR met_obj_in.count = 0 )
  THEN
    RETURN MGMT_GLOBAL.G_FALSE;
  END IF;
  
  FOR i in 1..met_obj_in.count    
  LOOP
    IF( met_obj_in(i).target_type = 'website' )
    THEN
      -- a website metric found, so return true
      RETURN MGMT_GLOBAL.G_TRUE;
    END IF;
  END LOOP;

  -- no website metrics found, so return false
  RETURN MGMT_GLOBAL.G_FALSE;
      
END website_met_exists;

--
-- PROCEDURE: get_rolled_up_met_sev_ws
--
-- PURPOSE:
--   A private util function to get the rolled up metric severity across 
--   all the targets of type website of the composite target - 
--   (target_name_in, target_type_in) for the slowest response time metric
--   for the (availability transaction;local beacon name) of every website
--   Note that only the slowest response metric is considered for now. 
--
FUNCTION get_rolled_up_met_sev_ws
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2)
RETURN NUMBER IS

  l_rolled_up_sev NUMBER := 15;
  l_curr_sev NUMBER := 15;
  l_avail_txn_name VARCHAR2(64) := '';
  L_NO_METRIC_GUID constant raw(16) := '0000000000000000';
  l_comp_key RAW(16) := L_NO_METRIC_GUID;
  
BEGIN

  FOR record IN (

    SELECT mem.member_target_name, 
           mem.member_target_type
      FROM mgmt_targets tgt,
           mgmt_target_assocs mem,
           mgmt_targets ct,
           mgmt_targets mt
     WHERE ct.target_name = target_name_in
       AND ct.target_type = target_type_in
       AND mt.target_type = 'website'
       AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
       AND mem.source_target_guid = ct.target_guid
       AND mem.assoc_target_guid = mt.target_guid
       AND tgt.target_guid = mem.source_target_guid )

  LOOP  

    -- for this website, get the availability transaction name
    BEGIN

      SELECT txn.name 
        INTO l_avail_txn_name
        FROM mgmt_targets tgt, mgmt_bcn_txn_defn txn  
       WHERE tgt.target_name = record.member_target_name
         AND tgt.target_type = record.member_target_type
         AND txn.target_guid = tgt.target_guid  
         AND txn.is_representative = 'Y'  
         AND txn.txn_type = 'HTTP' ;

      EXCEPTION
        WHEN OTHERS
        THEN
          l_avail_txn_name := '';
    END;
              
    -- use this transaction name, and "-" for the beacon name and get the 
    -- composite key
    BEGIN
      SELECT composite_key  
        INTO l_comp_key
        FROM mgmt_metrics_composite_keys keys,  
             mgmt_targets tgt  
       WHERE tgt.target_name = record.member_target_name
         AND tgt.target_type = record.member_target_type
         AND tgt.target_guid = keys.target_guid  
         AND keys.key_part1_value = l_avail_txn_name
         AND keys.key_part2_value = '-' ;
                  
      EXCEPTION
        WHEN OTHERS
        THEN
          -- is this okay?
          l_comp_key := L_NO_METRIC_GUID;
    END;
    
    -- use these values to get the met sev status for the http_response
    -- max_response_time metric
    l_curr_sev := get_met_sev_blkout_status( record.member_target_name, 
                                             record.member_target_type, 
                                             'http_response', 
                                             'max_response_time', 
                                             l_comp_key );
    
    -- treat a blackout status as clear status
    IF( l_curr_sev = 1 )
    THEN
      l_curr_sev := 15;
    END IF;

    -- decide if rollup should happen between the values for the rolled up
    -- sev and the current sev            
    IF( should_rollup_sev( l_rolled_up_sev, l_curr_sev ) = 1 )
    THEN
      l_rolled_up_sev := l_curr_sev;
    END IF;

  END LOOP;
  
  RETURN l_rolled_up_sev;
  
END get_rolled_up_met_sev_ws;

-- A private util function. This is the same as in blackout_engine_pkgbody.sql
-- "Adjust" the target time to account for severities that
-- may have come in since this time.
-- This could happen with immediate blackouts where severities
-- come in before the blackout can be inserted, and the 
-- collection timestamp is after the blackout start time 
FUNCTION adjust_target_time(p_timestamp DATE,
                           p_target_guid RAW) RETURN DATE IS
l_agent_guid MGMT_TARGETS.target_guid%TYPE;
l_last_hb DATE;
l_last_hb_utc DATE;
l_comp_time DATE;
l_timezone_region VARCHAR2(64);
BEGIN
    -- Get the "current" agent time, as defined by the heartbeat
    BEGIN
        -- get the agent's target guid
        SELECT t2.target_guid 
          INTO l_agent_guid 
          FROM MGMT_TARGETS t1, 
               MGMT_TARGETS t2 
         WHERE t1.target_guid=p_target_guid 
           AND t1.emd_url=t2.emd_url 
           AND t2.target_type=MGMT_GLOBAL.G_AGENT_TARGET_TYPE;

        -- get the agent's last heartbeat timestamp, last heartbeat timestamp utc, timezone region
        SELECT p.last_heartbeat_ts, 
               p.last_heartbeat_utc,
               t.timezone_region
          INTO l_last_hb, 
               l_last_hb_utc,
               l_timezone_region
          FROM MGMT_EMD_PING p, 
               MGMT_TARGETS t 
         WHERE t.target_guid=p.target_guid 
           AND t.target_guid=l_agent_guid;

        -- now add the agent skew to the target's time
        l_comp_time := p_timestamp + (mgmt_global.to_utc(l_last_hb, l_timezone_region) - l_last_hb_utc);

        -- add 10 seconds to account for the oms loader processing ...
        l_comp_time := l_comp_time + (10/(24*60*60));
        
        RETURN l_comp_time;

    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            RETURN p_timestamp;
            
    END;

END adjust_target_time;

--
-- PROCEDURE: get_group_comp_sum_met
--
-- PURPOSE:
--   A private util procedure.
--   Returns a summary of severity details for the members of 
--     a particular type of an ocs group target.
--
PROCEDURE get_group_comp_sum_met(target_name_in IN VARCHAR2,
                               target_type_in IN VARCHAR2,
                               member_target_type_in IN VARCHAR2,
                               target_types IN SMP_EMD_STRING_ARRAY,
                               metric_names IN SMP_EMD_STRING_ARRAY,
                               metric_columns IN SMP_EMD_STRING_ARRAY,
                               key_values IN SMP_EMD_STRING_ARRAY,
                               metric_cols_cur_out OUT cursorType,
                               metric_sevs_cur_out OUT cursorType,
                               metric_vals_cur_out OUT cursorType,
                               metric_blkout_cur_out OUT cursorType,
                               metric_blkout_hist_out OUT cursorType,
                               targets_cur_out OUT cursorType) IS
  l_target_guid RAW(16);
  
  metric_guids METRIC_KEY_ARRAY := METRIC_KEY_ARRAY();
  mguid raw(16) := '0000000000000000';
  target_guids MGMT_GUID_ARRAY := MGMT_GUID_ARRAY();
  tguid raw(16) := '0000000000000000';  
  l_curr_idx INTEGER := 0;
  
  mname VARCHAR2(64) := '';
  mcol VARCHAR2(64) := '';
  msname VARCHAR2(64) := '';
BEGIN
  
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);
  
  FOR i in 1..metric_names.count
  LOOP
  
    BEGIN
      SELECT metric_guid
        INTO mguid
        FROM mgmt_metrics
       WHERE target_type = target_types(i)
         AND metric_name = metric_names(i)
         AND metric_column = metric_columns(i);
         
      metric_guids.extend;
      l_curr_idx := l_curr_idx + 1;
      metric_guids(l_curr_idx) := METRIC_KEY_OBJ( NULL, NULL, NULL );
      metric_guids(l_curr_idx).guid := mguid;
      metric_guids(l_curr_idx).name := target_types(i);
      metric_guids(l_curr_idx).key_value  := key_values(i);
      
      EXCEPTION
        WHEN OTHERS
        THEN
          null;
    END;
    
  END LOOP;
  
  FOR i in 1..metric_guids.count
  LOOP
    dbms_output.put_line( 'metric_guids[' || i || ']: ' || metric_guids(i).guid );
  END LOOP;
  
  l_curr_idx := 0;
  FOR record IN (
    SELECT mem.member_target_guid
      FROM mgmt_targets tgt,
           mgmt_target_assocs mem,
           mgmt_targets ct,
           mgmt_targets mt
     WHERE ct.target_name = target_name_in
          AND ct.target_type = target_type_in
          AND mt.target_type = member_target_type_in
          AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
          AND mem.source_target_guid = ct.target_guid
          AND mem.assoc_target_guid = mt.target_guid
          AND tgt.target_guid = mem.source_target_guid )
  LOOP  
      
    target_guids.extend;                   
    l_curr_idx := l_curr_idx + 1;
    target_guids(l_curr_idx) := MGMT_GUID_OBJ( NULL, NULL );
    target_guids(l_curr_idx).guid := record.member_target_guid;
    target_guids(l_curr_idx).name := '';

  END LOOP;
    
  FOR i in 1..target_guids.count
  LOOP
    dbms_output.put_line( 'target_guids[' || i || ']: ' || target_guids(i).guid );
  END LOOP;

  -- get the data for metric_cols_cur_out
  --  This only handles non composite keys
  OPEN metric_cols_cur_out FOR
      SELECT met.metric_guid, 
             met.metric_name, 
             met.metric_column, 
             met.short_name || ' ' || guids.key_value as short_name,
             guids.key_value
        FROM mgmt_metrics met,
             TABLE(CAST(metric_guids AS METRIC_KEY_ARRAY) ) guids
       WHERE met.target_type=guids.name
         AND met.metric_guid=guids.guid
         AND met.metric_type = 0
       ORDER BY met.short_name;  
    
  dbms_output.put_line( 'After getting the metric columns' );

  -- get the data for metric_sevs_cur_out
  OPEN metric_sevs_cur_out FOR
     SELECT /*+ ORDERED USE_HASH(sev) INDEX(tgt) INDEX(sev) INDEX(thr) */
            thr.target_guid, 
            thr.metric_guid, 
            NVL(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_CLEAR) severity_code,
            mguids.key_value
     FROM   mgmt_targets tgt, 
            mgmt_metrics met, 
            mgmt_metric_thresholds thr, 
            mgmt_current_severity sev,
            TABLE(CAST(target_guids AS MGMT_GUID_ARRAY) ) tguids,
            TABLE(CAST(metric_guids AS METRIC_KEY_ARRAY) ) mguids
     WHERE  tgt.target_guid    = tguids.guid
       AND  met.target_type    = tgt.target_type
       AND  met.metric_guid    = mguids.guid
       AND  met.metric_type    = 0
       AND  thr.target_guid    = tgt.target_guid 
       AND  thr.metric_guid    = met.metric_guid
       AND  thr.target_guid    = sev.target_guid (+)
       AND  thr.metric_guid    = sev.metric_guid (+)
       AND  sev.key_value      = mguids.key_value
       AND  thr.key_value      = mguids.key_value;
   
  dbms_output.put_line( 'After getting the metric severities' );

  -- get the data for metric_vals_cur_out
  OPEN metric_vals_cur_out FOR
    SELECT /*+ INDEX(curmet) */ 
           curmet.target_guid, 
           curmet.metric_guid, 
           ROUND(curmet.value,2) value,
           mguids.key_value
    FROM   mgmt_metrics met, 
           mgmt_current_metrics curmet,
           TABLE(CAST(target_guids AS MGMT_GUID_ARRAY) ) tguids,
           TABLE(CAST(metric_guids AS METRIC_KEY_ARRAY) ) mguids
    WHERE  met.target_type    = mguids.name
      AND  met.metric_guid    = mguids.guid       
      AND  curmet.target_guid = tguids.guid
      AND  met.metric_type    = 0
      AND  curmet.metric_guid = met.metric_guid
      AND  curmet.key_value   = mguids.key_value; 
  
  dbms_output.put_line( 'After getting the metric values' );

  -- get the data for metric_blkout_cur_out
  OPEN metric_blkout_cur_out FOR
     SELECT /*+ INDEX(tgt) INDEX(thr) */
            thr.target_guid, 
            thr.metric_guid, 
            mguids.key_value,
			group_comp_mntr.get_met_sev_blkout_status(tgt.target_name, tgt.target_type, met.metric_name, met.metric_column, mguids.key_value) blkout_status
     FROM   mgmt_targets tgt, 
            mgmt_metrics met, 
            mgmt_metric_thresholds thr, 
            TABLE(CAST(target_guids AS MGMT_GUID_ARRAY) ) tguids,
            TABLE(CAST(metric_guids AS METRIC_KEY_ARRAY) ) mguids
     WHERE  tgt.target_guid    = tguids.guid
       AND  met.target_type    = tgt.target_type
       AND  met.metric_guid    = mguids.guid
       AND  met.metric_type    = 0
       AND  thr.target_guid    = tgt.target_guid 
       AND  thr.metric_guid    = met.metric_guid
       AND  thr.key_value      = mguids.key_value;

  dbms_output.put_line( 'After getting the metric blackout status value' );

  OPEN targets_cur_out FOR
    SELECT tgt.target_name,
           tgt.target_type,
           tgt.target_name as display_name,
           tgt.type_display_name,
           NVL(avail.current_status,MGMT_GLOBAL.G_STATUS_UNKNOWN) cur_status,
           tgt.target_guid,
           NVL(sev.warning_count,0) AS warning_count,
           NVL(sev.critical_count,0) AS critical_count,
           DECODE(prop.property_value, NULL, 0, 1) is_group
      FROM mgmt_targets tgt,
           mgmt_target_assocs mem,
           mgmt_targets ct
           mgmt_current_availability avail,
           (SELECT SUM(DECODE(csev.severity_code, MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                   SUM(DECODE(csev.severity_code, MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                   target_guid tguid
              FROM mgmt_current_severity csev
             GROUP BY target_guid ) sev,
           (SELECT target_type, property_value
              FROM mgmt_type_properties
             WHERE property_name = 'is_group') prop
     WHERE ct.target_name = target_name_in
       AND ct.target_type = target_type_in
       AND mem.assoc_target_guid = tgt.target_guid
       AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
       AND mem.source_target_guid = ct.target_guid
       AND tgt.target_guid = avail.target_guid (+)
       AND tgt.target_guid = sev.tguid (+)
       AND tgt.target_type = prop.target_type (+)
       AND tgt.target_type = member_target_type_in
     ORDER BY tgt.type_display_name, tgt.target_name;           

  OPEN metric_blkout_hist_out FOR
    SELECT *
      FROM
        (SELECT /*+ INDEX(tgt) INDEX(met) INDEX(blkout) */
                tgt.target_name,
	    		met.column_label,
		    	blkout.blkout_timestamp,
			    blkout.severity_code,
	    		blkout.user_name
         FROM   mgmt_targets tgt, 
	            mgmt_metrics met,
                met_sev_blkout blkout, 
                TABLE(CAST(target_guids AS MGMT_GUID_ARRAY) ) tguids,
                TABLE(CAST(metric_guids AS METRIC_KEY_ARRAY) ) mguids
         WHERE tgt.target_guid = tguids.guid
	       AND tguids.guid = blkout.target_guid
    	   AND tgt.target_type = mguids.name
	       AND mguids.guid = blkout.metric_guid
    	   AND mguids.key_value = blkout.key_value
	       AND blkout.metric_guid = met.metric_guid
     	   AND mguids.name = met.target_type 
   	     ORDER BY blkout_timestamp desc   
    	 ) 
    WHERE rownum < 15;

  dbms_output.put_line( 'After getting the target values' );

END get_group_comp_sum_met;

--
-- PURPOSE:
--   Returns a summary of monitoring details for the members of
--     a particular target
--
PROCEDURE get_group_comp_mntr_summary
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2,
   member_target_type_in IN VARCHAR2,
   met_obj_in IN METRICS_OBJ_ARRAY,
   summary_out OUT cursorType,
   rolled_up_met_sev_out OUT cursorType)
IS

  l_target_guid  mgmt_targets.target_guid%TYPE;

BEGIN

  -- may throw a TARGET_DOES_NOT_EXIST EXCEPTION
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);

  IF( member_target_type_in IS NULL OR length(member_target_type_in) = 0 )
  THEN
    -- get member monitoring summary for all member targets of the
    -- composite target
    OPEN summary_out FOR
      SELECT tgt.target_name,
             tgt.target_type,
             tgt.target_name as display_name,
             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.target_guid,
             tgt.is_group AS is_group_type
        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_target_assocs members,
                     mgmt_current_severity sev,
                     mgmt_metrics met,
                     TABLE(CAST(met_obj_in AS METRICS_OBJ_ARRAY)) met_obj
               WHERE members.source_target_guid=l_target_guid
                 AND members.assoc_target_guid=sev.target_guid
                 AND members.assoc_guid = MGMT_ASSOC.g_contains_guid
                 AND met_obj.target_type = met.target_type
                 AND met_obj.metric_name = met.metric_name
                 AND met_obj.metric_column = met.metric_column
                 AND met.metric_guid = sev.metric_guid
                 AND met_obj.key_value = sev.key_value
               GROUP BY sev.target_guid) sev_detail
       WHERE mem.source_target_guid=l_target_guid
         AND mem.assoc_target_guid = tgt.target_guid
         AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
         AND tgt.target_guid = sev_detail.target_guid (+)
       ORDER BY tgt.type_display_name, tgt.target_name;

  ELSE
    -- get member monitoring summary for member targets of type -
    -- member_target_type_in of the composite target
    OPEN summary_out FOR
      SELECT tgt.target_name,
             tgt.target_type,
             tgt.target_name as display_name,
             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.target_guid,
             tgt.is_group AS is_group_type
        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_target_assocs members,
                     mgmt_current_severity sev,
                     mgmt_metrics met,
                     TABLE(CAST(met_obj_in AS METRICS_OBJ_ARRAY)) met_obj
               WHERE members.source_target_guid=l_target_guid
                 AND members.assoc_target_guid=sev.target_guid
                 AND members.assoc_guid = MGMT_ASSOC.g_assoc_guid
                 AND met_obj.target_type = met.target_type
                 AND met_obj.metric_name = met.metric_name
                 AND met_obj.metric_column = met.metric_column
                 AND met.metric_guid = sev.metric_guid
                 AND met_obj.key_value = sev.key_value
               GROUP BY sev.target_guid) sev_detail
       WHERE mem.source_target_guid=l_target_guid
         AND mem.assoc_target_guid = tgt.target_guid
         AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
         AND tgt.target_guid = sev_detail.target_guid (+)
         AND tgt.target_type=member_target_type_in
       ORDER BY tgt.type_display_name, tgt.target_name;
  END IF;

  -- get the rolled up metric sev data        
  get_grp_comp_rolledup_met_sev( target_name_in,
                                 target_type_in,
                                 member_target_type_in,
                                 met_obj_in,
                                 rolled_up_met_sev_out );
    
END get_group_comp_mntr_summary;

--
-- PROCEDURE: get_group_comp_sev_summary
--
-- PURPOSE:
--   Returns a summary of severity details for the members of 
--     a particular ocs group target.
--
PROCEDURE get_group_comp_sev_summary
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2,
   member_target_type_in IN VARCHAR2,
   met_obj_in IN METRICS_OBJ_ARRAY,
   metric_cols_cur_out OUT cursorType,
   metric_sevs_cur_out OUT cursorType,
   metric_vals_cur_out OUT cursorType,
   metric_blkout_cur_out OUT cursorType,
   metric_blkout_hist_out OUT cursorType,
   targets_cur_out OUT cursorType)
 IS
  target_types SMP_EMD_STRING_ARRAY;
  metric_names SMP_EMD_STRING_ARRAY;
  metric_columns SMP_EMD_STRING_ARRAY;
  metric_short_names SMP_EMD_STRING_ARRAY;
  key_values SMP_EMD_STRING_ARRAY;
  l_curr_idx INTEGER := 0;
  l_key_value VARCHAR2(256) := '';
BEGIN

  IF( met_obj_in IS NULL OR met_obj_in.count = 0 )
  THEN
    -- only create empty arrays
    target_types := SMP_EMD_STRING_ARRAY();
    metric_names := SMP_EMD_STRING_ARRAY();
    metric_columns := SMP_EMD_STRING_ARRAY();
    key_values := SMP_EMD_STRING_ARRAY();
  ELSE
    -- copy the contents of met_obj_in the different arrays
    target_types := SMP_EMD_STRING_ARRAY();
    metric_names := SMP_EMD_STRING_ARRAY();
    metric_columns := SMP_EMD_STRING_ARRAY();
    key_values := SMP_EMD_STRING_ARRAY();

    FOR i in 1..met_obj_in.count
    LOOP
        
      target_types.extend;
      metric_names.extend;
      metric_columns.extend;
      key_values.extend;  
         
      l_curr_idx := l_curr_idx + 1;
      
      target_types(l_curr_idx) := met_obj_in(l_curr_idx).target_type;
      metric_names(l_curr_idx) := met_obj_in(l_curr_idx).metric_name;
      metric_columns(l_curr_idx) := met_obj_in(l_curr_idx).metric_column;
      l_key_value := met_obj_in(l_curr_idx).key_value;
      -- handle null value for key values
      IF( l_key_value IS NULL )
      THEN
        l_key_value := ' ';
      END IF;
         
      key_values(l_curr_idx) := l_key_value;
      
    END LOOP;    
  END IF;
  
  get_group_comp_sum_met( target_name_in,
                            target_type_in,
                            member_target_type_in,
                            target_types,                            
                            metric_names,
                            metric_columns,
                            key_values,
                            metric_cols_cur_out,
                            metric_sevs_cur_out,
                            metric_vals_cur_out,
                            metric_blkout_cur_out,
                            metric_blkout_hist_out,
                            targets_cur_out );                                                        
                            
END get_group_comp_sev_summary;

--
-- PROCEDURE: get_group_comp_ws_sev_summary
--
-- PURPOSE:
--   Returns a summary of severity details for the website members of 
--     a particular ocs group target.
--
PROCEDURE get_group_comp_ws_sev_summary
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2,   
   targets_sev_cur_out OUT cursorType,
   metric_blkout_hist_out OUT cursorType)
IS
  l_target_name VARCHAR2(256) := '';
  l_target_type VARCHAR2(64) := '';
  l_target_display_name VARCHAR2(256) := '';
  l_target_guid RAW(16) := '0000000000000000';
  l_metric_name VARCHAR2(64) := 'http_response';
  l_metric_column VARCHAR2(64) := 'max_response_time';
  l_metric_guid RAW(16);
  l_key_value VARCHAR2(256) := '';
  l_metric_value NUMBER := 0;
  l_severity_value NUMBER := 15;
  l_blackout_status_value NUMBER := 15;
  l_has_thresholds NUMBER := 0; -- 0 does not have thresholds, 1 has thresholds
  target_met_sevs TARGET_MET_SEV_ARRAY := TARGET_MET_SEV_ARRAY();
  l_curr_idx INTEGER := 0;
  l_avail_txn_name VARCHAR2(64) := '';
  L_NO_METRIC_GUID constant raw(16) := '0000000000000000';
  l_num_thr NUMBER := 0;
  
BEGIN

  -- get the metric guid
  l_metric_guid := mgmt_target.get_metric_guid( 'website', 
                                                l_metric_name,
                                                l_metric_column );  
  
  FOR record IN (
    SELECT tgt.target_name,
           tgt.target_type, 
		   tgt.display_name,
		   tgt.target_guid
      FROM mgmt_targets tgt,
           mgmt_target_assocs mem,
           mgmt_targets ct,
           mgmt_targets nt
     WHERE ct.target_name = target_name_in
       AND ct.target_type = target_type_in
       AND mt.target_type = 'website'
       AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
       AND mt.target_guid = mem.assoc_target_guid
       AND ct.target_guid = mem.source_target_guid
       AND tgt.target_guid = mem.assoc_target_guid )
  LOOP  
      
    target_met_sevs.extend;                   
    l_curr_idx := l_curr_idx + 1;
    target_met_sevs(l_curr_idx) := TARGET_MET_SEV_OBJ( NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );

    -- add the target details
    target_met_sevs(l_curr_idx).target_name := record.target_name;
    target_met_sevs(l_curr_idx).target_type := record.target_type;
    target_met_sevs(l_curr_idx).target_display_name := record.display_name;
    target_met_sevs(l_curr_idx).target_guid := record.target_guid;

    -- add the metric details
    target_met_sevs(l_curr_idx).metric_name := l_metric_name;
    target_met_sevs(l_curr_idx).metric_column := l_metric_column;
    target_met_sevs(l_curr_idx).metric_guid := l_metric_guid;
    
    -- for this website, get the availability transaction name
    BEGIN

      SELECT txn.name 
        INTO l_avail_txn_name
        FROM mgmt_targets tgt, mgmt_bcn_txn_defn txn  
       WHERE tgt.target_name = record.target_name
         AND tgt.target_type = record.target_type
         AND txn.target_guid = tgt.target_guid  
         AND txn.is_representative = 'Y'  
         AND txn.txn_type = 'HTTP' ;

      EXCEPTION
        WHEN OTHERS
        THEN
          l_avail_txn_name := '';
    END;
        
    -- add the transaction name
    target_met_sevs(l_curr_idx).txn_name := l_avail_txn_name;

    -- add the beacon name
    target_met_sevs(l_curr_idx).bcn_name := '-';
              
    -- use this transaction name, and "-" for the beacon name and get the 
    -- composite key
    BEGIN
      SELECT composite_key  
        INTO l_key_value
        FROM mgmt_metrics_composite_keys keys,  
             mgmt_targets tgt  
       WHERE tgt.target_name = record.target_name
         AND tgt.target_type = record.target_type
         AND tgt.target_guid = keys.target_guid  
         AND keys.key_part1_value = l_avail_txn_name
         AND keys.key_part2_value = '-' ;
                  
      EXCEPTION
        WHEN OTHERS
        THEN
          -- is this okay?
          l_key_value := L_NO_METRIC_GUID;
    END;
    
    -- add the key value
    target_met_sevs(l_curr_idx).key_value := l_key_value;
    
    -- use these values to get the met sev status for the http_response
    -- max_response_time metric
    l_blackout_status_value := get_met_sev_blkout_status( record.target_name, 
                                                          record.target_type, 
                                                          l_metric_name,
                                                          l_metric_column,
                                                          l_key_value );
    
    -- add the blackout status value
    target_met_sevs(l_curr_idx).blackout_status_value := l_blackout_status_value;
    
    -- get the metric value
    BEGIN
      SELECT value  
        INTO l_metric_value
        FROM mgmt_current_metrics curmet
       WHERE curmet.target_guid = record.target_guid
         AND curmet.metric_guid = l_metric_guid
         AND curmet.key_value = l_key_value;
                  
      EXCEPTION
        WHEN OTHERS
        THEN
          -- is this okay?
          l_metric_value := 0;
    END;

    -- add the metric value    
    target_met_sevs(l_curr_idx).metric_value := l_metric_value;
    
    -- get the current severity value
    BEGIN
      SELECT severity_code  
        INTO l_severity_value
        FROM mgmt_current_severity cursev
       WHERE cursev.target_guid = record.target_guid
         AND cursev.metric_guid = l_metric_guid
         AND cursev.key_value = l_key_value;
                  
      EXCEPTION
        WHEN OTHERS
        THEN
          -- is this okay?
          l_severity_value := 15;
    END;
    
    -- add the severity value
    target_met_sevs(l_curr_idx).severity_value := l_severity_value;
    
    -- find out if this metric has thresholds
    BEGIN
      SELECT count(*)
        INTO l_num_thr
        FROM mgmt_admin_metric_thresholds
       WHERE target_guid = record.target_guid
         AND metric_guid = l_metric_guid
         AND key_value <> 'default_threshold'
         AND key_value = l_key_value;
                  
      EXCEPTION
        WHEN OTHERS
        THEN
          -- is this okay?
          l_num_thr := 0;
    END;
    
    -- add the has thr value
    IF( l_num_thr > 0 )
    THEN
      target_met_sevs(l_curr_idx).has_thresholds := 1;
    ELSE 
      target_met_sevs(l_curr_idx).has_thresholds := 0;
    END IF;
    
  END LOOP;
  
  -- return the targets cursor
  OPEN targets_sev_cur_out FOR
    SELECT s.target_name,
           s.target_type,
           s.target_display_name,
           s.target_guid,
           s.metric_name,
           s.metric_column,
           s.metric_guid,
           s.key_value,
           s.txn_name,
           s.bcn_name,
           s.metric_value,
           s.severity_value,
           s.blackout_status_value,
           s.has_thresholds
      FROM TABLE(CAST(target_met_sevs AS TARGET_MET_SEV_ARRAY) ) s;

  -- return the metric blackout history cursor
  OPEN metric_blkout_hist_out FOR
    SELECT *
      FROM
        (SELECT /*+ INDEX(blkout) */
                tsev.target_name,
	    		'Slowest Page Response' || ' ' || tsev.txn_name || ';' || tsev.bcn_name as column_label,
		    	blkout.blkout_timestamp,
			    blkout.severity_code,
	    		blkout.user_name
           FROM met_sev_blkout blkout, 
                TABLE(CAST(target_met_sevs AS TARGET_MET_SEV_ARRAY) ) tsev
         WHERE tsev.target_guid = blkout.target_guid
	       AND tsev.metric_guid = blkout.metric_guid
    	   AND tsev.key_value = blkout.key_value
   	     ORDER BY blkout_timestamp desc   
    	 ) 
    WHERE rownum < 15;
  
END get_group_comp_ws_sev_summary;

--
-- PROCEDURE: get_met_sev_blkout_status
--
-- PURPOSE:
--   Returns 1  if this metric severity should be shown as a blacked out icon.
--              this also means that the metric severity blackout is currently set.
--           15 if this metric severity should be shown as clear
--           20 if this metric severity should be shown as warning
--           25 if this metric severity should be shown as critical
--
FUNCTION get_met_sev_blkout_status
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2,
   metric_name_in IN VARCHAR2,
   metric_column_in IN VARCHAR2,
   key_value_in IN VARCHAR2)
RETURN NUMBER
IS
  -- the target guid of this target
  tguid RAW(16);
  
  -- the metric guid of this metric
  mguid RAW(16);

  -- the severity code of the most recent severity for this metric of this 
  -- target
  scode NUMBER := 15;

  -- the collection timestamp of the most recent severity for this metric 
  -- of this target
  stime DATE := SYSDATE;

  -- the severity code of the most recent blackout record for this metric 
  -- of this target
  bcode NUMBER := 15;

  -- the timestamp of the most recent blackout record for this metric of 
  -- this target
  btime DATE := SYSDATE;

  -- the collection timestamp of the most recent clear severity for this 
  -- metric of this target
  ctime DATE := SYSDATE;    
  
  severity_record_found NUMBER := 0; -- not found
  blkout_record_found NUMBER := 0; -- not found
  clr_sev_record_found NUMBER := 0; -- not found
  clr_sev_exists NUMBER := 0; -- 0 does not exist. 1 if a clear sev exists at a time greater than btime and less than stime
BEGIN

  -- the blackout references made in this procedure, the met_sev_blkout table
  -- is not related to the EM blackout feature
  -- NOTE: key_value_in is expected as ' ' if empty

  --dbms_output.put_line( target_name_in || ' - ' || target_type_in || ' - ' || metric_name_in || ' - ' || metric_column_in || ' - ' || key_value_in );

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

  -- get the metric guid
  mguid := mgmt_target.get_metric_guid( target_type_in, metric_name_in, metric_column_in );

  --dbms_output.put_line( 'tguid: ' || tguid || ', mguid: ' || mguid );
  
  -- get the severity code, collection timestamp for the most recent severity
  -- record for the metric of this target
  FOR record IN (
    SELECT severity_code, collection_timestamp
      FROM mgmt_severity
     WHERE target_guid = tguid
       AND metric_guid = mguid
       AND key_value = key_value_in
       AND severity_code IN ( 15, 20, 25 )
     ORDER by collection_timestamp DESC,
              severity_code ASC )
  LOOP
    
    -- only get the most recent row
    severity_record_found := 1;
    scode := record.severity_code;
    stime := record.collection_timestamp;    
    exit;
    
  END LOOP;
  
  --dbms_output.put_line( 'severity_record_found: ' || severity_record_found );
  --dbms_output.put_line( 'scode: ' || scode );
  --dbms_output.put_line( 'stime: ' || to_char(stime, 'mm-dd-yyyy hh:mi:ss AM') );

  -- if the most recent severity was not found, use an old date for stime
  IF( severity_record_found = 0 )
  THEN
    scode := 15;
    stime := SYSDATE-10000;
  END IF;
  
  -- get the severity code, timestamp of the most recent blackout record for 
  -- the metric of this target
  FOR record IN (
    SELECT severity_code, blkout_timestamp
      FROM met_sev_blkout
     WHERE target_guid = tguid
       AND metric_guid = mguid
       AND key_value = key_value_in
     ORDER by blkout_timestamp DESC )
  LOOP
    
    -- only get the most recent row
    blkout_record_found := 1;
    bcode := record.severity_code;
    btime := record.blkout_timestamp;    
    exit;
    
  END LOOP;
  
  --dbms_output.put_line( 'blkout_record_found: ' || blkout_record_found );
  --dbms_output.put_line( 'bcode: ' || bcode );
  --dbms_output.put_line( 'btime: ' || to_char(btime, 'mm-dd-yyyy hh:mi:ss AM') );

  -- if the blackout record is not found, simply return the value of scode
  IF( blkout_record_found = 0 )
  THEN
    -- show the severity icon based on scode
    --dbms_output.put_line( 'blackout record not found, so returning: ' || scode );
    RETURN scode;
  END IF;
  
  -- if the blackout record is found but bcode is 0, then this means that the
  -- metric severity blackout has been unset. in this case also, simply
  -- return the value of scode
  IF( bcode = 0 )
  THEN
    -- show the severity icon based on scode
    --dbms_output.put_line( 'blackout unset, so returning: ' || scode );
    RETURN scode;
  END IF;
  
  -- get the severity code, collection timestamp for the most recent 
  -- clear severity for the metric of this target
  FOR record IN (
    SELECT severity_code, collection_timestamp
      FROM mgmt_severity
     WHERE target_guid = tguid
       AND metric_guid = mguid
       AND key_value = key_value_in
       AND severity_code = 15
     ORDER by collection_timestamp DESC )
  LOOP
    
    -- only get the most recent row
    clr_sev_record_found := 1;
    ctime := record.collection_timestamp;    
    exit;
    
  END LOOP;
  
  --dbms_output.put_line( 'clr_sev_record_found: ' || clr_sev_record_found );
  --dbms_output.put_line( 'ctime: ' || to_char(ctime, 'mm-dd-yyyy hh:mi:ss AM') );

  -- if the most recent clear severity was not found, use an old date for ctime
  -- this also means a clear never occurred ...
  -- if the most recent clear severity is found, figure out if the clear
  -- severity timestamp is between greater than btime and less than stime
  IF( clr_sev_record_found = 0 )
  THEN
    ctime := SYSDATE-10000;
    clr_sev_exists := 0;
  ELSE    
    IF( (ctime > btime) AND (ctime < stime) )
    THEN
      clr_sev_exists := 1;
    ELSE
      clr_sev_exists := 0;
    END IF;
  END IF;
  
  --dbms_output.put_line( 'clr_sev_exists: ' || clr_sev_exists );

  -- if btime is greater than stime, simply return blue icon code
  IF( btime > stime )
  THEN
    -- show blue
    --dbms_output.put_line( 'btime > stime, so returning 1' );
    RETURN 1;
  END IF;
  
  --dbms_output.put_line( 'FINAL: bcode: ' || bcode || ', scode: ' || scode || ', clr_sev_exists: ' || clr_sev_exists );

  -- if we have reached this point, then btime < stime and a clear sev
  -- may or may not have occurred ...
  IF( (bcode = 15) OR (bcode = 25) )
  THEN
    -- the blue-ing/blackout was initiated when the severity was 15 or 25
    IF( scode = 15 )
    THEN
      -- show green
      RETURN 15;
    ELSIF( scode = 20 )
    THEN
      IF( clr_sev_exists = 1 )
      THEN
        -- show yellow
        RETURN 20;
      ELSE
        -- show blue
        RETURN 1;
      END IF;
    ELSIF( scode = 25 )
    THEN
      IF( clr_sev_exists = 1 )
      THEN
        -- show red
        RETURN 25;
      ELSE
        -- show blue
        RETURN 1;
      END IF;
    ELSE
      -- default to green
      RETURN 15;
    END IF;    
  ELSIF( bcode = 20 )
  THEN
    -- the blue-ing/blackout was initiated when the severity was 20
    IF( scode = 15 )
    THEN
      -- show green
      RETURN 15;
    ELSIF( scode = 20 )
    THEN
      -- show yellow
      RETURN 20;
    ELSIF( scode = 25 )
    THEN
      -- show red
      RETURN 25;
    ELSE
      -- default to green
      RETURN 15;
    END IF;    
  END IF;
  
END get_met_sev_blkout_status;

--
-- PROCEDURE: update_met_sev_blkout_status
--
-- PURPOSE:
--   Update the metrci severity blackout status for the given metric of the
--   given target type. This inserts a record in the table - MET_SEV_BLKOUT
--
--   If mode_in = 1, this is an action to "set" the blackout on the metric 
--   severity. 
--   If mode_in = 0, this is an action to "unset" the blackout on the metric 
--   severity
--
PROCEDURE update_met_sev_blkout_status
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2,
   metric_name_in IN VARCHAR2,
   metric_column_in IN VARCHAR2,
   key_value_in IN VARCHAR2,
   sev_code_in IN NUMBER,
   mode_in IN NUMBER) IS

  l_target_guid RAW(16);
  l_agent_target_guid RAW(16);
  l_metric_guid RAW(16);
  l_user_name VARCHAR2(64);
  l_message VARCHAR2(4000);
  l_sysdate DATE;
  l_cur_blkout_status NUMBER := 1; -- 1 -> set else not set

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

  -- get the metric guid
  l_metric_guid := mgmt_target.get_metric_guid( target_type_in, metric_name_in, metric_column_in );

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

  -- figure out if the metric severity is currently under blackout or not
  -- if l_cur_blkout_status = 1, the blackout is set, otherwise it is not set
  l_cur_blkout_status := get_met_sev_blkout_status( target_name_in,
                                                    target_type_in,
                                                    metric_name_in, 
                                                    metric_column_in,
                                                    key_value_in );
                                                    
  -- the current time should be converted to the time in the target's timezone
  -- because the severities for this target also have the timestamp in the
  -- target's timezone  
  l_sysdate := TGT_SYSDATE_WGUID( l_target_guid );
  
  -- the converted time could still be off because of the time skews on the
  -- repository machine and the target machine. so adjust the target time 
  -- further based on the last heart beat of the agent of this target ...
  l_sysdate := adjust_target_time( l_sysdate, l_target_guid );
  
  IF( mode_in = 1 )
  THEN
    -- set the blackout on the metric severity only if it not already set
    IF( l_cur_blkout_status <> 1 )
    THEN
      -- get the message
      l_message := 'Setting metric severity blackout ...';
  
      -- insert the record in the MET_SEV_BLKOUT table     
      BEGIN
        INSERT INTO MET_SEV_BLKOUT
          (target_guid,
           metric_guid,
           key_value,
           blkout_timestamp,
           severity_code,
           message,
           user_name)
        VALUES
          (l_target_guid,
           l_metric_guid,
           key_value_in,
           l_sysdate,
           sev_code_in,
           l_message,
           l_user_name);    

          EXCEPTION
            WHEN OTHERS
            THEN
              -- ignore the error message. 
              NULL;
      END;
    END IF;
  ELSE
    -- clear the blackout on the metric severity only if it is set
    IF( l_cur_blkout_status = 1 )
    THEN
      -- get the message
      l_message := 'Clearing metric severity blackout ...';

      -- insert the record in the MET_SEV_BLKOUT table with "0" for the 
      -- severity code indicating this is a unset of the metric sevetity
      -- blackout     
      BEGIN
        INSERT INTO MET_SEV_BLKOUT
          (target_guid,
           metric_guid,
           key_value,
           blkout_timestamp,
           severity_code,
           message,
           user_name)
        VALUES
          (l_target_guid,
           l_metric_guid,
           key_value_in,
           l_sysdate,
           0,
           l_message,
           l_user_name);    

          EXCEPTION
            WHEN OTHERS
            THEN
              -- ignore the error message. 
              NULL;
      END;
    END IF;
  END IF;
        
END update_met_sev_blkout_status;

--
-- PROCEDURE: get_rolled_up_met_sev
--
-- PURPOSE:
--   Get the rolled up metric severity across all the targets of type 
--   member_target_type_in of the composite target - 
--   (target_name_in, target_type_in) for all the summary metrics defined 
--   in met_obj_in 
--
FUNCTION get_rolled_up_met_sev
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2,
   member_target_type_in IN VARCHAR2,
   met_obj_in IN METRICS_OBJ_ARRAY)
RETURN NUMBER IS

  l_rolled_up_sev NUMBER := 15;
  l_curr_sev NUMBER := 15;
  
BEGIN

  FOR record IN (

    SELECT mem.member_target_name, 
           mem.member_target_type
      FROM mgmt_targets tgt,
           mgmt_target_assocs mem,
           mgmt_targets ct,
           mgmt_targets mt
     WHERE ct.target_name = target_name_in
       AND ct.target_type = target_type_in
       AND mt.target_type = member_target_type_in
       AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
       AND mt.target_guid = mem.assoc_target_guid
       AND ct.target_guid = mem.source_target_guid
       AND tgt.target_guid = mem.source_target_guid )

  LOOP  
      
    FOR i in 1..met_obj_in.count    
    LOOP
      --dbms_output.put_line( record.member_target_type || ' <-> ' || met_obj_in(i).target_type );
      IF( record.member_target_type = met_obj_in(i).target_type )
      THEN
        l_curr_sev := get_met_sev_blkout_status( record.member_target_name, record.member_target_type, met_obj_in(i).metric_name, met_obj_in(i).metric_column, met_obj_in(i).key_value );
        --dbms_output.put_line( record.member_target_name || ' - ' || record.member_target_type || ' - ' || met_obj_in(i).metric_name || ' - ' || met_obj_in(i).metric_column || ' - ' || met_obj_in(i).key_value || ' -> ' || l_curr_sev );

        -- treat a blackout status as clear status
        IF( l_curr_sev = 1 )
        THEN
          l_curr_sev := 15;
        END IF;

        -- decide if rollup should happen between the values for the rolled up
        -- sev and the current sev            
        IF( should_rollup_sev( l_rolled_up_sev, l_curr_sev ) = 1 )
        THEN
          l_rolled_up_sev := l_curr_sev;
        END IF;

      END IF;
    END LOOP;
    
  END LOOP;
  
  RETURN l_rolled_up_sev;
  
END get_rolled_up_met_sev;

--
-- PROCEDURE: get_grp_comp_rolledup_met_sev
--
-- PURPOSE:
--   Returns a summary of the rolled metric severity for every target type
--     of this composite target
--
-- ERROR CODES:
--   TARGET_DOES_NOT_EXIST_ERR: No such target was found. This could also mean
--     that the user does not have access to view this target.
--
PROCEDURE get_grp_comp_rolledup_met_sev
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2,
   member_target_type_in IN VARCHAR2,
   met_obj_in IN METRICS_OBJ_ARRAY,
   rolled_up_met_sev_out OUT cursorType) IS

  summary_data SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
  l_curr_idx NUMBER := 0;
  l_result NUMBER := 15;
  l_type_disp_name VARCHAR2(64) := '';
  l_website_met_exists NUMBER := MGMT_GLOBAL.G_FALSE;
  
BEGIN

  -- find out if atleast one website metric exists in met_obj_in ...
  l_website_met_exists := website_met_exists( met_obj_in );      
  
  IF( member_target_type_in IS NULL OR length(member_target_type_in) = 0 )
  THEN
    -- get rolled up severity summary for all member targets of the
    -- composite target
    FOR record IN (

      SELECT distinct mem.member_target_type,
             tgt.target_type type_display_name
        FROM mgmt_target_assocs mem,
             mgmt_targets ct
             mgmt_targets tgt
       WHERE ct.target_name = target_name_in
         AND ct.target_type = target_type_in
         AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
         AND ct.target_guid = mem.source_target_guid
         AND mem.assoc_target_guid = tgt.target_guid )

    LOOP  

      -- special case for websites: if the target type is website and
      -- no metrics have been configured for the website targets, 
      -- in this case, call a seperate function to get the rolled up
      -- metric severity for websites ...      
      IF( record.member_target_type = 'website' AND 
          l_website_met_exists = MGMT_GLOBAL.G_FALSE ) 
      THEN
        l_result := group_comp_mntr.get_rolled_up_met_sev_ws( 
                                              target_name_in, 
                                              target_type_in);
      ELSE
        -- get rolled up severity summary for member targets of type -
        -- record.member_target_type of the composite target
        l_result := group_comp_mntr.get_rolled_up_met_sev( 
                                              target_name_in, 
                                              target_type_in, 
                                              record.member_target_type,
                                              met_obj_in );
      END IF;
 
      summary_data.extend;
      l_curr_idx := l_curr_idx + 1;
      summary_data(l_curr_idx) := SMP_EMD_NVPAIR( NULL, NULL );
      summary_data(l_curr_idx).name := record.type_display_name;
      summary_data(l_curr_idx).value := l_result;

      dbms_output.put_line( record.member_target_type || ': ' || l_result );
            
    END LOOP;

    OPEN rolled_up_met_sev_out FOR
      SELECT s.name type_display_name,
             s.value rolled_up_sev
        FROM TABLE(CAST(summary_data AS SMP_EMD_NVPAIR_ARRAY) ) s;
        
  ELSE
      -- special case for websites: if the target type is website and
      -- no metrics have been configured for the website targets, 
      -- in this case, call a seperate function to get the rolled up
      -- metric severity for websites ...      
      IF( member_target_type_in = 'website' AND 
          l_website_met_exists = MGMT_GLOBAL.G_FALSE ) 
      THEN
        l_result := group_comp_mntr.get_rolled_up_met_sev_ws( 
                                              target_name_in, 
                                              target_type_in);
      ELSE
        -- get rolled up severity summary for member targets of type -
        -- member_target_type_in of the composite target
        l_result := group_comp_mntr.get_rolled_up_met_sev( 
                                              target_name_in, 
                                              target_type_in, 
                                              member_target_type_in,
                                              met_obj_in );
      END IF;
 
    -- get the type display name for member_target_type_in
    BEGIN
      SELECT distinct type_display_name
        INTO l_type_disp_name
        FROM mgmt_targets
       WHERE target_type = member_target_type_in;
    
      EXCEPTION
        WHEN OTHERS
        THEN
          l_type_disp_name := member_target_type_in;
    END;
    
    summary_data.extend;
    l_curr_idx := l_curr_idx + 1;
    summary_data(l_curr_idx) := SMP_EMD_NVPAIR( NULL, NULL );
    summary_data(l_curr_idx).name := l_type_disp_name;
    summary_data(l_curr_idx).value := l_result;

    dbms_output.put_line( member_target_type_in || ': ' || l_result );
    
    OPEN rolled_up_met_sev_out FOR
      SELECT s.name type_display_name,
             s.value rolled_up_sev
        FROM TABLE(CAST(summary_data AS SMP_EMD_NVPAIR_ARRAY) ) s;

  END IF;
  
END get_grp_comp_rolledup_met_sev;

--
-- PROCEDURE: get_group_overview_summary
--
-- PURPOSE:
--   Returns a summary of monitoring details for the members of 
--     a particular group target.
--
PROCEDURE get_group_overview_summary
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2,
   met_obj_in IN METRICS_OBJ_ARRAY,
   rolled_up_data_out OUT cursorType) IS
  
  summary_cur cursorType;
  rolled_up_met_sev_cur cursorType;
  target_rollup_data TARGET_ROLLUP_ARRAY := TARGET_ROLLUP_ARRAY();  
  l_curr_idx INTEGER := 0;
  l_current_status NUMBER := MGMT_GLOBAL.G_STATUS_UP; -- default to up
  l_rolled_up_status NUMBER := MGMT_GLOBAL.G_STATUS_UP; -- default to up
  l_current_severity NUMBER := MGMT_GLOBAL.G_SEVERITY_CLEAR; -- default to clear
  l_rolled_up_severity NUMBER := MGMT_GLOBAL.G_SEVERITY_CLEAR; -- default to clear
  ignore1 VARCHAR2(512) := '';
  ignore2 VARCHAR2(512) := '';
  ignore3 VARCHAR2(512) := '';
  ignore4 VARCHAR2(512) := '';
  ignore5 NUMBER := 0;
  ignore6 NUMBER := 0;
  ignore7 RAW(16) := '0000000000000000';
  ignore8 NUMBER := 0;
  
BEGIN

  -- loop through the group target and call get_group_comp_mntr_summary for
  -- every member of the group target
  FOR record IN (

    SELECT tgt.target_name, 
           tgt.target_type,
           tgt.display_name,
           tgt.type_display_name  
      FROM mgmt_targets tgt,
           mgmt_target_assocs mem,
           mgmt_targets ct
     WHERE ct.target_name = target_name_in
       AND ct.target_type = target_type_in
       AND ct.target_guid = mem.source_target_guid
       AND mem.assoc_guid = MGMT_ASSOc.g_contains_guid
       AND mem.assoc_target_guid = tgt.target_guid )

  LOOP  
    
    dbms_output.put_line( 'calling get_group_comp_mntr_summary for: ' || record.target_name || ', ' || record.target_type );
    get_group_comp_mntr_summary( record.target_name, 
                                 record.target_type, 
                                 '', 
                                 met_obj_in, 
                                 summary_cur, 
                                 rolled_up_met_sev_cur );
    
    -- initialize the rolled up status and severity values        
    l_rolled_up_status := MGMT_GLOBAL.G_STATUS_UP; 
    l_rolled_up_severity := MGMT_GLOBAL.G_SEVERITY_CLEAR;

    -- loop through summary_cur to get the rolled up status    
    dbms_output.put_line( 'looping through summary_cur' );
    LOOP
      FETCH summary_cur
       INTO ignore1,
            ignore2,
            ignore3,
            ignore4,
            l_current_status,
            ignore5,
            ignore6,
            ignore7,
            ignore8;

      EXIT WHEN summary_cur%NOTFOUND;

      dbms_output.put_line( 'before rollup: l_rolled_up_status: ' || l_rolled_up_status || ', l_current_status: ' || l_current_status );

      -- rollup the status
      IF( should_rollup_status( l_rolled_up_status, l_current_status ) = 1 )                   
      THEN
        l_rolled_up_status := l_current_status;
        dbms_output.put_line( 'after rollup: l_rolled_up_status: ' || l_rolled_up_status );
      END IF;
      
    END LOOP;
    CLOSE summary_cur;
    
    -- at this point l_rolled_up_status will have the rolled up status of 
    -- all member targets of record.target_name, record.target_type
    
    -- loop through rolled_up_met_sev_cur to get the rolled up metric
    -- severity 
    dbms_output.put_line( 'looping through rolled_up_met_sev_cur' );
    LOOP
      FETCH rolled_up_met_sev_cur
       INTO ignore1,
            l_current_severity;

      EXIT WHEN rolled_up_met_sev_cur%NOTFOUND;

      dbms_output.put_line( 'before rollup: l_rolled_up_severity: ' || l_rolled_up_severity || ', l_current_severity: ' || l_current_severity );

      -- rollup the severity
      IF( should_rollup_sev( l_rolled_up_severity, l_current_severity ) = 1 )                   
      THEN
        l_rolled_up_severity := l_current_severity;
        dbms_output.put_line( 'after rollup: l_rolled_up_severity: ' || l_rolled_up_severity );
      END IF;
      
    END LOOP;
    CLOSE rolled_up_met_sev_cur;
        
    -- at this point l_rolled_up_severity will have the rolled up severity of 
    -- all summary metrics of all member targets of 
    -- record.target_name, record.target_type

    -- now add the data to target_rollup_data
    target_rollup_data.extend;                   
    l_curr_idx := l_curr_idx + 1;
    target_rollup_data(l_curr_idx) := TARGET_ROLLUP_OBJ( NULL, NULL, NULL, NULL, NULL, NULL );

    dbms_output.put_line( 'adding record: ' ||
                          record.target_name || ', ' ||
                          record.target_type || ', ' ||
                          record.display_name || ', ' ||
                          record.type_display_name || ', ' ||
                          l_rolled_up_status || ', ' ||
                          l_rolled_up_severity );
                          
    target_rollup_data(l_curr_idx).target_name := record.target_name;    
    target_rollup_data(l_curr_idx).target_type := record.target_type;    
    target_rollup_data(l_curr_idx).display_name := record.display_name;    
    target_rollup_data(l_curr_idx).type_display_name := record.type_display_name;    
    target_rollup_data(l_curr_idx).rolled_up_status := l_rolled_up_status;    
    target_rollup_data(l_curr_idx).rolled_up_severity := l_rolled_up_severity;    
    
  END LOOP;

  -- return the targets rolled up data cursor - rolled_up_data_out
  OPEN rolled_up_data_out FOR
    SELECT s.target_name,
           s.target_type,
           s.display_name,
           s.type_display_name,
           s.rolled_up_status,
           s.rolled_up_severity
      FROM TABLE(CAST(target_rollup_data AS TARGET_ROLLUP_ARRAY) ) s;
  
END get_group_overview_summary;  

END group_comp_mntr;
/

show errors;