Rem
Rem $Header: severity_pkgbodys.sql 01-may-2008.23:59:54 pmodi Exp $
Rem
Rem severity_pkgbodys.sql
Rem
Rem Copyright (c) 2002, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      severity_pkgbodys.sql - utility functions used by severity trigger
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    pmodi       05/07/08 - Backport pmodi_bug-7010707 from
Rem                           st_emcore_10.2.0.1.0
Rem    pmodi       03/30/07 - Backport:Bug-5901284/5012587 Ignore ierr -ve 
Rem                           durtaion clsoing in Agent UP/DOWN sev
Rem    smalathe    09/04/07 - Bug 6379336: Fix query in get_severity_labels
Rem    pmodi       05/28/07 - Bug-6038522 Check previous status fro website
Rem                           target
Rem    pmodi       06/11/07 - Backport pmodi_bug-6038522 from main
Rem    rrawat      02/07/07 - Backport rrawat_5382668_10.2.0.2 from
Rem                           st_emcore_10.2.0.1.0
Rem    pmodi       03/16/06 - Bug:5095885 Perf related change in severity 
Rem                           purge routine 
Rem    pmodi       03/16/06 - Bug:5095885 Perf related change in severity 
Rem                           purge routine 
Rem    pmodi       08/07/06 - Backport pmodi_bug-5095885 from main
Rem    rkpandey    03/10/06 - Fix host notif supress
Rem    rkpandey    08/02/06 - Backport rkpandey_fix_host_notif from main
Rem    neearora    07/24/06 - project 21470. Added exec_crs_event_callbacks 
Rem    jsadras     07/17/06 - Backport jsadras_bug-4897312 from main 
Rem    jsadras     01/17/06 - Bug:4897312, clear_open_alerts 
Rem    jsadras     01/02/06 - add function for logging violation from external 
Rem                           systems 
Rem    hbadheka    12/27/05 - Add new function 
Rem    rpinnama    12/09/05 - Backport rpinnama_bug-4635074 from main 
Rem    pmodi       12/01/05 - Backport pmodi_bug-4740910 from main 
Rem    pmodi       11/21/05 - Add procedure for vio. deletions
Rem    rpinnama    09/29/05 - Fix 4639503 : Handle out of time sequence errors 
Rem                           while processing agent_up 
Rem    jsadras     09/12/05 - add keys_from_mult_colls 
Rem    paachary    09/01/05 - Bug 4586135
Rem    yaofeng     08/31/05 - fix alert history 
Rem    gan         08/30/05 - bug 4559108 
Rem    jsadras     08/02/05 - Bug:4529040, key exceptions handle 
Rem    rpinnama    07/25/05 - Fix 4415154 : Return new tatus from 
Rem                           process_unreachable_clear 
Rem    jsadras     07/19/05 - get_metric_data fix 4501159 
Rem    jsadras     07/07/05 - Add clear_alerts/clear_alerts for key
Rem    jsadras     07/04/05 - add cfg_columns to current violation 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    pmodi       06/20/05 - Bug:4389284- Change for NLS Message 
Rem    pmodi       06/17/05 - Use existing API for deleting violation 
Rem    pmodi       06/10/05 - Bug:4406767 -New proc for key-val deletion 
Rem    vkgarg      06/07/05 - return target timezone in get_severity_labels 
Rem    rpinnama    05/17/05 - Change violation_duration default to NULL 
Rem    rpinnama    04/20/05 - Fix 3981814: Calculate notif status based on 
Rem                           status before blackout 
Rem    rpinnama    04/15/05 - Remove redundant traces 
Rem    jsadras     04/01/05 - convert to urowid 
Rem    jsadras     03/30/05 - Bug:3952025, purge_rowids made to urowid 
Rem    rpinnama    04/05/05 - Add message parameter to clear_open_alers API 
Rem    jsadras     03/10/05 - Bug:4186866, convert to use timezone region 
Rem    neearora    03/03/05 - Added entry for emdw_log 
Rem    ancheng     02/17/05 - bug 2662095 
Rem    rpinnama    02/21/05 - Use violation API to close stateless severities
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    yaofeng     02/08/05 - improve sql performance 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    streddy     01/30/05 - Handle non-unique cluster memberships 
Rem    scgrover    01/27/05 - change ROWID type for iot conversion 
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    rpinnama    01/25/05 - Fix 4144032: Do a proper receovery for agent up after blackout
Rem    snakai      01/06/05 - use gensvc_avail instead of bcn_avail pkg 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    jsadras     12/05/04 - purge apis 
Rem    rpinnama    11/30/04 - Fix 3893345 : Fix add_target to delete avail 
Rem                           data before bootstrapping it. 
Rem    rpinnama    11/18/04 - Bug 4016430: Fix availabilty purge to remove 
Rem                           less records 
Rem    rpinnama    10/04/04 - Fix bug 3806046. 
Rem    groyal      09/23/04 - Change default of 
Rem                           MGMT_CURRENT_VIOLATION.EXEMPT_BY 
Rem    rpinnama    09/20/04 - Support informational violations
Rem    rpinnama    09/14/04 - Apply default 
Rem    ramalhot    08/25/04 - cutover to new assoc tables 
Rem    rpinnama    09/12/04 - Apply defaults on NULL values 
Rem    rpinnama    09/08/04 - mgmt_severity -> mgmt_violations change
Rem    rpinnama    09/07/04 - Add inser_violation API 
Rem    rpinnama    04/12/04 - Lock availability to serialize updates to 
Rem                           availability system
Rem    yaofeng     11/11/03 - don't use . * 
Rem    rpinnama    10/31/03 - Fix severity purge 
Rem    streddy     10/07/03 - Initialize comp target availability 
Rem    rpinnama    10/15/03 - Dont accept unreachable clear during blackouts 
Rem    rpinnama    09/07/03 - Add purge policy callbacks 
Rem    rzazueta    08/26/03 - Fix bug 2992498 
Rem    streddy     06/30/03 - Handle cluster member changes in OMS mediated monitoring
Rem    streddy     06/09/03 - Master Agent support
Rem    yaofeng     06/04/03 - fix wrong condition
Rem    mbhoopat    05/13/03 - support server alerts
Rem    streddy     04/17/03 - Handle composite targets during agent up/down
Rem    streddy     04/07/03 - Handle repository only targets
Rem    rpinnama    05/05/03 - Fix bug 2938027 : Remove time sequence check for blackouts
Rem    ancheng     03/24/03 - support 5 composite keys
Rem    jpyang      03/31/03 - nls support
Rem    rpinnama    03/03/03 - Consolidate avail record cursor
Rem    rpinnama    03/14/03 - Consolidate avail record cursor
Rem    ancheng     02/13/03 - target version fix
Rem    ancheng     12/12/02 - target version support
Rem    rpinnama    01/23/03 - Handle severities received during UNKWN after BLKOUT
Rem    rpinnama    12/18/02 - Add ordering for agent down
Rem    rpinnama    12/05/02 - Discard severities received during blackouts
Rem    skini       11/15/02 - Fix invalid cursor problem
Rem    rpinnama    11/08/02 - Add add_target API
Rem    rpinnama    11/01/02 - Disable logging for heartbeats
Rem    rpatti      10/31/02 - remove grp availability
Rem    rpinnama    11/06/02 - Close cursors
Rem    rpinnama    11/06/02 - Give lowest precedence to UNKNOWN status
Rem    yaofeng     09/26/02 - fix wrong column name
Rem    rpinnama    09/19/02 - Remove unnecessary errors
Rem    hsu         09/23/02 - fix no alert problem
Rem    yaofeng     08/22/02 - fix outer join problem
Rem    yaofeng     08/22/02 - fix outer join; improve performance
Rem    yaofeng     08/20/02 - fix wrong outer join
Rem    rpinnama    08/19/02 - Fix 2520691 : Exclude 
Rem    yaofeng     08/16/02 - multi column key support
Rem    yaofeng     08/15/02 - fix passing out an invalid record
Rem    rpinnama    08/15/02 - Loop through availability till we get a non-agentdown state
Rem    yaofeng     08/14/02 - use constans in SQL
Rem    yaofeng     08/13/02 - simplify severity chart computation
Rem    rpinnama    08/02/02 - Catch out-of-time sequence severities.
Rem    snakai      07/25/02 - beacon funcs/procs moved to pkg
Rem    rpinnama    07/25/02 - Fix host availability when EMD bounces
Rem    rpinnama    07/22/02 - Use newly created errors and exception
Rem    rpinnama    07/18/02 - Check for is_group property also
Rem    yaofeng     07/19/02 - add navigation on alert history page
Rem    rpinnama    07/09/02 - Populate the unreachable message
Rem    snakai      06/23/02 - add beacon availabililty
Rem    yaofeng     06/24/02 - fix severit chart problems
Rem    rpinnama    06/25/02 - Use constants for error messages
Rem    yaofeng     06/21/02 - add procedures for severity history chart
Rem    rpinnama    06/18/02 - Log errors 
Rem    rpinnama    06/12/02 - rpinnama_tgt_avail
Rem    rpinnama    06/06/02 - Created
Rem

CREATE OR REPLACE PACKAGE BODY EM_SEVERITY AS

  G_MODULE_NAME CONSTANT VARCHAR2(64) := MODULE;

  TYPE max_timestamp_cur IS REF CURSOR ;

  -- Forwad declaration - Start
  FUNCTION get_avail_string_nls(p_avail_status IN NUMBER)
    RETURN VARCHAR2 ;

  -- Forwad declaration - End
   

  PROCEDURE lock_avail_for_tgt(p_target_guid IN RAW) IS
    l_target_guid RAW(16);
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('lock_avail_for_tgt:Entry p_target_guid = ' || p_target_guid, G_MODULE_NAME);
    END IF;

    SELECT target_guid
      INTO l_target_guid
      FROM MGMT_CURRENT_AVAILABILITY
     WHERE target_guid = p_target_guid
      FOR UPDATE;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('lock_avail_for_tgt:Exit', G_MODULE_NAME);
    END IF;
  END lock_avail_for_tgt;

  PROCEDURE lock_avail_for_agt(p_emd_url IN VARCHAR2) IS
    l_avail_statuses      t_number_list;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('lock_avail_for_agt:Entry p_emd_url = ' || p_emd_url, G_MODULE_NAME);
    END IF;

    SELECT current_status
      BULK COLLECT INTO l_avail_statuses
      FROM MGMT_CURRENT_AVAILABILITY
     WHERE target_guid IN (SELECT target_guid FROM MGMT_TARGETS
                            WHERE emd_url = p_emd_url)
      ORDER BY target_guid
      FOR UPDATE;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('lock_avail_for_agt:Exit', G_MODULE_NAME);
    END IF;
  END lock_avail_for_agt;

  --
  -- PURPOSE 
  --   Checks whether the target is in blackout for the given timestamp
  -- 
  -- IN PARAMETERS 
  --   p_target_guid - target guid for which to check the blackout
  --   p_coll_ts     - timestamp that needs to be checked for blackout
  -- 
  -- OUT PARAMETERS 
  --   Returns TRUE if the timestamp is in blackout for the target
  --           FALSE otherwise.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity trigger
  --
  FUNCTION is_sev_in_blackout(p_target_guid IN RAW,
                              p_coll_ts     IN DATE) 
    RETURN BOOLEAN IS
    l_cnt_blkout NUMBER := 0;
    l_is_blackout BOOLEAN := FALSE;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('is_sev_in_blackout:Entry p_target_guid = ' || p_target_guid ||
                     ' p_coll_ts = ' || p_coll_ts, G_MODULE_NAME);
    END IF;

    SELECT COUNT(*) INTO l_cnt_blkout
      FROM MGMT_AVAILABILITY
     WHERE target_guid = p_target_guid
       AND current_status = MGMT_GLOBAL.G_STATUS_BLACKOUT
       AND start_collection_timestamp <= p_coll_ts
       AND (end_collection_timestamp >= p_coll_ts
         OR end_collection_timestamp IS NULL);

    IF (l_cnt_blkout > 0) THEN
      l_is_blackout := TRUE;
    END IF;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('is_sev_in_blackout:Exit', G_MODULE_NAME);
    END IF;

    RETURN l_is_blackout;

  END is_sev_in_blackout;

  --
  -- PURPOSE 
  --   Adds the target to the severity sub system
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the insert_target_trigger
  --
  PROCEDURE add_target(p_target_guid IN RAW,
                       p_target_type IN VARCHAR2,
                       p_is_group    IN NUMBER,
                       p_timezone_region IN VARCHAR2) IS
    l_start_time DATE := NULL;
    l_timezone_region MGMT_TARGETS.timezone_region%TYPE;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('add_target:Entry p_target_guid = ' || p_target_guid ||
                     ' p_target_type = '     || p_target_type ||
                     ' p_is_group = '        || p_is_group    ||    
                     ' p_timezone_region = ' || p_timezone_region, G_MODULE_NAME);
    END IF;

    l_start_time := TRUNC( (MGMT_GLOBAL.SYSDATE_TZRGN(p_timezone_region) - 32), 'DD') ;

    -- only if not a group - insert the bootstrap record for availability
    IF (p_is_group = 0) THEN

      -- Delete any existing records. 
      -- Should not have any previous records.
      DELETE FROM MGMT_AVAILABILITY
       WHERE target_guid = p_target_guid;

      DELETE FROM mgmt_current_availability
       WHERE target_guid = p_target_guid;

      DELETE FROM mgmt_availability_marker
       WHERE target_guid = p_target_guid;

      -- Add bootstrap records..
      INSERT INTO MGMT_AVAILABILITY
         (target_guid, current_status, start_collection_timestamp )
       VALUES
         (p_target_guid, MGMT_GLOBAL.G_STATUS_UNKNOWN, l_start_time);

       INSERT INTO MGMT_CURRENT_AVAILABILITY
         (target_guid, current_status, severity_guid,
          start_collection_timestamp)
       VALUES
         (p_target_guid, mgmt_global.G_STATUS_UNKNOWN, NULL,
          l_start_time);

      INSERT INTO MGMT_AVAILABILITY_MARKER
        (target_guid, marker_timestamp, marker_avail_status )
       VALUES
        (p_target_guid, l_start_time, MGMT_GLOBAL.G_STATUS_UNKNOWN);

    END IF;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('add_target:Exit', G_MODULE_NAME);
    END IF;
  END add_target;

  PROCEDURE insert_violation(
       p_target_guid           IN RAW,
       p_policy_guid           IN RAW,
       p_key_value             IN VARCHAR2 DEFAULT ' ',
       p_collection_timestamp  IN DATE     DEFAULT SYSDATE,
       p_violation_guid        IN RAW      DEFAULT SYS_GUID(),
       p_violation_level       IN NUMBER, 
       p_violation_type        IN NUMBER   DEFAULT 0,
       p_violation_duration    IN NUMBER   DEFAULT NULL,
       p_annotated_flag        IN NUMBER   DEFAULT 0,
       p_notification_status   IN NUMBER   DEFAULT 1,
       p_value                 IN NUMBER   DEFAULT NULL,
       p_string_value          IN VARCHAR2 DEFAULT NULL,
       p_message               IN VARCHAR2 DEFAULT NULL,
       p_message_nlsid         IN VARCHAR2 DEFAULT NULL,
       p_message_params        IN VARCHAR2 DEFAULT NULL,
       p_action_message        IN VARCHAR2 DEFAULT NULL,
       p_action_nlsid          IN VARCHAR2 DEFAULT NULL,    
       p_action_message_params IN VARCHAR2 DEFAULT NULL,
       p_advisory_id           IN VARCHAR2 DEFAULT NULL,
       p_cfg_coll_name         IN VARCHAR2 DEFAULT NULL,
       p_cfg_key_value         IN VARCHAR2 DEFAULT NULL,
       p_cfg_key_operator      IN NUMBER   DEFAULT NULL,
       p_load_timestamp        IN DATE     DEFAULT SYSDATE,
       p_user_name             IN VARCHAR2 DEFAULT '<SYSTEM>')
  IS
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('insert_violation:Entry p_target_guid = ' || p_target_guid ||
                     ' p_policy_guid = '           || p_policy_guid           ||
                     ' p_key_value = '             || p_key_value             ||
                     ' p_collection_timestamp = '  || p_collection_timestamp  ||
                     ' p_violation_guid = '        || p_violation_guid        ||
                     ' p_violation_level = '       || p_violation_level       ||
                     ' p_violation_type = '        || p_violation_type        ||
                     ' p_violation_duration = '    || p_violation_duration    ||
                     ' p_annotated_flag = '        || p_annotated_flag        ||
                     ' p_notification_status = '   || p_notification_status   ||
                     ' p_value = '                 || p_value                 ||     
                     ' p_string_value = '          || p_string_value          ||
                     ' p_message = '               || p_message               ||
                     ' p_message_nlsid = '         || p_message_nlsid         ||
                     ' p_message_params = '        || p_message_params        ||
                     ' p_action_message = '        || p_action_message        ||
                     ' p_action_nlsid = '          || p_action_nlsid          ||
                     ' p_action_message_params = ' || p_action_message_params ||     
                     ' p_advisory_id = '           || p_advisory_id           ||
                     ' p_cfg_coll_name = '         || p_cfg_coll_name         ||
                     ' p_cfg_key_value = '         || p_cfg_key_value         ||
                     ' p_cfg_key_operator = '      || p_cfg_key_operator      ||
                     ' p_load_timestamp = '        || p_load_timestamp        ||
                     ' p_user_name = '             || p_user_name, G_MODULE_NAME);
    END IF;

    INSERT INTO mgmt_violations
      (target_guid, policy_guid, key_value, collection_timestamp,
       violation_level, violation_type, violation_duration, violation_guid,
       annotated_flag, notification_status, value, string_value,
       message, message_nlsid, message_params,
       action_message, action_message_nlsid, action_message_params, advisory_id,
       cfg_coll_name, cfg_key_value, cfg_key_operator,
       load_timestamp, user_name)
     VALUES
      (p_target_guid, p_policy_guid, NVL(p_key_value, ' '), NVL(p_collection_timestamp, SYSDATE),
       p_violation_level, NVL(p_violation_type, 0), p_violation_duration, NVL(p_violation_guid, SYS_GUID()),
       NVL(p_annotated_flag, 0), NVL(p_notification_status, 1), p_value, p_string_value,
       p_message, p_message_nlsid, p_message_params,
       p_action_message, p_action_nlsid, p_action_message_params, p_advisory_id,
       p_cfg_coll_name, p_cfg_key_value, p_cfg_key_operator,
       NVL(p_load_timestamp, SYSDATE), NVL(p_user_name, '<SYSTEM>') );

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('insert_violation:Exit', G_MODULE_NAME);
    END IF;
  END insert_violation;

  --
  -- Update a violation row.
  --
  PROCEDURE update_violation(
       p_target_guid           IN RAW,
       p_policy_guid           IN RAW,
       p_key_value             IN VARCHAR2,
       p_collection_timestamp  IN DATE,
       p_violation_level       IN NUMBER   DEFAULT NULL,
       p_violation_type        IN NUMBER   DEFAULT NULL,
       p_violation_duration    IN NUMBER   DEFAULT NULL,
       p_annotated_flag        IN NUMBER   DEFAULT NULL,
       p_notification_status   IN NUMBER   DEFAULT NULL,
       p_value                 IN NUMBER   DEFAULT NULL,
       p_string_value          IN VARCHAR2 DEFAULT NULL,
       p_message               IN VARCHAR2 DEFAULT NULL,
       p_message_nlsid         IN VARCHAR2 DEFAULT NULL,
       p_message_params        IN VARCHAR2 DEFAULT NULL,
       p_action_message        IN VARCHAR2 DEFAULT NULL,
       p_action_nlsid          IN VARCHAR2 DEFAULT NULL,    
       p_action_message_params IN VARCHAR2 DEFAULT NULL,
       p_advisory_id           IN VARCHAR2 DEFAULT NULL,
       p_cfg_coll_name         IN VARCHAR2 DEFAULT NULL,
       p_cfg_key_value         IN VARCHAR2 DEFAULT NULL,
       p_cfg_key_operator      IN NUMBER   DEFAULT NULL,
       p_load_timestamp        IN DATE     DEFAULT NULL,
       p_user_name             IN VARCHAR2 DEFAULT NULL)
  IS
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('update_violation:Entry p_target_guid = ' || p_target_guid ||
                     ' p_policy_guid = '           || p_policy_guid           ||
                     ' p_key_value = '             || p_key_value             ||
                     ' p_collection_timestamp = '  || p_collection_timestamp  ||
                     ' p_violation_level = '       || p_violation_level       ||
                     ' p_violation_type = '        || p_violation_type        ||
                     ' p_violation_duration = '    || p_violation_duration    ||
                     ' p_annotated_flag = '        || p_annotated_flag        ||
                     ' p_notification_status = '   || p_notification_status   ||
                     ' p_value = '                 || p_value                 ||     
                     ' p_string_value = '          || p_string_value          ||
                     ' p_message = '               || p_message               ||
                     ' p_message_nlsid = '         || p_message_nlsid         ||
                     ' p_message_params = '        || p_message_params        ||
                     ' p_action_message = '        || p_action_message        ||
                     ' p_action_nlsid = '          || p_action_nlsid          ||
                     ' p_action_message_params = ' || p_action_message_params ||     
                     ' p_advisory_id = '           || p_advisory_id           ||
                     ' p_cfg_coll_name = '         || p_cfg_coll_name         ||
                     ' p_cfg_key_value = '         || p_cfg_key_value         ||
                     ' p_cfg_key_operator = '      || p_cfg_key_operator      ||
                     ' p_load_timestamp = '        || p_load_timestamp        ||
                     ' p_user_name = '             || p_user_name, G_MODULE_NAME);
    END IF;

    UPDATE mgmt_violations
       SET violation_level = NVL(p_violation_level, violation_level),
           violation_type = NVL(p_violation_type, violation_type),
           violation_duration = NVL(p_violation_duration, violation_duration),
           annotated_flag = NVL(p_annotated_flag, annotated_flag),
           notification_status = NVL(p_notification_status, notification_status),
           value = NVL(p_value, value),
           string_value = NVL(p_string_value, string_value),
           message = NVL(p_message, message),
           message_nlsid = NVL(p_message_nlsid, message_nlsid),
           message_params = NVL(p_message_params, message_params),
           action_message = NVL(p_action_message, action_message),
           action_message_nlsid = NVL(p_action_nlsid, action_message_nlsid),
           action_message_params = NVL(p_action_message_params, action_message_params),
           advisory_id = NVL(p_advisory_id, advisory_id),
           cfg_coll_name = NVL(p_cfg_coll_name, cfg_coll_name),
           cfg_key_value = NVL(p_cfg_key_value, cfg_key_value),
           cfg_key_operator = NVL(p_cfg_key_operator, cfg_key_operator),
           load_timestamp = NVL(p_load_timestamp, load_timestamp),
           user_name = NVL(p_user_name, user_name)
     WHERE target_guid = p_target_guid
       AND policy_guid = p_policy_guid
       AND key_value = p_key_value
       AND collection_timestamp = p_collection_timestamp;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('update_violation:Exit', G_MODULE_NAME);
    END IF;
  END update_violation;


  --
  -- Delete a violation row.
  --
  PROCEDURE delete_violation(
       p_target_guid           IN RAW,
       p_policy_guid           IN RAW,
       p_key_value             IN VARCHAR2,
       p_collection_timestamp  IN DATE)
  IS
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('delete_violation:Entry p_target_guid = ' || p_target_guid ||
                     ' p_policy_guid = '           || p_policy_guid           ||
                     ' p_key_value = '             || p_key_value             ||
                     ' p_collection_timestamp = '  || p_collection_timestamp, G_MODULE_NAME);
    END IF;

    DELETE FROM mgmt_violations
     WHERE target_guid = p_target_guid
       AND policy_guid = p_policy_guid
       AND key_value = p_key_value
       AND collection_timestamp = p_collection_timestamp;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('delete_violation:Exit', G_MODULE_NAME);
    END IF;
  END delete_violation;

  PROCEDURE add_violation_ctxt_row(
       p_target_guid           IN RAW,
       p_policy_guid           IN RAW,
       p_key_value             IN VARCHAR2,
       p_collection_timestamp  IN DATE,
       p_column_name           IN VARCHAR2,
       p_column_type           IN NUMBER,
       p_column_value          IN NUMBER,
       p_column_str_value      IN VARCHAR2)
  IS
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('add_violation_ctxt_row:Entry p_target_guid = ' || p_target_guid ||
                     ' p_policy_guid = '           || p_policy_guid           ||
                     ' p_key_value = '             || p_key_value             ||
                     ' p_collection_timestamp = '  || p_collection_timestamp  ||
                     ' p_column_name = '           || p_column_name           ||
                     ' p_column_type = '           || p_column_type           || 
                     ' p_column_value = '          || p_column_value          ||
                     ' p_column_str_value = '      || p_column_str_value, G_MODULE_NAME);
    END IF;

    INSERT INTO mgmt_violation_context
      (target_guid, policy_guid, key_value, collection_timestamp,
       column_name, column_type, column_value, column_str_value)
     VALUES
      (p_target_guid, p_policy_guid, p_key_value, p_collection_timestamp,
       p_column_name, p_column_type, p_column_value, p_column_str_value);

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('add_violation_ctxt_row:Exit', G_MODULE_NAME);
    END IF;
  END add_violation_ctxt_row;

  PROCEDURE add_violation_context(
       p_target_guid           IN RAW,
       p_policy_guid           IN RAW,
       p_key_value             IN VARCHAR2,
       p_collection_timestamp  IN DATE,
       p_violation_ctxt_list   IN MGMT_VIOL_NAME_VALUE_ARRAY DEFAULT NULL)
  IS
    l_violation_guid mgmt_violations.violation_guid%TYPE;
    l_viol_nvpair    mgmt_viol_name_value;
    l_col_val        NUMBER;
    l_col_str_val    mgmt_violation_context.column_str_value%TYPE;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('add_violation_context:Entry p_target_guid = ' || p_target_guid ||
                     ' p_policy_guid = '           || p_policy_guid           ||
                     ' p_key_value = '             || p_key_value             ||
                     ' p_collection_timestamp = '  || p_collection_timestamp, G_MODULE_NAME);
    END IF;

    IF (p_violation_ctxt_list IS NOT NULL) AND (p_violation_ctxt_list.COUNT > 0) THEN

      FOR viol_ctr IN p_violation_ctxt_list.FIRST..p_violation_ctxt_list.LAST
      LOOP

        l_viol_nvpair := p_violation_ctxt_list(viol_ctr);

        IF (l_viol_nvpair.type = MGMT_GLOBAL.G_METRIC_TYPE_NUMBER) THEN
          l_col_val := TO_NUMBER(l_viol_nvpair.value);
          l_col_str_val := NULL;
        ELSE
          l_col_val := NULL;
          l_col_str_val := l_viol_nvpair.value;
        END IF;

	add_violation_ctxt_row(
            p_target_guid           => p_target_guid,
            p_policy_guid           => p_policy_guid,
            p_key_value             => p_key_value,
            p_collection_timestamp  => p_collection_timestamp,
            p_column_name           => l_viol_nvpair.name,
            p_column_type           => l_viol_nvpair.type,
            p_column_value          => l_col_val,
            p_column_str_value      => l_col_str_val);

      END LOOP;

    END IF;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('add_violation_context:Exit', G_MODULE_NAME);
    END IF;
  END add_violation_context;
  -- 
  -- internal procedure to insert composite key
  --
  FUNCTION get_set_composite_key
    (p_target_guid   IN RAW,
     p_key_parts     IN MGMT_MEDIUM_STRING_ARRAY)
  RETURN RAW
  IS
  l_key_value mgmt_violations.key_value%TYPE ;
  l_dummy     NUMBER(1) ;
  l_string_array SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(' ',' ',' ',' ',' ') ;
  BEGIN
    FOR i IN p_key_parts.FIRST..p_key_parts.LAST
    LOOP
      l_string_array(i) := p_key_parts(i) ;
    END LOOP ;
 
    l_key_value := MGMT_GLOBAL.GET_COMPOSITE_KEY_GUID(l_string_array) ;

    BEGIN
      SELECT NULL
        INTO l_dummy
        FROM mgmt_metrics_composite_keys
       WHERE target_guid    = p_target_guid 
         AND composite_key  = l_key_value ;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      BEGIN
        INSERT INTO MGMT_METRICS_COMPOSITE_KEYS
        (target_guid, composite_key, key_part1_value, key_part2_value, 
         key_part3_value,key_part4_value,key_part5_value)
        VALUES
        (p_target_guid, l_key_value, l_string_array(1), l_string_array(2),
        l_string_array(3), l_string_array(4), l_string_array(5)) ;
      EXCEPTION  
      -- ignore dup keys if some other process inserted in meantime
      WHEN DUP_VAL_ON_INDEX THEN NULL ;
      END ;
   END ;

   RETURN(l_key_value) ;

  END get_set_composite_key ; 
  --
  -- Function to log a threshold alert
  -- and return the violation guid of the alert
  --
  FUNCTION log_external_alert
    (p_producer_id          IN VARCHAR2,
     p_target_type          IN VARCHAR2,
     p_target_name          IN VARCHAR2,
     p_metric_name          IN VARCHAR2,
     p_metric_column        IN VARCHAR2,
     p_key_parts            IN MGMT_MEDIUM_STRING_ARRAY,
     p_collection_timestamp IN DATE ,
     p_violation_level      IN NUMBER,
     p_value                IN VARCHAR2,
     p_message              IN VARCHAR2
     -- TBD: notify status
     )
  RETURN RAW
  IS
  l_target_guid mgmt_targets.target_guid%TYPE ;
  l_author      mgmt_metrics.author%TYPE ;
  l_metric_guid mgmt_metrics.metric_guid%TYPE ;
  l_metric_type mgmt_metrics.metric_type%TYPE ;
  l_value       mgmt_violations.value%TYPE ;
  l_string_value       mgmt_violations.string_value%TYPE ;
  l_num_keys    mgmt_metrics.num_keys%TYPE ;
  l_key_value   mgmt_violations.key_value%TYPE ;
  l_violation_guid  mgmt_violations.violation_guid%TYPE ;
  BEGIN
    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      DECLARE
      l_key VARCHAR2(4000) ;
      BEGIN
        IF p_key_parts IS NOT NULL AND
         p_key_parts.COUNT > 0
        THEN
          FOR i IN p_key_parts.FIRST..p_key_parts.LAST
          LOOP
            l_key := l_key||','||p_key_parts(i) ;
          END LOOP ;
        END IF ;
        EMDW_LOG.INFO('log_external_alert:Enter '||
                     p_producer_id||':'||
                     p_target_type ||':'||
                     p_target_name||':'||
                     p_metric_name ||':'||     
                     p_metric_column ||':'||  
                       l_key ||':'||
                     TO_CHAR(p_collection_timestamp,'DD-MON-YY HH24:MI:SS')||':'||
                     p_violation_level ||':',G_MODULE_NAME) ;
      END ;
    END IF ;
         
    BEGIN
      SELECT author,num_keys,target_guid,metric_guid,metric_type
        INTO l_author,l_num_keys,l_target_guid,l_metric_guid,l_metric_type
        FROM mgmt_metrics met,
             mgmt_targets tgt
       WHERE met.target_type   = p_target_type
         AND met.metric_name   = p_metric_name
         AND met.metric_column = p_metric_column
         AND tgt.target_type   = p_target_type
         AND tgt.target_name   = p_target_name
         AND met.type_meta_ver = tgt.type_meta_ver
         AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ')
         AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ') 
         AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ') 
         AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ') 
         AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ')  ;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR,
        'Invaid Target or Metric') ;
    END ;

    IF EMDW_LOG.P_IS_DEBUG_SET
    THEN
      EMDW_LOG.DEBUG('log_external_alerts:'||
          ' target='||l_target_guid||
          ' metric='||l_metric_guid||
          ' numkeys='||l_num_keys||
          ' mettype='||l_metric_type||
          ' author='||l_author,G_MODULE_NAME) ;
    END IF ;

    IF p_metric_name  !=  MGMT_GLOBAL.G_EXTERNAL_METRIC_NAME OR
       l_author != MGMT_GLOBAL.G_AUTHOR_EXTERNAL||p_producer_id 
    THEN
      RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR,
        'Cannot log alerts for EM defined metrics') ;
    END IF ;

    IF l_num_keys = 0
    THEN
      l_key_value := ' ' ;
    ELSE
      IF p_key_parts IS NULL OR
         p_key_parts.COUNT != l_num_keys
      THEN
        RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR,
          'Incorrect number of keys passed') ;
      END IF ;   
      IF l_num_keys = 1
      THEN
        l_key_value := p_key_parts(1) ;
      ELSE
        l_key_value := get_set_composite_key(l_target_guid,p_key_parts) ;
      END IF ; 
    END IF ;

    IF l_metric_type = MGMT_GLOBAL.G_METRIC_TYPE_NUMBER
    THEN
      l_value        := p_value ;
      l_string_value := NULL ;
    ELSE
      l_value        := NULL ;
      l_string_value := p_value ;
    END IF ;

    mgmt_violation.log_threshold_violation
     (
      p_target_type           => p_target_type,
      p_target_name           => p_target_name,
      p_metric_name           => p_metric_name,
      p_metric_column         => p_metric_column,
      p_key_value             => l_key_value,
      p_collection_timestamp  => p_collection_timestamp,
      p_violation_level       => p_violation_level,
      p_value                 => l_value,
      p_string_value          => l_string_value,
      p_message               => p_message
      ) ;

    SELECT violation_guid
      INTO l_violation_guid
      FROM mgmt_violations
     WHERE target_guid           = l_target_guid 
       AND policy_guid           = l_metric_guid
       AND key_value             = l_key_value
       AND collection_timestamp  = p_collection_timestamp
       AND violation_level       = p_violation_level ;

    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO('log_external_alerts:Exit '||
         ' key='||l_key_value||' guid='||l_violation_guid,G_MODULE_NAME) ;
    END IF ;

    RETURN(l_violation_guid) ;
 
  END log_external_alert ;

  PROCEDURE remove_violation_context(
       p_target_guid           IN RAW,
       p_policy_guid           IN RAW,
       p_key_value             IN VARCHAR2,
       p_collection_timestamp  IN DATE,
       p_column_name           IN VARCHAR2)
  IS
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('remove_violation_context:Entry p_target_guid = ' || p_target_guid ||
                     ' p_policy_guid = '           || p_policy_guid           ||
                     ' p_key_value = '             || p_key_value             ||
                     ' p_collection_timestamp = '  || p_collection_timestamp  ||
                     ' p_column_name = '           || p_column_name, G_MODULE_NAME);
    END IF;


    DELETE FROM mgmt_violation_context
     WHERE target_guid = p_target_guid
       AND policy_guid = p_policy_guid
       AND key_value = p_key_value
       AND collection_timestamp = p_collection_timestamp
       AND column_name = NVL(p_column_name, column_name);

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('remove_violation_context:Exit', G_MODULE_NAME);
    END IF;
  END remove_violation_context;

  --
  -- PURPOSE 
  --   Updates the current severity
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  --
  PROCEDURE update_current_severity(p_target_guid    IN RAW,
                                    p_metric_guid    IN RAW,
                                    p_collection_ts  IN DATE,
                                    p_load_timestamp IN DATE,
                                    p_severity_code  IN NUMBER,
                                    p_severity_type  IN NUMBER,
                                    p_severity_guid  IN RAW,
                                    p_annotated_flag IN NUMBER,
                                    p_key_value      IN VARCHAR2,
                                    p_message        IN VARCHAR2,
                                    p_message_nlsid  IN VARCHAR2,
                                    p_message_params IN VARCHAR2,
                                    p_action_message IN VARCHAR2,
                                    p_action_nlsid  IN VARCHAR2,
                                    p_action_message_params IN VARCHAR2,
                                    p_advisory_id IN VARCHAR2) IS
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('update_current_severity:Entry p_target_guid = ' || p_target_guid ||
                     ' p_metric_guid = '           || p_metric_guid           ||
                     ' p_collection_ts = '         || p_collection_ts         ||
                     ' p_load_timestamp = '        || p_load_timestamp        ||
                     ' p_severity_code = '         || p_severity_code         ||
                     ' p_severity_type = '         || p_severity_type         ||
                     ' p_severity_guid = '         || p_severity_guid         ||
                     ' p_annotated_flag = '        || p_annotated_flag        ||
                     ' p_key_value = '             || p_key_value             ||     
                     ' p_message = '               || p_message               || 
                     ' p_message_nlsid = '         || p_message_nlsid         ||
                     ' p_message_params = '        || p_message_params        ||
                     ' p_action_message = '        || p_action_message        ||
                     ' p_action_nlsid = '          || p_action_nlsid          ||
                     ' p_action_message_params = ' || p_action_message_params ||     
                     ' p_advisory_id = '           || p_advisory_id, G_MODULE_NAME);
    END IF;
    --
    -- Try to update the current severity record, since most times the
    -- record will exist, so we want to optimize for that case.
    --
    IF (EMDW_LOG.p_is_debug_set) THEN
      EMDW_LOG.debug('Updating current severity...', G_MODULE_NAME);
    END IF;

    UPDATE MGMT_CURRENT_SEVERITY
       SET severity_code = p_severity_code,
           collection_timestamp = p_collection_ts,
           message = p_message,
           message_nlsid = p_message_nlsid,
           message_params = p_message_params,
           action_message = p_action_message,
           action_nlsid = p_action_nlsid,
           action_message_params = p_action_message_params,
           advisory_id = p_advisory_id
     WHERE target_guid = p_target_guid
       AND metric_guid = p_metric_guid
       AND key_value = p_key_value;

    --
    -- The Update didn't find a row, so we need to insert the row into the
    -- MGMT_CURRENT_SEVERITY table...
    --
    IF SQL%NOTFOUND THEN
      INSERT INTO MGMT_CURRENT_SEVERITY
        (target_guid, metric_guid, collection_timestamp,
         load_timestamp, severity_code, severity_type, 
         severity_guid, annotated_flag, key_value, 
         message, message_nlsid, message_params,
         action_message, action_nlsid, action_message_params, 
         advisory_id)
       VALUES
        (p_target_guid, p_metric_guid, p_collection_ts,
         p_load_timestamp, p_severity_code, p_severity_type, 
         p_severity_guid, p_annotated_flag, p_key_value, 
         p_message, p_message_nlsid, p_message_params,
         p_action_message, p_action_nlsid, p_action_message_params,
         p_advisory_id);
    END IF; -- SQL%NOTFOUND

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('update_current_severity:Exit', G_MODULE_NAME);
    END IF;
  END update_current_severity;

  --
  -- PURPOSE 
  --   Deletes the current severity for the given target.
  -- 
  -- IN PARAMETERS 
  --   p_target_guid - target_guid for which the current severity
  --                   has to be deleted.
  --   p_metric_guid - metric_guid for which the current severity
  --                   has to be deleted.
  --   p_key_value   - key value for which the current severity
  --                   has to be deleted.
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  --
  PROCEDURE delete_current_severity(p_target_guid IN RAW,
                                    p_metric_guid IN RAW,
                                    p_key_value   IN VARCHAR2) IS
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('delete_current_severity:Entry p_target_guid = ' || p_target_guid ||
                     ' p_metric_guid = '|| p_metric_guid ||
                     ' p_key_value = '  || p_key_value, G_MODULE_NAME);
    END IF;

    DELETE FROM MGMT_CURRENT_SEVERITY
     WHERE target_guid = p_target_guid
       AND metric_guid = p_metric_guid
       AND key_value = p_key_value;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('delete_current_severity:Exit', G_MODULE_NAME);
    END IF;
  END delete_current_severity;

  PROCEDURE update_current_violation(p_target_guid     IN RAW,
                                     p_policy_guid     IN RAW,
				     p_key_value       IN VARCHAR2,
				     p_collection_ts   IN DATE DEFAULT NULL,
				     p_violation_guid  IN RAW DEFAULT NULL,
				     p_violation_level IN NUMBER DEFAULT NULL,
				     p_violation_type  IN NUMBER DEFAULT NULL,
				     p_annotated_flag  IN NUMBER DEFAULT NULL,
				     p_value           IN NUMBER DEFAULT NULL,
				     p_string_value    IN VARCHAR2 DEFAULT NULL,
				     p_message         IN VARCHAR2 DEFAULT NULL,
				     p_message_nlsid   IN VARCHAR2 DEFAULT NULL,
				     p_message_params  IN VARCHAR2 DEFAULT NULL,
				     p_action_message  IN VARCHAR2 DEFAULT NULL,
				     p_action_nlsid    IN VARCHAR2 DEFAULT NULL,
				     p_action_message_params IN VARCHAR2 DEFAULT NULL,
                                     p_advisory_id     IN VARCHAR2 DEFAULT NULL,
				     p_exempt_code     IN NUMBER   DEFAULT NULL,
				     p_exempt_until    IN DATE     DEFAULT NULL,
				     p_exempt_by       IN VARCHAR2 DEFAULT NULL,
                                     p_cfg_coll_name   IN VARCHAR2 DEFAULT NULL,
                                     p_cfg_key_value   IN VARCHAR2 DEFAULT NULL,
                                     p_cfg_key_operator IN NUMBER  DEFAULT NULL 
                                    )
  IS
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('update_current_violation:Entry p_target_guid = ' || p_target_guid ||
                     ' p_policy_guid = '           || p_policy_guid           ||
                     ' p_key_value = '             || p_key_value             ||
                     ' p_collection_ts = '         || p_collection_ts         ||
                     ' p_violation_guid = '        || p_violation_guid        ||
                     ' p_violation_level = '       || p_violation_level       ||
                     ' p_violation_type = '        || p_violation_type        ||
                     ' p_annotated_flag = '        || p_annotated_flag        ||
                     ' p_value = '                 || p_value                 ||     
                     ' p_string_value = '          || p_string_value          ||
                     ' p_message = '               || p_message               ||
                     ' p_message_nlsid = '         || p_message_nlsid         ||
                     ' p_message_params = '        || p_message_params        ||
                     ' p_action_message = '        || p_action_message        ||
                     ' p_action_nlsid = '          || p_action_nlsid          ||
                     ' p_action_message_params = ' || p_action_message_params ||     
                     ' p_advisory_id = '           || p_advisory_id           ||
                     ' p_exempt_code = '           || p_exempt_code           ||
                     ' p_exempt_until = '          || p_exempt_until          ||
                     ' p_exempt_by = '             || p_exempt_by             ||
                     ' cfg_coll_name = '           || p_cfg_coll_name         ||
                     ' cfg_key_value = '           || p_cfg_key_value         ||
                     ' cfg_key_op =:'              || p_cfg_key_operator      
                     , G_MODULE_NAME);
    END IF;

    --
    -- Try to update the current severity record, since most times the
    -- record will exist, so we want to optimize for that case.
    --
    UPDATE mgmt_current_violation
       SET collection_timestamp = NVL(p_collection_ts, collection_timestamp),
           violation_guid = NVL(p_violation_guid, violation_guid),
           violation_level = NVL(p_violation_level, violation_level),
           violation_type = NVL(p_violation_type, violation_type),
	   annotated_flag = NVL(p_annotated_flag, annotated_flag),
	   value = NVL(p_value, value),
	   string_value = NVL(p_string_value, string_value),
           message = NVL(p_message, message),
           message_nlsid = NVL(p_message_nlsid, message_nlsid),
           message_params = NVL(p_message_params, message_params),
           action_message = NVL(p_action_message, action_message),
           action_message_nlsid = NVL(p_action_nlsid, action_message_nlsid),
           action_message_params = NVL(p_action_message_params, action_message_params),
           advisory_id = NVL(p_advisory_id, advisory_id),
	   exempt_code = NVL(p_exempt_code, exempt_code),
	   exempt_until = NVL(p_exempt_until, exempt_until),
	   exempt_by = NVL(p_exempt_by, exempt_by),
           cfg_coll_name=NVL(p_cfg_coll_name,cfg_coll_name),
           cfg_key_value=NVL(p_cfg_key_value,cfg_key_value),
           cfg_key_operator=NVL(p_cfg_key_operator,cfg_key_operator)
     WHERE target_guid = p_target_guid
       AND policy_guid = p_policy_guid
       AND key_value = p_key_value;

    IF SQL%NOTFOUND THEN

      -- Enforce default values for exempt_code and exempt_by 
      -- while inserting
      INSERT INTO mgmt_current_violation
        (target_guid, policy_guid, key_value, collection_timestamp,
	 violation_guid, violation_level, violation_type, annotated_flag,
	 value, string_value, message, message_nlsid, message_params,
         action_message, action_message_nlsid, action_message_params, 
         advisory_id, exempt_code, exempt_until, 
	 exempt_by,cfg_coll_name,cfg_key_value,cfg_key_operator)
       VALUES
        (p_target_guid, p_policy_guid, p_key_value, p_collection_ts,
	 p_violation_guid, p_violation_level, p_violation_type, p_annotated_flag,
	 p_value, p_string_value, p_message, p_message_nlsid, p_message_params,
         p_action_message, p_action_nlsid, p_action_message_params, 
         p_advisory_id, NVL(p_exempt_code, 0), p_exempt_until, p_exempt_by,
         p_cfg_coll_name,p_cfg_key_value,p_cfg_key_operator);

    END IF; -- SQL%NOTFOUND

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('update_current_violation:Exit', G_MODULE_NAME);
    END IF;
  END update_current_violation;


  PROCEDURE delete_current_violation(p_target_guid IN RAW,
                                     p_policy_guid IN RAW,
				     p_key_value   IN VARCHAR2)
  IS
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('delete_current_violation:Entry p_target_guid = ' || p_target_guid ||
                     ' p_policy_guid = ' || p_policy_guid           ||
                     ' p_key_value = '   || p_key_value, G_MODULE_NAME);
    END IF;

    DELETE FROM mgmt_current_violation
     WHERE target_guid = p_target_guid
       AND policy_guid = p_policy_guid
       AND key_value = p_key_value;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('delete_current_violation:Exit', G_MODULE_NAME);
    END IF;
  END delete_current_violation;

  PROCEDURE convert_keys(p_target_guid          IN RAW,
                         p_policy_guid          IN RAW,
                         p_key_values_in        IN mgmt_key_value_array,
                         p_collection_timestamp IN DATE,
                         p_num_keys             IN NUMBER,
                         p_key_values_out       OUT NOCOPY mgmt_medium_string_table)
  IS
  l_key_values_temp mgmt_medium_string_table := mgmt_medium_string_table() ;
  l_key_values_out_temp mgmt_medium_string_table := mgmt_medium_string_table() ;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('convert_keys:Entry p_target_guid = ' || p_target_guid ||
                     ' p_policy_guid = '           || p_policy_guid           ||
                     ' p_collection_timestamp = '  || p_collection_timestamp  ||
                     ' p_num_keys = '              || p_num_keys, G_MODULE_NAME);
    END IF;

    p_key_values_out := mgmt_medium_string_table() ;
    FOR row IN p_key_values_in.FIRST..p_key_values_in.LAST
    LOOP
      l_key_values_temp := mgmt_medium_string_table('%','%','%','%','%') ;
      l_key_values_out_temp := NULL ;
      IF p_key_values_in.EXISTS(row)
      THEN
        FOR col IN 1..p_num_keys
        LOOP
          IF p_key_values_in(row).EXISTS(col) AND
             p_key_values_in(row)(col) IS NOT NULL
          THEN
            l_key_values_temp(col) := p_key_values_in(row)(col) ;
          END IF ;
        END LOOP ;

        IF p_num_keys = 1
        THEN
          BEGIN
            SELECT DISTINCT key_value
            BULK COLLECT INTO l_key_values_out_temp
              FROM mgmt_violations
             WHERE target_guid = p_target_guid 
               AND policy_guid = p_policy_guid 
               AND collection_timestamp <= NVL(p_collection_timestamp,
                                    collection_timestamp)
               AND key_value   LIKE l_key_values_temp(1) ESCAPE '\' ; --'
          EXCEPTION WHEN NO_DATA_FOUND THEN NULL ;
          END  ;
        ELSE
          BEGIN
            -- TBD: What if key values contain ' ' should end user enter
            -- ' '??
            SELECT composite_key
              BULK COLLECT INTO l_key_values_out_temp
              FROM mgmt_metrics_composite_keys
             WHERE target_guid = p_target_guid 
               AND key_part1_value          LIKE l_key_values_temp(1) ESCAPE '\'
               AND NVL(key_part2_value,'%') LIKE l_key_values_temp(2) ESCAPE '\'
               AND NVL(key_part3_value,'%') LIKE l_key_values_temp(3) ESCAPE '\'
               AND NVL(key_part4_value,'%') LIKE l_key_values_temp(4) ESCAPE '\'
               AND NVL(key_part5_value,'%') LIKE l_key_values_temp(5) ESCAPE '\'
               ; --'
          EXCEPTION 
          WHEN NO_DATA_FOUND THEN NULL ;
          END ;
        END IF ; -- num keys
        IF l_key_values_out_temp IS NOT NULL AND l_key_values_out_temp.COUNT >0
        THEN
          FOR i IN l_key_values_out_temp.FIRST..l_key_values_out_temp.LAST
          LOOP
            p_key_values_out.extend(1);
            p_key_values_out(p_key_values_out.COUNT):=l_key_values_out_temp(i);
          END LOOP ;
        END IF ;
      END IF ; --exists row
    END LOOP ; -- loop key_values_in
  
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('convert_keys:Exit', G_MODULE_NAME);
    END IF;
  END convert_keys ;
  --
  --Internal procedure not to be exposed in spec.
  --Purges all records for a given key below the given timestamp
  --Assumption: the timestamp given does not cut across life cycle.
  --            Make sure the timestamp provided does not break
  --            life cycles before calling this. 
  --
  PROCEDURE purge_violations_for_key(p_target_guid       IN RAW,
                                     p_policy_guid       IN RAW,
                                     p_key_value         IN VARCHAR2,
                                     p_end_timestamp     IN DATE,
                                     p_start_timestamp   IN DATE DEFAULT NULL)
  IS
  l_violation_guids mgmt_target_guid_array := mgmt_target_guid_array() ;
  l_viol_purged NUMBER ;
  l_viol_ctx_purged NUMBER ;
  l_notify_log_purged  NUMBER ;
  l_annot_purged    NUMBER ;
  l_notify_rq       NUMBER ;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('purge_violations_for_key:Entry p_target_guid = ' || p_target_guid ||
                     ' p_policy_guid = '    || p_policy_guid   ||
                     ' p_key_value = '      || p_key_value     ||
                     ' p_end_timestamp = '  || p_end_timestamp ||
                     ' p_start_timestamp = '|| p_start_timestamp, G_MODULE_NAME);
    END IF;


    IF EMDW_LOG.P_IS_DEBUG_SET
    THEN
      EMDW_LOG.DEBUG('purge_violations_for_key:Enter Key='|| p_key_value||
        ' timestamp<='||to_char(p_end_timestamp,'DD-MON-YY HH24:MI')||' AND >='
         ||to_char(p_start_timestamp,'DD-MON-YY HH24:MI'),G_MODULE_NAME);
    END IF ;
        
    DELETE mgmt_violations
     WHERE target_guid = p_target_guid 
       AND policy_guid = p_policy_guid 
       AND key_value   = p_key_value  
       AND collection_timestamp <= p_end_timestamp
       AND collection_timestamp >= NVL(p_start_timestamp,collection_timestamp)
     RETURNING violation_guid BULK COLLECT INTO l_violation_guids ;

    l_viol_purged := SQL%ROWCOUNT ;
    -- if there are no violations , then there will not be related records
    -- we are not deleting mgmt_current_violation because we are only deleting
    -- closed violations
    IF l_viol_purged > 0 
    THEN
        -- Delete violation context
        DELETE FROM mgmt_violation_context
         WHERE target_guid = p_target_guid
           AND policy_guid = p_policy_guid
           AND key_value   = p_key_value
           AND collection_timestamp  <= p_end_timestamp  
           AND collection_timestamp >= NVL(p_start_timestamp,
                                           collection_timestamp) ;
   
        l_viol_ctx_purged := SQL%ROWCOUNT ;

        purge_for_violation_guids
        (
          p_violation_guids    => l_violation_guids,
          p_annot_purged       => l_annot_purged,
          p_notify_log_purged  => l_notify_log_purged,
          p_notify_rq          => l_notify_rq
        );

        COMMIT ;
      END IF ;
      IF EMDW_LOG.P_IS_INFO_SET
      THEN
        EMDW_LOG.DEBUG('purge_violations_for_key:Purged'||
                    ' Violations='||l_viol_purged||
                    ' Contexts='||l_viol_ctx_purged||
                    ' Annot='||l_annot_purged||
                     ' Requeue='||l_notify_rq||
                     ' Notify log='||l_notify_log_purged
                      ,G_MODULE_NAME) ;
        EMDW_LOG.INFO('purge_violations_for_key:Exit normal',G_MODULE_NAME) ;
      END IF ;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('purge_violations_for_key:Exit', G_MODULE_NAME);
    END IF;
  END purge_violations_for_key ;
  --
  -- Purge the severity lifecycle which is in the collection timestamp
  --
  PROCEDURE purge_cycle(p_target_guid         IN RAW,
                       p_policy_guid          IN RAW,
                       p_key_value            IN VARCHAR2,
                       p_collection_timestamp IN DATE)
  IS
  l_start_timestamp mgmt_violations.collection_timestamp%type ;
  l_end_timestamp mgmt_violations.collection_timestamp%type ;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('purge_cycle:Entry p_target_guid = ' || p_target_guid ||
                     ' p_policy_guid = '           || p_policy_guid           ||
                     ' p_key_value = '             || p_key_value             ||
                     ' p_collection_timestamp = '  || p_collection_timestamp, G_MODULE_NAME);
    END IF;

    SELECT min(collection_timestamp) ,
           max(collection_timestamp)
      INTO l_start_timestamp,
           l_end_timestamp
      FROM mgmt_violations
     WHERE target_guid = p_target_guid 
       AND policy_guid = p_policy_guid 
       AND key_value   = p_key_value   
     GROUP by cycle_guid
    HAVING min(collection_timestamp) <= p_collection_timestamp 
       AND max(collection_timestamp) >= p_collection_timestamp ;
                       
    purge_violations_for_key(p_target_guid=>p_target_guid,
                             p_policy_guid=>p_policy_guid,
                             p_key_value=>p_key_value,
                             p_end_timestamp=>l_end_timestamp,
                             p_start_timestamp=>l_start_timestamp) ;

    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO('purge_cycle:Exit',G_MODULE_NAME) ;
    END IF ;

  EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
      ' More than 1 severity life cycle for the collection timestamp for'||
      ' target_guid='||p_target_guid||' policy_guid='||p_policy_guid) ; 
  WHEN NO_DATA_FOUND THEN 
    IF EMDW_LOG.P_IS_ERROR_SET
    THEN
      EMDW_LOG.ERROR('purge_cycle:Exit no data',G_MODULE_NAME) ;
    END IF ;
  WHEN OTHERS THEN
    raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
      substr(sqlerrm,1,100)||' When processing purging of severity for '||
      ' target_guid='||p_target_guid||' policy_guid='||p_policy_guid) ; 

  END purge_cycle ;
  --
  -- Purge violations for the list of key values before the given timestamp
  -- This procedure makes sure that severity life cycles are not broken.
  --
  PROCEDURE purge_violations(p_target_guid          IN RAW,
                             p_policy_guid          IN RAW,
                             p_key_values           IN mgmt_medium_string_table,
                             p_collection_timestamp IN DATE)
  IS
  l_violation_guids severity_guid_array ;
  l_violations_purged NUMBER  ;
  l_row_count NUMBER := 10 ;
  l_from_date DATE ;

  CURSOR sev_cycles(p_check_keys IN NUMBER) 
  IS 
        SELECT * 
        FROM
         (
         SELECT key_value, 
                cycle_guid, 
                MAX(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_CLEAR,
                                           collection_timestamp,NULL)) 
                max_coll_timestamp,
                count(*) num_records
          FROM mgmt_violations viol
         WHERE viol.target_guid = p_target_guid 
           AND viol.policy_guid = p_policy_guid
           AND ( p_check_keys = 0 OR
                 viol.key_value  IN ( SELECT * FROM  
                                    TABLE(CAST(p_key_values AS 
                                     mgmt_medium_string_table)))
               )
           AND viol.collection_timestamp <= NVL(p_collection_timestamp,
                                               collection_timestamp) 
         GROUP by key_value,cycle_guid
         )
         WHERE max_coll_timestamp IS NOT NULL
         ORDER BY 1,3 ;
  l_key_values  mgmt_medium_string_table := mgmt_medium_string_table() ;
  l_cycle_guids mgmt_target_guid_array  := mgmt_target_guid_array() ;
  l_prev_key_value mgmt_violations.key_value%type ;
  l_check_keys NUMBER(1) := 1;
  l_record_count NUMBER ;
  l_max_times   mgmt_date_table := mgmt_date_table();
  l_max_time   DATE ;
  l_records     mgmt_integer_array := mgmt_integer_array() ;
  l_idx NUMBER ;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('purge_violations:Entry p_target_guid = ' || p_target_guid ||
                     ' p_policy_guid = '           || p_policy_guid           ||
                     ' p_collection_timestamp = '  || p_collection_timestamp, G_MODULE_NAME);
    END IF;

    IF p_key_values IS NULL or p_key_values.COUNT= 0 
    THEN
      l_check_keys := 0;
    END IF ;

    OPEN sev_cycles(l_check_keys) ;

    --disable the trigger on delete
    G_FROM_PURGE_API := TRUE ;
    LOOP
      l_key_values.DELETE ; l_cycle_guids.DELETE ;
      l_max_times.DELETE ; l_records.DELETE ;
      FETCH sev_cycles 
      BULK COLLECT INTO l_key_values,l_cycle_guids,l_max_times,l_records
      LIMIT g_fetch_batch_size ; 
      EXIT WHEN l_key_values IS NULL OR l_key_values.COUNT= 0 ;
      l_idx := l_key_values.FIRST ;

      LOOP
        l_prev_key_value := l_key_values(l_idx) ;
        l_record_count := 0 ;
        WHILE (l_idx IS NOT NULL AND 
               l_record_count <= g_purge_batch_size AND
               l_key_values(l_idx) = l_prev_key_value)
        LOOP
          l_max_time := l_max_times(l_idx) ;
          l_record_count := l_record_count + l_records(l_idx) ;
          l_prev_key_value := l_key_values(l_idx) ;
          l_idx := l_key_values.NEXT(l_idx) ;
        END LOOP ;

        purge_violations_for_key(p_target_guid=>p_target_guid,
                                 p_policy_guid=>p_policy_guid,
                                 p_key_value=>l_prev_key_value,
                                 p_end_timestamp=>l_max_time,
                                 p_start_timestamp=>NULL) ;

        EXIT WHEN l_idx IS NULL ;
      END LOOP ;
    END LOOP ;

    -- This collection gets populated in AFTER DELETE trigger of MGMT_VIOLATIONS
    -- upon TRUE of G_FROM_PURGE_API. 
    -- Hence resetting it before resetting G_FROM_PURGE_API.
    EM_SEVERITY.p_violation_guids.delete;
    EM_SEVERITY.p_violation_guids := MGMT_TARGET_GUID_ARRAY();

    -- enable the trigger
    G_FROM_PURGE_API := FALSE ;

    CLOSE sev_cycles ;


    IF EMDW_LOG.P_IS_INFO_SET 
    THEN
      EMDW_LOG.INFO('purge_violations:Exit ',G_MODULE_NAME) ;
    END IF ; 
  EXCEPTION
  WHEN OTHERS THEN
    IF EMDW_LOG.P_IS_INFO_SET 
    THEN
      EMDW_LOG.INFO('purge_violations:Exit exception '||sqlerrm,G_MODULE_NAME) ;
    END IF ; 
    raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
      substr(sqlerrm,1,100)||' When processing purging of severity for '||
      ' target_guid='||p_target_guid||' policy_guid='||p_policy_guid) ; 
  END purge_violations ;

  PROCEDURE clear_open_alerts(p_target_guid IN RAW,
                              p_policy_guid IN RAW,
                              p_key_values  IN mgmt_medium_string_table,
                              p_is_metric   IN BOOLEAN := FALSE,
                              p_clear_message IN VARCHAR2 DEFAULT NULL,
                              p_clear_nlsid   IN VARCHAR2 DEFAULT NULL)
  IS
  l_check_keys NUMBER := 1; 
  CURSOR open_alerts_cur(p_check_keys IN NUMBER)
  IS 
    -- distinct may not be needed, but just to be sure
    SELECT DISTINCT
           curr_viol.key_value,
           viol.cfg_coll_name,
           viol.cfg_key_value,
           viol.cfg_key_operator,
           viol.violation_type,
           CAST(systimestamp at time zone tgt.timezone_region AS DATE) 
                 collection_time
      FROM mgmt_targets tgt,
           mgmt_current_violation curr_viol,
           mgmt_violations viol
     WHERE tgt.target_guid           = p_target_guid 
       AND curr_viol.target_guid     = p_target_guid 
       AND curr_viol.policy_guid     = p_policy_guid
       AND (p_check_keys = 0 OR
            curr_viol.key_value IN ( SELECT * 
                                     FROM TABLE(CAST(p_key_values AS
                                             mgmt_medium_string_table))
                                    )
           )
       AND viol.target_guid          = curr_viol.target_guid
       AND viol.policy_guid          = curr_viol.policy_guid 
       AND viol.key_value            = curr_viol.key_value  
       AND viol.collection_timestamp = curr_viol.collection_timestamp 
       AND viol.violation_level      = curr_viol.violation_level ;
  BEGIN
    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO('clear_open_alerts:Entry p_target_guid = '||p_target_guid||
                    ' p_policy_guid = ' || p_policy_guid, G_MODULE_NAME);
    END IF ;

   IF p_key_values IS NULL OR p_key_values.COUNT = 0
   THEN
     l_check_keys := 0 ;
   END IF ;

   FOR open_alert IN open_alerts_cur(l_check_keys)
   LOOP
     IF EMDW_LOG.P_IS_DEBUG_SET
     THEN
       EMDW_LOG.DEBUG('clear_open_alerts:Clearing Key='||
            open_alert.key_value, G_MODULE_NAME);
     END IF ;

     MGMT_VIOLATION.log_violation(
                    p_target_guid=>p_target_guid,
                    p_policy_guid=>p_policy_guid,
                    p_key_value=>open_alert.key_value,
                    p_collection_timestamp=>open_alert.collection_time,
                    p_violation_level=>MGMT_GLOBAL.G_SEVERITY_CLEAR,
                    p_violation_type=>open_alert.violation_type,
                    p_value=>null,
                    p_string_value=>null,
                    p_cfg_coll_name=>open_alert.cfg_coll_name,
                    p_cfg_key_value=>open_alert.cfg_key_value,
                    p_cfg_key_operator=>open_alert.cfg_key_operator,
                    p_violation_ctxt_list=>NULL,
                    p_message=>NVL(p_clear_message,G_CLR_MANUAL_MSG) ,
                    p_message_nlsid=>p_clear_nlsid,
                    p_message_params=>NULL
                   ) ;
   END LOOP ;

   IF EMDW_LOG.P_IS_INFO_SET
   THEN
     EMDW_LOG.INFO('clear_open_alerts:Exit',G_MODULE_NAME) ;
   END IF ;
  END clear_open_alerts ;
  -- Procedure to get transposed keys for a transposed metric.
  -- if num keys = 1 then collection name is stored as key value 
  -- if num keys > 1 then collection name is stored as key part 1 value
  PROCEDURE get_transposed_keys
              (p_target_guid   IN RAW,
               p_cfg_coll_name IN VARCHAR2,
               p_num_keys      IN NUMBER,
               p_key_values    OUT mgmt_medium_string_table
              )
  IS
  BEGIN
    p_key_values := mgmt_medium_string_table() ;
    IF p_num_keys = 1
    THEN
      p_key_values.extend(1) ;
      p_key_values(1) := p_cfg_coll_name ;
    ELSE
      SELECT composite_key
        BULK COLLECT INTO p_key_values
        FROM mgmt_metrics_composite_keys
       WHERE target_guid     = p_target_guid
         AND key_part1_value = p_cfg_coll_name ;
    END IF ;
  END get_transposed_keys ;
  --
  -- Check if metric is transposed
  -- RETURN TRUE if transposed else FALSE
  -- if metric is transposed, returns also the matching keys
  --
  PROCEDURE get_metric_data
             (p_target_guid         IN RAW,
              p_metric_guid         IN RAW,
              p_metric_info         OUT em_metric.metric_info_rec,
              p_metric_policy_list  OUT MGMT_TARGET_GUID_ARRAY,
              p_type_list           OUT MGMT_INTEGER_ARRAY
             )
  IS
  BEGIN 
    p_metric_policy_list := mgmt_target_guid_array() ;
    p_type_list   := mgmt_integer_array() ; 

    EM_METRIC.get_metric_info_for_target(p_metric_guid,
                                         p_target_guid,
                                         p_metric_info) ;

    IF p_metric_guid = p_metric_info.metric_guid
    THEN
      SELECT policy_guid, policy_type
        BULK COLLECT INTO p_metric_policy_list, p_type_list
        FROM mgmt_policies
       WHERE metric_guid = p_metric_guid  ;
    ELSE
      p_metric_policy_list.extend(1) ;
      p_type_list.extend(1) ;
      p_metric_policy_list(1) := p_metric_guid ;
      p_type_list(1) := MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC ;
    END IF ;

  END get_metric_data ;

  PROCEDURE get_matching_violations
               (p_target_guid      IN RAW,
                p_policy_guid      IN RAW,
                p_cfg_coll_name    IN VARCHAR2,
                p_cfg_key_value    IN VARCHAR2,
                p_cfg_key_operator IN NUMBER,
                p_violating_keys   OUT mgmt_medium_string_table
               )
  IS
  l_metric_guid          mgmt_metrics.metric_guid%type ;
  l_cfg_key_parts        mgmt_medium_string_array := mgmt_medium_string_array() ;
  l_violating_key_parts  mgmt_medium_string_array := mgmt_medium_string_array() ;
  l_violating_key        mgmt_current_violation.key_value%type ;
  TYPE violating_keys_cursor   IS REF CURSOR ;
  l_violating_keys_cursor      violating_keys_cursor ;
  l_metric_info EM_METRIC.METRIC_INFO_REC ;
  BEGIN
    BEGIN
      SELECT metric_guid
        INTO l_metric_guid
        FROM mgmt_policies
       WHERE policy_guid = p_policy_guid ;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN ;
    END ;

    EM_METRIC.get_metric_info_for_target(l_metric_guid,
                                         p_target_guid,
                                         l_metric_info) ;
  
    -- If 1 key metric then key values are stored directly
    -- else key parts are in metrics composite 
    IF l_metric_info.num_keys = 1
    THEN
      -- get config keys
      l_cfg_key_parts.extend(5) ;
      l_cfg_key_parts(1) := p_cfg_key_value ;

      -- get violating keys 
      OPEN l_violating_keys_cursor FOR
      SELECT key_value,
             mgmt_medium_string_array(key_value)
        FROM mgmt_current_violation
       WHERE target_guid      = p_target_guid 
         AND policy_guid      = p_policy_guid
         AND cfg_coll_name    = p_cfg_coll_name ;
    ELSE
      -- get config keys
      BEGIN
        SELECT mgmt_medium_string_array(key_part1_value,
                                        key_part2_value,
                                        key_part3_value,
                                        key_part4_value,
                                        key_part5_value)
          INTO l_cfg_key_parts
          FROM mgmt_metrics_composite_keys
         WHERE target_guid = p_target_guid AND
               composite_key   = p_cfg_key_value ;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN ;
      END ;  
      -- get violating keys 
      OPEN l_violating_keys_cursor FOR
      SELECT curr_vio.key_value,
             mgmt_medium_string_array(key_part1_value,
                                      key_part2_value,
                                      key_part3_value,
                                      key_part4_value,
                                      key_part5_value)
        FROM mgmt_current_violation curr_vio,
             mgmt_metrics_composite_keys comp_keys
       WHERE curr_vio.target_guid      = p_target_guid 
         AND curr_vio.policy_guid      = p_policy_guid
         AND curr_vio.cfg_coll_name    = p_cfg_coll_name 
         AND comp_keys.target_guid     = p_target_guid
         AND comp_keys.composite_key   = curr_vio.key_value ;
    END IF ; 

    p_violating_keys := mgmt_medium_string_table() ;

    -- Loop through violating keys and find matching violating keys
    LOOP

      FETCH l_violating_keys_cursor INTO l_violating_key, l_violating_key_parts;

      EXIT WHEN l_violating_keys_cursor%NOTFOUND ;

      IF EM_POLICY.config_key_match(p_cfg_key     =>l_cfg_key_parts,
                                    p_result_key  =>l_violating_key_parts,
                                    p_key_operator=>p_cfg_key_operator,
                                    p_num_keys    =>l_metric_info.num_keys
                                    )
      THEN
        p_violating_keys.EXTEND(1) ;
        p_violating_keys(p_violating_keys.COUNT) := l_violating_key ;
      END IF ;

    END LOOP ;  

    CLOSE l_violating_keys_cursor ;

  END get_matching_violations ;
  --
  -- Called when a key is excepted from policy evaluation
  -- This will remove all current violations and eval details
  -- for the key
  --
  PROCEDURE clear_excepted_key
               (p_target_guid      IN RAW,
                p_policy_guid      IN RAW,
                p_cfg_coll_name    IN VARCHAR2,
                p_cfg_key_value    IN VARCHAR2,
                p_cfg_key_operator IN NUMBER
                )
  IS
  l_violating_keys  mgmt_medium_string_table := mgmt_medium_string_table() ;
  BEGIN
    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO('clear_excepted_key:Enter '||
                   ' Target='||p_target_guid||
                   ' Policy='||p_policy_guid||
                   ' Coll=:' ||p_cfg_coll_name||
                   ': Key=:' ||p_cfg_key_value||
                   ': Op=:'  ||p_cfg_key_operator,G_MODULE_NAME) ;
    END IF ;

    get_matching_violations
               (p_target_guid      => p_target_guid,
                p_policy_guid      => p_policy_guid,
                p_cfg_coll_name    => p_cfg_coll_name,
                p_cfg_key_value    => p_cfg_key_value,
                p_cfg_key_operator => p_cfg_key_operator,
                p_violating_keys   => l_violating_keys
               ) ;

    IF l_violating_keys IS NOT NULL AND l_violating_keys.COUNT >0
    THEN
      clear_open_alerts
        (
        p_target_guid  => p_target_guid,
        p_policy_guid  => p_policy_guid,
        p_is_metric    => FALSE,
        p_key_values   => l_violating_keys,
        p_clear_message=> G_CLR_KEY_EXCLUDED_MSG,
        p_clear_nlsid  => G_CLR_KEY_EXCLUDED_NLSID
      ) ;

      FORALL i in l_violating_keys.FIRST..l_violating_keys.LAST
        DELETE mgmt_policy_assoc_eval_details
        WHERE target_guid      = p_target_guid 
          AND policy_guid      = p_policy_guid
          AND result_key_value = l_violating_keys(i) ;
    END IF ;

    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO('clear_excepted_key:Exit',G_MODULE_NAME) ;
    END IF ;
     
  END clear_excepted_key ;
  --
  -- Clear alerts for a Key configuration
  -- Called when a threshold is deleted or set to null
  --
  PROCEDURE clear_alerts_for_key
              (p_object_guid      IN RAW,
               p_policy_guid      IN RAW,
               p_cfg_coll_name    IN VARCHAR2,
               p_cfg_key_value    IN VARCHAR2,
               p_cfg_key_operator IN NUMBER,
               p_clear_message    IN VARCHAR2 DEFAULT G_CLR_THRESHOLD_NULL_MSG,
               p_clear_nlsid      IN VARCHAR2 DEFAULT G_CLR_THRESHOLD_NULL_NLSID
               )
  IS
  l_object_type mgmt_policy_assoc.object_type%type ;
  l_policy_type mgmt_policy_assoc.policy_type%type ;
  l_key_values mgmt_medium_string_table := mgmt_medium_string_table() ;
  BEGIN
    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO('clear_alerts_for_key:Enter '||
                   ' Target='||p_object_guid||
                   ' Policy='||p_policy_guid||
                   ' Coll=:'||p_cfg_coll_name||
                   ': Key=:'||p_cfg_key_value||
                   ': Op=:'||p_cfg_key_operator,G_MODULE_NAME) ;
    END IF ;
    -- get metric/policy list
    BEGIN
      SELECT object_type,
             policy_type
        INTO l_object_type,
             l_policy_type
        FROM mgmt_policy_assoc
       WHERE object_guid = p_object_guid  
         AND policy_guid = p_policy_guid 
         AND coll_name   = p_cfg_coll_name ;    
    EXCEPTION      
    -- we will not clear anything
    WHEN NO_DATA_FOUND THEN  NULL ;
    END ;

    IF  l_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET  
    THEN
      IF EMDW_LOG.P_IS_DEBUG_SET
      THEN
        EMDW_LOG.DEBUG('clear_alerts_for_key:Get violations',G_MODULE_NAME);
      END IF ;

      SELECT key_value
        BULK COLLECT INTO l_key_values
        FROM mgmt_current_violation
       WHERE target_guid      = p_object_guid 
         AND policy_guid      = p_policy_guid
         AND cfg_coll_name    = p_cfg_coll_name
         AND cfg_key_value    = p_cfg_key_value
         AND cfg_key_operator = p_cfg_key_operator ;

      IF l_key_values IS NOT NULL AND l_key_values.COUNT >0
      THEN
        clear_open_alerts
          (
          p_target_guid  => p_object_guid,
          p_policy_guid  => p_policy_guid,
          p_is_metric    => (l_policy_type=MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC),
          p_key_values   => l_key_values,
          p_clear_message=> p_clear_message,
          p_clear_nlsid  => p_clear_nlsid
        ) ;
      END IF ;
      -- Set eval counters to 0
      UPDATE mgmt_policy_assoc_eval_details
        SET crit_counter = 0,
            warn_counter = 0,
            info_counter = 0
      WHERE target_guid      = p_object_guid 
        AND policy_guid      = p_policy_guid
        AND coll_name        = p_cfg_coll_name
        AND cfg_key_value    = p_cfg_key_value
        AND cfg_key_operator = p_cfg_key_operator ;

      IF EMDW_LOG.P_IS_DEBUG_SET
      THEN
        EMDW_LOG.DEBUG('clear_alerts_for_key: '||SQL%ROWCOUNT ||
            ' eval details updated ',g_module_name) ;
      END IF ;

    END IF ;

    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO('clear_alerts_for_key:Exit',G_MODULE_NAME) ;
    END IF ;

  END clear_alerts_for_key ;
  --
  -- Clear outstanding alerts and errors
  -- Called when a collection is disabled
  -- or if a target to metric/policy association is removed
  --
  PROCEDURE clear_alerts
              (p_target_guid      IN RAW,
               p_policy_guid      IN RAW,
               p_cfg_coll_name    IN VARCHAR2,
               p_clear_message    IN VARCHAR2 DEFAULT NULL,
               p_clear_nlsid      IN VARCHAR2 DEFAULT NULL,
               p_is_metric        IN BOOLEAN  DEFAULT FALSE
              )
  IS
  l_key_values mgmt_medium_string_table := mgmt_medium_string_table() ;
  l_rows_evaluated mgmt_policy_assoc_eval_summ.rows_evaluated%type ;
  l_clear_violation BOOLEAN := TRUE ;
  l_detail_count NUMBER := 0 ;
  l_metric_policy_list mgmt_target_guid_array := mgmt_target_guid_array() ;
  l_type_list          mgmt_integer_array := mgmt_integer_array() ;
  l_metric_found BOOLEAN := FALSE ;
  l_metric_info  em_metric.metric_info_rec ;
  BEGIN
    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO('clear_alerts:Enter '||
                     ' Target='||p_target_guid||
                     ' Policy='||p_policy_guid||
                     ' Coll=:'||p_cfg_coll_name||
                     ': Clear Message='||p_clear_message,G_MODULE_NAME) ;
    END IF ;
    --
    -- get metric/policy list
    --
    IF p_is_metric
    THEN
      get_metric_data
        (p_target_guid       =>p_target_guid,
         p_metric_guid       =>p_policy_guid,
         p_metric_info       =>l_metric_info,
         p_metric_policy_list=>l_metric_policy_list,
         p_type_list         =>l_type_list
        ) ;
    ELSE
      l_metric_policy_list.extend(1) ;
      l_type_list.extend(1) ;
      l_metric_policy_list(1) := p_policy_guid ;
      l_type_list(1)          := MGMT_GLOBAL.G_TYPE_POLICY ;
    END IF ;
  
    IF l_metric_policy_list IS NULL OR
       l_metric_policy_list.COUNT < 1
    THEN
      IF EMDW_LOG.P_IS_INFO_SET
      THEN
        EMDW_LOG.INFO('clear_alerts:Exit No metric' ,G_MODULE_NAME);
      END IF ;
      RETURN ;
    END IF ;
    --
    -- get key list
    -- if metric has multiple collections then get keys from mgmt_metrics_composite keys
    --
    IF p_is_metric AND l_metric_info.keys_from_mult_colls = MGMT_GLOBAL.G_TRUE
    THEN
      get_transposed_keys
        (p_target_guid     => p_target_guid,
         p_cfg_coll_name   => p_cfg_coll_name,
         p_num_keys        => l_metric_info.num_keys,
         p_key_values      => l_key_values
        ) ;
      l_clear_violation := l_key_values IS NOT NULL AND
                           l_key_values.COUNT > 0 ;
    END IF ;
    --
    -- Clear all existing violations for the given metric/policy
    --
    IF l_clear_violation 
    THEN
      IF EMDW_LOG.P_IS_DEBUG_SET
      THEN
        EMDW_LOG.DEBUG('clear_alerts:Clearing alerts', G_MODULE_NAME);
      END IF ; 
      FOR i IN l_metric_policy_list.FIRST..l_metric_policy_list.LAST
      LOOP
        clear_open_alerts(
         p_target_guid  => p_target_guid,
         p_policy_guid  => l_metric_policy_list(i),
         p_is_metric    => (l_type_list(i)=MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC),
         p_key_values   => l_key_values,
         p_clear_message=> p_clear_message,
         p_clear_nlsid  => p_clear_nlsid)  ;
      END LOOP ;
    END IF ;
    --
    -- Remove evaluation details
    --
    -- policy and key value is null
    IF EMDW_LOG.P_IS_DEBUG_SET
    THEN 
      EMDW_LOG.DEBUG('clear_alerts:removing eval summary/details '
         ,G_MODULE_NAME) ;
    END IF ;
  
    FOR i IN l_metric_policy_list.FIRST..l_metric_policy_list.LAST
    LOOP
      -- clear if policy
      IF l_type_list(i) = MGMT_GLOBAL.G_TYPE_POLICY
      THEN
        EM_POLICY.remove_policy_eval_details(
          p_target_guid     => p_target_guid,
          p_policy_guid     => l_metric_policy_list(i),
          p_coll_name       => p_cfg_coll_name) ;
     
        EM_POLICY.remove_policy_eval_summ(
          p_target_guid     => p_target_guid,
          p_policy_guid     => l_metric_policy_list(i),
          p_coll_name       => p_cfg_coll_name) ;
      END IF ;
    END LOOP ;
  
    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO('clear_alerts:Exit ' ,G_MODULE_NAME) ;
    END IF ;

  END clear_alerts ;
  --
  -- PURPOSE 
  --   Updates the current availability
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  --
  PROCEDURE update_current_availability(p_target_guid         IN RAW,
                                        p_current_status      IN NUMBER,
                                        p_start_collection_ts IN DATE,
                                        p_severity_guid       IN RAW DEFAULT NULL) IS
   l_target_type        MGMT_TARGETS.target_type%TYPE;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('update_current_availability:Entry p_target_guid = ' || p_target_guid ||
                     ' p_current_status = '      || p_current_status      ||
                     ' p_start_collection_ts = ' || p_start_collection_ts ||    
                     ' p_severity_guid = '       || p_severity_guid, G_MODULE_NAME);
    END IF;

    IF (EMDW_LOG.p_is_debug_set) THEN
      EMDW_LOG.debug('Updating current availability...', G_MODULE_NAME);
    END IF;
 
    UPDATE MGMT_CURRENT_AVAILABILITY
       SET start_collection_timestamp = p_start_collection_ts,
           current_status = p_current_status,
           severity_guid = p_severity_guid
     WHERE target_guid = p_target_guid;

    --
    -- Update master-agent if needed for multi-agent targets.
    -- If we are changing availability status of an agent,
    -- then this could potentially affect master-agent role of
    -- one or multi-agent targets
    SELECT target_type
     INTO l_target_type
     FROM mgmt_targets
    WHERE target_guid = p_target_guid;
 
    IF (l_target_type = mgmt_global.G_AGENT_TARGET_TYPE) THEN
      em_master_agent.process_agent_avail_change(p_target_guid,
                                                 p_current_status,
                                                 p_start_collection_ts);
    ELSE
      -- Check for cluster_member changes in OMS mediated mode.
      -- If this target is a member of at least cluster, then
      -- recalculate the master-agent for the cluster targets.
      BEGIN
        SELECT tgt.target_type INTO l_target_type
          FROM mgmt_target_assocs tm, mgmt_targets tgt,
               mgmt_type_properties p
         WHERE tm.assoc_target_guid = p_target_guid
           AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
           AND p.target_type = tgt.target_type
           AND p.property_name = MGMT_GLOBAL.G_IS_CLUSTER_PROP 
           AND tm.source_target_guid = tgt.target_guid
           AND tgt.monitoring_mode = mgmt_global.G_MON_MODE_OMS_MEDIATED
           AND ROWNUM < 2;

        em_master_agent.process_cluster_inst_changes(p_target_guid,
                                                     p_current_status,
                                                     p_start_collection_ts);

      EXCEPTION
        -- Ignore the non-cluster member cases
        WHEN NO_DATA_FOUND THEN
           NULL;
      END;
    END IF;

    -- Initialize the repository side collections if needed as a 
    -- result of this availability change
    em_rep_metric.init_collection_timestamp(p_target_guid);
    
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('update_current_availability:Exit', G_MODULE_NAME);
    END IF;
  END update_current_availability;

  --
  -- PURPOSE 
  --   Sets the group's current availability
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --
  PROCEDURE update_group_availability(p_target_guid   IN RAW) IS

    CURSOR avail_details_cursor (c_target_guid RAW) IS
      SELECT SUM(DECODE(ca.current_status, MGMT_GLOBAL.G_STATUS_UP, 0, 1)) targets_not_up,
             COUNT(*) total_targets
        FROM MGMT_TARGET_ASSOCS tm, MGMT_CURRENT_AVAILABILITY ca, mgmt_targets ct
       WHERE tm.source_target_guid = c_target_guid
         AND ca.target_guid = tm.assoc_target_guid
         AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
         AND ct.target_guid = tm.source_target_guid
         AND EXISTS (SELECT 1 
                       FROM MGMT_TYPE_PROPERTIES tp 
                      WHERE tp.target_type = ct.target_type 
                        AND tp.property_name = MGMT_GLOBAL.G_DERIVED_STATUS_PROP)
         AND EXISTS (SELECT 1 
                       FROM MGMT_TYPE_PROPERTIES tp 
                      WHERE tp.target_type = ct.target_type 
                        AND tp.property_name = MGMT_GLOBAL.G_IS_GROUP_PROP);

    l_targets_not_up NUMBER := NULL;
    l_total_targets  NUMBER := 0;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('update_group_availability:Entry p_target_guid = ' ||
                      p_target_guid, G_MODULE_NAME);
    END IF;

    OPEN avail_details_cursor(p_target_guid);
    FETCH avail_details_cursor INTO l_targets_not_up, l_total_targets;
    CLOSE avail_details_cursor;

    IF (l_total_targets IS NULL) OR (l_total_targets = 0) THEN
      update_current_availability(p_target_guid, 
                                  MGMT_GLOBAL.G_STATUS_UNKNOWN,
                                  SYSDATE);
    ELSIF l_targets_not_up = 0 THEN
      -- There are no down member targets.
      update_current_availability(p_target_guid, 
                                  MGMT_GLOBAL.G_STATUS_UP,
                                  SYSDATE);
    ELSE
      update_current_availability(p_target_guid, 
                                  MGMT_GLOBAL.G_STATUS_DOWN,
                                  SYSDATE);
    END IF; 

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('update_group_availability:Exit', G_MODULE_NAME);
    END IF;
  END update_group_availability;


  --
  -- PURPOSE 
  --   Updates the availability marker for a given target guid
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  -- 
  --   The availability marker is only updated if the new marker timestamp 
  --   is later than the existing marker timestamp
  --
  PROCEDURE update_availability_marker(p_target_guid   IN RAW,
                                       p_marker_ts     IN DATE,
                                       p_marker_status IN NUMBER  DEFAULT NULL,
                                       p_log_error     IN BOOLEAN DEFAULT TRUE)
  IS
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('update_availability_marker:Entry p_target_guid = ' || p_target_guid ||
                     ' p_marker_ts = '   || p_marker_ts     ||
                     ' p_marker_status ' || p_marker_status, G_MODULE_NAME);
    END IF;

    -- Validate inputs..
    IF ( (p_target_guid IS NULL) OR 
         (p_marker_ts IS NULL) ) THEN

      RAISE_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
        MGMT_GLOBAL.INTERNAL_SEVERITY_ERR_M ||
        'Invalid inputs to update availability marker : ' ||
        ' (target_guid = ' || p_target_guid || ')' ||
        ' (marker_timestamp = ' || p_marker_ts || ')' ||
        ' (marker_status = ' || get_avail_string(p_marker_status) || ')' );

    END IF;

    IF (p_marker_status IS NULL) THEN

      -- Extend the marker for the same status.
      UPDATE MGMT_AVAILABILITY_MARKER
         SET marker_timestamp = p_marker_ts
       WHERE target_guid = p_target_guid
         AND marker_timestamp <= p_marker_ts; 

    ELSE

      -- Extend the marker timestamp and status.
      UPDATE MGMT_AVAILABILITY_MARKER
         SET marker_timestamp = p_marker_ts,
             marker_avail_status = p_marker_status
       WHERE target_guid = p_target_guid
         AND marker_timestamp <= p_marker_ts; 

    END IF;

    -- If update failed, log the error.
    IF SQL%NOTFOUND THEN

      IF (p_log_error = TRUE) THEN

        LOG_ERROR(p_target_guid, 4,
          'Unable to update availability marker for : ' ||
          ' (target_guid = ' || p_target_guid || ')' ||
          ' (marker_timestamp = ' || p_marker_ts || ')' ||
          ' (marker_status = ' || get_avail_string(p_marker_status) || ')' );

      END IF;
         
    END IF;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('update_availability_marker:Exit', G_MODULE_NAME);
    END IF;
  END update_availability_marker;

  --
  -- PURPOSE 
  --   Performs basic availability update.
  --   This closes the given availability record
  --   Open a new availability record with the given status.
  --   Updates the current_availability
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  --
  PROCEDURE process_simple_avail (p_availability_rowid  IN  UROWID,
                                  p_target_guid         IN  RAW,
                                  p_current_status      IN  NUMBER,
                                  p_severity_guid       IN  RAW,
                                  p_start_collection_ts IN  DATE,
                                  p_error               OUT INTEGER) IS
    l_marker_timestamp    MGMT_AVAILABILITY_MARKER.marker_timestamp%TYPE;
    l_marker_status       MGMT_AVAILABILITY_MARKER.marker_avail_status%TYPE;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('process_simple_avail:Entry' ||
                     ' p_availability_rowid = '  || p_availability_rowid ||  
                     ' p_target_guid = '         || p_target_guid        ||
                     ' p_current_status = '      || p_current_status     ||
                     ' p_severity_guid = '       || p_severity_guid      ||
                     ' p_start_collection_ts = ' || p_start_collection_ts, G_MODULE_NAME);
    END IF;

    p_error := e_no_error;

    -- Validate inputs..
    IF ( (p_availability_rowid IS NULL) OR 
         (p_target_guid IS NULL) OR
         (p_current_status IS NULL) OR
         (p_start_collection_ts IS NULL) ) THEN

      p_error := e_bad_params;

      raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
        MGMT_GLOBAL.INTERNAL_SEVERITY_ERR_M ||
        'Invalid inputs to process simple availability : ' ||
        ' (target_guid = ' || p_target_guid || ')' ||
        ' (collection_ts = ' || p_start_collection_ts || ')' ||
        ' (current_status = ' || get_avail_string(p_current_status) || ')' );

    END IF;

    -- 
    -- Make sure that the availability record has non-negative duration
    UPDATE MGMT_AVAILABILITY
       SET end_collection_timestamp = p_start_collection_ts
     WHERE ROWID = p_availability_rowid
       AND start_collection_timestamp <= p_start_collection_ts;

    -- If update failed, log the error.
    IF SQL%NOTFOUND THEN

      raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
          MGMT_GLOBAL.INTERNAL_SEVERITY_ERR_M ||
        ' Closing availability record with negative duration for : ' ||
        ' (target_guid = ' || p_target_guid || ')' ||
        ' (collection_ts = ' || p_start_collection_ts || ')' ||
        ' (current_status = ' || get_avail_string(p_current_status) || ')' );

    END IF;

    -- Open an availability record with ERROR status
    INSERT INTO MGMT_AVAILABILITY
      (target_guid, current_status, severity_guid,
       start_collection_timestamp, end_collection_timestamp)
     VALUES
      (p_target_guid, p_current_status, p_severity_guid, 
       p_start_collection_ts, NULL);

    -- Update current avaialbility..
    update_current_availability(p_target_guid,
                                p_current_status,
                                p_start_collection_ts,
                                p_severity_guid);
  
    -- If the target is a beacon, recompute the beacon availability of 
    -- all tests for which it is an availability beacon.
    IF ( MGMT_GENSVC_AVAIL.IS_BEACON(p_target_guid) ) THEN
      MGMT_GENSVC_AVAIL.BEACON_AVAIL_EVENT(p_target_guid);
    END IF;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('process_simple_avail:Exit = ' || p_error, G_MODULE_NAME);
    END IF;
  END process_simple_avail;

 PROCEDURE split_avail_on_unreachable(p_target_guid IN RAW, 
                                      p_marker_timestamp IN DATE, 
                                      p_curr_avail_start_ts IN DATE,
                                      p_severity_guid IN RAW)
 AS
    l_avail_cursor        AVAIL_CURSOR;
    l_rowids              t_urowid_list;
    l_avail_statuses      t_number_list;
    l_start_times         t_date_list;
    l_end_times           t_date_list;
    l_ctr                 INTEGER;
    l_message             VARCHAR2(3000);

 BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('split_avail_on_unreachable:Entry' ||
                     ' p_target_guid = '     || p_target_guid   ||
                     ' p_marker_timestamp = '   || p_marker_timestamp ||  
                     ' p_curr_avail_start_ts = '|| p_curr_avail_start_ts ||
                     ' p_severity_guid = ' || p_severity_guid
                     , G_MODULE_NAME);
    END IF;
    -- Get all availability records that will get affected.
    -- Get one record before the l_marker_ts, as that may also be affected.
    l_avail_cursor := get_avail_cursor_between(p_target_guid, 
                                               p_marker_timestamp, 
                                               p_curr_avail_start_ts);

    FETCH l_avail_cursor BULK COLLECT 
      INTO l_rowids, l_avail_statuses, l_start_times, l_end_times;

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

    -- Print the avail records...
    IF (EMDW_LOG.p_is_debug_set) THEN
      FOR i IN l_rowids.FIRST..l_rowids.LAST LOOP

        l_message := '  ctr = ' || i || 
                   ' status = ' || get_avail_string(l_avail_statuses(i)) ||
                   ' start_time = ' || to_char(l_start_times(i), 'YYYY-MM-DD HH24:MI:SS') ;
        IF (l_end_times(i) IS NULL) THEN
          l_message := l_message || ' end_time = NULL';
        ELSE
          l_message := l_message || ' end_time = ' || to_char(l_end_times(i), 'YYYY-MM-DD HH24:MI:SS') ;
        END IF;

        EMDW_LOG.debug(' ' || l_message, G_MODULE_NAME);
      END LOOP;
    END IF; 
    
    -- split the first record contains marker, preserve blackout/unreachable,
    -- change the last open record to unreachable
 
    -- Iterate through the records and affect the availability..
    l_ctr := 1;
    WHILE (l_ctr <= l_rowids.LAST) LOOP

      IF (EMDW_LOG.p_is_debug_set) THEN
        EMDW_LOG.debug('extend_AM : Looping ctr = ' || l_ctr || 
         ' total = ' || l_rowids.LAST ||
         ' start_time = ' || to_char(l_start_times(l_ctr), 'YYYY-MM-DD HH24:MI:SS')
         , G_MODULE_NAME);
      END IF;

      IF l_start_times(l_ctr) <= p_marker_timestamp 
         AND l_end_times(l_ctr) > p_marker_timestamp 
      THEN
        IF (EMDW_LOG.p_is_debug_set) THEN
          EMDW_LOG.debug('Split records ', G_MODULE_NAME);
        END IF;

        UPDATE mgmt_availability
           SET end_collection_timestamp = p_marker_timestamp
         WHERE rowid = l_rowids(l_ctr);

        INSERT INTO MGMT_AVAILABILITY
                (target_guid, current_status, start_collection_timestamp,
                 end_collection_timestamp)
        VALUES
                (p_target_guid, mgmt_global.G_STATUS_UNREACHABLE, p_marker_timestamp,
                 l_end_times(l_ctr));

      ELSIF l_start_times(l_ctr) = p_curr_avail_start_ts 
            AND l_end_times(l_ctr) IS NULL 
            AND l_avail_statuses(l_ctr) = mgmt_global.G_STATUS_UNKNOWN THEN

        IF (EMDW_LOG.p_is_debug_set) THEN
              EMDW_LOG.debug('Change last open record to unreachable ', G_MODULE_NAME);
        END IF;

        UPDATE mgmt_availability
           SET current_status = mgmt_global.G_STATUS_UNREACHABLE
         WHERE rowid = l_rowids(l_ctr);

        -- Update current avaialbility..
        update_current_availability(p_target_guid,
                                    mgmt_global.G_STATUS_UNREACHABLE,
                                    p_curr_avail_start_ts,
                                    p_severity_guid);

      ELSE
        IF (EMDW_LOG.p_is_debug_set) THEN
           EMDW_LOG.debug(' Do nothing.', G_MODULE_NAME);
        END IF;
      END IF;
      l_ctr := l_ctr + 1;
   END LOOP;



   IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('split_avail_on_unreachable:Exit'
                     , G_MODULE_NAME);
   END IF;
 
 END split_avail_on_unreachable;

 PROCEDURE process_unreachable_start(p_target_guid IN RAW,
                                      p_curr_avail_start_ts IN DATE,
                                      p_curr_avail_rowid IN UROWID,
                                      p_violation_guid IN RAW,
                                      p_error OUT VARCHAR2)
 AS
   l_need_split NUMBER := 0;
   l_marker_ts DATE;
 BEGIN
   IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('process_unreachable_start:Enter ' ||
                     ' target ' || p_target_guid ||
                     ' curr avail start ' || to_char(p_curr_avail_start_ts,
                                               'YYYY-MM-DD HH24:MI:SS') ||
                     ' violation guid ' || p_violation_guid
                     , G_MODULE_NAME);
   END IF;

   -- update existing availrec with end_ts = UNRCH_START_TS
   -- add new availrec with status = UNRCH and start_ts = UNRCH_START_TS
   -- update current_avail with status = URNCH and start=UNRCH_START_TS
   -- Update avail_marker

   --   (bug 4559108)
           
   SELECT marker_timestamp INTO l_marker_ts 
     FROM MGMT_AVAILABILITY_MARKER
    WHERE target_guid = p_target_guid;
          
   -- check if marker is inside an avail record
   IF l_marker_ts >= p_curr_avail_start_ts THEN
      l_need_split := 0;
   ELSE
      l_need_split := 1;
   END IF;
     
   IF EMDW_LOG.p_is_debug_set THEN
      EMDW_LOG.debug('Check if avail records need split. Marker is ' ||
                    to_char(l_marker_ts, 'YYYY-MM-DD HH24:MI:SS')
                    || ' current record start time '
                    || to_char(p_curr_avail_start_ts, 'YYYY-MM-DD HH24:MI:SS')
                    || ' need split flag ' || l_need_split, 
                     EM_SEVERITY.MODULE);
   END IF;
         
   IF l_need_split = 0 THEN
      process_simple_avail(p_curr_avail_rowid,
                           p_target_guid,
                           MGMT_GLOBAL.G_STATUS_UNREACHABLE,
                           p_violation_guid,
                           l_marker_ts,
                           p_error);
   ELSE
     split_avail_on_unreachable(p_target_guid, 
                                l_marker_ts, 
                                p_curr_avail_start_ts,
                                p_violation_guid);
   END IF;

          -- Do not affect availability marker..
   IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('process_unreachable_start:Exit'
                     , G_MODULE_NAME);
   END IF;

 END process_unreachable_start;

  --
  -- PURPOSE 
  --   Extends the availability marker to the new timestamp
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  --
  PROCEDURE extend_avail_marker(p_target_guid      IN  RAW,
                                p_new_marker_ts    IN  DATE,
                                p_new_avail_status IN  NUMBER,
                                p_error            OUT INTEGER) IS

    CURSOR get_marker_details(c_target_guid RAW) IS
      SELECT ROWID, marker_timestamp, marker_avail_status 
        FROM MGMT_AVAILABILITY_MARKER
       WHERE target_guid = c_target_guid;

    CURSOR get_marker_ts(c_target_guid RAW, c_marker_ts DATE) IS
      SELECT MIN(start_collection_timestamp)
        FROM MGMT_AVAILABILITY
       WHERE target_guid = c_target_guid
         AND start_collection_timestamp <= c_marker_ts
	 AND NVL(end_collection_timestamp, DATE_2100_01_01) >= c_marker_ts
         AND current_status = MGMT_GLOBAL.G_STATUS_UNREACHABLE;

    l_marker_rowid        UROWID;
    l_marker_ts           MGMT_AVAILABILITY_MARKER.marker_timestamp%TYPE := NULL;
    l_marker_avail_status MGMT_AVAILABILITY_MARKER.marker_avail_status%TYPE;
    l_unrch_marker_ts     MGMT_AVAILABILITY_MARKER.marker_timestamp%TYPE := NULL;
    l_avail_cursor        AVAIL_CURSOR;
    l_rowids              t_urowid_list;
    l_avail_statuses      t_number_list;
    l_start_times         t_date_list;
    l_end_times           t_date_list;
    l_end_time            MGMT_AVAILABILITY.end_collection_timestamp%TYPE;
    l_ctr                 INTEGER;
    l_message             VARCHAR2(3000);

  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('extend_avail_marker:Entry' ||
                     ' p_target_guid = '     || p_target_guid   ||
                     ' p_new_marker_ts = '   || p_new_marker_ts ||  
                     ' p_new_avail_status = '|| p_new_avail_status, G_MODULE_NAME);
    END IF;


    -- Validate inputs..
    IF ( (p_target_guid IS NULL) OR 
         (p_new_marker_ts IS NULL) ) THEN

      p_error := e_bad_params;

      raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
        MGMT_GLOBAL.INTERNAL_SEVERITY_ERR_M ||
        'Invalid inputs to extend availability marker for : ' ||
        ' (target_guid = ' || p_target_guid || ')' ||
        ' (new_marker_ts = ' || p_new_marker_ts || ')' ||
        ' (new_marker_status = ' || get_avail_string(p_new_avail_status) || ')' );

    END IF;

    IF (EMDW_LOG.p_is_debug_set) THEN
      EMDW_LOG.debug('extend_am : Entered. tguid = ' || p_target_guid ||
         ' new marker ts = ' || p_new_marker_ts || 
         ' new avail stat = ' || get_avail_string(p_new_avail_status), G_MODULE_NAME);
    END IF;

    -- Get marker details..
    OPEN get_marker_details(p_target_guid);
    FETCH get_marker_details INTO l_marker_rowid, 
                                  l_marker_ts, 
                                  l_marker_avail_status;
    IF (get_marker_details%ISOPEN) THEN
      CLOSE get_marker_details;
    END IF;

    -- Get the start of the unreachable record
    OPEN get_marker_ts(p_target_guid, l_marker_ts);
    FETCH get_marker_ts INTO l_unrch_marker_ts;
    IF (get_marker_ts%ISOPEN) THEN
      CLOSE get_marker_ts;
    END IF;

    IF (l_unrch_marker_ts IS NOT NULL) THEN
      l_marker_ts := l_unrch_marker_ts;
    END IF;

    IF (l_marker_ts IS NULL) THEN

      p_error := e_no_avail_marker;

      raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
        MGMT_GLOBAL.INTERNAL_SEVERITY_ERR_M ||
        'No availability marker found while extending availability marker for : ' ||
        ' (target_guid = ' || p_target_guid || ')' ||
        ' (new_marker_ts = ' || p_new_marker_ts || ')' ||
        ' (new_marker_status = ' || get_avail_string(p_new_avail_status) || ')' );

    END IF;

    IF (EMDW_LOG.p_is_debug_set) THEN
      EMDW_LOG.debug('extend_am : Got marker details ts = ' ||
         to_char(l_marker_ts, 'DD-MM-YYYY HH24:MI:SS') || ' status = ' ||
         get_avail_string(l_marker_avail_status), G_MODULE_NAME);
    END IF;

    -- 
    -- Check if the new marker is after the old marker.
    -- 
    IF (l_marker_ts > p_new_marker_ts) THEN

      raise_application_error(MGMT_GLOBAL.SEVERITY_OUT_ORDER_ERR,
          MGMT_GLOBAL.SEVERITY_OUT_ORDER_ERR_M ||
          ' Severity received out of time sequence. ' ||
          '(target_guid = ' || p_target_guid || ')' ||
          '(marker_ts = ' || l_marker_ts || ')' ||
          '(new collection_ts = ' || p_new_marker_ts || ')' ||
          '(marker_status =' || EM_SEVERITY.get_avail_string(p_new_avail_status) || ')' );

    END IF;


    -- Get all availability records that will get affected.
    -- Get one record before the l_marker_ts, as that may also be affected.
    l_avail_cursor := get_avail_cursor_between(p_target_guid, 
                                               l_marker_ts, 
                                               p_new_marker_ts);

    FETCH l_avail_cursor BULK COLLECT 
      INTO l_rowids, l_avail_statuses, l_start_times, l_end_times;

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

    -- Print the avail records...
    if (EMDW_LOG.p_is_debug_set) THEN
      FOR i IN l_rowids.FIRST..l_rowids.LAST LOOP

        l_message := '  ctr = ' || i || 
                   ' status = ' || get_avail_string(l_avail_statuses(i)) ||
                   ' start_time = ' || l_start_times(i) ;
        IF (l_end_times(i) IS NULL) THEN
          l_message := l_message || ' end_time = NULL';
        ELSE
          l_message := l_message || ' end_time = ' || l_end_times(i) ;
        END IF;

        EMDW_LOG.debug(' ' || l_message, G_MODULE_NAME);
      END LOOP;
    END IF; 

    -- Iterate through the records and affect the availability..
    -- case 1: If avail_rec contains both old_marker and new_marker
    --   avail_rec.start_time <= old_marker <= new_marker <= avail_rec.end_time

    -- case 2: If avail_rec contains only old_marker
    --   avail_rec.start_time <= old_marker <= avail_rec.end_time <= new_marker

    -- case 3: If avail_rec contains only new_marker
    --   old_marker < avail_rec.start_time <= new_marker <= avail_rec.end_time

    -- case 4: If avail_rec is within the old_marker and new_marker
    --   old_marker < avail_rec.start_time <= avail_rec.end_time < new_marker

    -- case 5: If avail_rec is before old_marker
    --   avail_rec.end_time < old_marker (Not <= )

    -- case 6: If avail_rec is after new marker
    --   avail_rec.start_time > new_marker 

    -- case 7: None of the above


    -- Iterate through the records and affect the availability..
    l_ctr := 1;
    WHILE (l_ctr <= l_rowids.LAST) LOOP

      IF (EMDW_LOG.p_is_debug_set) THEN
        EMDW_LOG.debug('extend_AM : Looping ctr = ' || l_ctr || 
         ' total = ' || l_rowids.LAST ||
         ' start_time = ' || l_start_times(l_ctr) ||
         ' marker_time = ' || l_marker_ts, G_MODULE_NAME);
      END IF;

      -- Treat NULL as a large date 
      l_end_time := NVL(l_end_times(l_ctr), DATE_2100_01_01); 

      -- If BLACKOUT record is seen, make the marker status as UNKNOWN
      IF (l_avail_statuses(l_ctr) = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN

        -- For host and agent, the marker status has to be UP
        l_marker_avail_status := MGMT_GLOBAL.G_STATUS_UNKNOWN;

      END IF;

      IF (l_start_times(l_ctr) <= l_marker_ts) THEN
        -- Cases 1, 2, 5 can be handled here

        IF (l_end_time >= p_new_marker_ts) THEN
	  -- Case 1.
          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('  Case 1: avail_rec contains both old_marker and new_marker..', G_MODULE_NAME);
          END IF;

	  IF (l_avail_statuses(l_ctr) = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN

            IF (EMDW_LOG.p_is_debug_set) THEN
              EMDW_LOG.debug('    Current rec is Unrchable. Split this and prev to 3 records', G_MODULE_NAME);
            END IF;

            UPDATE MGMT_AVAILABILITY
               SET end_collection_timestamp = p_new_marker_ts
             WHERE ROWID = l_rowids(l_ctr-1);
            
	    -- Insert a zero length record to signify the change of avail state
            INSERT INTO MGMT_AVAILABILITY
                (target_guid, current_status, start_collection_timestamp,
                 end_collection_timestamp)
             VALUES
                (p_target_guid, p_new_avail_status, p_new_marker_ts,
                 p_new_marker_ts);

            UPDATE MGMT_AVAILABILITY
               SET start_collection_timestamp = p_new_marker_ts
             WHERE ROWID = l_rowids(l_ctr);

          ELSIF (l_avail_statuses(l_ctr) <> MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN

	    IF ( p_new_marker_ts < l_end_time ) THEN

	      -- Sometimes, when two events happen at the same time like agentup and target up,
	      --   both the new marker and the old marker would be same.
	      --   In such cases (see testcase tvmrd3c1), there is no need to split this record

              IF (EMDW_LOG.p_is_debug_set) THEN
                EMDW_LOG.debug('    Current rec is not BLACKOUT and the record can be split.' ||
		                     ' Close this and open a new one.', G_MODULE_NAME);
              END IF;

              -- Like processing simple availability
              UPDATE MGMT_AVAILABILITY
                 SET end_collection_timestamp = p_new_marker_ts
               WHERE ROWID = l_rowids(l_ctr);

              INSERT INTO MGMT_AVAILABILITY
                (target_guid, current_status, start_collection_timestamp,
                 end_collection_timestamp)
               VALUES
                (p_target_guid, p_new_avail_status, p_new_marker_ts,
                 l_end_times(l_ctr));
            ELSE
              IF (EMDW_LOG.p_is_debug_set) THEN
                EMDW_LOG.debug('    Current rec is not BLACKOUT, but the record cannot be split.' ||
		                     ' Do Nothing..', G_MODULE_NAME);
              END IF;
            END IF;

          ELSE
            IF (EMDW_LOG.p_is_debug_set) THEN
              EMDW_LOG.debug('    Current rec is BLACKOUT. Do Nothing..', G_MODULE_NAME);
            END IF;

	  END IF;

	ELSIF (l_end_time > l_marker_ts) THEN
	  -- Case 2. Same as case 1
          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('  Case 2: avail_rec contains only old_marker ..', G_MODULE_NAME);
          END IF;

	  IF (l_avail_statuses(l_ctr) = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN

            IF (EMDW_LOG.p_is_debug_set) THEN
              EMDW_LOG.debug('    Current rec is Unreachable. Extend prev one and delete the current one.', G_MODULE_NAME);
            END IF;
            -- The new marker lies beyond the next record.
            UPDATE MGMT_AVAILABILITY
               SET end_collection_timestamp = l_end_times(l_ctr)
             WHERE ROWID = l_rowids(l_ctr-1);

            DELETE FROM MGMT_AVAILABILITY
             WHERE ROWID = l_rowids(l_ctr);

          ELSIF ( (l_avail_statuses(l_ctr) = MGMT_GLOBAL.G_STATUS_UNKNOWN) AND
	          (l_ctr > 1) AND
                  (l_avail_statuses(l_ctr-1) = MGMT_GLOBAL.G_STATUS_BLACKOUT) ) THEN

            IF (EMDW_LOG.p_is_debug_set) THEN
              EMDW_LOG.debug('    Current rec is UNK, prev BLK. Do nothing..', G_MODULE_NAME);
            END IF;
	    -- No need to do any special processing
            NULL;

          ELSE

            IF (EMDW_LOG.p_is_debug_set) THEN
              EMDW_LOG.debug('    Else of case 2. Do nothing..', G_MODULE_NAME);
            END IF;

          END IF;

	ELSE
	  -- Case 5.
          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('  Case 5: avail_rec is before old_marker..', G_MODULE_NAME);
            EMDW_LOG.debug('    Do nothing. Skip this and go to the next one', G_MODULE_NAME);
          END IF;

	  -- skip this and go to the next

	END IF;

      ELSE
        -- Cases 3, 4, 6 are handled here
        IF (l_end_time >= p_new_marker_ts) THEN
	  -- Case 3.
          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('  Case 3: avail_rec contains only new_marker...', G_MODULE_NAME);
          END IF;

	  IF (l_avail_statuses(l_ctr) = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN

            IF (EMDW_LOG.p_is_debug_set) THEN
              EMDW_LOG.debug('    Current rec is Unrch. Split it into three.', G_MODULE_NAME);
            END IF;

            -- Split the record into three availability records..
            INSERT INTO MGMT_AVAILABILITY
              (target_guid, current_status, start_collection_timestamp,
               end_collection_timestamp)
             VALUES
              (p_target_guid, l_marker_avail_status, l_start_times(l_ctr),
               p_new_marker_ts);

            IF (l_marker_avail_status <> p_new_avail_status) THEN

              INSERT INTO MGMT_AVAILABILITY
                (target_guid, current_status, start_collection_timestamp,
                 end_collection_timestamp)
               VALUES
                (p_target_guid, p_new_avail_status, p_new_marker_ts,
                 p_new_marker_ts);

            END IF;

            UPDATE MGMT_AVAILABILITY
               SET start_collection_timestamp = p_new_marker_ts
             WHERE ROWID = l_rowids(l_ctr);

          ELSIF ( (l_avail_statuses(l_ctr) = MGMT_GLOBAL.G_STATUS_UNKNOWN) AND
	          (l_ctr > 1) AND
                  (l_avail_statuses(l_ctr-1) = MGMT_GLOBAL.G_STATUS_BLACKOUT) ) THEN

            IF (p_new_avail_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) THEN

	      -- If the new status is already UNKWN, no need to split, we are already
	      -- in UNKNOWN state
              IF (EMDW_LOG.p_is_debug_set) THEN
                EMDW_LOG.debug('    Current rec is UNKWN, prev BLKOUT. Curr avl UNKWN, Do nothing', G_MODULE_NAME);
              END IF;

            ELSE

              IF (EMDW_LOG.p_is_debug_set) THEN
                EMDW_LOG.debug('    Current rec is UNKWN, prev BLKOUT. Close UNKWN, open new rec.', G_MODULE_NAME);
              END IF;

              -- Close the UNKNOWN record
              UPDATE MGMT_AVAILABILITY
                 SET end_collection_timestamp = p_new_marker_ts
               WHERE ROWID = l_rowids(l_ctr);

              -- Insert new record for new marker status (l_end_times(lctr) can be NULL)
              INSERT INTO MGMT_AVAILABILITY
                (target_guid, current_status, start_collection_timestamp,
                 end_collection_timestamp)
               VALUES
                (p_target_guid, p_new_avail_status, p_new_marker_ts,
                 l_end_times(l_ctr));

            END IF;

	  END IF;

	ELSIF (l_end_time > l_marker_ts) THEN
	  -- Case 4.
          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('  Case 4: avail_rec is within the old_marker and new_marker...', G_MODULE_NAME);
          END IF;

	  IF (l_avail_statuses(l_ctr) = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN

            IF (EMDW_LOG.p_is_debug_set) THEN
              EMDW_LOG.debug('    Current rec is Unrch. Update status..', G_MODULE_NAME);
            END IF;

            UPDATE MGMT_AVAILABILITY
               SET current_status = p_new_avail_status
             WHERE ROWID = l_rowids(l_ctr);

	  END IF;

	ELSE
	  -- Case 6.
          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('  Case 6: avail_rec is after new marker...', G_MODULE_NAME);
            EMDW_LOG.debug('    Skip and go to next rec.', G_MODULE_NAME);
          END IF;

	  -- Skip this and go to the next record.

	END IF;

      END IF;

      l_ctr := l_ctr + 1;

    END LOOP;


    -- Update the marker with the new timestamp and status
    update_availability_marker(p_target_guid, p_new_marker_ts, p_new_avail_status);

    IF (EMDW_LOG.p_is_debug_set) THEN
      EMDW_LOG.debug('extend_am : Exit. tguid = ' || p_target_guid, G_MODULE_NAME);
    END IF;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('extend_avail_marker:Exit', G_MODULE_NAME);
    END IF;
  END extend_avail_marker;


  --
  -- PURPOSE 
  --   Process the UNREACHABLE CLEAR for the target.
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  --
  PROCEDURE process_unreachable_clear(p_target_guid   IN     RAW,
                                      p_new_marker_ts IN     DATE,
                                      p_message       IN OUT VARCHAR2,
                                      p_error         OUT    INTEGER,
                                      p_message_nlsid IN OUT VARCHAR2,
                                      p_message_params IN OUT VARCHAR2,
                                      p_new_avail_status IN OUT NUMBER
                                     ) IS

    CURSOR get_marker_details(c_target_guid RAW) IS
      SELECT ROWID, marker_timestamp, marker_avail_status 
        FROM MGMT_AVAILABILITY_MARKER
       WHERE target_guid = c_target_guid;

    CURSOR get_marker_ts(c_target_guid RAW, c_marker_ts DATE) IS
      SELECT MIN(start_collection_timestamp)
        FROM MGMT_AVAILABILITY
       WHERE target_guid = c_target_guid
         AND start_collection_timestamp <= c_marker_ts
	 AND NVL(end_collection_timestamp, DATE_2100_01_01) >= c_marker_ts
         AND current_status = MGMT_GLOBAL.G_STATUS_UNREACHABLE;

    l_marker_rowid         UROWID;
    l_marker_ts            MGMT_AVAILABILITY_MARKER.marker_timestamp%TYPE := NULL;
    l_marker_avail_status  MGMT_AVAILABILITY_MARKER.marker_avail_status%TYPE;
    l_unrch_marker_ts      MGMT_AVAILABILITY_MARKER.marker_timestamp%TYPE := NULL;
    l_avail_cursor         AVAIL_CURSOR;
    l_rowids               t_urowid_list;
    l_avail_statuses       t_number_list;
    l_start_times          t_date_list;
    l_end_times            t_date_list;
    l_end_time             MGMT_AVAILABILITY.end_collection_timestamp%TYPE;
    l_ctr                  INTEGER;
    l_curr_coll_ts         MGMT_AVAILABILITY.start_collection_timestamp%TYPE;

    l_new_avail_status     MGMT_AVAILABILITY.current_status%TYPE;
    l_is_host              NUMBER := 0;
    l_message              VARCHAR2(3000);

  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('process_unreachable_clear:Entry' ||
                     ' p_target_guid = '     || p_target_guid   ||
                     ' p_new_marker_ts = '   || p_new_marker_ts ||  
                     ' p_message = ' || p_message, G_MODULE_NAME);   
    END IF;
    -- Validate inputs..
    IF ( (p_target_guid IS NULL) OR 
         (p_new_marker_ts IS NULL) ) THEN

      p_error := e_bad_params;
      raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
        MGMT_GLOBAL.INTERNAL_SEVERITY_ERR_M ||
        'Invalid inputs to process unreachable clear : ' ||
        ' (target_guid = ' || p_target_guid || ')' ||
        ' (marker_ts = ' || p_new_marker_ts || ')' );

    END IF;

    IF (EMDW_LOG.p_is_debug_set) THEN
      EMDW_LOG.debug('Entered : process_unreachable_clear tguid ' || p_target_guid, G_MODULE_NAME);
    END IF;

    -- Get marker details..
    OPEN get_marker_details(p_target_guid);
    FETCH get_marker_details INTO l_marker_rowid, 
                                  l_marker_ts, 
                                  l_marker_avail_status;
    IF (get_marker_details%ISOPEN) THEN
      CLOSE get_marker_details;
    END IF;

    -- Get marker details..
    OPEN get_marker_ts(p_target_guid, l_marker_ts);
    FETCH get_marker_ts INTO l_unrch_marker_ts;
    IF (get_marker_ts%ISOPEN) THEN
      CLOSE get_marker_ts;
    END IF;

    IF (l_unrch_marker_ts IS NOT NULL) THEN
      l_marker_ts := l_unrch_marker_ts;
    END IF;

    IF (l_marker_ts IS NULL) THEN

      p_error := e_no_avail_marker;

      raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
        MGMT_GLOBAL.INTERNAL_SEVERITY_ERR_M ||
        'No marker availabile while processing unreachable clear for : ' ||
        ' (target_guid = ' || p_target_guid || ')' );

    END IF;

    IF (EMDW_LOG.p_is_debug_set) THEN
      EMDW_LOG.debug('Got marker details ts = ' ||
        to_char(l_marker_ts, 'DD-MM-YYYY HH24:MI:SS') || ' status = ' ||
        l_marker_avail_status, G_MODULE_NAME);
    END IF;

    -- Get all availability records that have atlease
    -- some time after the marker..
    l_avail_cursor := get_avail_cursor_between(p_target_guid,
                                               l_marker_ts,
                                               p_new_marker_ts);

    FETCH l_avail_cursor BULK COLLECT 
      INTO l_rowids, l_avail_statuses, 
           l_start_times, l_end_times;

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

    -- Print the avail records...
    IF (EMDW_LOG.p_is_debug_set) THEN
      FOR i IN l_rowids.FIRST..l_rowids.LAST LOOP

        l_message := '  ctr = ' || i || 
                   ' status = ' || get_avail_string(l_avail_statuses(i)) ||
                   ' start_time = ' || l_start_times(i) ;
        IF (l_end_times(i) IS NULL) THEN
          l_message := l_message || ' end_time = NULL';
        ELSE
          l_message := l_message || ' end_time = ' || l_end_times(i) ;
        END IF;

        EMDW_LOG.debug(' ' || l_message, G_MODULE_NAME);
      END LOOP;
    END IF; 

    l_is_host := 0;
    SELECT COUNT(1) INTO l_is_host         
      FROM MGMT_TARGETS
     WHERE target_guid = p_target_guid
       AND target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE;

    -- Iterate through the records and affect the availability..
    -- case 1: If avail_rec contains both old_marker and new_marker
    --   avail_rec.start_time <= old_marker <= new_marker <= avail_rec.end_time

    -- case 2: If avail_rec contains only old_marker
    --   avail_rec.start_time <= old_marker <= avail_rec.end_time <= new_marker

    -- case 3: If avail_rec contains only new_marker
    --   old_marker < avail_rec.start_time <= new_marker <= avail_rec.end_time

    -- case 4: If avail_rec is within the old_marker and new_marker
    --   old_marker < avail_rec.start_time <= avail_rec.end_time < new_marker

    -- case 5: If avail_rec is before old_marker
    --   avail_rec.end_time < old_marker (Not <= )

    -- case 6: If avail_rec is after new marker
    --   avail_rec.start_time > new_marker 

    -- case 7: None of the above

    

    -- Also, update avail marker to collection ts as this procedure is called in 
    -- the context of a clean hb.
    l_ctr := 1;

    WHILE (l_ctr <= l_rowids.LAST) LOOP

      IF (EMDW_LOG.p_is_debug_set) THEN
        EMDW_LOG.debug('Looping ctr = ' || l_ctr || 
         ' total = ' || l_rowids.LAST ||
         ' start_time = ' || l_start_times(l_ctr) ||
         ' marker_time = ' || l_marker_ts, G_MODULE_NAME);
      END IF;

      l_end_time := NVL(l_end_times(l_ctr), DATE_2100_01_01); 

      IF (l_start_times(l_ctr) <= l_marker_ts) THEN
        -- Cases 1, 2, 5 are handled here

        IF (l_end_time >= p_new_marker_ts) THEN
	  -- Case 1.

	  IF (l_avail_statuses(l_ctr) = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN

	    IF (l_avail_statuses(l_ctr-1) = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN

              IF (l_is_host > 0) THEN
	        -- For host mark the status as UP.
                l_new_avail_status := MGMT_GLOBAL.G_STATUS_UP;
	      ELSE
                l_new_avail_status := MGMT_GLOBAL.G_STATUS_UNKNOWN;
              END IF;

              IF (EMDW_LOG.p_is_debug_set) THEN
                EMDW_LOG.debug('Case 1: avail_rec contains both markers..' ||
		  ' curr_status = UNRCH, prev_status = BLACKOUT, l_host = ' || l_is_host, G_MODULE_NAME);
              END IF;

	      UPDATE MGMT_AVAILABILITY
		 SET current_status = l_new_avail_status
              WHERE ROWID = l_rowids(l_ctr);

	    ELSE

              IF (EMDW_LOG.p_is_debug_set) THEN
                EMDW_LOG.debug('Case 1: avail_rec contains both markers..' ||
		  ' curr_status = UNRCH, prev_status NOT BLACKOUT', G_MODULE_NAME);
              END IF;

              -- If the prev record is not blackout, extend the end time for the record
              -- and remove the unreachable record.
              UPDATE MGMT_AVAILABILITY
                 SET end_collection_timestamp = l_end_times(l_ctr)
               WHERE ROWID = l_rowids(l_ctr-1);
            
              DELETE MGMT_AVAILABILITY
               WHERE ROWID = l_rowids(l_ctr);

	    END IF;

	  END IF;

	ELSIF (l_end_time > l_marker_ts) THEN
	  -- Case 2. Same as case 1

	  IF (l_avail_statuses(l_ctr) = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN

	    IF (l_avail_statuses(l_ctr-1) = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN

              IF (l_is_host > 0) THEN
	        -- For host mark the status as UP.
                l_new_avail_status := MGMT_GLOBAL.G_STATUS_UP;
	      ELSE
                l_new_avail_status := MGMT_GLOBAL.G_STATUS_UNKNOWN;
              END IF;

              IF (EMDW_LOG.p_is_debug_set) THEN
                EMDW_LOG.debug('Case 2: avail_rec contains only old_marker ..' ||
		  ' curr_status = UNRCH, prev_status = BLACKOUT', G_MODULE_NAME);
              END IF;

	      UPDATE MGMT_AVAILABILITY
		 SET current_status = l_new_avail_status
              WHERE ROWID = l_rowids(l_ctr);

	    ELSE

              IF (EMDW_LOG.p_is_debug_set) THEN
                EMDW_LOG.debug('Case 2: avail_rec contains only old_marker ..' ||
		  ' curr_status = UNRCH, prev_status NOT BLACKOUT', G_MODULE_NAME );
              END IF;

              -- If the prev record is not blackout, extend the end time for the record
              -- and remove the unreachable record.
              UPDATE MGMT_AVAILABILITY
                 SET end_collection_timestamp = l_end_times(l_ctr)
               WHERE ROWID = l_rowids(l_ctr-1);
            
              DELETE MGMT_AVAILABILITY
               WHERE ROWID = l_rowids(l_ctr);

	    END IF;

          END IF;

	ELSE
	  -- Case 5.
          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('Case 5: avail_rec is before old_marker..', G_MODULE_NAME);
          END IF;

	  -- skip this and go to the next

	END IF;

      ELSE
        -- Cases 3, 4, 6 are handled here
        IF (l_end_time >= p_new_marker_ts) THEN
	  -- Case 3.
          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('Case 3: avail_rec contains only new_marker...', G_MODULE_NAME);
          END IF;

	  IF (l_avail_statuses(l_ctr) = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN

            UPDATE MGMT_AVAILABILITY
               SET current_status = l_marker_avail_status
             WHERE ROWID = l_rowids(l_ctr);

	  END IF;

	ELSIF (l_end_time > l_marker_ts) THEN
	  -- Case 4.
          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('Case 4: avail_rec is within the old_marker and new_marker...', G_MODULE_NAME);
          END IF;

	  IF (l_avail_statuses(l_ctr) = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN

            UPDATE MGMT_AVAILABILITY
               SET current_status = l_marker_avail_status
             WHERE ROWID = l_rowids(l_ctr);

	  END IF;

	ELSE
	  -- Case 6.
          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('Case 6: avail_rec is after new marker...', G_MODULE_NAME);
          END IF;

	  -- Skip this and go to the next record.

	END IF;

      END IF;

      l_ctr := l_ctr + 1;

    END LOOP;

    -- Update current availability..
    SELECT current_status, start_collection_timestamp
      INTO p_new_avail_status, l_curr_coll_ts
      FROM MGMT_AVAILABILITY
     WHERE end_collection_timestamp IS NULL
       AND target_guid = p_target_guid;

    update_current_availability(p_target_guid, p_new_avail_status, 
                                l_curr_coll_ts, NULL);

    p_message := p_message || ' The current status of the target is ' || 
                 get_avail_string(p_new_avail_status) || '.';

    p_message_nlsid := EM_PING.NLSID_AGENT_UNRCH_CLEAR ;
    p_message_params :=  get_avail_string(p_new_avail_status)||';'||get_avail_string_nls(p_new_avail_status) ;

    -- Update the marker with the new timestamp and status
    -- The 4th parameter is FALSE as we dont want to log errors 
    -- even if the marker is not moved as (CLEAN) heartbeats are recorded in batches and so
    -- a heartbeat can be delayed while severities for the target with later timestamps
    -- could be recorded.
    update_availability_marker(p_target_guid, p_new_marker_ts, l_marker_avail_status, FALSE);
    
    -- If the target is a beacon, recompute the beacon availability of 
    -- all tests for which it is an availability beacon.
    IF ( MGMT_GENSVC_AVAIL.IS_BEACON(p_target_guid) ) THEN
      MGMT_GENSVC_AVAIL.BEACON_AVAIL_EVENT(p_target_guid);
    END IF;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('process_unreachable_clear:Exit p_message = ' || p_message ||
                     ' p_error = ' || p_error,  G_MODULE_NAME);   
    END IF;
  END process_unreachable_clear;


  --
  -- PURPOSE 
  --   Process the AGENT DOWN severity
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  --
  PROCEDURE process_agent_down_sev(p_target_guid   IN  RAW,
                                   p_coll_ts       IN  DATE,
                                   p_emd_url       IN  VARCHAR2, 
                                   p_curr_stat     IN  NUMBER,
                                   p_error         OUT INTEGER) IS

    l_tgt_avail_cursor       AVAIL_CURSOR;

    l_curr_tgt_rowid         UROWID;
    l_curr_tgt_avail         MGMT_AVAILABILITY.current_status%TYPE;
    l_curr_tgt_start_coll_ts MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
    l_curr_tgt_end_coll_ts   MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
    l_prev_tgt_rowid         UROWID;
    l_prev_tgt_avail         MGMT_AVAILABILITY.current_status%TYPE;
    l_prev_tgt_start_coll_ts MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
    l_prev_tgt_end_coll_ts   MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
    l_agent_unrch            BOOLEAN := FALSE;
    l_new_avail              MGMT_AVAILABILITY.current_status%TYPE;
    l_upd_marker             BOOLEAN := TRUE;

  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('process_agent_down_sev:Entry p_target_guid = ' || p_target_guid ||
                     ' p_coll_ts = '   || p_coll_ts ||
                     ' p_emd_url = '   || p_emd_url ||
                     ' p_curr_stat = ' || p_curr_stat,G_MODULE_NAME);
    END IF;

    -- Validate inputs..
    IF ( (p_target_guid IS NULL) OR 
         (p_coll_ts IS NULL) OR
         (p_emd_url IS NULL) OR 
         (p_curr_stat IS NULL) )THEN

      p_error := e_bad_params;

      raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
        MGMT_GLOBAL.INTERNAL_SEVERITY_ERR_M ||
        'Invalid inputs to process agent down severity : ' ||
        ' (target_guid = ' || p_target_guid || ')' ||
        ' (collection_ts = ' || p_coll_ts || ')' ||
        ' (emd_url = ' || p_emd_url || ')' ||
        ' (current_status = ' || get_avail_string(p_curr_stat) || ')' );

    END IF;

    IF (EMDW_LOG.p_is_debug_set) THEN
      EMDW_LOG.debug('process_agent_down_sev : Entered. emd_url = ' || p_emd_url ||
                 ' curr_stat = ' || p_curr_stat, G_MODULE_NAME);
    END IF;

    -- Extend the availability marker for all targets defined on this
    -- agent.
    --
    FOR tgt_rec IN (SELECT target_guid, target_type, target_name
                      FROM MGMT_TARGETS
                     WHERE emd_url = p_emd_url
                       AND rep_side_avail = 0
                     ORDER BY target_guid) LOOP

      IF (EMDW_LOG.p_is_debug_set) THEN
        EMDW_LOG.debug('Processing agent DOWN for ' ||
          ' (target_guid = ' || tgt_rec.target_guid || ') ' || 
          ' (target_name = ' || tgt_rec.target_name || ') ' || 
          ' (target_type = ' || tgt_rec.target_type || ') ', G_MODULE_NAME);
      END IF;
      --
      -- Do not change the availability of the target if the target 
      -- uses beacon availability
      --
      IF ( MGMT_GENSVC_AVAIL.IS_AVAIL_ENABLED(tgt_rec.target_guid) ) THEN

            NULL;

      ELSE

        IF (tgt_rec.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE) THEN
          l_new_avail := MGMT_GLOBAL.G_STATUS_DOWN;
        ELSE
          l_new_avail := MGMT_GLOBAL.G_STATUS_AGENT_DOWN;
        END IF;

        l_tgt_avail_cursor := get_avail_cursor(tgt_rec.target_guid);
  
        FETCH l_tgt_avail_cursor INTO l_curr_tgt_rowid,
                                      l_curr_tgt_avail,
                                      l_curr_tgt_start_coll_ts,
                                      l_curr_tgt_end_coll_ts;

        FETCH l_tgt_avail_cursor INTO l_prev_tgt_rowid,
                                      l_prev_tgt_avail,
                                      l_prev_tgt_start_coll_ts,
                                      l_prev_tgt_end_coll_ts;

        IF (l_tgt_avail_cursor%NOTFOUND) THEN
             l_prev_tgt_rowid := NULL;
             l_prev_tgt_avail := MGMT_GLOBAL.G_STATUS_UNKNOWN;
             l_prev_tgt_start_coll_ts := NULL;
             l_prev_tgt_end_coll_ts := NULL;
        END IF;

        IF l_tgt_avail_cursor%ISOPEN THEN
          CLOSE l_tgt_avail_cursor;
        END IF;

        IF (EMDW_LOG.p_is_debug_set) THEN
          EMDW_LOG.debug(' Latest availability = ' || get_avail_string(l_curr_tgt_avail) || 
                ' Prev Availability = ' || get_avail_string(l_prev_tgt_avail), G_MODULE_NAME);
        END IF;

        IF ( (l_curr_tgt_avail IN (MGMT_GLOBAL.G_STATUS_UNREACHABLE, 
	                           MGMT_GLOBAL.G_STATUS_BLACKOUT) ) OR 
             ( (l_curr_tgt_avail = MGMT_GLOBAL.G_STATUS_UNKNOWN) AND
               (l_prev_tgt_avail = MGMT_GLOBAL.G_STATUS_BLACKOUT)) ) THEN

          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('process_agent_down_sev: Current avail is BLK or BLK/UNK', G_MODULE_NAME);
	    EMDW_LOG.debug(' Extending Avail marker till blackout..', G_MODULE_NAME);
          END IF;

          extend_avail_marker(tgt_rec.target_guid, p_coll_ts, 
                                l_new_avail, p_error);
        ELSE

          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('process_agent_down_sev: Current avail is NOT BLK', G_MODULE_NAME);
            EMDW_LOG.debug('Performing simple avail.', G_MODULE_NAME);
          END IF;

          -- If processing simple availability raise exception as, internal severity error
          -- for closing availability with -ve duration then just ignore it and do not
          -- update availability marker.

          l_upd_marker := TRUE;
          BEGIN
            process_simple_avail(l_curr_tgt_rowid, tgt_rec.target_guid,
                                 l_new_avail, NULL, p_coll_ts, p_error);

          EXCEPTION
            WHEN OTHERS THEN
              IF SQLCODE = MGMT_GLOBAL.INTERNAL_SEVERITY_ERR THEN
                l_upd_marker := FALSE;
              ELSE
                raise;
              END IF;
          END;

          IF l_upd_marker = TRUE THEN
            update_availability_marker(tgt_rec.target_guid, p_coll_ts, 
                                       l_new_avail, FALSE);
          END IF;

        END IF;
                 
 
      END IF; -- Has beacon availability?

    END LOOP;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('process_agent_down_sev:Exit p_error = ' || p_error, G_MODULE_NAME);
    END IF;
  END process_agent_down_sev;


  --
  -- PURPOSE 
  --   Process the AGENT UP severity
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  --
  PROCEDURE process_agent_up_sev(p_target_guid   IN  RAW,
                                 p_coll_ts       IN  DATE,
                                 p_emd_url       IN  VARCHAR2, 
                                 p_curr_stat     IN  NUMBER,
                                 p_error         OUT INTEGER) IS
    l_tgt_avail_cursor AVAIL_CURSOR;

    l_curr_tgt_rowid   UROWID;
    l_curr_tgt_avail   MGMT_AVAILABILITY.current_status%TYPE;
    l_curr_tgt_start_coll_ts MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
    l_curr_tgt_end_coll_ts MGMT_AVAILABILITY.end_collection_timestamp%TYPE;
    l_prev_tgt_rowid   UROWID;
    l_prev_tgt_avail   MGMT_AVAILABILITY.current_status%TYPE;
    l_prev_tgt_start_coll_ts MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
    l_prev_tgt_end_coll_ts MGMT_AVAILABILITY.end_collection_timestamp%TYPE;
    l_marker_avail     MGMT_AVAILABILITY.current_status%TYPE;
    l_agent_unrch      BOOLEAN := FALSE;
    l_calc_status      MGMT_AVAILABILITY.current_status%TYPE;
    l_typ_agtorhost    NUMBER := 0;
    l_process_simple   NUMBER := 0;
    l_extend_marker    NUMBER := 0;
    l_upd_marker             BOOLEAN := TRUE;

  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('process_agent_up_sev:Entry p_target_guid = ' || p_target_guid ||
                     ' p_coll_ts = '   || p_coll_ts ||
                     ' p_emd_url = '   || p_emd_url ||
                     ' p_curr_stat = ' || p_curr_stat,G_MODULE_NAME);
    END IF;
    -- Validate inputs..
    IF ( (p_target_guid IS NULL) OR 
         (p_coll_ts IS NULL) OR
         (p_emd_url IS NULL) OR 
         (p_curr_stat IS NULL) )THEN

      p_error := e_bad_params;

      raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
        MGMT_GLOBAL.INTERNAL_SEVERITY_ERR_M ||
        'Invalid inputs to process agent up severity : ' ||
        ' (target_guid = ' || p_target_guid || ')' ||
        ' (collection_ts = ' || p_coll_ts || ')' ||
        ' (emd_url = ' || p_emd_url || ')' ||
        ' (current_status = ' || get_avail_string(p_curr_stat) || ')' );

    END IF;

    IF (EMDW_LOG.p_is_debug_set) THEN
      EMDW_LOG.debug('process_agent_up_sev : Entered. emd_url = ' || p_emd_url ||
                 ' curr_stat = ' || p_curr_stat, G_MODULE_NAME);
    END IF;


    -- 
    -- Algo: 
    --   For each target
    --     If target has beacon avail, perform becaon update
    --     Calculate the new status
    --       If type = host/agent new_status = UP
    --       Else look back up the avail history for non UNRCH/BLKOUT/AGDWN status
    --            If blkout new_stauts = UNK
    --     If curr = UNRCH/BLK or prev-curr = BLK-UNK
    --       exten avail marker, dont update curr availability
    --     else
    --       process simple avail, update marker to new time
    -- 
    FOR tgt_rec IN (SELECT target_guid, target_type, target_name
                      FROM MGMT_TARGETS
                     WHERE emd_url = p_emd_url 
                       AND rep_side_avail = 0
                     ORDER BY target_guid) LOOP

      IF (EMDW_LOG.p_is_debug_set) THEN
        EMDW_LOG.debug('Processing agent UP for ' ||
              ' (target_guid = ' || tgt_rec.target_guid || ') ' || 
              ' (target_name = ' || tgt_rec.target_name || ') ' || 
              ' (target_type = ' || tgt_rec.target_type || ') ', G_MODULE_NAME);
      END IF;
      l_typ_agtorhost := 0;
      --
      -- Do not change the availability of the target if the target uses 
      -- beacon availability
      --
      IF ( MGMT_GENSVC_AVAIL.IS_AVAIL_ENABLED(tgt_rec.target_guid) ) THEN

        NULL;

      ELSE

        -- Get the status of the target before the ag down..
        l_tgt_avail_cursor := get_avail_cursor(tgt_rec.target_guid);

        FETCH l_tgt_avail_cursor INTO l_curr_tgt_rowid,
                                      l_curr_tgt_avail,
                                      l_curr_tgt_start_coll_ts,
                                      l_curr_tgt_end_coll_ts;
        FETCH l_tgt_avail_cursor INTO l_prev_tgt_rowid,
                                      l_prev_tgt_avail,
                                      l_prev_tgt_start_coll_ts,
                                      l_prev_tgt_end_coll_ts;

        IF (l_tgt_avail_cursor%NOTFOUND) THEN
	  l_prev_tgt_avail := MGMT_GLOBAL.G_STATUS_UNKNOWN;
	END IF;

        IF (tgt_rec.target_type IN (MGMT_GLOBAL.G_AGENT_TARGET_TYPE,
                                    MGMT_GLOBAL.G_HOST_TARGET_TYPE) ) THEN

          -- For host and Agent target types, dont check the previous avail state..
          -- Just mark them as UP.
          l_calc_status := MGMT_GLOBAL.G_STATUS_UP;
	  l_typ_agtorhost := 1;

        ELSE
          -- Recover the availability state by looking at the history.
          -- Keep fetching until we get a good availability state to recover to
          WHILE (l_prev_tgt_avail IN (MGMT_GLOBAL.G_STATUS_AGENT_DOWN,
				      MGMT_GLOBAL.G_STATUS_UNREACHABLE) ) LOOP
  
            FETCH l_tgt_avail_cursor INTO l_prev_tgt_rowid,
                                          l_prev_tgt_avail,
                                          l_prev_tgt_start_coll_ts,
                                          l_prev_tgt_end_coll_ts;
          END LOOP;

	  IF (l_prev_tgt_avail = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
	    l_calc_status := MGMT_GLOBAL.G_STATUS_UNKNOWN;
          ELSE
	    l_calc_status := l_prev_tgt_avail;
	  END IF;

	END IF;

	IF ( (l_curr_tgt_avail IN (MGMT_GLOBAL.G_STATUS_UNREACHABLE, 
	                           MGMT_GLOBAL.G_STATUS_BLACKOUT)) OR
             ( (l_prev_tgt_avail = MGMT_GLOBAL.G_STATUS_BLACKOUT) AND
	       (l_curr_tgt_avail = MGMT_GLOBAL.G_STATUS_UNKNOWN) ) ) THEN

          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('process_agent_up_sev : Extending marker to ' ||
	      ' coll_ts = ' || p_coll_ts ||
	      ' calc_status = ' || l_calc_status, G_MODULE_NAME );
          END IF;

          BEGIN
            -- Extend history without affecting current availability
            extend_avail_marker(tgt_rec.target_guid, p_coll_ts, l_calc_status, p_error);
          EXCEPTION
            WHEN MGMT_GLOBAL.serverity_out_order THEN
              -- Ignore any out of time sequence errors
              IF (EMDW_LOG.p_is_debug_set) THEN
                EMDW_LOG.debug('process_agent_up_sev : Out of seq error while ' ||
                  ' extending avail marker ' ||
	          ' target_guid = ' || tgt_rec.target_guid ||
	          ' coll_ts = ' || p_coll_ts ||
	          ' calc_status = ' || l_calc_status, G_MODULE_NAME );
              END IF;
              NULL;
          END;

        ELSE

          IF (EMDW_LOG.p_is_debug_set) THEN
            EMDW_LOG.debug('process_agent_up_sev : Process simple avail ' ||
	      ' coll_ts = ' || p_coll_ts ||
	      ' calc_status = ' || l_calc_status, G_MODULE_NAME );
          END IF;

          l_process_simple := 0;
          -- Affect current availability, only if the curr is not (up/down/error) 
	  --   and curr status <> new calc status
	  IF ( (l_curr_tgt_avail NOT IN (MGMT_GLOBAL.G_STATUS_UP, 
					 MGMT_GLOBAL.G_STATUS_DOWN,
					 MGMT_GLOBAL.G_STATUS_ERROR) ) AND
               (l_curr_tgt_avail <> l_calc_status) ) THEN
	    -- Only if there is a change in status, add a new avail row.
            l_process_simple := 1;
          END IF;

          -- For agent to recover from down state
	  IF ((l_typ_agtorhost = 1) AND 
	      (l_curr_tgt_avail = MGMT_GLOBAL.G_STATUS_DOWN) ) THEN
            l_process_simple := 1;
	  END IF;

          l_upd_marker := TRUE;
          IF (l_process_simple = 1) THEN
            ---
            -- If processing simple availability raise exception as, internal severity error
            -- for closing availability with -ve duration then just ignore it and do not
            -- update availability marker.

            BEGIN
              process_simple_avail(l_curr_tgt_rowid, tgt_rec.target_guid,
                                   l_calc_status, NULL, p_coll_ts, p_error);
            EXCEPTION
              WHEN OTHERS THEN
                IF SQLCODE = MGMT_GLOBAL.INTERNAL_SEVERITY_ERR THEN
                  l_upd_marker := FALSE;
                ELSE
                  raise;
                END IF;
            END;
           ---
          END IF;

          IF l_upd_marker = TRUE THEN
            update_availability_marker(tgt_rec.target_guid, p_coll_ts, l_calc_status, FALSE);
          END IF;

        END IF;

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

      END IF;

    END LOOP;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('process_agent_up_sev:Exit p_error = ' || p_error, G_MODULE_NAME);
    END IF;
  END process_agent_up_sev;


  FUNCTION state_matches_preblkout_state(p_target_guid IN RAW,
                                           p_violation_level IN NUMBER)
    RETURN NUMBER
  IS
    l_tgt_avail_cursor AVAIL_CURSOR;

    l_curr_tgt_rowid   UROWID;
    l_curr_tgt_avail   MGMT_AVAILABILITY.current_status%TYPE;
    l_curr_tgt_start_coll_ts MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
    l_curr_tgt_end_coll_ts MGMT_AVAILABILITY.end_collection_timestamp%TYPE;
    l_prev_tgt_rowid   UROWID;
    l_prev_tgt_avail   MGMT_AVAILABILITY.current_status%TYPE;
    l_prev_tgt_start_coll_ts MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
    l_prev_tgt_end_coll_ts MGMT_AVAILABILITY.end_collection_timestamp%TYPE;
    l_pre_blkout_avail   MGMT_AVAILABILITY.current_status%TYPE;
    l_retval NUMBER := 0;
    l_proc_name     VARCHAR2(40) := 'status_matches_preblkout_status';
    l_tgt_type MGMT_TARGETS.target_type%TYPE;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info(l_proc_name || ' :Enter target_guid = ' || p_target_guid ||
         ' viol lvl = ' || p_violation_level, G_MODULE_NAME);
    END IF;
    -- Get pre blackout status
    l_tgt_avail_cursor := get_avail_cursor(p_target_guid);

    FETCH l_tgt_avail_cursor INTO l_curr_tgt_rowid,
                                  l_curr_tgt_avail,
                                  l_curr_tgt_start_coll_ts,
                                  l_curr_tgt_end_coll_ts;
    FETCH l_tgt_avail_cursor INTO l_prev_tgt_rowid,
                                  l_prev_tgt_avail,
                                  l_prev_tgt_start_coll_ts,
                                  l_prev_tgt_end_coll_ts;

    IF (EMDW_LOG.p_is_debug_set) THEN
      EMDW_LOG.debug(l_proc_name || ' curr avail = ' || l_curr_tgt_avail ||
              ' prev avail = ' || l_prev_tgt_avail, G_MODULE_NAME );
    END IF;

    IF (l_tgt_avail_cursor%NOTFOUND) THEN
      l_pre_blkout_avail := MGMT_GLOBAL.G_STATUS_UNKNOWN;

    ELSIF (l_curr_tgt_avail = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
      l_pre_blkout_avail := l_prev_tgt_avail;

    ELSIF (l_prev_tgt_avail = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
      FETCH l_tgt_avail_cursor INTO l_prev_tgt_rowid,
                                    l_prev_tgt_avail,
                                    l_prev_tgt_start_coll_ts,
                                    l_prev_tgt_end_coll_ts;
     
      IF (l_tgt_avail_cursor%NOTFOUND) THEN
        l_pre_blkout_avail := MGMT_GLOBAL.G_STATUS_UNKNOWN;
      ELSE
        l_pre_blkout_avail := l_prev_tgt_avail;
      END IF;

    END IF;

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

    IF (EMDW_LOG.p_is_debug_set) THEN
      EMDW_LOG.debug(l_proc_name || ' pre blkout avail = ' || l_pre_blkout_avail ||
              ' viol lvl = ' || p_violation_level, G_MODULE_NAME );
    END IF;

    BEGIN

      SELECT target_type INTO l_tgt_type
        FROM MGMT_TARGETS
       WHERE target_guid = p_target_guid;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        raise_application_error(MGMT_GLOBAL.UNKNOWN_TARGET_ERR,
            MGMT_GLOBAL.UNKNOWN_TARGET_ERR_M ||
            '(target guid = ' || p_target_guid || ')');

      WHEN OTHERS THEN
        raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
            MGMT_GLOBAL.INTERNAL_SEVERITY_ERR_M ||
            '(target guid = ' || p_target_guid || ')' ||
            '(SQLERROR = ' || SQLERRM || ')' );
    END;

    IF ( ( (l_tgt_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) OR
           (l_tgt_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE) ) AND
         (l_pre_blkout_avail = MGMT_GLOBAL.G_STATUS_UP)  AND
         (p_violation_level = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_CLEAR) ) THEN
      l_retval := 1;

    ELSIF ( (l_pre_blkout_avail = MGMT_GLOBAL.G_STATUS_UP)  AND
         (p_violation_level = MGMT_GLOBAL.G_SEVERITY_CLEAR) ) THEN
      l_retval := 1;

    ELSIF ( (l_pre_blkout_avail = MGMT_GLOBAL.G_STATUS_DOWN) AND
            (p_violation_level IN (MGMT_GLOBAL.G_SEVERITY_WARNING,
                                   MGMT_GLOBAL.G_SEVERITY_CRITICAL) ) ) THEN
      l_retval := 1;

    ELSIF ( (l_pre_blkout_avail = MGMT_GLOBAL.G_STATUS_ERROR)  AND
            (p_violation_level = MGMT_GLOBAL.G_SEVERITY_ERROR_START) ) THEN
      l_retval := 1;

    ELSE
      l_retval := 0;
    END IF;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info(l_proc_name || ' :Exit ret_vale = ' || l_retval, G_MODULE_NAME);
    END IF;

    RETURN l_retval;

  END state_matches_preblkout_state;

  --
  -- get_severity_segments
  --
  --  Given a target_name, target_type, target_guid, 
  --  metric_name (could be null), metric_column (could be null),
  --  metric_guid (could be null), key_value (could be null),
  --  start_date, end_date, (or the last num_of_days), how many 
  --  minutesa pixel spans, returns a severity history chart.
  --
  --  The returned array represents one chart with several segments.
  --  Each segment represents a different state with a start_date,
  --  duration (in minutes) and severity code:
  --    15 (MGMT_GLOBAL.G_SEVERITY_CLEAR),
  --    20 (MGMT_GLOBAL.G_SEVERITY_WARNING),
  --    25 (MGMT_GLOBAL.G_SEVERITY_CRITICAL)
  -- 
  --  EM_SEVERITY_HISTORY_ARRAY is a TABLE of EM_SEVERITY_HISTORY_OBJ
  --  which has 3 columns:
  --    start_date        DATE,
  --    severity_duration NUMBER,
  --    severity_code     NUMBER
  --


  PROCEDURE get_severity_segments(target_name_in  IN  VARCHAR2,
                                  target_type_in  IN  VARCHAR2,
                                  metric_name_in  IN  VARCHAR2,
                                  metric_col_in   IN  VARCHAR2,
                                  metric_guid_in  IN  RAW,
                                  key_value_in    IN  VARCHAR2,
                                  start_date_in   IN  DATE,
                                  end_date_in     IN  DATE,
                                  num_of_days_in  IN  NUMBER,
                                  pixel_size_in   IN  NUMBER,
                                  chart_record_out OUT EM_SEVERITY_HISTORY_ARRAY)
  IS

    l_target_guid mgmt_targets.target_guid%TYPE;
    l_target_tzr  mgmt_targets.timezone_region%TYPE;
    l_target_sysdate DATE;
    l_start_date DATE;
    l_end_date DATE;
    l_num_of_days NUMBER;
    l_num_of_pixels NUMBER;
    l_start_point DATE;
    l_severity_code mgmt_severity.severity_code%TYPE;
    j NUMBER;
  
    TYPE START_POINT_LIST IS TABLE OF DATE;
    l_start_point_list START_POINT_LIST;
    TYPE SEVERITY_CODE_LIST IS TABLE OF mgmt_severity.severity_code%TYPE;
    l_severity_code_list SEVERITY_CODE_LIST;

    l_min_collection_timestamp DATE;
    l_pre_start_point DATE;
    l_pre_severity_code mgmt_severity.severity_code%TYPE;
    l_pre_severity_duration mgmt_severity.severity_duration%TYPE;
    l_no_data_duration mgmt_severity.severity_duration%TYPE;
    l_clear_duration mgmt_severity.severity_duration%TYPE;
    l_clear_date DATE;
    l_status_metric_guid mgmt_metrics.metric_guid%TYPE;
  
    NO_DATA_SEVERITY_CODE CONSTANT NUMBER := -5;

    -- This cursor gets all severities for a specific target
    -- with a Status/Response metric (to be filtered out).
    -- It splits the duration to 240 segments and returns 240
    -- rows. Each row is one segment which is a part of the
    -- whole duration. Each row has a start_point and the max
    -- of all severities during that segment.
    --
    -- The order of the columns in the select clause must be kept 
    -- as is because a bulk collect is used later
    --
    CURSOR c_target_severity_1
    IS
      SELECT l_start_date + (cnt.c*l_num_of_days)/l_num_of_pixels start_point,
             MAX(sev.severity_code) severity_code
        FROM (SELECT /*+ INDEX(s) */ 
                     s.collection_timestamp,
                     NVL(s.severity_duration/24, l_end_date-s.collection_timestamp) severity_duration,
                     s.severity_code
                FROM mgmt_severity s
               WHERE s.target_guid = l_target_guid
                 ANd s.metric_guid != l_status_metric_guid
                 AND s.severity_code IN (MGMT_GLOBAL.G_SEVERITY_WARNING, 
                                         MGMT_GLOBAL.G_SEVERITY_CRITICAL)
                 AND s.collection_timestamp+NVL(s.severity_duration/24, l_end_date-s.collection_timestamp) > l_start_date 
                 AND s.collection_timestamp < l_end_date) sev,
             (SELECT /*+ INDEX(o) */ 
                     rownum-1 c 
                FROM sys.obj$ o 
               WHERE obj# > 0 
                 AND rownum <= l_num_of_pixels) cnt
       WHERE sev.collection_timestamp >= 
             l_start_date + (cnt.c*l_num_of_days)/l_num_of_pixels - 
             sev.severity_duration
         AND sev.collection_timestamp <= 
             l_start_date + ((cnt.c+1)*l_num_of_days)/l_num_of_pixels
       GROUP BY l_start_date + (cnt.c*l_num_of_days)/l_num_of_pixels;

    -- This cursor gets all severities for a specific target
    -- without a Response/Status metric.
    -- It splits the duration to 240 segments and returns 240
    -- rows. Each row is one segment which is a part of the
    -- whole duration. Each row has a start_point and the max
    -- of all severities during that segment.
    --
    -- The order of the columns in the select clause must be kept
    -- as is because a bulk collect is used later
    --
    CURSOR c_target_severity_2
    IS
      SELECT l_start_date + (cnt.c*l_num_of_days)/l_num_of_pixels start_point,
             MAX(sev.severity_code) severity_code
        FROM (SELECT /*+ INDEX(s) */ 
                     s.collection_timestamp,
                     NVL(s.severity_duration/24, l_end_date-s.collection_timestamp) severity_duration,
                     s.severity_code
                FROM mgmt_severity s
               WHERE s.target_guid = l_target_guid
                 AND s.severity_code IN (MGMT_GLOBAL.G_SEVERITY_WARNING, 
                                         MGMT_GLOBAL.G_SEVERITY_CRITICAL)
                 AND s.collection_timestamp+NVL(s.severity_duration/24, l_end_date-s.collection_timestamp) > l_start_date 
                 AND s.collection_timestamp < l_end_date) sev,
             (SELECT /*+ INDEX(o) */ 
                     rownum-1 c 
                FROM sys.obj$ o 
               WHERE obj# > 0 
                 AND rownum <= l_num_of_pixels) cnt
       WHERE sev.collection_timestamp >= 
             l_start_date + (cnt.c*l_num_of_days)/l_num_of_pixels - 
             sev.severity_duration
         AND sev.collection_timestamp <= 
             l_start_date + ((cnt.c+1)*l_num_of_days)/l_num_of_pixels
       GROUP BY l_start_date + (cnt.c*l_num_of_days)/l_num_of_pixels;

    -- This cursor gets all severities for a specific metric.
    -- It splits the duration to 240 segments and returns 240
    -- rows. Each row is one segment which is a part of the
    -- whole duration. Each row has a start_point and the max
    -- of all severities during that segment.
    --
    -- The order of the columns in the select clause must be kept 
    -- as is because a bulk collect is used later
    --
    CURSOR c_metric_severity
    IS
      SELECT l_start_date + (cnt.c*l_num_of_days)/l_num_of_pixels start_point,
             MAX(sev.severity_code) severity_code
        FROM (SELECT /*+ INDEX(s) */ 
                     s.collection_timestamp,
                     NVL(s.severity_duration/24, l_end_date-s.collection_timestamp) severity_duration,
                     s.severity_code
                FROM mgmt_severity s
               WHERE s.target_guid = l_target_guid
                 ANd s.metric_guid = metric_guid_in
                 AND s.severity_code IN (MGMT_GLOBAL.G_SEVERITY_WARNING, 
                                         MGMT_GLOBAL.G_SEVERITY_CRITICAL)
                 AND s.collection_timestamp+NVL(s.severity_duration/24, l_end_date-s.collection_timestamp) > l_start_date 
                 AND s.collection_timestamp < l_end_date) sev,
             (SELECT /*+ INDEX(o) */ 
                     rownum-1 c 
                FROM sys.obj$ o 
               WHERE obj# > 0 
                 AND rownum <= l_num_of_pixels) cnt
       WHERE sev.collection_timestamp >= 
             l_start_date + (cnt.c*l_num_of_days)/l_num_of_pixels - 
             sev.severity_duration
         AND sev.collection_timestamp <= 
             l_start_date + ((cnt.c+1)*l_num_of_days)/l_num_of_pixels
       GROUP BY l_start_date + (cnt.c*l_num_of_days)/l_num_of_pixels;

    -- This cursor gets all severities for a specific key value.
    -- It splits the duration to 480 segments and returns 240
    -- rows. Each row is one segment which is a part of the
    -- whole duration. Each row has a start_point and the max
    -- of all severities during that segment.
    --
    -- The order of the columns in the select clause must be kept 
    -- as is because a bulk collect is used later
    --
    CURSOR c_key_severity
    IS
      SELECT l_start_date + (cnt.c*l_num_of_days)/l_num_of_pixels start_point,
             MAX(sev.severity_code) severity_code
        FROM (SELECT /*+ INDEX(s) */ 
                     s.collection_timestamp,
                     NVL(s.severity_duration/24, l_end_date-s.collection_timestamp) severity_duration,
                     s.severity_code
                FROM mgmt_severity s
               WHERE s.target_guid = l_target_guid
                 ANd s.metric_guid = metric_guid_in
                 AND s.key_value = key_value_in
                 AND s.severity_code IN (MGMT_GLOBAL.G_SEVERITY_WARNING, 
                                         MGMT_GLOBAL.G_SEVERITY_CRITICAL)
                 AND s.collection_timestamp+NVL(s.severity_duration/24, l_end_date-s.collection_timestamp) > l_start_date 
                 AND s.collection_timestamp < l_end_date) sev,
             (SELECT /*+ INDEX(o) */ 
                     rownum-1 c 
                FROM sys.obj$ o 
               WHERE obj# > 0 
                 AND rownum <= l_num_of_pixels) cnt
       WHERE sev.collection_timestamp >= 
             l_start_date + (cnt.c*l_num_of_days)/l_num_of_pixels - 
             sev.severity_duration
         AND sev.collection_timestamp <= 
             l_start_date + ((cnt.c+1)*l_num_of_days)/l_num_of_pixels
       GROUP BY l_start_date + (cnt.c*l_num_of_days)/l_num_of_pixels;

  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('get_severity_segments:Entry target_name_in = ' || target_name_in ||
                     ' target_type_in = ' || target_type_in ||
                     ' metric_name_in = ' || metric_name_in ||
                     ' metric_col_in = '  || metric_col_in  ||
                     ' metric_guid_in = ' || metric_guid_in ||
                     ' key_value_in = '   || key_value_in   ||
                     ' start_date_in = '  || start_date_in  ||
                     ' end_date_in = '    || end_date_in    ||
                     ' num_of_days_in = ' || num_of_days_in ||
                     ' pixel_size_in = '  || pixel_size_in, G_MODULE_NAME);
    END IF;

    -- Store the target guid to improve performance.  
    BEGIN
      SELECT target_guid, timezone_region
        INTO l_target_guid, l_target_tzr
        FROM mgmt_targets
        WHERE target_name = target_name_in
          AND target_type = target_type_in;
    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        raise MGMT_GLOBAL.target_does_not_exist;
    END;

    -- Get the target's sysdate using targets time zone.
    l_target_sysdate := MGMT_GLOBAL.SYSDATE_TZRGN(l_target_tzr);

    -- only if start_date_in and end_date_in are both not null, use them
    -- else use num_of_days_in to compute l_start_date, l_end_date
    IF( start_date_in IS NOT NULL AND end_date_in IS NOT NULL )
    THEN
      -- ignore num_days_in
      l_start_date := start_date_in;
      l_end_date := end_date_in;
      l_num_of_days := end_date_in - start_date_in;
    ELSE
      -- use num_days_in
      l_num_of_days := num_of_days_in;
      l_end_date := l_target_sysdate;
      l_start_date := l_end_date - num_of_days_in;
    END IF;

    -- the number of pixels for a bar chart
    l_num_of_pixels := (l_end_date - l_start_date) * 24 * 60 / pixel_size_in;

    l_pre_start_point := l_start_date;
    l_pre_severity_code := 0;
    l_pre_severity_duration := pixel_size_in;
    l_no_data_duration := 0;
    
    chart_record_out := EM_SEVERITY_HISTORY_ARRAY();
    j := 0;    

    IF (key_value_in IS NOT NULL)
    THEN
        -- a key value; get the first available data date
        /*
        SELECT MIN(collection_timestamp)
          INTO l_min_collection_timestamp
          FROM mgmt_severity
         WHERE target_guid = l_target_guid
           AND metric_guid = metric_guid_in
           AND key_value = key_value_in
           AND severity_code IN (MGMT_GLOBAL.G_SEVERITY_CLEAR,
                                 MGMT_GLOBAL.G_SEVERITY_WARNING,
                                 MGMT_GLOBAL.G_SEVERITY_CRITICAL);
         */
      
        BEGIN
              SELECT collection_timestamp
              INTO l_min_collection_timestamp
              FROM 
              (
                  SELECT collection_timestamp
                  FROM   mgmt_violations
                  WHERE target_guid = HEXTORAW(l_target_guid)
                    AND policy_guid = HEXTORAW(metric_guid_in)
                    AND key_value = key_value_in
                    AND violation_level IN (MGMT_GLOBAL.G_SEVERITY_CLEAR,
                                            MGMT_GLOBAL.G_SEVERITY_WARNING,
                                            MGMT_GLOBAL.G_SEVERITY_CRITICAL)
                    AND violation_type IN (0,1,2)
                  ORDER BY collection_timestamp ASC
              ) WHERE ROWNUM < 2;

        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                l_min_collection_timestamp := NULL;
        END;
        -- get the severity history for a key 
        OPEN c_key_severity;
        FETCH c_key_severity BULK COLLECT INTO
          l_start_point_list, l_severity_code_list;
        CLOSE c_key_severity;
    ELSIF (metric_name_in IS NOT NULL AND metric_col_in IS NOT NULL)
    THEN
        -- a metric; get the first available data date
       /*
        SELECT MIN(collection_timestamp)
          INTO l_min_collection_timestamp
          FROM mgmt_severity
         WHERE target_guid = l_target_guid
           AND metric_guid = metric_guid_in
           AND severity_code IN (MGMT_GLOBAL.G_SEVERITY_CLEAR,
                                 MGMT_GLOBAL.G_SEVERITY_WARNING,
                                 MGMT_GLOBAL.G_SEVERITY_CRITICAL);
       */
        BEGIN
            SELECT collection_timestamp
            INTO l_min_collection_timestamp
            FROM 
            (
                SELECT collection_timestamp
                FROM   mgmt_violations
                WHERE target_guid = HEXTORAW(l_target_guid)
                  AND policy_guid = HEXTORAW(metric_guid_in)
                  AND violation_level IN (MGMT_GLOBAL.G_SEVERITY_CLEAR,
                                          MGMT_GLOBAL.G_SEVERITY_WARNING,
                                          MGMT_GLOBAL.G_SEVERITY_CRITICAL)
                  AND violation_type IN (0,1,2)
                ORDER BY collection_timestamp ASC
            ) WHERE ROWNUM < 2;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                l_min_collection_timestamp := NULL;
        END;
        -- get the severity history for a metric
        OPEN c_metric_severity;
        FETCH c_metric_severity BULK COLLECT INTO
          l_start_point_list, l_severity_code_list;
        CLOSE c_metric_severity;
    ELSE
        -- a target; filter out the Response/Status metric
        BEGIN
          l_status_metric_guid := 
            mgmt_target.get_metric_guid(
                  target_type_in, 
                  MGMT_GLOBAL.G_AVAIL_METRIC_NAME,
                  MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN);
        EXCEPTION
          WHEN OTHERS
          THEN
            l_status_metric_guid := null;
        END;

        IF (l_status_metric_guid IS NOT NULL)
        THEN
          -- get the first available data date
          /*
          SELECT MIN(collection_timestamp)
            INTO l_min_collection_timestamp
            FROM mgmt_severity
           WHERE target_guid = l_target_guid
             AND metric_guid != l_status_metric_guid
             AND severity_code IN (MGMT_GLOBAL.G_SEVERITY_CLEAR,
                                   MGMT_GLOBAL.G_SEVERITY_WARNING,
                                   MGMT_GLOBAL.G_SEVERITY_CRITICAL);
           */
           BEGIN
              SELECT collection_timestamp
              INTO l_min_collection_timestamp
              FROM
              (
                  SELECT collection_timestamp
                  FROM mgmt_violations
                  WHERE target_guid = HEXTORAW(l_target_guid)
                    AND policy_guid != HEXTORAW(l_status_metric_guid)
                    AND violation_level IN (MGMT_GLOBAL.G_SEVERITY_CLEAR,
                                            MGMT_GLOBAL.G_SEVERITY_WARNING,
                                            MGMT_GLOBAL.G_SEVERITY_CRITICAL)
                    AND violation_type IN (0,1,2)
                    ORDER BY collection_timestamp ASC
                ) WHERE ROWNUM < 2;
           EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    l_min_collection_timestamp := NULL;                       
           END;
          -- get the severity history for a target
          OPEN c_target_severity_1;
          FETCH c_target_severity_1 BULK COLLECT INTO
            l_start_point_list, l_severity_code_list;
          CLOSE c_target_severity_1;
        ELSE
          -- get the first available data date
          /*
          SELECT MIN(collection_timestamp)
            INTO l_min_collection_timestamp
            FROM mgmt_severity
           WHERE target_guid = l_target_guid
             AND severity_code IN (MGMT_GLOBAL.G_SEVERITY_CLEAR,
                                   MGMT_GLOBAL.G_SEVERITY_WARNING,
                                   MGMT_GLOBAL.G_SEVERITY_CRITICAL);
          */
          BEGIN
              SELECT collection_timestamp
              INTO l_min_collection_timestamp
              FROM
              (
                   SELECT collection_timestamp
                   FROM mgmt_violations
                   WHERE target_guid = HEXTORAW(l_target_guid)
                     AND violation_level IN (MGMT_GLOBAL.G_SEVERITY_CLEAR,
                                             MGMT_GLOBAL.G_SEVERITY_WARNING,
                                             MGMT_GLOBAL.G_SEVERITY_CRITICAL)
                     AND violation_type IN (0,1,2)
                   ORDER BY collection_timestamp ASC 
              )WHERE ROWNUM < 2; 
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
                l_min_collection_timestamp := NULL;
          END;

          OPEN c_target_severity_2;
          FETCH c_target_severity_2 BULK COLLECT INTO
            l_start_point_list, l_severity_code_list;
          CLOSE c_target_severity_2;
        END IF;
    END IF;

    -- First check if there's any alerts at all
    IF (l_start_point_list.COUNT = 0)
    THEN
        -- No Alert, check the min collection time
        IF (l_min_collection_timestamp IS NULL)
        THEN
            chart_record_out.extend;
            j := j+1;
            chart_record_out(j) := EM_SEVERITY_HISTORY_OBJ(
                l_start_date, 
                l_num_of_days*24*60,
                NO_DATA_SEVERITY_CODE);
        ELSIF (l_min_collection_timestamp < l_start_date)
        THEN
            chart_record_out.extend;
            j := j+1;
            chart_record_out(j) := EM_SEVERITY_HISTORY_OBJ(
                l_start_date, 
                l_num_of_days*24*60,
                MGMT_GLOBAL.G_SEVERITY_CLEAR);
        ELSIF (l_min_collection_timestamp > l_end_date)  
        THEN
            chart_record_out.extend;
            j := j+1;
            chart_record_out(j) := EM_SEVERITY_HISTORY_OBJ(
                l_start_date, 
                l_num_of_days*24*60,
                NO_DATA_SEVERITY_CODE);
        ELSE
            chart_record_out.extend;
            j := j+1;
            chart_record_out(j) := EM_SEVERITY_HISTORY_OBJ(
                l_start_date, 
                (l_min_collection_timestamp - l_start_date)*24*60,
                NO_DATA_SEVERITY_CODE);
            
            chart_record_out.extend;
            j := j+1;
            chart_record_out(j) := EM_SEVERITY_HISTORY_OBJ(
                l_min_collection_timestamp,
                (l_end_date - l_min_collection_timestamp)*24*60,
                MGMT_GLOBAL.G_SEVERITY_CLEAR);
        END IF;

        IF (EMDW_LOG.p_is_info_set)THEN
          EMDW_LOG.info('get_severity_segments:Exit l_start_point_list.COUNT = 0', G_MODULE_NAME);
        END IF;

        RETURN;
    END IF;

    -- check the CLEAR state (severity_code = 15)
    l_start_point := l_start_point_list(1);

    -- an estimate of (l_start_date < l_start_point)
    IF (ROUND((l_start_point-l_start_date)*24*60/pixel_size_in) > 0)
    THEN
      -- check "no data found":  no severity data for sometime
      -- add a special segment with a severity code of -5
      -- There is at least one record in the list
      --

      -- the number of pixels for possible NO_DATA state
      l_no_data_duration := ROUND((l_min_collection_timestamp-l_start_date)*24*60/pixel_size_in);

      -- an estimate of (l_start_date < l_min_collection_timestamp)
      IF (l_no_data_duration > 0)
      THEN
        l_clear_date := l_min_collection_timestamp;

        -- convert to MINUTES and add a NO_DATA state
        l_no_data_duration := l_no_data_duration * pixel_size_in;

        chart_record_out.extend;
        j := j+1;

        chart_record_out(j) := EM_SEVERITY_HISTORY_OBJ(
                l_start_date, l_no_data_duration, NO_DATA_SEVERITY_CODE);
      ELSE
        l_clear_date := l_start_date;
      END IF;

      -- the number of pixels for possible CLEAR state
      l_clear_duration := ROUND((l_start_point-l_clear_date)*24*60/pixel_size_in);

      -- an estimate of (l_clear_date < l_start_point)
      IF (l_clear_duration > 0)
      THEN
        -- convert to MINUTES and add a CLEAR state
        l_clear_duration := l_clear_duration * pixel_size_in;

        chart_record_out.extend;
        j := j+1;

        chart_record_out(j) := EM_SEVERITY_HISTORY_OBJ(
                l_clear_date, l_clear_duration, MGMT_GLOBAL.G_SEVERITY_CLEAR);
      END IF;

    END IF;

    -- combine segments with the same severity code 
    -- It will reduce the amount of returned data
    FOR i IN 1..l_start_point_list.COUNT LOOP

      l_start_point := l_start_point_list(i);
      l_severity_code := l_severity_code_list(i);

      -- an estimate of (l_start_point-l_pre_start_point-l_pre_severity_duration/(24*60))
      -- the number of pixels for possible CLEAR state
      l_clear_duration := ROUND(((l_start_point-l_pre_start_point)*24*60-l_pre_severity_duration)
                                /pixel_size_in);

      IF (l_severity_code = l_pre_severity_code AND
          l_clear_duration = 0)
      THEN
        -- same severity code; combine them
        l_pre_severity_duration := l_pre_severity_duration + pixel_size_in;
      ELSE
        IF (l_pre_severity_code > 0)
        THEN
          -- different severity code, add the previous valid one 
          chart_record_out.extend;
          j := j+1;

          chart_record_out(j) := EM_SEVERITY_HISTORY_OBJ(
              l_pre_start_point, 
              l_pre_severity_duration, l_pre_severity_code);

          -- checking if a clear state is needed
          IF (l_clear_duration > 0)
          THEN
            -- convert to MINUTES and add a CLEAR state
            l_clear_duration := l_clear_duration * pixel_size_in;
            chart_record_out.extend;
            j := j+1;

            chart_record_out(j) := EM_SEVERITY_HISTORY_OBJ(
              l_pre_start_point + l_pre_severity_duration/(24*60),
              l_clear_duration,
              MGMT_GLOBAL.G_SEVERITY_CLEAR);
          END IF;
        END IF;

        -- reset the pre_ value
        l_pre_start_point := l_start_point;
        l_pre_severity_code := l_severity_code;
        l_pre_severity_duration := pixel_size_in;
                  
      END IF;    
    END LOOP;

    -- add the last one
    IF (l_pre_severity_code <> 0)
    THEN

      chart_record_out.extend;
      j := j+1;

      chart_record_out(j) := EM_SEVERITY_HISTORY_OBJ(
            l_pre_start_point, 
            l_pre_severity_duration, l_pre_severity_code);

    END IF;
  
    -- check the clear state at the end
    -- an estimate of (l_end_date > l_pre_start_point + l_pre_severity_duration/(24*60))
    -- the number of pixels for possible CLEAR state
    l_clear_duration := ROUND(((l_end_date-l_pre_start_point)*24*60
                               -l_pre_severity_duration)
                              /pixel_size_in);

    IF (l_clear_duration > 0)
    THEN
      -- convert to MINUTES and add the last CLEAR state
      l_clear_duration := l_clear_duration * pixel_size_in;

      chart_record_out.extend;
      j := j+1;

      chart_record_out(j) := EM_SEVERITY_HISTORY_OBJ(
            l_pre_start_point + l_pre_severity_duration/(24*60),
            l_clear_duration,
            MGMT_GLOBAL.G_SEVERITY_CLEAR);

    END IF;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('get_severity_segments:Exit', G_MODULE_NAME);
    END IF;
  END get_severity_segments;


  --
  -- get_severity_labels
  --
  --  Given a target_name, target_type, metric_name (could be null), 
  --  metric_column (could be null), member_status_in (a target with
  --  members? could be null), start_date, end_date, (or the last 
  --  num_of_days), returns the display name of the target or metric
  --  and a cursor.
  --
  --  The returned cursor has a list of 
  --     1. labels to be shown on the first column of the severity 
  --        chart page (e.g, alert history page), and
  --     2. other information necessary to get the date to draw a
  --        chart (by calling get_severity_segments for each label).
  -- 

  PROCEDURE get_severity_labels(target_name_in   IN  VARCHAR2,
                                target_type_in   IN  VARCHAR2,
                                metric_name_in   IN  VARCHAR2,
                                metric_col_in    IN  VARCHAR2,
                                member_status_in IN  VARCHAR2,
                                start_date_in    IN  DATE,
                                end_date_in      IN  DATE,
                                num_of_days_in   IN  NUMBER,
                                display_name_out OUT VARCHAR2,
                                chart_data_out   OUT MGMT_TARGET.CURSOR_TYPE,
                                target_tzrgn_out OUT VARCHAR2)
  IS
    l_target_guid mgmt_targets.target_guid%TYPE;
    l_metric_guid mgmt_metrics.metric_guid%TYPE;
    l_target_sysdate DATE;
    l_start_date DATE;
    l_end_date DATE;
    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
    IF (EMDW_LOG.p_is_info_set)THEN     
      EMDW_LOG.info('get_severity_labels:Entry target_name_in = ' || target_name_in ||
                     ' target_type_in = '   || target_type_in   ||
                     ' metric_name_in = '   || metric_name_in   ||
                     ' metric_col_in = '    || metric_col_in    ||
                     ' member_status_in = ' || member_status_in ||
                     ' start_date_in = '    || start_date_in    ||
                     ' end_date_in = '      || end_date_in      ||
                     ' num_of_days_in = '   || num_of_days_in, G_MODULE_NAME);
    END IF;

    -- Store the target guid to improve performance.  
    BEGIN
      SELECT target_guid, display_name, timezone_region, type_meta_ver,
             category_prop_1, category_prop_2, category_prop_3,
             category_prop_4, category_prop_5
        INTO l_target_guid, display_name_out, target_tzrgn_out, 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_name = target_name_in
          AND target_type = target_type_in;
    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        raise MGMT_GLOBAL.target_does_not_exist;
    END;
  
    -- Get the target's sysdate using targets time zone.
    l_target_sysdate := MGMT_GLOBAL.sysdate_tzrgn(target_tzrgn_out);

    -- only if start_date_in and end_date_in are both not null, use them
    -- else use num_of_days_in to compute l_start_date, l_end_date
    IF( start_date_in IS NOT NULL AND end_date_in IS NOT NULL )
    THEN
      -- ignore num_days_in
      l_start_date := start_date_in;
      l_end_date := end_date_in;
    ELSE
      -- use num_days_in
      l_end_date := l_target_sysdate;
      l_start_date := l_end_date - num_of_days_in;
    END IF;

    IF (metric_name_in IS NOT NULL AND metric_col_in IS NOT NULL)
    THEN
      -- a metric, get its metric column label first
      SELECT metric_guid, column_label
        INTO l_metric_guid, display_name_out
        FROM mgmt_metrics
        WHERE metric_name = metric_name_in
          AND metric_column = metric_col_in
          AND target_type = target_type_in
          AND type_meta_ver = l_type_meta_ver
          AND (category_prop_1 = l_category_prop_1 OR category_prop_1 = ' ')
          AND (category_prop_2 = l_category_prop_2 OR category_prop_2 = ' ')
          AND (category_prop_3 = l_category_prop_3 OR category_prop_3 = ' ')
          AND (category_prop_4 = l_category_prop_4 OR category_prop_4 = ' ')
          AND (category_prop_5 = l_category_prop_5 OR category_prop_5 = ' ');

      -- then get its key values
      OPEN chart_data_out FOR
      SELECT unique l_metric_guid metric_guid,
             sev.key_value, 
             ck.key_part1_value,
             ck.key_part2_value,
             ck.key_part3_value,
             ck.key_part4_value,
             ck.key_part5_value
        FROM mgmt_severity sev, 
             mgmt_metrics_composite_keys ck
       WHERE sev.target_guid = l_target_guid 
         AND sev.metric_guid = l_metric_guid 
         AND sev.severity_code IN (MGMT_GLOBAL.G_SEVERITY_WARNING, 
                                   MGMT_GLOBAL.G_SEVERITY_CRITICAL)
         AND sev.collection_timestamp <= l_end_date
         AND sev.collection_timestamp +
             NVL(sev.severity_duration/24, l_end_date-sev.collection_timestamp)
             >= l_start_date 
         AND ck.target_guid (+) = l_target_guid
         AND sev.key_value = ck.composite_key (+)
       ORDER BY key_value;

    ELSIF (member_status_in IS NULL OR member_status_in != 'true')
    THEN
      -- a target, get its metrics (name, column, column display name)
      OPEN chart_data_out FOR
    SELECT l_target_guid target_guid, met.metric_guid, 
           met.metric_name, met.metric_column, met.column_label, met.key_column, 
           met.column_label_nlsid
      FROM mgmt_metrics met
     WHERE met.target_type = target_type_in 
       AND (NOT (met.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND
                 met.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN))
       AND met.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
       AND met.type_meta_ver = l_type_meta_ver
       AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ')
       AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ')
       AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ')
       AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ')
       AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ')
       AND EXISTS 
            (SELECT 1 FROM mgmt_severity sev 
              WHERE sev.target_guid = l_target_guid 
                AND sev.metric_guid = met.metric_guid 
                AND sev.severity_code IN (MGMT_GLOBAL.G_SEVERITY_WARNING, 
                                          MGMT_GLOBAL.G_SEVERITY_CRITICAL) 
                AND sev.collection_timestamp <= l_end_date
                AND sev.collection_timestamp + 
                    NVL(sev.severity_duration/24, l_end_date-sev.collection_timestamp) 
                    >= l_start_date) 
     ORDER BY met.column_label;
     
    ELSE      
      -- a composite target, get its member targets (name, type, display_name)
      OPEN chart_data_out FOR
      -- Bug 6379336, modify query for Composite target
    SELECT tgt.target_guid, tgt.target_name, tgt.target_type, tgt.display_name
      FROM
        (SELECT l_target_guid target_guid, target_name_in target_name,
                target_type_in target_type, display_name_out display_name,
                l_type_meta_ver type_meta_ver,
                l_category_prop_1 category_prop_1,
                l_category_prop_2 category_prop_2,
                l_category_prop_3 category_prop_3,
                l_category_prop_4 category_prop_4,
                l_category_prop_5 category_prop_5
           FROM dual
         UNION ALL
         SELECT c_tgt.target_guid, c_tgt.target_name, c_tgt.target_type,
                c_tgt.display_name, c_tgt.type_meta_ver, c_tgt.category_prop_1,
                c_tgt.category_prop_2, c_tgt.category_prop_3,
                c_tgt.category_prop_4, c_tgt.category_prop_5
           FROM mgmt_target_assocs, mgmt_targets c_tgt
           WHERE source_target_guid = l_target_guid
             AND assoc_target_guid = c_tgt.target_guid
             AND assoc_guid = MGMT_ASSOC.g_contains_guid
        ) tgt
      WHERE EXISTS (
              SELECT 1 FROM mgmt_violations sev, mgmt_metrics met
                WHERE  sev.target_guid = tgt.target_guid
                  AND  sev.policy_guid = met.metric_guid
                  AND  sev.violation_level IN (MGMT_GLOBAL.G_SEVERITY_WARNING,
                                               MGMT_GLOBAL.G_SEVERITY_CRITICAL)
                  AND  sev.violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_THRESHOLD
                  AND  met.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
                  AND  met.type_meta_ver = tgt.type_meta_ver
                  AND  met.target_type = tgt.target_type
                  AND  (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ')
                  AND  (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ') 
                  AND  (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ')
                  AND  (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ')
                  AND  (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ')
                  AND  sev.collection_timestamp <= l_end_date
                  AND  sev.collection_timestamp +
                       NVL(sev.violation_duration/24, l_end_date-sev.collection_timestamp)
                       >= l_start_date
              )
      ORDER BY tgt.display_name;

    END IF;

    IF (EMDW_LOG.p_is_info_set)THEN     
      EMDW_LOG.info('get_severity_labels:Exit display_name_out = ' ||
                     display_name_out, G_MODULE_NAME);
    END IF;
  END get_severity_labels;



  PROCEDURE log_error(target_guid_in IN RAW,
                      error_code_in  IN NUMBER,
                      error_msg_in   IN VARCHAR2) IS
  l_emd_url MGMT_TARGETS.emd_url%TYPE;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('log_error:Entry target_guid_in = ' || target_guid_in ||
                     ' error_code_in = '   || error_code_in ||
                     ' error_msg_in = '   || error_msg_in, G_MODULE_NAME);
    END IF;

    -- Get EMD_URL.
    IF (target_guid_in IS NOT NULL) THEN
      BEGIN
        SELECT emd_url INTO l_emd_url 
          FROM MGMT_TARGETS
         WHERE target_guid = target_guid_in;
      EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_emd_url := ' '; -- log with blank
      END;
    END IF;
 
    -- Log the specified error to system error log.
    MGMT_LOG.LOG_ERROR(v_module_name_in => MODULE,
                       v_error_code_in => error_code_in,
               v_error_msg_in => error_msg_in,
               v_emd_url_in => l_emd_url);

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('log_error:Exit', G_MODULE_NAME);
    END IF;

  END log_error;

  FUNCTION is_agent_unreachable(p_target_guid IN RAW)
    RETURN BOOLEAN IS
    l_agent_guid                 MGMT_TARGETS.target_guid%TYPE;
    l_agent_unrch                BOOLEAN := FALSE;
    l_agt_avl_cursor             AVAIL_CURSOR;
    l_agt_avl_curr_rowid         UROWID;
    l_agt_avl_curr_status        MGMT_AVAILABILITY.current_status%TYPE;
    l_agt_avl_curr_start_coll_ts MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
    l_agt_avl_curr_end_coll_ts   MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
    l_agt_avl_prev_rowid         UROWID;
    l_agt_avl_prev_status        MGMT_AVAILABILITY.current_status%TYPE;
    l_agt_avl_prev_start_coll_ts MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
    l_agt_avl_prev_end_coll_ts   MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
 
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('is_agent_unreachable:Entry p_target_guid = ' || p_target_guid, G_MODULE_NAME);
    END IF;

    BEGIN 
      l_agent_guid := get_agent_guid(p_target_guid);
    
      l_agt_avl_cursor := get_avail_cursor(l_agent_guid);
      FETCH l_agt_avl_cursor INTO l_agt_avl_curr_rowid,
                                  l_agt_avl_curr_status,
                                  l_agt_avl_curr_start_coll_ts,
                                  l_agt_avl_curr_end_coll_ts;

      IF (EMDW_LOG.p_is_debug_set) THEN
        EMDW_LOG.debug('is_agent_unrch : for agent_guid ' || l_agent_guid ||
          ' curr_agent_stat = ' || l_agt_avl_curr_status, G_MODULE_NAME);
      END IF;

      IF (l_agt_avl_curr_status = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN

        l_agent_unrch := TRUE;

      ELSIF (l_agt_avl_curr_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN

        -- Get the prev avail record..
        FETCH l_agt_avl_cursor INTO l_agt_avl_prev_rowid,
                                    l_agt_avl_prev_status,
                                    l_agt_avl_prev_start_coll_ts,
                                    l_agt_avl_prev_end_coll_ts;

        IF (EMDW_LOG.p_is_debug_set) THEN
          EMDW_LOG.debug('is_agent_unrch : prev_agent_stat = ' || 
            l_agt_avl_prev_status, G_MODULE_NAME);
        END IF;

        IF (l_agt_avl_prev_status = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN
          l_agent_unrch := TRUE;
        ELSE
          l_agent_unrch := FALSE;
        END IF;
      ELSE
        l_agent_unrch := FALSE;
      END IF;

    EXCEPTION
    -- Handle repository only targets 
    WHEN NO_DATA_FOUND THEN
      l_agent_unrch := FALSE;
    WHEN OTHERS THEN
      l_agent_unrch := FALSE;
    END;

    IF l_agt_avl_cursor%ISOPEN THEN
      CLOSE l_agt_avl_cursor;
    END IF;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('is_agent_unreachable:Exit', G_MODULE_NAME);
    END IF;

    RETURN l_agent_unrch;

  END is_agent_unreachable;


  --
  -- PURPOSE 
  --   Utility function to get the agent guid.
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  --
  FUNCTION get_agent_guid(p_target_guid IN RAW)
    RETURN RAW IS
    l_ret_guid MGMT_TARGETS.target_guid%TYPE;
    l_emd_url  MGMT_TARGETS.emd_url%TYPE;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('get_agent_guid:Entry p_target_guid = ' || p_target_guid, G_MODULE_NAME);
    END IF;

    IF (p_target_guid IS NOT NULL) THEN
      SELECT emd_url INTO l_emd_url
        FROM MGMT_TARGETS
       WHERE target_guid = p_target_guid;

      IF (EMDW_LOG.p_is_debug_set) THEN
        EMDW_LOG.debug('get_agent_guid : agent URL = ' || 
           l_emd_url, G_MODULE_NAME);
      END IF;

      SELECT target_guid INTO l_ret_guid
        FROM MGMT_TARGETS
       WHERE emd_url = l_emd_url 
         AND target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE;
    END IF;

    IF (EMDW_LOG.p_is_debug_set) THEN
      EMDW_LOG.debug('get_agent_guid : agent guid = ' || 
           l_ret_guid, G_MODULE_NAME);
    END IF;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('get_agent_guid:Exit agent_guid = ' || l_ret_guid, G_MODULE_NAME);
    END IF;

    RETURN l_ret_guid;

  END get_agent_guid;

  --
  -- PURPOSE 
  --   Utility function to order of the status
  --   for the same timestamp.
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  --
  FUNCTION get_status_order(p_current_status IN NUMBER)
    RETURN INTEGER IS
    l_retVal INTEGER := 0;
  BEGIN

    -- For the same timestamp.. the order is
    --     UNKNOWN 
    --     AGENT_DOWN
    --     UP/DOWN/BLKOUT/METRIC_ERROR
    --     UNREACHABLE
    IF (p_current_status = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN
      l_retVal := 9;
    ELSIF (p_current_status = MGMT_GLOBAL.G_STATUS_AGENT_DOWN) THEN
      l_retVal := -8;
    ELSIF (p_current_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) THEN
      l_retVal := -9;
    END IF;

    RETURN l_retVal;
 END get_status_order;

  --
  -- PURPOSE 
  --   Utility function to get the availability cursor
  --
  FUNCTION get_avail_cursor(p_target_guid IN RAW)
    RETURN AVAIL_CURSOR IS
    l_avail_cursor AVAIL_CURSOR; 
  BEGIN

    OPEN l_avail_cursor FOR
      SELECT ROWID, current_status, start_collection_timestamp, 
             end_collection_timestamp
        FROM MGMT_AVAILABILITY
       WHERE target_guid = p_target_guid
       ORDER BY start_collection_timestamp DESC, 
                NVL(end_collection_timestamp, 
                    TO_DATE('2100-01-01', 'YYYY-MM-DD')) DESC,
                get_status_order(current_status) DESC;

    RETURN l_avail_cursor;

  END get_avail_cursor;
 
  --
  -- PURPOSE 
  --   Utility function to get the availability cursor between markers
  --
  FUNCTION get_avail_cursor_between(p_target_guid  IN RAW,
                                    p_start_marker IN DATE,
                                    p_end_marker   IN DATE)
    RETURN AVAIL_CURSOR IS
    l_avail_cursor AVAIL_CURSOR; 
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('get_avail_cursor_between:Entry p_target_guid = ' 
                     || p_target_guid ||
                     ' p_start_marker = ' || p_start_marker ||
                     ' p_end_marker = '   || p_end_marker,   G_MODULE_NAME);
    END IF;

    OPEN l_avail_cursor FOR
      SELECT ROWID, current_status, start_collection_timestamp, 
             end_collection_timestamp
        FROM MGMT_AVAILABILITY
       WHERE target_guid = p_target_guid
         AND start_collection_timestamp <= p_end_marker
         AND (end_collection_timestamp >= p_start_marker 
              OR end_collection_timestamp IS NULL)
       ORDER BY start_collection_timestamp, 
                NVL(end_collection_timestamp, 
                    TO_DATE('2100-01-01', 'YYYY-MM-DD')),
                get_status_order(current_status);

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('get_avail_cursor_between:Exit', G_MODULE_NAME);
    END IF;

    RETURN l_avail_cursor;

  END get_avail_cursor_between;


  --
  -- PURPOSE 
  --   Utility function to get the severity string
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  --
  FUNCTION get_severity_string(p_severity_code IN NUMBER)
    RETURN VARCHAR2 IS
    l_ret_sev_string VARCHAR2(20) := '**ERROR**';
  BEGIN

    IF (p_severity_code IS NULL) THEN
      NULL; -- Do nothing..

    ELSIF (p_severity_code = MGMT_GLOBAL.G_SEVERITY_COMMENT) THEN
      l_ret_sev_string := SEVERITY_COMMENT_STR;

    ELSIF (p_severity_code = MGMT_GLOBAL.G_SEVERITY_CLEAR) THEN
      l_ret_sev_string := SEVERITY_CLEAR_STR;

    ELSIF (p_severity_code = MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL) THEN
      l_ret_sev_string := SEVERITY_INFORMATIONAL_STR;

    ELSIF (p_severity_code = MGMT_GLOBAL.G_SEVERITY_WARNING) THEN
      l_ret_sev_string := SEVERITY_WARNING_STR;

    ELSIF (p_severity_code = MGMT_GLOBAL.G_SEVERITY_CRITICAL) THEN
      l_ret_sev_string := SEVERITY_CRITICAL_STR;

    ELSIF (p_severity_code = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_CLEAR) THEN
      l_ret_sev_string := SEVERITY_UNREACHABLE_CLEAR_STR;

    ELSIF (p_severity_code = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_START) THEN
      l_ret_sev_string := SEVERITY_UNREACHABLE_START_STR;

    ELSIF (p_severity_code = MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END) THEN
      l_ret_sev_string := SEVERITY_BLACKOUT_END_STR;

    ELSIF (p_severity_code = MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START) THEN
      l_ret_sev_string := SEVERITY_BLACKOUT_START_STR;

    ELSIF (p_severity_code = MGMT_GLOBAL.G_SEVERITY_ERROR_END) THEN
      l_ret_sev_string := SEVERITY_ERROR_END_STR;

    ELSIF (p_severity_code = MGMT_GLOBAL.G_SEVERITY_ERROR_START) THEN
      l_ret_sev_string := SEVERITY_ERROR_START_STR;

    END IF;


    return l_ret_sev_string;

  END get_severity_string;


  --
  -- PURPOSE 
  --   Utility function to get the availability String
  -- 
  -- IN PARAMETERS 
  -- 
  -- OUT PARAMETERS 
  --   None.
  -- 
  -- ERROR CODES
  --   None.
  -- 
  -- NOTES 
  --   This is private routine called only by the severity_trigger
  --
  FUNCTION get_avail_string(p_avail_status IN NUMBER)
    RETURN VARCHAR2 IS
    l_ret_avail_string VARCHAR2(20) := '**ERROR**';
  BEGIN

    IF (p_avail_status IS NULL) THEN
      NULL; -- Do nothing

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_DOWN) THEN
      l_ret_avail_string := AVAIL_DOWN_STR;

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_UP) THEN
      l_ret_avail_string := AVAIL_UP_STR;

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_ERROR) THEN
      l_ret_avail_string := AVAIL_ERROR_STR;

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_AGENT_DOWN) THEN
      l_ret_avail_string := AVAIL_AGENT_DOWN_STR;

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN
      l_ret_avail_string := AVAIL_UNREACHABLE_STR;

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
      l_ret_avail_string := AVAIL_BLACKOUT_STR;

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) THEN
      l_ret_avail_string := AVAIL_UNKNOWN_STR;

    END IF;


    return l_ret_avail_string;

  END get_avail_string;

------------------------------------------------------------------------
 
  PROCEDURE SEVERITY_PURGE(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS) IS
    l_purge_rowids t_rowid_list;

    l_target_guid RAW(16);
    l_policy_guid t_guid_list;
    l_key_value   t_varchar2_256_list;
    l_max_coll    t_date_list;
    l_rec_count   t_number_list;

    l_all_zero_guid RAW(16) := MGMT_GLOBAL.G_ALL_ZERO_GUID ;

    l_rows_purged NUMBER := 0;
    l_count       NUMBER := 0;
    l_start_idx   NUMBER := 1;
    l_end_idx     NUMBER;
    l_batch_size  NUMBER := EMD_LOADER.get_purge_batchsize;

  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('severity_purge:Entry', G_MODULE_NAME);
    END IF;

   l_target_guid := pcb_params.target_guid ;

    SELECT  /*+ INDEX(mgmt_violations MGMT_VIOLATIONS_PK) */
            policy_guid, key_value, max(collection_timestamp) max_coll, 
           COUNT(*) rec_count BULK COLLECT
      INTO l_policy_guid, l_key_value, l_max_coll, l_rec_count
      FROM mgmt_violations
     WHERE target_guid = l_target_guid
       AND policy_guid > l_all_zero_guid
       AND violation_level = MGMT_GLOBAL.G_SEVERITY_CLEAR
       AND collection_timestamp < pcb_params.purge_upto_time
     GROUP BY policy_guid, key_value ;

    FOR i IN 1..l_policy_guid.COUNT LOOP

      l_count := l_count + l_rec_count(i);

      IF l_count >= l_batch_size THEN
        l_end_idx := i;

        FORALL j IN l_start_idx..l_end_idx
        DELETE 
          FROM mgmt_violations 
         WHERE target_guid = HEXTORAW(l_target_guid)
           AND policy_guid = HEXTORAW(l_policy_guid(j))
           AND key_value   = l_key_value(j)
           AND collection_timestamp <= l_max_coll(j) ;

         l_rows_purged := l_rows_purged + SQL%ROWCOUNT;

         COMMIT;
         
         l_start_idx := l_end_idx + 1;
         l_count := 0;
      END IF;

    END LOOP;
         
    l_policy_guid.DELETE;
    l_key_value.DELETE;
    l_max_coll.DELETE;

    pcb_params.callback_result := 0;
    pcb_params.rows_processed  := l_rows_purged;
    pcb_params.error_code      := 0;
    pcb_params.error_msg       := NULL;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('severity_purge:Exit', G_MODULE_NAME);
    END IF;
  END SEVERITY_PURGE;

------------------------------------------------------------------------

  PROCEDURE STATELESS_SEVERITY_CLEAR(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS) IS

    CURSOR stateless_severity_cursor (c_target_guid RAW,
                                      c_purge_till_time DATE) IS
      SELECT tgt.target_name, tgt.target_type, met.metric_name, met.metric_column,
             csev.key_value
        FROM mgmt_current_violation csev, MGMT_TARGETS tgt, MGMT_METRICS met
       WHERE csev.target_guid = c_target_guid
         AND csev.target_guid = tgt.target_guid
	 AND csev.policy_guid = met.metric_guid
	 AND csev.violation_level IN (MGMT_GLOBAL.G_SEVERITY_WARNING,
	                             MGMT_GLOBAL.G_SEVERITY_CRITICAL)
         AND csev.collection_timestamp < c_purge_till_time
         AND met.target_type = tgt.target_type
         AND met.type_meta_ver = tgt.type_meta_ver
         AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ')
         AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ')
         AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ')
         AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ')
         AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ')
	 AND met.statefull = 0;

    l_target_name MGMT_TARGETS.target_name%TYPE;
    l_target_type MGMT_TARGETS.target_type%TYPE;
    l_metric_name MGMT_METRICS.metric_name%TYPE;
    l_metric_column MGMT_METRICS.metric_column%TYPE;
    l_key_value   mgmt_violations.key_value%TYPE;

    l_rows_processed NUMBER := 0;
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('stateless_severity_clear:Entry', G_MODULE_NAME);
    END IF;

    -- TODO: Consider bulk insert of severities
    OPEN stateless_severity_cursor(pcb_params.target_guid, 
                                   pcb_params.purge_upto_time);
    LOOP
      FETCH stateless_severity_cursor INTO l_target_name, l_target_type,
                    l_metric_name, l_metric_column, l_key_value;
      EXIT WHEN stateless_severity_cursor%NOTFOUND;

      l_rows_processed := l_rows_processed + 1;

      MGMT_VIOLATION.log_threshold_violation(
        p_target_type           => l_target_type,
        p_target_name           => l_target_name,
        p_metric_name           => l_metric_name,
        p_metric_column         => l_metric_column,
        p_key_value             => l_key_value,
        p_collection_timestamp  => SYSDATE, -- Should use target timezone here ?
        p_violation_level       => MGMT_GLOBAL.G_SEVERITY_CLEAR,
        p_message               => 'Closing stateless severity.');

    END LOOP;

    -- Close the cursor if open
    IF (stateless_severity_cursor%ISOPEN) THEN
      CLOSE stateless_severity_cursor;
    END IF;

    pcb_params.callback_result := 0;
    pcb_params.rows_processed  := l_rows_processed;
    pcb_params.error_code      := 0;
    pcb_params.error_msg       := NULL;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('stateless_severity_clear:Exit', G_MODULE_NAME);
    END IF;
  END STATELESS_SEVERITY_CLEAR;

------------------------------------------------------------------------

  PROCEDURE AVAILABILITY_PURGE(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS) IS
    l_purge_rowids t_urowid_list ;
    l_rows_purged NUMBER := 0;

    CURSOR availability_cursor (c_target_guid RAW,
                                c_purge_till_time DATE) IS
      SELECT avl.ROWID
        FROM MGMT_AVAILABILITY avl
       WHERE avl.target_guid = c_target_guid
         AND avl.end_collection_timestamp < 
               (SELECT max(end_collection_timestamp)
                  FROM MGMT_AVAILABILITY
                 WHERE target_guid = c_target_guid
	           AND current_status = MGMT_GLOBAL.G_STATUS_UP
	           AND end_collection_timestamp <= c_purge_till_time) ;

  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('availability_purge:Entry', G_MODULE_NAME);
    END IF;

    OPEN availability_cursor(pcb_params.target_guid, pcb_params.purge_upto_time);
    LOOP
      -- Bulk collect row ids
      FETCH availability_cursor BULK COLLECT INTO l_purge_rowids LIMIT 500;

      -- Exit when no more row ids fetched
      EXIT WHEN l_purge_rowids.COUNT <= 0;

      -- BULK delete
      FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST
        DELETE FROM MGMT_AVAILABILITY WHERE ROWID = l_purge_rowids(i);

      COMMIT;

      l_rows_purged := l_rows_purged + l_purge_rowids.COUNT;

      l_purge_rowids.DELETE;

    END LOOP;

    -- Close the cursor if open
    IF (availability_cursor%ISOPEN) THEN
      CLOSE availability_cursor;
    END IF;

    pcb_params.callback_result := 0;
    pcb_params.rows_processed  := l_rows_purged;
    pcb_params.error_code      := 0;
    pcb_params.error_msg       := NULL;

    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('availability_purge:Exit', G_MODULE_NAME);
    END IF;
  END AVAILABILITY_PURGE;

------------------------------------------------------------------------
  -- Procedure for deletion of all violation based on target_guid, metric_guid and key-value
  -- Would be call by callback EM_METRIC.handle_metric_keyval_deletion

  PROCEDURE del_keyval_violation
  (
    p_target_guid  IN mgmt_targets.target_guid%TYPE,
    p_metric_guid  IN mgmt_metrics.metric_guid%TYPE,
    p_key_value    IN mgmt_metrics_raw.key_value%TYPE
  )
  IS
    l_counter    NUMBER ;
    l_org_from_purge_api BOOLEAN;
  BEGIN

    -- Setting purge global variable to TRUE in order to avoid data deletion from trigger 
    --
     l_org_from_purge_api := G_FROM_PURGE_API ;
     G_FROM_PURGE_API := TRUE ; 

    -- Deleting violation record using purge API
    -- End timestamp is set to future date in order to deleting all violations
    

    purge_violations_for_key(p_target_guid       => p_target_guid,
                             p_policy_guid       => p_metric_guid,
                             p_key_value         => p_key_value,
                             p_end_timestamp     => SYSDATE + 1
                            ) ;

     --Reassiging original value
     G_FROM_PURGE_API := l_org_from_purge_api ;

  END del_keyval_violation ;

  -- Procedure for deletion of current violation based on target_guid, metric_guid and key-value
  -- Would be call by callback EM_METRIC.metric_keyval_deletion_sync

  PROCEDURE del_keyval_curr_violation
  (
    p_target_guid  IN mgmt_targets.target_guid%TYPE,
    p_metric_guid  IN mgmt_metrics.metric_guid%TYPE,
    p_key_value    IN mgmt_metrics_raw.key_value%TYPE
  )
  IS
  BEGIN

    DELETE
      FROM mgmt_current_violation
     WHERE target_guid = p_target_guid
       AND policy_guid = p_metric_guid
       AND key_value   = p_key_value
       AND violation_type <> MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY ;

  END del_keyval_curr_violation;

 FUNCTION get_avail_string_nls(p_avail_status IN NUMBER)
    RETURN VARCHAR2 IS
    l_ret_avail_string mgmt_violations.message_nlsid%TYPE := '**ERROR**';
  BEGIN

    IF (p_avail_status IS NULL) THEN
      NULL; -- Do nothing

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_DOWN) THEN
      l_ret_avail_string :=  'MntrResourceBundleID.DOWN_STATUS' ;

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_UP) THEN
      l_ret_avail_string := 'MntrResourceBundleID.UP_STATUS' ;

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_ERROR) THEN
      l_ret_avail_string := 'MntrResourceBundleID.ERROR_STATUS' ;

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_AGENT_DOWN) THEN
      l_ret_avail_string := 'MntrResourceBundleID.AGENT_DOWN_STATUS' ;

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN
      l_ret_avail_string := 'MntrResourceBundleID.UNREACHABLE_STATUS' ;

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
      l_ret_avail_string := 'MntrResourceBundleID.BLACKED_OUT' ;

    ELSIF (p_avail_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) THEN
      l_ret_avail_string := 'MntrResourceBundleID.UNKNOWN_AVAILABILITY' ;

    END IF;


    return l_ret_avail_string;

  END get_avail_string_nls ;

  -- Internal procedure. Not to be called from outside except
  -- EM_POLICY.delete_policy_data.
  -- This procedure deletes all violations related record from other tables
  -- based on I/P violation guid collection.

  PROCEDURE purge_for_violation_guids
  (
    p_violation_guids    IN  MGMT_TARGET_GUID_ARRAY,
    p_annot_purged       OUT NUMBER,
    p_notify_log_purged  OUT NUMBER,
    p_notify_rq          OUT NUMBER
  ) 
  IS
  BEGIN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('purge_for_violation_guids:Start', G_MODULE_NAME);
    END IF;

    -- Delete all annotations for the severities
    FORALL i IN p_violation_guids.FIRST..p_violation_guids.LAST
      DELETE FROM MGMT_ANNOTATION
       WHERE source_obj_type = mgmt_global.G_ANNOTATION_SOURCE_SEVERITY
         AND source_obj_guid =  p_violation_guids(i)  ;
    
    p_annot_purged    := SQL%ROWCOUNT ;
  
    -- Delete notification history for the severities
    FORALL i IN p_violation_guids.FIRST..p_violation_guids.LAST
      DELETE FROM MGMT_NOTIFICATION_LOG l
       WHERE l.source_obj_type = mgmt_global.G_ANNOTATION_SOURCE_SEVERITY
         AND l.source_obj_guid = p_violation_guids(i) ;

    p_notify_log_purged  := SQL%ROWCOUNT ;

    -- Delete notify requeues for the severities 
    FORALL i IN p_violation_guids.FIRST..p_violation_guids.LAST
      DELETE FROM MGMT_NOTIFY_REQUEUE l
       WHERE l.source_type  IN ( EMD_NOTIFICATION.POLICY_VIOLATION,
                                 EMD_NOTIFICATION.METRIC_SEVERITY)
         AND l.source_guid = p_violation_guids(i) ;

    p_notify_rq      := SQL%ROWCOUNT ; 

 IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('purge_for_violation_guids:Exit', G_MODULE_NAME);
    END IF;

  END; 

  --wrapper function to get the OUT variable as NUMBER
  FUNCTION is_sev_in_blackout_wrap(p_target_guid IN RAW,
                              p_coll_ts     IN DATE)
  RETURN NUMBER
  IS
    l_out NUMBER := 0;
  BEGIN

    IF is_sev_in_blackout (p_target_guid, p_coll_ts ) THEN
      l_out := 1;
    ELSE
      l_out := 0;
    END IF;

    RETURN l_out;
  
  END is_sev_in_blackout_wrap;

 -- execute the callback registered for crs_event metric cluster target type. 
 PROCEDURE exec_crs_event_callbacks(p_violation IN MGMT_VIOLATION_SUMMARY_REC,
                                    p_save_violation OUT NUMBER)
 IS
   l_current_user mgmt_targets.owner%TYPE := MGMT_USER.get_current_em_user();
   l_callback_name VARCHAR2(32);
 BEGIN
   BEGIN 
     SELECT callback_name
       INTO l_callback_name
       FROM mgmt_callbacks
      WHERE callback_type = MGMT_GLOBAL.G_CLUSTER_CRS_EVENT_CALLBACK;

     -- Enter super-user mode. This is necessary because some callbacks 
     -- involves making calls to the security system that only super-users
     -- are allowed to make
     SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER,
                      MGMT_USER.OP_SET_IDENTIFIER);

     EXECUTE IMMEDIATE
       'BEGIN ' || EM_CHECK.qualified_sql_name(l_callback_name) || 
                  '(:1, :2); END; ' USING
          p_violation, out p_save_violation;

   EXCEPTION
     -- Ignore exceptions so that main transaction is not affected; we do not
     -- treat callback exceptions on par with repository exceptions. In other
     -- words, we will not let some callback mess up repository operations.
     WHEN OTHERS THEN
       mgmt_log.log_error(v_module_name_in => MODULE,
                          v_error_code_in  =>  0,
                          v_error_msg_in   => SUBSTR('Exception while executing callback proc ' || 
                                                 l_callback_name || ' : ' || SQLERRM, 1, 2000));
  
   END;
   -- Revert back to being the same user as we entered
   SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
  END exec_crs_event_callbacks;                

  -- Private to severity. To be called from severity trigger ONLY
  -- Applicable only for beacon managed target
  --
  FUNCTION viol_matches_pre_state
  (
    p_target_guid mgmt_violations.target_guid%type,
    p_policy_guid mgmt_violations.policy_guid%type,
    p_key_value mgmt_violations.key_value%type,
    p_new_viol_level NUMBER
  ) RETURN BOOLEAN
  IS
    CURSOR c1 
    IS
    SELECT violation_level
      FROM mgmt_violations
     WHERE target_guid = p_target_guid
       AND policy_guid = p_policy_guid
       AND key_value = p_key_value
       AND violation_level != MGMT_GLOBAL.G_SEVERITY_COMMENT
     ORDER BY collection_timestamp DESC, load_timestamp DESC ,
              DECODE(violation_level,
                      MGMT_GLOBAL.G_SEVERITY_CLEAR,          1,
                      MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,  2,
                      MGMT_GLOBAL.G_SEVERITY_WARNING,        3,
                      MGMT_GLOBAL.G_SEVERITY_CRITICAL,       4,
                      MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START, 5,
                      MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END,   6,
                                                             9);
    l_state_matches BOOLEAN := FALSE;
    l_violation_level NUMBER := -1 ;
  BEGIN

    OPEN C1;
    Fetch c1 INTO l_violation_level;

    WHILE (l_violation_level IN (MGMT_GLOBAL.G_SEVERITY_NO_BEACONS,
                                 MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START,
                                 MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END))
    LOOP
      EXIT WHEN c1%NOTFOUND ;
      Fetch c1 INTO l_violation_level;
    END LOOP;

    IF l_violation_level = p_new_viol_level THEN
       l_state_matches := TRUE;
    ELSE
       l_state_matches := FALSE;
    END IF;

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

    RETURN l_state_matches ;

  END viol_matches_pre_state ;

END EM_SEVERITY;
/
show errors;