Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/sdk/sdk_admin_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/2 2009/06/12 04:40:23 jsadras Exp $
Rem
Rem sdk_admin_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      sdk_admin_pkgbody.sql
Rem
Rem    DESCRIPTION
Rem      The sql procs used by the admin ui.  Only administrators have access.  
Rem      These are part of the SDK
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jsadras     06/11/09  - Bug:8592061, remove noops
Rem    jsadras     11/19/08  - fix perf issues
Rem    pmodi       08/24/07  - Bug6270192 Perf related - 
Rem                            Backport pmodi_bug-6270192 from main
Rem    aptrived    04/18/06  - Bug#5152908, Delete metrics if 
Rem                            metric_deletion_enabled<>FALSE 
Rem    rrawat      11/17/06  - Bug-5590461
Rem    rrawat      02/19/07  - Backport rrawat_bug-5590461 from main
Rem    pmodi       07/07/06  - Backport pmodi_bug-3900473 from main 
Rem    neearora    12/22/05  - Backport neearora_bug-4889327 from main 
Rem    pmodi       04/26/06  - Bug:3900473 Use function to get default 
Rem                            retention window for metric data deletion 
Rem    neearora    12/15/05  - Bug 4889327 : In delete_target get only the 
Rem                            agent side Cluster parent for which emd_url is 
Rem                            not null 
Rem    rkpandey    09/27/05  - Bug 4634459: Delete cluster target from standby 
Rem                            agent 
Rem    aholser     06/30/05  - 4465889 
Rem    aholser     06/08/05  - add oms apisadd api to return oms/oc4j/ias 
Rem    pmodi       05/31/05  - Bug:4391453-  target_delete to delete data from 
Rem                            OCS-like table too
Rem    neearora    04/22/05  - For bug 4306645 
Rem    jsadras     03/28/05  - security fix 
Rem    jsadras     03/17/05  - Bug:4148618, security bugs, execute immediate 
Rem    dcawley     02/21/05  - Replace enter super user mode 
Rem    ramalhot    02/17/05  - agent deletion should delete entries from 
Rem                            mgmt_target_agent_assoc_table 
Rem    rkpandey    02/06/05  - clean mgmt_targets_delete in cleanup_agent 
Rem    ramalhot    02/03/05 -  callback bug fixed 
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    dcawley     01/06/05 - Remove direct call to target deleted for user 
Rem                           model 
Rem    rkpandey    01/06/05 - code modified for agent target deletion 
Rem    rkpandey    12/15/04 - fix bug 4033439 
Rem    rkpandey    10/28/04 - Added parentCluster info parameter for delete 
Rem                           target 
Rem    vkhizder    10/14/04 - adding function to determine OMS status 
Rem    streddy     10/08/04 - Remove hard-coding of delete callback 
Rem    ramalhot    08/25/04 - cutover to new assoc tables 
Rem    ramalhot    08/18/04 - pre delete callback added 
Rem    streddy     08/17/04 - Call composite availability cleanup during 
Rem                           target deletion 
Rem    ramalhot    07/27/04 - assoc callback added 
Rem    dcawley     07/07/04 - INcrease user name size 
Rem    dcawley     07/23/04 - Remove manage target group 
Rem    dsahrawa    06/22/04 - use MGMT_JOBS.get_job_schedule_record 
Rem    rzazueta    04/06/04 - Fix 3502117 - Target deletion
Rem    rzazueta    04/01/04 - Fix 3545786 - Fix emd_url subquery in  
Rem                           delete_target
Rem    rzazueta    11/12/03 - Fix 3156431: special-case deletion of targets
Rem                           from MGMT_METRICS_RAW and MGMT_METRICS_1HOUR 
Rem    lgloyd      10/29/03 - keep mgmt_target_membership info until 
Rem                           delete_target_internal step 
Rem    streddy     09/23/03 - Update flat memberships table on target deletes 
Rem    lgloyd      09/18/03 - 
Rem    lgloyd      09/17/03 - perf: query base tables 
Rem    skini       09/09/03 - Change in schedule_record structure 
Rem    streddy     08/04/03 - Fix the deletion order for composite targets 
Rem    skini       07/06/03 - Put hextoraw around delete statement
Rem    vkhizder    06/07/03 - move ecm snapshot deletion due to target deletion 
Rem                           to ecm callback
Rem    streddy     04/30/03 - Handle master agent deletions
Rem    streddy     04/21/03 - Target added callback support
Rem    rpinnama    06/09/03 - Fix 2902066 : Do not trim target name/type
Rem    aholser     04/02/03 - add get_oms_urls
Rem    aholser     03/15/03 - 4.1 changes
Rem    rpatti      04/10/03 - commit after each delete callback
Rem    rpinnama    02/14/03 - Add del_completion_time_in to delete_target_internal
Rem    rpinnama    12/18/02 - Support delete_complete_timestamp
Rem    dcawley     12/11/02 - Move TARGET_DELETED call
Rem    rpinnama    11/25/02 - cleanup_agent API
Rem    skini       11/26/02 - Blackout callback
Rem    skini       07/08/02 - Remove reference to the snapshot table
Rem    skini       07/05/02 - Add synchronous and force options to deleteTarget
Rem    skini       07/02/02 - Make delete_target asynchronous
Rem    dcawley     06/13/02 - Notify User Model when a target is deleted
Rem    tjaiswal    05/17/02 - Move notif plsql to notification_ui_pkgbody
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Restructured.
Rem    rpatti      04/26/02 - allow grp dlt if user has manage target grp priv
Rem    rpatti      04/17/02 - update grp avail on target deletion
Rem    tjaiswal    03/20/02 - Add support for pl sql notif device
Rem    rpatti      02/25/02 - use function to get current user
Rem    skini       02/03/02 - Add permission info to delete_target
Rem    skini       01/21/02 - Introduce target deletion callbacks, exceptions
Rem    skini       01/02/02 - Remove reference to security packages
Rem    aholser     11/28/01 - make targets case-sensitive.
Rem    aholser     10/30/01 - role_target_map changes.
Rem    aholser     10/17/01 - fix counter
Rem    aholser     10/12/01 - delete_target changes
Rem    aholser     10/08/01 - throw dup user
Rem    aholser     10/09/01 - fix delete-target with target_guid in view
Rem    tjaiswal    10/09/01 - Add test_snmp_host procedure
Rem    aholser     10/12/01 - remove ability for user to retry delete_target
Rem    tjaiswal    09/07/01 - Move notif plsql
Rem    aholser     08/09/01 - review changes
Rem    aholser     07/17/01 - Created
Rem

-- The mgmt_admin_util package is accessible only BY administrators.  
-- At present, this is only the mgmt_rep defined in CreateRep WHEN access to 
-- the package is granted. 

CREATE OR REPLACE PACKAGE BODY mgmt_admin AS

---
-- PURPOSE 
--   Procedure(s) to enable/disable/verify metric deletion
-- 
PROCEDURE enable_metric_deletion IS
BEGIN
  BEGIN
    INSERT INTO MGMT_PARAMETERS
      (parameter_name, parameter_value, parameter_comment, internal_flag)
     VALUES
      (METRIC_DELETION, 'TRUE', METRIC_DELETION_CMT, 1);
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      UPDATE MGMT_PARAMETERS
         SET parameter_value = 'TRUE'
       WHERE parameter_name = METRIC_DELETION;
  END;
END enable_metric_deletion;

PROCEDURE disable_metric_deletion IS
BEGIN
  BEGIN
    INSERT INTO MGMT_PARAMETERS
      (parameter_name, parameter_value, parameter_comment, internal_flag)
     VALUES
      (METRIC_DELETION, 'FALSE', METRIC_DELETION_CMT, 1);
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      UPDATE MGMT_PARAMETERS
         SET parameter_value = 'FALSE'
       WHERE parameter_name = METRIC_DELETION;
  END;
END disable_metric_deletion;

FUNCTION is_metric_deletion_enabled
  RETURN INTEGER IS
  l_retVal INTEGER := 1;     -- default value is 1 (enabled)
  l_cnt    INTEGER := 0;
BEGIN
  SELECT COUNT(*) into l_cnt
    FROM MGMT_PARAMETERS
   WHERE parameter_name=METRIC_DELETION
     AND upper(parameter_value)='FALSE';

  IF (l_cnt > 0) THEN
    -- metric_deletion_enabled property is FALSE
    l_retVal := 0;
  END IF;

  RETURN l_retVal;
END is_metric_deletion_enabled;


--
-- Function VERIFY_NAME
--
-- PURPOSE: To verity that a name is valid for roles and users 
-- IN parameters:
--   name: VARCHAR2 The name to be verified 
-- RETURNS:
--   G_TRUE: Name is valid
--   G_FALSE: Name is invalid
--


FUNCTION verify_name(string_in IN OUT VARCHAR2, min_len IN NUMBER, max_len IN NUMBER)
  RETURN NUMBER
IS

  ret NUMBER(1) := mgmt_global.G_TRUE;

BEGIN

  IF LENGTH(string_in) < min_len 
    OR LENGTH(string_in) > max_len THEN
    ret := mgmt_global.G_FALSE;
  END IF;

  RETURN ret;
    
END;

PROCEDURE add_target_deletion_callback
  (p_procedure_name_in IN VARCHAR2) IS
BEGIN
    em_target.add_tgt_post_deletion_callback(p_procedure_name_in);
END;

-- adds a target addition callback
PROCEDURE add_target_addition_callback
  (p_procedure_name_in IN VARCHAR2,
   p_target_type_in    IN VARCHAR2 DEFAULT ' ')
IS
BEGIN
    emd_loader.add_target_addition_callback(p_procedure_name_in,
                                            p_target_type_in);
END;

-- deletes a target deletion callback
PROCEDURE del_target_addition_callback
  (p_procedure_name_in IN VARCHAR2,
   p_target_type_in    IN VARCHAR2 DEFAULT ' ')
IS
BEGIN
    emd_loader.del_target_addition_callback(p_procedure_name_in,
                                            p_target_type_in);
END;



PROCEDURE add_target_deletion_exceptions(p_exceptions_in SMP_EMD_STRING_ARRAY) IS
unique_constraint EXCEPTION;
PRAGMA EXCEPTION_INIT(unique_constraint, -1);
BEGIN
    FOR i in 1..p_exceptions_in.count LOOP
    BEGIN
        INSERT INTO MGMT_TARGET_DELETE_EXCEPTIONS(table_name)
            VALUES(p_exceptions_in(i));

    EXCEPTION
        WHEN unique_constraint THEN
            -- Table already registered...OK
            NULL;
    END;
    END LOOP;    
END;

-- PURPOSE:
--  update the availability of the parent groups
--  upon deletion of a member target
PROCEDURE update_grp_avail_on_tgt_delete(
                          p_target_name IN VARCHAR2,
                          p_target_type IN VARCHAR2,
                          p_target_guid IN RAW)
IS

  -- When a target is deleted, group availability only need
  -- be updated for targets containing this target that have
  -- both 'is_group' and 'memberDerivedStatus' set
  CURSOR parent_cursor (v_target_guid RAW) IS
    SELECT SMP_EMD_NVPAIR(ct.target_name, ct.target_type)
    FROM MGMT_TARGET_ASSOCS TM, MGMT_TARGETS ct
    WHERE tm.assoc_target_guid = v_target_guid
    AND ct.target_guid = tm.source_target_guid
    AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
    AND EXISTS (SELECT 1 FROM MGMT_TYPE_PROPERTIES TP WHERE
                TP.TARGET_TYPE = CT.TARGET_TYPE AND
                TP.PROPERTY_NAME = MGMT_GLOBAL.G_DERIVED_STATUS_PROP)
    AND EXISTS (SELECT 1 FROM MGMT_TYPE_PROPERTIES TP WHERE
                TP.TARGET_TYPE = CT.TARGET_TYPE AND
                TP.PROPERTY_NAME = MGMT_GLOBAL.G_IS_GROUP_PROP);

  l_parent_list            SMP_EMD_NVPAIR_ARRAY;
  l_dummy_add_targets_list SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
  l_del_member_target_list SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();

BEGIN

  l_del_member_target_list.extend(1);
  l_del_member_target_list(1) := SMP_EMD_NVPAIR(p_target_name, p_target_type);

  -- fetch the list of parents and for each parent
  -- modify the group..
  OPEN parent_cursor(p_target_guid);

  FETCH parent_cursor BULK COLLECT INTO l_parent_list;

  IF parent_cursor%ISOPEN THEN
    CLOSE parent_cursor;
  END IF;

  IF l_parent_list IS NOT NULL AND l_parent_list.EXISTS(1) THEN
    --  For each composite target the target passed in is a member of,
    --  loop through
    FOR i IN l_parent_list.FIRST..l_parent_list.LAST LOOP
      mgmt_target.modify_group(l_parent_list(i).name, l_parent_list(i).value,
                               l_dummy_add_targets_list, l_del_member_target_list);
    END LOOP;
  END IF;  -- Parent cursor found

EXCEPTION
  WHEN OTHERS THEN
      IF (parent_cursor%ISOPEN) THEN
        CLOSE parent_cursor;
      END IF;
      RAISE;

END update_grp_avail_on_tgt_delete;

PROCEDURE submit_delete_job(p_target_name VARCHAR2,
                            p_target_type VARCHAR2,
                            p_parent_target_name VARCHAR2,
                            p_parent_target_type VARCHAR2,
                            p_member_name_array MGMT_JOB_VECTOR_PARAMS,
                            p_member_type_array MGMT_JOB_VECTOR_PARAMS,
                            p_member_emd_url_array MGMT_JOB_VECTOR_PARAMS,
                            p_association_array MGMT_JOB_VECTOR_PARAMS,
                            p_delete_members BOOLEAN,
                            p_delete_hosts BOOLEAN,
                            p_force_delete BOOLEAN) IS
l_job_params MGMT_JOB_PARAM_LIST;
l_job_targets MGMT_JOB_TARGET_LIST;
l_schedule MGMT_JOB_SCHEDULE_RECORD;
l_job_name VARCHAR2(64);
l_job_id RAW(16);
l_execution_id RAW(16);
l_delete_members VARCHAR2(10) := 'false';
l_delete_hosts VARCHAR2(10) := 'false';
l_force_delete VARCHAR2(10) := 'false';
BEGIN
    IF p_delete_members THEN
        l_delete_members := 'true';

        IF p_delete_hosts THEN
            l_delete_hosts := 'true';
        END IF;
    END IF;

    IF p_force_delete THEN
        l_force_delete := 'true';
    END IF;

    -- This job has no targets.
    l_job_targets := MGMT_JOB_TARGET_LIST();

    l_job_params := MGMT_JOB_PARAM_LIST();
    l_job_params.extend(11);
    l_job_params(1) := MGMT_JOB_PARAM_RECORD('targetName', 1,
                                             p_target_name, null);
    l_job_params(2) := MGMT_JOB_PARAM_RECORD('targetType', 1,
                                             p_target_type, null);
    l_job_params(3) := MGMT_JOB_PARAM_RECORD('parentTargetName', 1,
                                              p_parent_target_name, null);
    l_job_params(4) := MGMT_JOB_PARAM_RECORD('parentTargetType', 1,
                                              p_parent_target_type, null);
    l_job_params(5) := MGMT_JOB_PARAM_RECORD('memberTargetNames', 0,
                                              null, p_member_name_array);
    l_job_params(6) := MGMT_JOB_PARAM_RECORD('memberTargetTypes', 0,
                                              null, p_member_type_array);
    l_job_params(7) := MGMT_JOB_PARAM_RECORD('memberEmdUrls', 0,
                                              null, p_member_emd_url_array);
    l_job_params(8) := MGMT_JOB_PARAM_RECORD('associations', 0,
                                              null, p_association_array);
    l_job_params(9) := MGMT_JOB_PARAM_RECORD('deleteMembers', 1,
                                             l_delete_members, null);
    l_job_params(10) := MGMT_JOB_PARAM_RECORD('deleteHost', 1,
                                             l_delete_hosts, null);
    l_job_params(11) := MGMT_JOB_PARAM_RECORD('forceDelete', 1,
                                             l_force_delete, null);

    -- Its schedule is immediate.
    l_schedule := MGMT_JOBS.get_job_schedule_record(MGMT_JOBS.ONE_TIME_FREQUENCY_CODE,
                                           SYSDATE, null, 0, 0, 0, null, null,
                                           MGMT_JOBS.TIMEZONE_REPOSITORY, 
                                           0, 0, null);
  
    l_job_name := 'DELTGT_JOB_' || SYS_GUID();

    -- Submit the job as a system job so that system thread pool is used
    MGMT_JOBS.submit_job(l_job_name,
                        'This is a delete target job',
                        'DeleteTargetJob',
                        l_job_targets,
                        l_job_params,
                        l_schedule,
                        l_job_id,
                        l_execution_id,
                        null,
                        MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY);

END;

-- Overloaded implementation of delete_target
PROCEDURE delete_target(target_name_in VARCHAR2,
                        target_type_in VARCHAR2,
                        raise_composite_error_in BOOLEAN,
                        submit_job_in BOOLEAN,
                        delete_members_in BOOLEAN,
                        delete_hosts_in BOOLEAN,
                        force_delete_in BOOLEAN,
                        delete_sync_in BOOLEAN DEFAULT false) IS

  l_target_name     MGMT_TARGETS.target_name%TYPE;
  l_target_type     MGMT_TARGETS.target_type%TYPE;
  l_parent_target_name     MGMT_TARGETS.target_name%TYPE;
  l_parent_target_type     MGMT_TARGETS.target_type%TYPE;
  l_member_target_name     MGMT_TARGETS.target_name%TYPE;
  l_member_target_type     MGMT_TARGETS.target_type%TYPE;
  l_member_name_array MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
  l_member_type_array MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
  l_member_emd_url_array MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
  l_association_array MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
  l_target_guid     MGMT_TARGETS.target_guid%TYPE;
  l_host_guid       MGMT_TARGETS.target_guid%TYPE;
  l_emd_url         MGMT_TARGETS.emd_url%TYPE;
  l_timezone_region MGMT_TARGETS.timezone_region%TYPE;
  l_del_req_time    MGMT_TARGETS_DELETE.delete_request_time%TYPE;
  l_comp_prop_value MGMT_TYPE_PROPERTIES.property_value%TYPE;
  l_current_user    VARCHAR2(256) := MGMT_USER.get_current_em_user();
  l_delete_members  BOOLEAN := false;
  l_cnt             INTEGER := 0;
  l_tgt_already_deleted BOOLEAN := false;
BEGIN

  BEGIN
    -- Get the target details..
    SELECT target_name, target_type, target_guid, emd_url, timezone_region
      INTO l_target_name, l_target_type, l_target_guid, l_emd_url, l_timezone_region
      FROM mgmt_targets 
     WHERE target_name = target_name_in
       AND target_type = target_type_in;

    -- Is this an agent target?
    IF l_target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE THEN
        SELECT count(*) into l_cnt
          FROM MGMT_TARGETS
         WHERE emd_url = l_emd_url
           AND target_type <> MGMT_GLOBAL.G_AGENT_TARGET_TYPE;

        IF l_cnt > 0 THEN
            raise_application_error(MGMT_GLOBAL.TARGET_CANNOT_BE_DELETED_ERR,
          'Target ' || l_target_name || ':' || l_target_type ||
          ' is monitoring other targets. It cannot be deleted');
        END IF;
    END IF;

    -- Is this a composite target?
    BEGIN
        SELECT property_value INTO l_comp_prop_value 
          FROM MGMT_TYPE_PROPERTIES 
         WHERE target_type = l_target_type 
           AND property_name = MGMT_GLOBAL.G_IS_COMPOSITE_PROP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
           l_comp_prop_value := NULL;
    END; 

    -- Get the parent cluster target name.
    -- Do not get the parent cluster name if the agent is misconfigured and has
    -- more than one instances of the cluster on the same agent.
    BEGIN

        SELECT ct.target_name, ct.target_type into l_parent_target_name, l_parent_target_type
          FROM MGMT_TARGETS t, MGMT_TARGETS ct, MGMT_TARGET_ASSOCS m, MGMT_TYPE_PROPERTIES p
         WHERE t.target_name = l_target_name
           AND t.target_type = l_target_type
           AND t.target_guid = m.assoc_target_guid
           AND m.source_target_guid = ct.target_guid
           AND m.assoc_guid = mgmt_assoc.g_contains_guid
           AND ct.target_type = p.target_type
           AND trim(ct.emd_url) IS NOT NULL
           AND p.property_name = MGMT_GLOBAL.G_IS_CLUSTER_PROP
           AND NOT EXISTS(
                SELECT ot.target_name 
                  FROM MGMT_TARGET_ASSOCS om, MGMT_TARGETS ot
                 WHERE ct.target_guid = om.SOURCE_TARGET_GUID
                   AND om.ASSOC_TARGET_GUID = ot.TARGET_GUID
                   AND om.assoc_guid = mgmt_assoc.g_contains_guid 
                   AND t.emd_url = ot.emd_url
                   AND ot.target_guid != t.target_guid)
           AND ROWNUM = 1;

        IF (EMDW_LOG.p_is_info_set)
        THEN
            EMDW_LOG.info('Parent Target Name :'||l_parent_target_name||': Parent Target Type :'||l_parent_target_type, 'MGMT_ADMIN.delete_target');
        END IF;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
           BEGIN
             l_parent_target_name := ' ';
             l_parent_target_type := ' ';
             IF (EMDW_LOG.p_is_info_set)
             THEN
                 EMDW_LOG.info('Putting Default values for Cluster Target Name :'||l_parent_target_name||': Cluster Target Type :'||l_parent_target_type, 'MGMT_ADMIN.delete_target');
             END IF;
           END;
    END; 

    IF delete_members_in THEN
        -- The target being deleted must be a composite target
        IF l_comp_prop_value=1 THEN
            l_delete_members := true;
        ELSIF raise_composite_error_in THEN
          raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
            'Target ' || l_target_name || ':' || l_target_type ||
            ' is not a composite target: delete_members cannot be set');
        END IF;
    END IF;

    -- The user issuing this call must have FULL privilege on the target
    IF MGMT_USER.has_priv(l_current_user, MGMT_USER.FULL_TARGET, 
                          l_target_name, l_target_type) = 0 THEN
        raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
        'The current user does not have sufficient privileges to perform this action');
    END IF;

    BEGIN 
      INSERT INTO mgmt_targets_delete 
        (target_name, target_type, target_guid, emd_url, 
         timezone_region, delete_request_time)
        VALUES
        (l_target_name, l_target_type, l_target_guid, l_emd_url,
         l_timezone_region, MGMT_GLOBAL.SYSDATE_TZRGN(l_timezone_region) );
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        -- If there is already a record in mgmt_targets_delete
	-- Can happen if the target is deleted, added and is being deleted again.
	UPDATE mgmt_targets_delete
	   SET emd_url = l_emd_url,
	       timezone_region = l_timezone_region,
	       delete_request_time = MGMT_GLOBAL.SYSDATE_TZRGN(l_timezone_region),
	       delete_complete_time = NULL
         WHERE target_guid = l_target_guid;
    END;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- Target does not exist in targets table, check in mgmt_targets_delete
      BEGIN
        SELECT target_name, target_type, target_guid 
	  INTO l_target_name, l_target_type, l_target_guid
          FROM mgmt_targets_delete 
	 WHERE target_name = target_name_in 
           AND target_type = target_type_in
	   AND delete_complete_time IS NULL;

        IF(force_delete_in)
        THEN
          l_tgt_already_deleted := true;
          l_parent_target_name := ' ';
          l_parent_target_type := ' ';
        ELSE
          raise_application_error(MGMT_GLOBAL.TARGET_BEING_DELETED_ERR,
          'Target ' || l_target_name || ':' || l_target_type ||
          ' is currently in the process of being deleted');
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          raise_application_error(mgmt_global.TARGET_DOES_NOT_EXIST_ERR, 
	    mgmt_global.TARGET_DOES_NOT_EXIST_ERR_M);
      END;
  END;

  IF(NOT l_tgt_already_deleted) THEN

    IF l_delete_members THEN
      BEGIN

        FOR crec IN ( 
        SELECT member.target_name target_name, member.target_type target_type,
             member.emd_url emd_url, MGMT_ASSOC.ASSOC_DEF_CONTAINS association
        FROM MGMT_FLAT_TARGET_ASSOC assoc, mgmt_targets member
        WHERE assoc.source_target_guid = l_target_guid
         AND member.target_guid = assoc.assoc_target_guid
         AND assoc.is_membership = 1)
        LOOP
          l_member_name_array.extend(1);
          l_member_type_array.extend(1);
          l_member_emd_url_array.extend(1);
          l_association_array.extend(1);
          l_member_name_array(l_member_name_array.count) := crec.target_name;
          l_member_type_array(l_member_type_array.count) := crec.target_type;
          l_member_emd_url_array(l_member_emd_url_array.count) := crec.emd_url;
          l_association_array(l_association_array.count) := crec.association;
          IF (EMDW_LOG.p_is_info_set)
             THEN
              EMDW_LOG.info(l_member_name_array.count||'Inserted target name:'||l_member_name_array(l_member_name_array.count)||' target_type:'||l_member_type_array(l_member_type_array.count)||'Association :'||l_association_array(l_association_array.count),'MGMT_ADMIN.delete_target');
          END IF;
        END LOOP; 
      EXCEPTION WHEN OTHERS THEN
        EMDW_LOG.error('Caught Exception :'||SQLERRM,'MGMT_ADMIN.delete_target');
      END;
    END IF;


    -- execute pre delete callbacks 
    --EM_TARGET.exec_tgt_pre_del_callbacks(l_target_name, l_target_type);
    EM_TARGET.exec_tgt_pre_del_callbacks(NEW SMP_EMD_TGT_OBJECT_TBL(NEW SMP_EMD_TGT_OBJECT(l_target_name,l_target_type,l_target_guid,NULL,NULL,NULL)));

    -- First delete all members, if requested
    IF l_delete_members THEN
      IF l_member_name_array IS NOT NULL AND l_member_name_array.COUNT > 0
         AND l_member_type_array IS NOT NULL AND l_member_type_array.COUNT > 0
        THEN
          FOR i IN l_member_name_array.FIRST..l_member_name_array.LAST 
          LOOP
          -- Do not delete host members of composite targets
          -- unless delete_hosts is true
            IF NOT delete_hosts_in AND l_member_type_array(i)=MGMT_GLOBAL.G_HOST_TARGET_TYPE THEN
               NULL;
            ELSE
              IF (EMDW_LOG.p_is_info_set)
                THEN
                  EMDW_LOG.info('Deleting Child Target Name :'||l_member_name_array(i)||': Child Target Type :'||l_member_type_array(i), 'MGMT_ADMIN.delete_target');
              END IF;
            -- As we get the members list from mgmt_flat_target_assoc there is no need to delete the members recursively.
            delete_target(l_member_name_array(i), l_member_type_array(i),
                        false, false, false, delete_hosts_in,
                        force_delete_in, delete_sync_in);
            END IF;
          END LOOP;
        END IF;
    END IF;


    -- update the availability of parent groups of this target - if any..
    BEGIN
      -- we go into super_user mode here because modify_group expects user to have
      -- OPERATOR_TARGET privilege on the group inorder to modify its
      -- membership. The user deleting this target may not have this access
      -- on all possible parent groups.
      SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, 
                     MGMT_USER.OP_SET_IDENTIFIER);

      update_grp_avail_on_tgt_delete(l_target_name, l_target_type, 
                                     l_target_guid);

      SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);

    EXCEPTION
      WHEN OTHERS THEN
        IF l_current_user IS NOT NULL THEN
          SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
        END IF;
        RAISE;
    END;

    -- Remove the entry for the target from the target table
    DELETE FROM mgmt_targets WHERE target_guid = l_target_guid; 
  END IF;
  -- Submit a job to continue with the deletion
  IF submit_job_in THEN
    submit_delete_job(l_target_name, l_target_type, 
                      l_parent_target_name, l_parent_target_type,
                      l_member_name_array, l_member_type_array, 
                      l_member_emd_url_array, l_association_array,
                      l_delete_members, delete_hosts_in,
                      force_delete_in);
  ELSIF delete_sync_in THEN
    COMMIT;

    -- Delete the target synchronously
    delete_target_internal(l_target_name, l_target_type);

    -- Delete the mgmt_targets_delete entry also.
    DELETE FROM mgmt_targets_delete WHERE target_guid = l_target_guid;

  END IF;
  
END;

-- This version of delete_target for repository regressions
-- only
PROCEDURE delete_target_sync(target_name_in VARCHAR2,
                             target_type_in VARCHAR2,
                             delete_members_in NUMBER DEFAULT 0,
                             delete_hosts_in NUMBER DEFAULT 0) IS
l_delete_members BOOLEAN := false;
l_delete_hosts BOOLEAN := false;
BEGIN
    IF delete_members_in=1 THEN
        l_delete_members := true;

        IF delete_hosts_in=1 THEN
            l_delete_hosts := true;
        END IF;
    END IF;

    delete_target(target_name_in, target_type_in, 
                  true, false, l_delete_members,
                  l_delete_hosts, false, true);
END;

-- SDK implementation of delete_target
PROCEDURE delete_target(target_name_in VARCHAR2,
                        target_type_in VARCHAR2,
                        delete_members_in NUMBER DEFAULT 0,
                        delete_hosts_in NUMBER DEFAULT 0,
                        force_delete_in NUMBER DEFAULT 0) IS
l_delete_members BOOLEAN := false;
l_delete_hosts BOOLEAN := false;
l_force_delete BOOLEAN := false;
BEGIN
    IF delete_members_in=1 THEN
        l_delete_members := true;

        IF delete_hosts_in=1 THEN
            l_delete_hosts := true;
        END IF;
    END IF;

    IF force_delete_in=1 THEN
        l_force_delete := true;
    END IF;

    delete_target(target_name_in, target_type_in, 
                  true, true, l_delete_members,
                  l_delete_hosts, l_force_delete);
END;

-- Actually delete the target from the repository. This should
-- never be called externally. It is only called from the system
-- job that deletes the target from the emd   
-- Note: throws TARGET_DOES_NOT_EXIST_ERR if the target is not marked for
-- deletion
PROCEDURE delete_target_internal
  (target_name_in IN VARCHAR2,
   target_type_in IN VARCHAR2,
   del_complete_time_in IN VARCHAR2 DEFAULT NULL)
IS

  l_target_name MGMT_TARGETS.target_name%TYPE;
  l_target_type MGMT_TARGETS.target_type%TYPE;
  l_target_guid MGMT_TARGETS.target_guid%TYPE;
  l_target_guids MGMT_TARGET_GUID_ARRAY := NEW MGMT_TARGET_GUID_ARRAY();
  l_timezone_rgn MGMT_TARGETS.timezone_region%TYPE;
  l_emd_url     MGMT_TARGETS_DELETE.emd_url%TYPE;
  l_del_complete_time MGMT_TARGETS_DELETE.delete_complete_time%TYPE;
  l_delete_stmt VARCHAR2(400);
  l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;
  counter INTEGER :=0;
  l_comp_prop_value MGMT_TYPE_PROPERTIES.property_value%TYPE;
  l_grp_prop_value  MGMT_TYPE_PROPERTIES.property_value%TYPE;
  l_del_pending_target_names SMP_EMD_STRING_ARRAY;
  l_del_pending_target_types SMP_EMD_STRING_ARRAY;

BEGIN
  -- Ensure that the caller is a super-user.
  IF MGMT_USER.has_priv(l_current_user, 'SUPER_USER') = 0 THEN
      raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
       'Only superusers can call delete_target_internal');
  END IF;

  BEGIN
      SELECT target_name, target_type, target_guid, timezone_region, emd_url
        INTO l_target_name, l_target_type, l_target_guid, l_timezone_rgn, l_emd_url
        FROM mgmt_targets_delete 
       WHERE target_name = target_name_in 
         AND target_type = target_type_in
         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 = ' || target_name_in || 
          ' and Target type = ' || target_type_in);   
  END;

  -- Is this an agent target?
  IF l_target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE THEN

      
      -- Retrieve targets pending deletion from this agent 
      SELECT target_name, target_type
             BULK COLLECT INTO l_del_pending_target_names, l_del_pending_target_types
        FROM MGMT_TARGETS_DELETE
       WHERE emd_url = l_emd_url
         AND target_type <> MGMT_GLOBAL.G_AGENT_TARGET_TYPE
         AND delete_complete_time IS NULL;

      IF l_del_pending_target_names IS NOT NULL AND 
         l_del_pending_target_names.count > 0 THEN
          -- The jobs previously submitted with force_delete=false for the pending 
          -- targets cannot succeed at this point because the agent has been stopped.
          -- Call delete_target_internal on all pending targets to force delete them.
          -- Their jobs will complete after they get target_does_not_exist exception.
          FOR i IN 1..l_del_pending_target_names.count LOOP
              BEGIN
                  delete_target_internal(l_del_pending_target_names(i),
                                         l_del_pending_target_types(i));
              EXCEPTION
                 WHEN MGMT_GLOBAL.target_does_not_exist THEN 
                     NULL; -- ignore, target has already been deleted
              END;
          END LOOP;
      END IF;

   -- Switch the master agent for multi-agent targets if necessary
      em_master_agent.process_agent_deletion(l_target_guid);
  END IF;

  IF (del_complete_time_in IS NULL) THEN
    l_del_complete_time := MGMT_GLOBAL.SYSDATE_TZRGN(l_timezone_rgn);
  ELSE
    l_del_complete_time := TO_DATE(del_complete_time_in, 
                                   MGMT_GLOBAL.G_AGENT_DATE_FORMAT);
  END IF;

  FOR crec IN ( SELECT o.name table_name , c.name column_name
        from sys.obj$ o, sys.tab$ t ,sys.col$ c
        where
        o.owner# = userenv('SCHEMAID')
        and c.name like ('%TARGET_GUID%')
        and c.obj# = o.obj#
        and (o.name like ('MGMT_%') OR o.name like ('OCS_%'))
        and o.obj# = t.obj#
        and bitand(t.property, 1) = 0
        and bitand(c.property,32) = 0
        and bitand(c.property,512) = 0
        and o.name not in (SELECT table_name
            FROM MGMT_TARGET_DELETE_EXCEPTIONS)
        order by o.name
          )
  LOOP
    l_delete_stmt := 'DELETE FROM ' || EM_CHECK.SIMPLE_SQL_NAME(crec.table_name)|| 
      ' WHERE ' || EM_CHECK.SIMPLE_SQL_NAME(crec.column_name)|| ' = HEXTORAW(:target_guid)
          AND ROWNUM <= :batchsize';
    LOOP
      EXECUTE IMMEDIATE l_delete_stmt
                  USING l_target_guid , MGMT_GLOBAL.MAX_COMMIT ;
      counter := SQL%ROWCOUNT;
      COMMIT;        
      IF counter < mgmt_global.MAX_COMMIT THEN
        EXIT;
      END IF;

    END LOOP;
  END LOOP;
  
  l_target_guids.extend;
  l_target_guids(l_target_guids.LAST) := l_target_guid;
  IF (is_metric_deletion_enabled > 0) THEN
    delete_target_metrics_raw(l_target_guids);
    delete_target_metrics_1hour(l_target_guids);
    delete_target_metrics_1day(l_target_guids);
  END IF;

  -- save point..
  COMMIT;

  EM_TARGET.exec_tgt_post_del_callbacks(NEW SMP_EMD_TGT_OBJECT_TBL(NEW SMP_EMD_TGT_OBJECT(l_target_name,l_target_type,l_target_guid,NULL,NULL,NULL)));

  DELETE FROM mgmt_notify_rule_configs 
   WHERE target_name = l_target_name 
     AND target_type = l_target_type;
  COMMIT;

  --DELETE FROM mgmt_ecm_snapshot 
  -- WHERE target_name = l_target_name 
  --   AND target_type = l_target_type 
  --   AND is_current = 'Y';
  --COMMIT;

  IF (l_emd_url IS NULL) THEN
    -- Delete the targets_delete entry, as there is no agent to send
    -- data 
    DELETE FROM mgmt_targets_delete
     WHERE target_guid=l_target_guid;
  ELSE
    -- Do not delete the entry from mgmt_targets_delete 
    -- but set the delete completion timestamp
    -- This entry will be used by the loader to block any delayed data
    -- being loaded into the repository.
    -- Any data/severity/.. will be discarded if it is before the
    -- completion time
    -- We are using Agent completion time (which accounts
    -- for time skew also).
    -- This entry would be deleted when loader receives data from this agent
    -- 1 hr past the completion time 
    UPDATE mgmt_targets_delete
       SET delete_complete_time = l_del_complete_time
     WHERE target_guid=l_target_guid;
  END IF;

  COMMIT;

END;


PROCEDURE cleanup_agent(v_agent_name_in IN VARCHAR2) IS
  l_emd_url MGMT_TARGETS.emd_url%TYPE;
  l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;
  l_agent_guid MGMT_TARGETS.target_guid%TYPE;
BEGIN
  -- Ensure that the caller is a super-user.
  IF MGMT_USER.has_priv(l_current_user, 'SUPER_USER') = 0 THEN
      raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
       'Only superusers can call cleanup_agent');
  END IF;

  BEGIN
    SELECT emd_url, target_guid INTO l_emd_url, l_agent_guid
      FROM MGMT_TARGETS
     WHERE target_name = v_agent_name_in
       AND target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      raise_application_error(mgmt_global.TARGET_DOES_NOT_EXIST_ERR, 
          mgmt_global.TARGET_DOES_NOT_EXIST_ERR_M || v_agent_name_in );
  END;

  -- Delete duplicate targets..
  DELETE FROM MGMT_DUPLICATE_TARGETS WHERE duplicate_emd_url = l_emd_url;

  -- Delete targets to be deleted.
  DELETE FROM MGMT_TARGETS_DELETE WHERE emd_url = l_emd_url;

  COMMIT; 
  EM_TARGET.DELETE_AGENT_TARGET(v_agent_name_in, MGMT_GLOBAL.G_AGENT_TARGET_TYPE, TRUE, FALSE, TRUE);

END cleanup_agent;

--
-- PURPOSE 
--   Procedure to set the ping interval for an agent
-- 

PROCEDURE set_inactive_time(p_agent_guid IN RAW,
                              p_max_inactive_time IN NUMBER) IS
  BEGIN

    UPDATE mgmt_emd_ping
       SET max_inactive_time = p_max_inactive_time
     WHERE target_guid = p_agent_guid;

  END set_inactive_time;



PROCEDURE set_inactive_time(p_agent_name IN VARCHAR2,
                              p_max_inactive_time IN NUMBER) IS
  l_agent_guid MGMT_TARGETS.target_guid%TYPE;

  BEGIN
    BEGIN
      SELECT target_guid INTO l_agent_guid
	      FROM MGMT_TARGETS
       WHERE target_name = p_agent_name
     	 AND target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE;

      UPDATE mgmt_emd_ping
         SET max_inactive_time = p_max_inactive_time
       WHERE target_guid = l_agent_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 = ' || p_agent_name || ') ' ||
          ' (target_type = ' || MGMT_GLOBAL.G_AGENT_TARGET_TYPE || ')');
    END;
  END set_inactive_time;

--
-- PURPOSE 
--   Procedure to set the logging level for system errors.
--   Valid values are: 'ERROR', 'WARN', 'INFO' and 'DEBUG'
--   The default level is 'ERROR'.  Set this if additional
--   system level error logging is needed.  
-- 
PROCEDURE set_log_level(level_in IN VARCHAR2) IS
  BEGIN
    IF level_in IN ('ERROR', 'WARN', 'INFO', 'DEBUG')
    THEN
       UPDATE mgmt_parameters SET parameter_value=level_in 
       WHERE parameter_name = 'system_error_log_level';
    END IF;
END set_log_level;


--
-- PURPOSE 
--   Procedure to set the purge window for system errors.  Positive numbers only,
--   rounded to integers.  The default is 168 hours (7 days).  A value of '0' causes 
--   errors to be purged hourly.  
-- 
PROCEDURE set_log_purge(hours_in IN NUMBER DEFAULT 168) IS
BEGIN
  IF hours_in > 0
  THEN
    emd_loader.p_LogPurgeWindow := ROUND(hours_in,0);
  END IF;
END;

--
-- PURPOSE
--   Procedure to delete an oms from the repository
--
PROCEDURE delete_oms(omsname_in IN VARCHAR2) IS

  l_target_guid RAW(16);

BEGIN
  SELECT target_guid
    INTO l_target_guid
    FROM mgmt_targets
      WHERE target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE
      AND ROWNUM = 1;

  DELETE FROM mgmt_oms_parameters WHERE host_url = omsname_in;
  DELETE FROM mgmt_current_metrics
    WHERE target_guid = l_target_guid 
    AND key_value = omsname_in;
  DELETE FROM mgmt_metric_thresholds 
    WHERE target_guid = l_target_guid 
    AND key_value = omsname_in;
END;

--
-- PURPOSE 
--   Procedure to get the Management System name.
-- 
FUNCTION get_ms_name RETURN VARCHAR2
IS
  l_name VARCHAR2(256);
BEGIN
  BEGIN
    SELECT parameter_value 
      INTO l_name 
      FROM mgmt_parameters 
      WHERE parameter_name = 'management_system_name';
  EXCEPTION
    WHEN NO_DATA_FOUND 
    THEN
      l_name := mgmt_global.G_MANAGEMENT_SYSTEM;
  END;
  RETURN l_name;

END;

--
-- PURPOSE
--  Returns a cursor containing: oms_name, url_name, url_value.
--  url_names include:
--     upload_url
--     secure_upload_url
--     console_url
--     secure_console_url
--  There will be at most one upload_port and optionally one secure_upload_port per oms.  There may be
--  any number of either flavor of console  
--
FUNCTION get_oms_urls RETURN CURSORTYPE IS
  l_oms_urls CURSORTYPE;
BEGIN
  OPEN l_oms_urls FOR
    SELECT host_url, name, value
    FROM mgmt_oms_parameters 
    WHERE name IN ('upload_port', 'secure_upload_port', 'console_port', 'secure_console_port')
    ORDER BY host_url;
  RETURN l_oms_urls; 
END;

--
-- PURPOSE
--  Returns up/down status of a given OMS, or if no parameter passed, 
--  number of OMSes that are up. If parameter is passed and not null, 
--  MGMT_GLOBAL.G_STATUS_UP or MGMT_GLOBAL.G_STATUS_DOWN are returned
--  if matching registered OMS is found. If there is no registered 
--  OMS with submitted non-null p_oms_host_url, MGMT_GLOBAL.G_STATUS_UNKNOWN 
--  is returned.
--
--  Parameter p_oms_host_url, if not null, comes from  
--  host_url column of MGMT_OMS_PARAMETERS table.
--
FUNCTION get_oms_status(p_oms_host_url IN VARCHAR2 DEFAULT NULL) RETURN NUMBER
IS
  l_result NUMBER := MGMT_GLOBAL.G_STATUS_UNKNOWN;
BEGIN
  IF p_oms_host_url IS NULL THEN
    SELECT COUNT(DISTINCT host_url) INTO l_result 
    FROM MGMT_FAILOVER_TABLE
    WHERE sysdate - last_time_stamp < heartbeat_interval * 4; 
  ELSE
    BEGIN
      SELECT 
        CASE WHEN 
          EXISTS (SELECT * FROM MGMT_FAILOVER_TABLE 
                   WHERE host_url = p_oms_host_url
                     AND sysdate - last_time_stamp < heartbeat_interval * 4)
        THEN MGMT_GLOBAL.G_STATUS_UP 
        ELSE MGMT_GLOBAL.G_STATUS_DOWN 
        END 
      INTO l_result 
      FROM dual
      WHERE EXISTS (SELECT * FROM MGMT_OMS_PARAMETERS
                     WHERE host_url = p_oms_host_url);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL; -- return G_STATUS_UNKNOWN
    END;
  END IF;
  RETURN l_result;
END;

--
-- Internal procedures to delete the rows of a target from the following tables
--   MGMT_METRICS_RAW
--   MGMT_METRICS_1HOUR
--   MGMT_METRICS_1DAY
--
PROCEDURE delete_target_metrics_data(p_target_guids IN MGMT_TARGET_GUID_ARRAY,
                                           p_tabname_in IN VARCHAR2,
                                           p_start_date_in IN DATE,
                                           p_end_date_in IN DATE) IS
  l_curr_date  DATE;
  l_max_time   DATE;
  l_counter    NUMBER;
  l_col_name    VARCHAR2(4000);
  l_delete_stmt VARCHAR2(4000);
  l_target_guid RAW(16);
  l_table_name  user_tables.table_name%TYPE ;
BEGIN
  l_table_name := DBMS_ASSERT.SQL_OBJECT_NAME(p_tabname_in) ;
  IF p_tabname_in = 'MGMT_METRICS_RAW' THEN
    l_col_name := 'collection_timestamp';
  ELSE -- ELSIF p_tabname_in = 'MGMT_METRICS_1HOUR'  OR p_tabname_in = 'MGMT_METRICS_1HOUR' THEN
    l_col_name := 'rollup_timestamp';
  END IF;

  FOR i in 1 .. p_target_guids.count
  LOOP
    l_curr_date := p_start_date_in;

    WHILE (l_curr_date <= p_end_date_in)
     LOOP
      IF p_tabname_in = 'MGMT_METRICS_RAW' THEN
        l_max_time := l_curr_date + 1/24;
      ELSE -- ELSIF p_tabname_in = 'MGMT_METRICS_1HOUR'  OR p_tabname_in = 'MGMT_METRICS_1HOUR' THEN
        l_max_time := l_curr_date + 1;
      END IF;
      LOOP -- endless loop

        l_target_guid :=p_target_guids(i);
        l_delete_stmt := 'DELETE FROM ' || l_table_name  ||
                          ' WHERE target_guid = HEXTORAW(:target_guid)' ||
                           ' AND ' || l_col_name || ' >= :curr_date'    ||
                           ' AND ' || l_col_name || ' < :max_time'      ||
                           ' AND ROWNUM <= :max_count';

        EXECUTE IMMEDIATE l_delete_stmt
          USING l_target_guid, l_curr_date, l_max_time, MGMT_GLOBAL.MAX_COMMIT;

        l_counter := SQL%ROWCOUNT;      
        COMMIT;        
        IF l_counter < mgmt_global.MAX_COMMIT THEN
          EXIT;
        END IF;
      END LOOP; -- endless loop

      IF p_tabname_in = 'MGMT_METRICS_RAW' THEN
        l_curr_date := l_curr_date + 1/24;
      ELSE -- ELSIF p_tabname_in = 'MGMT_METRICS_1HOUR'  OR p_tabname_in = 'MGMT_METRICS_1HOUR' THEN
        l_curr_date := l_curr_date + 1;
      END IF;

    END LOOP; -- while loop
  END LOOP; -- for loop for each target

END delete_target_metrics_data;

PROCEDURE delete_target_metrics_raw(p_target_guids IN MGMT_TARGET_GUID_ARRAY) IS
  l_start_date DATE;
  l_end_date   DATE;
  l_counter    NUMBER;
BEGIN

  l_start_date := TRUNC(sysdate - (EMD_LOADER.get_raw_retention_window + 1));
  l_end_date := TRUNC(sysdate + 1/12, 'HH24');

  delete_target_metrics_data(p_target_guids, 'MGMT_METRICS_RAW', l_start_date, l_end_date);

  -- The following two loops will delete data from the overflow segments. 
  -- This loop will delete data from the lower range
  FOR i in 1 .. p_target_guids.count
  LOOP
    LOOP
      DELETE FROM MGMT_METRICS_RAW
       WHERE target_guid = p_target_guids(i)
         AND collection_timestamp < l_start_date
         AND ROWNUM <= mgmt_global.MAX_COMMIT;

      l_counter := SQL%ROWCOUNT;      
      COMMIT;
      IF l_counter < mgmt_global.MAX_COMMIT THEN
        EXIT;
      END IF;
    END LOOP;
  END LOOP;

  -- This loop will delete data from the upper range
  FOR i in 1 .. p_target_guids.count
  LOOP
    LOOP
      DELETE FROM MGMT_METRICS_RAW
       WHERE target_guid = p_target_guids(i)
         AND collection_timestamp > l_end_date
         AND ROWNUM <= mgmt_global.MAX_COMMIT;

      l_counter := SQL%ROWCOUNT;      
      COMMIT;
      IF l_counter < mgmt_global.MAX_COMMIT THEN
        EXIT;
      END IF;
    END LOOP;
  END LOOP;

END delete_target_metrics_raw;

PROCEDURE delete_target_metrics_1hour(p_target_guids IN MGMT_TARGET_GUID_ARRAY) IS
  l_start_date DATE;
  l_end_date   DATE;
BEGIN
  l_start_date := TRUNC(sysdate - (EMD_LOADER.get_1hour_retention_window + 1));
  l_end_date := TRUNC(sysdate + 2);

  delete_target_metrics_data(p_target_guids, 'MGMT_METRICS_1HOUR', l_start_date, l_end_date);

END delete_target_metrics_1hour;

PROCEDURE delete_target_metrics_1day(p_target_guids IN MGMT_TARGET_GUID_ARRAY) IS
  l_start_date DATE;
  l_end_date   DATE;
BEGIN
  l_start_date := TRUNC(sysdate - (EMD_LOADER.get_1day_retention_window + 1));
  l_end_date := TRUNC(sysdate + 2);

  delete_target_metrics_data(p_target_guids, 'MGMT_METRICS_1DAY', l_start_date, l_end_date);

END delete_target_metrics_1day;

--
-- PURPOSE
--  Returns a cursor containing: oms_name, host_name, oc4j_name, ias_name
--
--  0 or 1 input parameters may be provided.
--
--  If 0 input parameters are provided:
--     the function will return all omss with their associated host, oc4j and ias.
--
--  If 1 input parameter is provided:
--     the function will return the missing target names, if there is an associated oms.  If there
--     is no associate oms, the function will return null.  If more than one input parameter is
--     provided, all but the first one encountered will be ignored.  If a host_name is provided,
--     all omss on the host will be returned as individual rows in the cursor.  All others return
--     a cursor with one row.
--
FUNCTION get_oms_data (p_oms_name IN VARCHAR2 DEFAULT NULL,
                   p_host_name IN VARCHAR2 DEFAULT NULL,
                   p_oc4j_name IN VARCHAR2 DEFAULT NULL,
                   p_ias_name IN VARCHAR2 DEFAULT NULL) RETURN CURSORTYPE IS

  l_oms_data CURSORTYPE;
  err NUMBER;
  errm VARCHAR2(4000);

BEGIN
  
  -- an oms name is provided
  IF p_oms_name IS NOT NULL
  THEN
      OPEN l_oms_data FOR
        SELECT p_oms_name oms_name,
          NVL(aa.target_name,'None') host_name,
          NVL(bb.target_name,'None') oc4j_name,
          NVL(ct.target_name, 'None') ias_name
        FROM mgmt_oms_parameters a,
             mgmt_oms_parameters b,
             mgmt_targets aa,
             mgmt_targets bb,
             mgmt_target_properties c,
             mgmt_target_properties d,
             mgmt_target_assocs e,
             mgmt_targets ct
        WHERE a.host_url = p_oms_name
          AND a.name = 'ORACLE_HOME'
          AND b.name = 'HOST_NAME'
          AND aa.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
          AND bb.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE
          AND bb.target_name like '%OC4J_EM'
          AND c.target_guid = bb.target_guid
          AND d.target_guid = bb.target_guid
          AND c.property_name = 'HTTPMachine'
          AND d.property_name = 'OracleHome'
          AND aa.host_name = b.value
          AND c.property_value = b.value
          AND d.property_value = a.value
          AND ct.target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE
          AND e.assoc_guid = MGMT_ASSOC.g_contains_guid
          AND e.source_target_guid = ct.target_guid
          ANd e.assoc_target_guid = bb.target_guid;
    RETURN l_oms_data;
  END IF;

  -- a host_name is provided
  IF p_host_name IS NOT NULL
  THEN
    -- get all omss on this host
      OPEN l_oms_data FOR
        SELECT a.host_url oms_name,
           p_host_name host_name,
           NVL(bb.target_name,'None') oc4j_name,
           NVL(ct.target_name, 'None') ias_name
        FROM mgmt_oms_parameters a,
            mgmt_oms_parameters b,
            mgmt_targets bb,
            mgmt_target_properties c,
            mgmt_target_properties d,
            mgmt_target_assocs e,
            mgmt_targets ct
         WHERE a.host_url = b.host_url
         AND a.name = 'ORACLE_HOME'
         AND b.name = 'HOST_NAME'
         AND b.value = p_host_name
         AND bb.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE
         AND bb.target_name like '%OC4J_EM'
         AND c.target_guid = bb.target_guid
         AND d.target_guid = bb.target_guid
         AND c.property_name = 'HTTPMachine'
         AND d.property_name = 'OracleHome'
         AND c.property_value = p_host_name
         AND d.property_value = a.value
         AND ct.target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE
         AND e.assoc_guid = MGMT_ASSOC.g_contains_guid
         AND e.source_target_guid = ct.target_guid
         ANd e.assoc_target_guid = bb.target_guid;
    RETURN l_oms_data;
  END IF;
  
  IF p_oc4j_name IS NOT NULL 
  THEN
      OPEN l_oms_data FOR
        SELECT a.host_url oms_name,
          NVL(aa.target_name,'None') host_name,
          p_oc4j_name oc4j_name,
          NVL(ct.target_name,'None') ias_name
          FROM mgmt_oms_parameters a,
            mgmt_oms_parameters b,
            mgmt_targets aa,
            mgmt_targets bb,
            mgmt_target_properties c,
            mgmt_target_properties d,
            mgmt_target_assocs e,
            mgmt_targets ct
          WHERE a.host_url = b.host_url
          AND a.name = 'ORACLE_HOME'
          AND b.name = 'HOST_NAME'
          AND a.value = d.property_value
          AND b.value = c.property_value
          AND aa.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
          AND bb.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE
          AND bb.target_name = p_oc4j_name
          AND aa.host_name = bb.host_name
          AND d.target_guid = bb.target_guid
          AND c.target_guid = bb.target_guid
          AND c.property_name = 'HTTPMachine'
          AND d.property_name = 'OracleHome'
          AND ct.target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE
          AND e.assoc_guid = MGMT_ASSOC.g_contains_guid
          AND e.source_target_guid = ct.target_guid
          AND e.assoc_target_guid = bb.target_guid;
    RETURN l_oms_data;
  END IF;
  
  IF p_ias_name IS NOT NULL
  THEN
      OPEN l_oms_data FOR
        SELECT a.host_url oms_name, 
        NVL(aa.target_name,'None') host_name,
        NVL(bb.target_name,'None') oc4j_name,
        p_ias_name ias_name
      FROM mgmt_oms_parameters a,
          mgmt_oms_parameters b,
          mgmt_targets aa,
          mgmt_targets bb,
          mgmt_target_properties d,
          mgmt_target_assocs e,
          mgmt_targets ct
        WHERE a.host_url = b.host_url
          AND a.name = 'ORACLE_HOME'
          AND b.name = 'HOST_NAME'
          AND a.value = d.property_value
          AND b.value = aa.host_name
          AND aa.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
          AND bb.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE
          AND bb.target_name LIKE ('%OC4J_EM%')
          AND ct.target_name = p_ias_name
          AND ct.target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE
          AND aa.host_name = ct.host_name
          AND d.target_guid = bb.target_guid
          AND d.property_name = 'OracleHome'
          AND e.assoc_guid = MGMT_ASSOC.g_contains_guid
          AND e.source_target_guid = ct.target_guid
          ANd e.assoc_target_guid = bb.target_guid;
    RETURN l_oms_data;
  END IF;

  -- If we are here then all parameters were null and we'll simply return a cursor
  -- with all the omss

    OPEN l_oms_data FOR
      SELECT a.host_url oms_name,
          NVL(a.target_name,'None') host_name,
          NVL(bb.target_name,'None') oc4j_name,
          NVL(ct.target_name, 'None') ias_name
        FROM mgmt_targets a,
          mgmt_oms_parameters a,
          mgmt_oms_parameters b,
          mgmt_targets bb,
          mgmt_target_properties c,
          mgmt_target_properties d,
          mgmt_target_assocs e,
          mgmt_targets ct
        WHERE a.host_url = b.host_url
          AND a.name = 'ORACLE_HOME'
          AND b.name = 'HOST_NAME'
          AND a.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
          AND bb.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE
          AND bb.target_name like '%OC4J_EM'
          AND c.target_guid = bb.target_guid
          AND d.target_guid = bb.target_guid
          AND c.property_name = 'HTTPMachine'
          AND d.property_name = 'OracleHome'
          AND a.host_name = b.value
          AND c.property_value = b.value
          AND d.property_value LIKE '%'||a.value||'%'
          AND ct.target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE
          AND e.assoc_guid = MGMT_ASSOC.g_contains_guid
          AND e.source_target_guid = ct.target_guid
          ANd e.assoc_target_guid = bb.target_guid;
    RETURN l_oms_data;

END get_oms_data;


END mgmt_admin;
/

SHOW ERRORS;