Rem
Rem $Header: beacon_txn_view_pkgbody.sql 27-apr-2007.16:21:12 andyao Exp $
Rem
Rem beacon_txn_view_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      beacon_txn_view_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    andyao      04/27/07 - add query to get beacon platform information
Rem    mfidanbo    11/23/05 - Backport mfidanbo_bug-4731612 from main 
Rem    mfidanbo    11/16/05 - sort by target_name, type 
Rem    mfidanbo    09/07/05 - step alerts should show up 
Rem    mfidanbo    08/30/05 - fix txn status 
Rem    mfidanbo    08/29/05 - get the current status of bcn/txn from 
Rem                           severities 
Rem    mfidanbo    08/10/05 - fix duplicate alerts bug 
Rem    mfidanbo    07/13/05 - support timezone conversion 
Rem    andyao      06/29/05 - add procedure to get beacon status 
Rem    mfidanbo    06/23/05 - alerts sql for test 
Rem    mfidanbo    06/08/05 - parameterize tz 
Rem    mfidanbo    05/22/05 - violation_duration can be null 
Rem    mfidanbo    04/10/05 - remove yellow bar 
Rem    mfidanbo    02/23/05 - 
Rem    mfidanbo    02/15/05 - superimpose only blackout for bcn,test avail 
Rem                           periods 
Rem    mfidanbo    01/18/05 - add local beacon 
Rem    mfidanbo    01/07/05 - 
Rem    mfidanbo    12/23/04 - fix fetch error 
Rem    mfidanbo    12/03/04 - fix composite key query 
Rem    mfidanbo    11/19/04 - dependency issue 
Rem    mfidanbo    11/18/04 - add composite key to cursor bcn_txn 
Rem    mfidanbo    11/14/04 - txn avail changes 
Rem    mfidanbo    11/10/04 - mfidanbo_mta_changes
Rem    mfidanbo    11/09/04 - Created
Rem


CREATE OR REPLACE PACKAGE BODY MGMT_BCN_TXN_VIEW IS


FUNCTION get_test_start_avail_marker ( target_guid_in IN RAW,
                                       test_guid_in IN RAW
                                     )
RETURN DATE
IS
 l_start_date DATE;

  CURSOR timestampCursor IS
    SELECT end_collection_timestamp 
      FROM mgmt_gensvc_test_avail
     WHERE target_guid = target_guid_in
       AND test_guid = test_guid_in
     ORDER BY start_collection_timestamp asc; 
BEGIN
  FOR record IN timestampCursor
  LOOP
    l_start_date := record.end_collection_timestamp;
    EXIT;
  END LOOP;

  IF( l_start_date is null) THEN
    l_start_date := mgmt_avail.get_target_start_marker_time(target_guid_in);
  END IF;

  return l_start_date;
END get_test_start_avail_marker;

FUNCTION get_test_end_avail_marker ( target_guid_in IN RAW,
                                       test_guid_in IN RAW)
RETURN DATE
IS
 l_end_date DATE := mgmt_avail.get_target_end_marker_time(target_guid_in);
BEGIN
  BEGIN
    SELECT marker_timestamp INTO l_end_date
      FROM mgmt_gensvc_test_avail_marker
     WHERE target_guid = target_guid_in
       AND test_guid = test_guid_in;

   EXCEPTION WHEN NO_DATA_FOUND THEN
     return l_end_date;
  END;
  return l_end_date;
END get_test_end_avail_marker;

PROCEDURE compute_start_end_dates(
              target_guid_in IN RAW,
              test_guid_in IN RAW,
              start_date_in IN DATE,
              end_date_in   IN DATE,
              num_of_days_in IN NUMBER,
              start_date_out OUT DATE,
              end_date_out   OUT DATE)
IS

l_target_sysdate DATE;
l_recomputed_start_date DATE;
l_recomputed_end_date DATE;
l_start_date DATE;
l_end_date DATE;
l_start_marker DATE;
l_end_marker DATE;

BEGIN

  l_target_sysdate := MGMT_TARGET.SYSDATE_TARGET(target_guid_in);

  IF( start_date_in IS NOT NULL AND end_date_in IS NOT NULL )
  THEN
    l_start_date := start_date_in;
    l_end_date := end_date_in;
  ELSE
    l_start_date := l_target_sysdate - num_of_days_in;
    l_end_date := l_target_sysdate;
  END IF;
  l_recomputed_start_date := GREATEST( l_start_date,  mgmt_avail.get_target_start_marker_time( target_guid_in ) );
  l_recomputed_end_date   := LEAST( l_end_date,  mgmt_avail.get_target_end_marker_time( target_guid_in ) );
  -- get the test avail marker times
  l_start_date := get_test_start_avail_marker(target_guid_in, test_guid_in);
  l_end_date := get_test_end_avail_marker(target_guid_in, test_guid_in);


  l_recomputed_start_date := GREATEST(l_recomputed_start_date, l_start_date);
  l_recomputed_end_date := LEAST(l_recomputed_end_date, l_end_date);

  IF( l_recomputed_start_date > l_recomputed_end_date )
  THEN
    l_recomputed_start_date := l_recomputed_end_date;
  END IF;


  start_date_out := l_recomputed_start_date;
  end_date_out := l_recomputed_end_date;

END compute_start_end_dates;

FUNCTION get_test_start_date( target_guid_in IN RAW,
                              test_guid_in IN RAW,
                              start_date_in IN DATE,
                              end_date_in IN DATE
                            )
RETURN DATE
IS
 l_start_date DATE;
 l_end_date DATE;
BEGIN
  compute_start_end_dates(target_guid_in, test_guid_in, start_date_in, end_date_in, 0, l_start_date, l_end_date);
  return l_start_date;
END get_test_start_date;

FUNCTION get_test_end_date( target_guid_in IN RAW,
                              test_guid_in IN RAW,
                              start_date_in IN DATE,
                              end_date_in IN DATE
                            )
RETURN DATE
IS
 l_start_date DATE;
 l_end_date DATE;
BEGIN
  compute_start_end_dates(target_guid_in, test_guid_in, start_date_in, end_date_in, 0, l_start_date, l_end_date);
  return l_end_date;
END get_test_end_date;




FUNCTION get_test_avail_pct(
             target_guid_in IN RAW,
             test_guid_in   IN RAW,
             start_date_in  IN DATE,
             end_date_in    IN DATE,
             num_of_days_in IN NUMBER) RETURN NUMBER
IS

l_target_sysdate DATE;
l_start_date DATE;
l_end_date DATE;
l_uptime NUMBER;
l_downtime NUMBER;
avail_pct_value NUMBER;
l_numerator NUMBER;
l_denominator NUMBER;

BEGIN

  compute_start_end_dates(target_guid_in, test_guid_in, start_date_in, end_date_in, num_of_days_in, l_start_date, l_end_date);

SELECT
  SUM(DECODE(a.current_status,
             MGMT_GLOBAL.G_STATUS_UP, NVL(a.end_collection_timestamp, l_end_date)-
             GREATEST(a.start_collection_timestamp, l_start_date),
             0)),
  SUM(DECODE(a.current_status,
             MGMT_GLOBAL.G_STATUS_DOWN, NVL(a.end_collection_timestamp, l_end_date)-
             GREATEST(a.start_collection_timestamp, l_start_date),
             0))
 INTO l_uptime, l_downtime
 FROM MGMT_GENSVC_TEST_AVAIL a
WHERE a.target_guid = target_guid_in
  AND a.test_guid = test_guid_in
  AND NVL(a.end_collection_timestamp, l_end_date) >= l_start_date
  AND a.start_collection_timestamp <= l_end_date
GROUP BY a.target_guid, a.test_guid;
  
  l_numerator := l_uptime;

  -- denominator is (uptime + downtime)
  l_denominator := l_uptime + l_downtime;

  IF( l_denominator = 0 )
  THEN
    avail_pct_value := -1;
  ELSE
    avail_pct_value := (l_numerator/l_denominator) * 100;
  END IF;

  RETURN avail_pct_value;

END get_test_avail_pct;


FUNCTION get_test_guid(
             tgt_guid IN RAW,
             test_name   IN VARCHAR2,
             test_type   IN VARCHAR2) RETURN RAW

IS

  l_test_guid RAW(16);

BEGIN

  SELECT d.txn_guid INTO l_test_guid
    FROM MGMT_BCN_TXN_DEFN d
   WHERE d.target_guid = tgt_guid
     AND d.name = test_name
     AND d.txn_type = test_type
     AND rownum = 1;

  RETURN l_test_guid;

END get_test_guid;


FUNCTION get_cur_test_status(
             tgt_guid IN RAW,
             tst_guid IN RAW) RETURN NUMBER
IS
  
  l_status NUMBER := MGMT_GLOBAL.G_STATUS_UNKNOWN;

BEGIN

  SELECT avail.current_status INTO l_status
    FROM MGMT_GENSVC_TEST_CUR_AVAIL avail
   WHERE avail.target_guid = tgt_guid
     AND avail.test_guid = tst_guid;

  RETURN l_status;

  EXCEPTION 
    WHEN NO_DATA_FOUND THEN
      RETURN MGMT_GLOBAL.G_STATUS_UNKNOWN;

END get_cur_test_status;


FUNCTION get_cur_test_status(
             target_name_in IN VARCHAR2,
             target_type_in IN VARCHAR2,
             test_name_in   IN VARCHAR2,
             test_type_in   IN VARCHAR2
                            ) RETURN NUMBER
IS

 l_target_guid RAW(16);
 l_test_guid RAW(16);

BEGIN
 l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);
 l_test_guid := get_test_guid(l_target_guid, test_name_in, test_type_in);
 return get_cur_test_status(l_target_guid, l_test_guid);
END get_cur_test_status;

PROCEDURE GET_SING_TEST_STATUS (
          target_name_in IN  VARCHAR2,
          target_type_in IN  VARCHAR2,
          test_guid_in   IN  RAW,
          start_date_in  IN  DATE,
          end_date_in    IN  DATE,
          num_days_in    IN  NUMBER,
          avail_pct_out  OUT NUMBER,
          current_status OUT NUMBER,
          since          OUT DATE)
IS
  l_target_guid RAW(16);
BEGIN

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

  SELECT avail.current_status,
         avail.start_collection_timestamp INTO current_status, since
    FROM MGMT_GENSVC_TEST_CUR_AVAIL avail
   WHERE avail.target_guid = l_target_guid
     AND avail.test_guid = test_guid_in;

  avail_pct_out := get_test_avail_pct(l_target_guid, test_guid_in, start_date_in, end_date_in, num_days_in);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    avail_pct_out := -1;
    current_status :=  MGMT_GLOBAL.G_STATUS_UNKNOWN;
      

END GET_SING_TEST_STATUS;

PROCEDURE GET_CUR_TESTS_STATUS ( 
          target_name_in IN  VARCHAR2,
          target_type_in IN  VARCHAR2,
          tests_in       IN  SMP_EMD_NVPAIR_ARRAY,
          status_cursor OUT CURSORTYPE)
IS

  l_target_guid RAW(16);

BEGIN

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

  OPEN status_cursor FOR
    SELECT tests.test_name AS test_name,
           tests.test_type AS test_type,
           avail.test_guid AS test_guid,
           avail.severity_guid AS sev_guid,
           avail.current_status AS status,
           avail.start_collection_timestamp AS since
      FROM MGMT_GENSVC_TEST_CUR_AVAIL avail,
           (SELECT d.txn_guid as test_guid, 
                   tests_inner.name as test_name, 
                   tests_inner.value as test_type
              FROM MGMT_BCN_TXN_DEFN d,
                   (SELECT * FROM TABLE(CAST(tests_in AS SMP_EMD_NVPAIR_ARRAY))) tests_inner
             WHERE d.target_guid = l_target_guid
               AND d.name = tests_inner.name
               AND d.txn_type = tests_inner.value) tests
     WHERE avail.target_guid = l_target_guid
       AND avail.test_guid = tests.test_guid
     ORDER BY tests.test_name, tests.test_type;

END GET_CUR_TESTS_STATUS;

PROCEDURE GET_TESTS_STATUS_AVAIL (
          target_name_in IN  VARCHAR2,
          target_type_in IN  VARCHAR2,
          tests_in       IN  SMP_EMD_NVPAIR_ARRAY,
          start_date_in  IN  DATE, -- should be in tgt tz
          end_date_in    IN  DATE, -- should be in tgt tz
          num_of_days_in IN  NUMBER,
          tz_used        IN  VARCHAR2, 
          status_cursor  OUT CURSORTYPE)
IS
  l_target_guid mgmt_targets.target_guid%TYPE;
  l_target_sysdate DATE;
  l_start_date DATE;
  l_end_date DATE;
  l_num_of_days NUMBER;
  l_tgt_tz mgmt_targets.timezone_region%TYPE;
  l_to_tz mgmt_targets.timezone_region%TYPE;

  l_test_info COMPONENT_ARRAY;
  l_single_test_avail COMP_AVAIL_ARRAY;
  l_all_test_avail COMP_AVAIL_ARRAY := COMP_AVAIL_ARRAY();
BEGIN
  l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in );
 
  SELECT timezone_region INTO l_tgt_tz
  FROM MGMT_TARGETS
  WHERE target_guid = l_target_guid;

  IF ( tz_used IS NULL) THEN
    l_to_tz := l_tgt_tz;
  ELSE
    l_to_tz := tz_used;
  END IF;

  SELECT COMPONENT(d.txn_guid,
                    tests_inner.name,
                    tests_inner.value,
                    get_cur_test_status(l_target_guid, d.txn_guid), null, null,
                    get_test_start_date(l_target_guid, d.txn_guid, start_date_in, end_date_in),
                    get_test_end_date(l_target_guid, d.txn_guid, start_date_in, end_date_in), 0, 0)
    BULK COLLECT INTO l_test_info
    FROM MGMT_BCN_TXN_DEFN d,
         (SELECT * FROM TABLE(CAST(tests_in AS SMP_EMD_NVPAIR_ARRAY))) tests_inner
   WHERE d.target_guid = l_target_guid
     AND d.name = tests_inner.name
     AND d.txn_type = tests_inner.value;

  IF(l_test_info IS NOT NULL AND l_test_info.COUNT > 0) THEN
    FOR i IN l_test_info.FIRST..l_test_info.LAST LOOP
      
     SELECT COMP_AVAIL(
            avail.test_guid,
            l_test_info(i).target_name,
            l_test_info(i).target_type,
            l_test_info(i).current_status,
            avail.severity_guid,
            avail.current_status,
            (LEAST(l_test_info(i).end_time, 
                   NVL(avail.end_collection_timestamp, l_test_info(i).end_time)
            ) -  
              GREATEST(avail.start_collection_timestamp, l_test_info(i).start_time)
            )*24*60*60,
            MGMT_GLOBAL.ADJUST_TZ(GREATEST(avail.start_collection_timestamp, l_test_info(i).start_time), l_tgt_tz, l_to_tz),
            MGMT_GLOBAL.ADJUST_TZ(LEAST(l_test_info(i).end_time, NVL(avail.end_collection_timestamp, l_test_info(i).end_time)), l_tgt_tz, l_to_tz), 0, 0
                      )
      BULK COLLECT INTO l_single_test_avail
      FROM MGMT_GENSVC_TEST_AVAIL avail
     WHERE avail.target_guid = l_target_guid
       AND avail.test_guid = l_test_info(i).target_guid
       AND NVL(avail.end_collection_timestamp, l_test_info(i).end_time) > l_test_info(i).start_time
       AND avail.start_collection_timestamp <= l_test_info(i).end_time 
       AND NVL(avail.end_collection_timestamp, l_test_info(i).end_time) > avail.start_collection_timestamp
       AND l_test_info(i).start_time < l_test_info(i).end_time;


    -- l_single_test_avail is already initialized so just check if count is 0.    
     IF(l_single_test_avail.COUNT = 0) THEN
       l_single_test_avail := COMP_AVAIL_ARRAY();
       l_single_test_avail.extend(1);
       l_single_test_avail(1) := gensvc_view.get_empty_record(l_test_info(i), start_date_in, end_date_in);
     END IF;
         
     FOR j IN l_single_test_avail.FIRST..l_single_test_avail.LAST LOOP
       l_all_test_avail.extend(1);
       l_all_test_avail(l_all_test_avail.count) := l_single_test_avail(j);
     END LOOP;

     IF(l_single_test_avail IS NOT NULL) THEN
       l_single_test_avail.DELETE; 
       l_single_test_avail := NULL;
     END IF;

    END LOOP;
  END IF;

  OPEN status_cursor FOR
    SELECT avail.target_name as test_name,
           avail.target_type as test_type,
           avail.target_guid as test_guid,
           avail.severity_guid as severity_guid,
           avail.current_status as current_status,
           avail.historical_status as historical_status,
           avail.start_timestamp as start_timestamp,
           avail.end_timestamp as end_timestamp,
           avail.duration as duration
      FROM (SELECT * FROM TABLE(CAST (l_all_test_avail AS COMP_AVAIL_ARRAY))) avail
    ORDER BY avail.target_type, avail.target_name, avail.start_timestamp;

END GET_TESTS_STATUS_AVAIL;

FUNCTION GET_SING_BCN_TXN_STATUS(target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            target_guid_in IN RAW,
                            txn_guid_in    IN RAW,
                            key_value_in   IN VARCHAR2)
RETURN NUMBER
IS

 l_status NUMBER;
 l_metric_name MGMT_METRICS.METRIC_NAME%TYPE;
 l_metric_column MGMT_METRICS.METRIC_COLUMN%TYPE;
 l_metric_guid MGMT_METRICS.METRIC_GUID%TYPE;
 l_last_sev_ts DATE;

BEGIN

  -- Get the availability metric for the given test type
  SELECT DISTINCT test.avail_metric, test.avail_metric_column 
    INTO l_metric_name, l_metric_column
    FROM MGMT_TEST test,
         MGMT_BCN_TXN_DEFN d
   WHERE test.test_type = d.txn_type
     AND d.txn_guid = txn_guid_in
     AND d.target_guid = target_guid_in;

  l_metric_guid := mgmt_metric.get_metric_guid_for_target(
                                      target_type_in,
                                      target_name_in,
                                      l_metric_name,
                                      l_metric_column);

  -- Select the most recent severity where the timestamp is before the
  -- test marker.

  SELECT MAX(v.collection_timestamp) INTO l_last_sev_ts
    FROM MGMT_VIOLATIONS v,
         MGMT_GENSVC_TEST_AVAIL_MARKER t
   WHERE t.target_guid = target_guid_in
     AND t.test_guid   = txn_guid_in
     AND v.target_guid = target_guid_in
     AND v.key_value   = key_value_in
     AND v.policy_guid = l_metric_guid   
     AND v.collection_timestamp < t.marker_timestamp;

  -- get the violation level of the severity we are looking for

  SELECT DECODE(v.violation_level, MGMT_GLOBAL.G_SEVERITY_CLEAR,    MGMT_GLOBAL.G_STATUS_UP,
                                   MGMT_GLOBAL.G_SEVERITY_CRITICAL, MGMT_GLOBAL.G_STATUS_DOWN,
                                   MGMT_GLOBAL.G_SEVERITY_UNKNOWN)
    INTO l_status
    FROM MGMT_VIOLATIONS v
   WHERE v.target_guid = target_guid_in
     AND v.key_value   = key_value_in
     AND v.policy_guid = l_metric_guid
     AND l_last_sev_ts = v.collection_timestamp;

  RETURN l_status;

  EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN MGMT_GLOBAL.G_STATUS_UNKNOWN;

END GET_SING_BCN_TXN_STATUS;
                             


PROCEDURE GET_BCN_TXN_STATUS (
              target_name_in IN  VARCHAR2,
              target_type_in IN  VARCHAR2,
              test_guids_in  IN  SMP_EMD_STRING_ARRAY,
              bcn_guids_in   IN  SMP_EMD_STRING_ARRAY,
              status_cursor  OUT CURSORTYPE)

IS
  l_target_guid mgmt_targets.target_guid%TYPE;
BEGIN
  l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in );

OPEN status_cursor FOR
 SELECT keys.bcn_guid as bcn_guid, 
        keys.test_guid as test_guid, 
        keys.bcn_name as bcn_name,
        keys.is_avail as is_avail,
        keys.is_local as is_local,
        GET_SING_BCN_TXN_STATUS(target_name_in, target_type_in, l_target_guid, keys.test_guid, keys.key_val) as status,
        keys.key_val as composite_key
   FROM
     (SELECT d.txn_guid as test_guid, t.beacon_target_guid as bcn_guid, t.is_local as is_local, 
             tt.target_name as bcn_name, k.composite_key as key_val, t.participates_avail as is_avail
        FROM MGMT_METRICS_COMPOSITE_KEYS k,
             MGMT_BCN_TXN_DEFN d,
             MGMT_BCN_TARGET t,
             MGMT_TARGETS tt
       WHERE t.target_guid = l_target_guid
         AND tt.target_guid = t.beacon_target_guid
         AND t.beacon_target_guid IN (SELECT * FROM TABLE(CAST(bcn_guids_in AS SMP_EMD_STRING_ARRAY)))
         AND d.txn_guid IN (SELECT * FROM TABLE(CAST(test_guids_in AS SMP_EMD_STRING_ARRAY)))
         AND d.target_guid = l_target_guid
         AND k.target_guid = l_target_guid
         AND k.key_part1_value = d.name
         AND k.key_part2_value = tt.target_name
         AND (k.key_part3_value IS NULL OR k.key_part3_value = ' ')
         AND (k.key_part4_value IS NULL OR k.key_part4_value = ' ')
         AND (k.key_part5_value IS NULL OR k.key_part5_value = ' ')) keys
  ORDER BY keys.test_guid, keys.bcn_guid;

END GET_BCN_TXN_STATUS;          


-------------------------------------------------------------------------------
--  Return true if any of the composite's members are in the state specified 
--  by the searchState argument.
-------------------------------------------------------------------------------
--
FUNCTION anyMembersInState (memberList IN OUT NOCOPY MemberListType,
                   searchState NUMBER) 
    RETURN BOOLEAN IS
    --
    returnFlag BOOLEAN := false;    
BEGIN
  FOR i IN memberList.targetGUID.FIRST..memberList.targetGUID.LAST LOOP
    --      
    IF (memberList.currentStatus(i) = searchState) THEN
        returnFlag := true;
    END IF;
  END LOOP;
--
  RETURN returnFlag;
END anyMembersInState;

--
--  MemberTargetStatus()  - Given the current target guid and state-change currentStatus,
--                          return the current state
--
--
FUNCTION MemberTargetStatus(memberList IN OUT NOCOPY MemberListType,
                            targetGUID RAW, currentStatus NUMBER)
   RETURN NUMBER IS
    returnState  NUMBER := 1;
    tmpState     BOOLEAN;
BEGIN

  FOR i IN memberList.targetGUID.FIRST..memberList.targetGUID.LAST LOOP
    --  Update the state of the current target while walking the list
    IF (memberList.targetGUID(i) = targetGUID) THEN
      memberList.currentStatus(i) := currentStatus;
    END IF;
    --

  END LOOP;
  --
  -------------------------------------------------------------------------------
  --  States that propagate to the composite and their order of prescedence
  --    (Example - if any member is in blackout the composite is blacked out,
  --    even if another member is down -OR- If a member is down the composite is 
  --    down even if another member is experiencing agent-down, error or unknown
  --    status:
  --
  --      Blackout - scheduled downtime takes prescedence over downtime...
  --      Down - down
  --      Agent down - network down, agent down or host down
  --      Error - error evaluating response/status metric
  --      Unknown - first time state of target while agent is calculating response/status
  --
  -------------------------------------------------------------------------------
  tmpState := anyMembersInState(memberList, mgmt_global.G_STATUS_BLACKOUT); 
  IF (tmpState = true) THEN
    returnState := mgmt_global.G_STATUS_BLACKOUT;
  ELSE
      --
      tmpState := anyMembersInState(memberList, mgmt_global.G_STATUS_DOWN);   
      IF (tmpState = true) THEN
    	  returnState := mgmt_global.G_STATUS_DOWN;	
      --
      ELSE
	      tmpState := anyMembersInState(memberList, mgmt_global.G_STATUS_AGENT_DOWN);   
          IF (tmpState = true) THEN
              returnState := mgmt_global.G_STATUS_AGENT_DOWN;
      --
          ELSE
              tmpState := anyMembersInState(memberList, mgmt_global.G_STATUS_ERROR);   
              IF (tmpState = true) THEN
                  returnState := mgmt_global.G_STATUS_ERROR;  
      --
              ELSE
                  tmpState := anyMembersInState(memberList, mgmt_global.G_STATUS_UNKNOWN);   
                  IF (tmpState = true) THEN
                      returnState := mgmt_global.G_STATUS_UNKNOWN;       
                  END IF;
              END IF;
          END IF;
      END IF;
  END IF;

  RETURN returnState;
  
END MemberTargetStatus;

-- internal procedure to get the txn,bcn avail history for a single test and beacon
FUNCTION BCN_TXN_AVAIL (
              target_type_in IN VARCHAR2,
              target_guid_in IN RAW,
              test_guid_in   IN RAW,
              bcn_guid_in    IN RAW,
              target_avail   IN MgmtBcnAvailTableType,
              bcn_avail      IN MgmtBcnAvailTableType,
              key_val_in     IN RAW,
              start_date_in  IN DATE,
              end_date_in    IN DATE,
              tgt_tz_in      IN VARCHAR2,
              to_tz_in       IN VARCHAR2
              ) RETURN MgmtBcnAvailTableType
IS

  availability_cursor     CURSORTYPE;
  memberStatusList        MemberListType;
  inRec                   MgmtBcnAvailRowType :=
                                 MgmtBcnAvailRowType(NULL,NULL,NULL,NULL,NULL);
  openRec                 MgmtBcnAvailRowType :=
                                 MgmtBcnAvailRowType(NULL,NULL,NULL,NULL,NULL);
  outRec                  MgmtBcnAvailRowType :=
                                 MgmtBcnAvailRowType(NULL,NULL,NULL,NULL,NULL);
  availRecords            MgmtBcnAvailTableType := MgmtBcnAvailTableType();
  
  compositeStatus         NUMBER := -999;
  compositeTimezoneRegion VARCHAR2(64);
  recordCount             NUMBER := 0;
  currentState            NUMBER := 1;
  start_date_used         DATE;
  end_date_used           DATE;
BEGIN

memberStatusList.targetGUID := TargetGUIDList(key_val_in, bcn_guid_in, target_guid_in);
memberStatusList.currentStatus := CurrentStatusList(MGMT_GLOBAL.G_STATUS_UNKNOWN, MGMT_GLOBAL.G_STATUS_UP, MGMT_GLOBAL.G_STATUS_UP);
availRecords.EXTEND;

-- combine avail periods coming from severities (CLEAR = UP, CRITICAL = DOWN), 
-- avail periods of the target and avail periods of beacon
start_date_used := MGMT_GLOBAL.ADJUST_TZ(start_date_in, to_tz_in, tgt_tz_in); --in tgt tz
end_date_used := MGMT_GLOBAL.ADJUST_TZ(end_date_in, to_tz_in, tgt_tz_in); --in tgt tz


OPEN availability_cursor FOR 
SELECT target_guid, severity_guid, current_status, start_collection_timestamp, end_collection_timestamp FROM
 ( 
   SELECT key_val_in as target_guid, null as severity_guid,
        DECODE(v.violation_level, MGMT_GLOBAL.G_SEVERITY_CRITICAL, MGMT_GLOBAL.G_STATUS_DOWN, MGMT_GLOBAL.G_STATUS_UP) as current_status,
        MGMT_GLOBAL.ADJUST_TZ(GREATEST(v.collection_timestamp, start_date_used),tgt_tz_in, to_tz_in) AS start_collection_timestamp,
        MGMT_GLOBAL.ADJUST_TZ(
                DECODE(v.violation_duration, null, 
                       end_date_used, 
                       LEAST((v.collection_timestamp + v.violation_duration / 24), end_date_used)
                      ), 
                tgt_tz_in, 
                to_tz_in
                ) AS end_collection_timestamp
   FROM
  (SELECT DISTINCT m.metric_guid as metric_guid
    FROM MGMT_TEST t,
         MGMT_BCN_TXN_DEFN d,
         MGMT_METRICS m
   WHERE t.test_type = d.txn_type
     AND d.txn_guid = test_guid_in
     AND t.avail_metric = m.metric_name
     AND t.avail_metric_column = m.metric_column
     AND target_type_in = m.target_type) metric_info,
   MGMT_VIOLATIONS v
  WHERE v.target_guid = target_guid_in
    AND v.policy_guid = metric_info.metric_guid
    AND v.key_value = key_val_in
    AND v.collection_timestamp < end_date_used
    AND ( ( ( v.collection_timestamp + (v.violation_duration / 24)) > start_date_used ) OR ( v.violation_duration is null ) )
  UNION ALL
  (SELECT target_guid, 
          severity_guid, 
          current_status, 
          GREATEST(start_collection_timestamp, start_date_in) AS start_collection_timestamp, 
          LEAST(end_collection_timestamp, end_date_in) AS end_collection_timestamp
     FROM TABLE(CAST(bcn_avail AS MgmtBcnAvailTableType))
    WHERE start_collection_timestamp < end_date_in -- dates are in to_tz
      AND end_collection_timestamp > start_date_in
      AND start_collection_timestamp < end_collection_timestamp
  )
  UNION ALL
  (SELECT target_guid, 
          severity_guid, 
          current_status, 
          GREATEST(start_collection_timestamp, start_date_in) AS start_collection_timestamp, 
          LEAST(end_collection_timestamp, end_date_in) AS end_collection_timestamp
     FROM TABLE(CAST(target_avail AS MgmtBcnAvailTableType))
    WHERE start_collection_timestamp < end_date_in -- dates are in to_tz
      AND end_collection_timestamp > start_date_in
      AND start_collection_timestamp < end_collection_timestamp
  )
  ) tbl
ORDER BY start_collection_timestamp;

LOOP
    FETCH availability_cursor INTO inRec.target_guid, inRec.severity_guid,
                                   inRec.current_status,
                                   inRec.start_collection_timestamp,
                                   inRec.end_collection_timestamp;
    EXIT WHEN availability_cursor%NOTFOUND;

    currentState := MemberTargetStatus(memberStatusList,
                                       inRec.target_guid,
                                       inRec.current_status);
   

 
    --
    -- We only want to examine changes in status of the composite and its
    -- members.  If the new record has the same status as the composite,
    -- skip to the next.
    --
    IF (currentState != compositeStatus) THEN
      IF (currentState != 1) THEN
        IF (openRec.target_guid IS NULL) THEN
          --
          -- If this is the first time through, we just assign the input record
          -- to the temporary and make sure the end timestamp is null.
          --
          openRec := inRec;
          openRec.current_status := currentState;       
          openRec.target_guid := key_val_in;
          openRec.end_collection_timestamp := null;
        ELSE
          --
          -- Move the temporary record to the output record and assign the
          -- the start timestamp of the input record to the end timestamp of the
          -- output record.
          --
          outRec := openRec;
          outRec.end_collection_timestamp := inRec.start_collection_timestamp;
          IF ((outRec.target_guid IS NOT NULL) AND
              (inRec.start_collection_timestamp >
               openRec.start_collection_timestamp) AND
              (inRec.start_collection_timestamp > start_date_in)) THEN
            recordCount := recordCount + 1;
            availRecords(recordCount) := outRec;
            availRecords.EXTEND;               
          END IF;
          --
          -- Move the input record to the temporary record and null the end
          -- timestamp.
          --
          openRec := inRec;           
          openRec.current_status := currentState;
          openRec.target_guid := key_val_in;
          openRec.end_collection_timestamp := null;
        END IF; -- End if openRec.target_guid is null
        --
        -- Maintain the composite status.
        --
        compositeStatus := currentState;
      ELSE
        --
        -- If all members are up, then send the completed record back to the
        -- caller.
        --
        IF (currentState = MGMT_GLOBAL.G_STATUS_UP) THEN        
          --
          -- Move the temporary record to the output record and assign the
          -- the start timestamp of the input record to the end timestamp of the
          -- output record.
          --

          outRec := openRec;
          outRec.end_collection_timestamp := inRec.start_collection_timestamp;
          IF outRec.target_guid IS NOT NULL THEN
            recordCount := recordCount + 1;
            availRecords(recordCount) := outRec;
            availRecords.EXTEND;
--            PIPE ROW(outRec);
          END IF;
          --
          -- Move the input record to the temporary record and null the end
          -- timestamp.
          --
          openRec := inRec;
          openRec.target_guid := key_val_in;
          openRec.end_collection_timestamp := null;
          --
          -- Maintain the composite status.
          --
          compositeStatus := currentState;
        END IF; -- End if allMembersUp
      END IF; -- End if currentState = 0
    END IF; -- End currentState != compositeStatus

  END LOOP;
  --
  -- When there are no more records to process for the members, and the open
  -- record is valid, send the open record back as the last record to the
  -- caller.
  --
  IF openRec.target_guid IS NOT NULL THEN
    recordCount := recordCount + 1;
    -- close the open record first
    openRec.end_collection_timestamp := end_date_in;
    IF recordCount = 1 THEN
      availRecords := MgmtBcnAvailTableType(openRec);
    ELSE
      availRecords(recordCount) := openRec;
    END IF;
  END IF;
  --
  -- Clean up and return to the caller.
  --
  IF availability_cursor%ISOPEN THEN
    CLOSE availability_cursor;
  END IF;
  memberStatusList.targetGUID.DELETE;
  memberStatusList.currentStatus.DELETE;

  RETURN availRecords;

END BCN_TXN_AVAIL;    

PROCEDURE GET_BCN_TXN_AVAIL (
              target_name_in IN VARCHAR2,
              target_type_in IN VARCHAR2,
              start_date_in  IN DATE,
              end_date_in    IN DATE,
              num_days_in    IN  NUMBER,
              test_guids_in IN SMP_EMD_STRING_ARRAY,
              bcn_guids_in  IN SMP_EMD_STRING_ARRAY,
              tz_used        IN  VARCHAR2,
              start_date_out OUT DATE,
              end_date_out   OUT DATE,
              status_cursor OUT mgmt_bcn_txn_avail_array)

IS
  l_target_guid mgmt_targets.target_guid%TYPE;
  bcn_cnt INTEGER;
  test_cnt INTEGER;
  total_cnt INTEGER;
  target_avail_tbl MgmtBcnAvailTableType := MgmtBcnAvailTableType();
  bcn_avail_tbl MgmtBcnAvailTableType;
  temp_tbl MgmtBcnAvailTableType;
  bcn_guid mgmt_targets.target_guid%TYPE;
  test_guid mgmt_targets.target_guid%TYPE;
  comp_key mgmt_targets.target_guid%TYPE;
  l_bcn_start_date DATE;
  l_bcn_end_date DATE;
  l_test_start_date DATE;
  l_test_end_date DATE;
  l_target_start_date DATE;
  l_target_end_date DATE;
  l_tgt_tz mgmt_targets.timezone_region%TYPE;
  l_bcn_tz mgmt_targets.timezone_region%TYPE;

  l_to_tz mgmt_targets.timezone_region%TYPE;
  l_temp_count PLS_INTEGER;
  l_temp_row MgmtBcnAvailRowType;
BEGIN
  l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in );

  SELECT timezone_region INTO l_tgt_tz
  FROM MGMT_TARGETS
  WHERE target_guid = l_target_guid;

  IF(tz_used IS NULL) THEN
    l_to_tz := l_tgt_tz;
  ELSE
    l_to_tz := tz_used;
  END IF;

  gensvc_view.compute_disp_start_end_dates(l_target_guid, NULL, start_date_in, 
                                      end_date_in, num_days_in, start_date_out, end_date_out);

  gensvc_view.compute_start_end_dates(l_target_guid, NULL, start_date_in, 
                                      end_date_in, num_days_in, l_target_start_date, l_target_end_date);


  BEGIN
    SELECT MgmtBcnAvailRowType(avail.target_guid, null, 
          DECODE(avail.current_status, MGMT_GLOBAL.G_STATUS_BLACKOUT, avail.current_status, MGMT_GLOBAL.G_STATUS_UP),
          MGMT_GLOBAL.ADJUST_TZ(GREATEST(avail.start_collection_timestamp, start_date_in), l_tgt_tz, l_to_tz),
          MGMT_GLOBAL.ADJUST_TZ(NVL(avail.end_collection_timestamp, end_date_in), l_tgt_tz, l_to_tz)) BULK COLLECT INTO target_avail_tbl
     FROM mgmt_availability avail
    WHERE avail.target_guid = l_target_guid
      AND NVL(avail.end_collection_timestamp, end_date_in) > l_target_start_date
      AND NVL(avail.end_collection_timestamp, end_date_in) > avail.start_collection_timestamp
      AND avail.start_collection_timestamp <= l_target_end_date;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    target_avail_tbl := MgmtBcnAvailTableType();
  END;

  status_cursor := mgmt_bcn_txn_avail_array();
  total_cnt := 0;

  FOR bcn_cnt IN 1..bcn_guids_in.COUNT LOOP
    bcn_guid :=  HEXTORAW(bcn_guids_in(bcn_cnt));
    bcn_avail_tbl := MgmtBcnAvailTableType();

    -- Get availability periods for the beacon. Adjust the timezone to the target timezone.

    SELECT GREATEST( MGMT_GLOBAL.ADJUST_TZ(l_target_start_date, l_tgt_tz, t.timezone_region), mgmt_avail.get_target_start_marker_time(bcn_guid)),
           LEAST( MGMT_GLOBAL.ADJUST_TZ(l_target_end_date, l_tgt_tz, t.timezone_region), mgmt_avail.get_target_end_marker_time(bcn_guid)),
           t.timezone_region INTO l_bcn_start_date, l_bcn_end_date, l_bcn_tz
      FROM MGMT_TARGETS t
     WHERE t.target_guid = bcn_guid;

    SELECT MgmtBcnAvailRowType(avail.target_guid, null, avail.current_status,
            MGMT_GLOBAL.ADJUST_TZ(GREATEST(avail.start_collection_timestamp, l_bcn_start_date), t.timezone_region, l_to_tz),
            MGMT_GLOBAL.ADJUST_TZ(NVL(avail.end_collection_timestamp, l_bcn_end_date),t.timezone_region, l_to_tz)) BULK COLLECT INTO bcn_avail_tbl
       FROM mgmt_availability avail, mgmt_targets t
      WHERE avail.target_guid = bcn_guid
        AND t.target_guid = bcn_guid
        AND NVL(avail.end_collection_timestamp, l_bcn_end_date) > l_bcn_start_date
        AND NVL(avail.end_collection_timestamp, l_bcn_end_date) > avail.start_collection_timestamp
        AND avail.start_collection_timestamp <= l_bcn_end_date;
    
    -- We have three independent sources to compute the availability periods from
    -- Therefore we need to make sure that final_start_marker = GREATEST(bcn_start_marker, tgt_start_marker, test_start_marker)
    -- and final_end_marker = LEAST(bcn_end_marker, tgt_end_marker, test_end_marker)

    l_bcn_start_date := GREATEST(MGMT_GLOBAL.ADJUST_TZ(l_bcn_start_date, l_bcn_tz, l_tgt_tz), l_target_start_date);
    l_bcn_end_date := LEAST(MGMT_GLOBAL.ADJUST_TZ(l_bcn_end_date, l_bcn_tz, l_tgt_tz), l_target_end_date);
  
    FOR test_cnt IN 1..test_guids_in.COUNT LOOP
      total_cnt := total_cnt + 1;
      status_cursor.extend;
      test_guid := HEXTORAW(test_guids_in(test_cnt));
      SELECT k.composite_key INTO comp_key
         FROM MGMT_METRICS_COMPOSITE_KEYS k,
              MGMT_BCN_TXN_DEFN d,
              MGMT_TARGETS t
        WHERE t.target_guid = bcn_guid
          AND d.txn_guid = test_guid
          AND k.target_guid = l_target_guid
          AND k.key_part1_value = d.name
          AND k.key_part2_value = t.target_name
          AND (k.key_part3_value IS NULL OR k.key_part3_value = ' ')
          AND (k.key_part4_value IS NULL OR k.key_part4_value = ' ')
          AND (k.key_part5_value IS NULL OR k.key_part5_value = ' ')          
          AND rownum=1;

      l_test_start_date := MGMT_GLOBAL.ADJUST_TZ(get_test_start_date(l_target_guid, test_guid, l_bcn_start_date, l_bcn_end_date), l_tgt_tz, l_to_tz);
      l_test_end_date := MGMT_GLOBAL.ADJUST_TZ(get_test_end_date(l_target_guid, test_guid, l_bcn_start_date, l_bcn_end_date), l_tgt_tz, l_to_tz);


      temp_tbl := BCN_TXN_AVAIL(target_type_in, 
                                l_target_guid, 
                                test_guid,
                                bcn_guid,
                                target_avail_tbl,
                                bcn_avail_tbl,
                                comp_key,
                                l_test_start_date,
                                l_test_end_date,
                                l_tgt_tz,
                                l_to_tz);
--      FOR l_temp_count IN temp_tbl.FIRST..temp_tbl.LAST
--      LOOP
--        l_temp_row := temp_tbl(l_temp_count);
--        MGMT_LOG.LOG_ERROR('BCN_TXN', 15, l_temp_row.target_guid || ' ' || l_temp_row.current_status || ' ' || to_char(l_temp_row.start_collection_timestamp,'dd/mm/yyyy hh:mi:ss') || ' ' || to_char(l_temp_row.end_collection_timestamp, 'dd/mm/yyyy hh:mi:ss'));     
--      END LOOP;
      status_cursor(total_cnt) := mgmt_bcn_txn_avail(bcn_guid, test_guid, temp_tbl);
    END LOOP;
    bcn_avail_tbl.DELETE;
  END LOOP;
  
  target_avail_tbl.DELETE;
  
END GET_BCN_TXN_AVAIL;


PROCEDURE GET_TEST_AVAIL (
          target_name_in IN  VARCHAR2,
          target_type_in IN  VARCHAR2,
          test_name_in   IN  VARCHAR2,
          test_type_in   IN  VARCHAR2,
          num_days_in    IN  NUMBER,
          start_date_out OUT DATE,
          end_date_out   OUT DATE,
          avail_pct_out  OUT NUMBER,
          current_status OUT NUMBER,
          since          OUT DATE,
          avail_states   OUT CURSORTYPE)

IS
  l_target_guid mgmt_targets.target_guid%TYPE;
  l_test_guid mgmt_targets.target_guid%TYPE;
  l_test_nv_pair SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
  l_start_date DATE;
  l_end_date DATE;
BEGIN
  l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in );
  l_test_guid :=   get_test_guid(l_target_guid, test_name_in, test_type_in );
 
  gensvc_view.compute_disp_start_end_dates(l_target_guid, null, null, null, num_days_in, start_date_out, end_date_out);

  l_test_nv_pair.EXTEND;
  l_test_nv_pair(1) := SMP_EMD_NVPAIR(test_name_in, test_type_in);

  GET_TESTS_STATUS_AVAIL(target_name_in, target_type_in, l_test_nv_pair, start_date_out, end_date_out, null, null, avail_states);
  GET_SING_TEST_STATUS(target_name_in, target_type_in, l_test_guid, start_date_out, end_date_out, null, avail_pct_out, current_status, since);

  l_test_nv_pair.DELETE;

END GET_TEST_AVAIL;

PROCEDURE GET_CUR_BEACONS_STATUS (
        bcn_guids_in  IN SMP_EMD_STRING_ARRAY,
        status_cursor OUT CURSORTYPE)
IS        
BEGIN
  OPEN status_cursor FOR
  SELECT cur_avail.target_guid, cur_avail.current_status 
    FROM mgmt_current_availability cur_avail
   WHERE RAWTOHEX(cur_avail.target_guid) IN (SELECT * FROM TABLE(CAST(bcn_guids_in AS SMP_EMD_STRING_ARRAY)));
END GET_CUR_BEACONS_STATUS;

PROCEDURE GET_CUR_BEACONS_AGENT_INFO (
        bcn_guids_in      IN  SMP_EMD_STRING_ARRAY,
        agent_info_cursor OUT CURSORTYPE)
IS        
BEGIN
  OPEN agent_info_cursor FOR
   SELECT t1.target_guid, p1.property_value, p2.property_value 
     FROM mgmt_targets t1, mgmt_targets t2, mgmt_target_properties p1, mgmt_target_properties p2 
    WHERE t1.emd_url = t2.emd_url 
      AND t1.target_type = 'oracle_beacon' 
      AND t2.target_type = 'oracle_emd' 
      AND p1.target_guid = t2.target_guid 
      AND p1.property_name = 'OS' 
      AND p2.target_guid = t2.target_guid 
      AND p2.property_name = 'Version'
      AND RAWTOHEX(t1.target_guid) IN (SELECT * FROM TABLE(CAST(bcn_guids_in AS SMP_EMD_STRING_ARRAY)));
END GET_CUR_BEACONS_AGENT_INFO;

  
PROCEDURE GET_TXN_ALERTS (
              target_name_in IN VARCHAR2,
              target_type_in IN VARCHAR2,
              test_name_in IN VARCHAR2,
              test_type_in IN VARCHAR2,
              alerts_out OUT CURSORTYPE)
IS
  l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
  l_test_guid MGMT_BCN_TXN_DEFN.TXN_GUID%TYPE;
BEGIN

  l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in );
  l_test_guid :=   get_test_guid(l_target_guid, test_name_in, test_type_in );
  

 OPEN alerts_out FOR
  SELECT keys.key_part2_value as beacon_name,
         alert.message as message,
         alert.message_nlsid as message_nlsid,
         alert.message_params as message_params,
         alert.violation_level as severity_code,
         alert.collection_timestamp as triggered_date,
         alert.violation_guid as severity_guid,
         metr.metric_label,
         metr.metric_label_nlsid,
         metr.column_label, 
         metr.column_label_nlsid
    FROM mgmt_bcn_txn_defn test,
         mgmt_current_violation alert,
         mgmt_metrics_composite_keys keys,
         mgmt_metrics metr,
         mgmt_targets t
   WHERE test.target_guid = t.target_guid
     AND t.target_guid = l_target_guid
     AND test.txn_guid = l_test_guid
     AND keys.target_guid = t.target_guid
     AND keys.key_part1_value = test.name
     -- AND (keys.key_part3_value IS NULL OR keys.key_part3_value = ' ')
     -- AND (keys.key_part4_value IS NULL OR keys.key_part4_value = ' ')
     -- AND (keys.key_part5_value IS NULL OR keys.key_part5_value = ' ')
     AND alert.key_value = keys.composite_key
     AND alert.target_guid = t.target_guid
     AND ( alert.violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING  OR
           alert.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL
         )
     AND metr.metric_guid = alert.policy_guid
     AND t.target_type = metr.target_type
     AND t.type_meta_ver = metr.type_meta_ver
     AND (t.category_prop_1 = metr.category_prop_1 OR metr.category_prop_1 = ' ')
     AND (t.category_prop_2 = metr.category_prop_2 OR metr.category_prop_2 = ' ')
     AND (t.category_prop_3 = metr.category_prop_3 OR metr.category_prop_3 = ' ')
     AND (t.category_prop_4 = metr.category_prop_4 OR metr.category_prop_4 = ' ')
     AND (t.category_prop_5 = metr.category_prop_5 OR metr.category_prop_5 = ' ');

END GET_TXN_ALERTS;

END MGMT_BCN_TXN_VIEW;
/
show errors;