Rem
Rem $Header: sdk_target_pkgbody.sql 07-may-2007.04:49:45 neearora Exp $
Rem
Rem sdk_target_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdk_target_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    neearora 04/19/07 - bug 5116622. Added API evaluate_target_status
Rem    neearora 05/07/07 - Backport neearora_bug-5116622 from main
Rem    jsadras  08/19/05 - Bug:4493312 change add_meta_ver callback to 
Rem                        metaver_catprop 
Rem    gan      08/15/05 - add opt parameter 
Rem    rkpandey 07/27/05 - Added get_agent_version 
Rem    gan      07/21/05 - remove check_composite call 
Rem    rkpandey 07/18/05 - Bug 4497257: Add remove_cluster_member 
Rem    pmodi    07/12/05 - Bug:4486910 - Remove NN check for type_meta_ver 
Rem    gan      07/07/05 - timezone adjustment for cluster target 
Rem    rpinnama 06/23/05 - 
Rem    pmodi    06/16/05 - Bug:4416497 - Change just single space to default value
Rem                        for type_meta_ver in register_target_type 
Rem    rpinnama 06/23/05 - Add target tzrgn update callback 
Rem    pmodi    05/03/05 - add_target can be used for composite and plane 
Rem                        aggregate target
Rem    rpinnama 05/08/05 - Bug 4302515: support change url req and targets 
Rem                        rowset in different files 
Rem    jsadras  04/21/05 - Bug:4300503, deadlock in register target type 
Rem    ramalhot 03/11/05 - moved add target code em_target pkg 
Rem    jsadras  03/10/05 - Bug:4186866, change to timezone_region from delta 
Rem    jsadras  03/14/05 - add target metaver callbacks 
Rem    ramalhot 03/03/05 - changed the code for MGMT_CHANGE_AGENT_URL 
Rem    rpinnama 02/25/05 - Modify the default for delete_target_type
Rem    dcawley  02/21/05 - Replace enter super user mode 
Rem    pmodi       02/22/05 - set default p_tz_rgn/p_timezone_rgn to NULL in add_target/add_group
Rem    dtsao       02/18/05 - add is_aggregate_target api 
Rem    gan         02/01/05 - add interval to repo coll 
Rem    ramalhot    01/18/05 - assoc_def_member --> assoc_def_contains 
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    rzazueta    01/12/05 - Remove call to 
Rem                           MGMT_BLACKOUT_ENGINE.handle_membership_change
Rem    streddy     01/05/05 - Comment out add_target property check for now
Rem    asawant     01/03/05 - Remove target type versions when type is 
Rem                           removed. 
Rem    vkgarg      01/04/05 - update get_groups_of_type_ct_priv due to change 
Rem                           in mgmt_user.get_targets 
Rem    rpinnama    01/03/05 - 
Rem    asawant     11/10/04 - Add version to register target type.
Rem    rpinnama    12/30/04 - Generate type guid 
Rem    streddy     12/29/04 - Call target addition callbacks for groups 
Rem    pmodi       12/28/04 - check required properties during target creation
Rem    gan         12/22/04 - check empty collection 
Rem    kmanicka    12/20/04 - bug 4065441 added USER_OBJECT_GROUP
Rem    ramalhot    12/07/04 - group/assoc perf bug fix 
Rem    rpinnama    11/18/04 - Bug 4016430 : Fix avail_pct_value 
Rem    rpinnama    11/29/04 - Add set_target_tz and set_agent_tz API to set 
Rem                           timezones 
Rem    streddy     11/08/04 - Added redundancy group APIs 
Rem    ramalhot    10/20/04 - modified call to assoc pkg 
Rem    rkpandey    11/16/04 - Removed p_memberinfo_out p_memberinfodel_out from 
Rem                           get_delete_target_info 
Rem    gan         10/05/04 - add timezone region to add_group 
Rem    groyal      10/05/04 - Fix bug in get_member_monitoring_summ 
Rem    groyal      10/04/04 - Update get_member_monitoring_summary to get 
Rem                           policy details 
Rem    kmanicka    09/20/04 - added user model callbacks
Rem    kmanicka    09/20/04 - added support for target owner
Rem    dcawley     09/15/04 - Remove call to user model 
Rem    ramalhot    08/25/04 - cutover to new assoc tables 
Rem    rzkrishn    08/19/04 - using space instead of NULL as default in target 
Rem                           relocation callbacks 
Rem    jsadras     08/18/04 - Bug_3492162, Call to Decrypt 
Rem    streddy     08/10/04 - Add metric dependency functions 
Rem    asawant     08/09/04 - Adding overloaded procedure get_target_info() 
Rem    dcawley     07/23/04 - Remove manage target group 
Rem    ramalhot    07/31/04 - assoc const name changed 
Rem    rzkrishn    07/30/04 - providing defaults for relocation callback 
Rem                           addition, deletion 
Rem    ramalhot    07/23/04 - code for target assocs added 
Rem    dcawley     07/07/04 - Increase user name size 
Rem    rzkrishn    07/07/04 - adding callbacks for target relocation 
Rem    asawant     06/24/04 - Adding UDTP 
Rem    rzazueta    04/05/04 - Throw target_does_not_exist exception in
Rem                           get_delete_target_info 
Rem    ancheng     02/09/04 - add another availability formula 
Rem    dcawley     01/26/04 - Rebuild visible target list 
Rem    snakai      12/11/03 - fix trimming of target_name during creation 
Rem    skini       12/09/03 - trim target name and type 
Rem    joguo       11/18/03 - fix group sql error
Rem    dcawley     11/16/03 - Fix index 
Rem    dcawley     11/13/03 - Change recreate context 
Rem    skini       11/14/03 - Fix invalid index 
Rem    groyal      11/06/03 - Fix policy violation ct for group 
Rem    joguo       11/05/03 - create new group pages
Rem    dcawley     10/29/03 - Remove check for website in modify_target 
Rem    dcawley     10/29/03 - Allow websites have targets with view 
Rem    gan         10/17/03 - fix index increment problem in add_target 
Rem    jpyang      10/13/03 - fix em_flat target issue in add_group
Rem    joguo       10/09/03 - fix of bug 3188820
Rem    streddy     10/02/03 - Do not add targets to groups for super_user 
Rem    shuberma    09/26/03 - Use group_flat membership 
Rem    skini       09/30/03 - Merge 
Rem    ancheng     09/26/03 - bug 3145487 
Rem    rpinnama    09/25/03 - Add APIs for add_target_type_property(s) 
Rem    rpinnama    09/25/03 - Fix 3143083: Support adding and modifying target 
Rem    skini       09/26/03 - 
Rem    skini       09/26/03 - Add target to all groups if user has 
Rem    streddy     09/22/03 - Added flat memberships table support 
Rem    skini       09/05/03 - 
Rem    skini       09/05/03 - 
Rem    skini       09/05/03 - Fix add_target check for privs 
Rem    skini       09/02/03 - Job system group callbacks 
Rem    groyal      08/20/03 - Fix policy support in 
Rem    sbadrina    08/14/03 - add policy violations 
Rem    skini       08/16/03 - Add APIs for creating associations 
Rem    streddy     07/22/03 - master agent APIs
Rem    skini       07/21/03 - Fix bug in query in move_target
Rem    rzazueta    06/25/03 - update standby agents in modify_target
Rem    streddy     06/09/03 - Master-agent support
Rem    streddy     03/03/03 - Add add_metric_severity* procs
Rem                           for composite target availability
Rem    jriel       02/10/03 - add host_name to member query
Rem    skini       05/12/03 - Handle deleted taregts in get_delete_target-info
Rem    rpinnama    05/13/03 - Support NULL for parameters to add_target API
Rem    skini       05/01/03 - Handle move to same emd url
Rem    rpinnama    12/20/02 - Remove unnecessary checks for target deletion
Rem    rpinnama    11/27/02 - 
Rem    rpinnama    11/22/02 - Support emd_url way of adding target
Rem    skini       11/26/02 - Change membership API
Rem    rpatti      11/19/02 - fix avail calculation
Rem    skini       11/01/02 - Update targets first, followed by properties
Rem    jpyang      10/23/02 - use internal name for display name
Rem    rpatti      10/30/02 - modify get_member_monitoring_summary
Rem    tzimmerm    10/24/02 - Bug 2641992 - Add agent downtime to avail calc for all target types
Rem    njuillar    10/17/02 - Fixed bug 2624812
Rem    skini       09/18/02 - Call blackout callback when group modified
Rem    rpatti      09/12/02 - support custom folders
Rem    ancheng     08/06/02 - modify get_targets_summary
Rem    tjaiswal    08/09/02 - Bug 2507607
Rem    tjaiswal    08/08/02 - Bug 2504831
Rem    tzimmerm    07/31/02 - Fix composite avail pct calculation
Rem    skini       07/25/02 - Do not return any members for groups
Rem    rpinnama    07/09/02 - Replace set_group..avail with procedure from em_severity package
Rem    tjaiswal    07/01/02 - Bug 2442570
Rem    skini       07/02/02 - Make delete_target asynchronous
Rem    tzimmerm    06/27/02 - 
Rem    tzimmerm    06/24/02 - Updating composites in get_avail_pct_value for GIT5
Rem    tjaiswal    06/27/02 - Fix avail pct value
Rem    tjaiswal    06/20/02 - Change avail pct calculation for non composites
Rem    jpyang      06/10/02 - add type_display_name to get_target_info
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Restructured.
Rem    ancheng     05/14/02 - fix changes
Rem    tzimmerm    05/09/02 - Performance work & bug fix to get_avail_pct_value
Rem    tzimmerm    05/01/02 - Updating get_avail_pct_value to use composite pseudo table
Rem    tzimmerm    04/30/02 - Updating get_avail_pct_value to reflect new availability calc.
Rem    rpatti      04/17/02 - allow nullarrays in modify_group
Rem    skini       04/16/02 - Fix bug 2315838: incorrect guid generated
Rem    rpatti      04/12/02 - remove blackout from avail pct calculation
Rem    rpatti      04/15/02 - fix groups folder query
Rem    skini       04/11/02 - Fix bug 2315828: use generated target guids in target APIs
Rem    skini       04/11/02 - Use generated target_guids
Rem    rpatti      04/10/02 - all targets page needs to know abt groups
Rem    rpatti      04/03/02 - change add group api to pickup type display names
Rem    tjaiswal    04/01/02 - Use mgmt current availability table
Rem    jpyang      03/13/02 - add display_name for add/modify targets 
Rem    ancheng     02/28/02 - use display_name for search
Rem    rpatti      02/25/02 - use function to get current user
Rem    skini       02/28/02 - Fix bug with null groups
Rem    skini       02/08/02 - Review comments
Rem    tjaiswal    02/04/02 - Use mgmt current availability
Rem    skini       02/04/02 - Enhance target APIs
Rem    skini       02/03/02 - Add create_target and  modify_target APIs
Rem    tjaiswal    01/22/02 - Fix all targets query
Rem    rpinnama    01/22/02 - Remove rownum=1 from the query.
Rem    rpatti      01/17/02 - change avail pct query to match availability packages
Rem    rpinnama    01/15/02 - Add unmonitored to the list of down time.
Rem    ancheng     01/16/02 - add procedures for folder
Rem    tjaiswal    01/04/02 - Change plsql for target subtabs
Rem    rpatti      12/26/01 - fix avail problem
Rem    ancheng     12/18/01 - change get_target_down_timestamp
Rem    tjaiswal    12/18/01 - Optimize all targets query
Rem    tjaiswal    12/17/01 - Add seperate api for all targets page
Rem    ancheng     12/11/01 - add get_target_down_timestamp
Rem    smishra     11/16/01 - Fix for 2087192.
Rem    rpatti      12/04/01 - spport availability of groups
Rem    tjaiswal    11/09/01 - Add get_traffic_light_info procedure
Rem    tjaiswal    11/06/01 - Add procedures for target list views
Rem    tjaiswal    10/31/01 - Optimize queries in get_target_monitoring_summary
Rem    tjaiswal    10/29/01 - Add get_member_monitoring_summary
Rem    tjaiswal    10/25/01 - Merged tjaiswal_remove_targetuistatus_usage
Rem    tjaiswal    10/22/01 - Created
Rem

CREATE OR REPLACE PACKAGE BODY mgmt_target AS
-- Forward declarations
-- END Forward declarations
  
  --
  -- PROCEDURE: register_target_type
  --
  -- PURPOSE: 
  --   Registers the given target type to the repository.
  --   If the target type is already registered, its properties are updated.
  PROCEDURE register_target_type
    (p_target_type_in           IN VARCHAR2,
     p_type_display_name_in     IN VARCHAR2 DEFAULT NULL,
     p_type_display_nlsid_in    IN VARCHAR2 DEFAULT NULL,
     p_type_property_list_in    IN SMP_EMD_NVPAIR_ARRAY DEFAULT NULL,
     p_type_resource_bundle_in  IN VARCHAR2 DEFAULT NULL,
     p_type_meta_ver_in         IN VARCHAR2 DEFAULT '1.0')
  IS
     l_type_display_name    MGMT_TARGET_TYPES.type_display_name%TYPE;
     l_type_display_nlsid   MGMT_TARGET_TYPES.type_display_nlsid%TYPE;
     l_type_resource_bundle MGMT_TARGET_TYPES.type_resource_bundle%TYPE;
     l_dflt_type_display_name    MGMT_TARGET_TYPES.type_display_name%TYPE;
     l_dflt_type_display_nlsid   MGMT_TARGET_TYPES.type_display_nlsid%TYPE;
     l_dflt_type_resource_bundle MGMT_TARGET_TYPES.type_resource_bundle%TYPE;
     l_curr_max_type_meta_ver MGMT_TARGET_TYPES.max_type_meta_ver%TYPE;
     l_target_type_guid     MGMT_TARGET_TYPES.target_type_guid%TYPE;
     l_type_ver_guid        MGMT_TARGET_TYPE_VERSIONS.target_type_ver_guid%TYPE;
     l_new_tgttype          INTEGER := 0;
     l_dot_idx              NUMBER := 0;
     l_comp_val             NUMBER := 0;
     l_proc_name             CONSTANT VARCHAR2(30) := 'register_target_type' ;
     l_lock_handle mgmt_lock_util.lock_handle_type ;
     l_type_meta_ver    MGMT_TARGET_TYPE_VERSIONS.TYPE_META_VER%TYPE;
  BEGIN

    -- Perform not null checks..
    EM_CHECK.check_not_null(p_target_type_in, 'p_target_type_in');

    IF trim(p_type_meta_ver_in) IS NULL THEN
      l_type_meta_ver := '1.0' ;
    ELSE
      l_type_meta_ver := p_type_meta_ver_in ;
    END IF;

    -- Verify that the type_meta_ver is properly formatted
    EM_CHECK.check_type_meta_ver(l_type_meta_ver, 'p_type_meta_ver_in');

    -- Use the following defaults
    l_dflt_type_display_name := NVL(p_type_display_name_in, p_target_type_in);
    l_dflt_type_display_nlsid := NVL(p_type_display_nlsid_in, p_target_type_in || '_name');
    l_dflt_type_resource_bundle := NVL(p_type_resource_bundle_in, G_DEFAULT_RESOURCE_BUNDLE);

    IF EMDW_LOG.P_IS_INFO_SET THEN 
      EMDW_LOG.INFO(l_proc_name||'Enter ' || p_target_type_in || ', ' || p_type_meta_ver_in, 
        G_MODULE_NAME) ; 
    END IF ;

    l_new_tgttype := 0;
    BEGIN
      SELECT type_display_name, type_display_nlsid, type_resource_bundle
        INTO l_type_display_name, l_type_display_nlsid, l_type_resource_bundle
        FROM mgmt_target_type_versions
       WHERE target_type = p_target_type_in
         AND type_meta_ver = l_type_meta_ver;
      --
      --Reduce updates by only updating if the values are different
      --
      IF (
         p_type_display_name_in IS NOT NULL AND
         p_type_display_name_in != NVL(l_type_display_name,p_type_display_name_in)
         ) OR
         (
         p_type_display_nlsid_in IS NOT NULL AND
         p_type_display_nlsid_in != NVL(l_type_display_nlsid,p_type_display_nlsid_in)
         ) OR
         ( 
         p_type_resource_bundle_in IS NOT NULL AND
         p_type_resource_bundle_in != NVL(l_type_resource_bundle,
                                          p_type_resource_bundle_in)
         )
      THEN
        l_type_display_name := NVL(p_type_display_name_in, l_type_display_name);
        l_type_display_nlsid := NVL(p_type_display_nlsid_in, l_type_display_nlsid);
        l_type_resource_bundle := NVL(p_type_resource_bundle_in, l_type_resource_bundle);

        -- Populate the mgmt_target_type_versions table
        -- If input is NULL, try to retain the existing value, unless it is also NULL
        UPDATE mgmt_target_type_versions
           SET type_display_name = l_type_display_name,
               type_display_nlsid = l_type_display_nlsid,
  	     type_resource_bundle = l_type_resource_bundle,
               last_updated_date = SYSDATE
         WHERE target_type = p_target_type_in
           AND type_meta_ver = l_type_meta_ver;
      END IF ;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_new_tgttype := 1;
        l_type_ver_guid := EM_TARGET.generate_type_ver_guid(p_target_type_in, l_type_meta_ver);

        -- If adding a new type version, pick the values from target types table
        BEGIN
          SELECT type_display_name, type_display_nlsid, type_resource_bundle
            INTO l_type_display_name, l_type_display_nlsid, l_type_resource_bundle
            FROM MGMT_TARGET_TYPES
           WHERE target_type = p_target_type_in;

          l_type_display_name := NVL(p_type_display_name_in, l_type_display_name);
          l_type_display_nlsid := NVL(p_type_display_nlsid_in, l_type_display_nlsid);
          l_type_resource_bundle := NVL(p_type_resource_bundle_in, l_type_resource_bundle);

        EXCEPTION 
          WHEN NO_DATA_FOUND THEN
            -- If target type also does not exist, use defaults
	    l_type_display_name := l_dflt_type_display_name;
	    l_type_display_nlsid := l_dflt_type_display_nlsid;
	    l_type_resource_bundle := l_dflt_type_resource_bundle;
        END;

        BEGIN
          --
          -- Disallow all registration for the same target type
          -- Cannot use table lock since target type may not exist yet
          --
          EM_TARGET.get_type_registration_lock(p_target_type_in) ;

          INSERT INTO MGMT_TARGET_TYPE_VERSIONS 
          (target_type, type_meta_ver, target_type_ver_guid, 
	   type_display_name, type_display_nlsid, type_resource_bundle, 
	   created_date, last_updated_date)
          VALUES 
          (p_target_type_in, l_type_meta_ver, l_type_ver_guid, 
	   l_type_display_name, l_type_display_nlsid, l_type_resource_bundle,
	   SYSDATE, SYSDATE);
        -- If there are 2 registrations at same time for same type and version
        -- then the second one will get dup_val_on_index and return
        EXCEPTION WHEN DUP_VAL_ON_INDEX THEN RETURN ; 
        END ;
    END;

    -- Update the mgmt_target_types table
    BEGIN

      SELECT max_type_meta_ver
        INTO l_curr_max_type_meta_ver
        FROM MGMT_TARGET_TYPES
       WHERE target_type = p_target_type_in;

      -- Compare l_max_type_meta_ver and l_type_meta_ver
      l_comp_val := EM_TARGET.compare_type_meta_vers(
                        p_type_meta_ver_1 => l_type_meta_ver,
                        p_type_meta_ver_2 => l_curr_max_type_meta_ver);

      IF (l_comp_val >= 0) THEN

        -- Update the mgmt_target types only if the new type meta ver
	-- is greater than the current one.
        UPDATE mgmt_target_types
           SET type_display_name = l_type_display_name,
	       type_display_nlsid = l_type_display_nlsid,
	       type_resource_bundle = l_type_resource_bundle,
	       max_type_meta_ver = l_type_meta_ver
         WHERE target_type = p_target_type_in;

      END IF;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
  
        l_target_type_guid := EM_TARGET.generate_target_type_guid(p_target_type_in);

	l_type_display_name := l_dflt_type_display_name;
	l_type_display_nlsid := l_dflt_type_display_nlsid;
	l_type_resource_bundle := l_dflt_type_resource_bundle;
        INSERT INTO MGMT_TARGET_TYPES
          (target_type, type_display_name, type_display_nlsid, 
  	   type_resource_bundle, target_type_guid, max_type_meta_ver)
        VALUES
          (p_target_type_in, l_type_display_name, l_type_display_nlsid, 
	   l_type_resource_bundle, l_target_type_guid, l_type_meta_ver);
    END;


    -- Add target type properties
    add_target_type_properties(p_target_type_in, p_type_property_list_in);

    -- Execute callbacks, if a new target type version is added.
    IF (l_new_tgttype = 1) THEN

      IF EMDW_LOG.P_IS_INFO_SET THEN 
        EMDW_LOG.INFO(l_proc_name||'New target type version added.. Calling callbacks..',
          G_MODULE_NAME) ; 
      END IF ;

      -- Call the target addition callbacks
      FOR callback IN (SELECT callback_name 
                         FROM mgmt_callbacks
                        WHERE callback_type = MGMT_GLOBAL.G_TGTTYPE_ADD_CALLBACK
			  AND (selector_1 = p_target_type_in OR selector_1 = ' ')
                        ORDER by callback_name -- ordering to avoid deadlocks
                        ) 
      LOOP

        BEGIN
          EXECUTE IMMEDIATE 
            'BEGIN ' || EM_CHECK.qualified_sql_name(callback.callback_name) || '(:1, :2); END; ' USING
             EM_CHECK.NOOP(p_target_type_in), l_type_meta_ver;
        EXCEPTION
          -- Ignore exceptions so that main transaction is not affected; we do not
          -- treat callback exceptions on par with repository exceptions. In other
          -- words, we will not let some callback mess up repository operations.
          WHEN OTHERS THEN
              mgmt_log.log_error(v_module_name_in => 'LOADER',
                v_error_code_in  =>  0,
                v_error_msg_in   => l_proc_name||' Error while calling callback ' || 
		   callback.callback_name || ' with target_type = ' || p_target_type_in ||
		   ' type_meta_ver = ' || l_type_meta_ver ||
		   ' ERROR = ' || SUBSTR(SQLERRM, 1, 2000) );

              IF EMDW_LOG.P_IS_INFO_SET THEN 
                EMDW_LOG.INFO(l_proc_name||'Error while calling callback ' || 
		   callback.callback_name || ' ERROR = ' || SUBSTR(SQLERRM, 1, 2000),
                  G_MODULE_NAME) ; 
              END IF ;
        END;

      END LOOP;
  
    END IF;

  END register_target_type;

  --
  -- PROCEDURE: delete_target_type
  --
  -- PURPOSE: 
  --   Deletes the given target type
  --   If NULL type_meta_ver is provided, all type meta versions are deleted.
  -- 
  PROCEDURE delete_target_type
      (p_target_type_in      IN VARCHAR2,
       p_type_meta_ver_in    IN VARCHAR2 DEFAULT NULL)
  IS
    l_version_cnt NUMBER := 0;
  BEGIN

    EM_CHECK.check_not_null(p_target_type_in, 'p_target_type_in');

    -- Delete user defined target prop defs
    DELETE FROM mgmt_target_prop_defs
     WHERE target_type = p_target_type_in
       AND type_meta_ver = NVL(p_type_meta_ver_in, type_meta_ver);

    DELETE FROM mgmt_target_type_versions
     WHERE target_type = p_target_type_in
       AND type_meta_ver = NVL(p_type_meta_ver_in, type_meta_ver);

    SELECT COUNT(*) INTO l_version_cnt
      FROM mgmt_target_type_versions
     WHERE target_type = p_target_type_in;

    IF (l_version_cnt = 0) THEN
      DELETE FROM MGMT_TARGET_TYPES
       WHERE target_type = p_target_type_in;
    END IF;

  END delete_target_type;

  --
  -- PROCEDURE: add_target_type_property
  --
  -- PURPOSE: 
  --   Adds a property to the given target type
  --   If the property already exists for this target type, its value is 
  --   updated.
  --
  PROCEDURE add_target_type_property
      (p_target_type_in         IN VARCHAR2,
       p_type_property_name_in  IN VARCHAR2,
       p_type_property_value_in IN VARCHAR2) IS
  BEGIN

    -- Perform NOT NULL checks
    EM_CHECK.check_not_null(p_target_type_in, 'p_target_type_in');
    EM_CHECK.check_not_null(p_type_property_name_in, 'p_type_property_name_in');

    BEGIN
      INSERT INTO MGMT_TYPE_PROPERTIES
        (target_type, property_name, property_value)
       VALUES
        (p_target_type_in, p_type_property_name_in, p_type_property_value_in);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        UPDATE MGMT_TYPE_PROPERTIES
	   SET property_value = p_type_property_value_in
         WHERE target_type = p_target_type_in
	   AND property_name = p_type_property_name_in;
    END;

  END add_target_type_property;

  --
  -- PROCEDURE: add_target_type_properties
  --
  -- PURPOSE: 
  --   Adds a list of properties to the given target type
  --   If a property already exists for this target type, its value is 
  --   updated.
  --
  PROCEDURE add_target_type_properties
      (p_target_type_in         IN VARCHAR2,
       p_type_property_list_in  IN SMP_EMD_NVPAIR_ARRAY)
  IS
  BEGIN

    -- Perform NOT NULL checks
    EM_CHECK.check_not_null(p_target_type_in, 'p_target_type_in');

    IF (p_type_property_list_in IS NOT NULL AND 
        p_type_property_list_in.COUNT > 0) THEN
      FOR i IN 1..p_type_property_list_in.count 
      LOOP
        BEGIN
          INSERT INTO MGMT_TYPE_PROPERTIES
            (target_type, property_name, property_value)
           VALUES
            (p_target_type_in, p_type_property_list_in(i).name, 
	     p_type_property_list_in(i).value);
        EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
            UPDATE MGMT_TYPE_PROPERTIES
	       SET property_value = p_type_property_list_in(i).value
             WHERE target_type = p_target_type_in
	       AND property_name = p_type_property_list_in(i).name;
	END;
      END LOOP;
    END IF;
  END add_target_type_properties;

  --
  -- PROCEDURE: delete_target_type_property
  --
  -- PURPOSE: 
  --   Removes the property for the given target type
  -- 
  PROCEDURE delete_target_type_property
      (p_target_type_in    IN VARCHAR2,
       p_type_property_name_in  IN VARCHAR2)
  IS
  BEGIN

    -- Perform NOT NULL checks
    EM_CHECK.check_not_null(p_target_type_in, 'p_target_type_in');
    EM_CHECK.check_not_null(p_type_property_name_in, 'p_type_property_name_in');

    DELETE FROM MGMT_TYPE_PROPERTIES
     WHERE target_type = p_target_type_in
       AND property_name = p_type_property_name_in;

  END delete_target_type_property;


  -- Procedure ADD_TGTTYPE_ADDITION_CALLBACK
  --
  -- PURPOSE: To add a callback that will be called when a new target type is 
  --   registerd to the repository. 
  --   Note 1: All the exceptions raised by the callback are logged as system errors
  PROCEDURE add_tgttype_addition_callback(p_callback_name IN VARCHAR2,
                                          p_target_type   IN VARCHAR2 DEFAULT ' ')
  IS
    l_arg_type_list MGMT_SHORT_STRING_ARRAY;
    l_err_msg       VARCHAR2(4096);
  BEGIN

    -- Perform NOT NULL checks
    EM_CHECK.check_not_null(p_callback_name, 'p_callback_name');

    l_arg_type_list := MGMT_SHORT_STRING_ARRAY('VARCHAR2', 'VARCHAR2');
    IF (EM_CHECK.is_valid_signature(p_callback_name, l_arg_type_list, l_err_msg) = FALSE) 
    THEN
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
        'Invalid callback '  || p_callback_name || ' . Error = ' || l_err_msg);
    END IF;

    BEGIN
      INSERT INTO mgmt_callbacks
        (callback_type, callback_name, selector_1, selector_2, selector_3)
      VALUES
        (MGMT_GLOBAL.G_TGTTYPE_ADD_CALLBACK, UPPER(p_callback_name), NVL(p_target_type, ' '), ' ', ' ');
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
	-- Ignore duplicate callback registrations..
	NULL;
    END;

  END add_tgttype_addition_callback;


  -- Procedure DEL_TGTTYPE_ADDITION_CALLBACK
  --
  -- PURPOSE: To remove a callback that will be called when a target type is 
  --   added to the repository.
  PROCEDURE del_tgttype_addition_callback(p_callback_name IN VARCHAR2,
                                          p_target_type   IN VARCHAR2 DEFAULT ' ')
  IS
    l_target_type mgmt_targets.target_type%TYPE ;
  BEGIN
    -- Perform NOT NULL checks
    EM_CHECK.check_not_null(p_callback_name, 'p_callback_name');

    l_target_type := NVL(p_target_type, ' ');

    DELETE FROM mgmt_callbacks
     WHERE callback_type = MGMT_GLOBAL.G_TGTTYPE_ADD_CALLBACK
       AND callback_name = UPPER(p_callback_name)
       AND selector_1 = l_target_type;

  END del_tgttype_addition_callback;

--
-- a private util function to get the start marker time for a target
--
--  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
--
--  NOTE: this function should be removed once the avail functions from this
--        package are moved to mgmt_avail package
FUNCTION get_tgt_start_marker_time(
              target_guid_in  IN  mgmt_targets.target_guid%TYPE)
  RETURN DATE
IS
  l_status            MGMT_AVAILABILITY.current_status%TYPE;
  l_start_collection_timestamp MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
  l_end_collection_timestamp   MGMT_AVAILABILITY.end_collection_timestamp%TYPE;
  l_start_marker_time DATE;

BEGIN

  -- get the start marker time

  -- Get the first record.
  -- If the current stauts is UNKNOWN, then start marker time is the end_collection_timestamp
  --   else it is the start_collection_timestamp
  -- end time of first avail record is the start market time.
  SELECT current_status, start_collection_timestamp, end_collection_timestamp 
    INTO l_status, l_start_collection_timestamp, l_end_collection_timestamp
    FROM mgmt_availability
   WHERE target_guid = target_guid_in
     AND start_collection_timestamp = 
         (SELECT MIN(start_collection_timestamp)
            FROM mgmt_availability
           WHERE target_guid = target_guid_in);

  IF (l_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) THEN
    l_start_marker_time := l_end_collection_timestamp;
  ELSE
    l_start_marker_time := l_start_collection_timestamp;
  END IF;

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

  RETURN l_start_marker_time;

  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      RAISE MGMT_GLOBAL.target_does_not_exist;

END get_tgt_start_marker_time;

--
-- a private util function to get the start marker time for a target
--
--  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
--
--  NOTE: this function should be removed once the avail functions from this
--        package are moved to mgmt_avail package
FUNCTION get_tgt_end_marker_time(
              target_guid_in  IN  mgmt_targets.target_guid%TYPE)
  RETURN DATE
IS

  l_end_marker_time DATE;

BEGIN

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

  RETURN l_end_marker_time;

  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      RAISE MGMT_GLOBAL.target_does_not_exist;

END get_tgt_end_marker_time;

--
-- PURPOSE:
--   The system stores metric details for different timeperiods
--     (day/week/month) in separate tables. This is an internal function
--     that provides these table names.
--   This function should not be exposed beyond this package.
--
FUNCTION get_table_name_for_timeperiod
   (timeperiod_in IN NUMBER DEFAULT MGMT_GLOBAL.G_DAY)
  RETURN VARCHAR2
IS

BEGIN
  IF (timeperiod_in = MGMT_GLOBAL.G_WEEK)
  THEN
    RETURN 'mgmt_metrics_1hour';
  ELSIF (timeperiod_in = MGMT_GLOBAL.G_MONTH)
  THEN
    RETURN 'mgmt_metrics_1day';
  ELSE
    RETURN 'mgmt_metrics_raw';
  END IF;

END get_table_name_for_timeperiod;

--
-- FUNCTION: get_target_guid
--
-- PURPOSE:
--   This function returns the guid for a given target.
--

FUNCTION get_target_guid
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2)
  RETURN mgmt_targets.target_guid%TYPE
IS

  l_target_guid  mgmt_targets.target_guid%TYPE;

BEGIN
  SELECT target_guid
    INTO l_target_guid
    FROM mgmt_targets
   WHERE target_name = target_name_in AND
         target_type = target_type_in;

  RETURN l_target_guid;

EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    raise_application_error(MGMT_GLOBAL.target_does_not_exist_err,
            MGMT_GLOBAL.target_does_not_exist_err_m ||
            ' ' || target_name_in || ':' || target_type_in);

END get_target_guid;

--
-- FUNCTION: get_metric_guid
--
-- PURPOSE:
--   This function returns the guid for a given metric.
--
FUNCTION get_metric_guid
  (target_type_in  IN VARCHAR2,
   metric_name_in  IN VARCHAR2,
   metric_column_in IN VARCHAR2 DEFAULT ' ')
  RETURN mgmt_metrics.metric_guid%TYPE
IS

  l_metric_guid  mgmt_metrics.metric_guid%TYPE;

BEGIN

  SELECT DISTINCT metric_guid
    INTO l_metric_guid
    FROM mgmt_metrics
   WHERE target_type = target_type_in 
     AND metric_name = metric_name_in 
     AND metric_column = metric_column_in;

  RETURN l_metric_guid;

EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    raise MGMT_GLOBAL.no_such_metric_found;

END get_metric_guid;

--
-- PURPOSE:
--   The metrics in the repository are identified by a globally unique
--   identifier (guid). This function returns all the metric column guids
--   for a given metric.
--
FUNCTION get_metric_column_guids
  (target_type_in  IN VARCHAR2,
   metric_name_in  IN VARCHAR2)
  RETURN SMP_EMD_NVPAIR_ARRAY
IS

  l_metric_column_guids SMP_EMD_NVPAIR_ARRAY;

BEGIN

  SELECT SMP_EMD_NVPAIR(met.metric_column, met.metric_guid)
    BULK COLLECT INTO l_metric_column_guids
    FROM mgmt_metrics met
   WHERE met.target_type = target_type_in AND
         met.metric_name = metric_name_in;

  RETURN l_metric_column_guids;

EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    raise MGMT_GLOBAL.no_such_metric_found;

END get_metric_column_guids;
---
-- Function to get system date as per timezone of target
--
FUNCTION sysdate_target(p_target_guid IN RAW)
RETURN DATE
IS
l_target_sysdate DATE ;
BEGIN
  SELECT CAST(systimestamp at time zone timezone_region AS DATE)
   INTO l_target_sysdate
   FROM mgmt_targets
  WHERE target_guid = p_target_guid;

  RETURN(l_target_sysdate) ;

EXCEPTION
WHEN NO_DATA_FOUND THEN
  raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
    'Target with guid ' || p_target_guid || ' does not exist');
END sysdate_target ;

--
-- PURPOSE:
--   The system treats the timeperiod - day/week/month as all timestamps
--     greater than or equals to 1/7/31 days from the current timestamp.
--     This function returns this difference given a timeperiod constant.
--     Meant to be used internally - as this definition might change.
--
FUNCTION get_time_difference
   (timeperiod_in IN NUMBER DEFAULT MGMT_GLOBAL.G_DAY)
  RETURN NUMBER
IS

BEGIN
  IF (timeperiod_in = MGMT_GLOBAL.G_WEEK)
  THEN
    RETURN 7;
  ELSIF (timeperiod_in = MGMT_GLOBAL.G_MONTH)
  THEN
    RETURN 31;
  ELSE
    RETURN 1;
  END IF;

END get_time_difference;

--
-- FUNCTION: get_avail_current_status
--
-- PURPOSE:
--   This function returns the availability current status of the given target
--   guid
--
-- TODO: Move this procedure/function to mgmt_avail package
--
FUNCTION get_avail_current_status
  (target_guid_in  IN  mgmt_targets.target_guid%TYPE)
  RETURN NUMBER
IS

  -- Unknown status to be returned by default
  current_status_val NUMBER := MGMT_GLOBAL.G_STATUS_UNKNOWN;
  targets_down NUMBER := 0;
  target_type        VARCHAR2(64) := NULL;

BEGIN
  -- no need to special case for a typed group target ...
  -- for all targets, get the avilability status from mgmt_current_availability ...

  SELECT a.current_status
    INTO current_status_val
    FROM mgmt_current_availability a
   WHERE a.target_guid=target_guid_in;

  IF current_status_val IS NOT NULL
  THEN
    return current_status_val;
  -- else return unknown status
  ELSE
    return MGMT_GLOBAL.G_STATUS_UNKNOWN;
  END IF;

  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      return MGMT_GLOBAL.G_STATUS_UNKNOWN;

    WHEN TOO_MANY_ROWS
    THEN
      RAISE;

END get_avail_current_status;

--
-- FUNCTION: get_avail_pct_value
--
-- PURPOSE:
--   This function returns the availability value as a percentage of the given
--   target name and target type
--
-- TODO: Move this procedure/function to mgmt_avail package
--
FUNCTION get_avail_pct_value
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2,
   num_of_days_in  IN  NUMBER)
  RETURN NUMBER
IS

  avail_pct_value NUMBER := 0;
  l_target_guid  mgmt_targets.target_guid%TYPE;

BEGIN

  -- first get the target_guid
  -- may throw a TARGET_DOES_NOT_EXIST EXCEPTION
  l_target_guid := get_target_guid(target_name_in, target_type_in);

  avail_pct_value := get_avail_pct_value( l_target_guid, num_of_days_in );

  return avail_pct_value;

END get_avail_pct_value;

--
-- FUNCTION: get_avail_pct_value
--
-- PURPOSE:
--   This function returns the availability value in percentage of the given
--   target guid
--
-- TODO: Move this procedure/function to mgmt_avail package
--
FUNCTION get_avail_pct_value
  (target_guid_in  IN  mgmt_targets.target_guid%TYPE,
   num_of_days_in  IN  NUMBER)
  RETURN NUMBER
IS

  l_target_tzd  mgmt_targets.timezone_delta%TYPE;
  avail_pct_value NUMBER := 0;

BEGIN

  -- Store the target timezone delta to improve performance.
  SELECT timezone_delta
    INTO l_target_tzd
    FROM mgmt_targets targets
   WHERE targets.target_guid = target_guid_in;

  avail_pct_value := get_avail_pct_value(target_guid_in, l_target_tzd, num_of_days_in);

  return avail_pct_value;

  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      RAISE MGMT_GLOBAL.target_does_not_exist;

END get_avail_pct_value;

--
-- FUNCTION: get_avail_pct_value
--
-- PURPOSE:
--   This function returns the availability value as a percentage of the given
--   target name and target type
--
-- TODO: Move this procedure/function to mgmt_avail package
--
FUNCTION get_avail_pct_value
  (target_name_in IN VARCHAR2,
   target_type_in IN VARCHAR2,
   target_tz_delta_in IN mgmt_targets.timezone_delta%TYPE,
   num_of_days_in  IN  NUMBER)
  RETURN NUMBER

IS

  avail_pct_value NUMBER := 0;
  l_target_guid  mgmt_targets.target_guid%TYPE;

BEGIN

  -- first get the target_guid
  -- may throw a TARGET_DOES_NOT_EXIST EXCEPTION
  l_target_guid := get_target_guid(target_name_in, target_type_in);

  avail_pct_value := get_avail_pct_value( l_target_guid, target_tz_delta_in, num_of_days_in );

  return avail_pct_value;

END get_avail_pct_value;

--------------------------------------------------------------------------------------------------------
--
--  FUNCTION get_avail_pct_value()
--
--------------------------------------------------------------------------------------------------------
--
-- TODO: Move this procedure/function to mgmt_avail package
-- target_tz_delta_in is no longer used , switched to timezone region
FUNCTION get_avail_pct_value
  (target_guid_in  IN  mgmt_targets.target_guid%TYPE,
   target_tz_delta_in IN mgmt_targets.timezone_delta%TYPE,
   num_of_days_in  IN  NUMBER)
  RETURN NUMBER
IS
  uptime NUMBER;
  avail_pct_value NUMBER DEFAULT 0;
  non_composite_flag NUMBER DEFAULT 0;
  comp_avail_table MgmtAvailTableType := MgmtAvailTableType();
  l_numerator NUMBER := 0;
  l_denominator NUMBER := 0;
  l_uptime NUMBER := 0;
  l_downtime NUMBER := 0;
  l_unmonitoredtime NUMBER := 0;
  l_target_type VARCHAR2(64);
  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;
  tmp NUMBER := 0;
  is_group NUMBER := 0;
  l_avail_formula NUMBER := 0;
BEGIN

   -- is this a group
   BEGIN
     SELECT t.is_group, t.target_type,mgmt_global.sysdate_tzrgn(timezone_region)
       INTO is_group, l_target_type,l_target_sysdate
       FROM mgmt_targets t
      WHERE t.target_guid = target_guid_in;

   EXCEPTION
     WHEN NO_DATA_FOUND
     THEN
       RAISE MGMT_GLOBAL.target_does_not_exist;
   END;

   -- return -1 if the target is a group - as we have no 
   -- availability for a group as of now
   IF is_group = 1
   THEN
     -- we're here - it's a group     
     RETURN -1;

   END IF;

   -- find out if the target passed in is a composite or non-composite
   SELECT COUNT (*)  into non_composite_flag
      FROM MGMT_METRICS m, MGMT_TARGETS mt
              WHERE mt.target_guid = target_guid_in
                AND m.target_type  = mt.target_type
                AND m.metric_name  = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
                AND m.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
                AND rownum  < 2;

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

   EXCEPTION
     WHEN NO_DATA_FOUND
     THEN
       l_avail_formula := 0;
   END;


   -- 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_tgt_start_marker_time( target_guid_in );

   -- determine the end marker time
   l_end_marker_time := get_tgt_end_marker_time( target_guid_in );

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

   IF non_composite_flag = 1
   THEN
     --
     --  Handle the non-composite targets case
     --
     BEGIN
       SELECT
         SUM(DECODE(a.current_status,
                    MGMT_GLOBAL.G_STATUS_UP, NVL(a.end_collection_timestamp, l_recomputed_end_date)-
                       GREATEST(a.start_collection_timestamp, l_recomputed_start_date),
                    0)),
         SUM(DECODE(a.current_status,
                    MGMT_GLOBAL.G_STATUS_DOWN, NVL(a.end_collection_timestamp, l_recomputed_end_date)-
                       GREATEST(a.start_collection_timestamp, l_recomputed_start_date),
                    0)),
         SUM(DECODE(a.current_status,
                   MGMT_GLOBAL.G_STATUS_AGENT_DOWN, NVL(a.end_collection_timestamp, l_recomputed_end_date)-
                       GREATEST(a.start_collection_timestamp, l_recomputed_start_date),
                    0))
        INTO l_uptime, l_downtime, l_unmonitoredtime
        FROM MGMT_AVAILABILITY a
       WHERE a.target_guid = target_guid_in
         AND NVL(a.end_collection_timestamp, l_recomputed_end_date) >= l_recomputed_start_date
         AND a.start_collection_timestamp <= l_recomputed_end_date;

       IF(l_avail_formula = 1 AND l_target_type != MGMT_GLOBAL.G_AGENT_TARGET_TYPE)
       THEN
         -- The Availability Percentage Calculation is:
         -- -------------------------------------------
         --    Hosts: [ 1 - (agent down time / number of days) ]*100
         --    Others (non-agent): [ 1 - (downtime / number of days) ]*100
         -------------------------------------------------------------
       
         -- numerator is downtime, but since the host target doesn't have 
         -- downtime, use agent down time instead
         IF(l_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE)
         THEN
           l_numerator := l_unmonitoredtime;
         ELSE
           l_numerator := l_downtime;
         END IF;

         -- denominator is number of days
         l_denominator := num_of_days_in;

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

       -- Default
       ELSE
         -- The Availability Percentage Calculation is:
         -- -------------------------------------------
         --    [ uptime / (uptime + downtime + agent down time) ]*100
         -------------------------------------------------------------
       
         -- numerator is uptime
         l_numerator := l_uptime;

         -- denominator is (uptime + downtime + agent down time)
         l_denominator := l_uptime + l_downtime + l_unmonitoredtime;

         IF( l_denominator = 0 )
         THEN
           avail_pct_value := -1;
         ELSE
           avail_pct_value := (l_numerator/l_denominator) * 100;
         END IF;
       END IF;
     END;
   ELSE
     --
     --  Handle the composite targets using the emd_mntr_comptgt.GetAvailability() function.
     --  It will return composite target availability intervals that appear to be like records
     --  from mgmt_availability, but are actually constructed on the fly in the GetAvailability() fcn.
     --
     BEGIN
     
      SELECT MgmtAvailRowType (target_guid, severity_guid, current_status, 
                               start_collection_timestamp, end_collection_timestamp)
          bulk collect into comp_avail_table
       FROM TABLE(CAST(emd_mntr_comptgt.GetAvailability(target_guid_in, num_of_days_in) AS
               MgmtAvailTableType));

       SELECT SUM(DECODE(a.current_status, 
                      MGMT_GLOBAL.G_STATUS_UP, NVL(a.end_collection_timestamp, l_target_sysdate)- 
                      GREATEST(a.start_collection_timestamp, (l_target_sysdate-num_of_days_in)) , 
                      0)),
              SUM(DECODE(a.current_status, 
                      MGMT_GLOBAL.G_STATUS_AGENT_DOWN, NVL(a.end_collection_timestamp, l_target_sysdate)- 
                      GREATEST(a.start_collection_timestamp, (l_target_sysdate-num_of_days_in)) , 
                      0)),      
              SUM(DECODE(a.current_status,
                      MGMT_GLOBAL.G_STATUS_DOWN,
                      NVL(a.end_collection_timestamp, l_target_sysdate)- 
                      GREATEST(a.start_collection_timestamp, (l_target_sysdate-num_of_days_in)),
                      0))
        INTO l_uptime, l_unmonitoredtime, l_downtime
        FROM TABLE(CAST(comp_avail_table AS MgmtAvailTableType)) a,
             TABLE(CAST(comp_avail_table AS MgmtAvailTableType)) ca
        WHERE a.target_guid = target_guid_in
          AND a.target_guid = ca.target_guid
          AND (a.end_collection_timestamp IS NULL
          OR a.end_collection_timestamp >= (l_target_sysdate - num_of_days_in))
          AND ca.end_collection_timestamp IS NULL;

       IF(l_avail_formula = 1)
       THEN
         -- The Availability Percentage Calculation is:
         -- -------------------------------------------
         --    [ 1 - (downtime / number of days) ]*100
         -------------------------------------------------------------
       
         -- numerator is downtime
         l_numerator := l_downtime;

         -- denominator is number of days
         l_denominator := num_of_days_in;

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

       -- Default
       ELSE
         -- The Availability Percentage Calculation is:
         -- -------------------------------------------
         --    [ uptime / (uptime + downtime + agent down time) ]*100
         -------------------------------------------------------------

         l_numerator   := l_uptime;
         l_denominator := l_uptime + l_downtime + l_unmonitoredtime;

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

     END;

   END IF;

   return avail_pct_value;

   EXCEPTION

        WHEN ZERO_DIVIDE
        THEN
            RETURN 0;

        WHEN TOO_MANY_ROWS
        THEN
          RETURN 0;

END get_avail_pct_value;

--
-- FUNCTION: get_target_max_severity
--
-- PURPOSE:
--   This function returns the maximum severity for the given target name and
--   target type
--
FUNCTION get_target_max_severity
  (target_name_in IN VARCHAR2,
   target_type_in IN VARCHAR2)
  RETURN NUMBER
IS

  l_severity_code NUMBER := MGMT_GLOBAL.G_SEVERITY_CLEAR;
  l_target_guid  mgmt_targets.target_guid%TYPE;

BEGIN

  -- first get the target_guid
  -- may throw a TARGET_DOES_NOT_EXIST EXCEPTION
  l_target_guid := get_target_guid(target_name_in, target_type_in);

  l_severity_code := get_target_max_severity( l_target_guid );

  -- handle null value
  l_severity_code := NVL( l_severity_code, MGMT_GLOBAL.G_SEVERITY_CLEAR );

  return l_severity_code;

END get_target_max_severity;

--
-- FUNCTION: get_target_max_severity
--
-- PURPOSE:
--   This function returns the maximum severity for the given target name and
--   target type
--
FUNCTION get_target_max_severity
  (target_guid_in  IN  mgmt_targets.target_guid%TYPE)
  RETURN NUMBER
IS

  l_severity_code NUMBER := MGMT_GLOBAL.G_SEVERITY_CLEAR;

BEGIN

  SELECT NVL( MAX(severity_code), MGMT_GLOBAL.G_SEVERITY_CLEAR )
    INTO l_severity_code
    FROM mgmt_targets tgt,
         mgmt_current_severity sev
   WHERE tgt.target_guid=target_guid_in
     AND tgt.target_guid=sev.target_guid;

  return l_severity_code;

  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      return MGMT_GLOBAL.G_SEVERITY_CLEAR;

END get_target_max_severity;

--
-- FUNCTION: get_target_num_severity
--
-- PURPOSE:
--   This function returns the number of severities for the given target guid
FUNCTION get_target_num_severity
  (target_guid_in IN mgmt_targets.target_guid%TYPE)
  RETURN INTEGER
IS

  l_num_severity INTEGER :=0;

BEGIN

  SELECT COUNT(severity_code)
    INTO l_num_severity
    FROM mgmt_current_severity
   WHERE target_guid = target_guid_in;

  return l_num_severity;

  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      return 0;

END get_target_num_severity;

--
-- FUNCTION: get_current_status_timestamp
--
-- PURPOSE:
--   This function returns the start timestamp of current status of the given
--   target guid or returns null if the timestamp is not found.
--
-- TODO: Move this procedure/function to mgmt_avail package
--
FUNCTION get_current_status_timestamp
  (target_guid_in IN mgmt_targets.target_guid%TYPE)
  RETURN Date
IS

  l_start_timestamp Date;

BEGIN

  SELECT a.start_collection_timestamp INTO l_start_timestamp
    FROM mgmt_current_availability a
   WHERE a.target_guid = target_guid_in;

  return l_start_timestamp;

  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      return null;

    WHEN TOO_MANY_ROWS
    THEN
      RAISE;

END get_current_status_timestamp;

--
-- PURPOSE:
--   Returns a summary of monitoring details for a particular target
--     or all the targets of a type if no target name is given
--     or all the targets if no target name or type is given.
--
PROCEDURE get_target_monitoring_summary
  (target_name_in  IN VARCHAR2 DEFAULT NULL,
   target_type_in  IN VARCHAR2 DEFAULT NULL,
   search_target_name_in IN VARCHAR2 DEFAULT NULL,
   timeperiod_in  IN NUMBER DEFAULT MGMT_GLOBAL.G_DAY,
   summary_out OUT CURSOR_TYPE)
IS

  l_target_guid  mgmt_targets.target_guid%TYPE;
  l_time_difference NUMBER := 1;

BEGIN

  l_time_difference := get_time_difference(timeperiod_in);

  IF (target_name_in IS NOT NULL AND target_type_in IS NOT NULL)
  THEN
    -- may throw a TARGET_DOES_NOT_EXIST EXCEPTION
    l_target_guid := get_target_guid(target_name_in, target_type_in);

    IF( search_target_name_in IS NULL )
    THEN
        -- no search target name clause
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.target_name as display_name,
                 tgt.type_display_name,
                 ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid,l_time_difference),0) AS avail_pct,
                 mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status,
                 NVL(sev_detail.warning_count, 0) AS warning_count,
                 NVL(sev_detail.critical_count, 0) AS critical_count,
                 tgt.target_guid
            FROM mgmt_targets tgt,
                 (SELECT target_guid,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count
                    FROM mgmt_current_severity
                   WHERE target_guid = l_target_guid
                   GROUP BY target_guid) sev_detail
           WHERE tgt.target_guid = l_target_guid
             AND tgt.target_guid = sev_detail.target_guid (+)
           ORDER BY tgt.type_display_name, tgt.target_name;
    ELSE
        -- search target name clause provided
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.target_name as display_name,
                 tgt.type_display_name,
                 ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid,l_time_difference),0) AS avail_pct,
                 mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status,
                 NVL(sev_detail.warning_count,0) AS warning_count,
                 NVL(sev_detail.critical_count,0) AS critical_count,
                 tgt.target_guid
            FROM mgmt_targets tgt,
                 (SELECT target_guid,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count
                    FROM mgmt_current_severity
                   WHERE target_guid = l_target_guid
                   GROUP BY target_guid) sev_detail
           WHERE tgt.target_guid = l_target_guid
             AND UPPER(tgt.target_name) LIKE search_target_name_in
             AND tgt.target_guid = sev_detail.target_guid (+)
           ORDER BY tgt.type_display_name, tgt.target_name;
    END IF;

  ELSIF (target_name_in IS NULL AND target_type_in IS NOT NULL)
  THEN
    --  All targets of a type..

    IF( search_target_name_in IS NULL )
    THEN
        -- no search target name clause
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.target_name as display_name,
                 tgt.type_display_name,
                 ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid, l_time_difference), 0) AS avail_pct,
                 mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status,
                 NVL(sev_detail.warning_count, 0) AS warning_count,
                 NVL(sev_detail.critical_count, 0) AS critical_count,
                 tgt.target_guid
            FROM mgmt_targets tgt,
                 (SELECT sev.target_guid,
                         SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count,
                         SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count
                    FROM mgmt_current_severity sev,
                         mgmt_targets target
                   WHERE target.target_type = target_type_in AND
                         target.target_guid = sev.target_guid
                   GROUP BY sev.target_guid ) sev_detail
           WHERE tgt.target_type = target_type_in
             AND tgt.target_guid = sev_detail.target_guid (+)
           ORDER BY tgt.type_display_name, tgt.target_name;
    ELSE
        -- search target name clause provided
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.target_name as display_name,
                 tgt.type_display_name,
                 ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid, l_time_difference), 0) AS avail_pct,
                 mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status,
                 NVL(sev_detail.warning_count, 0) AS warning_count,
                 NVL(sev_detail.critical_count, 0) AS critical_count,
                 tgt.target_guid
            FROM mgmt_targets tgt,
                 (SELECT sev.target_guid,
                         SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count,
                         SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count
                    FROM mgmt_current_severity sev,
                         mgmt_targets target
                   WHERE target.target_type = target_type_in AND
                         target.target_guid = sev.target_guid
                   GROUP BY sev.target_guid ) sev_detail
           WHERE tgt.target_type = target_type_in
             AND tgt.target_guid = sev_detail.target_guid (+)
             AND UPPER(tgt.target_name) like search_target_name_in
           ORDER BY tgt.type_display_name, tgt.target_name;
    END IF;

  ELSE
    --  All targets..

    IF( search_target_name_in IS NULL )
    THEN
        -- no search target name clause
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.target_name as display_name,
                 tgt.type_display_name,
                 ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid, l_time_difference), 0) AS avail_pct,
                 mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status,
                 NVL(sev_detail.warning_count, 0) AS warning_count,
                 NVL(sev_detail.critical_count, 0) AS critical_count,
                 tgt.target_guid
            FROM mgmt_targets tgt,
                 (SELECT target_guid,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count
                    FROM mgmt_current_severity
                   GROUP BY target_guid ) sev_detail
           WHERE tgt.target_guid = sev_detail.target_guid (+)
           ORDER BY tgt.type_display_name, tgt.target_name;
    ELSE
        -- search target name clause provided
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.target_name as display_name,
                 tgt.type_display_name,
                 ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid, l_time_difference), 0) AS avail_pct,
                 mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status,
                 NVL(sev_detail.warning_count, 0) AS warning_count,
                 NVL(sev_detail.critical_count, 0) AS critical_count,
                 tgt.target_guid
            FROM mgmt_targets tgt,
                 (SELECT target_guid,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count
                    FROM mgmt_current_severity
                   GROUP BY target_guid ) sev_detail
           WHERE tgt.target_guid = sev_detail.target_guid (+)
             AND UPPER(tgt.target_name) like search_target_name_in
           ORDER BY tgt.type_display_name, tgt.target_name;
    END IF;

  END IF;

END get_target_monitoring_summary;

--
-- PURPOSE:
--   Returns a summary of monitoring details for all targets
--
PROCEDURE get_all_targets_mntr_summary
  (search_target_name_in IN VARCHAR2 DEFAULT NULL,
   summary_out OUT CURSOR_TYPE)
IS

BEGIN

  --  All targets..
  IF( search_target_name_in IS NULL )
  THEN
      -- no search target name clause
      OPEN summary_out FOR
        SELECT tgt.target_name,
               tgt.target_type,
               tgt.display_name,
               tgt.type_display_name,
               DECODE(avail_status.cur_status, NULL, MGMT_GLOBAL.G_STATUS_UNKNOWN, avail_status.cur_status) cur_status,
               NVL(alltgt.warning_count,0)+NVL(comptgt.warning_count,0) AS warning_count,
               NVL(alltgt.critical_count,0)+NVL(comptgt.critical_count,0) AS critical_count,
               tgt.target_guid,
               NVL(avail_status.target_status_down,0) AS target_status_down,
               DECODE(comptgt.has_member_status,1,1,0) has_member_status
          FROM mgmt_targets tgt,
               (SELECT cur_status,
                       target_guid,
                       target_status_down
                  FROM (SELECT DECODE(avail.current_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail.current_status) cur_status,
                               avail.target_guid target_guid,
                               0 AS target_status_down
                          FROM mgmt_current_availability avail,
                               mgmt_metrics met,
                               mgmt_targets tgt
                        WHERE met.target_type = tgt.target_type
                          AND met.metric_name=MGMT_GLOBAL.G_AVAIL_METRIC_NAME
                          AND met.metric_column=MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
                          AND tgt.target_guid=avail.target_guid)
                       UNION ALL
                       (SELECT DECODE(SUM(DECODE(avail.current_status,0,1,0)),0,1,0) cur_status,
                               mem.source_target_guid target_guid,
                               1 AS target_status_down
                          FROM mgmt_current_availability avail,
                               mgmt_target_assocs mem,
                               mgmt_targets ct,
                               mgmt_type_properties prop
                         WHERE prop.property_name=MGMT_GLOBAL.G_DERIVED_STATUS_PROP
                           AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
                           AND ct.target_type=prop.target_type
                           AND mem.source_target_guid = ct.target_guid              
                           AND avail.target_guid=mem.assoc_target_guid
                         GROUP BY mem.source_target_guid)) avail_status,
               (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                       SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                       target_guid tguid
                  FROM mgmt_current_severity
                 GROUP BY target_guid ) alltgt,
               (SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                       SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                       tm.source_target_guid tguid,
                       1 as has_member_status
                  FROM mgmt_target_assocs tm,
                       mgmt_current_severity cs
                 WHERE tm.assoc_target_guid = cs.target_guid
                   AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
                 GROUP BY tm.source_target_guid) comptgt
         WHERE tgt.target_guid = avail_status.target_guid(+)
           AND tgt.target_guid = comptgt.tguid(+)
           AND tgt.target_guid = alltgt.tguid(+)
         ORDER BY tgt.type_display_name, tgt.target_name;
  ELSE
      -- search target name clause provided
      OPEN summary_out FOR
        SELECT tgt.target_name,
               tgt.target_type,
               tgt.display_name,
               tgt.type_display_name,
               DECODE(avail_status.cur_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail_status.cur_status) cur_status,
               NVL(alltgt.warning_count,0)+NVL(comptgt.warning_count,0) AS warning_count,
               NVL(alltgt.critical_count,0)+NVL(comptgt.critical_count,0) AS critical_count,
               tgt.target_guid,
               NVL(avail_status.target_status_down,0) AS target_status_down,
               DECODE(comptgt.has_member_status,1,1,0) has_member_status
          FROM mgmt_targets tgt,
               (SELECT cur_status,
                       target_guid,
                       target_status_down
                  FROM (SELECT DECODE(avail.current_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail.current_status) cur_status,
                               avail.target_guid target_guid,
                               0 AS target_status_down
                          FROM mgmt_current_availability avail,
                               mgmt_metrics met,
                               mgmt_targets tgt
                         WHERE met.target_type = tgt.target_type
                           AND met.metric_name=MGMT_GLOBAL.G_AVAIL_METRIC_NAME
                           AND met.metric_column=MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
                           AND tgt.target_guid=avail.target_guid)
                       UNION ALL
                       (SELECT DECODE(SUM(DECODE(avail.current_status,0,1,0)),0,1,0) cur_status,
                               mem.source_target_guid target_guid,
                               1 AS target_status_down
                          FROM mgmt_current_availability avail,
                               mgmt_target_assocs mem,
                               mgmt_targets ct,
                               mgmt_type_properties prop
                         WHERE prop.property_name=MGMT_GLOBAL.G_DERIVED_STATUS_PROP
                           AND ct.target_type=prop.target_type
                           AND mem.source_target_guid = ct.target_guid
                           AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
                           AND avail.target_guid=mem.assoc_target_guid
                         GROUP BY mem.source_target_guid)) avail_status,
               (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                       SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                       target_guid tguid
                  FROM mgmt_current_severity
                 GROUP BY target_guid ) alltgt,
               (SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                       SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                       tm.source_target_guid tguid,
                       1 as has_member_status
                  FROM mgmt_target_assocs tm,
                       mgmt_current_severity cs
                 WHERE tm.assoc_target_guid = cs.target_guid
                   AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
                 GROUP BY tm.source_target_guid) comptgt
         WHERE tgt.target_guid = avail_status.target_guid(+)
           AND tgt.target_guid = alltgt.tguid(+)
           AND tgt.target_guid = comptgt.tguid(+)
           AND UPPER(tgt.target_name) like search_target_name_in
         ORDER BY tgt.type_display_name, tgt.target_name;
  END IF;

END get_all_targets_mntr_summary;

--
-- PURPOSE:
--   Returns a summary of monitoring details for the members of
--     a particular target
--
PROCEDURE get_member_monitoring_summary
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2,
   member_target_type_in IN VARCHAR2,
   timeperiod_in   IN NUMBER DEFAULT MGMT_GLOBAL.G_DAY,
   summary_out OUT CURSOR_TYPE)
IS

  l_target_guid  mgmt_targets.target_guid%TYPE;
  l_time_difference NUMBER := 1;

BEGIN

  l_time_difference := get_time_difference(timeperiod_in);

  -- may throw a TARGET_DOES_NOT_EXIST EXCEPTION
  l_target_guid := get_target_guid(target_name_in, target_type_in);

  IF( member_target_type_in IS NULL OR length(member_target_type_in) = 0 )
  THEN
    -- get member monitoring summary for all member targets of the
    -- composite target
    OPEN summary_out FOR
      SELECT tgt.target_name,
             tgt.target_type,
             tgt.target_name as display_name,
             tgt.type_display_name,
             ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid,l_time_difference),0) AS avail_pct,
             mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status,
             NVL(sev_detail.warning_count, 0) AS warning_count,
             NVL(sev_detail.critical_count, 0) AS critical_count,
             tgt.target_guid,
             tgt.is_group AS is_group_type,
             tgt.host_name,
             NVL(policy_detail.information_count, 0) AS policy_information_count,             
             NVL(policy_detail.warning_count, 0) AS policy_warning_count,
             NVL(policy_detail.critical_count, 0) AS policy_critical_count,
             NVL(policy_detail.compliance_score, -1) as target_compliance_score             
        FROM mgmt_flat_target_assoc mem,
             mgmt_targets tgt,
             (SELECT a1.assoc_target_guid as target_guid,
                     SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count,
                     SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count
                FROM mgmt_flat_target_assoc a1,
                     mgmt_flat_target_assoc a2,
                     mgmt_targets mt1,
                     mgmt_current_severity sev
                WHERE a1.source_target_guid=l_target_guid
                  AND a1.is_membership = 1
                  AND a2.is_membership = 1
                  AND mt1.target_guid = a1.assoc_target_guid
	         AND (  (a1.assoc_target_guid=a2.source_target_guid 
                             AND (mt1.target_type='composite' 
                                  OR mt1.target_type='oracle_database_group'
                                  OR mt1.target_type='host_group') 
                        )                      
                        OR 
                        (a1.assoc_target_guid = a2.assoc_target_guid 
			     AND a2.source_target_guid=l_target_guid
                             AND mt1.target_type!='composite'
                             AND mt1.target_type!='oracle_database_group'
                             AND mt1.target_type!='host_group'
                        ) 
                     )
                 AND a2.assoc_target_guid=sev.target_guid
               GROUP BY a1.assoc_target_guid) sev_detail,
              (SELECT
                  a1.assoc_target_guid  as tguid,
                  SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,e.non_exempt_violations_logged,0)) information_count,
                  SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,e.non_exempt_violations_logged,0)) warning_count,
                  SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,e.non_exempt_violations_logged,0)) critical_count,   
                  ROUND(AVG(compliance_score)) as compliance_score
                FROM
                  mgmt_flat_target_assoc a1,
                  mgmt_flat_target_assoc a2,
                  mgmt_targets mt1,
                  mgmt_policy_assoc_eval_summ e, 
                  mgmt_policies r
                WHERE a1.source_target_guid = l_target_guid
                  AND a1.is_membership = 1
                  AND a2.is_membership = 1
                  AND e.target_guid = a2.assoc_target_guid
                  AND r.policy_guid = e.policy_guid 
                  AND r.policy_type = MGMT_GLOBAL.G_TYPE_POLICY              
                  AND mt1.target_guid = a1.assoc_target_guid
                       AND ((a1.assoc_target_guid = a2.source_target_guid 
                             AND (mt1.target_type='composite' 
                                  OR mt1.target_type='oracle_database_group'
                                  OR mt1.target_type='host_group') 
                            )                      
                            OR 
                            (a2.assoc_target_guid = a1.assoc_target_guid 
                             AND a2.source_target_guid = a1.source_target_guid
                             AND mt1.target_type!='composite'
                             AND mt1.target_type!='oracle_database_group'
                             AND mt1.target_type!='host_group'
                             ) 
                           )        
               GROUP BY a1.assoc_target_guid) policy_detail                               
       WHERE mem.source_target_guid = l_target_guid
         AND mem.assoc_target_guid = tgt.target_guid
         ANd mem.is_membership  = 1
         AND tgt.target_guid = sev_detail.target_guid (+)
         AND tgt.target_guid = policy_detail.tguid(+)
       ORDER BY tgt.type_display_name, tgt.target_name;
  ELSE
    -- get member monitoring summary for member targets of type -
    -- member_target_type_in of the composite target
    OPEN summary_out FOR
      SELECT tgt.target_name,
             tgt.target_type,
             tgt.target_name as display_name,
             tgt.type_display_name,
             ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid,l_time_difference),0) AS avail_pct,
             mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status,
             NVL(sev_detail.warning_count,0) AS warning_count,
             NVL(sev_detail.critical_count,0) AS critical_count,
             tgt.target_guid,
             tgt.is_group AS is_group_type,
             NVL(policy_detail.information_count, 0) AS policy_information_count,             
             NVL(policy_detail.warning_count, 0) AS policy_warning_count,
             NVL(policy_detail.critical_count, 0) AS policy_critical_count,
             NVL(policy_detail.compliance_score, -1) as target_compliance_score  
        FROM mgmt_flat_target_assoc mem,
             mgmt_targets tgt,
             (SELECT a1.assoc_target_guid as target_guid,
                     SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count,
                     SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count
                FROM mgmt_flat_target_assoc a1,
                     mgmt_flat_target_assoc a2,
                     mgmt_targets mt1,
                     mgmt_current_severity sev
                WHERE a1.source_target_guid=l_target_guid
                  AND a1.is_membership = 1
                  AND a2.is_membership = 1
                  AND mt1.target_guid = a1.assoc_target_guid
	         AND (  (a1.assoc_target_guid=a2.source_target_guid 
                             AND (mt1.target_type='composite' 
                                  OR mt1.target_type='oracle_database_group'
                                  OR mt1.target_type='host_group') 
                        )                      
                        OR 
                        (a1.assoc_target_guid = a2.assoc_target_guid 
			     AND a2.source_target_guid=l_target_guid
                             AND mt1.target_type!='composite'
                             AND mt1.target_type!='oracle_database_group'
                             AND mt1.target_type!='host_group'
                        ) 
                     )
                 AND a2.assoc_target_guid=sev.target_guid
               GROUP BY a1.assoc_target_guid) sev_detail,
              (SELECT
                  a1.assoc_target_guid  as tguid,
                  SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,e.non_exempt_violations_logged,0)) information_count,
                  SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,e.non_exempt_violations_logged,0)) warning_count,
                  SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,e.non_exempt_violations_logged,0)) critical_count,   
                  ROUND(AVG(compliance_score)) as compliance_score
                FROM
                  mgmt_flat_target_assoc a1,
                  mgmt_flat_target_assoc a2,
                  mgmt_targets mt1,
                  mgmt_policy_assoc_eval_summ e, 
                  mgmt_policies r
                WHERE a1.source_target_guid = l_target_guid
                  AND a1.is_membership = 1
                  AND a2.is_membership = 1
                  AND e.target_guid = a2.assoc_target_guid
                  AND r.policy_guid = e.policy_guid 
                  AND r.policy_type = MGMT_GLOBAL.G_TYPE_POLICY              
                  AND mt1.target_guid = a1.assoc_target_guid
                       AND ((a1.assoc_target_guid = a2.source_target_guid 
                             AND (mt1.target_type='composite' 
                                  OR mt1.target_type='oracle_database_group'
                                  OR mt1.target_type='host_group') 
                            )                      
                            OR 
                            (a2.assoc_target_guid = a1.assoc_target_guid 
                             AND a2.source_target_guid = a1.source_target_guid
                             AND mt1.target_type!='composite'
                             AND mt1.target_type!='oracle_database_group'
                             AND mt1.target_type!='host_group'
                             ) 
                           )        
                GROUP BY a1.assoc_target_guid) policy_detail                     
       WHERE mem.source_target_guid = l_target_guid
         AND mem.assoc_target_guid = tgt.target_guid
         AND mem.is_membership = 1
         AND tgt.target_guid = sev_detail.target_guid (+)
         AND tgt.target_guid = policy_detail.tguid(+)
         AND tgt.target_type = member_target_type_in
       ORDER BY tgt.type_display_name, tgt.target_name;
  END IF;

END get_member_monitoring_summary;

--
-- PURPOSE:
--   Returns a summary of monitoring details for the members of
--     a particular target list view
--
PROCEDURE get_target_list_view_summary
  (targets_in     IN MGMT_GUID_ARRAY,
   list_mode_in   IN NUMBER,
   search_target_name_in IN VARCHAR2 DEFAULT NULL,
   timeperiod_in  IN NUMBER DEFAULT MGMT_GLOBAL.G_DAY,
   summary_out OUT CURSOR_TYPE)
IS

  -- l_target_guid  mgmt_targets.target_guid%TYPE;
  l_time_difference NUMBER := 1;

BEGIN

  l_time_difference := get_time_difference(timeperiod_in);

  IF( list_mode_in = SELECT_TYPE )
  THEN
    -- use only the target types from the targets_in obj array
    IF( search_target_name_in IS NULL )
    THEN
        -- no search target name clause
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.display_name,
                 tgt.type_display_name,
                 DECODE(avail_status.cur_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail_status.cur_status) cur_status,
                 NVL(alltgt.warning_count,0)+NVL(comptgt.warning_count, 0) AS warning_count,
                 NVL(alltgt.critical_count,0)+NVL(comptgt.critical_count, 0) AS critical_count,
                 tgt.target_guid,
                 NVL(avail_status.target_status_down,0) AS target_status_down,
                 DECODE(comptgt.has_member_status,1,1,0) has_member_status
            FROM mgmt_targets tgt,
                 (SELECT cur_status,
                         target_guid,
                         target_status_down
                    FROM (SELECT DECODE(avail.current_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail.current_status) cur_status,
                                 avail.target_guid target_guid,
                                 0 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_metrics met,
                                 mgmt_targets tgt
                           WHERE met.target_type = tgt.target_type
                             AND met.metric_name=MGMT_GLOBAL.G_AVAIL_METRIC_NAME
                             AND met.metric_column=MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
                             AND tgt.target_guid=avail.target_guid)
                         UNION ALL
                         (SELECT DECODE(SUM(DECODE(avail.current_status,0,1,0)),0,1,0) cur_status,
                                 mem.source_target_guid target_guid,
                                 1 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_target_assocs mem,
                                 mgmt_targets ct,
                                 mgmt_type_properties prop
                           WHERE prop.property_name=MGMT_GLOBAL.G_DERIVED_STATUS_PROP
                             AND ct.target_type=prop.target_type
                             AND mem.source_target_guid = ct.target_guid
                             AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
                             AND avail.target_guid=mem.assoc_target_guid
                           GROUP BY mem.source_target_guid)) avail_status,
                 (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         target_guid tguid
                    FROM mgmt_current_severity
                   GROUP BY target_guid ) alltgt,
                 (SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         tm.source_target_guid tguid,
                         1 as has_member_status
                    FROM mgmt_target_assocs tm,
                         mgmt_current_severity cs
                   WHERE tm.assoc_target_guid = cs.target_guid
                     AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
                   GROUP BY tm.source_target_guid) comptgt,
                 TABLE(CAST(targets_in AS MGMT_GUID_ARRAY)) guids
           WHERE tgt.target_type = guids.name
             AND tgt.target_guid = avail_status.target_guid(+)
             AND tgt.target_guid = comptgt.tguid(+)
             AND tgt.target_guid = alltgt.tguid(+)
           ORDER BY tgt.type_display_name, tgt.target_name;
    ELSE
        -- search target name clause provided
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.display_name,
                 tgt.type_display_name,
                 DECODE(avail_status.cur_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail_status.cur_status) cur_status,
                 NVL(alltgt.warning_count,0)+NVL(comptgt.warning_count,0) AS warning_count,
                 NVL(alltgt.critical_count,0)+NVL(comptgt.critical_count,0) AS critical_count,
                 tgt.target_guid,
                 NVL(avail_status.target_status_down,0) AS target_status_down,
                 DECODE(comptgt.has_member_status,1,1,0) has_member_status
            FROM mgmt_targets tgt,
                 (SELECT cur_status,
                         target_guid,
                         target_status_down
                    FROM (SELECT DECODE(avail.current_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail.current_status) cur_status,
                                 avail.target_guid target_guid,
                                 0 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_metrics met,
                                 mgmt_targets tgt
                           WHERE met.target_type = tgt.target_type
                             AND met.metric_name=MGMT_GLOBAL.G_AVAIL_METRIC_NAME
                             AND met.metric_column=MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
                             AND tgt.target_guid=avail.target_guid)
                         UNION ALL
                         (SELECT DECODE(SUM(DECODE(avail.current_status,0,1,0)),0,1,0) cur_status,
                                 mem.source_target_guid target_guid,
                                 1 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_target_assocs mem,
                                 mgmt_targets ct,
                                 mgmt_type_properties prop
                           WHERE prop.property_name=MGMT_GLOBAL.G_DERIVED_STATUS_PROP
                             AND ct.target_type=prop.target_type
                             AND mem.source_target_guid = ct.target_guid
                             AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
                             AND avail.target_guid=mem.assoc_target_guid
                           GROUP BY mem.source_target_guid)) avail_status,
                 (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         target_guid tguid
                    FROM mgmt_current_severity
                   GROUP BY target_guid ) alltgt,
                 (SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         tm.source_target_guid tguid,
                         1 as has_member_status
                    FROM mgmt_target_assocs tm,
                         mgmt_current_severity cs
                   WHERE tm.assoc_target_guid = cs.target_guid
                     AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
                   GROUP BY tm.source_target_guid) comptgt,
                 TABLE(CAST(targets_in AS MGMT_GUID_ARRAY)) guids
           WHERE tgt.target_type = guids.name
             AND UPPER(tgt.target_name) like search_target_name_in
             AND tgt.target_guid = avail_status.target_guid(+)
             AND tgt.target_guid = comptgt.tguid(+)
             AND tgt.target_guid = alltgt.tguid(+)
           ORDER BY tgt.type_display_name, tgt.target_name;
    END IF;
  ELSE
    -- use only the target guids from the targets_in obj array
    IF( search_target_name_in IS NULL )
    THEN
        -- no search target name clause
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.display_name,
                 tgt.type_display_name,
                 DECODE(avail_status.cur_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail_status.cur_status) cur_status,
                 NVL(alltgt.warning_count,0)+NVL(comptgt.warning_count,0) AS warning_count,
                 NVL(alltgt.critical_count,0)+NVL(comptgt.critical_count,0) AS critical_count,
                 tgt.target_guid,
                 NVL(avail_status.target_status_down,0) AS target_status_down,
                 DECODE(comptgt.has_member_status,1,1,0) has_member_status
            FROM mgmt_targets tgt,
                 (SELECT cur_status,
                         target_guid,
                         target_status_down
                    FROM (SELECT DECODE(avail.current_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail.current_status) cur_status,
                                 avail.target_guid target_guid,
                                 0 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_metrics met,
                                 mgmt_targets tgt
                           WHERE met.target_type = tgt.target_type
                             AND met.metric_name=MGMT_GLOBAL.G_AVAIL_METRIC_NAME
                             AND met.metric_column=MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
                             AND tgt.target_guid=avail.target_guid)
                         UNION ALL
                         (SELECT DECODE(SUM(DECODE(avail.current_status,0,1,0)),0,1,0) cur_status,
                                 mem.source_target_guid target_guid,
                                 1 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_target_assocs mem,
                                 mgmt_targets ct,
                                 mgmt_type_properties prop
                           WHERE prop.property_name=MGMT_GLOBAL.G_DERIVED_STATUS_PROP
                             AND ct.target_type=prop.target_type
                             AND mem.source_target_guid = ct.target_guid
                             AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
                             AND avail.target_guid=mem.assoc_target_guid
                           GROUP BY mem.source_target_guid)) avail_status,
                 (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         target_guid tguid
                    FROM mgmt_current_severity
                   GROUP BY target_guid ) alltgt,
                 (SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         tm.source_target_guid tguid,
                         1 as has_member_status
                    FROM mgmt_target_assocs tm,
                         mgmt_current_severity cs
                   WHERE tm.assoc_target_guid = cs.target_guid
                     AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
                   GROUP BY tm.source_target_guid) comptgt,
                 TABLE(CAST(targets_in AS MGMT_GUID_ARRAY)) guids
           WHERE tgt.target_guid = guids.guid
             AND tgt.target_guid = avail_status.target_guid(+)
             AND tgt.target_guid = comptgt.tguid(+)
             AND tgt.target_guid = alltgt.tguid(+)
           ORDER BY tgt.type_display_name, tgt.target_name;
    ELSE
        -- search target name clause provided
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.display_name,
                 tgt.type_display_name,
                 DECODE(avail_status.cur_status, NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN, avail_status.cur_status) cur_status,
                 NVL(alltgt.warning_count,0)+NVL(comptgt.warning_count,0) AS warning_count,
                 NVL(alltgt.critical_count,0)+NVL(comptgt.critical_count,0) AS critical_count,
                 tgt.target_guid,
                 NVL(avail_status.target_status_down,0) AS target_status_down,
                 DECODE(comptgt.has_member_status,1,1,0) has_member_status
            FROM mgmt_targets tgt,
                 (SELECT cur_status,
                         target_guid,
                         target_status_down
                    FROM (SELECT DECODE(avail.current_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail.current_status) cur_status,
                                 avail.target_guid target_guid,
                                 0 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_metrics met,
                                 mgmt_targets tgt
                           WHERE met.target_type = tgt.target_type
                             AND met.metric_name=MGMT_GLOBAL.G_AVAIL_METRIC_NAME
                             AND met.metric_column=MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
                             AND tgt.target_guid=avail.target_guid)
                         UNION ALL
                         (SELECT DECODE(SUM(DECODE(avail.current_status,0,1,0)),0,1,0) cur_status,
                                 mem.source_target_guid target_guid,
                                 1 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_target_assocs mem,
                                 mgmt_targets ct,
                                 mgmt_type_properties prop
                           WHERE prop.property_name=MGMT_GLOBAL.G_DERIVED_STATUS_PROP
                             AND ct.target_type=prop.target_type
                             AND mem.source_target_guid = ct.target_guid
                             AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
                             AND avail.target_guid=mem.assoc_target_guid
                           GROUP BY mem.source_target_guid)) avail_status,
                 (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         target_guid tguid
                    FROM mgmt_current_severity
                   GROUP BY target_guid ) alltgt,
                 (SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         tm.source_target_guid tguid,
                         1 as has_member_status
                    FROM mgmt_target_assocs tm,
                         mgmt_current_severity cs
                   WHERE tm.assoc_target_guid = cs.target_guid
                     AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
                   GROUP BY tm.source_target_guid) comptgt,
                 TABLE(CAST(targets_in AS MGMT_GUID_ARRAY)) guids
           WHERE tgt.target_guid = guids.guid
             AND UPPER(tgt.target_name) like search_target_name_in
             AND tgt.target_guid = avail_status.target_guid(+)
             AND tgt.target_guid = comptgt.tguid(+)
             AND tgt.target_guid = alltgt.tguid(+)
           ORDER BY tgt.type_display_name, tgt.target_name;
    END IF;
  END IF;

END get_target_list_view_summary;

--
-- PURPOSE:
--   Returns a summary of monitoring details for a target subtab
--
PROCEDURE get_target_subtab_summary
  (target_type_in IN VARCHAR2,
   targets_in IN MGMT_GUID_ARRAY,
   search_target_name_in IN VARCHAR2 DEFAULT NULL,
   timeperiod_in  IN NUMBER DEFAULT MGMT_GLOBAL.G_DAY,
   summary_out OUT CURSOR_TYPE)
IS

  -- l_target_guid  mgmt_targets.target_guid%TYPE;
  l_time_difference NUMBER := 1;

BEGIN

  l_time_difference := get_time_difference(timeperiod_in);

  IF (targets_in IS NOT NULL AND targets_in.COUNT > 0)
  THEN
    -- use the target guids from the targets_in obj array
    IF( search_target_name_in IS NULL )
    THEN
        -- no search target name clause
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.display_name,
                 tgt.type_display_name,
                 DECODE(avail_status.cur_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail_status.cur_status) cur_status,
                 NVL(alltgt.warning_count,0)+NVL(comptgt.warning_count,0) AS warning_count,
                 NVL(alltgt.critical_count,0)+NVL(comptgt.critical_count,0) AS critical_count,
                 tgt.target_guid,
                 NVL(avail_status.target_status_down,0) AS target_status_down,
                 DECODE(comptgt.has_member_status,1,1,0) has_member_status
            FROM mgmt_targets tgt,
                 (SELECT cur_status,
                         target_guid,
                         target_status_down
                    FROM (SELECT DECODE(avail.current_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail.current_status) cur_status,
                                 avail.target_guid target_guid,
                                 0 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_metrics met,
                                 mgmt_targets tgt
                           WHERE met.target_type = tgt.target_type
                             AND met.metric_name=MGMT_GLOBAL.G_AVAIL_METRIC_NAME
                             AND met.metric_column=MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
                             AND tgt.target_guid=avail.target_guid)
                         UNION ALL
                         (SELECT DECODE(SUM(DECODE(avail.current_status,0,1,0)),0,1,0) cur_status,
                                 mem.source_target_guid target_guid,
                                 1 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_target_assocs mem,
                                 mgmt_targets ct,
                                 mgmt_type_properties prop
                           WHERE prop.property_name=MGMT_GLOBAL.G_DERIVED_STATUS_PROP
                             AND ct.target_type=prop.target_type
                             AND mem.source_target_guid = ct.target_guid
                             AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
                             AND avail.target_guid=mem.assoc_target_guid
                           GROUP BY mem.source_target_guid)) avail_status,
                 (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         target_guid tguid
                    FROM mgmt_current_severity
                   GROUP BY target_guid ) alltgt,
                 (SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         tm.source_target_guid tguid,
                         1 as has_member_status
                    FROM mgmt_target_assocs tm,
                         mgmt_current_severity cs
                   WHERE tm.assoc_target_guid = cs.target_guid
                     AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
                   GROUP BY tm.source_target_guid) comptgt,
                 TABLE(CAST(targets_in AS MGMT_GUID_ARRAY)) guids
           WHERE tgt.target_type = target_type_in
             AND tgt.target_guid = guids.guid
             AND tgt.target_guid = avail_status.target_guid(+)
             AND tgt.target_guid = comptgt.tguid(+)
             AND tgt.target_guid = alltgt.tguid(+)
           ORDER BY tgt.type_display_name, tgt.target_name;
    ELSE
        -- search target name clause provided
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.display_name,
                 tgt.type_display_name,
                 DECODE(avail_status.cur_status, NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN, avail_status.cur_status) cur_status,
                 NVL(alltgt.warning_count,0)+NVL(comptgt.warning_count,0) AS warning_count,
                 NVL(alltgt.critical_count,0)+NVL(comptgt.critical_count,0) AS critical_count,
                 tgt.target_guid,
                 NVL(avail_status.target_status_down,0) AS target_status_down,
                 DECODE(comptgt.has_member_status,1,1,0) has_member_status
            FROM mgmt_targets tgt,
                 (SELECT cur_status,
                         target_guid,
                         target_status_down
                    FROM (SELECT DECODE(avail.current_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail.current_status) cur_status,
                                 avail.target_guid target_guid,
                                 0 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_metrics met,
                                 mgmt_targets tgt
                           WHERE met.target_type = tgt.target_type
                             AND met.metric_name=MGMT_GLOBAL.G_AVAIL_METRIC_NAME
                             AND met.metric_column=MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
                             AND tgt.target_guid=avail.target_guid)
                         UNION ALL
                         (SELECT DECODE(SUM(DECODE(avail.current_status,0,1,0)),0,1,0) cur_status,
                                 mem.source_target_guid target_guid,
                                 1 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_target_assocs mem,
                                 mgmt_targets ct,
                                 mgmt_type_properties prop
                           WHERE prop.property_name=MGMT_GLOBAL.G_DERIVED_STATUS_PROP
                             AND ct.target_type=prop.target_type
                             AND mem.source_target_guid = ct.target_guid
                             AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
                             AND avail.target_guid=mem.assoc_target_guid
                           GROUP BY mem.source_target_guid)) avail_status,
                 (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         target_guid tguid
                    FROM mgmt_current_severity
                   GROUP BY target_guid ) alltgt,
                 (SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         tm.source_target_guid tguid,
                         1 as has_member_status
                    FROM mgmt_target_assocs tm,
                         mgmt_current_severity cs
                   WHERE tm.assoc_target_guid = cs.target_guid
                     AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
                   GROUP BY tm.source_target_guid) comptgt,
                 TABLE(CAST(targets_in AS MGMT_GUID_ARRAY)) guids
           WHERE tgt.target_type = target_type_in
             AND tgt.target_guid = guids.guid
             AND UPPER(tgt.target_name) like search_target_name_in
             AND tgt.target_guid = avail_status.target_guid(+)
             AND tgt.target_guid = comptgt.tguid(+)
             AND tgt.target_guid = alltgt.tguid(+)
           ORDER BY tgt.type_display_name, tgt.target_name;
    END IF;
  ELSE
    -- do not use the targets_in obj array
    IF( search_target_name_in IS NULL )
    THEN
        -- no search target name clause
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.display_name,
                 tgt.type_display_name,
                 DECODE(avail_status.cur_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail_status.cur_status) cur_status,
                 NVL(alltgt.warning_count,0)+NVL(comptgt.warning_count,0) AS warning_count,
                 NVL(alltgt.critical_count,0)+NVL(comptgt.critical_count,0) AS critical_count,
                 tgt.target_guid,
                 NVL(avail_status.target_status_down, 0) AS target_status_down,
                 DECODE(comptgt.has_member_status,1,1,0) has_member_status
            FROM mgmt_targets tgt,
                 (SELECT cur_status,
                         target_guid,
                         target_status_down
                    FROM (SELECT DECODE(avail.current_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail.current_status) cur_status,
                                 avail.target_guid target_guid,
                                 0 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_metrics met,
                                 mgmt_targets tgt
                           WHERE met.target_type = tgt.target_type
                             AND met.metric_name=MGMT_GLOBAL.G_AVAIL_METRIC_NAME
                             AND met.metric_column=MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
                             AND tgt.target_guid=avail.target_guid)
                         UNION ALL
                         (SELECT DECODE(SUM(DECODE(avail.current_status,0,1,0)),0,1,0) cur_status,
                                 mem.source_target_guid target_guid,
                                 1 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_target_assocs mem,
                                 mgmt_targets ct,
                                 mgmt_type_properties prop
                           WHERE prop.property_name=MGMT_GLOBAL.G_DERIVED_STATUS_PROP
                             AND ct.target_type=prop.target_type
                             AND mem.source_target_guid = ct.target_guid
                             AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
                             AND avail.target_guid=mem.assoc_target_guid
                           GROUP BY mem.source_target_guid)) avail_status,
                 (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         target_guid tguid
                    FROM mgmt_current_severity
                   GROUP BY target_guid ) alltgt,
                 (SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         tm.source_target_guid tguid,
                         1 as has_member_status
                    FROM mgmt_target_assocs tm,
                         mgmt_current_severity cs
                   WHERE tm.assoc_target_guid = cs.target_guid
                     AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
                   GROUP BY tm.source_target_guid) comptgt
           WHERE tgt.target_type = target_type_in
             AND tgt.target_guid = avail_status.target_guid(+)
             AND tgt.target_guid = comptgt.tguid(+)
             AND tgt.target_guid = alltgt.tguid(+)
           ORDER BY tgt.type_display_name, tgt.target_name;
    ELSE
        -- search target name clause provided
        OPEN summary_out FOR
          SELECT tgt.target_name,
                 tgt.target_type,
                 tgt.display_name,
                 tgt.type_display_name,
                 DECODE(avail_status.cur_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail_status.cur_status) cur_status,
                 NVL(alltgt.warning_count,0)+NVL(comptgt.warning_count,0) AS warning_count,
                 NVL(alltgt.critical_count,0)+NVL(comptgt.critical_count,0) AS critical_count,
                 tgt.target_guid,
                 NVL(avail_status.target_status_down,0) AS target_status_down,
                 DECODE(comptgt.has_member_status,1,1,0) has_member_status
            FROM mgmt_targets tgt,
                 (SELECT cur_status,
                         target_guid,
                         target_status_down
                    FROM (SELECT DECODE(avail.current_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail.current_status) cur_status,
                                 avail.target_guid target_guid,
                                 0 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_metrics met,
                                 mgmt_targets tgt
                           WHERE met.target_type = tgt.target_type
                             AND met.metric_name=MGMT_GLOBAL.G_AVAIL_METRIC_NAME
                             AND met.metric_column=MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
                             AND tgt.target_guid=avail.target_guid)
                         UNION ALL
                         (SELECT DECODE(SUM(DECODE(avail.current_status,0,1,0)),0,1,0) cur_status,
                                 mem.source_target_guid target_guid,
                                 1 AS target_status_down
                            FROM mgmt_current_availability avail,
                                 mgmt_target_assocs mem,
                                 mgmt_targets ct,
                                 mgmt_type_properties prop
                           WHERE prop.property_name=MGMT_GLOBAL.G_DERIVED_STATUS_PROP
                             AND ct.target_type=prop.target_type
                             AND mem.source_target_guid = ct.target_guid
                             AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid
                             AND avail.target_guid=mem.assoc_target_guid
                           GROUP BY mem.source_target_guid)) avail_status,
                 (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         target_guid tguid
                    FROM mgmt_current_severity
                   GROUP BY target_guid ) alltgt,
                 (SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count,
                         SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count,
                         tm.source_target_guid tguid,
                         1 as has_member_status
                    FROM mgmt_target_assocs tm,
                         mgmt_current_severity cs
                   WHERE tm.assoc_target_guid = cs.target_guid
                     AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
                   GROUP BY tm.source_target_guid) comptgt
           WHERE tgt.target_type = target_type_in
             AND UPPER(tgt.target_name) like search_target_name_in
             AND tgt.target_guid = avail_status.target_guid(+)
             AND tgt.target_guid = comptgt.tguid(+)
             AND tgt.target_guid = alltgt.tguid(+)
           ORDER BY tgt.type_display_name, tgt.target_name;
    END IF;
  END IF;

END get_target_subtab_summary;

--
-- PROCEDURE: get_traffic_light_info
--
-- PURPOSE:
--   Returns a summary of traffic light related data for a given target name,
--   target type
--
PROCEDURE get_traffic_light_info
  (target_name_in  IN VARCHAR2,
   target_type_in  IN VARCHAR2,
   timeperiod_in IN NUMBER DEFAULT MGMT_GLOBAL.G_DAY,
   avail_pct_out OUT NUMBER,
   current_status_out OUT NUMBER)
IS

  l_target_guid mgmt_targets.target_guid%TYPE;
  l_time_difference NUMBER := 1;

BEGIN

  -- initialize OUT parameters
  avail_pct_out := 0;
  current_status_out := 0;

  l_time_difference := get_time_difference(timeperiod_in);

  -- may throw a TARGET_DOES_NOT_EXIST EXCEPTION
  l_target_guid := get_target_guid(target_name_in, target_type_in);

  avail_pct_out := mgmt_target.get_avail_pct_value(l_target_guid, l_time_difference);
  current_status_out := mgmt_target.get_avail_current_status(l_target_guid);

END get_traffic_light_info;

FUNCTION get_type_property(p_target_type VARCHAR2,
                           p_property_name VARCHAR2,
                           p_default VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
l_value MGMT_TYPE_PROPERTIES.property_value%TYPE;
BEGIN

    SELECT property_value INTO l_value 
      FROM MGMT_TYPE_PROPERTIES 
     WHERE target_type=p_target_type 
       AND upper(property_name)=upper(p_property_name);

    RETURN l_value;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN p_default;

END;

-- Check that the target type is composite: throw an exception
-- if not. Internal method
PROCEDURE check_composite(p_target_type VARCHAR2) IS
l_is_composite NUMBER := 0;
BEGIN
    l_is_composite := get_type_property(p_target_type, 
                                        MGMT_GLOBAL.G_IS_COMPOSITE_PROP,
                                        '0');

    IF l_is_composite=0 THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
        'Membership information cannot be provided for a target not designated as composite');
    END IF;

END;

-- Check that the specified members can all be added to the group
-- whose type is p_group_type. This enforces that only targets of
-- a pre-defined type can be added to typed groups.
PROCEDURE check_group_members(p_group_type VARCHAR2,
                              p_members SMP_EMD_NVPAIR_ARRAY) IS
l_member_target_type VARCHAR2(1024);
BEGIN
    l_member_target_type := get_type_property(p_group_type, 
                                              MGMT_GLOBAL.G_MEMBER_TYPE_PROP, 
                          null);

    IF l_member_target_type IS NULL THEN
        -- This group is not a typed group:
        -- Any kind of target can be added to this group
        RETURN;
    ELSE
      IF p_members IS NOT NULL THEN
        -- This group is a typed group: the only targets that can
        -- be added to this group are targets of type l_member_target_type
        FOR i in 1..p_members.count LOOP
            IF p_members(i).value != l_member_target_type THEN
                raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
                'Cannot add target ' || p_members(i).name || ':' ||
                 p_members(i).value ||
                ' to typed group of base type ' || l_member_target_type);
            END IF;
        END LOOP;
      END IF;
    END IF;
END;

-- Check that the current user has the specified privilege on each one
-- of the specified member targets
PROCEDURE check_member_targets_priv(p_member_targets SMP_EMD_NVPAIR_ARRAY,
                                    p_privilege VARCHAR2,
                                    p_user VARCHAR2) IS
BEGIN
    IF p_member_targets IS NOT NULL AND
       p_member_targets.count > 0 THEN
        -- The caller must have the requested privilege over all member targets
        FOR i IN 1..p_member_targets.count LOOP

            IF MGMT_USER.has_priv(p_user,
                                  p_privilege,
                                  p_member_targets(i).name,
                                  p_member_targets(i).value) = 0 THEN
                raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
                'The current user must have ' || p_privilege || ' privilege over all member targets');
            END IF;
        END LOOP;

    END IF;
END;

-- Generate the target guid for the specified target
FUNCTION generate_target_guid(p_target_name IN VARCHAR2,
                              p_target_type IN VARCHAR2)
    RETURN RAW IS
BEGIN
    RETURN DBMS_OBFUSCATION_TOOLKIT.md5(
             input => UTL_RAW.cast_to_raw(p_target_type ||
                            ';'|| p_target_name));
END;


-- Generate the metric guid for the specified metric
FUNCTION generate_metric_guid(p_target_type IN VARCHAR2,
                              p_metric_name IN VARCHAR2)
    RETURN RAW IS
BEGIN
    RETURN DBMS_OBFUSCATION_TOOLKIT.md5(
             input => UTL_RAW.cast_to_raw(p_target_type ||
                            ';'|| p_metric_name));
END;


-- Generate the metric guid for the specified metric
FUNCTION generate_metric_column_guid(p_target_type IN VARCHAR2,
                                     p_metric_name IN VARCHAR2,
                                     p_metric_column IN VARCHAR2)
    RETURN RAW IS
BEGIN
    RETURN DBMS_OBFUSCATION_TOOLKIT.md5(
             input => UTL_RAW.cast_to_raw(p_target_type ||
                            ';'|| p_metric_name ||';'|| p_metric_column));
END;




-- Checks whether the given string is a URL or not
-- by checking whether it starts with 'http:' or 'https:'
FUNCTION is_url(p_test_string IN VARCHAR2)
    RETURN BOOLEAN IS
  l_sub_string VARCHAR2(32);
  l_ret_val BOOLEAN := FALSE;
BEGIN
  IF (length(p_test_string) >= 5) THEN
    l_sub_string := SUBSTR(p_test_string, 1, 5);
    IF (UPPER(l_sub_string) = 'HTTP:') THEN
      l_ret_val := TRUE;
    END IF;

  ELSIF (length(p_test_string) >= 6) THEN
    l_sub_string := SUBSTR(p_test_string, 1, 6);
    IF (UPPER(l_sub_string) = 'HTTPS:') THEN
      l_ret_val := TRUE;
    END IF;

  END IF;
  RETURN l_ret_val;
END;

-- Return all the groups of the specified type that
-- the user has CREATE_TARGET_IN_GROUP privilege over
-- Append the groups to the array p_groups
PROCEDURE get_groups_of_type_ct_priv(p_group_type VARCHAR2, 
                                     p_groups IN OUT SMP_EMD_NVPAIR_ARRAY) IS
TYPE DATE_ARRAY IS TABLE OF DATE;

l_group_guids MGMT_USER_GUID_ARRAY;
l_group_names SMP_EMD_STRING_ARRAY;
l_group_types SMP_EMD_STRING_ARRAY;

-- We need the rest only because the cursor returns them
l_load_times DATE_ARRAY;
l_tz_deltas SMP_EMD_INTEGER_ARRAY;
l_display_names SMP_EMD_STRING_ARRAY;
l_type_display_names SMP_EMD_STRING_ARRAY;
l_service_types SMP_EMD_STRING_ARRAY;
l_host_names SMP_EMD_STRING_ARRAY;
l_emd_urls SMP_EMD_STRING_ARRAY;
l_last_load_times DATE_ARRAY;
l_is_groups SMP_EMD_STRING_ARRAY;
l_broken_reasons SMP_EMD_INTEGER_ARRAY;
l_broken_string SMP_EMD_STRING_ARRAY;
l_rt_load_times DATE_ARRAY;
l_tz_regions SMP_EMD_STRING_ARRAY;

l_group_cursor CURSOR_TYPE;
l_index NUMBER;
BEGIN
    l_group_cursor := MGMT_USER.get_targets(p_group_type,
                                          MGMT_USER.CREATE_TARGET_IN_GROUP);

    FETCH l_group_cursor BULK COLLECT INTO 
        l_group_guids,
        l_group_names,
        l_group_types,
        l_load_times,
        l_tz_deltas,
        l_display_names,
        l_type_display_names,
        l_service_types,
        l_host_names,
        l_emd_urls,
        l_last_load_times,
        l_is_groups,
        l_broken_reasons,
        l_broken_string,
        l_rt_load_times,
	l_tz_regions;

    CLOSE l_group_cursor;

    IF l_group_guids IS NOT NULL AND l_group_guids.COUNT > 0 THEN
        IF p_groups IS NULL THEN
            p_groups := SMP_EMD_NVPAIR_ARRAY();
            l_index := 1;     
        ELSE
            l_index := p_groups.COUNT + 1;
        END IF;

        p_groups.extend(l_group_guids.COUNT);

        FOR i IN 1..l_group_guids.COUNT LOOP
            p_groups(l_index) := SMP_EMD_NVPAIR(l_group_names(i),
                                                l_group_types(i));
            l_index := l_index+1;
        END LOOP;
    END IF;

END;

-- Return all applicable groups (for the specified target type)
-- the user has CREATE_TARGET_IN_GROUP privilege over
PROCEDURE get_create_target_groups(p_target_type VARCHAR2,
                                   p_groups_out OUT SMP_EMD_NVPAIR_ARRAY) IS
l_group_types SMP_EMD_STRING_ARRAY;

l_actual_group_types SMP_EMD_STRING_ARRAY;
l_member_target_type MGMT_TARGETS.target_type%TYPE;

BEGIN
    -- Get all group types that qualify 
    SELECT target_type BULK COLLECT INTO l_group_types
    FROM   MGMT_TYPE_PROPERTIES
    WHERE  property_name=MGMT_GLOBAL.G_IS_GROUP_PROP
    AND    property_value=1;

    IF l_group_types IS NULL THEN
        -- Shouldn't happen
        RETURN;
    END IF;

    l_actual_group_types := SMP_EMD_STRING_ARRAY();

    FOR i IN 1..l_group_types.COUNT LOOP
        l_member_target_type := get_type_property(l_group_types(i),
                                              MGMT_GLOBAL.G_MEMBER_TYPE_PROP, 
                                              null);

        IF l_member_target_type IS NULL OR
           l_member_target_type=p_target_type THEN
            l_actual_group_types.extend(1);
            l_actual_group_types(l_actual_group_types.COUNT) := 
                    l_group_types(i);
        END IF;
    END LOOP;

    -- Now get all groups corresponding to each of the
    -- applicable types
    FOR i IN 1..l_actual_group_types.COUNT LOOP
        get_groups_of_type_ct_priv(l_actual_group_types(i), p_groups_out);
    END LOOP;
END;

PROCEDURE add_target(p_target_name IN VARCHAR2,
                     p_target_type IN VARCHAR2,
                     p_host_name   IN VARCHAR2,
                     p_properties IN MGMT_TARGET_PROPERTY_LIST,
                     p_groups IN SMP_EMD_NVPAIR_ARRAY,
                     p_member_targets IN SMP_EMD_NVPAIR_ARRAY DEFAULT NULL,
                     p_monitoring_credentials IN SMP_EMD_NVPAIR_ARRAY
                                              DEFAULT NULL,
                     p_target_display_name IN VARCHAR2 DEFAULT NULL,
                     p_agent_url IN VARCHAR2 DEFAULT NULL,
                     p_tz_rgn    IN VARCHAR2 DEFAULT NULL, 
                     p_mon_mode  IN NUMBER 
                                 DEFAULT MGMT_GLOBAL.G_MON_MODE_DEFAULT,
                     p_type_meta_ver      IN VARCHAR2 DEFAULT '1.0',
                     p_category_prop_1    IN VARCHAR2 DEFAULT ' ',
                     p_category_prop_2    IN VARCHAR2 DEFAULT ' ',
                     p_category_prop_3    IN VARCHAR2 DEFAULT ' ',
                     p_category_prop_4    IN VARCHAR2 DEFAULT ' ',
                     p_category_prop_5    IN VARCHAR2 DEFAULT ' ',
                     p_repository_only_target IN NUMBER DEFAULT 0,
                     p_target_owner       IN VARCHAR2 DEFAULT NULL) IS
  l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user; 
  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_target_display_name MGMT_TARGETS.display_name%TYPE;
  l_group_guid MGMT_TARGETS.target_guid%TYPE;
  l_cnt NUMBER := 0;
  
  l_groups SMP_EMD_NVPAIR_ARRAY := null;

  l_target_name MGMT_TARGETS.target_name%TYPE:= trim(p_target_name);
  l_target_type MGMT_TARGETS.target_type%TYPE:= trim(p_target_type);

BEGIN

    -- Check whether target has some specialzed method to 
    -- add this type of target
    EM_TARGET.check_is_def_target_factory(l_target_type);

    IF p_groups IS NOT NULL AND p_groups.count > 0 THEN
      -- Go through each of the specified groups. Does the user have
      -- CREATE_TARGET_IN_GROUP privilege over each of them? Note that
      -- this is sufficient: the user does not need to additionally
      -- have the CREATE_TARGET system privilege
      FOR i IN 1..p_groups.COUNT LOOP
        IF MGMT_USER.has_priv(l_current_user,
                              'CREATE_TARGET_IN_GROUP',
                              p_groups(i).name,
                              p_groups(i).value) = 0 THEN
          raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
                'CREATE_TARGET_IN_GROUP privilege is required on all the specified groups');
        END IF;
      END LOOP;
      
      l_groups := p_groups;
    ELSE
      -- If no groups have been specified, then the user must have
      -- the CREATE_TARGET privilege
      IF MGMT_USER.has_priv(l_current_user, 'CREATE_TARGET') = 0 THEN
        raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
            'CREATE_TARGET privilege is required to create this target');
      END IF;

      -- The target must be added to all groups that the user has
      -- CREATE_TARGET_IN_GROUP privilege on. Get this set of
      -- groups. Do not this do for "SUPER USER" because super
      -- case has CREATE_TARGET_IN_GROUP on all groups.
      IF (MGMT_USER.has_priv(l_current_user, 'SUPER_USER') = 0) THEN
         get_create_target_groups(l_target_type, l_groups);
      END IF;
    END IF;

    -- If member targets have been provided, then the target type
    -- must have the is_composite or is_aggregate property set
    IF p_member_targets IS NOT NULL 
    THEN

      EM_TARGET.add_aggregate_target(p_target_guid => l_target_guid,
                           p_target_name => l_target_name,
                           p_target_type => l_target_type,
                           p_host_name   => p_host_name,
                           p_properties  => p_properties,
                           p_aggregates  => l_groups,
                           p_monitoring_credentials => p_monitoring_credentials,
                           p_target_display_name => p_target_display_name,
                           p_agent_url => p_agent_url,
                           p_tz_rgn    => p_tz_rgn,
                           p_mon_mode  => p_mon_mode,
                           p_type_meta_ver  => p_type_meta_ver,
                           p_category_prop_1 => p_category_prop_1,
                           p_category_prop_2 => p_category_prop_2,
                           p_category_prop_3 => p_category_prop_3,
                           p_category_prop_4 => p_category_prop_4,
                           p_category_prop_5 => p_category_prop_5,
                           p_repository_only_target => p_repository_only_target,
                           p_target_owner => p_target_owner,
                           p_member_targets => p_member_targets,
                           p_required_member_priv => MGMT_USER.FULL_TARGET
                         );

    ELSE
      EM_TARGET.add_target(p_target_guid => l_target_guid,
                           p_target_name => l_target_name,
                           p_target_type => l_target_type,
                           p_host_name   => p_host_name,
                           p_properties  => p_properties,
                           p_aggregates  => l_groups,
                           p_monitoring_credentials => p_monitoring_credentials,
                           p_target_display_name => p_target_display_name,
                           p_agent_url => p_agent_url,
                           p_tz_rgn    => p_tz_rgn,
                           p_mon_mode  => p_mon_mode,
                           p_type_meta_ver  => p_type_meta_ver,
                           p_category_prop_1 => p_category_prop_1,
                           p_category_prop_2 => p_category_prop_2,
                           p_category_prop_3 => p_category_prop_3,
                           p_category_prop_4 => p_category_prop_4,
                           p_category_prop_5 => p_category_prop_5,
                           p_repository_only_target => p_repository_only_target,
                           p_target_owner => p_target_owner
                         );
    END IF;

END add_target;

-- Move a target from one emd to another
PROCEDURE move_target(p_target_name IN VARCHAR2,
                      p_target_type IN VARCHAR2,
                      p_new_emd_url IN VARCHAR2) IS

l_last_emd_url MGMT_TARGETS.emd_url%TYPE;
l_target_guid MGMT_TARGETS.target_guid%TYPE;
l_timezone_region MGMT_TARGETS.timezone_region%TYPE;
l_resolved_time MGMT_DUPLICATE_TARGETS.resolved_time%TYPE;
BEGIN
    BEGIN
        SELECT emd_url, target_guid, timezone_region INTO 
              l_last_emd_url, l_target_guid, l_timezone_region
            FROM MGMT_TARGETS WHERE
                target_name=p_target_name AND
                target_type=p_target_type;

    EXCEPTION
         WHEN NO_DATA_FOUND THEN
             raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
                                     'Target ' || p_target_name || ':' ||
                                     p_target_type || ' does not exist');
    END;

    -- The new emd url cannot be the same as the old one. If it is,
    -- then raise an exception
    IF p_new_emd_url = l_last_emd_url THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
          'The new emd url is the same as the old emd url');
    END IF;

    -- Since updates on the targets table are disabled, we would need to
    -- first insert a row into the MGMT_CHANGE_AGENT_URL table
    emd_loader.set_emd_url_change(l_last_emd_url, p_new_emd_url);

    UPDATE MGMT_TARGETS SET emd_url=p_new_emd_url WHERE
        target_guid=l_target_guid;

    -- Resolve any duplicate reference to this target on the new emd
    UPDATE MGMT_DUPLICATE_TARGETS SET 
        resolved_time=MGMT_GLOBAL.sysdate_tzrgn(timezone_region) WHERE
            target_guid=l_target_guid AND
            duplicate_emd_url=p_new_emd_url;
    
    -- Mark the target with the old url as duplicate, so that
    -- we reject any further data sent for that target from
    -- the old agent
    BEGIN
        INSERT INTO MGMT_DUPLICATE_TARGETS(target_name, target_type, 
             target_guid, duplicate_emd_url, timezone_region, 
             detection_time, resolved_time) VALUES
         (p_target_name, p_target_type, l_target_guid, l_last_emd_url,
          l_timezone_region, MGMT_GLOBAL.sysdate_tzrgn(l_timezone_region),
          null);
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            SELECT resolved_time INTO l_resolved_time 
               FROM MGMT_DUPLICATE_TARGETS WHERE
                   target_guid=l_target_guid AND
                   duplicate_emd_url=l_last_emd_url;

            IF l_resolved_time IS NULL THEN
                RAISE;
            ELSE        
                UPDATE MGMT_DUPLICATE_TARGETS SET 
                    detection_time=MGMT_GLOBAL.sysdate_tzrgn(l_timezone_region),
                    resolved_time=null WHERE 
                        target_guid=l_target_guid AND
                       duplicate_emd_url=l_last_emd_url;
            END IF;
    END;
    
    emd_loader.clear_emd_url_change(l_last_emd_url, p_new_emd_url);

END;

PROCEDURE modify_target(p_target_name IN VARCHAR2,
                        p_target_type IN VARCHAR2,
                        p_properties IN MGMT_TARGET_PROPERTY_LIST,
                        p_member_targets_to_add IN SMP_EMD_NVPAIR_ARRAY DEFAULT NULL,
                        p_member_targets_to_remove IN SMP_EMD_NVPAIR_ARRAY DEFAULT NULL,
                        p_monitoring_credentials IN SMP_EMD_NVPAIR_ARRAY DEFAULT NULL,
                        p_target_display_name IN VARCHAR2 DEFAULT NULL,
                        p_propagate_to_agent IN NUMBER DEFAULT 0,
                        p_target_owner       IN VARCHAR2 DEFAULT NULL) IS

l_target_guid MGMT_TARGETS.target_guid%TYPE;
BEGIN

    -- Check whether target has some specialzed method to 
    -- add this type of target
    EM_TARGET.check_is_def_target_factory(p_target_type);

    -- Add/remove the specified targets
    IF p_member_targets_to_add IS NOT NULL OR
       p_member_targets_to_remove IS NOT NULL 
    THEN
      -- Check that the user has full privilege over all targets being added...
      --IF p_target_type <> MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE
      --THEN
      --  check_member_targets_priv(p_member_targets_to_add,
      --                            MGMT_USER.FULL_TARGET,
      --                            MGMT_USER.get_current_em_user);
      --END IF;

      EM_TARGET.modify_aggregate_target(p_target_guid => l_target_guid,
                                        p_target_name => p_target_name,
                                        p_target_type => p_target_type,
                                        p_properties  => p_properties,
                                        p_monitoring_credentials => p_monitoring_credentials,
                                        p_target_display_name => p_target_display_name,
                                        p_target_owner => p_target_owner,
                                        p_propagate_to_agent => p_propagate_to_agent,
                                        p_required_target_priv => MGMT_USER.OPERATOR_TARGET,
                                        p_member_targets_to_add => p_member_targets_to_add,
                                        p_required_member_priv => MGMT_USER.FULL_TARGET,
                                        p_member_targets_to_remove => p_member_targets_to_remove                                                        );
    ELSE 
      EM_TARGET.modify_target(p_target_guid => l_target_guid,
                              p_target_name => p_target_name,
                              p_target_type => p_target_type,
                              p_properties  => p_properties,
                              p_monitoring_credentials => p_monitoring_credentials,
                              p_target_display_name => p_target_display_name,
                              p_target_owner => p_target_owner,
                              p_propagate_to_agent => p_propagate_to_agent,
                              p_required_target_priv => MGMT_USER.OPERATOR_TARGET
                             );
    END IF;
    
END;


PROCEDURE add_group(p_group_name IN VARCHAR2,
                    p_group_type VARCHAR2,
                    p_member_targets IN SMP_EMD_NVPAIR_ARRAY DEFAULT NULL,
                    p_group_owner IN VARCHAR2 DEFAULT NULL,
                    p_timezone_rgn IN VARCHAR2 DEFAULT NULL ) IS
BEGIN
  EM_GROUP.create_group( p_group_name => p_group_name,
                         p_group_type => p_group_type,
                         p_member_targets => p_member_targets,
                         p_group_owner => p_group_owner,
                         p_timezone_rgn => p_timezone_rgn
                       );
END add_group;

-- The external modify_group procedure
PROCEDURE modify_group(p_group_name IN VARCHAR2,
                       p_group_type IN VARCHAR2,
                       p_targets_to_add IN SMP_EMD_NVPAIR_ARRAY,
                       p_targets_to_remove IN SMP_EMD_NVPAIR_ARRAY,
                       p_group_owner IN VARCHAR2 DEFAULT NULL) IS
BEGIN
  EM_GROUP.modify_group(p_group_name => p_group_name,
                        p_group_type => p_group_type,
                        p_targets_to_add => p_targets_to_add,
                        p_targets_to_remove => p_targets_to_remove,
                        p_group_owner => p_group_owner
                       );
END;

PROCEDURE get_target_info_internal(p_target_guid IN RAW,
                          p_properties_out OUT CURSOR_TYPE,
                          p_compositeinfo_out OUT CURSOR_TYPE,
                          p_monitor_creds_out OUT CURSOR_TYPE) IS

l_cred_guid RAW(16);
BEGIN

    OPEN p_properties_out FOR
        SELECT property_name, property_value FROM
            MGMT_TARGET_PROPERTIES WHERE
            target_guid=p_target_guid AND
            property_type='INSTANCE';

    OPEN p_compositeinfo_out FOR
        SELECT composite_target_name, composite_target_type,
            association FROM
            MGMT_TARGET_MEMBERSHIPS m, MGMT_TARGETS t WHERE
            m.member_target_guid=p_target_guid AND
            t.target_guid=m.composite_target_guid AND
            t.is_group=0;

    -- See if there are any monitoring credentials
    -- stored for the target
    BEGIN
        SELECT credential_guid INTO l_cred_guid
        FROM   MGMT_TARGET_CREDENTIALS tc, MGMT_CREDENTIAL_SETS cs,
               MGMT_TARGETS t
        WHERE  tc.target_guid=p_target_guid
        AND    tc.target_guid=t.target_guid
        AND    cs.target_type=t.target_type
        AND    cs.target_type_meta_ver=t.type_meta_ver
        AND    cs.set_name=tc.credential_set_name
        AND    cs.set_usage=MGMT_CREDENTIAL.MONITORING_SET_USAGE
        AND    ROWNUM=1;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- There may be no monitoring sets for the type, or
        -- no monitoring credentials saved for the target
        OPEN p_monitor_creds_out FOR
            SELECT '1', '2' FROM DUAL WHERE ROWNUM < 1;
        RETURN;
    END;

    OPEN p_monitor_creds_out FOR
        SELECT credential_set_column, 
               decode(credential_value,null,null,decrypt(credential_value)) 
        FROM   MGMT_CREDENTIALS2
        WHERE  credential_guid=l_cred_guid;

END;

-- Return UDTP defs for the given target type version
-- This method serves a UI page, and users are not allowed to see hidden 
-- properties so don't even send them back...
PROCEDURE get_type_udtp(p_target_type   IN VARCHAR2,
                        p_type_meta_ver IN VARCHAR2,
                        p_udtp_defs_out OUT CURSOR_TYPE)
IS
BEGIN
    OPEN p_udtp_defs_out FOR
        SELECT property_name, property_display_name, property_display_nlsid,
               required_flag, credential_flag, default_value, computed_flag,
               read_only_flag, all_versions
          FROM mgmt_target_prop_defs
         WHERE type_meta_ver = p_type_meta_ver
           AND property_type = 'INSTANCE'
           AND target_type = p_target_type
           AND all_versions = 1
           AND hidden_flag = 0;

END get_type_udtp;

-- Return information about the specified target
PROCEDURE get_target_info(p_target_name IN VARCHAR2,
                          p_target_type IN VARCHAR2,
                          p_basic_info_out OUT CURSOR_TYPE,
                          p_properties_out OUT CURSOR_TYPE,
                          p_prop_defs_out OUT CURSOR_TYPE,
                          p_compositeinfo_out OUT CURSOR_TYPE,
                          p_memberinfo_out out CURSOR_TYPE,
                          p_monitor_creds_out OUT CURSOR_TYPE) IS
l_type_meta_ver mgmt_target_prop_defs.type_meta_ver%TYPE;
BEGIN
    get_target_info(p_target_name, p_target_type, p_basic_info_out,
                    p_properties_out, p_compositeinfo_out, p_memberinfo_out,
                    p_monitor_creds_out);

    SELECT type_meta_ver INTO l_type_meta_ver
      FROM mgmt_targets
     WHERE target_name=p_target_name AND
           target_type=p_target_type;

    OPEN p_prop_defs_out FOR
        SELECT property_name, property_display_name, property_display_nlsid,
               required_flag, credential_flag, default_value, computed_flag,
               read_only_flag, all_versions
          FROM mgmt_target_prop_defs
         WHERE type_meta_ver = l_type_meta_ver
           AND property_type = 'INSTANCE'
           AND target_type = p_target_type
           AND hidden_flag = 0;

END get_target_info;

-- Return information about the specified target
PROCEDURE get_target_info(p_target_name IN VARCHAR2,
                          p_target_type IN VARCHAR2,
                          p_basic_info_out OUT CURSOR_TYPE,
                          p_properties_out OUT CURSOR_TYPE,
                          p_compositeinfo_out OUT CURSOR_TYPE,
                          p_memberinfo_out out CURSOR_TYPE,
                          p_monitor_creds_out OUT CURSOR_TYPE) IS
l_target_guid RAW(16);
BEGIN
    SELECT target_guid INTO l_target_guid FROM MGMT_TARGETS
        WHERE target_name=p_target_name AND
             target_type=p_target_type;

    OPEN p_basic_info_out FOR
        SELECT target_guid, emd_url, target_name as display_name, host_name, type_display_name
             FROM MGMT_TARGETS WHERE
             target_guid=l_target_guid;

    OPEN p_memberinfo_out FOR
        SELECT member_target_name, member_target_type, association,
               emd_url FROM
            MGMT_TARGET_MEMBERSHIPS m, MGMT_TARGETS t WHERE
              composite_target_guid=l_target_guid AND
              m.member_target_guid=t.target_guid;

    get_target_info_internal(l_target_guid, p_properties_out,
                    p_compositeinfo_out,
                    p_monitor_creds_out);

END;

-- Return information about the specified target that is currently
-- being deleted. Throws a NO_DATA_FOUND is the target doesn't
-- exist or is already deleted.
PROCEDURE get_delete_target_info(p_target_name IN VARCHAR2,
                                 p_target_type IN VARCHAR2,
                                 p_basic_info_out OUT CURSOR_TYPE,
                                 p_properties_out OUT CURSOR_TYPE,
                                 p_compositeinfo_out OUT CURSOR_TYPE,
                                 p_monitor_creds_out OUT CURSOR_TYPE,
                                 p_agent_url_list_out OUT CURSOR_TYPE) IS
l_target_guid RAW(16);
BEGIN
    BEGIN
        SELECT target_guid INTO l_target_guid 
          FROM MGMT_TARGETS_DELETE
         WHERE target_name=p_target_name 
           AND target_type=p_target_type
           AND delete_complete_time IS NULL;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
            ' The target does not exist or may have already been deleted: '||
            ' Target name = ' || p_target_name || 
            ' and Target type = ' || p_target_type);   
    END;

    OPEN p_basic_info_out FOR
        SELECT target_guid, emd_url
          FROM MGMT_TARGETS_DELETE 
          WHERE target_guid=l_target_guid;

    get_target_info_internal(l_target_guid, p_properties_out,
                    p_compositeinfo_out,
                    p_monitor_creds_out);


   OPEN p_agent_url_list_out FOR
        SELECT emd_url 
          FROM mgmt_targets
         WHERE (target_guid IN 
               (SELECT agent_guid 
                  FROM mgmt_target_agent_assoc
                 WHERE target_guid = l_target_guid));

END;

--
-- Checks whether a given target is a multi-agent target or not
--
FUNCTION is_multi_agent_target(p_target_name IN VARCHAR2,
                               p_target_type IN VARCHAR2)
                               RETURN NUMBER IS
BEGIN
     return emd_loader.is_multi_agent_target(p_target_name,
                                             p_target_type);
END is_multi_agent_target;

--
-- Returns the master agent url
--
FUNCTION get_master_agent_url(p_target_name IN VARCHAR2,
                              p_target_type IN VARCHAR2)
   RETURN VARCHAR2
IS
  l_emd_url mgmt_targets.emd_url%TYPE;
BEGIN
  SELECT emd_url 
    INTO l_emd_url
    FROM mgmt_targets
   WHERE target_name = p_target_name
     AND target_type = p_target_type;
  
  RETURN l_emd_url;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
       raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
           MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR_M ||
           ' Target name = ' || p_target_name || 
           ' and Target type = ' || p_target_type);   
END get_master_agent_url;

--
-- Adds a master changed callback.
--  
PROCEDURE add_master_changed_callback(v_target_name IN VARCHAR2,
                                      v_target_type IN VARCHAR2,
                                      v_callback_name IN VARCHAR2)
IS
BEGIN
  em_master_agent.add_master_changed_callback(v_target_name, 
                                      v_target_type, v_callback_name);
END add_master_changed_callback;


--
--  Deletes a master changed callback
--
PROCEDURE delete_master_changed_callback(v_target_name IN VARCHAR2,
                                     v_target_type IN VARCHAR2,
                                     v_callback_name IN VARCHAR2 DEFAULT NULL)
IS
BEGIN
  em_master_agent.delete_master_changed_callback(v_target_name, 
                                      v_target_type, v_callback_name);
END delete_master_changed_callback;

--
-- PROCEDURE: add_tgt_relocation_callback
--
-- PURPOSE
--   Adds a callback to the repository. This callback is called whenever
--   that target is relocated from one agent to another.
--
--  PROCEDURE callback_name( target_name   IN VARCHAR2,
--                           target_type   IN VARCHAR2,
--                           old_agent_url IN VARCHAR2,
--                           new_agent_url IN VARCHAR2);
--
--
PROCEDURE add_tgt_relocation_callback( v_callback_name IN VARCHAR2,
                                       v_target_name IN VARCHAR2 DEFAULT ' ',
                                       v_target_type IN VARCHAR2 DEFAULT ' ')
IS
BEGIN

  em_target_relocate.add_tgt_relocation_callback( v_callback_name,
                                              v_target_name, v_target_type );

END add_tgt_relocation_callback;

--
-- PROCEDURE: delete_tgt_relocation_callback
--
-- PURPOSE
--   Deletes a callback from repository.
--
--  PROCEDURE callback_name( target_name   IN VARCHAR2,
--                           target_type   IN VARCHAR2,
--                           old_agent_url IN VARCHAR2,
--                           new_agent_url IN VARCHAR2);
--
--
PROCEDURE delete_tgt_relocation_callback( v_callback_name IN VARCHAR2,
                                    v_target_name IN VARCHAR2 DEFAULT ' ',
                                    v_target_type IN VARCHAR2 DEFAULT ' ')
IS
BEGIN

  em_target_relocate.delete_tgt_relocation_callback( v_callback_name,
                                             v_target_name, v_target_type );

END delete_tgt_relocation_callback;
--
-- Register callback to called on change of target's catprop or metaver
--
PROCEDURE add_metaver_catprop_callback(p_callback_name IN VARCHAR2,
                                       p_target_type   IN VARCHAR2,
                                       p_to_meta_ver   IN VARCHAR2 := ' ',
                                       p_eval_order    IN NUMBER := 0
                                      )
IS
l_error_message VARCHAR2(1024) ;
BEGIN
  EM_CHECK.check_not_null(p_callback_name,'p_callback name') ;
  -- Check if signature is valid
  IF NOT EM_CHECK.is_valid_signature
      (p_callback_name,
       mgmt_short_string_array('MGMT_TARGET_META_VER_CBK_OBJ'),
       l_error_message)
  THEN
    raise_application_error(mgmt_global.invalid_params_err,l_error_message) ;
  END IF ;
                       
  INSERT INTO mgmt_callbacks
  (callback_type,callback_name,
   selector_1,selector_2,eval_order)
  VALUES 
   (mgmt_global.G_TARGET_METAVER_UPD_CALLBACK,p_callback_name,
    p_target_type,p_to_meta_ver, p_eval_order) ;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    raise_application_error(mgmt_global.invalid_params_err,
                            'callback  already exist');  
END add_metaver_catprop_callback ;
--
-- Delete callback for target meta version/category prop change
--
PROCEDURE del_metaver_catprop_callback(p_callback_name IN VARCHAR2,
                                       p_target_type   IN VARCHAR2,
                                       p_to_meta_ver   IN VARCHAR2 := ' '
                                      )
IS
BEGIN
  EM_CHECK.check_not_null(p_callback_name,'callback name') ;
  DELETE mgmt_callbacks
   WHERE callback_type = MGMT_GLOBAL.G_TARGET_METAVER_UPD_CALLBACK
     AND callback_name = p_callback_name
     AND selector_1    = p_target_type
     AND selector_2    = p_to_meta_ver ;
END del_metaver_catprop_callback ;
--
-- Add callback for target meta ver change
-- DEPRECATED: Please use add_metaver_catprop_callback
-- Note: The registered callback will also be called on category prop change
-- Callback's responsbility to ignore category prop changes if it is not
-- interested ( if p_from_meta_ver = p_to_meta_ver then RETURN)
--
PROCEDURE add_metaver_upd_callback(p_callback_name IN VARCHAR2,
                                   p_target_type   IN VARCHAR2,
                                   p_to_meta_ver   IN VARCHAR2 := ' ',
                                   p_eval_order    IN NUMBER := 0
                                  )
IS
BEGIN
  add_metaver_catprop_callback(p_callback_name,
                               p_target_type,
                               p_to_meta_ver,
                               p_eval_order) ;
END add_metaver_upd_callback ;
--
-- Delete callback for target meta version change
-- DEPRECATED: Please use del_metaver_catprop_callback
--
PROCEDURE del_metaver_upd_callback(p_callback_name IN VARCHAR2,
                                       p_target_type   IN VARCHAR2,
                                       p_to_meta_ver   IN VARCHAR2 := ' '
                                       )
IS
BEGIN
  del_metaver_catprop_callback(p_callback_name,
                               p_target_type,
                               p_to_meta_ver) ;

END del_metaver_upd_callback ;
--
-- PURPOSE
--   To add a list of dependencies that affects the severity state
--   of a metric. The dependencies themselves are metrics. These dependencies
--   are used by the repository to trigger the execution of metric's
--   severity_eval_proc.
--
--   This method allows specifying dependencies at a target type
--   level and meant to be used only for composite target types.
--   
-- PARAMTERS
--
--   v_metric_desc : A MGMT_METRIC_DESC object describing the metric.
--
--   v_dependency_list: A list of MGMT_METRIC_DESC objects containing
--                      the metric dependencies. 
--                    
-- ERROR CODES
--    NO_SUCH_METRIC_ERR: If the metric in metric_desc is not defined.
--    ALREADY_EXISTS_ERR: If dependencies are already defiend for this
--                        metric. Call DELETE_METRIC_SEVERITY_DEPS
--                        to delete existing dependencies.
--
PROCEDURE ADD_METRIC_SEVERITY_DEPS(v_metric_desc IN MGMT_METRIC_DESC,
                                   v_dependency_list IN 
                                             MGMT_METRIC_DESC_ARRAY,
                                   v_opt_code IN NUMBER DEFAULT NULL
                                            )
IS
BEGIN
   em_rep_metric.add_metric_severity_deps(v_metric_desc, v_dependency_list, 
                                          v_opt_code);   
END ADD_METRIC_SEVERITY_DEPS;


--
-- PURPOSE
--   To add a list of dependencies that affects the severity state
--   of a metric for a specific target instance. The dependencies 
--   themselves are metrics. These dependencies are used by the repository
--   to trigger the execution of metric's severity_eval_proc. If 
--   dependency metrics already exist for this metric, then the new
--   dependencies are appended. Duplicate dependencies are ignored.
--
-- PARAMTERS
--
--   v_metric_instance: A MGMT_METRIC_INSTANCE object defining the metric. The
--                   'key_value' attribute is optional.
--
--   v_dependency_list: A list of MGMT_METRIC_INSTANCE objects containing
--                      the metric dependencies.  The 'key_value' attribute
--                      is optional.
--
-- ERROR
--   NO_SUCH_METRIC_ERROR: If any of the metrics don't exist.
--   TARGET_DOES_NOT_EXIST: If any of the targets don't exist.
--                     
PROCEDURE ADD_METRIC_SEVERITY_DEPS(v_metric_instance IN MGMT_METRIC_INSTANCE,
                                   v_dependency_list IN 
                                        MGMT_METRIC_INSTANCE_ARRAY)
IS
BEGIN
   em_rep_metric.add_metric_severity_deps(v_metric_instance, v_dependency_list);
END ADD_METRIC_SEVERITY_DEPS;


--
-- PURPOSE
--     Remove the dependency list for a metric for an entire target
--     type.
-- PARAMTERS
--   v_metric_desc : A MGMT_METRIC_DESC object describing the metric.
-- ERROR CODES
--    NO_SUCH_METRIC_ERR  - If there is no such metric 
-- 
PROCEDURE DELETE_METRIC_SEVERITY_DEPS(v_metric_desc IN MGMT_METRIC_DESC)
IS
BEGIN
  em_rep_metric.delete_metric_severity_deps(v_metric_desc);
END DELETE_METRIC_SEVERITY_DEPS;



--
-- PURPOSE
--     Remove the dependency list for a metric of a specific target
--     instance.
-- PARAMTERS
--   v_metric_instance:  A metric instance object.
--
--   v_dependency_list: If NULL, all dependencies are removed for this
--              metric. If not NULL, only the specified depenendecies
--              are removed.
-- 
-- ERROR CODES
--    TARGET_NOT_FOUND    - If there is no such metric 
--    NO_SUCH_METRIC_ERR  - If there is no such metric 
-- 
PROCEDURE DELETE_METRIC_SEVERITY_DEPS(v_metric_instance IN
                                            MGMT_METRIC_INSTANCE,
                                      v_dependency_list IN
                                            MGMT_METRIC_INSTANCE_ARRAY)
IS
BEGIN
   em_rep_metric.delete_metric_severity_deps(v_metric_instance,
                                             v_dependency_list);        
END DELETE_METRIC_SEVERITY_DEPS;




--
-- PURPOSE
--   Sets the eval proc for a repo collection
--
PROCEDURE SET_REPO_METRIC_EVAL_PROC(v_metric          IN MGMT_METRIC_INSTANCE,
                                    v_eval_proc       IN VARCHAR2,
                                    v_opt_code  IN NUMBER 
                                                 DEFAULT NULL)
IS
BEGIN
   em_rep_metric.set_repo_metric_eval_proc(v_metric,
                                           v_eval_proc,
                                           v_opt_code);
END SET_REPO_METRIC_EVAL_PROC;


--
-- PURPOSE
--   Sets the eval proc for a repo collection
--
PROCEDURE GET_REPO_METRIC_EVAL_PROC(v_metric          IN MGMT_METRIC_INSTANCE,
                                    v_eval_proc       OUT VARCHAR2)
IS
BEGIN
   em_rep_metric.get_repo_metric_eval_proc(v_metric,
                                           v_eval_proc);
END GET_REPO_METRIC_EVAL_PROC;


--
--   Sets up a repository collection for the specified metric.  
--
PROCEDURE ADD_REPO_METRIC_COLLECTION(v_metric          IN MGMT_METRIC_INSTANCE,
                                     v_eval_proc       IN VARCHAR2,
                                     v_dep_metrics     IN 
                                            MGMT_METRIC_INSTANCE_ARRAY,
                                     v_coll_interval IN NUMBER DEFAULT NULL,
                                     v_opt_code  IN NUMBER 
                                                 DEFAULT NULL)
IS
BEGIN
   em_rep_metric.add_repo_metric_collection(v_metric => v_metric,
                                            v_eval_proc => v_eval_proc,
                                            v_dep_metrics => v_dep_metrics,
                                            v_coll_interval => v_coll_interval,
                                            v_opt_code => v_opt_code);
END ADD_REPO_METRIC_COLLECTION;



--
--   Modifies repository collection details for the specified metric. 
--
PROCEDURE MODIFY_REPO_METRIC_COLLECTION(v_metric          IN MGMT_METRIC_INSTANCE,
                                        v_eval_proc       IN VARCHAR2,
                                        v_dep_metrics     IN 
                                            MGMT_METRIC_INSTANCE_ARRAY,
                                     v_coll_interval IN NUMBER DEFAULT NULL,
                                     v_opt_code  IN NUMBER 
                                                 DEFAULT NULL)

IS
BEGIN
   em_rep_metric.modify_repo_metric_collection(v_metric => v_metric,
                                               v_eval_proc => v_eval_proc,
                                               v_dep_metrics => v_dep_metrics,
                                               v_coll_interval => v_coll_interval,
                                               v_opt_code => v_opt_code);
null;
END MODIFY_REPO_METRIC_COLLECTION;


--
--   Modifies repository collection details for the specified metric. 
--
PROCEDURE MODIFY_REPO_METRIC_DEPS(v_metric IN MGMT_METRIC_INSTANCE,
                                  v_dep_metrics_to_add IN 
                                       MGMT_METRIC_INSTANCE_ARRAY,
                                  v_dep_metrics_to_del IN
                                       MGMT_METRIC_INSTANCE_ARRAY)
IS
BEGIN
null;
   em_rep_metric.modify_repo_metric_deps(v_metric,
                   v_dep_metrics_to_add, v_dep_metrics_to_del);
END MODIFY_REPO_METRIC_DEPS;

--
--     Remove a repository metric collection.
--
PROCEDURE DELETE_REPO_METRIC_COLLECTION(v_metric IN MGMT_METRIC_INSTANCE)
IS
BEGIN
   em_rep_metric.delete_repo_metric_collection(v_metric);
END DELETE_REPO_METRIC_COLLECTION;



--
-- PURPOSE
--   populate table mgmt_target_prop_def. If duplicate record is found,
--   upsert happens.  Target name, type meta ver and property name are keys.
-- PARAMTERS
--   v_target_type, target type
--   v_type_meta_ver, target type meta version
--   v_property_name, property name
--   v_property_type, property type, such as 'INSTANCE'
--   v_property_display_name,
--   v_property_display_nlsid
--   v_required_flag
--   v_credential_flag, is a credential column
--   v_default_value
--   v_computed_flag, is computed 
--   v_system_flag, is set by system
--   v_all_versions, is appliable to all versions
--   v_read_only_flag, is a read-only (UI) column
--   v_hidden_flag, is a hidden (UI) column
--
PROCEDURE UPSERT_TARGET_PROPERTY_DEF(v_target_type VARCHAR2,
                                  v_type_meta_ver VARCHAR2,
                                  v_property_name VARCHAR2,
                                  v_property_type VARCHAR2,
                                  v_property_display_name VARCHAR2,
                                  v_property_display_nlsid VARCHAR2 DEFAULT NULL,
                                  v_required_flag NUMBER DEFAULT 0,
                                  v_credential_flag NUMBER DEFAULT 0,
                                  v_default_value VARCHAR2 DEFAULT ' ',
                                  v_computed_flag NUMBER DEFAULT 0,
                                  v_system_flag NUMBER DEFAULT 1,
                                  v_all_versions NUMBER DEFAULT 0,
                                  v_read_only_flag NUMBER DEFAULT 0,
                                  v_hidden_flag NUMBER DEFAULT 0) IS
l_property_display_name MGMT_TARGET_PROP_DEFS.property_display_name%TYPE;
BEGIN
    IF (v_target_type IS NULL) THEN
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
        'Invalid parameters : NULL target type');
    END IF;

    IF (v_type_meta_ver IS NULL) THEN
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
        'Invalid parameters : NULL type meta ver');
    END IF;

    IF (v_property_name IS NULL) THEN
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
        'Invalid parameters : NULL property name');
    END IF;

    if (v_property_display_name IS NOT NULL) THEN
       l_property_display_name := v_property_display_name;
    ELSE
       l_property_display_name := v_property_name;
    END IF;

    INSERT INTO MGMT_TARGET_PROP_DEFS(target_type, type_meta_ver, property_name,
           property_type, property_display_name, property_display_nlsid,
           required_flag, credential_flag, default_value, computed_flag,
           read_only_flag, hidden_flag, system_flag, all_versions)
    VALUES(v_target_type, v_type_meta_ver, v_property_name, v_property_type,
           l_property_display_name, v_property_display_nlsid, v_required_flag,
           v_credential_flag, v_default_value, v_computed_flag,
           v_read_only_flag, v_hidden_flag, v_system_flag, v_all_versions);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
       UPDATE MGMT_TARGET_PROP_DEFS
       SET property_type = v_property_type,
           property_display_name = l_property_display_name,
           property_display_nlsid = v_property_display_nlsid,
           required_flag = v_required_flag,
           credential_flag = v_credential_flag,
           default_value = v_default_value,
           computed_flag = v_computed_flag,
           read_only_flag = v_read_only_flag,
           hidden_flag = v_hidden_flag,
           system_flag = v_system_flag,
           all_versions = v_all_versions
       WHERE target_type = v_target_type
         AND type_meta_ver = v_type_meta_ver
         AND property_name = v_property_name;

END UPSERT_TARGET_PROPERTY_DEF;

-- PURPOSE
--    delete target property def
-- PARAMETERS
--
PROCEDURE DELETE_TARGET_PROPERTY_DEF(v_target_type VARCHAR2,
                                     v_type_meta_ver VARCHAR2,
                                     v_property_name VARCHAR2) IS
BEGIN
    DELETE FROM MGMT_TARGET_PROP_DEFS
    WHERE target_type = v_target_type
      AND type_meta_ver = v_type_meta_ver
      AND property_name = v_property_name;

END DELETE_TARGET_PROPERTY_DEF;

--
-- PROCEDURE : get_user_targets
--
-- PURPOSE
--
--   this is a callback which will be registered to the user_model pkg 
--   user_model pkg will call this procedure to get a list of targets which the 
--   user has
--  
-- PARAMETERS
--
--  user_name_in     - name of the user
--  user_objects_out - list of targets will be appended to user_objects_out
--  type_in          - type of user model callback
--
-- NOTES
--
  
PROCEDURE get_user_targets(user_name_in      IN VARCHAR2,
                           user_objects_out  OUT MGMT_USER_OBJECTS,
                           type_in           IN NUMBER) IS
i INTEGER := 0;
l_user_targets MGMT_USER_OBJECTS := MGMT_USER_OBJECTS();

CURSOR c_targets IS SELECT target_name,target_type
FROM MGMT_TARGETS WHERE owner=user_name_in and is_group=0;

CURSOR c_groups IS SELECT target_name,target_type
FROM MGMT_TARGETS WHERE owner=user_name_in and is_group=1;

BEGIN
    
    FOR c IN c_targets
    LOOP
        l_user_targets.extend(1);
        i := i + 1;
        l_user_targets(i) := MGMT_USER_OBJECT(MGMT_USER.USER_OBJECT_TARGET,
                                                c.target_name,
                                                c.target_type,
                                                MGMT_USER.SYNC_DROP_OBJECT);  
    END LOOP;

    FOR c IN c_groups
    LOOP
        l_user_targets.extend(1);
        i := i + 1;
        l_user_targets(i) := MGMT_USER_OBJECT(MGMT_USER.USER_OBJECT_GROUP,
                                                c.target_name,
                                                c.target_type,
                                                MGMT_USER.SYNC_DROP_OBJECT);  
    END LOOP;
    
    user_objects_out := l_user_targets;
END;

--
-- PROCEDURE : drop_user_targets
--
-- PURPOSE
--
--   this is a callback which will be registered to the user_model pkg 
--   user_model pkg will call this procedure to delete the target
--   owned by a user. the target will not be deleted but will be reassigned to "SYSTEM"
--  
-- PARAMETERS
--
--  user_name_in     - name of the user
--  type_in          - type of user model callback
--
-- NOTES
--
        
PROCEDURE drop_user_targets(user_name_in      IN VARCHAR2,
                            type_in           IN NUMBER) IS
BEGIN
    reassign_user_targets(user_name_in,
                          MGMT_GLOBAL.G_DEFAULT_TARGET_OWNER,
                          MGMT_USER.USER_REASSIGN_CALLBACK);
END;


--
-- PROCEDURE : reassign_user_targets
--
-- PURPOSE
--
--   this is a callback which will be registered to the user_model pkg 
--   user_model pkg will call this procedure to reassign the target
--   owned by a user to a new user.
--  
-- PARAMETERS
--
--  user_name_in     - name of the user
--  new_user_name_in - the new user name
--  type_in          - type of user model callback
--
-- NOTES
--
        
PROCEDURE reassign_user_targets(user_name_in      IN VARCHAR2,
                                new_user_name_in  IN VARCHAR2,
                                type_in           IN NUMBER) IS                   

  l_user_name       VARCHAR2(256) := UPPER(TRIM(user_name_in));
  l_new_user_name   VARCHAR2(256) := UPPER(TRIM(new_user_name_in));
                             
  CURSOR c_targets IS SELECT target_guid,target_name, target_type
     FROM MGMT_TARGETS WHERE owner=l_user_name;

BEGIN
  
    FOR c IN c_targets
    LOOP
        UPDATE MGMT_TARGETS
        SET    owner = l_new_user_name 
        WHERE  target_guid = c.target_guid;
        
        IF (l_new_user_name != MGMT_GLOBAL.G_DEFAULT_TARGET_OWNER)
        THEN
            MGMT_USER.GRANT_PRIV(l_new_user_name, MGMT_USER.FULL_TARGET,
                                 c.target_name,c.target_type);
        END IF;
        
    END LOOP;

END;

  PROCEDURE add_tzrgn_update_callback(p_callback_name IN VARCHAR2,
                                      p_target_type   IN VARCHAR2 DEFAULT '%',
                                      p_eval_order    IN NUMBER   DEFAULT 0)
  IS
    l_error_message VARCHAR2(1024) ;
  BEGIN
    EM_CHECK.check_not_null(p_callback_name,'p_callback name') ;
    -- Check if signature is valid
    IF NOT EM_CHECK.is_valid_signature(
                p_callback_name, 
                mgmt_short_string_array('MGMT_TZRGN_UPDATE_CBK_OBJ'),
                l_error_message)
    THEN
      raise_application_error(mgmt_global.invalid_params_err,l_error_message) ;
    END IF ;
                       
    INSERT INTO mgmt_callbacks
      (callback_type, callback_name, selector_1, eval_order)
    VALUES 
     (MGMT_GLOBAL.G_TZRGN_UPDATE_CALLBACK, UPPER(p_callback_name),
      NVL(p_target_type, '%'), p_eval_order) ;
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      raise_application_error(mgmt_global.invalid_params_err,
                            'callback ' || p_callback_name || ' already exists');  
  END add_tzrgn_update_callback;

  PROCEDURE del_tzrgn_update_callback(p_callback_name IN VARCHAR2,
                                      p_target_type   IN VARCHAR2 DEFAULT '%')
  IS
  BEGIN
    EM_CHECK.check_not_null(p_callback_name,'p_callback_name') ;

    DELETE mgmt_callbacks
     WHERE callback_type = MGMT_GLOBAL.G_TZRGN_UPDATE_CALLBACK
       AND callback_name = UPPER(p_callback_name)
       AND selector_1    = NVL(p_target_type, selector_1);

  END del_tzrgn_update_callback;
  
  --
  -- PROCEDURE : set_target_tzrgn
  --
  -- PURPOSE
  --   This procedure updates the target timezone region to the specific region
  --
  PROCEDURE set_target_tzrgn(p_target_name     IN VARCHAR2,
                             p_target_type     IN VARCHAR2,
                             p_timezone_region IN VARCHAR2)
  IS
    l_target_guid mgmt_targets.target_guid%TYPE;
    l_tz_offset mgmt_targets.timezone_region%TYPE;
  BEGIN
    -- Check for super user privilege.
    EM_CHECK.check_super_user_priv;

    -- Perform validations
    EM_CHECK.check_not_null(p_target_name, 'p_target_name');
    EM_CHECK.check_not_null(p_target_type, 'p_target_type');
    EM_CHECK.check_not_null(p_timezone_region, 'p_timezone_region');

    -- Check to see if the timezone region is a valid region
    BEGIN
      l_tz_offset := TZ_OFFSET(p_timezone_region);
    EXCEPTION
      WHEN OTHERS THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid timezone region ' || 
	p_timezone_region);
    END;

    l_target_guid := get_target_guid(p_target_name, p_target_type);

    EM_TARGET.set_target_tzrgn(l_target_guid, p_timezone_region);

  END set_target_tzrgn;

  --
  -- PROCEDURE : set_agent_tzrgn
  --
  -- PURPOSE
  --
  --   This procedure updates the timezone region to the given timezone region
  --   for all the targets monitored by the given agent
  --  
  PROCEDURE set_agent_tzrgn(p_agent_name      IN VARCHAR2,
                            p_timezone_region IN VARCHAR2)
  IS
    l_emd_url mgmt_targets.emd_url%TYPE;
    l_agent_guid mgmt_targets.target_guid%TYPE;
    l_tz_offset mgmt_targets.timezone_region%TYPE;
  BEGIN
    -- Check for super user privilege.
    EM_CHECK.check_super_user_priv;

    -- Validate p_agent_name
    EM_CHECK.check_not_null(p_agent_name, 'p_agent_name');
    EM_CHECK.check_not_null(p_timezone_region, 'p_timezone_region');

    -- Check to see if the timezone region is a valid region
    BEGIN
      l_tz_offset := TZ_OFFSET(p_timezone_region);
    EXCEPTION
      WHEN OTHERS THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid timezone region ' || 
	p_timezone_region);
    END;

    -- Get the emd_url
    BEGIN
      SELECT emd_url, target_guid INTO l_emd_url, l_agent_guid
        FROM mgmt_targets
       WHERE target_name = p_agent_name
         AND target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid agent name ' || 
	   p_agent_name);
    END;

    -- cluster target may associate with another agent due to agent shutdown when
    -- adjusting timezone
    FOR tgt_rec IN (SELECT target_guid
                      FROM mgmt_targets
                     WHERE emd_url = l_emd_url
                    UNION  
                    SELECT t.target_guid
                      FROM mgmt_target_agent_assoc a,
                           mgmt_targets t,
                           mgmt_type_properties p
                     WHERE a.agent_guid = l_agent_guid
                       AND t.target_guid = a.target_guid
                       AND t.target_type = p.target_type
                       AND p.property_name = mgmt_global.G_IS_CLUSTER_PROP
                       AND p.property_value = mgmt_global.G_TRUE)
    LOOP
      EM_TARGET.set_target_tzrgn(tgt_rec.target_guid, p_timezone_region);
    END LOOP;

  END set_agent_tzrgn;


--
-- PROCEDURE : set_instance_target_property
--
-- PURPOSE
--
--   Set the value of a target property with property type INSTANCE.
--  
-- PARAMETERS
--
--  See signatures.
--
-- NOTES
-- Sets the value of a given target property of type instance. The definition 
-- of the property is validated in mgmt_target_prop_defs, and an error is 
-- thrown if this def does not exist.
-- WARNING: This function does not update any copies of the property that might
-- exist on any agent(s).
--
PROCEDURE set_instance_target_property(
         p_target_name    IN mgmt_targets.target_name%TYPE,
         p_target_type    IN mgmt_targets.target_type%TYPE,
         p_property_name  IN mgmt_target_properties.property_name%TYPE,
         p_property_value IN mgmt_target_properties.property_value%TYPE)
IS
BEGIN
  set_target_property(p_target_name, p_target_type, p_property_name,
                      mgmt_properties.INSTANCE, p_property_value);
END set_instance_target_property;

--
-- PROCEDURE : set_target_property
--
-- PURPOSE
--
--   Set the value of a target property.
--  
-- PARAMETERS
--
--  See signatures.
--
-- NOTES
-- Sets the value of a given target property. The definition of the property
-- is validated in mgmt_target_prop_defs, and an error is thrown if this def
-- does not exist.
-- WARNING: This function does not update any copies of the property that might
-- exist on any agent(s).
--
PROCEDURE set_target_property(
         p_target_name    IN mgmt_targets.target_name%TYPE,
         p_target_type    IN mgmt_targets.target_type%TYPE,
         p_property_name  IN mgmt_target_properties.property_name%TYPE,
         p_property_type  IN mgmt_target_properties.property_type%TYPE,
         p_property_value IN mgmt_target_properties.property_value%TYPE)
IS
  l_target_guid mgmt_target_properties.target_guid%TYPE;
BEGIN
  mgmt_properties.set_target_property(p_target_name, p_target_type,
                                      p_property_name, p_property_type,
                                      p_property_value);
END set_target_property;

--
-- PROCEDURE : set_target_properties
--
-- PURPOSE
--
--   Set the value of a set of target properties.
--  
-- PARAMETERS
--
--  See signature.
--
-- NOTES
-- Sets the values of a the given target properties. The definition of the 
-- properties is validated in mgmt_target_prop_defs, and an error is thrown 
-- if this def does not exist (no prop is updated in this case).
-- WARNING: This function does not update any copies of the properties that 
-- might exist on any agent(s).
--
PROCEDURE set_target_properties(
         p_target_name    IN mgmt_targets.target_name%TYPE,
         p_target_type    IN mgmt_targets.target_type%TYPE,
         p_properties     IN MGMT_TARGET_PROPERTY_LIST)
IS
  l_target_guid mgmt_target_properties.target_guid%TYPE;
BEGIN
  mgmt_properties.set_target_properties(p_target_name, p_target_type,
                                        p_properties);
END set_target_properties;

--
-- checks if the specified target type is an aggregate type
-- Returns 0 the specified type is not an aggregate type
--         1 the specified type is an aggregate type
--
FUNCTION is_aggregate_type( p_target_type IN VARCHAR2)
                            RETURN NUMBER IS
result_value varchar2(1024);
BEGIN
    SELECT p.property_value
      INTO result_value
      FROM mgmt_type_properties p
     WHERE p.target_type = p_target_type
       AND p.PROPERTY_NAME = 'is_aggregate';

    IF ('1' = result_value) THEN
      return 1;
    ELSE
      return 0;
    END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
    RETURN 0;
END is_aggregate_type;

  --
  -- PROCEDURE : get_agent_version
  --
  -- PURPOSE
  --
  --   Gets the agent version.
  --  
  -- PARAMETERS
  --    IN parameters
  --         p_emd_url: emd_url of the agent
  --    OUT parameters 
  --         p_agent_version: version of the agent
  --  See signature.
  --
  -- NOTES
  --
  PROCEDURE get_agent_version
  (
    p_emd_url IN mgmt_targets.emd_url%TYPE,
    p_agent_version OUT mgmt_target_properties.property_value%TYPE
  )
  IS
  BEGIN
    
    IF EMDW_LOG.P_IS_INFO_SET THEN 
      EMDW_LOG.INFO('get_agent_version: Enter ' || p_emd_url, G_MODULE_NAME) ; 
    END IF ;

    EM_TARGET.get_agent_version(p_emd_url, p_agent_version);

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

  END get_agent_version;

  --
  -- PROCEDURE: remove_cluster_member
  --
  -- PURPOSE: 
  --   Remove the cluster member from the cluster target.
  --   This api does not delete the cluster member, it just removes the
  --   association with the cluster target.
  PROCEDURE remove_cluster_member
  (
    p_cluster_name IN mgmt_targets.target_name%TYPE,
    p_cluster_type IN mgmt_targets.target_type%TYPE,
    p_member_name IN mgmt_targets.target_name%TYPE,
    p_member_type IN mgmt_targets.target_type%TYPE
  )
  IS
  BEGIN

    IF EMDW_LOG.P_IS_INFO_SET THEN 
      EMDW_LOG.INFO('remove_cluster_member: Enter ' || p_cluster_name || ', ' || p_cluster_type ||
                     ', ' || p_member_name || ', ' || p_member_type, G_MODULE_NAME) ; 
    END IF ;

    EM_TARGET.remove_cluster_member(p_cluster_name,
                                    p_cluster_type,
                                    p_member_name,
                                    p_member_type);

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

  END remove_cluster_member;
  
  
  --
  -- PROCEDURE: set_master_agent
  --
  -- PURPOSE: 
  --   To set the specific agent to be the master agent.
  --
  PROCEDURE set_master_agent
  (
    p_target_name IN mgmt_targets.target_name%TYPE,
    p_target_type IN mgmt_targets.target_type%TYPE,
    p_agent_name IN  mgmt_targets.target_name%TYPE
  )
  IS
  BEGIN

    IF EMDW_LOG.P_IS_INFO_SET THEN 
      EMDW_LOG.INFO('set_master_agent: Enter ' || p_target_name || ', ' || p_target_type ||
                     ', ' || p_agent_name, G_MODULE_NAME) ; 
    END IF ;

    EM_TARGET.set_master_agent(p_target_name,
                               p_target_type,
                               p_agent_name);

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

  END set_master_agent;


  --
  -- PROCEDURE: eval_repo_target_status 
  --
  -- PURPOSE: To evaluate the status of a target that has a repository side Response metric.
  --
  -- IN PARAMETERS:
  --   p_target_guid : GUID of the target whose status need to be evaluated.
  --
  -- OUT PARAMETERS
  --   NONE
  -- 
  --  This API will evaluate the status immediately if the group consist of only single target.
  --  If group has mulpliple memebers then staus evaluation can be postphomed depending upon the 
  --  marker timestamp of the members target.
  PROCEDURE eval_repo_target_status
  (
    p_target_guid mgmt_targets.target_guid%type
  )
  IS
  BEGIN
    EM_SEVERITY_REPOS.execute_repos_severity_eval(p_target_guid);
  END eval_repo_target_status;
END mgmt_target;
/
show errors;