Rem
Rem $Header: group_mntr_pkgbody.sql 27-jun-2005.01:43:46 pratagar Exp $
Rem
Rem group_mntr_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      group_mntr_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    pratagar    06/27/05 - Type Display Name 
Rem    rpatti      03/15/05 - remove references to comp_target_def table 
Rem    nigupta     01/19/05 - cleanup topology tables on target deletion 
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    ckumar      10/14/04 - for fixing bug # 3931745 
Rem    asawant     09/14/04 - Modifying MGMT_USER_TYPE_METRIC_PREFS 
Rem    rpinnama    10/05/04 - Reflect modifications done to 
Rem                           mgmt_metric_column_obj.new func 
Rem    vagarwal    09/29/04 - 
Rem    lyang     09/28/04 - 
Rem    ckumar	   08/24/04 - review comments incorporated
Rem    vagarwal    09/23/04 - 
Rem    nigupta     09/20/04 - move expand_node, get_members_and_associations 
Rem                           and get_target_list_details to groups_ui_pkgbody 
Rem    nigupta     09/09/04 - add expand_node 
Rem    nigupta     09/01/04 - add procedures get_members_and_associations and 
Rem                           get_target_list_details 
Rem    ramalhot    08/24/04 - cutover to new assoc tables 
Rem    dcawley     07/23/04 - Remove manage target group 
Rem    joguo       11/14/03 - cleanup 
Rem    ancheng     11/07/03 - change get_summary_ui_details
Rem    joguo       11/04/03 - create new group pages
Rem    jpyang      09/23/03 - fix summary metrics query 
Rem    rpatti      04/03/03 - fix perf & tz support
Rem    rpatti      04/09/03 - add callback for member target delete
Rem    jpyang      03/31/03 - nls support
Rem    ancheng     02/21/03 - targets and metrics version support
Rem    rpatti      12/18/02 - fix perf
Rem    rpatti      10/31/02 - change get_summary_ui_details
Rem    tzimmerm    10/23/02 - Bug 2620650 - Replace tz delta w/ tz region
Rem    tzimmerm    10/17/02 - Bug 2622266 - Fixing composite tz normalization
Rem    tzimmerm    10/09/02 - Remove dbms_output calls
Rem    tzimmerm    10/08/02 - Align member dates w composite tz in GetAvailability
Rem    tzimmerm    07/31/02 - Fixing website composite avail & state prescedence
Rem    skini       07/12/02 - Change in target_name column size
Rem    rpatti      06/27/02 - handle duplicate group names
Rem    tzimmerm    06/17/02 - Handle new availability status codes for composites
Rem    rpatti      06/12/02 - pass current status as a number
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Restructured.
Rem    tzimmerm    05/09/02 - Fixing website composites in UNION w/ GetAvailability fcn
Rem    edemembe    05/06/02 - Moving comp target package spec
Rem    tzimmerm    05/01/02 - Alter get_cmpt_target_availability to use new composite avail code
Rem    edemembe    04/29/02 - Bug 2278619
Rem    rpatti      04/26/02 - users with full privilege can delete group
Rem    rpatti      04/26/02 - can delete grp even if it is member of a grp
Rem    rpatti      04/26/02 - users with full privilege can delete group
Rem    rpatti      04/25/02 - fix compare metrics
Rem    rpatti      04/12/02 - move recomputing grp availability to api
Rem    tzimmerm    04/10/02 - Adding start & end interval dates to avail_cur_out cursor
Rem    rpatti      04/11/02 - fix from review
Rem    rpatti      04/03/02 - use add_group api
Rem    rpatti      04/05/02 - cleanup data for unwanted summary metrics
Rem    tjaiswal    04/01/02 - Misc group fixes
Rem    dcawley     03/14/02 - New Collection APIs..
Rem    rpatti      02/25/02 - use function to get current user
Rem    rpatti      02/19/02 - fix metrics returned in compare metrics page
Rem    tjaiswal    02/12/02 - Bug 2221227
Rem    tjaiswal    02/08/02 - Fix adding summary met to collections in update_group
Rem    rpatti      01/24/02 - fix build
Rem    rpatti      01/23/02 - fix privilege check
Rem    rpatti      01/23/02 - check premissions for edit or delete
Rem    aholser     01/20/02 - insert IS_GROUP in mgmt_targets on creation
Rem    rpatti      01/17/02 - remove group availability/performance
Rem    rpatti      01/10/02 - support folders for composite page
Rem    rpatti      01/09/02 - make sure username is uppercase
Rem    rpatti      01/08/02 - support custom summary metric columns
Rem    edemembe    01/08/02 - Metrics are now target independent
Rem    rpatti      12/28/01 - use group label
Rem    edemembe    12/27/01 - Removing target name/type and metric name/column references
Rem    rpatti      12/03/01 - support typed groups
Rem    rpatti      11/18/01 - add avail pcts in the query
Rem    rpatti      11/08/01 - redo sql for better perf
Rem    rpatti      11/02/01 - limit availability records to 25
Rem    rpatti      11/01/01 - updates
Rem    rpatti      10/31/01 - perf changes
Rem    tjaiswal    10/29/01 - Remove usage of emtarget_ui_status
Rem    tjaiswal    10/26/01 - Fix critical, warning count
Rem    rpatti      10/25/01 -
Rem    rpinnama    10/15/01 - Rollback TIMESTAMP WITH TIMEZONE changes
Rem    rpatti      10/09/01 - revamp
Rem    rpatti      10/03/01 - improve perf of get timeseries
Rem    rpatti      10/02/01 - insert thresholds correctly
Rem    rpatti      09/26/01 - schema normalization changes
Rem    rpatti      09/20/01 - use new metric label column
Rem    rpatti      09/17/01 - cleanup sql
Rem    rpinnama    09/13/01 - Insert collection_timestamp and collection_ts_utc
Rem    rpatti      08/24/01 - tune sql
Rem    rpatti      08/07/01 - update FOR new UI options
Rem    rpatti      07/20/01 - update FOR repository changes
Rem    skini       07/21/01 - Merge in rpatti transaction
Rem    rpatti      07/10/01 - update FOR schema revamp
Rem    rpatti      06/25/01 - comp target mgmt related pkg
Rem    rpatti      06/25/01 - Created
Rem

CREATE OR REPLACE PACKAGE BODY emd_mntr_comptgt AS

  COMPOSITE_TYPE CONSTANT VARCHAR(10) := 'composite';
  REDUNDANCY_GROUP CONSTANT VARCHAR(30) := 'redundancy_group';

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

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

  STATS_RELATED CONSTANT NUMBER(1) := 0;

  G_SEVERITY_CLEAR CONSTANT INTEGER := 15;

-------------------------------------------------------------------------------
--  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;

FUNCTION GetAvailability(compositeGUID RAW, numDays NUMBER DEFAULT 1)
    RETURN MgmtAvailTableType IS
  -- This cursor will retrieve all availability records for composite members
  CURSOR availability_cursor(v_compTargetGUID RAW,
                             v_cutoff DATE,
                             v_compTZRegion VARCHAR2) IS
    SELECT a.target_guid, a.severity_guid,
           a.current_status,
           mgmt_global.adjust_tz(a.start_collection_timestamp, t.timezone_region, v_compTZRegion),
           mgmt_global.adjust_tz(a.end_collection_timestamp, t.timezone_region, v_compTZRegion)
    FROM
       mgmt_availability a,
      (SELECT mt.target_guid, mt.timezone_region
        FROM MGMT_TARGETS mt, MGMT_TARGET_ASSOCS tm
        WHERE tm.source_target_guid = v_compTargetGUID
          AND tm.assoc_target_guid = mt.target_guid
          AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
          AND EXISTS (SELECT 1 FROM MGMT_METRICS m
              WHERE m.target_type = mt.target_type
              AND m.metric_name = 'Response'
              AND m.metric_column = 'Status'
              AND m.type_meta_ver = mt.type_meta_ver
              AND (m.category_prop_1 = mt.category_prop_1 OR m.category_prop_1 = ' ')
              AND (m.category_prop_2 = mt.category_prop_2 OR m.category_prop_2 = ' ')
              AND (m.category_prop_3 = mt.category_prop_3 OR m.category_prop_3 = ' ')
              AND (m.category_prop_4 = mt.category_prop_4 OR m.category_prop_4 = ' ')
              AND (m.category_prop_5 = mt.category_prop_5 OR m.category_prop_5 = ' ') )) t
    WHERE
       a.target_guid = t.target_guid AND
       (mgmt_global.adjust_tz(a.end_collection_timestamp, t.timezone_region, v_compTZRegion) >= v_cutoff OR
        a.end_collection_timestamp is null)
    ORDER BY 4 asc;

  -- This cursor will retrieve all the non-composite member targets
  CURSOR member_cursor(v_compTargetGUID RAW) IS
    SELECT tm.assoc_target_guid, 1
      FROM MGMT_TARGETS t, MGMT_TARGET_ASSOCS tm
      WHERE tm.source_target_guid = v_compTargetGUID
        AND tm.assoc_target_guid = t.target_guid
        AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
        AND EXISTS (SELECT 1 FROM MGMT_METRICS m
              WHERE m.target_type = t.target_type
              AND m.metric_name = 'Response'
              AND m.metric_column = 'Status'
              AND m.type_meta_ver = t.type_meta_ver
              AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
              AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
              AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
              AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
              AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ') );

  compositeStatus         NUMBER := -999;
  compositeTimezoneRegion VARCHAR2(64);
  recordCount             NUMBER := 0;
  currentState            NUMBER := 1;
  cutoffDate              DATE := TGT_SYSDATE_WGUID(compositeGUID) - numDays;
  memberStatusList        MemberListType;
  inRec                   MgmtAvailRowType :=
                                 MgmtAvailRowType(NULL,NULL,NULL,NULL,NULL);
  openRec                 MgmtAvailRowType :=
                                 MgmtAvailRowType(NULL,NULL,NULL,NULL,NULL);
  outRec                  MgmtAvailRowType :=
                                 MgmtAvailRowType(NULL,NULL,NULL,NULL,NULL);
  availRecords            MgmtAvailTableType := MgmtAvailTableType();

BEGIN

  availRecords.EXTEND;
  --
  -- Get the composite target timezone.  Times for all members will be
  -- normalized to the timezone of the composite.
  --
  SELECT timezone_region INTO compositeTimezoneRegion
  FROM MGMT_TARGETS
  WHERE target_guid = compositeGUID;

  --
  -- Collect the members of the composite target and default their status to up.
  --
  OPEN member_cursor(compositeGUID);
  FETCH member_cursor BULK COLLECT INTO memberStatusList.targetGUID,
                                        memberStatusList.currentStatus;
  CLOSE member_cursor;
  --
  -- Exit if no non-composite member targets are found.
  --
  IF NOT memberStatusList.targetGUID.EXISTS(1) THEN
    RETURN null;
  END IF;

  --
  OPEN availability_cursor(compositeGUID, cutoffDate, compositeTimezoneRegion);
  --
  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 := compositeGUID;
          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 > cutoffDate)) 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.current_status := currentState;
          openRec.target_guid := compositeGUID;
          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 = 1) 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 := compositeGUID;
          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;
    IF recordCount = 1 THEN
      availRecords := MgmtAvailTableType(openRec);
    ELSE
      availRecords(recordCount) := openRec;
    END IF;
--    PIPE ROW(openRec);
  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;
  DBMS_OUTPUT.PUT_LINE('availRecords count: ' || availRecords.COUNT);
  --
  -- Set the lower date boundary to our cutoff if it is less than our
  -- cutoff.
  --
  IF (availRecords(1).start_collection_timestamp < cutoffDate) THEN
    availRecords(1).start_collection_timestamp := cutoffDate;
  END IF;
  --
  RETURN availRecords;
--  RETURN;
END GetAvailability;

PROCEDURE getDetailsForGroupMainPage(
                groupTargets OUT cursorType,
                groupTypes   OUT cursorType
)
IS
BEGIN

  OPEN groupTargets FOR
    SELECT target_name, target_type,
           type_display_name 
      FROM mgmt_targets
     WHERE target_type = COMPOSITE_TYPE OR target_type = REDUNDANCY_GROUP OR 
           target_type IN 
           (
             SELECT target_type 
               FROM mgmt_type_properties
              WHERE property_name = 'baseMemberType' AND
                    property_value IS NOT NULL
           )
     ORDER BY type_display_name, target_name;

  OPEN groupTypes FOR
    SELECT DISTINCT  
           t.target_type type_display_name, p.target_type, p.property_value
      FROM mgmt_type_properties p, mgmt_targets t
     WHERE p.property_name = 'baseMemberType' AND
           p.property_value IS NOT NULL AND
           p.property_value = t.target_type;

END;

-- PURPOSE:
--   Cleanup groups when a member target is deleted
--    if this member target is the last of this type
--    in a group and there are summary metrics defined 
--    for that type - we need to clean the summary
--    metric definitions.
--
--   IN Parameters:
--     target_name_in : target name of the deleted target
--     target_type_in : target type of the deleted target
--     target_guid_in : target guid of the deleted target
--
--
--   OUT Parameters:
--     NONE
PROCEDURE clean_group_on_mem_tgt_del
   (target_name_in IN VARCHAR2,
    target_type_in IN VARCHAR2,
    target_guid_in IN RAW)
IS
  l_sum_metric_defs MGMT_GUID_ARRAY;
  l_rem_sum_metric_defs MGMT_GUID_ARRAY;
  l_metric_guids MGMT_GUID_ARRAY;

BEGIN
  -- cleanup the topology tables
  em_topology.clean_topology_tables('grpsTopoPageType', target_guid_in);

END clean_group_on_mem_tgt_del;

PROCEDURE get_cmpt_target_availability(
               target_name_in IN  VARCHAR2,
               target_type_in IN  VARCHAR2,
               num_of_days_in IN  NUMBER,
               disp_type_in   IN  VARCHAR2,
               compare_type_in  IN  VARCHAR2,
               start_time_out OUT DATE,
               end_time_out   OUT DATE,
               avail_pcts_out  OUT cursorType,
               avail_cur_out  OUT cursorType
) IS

  l_target_guid mgmt_targets.target_guid%TYPE;
  l_target_name MGMT_TARGETS.target_name%TYPE;

  comp_guid mgmt_targets.target_guid%TYPE;
  comp_tz NUMBER;

  l_mem_targets   MGMT_GUID_ARRAY := MGMT_GUID_ARRAY();
  avail_array     COMP_DEF_AVAIL_REC_ARRAY := COMP_DEF_AVAIL_REC_ARRAY();
  tgt_list        cursorType;
  l_avg_avail     NUMBER;
  i               INTEGER;

BEGIN
  -- Get composite target guid
  comp_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);

  -- Get composite target timezone delta
  select timezone_delta into comp_tz from mgmt_targets where target_guid = comp_guid;

  --  Return only targets that have below average availability
  --
  IF compare_type_in = 'tgtsBelowAvg'
  THEN
    IF disp_type_in IS NOT NULL AND length(disp_type_in) <> 0
    THEN -- all targets of a type..
        SELECT COMP_DEF_AVAIL_REC(target_name, target_type, avail_pct)
         BULK COLLECT INTO avail_array
         FROM
              ( SELECT target_name, target_type,
                       mgmt_target.get_avail_pct_value(tgt.target_guid, num_of_days_in) AS avail_pct
                  FROM mgmt_targets tgt, mgmt_target_assocs members
                 WHERE members.source_target_guid = comp_guid AND
                       members.assoc_target_guid = tgt.target_guid AND
                       members.assoc_guid = MGMT_ASSOC.g_contains_guid AND
                       tgt.target_type = disp_type_in
                 );

    ELSE
        SELECT COMP_DEF_AVAIL_REC(target_name, target_type, avail_pct)
         BULK COLLECT INTO avail_array
         FROM
              ( SELECT target_name, target_type,
                       mgmt_target.get_avail_pct_value(tgt.target_guid, num_of_days_in) AS avail_pct
                  FROM mgmt_targets tgt, mgmt_target_assocs members
                 WHERE members.source_target_guid = comp_guid AND
                       members.assoc_guid = MGMT_ASSOC.g_contains_guid AND
                       members.assoc_target_guid = tgt.target_guid);

    END IF;

    SELECT ROUND(AVG(avail_pct), 2)
     INTO l_avg_avail
     FROM
          TABLE(CAST(avail_array AS COMP_DEF_AVAIL_REC_ARRAY));

    SELECT MGMT_GUID_OBJ(tgt.target_guid, tgt.target_name)
      BULK COLLECT INTO l_mem_targets
      FROM mgmt_targets tgt,
           TABLE(CAST(avail_array AS COMP_DEF_AVAIL_REC_ARRAY)) avail
     WHERE tgt.target_name = avail.target_name AND
           tgt.target_type = avail.target_type AND
           avail.avail_pct < l_avg_avail;

  --  Return the least available targets in a group
  --
  ELSIF compare_type_in = 'leastAvailable'
  THEN
    IF disp_type_in IS NOT NULL AND length(disp_type_in) <> 0
    THEN -- all targets of a type..
        SELECT COMP_DEF_AVAIL_REC(target_name, target_type, avail_pct)
         BULK COLLECT INTO avail_array
         FROM
              ( SELECT target_name, target_type,
                       mgmt_target.get_avail_pct_value(tgt.target_guid, num_of_days_in) AS avail_pct
                  FROM mgmt_targets tgt, mgmt_target_assocs members
                 WHERE members.source_target_guid = comp_guid AND
                       members.assoc_target_guid = tgt.target_guid AND
                       members.assoc_guid = MGMT_ASSOC.g_contains_guid AND
                       tgt.target_type = disp_type_in
                 );

    ELSE
        SELECT COMP_DEF_AVAIL_REC(target_name, target_type, avail_pct)
         BULK COLLECT INTO avail_array
         FROM
              ( SELECT target_name, target_type,
                       mgmt_target.get_avail_pct_value(tgt.target_guid, num_of_days_in) AS avail_pct
                  FROM mgmt_targets tgt, mgmt_target_assocs members
                 WHERE members.source_target_guid = comp_guid AND
                       members.assoc_guid = MGMT_ASSOC.g_contains_guid AND
                       members.assoc_target_guid = tgt.target_guid);

    END IF;

    SELECT MGMT_GUID_OBJ(tgt.target_guid, tgt.target_name)
      BULK COLLECT INTO l_mem_targets
      FROM mgmt_targets tgt,
           TABLE(CAST(avail_array AS COMP_DEF_AVAIL_REC_ARRAY)) avail
     WHERE tgt.target_name = avail.target_name AND
           tgt.target_type = avail.target_type 
     ORDER BY avail.avail_pct;

  ELSE 

    IF disp_type_in IS NOT NULL AND length(disp_type_in) <> 0
    THEN -- all targets of a type..
        SELECT MGMT_GUID_OBJ(tgt.target_guid, tgt.target_name)
          BULK COLLECT INTO l_mem_targets
          FROM mgmt_targets tgt, mgmt_target_assocs mem
         WHERE mem.source_target_guid = comp_guid AND
               mem.assoc_target_guid = tgt.target_guid AND
               mem.assoc_guid = MGMT_ASSOC.g_contains_guid AND
               tgt.target_type = disp_type_in;
    ELSE
        SELECT MGMT_GUID_OBJ(tgt.target_guid, tgt.target_name)
          BULK COLLECT INTO l_mem_targets
          FROM mgmt_targets tgt, mgmt_target_assocs mem
         WHERE mem.source_target_guid = comp_guid AND
               mem.assoc_guid = MGMT_ASSOC.g_contains_guid AND
               mem.assoc_target_guid = tgt.target_guid;
    END IF;

  END IF;

  
    -- Get the start and end timestamps relative to the composite's local time / timezone
    --   ie:  If Last 24 Hours is chosen the timeframe is the last 24 hours 
    --   for the composite.  The members availability intervals will be 
    --   skewed to the timezone of the composite.
    --
    end_time_out := TGT_SYSDATE_WGUID(comp_guid);
    start_time_out := end_time_out - num_of_days_in;

    OPEN avail_cur_out FOR
        SELECT tgt.target_name, tgt.target_type,
--               tgt.display_name, tgt.type_display_name,
               tgt.target_name, tgt.type_display_name,               
               tgt.target_guid, avail.current_status,
               (LEAST(NVL(avail.end_collection_timestamp + ((comp_tz - tgt.timezone_delta) / 1440), end_time_out),
                            end_time_out) -
                      GREATEST(avail.start_collection_timestamp + ((comp_tz - tgt.timezone_delta) / 1440), start_time_out))*24*60*60 as sec,
               GREATEST(avail.start_collection_timestamp + ((comp_tz - tgt.timezone_delta) / 1440), start_time_out) as start_timestamp,
               NVL(avail.end_collection_timestamp + ((comp_tz - tgt.timezone_delta) / 1440), end_time_out) as end_timestamp
          FROM mgmt_targets tgt, mgmt_availability avail,
               TABLE(CAST(l_mem_targets AS MGMT_GUID_ARRAY)) guids
         WHERE (tgt.target_guid = guids.guid OR
               (tgt.target_guid = comp_guid AND tgt.target_type = 'website'))
           AND tgt.target_guid = avail.target_guid
           AND NVL(avail.end_collection_timestamp + ((comp_tz - tgt.timezone_delta) / 1440), end_time_out) > start_time_out
           AND avail.start_collection_timestamp + ((comp_tz - tgt.timezone_delta) / 1440) < end_time_out
         GROUP BY tgt.target_type, tgt.target_name,
               avail.start_collection_timestamp,
               avail.end_collection_timestamp,
--               tgt.target_guid, tgt.type_display_name, tgt.display_name,
               tgt.target_guid, tgt.type_display_name, tgt.target_name,
               avail.current_status, tgt.timezone_delta
    UNION ALL
        SELECT
               compTgt.target_name, compTgt.target_type,
--               compTgt.display_name, compTgt.type_display_name,
               compTgt.target_name, compTgt.type_display_name,
               compTgt.target_guid, compositeAvail.current_status,
               (LEAST(NVL(compositeAvail.end_collection_timestamp, end_time_out),
               end_time_out) -
                 GREATEST(compositeAvail.start_collection_timestamp, start_time_out))*24*60*60  as sec,
               GREATEST(compositeAvail.start_collection_timestamp, start_time_out) as start_timestamp,
               NVL(compositeAvail.end_collection_timestamp, end_time_out) as end_timestamp
        FROM mgmt_targets compTgt,
             TABLE(CAST(emd_mntr_comptgt.GetAvailability(comp_guid, num_of_days_in) AS MgmtAvailTableType)) compositeAvail
         WHERE  compTgt.target_type != 'website'
           AND (compTgt.target_guid = comp_guid)
           AND  compTgt.target_guid = compositeAvail.target_guid
           AND  NVL(compositeAvail.end_collection_timestamp, end_time_out) > start_time_out
           AND  compositeAvail.start_collection_timestamp < end_time_out
      GROUP BY  compTgt.target_type, compTgt.target_name,
                compositeAvail.start_collection_timestamp,
                compositeAvail.end_collection_timestamp,
--                compTgt.target_guid, compTgt.type_display_name, compTgt.display_name,
                compTgt.target_guid, compTgt.type_display_name, compTgt.target_name,
                compositeAvail.current_status;


    OPEN avail_pcts_out FOR
        SELECT tgt.target_name, tgt.target_type,
               mgmt_target.get_avail_pct_value(tgt.target_guid, num_of_days_in) AS avail_pct
          FROM mgmt_targets tgt, 
               TABLE(CAST(l_mem_targets AS MGMT_GUID_ARRAY)) guids
         WHERE (tgt.target_guid = guids.guid 
           OR   tgt.target_guid = comp_guid);

  END get_cmpt_target_availability;
  
END emd_mntr_comptgt;
/

show errors;