Rem
Rem $Header: availability_pkgbodys.sql 21-sep-2006.03:16:17 kannatar Exp $
Rem
Rem availability_pkgbodys.sql
Rem
Rem Copyright (c) 2002, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      availability_pkgbodys.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    kannatar    09/21/06 - 
Rem    sbhagwat    03/29/06 - Retroactive Blackout 
Rem    pratagar    08/29/05 - bug 4572196 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    vkgarg      05/20/05 - bug # 3970199 
Rem    yaofeng     05/05/05 - add timezone 
Rem    jsadras     03/11/05 - Bug:4186866, replace tgt_sysdate_wguid 
Rem    neearora    03/03/05 - Added entry for emdw_log 
Rem    dcawley     02/21/05 - Replace enter super user mode 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    jpyang      11/22/04 - bug 3725326 
Rem    dcawley     07/07/04 - Increase user name size 
Rem    ancheng     02/09/04 - add availability formula 
Rem    ancheng     09/24/03 - bug 3146085 
Rem    ancheng     08/06/03 - severity/annotation change 
Rem    rpatti      04/15/03 - add func to get target availability
Rem    tjaiswal    10/04/02 - Bug 2607107
Rem    tjaiswal    09/19/02 - Bug 2566344
Rem    tjaiswal    07/02/02 - Fix sorting of avail tables
Rem    tjaiswal    06/26/02 - Fix avail overview plsql
Rem    tjaiswal    06/20/02 - Cleanup plsql after review
Rem    tjaiswal    06/17/02 - Misc avail plsql changes
Rem    tjaiswal    06/14/02 - Modify queries for avail overview details
Rem    tjaiswal    06/10/02 - Add plsql for avail ui changes
Rem    rpatti      05/21/02 - show more detailed availability states
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Restructured.
Rem    rpatti      04/12/02 - ignore blackout in avail% calculation
Rem    smishra     01/23/02 - Fixed the NPE bug after metric_guid cutover.
Rem    rpinnama    01/15/02 - Add support for unmonitored state.
Rem    edemembe    12/27/01 - Removing target name/type and metric name/column references
Rem    rpinnama    10/23/01 - Use target time for calculating the availability
Rem    tjaiswal    10/11/01 - Fix get_outage_detail
Rem    rpinnama    10/15/01 - Rollback TIMESTAMP WITH TIMEZONE changes
Rem    smishra     10/09/01 - Put the workaround for jdbc timestamp bug
Rem    smishra     10/05/01 - Created
Rem

-------------------------------------------------------
-- The PL/SQL package body for mgmt_avail
-------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY mgmt_avail
IS

-- 
-- Internal procedure to get the emd target_guid given the target_guid of a 
-- target
-- since not all targets are agent target, return null (instead of target
-- not found error when target is a repository only target.
FUNCTION get_emd_target
  (target_guid_in  IN  mgmt_targets.target_guid%TYPE)
  RETURN mgmt_targets.target_guid%TYPE
IS

  l_target_guid  mgmt_targets.target_guid%TYPE;

BEGIN
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_emd_target:Entry target_guid_in = ' || target_guid_in, MODULE_NAME);
  END IF;

  SELECT target_guid 
    INTO l_target_guid
    FROM mgmt_targets
   WHERE target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE
     AND emd_url = ( SELECT emd_url 
                       FROM mgmt_targets 
                      WHERE target_guid = target_guid_in);

  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_emd_target:Exit l_target_guid  = ' || l_target_guid, MODULE_NAME);
  END IF;

  RETURN l_target_guid;

EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    IF (EMDW_LOG.p_is_info_set)THEN
       EMDW_LOG.info('get_emd_target:Exit Exception NO_DATA_FOUND returning NULL', MODULE_NAME);
    END IF;

    RETURN NULL;

END get_emd_target;

-- 
-- Internal procedure for get_avail_overview_details
--
PROCEDURE get_avail_summary(
              target_guid_in  IN  mgmt_targets.target_guid%TYPE,
              target_type_in  IN  VARCHAR2,
              recomputed_start_date_in IN DATE,
              recomputed_end_date_in IN DATE,
              avail_summary_cur_out OUT CURSORTYPE
              )
IS
  l_num_of_days NUMBER;
  l_last_load_time DATE := NULL;
  l_timezone_region VARCHAR2(64) := NULL;
BEGIN
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_avail_summary:Entry target_guid_in = ' || target_guid_in ||
                   ' target_type_in = '           || target_type_in           ||
                   ' recomputed_start_date_in = ' || recomputed_start_date_in ||
                   ' recomputed_end_date_in = '   || recomputed_end_date_in, MODULE_NAME);
  END IF;

  -- get the number of days based on recomputed start date and end date
  l_num_of_days := recomputed_end_date_in - recomputed_start_date_in;

  -- target begin marker is the first time from when the target's availability
  -- is known
  -- target end marker is the time upto which the target's availability is 
  -- known reliably
   
  SELECT last_load_time, timezone_region
    INTO l_last_load_time, l_timezone_region
    FROM mgmt_targets
   WHERE target_guid = target_guid_in;

  -- get the avail summary data  
  OPEN avail_summary_cur_out FOR 
  SELECT l_last_load_time "last_load_time",
         l_timezone_region "timezone_region",
         up_time,
         total_downtime,
         total_error_time,
         total_unmonitored_time,
         total_unreachable_time,
         total_blackout_time,
         total_unknown_time,
         times_down,
         (DECODE(times_down,
                 0, 0,
                 (total_downtime+
                  total_error_time+
                  total_unmonitored_time+
                  total_unreachable_time+
                  total_unknown_time)/times_down)) avg_repair_time,
         LEAST(l_num_of_days,longest_downtime) longest_downtime 
  FROM ( 
  SELECT 
    SUM(DECODE(a.current_status,
               MGMT_GLOBAL.G_STATUS_UP, NVL(a.end_collection_timestamp, recomputed_end_date_in)-
                  GREATEST(a.start_collection_timestamp, recomputed_start_date_in),
               0)) up_time,
    SUM(DECODE(a.current_status,
               MGMT_GLOBAL.G_STATUS_DOWN, NVL(a.end_collection_timestamp, recomputed_end_date_in)-
                  GREATEST(a.start_collection_timestamp, recomputed_start_date_in),
               0)) total_downtime,
    SUM(DECODE(a.current_status,
               MGMT_GLOBAL.G_STATUS_ERROR, NVL(a.end_collection_timestamp, recomputed_end_date_in)-
                  GREATEST(a.start_collection_timestamp, recomputed_start_date_in),
               0)) total_error_time,
    SUM(DECODE(a.current_status,
              MGMT_GLOBAL.G_STATUS_AGENT_DOWN, NVL(a.end_collection_timestamp, recomputed_end_date_in)-
                  GREATEST(a.start_collection_timestamp, recomputed_start_date_in),
               0)) total_unmonitored_time,
    SUM(DECODE(a.current_status,
               MGMT_GLOBAL.G_STATUS_UNREACHABLE, NVL(a.end_collection_timestamp, recomputed_end_date_in)-
                  GREATEST(a.start_collection_timestamp, recomputed_start_date_in),
               0)) total_unreachable_time,
    SUM(DECODE(a.current_status,
               MGMT_GLOBAL.G_STATUS_BLACKOUT, NVL(a.end_collection_timestamp, recomputed_end_date_in)-
                  GREATEST(a.start_collection_timestamp, recomputed_start_date_in),
               0)) total_blackout_time,
    SUM(DECODE(a.current_status,
               MGMT_GLOBAL.G_STATUS_UNKNOWN, NVL(a.end_collection_timestamp, recomputed_end_date_in)-
                  GREATEST(a.start_collection_timestamp, recomputed_start_date_in),
               0)) total_unknown_time,
    SUM(DECODE(a.current_status,
               MGMT_GLOBAL.G_STATUS_DOWN, 1, 
               MGMT_GLOBAL.G_STATUS_ERROR, 1,
               MGMT_GLOBAL.G_STATUS_AGENT_DOWN, 1, 
               MGMT_GLOBAL.G_STATUS_UNREACHABLE, 1,
               MGMT_GLOBAL.G_STATUS_UNKNOWN, 1,
               0)) times_down,
    MAX(DECODE(a.current_status,
               MGMT_GLOBAL.G_STATUS_DOWN, NVL(a.end_collection_timestamp, recomputed_end_date_in)-
                  GREATEST(a.start_collection_timestamp, recomputed_start_date_in),
               MGMT_GLOBAL.G_STATUS_ERROR, NVL(a.end_collection_timestamp, recomputed_end_date_in)-
                  GREATEST(a.start_collection_timestamp, recomputed_start_date_in),
               MGMT_GLOBAL.G_STATUS_AGENT_DOWN, NVL(a.end_collection_timestamp, recomputed_end_date_in)-
                  GREATEST(a.start_collection_timestamp, recomputed_start_date_in),
               MGMT_GLOBAL.G_STATUS_UNREACHABLE, NVL(a.end_collection_timestamp, recomputed_end_date_in)-
                  GREATEST(a.start_collection_timestamp, recomputed_start_date_in),
               MGMT_GLOBAL.G_STATUS_UNKNOWN, NVL(a.end_collection_timestamp, recomputed_end_date_in)-
                  GREATEST(a.start_collection_timestamp, recomputed_start_date_in),
               0)) longest_downtime
   FROM MGMT_AVAILABILITY a
   WHERE a.target_guid = target_guid_in
     AND NVL(a.end_collection_timestamp, recomputed_end_date_in) > recomputed_start_date_in
     AND a.start_collection_timestamp <= recomputed_end_date_in);
   
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_avail_summary:Exit', MODULE_NAME);
  END IF;
END get_avail_summary;

--
-- get_avail_current_status function
--
--  PURPOSE: Procedure to find out the current status for a given target.
--
--  IN parameters:
--    target_name_in : target name
--    target_type_in : target type
--
--  Returns:
--    the target's current availability state
--
--  ERROR CODES:
--    1. target does not exist
--
FUNCTION get_avail_current_status(
              target_name_in  IN  VARCHAR2,
              target_type_in  IN  VARCHAR2)
  RETURN NUMBER
IS
  -- Unknown status to be returned by default
  l_current_status_val NUMBER := MGMT_GLOBAL.G_STATUS_UNKNOWN;
  l_target_guid mgmt_targets.target_guid%TYPE;
BEGIN
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_avail_current_status:Entry target_name_in = ' || target_name_in ||
                   ' target_type_in  = '  || target_type_in, MODULE_NAME);
  END IF;
  
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);

  SELECT a.current_status
    INTO l_current_status_val
    FROM mgmt_current_availability a
   WHERE a.target_guid=l_target_guid;


  IF l_current_status_val IS NOT NULL
  THEN
    IF (EMDW_LOG.p_is_info_set)THEN
        EMDW_LOG.info('get_avail_current_status:Exit l_current_status_val = ' ||
                      l_current_status_val, MODULE_NAME);
    END IF;
    return l_current_status_val;
  -- else return unknown status
  ELSE
    IF (EMDW_LOG.p_is_info_set)THEN
        EMDW_LOG.info('get_avail_current_status:Exit l_current_status_val = ' ||
                      MGMT_GLOBAL.G_STATUS_UNKNOWN, MODULE_NAME);
    END IF;

    return MGMT_GLOBAL.G_STATUS_UNKNOWN;
  END IF;

  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      IF (EMDW_LOG.p_is_info_set)THEN
          EMDW_LOG.info('get_avail_current_status:Exit l_current_status_val = ' ||
                        MGMT_GLOBAL.G_STATUS_UNKNOWN, MODULE_NAME);
       END IF;
      return MGMT_GLOBAL.G_STATUS_UNKNOWN;

    WHEN TOO_MANY_ROWS
    THEN
      RAISE;

END;

--
-- get_target_start_marker_time function
--
--  PURPOSE: Procedure to find out the start marker time for a given target.
--           start marker time for a target is the first time from when the 
--           target's availability is known
--
FUNCTION get_target_start_marker_time(
              target_name_in  IN  VARCHAR2,
              target_type_in  IN  VARCHAR2)
  RETURN DATE
IS

  l_target_guid  mgmt_targets.target_guid%TYPE;
  l_start_marker_time DATE;

BEGIN
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_target_start_marker_time:Entry target_name_in = ' || target_name_in ||
                   ' target_type_in  = '  || target_type_in, MODULE_NAME);
  END IF;
  -- first get the target_guid
  -- may throw a TARGET_DOES_NOT_EXIST EXCEPTION
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in); 

  -- get the start marker time
  l_start_marker_time := get_target_start_marker_time( l_target_guid );
  
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_target_start_marker_time:Exit l_start_marker_time = '||
                    l_start_marker_time, MODULE_NAME);
  END IF;

  RETURN l_start_marker_time;

END get_target_start_marker_time;

--
-- get_target_start_marker_time function
--
--  PURPOSE: Procedure to find out the start marker time for a given target.
--           start marker time for a target is the first time from when the 
--           target's availability is known
--
FUNCTION get_target_start_marker_time(
              target_guid_in  IN  mgmt_targets.target_guid%TYPE)
  RETURN DATE
IS

  l_start_marker_time DATE := NULL;
  
  CURSOR timestampCursor IS
    SELECT end_collection_timestamp 
      FROM mgmt_availability 
     WHERE target_guid = target_guid_in
     ORDER BY start_collection_timestamp asc; 

BEGIN
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_target_start_marker_time:Entry target_guid_in = ' || target_guid_in, MODULE_NAME);
  END IF;

  -- get the start marker time
  -- TODO: should get the start marker time from mgmt_availability_marker table
  
  -- end time of first avail record is the start marker time.
  FOR record IN timestampCursor
  LOOP
    -- pick only the first record
    l_start_marker_time := record.end_collection_timestamp;
    EXIT;
  END LOOP;
  
  -- if this is null, use the load timestamp for this target  
  IF( l_start_marker_time IS NULL )
  THEN
    BEGIN
      SELECT load_timestamp
        INTO l_start_marker_time
        FROM mgmt_targets
       WHERE target_guid = target_guid_in;

      EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
          RAISE MGMT_GLOBAL.target_does_not_exist;
    END;        
  END IF;
    
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_target_start_marker_time:Exit l_start_marker_time = '||
                    l_start_marker_time, MODULE_NAME);
  END IF;

  RETURN l_start_marker_time;
    
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      RAISE MGMT_GLOBAL.target_does_not_exist;

END get_target_start_marker_time;

--
-- get_target_end_marker_time function
--
--  PURPOSE: Procedure to find out the end marker time for a given target.
--           end marker time for a target is the time upto which the target's 
--           availability is known reliably
--
FUNCTION get_target_end_marker_time(
              target_name_in  IN  VARCHAR2,
              target_type_in  IN  VARCHAR2)
  RETURN DATE
IS

  l_target_guid mgmt_targets.target_guid%TYPE;
  l_end_marker_time DATE;

BEGIN
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_target_end_marker_time:Entry target_name_in = ' || target_name_in ||
                   ' target_type_in = ' || target_type_in,MODULE_NAME);
  END IF;
  -- first get the target_guid
  -- may throw a TARGET_DOES_NOT_EXIST EXCEPTION
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in); 

  -- get the end marker time
  l_end_marker_time := get_target_end_marker_time( l_target_guid );
  
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_target_end_marker_time:Exit l_end_marker_time = '||
                    l_end_marker_time, MODULE_NAME);
  END IF;

  RETURN l_end_marker_time;

END get_target_end_marker_time;

--
-- get_target_end_marker_time function
--
--  PURPOSE: Procedure to find out the end marker time for a given target.
--           end marker time for a target is the time upto which the target's 
--           availability is known reliably
--
FUNCTION get_target_end_marker_time(
              target_guid_in  IN  mgmt_targets.target_guid%TYPE)
  RETURN DATE
IS

  l_end_marker_time DATE;

BEGIN
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_target_end_marker_time:Entry target_guid_in = ' || target_guid_in, MODULE_NAME);
  END IF;

  -- get the end marker time
  SELECT marker_timestamp
    INTO l_end_marker_time
    FROM mgmt_availability_marker
   WHERE target_guid = target_guid_in;

  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_target_end_marker_time:Exit l_end_marker_time = '||
                    l_end_marker_time, MODULE_NAME);
  END IF;

  RETURN l_end_marker_time;
    
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      RAISE MGMT_GLOBAL.target_does_not_exist;
  
END get_target_end_marker_time;

--
-- get_availability_summary procedure
--
PROCEDURE get_availability_summary(
              target_name_in  IN  VARCHAR2,
              target_type_in  IN  VARCHAR2,
              num_of_days_in  IN  NUMBER,
              avail_summary_cur_out OUT CURSORTYPE
              )
IS
  l_target_guid mgmt_targets.target_guid%TYPE;
  l_target_sysdate DATE;
  l_start_date DATE;
  l_end_date DATE;
  l_start_marker_time DATE;
  l_end_marker_time DATE;
  l_recomputed_start_date DATE;
  l_recomputed_end_date DATE;
BEGIN
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_availability_summary:Entry target_name_in = ' || target_name_in ||
                   ' target_type_in = '        || target_type_in ||
                   ' num_of_days_in = '        || num_of_days_in, MODULE_NAME);
  END IF;
  -- Store the target guid to improve performance.
  l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in );
  
  -- Get the target's sysdate using targets time zone.
  l_target_sysdate := MGMT_TARGET.sysdate_target(l_target_guid);

  -- use num_days_in to figure out start date and end date
  l_start_date := l_target_sysdate - num_of_days_in;
  l_end_date := l_target_sysdate;

  -- determine the start marker time
  l_start_marker_time := get_target_start_marker_time( l_target_guid );
    
  -- determine the end marker time
  l_end_marker_time := get_target_end_marker_time( l_target_guid );
  
  -- now factor in the start marker time and end marker time and recompute the
  -- l_start_date and l_end_date
  
  -- recompute the start date using the start_merker_time_out
  l_recomputed_start_date := GREATEST( l_start_date, l_start_marker_time );
  
  -- recompute the end date using the start_merker_time_out
  l_recomputed_end_date := LEAST( l_end_date, l_end_marker_time );
    
  -- if l_recomputed_start_date is after l_recomputed_end_date, make
  -- l_recomputed_start_date the same as l_recomputed_end_date
  IF( l_recomputed_start_date > l_recomputed_end_date )
  THEN
    l_recomputed_start_date := l_recomputed_end_date;
  END IF;
   
  -- get the avail summary data
  get_avail_summary(l_target_guid, 
                    target_type_in, 
                    l_recomputed_start_date,
                    l_recomputed_end_date,
                    avail_summary_cur_out); 


  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_availability_summary:Exit', MODULE_NAME);
  END IF;
END get_availability_summary;

--
-- get_avail_overview_details procedure
--
PROCEDURE get_avail_overview_details(
              target_name_in  IN  VARCHAR2,
              target_type_in  IN  VARCHAR2,
              start_date_in   IN  DATE,
              end_date_in     IN  DATE,
              num_of_days_in  IN  NUMBER,
              view_type_in    IN  NUMBER,
              has_agent_view_priv_out OUT NUMBER,
              avail_pct_out OUT NUMBER,
              avail_formula_out OUT NUMBER,
              current_status_out OUT NUMBER,
              current_status_since_out OUT DATE,
              start_time_out OUT DATE,
              end_time_out OUT DATE,
              start_marker_time_out OUT DATE,
              end_marker_time_out OUT DATE,
              avail_summary_cur_out OUT CURSORTYPE,
              avail_states_cur_out OUT CURSORTYPE,
              avail_met_sev_cur_out OUT CURSORTYPE,
              outage_detail_cur_out OUT CURSORTYPE
              )
IS
  l_target_guid mgmt_targets.target_guid%TYPE;
  l_emd_target_guid mgmt_targets.target_guid%TYPE;
  l_metric_guid mgmt_metrics.metric_guid%TYPE;
  l_emd_metric_guid mgmt_metrics.metric_guid%TYPE;
  l_target_sysdate DATE;
  l_start_date DATE;
  l_end_date DATE;
  l_first_point_timestamp DATE;
  l_recomputed_start_date DATE;
  l_recomputed_end_date DATE;
  l_num_of_days NUMBER;
  l_current_user VARCHAR2(256);

BEGIN
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_avail_overview_details:Entry target_name_in = ' || target_name_in ||
                   ' target_type_in = ' || target_type_in  ||
                   ' start_date_in = '  || start_date_in   ||
                   ' end_date_in = '    || end_date_in     ||
                   ' num_of_days_in = ' || num_of_days_in  ||
                   ' view_type_in = '   || view_type_in, MODULE_NAME);
  END IF;

  -- Store the target guid to improve performance.
  l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in );
  
  -- get the metric guid for Response/Status metric for type - target_type_in
  l_metric_guid := mgmt_target.get_metric_guid( target_type_in,
                                                MGMT_GLOBAL.G_AVAIL_METRIC_NAME,
                                                MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN );
                                                 
  -- Get the target's sysdate using targets time zone.
  l_target_sysdate := mgmt_target.sysdate_target(l_target_guid);

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

  -- the start time and the end time for the timeline window needs to be 
  -- returned in the timezone of the target in question
  -- if start date and end date were passed in from the ui, then this will 
  -- be used for the start time and end time to be returned. otherwise, the 
  -- start date and end date computed above will be used for the start time 
  -- and end time to be returned
  
  -- determine the start time to be returned
  start_time_out := l_start_date;
  
  -- determine the end time to be returned
  end_time_out := l_end_date;
  
  -- determine the start marker time
  start_marker_time_out := get_target_start_marker_time( l_target_guid );
    
  -- determine the end marker time
  end_marker_time_out := get_target_end_marker_time( l_target_guid );
  
  -- now factor in the start marker time and end marker time and recompute the
  -- l_start_date and l_end_date
  
  -- recompute the start date using the start_merker_time_out
  l_recomputed_start_date := GREATEST( l_start_date, start_marker_time_out );
  
  -- recompute the end date using the start_merker_time_out
  l_recomputed_end_date := GREATEST( l_end_date, end_marker_time_out );

  -- if l_recomputed_start_date is after l_recomputed_end_date, make
  -- l_recomputed_start_date the same as l_recomputed_end_date
  IF( l_recomputed_start_date > l_recomputed_end_date )
  THEN
    l_recomputed_start_date := l_recomputed_end_date;
  END IF;
        
  -- get the avail pct of the target using the computed l_num_of_days above
  avail_pct_out := mgmt_target.get_avail_pct_value( l_target_guid, l_num_of_days );

  -- find out which availability formula to use by looking for the
  -- avail_formula parameter
  BEGIN
    SELECT parameter_value into avail_formula_out
      FROM MGMT_PARAMETERS p
     WHERE p.parameter_name = 'avail_formula';

  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      avail_formula_out := 0;
  END;

  -- get the current status of the target
  current_status_out := mgmt_target.get_avail_current_status( l_target_guid );
  
  -- get the current status since timestamp
  current_status_since_out := mgmt_target.get_current_status_timestamp(l_target_guid);
  
  -- get the avail summary data
  get_avail_summary(l_target_guid, 
                    target_type_in, 
                    l_recomputed_start_date,
                    l_recomputed_end_date,
                    avail_summary_cur_out); 

  -- get the avail states data needed for the avail time line 
  OPEN avail_states_cur_out FOR
    SELECT avail.target_guid, 
           avail.current_status,
           (LEAST(NVL(avail.end_collection_timestamp, l_recomputed_end_date),l_recomputed_end_date) -
            GREATEST(avail.start_collection_timestamp, l_recomputed_start_date))*24*60*60 as duration,
           GREATEST(avail.start_collection_timestamp, l_recomputed_start_date) as start_timestamp,
           NVL(avail.end_collection_timestamp, l_recomputed_end_date) as end_timestamp
      FROM mgmt_availability avail
     WHERE avail.target_guid = l_target_guid
       AND NVL(avail.end_collection_timestamp, l_recomputed_end_date) > l_recomputed_start_date
       AND avail.start_collection_timestamp <= l_recomputed_end_date
     ORDER BY start_timestamp;
       
  -- get the avail metric severity state data          
  -- first find the extra point timestamp
  BEGIN
    SELECT MAX(collection_timestamp) INTO l_first_point_timestamp
      FROM mgmt_severity
     WHERE target_guid = l_target_guid
       AND metric_guid = l_metric_guid
       AND collection_timestamp <= l_start_date
       AND severity_code != MGMT_GLOBAL.G_SEVERITY_COMMENT;

    IF(l_first_point_timestamp IS NULL)
    THEN
      l_first_point_timestamp := l_start_date;
    END IF;

    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        l_first_point_timestamp := l_start_date;
  END;

  -- now get the metric sev state data only if view_type = AVAIL_STATUS_VIEW_TYPE
  -- if target_type_in is not of agent type, then the severity state data should
  -- include that of this target and of the agent target too
  -- if target_type_in is of agent type, then the severity state data should
  -- be of this target only
  IF( view_type_in = AVAIL_STATUS_VIEW_TYPE )
  THEN
    IF( target_type_in = MGMT_GLOBAL.G_AGENT_TARGET_TYPE )
    THEN
      OPEN avail_met_sev_cur_out FOR
        SELECT sev.severity_guid, sev.severity_code, 
               sev.collection_timestamp AS collection_timestamp_date,
               '<SYSTEM>' AS user_name, sev.message, sev.message_nlsid, sev.message_params,
               ( (SELECT COUNT(*)
                    FROM mgmt_severity_annotation
                   WHERE severity_guid = sev.severity_guid) + 
                 (SELECT COUNT(*)
                    FROM mgmt_notification_log
                   WHERE source_obj_type = MGMT_GLOBAL.G_ANNOTATION_SOURCE_SEVERITY
                     AND source_obj_guid = sev.severity_guid) ) AS annotation_count,
               tgt.target_name, tgt.target_type
          FROM mgmt_severity sev, mgmt_targets tgt
         WHERE sev.target_guid = l_target_guid
           AND sev.metric_guid = l_metric_guid
           AND sev.collection_timestamp >= l_first_point_timestamp
           AND sev.severity_code != MGMT_GLOBAL.G_SEVERITY_COMMENT
           AND sev.target_guid = tgt.target_guid
         ORDER BY sev.collection_timestamp desc;
    ELSE
      -- in this case, first get the target_guid of the corresponding emd target

      -- enter super user mode just in case user doesn't have the priviledge to
      -- access agent. We always need to get agent severity info with the target
      -- severity info

      l_current_user := MGMT_USER.get_current_em_user();
      SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, 
                       MGMT_USER.OP_SET_IDENTIFIER);

      l_emd_target_guid := get_emd_target( l_target_guid );
      
      -- get the metric guid for Response/Status metric for type - oracle_emd
      l_emd_metric_guid := mgmt_target.get_metric_guid( MGMT_GLOBAL.G_AGENT_TARGET_TYPE,
                                                        MGMT_GLOBAL.G_AVAIL_METRIC_NAME,
                                                        MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN );

      -- now get the severity state data for this target and for the emd
      -- target      
      -- since this can result in duplicate records, therefore, do a unique
      -- to remove these duplicates
      OPEN avail_met_sev_cur_out FOR
        SELECT DISTINCT sev.severity_guid, sev.severity_code, 
               sev.collection_timestamp AS collection_timestamp_date,
               '<SYSTEM>' AS user_name, sev.message, sev.message_nlsid, sev.message_params,
               ( (SELECT COUNT(*)
                    FROM mgmt_severity_annotation
                   WHERE severity_guid = sev.severity_guid) + 
                 (SELECT COUNT(*)
                    FROM mgmt_notification_log
                   WHERE source_obj_type = MGMT_GLOBAL.G_ANNOTATION_SOURCE_SEVERITY
                     AND source_obj_guid = sev.severity_guid) ) AS annotation_count,
               tgt.target_name, tgt.target_type
          FROM mgmt_severity sev, mgmt_targets tgt
         WHERE sev.target_guid IN (l_target_guid, l_emd_target_guid)
           AND sev.metric_guid IN (l_metric_guid, l_emd_metric_guid)
           AND sev.collection_timestamp >= l_first_point_timestamp
           AND sev.severity_code != MGMT_GLOBAL.G_SEVERITY_COMMENT
           AND sev.target_guid = tgt.target_guid
           AND NOT (sev.target_guid = l_target_guid AND
                    (sev.severity_code = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_START OR 
                     sev.severity_code = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_CLEAR) )
         ORDER BY sev.collection_timestamp desc;

      --- get out of superuser mode
      SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);

      -- check if the user has VIEW_TARGET privilege on the agent
      has_agent_view_priv_out := mgmt_user.has_priv(
        l_current_user, mgmt_user.VIEW_TARGET, l_emd_target_guid);
    END IF;       
  ELSIF( view_type_in = AVAIL_STATUS_WITH_RBK ) THEN
  
    -- now get the metric sev state data only if view_type = AVAIL_STATUS_WITH_RBK
    -- This will be a union of conventional severity plus rbk severity
    -- if target_type_in is not of agent type, then the severity state data should
    -- include that of this target and of the agent target too
    -- if target_type_in is of agent type, then the severity state data should
    -- be of this target only
    -- Changes done to show the severity collection in the given range.Not using l_first_point_timestamp     
    IF( target_type_in = MGMT_GLOBAL.G_AGENT_TARGET_TYPE )
    THEN
      OPEN avail_met_sev_cur_out FOR
       SELECT severity_guid,severity_code,collection_timestamp_date,user_name,
        message,message_nlsid,message_params,annotation_count,target_name,
        target_type,is_rbk from
      (SELECT sev.severity_guid, sev.severity_code, 
               sev.collection_timestamp AS collection_timestamp_date,
               '<SYSTEM>' AS user_name, sev.message, sev.message_nlsid, sev.message_params,
               (0) AS annotation_count,
               tgt.target_name, tgt.target_type, 'Y' AS is_rbk
          FROM mgmt_severity_rbk sev, mgmt_targets tgt
         WHERE sev.target_guid = l_target_guid
           AND sev.metric_guid = l_metric_guid
           AND sev.collection_timestamp >= l_start_date
           AND sev.collection_timestamp <= l_end_date
           AND sev.severity_code != MGMT_GLOBAL.G_SEVERITY_COMMENT
           AND sev.target_guid = tgt.target_guid
        
        UNION
        
        SELECT sev.severity_guid, sev.severity_code, 
               sev.collection_timestamp AS collection_timestamp_date,
               '<SYSTEM>' AS user_name, sev.message, sev.message_nlsid, sev.message_params,
               ( (SELECT COUNT(*)
                    FROM mgmt_severity_annotation
                   WHERE severity_guid = sev.severity_guid) + 
                 (SELECT COUNT(*)
                    FROM mgmt_notification_log
                   WHERE source_obj_type = MGMT_GLOBAL.G_ANNOTATION_SOURCE_SEVERITY
                     AND source_obj_guid = sev.severity_guid) ) AS annotation_count,
               tgt.target_name, tgt.target_type, 'N' AS is_rbk
          FROM mgmt_severity sev, mgmt_targets tgt
         WHERE sev.target_guid = l_target_guid
           AND sev.metric_guid = l_metric_guid
           AND sev.collection_timestamp >= l_start_date
           AND sev.collection_timestamp <= l_end_date
           AND sev.severity_code != MGMT_GLOBAL.G_SEVERITY_COMMENT
           AND sev.target_guid = tgt.target_guid)
         ORDER BY collection_timestamp_date desc,
                  DECODE(severity_code,
                        MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START, 3,
                        MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END,   2,                      
                                                             1);
  ELSE
      -- in this case, first get the target_guid of the corresponding emd target
      -- enter super user mode just in case user doesn't have the priviledge to
      -- access agent. We always need to get agent severity info with the target
      -- severity info

      l_current_user := MGMT_USER.get_current_em_user();
      SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, 
                       MGMT_USER.OP_SET_IDENTIFIER);

      l_emd_target_guid := get_emd_target( l_target_guid );
      
      -- get the metric guid for Response/Status metric for type - oracle_emd
      l_emd_metric_guid := mgmt_target.get_metric_guid( MGMT_GLOBAL.G_AGENT_TARGET_TYPE,
                                                        MGMT_GLOBAL.G_AVAIL_METRIC_NAME,
                                                        MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN );

      -- now get the severity state data for this target and for the emd
      -- target      
      -- since this can result in duplicate records, therefore, do a unique
      -- to remove these duplicates
      OPEN avail_met_sev_cur_out FOR
        SELECT severity_guid,severity_code,collection_timestamp_date,user_name,
        message,message_nlsid,message_params,annotation_count,target_name,
        target_type,is_rbk from 
       (SELECT DISTINCT sev.severity_guid, sev.severity_code, 
               sev.collection_timestamp AS collection_timestamp_date,
               '<SYSTEM>' AS user_name, sev.message, sev.message_nlsid, sev.message_params,
               (0) AS annotation_count,
               tgt.target_name, tgt.target_type,'Y' AS is_rbk
          FROM mgmt_severity_rbk sev, mgmt_targets tgt
         WHERE sev.target_guid = l_target_guid
           AND sev.metric_guid = l_metric_guid
           AND sev.collection_timestamp >= l_start_date
           AND sev.collection_timestamp <= l_end_date
           AND sev.severity_code != MGMT_GLOBAL.G_SEVERITY_COMMENT
           AND sev.target_guid = tgt.target_guid
           AND NOT (sev.target_guid = l_target_guid AND
                    (sev.severity_code = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_START OR 
                     sev.severity_code = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_CLEAR) )
        UNION            
        SELECT DISTINCT sev.severity_guid, sev.severity_code, 
               sev.collection_timestamp AS collection_timestamp_date,
               '<SYSTEM>' AS user_name, sev.message, sev.message_nlsid, sev.message_params,
               ( (SELECT COUNT(*)
                    FROM mgmt_severity_annotation
                   WHERE severity_guid = sev.severity_guid) + 
                 (SELECT COUNT(*)
                    FROM mgmt_notification_log
                   WHERE source_obj_type = MGMT_GLOBAL.G_ANNOTATION_SOURCE_SEVERITY
                     AND source_obj_guid = sev.severity_guid) ) AS annotation_count,
               tgt.target_name, tgt.target_type, 'N' AS is_rbk
          FROM mgmt_severity sev, mgmt_targets tgt
         WHERE sev.target_guid IN (l_target_guid, l_emd_target_guid)
           AND sev.metric_guid IN (l_metric_guid, l_emd_metric_guid)
           AND sev.collection_timestamp >= l_start_date
           AND sev.collection_timestamp <= l_end_date
           AND sev.severity_code != MGMT_GLOBAL.G_SEVERITY_COMMENT
           AND sev.target_guid = tgt.target_guid
           AND NOT (sev.target_guid = l_target_guid AND
                    (sev.severity_code = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_START OR 
                     sev.severity_code = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_CLEAR)))
                     
         ORDER BY collection_timestamp_date desc, 
                DECODE(severity_code,
                      MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START, 3,
                      MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END,   2,                      
                                                             1);

      --- get out of superuser mode
      SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);

      -- check if the user has VIEW_TARGET privilege on the agent
      has_agent_view_priv_out := mgmt_user.has_priv(
        l_current_user, mgmt_user.VIEW_TARGET, l_emd_target_guid);
    END IF;
    
  ELSE
    -- open a dummy empty cursor
    OPEN avail_met_sev_cur_out FOR
        SELECT * 
          FROM dual
		 WHERE rownum < 1;
  END IF;

  -- get the outage details data only if view_type = OUTAGE_DETAILS_VIEW_TYPE    
  -- for the bound l_first_point_timestamp to l_recomputed_end_date
  IF( view_type_in = OUTAGE_DETAILS_VIEW_TYPE )
  THEN
    OPEN outage_detail_cur_out FOR
      SELECT avail.target_guid, 
             avail.current_status outage_type,
             DECODE(avail.end_collection_timestamp, NULL, 'TRUE', 'FALSE') is_open,
             (LEAST(NVL(avail.end_collection_timestamp, l_recomputed_end_date),l_recomputed_end_date) -
              avail.start_collection_timestamp)*24*60 as duration,
             avail.start_collection_timestamp as start_timestamp_date,
             NVL(avail.end_collection_timestamp, l_recomputed_end_date) as end_timestamp_date
        FROM mgmt_availability avail
       WHERE avail.target_guid = l_target_guid
         AND ( (avail.current_status = MGMT_GLOBAL.G_STATUS_DOWN) OR 
               (avail.current_status = MGMT_GLOBAL.G_STATUS_ERROR) OR
               (avail.current_status = MGMT_GLOBAL.G_STATUS_AGENT_DOWN) OR
               (avail.current_status = MGMT_GLOBAL.G_STATUS_UNREACHABLE) OR
--               (avail.current_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) OR
               (avail.current_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) )
         AND NVL(avail.end_collection_timestamp, l_recomputed_end_date) > l_recomputed_start_date
         AND avail.start_collection_timestamp <= l_recomputed_end_date
       ORDER BY start_timestamp_date desc;
  ELSE
    -- open a dummy empty cursor
    OPEN outage_detail_cur_out FOR
        SELECT * 
          FROM dual
		 WHERE rownum < 1;
  END IF;

  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('get_avail_overview_details:Exit' || 
                   ' has_agent_view_priv_out = '  || has_agent_view_priv_out  ||
                   ' avail_pct_out = '            || avail_pct_out            ||
                   ' avail_formula_out = '        || avail_formula_out        ||
                   ' current_status_out = '       || current_status_out       ||
                   ' current_status_since_out = ' || current_status_since_out ||       
                   ' start_time_out = '           || start_time_out           ||     
                   ' end_time_out = '             || end_time_out             ||
                   ' start_marker_time_out = '    || start_marker_time_out    ||
                   ' end_marker_time_out = '      || end_marker_time_out, MODULE_NAME);
  END IF;
EXCEPTION WHEN OTHERS
THEN
  -- Make sure to reset the context in the case of any errors
  SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
  RAISE;
  
END get_avail_overview_details;


--
-- Note: Should not use this to add annotations any more. Will be removed.
--       Call emd_schema.add_severity_annotation. 
--
PROCEDURE add_avail_comment(target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            annotation_in IN VARCHAR2) IS
  l_target_guid mgmt_targets.target_guid%TYPE;
  l_metric_guid mgmt_metrics.metric_guid%TYPE;
  l_user_name VARCHAR2(64);
  l_key_value VARCHAR2(256) := ' ';

BEGIN
  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('add_avail_comment:Entry target_name_in = ' || target_name_in ||
                   ' target_type_in = ' || target_type_in  ||
                   ' annotation_in = '  || annotation_in, MODULE_NAME);
  END IF;

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

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

  -- get the metric guid
  l_metric_guid := mgmt_target.get_metric_guid( target_type_in,
                                                MGMT_GLOBAL.G_AVAIL_METRIC_NAME,
                                                MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN );

  -- get the avail severity state data
  INSERT INTO mgmt_severity
    (target_guid, 
     metric_guid, 
     key_value, 
     collection_timestamp, 
     load_timestamp,
     severity_code, 
     severity_type, 
     severity_guid, 
     annotated_flag, 
     notification_status, 
     message, 
     user_name)
  VALUES
    (l_target_guid, 
     l_metric_guid, 
     l_key_value, 
     SYSDATE, 
     NULL,
     MGMT_GLOBAL.G_SEVERITY_COMMENT, 
     NULL, 
     NULL, 
     NULL, 
     NULL, 
     annotation_in, 
     l_user_name);

  IF (EMDW_LOG.p_is_info_set)THEN
     EMDW_LOG.info('add_avail_comment:Exit', MODULE_NAME);
  END IF;
END add_avail_comment;

END mgmt_avail;
/
show errors;