Rem drv: <create type="pkgbodys" pos="target/target_ui_pkgbody.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/target/relocate_target_pkgbody.sql /st_emcore_10.2.0.4.4db11.2.0.3/1 2011/06/14 21:59:10 vnukal Exp $
Rem
Rem relocate_target_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      relocate_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    vnukal      06/14/11 - Backport vnukal_10222057_1 from main
Rem    vnukal      04/14/10 - cutover to DBMS_ASSERT
Rem    joyoon      05/12/09 - Ignore duplicate record on
Rem                           mgmt_target_agent_assoc
Rem    jsadras     09/23/08 - Bug:7426011, add dbms_assert calls
Rem    aaitghez    07/06/05 - use sdk error codes. (sdk_global_pkgdef.sql)
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    rzkrishn    02/21/05 - get siblings as well 
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    rzkrishn    01/12/05 - procedure to find if duplicate target 
Rem    rzkrishn    01/10/05 - return blackout guid for CLI blackouts as well.
Rem    ramalhot    08/25/04 - cutover to new assoc tables 
Rem    rzkrishn    08/24/04 - fail if the target is already moved or source 
Rem                           agent does not monitor the target 
Rem    rzkrishn    08/19/04 - using space as default in callbacks 
Rem    rzkrishn    07/30/04 - callbacks cant be NULL in delete_tgt_relocation 
Rem                           procedure 
Rem    rzkrishn    07/14/04 - rzkrishn_oms_side_of_13253
Rem    rzkrishn    07/07/04 - adding callbacks 
Rem    rzkrishn    06/15/04 - Created
Rem

--
-- Package: em_target_relocate
--
-- PURPOSE:
--       This package contains internal procedures used by target relocation.
--        
--
-- NOTES:
--
CREATE OR REPLACE PACKAGE BODY EM_TARGET_RELOCATE
AS

--
-- compare_agent_versions
--
-- Takes 2 agent URL;s and determine if agent1 is of higher, equal or 
-- lower version than agent2.
-- returns 0 if equal, -1 if src_agent > dest_agent, 1 src_agent < dest_agent
--
FUNCTION compare_agent_versions (p_src_agent_target IN VARCHAR2,
                             p_dest_agent_target IN VARCHAR2) RETURN NUMBER IS

l_src_agent_ver MGMT_TARGET_PROPERTIES.property_value%TYPE;
l_dest_agent_ver MGMT_TARGET_PROPERTIES.property_value%TYPE;
l_srctok varchar2(32);
l_desttok varchar2(32);
l_srcrem  varchar2(32);
l_destrem  varchar2(32);
l_v_src    number;
l_v_dest   number;

l_ret_value NUMBER;

BEGIN

   SELECT tgtprop.property_value INTO l_src_agent_ver 
     FROM mgmt_target_properties tgtprop, mgmt_targets tgt 
     WHERE tgtprop.property_name ='Version' 
       AND tgtprop.target_guid = tgt.target_guid 
       AND tgt.target_name = p_src_agent_target 
       AND tgt.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE ; 

   SELECT tgtprop.property_value INTO l_dest_agent_ver 
     FROM mgmt_target_properties tgtprop, mgmt_targets tgt 
     WHERE tgtprop.property_name ='Version' 
       AND tgtprop.target_guid = tgt.target_guid 
       AND tgt.target_name = p_dest_agent_target 
       AND tgt.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE ; 

  l_srctok := substr(l_src_agent_ver, 1, INSTR(l_src_agent_ver, '.')-1 );
  l_srcrem := substr(l_src_agent_ver, INSTR(l_src_agent_ver, '.')+1 );
  l_desttok := substr(l_dest_agent_ver, 1, INSTR(l_dest_agent_ver, '.')-1 );
  l_destrem := substr(l_dest_agent_ver, INSTR(l_dest_agent_ver, '.')+1 );

  --
  -- Parses versions that are of any length.
  --
  WHILE l_srctok is not NULL AND l_desttok is not NULL
  LOOP
    l_v_src := TO_NUMBER(l_srctok);
    l_v_dest := TO_NUMBER(l_desttok);

    IF l_v_src = l_v_dest AND
         l_srcrem is NULL AND
         l_destrem is NULL THEN
      l_ret_value := 0;
      goto endof_func;
    ELSIF  l_v_src < l_v_dest THEN
      l_ret_value := 1;
      goto endof_func;
    ELSIF l_v_src > l_v_dest THEN
      l_ret_value := -1;
      goto endof_func;
    END IF;

    l_srctok := substr(l_srcrem, 1, INSTR(l_srcrem, '.')-1 );
    l_srcrem := substr(l_srcrem, INSTR(l_srcrem, '.')+1 );
    l_desttok := substr(l_destrem, 1, INSTR(l_destrem, '.')-1 );
    l_destrem := substr(l_destrem, INSTR(l_destrem, '.')+1 );
  END LOOP;

  IF l_srctok is not NULL THEN
     l_v_src := TO_NUMBER(l_srctok);
  ELSIF l_srcrem is not NULL THEN
     l_v_src := TO_NUMBER(l_srcrem);
  END IF;

  IF l_desttok is not NULL THEN
     l_v_dest := TO_NUMBER(l_desttok);
  ELSIF l_destrem is not NULL THEN
     l_v_dest := TO_NUMBER(l_destrem);
  END IF;

  IF l_v_src = l_v_dest AND
       l_srcrem is NULL AND
       l_destrem is NULL THEN
     l_ret_value := 0;
      goto endof_func;
  ELSIF  l_v_src < l_v_dest THEN
     l_ret_value := 1;
     goto endof_func;
  ELSIF l_v_src > l_v_dest THEN
     l_ret_value := -1;
     goto endof_func;
  END IF;

  IF l_srctok is NULL AND
       l_desttok is not NULL THEN
    l_ret_value := 1;
  ELSIF l_srctok is not NULL AND
        l_desttok is NULL THEN
    l_ret_value := -1;
  ELSE
    l_ret_value := 0;
  END IF;

<<endof_func>> 
    RETURN l_ret_value;

END compare_agent_versions;

--
-- is_cluster_target
--
-- Takes a target type and determines if it is a]cluster target or not.
-- Returns FALSE if non-cluster target. TRUE if cluster target.
--
FUNCTION is_cluster_target(p_target_type IN VARCHAR2) RETURN INTEGER IS

l_is_cluster MGMT_TYPE_PROPERTIES.property_value%TYPE;

l_ret_value INTEGER;

BEGIN

   BEGIN
     SELECT props.property_value INTO l_is_cluster 
       FROM mgmt_type_properties props 
        WHERE props.target_type = p_target_type 
          AND props.property_name = 'is_cluster';
   EXCEPTION
     WHEN OTHERS THEN
        l_is_cluster := '0' ;
   END;

   IF l_is_cluster = '0' THEN
      l_ret_value := 0;  
   ELSIF l_is_cluster = '1' THEN
      l_ret_value := 1;
   ELSE
      l_ret_value := 1;
   END IF;

  RETURN l_ret_value;

END is_cluster_target;

--
-- find_agent_monitoring_for_tgt
--
-- Takes a target name, target type and determines the current agent
-- monitoring for that target.
--
PROCEDURE find_agent_monitoring_for_tgt(p_target_name IN VARCHAR2,
                                            p_target_type IN VARCHAR2,
                                            p_agent_target OUT VARCHAR2) IS

BEGIN
   SELECT tgt.target_name INTO p_agent_target 
    FROM mgmt_targets tgt
      WHERE tgt.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE AND
            tgt.emd_url in (SELECT tgt1.emd_url 
                              FROM mgmt_targets tgt1
                               WHERE tgt1.target_name = p_target_name 
                                AND  tgt1.target_type = p_target_type);
END find_agent_monitoring_for_tgt;

--
-- check_conditions_for_relocation
--
-- Checks all the sql conditions to determine if target can be relocated.
-- returns TRUE for ok to proceed, FALSE for failure to proceed.
-- 1. Check if user has privileges to relocate the target.
-- 2. Check if src agent version <= dest agent version.
-- 3. Check if target is not cluster target.
--
FUNCTION check_preconditions(p_target_name IN VARCHAR2,
                             p_target_type IN VARCHAR2,
                             p_user_name IN VARCHAR2,
                             p_src_agent_target IN VARCHAR2,
                             p_dest_agent_target IN VARCHAR2) RETURN INTEGER IS

l_user_priv NUMBER;
l_version_compatible NUMBER;
l_is_cluster  INTEGER;
l_src_agent_url MGMT_TARGETS.emd_url%TYPE;
l_monitoring_agent_url MGMT_TARGETS.emd_url%TYPE;

l_ok_to_relocate  INTEGER := 0;

BEGIN
   
   IF p_src_agent_target = p_dest_agent_target THEN
      raise_application_error(MGMT_GLOBAL.SRC_DEST_SAME_AGENT_ERR, 
                              'source agent is same as target agent. '); 
   END IF;  

--   check if source agent monitors the target
     
    l_monitoring_agent_url := 
                  find_emd_url_for_target(p_target_name, p_target_type); 

    l_src_agent_url := find_emd_url_for_target(p_src_agent_target,
                                           MGMT_GLOBAL.G_AGENT_TARGET_TYPE );

    IF l_monitoring_agent_url != l_src_agent_url THEN
      raise_application_error(MGMT_GLOBAL.SRC_AGENT_NOT_MONITORING_ERR, 
                              p_src_agent_target || 
                              ' does not monitor ' ||
                              p_target_name || ':' || p_target_type ); 
    END IF;
   

--   check if user has priviliges to relocate target.

   l_user_priv := MGMT_USER.HAS_PRIV(p_user_name, MGMT_USER.FULL_TARGET,
                                     p_target_name, p_target_type);

   IF l_user_priv = MGMT_USER.USER_DOES_NOT_HAVE_PRIV  THEN 
      raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIV_RELOC_ERR, 
                              p_user_name || 
                              ' has no privilege to relocate target ' ||
                              p_target_name || ':' || p_target_type ||
                              ' from ' || p_src_agent_target || ' to ' ||
                              p_dest_agent_target); 
   END IF;

--   check if the new agent version is backward compatible

   l_version_compatible := compare_agent_versions(p_src_agent_target,
                                                  p_dest_agent_target);
   
   IF l_version_compatible < 0 THEN
      raise_application_error(MGMT_GLOBAL.VERSION_MISMATCH_RELOC_ERR, 
                              'version incompatible: ' ||
                            p_src_agent_target || ' > ' ||
                            p_dest_agent_target || ' when relocating ' ||
                            p_target_name || ':' || p_target_type ||
                            ' from ' || p_src_agent_target || ' to ' ||
                            p_dest_agent_target );
   END IF;
   
-- Check if it is cluster target. 
 
   l_is_cluster := is_cluster_target(p_target_type);

   IF l_is_cluster = 1 THEN 
      raise_application_error(MGMT_GLOBAL.MULTI_AGENT_TARGET_RELOC_ERR, 
                              p_target_type ||
                            ' is cluster target type when relocating ' ||
                            p_target_name || ':' || p_target_type  ||
                            ' from ' || p_src_agent_target || ' to '  ||
                            p_dest_agent_target );
   END IF;

   l_ok_to_relocate := 1;

   return l_ok_to_relocate;
   
END check_preconditions;
 
--
-- find_emd_url_for_target
--
-- takes a target name and target type and finds the EMD url of 
-- the agent monitoring the target.
--
FUNCTION find_emd_url_for_target(p_target_name IN VARCHAR2,
                                 p_target_type IN VARCHAR2) 
                    RETURN STRING IS

l_emd_url MGMT_TARGETS.emd_url%TYPE;

BEGIN

  SELECT tgt.emd_url INTO l_emd_url 
     FROM MGMT_TARGETS tgt
       WHERE tgt.target_name = p_target_name 
         AND tgt.target_type = p_target_type;

  return l_emd_url;

END find_emd_url_for_target;

--
-- find_target_guid
--
-- takes a target name and target type and finds  
-- its target guid.
--
FUNCTION find_target_guid(p_target_name IN VARCHAR2,
                          p_target_type IN VARCHAR2) 
                RETURN STRING IS

l_target_guid MGMT_TARGETS.target_guid%TYPE;

BEGIN

  SELECT tgt.target_guid INTO l_target_guid 
     FROM MGMT_TARGETS tgt
       WHERE tgt.target_name = p_target_name 
         AND tgt.target_type = p_target_type;

  return RAWTOHEX(l_target_guid);

END find_target_guid;

--
-- 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( p_callback_name IN VARCHAR2,
                                       p_target_name IN VARCHAR2 DEFAULT ' ',
                                       p_target_type IN VARCHAR2 DEFAULT ' ')
IS
BEGIN
    INSERT INTO mgmt_callbacks
        (callback_type, callback_name, selector_1, selector_2)
    VALUES
       (mgmt_global.G_TARGET_RELOCATION_CALLBACK, p_callback_name,
         NVL(p_target_name, ' '), NVL(p_target_type, ' '));
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        raise_application_error(MGMT_GLOBAL.RELOC_CALLBACK_EXISTS_ERR,
                                'relocation callback ' || p_callback_name ||
                                'already exists. ');
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( p_callback_name   IN VARCHAR2,
                                        p_target_name IN VARCHAR2 DEFAULT ' ',
                                        p_target_type IN VARCHAR2 DEFAULT ' ')
IS
BEGIN
  DELETE mgmt_callbacks
    WHERE callback_type = mgmt_global.G_TARGET_RELOCATION_CALLBACK 
      AND upper(callback_name) = upper(p_callback_name)
      AND selector_1 = NVL(p_target_name, ' ')
      AND selector_2 = NVL(p_target_type, ' ');

END delete_tgt_relocation_callback;

-- 
-- Execute callbacks for target relocation.
--
PROCEDURE exec_tgt_relocation_callbacks( p_target_name   IN VARCHAR2,
                                         p_target_type   IN VARCHAR2,
                                         p_old_agent_url  IN VARCHAR2,
                                         p_new_agent_url  IN VARCHAR2)
IS
l_error_trace VARCHAR2(2000);
l_a VARCHAR2(32767);
l_b VARCHAR2(32767);
l_c VARCHAR2(32767);
l_l VARCHAR2(32767);
l_cb VARCHAR2(32767);
l_n NUMBER;
BEGIN
   FOR callback IN (SELECT callback_name
                     FROM mgmt_callbacks
               WHERE callback_type = mgmt_global.G_TARGET_RELOCATION_CALLBACK 
                 AND ( selector_1 = p_target_name OR
                       selector_1 = ' ' )
                 AND ( selector_2 = p_target_type OR
                       selector_2 = ' ' ))
   LOOP
     BEGIN
   
      DBMS_UTILITY.NAME_TOKENIZE(callback.callback_name,l_a,l_b,l_c,l_l,l_n);
      IF l_a IS NOT NULL THEN
        l_cb := DBMS_ASSERT.SIMPLE_SQL_NAME(l_a);
      END IF;
      IF l_b IS NOT NULL THEN
        l_cb := l_cb || '.' || DBMS_ASSERT.SIMPLE_SQL_NAME(l_b);
      END IF;
      IF l_c IS NOT NULL THEN
        l_cb := l_cb || '.' || DBMS_ASSERT.SIMPLE_SQL_NAME(l_c);
      END IF;
      EXECUTE IMMEDIATE
          'BEGIN ' || l_cb || '(:1, :2, :3, :4); END; ' USING
          p_target_name, p_target_type, p_old_agent_url, p_new_agent_url;
     EXCEPTION
        WHEN OTHERS THEN
	  l_error_trace := SUBSTR(SQLERRM || ' - ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 2000);
	  mgmt_log.log_error(v_module_name_in => 'RELOCATE',
                             v_error_code_in  =>  0,
                             v_error_msg_in   => 'Err executing assoc 
callback :'
                                                   ||' - '|| callback.callback_name
                                                   ||' - '|| l_error_trace);


          raise_application_error(MGMT_GLOBAL.RELOC_CALLBACK_EXEC_ERR, 
                                  'Error executing callback ' ||
                                  callback.callback_name || ' for ' || 
                                  p_target_name || ':' || p_target_type|| ' : '|| l_error_trace);
     END;
   END LOOP;

END exec_tgt_relocation_callbacks;

--
-- PROCEDURE: update_tables_for_relocation
--
-- PURPOSE
--  updates MGMT_TARGETS, MGMT_DUPLICATE_TARGETS and invoke
--  all the callbacks registered.
--
PROCEDURE update_tables_for_relocation( p_target_name IN VARCHAR2,
                                        p_target_type IN VARCHAR2,
                                        p_old_agent_url IN VARCHAR2,
                                        p_new_agent_url IN VARCHAR2)
IS

BEGIN

   -- change the mgmt_targets, mgmt_duplicate_targets tables
   emd_loader.change_target_emd_url( p_target_name, p_target_type,
                                     p_old_agent_url, p_new_agent_url );

   -- call all registered callbacks
   exec_tgt_relocation_callbacks( p_target_name, p_target_type,
                                     p_old_agent_url, p_new_agent_url );

END update_tables_for_relocation;

--
-- PROCEDURE get_cli_blackouts_for_target
--
-- PURPOSE
--  gets a cursor to fetch all CLI blackouts for the target given the
--  target name, target type and emd_url.
--
PROCEDURE get_cli_blackouts_for_target ( p_target_name IN VARCHAR2,
                                         p_target_type IN VARCHAR2,
                                         p_emd_url     IN VARCHAR2,
                                         p_cli_blackouts_cur OUT CURSORTYPE )
IS

l_target_guid mgmt_targets.target_guid%TYPE;

BEGIN
  l_target_guid := mgmt_target.get_target_guid( p_target_name, p_target_type );

  OPEN p_cli_blackouts_cur FOR
    SELECT blkouts.BLACKOUT_NAME, blkouts.BLACKOUT_GUID, 
           blkouts.BLACKOUT_DESC,                    
        blkout_tgt_details.INCLUDE_MEMBERS,                              
        blkout_sched.START_TIME, blkout_sched.DURATION                   
   FROM MGMT_BLACKOUTS blkouts,                                           
       MGMT_BLACKOUT_FLAT_TARGETS blkout_flat_tgts,                      
       MGMT_BLACKOUT_SCHEDULE blkout_sched,                              
       MGMT_BLACKOUT_TARGET_DETAILS blkout_tgt_details                   
   WHERE blkout_flat_tgts.TARGET_GUID = l_target_guid                          
     AND blkout_flat_tgts.BLACKOUT_GUID = blkouts.BLACKOUT_GUID           
     AND blkouts.CREATED_THRU = p_emd_url                             
     AND blkout_sched.BLACKOUT_GUID = blkout_flat_tgts.BLACKOUT_GUID     
     AND blkout_tgt_details.BLACKOUT_GUID = blkout_flat_tgts.BLACKOUT_GUID 
     AND blkout_tgt_details.TARGET_GUID = blkout_flat_tgts.TARGET_GUID;


END get_cli_blackouts_for_target;

--
--  PROCEDURE get_related_targets
--
-- PURPOSE
--  gets related targets given a target.
--
PROCEDURE get_related_targets ( p_target_name IN VARCHAR2,
                                p_target_type IN VARCHAR2,
                                p_emd_url     IN VARCHAR2,
                                p_related_parents_cur OUT CURSORTYPE,
                                p_related_children_cur OUT CURSORTYPE,
                                p_related_siblings_cur OUT CURSORTYPE )
IS

l_target_guid mgmt_targets.target_guid%TYPE;
l_parent_target_guid mgmt_target_assocs.source_target_guid%TYPE;

BEGIN

  l_target_guid := mgmt_target.get_target_guid( p_target_name, p_target_type );

  OPEN p_related_parents_cur FOR
    SELECT tgts.TARGET_NAME, tgts.TARGET_TYPE
      FROM MGMT_TARGETS tgts, MGMT_TARGET_ASSOCS members
       WHERE tgts.target_guid = members.source_target_guid
         AND members.assoc_guid = MGMT_ASSOC.g_contains_guid
         AND tgts.EMD_URL = p_emd_url
         AND members.assoc_target_guid = l_target_guid;

  OPEN  p_related_children_cur FOR
    SELECT tgts.TARGET_NAME, tgts.TARGET_TYPE
     FROM MGMT_TARGETS tgts, MGMT_TARGET_ASSOCS members
      WHERE tgts.target_guid = members.assoc_target_guid
        AND members.assoc_guid = MGMT_ASSOC.g_contains_guid
        AND tgts.EMD_URL = p_emd_url
        AND members.source_target_guid = l_target_guid; 

   BEGIN
     -- Get the parent guid in the agent.
       SELECT members.source_target_guid INTO l_parent_target_guid
          FROM MGMT_TARGETS tgts, MGMT_TARGET_ASSOCS members
            WHERE tgts.target_guid = members.source_target_guid
              AND members.assoc_guid = MGMT_ASSOC.g_contains_guid
              AND tgts.EMD_URL = p_emd_url
              AND members.assoc_target_guid = l_target_guid;

     -- get all the siblings for the given target 
       OPEN p_related_siblings_cur FOR
         SELECT tgts.TARGET_NAME, tgts.TARGET_TYPE
          FROM MGMT_TARGETS tgts, MGMT_TARGET_ASSOCS members
           WHERE members.source_target_guid = l_parent_target_guid
             AND tgts.EMD_URL = p_emd_url
             AND tgts.target_guid = members.assoc_target_guid
             AND tgts.target_guid != l_target_guid
             AND members.assoc_guid = MGMT_ASSOC.g_contains_guid;
   EXCEPTION WHEN NO_DATA_FOUND
   THEN
        OPEN p_related_siblings_cur FOR
          select p_target_name, p_target_type from dual; 
   END;
 
END get_related_targets;

--
-- check_if_duplicate_target
--
-- Takes a target, source agent and destination agent url. determines if the
-- target is monitored by source agent and is considered as duplicate for
-- destination agent.
-- returns 1 if duplicate, 0 if not duplicate.
-- 
FUNCTION check_if_duplicate_target( p_target_name IN VARCHAR2,
                                    p_target_type IN VARCHAR2,
                                    p_src_agent_url IN VARCHAR2,
                                    p_dest_agent_url IN VARCHAR2 ) 
                           RETURN INTEGER 
IS

l_duplicate_target INTEGER := 0;
l_target_name  MGMT_TARGETS.target_name%TYPE; 

BEGIN

  l_target_name := NULL;

  SELECT t.target_name INTO l_target_name
    FROM mgmt_targets t , mgmt_duplicate_targets d
  WHERE t.target_name = d.target_name
    AND t.target_type = d.target_type
    AND t.target_name = p_target_name
    AND t.target_type = p_target_type
    AND t.emd_url = p_src_agent_url 
    AND d.duplicate_emd_url = p_dest_agent_url 
    AND d.resolved_time is NULL; 

  IF  l_target_name IS NOT NULL THEN
      l_duplicate_target := 1;
  ELSE 
     l_duplicate_target := 0;
  END IF;

  return l_duplicate_target;

END check_if_duplicate_target;

--
-- Check a given target is a member of cluster target AND 
-- its agent is master agent. If so, we need to change 
-- source target's master agent. 
-- NOTE: We can't guarantee p_new_agent_url is the best candidate 
--       of master agent. 
--
PROCEDURE relocate_master_agent(p_target_name IN VARCHAR2,
                                p_target_type IN VARCHAR2,
                                p_old_agent_url IN VARCHAR2,
                                p_new_agent_url IN VARCHAR2 ) 
IS
l_assoc_target_guid mgmt_targets.target_guid%TYPE;
l_source_target_guid mgmt_targets.target_guid%TYPE;
l_old_agent_guid mgmt_targets.target_guid%TYPE;
l_new_agent_guid mgmt_targets.target_guid%TYPE;
l_master_agent_guid mgmt_targets.target_guid%TYPE;
l_count NUMBER;
BEGIN
  l_assoc_target_guid := mgmt_target.get_target_guid(p_target_name, p_target_type);

  SELECT target_guid INTO l_old_agent_guid
    FROM MGMT_TARGETS 
   WHERE emd_url = p_old_agent_url 
     AND target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE;

  SELECT target_guid INTO l_new_agent_guid
    FROM MGMT_TARGETS 
   WHERE emd_url = p_new_agent_url 
     AND target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE;

   -- It is possible that the cluser instance is a member
   -- of more than one cluster parent target. Get all the
   -- cluster parents and iterate over them
   FOR ct IN (
       SELECT  tm.source_target_guid target_guid
         FROM  mgmt_target_assocs tm,
               mgmt_type_properties p,
               mgmt_targets ct
        WHERE tm.assoc_target_guid = l_assoc_target_guid
          AND tm.assoc_guid = MGMT_ASSOC.g_contains_guid
          AND ct.target_guid = tm.source_target_guid
          AND p.target_type = ct.target_type
          AND p.property_name = MGMT_GLOBAL.G_IS_CLUSTER_PROP)
   LOOP
     BEGIN
     l_source_target_guid := ct.target_guid; 

     -- update MGMT_TARGET_AGENT_ASSOC with new agent_guid 
     BEGIN
     DELETE FROM MGMT_TARGET_AGENT_ASSOC 
      WHERE target_guid = l_source_target_guid 
        AND agent_guid  = l_old_agent_guid; 
     EXCEPTION 
       WHEN NO_DATA_FOUND THEN 
          NULL; 
     END; 
 
     BEGIN 
     INSERT INTO MGMT_TARGET_AGENT_ASSOC 
     VALUES (l_source_target_guid, l_new_agent_guid); 
     EXCEPTION 
       WHEN DUP_VAL_ON_INDEX THEN 
          NULL;
     END;

     -- If relocation happended on master agent, need to find 
     -- the best candidate of master agent among agents of the given target
     SELECT COUNT(*) INTO l_count
       FROM MGMT_MASTER_AGENT
      WHERE target_guid = l_source_target_guid 
        AND agent_guid = l_old_agent_guid 
        AND end_timestamp IS NULL; 

     IF l_count = 1 THEN 
       em_master_agent.change_master_agent(l_source_target_guid, l_old_agent_guid, 
                                           SYSDATE, TRUE, l_master_agent_guid); 
     END IF; 

     EXCEPTION 
       WHEN NO_DATA_FOUND THEN 
          NULL; 
       WHEN OTHERS THEN 
          RAISE; 
     END; 
   END LOOP; 

   EXCEPTION 
     WHEN OTHERS THEN 
       raise_application_error(MGMT_GLOBAL.MULTI_AGENT_TARGET_RELOC_ERR,
                            'Error when relocating ' ||
                            p_target_name || ':' || p_target_type  ||
                            ' from ' || p_old_agent_url || ' to '  || p_new_agent_url );
END relocate_master_agent;

END EM_TARGET_RELOCATE;
/ 
show errors;