Rem
Rem $Header: gensvc_avail_pkgbody.sql 12-mar-2007.09:46:33 thsu Exp $
Rem
Rem gensvc_avail_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      gensvc_avail_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    thsu        03/12/07 - Backport thsu_bug-5151843 from main
Rem    thsu        02/23/07 - bug 5151843, change the log level for
Rem                           test_response/status metrics.
Rem    mfidanbo    04/18/06 - fix bug 5169942 
Rem    mfidanbo    07/11/06 - Backport mfidanbo_bug-5169942 from main 
Rem    mfidanbo    12/12/05 - Backport mfidanbo_bug-4739187 from main 
Rem    mfidanbo    12/05/05 - pass nls_id for the message 
Rem    snakai      12/12/05 - Backport snakai_bug-4659126 from main 
Rem    snakai      12/06/05 - test blackout state must match its targets 
Rem    snakai      09/21/05 - use blackout windows 
Rem    mfidanbo    09/15/05 - clear status alerts 
Rem    snakai      08/31/05 - validate timestamps 
Rem    snakai      08/28/05 - eliminate large volume of system errors 
Rem    mfidanbo    08/25/05 - fix OCS break
Rem    mfidanbo    08/17/05 - insert severity when txn_status is updated 
Rem    mfidanbo    07/27/05 - move avail markers faster 
Rem    snakai      07/28/05 - ignore blacked out beacons 
Rem    snakai      07/22/05 - ignore beacons with older av markers 
Rem    scgrover    07/07/05 - add extended sql trace 
Rem    mfidanbo    06/24/05 - change discard_state_job 
Rem    snakai      06/14/05 - add severity msg 
Rem    mfidanbo    05/25/05 - deregister avail api 
Rem    mfidanbo    06/06/05 - fix coll_name 
Rem    mfidanbo    05/12/05 - change test avail algorithm 
Rem    andyao      04/20/05 - change the sleep time 
Rem                           from k_lo_pri_job_sleep to k_hi_pri_job_sleep at start time 
Rem    snakai      04/15/05 - update marker after blackout only if avail 
Rem                           enabled 
Rem    snakai      04/11/05 - optimize svc avail computation 
Rem    snakai      01/03/05 - fix disable_avail 
Rem    snakai      12/01/04 - temp reschedule fix 
Rem    rmarripa    11/29/04 - add GET_SVC_EVAL_LOGIC 
Rem    snakai      11/24/04 - snakai_mta_2
Rem    snakai      11/11/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY MGMT_GENSVC_AVAIL AS

--------------------------------------------------------------------------
-- Forward Definitions
--------------------------------------------------------------------------
PROCEDURE SCHEDULE_JOB ( p_target_guid IN RAW, 
                         p_test_guid IN RAW, 
                         p_period_key IN NUMBER);
--------------------------------------------------------------------------
PROCEDURE RESCHEDULE_JOB ( p_target_guid IN RAW, 
                           p_test_guid IN RAW, 
                           p_period_key IN NUMBER,
                           p_in_error IN NUMBER );
--------------------------------------------------------------------------
PROCEDURE UNSCHEDULE_JOB ( p_target_guid IN RAW, 
                           p_test_guid IN RAW );
--------------------------------------------------------------------------
PROCEDURE REMOVE_TEST ( p_target_guid IN RAW, 
                        p_test_guid IN RAW );
--------------------------------------------------------------------------
PROCEDURE HANDLE_TEST_AVAIL_EVENT( p_target_guid IN RAW, 
                                   p_test_guid IN RAW, 
                                   p_new_status IN NUMBER,
                                   p_event_ts IN DATE );
--------------------------------------------------------------------------
PROCEDURE UPDATE_TEST_AVAIL( p_target_guid IN RAW, 
                             p_test_guid IN RAW, 
                             p_new_status IN NUMBER,
                             p_new_start_ts IN DATE, 
                             p_new_avmarker IN DATE,
                             p_check_blackout IN BOOLEAN,
                             p_avail_updated OUT BOOLEAN );
--------------------------------------------------------------------------
/*
PROCEDURE CHECK_TARGET_SCHEDULE( p_target_guid IN RAW );
*/
--------------------------------------------------------------------------
PROCEDURE GET_ERROR_METRIC ( p_metric_guid IN RAW, 
                             p_err_metric_guid OUT RAW );
--------------------------------------------------------------------------
FUNCTION HAS_AVAIL_DEFINTION ( p_target_guid IN RAW )
RETURN BOOLEAN;
--------------------------------------------------------------------------
FUNCTION LOCK_TEST( p_target_guid IN RAW, p_test_guid IN RAW,
                    p_next_run OUT DATE, p_in_error OUT NUMBER) 
RETURN NUMBER;
--------------------------------------------------------------------------
FUNCTION LOCK_TARGET( p_target_guid IN RAW,
                      p_next_run OUT DATE, p_in_error OUT NUMBER) 
RETURN NUMBER;
--------------------------------------------------------------------------
-- Return true if the target is blacked out for the entire period
-- [start_ts, end_ts].
-- Returns false if a leading segment (possibly the entire period)
-- is not in blackout.  In this case, new_end_ts is set to the end
-- of the non-blacked out period (new_end_ts == end_ts when the
-- entire period does not fall within a blackout period).
FUNCTION IS_IN_BLACKOUT( p_target_guid IN RAW,
                         p_start_ts IN DATE,
                         p_end_ts IN DATE,
                         p_new_end_ts OUT DATE ) RETURN BOOLEAN;
--------------------------------------------------------------------------
FUNCTION IS_IN_BLACKOUT( p_target_guid IN RAW,
                         p_timestamp IN DATE ) RETURN BOOLEAN;
--------------------------------------------------------------------------
PROCEDURE GET_PERIODS;
--------------------------------------------------------------------------
FUNCTION GET_RESCHEDULE_PERIOD(p_period_key IN NUMBER)
RETURN NUMBER;
--------------------------------------------------------------------------
-- Trace Message
PROCEDURE TRACE_MSG(p_msg IN OUT VARCHAR2, p_add IN VARCHAR2);

PROCEDURE LOG_STATUS_SEVERITY(p_target_guid IN RAW,
                              p_test_guid IN RAW,
                              p_new_status IN NUMBER,
                              p_severity_ts IN DATE);

PROCEDURE CLEAR_STATUS_SEVERITY( p_target_guid IN RAW,
                                 p_test_guid   IN RAW,
                                 p_msg         IN VARCHAR2);


--------------------------------------------------------------------------
-- Public Methods
--------------------------------------------------------------------------

PROCEDURE SET_BEACONS( p_target_name IN VARCHAR2,
                       p_target_type IN VARCHAR2,
                       p_beacon_list IN MGMT_GENSVC_TGT_NAME_ARRAY )

IS

  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_beacon_guid MGMT_TARGETS.target_guid%TYPE;

BEGIN

  -- get the target guid
  SELECT target_guid 
    INTO l_target_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  BEGIN
    -- clear the list of beacons
    DELETE FROM MGMT_GENSVC_AVAIL_BEACONS
     WHERE target_guid = l_target_guid;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;

    -- add the new beacons
  IF (p_beacon_list IS NOT NULL) AND (p_beacon_list.COUNT > 0) THEN
    FOR l_bcn_idx IN 1..p_beacon_list.COUNT LOOP
      -- get the beacon guid
      SELECT target_guid
        INTO l_beacon_guid
        FROM MGMT_TARGETS
       WHERE target_name = p_beacon_list(l_bcn_idx)
         AND target_type = EMD_BCNTXN.p_beacon_type;
      -- add the beacon
      INSERT INTO MGMT_GENSVC_AVAIL_BEACONS
        ( target_guid, beacon_target_guid )
      VALUES 
        ( l_target_guid, l_beacon_guid );
    END LOOP;
  END IF;

END SET_BEACONS;

--------------------------------------------------------------------------
PROCEDURE SET_TESTS ( p_target_name IN VARCHAR2,
                      p_target_type IN VARCHAR2,
                      p_test_list IN MGMT_GENSVC_AV_TEST_ARRAY )
IS

  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_test_guid MGMT_GENSVC_AVAIL_TESTS.test_guid%TYPE;
  l_new_tests MGMT_GENSVC_GUID_ARRAY;
  l_new_test_count NUMBER;
  l_more BOOLEAN;

  CURSOR l_delete_tests IS
    SELECT test_guid 
      FROM MGMT_GENSVC_AVAIL_TESTS
     WHERE target_guid = l_target_guid
       AND test_guid NOT IN 
             ( SELECT * 
                 FROM TABLE(CAST(l_new_tests AS MGMT_GENSVC_GUID_ARRAY)) );

  CURSOR l_all_tests IS
    SELECT test_guid 
      FROM MGMT_GENSVC_AVAIL_TESTS
     WHERE target_guid = l_target_guid;

BEGIN

  -- get the target guid
  SELECT target_guid 
    INTO l_target_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  -- add the new tests
  IF (p_test_list IS NOT NULL) AND (p_test_list.COUNT > 0) THEN
    FOR l_test_idx IN 1..p_test_list.COUNT LOOP
      ADD_TEST(p_target_name, p_target_type, p_test_list(l_test_idx));
    END LOOP;
  END IF;

  -- delete tests not included in the new list      
  IF (p_test_list IS NULL) OR (p_test_list.COUNT <= 0) THEN
    -- new list is empty, delete all tests
    BEGIN
      OPEN l_all_tests;
      l_more := TRUE;
      WHILE l_more LOOP
        FETCH l_all_tests INTO l_test_guid;
        l_more := l_all_tests%FOUND;
        IF l_more THEN
          REMOVE_TEST(l_target_guid, l_test_guid);
        END IF;
      END LOOP;
      CLOSE l_all_tests;
    EXCEPTION
      WHEN OTHERS THEN
        IF l_all_tests%ISOPEN THEN
          CLOSE l_all_tests;
        END IF;
        RAISE;
    END;
  ELSE
    -- delete only the tests that are not in the new list
    -- first build a list of the test guids
    FOR l_test_idx IN 1..p_test_list.COUNT LOOP
      -- get the test guid
      SELECT test_guid
        INTO l_test_guid
        FROM MGMT_GENSVC_AVAIL_TESTS
       WHERE target_guid = l_target_guid
         AND test_name = p_test_list(l_test_idx).test_name
         AND test_type = p_test_list(l_test_idx).test_type;
      -- add the test to the list      
      IF l_test_idx = 1 THEN
        l_new_tests := MGMT_GENSVC_GUID_ARRAY();
      END IF;
      l_new_tests.EXTEND(1);
      l_new_tests(l_test_idx) := l_test_guid;
    END LOOP;
    -- delete the tests
    BEGIN
      OPEN l_delete_tests;
      l_more := TRUE;
      WHILE l_more LOOP
        FETCH l_delete_tests INTO l_test_guid;
          l_more := l_delete_tests%FOUND;
          IF l_more THEN
            REMOVE_TEST(l_target_guid, l_test_guid);
          END IF;
      END LOOP;
      CLOSE l_delete_tests;
    EXCEPTION
      WHEN OTHERS THEN
        IF l_delete_tests%ISOPEN THEN
          CLOSE l_delete_tests;
        END IF;
        RAISE;
    END;
  END IF;

END SET_TESTS;

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

/*
PROCEDURE SET_TEST_STATUS_THRESH(tgt_name   IN VARCHAR2,
                                 tgt_type   IN VARCHAR2,
                                 test_name  IN VARCHAR2, 
                                 test_type  IN VARCHAR2)

IS

  l_policy_key_val_list MGMT_POLICY_KEY_VAL_ARRAY := NULL;

BEGIN

  l_policy_key_val_list := MGMT_POLICY_KEY_VAL_ARRAY();
  l_policy_key_val_list.extend;

  l_policy_key_val_list(1) := 
      MGMT_POLICY_KEY_VAL.NEW(
        p_key_value => MGMT_POLICY_KEY_COL_COND_ARRAY(
          MGMT_POLICY_KEY_COL_COND.NEW(
            p_key_value => test_name,
            p_has_wildcard => MGMT_GLOBAL.G_FALSE
            --p_key_column_name is not required for single key metrics
          )
        ),
        p_param_values => MGMT_POLICY_PARAM_VAL_ARRAY(
          MGMT_POLICY_PARAM_VAL.NEW(
            p_param_name => ' ',
            p_crit_threshold => '0')
         ),
        p_condition_operator => MGMT_GLOBAL.G_THRESHOLD_EQ
      );

    mgmt_monitoring.save_target_metric_config( 
          tgt_type, 
          tgt_name, 
          MGMT_GLOBAL.G_AVAIL_TEST_METRIC_NAME, 
          MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN,  
          test_name, 0, l_policy_key_val_list ); 
            
END SET_TEST_STATUS_THRESH;
*/

--------------------------------------------------------------------------
PROCEDURE ADD_TEST ( p_target_name IN VARCHAR2,
                     p_target_type IN VARCHAR2,
                     p_test IN MGMT_GENSVC_AV_TEST )
IS

  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_target_tz MGMT_TARGETS.timezone_region%TYPE;
  l_test_guid MGMT_BCN_TXN_DEFN.txn_guid%TYPE;
  l_metric_guid MGMT_METRICS.metric_guid%TYPE;
  l_avail_test MGMT_GENSVC_AVAIL_TESTS.avail_test%TYPE;
  l_monit_status MGMT_GENSVC_AVAIL_TESTS.monit_status%TYPE;
  l_test_rowid ROWID;
  l_start_time DATE;

BEGIN  

  -- get the target guid
  SELECT target_guid, timezone_region
    INTO l_target_guid, l_target_tz
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  -- get the test guid
  SELECT txn_guid
    INTO l_test_guid
    FROM MGMT_BCN_TXN_DEFN
   WHERE target_guid = l_target_guid
     AND name = p_test.test_name
     AND txn_type = p_test.test_type;

  -- validate the avail_test and monit_status values
  IF (p_test.avail_test IS NULL) OR (p_test.avail_test = 0) THEN
    l_avail_test := 0;
  ELSE
    l_avail_test := 1;
  END IF;
  IF (p_test.monit_status IS NULL) OR (p_test.monit_status = 0) THEN
    l_monit_status := 0;
  ELSE
    l_monit_status := 1;
  END IF;

  -- get the metric guid
  SELECT m.metric_guid
    INTO l_metric_guid
    FROM MGMT_METRICS m, MGMT_TARGETS t
   WHERE t.target_guid = l_target_guid
     AND m.metric_name = p_test.metric_name
     AND m.metric_column = p_test.metric_column
     AND t.target_type = m.target_type
     AND t.type_meta_ver = m.type_meta_ver
     AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ')
     AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ')
     AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ')
     AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ')
     AND (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ');

  -- make sure it's a new test
  BEGIN
    SELECT ROWID
      INTO l_test_rowid
      FROM MGMT_GENSVC_AVAIL_TESTS
     WHERE target_guid = l_target_guid
       AND test_guid = l_test_guid;
    -- the test already exists, update it
    UPDATE MGMT_GENSVC_AVAIL_TESTS
       SET metric_guid = l_metric_guid,
           avail_test = l_avail_test,
           monit_status = l_monit_status
     WHERE ROWID = l_test_rowid;
    -- done
    RETURN;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- it's a new test
      NULL;
  END;

  -- add the test
  INSERT INTO MGMT_GENSVC_AVAIL_TESTS
    ( target_guid, test_guid, test_name, test_type, metric_guid, 
      avail_test, monit_status )
  VALUES
    ( l_target_guid, l_test_guid, p_test.test_name, p_test.test_type, 
      l_metric_guid, l_avail_test, l_monit_status );

  -- add initial records in the test avail tables
  l_start_time := TRUNC( (MGMT_GLOBAL.SYSDATE_TZRGN(l_target_tz) - 32), 'DD');
  INSERT INTO MGMT_GENSVC_TEST_AVAIL
    ( target_guid, test_guid, current_status, start_collection_timestamp )
  VALUES
    ( l_target_guid, l_test_guid, MGMT_GLOBAL.G_STATUS_UNKNOWN, l_start_time);
  INSERT INTO MGMT_GENSVC_TEST_CUR_AVAIL
    ( target_guid, test_guid, current_status, start_collection_timestamp )
  VALUES
    ( l_target_guid, l_test_guid, MGMT_GLOBAL.G_STATUS_UNKNOWN, l_start_time);
  INSERT INTO MGMT_GENSVC_TEST_AVAIL_MARKER
    ( target_guid, test_guid, marker_timestamp, marker_avail_status )
  VALUES
    ( l_target_guid, l_test_guid, l_start_time, MGMT_GLOBAL.G_STATUS_UNKNOWN );

  -- add it to the avail job queue
  IF l_monit_status = 1 THEN
    SCHEDULE_JOB( l_target_guid, l_test_guid, init_job_sleep_key);
  END IF;

  -- set the threshold for the test-status metric
  BEGIN
    em_rep_metric.add_dummy_collection(p_target_name,
                                       p_target_type,
                                       MGMT_GLOBAL.G_AVAIL_TEST_METRIC_NAME,
                                       MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN,
                                       p_test.test_name);
    EXCEPTION
      WHEN NO_DATA_FOUND OR DUP_VAL_ON_INDEX OR mgmt_global.key_already_exists THEN
         -- Ingore duplicate entries
        NULL;
      WHEN OTHERS THEN -- if cannot create, then metric is not defined
        MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 
            'Could not create the test_status collection for target: ' ||
             p_target_name || ' and Test: '|| p_test.test_name ||'. Error: ' || SQLERRM,
             v_log_level_in => MGMT_GLOBAL.G_WARN);
  END;

/*  SET_TEST_STATUS_THRESH(p_target_name,
                         p_target_type,
                         p_test.test_name,
                         p_test.test_type);
*/

END ADD_TEST;

--------------------------------------------------------------------------
PROCEDURE REMOVE_TEST ( p_target_name IN VARCHAR2,
                        p_target_type IN VARCHAR2,
                        p_test IN MGMT_GENSVC_AV_TEST )
IS

  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_test_guid MGMT_GENSVC_AVAIL_TESTS.test_guid%TYPE;

BEGIN  

  -- get the target guid
  SELECT target_guid 
    INTO l_target_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  -- get the test guid
  SELECT test_guid
    INTO l_test_guid
    FROM MGMT_GENSVC_AVAIL_TESTS
   WHERE target_guid = l_target_guid 
     AND test_name = p_test.test_name
     AND test_type = p_test.test_type;

  -- remove the test
  REMOVE_TEST(l_target_guid, l_test_guid);

END REMOVE_TEST;

--------------------------------------------------------------------------
PROCEDURE SET_SVC_EVAL_LOGIC( p_target_name IN VARCHAR2,
                              p_target_type IN VARCHAR2,
                              p_eval_logic IN NUMBER )
IS

  l_target_guid MGMT_TARGETS.target_guid%TYPE;

BEGIN
  
  -- validate params
  IF (p_eval_logic <> k_or_eval) AND (p_eval_logic <> k_and_eval) THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
                            'Invalid parameters: Unknown Eval Logic');    
  END IF;

  -- get the target guid
  SELECT target_guid 
    INTO l_target_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  -- insert/update the target's eval logic
  BEGIN
    INSERT INTO MGMT_GENSVC_AVAIL_CONFIG
      ( target_guid, eval_logic )
    VALUES
      ( l_target_guid, p_eval_logic );
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      UPDATE MGMT_GENSVC_AVAIL_CONFIG
         SET eval_logic = p_eval_logic
       WHERE target_guid = l_target_guid;
  END;

END SET_SVC_EVAL_LOGIC;

--------------------------------------------------------------------------
--k_or_eval 
--k_and_eval   
-- -1 for no availability registration
FUNCTION GET_SVC_EVAL_LOGIC( p_target_name IN VARCHAR2,
                              p_target_type IN VARCHAR2) RETURN NUMBER
IS
  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_avail_enabled  NUMBER;
  l_eval_logic     NUMBER;
BEGIN

  -- get the target guid
  l_target_guid := mgmt_target.get_target_guid ( p_target_name, p_target_type);
  BEGIN
    SELECT avail_enabled, eval_logic 
      INTO l_avail_enabled, l_eval_logic
      FROM MGMT_GENSVC_AVAIL_CONFIG
     WHERE target_guid = l_target_guid;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
      RETURN -1;
  END;
  IF ( l_avail_enabled = 0 ) THEN
    RETURN -1;
  END IF;
  return l_eval_logic;

END GET_SVC_EVAL_LOGIC;

--------------------------------------------------------------------------
PROCEDURE ENABLE_AVAIL( p_target_name IN VARCHAR2,
                        p_target_type IN VARCHAR2 )
IS

  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_num_avail_states NUMBER;
  l_next_period NUMBER;
  CURSOR l_avail_test_list IS
    SELECT t.test_guid 
      FROM MGMT_GENSVC_AVAIL_TESTS t
     WHERE t.target_guid = l_target_guid
       AND t.monit_status <> 0
       AND t.avail_test <> 0;

BEGIN

  -- get the target guid
  SELECT target_guid 
    INTO l_target_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  -- enable availability on the svc target (default OR LOGIC)
  BEGIN
    INSERT INTO MGMT_GENSVC_AVAIL_CONFIG
      ( target_guid, avail_enabled )
    VALUES
      ( l_target_guid, 1 );
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      UPDATE MGMT_GENSVC_AVAIL_CONFIG
         SET avail_enabled = 1
       WHERE target_guid = l_target_guid;
  END;

  -- is this the create mode or edit mode

  SELECT COUNT(*) INTO l_num_avail_states
    FROM mgmt_gensvc_avail_job
   WHERE target_guid = l_target_guid
     AND test_guid = k_no_test;

  IF ( l_num_avail_states > 0) THEN
    l_next_period := min_job_sleep_key;
  ELSE
    l_next_period := init_job_sleep_key;
  END IF;

  -- schedule each test for avail computation
  FOR subrec IN l_avail_test_list 
  LOOP
    schedule_job(l_target_guid, subrec.test_guid, l_next_period);
  END LOOP;

  -- schedule the target for avail computation
  SCHEDULE_JOB ( l_target_guid, NULL, l_next_period);
  
END ENABLE_AVAIL;

--------------------------------------------------------------------------
PROCEDURE ENABLE_AVAIL( p_target_name IN VARCHAR2,
                        p_target_type IN VARCHAR2,
                        p_test_list IN MGMT_GENSVC_AV_TEST_ARRAY,
                        p_beacon_list IN MGMT_GENSVC_TGT_NAME_ARRAY,
                        p_eval_logic IN NUMBER)
IS

BEGIN

  -- load schedule periods
  GET_PERIODS;
  SET_TESTS( p_target_name, p_target_type, p_test_list );
  SET_BEACONS( p_target_name, p_target_type, p_beacon_list );
  SET_SVC_EVAL_LOGIC( p_target_name, p_target_type, p_eval_logic );
  ENABLE_AVAIL( p_target_name, p_target_type );

END ENABLE_AVAIL;

--------------------------------------------------------------------------
PROCEDURE DEREGISTER_AVAIL( p_target_name IN VARCHAR2,
                            p_target_type IN VARCHAR2 )
IS
  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_tmp_guid MGMT_TARGETS.target_guid%TYPE;

  CURSOR l_all_tests IS
    SELECT test_guid 
      FROM MGMT_GENSVC_AVAIL_TESTS
     WHERE target_guid = l_target_guid;

BEGIN

  -- get the target guid
  SELECT target_guid 
    INTO l_target_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  FOR subrec IN l_all_tests LOOP
    REMOVE_TEST(l_target_guid, subrec.test_guid);
  END LOOP;

  DISABLE_AVAIL(p_target_name, p_target_type);

EXCEPTION WHEN NO_DATA_FOUND THEN
  NULL;

END DEREGISTER_AVAIL;

--------------------------------------------------------------------------
PROCEDURE DISABLE_AVAIL( p_target_name IN VARCHAR2,
                         p_target_type IN VARCHAR2 )
IS

  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_tmp_guid MGMT_TARGETS.target_guid%TYPE;

BEGIN

  -- get the target guid
  SELECT target_guid 
    INTO l_target_guid
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  -- lock the svc/test to be processed
  BEGIN
    SELECT target_guid
      INTO l_tmp_guid
      FROM MGMT_GENSVC_AVAIL_JOB
     WHERE target_guid = l_target_guid
       AND test_guid = k_no_test
       FOR UPDATE NOWAIT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;

  -- disable availability on the svc target
  BEGIN
    -- update the config table
    UPDATE MGMT_GENSVC_AVAIL_CONFIG
       SET avail_enabled = 0
     WHERE target_guid = l_target_guid;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;

  -- unschedule the target for avail computation
  BEGIN
    UNSCHEDULE_JOB ( l_target_guid, NULL );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;

END DISABLE_AVAIL;

--------------------------------------------------------------------------
FUNCTION IS_AVAIL_ENABLED( p_target_guid IN RAW )
RETURN BOOLEAN IS

  l_count NUMBER;

BEGIN

  SELECT COUNT(*)
    INTO l_count
    FROM MGMT_GENSVC_AVAIL_CONFIG
   WHERE target_guid = p_target_guid
     AND avail_enabled = 1;
 
  IF l_count > 0 THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

END IS_AVAIL_ENABLED;

--------------------------------------------------------------------------
FUNCTION IS_AVAIL_BEACON( p_target_guid IN RAW, p_beacon_guid IN RAW )
RETURN BOOLEAN IS

  l_count NUMBER;

BEGIN

  SELECT COUNT(*)
    INTO l_count
    FROM MGMT_GENSVC_AVAIL_BEACONS
   WHERE target_guid = p_target_guid
     AND beacon_target_guid = p_beacon_guid;
 
  IF l_count > 0 THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
  
END IS_AVAIL_BEACON;

--------------------------------------------------------------------------
FUNCTION IS_BEACON( p_target_guid IN RAW )
RETURN BOOLEAN IS

  l_target_name MGMT_TARGETS.target_name%TYPE;

BEGIN

  IF p_target_guid IS NULL THEN
    RETURN FALSE;
  END IF;

  SELECT target_name
    INTO l_target_name
    FROM MGMT_TARGETS
   WHERE target_guid = p_target_guid
     AND target_type = EMD_BCNTXN.p_beacon_type;

  RETURN TRUE;

EXCEPTION

  WHEN OTHERS THEN
    RETURN FALSE;

END IS_BEACON;

--------------------------------------------------------------------------
FUNCTION IS_TARGET_AVAIL_VIOL( p_target_guid IN RAW,
                               p_metric_guid IN RAW )
RETURN BOOLEAN IS

  l_target_type  MGMT_TARGETS.target_type%TYPE;
  l_avail_metric MGMT_METRICS.metric_guid%TYPE;

BEGIN

  IF NOT IS_AVAIL_ENABLED(p_target_guid) THEN
    RETURN FALSE;
  END IF;
 
  SELECT target_type
    INTO l_target_type 
    FROM MGMT_TARGETS
   WHERE target_guid = p_target_guid;

  l_avail_metric := mgmt_target.get_metric_guid( l_target_type,
                                                 MGMT_GLOBAL.G_AVAIL_METRIC_NAME,
                                                 MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN );
  IF l_avail_metric = p_metric_guid THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;

END IS_TARGET_AVAIL_VIOL;

--------------------------------------------------------------------------
FUNCTION IS_TEST_AVAIL_VIOL( p_target_guid IN RAW,
                             p_metric_guid IN RAW,
                             p_key_value IN VARCHAR2 )
RETURN BOOLEAN IS

  l_test_name  MGMT_METRICS_COMPOSITE_KEYS.key_part1_value%TYPE;
  l_bcn_name   MGMT_METRICS_COMPOSITE_KEYS.key_part2_value%TYPE;
  l_bcn_guid   MGMT_TARGETS.target_guid%TYPE;
  l_count      NUMBER;

BEGIN
  
  SELECT key_part1_value, key_part2_value
    INTO l_test_name, l_bcn_name
    FROM MGMT_METRICS_COMPOSITE_KEYS
   WHERE target_guid = p_target_guid
     AND composite_key = HEXTORAW(p_key_value);

  SELECT target_guid
    INTO l_bcn_guid
    FROM MGMT_TARGETS
   WHERE target_name = l_bcn_name
     AND target_type = EMD_BCNTXN.p_beacon_type;

  IF NOT IS_AVAIL_BEACON(p_target_guid, l_bcn_guid) THEN
    RETURN FALSE;
  END IF;

  SELECT COUNT(*)
    INTO l_count
    FROM MGMT_GENSVC_AVAIL_TESTS
   WHERE target_guid = p_target_guid
     AND metric_guid = p_metric_guid
     AND test_name = l_test_name;

  IF l_count > 0 THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
  
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;

END IS_TEST_AVAIL_VIOL;

--------------------------------------------------------------------------
PROCEDURE SCHEDULE_TARGET_AVAIL( p_target_guid IN RAW )
IS

BEGIN

  GET_PERIODS;
  IF p_target_guid IS NOT NULL THEN
    RESCHEDULE_JOB( p_target_guid, NULL, min_job_sleep_key,NULL );
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    NULL;

END SCHEDULE_TARGET_AVAIL;

--------------------------------------------------------------------------
PROCEDURE SCHEDULE_TEST_AVAIL( p_target_guid IN RAW,
                               p_key_value IN VARCHAR2  )
IS

  l_test_guid MGMT_GENSVC_AVAIL_TESTS.test_guid%TYPE;

BEGIN


  IF (p_target_guid IS NULL) OR (p_key_value IS NULL) THEN
    RETURN;
  END IF;

  GET_PERIODS;

  SELECT test_guid
    INTO l_test_guid
    FROM MGMT_GENSVC_AVAIL_TESTS t, MGMT_METRICS_COMPOSITE_KEYS k
   WHERE t.target_guid = p_target_guid
     AND t.target_guid = k.target_guid
     AND k.composite_key = HEXTORAW(p_key_value)
     AND t.test_name = k.key_part1_value;

  RESCHEDULE_JOB( p_target_guid, l_test_guid, min_job_sleep_key,NULL );

EXCEPTION
  WHEN OTHERS THEN
    NULL;

END SCHEDULE_TEST_AVAIL;

--------------------------------------------------------------------------
PROCEDURE BEACON_AVAIL_EVENT( p_beacon_guid IN RAW )
IS

  CURSOR l_avail_test_list IS
    SELECT t.target_guid, t.test_guid 
      FROM MGMT_GENSVC_AVAIL_BEACONS b, 
           MGMT_GENSVC_AVAIL_TESTS t
     WHERE b.beacon_target_guid = p_beacon_guid
       AND t.target_guid = b.target_guid
       AND t.monit_status <> 0
       AND t.avail_test <> 0;

  CURSOR l_nonavail_test_list IS
    SELECT t.target_guid, t.test_guid 
      FROM MGMT_GENSVC_AVAIL_BEACONS b, 
           MGMT_GENSVC_AVAIL_TESTS t
     WHERE b.beacon_target_guid = p_beacon_guid
       AND t.target_guid = b.target_guid
       AND t.monit_status <> 0
       AND t.avail_test = 0;

 l_test l_avail_test_list%ROWTYPE;
 l_more BOOLEAN;

BEGIN

  GET_PERIODS;

  IF (IS_BEACON(p_beacon_guid) = FALSE) THEN
    RETURN;
  END IF;

  -- process avail tests first
  l_more := TRUE;
  OPEN l_avail_test_list;
  WHILE l_more LOOP
    FETCH l_avail_test_list INTO l_test;
    l_more := l_avail_test_list%FOUND;
    IF l_more THEN
      RESCHEDULE_JOB(l_test.target_guid, l_test.test_guid, min_job_sleep_key, NULL);
    END IF;
  END LOOP;
  CLOSE l_avail_test_list;

  -- process non-avail tests next
  l_more := TRUE;
  OPEN l_nonavail_test_list;
  WHILE l_more LOOP
    FETCH l_nonavail_test_list INTO l_test;
    l_more := l_nonavail_test_list%FOUND;
    IF l_more THEN
      RESCHEDULE_JOB(l_test.target_guid, l_test.test_guid, init_job_sleep_key, NULL);
    END IF;
  END LOOP;
  CLOSE l_nonavail_test_list;

EXCEPTION
  WHEN OTHERS THEN
    IF l_avail_test_list%ISOPEN THEN
      CLOSE l_avail_test_list;
    END IF;
    IF l_nonavail_test_list%ISOPEN THEN
      CLOSE l_nonavail_test_list;
    END IF;

END BEACON_AVAIL_EVENT;

--------------------------------------------------------------------------
PROCEDURE AVAIL_EVENT( p_target_name IN VARCHAR2,
                       p_target_type IN VARCHAR2,
                       p_test_name IN VARCHAR2,
                       p_test_type IN VARCHAR2,
                       p_beacon_name IN VARCHAR2,
                       p_event_code IN NUMBER,
                       p_event_desc IN VARCHAR2 )
IS

  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_target_tz MGMT_TARGETS.timezone_region%TYPE;
  l_test_guid MGMT_GENSVC_AVAIL_TESTS.test_guid%TYPE;
  l_beacon_guid MGMT_TARGETS.target_guid%TYPE;
  l_cur_avmarker MGMT_GENSVC_TEST_AVAIL_MARKER.marker_timestamp%TYPE;
  l_event_ts DATE;

BEGIN

  -- get the target guid
  SELECT target_guid, timezone_region
    INTO l_target_guid, l_target_tz
    FROM MGMT_TARGETS
   WHERE target_name = p_target_name
     AND target_type = p_target_type;

  -- get the test guid
  IF p_test_name IS NOT NULL THEN
    SELECT test_guid
      INTO l_test_guid
      FROM MGMT_GENSVC_AVAIL_TESTS
     WHERE target_guid = l_target_guid 
       AND test_name = p_test_name
       AND test_type = p_test_type;
  ELSE
    l_test_guid := NULL;
  END IF;

  -- get the beacon guid
  IF p_beacon_name IS NOT NULL THEN
    SELECT target_guid
      INTO l_beacon_guid
      FROM MGMT_TARGETS
     WHERE target_name = p_beacon_name
       AND target_type = EMD_BCNTXN.p_beacon_type;
  ELSE
    l_beacon_guid := NULL;
  END IF;

  -- use sysdate adjusted to the target's tz as the timestamp
  l_event_ts := MGMT_GLOBAL.SYSDATE_TZRGN(l_target_tz);

  -- add the event
  INSERT INTO MGMT_GENSVC_AVAIL_EVENTS
    ( target_guid, test_guid, beacon_target_guid,
      event_code, event_description, event_timestamp )
  VALUES
    ( l_target_guid, l_test_guid, l_beacon_guid,
      p_event_code, p_event_desc, l_event_ts );

  -- process monitoring status events
  IF p_event_code = k_event_start_monit THEN
    -- update the monit status
    UPDATE MGMT_GENSVC_AVAIL_TESTS
       SET monit_status = 1
     WHERE target_guid = l_target_guid
       AND test_guid = l_test_guid;
    -- schedule avail job
    SCHEDULE_JOB( l_target_guid, l_test_guid, init_job_sleep_key);
  ELSIF p_event_code = k_event_stop_monit THEN
    -- update the monit status
    UPDATE MGMT_GENSVC_AVAIL_TESTS
       SET monit_status = 0
     WHERE target_guid = l_target_guid
       AND test_guid = l_test_guid;
    -- unschedule avail job
    UNSCHEDULE_JOB( l_target_guid, l_test_guid );
    -- change the avail status to UNKNOWN
    -- this will clear the open severity
    HANDLE_TEST_AVAIL_EVENT( l_target_guid, l_test_guid, 
                             MGMT_GLOBAL.G_STATUS_UNKNOWN, l_event_ts);

    -- clear any existing alerts first
    clear_status_severity(l_target_guid,
                          l_test_guid,
                          'Test is disabled. Closing all availability related alerts.');


  END IF;

END AVAIL_EVENT;

--------------------------------------------------------------------------
PROCEDURE PROCESS_AVAIL_BEACON( p_beacon_guid IN RAW )
IS

  l_more_tgts BOOLEAN;
  l_more_tests BOOLEAN;

  CURSOR l_targets IS
    SELECT target_guid 
      FROM MGMT_GENSVC_AVAIL_BEACONS
     WHERE beacon_target_guid = p_beacon_guid;

  l_tmp_target l_targets%ROWTYPE;

  CURSOR l_tests (c_tgt IN RAW) IS
    SELECT j.target_guid, j.test_guid
      FROM MGMT_GENSVC_AVAIL_JOB j, MGMT_GENSVC_AVAIL_TESTS t
     WHERE j.target_guid = c_tgt
       AND j.target_guid = t.target_guid
       AND j.test_guid = t.test_guid
       AND t.avail_test = 1;

  l_tmp_test l_tests%ROWTYPE;

BEGIN

  OPEN l_targets;
  l_more_tgts := TRUE;
  WHILE l_more_tgts LOOP
    FETCH l_targets INTO l_tmp_target;
    l_more_tgts := l_targets%FOUND;
    IF l_more_tgts THEN
      OPEN l_tests(l_tmp_target.target_guid);
      l_more_tests := TRUE;
      WHILE l_more_tests LOOP
        FETCH l_tests INTO l_tmp_test;
        l_more_tests := l_tests%FOUND;
        IF l_more_tests THEN
          -- reschedule the test for immediate computation
          RESCHEDULE_JOB( l_tmp_target.target_guid, 
                          l_tmp_test.test_guid,
                          min_job_sleep_key,
                          NULL );
        END IF;
      END LOOP;
      CLOSE l_tests;
      -- reschedule the target, but not for immediate
      RESCHEDULE_JOB( l_tmp_target.target_guid, NULL,
                     init_job_sleep_key, NULL );
    END IF;
  END LOOP;
  CLOSE l_targets;

EXCEPTION
  WHEN OTHERS THEN
    IF l_targets%ISOPEN THEN
      CLOSE l_targets;
    END IF;
    IF l_tests%ISOPEN THEN
      CLOSE l_tests;
    END IF;

END PROCESS_AVAIL_BEACON;

--------------------------------------------------------------------------
-- it's important that the tests are processed in order.  if the target
-- doesn't use test availability, the tests need to be locked.  hence,
-- they need to be locked in order.
PROCEDURE PROCESS_BLACKOUT_START ( p_target_guid IN RAW, p_timestamp IN DATE )
IS

  CURSOR l_test_list IS
    SELECT test_guid
      FROM MGMT_GENSVC_AVAIL_TESTS
     WHERE target_guid = p_target_guid
     ORDER BY test_guid;

  l_test_guid  MGMT_GENSVC_AVAIL_TESTS.test_guid%TYPE; 
  l_more       BOOLEAN;
  l_tgt_locked NUMBER;
  l_test_locked NUMBER;
  l_next_run   DATE;
  l_in_error   NUMBER;

BEGIN

  -- first lock the target
  l_tgt_locked := LOCK_TARGET(p_target_guid, l_next_run, l_in_error);
  IF l_tgt_locked <> 0 AND l_tgt_locked <> 1 THEN
    raise_application_error(MGMT_GLOBAL.EXEC_LOCK_ERR,
          'Unable to start blackout for target ' || RAWTOHEX(p_target_guid) || '. Target availability status being processed.');
  END IF;

  -- set all tests of the target to the BLACKOUT state
  l_more := TRUE;
  OPEN l_test_list;
  WHILE l_more LOOP
    FETCH l_test_list INTO l_test_guid;
    l_more := l_test_list%FOUND;
    IF l_more THEN
      IF l_tgt_locked = 0 THEN
        l_test_locked := 0;
      ELSE
        l_test_locked := LOCK_TEST(p_target_guid, l_test_guid, l_next_run, l_in_error);
      END IF;
      IF l_test_locked = 0 THEN
        -- change the avail status to UNKNOWN
        HANDLE_TEST_AVAIL_EVENT( p_target_guid, l_test_guid, 
                                 MGMT_GLOBAL.G_STATUS_BLACKOUT, p_timestamp );
      ELSE
        raise_application_error(MGMT_GLOBAL.EXEC_LOCK_ERR,
              'Unable to start blackout for target ' || RAWTOHEX(p_target_guid) || 
              '. Target and/or test availability status being processed.');
      END IF;
    END IF;
  END LOOP;
  CLOSE l_test_list;

EXCEPTION
  WHEN OTHERS THEN
    IF l_test_list%ISOPEN THEN
      CLOSE l_test_list;
    END IF;
    RAISE;

END PROCESS_BLACKOUT_START;

--------------------------------------------------------------------------
-- it's important that the tests are processed in order.  if the target
-- doesn't use test availability, the tests need to be locked.  hence,
-- they need to be locked in order.
PROCEDURE PROCESS_BLACKOUT_END ( p_target_guid IN RAW, p_timestamp IN DATE )
IS

  CURSOR l_test_list IS
    SELECT test_guid
      FROM MGMT_GENSVC_AVAIL_TESTS
     WHERE target_guid = p_target_guid
     ORDER BY test_guid;

  CURSOR l_emd_list IS
    SELECT DISTINCT t.emd_url, t.timezone_region
      FROM MGMT_TARGETS t, MGMT_BCN_TARGET b
     WHERE b.target_guid = p_target_guid
       AND b.is_removing <> 'Y'
       AND b.beacon_target_guid = t.target_guid;     

  l_agent          l_emd_list%ROWTYPE;
  l_test_guid      MGMT_GENSVC_AVAIL_TESTS.test_guid%TYPE;   
  l_svc_tgt_name   MGMT_TARGETS.TARGET_NAME%TYPE;
  l_svc_tgt_type   MGMT_TARGETS.TARGET_TYPE%TYPE;
  l_svc_tz_region  MGMT_TARGETS.TIMEZONE_REGION%TYPE;
  l_beacon_ts      DATE;
  l_more           BOOLEAN;
  l_target_names   MGMT_JOB_VECTOR_PARAMS;
  l_target_types   MGMT_JOB_VECTOR_PARAMS;
  l_avail_enabled  NUMBER;  
  l_tmp_bool       BOOLEAN;
  l_tgt_locked     NUMBER;
  l_test_locked    NUMBER;
  l_next_run       DATE;
  l_in_error       NUMBER;

BEGIN

  IF (p_target_guid IS NULL) OR (p_timestamp IS NULL) THEN
    RETURN;
  END IF;

  -- reset the beacons' state
  SELECT target_name, target_type, timezone_region
    INTO l_svc_tgt_name, l_svc_tgt_type, l_svc_tz_region
    FROM MGMT_TARGETS
   WHERE target_guid = p_target_guid;

  FOR l_agent IN l_emd_list LOOP
    BEGIN
      -- set up the params for the discard job
      l_target_names := MGMT_JOB_VECTOR_PARAMS();
      l_target_names.EXTEND(1);
      l_target_names(1) := l_svc_tgt_name;
      l_target_types := MGMT_JOB_VECTOR_PARAMS();
      l_target_types.EXTEND(1);
      l_target_types(1) := l_svc_tgt_type;

      -- get the agent tz and adjust the timestamp accordingly
      IF (l_agent.timezone_region <> l_svc_tz_region) THEN
        l_beacon_ts := MGMT_GLOBAL.ADJUST_TZ(p_timestamp, l_svc_tz_region, l_agent.timezone_region);
      ELSE
        l_beacon_ts := p_timestamp;
      END IF;
    
      -- submit the discard state job for proxy target
      MGMT_BLACKOUT_ENGINE.submit_discard_state_job( l_agent.emd_url,
                                                     l_target_names,
                                                     l_target_types,
                                                     l_beacon_ts,
                                                     'Y');
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;

  -- lock the target to change its availability status
  l_tgt_locked := LOCK_TARGET(p_target_guid, l_next_run, l_in_error);
  IF l_tgt_locked <> 0 AND l_tgt_locked <> 1 THEN
    raise_application_error(MGMT_GLOBAL.EXEC_LOCK_ERR,
          'Unable to end blackout for target ' || RAWTOHEX(p_target_guid) || '. Target availability status being processed.');
  END IF;

  GET_PERIODS;
  
  -- set all tests to UNKNOWN
  l_more := TRUE;
  OPEN l_test_list;
  WHILE l_more LOOP
    FETCH l_test_list INTO l_test_guid;
    l_more := l_test_list%FOUND;
    IF l_more THEN
      IF l_tgt_locked = 0 THEN
        l_test_locked := 0;
      ELSE
        l_test_locked := LOCK_TEST(p_target_guid, l_test_guid, l_next_run, l_in_error);
      END IF;
      IF l_test_locked = 0 THEN
        UPDATE_TEST_AVAIL( p_target_guid, l_test_guid, MGMT_GLOBAL.G_STATUS_UNKNOWN,
                           p_timestamp, p_timestamp, FALSE, l_tmp_bool);
        --schedule runs for each test
        SCHEDULE_JOB( p_target_guid, l_test_guid, init_job_sleep_key );
      ELSE
        raise_application_error(MGMT_GLOBAL.EXEC_LOCK_ERR,
              'Unable to stop blackout for target ' || RAWTOHEX(p_target_guid) || 
              '. Target and/or test availability status being processed.');
      END IF;
    END IF;
  END LOOP;
  CLOSE l_test_list;

  -- move the avail marker of the target if it uses test-based avail
  BEGIN
    SELECT avail_enabled
      INTO l_avail_enabled
      FROM MGMT_GENSVC_AVAIL_CONFIG
     WHERE target_guid = p_target_guid;
    IF l_avail_enabled <> 0 THEN
      EM_SEVERITY.update_availability_marker(p_target_guid, p_timestamp, MGMT_GLOBAL.G_STATUS_UNKNOWN);
 
      -- schedule job for target
      SCHEDULE_JOB( p_target_guid, NULL, init_job_sleep_key );      

    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;

  -- clean up
  IF l_target_names IS NOT NULL THEN
    l_target_names.DELETE;
  END IF;
  IF l_target_types IS NOT NULL THEN
    l_target_types.DELETE;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    IF l_test_list%ISOPEN THEN
      CLOSE l_test_list;
    END IF;
    IF l_emd_list%ISOPEN THEN
      CLOSE l_emd_list;
    END IF;
    IF l_target_names IS NOT NULL THEN
      l_target_names.DELETE;
    END IF;
    IF l_target_types IS NOT NULL THEN
      l_target_types.DELETE;
    END IF;
    RAISE;

END PROCESS_BLACKOUT_END;

--------------------------------------------------------------------------
FUNCTION PROCESS_UNREACHABLE( p_target_guid IN RAW )
RETURN BOOLEAN IS
  l_target_type MGMT_TARGETS.target_type%TYPE;
BEGIN

  IF (p_target_guid IS NULL) THEN
    RETURN FALSE;
  END IF;
  
  IF HAS_AVAIL_DEFINTION(p_target_guid) THEN
    RETURN TRUE;    
  ELSE
    SELECT target_type
      INTO l_target_type
      FROM MGMT_TARGETS
     WHERE target_guid = p_target_guid;
    IF l_target_type = EMD_BCNTXN.p_beacon_type THEN
      PROCESS_AVAIL_BEACON(p_target_guid);
    END IF;
    RETURN FALSE;
  END IF;

EXCEPTION

  WHEN OTHERS THEN
    RETURN FALSE;

END PROCESS_UNREACHABLE;

--------------------------------------------------------------------------
PROCEDURE PROCESS_METRIC_ERROR ( p_target_guid IN RAW, 
                                 p_metric_guid IN RAW,
                                 p_test_guid IN VARCHAR2 )
IS

  l_test_guid MGMT_GENSVC_AVAIL_JOB.test_guid%TYPE;
  l_avail_metric_guid MGMT_METRICS.metric_guid%TYPE;
  l_err_metric_guid MGMT_METRICS.metric_guid%TYPE;

BEGIN

  IF NOT HAS_AVAIL_DEFINTION(p_target_guid) THEN
    RETURN;
  END IF;

  GET_PERIODS;

  l_test_guid := HEXTORAW(p_test_guid);

  SELECT metric_guid
    INTO l_avail_metric_guid
    FROM MGMT_GENSVC_AVAIL_TESTS
   WHERE target_guid = p_target_guid
     AND test_guid = l_test_guid;

  GET_ERROR_METRIC(l_avail_metric_guid, l_err_metric_guid);

  IF p_metric_guid = l_err_metric_guid THEN
    SCHEDULE_JOB( p_target_guid, l_test_guid, min_job_sleep_key );
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    NULL;

END PROCESS_METRIC_ERROR;

--------------------------------------------------------------------------
-- it's imperative that this procedure generates the error msg determinstically 
-- given the set of input params.  this is because the error message itself is 
-- used to check for duplication of the error. this is a problem if there are
-- multiple beacons in a given agent, in which case we choose the first one
-- using an alphabetical order.
PROCEDURE METRIC_ERROR_MSG( p_target_guid IN RAW, 
                            p_metric_guid IN RAW, 
                            p_collection_name IN VARCHAR2, 
                            p_agent_guid IN RAW, 
                            p_err_msg IN VARCHAR2, 
                            p_new_err_msg OUT VARCHAR2) 
IS

  l_test_name MGMT_GENSVC_AVAIL_TESTS.test_name%TYPE;
  l_bcn_name MGMT_TARGETS.target_name%TYPE;

BEGIN

  -- get the txn name
  SELECT test_name
    INTO l_test_name
    FROM MGMT_GENSVC_AVAIL_TESTS
   WHERE target_guid = p_target_guid 
     AND test_guid = HEXTORAW(p_collection_name);

  -- get the beacon name
  SELECT bcns.target_name 
    INTO l_bcn_name
    FROM (
      SELECT bcn.target_name
        FROM MGMT_TARGETS emd, MGMT_TARGETS bcn, MGMT_BCN_TARGET bt
       WHERE emd.target_guid = p_agent_guid
         AND bt.target_guid = p_target_guid
         AND bt.beacon_target_guid = bcn.target_guid
         AND bcn.emd_url = emd.emd_url
       ORDER BY bcn.target_name ASC ) bcns
   WHERE ROWNUM = 1;

  p_new_err_msg := SUBSTR( 'The beacon ' || l_bcn_name || 
                           ' failed play the transaction ' ||  l_test_name || 
                           ' due to the following error: ' || p_err_msg, 
                           1, 4000 );

EXCEPTION
  WHEN OTHERS THEN
    -- prevent any exception from being raised up
    p_new_err_msg := p_err_msg;

END METRIC_ERROR_MSG;

PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN) IS
BEGIN  
  MGMT_SQLTRACE.EXTENDED_SQL_TRACE_ON(EST_GENSVC_NAME, p_value);
END DBMSJOB_EXTENDED_SQL_TRACE_ON;

--------------------------------------------------------------------------
PROCEDURE EXEC_AVAIL_JOB
IS
  CURSOR l_tgts (limit_t DATE) IS
    SELECT DISTINCT target_guid
      FROM MGMT_GENSVC_AVAIL_JOB
     WHERE next_run <= limit_t;

  -- it's important that the tests are processed in order.  if the target
  -- doesn't use test availability, the tests need to be locked.  hence,
  -- they need to be locked in order.
  CURSOR l_tests (tgt RAW) IS
    SELECT test_guid
      FROM MGMT_GENSVC_AVAIL_JOB
     WHERE target_guid = tgt
       AND test_guid <> k_no_test
     ORDER BY test_guid;

  l_cur_time     DATE;
  l_rowid        ROWID;
  l_target_guid  MGMT_GENSVC_AVAIL_JOB.target_guid%TYPE;
  l_test_guid    MGMT_GENSVC_AVAIL_JOB.test_guid%TYPE;
  l_next_in_error MGMT_GENSVC_AVAIL_JOB.in_error%TYPE;
  l_next_run     NUMBER;
  l_more         BOOLEAN; 
  l_more_tests   BOOLEAN;
  l_process_record BOOLEAN;
  -- performance variables
  l_start_time   DATE := SYSDATE;
  l_total_cnt    NUMBER := 0;
  l_locked_cnt   NUMBER := 0;
  l_error_cnt    NUMBER := 0;
  --
  l_tgt_locked   NUMBER;
  l_tgt_next_run DATE;
  l_tgt_in_error NUMBER;
  l_test_locked   NUMBER;
  l_test_next_run DATE;
  l_test_in_error NUMBER;
  l_test_count   NUMBER;
  
BEGIN

  MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_GENSVC_NAME);

  GET_PERIODS;
  l_cur_time := CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE);
  OPEN l_tgts(l_cur_time);
  l_more := TRUE;
  WHILE l_more LOOP
    -- process one target at a time.  for each target, process its tests first and 
    -- then the target.  check the run timestamp for each
    FETCH l_tgts INTO l_target_guid;
    l_more := l_tgts%FOUND;
    IF l_more THEN
      l_total_cnt := l_total_cnt + 1;
      -- lock the target
      l_tgt_locked := LOCK_TARGET(l_target_guid, l_tgt_next_run, l_tgt_in_error);
      IF l_tgt_locked = 2 THEN
        -- target is locked, skip it.  it'll be processed the next time the job runs
        l_locked_cnt := l_locked_cnt + 1;
        l_next_in_error := NULL;
        -- nothing was done for this target, so no need to rollback
        GOTO NEXT_TGT;
      ELSIF l_tgt_locked = 3 THEN
        -- target is locked, skip it.  it'll be processed the next time the job runs
        l_error_cnt := l_error_cnt + 1;
        l_next_in_error := 1;
        -- nothing was done for this target, so no need to rollback
        GOTO NEXT_TGT;
      END IF;

      -- process the target's tests
      OPEN l_tests(l_target_guid);
      l_more_tests := TRUE;
      l_test_count := 0;
      WHILE l_more_tests LOOP
        FETCH l_tests INTO l_test_guid;
        l_more_tests := l_tests%FOUND;
        IF l_more_tests THEN
          -- increment the total counter
          l_total_cnt := l_total_cnt + 1;
          l_test_count := l_test_count + 1;
          l_next_in_error := NULL;
          -- lock the test
          l_test_locked := LOCK_TEST(l_target_guid, l_test_guid, l_test_next_run, l_test_in_error);
          IF (l_tgt_locked = 1) AND (l_test_count = 1) AND (l_test_locked <> 0) THEN
            -- must lock the first test if the target was not locked
            -- nothing was done for this target, so no need to rollback
            GOTO NEXT_TGT;
          END IF;
          IF l_test_locked = 2 THEN
            l_locked_cnt := l_locked_cnt + 1;
            l_next_in_error := NULL;
            GOTO NEXT_TEST;
          ELSIF l_test_locked = 1 OR l_test_locked = 3 THEN
            l_error_cnt := l_error_cnt + 1;
            l_next_in_error := 1;
            GOTO NEXT_TEST;
          END IF;
          -- compute the avail of the test
          BEGIN
            SAVEPOINT cur_tgt_svpt;
            IF l_test_next_run <= l_cur_time THEN
              l_next_run := max_job_sleep_key;
              COMPUTE_TEST_AVAIL( l_target_guid, l_test_guid, l_next_run );
              l_next_in_error := 0;
            ELSE
              l_total_cnt := l_total_cnt - 1;
            END IF;
          EXCEPTION
            WHEN OTHERS THEN
              ROLLBACK TO cur_tgt_svpt;
              l_error_cnt := l_error_cnt + 1;
              l_next_in_error := 1;
              IF (l_test_in_error IS NULL) OR (l_test_in_error = 0) THEN
                MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 
                     'Test availability evaluation failed. Target: ' || 
                     RAWTOHEX(l_target_guid) ||', Test: ' || RAWTOHEX(l_test_guid) ||
                     '.  Error: ' || SQLERRM);
              END IF;
          END;
          BEGIN
            IF l_test_next_run <= l_cur_time THEN
              RESCHEDULE_JOB( l_target_guid, l_test_guid, l_next_run, l_next_in_error );
            END IF;
          EXCEPTION
            WHEN OTHERS THEN
              NULL;
          END;
        END IF;
<<NEXT_TEST>>
        NULL;        
      END LOOP;

      -- process the target
      BEGIN
        SAVEPOINT cur_tgt_svpt;
        IF l_tgt_next_run <= l_cur_time THEN
          l_next_run := max_job_sleep_key;
          COMPUTE_SVC_AVAIL( l_target_guid, l_next_run );
          l_next_in_error := 0;
        ELSE
          l_total_cnt := l_total_cnt - 1;
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK TO cur_tgt_svpt;
          l_error_cnt := l_error_cnt + 1;
          l_next_in_error := 1;
          IF (l_tgt_in_error IS NULL) OR (l_tgt_in_error = 0) THEN         
            MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 
                 'Service test-based availability evaluation failed. Target: ' ||
                 RAWTOHEX(l_target_guid) || '. Error: ' || SQLERRM);
          END IF;
      END;   
      BEGIN
        IF l_tgt_next_run <= l_cur_time THEN
          RESCHEDULE_JOB( l_target_guid, k_no_test, l_next_run, l_next_in_error );
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;

      -- commit all work for the target and release the locks
      COMMIT;
    END IF;
<<NEXT_TGT>>
    IF l_tests%ISOPEN THEN
      CLOSE l_tests;
    END IF;
  END LOOP;     

  -- close the cursor
  IF l_tgts%ISOPEN THEN
    CLOSE l_tgts;
  END IF;

  -- Log a performance msgs
  MGMT_LOG.LOG_PERFORMANCE(MODULE_NAME, (SYSDATE - l_start_time)*(24*60*60*1000), l_start_time, 'Y', 'Total Services and Tests Processed', l_total_cnt);
  MGMT_LOG.LOG_PERFORMANCE(MODULE_NAME, 0, l_start_time, 'N', 'Locked Services and Tests', l_locked_cnt); 
  MGMT_LOG.LOG_PERFORMANCE(MODULE_NAME, 0, l_start_time, 'N', 'Failed Services and Tests', l_error_cnt); 

EXCEPTION

  WHEN OTHERS THEN
    ROLLBACK;
    IF l_tgts%ISOPEN THEN
      CLOSE l_tgts;
    END IF;
    IF l_tests%ISOPEN THEN
      CLOSE l_tests;
    END IF;
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'EXEC_AVAIL_JOB failed.  Error: ' || SQLERRM);
    -- set the context
    EMDW_LOG.set_context( v_context_type=>EMDW_LOG_CTX );
    EMDW_LOG.error(SUBSTR('EXEC_AVAIL_JOB Error: ' || SQLERRM, 1, k_trc_msg_len), MODULE_NAME);
    -- reset back to default ctx
    EMDW_LOG.set_context;

END EXEC_AVAIL_JOB;

--------------------------------------------------------------------------
PROCEDURE COMPUTE_TEST_AVAIL( p_target_guid IN RAW,
                              p_test_guid IN RAW,
                              p_next_run OUT NUMBER )
IS

  l_cur_status MGMT_GENSVC_TEST_CUR_AVAIL.current_status%TYPE;
  l_cur_ts MGMT_GENSVC_TEST_CUR_AVAIL.start_collection_timestamp%TYPE;
  l_cur_avmarker MGMT_GENSVC_TEST_AVAIL_MARKER.marker_timestamp%TYPE;
  l_new_status MGMT_GENSVC_TEST_CUR_AVAIL.current_status%TYPE;
  l_new_ts MGMT_GENSVC_TEST_CUR_AVAIL.start_collection_timestamp%TYPE;
  l_new_avmarker MGMT_GENSVC_TEST_AVAIL_MARKER.marker_timestamp%TYPE;
  l_target_tz MGMT_TARGETS.timezone_region%TYPE;
  l_target_name MGMT_TARGETS.target_name%TYPE;
  l_target_type MGMT_TARGETS.target_type%TYPE;
  l_test_name MGMT_GENSVC_AVAIL_TESTS.test_name%TYPE;
  l_test_type MGMT_GENSVC_AVAIL_TESTS.test_type%TYPE;
  l_metric_guid MGMT_GENSVC_AVAIL_TESTS.metric_guid%TYPE;
  l_err_metric_guid MGMT_GENSVC_AVAIL_TESTS.metric_guid%TYPE;
  l_coll_name MGMT_COLLECTIONS.coll_name%TYPE;
  l_monit_status MGMT_GENSVC_AVAIL_TESTS.monit_status%TYPE;  
  l_met_err_ts MGMT_CURRENT_METRIC_ERRORS.collection_timestamp%TYPE;
  l_ck MGMT_METRICS_COMPOSITE_KEYS.composite_key%TYPE;
  l_bcn_sev_code MGMT_SEVERITY.severity_code%TYPE;
  l_bcn_sev_ts MGMT_SEVERITY.collection_timestamp%TYPE;
  l_bcn_sev_msg MGMT_SEVERITY.message%TYPE;
  l_bcn_max_coll_ts MGMT_CURRENT_METRICS.collection_timestamp%TYPE;
  l_svc_status MGMT_CURRENT_AVAILABILITY.current_status%TYPE;
  l_svc_start_ts MGMT_CURRENT_AVAILABILITY.start_collection_timestamp%TYPE;

  l_bcn_avmarker DATE;
  l_bcn_start_ts DATE;
  l_svc_avmarker_in_bcntz DATE;
  l_max_bcn_avmarker DATE := NULL;
  l_min_bcn_avmarker DATE := NULL;
  l_bcn_up_start_ts DATE;
  l_bcn_up_end_ts DATE;

  l_unk_found BOOLEAN;
  l_unk_end_ts DATE;
  l_up_found BOOLEAN;
  l_up_sev_ts DATE;
  l_up_end_ts DATE;
  l_dn_found BOOLEAN;
  l_dn_sev_ts DATE;
  l_dn_end_ts DATE;

  l_process_beacon BOOLEAN;
  l_blackout_end BOOLEAN;
  l_tmp_number NUMBER;
  l_found_sev BOOLEAN;
  l_has_sevs NUMBER;
  l_more BOOLEAN;
  l_avail_updated BOOLEAN;
  
  l_blackout_ts DATE;
  l_tmp_bool BOOLEAN;
  l_trc_msg VARCHAR2(1000);

  -- Retrieves the list of beacons that participate in availability. 
  CURSOR l_bcn_list IS
    SELECT avbcn.target_guid, avbcn.target_name, 
           avbcn.timezone_region, avbcn.agent_guid,
           m.marker_timestamp, a.current_status, a.start_collection_timestamp
      FROM MGMT_CURRENT_AVAILABILITY a, 
           MGMT_AVAILABILITY_MARKER m,
           ( SELECT bcn.target_guid, bcn.target_name, 
                    agnt.target_guid AS agent_guid,
                    bcn.timezone_region AS timezone_region
               FROM MGMT_GENSVC_AVAIL_BEACONS b, 
                    MGMT_TARGETS bcn, MGMT_TARGETS agnt
              WHERE b.target_guid = p_target_guid
                AND bcn.target_guid = b.beacon_target_guid
                AND agnt.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE
                AND agnt.emd_url = bcn.emd_url ) avbcn
     WHERE a.target_guid = avbcn.target_guid
       AND m.target_guid = avbcn.target_guid;

  l_bcn   l_bcn_list%ROWTYPE;

BEGIN

  -- re-compute avail in max mins by default
  p_next_run := max_job_sleep_key;
  l_avail_updated := FALSE;
  l_trc_msg := 'TESTAV';

  -- set the svc avail trace ctx
  EMDW_LOG.set_context( v_context_type=>EMDW_LOG_CTX,
                        v_context_identifier=>'Svc Test Avail' );

  -- validate parameters
  IF (p_target_guid IS NULL) OR (p_test_guid IS NULL) THEN
    TRACE_MSG(l_trc_msg, '|E:Invalid Args');
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'Invalid Service Target GUID and/or Test GUID');
  END IF;

  -- get the test's current status 
  SELECT current_status, start_collection_timestamp
    INTO l_cur_status, l_cur_ts
    FROM MGMT_GENSVC_TEST_CUR_AVAIL 
   WHERE target_guid = p_target_guid
     AND test_guid = p_test_guid;

  -- get the test's avail marker
  SELECT marker_timestamp
    INTO l_cur_avmarker
    FROM MGMT_GENSVC_TEST_AVAIL_MARKER
   WHERE target_guid = p_target_guid
     AND test_guid = p_test_guid;

  TRACE_MSG(l_trc_msg, ('|SG:' || RAWTOHEX(p_target_guid) || 
                        '|TG:' || RAWTOHEX(p_test_guid) ||
                        '|St:' || TO_CHAR(l_cur_status) || 
                        '|Ct:' || TO_CHAR(l_cur_ts, k_trc_date_format) ||
                        '|Am:' || TO_CHAR(l_cur_avmarker, k_trc_date_format)));

  -- get the target's avail
  BEGIN
    SELECT current_status, start_collection_timestamp
      INTO l_svc_status, l_svc_start_ts
      FROM MGMT_CURRENT_AVAILABILITY
     WHERE target_guid = p_target_guid;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_svc_status :=  MGMT_GLOBAL.G_STATUS_UNKNOWN;
  END;

  -- make sure that the blackout status of the test matches the target's
  IF l_svc_status = MGMT_GLOBAL.G_STATUS_BLACKOUT THEN
    IF l_cur_status != MGMT_GLOBAL.G_STATUS_BLACKOUT THEN
      -- target is in blackout, test is not
      BEGIN
        -- get the target's start blackout ts
        SELECT MAX(start_collection_timestamp)
          INTO l_blackout_ts
          FROM MGMT_CURRENT_AVAILABILITY
         WHERE target_guid = p_target_guid
           AND current_status = MGMT_GLOBAL.G_STATUS_BLACKOUT
           AND start_collection_timestamp IS NOT NULL;
        -- cannot make a change earlier than the test's avail marker
        IF (l_blackout_ts < l_cur_avmarker) THEN
          l_blackout_ts := l_cur_avmarker;
        END IF;
        HANDLE_TEST_AVAIL_EVENT( p_target_guid, p_test_guid, 
                                 MGMT_GLOBAL.G_STATUS_BLACKOUT, l_blackout_ts );

        SELECT current_status, start_collection_timestamp
          INTO l_cur_status, l_cur_ts
          FROM MGMT_GENSVC_TEST_CUR_AVAIL 
         WHERE target_guid = p_target_guid
           AND test_guid = p_test_guid;

        SELECT marker_timestamp
          INTO l_cur_avmarker
          FROM MGMT_GENSVC_TEST_AVAIL_MARKER
         WHERE target_guid = p_target_guid
           AND test_guid = p_test_guid;

        TRACE_MSG(l_trc_msg, '|I:Corrected test blackout start' ||
                             '|St:' || TO_CHAR(l_cur_status) || 
                             '|Ct:' || TO_CHAR(l_cur_ts, k_trc_date_format) ||
                             '|Am:' || TO_CHAR(l_cur_avmarker, k_trc_date_format));

      EXCEPTION
        WHEN OTHERS THEN
          TRACE_MSG(l_trc_msg, '|I:Failed to correct Test blackout start.');
          RAISE;
      END;
    END IF;
  ELSE
    IF l_cur_status = MGMT_GLOBAL.G_STATUS_BLACKOUT THEN
      -- target is not in blackout, test is
      BEGIN
        SELECT MAX(end_collection_timestamp)
          INTO l_blackout_ts
          FROM MGMT_AVAILABILITY
         WHERE target_guid = p_target_guid
           AND current_status = MGMT_GLOBAL.G_STATUS_BLACKOUT
           AND end_collection_timestamp IS NOT NULL;

        IF (l_blackout_ts < l_cur_avmarker) THEN
          l_blackout_ts := l_cur_avmarker;
        END IF;
        UPDATE_TEST_AVAIL( p_target_guid, p_test_guid, MGMT_GLOBAL.G_STATUS_UNKNOWN,
                           l_blackout_ts, l_blackout_ts, FALSE, l_tmp_bool);

        SELECT current_status, start_collection_timestamp
          INTO l_cur_status, l_cur_ts
          FROM MGMT_GENSVC_TEST_CUR_AVAIL 
         WHERE target_guid = p_target_guid
           AND test_guid = p_test_guid;

        SELECT marker_timestamp
          INTO l_cur_avmarker
          FROM MGMT_GENSVC_TEST_AVAIL_MARKER
         WHERE target_guid = p_target_guid
           AND test_guid = p_test_guid;

        TRACE_MSG(l_trc_msg, '|I:Corrected test blackout end' ||
                             '|St:' || TO_CHAR(l_cur_status) || 
                             '|Ct:' || TO_CHAR(l_cur_ts, k_trc_date_format) ||
                             '|Am:' || TO_CHAR(l_cur_avmarker, k_trc_date_format));

      EXCEPTION
        WHEN OTHERS THEN
          TRACE_MSG(l_trc_msg, '|I:Failed to correct Test blackout end.');
          RAISE;
      END;
    END IF;
  END IF;

  -- bail if the test is in blackout
  IF l_cur_status = MGMT_GLOBAL.G_STATUS_BLACKOUT THEN
    TRACE_MSG(l_trc_msg, '|I:In Blackout(1)');
    GOTO DONE_TSTAV;
  END IF;

  -- check if the blackout job is running late
  IF (IS_IN_BLACKOUT(p_target_guid, l_cur_avmarker)) THEN    
    TRACE_MSG(l_trc_msg, '|I:In Blackout(2)');
    GOTO DONE_TSTAV;
  END IF;

  -- get the target's timezone
  -- get target name and type
  SELECT timezone_region, target_name, target_type
    INTO l_target_tz, l_target_name, l_target_type
    FROM MGMT_TARGETS
   WHERE target_guid = p_target_guid;

  -- if the test is not monitoring: status = UNKNOWN, avmrkr = current time
  SELECT test_name, metric_guid, monit_status, test_type
    INTO l_test_name, l_metric_guid, l_monit_status, l_test_type
    FROM MGMT_GENSVC_AVAIL_TESTS
   WHERE target_guid = p_target_guid
     AND test_guid = p_test_guid;

  TRACE_MSG(l_trc_msg, ('|N:' || l_test_name || 
                        '|MG:' || RAWTOHEX(l_metric_guid) ||
                        '|MS:' || TO_CHAR(l_monit_status) ||
                        '|TZ:' || l_target_tz));

  IF l_monit_status <> 1 THEN
    -- when the test is not monitoring, its status is UNKNOWN (see AVAIL_EVENT)
    -- thus, we need only move the av marker if needed
    l_new_avmarker := MGMT_GLOBAL.SYSDATE_TZRGN(l_target_tz);
    IF (l_new_avmarker IS NOT NULL) AND (l_new_avmarker >= l_cur_avmarker) THEN
      UPDATE_TEST_AVAIL( p_target_guid, p_test_guid, MGMT_GLOBAL.G_STATUS_UNKNOWN,
                         l_cur_avmarker, l_new_avmarker, TRUE, l_avail_updated );
    END IF;
    TRACE_MSG(l_trc_msg, '|I:Test in Not Monitoring state');
    GOTO DONE_TSTAV;
  END IF;

  -- If it's out of a blackout, don't use any severities older than the blackout end event
  l_blackout_end := FALSE;
  IF l_cur_status = MGMT_GLOBAL.G_STATUS_UNKNOWN THEN
    BEGIN
      SELECT current_status
        INTO l_tmp_number
        FROM MGMT_AVAILABILITY
       WHERE target_guid = p_target_guid
         AND end_collection_timestamp = l_cur_ts
         AND current_status = MGMT_GLOBAL.G_STATUS_BLACKOUT;
      l_blackout_end := TRUE;
      TRACE_MSG(l_trc_msg, '|I:Blackout End');
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_blackout_end := FALSE;
    END;
  END IF;
  
  -- check if the metric provided is a metric column and if so, find the metric guid
  -- of the table metric.
  -- this is needed to check for metric errors, since the agent sends those for the
  -- the table metric only
  GET_ERROR_METRIC(l_metric_guid, l_err_metric_guid);

  --
  -- Calculate the availability.
  -- If one of the beacons is UP, return UP
  -- If no beacon is UP and at least 1 is down, return DOWN
  -- If no beacons vote, return UNKNOWN.
  --
  -- note on timezones:
  --  old_avmrkr is the webapp's av marker in its own tz
  --  v_webapp_adj_marker is the webapp's av marker converted to beacon tz
  --  v_bcn.marker_timestamp from the cursor is the beacon's av marker in its own tz
  --  v_beacon_adj_mrkr is the beacon's av marker converted to webapp's tz
  --  v_bcn.start_collection_timestamp from the cursor is the beacon's latest sev in its own tz
  --  v_beacon_adj_collts is the beacon's latest sev converted to webapp's tz
  --  v_bcn_start_ts and v_bcn_end_ts are the beacon's latest UP period start and end times converted to webapp's tz
  --
  l_more := TRUE;
  OPEN l_bcn_list;
  WHILE l_more LOOP
    --
    -- Get a beacon in the availability list.    
    FETCH l_bcn_list INTO l_bcn;
    l_more := l_bcn_list%FOUND;
    IF l_more THEN
      -- convert the beacon timestamps to the svc's tz
      IF l_bcn.timezone_region <> l_target_tz THEN
        l_bcn_avmarker := MGMT_GLOBAL.ADJUST_TZ( l_bcn.marker_timestamp, 
                                                 l_bcn.timezone_region, l_target_tz );
        l_bcn_start_ts := MGMT_GLOBAL.ADJUST_TZ( l_bcn.start_collection_timestamp, 
                                                 l_bcn.timezone_region, l_target_tz );
        l_svc_avmarker_in_bcntz := MGMT_GLOBAL.ADJUST_TZ( l_cur_avmarker, 
                                                          l_target_tz, l_bcn.timezone_region );
      ELSE
        l_bcn_avmarker := l_bcn.marker_timestamp;
        l_bcn_start_ts := l_bcn.start_collection_timestamp;
        l_svc_avmarker_in_bcntz := l_cur_avmarker;
      END IF;

      TRACE_MSG(l_trc_msg, ('|Bn:' || l_bcn.target_name ||
                            '|Bs:' || TO_CHAR(l_bcn.current_status) ||
                            '|Bb:' || TO_CHAR(l_bcn_start_ts, k_trc_date_format) ||
                            '|Bm:' || TO_CHAR(l_bcn_avmarker, k_trc_date_format) ||
                            '|Btz:'|| l_bcn.timezone_region));

      -- keep track of the min and max av markers of all beacons
      IF (l_max_bcn_avmarker IS NULL) OR (l_max_bcn_avmarker < l_bcn_avmarker) THEN
        l_max_bcn_avmarker := l_bcn_avmarker;
      END IF;
      IF (l_min_bcn_avmarker IS NULL) OR (l_min_bcn_avmarker > l_bcn_avmarker) THEN
        l_min_bcn_avmarker := l_bcn_avmarker;
      END IF;

      -- find the beacon's latest UP period
      IF l_bcn.current_status = MGMT_GLOBAL.G_STATUS_UP THEN
        -- beacon is currently UP, check if it's avail marker >= target's
        -- it's important that the bcn marker be >= the target's (and not only >).
        -- otherwise we'd get in a situation where 2 beacons--both UP, but on voting 
        -- up and the other voting down--would change the avail of the svc on every 
        -- computation.  this is incorrect of course, the svc should remain in the
        -- same state (up or down depending on the AND/OR eval logic).
        IF l_bcn_avmarker >= l_cur_avmarker THEN
          l_bcn_up_start_ts := l_bcn_start_ts;
          l_bcn_up_end_ts   := l_bcn_avmarker;
          l_process_beacon := TRUE;
          TRACE_MSG(l_trc_msg, ('|BUs1:' || TO_CHAR(l_bcn_up_start_ts, k_trc_date_format) ||
                                '|BUe1:' || TO_CHAR(l_bcn_up_end_ts, k_trc_date_format)));
        ELSE
          -- ignore this beacon, no UP period found
          l_process_beacon := FALSE;
          TRACE_MSG(l_trc_msg, '|Bi:Not UP(1)');
        END IF;
      ELSIF l_bcn_avmarker <= l_cur_avmarker THEN
        -- ignore the beacon if it's not UP and its avail marker is not higher than
        -- the tests (i.e. it cannot have a valid UP period).
        -- this prevents blackout beacons from holding test avail updates
        l_process_beacon := FALSE;
        TRACE_MSG(l_trc_msg, '|Bi:Not UP(4)');
      ELSE
        -- the beacon is not currently UP, look for its latest UP period AFTER the
        -- avail marker of the test
        BEGIN
          -- don't select UP periods which end on the target's cur avail marker as
          -- it'd allow beacons that are now DOWN or in BLACKOUT,etc to vote.
          SELECT avail.start_collection_timestamp, avail.end_collection_timestamp
            INTO l_bcn_up_start_ts, l_bcn_up_end_ts
            FROM ( SELECT start_collection_timestamp, end_collection_timestamp
                     FROM MGMT_AVAILABILITY
                    WHERE target_guid = l_bcn.target_guid
                      AND current_status = MGMT_GLOBAL.G_STATUS_UP 
                      AND start_collection_timestamp <= l_bcn.marker_timestamp
                      AND end_collection_timestamp > l_svc_avmarker_in_bcntz
                    ORDER BY start_collection_timestamp DESC 
                 ) avail
           WHERE ROWNUM = 1;
          -- found an UP period
          IF l_bcn.timezone_region <> l_target_tz THEN
            l_bcn_up_start_ts := MGMT_GLOBAL.ADJUST_TZ( l_bcn_up_start_ts, 
                                                        l_bcn.timezone_region, l_target_tz );
          END IF;
          IF (l_bcn_up_end_ts IS NULL) OR (l_bcn_up_end_ts > l_bcn.marker_timestamp) THEN
            l_bcn_up_end_ts := l_bcn_avmarker;
          ELSIF l_bcn.timezone_region <> l_target_tz THEN
            l_bcn_up_end_ts := MGMT_GLOBAL.ADJUST_TZ( l_bcn_up_end_ts, 
                                                      l_bcn.timezone_region, l_target_tz );
          END IF;
          -- the up period must end after the test's av marker
          IF l_bcn_up_end_ts > l_cur_avmarker THEN
            TRACE_MSG(l_trc_msg, ('|BUs2:' || TO_CHAR(l_bcn_up_start_ts, k_trc_date_format) ||
                                  '|BUe2:' || TO_CHAR(l_bcn_up_end_ts, k_trc_date_format)));
            l_process_beacon := TRUE;
          ELSE
            TRACE_MSG(l_trc_msg, '|Bi: Not Up(3)');
            l_process_beacon := FALSE;
          END IF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            -- Ignore this beacon, no UP period found
            l_process_beacon := FALSE;
            TRACE_MSG(l_trc_msg, '|Bi:Not UP(2)');
        END;
      END IF;
      
      -- validate timestamps
      IF l_process_beacon THEN
        IF l_bcn_up_start_ts < l_cur_avmarker THEN
          l_bcn_up_start_ts := l_cur_avmarker;
        END IF;
       IF l_bcn_up_end_ts < l_bcn_up_start_ts THEN
         TRACE_MSG(l_trc_msg, '|Bi: Not Up(5)');
         l_process_beacon := FALSE;
       END IF;
      END IF;

      -- there's an initial portion in which the beacon was not UP, make it UNK
      IF l_process_beacon THEN
        IF l_bcn_up_start_ts > l_cur_avmarker THEN
          l_unk_found := TRUE;
          IF (l_unk_end_ts IS NULL) OR (l_unk_end_ts > l_bcn_up_start_ts) THEN
            l_unk_end_ts := l_bcn_up_start_ts;
          END IF;              
          l_process_beacon := FALSE;
          TRACE_MSG(l_trc_msg, '|Bi:Initial Unknown period');
        END IF;
      END IF;

      IF l_process_beacon THEN
        -- verify that the test is not in metric error from the beacon
        BEGIN
          l_coll_name := EMD_BCNTXN.EMD_BCN_GET_COLL_NAME(l_target_name, l_target_type, 
                                                          l_bcn.target_name, 
                                                          l_test_name, l_test_type);
          SELECT collection_timestamp
            INTO l_met_err_ts
            FROM MGMT_CURRENT_METRIC_ERRORS
           WHERE target_guid = p_target_guid
             AND metric_guid = l_err_metric_guid
             AND agent_guid = l_bcn.agent_guid
             AND coll_name = l_coll_name
             AND metric_error_type = 0;
          -- It's in metric error, chop off the portion in metric error from the UP period
          IF l_met_err_ts < l_bcn_up_end_ts THEN
            TRACE_MSG(l_trc_msg, '|Bi:Metric Error');
            IF l_met_err_ts <= l_bcn_up_start_ts THEN
              -- in metric error for the entire UP period, the beacon cannot vote
              l_unk_found := TRUE;
              IF (l_unk_end_ts IS NULL) OR (l_unk_end_ts > l_bcn_up_end_ts) THEN
                l_unk_end_ts := l_bcn_up_end_ts;
              END IF;              
              l_process_beacon := FALSE;
              TRACE_MSG(l_trc_msg, '|Bi:No vote');
            ELSE
              -- part of the UP period can be used
              l_bcn_up_end_ts := l_met_err_ts;
              TRACE_MSG(l_trc_msg, ('|Bue3:' || TO_CHAR(l_bcn_up_end_ts, k_trc_date_format)));
            END IF;
          END IF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            -- No metric errors, continue processing the beacon
            NULL;
        END;
      END IF;

      -- get the composite key
      IF l_process_beacon THEN
        SELECT composite_key
          INTO l_ck
          FROM MGMT_METRICS_COMPOSITE_KEYS
         WHERE target_guid = p_target_guid
           AND key_part1_value = l_test_name
           AND key_part2_value = l_bcn.target_name
           AND ( (key_part3_value IS NULL) OR (key_part3_value = ' ') )
           AND ( (key_part4_value IS NULL) OR (key_part4_value = ' ') )
           AND ( (key_part5_value IS NULL) OR (key_part5_value = ' ') );
      END IF;

      -- get the latest severity of the availability metric valid in the UP period
      -- calculated above.     
      IF l_process_beacon THEN
        BEGIN
          -- Get the severity
          l_found_sev := FALSE;
          IF l_blackout_end THEN
            BEGIN
              -- blackout end.  only use severities that came after the 
              -- blackout end event.
              SELECT severity_code, collection_timestamp, message
                INTO l_bcn_sev_code, l_bcn_sev_ts, l_bcn_sev_msg
                FROM ( SELECT severity_code, collection_timestamp, message
                         FROM MGMT_SEVERITY
                        WHERE target_guid = p_target_guid
                          AND metric_guid = l_metric_guid
                          AND key_value = l_ck
                          AND ( severity_code = MGMT_GLOBAL.G_SEVERITY_CLEAR
                              OR severity_code = MGMT_GLOBAL.G_SEVERITY_CRITICAL )
                          AND collection_timestamp <= l_bcn_up_end_ts
                          AND collection_timestamp >= l_cur_ts
                        ORDER BY collection_timestamp DESC )
                WHERE ROWNUM = 1;                
                l_found_sev := TRUE;
                TRACE_MSG(l_trc_msg, ('|Bc1:' || TO_CHAR(l_bcn_sev_code) ||
                                      '|Bt1:' || TO_CHAR(l_bcn_sev_ts, k_trc_date_format)));
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                l_found_sev := FALSE;
                TRACE_MSG(l_trc_msg, '|Bi:No new Sevs');
            END;
            IF NOT l_found_sev THEN
              -- the sev may have been rejected as a duplicate of the last one before
              -- blackout started.
              -- check if there is metric data that came after the blackout end
              -- to verify this.
              SELECT collection_timestamp
                INTO l_bcn_max_coll_ts
                FROM MGMT_CURRENT_METRICS
               WHERE target_guid = p_target_guid
                 AND metric_guid = l_metric_guid
                 AND key_value = l_ck
                 AND collection_timestamp > l_cur_ts;
              -- if we reach this point, it means that we've found metric data after 
              -- the blackout end, use the latest sev.  if there wasn't any, the query
              -- would've raised an exception.             
              TRACE_MSG(l_trc_msg, '|Bi:Metric data');
            END IF;
          END IF;
          IF NOT l_found_sev THEN
            SELECT severity_code, collection_timestamp, message
              INTO l_bcn_sev_code, l_bcn_sev_ts, l_bcn_sev_msg
              FROM ( SELECT severity_code, collection_timestamp, message
                       FROM MGMT_SEVERITY
                      WHERE target_guid = p_target_guid
                        AND metric_guid = l_metric_guid
                        AND key_value = l_ck
                        AND ( severity_code = MGMT_GLOBAL.G_SEVERITY_CLEAR
                            OR severity_code = MGMT_GLOBAL.G_SEVERITY_CRITICAL )
                        AND collection_timestamp <= l_bcn_up_end_ts
                      ORDER BY collection_timestamp DESC )
              WHERE ROWNUM = 1;
          END IF;
          TRACE_MSG(l_trc_msg, ('|Bc2:' || TO_CHAR(l_bcn_sev_code) ||
                                '|Bt2:' || TO_CHAR(l_bcn_sev_ts, k_trc_date_format)));
          -- Get the number of severities that occurred between the
          -- old avmrkr and the latest sevs
          l_has_sevs := 0;
          IF l_bcn_sev_ts > l_cur_avmarker THEN
            BEGIN
              SELECT 1
                INTO l_has_sevs
                FROM DUAL
               WHERE EXISTS ( SELECT 1
                                FROM MGMT_SEVERITY
                               WHERE target_guid = p_target_guid
                                 AND metric_guid = l_metric_guid
                                 AND key_value = l_ck
                                 AND ( severity_code = MGMT_GLOBAL.G_SEVERITY_CLEAR
                                       OR severity_code = MGMT_GLOBAL.G_SEVERITY_CRITICAL )
                                 AND collection_timestamp > l_cur_avmarker
                                 AND collection_timestamp < l_bcn_sev_ts );
            EXCEPTION
              WHEN OTHERS THEN
                -- ignore
                NULL;
            END;
          END IF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            -- No sevs have yet made it to repos
            l_process_beacon := FALSE;
            IF l_bcn_up_end_ts > l_cur_ts THEN
              -- however, its avail marker is greater than the target's.
              -- use it as an UNK vote.
              l_unk_found := TRUE;
              IF (l_unk_end_ts IS NULL) OR (l_unk_end_ts > l_bcn_up_end_ts) THEN
                l_unk_end_ts := l_bcn_up_end_ts;
              END IF;              
            END IF;
            TRACE_MSG(l_trc_msg, '|Bi:No sevs');
        END;
      END IF;

      -- Process the severity
      IF l_process_beacon THEN 
        IF ( l_bcn_sev_code = MGMT_GLOBAL.G_SEVERITY_CLEAR ) THEN
          IF l_has_sevs > 0 THEN
            -- there were several UP/DOWN severities.  the period from
            -- old avmrkr to the latest sev is made UNKNOWN
            IF (l_unk_end_ts IS NULL) OR (l_unk_end_ts > l_bcn_sev_ts) THEN
              l_unk_end_ts := l_bcn_sev_ts;
            END IF;
            l_unk_found := TRUE;
            TRACE_MSG(l_trc_msg, '|Bv:Unk(1)');
          ELSE
            -- UP vote 
            IF l_bcn_sev_ts IS NOT NULL THEN
              l_bcn_up_start_ts := GREATEST(l_bcn_up_start_ts, l_bcn_sev_ts);
            END IF;
            IF (l_up_sev_ts IS NULL) OR (l_up_sev_ts > l_bcn_up_start_ts) THEN
              l_up_sev_ts := l_bcn_up_start_ts;
            END IF;
            IF (l_up_end_ts IS NULL) OR (l_up_end_ts < l_bcn_up_end_ts) THEN
              l_up_end_ts := l_bcn_up_end_ts;
            END IF;
            l_up_found := TRUE;
            TRACE_MSG(l_trc_msg, '|Bv:Up');
          END IF;
        ELSIF (l_bcn_sev_code = MGMT_GLOBAL.G_SEVERITY_CRITICAL) THEN
          IF l_has_sevs > 0 THEN
            -- there were several UP/DOWN severities.  the period from
            -- old avmrkr to the latest sev is made UNKNOWN
            IF (l_unk_end_ts IS NULL) OR (l_unk_end_ts > l_bcn_sev_ts) THEN
              l_unk_end_ts := l_bcn_sev_ts;
            END IF;
            l_unk_found := TRUE;
            TRACE_MSG(l_trc_msg, '|Bv:Unk(2)');
          ELSE
            -- DOWN vote
            IF l_bcn_sev_ts IS NOT NULL THEN
              l_bcn_up_start_ts := GREATEST(l_bcn_up_start_ts, l_bcn_sev_ts);
            END IF;
            IF (l_dn_sev_ts IS NULL) OR (l_dn_sev_ts < l_bcn_up_start_ts) THEN
              l_dn_sev_ts := l_bcn_up_start_ts;
            END IF;
            IF (l_dn_end_ts IS NULL) OR (l_dn_end_ts > l_bcn_up_end_ts) THEN
              l_dn_end_ts := l_bcn_up_end_ts;
            END IF;
            l_dn_found := TRUE;
            TRACE_MSG(l_trc_msg, '|Bv:Down');
          END IF;
        END IF;
      END IF;
    END IF;
  END LOOP;

  IF l_up_found = TRUE THEN
    IF (l_up_sev_ts IS NOT NULL) AND (l_up_end_ts IS NOT NULL) AND
       (l_up_sev_ts <= l_up_end_ts) THEN
      TRACE_MSG(l_trc_msg, ('|Ns:UP' ||
                            '|Nst:' || TO_CHAR(l_up_sev_ts, k_trc_date_format) ||
                            '|Nse:' || TO_CHAR(l_up_end_ts, k_trc_date_format)));
      UPDATE_TEST_AVAIL( p_target_guid, p_test_guid, MGMT_GLOBAL.G_STATUS_UP,
                         l_up_sev_ts, l_up_end_ts, TRUE, l_avail_updated );
    END IF;
  ELSIF l_dn_found = TRUE THEN
    IF (l_dn_sev_ts IS NOT NULL) AND (l_dn_end_ts IS NOT NULL) AND
       (l_dn_sev_ts <= l_dn_end_ts) THEN
      TRACE_MSG(l_trc_msg, ('|Ns:DOWN' ||
                            '|Nst:' || TO_CHAR(l_dn_sev_ts, k_trc_date_format) ||
                            '|Nse:' || TO_CHAR(l_dn_end_ts, k_trc_date_format)));
      UPDATE_TEST_AVAIL( p_target_guid, p_test_guid, MGMT_GLOBAL.G_STATUS_DOWN,
                         l_dn_sev_ts, l_dn_end_ts, TRUE, l_avail_updated );
    END IF;
  ELSIF l_unk_found = TRUE THEN
    IF (l_cur_avmarker IS NOT NULL) AND (l_unk_end_ts IS NOT NULL) AND
       (l_cur_avmarker <= l_unk_end_ts) THEN
      TRACE_MSG(l_trc_msg, ('|Ns:UNK' ||
                            '|Nst:' || TO_CHAR(l_cur_avmarker, k_trc_date_format) ||
                            '|Nse:' || TO_CHAR(l_unk_end_ts, k_trc_date_format)));
      UPDATE_TEST_AVAIL( p_target_guid, p_test_guid, MGMT_GLOBAL.G_STATUS_UNKNOWN,
                         l_cur_avmarker, l_unk_end_ts, TRUE, l_avail_updated );   
      -- if the new status is UNKNOWN and there's a beacon with an av marker higher 
      -- than the new webapp av marker, it means that we can recalculate quickly as 
      -- there's enough data to change the webapp to UP/DOWN.
      IF (l_cur_status <> MGMT_GLOBAL.G_STATUS_UNKNOWN) AND 
         (l_unk_end_ts < l_max_bcn_avmarker) THEN
        p_next_run := min_job_sleep_key;
      ELSIF (l_cur_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) THEN
        p_next_run := min_job_sleep_key;
        -- andyao commented this line below and use k_hi_pri_job_sleep instead
        -- because k_lo_pri_job_sleep is too long when the test was created.
        -- p_next_run := k_lo_pri_job_sleep;
      END IF;
    END IF;
  END IF;

  -- reschedule for a quick reevaluation if the avail status/marker changed
  IF l_avail_updated THEN
    p_next_run := min_job_sleep_key;
  END IF;

<<DONE_TSTAV>>
  IF l_bcn_list%ISOPEN THEN
    CLOSE l_bcn_list;
  END IF;
  -- log the trace msg
  IF l_avail_updated THEN
    -- not really an error, but we need it logged and only ERRORs are enabled 
    -- out-of-the-box
    EMDW_LOG.info(l_trc_msg, MODULE_NAME);
  ELSE
    EMDW_LOG.debug(l_trc_msg, MODULE_NAME);
  END IF;
  -- reset back to default ctx
  EMDW_LOG.set_context;

  --dbms_output.put_line('test avail: ' || l_trc_msg);

EXCEPTION

  WHEN OTHERS THEN
    IF l_bcn_list%ISOPEN THEN
      CLOSE l_bcn_list;
    END IF;
    TRACE_MSG(l_trc_msg, ('|Ex:' || SQLERRM));
    EMDW_LOG.error(l_trc_msg, MODULE_NAME);
    -- reset back to default ctx
    EMDW_LOG.set_context;
    -- dbms_output.put_line('test avail error: ' || l_trc_msg);
    RAISE;
END COMPUTE_TEST_AVAIL;

--------------------------------------------------------------------------
PROCEDURE DO_SVC_AVAIL ( p_target_guid IN RAW,
                         p_target_name IN VARCHAR2, 
                         p_eval_logic IN NUMBER, 
                         p_run_again OUT BOOLEAN )
IS

  l_cur_status MGMT_CURRENT_AVAILABILITY.current_status%TYPE;
  l_cur_ts MGMT_CURRENT_AVAILABILITY.start_collection_timestamp%TYPE;
  l_cur_avmarker MGMT_AVAILABILITY_MARKER.marker_timestamp%TYPE;
  l_new_status MGMT_CURRENT_AVAILABILITY.current_status%TYPE;
  l_new_avmarker MGMT_AVAILABILITY_MARKER.marker_timestamp%TYPE;
  l_test_status MGMT_GENSVC_TEST_AVAIL.current_status%TYPE;
  l_test_start_ts MGMT_GENSVC_TEST_AVAIL.start_collection_timestamp%TYPE;
  l_test_end_ts MGMT_GENSVC_TEST_AVAIL.end_collection_timestamp%TYPE;
  l_av_metric_guid MGMT_METRICS.metric_guid%TYPE;

  l_sev_msg VARCHAR2(4000);
  l_sev_msg_id VARCHAR2(64);
  l_up_tests VARCHAR2(3800);
  l_dn_tests VARCHAR2(3800);

  l_unk_found BOOLEAN := FALSE;
  l_unk_end_ts DATE := NULL;
  l_up_found BOOLEAN := FALSE;
  l_up_end_ts DATE := NULL;
  l_dn_found BOOLEAN := FALSE;
  l_dn_end_ts DATE := NULL;
  l_nodata_found BOOLEAN := FALSE;
  l_sev_guid RAW(16);
  l_avail_updated BOOLEAN;
  l_more BOOLEAN;
  l_trc_msg VARCHAR2(1000);
  l_in_blackout BOOLEAN;
  l_new_end_ts DATE;

  CURSOR l_test_list IS
    SELECT svc_tests.test_guid, 
           svc_tests.test_name,
           test_marker.marker_timestamp
      FROM MGMT_GENSVC_AVAIL_TESTS svc_tests, 
           MGMT_GENSVC_TEST_AVAIL_MARKER test_marker
     WHERE svc_tests.target_guid = p_target_guid
       AND svc_tests.avail_test = 1
       AND test_marker.target_guid = svc_tests.target_guid
       AND test_marker.test_guid = svc_tests.test_guid;

  l_test l_test_list%ROWTYPE;

BEGIN

  -- do not rerun the avail computation by default
  p_run_again := FALSE;  
  l_avail_updated := FALSE;
  l_trc_msg := 'SVCAV';

  -- set the svc avail trace ctx
  EMDW_LOG.set_context( v_context_type=>EMDW_LOG_CTX,
                        v_context_identifier=>'Svc Avail' );

  TRACE_MSG(l_trc_msg, ('|G:' || RAWTOHEX(p_target_guid)));
  TRACE_MSG(l_trc_msg, ('|N:' || p_target_name));
  TRACE_MSG(l_trc_msg, ('|L:' || TO_CHAR(p_eval_logic)));

  -- get the target's current status 
  SELECT current_status, start_collection_timestamp
    INTO l_cur_status, l_cur_ts
    FROM MGMT_CURRENT_AVAILABILITY
   WHERE target_guid = p_target_guid;
  TRACE_MSG(l_trc_msg, ('|ST:' || TO_CHAR(l_cur_status) || 
                        '|CT:' || TO_CHAR(l_cur_ts, k_trc_date_format)));

  -- get the test's avail marker
  SELECT marker_timestamp
    INTO l_cur_avmarker
    FROM MGMT_AVAILABILITY_MARKER
   WHERE target_guid = p_target_guid;
  TRACE_MSG(l_trc_msg, ('|AM:' || TO_CHAR(l_cur_avmarker, k_trc_date_format)));

  -- the blackout start event does not move the marker
  IF l_cur_avmarker < l_cur_ts THEN
    l_cur_avmarker := l_cur_ts;
  END IF;

  -- bail if the target is in blackout
  IF (IS_IN_BLACKOUT(p_target_guid, l_cur_avmarker)) THEN    
    TRACE_MSG(l_trc_msg, '|I:Svc in Blackout(1)');
    GOTO DONE_SVCAV;
  END IF;

  -- process the tests
  l_more := TRUE;
  OPEN l_test_list;
  WHILE l_more LOOP
    FETCH l_test_list INTO l_test;
    l_more := l_test_list%FOUND;
    IF l_more THEN
      TRACE_MSG(l_trc_msg, ('|Tn:' || l_test.test_name ||
                            '|Tg:' || l_test.test_guid ||
                            '|Tam:' || TO_CHAR(l_test.marker_timestamp, k_trc_date_format)));

      -- verify that the test's marker is greater than the service's
      IF l_test.marker_timestamp <= l_cur_avmarker THEN
        -- the test's marker is older than the services.  
        l_nodata_found := TRUE;
        TRACE_MSG(l_trc_msg, '|I:No data(1)');
        GOTO NEXT_TEST;
      END IF;
      -- test's marker is ok, get the avail record.
      -- find the avail record with the earliest end timestamp that is higher than
      -- the current avail marker.  
      -- if none is found, i.e. no avail records exist with an end timestamp higher than
      -- the current avail marker, use the current avail record.
      -- when using the current avail record, make sure to account any leading time to unknown if
      -- it started after the current avail record. 
      BEGIN
        SELECT test_avail.current_status, test_avail.start_collection_timestamp,
               test_avail.end_collection_timestamp
          INTO l_test_status, l_test_start_ts, l_test_end_ts
          FROM ( SELECT current_status, start_collection_timestamp, 
                        end_collection_timestamp
                   FROM MGMT_GENSVC_TEST_AVAIL
                  WHERE target_guid = p_target_guid
                    AND test_guid = l_test.test_guid
                    AND end_collection_timestamp > l_cur_avmarker
                  ORDER BY end_collection_timestamp ASC
               ) test_avail
        WHERE ROWNUM = 1;
        TRACE_MSG(l_trc_msg, ('|Tas1:' || TO_CHAR(l_test_status) ||
                              '|Tab1:' || TO_CHAR(l_test_start_ts, k_trc_date_format) ||
                              '|Tae1:' || TO_CHAR(l_test_end_ts, k_trc_date_format)));
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          -- none were found, use the current avail record
          BEGIN
            SELECT current_status, start_collection_timestamp
              INTO l_test_status, l_test_start_ts
              FROM MGMT_GENSVC_TEST_CUR_AVAIL
             WHERE target_guid = p_target_guid
               AND test_guid = l_test.test_guid;
            l_test_end_ts := l_test.marker_timestamp;
          TRACE_MSG(l_trc_msg, ('|Tas2:' || TO_CHAR(l_test_status) ||
                                '|Tab2:' || TO_CHAR(l_test_start_ts, k_trc_date_format) ||
                                '|Tae2:' || TO_CHAR(l_test_end_ts, k_trc_date_format)));
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
              -- the test does not have an avail record yet
              l_nodata_found := TRUE;
              TRACE_MSG(l_trc_msg, '|I:No data(2)');
              GOTO NEXT_TEST;
          END;
      END;
 
      -- validate test data
      IF l_test_start_ts > l_test_end_ts THEN
        l_nodata_found := TRUE;
        TRACE_MSG(l_trc_msg, '|I:No data(3)');
        GOTO NEXT_TEST;
      END IF;

      -- got the avail record to use
      -- make sure it doesn't have a leading period which is unknown
      IF l_test_start_ts > l_cur_avmarker THEN
        l_unk_found := TRUE;
        IF p_eval_logic = k_and_eval THEN
          -- AND eval logic
          IF (l_unk_end_ts IS NULL) OR (l_unk_end_ts < l_test_start_ts) THEN
            l_unk_end_ts := l_test_start_ts;
          END IF;
        ELSE
          -- OR eval logic
          IF (l_unk_end_ts IS NULL) OR (l_unk_end_ts > l_test_start_ts) THEN
            l_unk_end_ts := l_test_start_ts;
          END IF;
        END IF;
        TRACE_MSG(l_trc_msg, ('|I:Unk(1) ' || TO_CHAR(l_unk_end_ts, k_trc_date_format)));
        GOTO NEXT_TEST;
      END IF;
      -- the avail record applies from the current avail marker, it can be used as proper vote
      IF l_test_status = MGMT_GLOBAL.G_STATUS_UP THEN
        l_up_found := TRUE;
        IF p_eval_logic = k_and_eval THEN
          -- AND eval logic
          IF (l_up_end_ts IS NULL) OR (l_test_end_ts < l_up_end_ts) THEN
            l_up_end_ts := l_test_end_ts;
          END IF;
        ELSE
          -- OR eval logic
          IF (l_up_end_ts IS NULL) OR (l_test_end_ts > l_up_end_ts) THEN
            l_up_end_ts := l_test_end_ts;
          END IF;
        END IF;
        BEGIN
          IF LENGTH(l_up_tests) IS NOT NULL THEN
            l_up_tests := l_up_tests || ',';
          END IF;
          l_up_tests := l_up_tests || l_test.test_name;
        EXCEPTION
          WHEN VALUE_ERROR THEN
            -- run out of space in the string
            NULL;
        END;
        TRACE_MSG(l_trc_msg, ('|I:Up ' || TO_CHAR(l_up_end_ts, k_trc_date_format)));
      ELSIF l_test_status = MGMT_GLOBAL.G_STATUS_DOWN THEN 
        l_dn_found := TRUE;
        IF p_eval_logic = k_and_eval THEN
          -- AND eval logic
          IF (l_dn_end_ts IS NULL) OR (l_test_end_ts > l_dn_end_ts) THEN
            l_dn_end_ts := l_test_end_ts;
          END IF;
        ELSE
          -- OR eval logic
          IF (l_dn_end_ts IS NULL) OR (l_test_end_ts < l_dn_end_ts) THEN
            l_dn_end_ts := l_test_end_ts;
          END IF;
        END IF;
        BEGIN
          IF LENGTH(l_dn_tests) IS NOT NULL THEN
            l_dn_tests := l_dn_tests || ',';
          END IF;
          l_dn_tests := l_dn_tests || l_test.test_name;
        EXCEPTION
          WHEN VALUE_ERROR THEN
            -- run out of space in the string
            NULL;
        END;
        TRACE_MSG(l_trc_msg, ('|I:Dn ' || TO_CHAR(l_dn_end_ts, k_trc_date_format)));
      ELSIF (l_test_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) OR
            (l_test_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN 
        l_unk_found := TRUE;
        IF p_eval_logic = k_and_eval THEN
          -- AND eval logic
          IF (l_unk_end_ts IS NULL) OR (l_test_end_ts > l_unk_end_ts) THEN
            l_unk_end_ts := l_test_end_ts;
          END IF;
        ELSE
          -- OR eval logic
          IF (l_unk_end_ts IS NULL) OR (l_test_end_ts < l_unk_end_ts) THEN
            l_unk_end_ts := l_test_end_ts;
          END IF;
        END IF;
        TRACE_MSG(l_trc_msg, ('|I:Unk(2) ' || TO_CHAR(l_unk_end_ts, k_trc_date_format)));
      ELSE
        -- invalid status
        NULL;  -- TO DO: error
        TRACE_MSG(l_trc_msg, '|I:Invalid Status');
     END IF;
    END IF;

<<NEXT_TEST>>
    NULL;
  END LOOP;

  CLOSE l_test_list;

  -- process the tests data
  IF p_eval_logic = k_and_eval THEN
    -- AND eval logic
    IF l_dn_found THEN
      -- if test down found -> target is down
      l_new_status := MGMT_GLOBAL.G_STATUS_DOWN;
      l_new_avmarker := l_dn_end_ts;
      TRACE_MSG(l_trc_msg, '|Ns:Down(1)');
    ELSIF NOT l_nodata_found THEN
      -- must have data from all tests to set the target to up or unk
      IF l_unk_found THEN
        -- no tests are down, and at least one is unk -> target is unk
        l_new_status := MGMT_GLOBAL.G_STATUS_UNKNOWN;
        l_new_avmarker := l_unk_end_ts;
        TRACE_MSG(l_trc_msg, '|Ns:Unk(1)');
      ELSIF l_up_found THEN
        -- all tests are reporting up -> the target is up
        l_new_status := MGMT_GLOBAL.G_STATUS_UP;
        l_new_avmarker := l_up_end_ts;
        TRACE_MSG(l_trc_msg, '|Ns:Up(1)');
      ELSE
        TRACE_MSG(l_trc_msg, '|Ns:No Change(1)');
        GOTO DONE_SVCAV;
      END IF;
    ELSE
      TRACE_MSG(l_trc_msg, '|Ns:No Change(2)');
      GOTO DONE_SVCAV;
    END IF;
  ELSE
    -- OR eval logic
    IF l_up_found THEN
      -- if test up found -> target is up
      l_new_status := MGMT_GLOBAL.G_STATUS_UP;
      l_new_avmarker := l_up_end_ts;
      TRACE_MSG(l_trc_msg, '|Ns:Up(2)');
    ELSIF NOT l_nodata_found THEN
      -- must have data from all tests to set the target to down or unk
      IF l_unk_found THEN
        -- no tests are up, and at least one is unk -> target is unk
        l_new_status := MGMT_GLOBAL.G_STATUS_UNKNOWN;
        l_new_avmarker := l_unk_end_ts;
        TRACE_MSG(l_trc_msg, '|Ns:Unk(2)');
      ELSIF l_dn_found AND (l_nodata_found = FALSE) THEN
        -- all tests are reporting down -> the target is down
        l_new_status := MGMT_GLOBAL.G_STATUS_DOWN;
        l_new_avmarker := l_dn_end_ts;
        TRACE_MSG(l_trc_msg, '|Ns:Dn(2)');
      ELSE
        TRACE_MSG(l_trc_msg, '|Ns:No Change(3)');
        GOTO DONE_SVCAV;
      END IF;
    ELSE
      TRACE_MSG(l_trc_msg, '|Ns:No Change(4)');
      GOTO DONE_SVCAV;
    END IF;
  END IF;

  TRACE_MSG(l_trc_msg, ('|Nam:' || TO_CHAR(l_new_avmarker, k_trc_date_format)));

  -- validate results
  IF l_new_avmarker < l_cur_avmarker THEN
    TRACE_MSG(l_trc_msg, '|Ex:Invalid New Av Marker');
    GOTO DONE_SVCAV;
  ELSIF (l_new_avmarker = l_cur_avmarker) THEN 
    IF (l_new_status = l_cur_status) THEN
      TRACE_MSG(l_trc_msg, '|I:No new data(0)');
      GOTO DONE_SVCAV;
    END IF;
    IF(l_new_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) THEN
      TRACE_MSG(l_trc_msg, '|I:No new data(1)');
      GOTO DONE_SVCAV;
    END IF;
    IF p_eval_logic = k_and_eval THEN
      IF (l_cur_status = MGMT_GLOBAL.G_STATUS_DOWN)
         AND (l_new_status = MGMT_GLOBAL.G_STATUS_UP) THEN
        TRACE_MSG(l_trc_msg, '|I:No new data(2)');
        GOTO DONE_SVCAV;
      END IF;
    ELSIF p_eval_logic = k_or_eval THEN
      IF (l_cur_status = MGMT_GLOBAL.G_STATUS_UP)
         AND (l_new_status = MGMT_GLOBAL.G_STATUS_DOWN) THEN
        TRACE_MSG(l_trc_msg, '|I:No new data(3)');
        GOTO DONE_SVCAV;
      END IF;
    END IF;
  END IF;

  l_in_blackout := IS_IN_BLACKOUT(p_target_guid, l_cur_avmarker, l_new_avmarker, l_new_end_ts);
  IF (l_in_blackout) OR (l_new_end_ts IS NULL) OR 
     (l_new_end_ts < l_cur_avmarker) THEN
    TRACE_MSG(l_trc_msg, '|I:Svc in Blackout(2)');
    GOTO DONE_SVCAV;
  END IF;

  TRACE_MSG(l_trc_msg, ('|Nam2:' || TO_CHAR(l_new_end_ts, k_trc_date_format)));

  -- update the availability of the target
  IF l_new_status <> l_cur_status THEN
    -- change in avail
    -- get the avail metric for the target
    SELECT metric_guid
      INTO l_av_metric_guid
      FROM MGMT_TARGETS t, MGMT_METRICS m
     WHERE t.target_guid = p_target_guid
       AND m.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
       AND m.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
       AND t.target_type = m.target_type
       AND t.type_meta_ver = m.type_meta_ver
       AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ')
       AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ')
       AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ')
       AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ')
       AND (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ');
    -- insert the severity to change the avail status
    l_sev_guid := SYS_GUID();
    BEGIN
      IF l_new_status = MGMT_GLOBAL.G_STATUS_UP THEN
        l_sev_msg := 'The following key tests are up: ' || l_up_tests;
        l_sev_msg_id := 'TESTS_ARE_UP';
        INSERT INTO MGMT_SEVERITY
          ( target_guid, metric_guid, collection_timestamp, 
            severity_guid, severity_code, key_value, message, message_nlsid, message_params )
        VALUES
          ( p_target_guid, l_av_metric_guid, l_cur_avmarker, l_sev_guid, 
            MGMT_GLOBAL.G_SEVERITY_CLEAR, ' ', l_sev_msg, l_sev_msg_id, l_up_tests );
      ELSIF l_new_status = MGMT_GLOBAL.G_STATUS_DOWN THEN
        l_sev_msg := 'The following key tests are down: ' || l_dn_tests;
        l_sev_msg_id := 'TESTS_ARE_DOWN';
        INSERT INTO MGMT_SEVERITY
          ( target_guid, metric_guid, collection_timestamp, 
            severity_guid, severity_code, key_value, message , message_nlsid, message_params )
        VALUES
          ( p_target_guid, l_av_metric_guid, l_cur_avmarker, l_sev_guid, 
            MGMT_GLOBAL.G_SEVERITY_CRITICAL, ' ', l_sev_msg, l_sev_msg_id, l_dn_tests );
      ELSIF l_new_status = MGMT_GLOBAL.G_STATUS_UNKNOWN THEN
        l_sev_msg := 'The availability status of the key test(s) could not be evaluated.';
        l_sev_msg_id := 'TESTS_ARE_UNKNOWN';
        INSERT INTO MGMT_SEVERITY
          ( target_guid, metric_guid, collection_timestamp, 
            severity_guid, severity_code, key_value, message , message_nlsid, message_params )
        VALUES
          ( p_target_guid, l_av_metric_guid, l_cur_avmarker, l_sev_guid, 
            MGMT_GLOBAL.G_SEVERITY_NO_BEACONS, ' ', l_sev_msg , l_sev_msg_id, '');
      END IF;
      TRACE_MSG(l_trc_msg, '|I:Status updated');
      l_avail_updated := TRUE;
      -- update the avail marker
      EM_SEVERITY.update_availability_marker(p_target_guid, l_new_end_ts, l_new_status);
      TRACE_MSG(l_trc_msg, '|I:Marker updated(1)');
      -- the avail marker/status was changed, recompute
      p_run_again := TRUE;  
    EXCEPTION
      WHEN MGMT_GLOBAL.duplicate_record OR DUP_VAL_ON_INDEX THEN
        -- Dont raise the exception so that the target is removed from the 
        -- list.  This can be done because the current severity already is
        -- of the value that we want to insert.
        EM_SEVERITY.update_availability_marker(p_target_guid, l_new_end_ts);
        TRACE_MSG(l_trc_msg, '|I:Marker updated(2)');
        -- the avail marker/status was changed, recompute
        p_run_again := TRUE;  
    END;
  ELSE
    -- same severity, just move the marker
    EM_SEVERITY.update_availability_marker(p_target_guid, l_new_end_ts);
    TRACE_MSG(l_trc_msg, '|I:Marker updated(3)');
    -- the avail marker/status was changed, recompute
    p_run_again := TRUE;  
  END IF;

<<DONE_SVCAV>>
  IF l_test_list%ISOPEN THEN
    CLOSE l_test_list;
  END IF;

  -- log the trace msg
  IF l_avail_updated THEN
    -- not really an error, but we need it logged and only ERRORs are enabled 
    -- out-of-the-box
    EMDW_LOG.info(l_trc_msg, MODULE_NAME);
  ELSE
    EMDW_LOG.debug(l_trc_msg, MODULE_NAME);
  END IF;
  -- reset back to default ctx
  EMDW_LOG.set_context;

  --dbms_output.put_line('svc avail: ' || l_trc_msg);

EXCEPTION
  WHEN OTHERS THEN
    IF l_test_list%ISOPEN THEN
      CLOSE l_test_list;
    END IF;
    TRACE_MSG(l_trc_msg, ('|Ex:' || SQLERRM));
    EMDW_LOG.error(l_trc_msg, MODULE_NAME);
    -- reset back to default ctx
    EMDW_LOG.set_context;
    -- dbms_output.put_line('svc avail error: ' || l_trc_msg);
    RAISE;

END DO_SVC_AVAIL;

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


--------------------------------------------------------------------------
PROCEDURE COMPUTE_SVC_AVAIL( p_target_guid IN RAW,
                             p_next_run OUT NUMBER )
IS

  l_target_name MGMT_TARGETS.target_name%TYPE;
  l_avail_enabled  MGMT_GENSVC_AVAIL_CONFIG.avail_enabled%TYPE;
  l_eval_logic MGMT_GENSVC_AVAIL_CONFIG.eval_logic%TYPE;
  l_run_again BOOLEAN;
  l_count NUMBER;

BEGIN

  -- re-compute avail in max mins by default
  p_next_run := max_job_sleep_key;

  -- validate parameters
  IF p_target_guid IS NULL THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'Invalid Service Target GUID');
  END IF;

  -- get the target name
  SELECT target_name
    INTO l_target_name
    FROM MGMT_TARGETS
   WHERE target_guid = p_target_guid;

  -- get the target's avail config
  SELECT avail_enabled, eval_logic
    INTO l_avail_enabled, l_eval_logic
    FROM MGMT_GENSVC_AVAIL_CONFIG
   WHERE target_guid = p_target_guid;
  IF l_avail_enabled <> 1 THEN
    -- bail if it's not configured to run beacon-based avail
    p_next_run := NULL;
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'Service Target is Not Configured to Use Test Availability (tgt: ' || RAWTOHEX(p_target_guid) || ')');
  END IF;

  l_run_again := TRUE;
  l_count := 0;
  WHILE l_run_again AND (l_count < 10) LOOP
    DO_SVC_AVAIL( p_target_guid, l_target_name, l_eval_logic, l_run_again );
    l_count := l_count + 1;
  END LOOP;
  IF l_run_again THEN
    -- run out of time for this target but there's more to do. reschedule for a quicker reeval
    p_next_run := min_job_sleep_key;
  END IF;

END COMPUTE_SVC_AVAIL;

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


--------------------------------------------------------------------------
-- Private Methods
--------------------------------------------------------------------------
PROCEDURE GET_PERIODS

IS

BEGIN

   BEGIN
    SELECT to_number(parameter_value) into v_min_job_sleep
      FROM mgmt_parameters
     WHERE parameter_name = to_char(min_job_sleep_key);
   EXCEPTION WHEN NO_DATA_FOUND THEN
    v_min_job_sleep := k_min_job_sleep;
   END;

   BEGIN
    SELECT to_number(parameter_value) into v_dep_job_sleep
      FROM mgmt_parameters
     WHERE parameter_name = to_char(dep_job_sleep_key);
   EXCEPTION WHEN NO_DATA_FOUND THEN
    v_dep_job_sleep := k_dep_job_sleep;
   END;

   BEGIN
    SELECT to_number(parameter_value) into v_max_job_sleep
      FROM mgmt_parameters
     WHERE parameter_name = to_char(max_job_sleep_key);
   EXCEPTION WHEN NO_DATA_FOUND THEN
    v_max_job_sleep := k_max_job_sleep;
   END;

   BEGIN
    SELECT to_number(parameter_value) into v_init_job_sleep
      FROM mgmt_parameters
     WHERE parameter_name = to_char(init_job_sleep_key);
   EXCEPTION WHEN NO_DATA_FOUND THEN
    v_init_job_sleep := k_init_job_sleep;
   END;

END GET_PERIODS;

--------------------------------------------------------------------------
FUNCTION GET_RESCHEDULE_PERIOD(p_period_key IN NUMBER)
RETURN NUMBER
IS
BEGIN


  IF (v_min_job_sleep  is null OR
      v_max_job_sleep  is null OR
      v_init_job_sleep is null OR
      v_dep_job_sleep  is null) THEN
    GET_PERIODS;
  END IF;

 IF (p_period_key = min_job_sleep_key) THEN
  RETURN v_min_job_sleep;
 END IF;

 IF (p_period_key = max_job_sleep_key) THEN
  RETURN v_max_job_sleep;
 END IF;

 IF (p_period_key = init_job_sleep_key) THEN
  RETURN v_init_job_sleep;
 END IF;

 IF (p_period_key = dep_job_sleep_key) THEN
  RETURN v_dep_job_sleep;
 END IF;

 RETURN 0; -- doesnt matter

END GET_RESCHEDULE_PERIOD;

--------------------------------------------------------------------------
PROCEDURE SCHEDULE_JOB ( p_target_guid IN RAW, 
                         p_test_guid IN RAW, 
                         p_period_key IN NUMBER)
IS


  l_next_run    DATE;
  l_test_guid   RAW(16);
  l_mins        NUMBER;

BEGIN

  -- compute the time of the next run in UTC
  l_next_run := CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE);
  l_mins := GET_RESCHEDULE_PERIOD(p_period_key);
  
  l_next_run := l_next_run + l_mins/1440;

  IF p_test_guid IS NULL THEN
    l_test_guid := k_no_test;
  ELSE
    l_test_guid := p_test_guid;
  END IF;

  BEGIN
    INSERT INTO MGMT_GENSVC_AVAIL_JOB
      ( target_guid, test_guid, next_run )
    VALUES
      ( p_target_guid, l_test_guid, l_next_run);
    RETURN;
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      -- there's a record already, reschedule it
      RESCHEDULE_JOB(p_target_guid, l_test_guid, p_period_key, NULL);
  END;
END SCHEDULE_JOB;

--------------------------------------------------------------------------
PROCEDURE RESCHEDULE_JOB ( p_target_guid IN RAW, 
                           p_test_guid IN RAW, 
                           p_period_key IN NUMBER,
                           p_in_error IN NUMBER )
IS

  l_cur_time     DATE;
  l_cur_next_run DATE;
  l_cur_in_error NUMBER;
  l_new_next_run DATE;
  l_new_in_error NUMBER;
  l_test_guid    RAW(16);
  l_mins        NUMBER;
  l_is_leaf_svc NUMBER;
  l_temp_mins   NUMBER;
  l_target_type MGMT_TARGETS.TARGET_TYPE%TYPE;
  l_avail_metric MGMT_METRICS.METRIC_GUID%TYPE;

BEGIN

  -- compute the time of the next run in UTC
  l_cur_time := CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE);
  l_mins := GET_RESCHEDULE_PERIOD(p_period_key);

  -- find out the avail metric guid for the target
  SELECT target_type
    INTO l_target_type
    FROM MGMT_TARGETS
   WHERE target_guid = p_target_guid;

  l_avail_metric := mgmt_target.get_metric_guid( l_target_type,
                                                 MGMT_GLOBAL.G_AVAIL_METRIC_NAME,
                                                 MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN );

  -- find out if its leaf svc
  SELECT COUNT(*) INTO l_is_leaf_svc
    FROM mgmt_metric_dependency_details 
   WHERE dep_target_guid = p_target_guid 
     AND target_guid <> p_target_guid
     AND dep_metric_guid = l_avail_metric;

  IF (l_is_leaf_svc > 0) THEN
    l_temp_mins := GET_RESCHEDULE_PERIOD(dep_job_sleep_key);
    IF (l_mins > l_temp_mins) THEN
      l_mins := l_temp_mins;
    END IF;
  END IF;
    
  l_new_next_run := l_cur_time + l_mins/1440;

  IF p_test_guid IS NULL THEN
    l_test_guid := k_no_test;
  ELSE
    l_test_guid := p_test_guid;
  END IF;

  -- find its current schedule, don't reschedule it to a later time
  SELECT next_run, in_error
    INTO l_cur_next_run, l_cur_in_error
    FROM MGMT_GENSVC_AVAIL_JOB
   WHERE target_guid = p_target_guid
     AND test_guid = l_test_guid;

  IF p_in_error IS NULL THEN
    l_new_in_error := l_cur_in_error;
  ELSE
    l_new_in_error := p_in_error;
  END IF;

  IF (l_cur_next_run < l_cur_time) OR (l_new_next_run < l_cur_next_run) THEN
    UPDATE MGMT_GENSVC_AVAIL_JOB
       SET next_run = l_new_next_run,
           in_error = l_new_in_error
     WHERE target_guid = p_target_guid
       AND test_guid = l_test_guid;
  ELSIF (l_new_in_error <> l_cur_in_error) THEN
    UPDATE MGMT_GENSVC_AVAIL_JOB
       SET in_error = l_new_in_error
     WHERE target_guid = p_target_guid
       AND test_guid = l_test_guid;
  END IF;

END RESCHEDULE_JOB;

--------------------------------------------------------------------------
PROCEDURE UNSCHEDULE_JOB ( p_target_guid IN RAW, 
                           p_test_guid IN RAW )
IS

  l_test_guid   RAW(16);

BEGIN

  IF p_test_guid IS NULL THEN
    l_test_guid := k_no_test;
  ELSE
    l_test_guid := p_test_guid;
  END IF;

  DELETE FROM MGMT_GENSVC_AVAIL_JOB
   WHERE target_guid = p_target_guid
     AND test_guid = l_test_guid;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;

END UNSCHEDULE_JOB;

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

FUNCTION GET_TEST_SEVERITY_METRIC (p_target_guid IN RAW)
RETURN RAW
IS
  l_metric_guid MGMT_METRICS.METRIC_GUID%TYPE;
BEGIN

  SELECT DISTINCT m.metric_guid
    INTO l_metric_guid
    FROM MGMT_TARGETS t, 
         MGMT_METRICS m
   WHERE t.target_guid = p_target_guid
     AND t.type_meta_ver = m.type_meta_ver
     AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ')
     AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ')
     AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ')
     AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ')
     AND (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
     AND t.target_type = m.target_type
     AND m.metric_name = MGMT_GLOBAL.G_AVAIL_TEST_METRIC_NAME
     AND m.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN;

  RETURN l_metric_guid;

  EXCEPTION WHEN OTHERS THEN
    -- log and swallow, we dont want the job to fail
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 
              'Could not get test_response/status metric guid for target: ' ||
               RAWTOHEX(p_target_guid) || '. Error: ' || SQLERRM,
               v_log_level_in => MGMT_GLOBAL.G_WARN);
    RETURN NULL;
END GET_TEST_SEVERITY_METRIC;

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

PROCEDURE CLEAR_STATUS_SEVERITY( p_target_guid IN RAW,
                                 p_test_guid   IN RAW,
                                 p_msg         IN VARCHAR2)
IS
 l_metric_guid MGMT_METRICS.METRIC_GUID%TYPE;
 v_key_values  mgmt_medium_string_table;
 l_test_name   MGMT_BCN_TXN_DEFN.NAME%TYPE;
BEGIN

  BEGIN
    SELECT name INTO l_test_name
      FROM MGMT_BCN_TXN_DEFN
     WHERE target_guid = p_target_guid
       AND txn_guid = p_test_guid;

   EXCEPTION WHEN OTHERS THEN
     -- log and swallow
      MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL,
                 'Could not get test name and type for Target: ' ||
                 RAWTOHEX(p_target_guid) || ' and Test: ' || p_test_guid ||'. Error: ' || SQLERRM);
     l_test_name := NULL;
   END;


  l_metric_guid := GET_TEST_SEVERITY_METRIC(p_target_guid); 

  IF(l_metric_guid IS NOT NULL
     AND p_target_guid IS NOT NULL
     AND l_test_name IS NOT NULL) THEN
    v_key_values := mgmt_medium_string_table();  
    v_key_values.extend;
    v_key_values(1) := l_test_name;
    EM_SEVERITY.clear_open_alerts(p_target_guid => p_target_guid,
                                  p_policy_guid => l_metric_guid,
                                  p_key_values => v_key_values,
                                  p_is_metric => TRUE,
                                  p_clear_message => p_msg
                                 );
    v_key_values.delete;
  END IF;
  EXCEPTION WHEN OTHERS THEN
    -- log and swallow, we could not close the severity
     MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 
              'Could not close the status severity for target: ' ||
               RAWTOHEX(p_target_guid) || ' and Test: '|| l_test_name ||'. Error: ' || SQLERRM,
               v_log_level_in => MGMT_GLOBAL.G_WARN);

END CLEAR_STATUS_SEVERITY;

---------------------------------------------------------------------------
PROCEDURE REMOVE_TEST ( p_target_guid IN RAW, 
                        p_test_guid IN RAW )
IS

  l_test_rowid ROWID;
  l_test_name  MGMT_GENSVC_AVAIL_TESTS.TEST_NAME%TYPE;
  l_target_name MGMT_TARGETS.TARGET_NAME%TYPE;
  l_target_type MGMT_TARGETS.TARGET_TYPE%TYPE;

BEGIN  



  BEGIN
    SELECT DISTINCT t.target_name, t.target_type, a.test_name 
      INTO l_target_name, l_target_type, l_test_name
      FROM MGMT_GENSVC_AVAIL_TESTS a,
           MGMT_TARGETS t
     WHERE t.target_guid = p_target_guid
       AND t.target_guid = a.target_guid
       AND a.test_guid = p_test_guid;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;


  BEGIN
    --  delete the test
    DELETE FROM MGMT_GENSVC_AVAIL_TESTS
     WHERE target_guid = p_target_guid
       AND test_guid = p_test_guid;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;

  -- remove it from the avail job queue
  UNSCHEDULE_JOB( p_target_guid, p_test_guid );
  
  -- delete all the avail records for the test.
  BEGIN
    -- TO DO: this may be a lot of records, do this asynchronously
    DELETE FROM MGMT_GENSVC_TEST_AVAIL
      WHERE target_guid = p_target_guid
        AND test_guid = p_test_guid;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;
  
  BEGIN
    DELETE FROM MGMT_GENSVC_TEST_CUR_AVAIL
      WHERE target_guid = p_target_guid
        AND test_guid = p_test_guid;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;

  BEGIN
    DELETE FROM MGMT_GENSVC_TEST_AVAIL_MARKER
      WHERE target_guid = p_target_guid
        AND test_guid = p_test_guid;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;

  
  -- delete dummy collection for this test
  -- it clears any severity for this collection as well
  -- it assumes that test_name is the key_value
  IF(l_target_name IS NOT NULL AND
     l_target_type IS NOT NULL AND
     l_test_name IS NOT NULL) THEN
    BEGIN
      -- clear any existing alerts first
      clear_status_severity(p_target_guid,
                            p_test_guid,
                            'Test ' || l_test_name || ' is deleted. Closing all availability related alerts.');

      em_rep_metric.delete_dummy_collection(l_target_name,
                                            l_target_type,
                                            MGMT_GLOBAL.G_AVAIL_TEST_METRIC_NAME,
                                            MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN,
                                            l_test_name);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
        WHEN OTHERS THEN
          MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 
              'Could not delete the test_status collection for target: ' ||
               l_target_name || ' and Test: '|| l_test_name ||'. Error: ' || SQLERRM,
               v_log_level_in => MGMT_GLOBAL.G_WARN);
     END;
  END IF;


END REMOVE_TEST;

--------------------------------------------------------------------------
PROCEDURE HANDLE_TEST_AVAIL_EVENT( p_target_guid IN RAW, 
                                   p_test_guid IN RAW, 
                                   p_new_status IN NUMBER,
                                   p_event_ts IN DATE )
IS

  l_cur_avmarker  MGMT_GENSVC_TEST_AVAIL_MARKER.marker_timestamp%TYPE;
  l_timestamp DATE;
  l_tmp_bool BOOLEAN;

BEGIN

  -- get the current avail marker
  SELECT marker_timestamp
    INTO l_cur_avmarker
    FROM MGMT_GENSVC_TEST_AVAIL_MARKER
   WHERE target_guid = p_target_guid
     AND test_guid = p_test_guid;

  l_timestamp := p_event_ts;
  IF l_cur_avmarker > l_timestamp THEN
    -- shift the event ts if it's older than the av marker.
    -- can't change anything once the av marker is moved. 
    l_timestamp := l_cur_avmarker;
  ELSIF l_cur_avmarker < p_event_ts THEN
    -- if the event ts > cur avmarker, make this period UNKNOWN
    UPDATE_TEST_AVAIL( p_target_guid, p_test_guid, MGMT_GLOBAL.G_STATUS_UNKNOWN,
                       l_cur_avmarker, l_timestamp, TRUE, l_tmp_bool );
  END IF;

  -- update the avail with the event
  UPDATE_TEST_AVAIL( p_target_guid, p_test_guid, p_new_status,
                     l_timestamp, l_timestamp, TRUE, l_tmp_bool );

END HANDLE_TEST_AVAIL_EVENT;

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

PROCEDURE LOG_STATUS_SEVERITY(p_target_guid IN RAW, 
                              p_test_guid IN RAW, 
                              p_new_status IN NUMBER,
                              p_severity_ts IN DATE)
IS
  
  l_status_metric_guid MGMT_METRICS.METRIC_GUID%TYPE;
  l_test_name          MGMT_BCN_TXN_DEFN.NAME%TYPE;
  l_test_type          MGMT_BCN_TXN_DEFN.TXN_TYPE%TYPE;
  l_violation_level    MGMT_VIOLATIONS.VIOLATION_LEVEL%TYPE := NULL;
  l_violation_msg      MGMT_VIOLATIONS.MESSAGE%TYPE;
  l_violation_msg_id   MGMT_VIOLATIONS.MESSAGE_NLSID%TYPE;

BEGIN


    BEGIN
      SELECT name, txn_type INTO l_test_name, l_test_type
        FROM MGMT_BCN_TXN_DEFN
       WHERE target_guid = p_target_guid
         AND txn_guid = p_test_guid;

     EXCEPTION WHEN OTHERS THEN
       -- log and swallow
        MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL,
                   'Could not get test name and type for Target: ' ||
                   RAWTOHEX(p_target_guid) || ' and Test: ' || p_test_guid ||'. Error: ' || SQLERRM);
       l_test_name := NULL;
     END;

    IF(l_test_name IS NOT NULL) THEN
      IF(p_new_status = MGMT_GLOBAL.G_STATUS_UP) THEN
        l_violation_level := MGMT_GLOBAL.G_SEVERITY_CLEAR;
        l_violation_msg   := 'Test ' || l_test_name || ' is now up.';
        l_violation_msg_id := 'TEST_IS_UP';
      ELSIF (p_new_status = MGMT_GLOBAL.G_STATUS_DOWN) THEN
        l_violation_level := MGMT_GLOBAL.G_SEVERITY_CRITICAL;
        l_violation_msg   := 'Test ' || l_test_name || ' is now down.';
        l_violation_msg_id := 'TEST_IS_DOWN';
      ELSE -- if status is changed to blackout or unknown, insert warning
        l_violation_level := MGMT_GLOBAL.G_SEVERITY_WARNING;
        IF (p_new_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) THEN
          l_violation_msg := 'Test ' || l_test_name || ' is now in unknown state.';
          l_violation_msg_id := 'TEST_IS_UNK';
        ELSIF (p_new_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
          l_violation_msg := 'Test ' || l_test_name || ' is now under blackout because of target blackout.';
          l_violation_msg_id := 'TEST_IS_BLACKEDOUT';
        END IF;
      END IF;
    END IF;

    -- get the metric guid fro the test_status metric
    l_status_metric_guid := GET_TEST_SEVERITY_METRIC(p_target_guid);

    IF (l_violation_level IS NOT NULL
        AND l_test_name IS NOT NULL
        AND l_status_metric_guid IS NOT NULL) THEN
    -- create a severity for the txn status metric
      BEGIN
        MGMT_VIOLATION.LOG_VIOLATION( p_target_guid          => p_target_guid,
                                      p_policy_guid          => l_status_metric_guid, 
                                      p_key_value            => l_test_name,
                                      p_collection_timestamp => p_severity_ts,
                                      p_violation_level      => l_violation_level,
                                      p_violation_type       => MGMT_GLOBAL.G_SEVERITY_TYPE_THRESHOLD,
                                      p_value                => p_new_status,
                                      p_message              => l_violation_msg,
                                      p_message_nlsid        => l_violation_msg_id,
                                      p_message_params       => l_test_name);
      EXCEPTION WHEN OTHERS THEN
        -- log and swallow
        MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL,
                   'Could not log severity for test status change. Target: ' ||
                   RAWTOHEX(p_target_guid) || ' and Test: ' || l_test_name ||'. Error: ' || SQLERRM,
                   v_log_level_in => MGMT_GLOBAL.G_WARN);
      END;
    END IF;



END LOG_STATUS_SEVERITY;

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

PROCEDURE UPDATE_TEST_AVAIL( p_target_guid IN RAW, 
                             p_test_guid IN RAW, 
                             p_new_status IN NUMBER,
                             p_new_start_ts IN DATE, 
                             p_new_avmarker IN DATE,
                             p_check_blackout IN BOOLEAN,
                             p_avail_updated OUT BOOLEAN )
IS

  l_cur_status MGMT_GENSVC_TEST_AVAIL.current_status%TYPE;
  l_cur_start_ts MGMT_GENSVC_TEST_AVAIL.start_collection_timestamp%TYPE;
  l_cur_avmarker MGMT_GENSVC_TEST_AVAIL_MARKER.marker_timestamp%TYPE;
  l_tgt_avail_enabled MGMT_GENSVC_AVAIL_CONFIG.avail_enabled%TYPE;
  l_tgt_avmarker MGMT_AVAILABILITY_MARKER.marker_timestamp%TYPE;
  l_testav_rowid ROWID;
  l_marker_rowid ROWID;
  l_timestamp DATE;

  l_status_metric_guid MGMT_METRICS.METRIC_GUID%TYPE;
  l_test_name          MGMT_BCN_TXN_DEFN.NAME%TYPE;
  l_test_type          MGMT_BCN_TXN_DEFN.TXN_TYPE%TYPE;
  l_violation_level    MGMT_VIOLATIONS.VIOLATION_LEVEL%TYPE := NULL;
  l_violation_msg      MGMT_VIOLATIONS.MESSAGE%TYPE;

  l_in_blackout BOOLEAN;
  l_new_end_ts DATE;

BEGIN

  -- init out params
  p_avail_updated := FALSE;

  -- validate params
  IF (p_new_start_ts IS NULL) THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'Invalid parameter: null violation timestamp');
  END IF;
  IF (p_new_avmarker IS NULL) THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'Invalid parameter: null avail marker');
  END IF;
  IF (p_new_start_ts > p_new_avmarker) THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'Invalid parameter: invalid timestamps');
  END IF;

  -- get the test avail marker    
  SELECT ROWID, marker_timestamp
    INTO l_marker_rowid, l_cur_avmarker
    FROM MGMT_GENSVC_TEST_AVAIL_MARKER
   WHERE target_guid = p_target_guid
     AND test_guid = p_test_guid;

  -- an older av marker cannot be processed
  IF p_new_avmarker < l_cur_avmarker THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'Invalid parameter: new avail marker is older than the current avail marker');
  END IF;

  -- make sure that the new start ts >= the current avail marker
  l_timestamp := p_new_start_ts;
  IF l_timestamp < l_cur_avmarker THEN
    l_timestamp := l_cur_avmarker;
  END IF;

  -- get the tests current avail status
  SELECT av.row_id, av.current_status, av.start_collection_timestamp
    INTO l_testav_rowid, l_cur_status, l_cur_start_ts
    FROM ( SELECT ROWID as row_id, current_status, start_collection_timestamp
             FROM MGMT_GENSVC_TEST_AVAIL
            WHERE target_guid = p_target_guid
              AND test_guid = p_test_guid
              AND end_collection_timestamp IS NULL
            ORDER BY start_collection_timestamp DESC ) av
   WHERE ROWNUM = 1;

  l_new_end_ts := p_new_avmarker;

  -- make sure that the target is not in blackout
  IF (p_new_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
    IF (l_cur_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
      -- already in blackout, nothing to do
      RETURN;
    END IF;
  ELSIF p_check_blackout THEN
    l_in_blackout := IS_IN_BLACKOUT(p_target_guid, l_timestamp, p_new_avmarker, l_new_end_ts);
    IF (l_in_blackout) OR (l_new_end_ts IS NULL) OR 
       (l_new_end_ts < l_timestamp) OR (l_new_end_ts < l_cur_avmarker) THEN
      RETURN;
    END IF;
  END IF;

  -- change in avail status 
  IF p_new_status <> l_cur_status THEN
    -- close the current avail record 
    UPDATE MGMT_GENSVC_TEST_AVAIL
       SET end_collection_timestamp = l_timestamp
     WHERE ROWID = l_testav_rowid;
    -- and open a new record
    INSERT INTO MGMT_GENSVC_TEST_AVAIL
      ( target_guid, test_guid, current_status, 
        start_collection_timestamp, end_collection_timestamp )
    VALUES
      ( p_target_guid, p_test_guid, p_new_status, 
        l_timestamp, NULL );
    -- update the cur avail table
    BEGIN
      INSERT INTO MGMT_GENSVC_TEST_CUR_AVAIL
        ( target_guid, test_guid, current_status, start_collection_timestamp )
      VALUES
        ( p_target_guid, p_test_guid, p_new_status, l_timestamp );
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        UPDATE MGMT_GENSVC_TEST_CUR_AVAIL
           SET current_status = p_new_status,
               start_collection_timestamp = l_timestamp
         WHERE target_guid = p_target_guid
           AND test_guid = p_test_guid;
    END;

    p_avail_updated := TRUE;

    IF(p_new_status = MGMT_GLOBAL.G_STATUS_UP OR p_new_status = MGMT_GLOBAL.G_STATUS_DOWN) THEN
      LOG_STATUS_SEVERITY(p_target_guid, p_test_guid, p_new_status, l_timestamp);
    END IF;

  END IF;

  -- update the marker
  UPDATE MGMT_GENSVC_TEST_AVAIL_MARKER
     SET marker_timestamp = l_new_end_ts,
         marker_avail_status = p_new_status
   WHERE ROWID = l_marker_rowid;
  -- the test's av marker was updated, see if the target's avail should
  -- be recomputed earlier 
  BEGIN
    -- make sure the target has test-avail enabled
    SELECT avail_enabled
      INTO l_tgt_avail_enabled
      FROM MGMT_GENSVC_AVAIL_CONFIG
     WHERE target_guid = p_target_guid;
    IF l_tgt_avail_enabled <> 0 THEN
       -- get the target's avail marker
       SELECT marker_timestamp
         INTO l_tgt_avmarker
         FROM MGMT_AVAILABILITY_MARKER
        WHERE target_guid = p_target_guid;
       -- recompute if the test's av marker is greater than the target's
       IF l_tgt_avmarker < l_new_end_ts THEN
         RESCHEDULE_JOB(p_target_guid, NULL, min_job_sleep_key, NULL);  
       END IF;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      -- this is an optimization, don't raise any exceptions on this block
      NULL;
  END;

END UPDATE_TEST_AVAIL;

/*
--------------------------------------------------------------------------
PROCEDURE CHECK_TARGET_SCHEDULE( p_target_guid IN RAW )
IS

  l_eval_logic MGMT_GENSVC_AVAIL_CONFIG.eval_logic%TYPE;
  l_target_avail_timestamp MGMT_AVAILABILITY_MARKER.marker_timestamp%TYPE;
  l_count NUMBER;

BEGIN

  IF p_target_guid IS NULL THEN
    RETURN;
  END IF;
  
  -- get the evail logic and verify that the target has test-avail enabled
  SELECT eval_logic
    INTO l_eval_logic
    FROM MGMT_GENSVC_AVAIL_CONFIG
   WHERE target_guid = p_target_guid
     AND avail_enabled <> 0;

  -- get the target's avail marker
  SELECT marker_timestamp
    INTO l_target_avail_timestamp
    FROM MGMT_AVAILABILITY_MARKER
   WHERE target_guid = p_target_guid;

  -- if all key tests have an avail marker greater than the target's, 
  -- recompute the target's
  SELECT COUNT(*)
    INTO l_count
    FROM MGMT_GENSVC_AVAIL_TESTS t,
         MGMT_GENSVC_TEST_AVAIL_MARKER m
   WHERE t.target_guid = p_target_guid
     AND t.monit_status <> 0
     AND t.avail_test <> 0
     AND t.target_guid = m.target_guid
     AND t.test_guid = m.test_guid
     AND m.marker_timestamp <= l_target_avail_timestamp;
  IF l_count = 0 THEN
    RESCHEDULE_JOB(p_target_guid, NULL, min_job_sleep_key, NULL);  
    RETURN;
  END IF;

  IF l_eval_logic = k_and_eval THEN
    SELECT COUNT(*)
      INTO l_count
      FROM MGMT_GENSVC_AVAIL_TESTS t,
           MGMT_GENSVC_TEST_CUR_AVAIL a,
           MGMT_GENSVC_TEST_AVAIL_MARKER m
     WHERE t.target_guid = p_target_guid
       AND t.monit_status <> 0
       AND t.avail_test <> 0
       AND t.target_guid = m.target_guid
       AND t.test_guid = m.test_guid
       AND m.marker_timestamp > l_target_avail_timestamp
       AND a.target_guid = t.target_guid
       AND a.test_guid = t.test_guid
       AND a.current_status =  MGMT_GLOBAL.G_STATUS_DOWN;
    IF l_count > 0 THEN
      -- at least one test is down
      RESCHEDULE_JOB(p_target_guid, NULL, min_job_sleep_key, NULL);
    END IF;
  ELSIF l_eval_logic = k_or_eval THEN
    SELECT COUNT(*)
      INTO l_count
      FROM MGMT_GENSVC_AVAIL_TESTS t,
           MGMT_GENSVC_TEST_CUR_AVAIL a,
           MGMT_GENSVC_TEST_AVAIL_MARKER m
     WHERE t.target_guid = p_target_guid
       AND t.monit_status <> 0
       AND t.avail_test <> 0
       AND t.target_guid = m.target_guid
       AND t.test_guid = m.test_guid
       AND m.marker_timestamp > l_target_avail_timestamp
       AND a.target_guid = t.target_guid
       AND a.test_guid = t.test_guid
       AND a.current_status =  MGMT_GLOBAL.G_STATUS_UP;
    IF l_count > 0 THEN
      -- at least one test is up
      RESCHEDULE_JOB(p_target_guid, NULL, min_job_sleep_key, NULL);
    END IF;
  END IF;

EXCEPTION 
  WHEN OTHERS THEN
    NULL;

END CHECK_TARGET_SCHEDULE;
*/

--------------------------------------------------------------------------
PROCEDURE GET_ERROR_METRIC ( p_metric_guid IN RAW, 
                             p_err_metric_guid OUT RAW )
IS

  l_metric_name      mgmt_metrics.metric_name%TYPE;
  l_metric_column    mgmt_metrics.metric_column%TYPE;
  l_metric_type      mgmt_metrics.metric_type%TYPE;
  l_target_type      mgmt_metrics.target_type%TYPE;

BEGIN

  p_err_metric_guid := p_metric_guid;
  
  IF p_metric_guid IS NULL THEN
    RETURN;
  END IF;

  SELECT metric_name, metric_column, metric_type, target_type
    INTO l_metric_name, l_metric_column, l_metric_type, l_target_type
    FROM MGMT_METRICS
   WHERE metric_guid = p_metric_guid
     AND ROWNUM = 1;

  IF (l_metric_column IS NOT NULL) AND (l_metric_column <> ' ') THEN
    -- Column of a table metric, find the guid of the table metric
    BEGIN
      SELECT metric_guid
        INTO p_err_metric_guid
        FROM MGMT_METRICS
       WHERE metric_name = l_metric_name
         AND target_type = l_target_type
         AND metric_type = MGMT_GLOBAL.G_METRIC_TYPE_TABLE
         AND metric_column = ' '
         AND ROWNUM = 1;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        p_err_metric_guid := p_metric_guid;
    END;
  END IF;
   
END GET_ERROR_METRIC;

--------------------------------------------------------------------------
FUNCTION HAS_AVAIL_DEFINTION ( p_target_guid IN RAW )
RETURN BOOLEAN IS
  l_count NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO l_count
    FROM MGMT_GENSVC_AVAIL_CONFIG
   WHERE target_guid = p_target_guid;
  IF l_count > 0 THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END HAS_AVAIL_DEFINTION;

--------------------------------------------------------------------------
-- Return true if the target is blacked out for the entire period
-- [start_ts, end_ts].
-- Returns false if a leading segment (possibly the entire period)
-- is not in blackout.  In this case, new_end_ts is set to the end
-- of the non-blacked out period (new_end_ts == end_ts when the
-- entire period does not fall within a blackout period).
FUNCTION IS_IN_BLACKOUT( p_target_guid IN RAW,
                         p_start_ts IN DATE,
                         p_end_ts IN DATE,
                         p_new_end_ts OUT DATE ) RETURN BOOLEAN 
IS

l_windows MGMT_BLACKOUT_ENGINE.BLACKOUT_CURSOR;
l_query_time DATE;

l_blackout_guid MGMT_BLACKOUTS.blackout_guid%TYPE;
l_start_time MGMT_BLACKOUT_WINDOWS.start_time%TYPE;
l_end_time MGMT_BLACKOUT_WINDOWS.end_time%TYPE;
l_status MGMT_BLACKOUTS.blackout_status%TYPE;
l_new_end_ts DATE;

BEGIN

    l_new_end_ts := p_end_ts;

    -- Loop over all blackout windows for the target
    l_windows := MGMT_BLACKOUT_ENGINE.get_blackout_windows( p_target_guid, 
                                                            l_query_time);

    IF NOT l_windows%ISOPEN THEN
      -- no blackouts
      p_new_end_ts := p_end_ts;
      RETURN FALSE;
    END IF;

    LOOP      
        FETCH l_windows INTO l_blackout_guid, l_start_time,
              l_end_time, l_status;

        EXIT WHEN l_windows%NOTFOUND;

        IF (l_status <> MGMT_BLACKOUT_ENGINE.BLK_STATE_STOPPED) AND
           (l_status <> MGMT_BLACKOUT_ENGINE.BLK_STATE_ENDED) THEN
          IF (p_start_ts >= l_start_time) THEN
            CLOSE l_windows;
            RETURN TRUE;
          ELSIF (l_new_end_ts > l_start_time) THEN
            l_new_end_ts := l_start_time;
          END IF;
        END IF;
    END LOOP;

    IF l_windows%ISOPEN THEN
        CLOSE l_windows;
    END IF;

    p_new_end_ts := l_new_end_ts;
    RETURN FALSE;

EXCEPTION
    WHEN OTHERS THEN
        IF l_windows%ISOPEN THEN
            CLOSE l_windows;
        END IF;
        RAISE;
END;

--------------------------------------------------------------------------
FUNCTION IS_IN_BLACKOUT( p_target_guid IN RAW,
                         p_timestamp IN DATE ) RETURN BOOLEAN 
IS

l_windows MGMT_BLACKOUT_ENGINE.BLACKOUT_CURSOR;
l_query_time DATE;

l_blackout_guid MGMT_BLACKOUTS.blackout_guid%TYPE;
l_start_time MGMT_BLACKOUT_WINDOWS.start_time%TYPE;
l_end_time MGMT_BLACKOUT_WINDOWS.end_time%TYPE;
l_status MGMT_BLACKOUTS.blackout_status%TYPE;

BEGIN

    -- Loop over all blackout windows for the target
    l_windows := MGMT_BLACKOUT_ENGINE.get_blackout_windows( p_target_guid, 
                                                            l_query_time);

    IF NOT l_windows%ISOPEN THEN
      -- no blackouts
      RETURN FALSE;
    END IF;

    LOOP      
        FETCH l_windows INTO l_blackout_guid, l_start_time,
              l_end_time, l_status;

        EXIT WHEN l_windows%NOTFOUND;

        IF (l_status <> MGMT_BLACKOUT_ENGINE.BLK_STATE_STOPPED) AND
           (l_status <> MGMT_BLACKOUT_ENGINE.BLK_STATE_ENDED) AND
           (p_timestamp >= l_start_time) AND 
           ((l_end_time IS NULL) OR (p_timestamp <= l_end_time)) THEN
          CLOSE l_windows;
          RETURN TRUE;
        END IF;
    END LOOP;

    IF l_windows%ISOPEN THEN
        CLOSE l_windows;
    END IF;

    RETURN FALSE;

EXCEPTION
    WHEN OTHERS THEN
        IF l_windows%ISOPEN THEN
            CLOSE l_windows;
        END IF;
        RAISE;
END;

--------------------------------------------------------------------------
FUNCTION LOCK_TEST( p_target_guid IN RAW, p_test_guid IN RAW,
                    p_next_run OUT DATE, p_in_error OUT NUMBER) 
RETURN NUMBER
IS

BEGIN

  SELECT next_run, in_error
    INTO p_next_run, p_in_error
    FROM MGMT_GENSVC_AVAIL_JOB
   WHERE target_guid = p_target_guid
     AND test_guid = p_test_guid
     FOR UPDATE NOWAIT;
 
  RETURN 0;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 1;
  WHEN k_row_locked THEN
    RETURN 2;
  WHEN OTHERS THEN
    EMDW_LOG.set_context( v_context_type=>EMDW_LOG_CTX );
    IF p_test_guid = k_no_test THEN
      EMDW_LOG.error(SUBSTR(('Error Locking the Target: ' || RAWTOHEX(p_target_guid) || 
                             '. Error: ' || SQLERRM), 
                            1, k_trc_msg_len), MODULE_NAME);
    ELSE
      EMDW_LOG.error(SUBSTR(('Error Locking the Target: ' || RAWTOHEX(p_target_guid) || 
                             ', Test: ' || RAWTOHEX(p_test_guid) ||
                             '. Error: ' || SQLERRM), 
                            1, k_trc_msg_len), MODULE_NAME);
    END IF;
    EMDW_LOG.set_context;     
    RETURN 3;
END;

--------------------------------------------------------------------------
FUNCTION LOCK_TARGET( p_target_guid IN RAW,
                      p_next_run OUT DATE, p_in_error OUT NUMBER) 
RETURN NUMBER
IS
BEGIN
  RETURN LOCK_TEST(p_target_guid, k_no_test, p_next_run, p_in_error);
END;

--------------------------------------------------------------------------
PROCEDURE TRACE_MSG(p_msg IN OUT VARCHAR2, p_add IN VARCHAR2)
IS

  l_msg_len NUMBER;
  l_add_len NUMBER;

BEGIN

  IF (p_msg IS NULL) OR (p_add IS NULL) 
     OR (LENGTH(p_add) IS NULL) 
     OR (LENGTH(p_msg) >= k_trc_msg_len) THEN
    RETURN;
  END IF;

  l_msg_len := NVL(LENGTH(p_msg), 0);
  l_add_len := NVL(LENGTH(p_add), 0);
  IF (l_msg_len + l_add_len > k_trc_msg_len) THEN
    p_msg := p_msg || SUBSTR(p_add, 1, k_trc_msg_len - l_msg_len);
  ELSE
    p_msg := p_msg || p_add;
  END IF;

END TRACE_MSG;

END MGMT_GENSVC_AVAIL;
/

SHOW ERRORS;