Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\assoc\assoc

Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\assoc\assoc_pkgbodys.sql

Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/assoc/assoc_pkgbodys.sql /st_emcore_10.2.0.4.2db11.2/1 2009/02/26 20:39:36 jsadras Exp $
Rem
Rem assoc_pkgbodys.sql
Rem
Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      assoc_pkgbodys.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jsadras     11/27/08  - Bug:7559214, fix perf issues
Rem    pmodi       05/07/08  - Backport pmodi_bug-7010707 from
Rem                            st_emcore_10.2.0.1.0
Rem    pmodi       04/10/07  - Backport pmodi_bug-5851571 from main
Rem    pmodi       03/25/07  - Bug-5851571 - Add hosted by assoc
Rem    pmodi       08/09/05  - New procedure for upgrade to poulate flat table
Rem    pmodi       08/04/05   - Bug:4526612 - CONNECT BY clause does not 
Rem                             support sub qry in 9.2 
Rem    gan         07/25/05   - comment out dbms_output 
Rem    gsbhatia    07/17/05   - Removing repmgr header 
Rem    pmodi       06/24/05   - Bug:4378524 - Change in query for perf 
Rem    pmodi       04/27/05   - Include parent membership count for setting 
Rem                             is_membership and consider ONE LEVEL prop view 
Rem                             count in delete singular target 
Rem    pmodi       05/02/05   - change in update_ref_count_in_flat_list reversed
Rem    ramalhot    04/19/05   - modified update_assoc_count 
Rem    pmodi       04/15/05   - change in assoc flattening algo 
Rem    ramalhot    04/08/05   - change in assoc flattening algo
Rem    dcawley     02/21/05   - Replace enter super user mode 
Rem    gsbhatia    02/13/05   - updating repmgr header 
Rem    gsbhatia    02/09/05   - updating repmgr header 
Rem    gsbhatia    02/07/05   - updating repmgr header 
Rem    ramalhot    02/02/05   - changed signature for delete_target 
Rem    dcawley     01/31/05   - Reset current user 
Rem    ramalhot    01/17/05  -  assoc name changed from member to contains 
Rem    ktlaw       01/11/05  - add repmgr header 
Rem    ramalhot    01/11/05  - call callbacks as super-user 
Rem    ramalhot    12/28/04  - default added to recreate_flat_target_list 
Rem    ramalhot    12/26/04  - bug in delete_target_assocs fixed 
Rem    ramalhot    10/19/04  - bulk apis based on guids added 
Rem    ramalhot    10/15/04  - membership loop detection added 
Rem    ramalhot    10/08/04 -  apis to add assoc from loader added 
Rem    ramalhot    10/05/04 - bulk apis added 
Rem    dcawley     09/14/04 - Call user model 
Rem    ramalhot    08/27/04 - properties support added 
Rem    ramalhot    08/18/04 - column name changed 
Rem    ramalhot    08/06/04 - error message changed 
Rem    ramalhot    07/31/04 - constant name changed 
Rem    ramalhot    07/23/04 - api renamed from relocate_target to handle_relocated_target
Rem    ramalhot    07/09/04 - added sdk apis 
Rem    ramalhot    07/07/04 - ramalhot_assoc_object_creation
Rem    ramalhot    07/06/04 - Created 
Rem

CREATE OR REPLACE PACKAGE BODY em_assoc AS

linked_target_list MGMT_ASSOC_TARGET_GUID_LIST := MGMT_ASSOC_TARGET_GUID_LIST();

-- Forword Declarations
  PROCEDURE  update_ref_count_in_flat_list
  (
    p_assocs IN  MGMT_TARGET_ASSOC_LIST
  );

FUNCTION chk_agent_singular_target
  (
    p_assocs IN  MGMT_TARGET_ASSOC_LIST,
    p_target_guid IN mgmt_targets.target_guid%TYPE
  ) RETURN NUMBER ;

PROCEDURE upd_ref_cnt_singular
  (
    p_assocs IN  MGMT_TARGET_ASSOC_LIST,
    p_target_guid IN mgmt_targets.target_guid%TYPE
  ) ;

-- Function and procedure implementations
  --
  -- Utility  fn to get the guid of a specified target
  --
  PROCEDURE get_sure_target_guid
  (
    p_target_name IN mgmt_targets.target_name%TYPE,
    p_target_type IN mgmt_targets.target_type%TYPE,
    p_target_guid OUT mgmt_targets.target_guid%TYPE,
    p_pending     IN OUT BOOLEAN
  )
  IS
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('get_sure_target_guid:Entry p_target_name ='||p_target_name ||
                                               ' p_target_type ='||p_target_type, MODULE_NAME);
    END IF;
                                                                                                                                             
    BEGIN
      p_target_guid := mgmt_target.get_target_guid(p_target_name,
                                                   p_target_type);
    EXCEPTION
      WHEN MGMT_GLOBAL.target_does_not_exist THEN
        IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('target does not exist',MODULE_NAME);
        END IF;
        p_pending := TRUE ;                                                                                                                                     
        p_target_guid := mgmt_target.generate_target_guid(p_target_name,
                                                          p_target_type);
    END;
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('get_sure_target_guid:Exit',MODULE_NAME);
    END IF;
  END get_sure_target_guid;

--
  -- Executes assoc addition/deletion callbacks
  --
  PROCEDURE EXEC_ASSOC_CALLBACKS
  (
    p_callback_type      mgmt_callbacks.callback_type%TYPE,
    p_assoc_guid         mgmt_target_assoc_defs.assoc_guid%TYPE,
    p_source_target_guid mgmt_targets.target_guid%TYPE,
    p_assoc_target_guid  mgmt_targets.target_guid%TYPE,
    p_scope_target_guid  mgmt_targets.target_guid%TYPE

)
  IS
    l_assoc_def_name mgmt_target_assoc_defs.assoc_def_name%TYPE;
    l_source_target_name mgmt_targets.target_name%TYPE;
    l_source_target_type mgmt_targets.target_type%TYPE;
    l_assoc_target_name mgmt_targets.target_name%TYPE := NULL;
    l_assoc_target_type mgmt_targets.target_type%TYPE;
    l_scope_target_name mgmt_targets.target_name%TYPE := ' ';
    l_scope_target_type mgmt_targets.target_type%TYPE := mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE;
    l_current_user mgmt_targets.owner%TYPE := MGMT_USER.get_current_em_user();
    l_user mgmt_targets.owner%TYPE := NULL;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('exec_assoc_callbacks:Entry p_assoc_guid ='||p_assoc_guid ||
                    'p_source_target_guid ='||p_source_target_guid||
                    'p_assoc_target_guid ='||p_assoc_target_guid||
                    'p_scope_target_guid ='||p_scope_target_guid, MODULE_NAME);
    END IF;

SELECT assoc_def_name
      INTO l_assoc_def_name
      FROM mgmt_target_assoc_defs
     WHERE assoc_guid = p_assoc_guid;

BEGIN
      SELECT target_name, target_type
        INTO l_source_target_name, l_source_target_type
        FROM mgmt_targets
       WHERE target_guid = p_source_target_guid;

EXCEPTION
      WHEN NO_DATA_FOUND THEN
        BEGIN
          SELECT target_name, target_type
            INTO l_source_target_name, l_source_target_type
            FROM mgmt_targets_delete
           WHERE target_guid = p_source_target_guid;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            IF (emdw_log.p_is_debug_set)THEN
              emdw_log.debug('Specified source target does not exist',MODULE_NAME);
            END IF;
            RETURN;
        END;
    END;

-- Call the target addition callbacks
    FOR callback IN (SELECT callback_name
                       FROM  mgmt_callbacks
                      WHERE callback_type = p_callback_type
                        AND ( selector_2 = l_source_target_type OR
                              selector_2 = mgmt_assoc.ANY_TARGET_TYPE
                            )
                        AND ( selector_1 = l_assoc_def_name OR
                              selector_1 = ' '
                            )
                    )LOOP
    BEGIN
      IF (emdw_log.p_is_info_set)THEN
        emdw_log.info('exec_assoc_callbacks:Loop p_assoc_guid ='||p_assoc_guid ||
                      'p_source_target_guid ='||p_source_target_guid||
                      'p_assoc_target_guid ='||p_assoc_target_guid||
                      'p_scope_target_guid ='||p_scope_target_guid, MODULE_NAME);
      END IF;

IF (l_assoc_target_name IS NULL) THEN
      BEGIN
        SELECT target_name, target_type
          INTO l_assoc_target_name, l_assoc_target_type
          FROM mgmt_targets
          WHERE target_guid = p_assoc_target_guid;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          IF (emdw_log.p_is_debug_set)THEN
            emdw_log.debug('Specified assoc target does not exist',MODULE_NAME);
          END IF;
          
          RETURN;
      END; --outer
      END IF;

IF ( p_scope_target_guid != mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID AND
         l_scope_target_name = ' ')
      THEN
      BEGIN
        SELECT target_name, target_type
          INTO l_scope_target_name, l_scope_target_type
          FROM mgmt_targets
         WHERE target_guid = p_scope_target_guid;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          IF (emdw_log.p_is_debug_set)THEN
            emdw_log.debug('Specified scope target does not exist',MODULE_NAME);
          END IF;
         
         RETURN;
      END; --outer
      END IF;

-- Enter super-user mode. This is necessary because some callbacks 
      -- involves making calls to the security system that only super-users
      -- are allowed to make
      SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, 
                       MGMT_USER.OP_SET_IDENTIFIER);

EXECUTE IMMEDIATE
        'BEGIN ' || EM_CHECK.qualified_sql_name(callback.callback_name) ||
                             '(:1, :2, :3, :4, :5, :6,:7); END; ' USING
                              l_assoc_def_name,l_source_target_name,l_source_target_type,
                              l_assoc_target_name ,l_assoc_target_type,
                              l_scope_target_name ,l_scope_target_type;
      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 => 'ASSOC',
                             v_error_code_in  =>  0,
                             v_error_msg_in   => SUBSTR(SQLERRM, 1, 2000));

-- Revert back to being the same user as we entered
          SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
      END;
    END LOOP;

-- Revert back to being the same user as we entered
    SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('exec_assoc_callbacks:Exit',MODULE_NAME);
    END IF;
  END exec_assoc_callbacks;
  -- It does following
  --  1. Updated the mgmt_targets table for assoc_count and timestamp
  --  2. updates error messages for the target
  PROCEDURE update_assoc_count
  (
     p_target_guid mgmt_targets.target_guid%TYPE
  )
  IS
    l_assoc_count NUMBER;
    l_act_assoc_count NUMBER;
    l_target_type mgmt_targets.target_type%TYPE;
    l_error_code NUMBER;
    CURSOR count_cur(target_type IN mgmt_targets.target_type%TYPE)
    IS
      SELECT d.assoc_guid assoc_guid,
             d.cardinality cardinality,
             a.scope_target_guid scope_target_guid,
             SUM( CASE
                    WHEN a.assoc_guid IS NULL
                      THEN 0
                    ELSE 1
                  END ) count
        FROM ( SELECT assoc_guid,cardinality
                 FROM mgmt_target_assoc_defs
                WHERE source_target_type = target_type
                   OR source_target_type = mgmt_assoc.ANY_TARGET_TYPE
                  AND cardinality <> 2 -- we don't need optional multicardinal
             ) d,mgmt_target_assocs a
       WHERE d.assoc_guid = a.assoc_guid(+)
       GROUP BY d.assoc_guid, d.cardinality,a.scope_target_guid;

count_rec count_cur%ROWTYPE;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('update_assoc_count:Entry',MODULE_NAME);
    END IF;

SELECT target_type
      INTO l_target_type
      FROM mgmt_targets
     WHERE target_guid = p_target_guid ;

-- delete the recard from error table
    DELETE
      FROM mgmt_target_assoc_error
     WHERE source_target_guid = p_target_guid;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('target_type = '||l_target_type,MODULE_NAME);
    END IF;

OPEN count_cur(l_target_type);

FETCH count_cur INTO count_rec;
   /* Keep fetching until no more records are FOUND */
   WHILE count_cur%FOUND
   LOOP
     l_error_code := -1;

IF( (count_rec.cardinality = 0) AND (count_rec.count >1 )) THEN
       l_error_code := 1 ;
     END IF;

IF( (count_rec.cardinality = 1) AND (count_rec.count >1 )) THEN
       l_error_code := 1 ;
     END IF;

IF( (count_rec.cardinality = 1) AND (count_rec.count = 0)) THEN
       l_error_code := 0 ;
     END IF;

IF( (count_rec.cardinality = 3) AND (count_rec.count = 0)) THEN
       l_error_code := 0 ;
     END IF;

IF (emdw_log.p_is_debug_set)THEN
       emdw_log.debug('error_code = '||l_error_code,MODULE_NAME);
     END IF;

IF( l_error_code <> -1) THEN
       INSERT
         INTO mgmt_target_assoc_error
              (source_target_guid, scope_target_guid,
               assoc_guid,error_code)
       VALUES
              (p_target_guid, count_rec.scope_target_guid,
               count_rec.assoc_guid, l_error_code );
     END IF;
     FETCH count_cur INTO count_rec;
   END LOOP;

CLOSE count_cur;

SELECT count(*)
     INTO l_act_assoc_count
     FROM mgmt_target_assocs
    WHERE source_target_guid = p_target_guid;

-- merge will get row level exclusive lock
   MERGE
    INTO mgmt_target_assoc_status s
   USING (SELECT p_target_guid target_guid
            FROM DUAL
         )v
      ON (s.target_guid = v.target_guid )
    WHEN MATCHED THEN
      UPDATE SET s.assoc_count = l_act_assoc_count,
                 s.last_update_time = SYSDATE
    WHEN NOT MATCHED THEN
      INSERT (target_guid,assoc_count,last_update_time)
      VALUES (p_target_guid,l_act_assoc_count, SYSDATE);
     
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('update_assoc_count:Exit',MODULE_NAME);
    END IF;
  END update_assoc_count;

FUNCTION target_already_covered
  (
    p_target_id IN mgmt_targets.target_guid%TYPE,
    p_target_list MGMT_ASSOC_TARGET_GUID_LIST
  ) RETURN BOOLEAN
  IS
  BEGIN
    FOR i IN 1..p_target_list.count
    LOOP
      IF ( p_target_list(i).target_guid = p_target_id) THEN
        RETURN TRUE ;
      END IF;
    END LOOP;

RETURN FALSE;
  END ;

FUNCTION target_already_covered
  (
    p_target_id IN mgmt_targets.target_guid%TYPE
  ) RETURN BOOLEAN
  IS
    l_obj mgmt_targets.target_guid%TYPE ;
  BEGIN
    SELECT target_guid
      INTO l_obj
      FROM mgmt_target_temp_list
     WHERE target_guid = p_target_id
       AND ROWNUM=1 ;

RETURN TRUE ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN FALSE ;
  END ;

--
  -- PURPOSE
  --    gets the list of all immediate associated target for a given target
  --
  PROCEDURE get_associated_targets
  (
    p_target_name  IN mgmt_targets.target_name%TYPE,
    p_target_type  IN mgmt_targets.target_type%TYPE,
    p_assoc_type   IN mgmt_target_assoc_defs.association_type%TYPE DEFAULT NULL,
    ref_cursor     OUT association_cvtype
  )
  IS

BEGIN
    IF (emdw_log.p_is_info_set) THEN
      emdw_log.info('get_associated_targets:Entry',MODULE_NAME);
    END IF;

-- put in to cursor
    IF (p_assoc_type IS NOT NULL)THEN
    IF (emdw_log.p_is_info_set) THEN
      emdw_log.info('p_assoc_type is not null',MODULE_NAME);
    END IF;
    OPEN ref_cursor
      FOR
        SELECT d.association_type,
               t1.target_guid ,
               p_target_name ,
               p_target_type ,
               t2.target_guid ,
               t2.target_name ,
               t2.target_type ,
               t3.target_guid ,
               t3.target_name ,
               t3.target_type ,
               a.is_editable
          FROM mgmt_targets t1,
               mgmt_targets t2,
               mgmt_targets t3,
               mgmt_target_assocs a,
               mgmt_target_assoc_defs d
         WHERE t1.target_type = p_target_type
           AND t1.target_name = p_target_name
           AND a.source_target_guid = t1.target_guid
           AND t2.target_guid = a.assoc_target_guid
           AND t3.target_guid = a.scope_target_guid
           AND d.assoc_guid = a.assoc_guid
           AND d.association_type = p_assoc_type
        UNION ALL
        SELECT d.association_type ,
               t1.target_guid ,
               p_target_name ,
               p_target_type ,
               t2.target_guid ,
               t2.target_name ,
               t2.target_type ,
               mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID ,
               ' ' ,
               mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE ,
               a.is_editable
          FROM mgmt_targets t1,
               mgmt_targets t2,
               mgmt_target_assocs a,
               mgmt_target_assoc_defs d
         WHERE t1.target_type = p_target_type
           AND t1.target_name = p_target_name
           AND a.source_target_guid = t1.target_guid
           AND a.scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID
           AND t2.target_guid = a.assoc_target_guid
           AND d.assoc_guid = a.assoc_guid
           AND d.association_type = p_assoc_type ;
    ELSE
    IF (emdw_log.p_is_info_set) THEN
      emdw_log.info('p_assoc_type is null',MODULE_NAME);
    END IF;

OPEN ref_cursor
      FOR
        SELECT d.association_type association_type,
               t1.target_guid source_target_guid,
               p_target_name source_target_name,
               p_target_type source_target_type,
               t2.target_guid assoc_target_guid,
               t2.target_name assoc_target_name,
               t2.target_type assoc_target_type,
               t3.target_guid scope_target_guid,
               t3.target_name scope_target_name,
               t3.target_type scope_target_type,
               a.is_editable  is_editable
          FROM mgmt_targets t1,
               mgmt_targets t2,
               mgmt_targets t3,
               mgmt_target_assocs a,
               mgmt_target_assoc_defs d
         WHERE t1.target_type = p_target_type
           AND t1.target_name = p_target_name
           AND a.source_target_guid = t1.target_guid
           AND t2.target_guid = a.assoc_target_guid
           AND t3.target_guid = a.scope_target_guid
           AND d.assoc_guid = a.assoc_guid
        UNION ALL
        SELECT d.association_type association_type ,
               t1.target_guid source_target_guid,
               p_target_name source_target_name,
               p_target_type source_target_type,
               t2.target_guid assoc_target_guid,
               t2.target_name assoc_target_name,
               t2.target_type assoc_target_type,
               mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID scope_target_guid,
               ' ' scope_target_name,
               mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE scope_target_type,
               a.is_editable is_editable
          FROM mgmt_targets t1,
               mgmt_targets t2,
               mgmt_target_assocs a,
               mgmt_target_assoc_defs d
         WHERE t1.target_type = p_target_type
           AND t1.target_name = p_target_name
           AND a.source_target_guid = t1.target_guid
           AND a.scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID
           AND t2.target_guid = a.assoc_target_guid
           AND d.assoc_guid = a.assoc_guid;

END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('get_associated_targets:Exit',MODULE_NAME);
    END IF;
  END get_associated_targets;

--
  -- PURPOSE
  --    gets the list of all immediate source target for a given target
  --
  PROCEDURE get_source_targets
  (
    p_target_name  IN mgmt_targets.target_name%TYPE,
    p_target_type  IN mgmt_targets.target_type%TYPE,
    p_assoc_type   IN mgmt_target_assoc_defs.association_type%TYPE DEFAULT NULL,
    ref_cursor     OUT association_cvtype
  )
  IS
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('get_source_targets:Entry',MODULE_NAME);
    END IF;

-- put in to cursor
    IF (p_assoc_type IS NOT NULL)THEN
    IF (emdw_log.p_is_info_set) THEN
      emdw_log.info('p_assoc_type is not null',MODULE_NAME);
    END IF;
    OPEN ref_cursor
      FOR
        SELECT d.association_type,
               t1.target_guid ,
               t1.target_name ,
               t1.target_type ,
               t2.target_guid ,
               p_target_name ,
               p_target_type ,
               t3.target_guid ,
               t3.target_name ,
               t3.target_type ,
               a.is_editable
          FROM mgmt_targets t1,
               mgmt_targets t2,
               mgmt_targets t3,
               mgmt_target_assocs a,
               mgmt_target_assoc_defs d
         WHERE t2.target_type = p_target_type
           AND t2.target_name = p_target_name
           AND a.assoc_target_guid = t2.target_guid
           AND t1.target_guid = a.source_target_guid
           AND t3.target_guid = a.scope_target_guid
           AND d.assoc_guid = a.assoc_guid
           AND d.association_type = p_assoc_type
        UNION ALL
        SELECT d.association_type ,
               t1.target_guid ,
               t1.target_name ,
               t1.target_type ,
               t2.target_guid ,
               p_target_name ,
               p_target_type ,
               mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID ,
               ' ' ,
               mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE ,
               a.is_editable
          FROM mgmt_targets t1,
               mgmt_targets t2,
               mgmt_target_assocs a,
               mgmt_target_assoc_defs d
         WHERE t2.target_type = p_target_type
           AND t2.target_name = p_target_name
           AND a.assoc_target_guid = t2.target_guid
           AND a.scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID
           AND t1.target_guid = a.source_target_guid
           AND d.assoc_guid = a.assoc_guid
           AND d.association_type = p_assoc_type ;
    ELSE
    IF (emdw_log.p_is_info_set) THEN
      emdw_log.info('p_assoc_type is null',MODULE_NAME);
    END IF;

OPEN ref_cursor
      FOR
        SELECT d.association_type association_type,
               t1.target_guid assoc_target_guid,
               t1.target_name assoc_target_name,
               t1.target_type assoc_target_type,
               t2.target_guid source_target_guid,
               p_target_name source_target_name,
               p_target_type source_target_type,
               t3.target_guid scope_target_guid,
               t3.target_name scope_target_name,
               t3.target_type scope_target_type,
               a.is_editable  is_editable
          FROM mgmt_targets t1,
               mgmt_targets t2,
               mgmt_targets t3,
               mgmt_target_assocs a,
               mgmt_target_assoc_defs d
         WHERE t2.target_type = p_target_type
           AND t2.target_name = p_target_name
           AND a.assoc_target_guid = t2.target_guid
           AND t1.target_guid = a.source_target_guid
           AND t3.target_guid = a.scope_target_guid
           AND d.assoc_guid = a.assoc_guid
        UNION ALL
        SELECT d.association_type association_type ,
               t1.target_guid assoc_target_guid,
               t1.target_name assoc_target_name,
               t1.target_type assoc_target_type,
               t2.target_guid source_target_guid,
               p_target_name source_target_name,
               p_target_type source_target_type,
               mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID scope_target_guid,
               ' ' scope_target_name,
               mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE scope_target_type,
               a.is_editable is_editable
          FROM mgmt_targets t1,
               mgmt_targets t2,
               mgmt_target_assocs a,
               mgmt_target_assoc_defs d
         WHERE t2.target_type = p_target_type
           AND t2.target_name = p_target_name
           AND a.assoc_target_guid = t2.target_guid
           AND a.scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID
           AND t1.target_guid = a.source_target_guid
           AND d.assoc_guid = a.assoc_guid;

END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('get_source_targets:Exit',MODULE_NAME);
    END IF;
  END;

PROCEDURE recreate_flat_list
  (
     p_target_guid IN mgmt_targets.target_guid%TYPE
  )
  IS
     l_target_list mgmt_assoc_target_guid_list := mgmt_assoc_target_guid_list();
  BEGIN

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('recreate_flat_list:Entry',MODULE_NAME);
    END IF;

--check if is already covered
    IF target_already_covered(p_target_guid)THEN
      RETURN; --alreday covered
    END IF;

--delete the OLD recards for this target
    DELETE
      FROM MGMT_FLAT_TARGET_ASSOC
     WHERE source_target_guid = p_target_guid ;

-- addd it to the covered target list
    INSERT INTO mgmt_target_temp_list
    VALUES (p_target_guid);

-- delete all the inferred depends-on associations
    DELETE
      FROM mgmt_target_assocs a
     WHERE assoc_guid = mgmt_assoc.g_depends_on_guid
       AND source_target_guid = p_target_guid
       AND scope_target_guid = p_target_guid
       AND a.created_by = mgmt_assoc.CREATED_INFERRED;

--NOTE: don't open cursor otherwise max number of open cursors may exceed
    SELECT MGMT_ASSOC_TARGET_GUID_REC.new(assoc_target_guid) BULK COLLECT
      INTO l_target_list
      FROM ( SELECT DISTINCT assoc_target_guid
               FROM mgmt_target_assocs
              WHERE (source_target_guid = hextoraw(p_target_guid) AND 
                     scope_target_guid = MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID)
                 OR scope_target_guid = hextoraw(p_target_guid)
           );

FOR i IN 1..l_target_list.count
    LOOP
       recreate_flat_list(l_target_list(i).target_guid);
    END LOOP;
    --now data for all its associated target exist

update_assoc_count(p_target_guid);

INSERT
      INTO MGMT_FLAT_TARGET_ASSOC
           (
            source_target_guid,
            assoc_target_guid,
            is_membership,
            ref_count,
            membership_count,
            prop_count
           )
    SELECT source_target_guid, assoc_target_guid, MAX(is_membership), SUM(cnt) ref_count
           , SUM(membership_count) member_count, SUM(prop_count) prop_count
      FROM (
           SELECT DECODE(scope_target_guid, 
                         MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID,p_target_guid,
                         scope_target_guid) source_target_guid,
                  a.assoc_target_guid assoc_target_guid,
                  DECODE(d.assoc_def_name, MGMT_ASSOC.ASSOC_DEF_CONTAINS,1,0) is_membership,
                  1 cnt,
                  DECODE(d.assoc_def_name, MGMT_ASSOC.ASSOC_DEF_CONTAINS,1,0) membership_count,
                  DECODE(d.prop_view_priv, MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION,1,0) prop_count
             FROM mgmt_target_assocs a, mgmt_target_assoc_defs d
            WHERE ((a.source_target_guid = hextoraw(p_target_guid) AND
                    a.scope_target_guid = MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID)
               OR a.scope_target_guid = hextoraw(p_target_guid))
              AND d.assoc_guid = a.assoc_guid
              AND (d.prop_view_priv = MGMT_ASSOC.ONE_LEVEL_VIEW_PROPAGATION OR
                   d.prop_view_priv = MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION )
     UNION ALL
           SELECT p_target_guid source_target_guid,
                  a.assoc_target_guid assoc_target_guid,
                  CASE
                    WHEN a.is_membership = 1 AND (d.assoc_def_name = MGMT_ASSOC.ASSOC_DEF_CONTAINS)
                      THEN 1
                    ELSE 0
                  END is_membership,
                  a.prop_count cnt,
                  CASE
                    WHEN a.is_membership = 1 AND (d.assoc_def_name = MGMT_ASSOC.ASSOC_DEF_CONTAINS)
                      THEN a.membership_count
                    ELSE 0
                  END membership_count,
                  a.prop_count prop_count
             FROM mgmt_flat_target_assoc a,
                  mgmt_target_assocs b, mgmt_target_assoc_defs d
            WHERE ((b.source_target_guid = hextoraw(p_target_guid) AND
                    b.scope_target_guid =  MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID)
                   OR b.scope_target_guid = hextoraw(p_target_guid))
              AND a.source_target_guid = b.assoc_target_guid
              -- remove cyclical associations
              AND a.assoc_target_guid != b.source_target_guid
              AND d.assoc_guid = b.assoc_guid
              AND d.prop_view_priv = MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION
              AND a.prop_count > 0
            )
     GROUP BY source_target_guid, assoc_target_guid;
    COMMIT;
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('recreate_flat_list:Exit',MODULE_NAME);
    END IF;

END recreate_flat_list;

PROCEDURE targetgraph_up
  (
    p_target_id mgmt_targets.target_guid%TYPE
  )
  IS
    l_target_list mgmt_assoc_target_guid_list;
  BEGIN

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('targetgraph_up:Entry',MODULE_NAME);
    END IF;

IF NOT target_already_covered(p_target_id,linked_target_list) THEN

linked_target_list.extend;
      linked_target_list(linked_target_list.count) := MGMT_ASSOC_TARGET_GUID_REC.new(p_target_id);

-- Use NOT EXISTS for linked_target_list to break cyclic loop

SELECT MGMT_ASSOC_TARGET_GUID_REC.new(source_target_guid)BULK COLLECT
        INTO l_target_list
        FROM ( SELECT DISTINCT DECODE (scope_target_guid, 
                                       MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID, source_target_guid, 
                                       scope_target_guid) source_target_guid
                 FROM mgmt_target_assocs
                WHERE assoc_target_guid = p_target_id
                  AND NOT EXISTS (SELECT 1
                                     FROM TABLE(CAST(linked_target_list AS mgmt_assoc_target_guid_list)) l
                                     WHERE l.target_guid = DECODE (scope_target_guid, 
                                                                   MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID, source_target_guid,
                                                                   scope_target_guid) 
                                 )
              );

IF l_target_list.count = 0 THEN

recreate_flat_list(p_target_id);

ELSE

FOR i IN 1..l_target_list.count
        LOOP
          targetgraph_up(l_target_list(i).target_guid) ;
        END LOOP ;

END IF;

END IF ;

END targetgraph_up;

--
  -- PURPOSE
  --    recreates the flat target list for specified targets
  --
  PROCEDURE recreate_flat_target_list
  (
    p_targets IN MGMT_TARGET_ASSOC_LIST ,
    p_delete_target_guid IN mgmt_targets.target_guid%TYPE DEFAULT NULL
  )
  IS
    l_targets MGMT_TARGET_ASSOC_LIST:= MGMT_TARGET_ASSOC_LIST();
    l_temp_assocs MGMT_TARGET_ASSOC_LIST:= MGMT_TARGET_ASSOC_LIST();
    l_user_model_assocs MGMT_TARGET_ASSOC_LIST:= MGMT_TARGET_ASSOC_LIST();
    l_assocs MGMT_TARGET_ASSOC_LIST:= MGMT_TARGET_ASSOC_LIST();
    l_last_target_guid mgmt_targets.target_guid%TYPE := NULL;
    l_last_operation NUMBER := NULL;
    l_call_back_type NUMBER;
    l_assoc_targets MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY();
    l_singular_chk NUMBER := 0;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('recreate_flat_target_list:Entry',MODULE_NAME);
    END IF;

SELECT MGMT_TARGET_ASSOC_REC.new(a.assoc_guid, a.source_target_guid,
                                     a.assoc_target_guid, a.scope_target_guid,
                                     a.operation,
                                     (SELECT d.prop_view_priv 
                                        FROM mgmt_target_assoc_defs d 
                                       WHERE d.assoc_guid = a.assoc_guid
                                      ) 
                                    )BULK COLLECT
      INTO l_targets
      FROM THE(SELECT  CAST(p_targets AS MGMT_TARGET_ASSOC_LIST) FROM DUAL ) a
     ORDER BY a.operation, a.source_target_guid;

-- While adding association check whether loop exists in the assoc 
    FOR i IN 1..l_targets.count
    LOOP
      IF (l_targets(i).operation = 1) AND (l_targets(i).prop_view_priv = MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION) 
         AND (NOT ( target_already_covered(l_targets(i).source_target_guid))) THEN
        BEGIN
          --BUG:4526612 - Connect by clause does not support sub query in v9
          IF (EMD_MAINTENANCE.repository_db_v9) THEN
            INSERT
              INTO mgmt_target_temp_list
            SELECT a.assoc_target_guid
              FROM (
                    SELECT decode(assoc.scope_target_guid,
                                  MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID, assoc.source_target_guid, assoc.scope_target_guid) source_target_guid, 
                            assoc.assoc_target_guid assoc_target_guid
                      FROM mgmt_target_assocs assoc, 
                           mgmt_target_assoc_defs def
                     WHERE def.assoc_guid = assoc.assoc_guid 
                       AND def.prop_view_priv = MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION
                    )a
              START WITH a.source_target_guid = l_targets(i).source_target_guid
              CONNECT BY PRIOR a.assoc_target_guid = a.source_target_guid;

ELSE  
            INSERT
              INTO mgmt_target_temp_list
              WITH outer_assoc as ( SELECT assoc_guid 
                                      FROM mgmt_target_assoc_defs 
                                     WHERE prop_view_priv = MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION ) 
             SELECT a.assoc_target_guid 
               FROM mgmt_target_assocs a
              START WITH  ( decode(a.scope_target_guid, 
                                   MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID, a.source_target_guid, 
                                   a.scope_target_guid )= l_targets(i).source_target_guid 
                              AND a.assoc_guid IN (SELECT assoc_guid FROM outer_assoc)
                          )
            CONNECT BY ( PRIOR a.assoc_target_guid = a.source_target_guid 
                           AND a.assoc_guid IN (SELECT assoc_guid FROM outer_assoc)
                       ) ;
           END IF;
         EXCEPTION
           WHEN OTHERS THEN
             IF SQLCODE = LOOP_IN_ASSOC THEN
               IF EMDW_LOG.P_IS_ERROR_SET THEN
                 EMDW_LOG.error('LOOP exist in Associations ', MODULE_NAME);
               END IF;
               raise_application_error(mgmt_global.cyclic_member_assoc_err,
                                      'Loop exist in associations  for target:'|| l_targets(i).source_target_guid);
            ELSE
              RAISE ;
            END IF;
         END;

INSERT
          INTO mgmt_target_temp_list
        VALUES (l_targets(i).source_target_guid);

END IF;

END LOOP;

DELETE
      FROM mgmt_target_temp_list;

-- When a target is deleted then we are checking whether given target has only HOSTED BY association or not
    -- If target being deleted has only one association of type HOSTED BY then we will update flat table
    -- by different procedure.
    IF p_delete_target_guid IS NOT NULL THEN
      l_singular_chk := chk_agent_singular_target(p_targets, p_delete_target_guid) ;
    END IF;

SELECT MGMT_TARGET_ASSOC_REC.new(a.assoc_guid, 
                                     decode(a.scope_target_guid,MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID,a.source_target_guid,a.scope_target_guid),
                                     a.assoc_target_guid, a.scope_target_guid,
                                     decode(a.operation,CREATE_ASSOC_OPER,1,-1),
                                      (SELECT d.prop_view_priv
                                        FROM mgmt_target_assoc_defs d
                                       WHERE d.assoc_guid = a.assoc_guid
                                      )
                                    ) BULK COLLECT
      INTO l_assocs
      FROM THE(SELECT  CAST(p_targets AS MGMT_TARGET_ASSOC_LIST) FROM DUAL ) a
     WHERE a.assoc_guid IN (SELECT d1.assoc_guid
                              FROM mgmt_target_assoc_defs d1
                             WHERE d1.prop_view_priv IN (MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION, MGMT_ASSOC.ONE_LEVEL_VIEW_PROPAGATION)
                            )
     ORDER BY a.source_target_guid;

-- If target being deleted is not a singular assoc target so update entry in flat table using update_ref_count_in_flat_list
    IF l_singular_chk = 0 THEN

l_last_target_guid := NULL;
      -- for each target
      FOR i IN 1..l_assocs.count
      LOOP
        -- update assoc count for target
        IF ((l_assocs(i).source_target_guid != l_last_target_guid ) OR
             l_last_target_guid IS NULL )
        THEN
          IF (l_last_target_guid IS NOT NULL ) THEN
            update_ref_count_in_flat_list(l_temp_assocs );
          END IF ;
  
          l_last_target_guid := l_assocs(i).source_target_guid;
          l_temp_assocs := MGMT_TARGET_ASSOC_LIST();
  
        END IF;
  
        l_temp_assocs.extend;
        l_temp_assocs(l_temp_assocs.count) := l_assocs(i);
  
      END LOOP;
  
      IF (l_last_target_guid IS NOT NULL) THEN
         update_ref_count_in_flat_list(l_temp_assocs );
      END IF ;

--run update_assoc_count in seperate loop
    -- to reduce the lock time on the row

-- Target being deleted is a singular assoc target so update entry in flat table using upd_ref_cnt_singular
   ELSIF l_singular_chk = 1 THEN

upd_ref_cnt_singular ( p_assocs => p_targets,
                            p_target_guid => p_delete_target_guid
                          ) ;
   END IF;

DELETE
      FROM mgmt_flat_target_assoc
     WHERE ref_count = 0;

l_last_target_guid := NULL;

SELECT MGMT_TARGET_ASSOC_REC.new(assoc_guid, 
                                     decode(scope_target_guid,MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID,source_target_guid,scope_target_guid),
                                     assoc_target_guid, scope_target_guid,
                                     operation) BULK COLLECT
      INTO l_user_model_assocs
      FROM THE(SELECT  CAST(p_targets AS MGMT_TARGET_ASSOC_LIST) FROM DUAL )
     WHERE assoc_guid IN (SELECT assoc_guid
                            FROM mgmt_target_assoc_defs
                           WHERE prop_view_priv IN (MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION, MGMT_ASSOC.ONE_LEVEL_VIEW_PROPAGATION)
                           )
     ORDER BY source_target_guid;
    -- for each target
    FOR i IN 1..l_user_model_assocs.count
    LOOP
      IF ((l_user_model_assocs(i).source_target_guid != l_last_target_guid ) OR
           l_last_target_guid IS NULL OR
           l_last_operation IS NULL OR
           (l_targets(i).operation != l_last_operation))
      THEN
        IF (l_last_target_guid IS NOT NULL OR l_last_operation IS NOT NULL) THEN
          -- inform the user model that one/more association(s) has been added/deleted/modified
          mgmt_user.target_assoc_changed(l_last_target_guid,l_assoc_targets,l_last_operation );

END IF ;

l_last_target_guid := l_user_model_assocs(i).source_target_guid;
        l_last_operation := l_user_model_assocs(i).operation;
        l_assoc_targets := MGMT_USER_GUID_ARRAY();

END IF;

l_assoc_targets.extend;
      l_assoc_targets(l_assoc_targets.count) := l_user_model_assocs(i).assoc_target_guid;

END LOOP;

IF (l_last_target_guid IS NOT NULL) THEN
      -- inform the user model that one/more association(s) has been added/deleted/modified
      mgmt_user.target_assoc_changed(l_last_target_guid,l_assoc_targets,l_last_operation);
    END IF ;

FOR i IN (SELECT DISTINCT source_target_guid source_target_guid
                FROM TABLE(CAST(p_targets as MGMT_TARGET_ASSOC_LIST))
             )
    LOOP
      -- update target assoc count
      update_assoc_count(i.source_target_guid);

END LOOP;

-- user model has set priviledges, now call callbacks
    -- for each target
    IF (p_targets IS NOT NULL) THEN 
      FOR i IN 1..p_targets.count
      LOOP
        IF ( p_targets(i).operation = em_assoc.CREATE_ASSOC_OPER) THEN
          l_call_back_type := mgmt_global.G_ASSOC_ADD_CALLBACK;
        ELSE
          l_call_back_type := mgmt_global.G_ASSOC_DELETE_CALLBACK;
        END IF;
        exec_assoc_callbacks(l_call_back_type,
                             p_targets(i).assoc_guid,
                             p_targets(i).source_target_guid,
                             p_targets(i).assoc_target_guid,
                             p_targets(i).scope_target_guid);
      END LOOP;
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('recreate_flat_target_list:Exit',MODULE_NAME);
    END IF;

END recreate_flat_target_list;

-- Interanl Procedure to create a specified target association
  -- Does NOT update assoc flat table,
  -- DOES NOT call the callbacks
  PROCEDURE create_target_assoc_int
  (
    p_assoc_guid           IN mgmt_target_assocs.assoc_guid%TYPE,
    p_source_target_guid   IN mgmt_target_assocs.source_target_guid%TYPE,
    p_assoc_target_guid    IN mgmt_target_assocs.assoc_target_guid%TYPE,
    p_scope_target_guid    IN mgmt_target_assocs.source_target_guid%TYPE,
    p_is_editable          IN mgmt_target_assocs.is_editable%TYPE
                              DEFAULT mgmt_assoc.ASSOC_EDITABLE,
    p_created_by           IN mgmt_target_assocs.created_by%TYPE
                              DEFAULT mgmt_assoc.CREATED_BY_END_USER
  )
  IS
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assoc_int:Entry',MODULE_NAME);
    END IF;

IF (emdw_log.p_is_debug_set)THEN
      emdw_log.debug(' p_assoc_guid = '|| p_assoc_guid||
                     ' p_source_target_guid = '||p_source_target_guid||
                     ' p_assoc_target_guid = '||p_assoc_target_guid||
                     ' p_scope_target_guid = '||p_scope_target_guid||
                     ' p_is_editable = '||p_is_editable||
                     ' p_created_by = '||p_created_by,MODULE_NAME
                    );
    END IF;
    -- insert the entry into mgmt_target_assoc table
    BEGIN
      INSERT
        INTO mgmt_target_assocs
             (assoc_guid, source_target_guid, assoc_target_guid,
              scope_target_guid,is_editable,created_by)
      VALUES
             (p_assoc_guid, p_source_target_guid, p_assoc_target_guid,
              p_scope_target_guid,p_is_editable, p_created_by);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        IF (emdw_log.p_is_error_set)THEN
          emdw_log.error('Association already exist for specified source and scope target',
                          MODULE_NAME);
        END IF;

raise_application_error(mgmt_global.assoc_already_exist_err,
          'Association already exist for specified source and scope target');
    END;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assoc_int:Exit',MODULE_NAME);
    END IF;

END create_target_assoc_int;

/* Commented on 15/4/2005
  -- procedure to create the inferred associations
  PROCEDURE recreate_inferred_associations
  (
    p_source_target_guid IN mgmt_targets.target_guid%TYPE,
    p_assoc_target_guid  IN mgmt_targets.target_guid%TYPE,
    p_assoc_type         IN mgmt_target_assoc_defs.association_type%TYPE,
    p_operation          IN NUMBER
  )
  IS
    l_target_guid_list mgmt_assoc_target_guid_list := mgmt_assoc_target_guid_list();
    l_agent_guid mgmt_targets.target_guid%TYPE;
    l_host_guid mgmt_targets.target_guid%TYPE;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('recreate_inferred_associations:Entry',MODULE_NAME);
    END IF;

-- find all the service s
    FOR rec IN( SELECT source_target_guid
                  FROM mgmt_flat_target_assoc
                 WHERE assoc_target_guid = p_source_target_guid
                   AND is_connected = 2
              )
    LOOP
      -- delete all the inferred associations
      DELETE
        FROM mgmt_target_assocs a
       WHERE a.source_target_guid = rec.source_target_guid
         AND a.created_by = mgmt_assoc.CREATED_INFERRED
         AND a.assoc_guid = mgmt_assoc.g_depends_on_guid;

-- create new inferred associtaion
      FOR target IN( SELECT assoc_target_guid
                       FROM mgmt_flat_target_assoc
                      WHERE source_target_guid = rec.source_target_guid
                        AND is_connected = 2
                   )
      LOOP
        IF NOT (p_operation = DELETE_ASSOC_OPER AND
                target.assoc_target_guid = p_assoc_target_guid)
        THEN
          create_target_assoc_int(mgmt_assoc.g_depends_on_guid,
                                  rec.source_target_guid,
                                  target.assoc_target_guid,
                                  rec.source_target_guid,
                                  mgmt_assoc.ASSOC_NOT_EDITABLE,
                                  mgmt_assoc.CREATED_INFERRED);
      END IF;

IF (p_operation = CREATE_ASSOC_OPER) THEN
        create_target_assoc_int(mgmt_assoc.g_depends_on_guid,
                                rec.source_target_guid,
                                p_assoc_target_guid,
                                rec.source_target_guid,
                                mgmt_assoc.ASSOC_NOT_EDITABLE,
                                mgmt_assoc.CREATED_INFERRED);
      END IF;

END LOOP; -- inner loop

END LOOP;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('recreate_inferred_associations:Exit',MODULE_NAME);
    END IF;

END recreate_inferred_associations;
*/
  --Procedure to delete a specified target association
  -- Does NOT update assoc flat table,
  -- DOES NOT call the callbacks
  PROCEDURE delete_target_assoc_int
  (
    p_assoc_guid         IN mgmt_target_assoc_defs.assoc_guid%TYPE,
    p_source_target_guid IN mgmt_target_assocs.source_target_guid%TYPE,
    p_assoc_target_guid  IN mgmt_target_assocs.assoc_target_guid%TYPE,
    p_scope_target_guid  IN mgmt_target_assocs.scope_target_guid%TYPE
  )
  IS
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assoc_int:Entry',MODULE_NAME);
    END IF;

BEGIN

DELETE
        FROM mgmt_target_assocs
       WHERE assoc_guid = p_assoc_guid
         AND source_target_guid = p_source_target_guid
         AND scope_target_guid = p_scope_target_guid
         AND assoc_target_guid = p_assoc_target_guid;

IF SQL%ROWCOUNT = 0 THEN
        raise NO_DATA_FOUND ;
      END IF;

EXCEPTION
      WHEN NO_DATA_FOUND THEN
        raise_application_error(mgmt_global.ASSOC_DOES_NOT_EXIST_ERR,
                                'Specified Association does not exist '||p_assoc_guid||','
                                ||p_source_target_guid||','||p_assoc_target_guid||','
                                ||p_scope_target_guid);

IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('Specified Association does not exist', MODULE_NAME);
        END IF;

END;

-- delete properties of this assoiation 
    DELETE
      FROM mgmt_target_assoc_prop
     WHERE assoc_guid = p_assoc_guid
       AND source_target_guid = p_source_target_guid
       AND scope_target_guid = p_scope_target_guid
       AND assoc_target_guid = p_assoc_target_guid;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assoc_int:Exit',MODULE_NAME);
    END IF;

END delete_target_assoc_int;

-- It is calledd, whenever a target is deleted
  PROCEDURE delete_target
  (
    p_target_name IN mgmt_targets.target_name%TYPE,
    p_target_type IN mgmt_targets.target_type%TYPE,
    p_target_guid IN mgmt_targets.target_guid%TYPE
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
    l_target_guid mgmt_targets.target_guid%TYPE;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target:Entry',MODULE_NAME);
    END IF;

-- l_target_guid := mgmt_target.get_target_guid(p_target_name,p_target_type);
    l_target_guid := p_target_guid ;
    --When a target is deleted, all its associations
    --should also be deleted.

SELECT MGMT_TARGET_ASSOC_REC.new(assoc_guid,source_target_guid,
                                     assoc_target_guid, scope_target_guid,
                                     em_assoc.DELETE_ASSOC_OPER)BULK COLLECT
      INTO l_affected_targets
      FROM mgmt_target_assocs
     WHERE source_target_guid = l_target_guid
        OR scope_target_guid = l_target_guid
        OR assoc_target_guid = l_target_guid ;

FOR i in 1..l_affected_targets.count
    LOOP
      delete_target_assoc_int(l_affected_targets(i).assoc_guid,
                              l_affected_targets(i).source_target_guid,
                              l_affected_targets(i).assoc_target_guid,
                              l_affected_targets(i).scope_target_guid);
    END LOOP ;

-- Also delete pending associations
    DELETE
      FROM mgmt_target_pending_assocs
     WHERE source_target_guid = l_target_guid
        OR scope_target_guid = l_target_guid
        OR assoc_target_guid = l_target_guid;

-- refresh flat assoc table
    IF (l_affected_targets.count != 0) THEN
      recreate_flat_target_list(l_affected_targets, l_target_guid);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target:Exit',MODULE_NAME);
    END IF;

END delete_target;

--Procedure to delete a specified target association
  -- Does NOT update assoc flat table,
  -- returns the affected associations
  -- Calls the callbacks

PROCEDURE delete_target_assoc_int
  (
    p_affected_targets   IN OUT MGMT_TARGET_ASSOC_LIST,
    p_assoc_def_name     IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_name IN mgmt_targets.target_name%TYPE,
    p_source_target_type IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_assoc_target_name  IN mgmt_targets.target_name%TYPE,
    p_assoc_target_type  IN mgmt_target_assoc_defs.assoc_target_type%TYPE,
    p_scope_target_name  IN mgmt_targets.target_name%TYPE DEFAULT ' ',
    p_scope_target_type  IN mgmt_target_assoc_defs.assoc_target_type%TYPE
                                   DEFAULT mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE

)
  IS
    l_pending_assoc BOOLEAN := TRUE;
    l_assoc_guid mgmt_target_assocs.assoc_guid%TYPE;
    l_source_target_guid mgmt_target_assocs.source_target_guid%TYPE;
    l_assoc_target_guid  mgmt_target_assocs.assoc_target_guid%TYPE;
    l_scope_target_guid  mgmt_target_assocs.scope_target_guid%TYPE;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assoc_int:Entry',MODULE_NAME);
    END IF;

BEGIN
      l_assoc_guid := get_assoc_def_guid(p_assoc_def_name,
                                         p_source_target_type,
                                         p_scope_target_type);
    EXCEPTION
      WHEN mgmt_global.assoc_def_does_not_exist THEN
        raise_application_error(mgmt_global.assoc_def_does_not_exist_err,
                                'Assoc def does not exist');
    END;

BEGIN
      l_source_target_guid := mgmt_target.get_target_guid(p_source_target_name,
                                                          p_source_target_type);
    EXCEPTION
      WHEN MGMT_GLOBAL.target_does_not_exist THEN
        l_pending_assoc := FALSE;
        l_source_target_guid := mgmt_target.generate_target_guid(p_source_target_name,
                                                                 p_source_target_type);
    END;

BEGIN
      l_assoc_target_guid := mgmt_target.get_target_guid(p_assoc_target_name,
                                                         p_assoc_target_type);
    EXCEPTION
      WHEN MGMT_GLOBAL.target_does_not_exist THEN
        l_pending_assoc := FALSE;
        l_assoc_target_guid := mgmt_target.generate_target_guid(p_assoc_target_name,
                                                                p_assoc_target_type);
    END;

IF (p_scope_target_type = mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE) THEN
        l_scope_target_guid := mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID;
    ELSE
      BEGIN
        l_scope_target_guid := mgmt_target.get_target_guid(p_scope_target_name,
                                                           p_scope_target_type);
      EXCEPTION
        WHEN MGMT_GLOBAL.target_does_not_exist THEN
          l_pending_assoc := FALSE;
          l_scope_target_guid := mgmt_target.generate_target_guid(p_scope_target_name,
                                                                  p_scope_target_type);
      END;
    END IF;

IF( l_pending_assoc = TRUE) THEN
      delete_target_assoc_int(l_assoc_guid,
                              l_source_target_guid,
                              l_assoc_target_guid,
                              l_scope_target_guid);

p_affected_targets.extend;
      p_affected_targets(p_affected_targets.count)
                                 := MGMT_TARGET_ASSOC_REC.new(l_assoc_guid,
                                                              l_source_target_guid,
                                                              l_assoc_target_guid,
                                                              l_scope_target_guid,
                                                              em_assoc.DELETE_ASSOC_OPER);

ELSE -- its a pending association
      DELETE
        FROM mgmt_target_pending_assocs
       WHERE assoc_guid = l_assoc_guid
         AND source_target_guid = l_source_target_guid
         AND assoc_target_guid = l_assoc_target_guid
         AND scope_target_guid = l_scope_target_guid;
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assoc_int:Exit',MODULE_NAME);
    END IF;

END delete_target_assoc_int;

--Procedure to delete a specified target association based on guids
  -- Does NOT update assoc flat table,
  -- returns the affected associations
  -- Calls the callbacks

PROCEDURE delete_target_assoc_int
  (
    p_affected_targets   IN OUT MGMT_TARGET_ASSOC_LIST,
    p_assoc_guid         IN mgmt_target_assocs.assoc_guid%TYPE,
    p_source_target_guid IN mgmt_target_assocs.source_target_guid%TYPE,
    p_assoc_target_guid  IN mgmt_target_assocs.assoc_target_guid%TYPE,
    p_scope_target_guid  IN mgmt_target_assocs.scope_target_guid%TYPE
                                   DEFAULT mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID

)
  IS
    l_pending_assoc BOOLEAN := TRUE;
    l_found NUMBER(1) ;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assoc_int:Entry',MODULE_NAME);
    END IF;

BEGIN
      SELECT count(*)
        INTO l_found
        FROM mgmt_target_assoc_defs 
       WHERE assoc_guid = p_assoc_guid ;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        raise_application_error(mgmt_global.assoc_def_does_not_exist_err,
                                'Assoc def does not exist');
    END;

BEGIN
      SELECT count(*)
        INTO l_found
        FROM mgmt_targets 
       WHERE target_guid = p_source_target_guid;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_pending_assoc := FALSE;
    END;

BEGIN
      SELECT count(*)
        INTO l_found
        FROM mgmt_targets 
       WHERE target_guid = p_assoc_target_guid;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_pending_assoc := FALSE;
    END;

IF (p_scope_target_guid != mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID) THEN
      BEGIN
        SELECT count(*)
          INTO l_found
          FROM mgmt_targets 
         WHERE target_guid = p_source_target_guid;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          l_pending_assoc := FALSE;
      END;
    END IF;

IF( l_pending_assoc = TRUE) THEN
      delete_target_assoc_int(p_assoc_guid,
                              p_source_target_guid,
                              p_assoc_target_guid,
                              p_scope_target_guid);

p_affected_targets.extend;
      p_affected_targets(p_affected_targets.count)
                                 := MGMT_TARGET_ASSOC_REC.new(p_assoc_guid,
                                                              p_source_target_guid,
                                                              p_assoc_target_guid,
                                                              p_scope_target_guid,
                                                              em_assoc.DELETE_ASSOC_OPER);

ELSE -- its a pending association
      DELETE
        FROM mgmt_target_pending_assocs
       WHERE assoc_guid = p_assoc_guid
         AND source_target_guid = p_source_target_guid
         AND assoc_target_guid = p_assoc_target_guid
         AND scope_target_guid = p_scope_target_guid;
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assoc_int:Exit',MODULE_NAME);
    END IF;

END delete_target_assoc_int;
  --Procedure to create a specified target association
  -- Does NOT update assoc flat table,
  -- returns the affected associations
  -- Calls the callbacks
 
  PROCEDURE create_target_assoc_int
  (
    p_affected_targets     IN OUT MGMT_TARGET_ASSOC_LIST,
    p_assoc_def_name       IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_name   IN mgmt_targets.target_name%TYPE,
    p_source_target_type   IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_assoc_target_name    IN mgmt_targets.target_name%TYPE,
    p_assoc_target_type    IN mgmt_target_assoc_defs.assoc_target_type%TYPE,
    p_scope_target_name    IN mgmt_targets.target_name%TYPE DEFAULT ' ',
    p_scope_target_type    IN mgmt_targets.target_type%TYPE
                                      DEFAULT mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE,
    p_is_editable          IN mgmt_target_assocs.is_editable%TYPE
                                      DEFAULT mgmt_assoc.ASSOC_NOT_EDITABLE,
    p_created_by           IN mgmt_target_assocs.created_by%TYPE
                                      DEFAULT mgmt_assoc.CREATED_BY_END_USER
  )
  IS
    l_pending_assoc BOOLEAN := TRUE;
    l_assoc_guid mgmt_target_assocs.assoc_guid%TYPE;
    l_source_target_guid mgmt_target_assocs.source_target_guid%TYPE;
    l_assoc_target_guid  mgmt_target_assocs.assoc_target_guid%TYPE;
    l_scope_target_guid  mgmt_target_assocs.scope_target_guid%TYPE;
    l_source_target_type mgmt_target_assoc_defs.source_target_type%TYPE;
    l_assoc_target_type  mgmt_target_assoc_defs.assoc_target_type%TYPE;
    l_scope_target_type  mgmt_target_assoc_defs.scope_target_type%TYPE;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assoc_int:Entry',MODULE_NAME);
    END IF;

BEGIN
      l_assoc_guid := get_assoc_def_guid(p_assoc_def_name,
                                         p_source_target_type,
                                         p_scope_target_type);
    EXCEPTION
      WHEN mgmt_global.assoc_def_does_not_exist THEN
        raise_application_error(MGMT_GLOBAL.ASSOC_DEF_DOES_NOT_EXIST_ERR,
                                'The specified association def does not exist');

IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('The specified association def does not exist',MODULE_NAME);
        END IF;
    END;
    SELECT source_target_type,assoc_target_type,scope_target_type
      INTO l_source_target_type,l_assoc_target_type,l_scope_target_type
      FROM mgmt_target_assoc_defs
     WHERE assoc_guid = l_assoc_guid;

-- check whether target type matches with types mentioned in association def
    IF( p_source_target_type != l_source_target_type AND
        l_source_target_type != mgmt_assoc.ANY_TARGET_TYPE)
    THEN
      raise_application_error(mgmt_global.invalid_params_err,
        'Invalid parameters : Invalid Source Target Type');

IF (emdw_log.p_is_error_set)THEN
        emdw_log.error('Invalid parameters : Invalid Source Target Type',MODULE_NAME);
      END IF;
    END IF;

IF ( p_assoc_target_type != l_assoc_target_type AND
         l_assoc_target_type != mgmt_assoc.ANY_TARGET_TYPE)
    THEN
      raise_application_error(mgmt_global.invalid_params_err,
                              'Invalid parameters : Invalid Associated Target Type');

IF (emdw_log.p_is_error_set)THEN
        emdw_log.error('Invalid parameters : Invalid Associated Target Type',MODULE_NAME);
      END IF;
    END IF;

IF ( p_scope_target_type != l_scope_target_type AND
         l_scope_target_type != mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE)
    THEN
      raise_application_error(mgmt_global.invalid_params_err,
                              'Invalid parameters : Invalid Scope Target Type');

IF (emdw_log.p_is_error_set)THEN
        emdw_log.error('Invalid parameters : Invalid Scope Target Type',MODULE_NAME);
      END IF;
    END IF;

IF (emdw_log.p_is_debug_set)THEN
      emdw_log.debug('All target types match with the target types'||
                     'specified in the association definition',MODULE_NAME);
    END IF;

IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('source target does not exist',MODULE_NAME);
        END IF;

l_source_target_guid := mgmt_target.generate_target_guid(p_source_target_name,
                                                                 p_source_target_type);
    END;

IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('assoc target does not exist',MODULE_NAME);
        END IF;

l_assoc_target_guid := mgmt_target.generate_target_guid(p_assoc_target_name,
                                                                p_assoc_target_type);
    END;

IF (emdw_log.p_is_debug_set)THEN
            emdw_log.debug('scope target does not exist',MODULE_NAME);
          END IF;
          l_scope_target_guid := mgmt_target.generate_target_guid(p_scope_target_name,
                                                                  p_scope_target_type);
      END;
    END IF;

IF( l_pending_assoc = TRUE) THEN
      create_target_assoc_int(l_assoc_guid,
                              l_source_target_guid,
                              l_assoc_target_guid,
                              l_scope_target_guid);

p_affected_targets.extend;
      p_affected_targets(p_affected_targets.count)
                             := MGMT_TARGET_ASSOC_REC.new(l_assoc_guid,
                                                          l_source_target_guid,
                                                          l_assoc_target_guid,
                                                          l_scope_target_guid,
                                                          em_assoc.CREATE_ASSOC_OPER);

ELSE -- its a pending association
      BEGIN
        INSERT
          INTO mgmt_target_pending_assocs
               (assoc_guid, source_target_guid, assoc_target_guid, scope_target_guid,
                is_editable, created_by)
        VALUES
               (l_assoc_guid, l_source_target_guid,l_assoc_target_guid,l_scope_target_guid,
                p_is_editable, p_created_by);

EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          IF (emdw_log.p_is_error_set)THEN
            emdw_log.error('Association already exist for specified source_target and scope_target',
                            MODULE_NAME);
          END IF;
          raise_application_error(mgmt_global.assoc_already_exist_err,
            'Association already exist for specified source_target and scope_target');
      END;
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assoc_int:Exit',MODULE_NAME);
    END IF;
  END create_target_assoc_int;

-- Generates the guid for a given assoc def
  -- For Global Scope, Guid is hash for source_target_type and assoc_def_name
  -- otherwise it is hash of scope_target_type and assoc_def_name
  FUNCTION generate_assoc_def_guid
  (
    p_assoc_name         IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_type IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_scope_target_type  IN mgmt_targets.target_type%TYPE
                              DEFAULT mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE
  )
    RETURN mgmt_target_assoc_defs.assoc_guid%TYPE
  IS
  BEGIN
    IF (p_scope_target_type = mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE) THEN
      RETURN DBMS_OBFUSCATION_TOOLKIT.md5(
         input => UTL_RAW.cast_to_raw(p_source_target_type ||
                  ';'|| p_assoc_name));
    END IF;

RETURN DBMS_OBFUSCATION_TOOLKIT.md5(
         input => UTL_RAW.cast_to_raw(p_scope_target_type ||
                  ';'|| p_assoc_name));

END generate_assoc_def_guid;

-- Returns the guid of the specified assoc_def
  FUNCTION get_assoc_def_guid
  (
    p_assoc_name         IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_type IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_scope_target_type  IN mgmt_targets.target_type%TYPE
                              DEFAULT mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE
  )
  RETURN mgmt_target_assoc_defs.assoc_guid%TYPE
  IS
    l_assoc_guid  mgmt_target_assoc_defs.assoc_guid%TYPE;
  BEGIN
    IF (p_scope_target_type = mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE) THEN
      BEGIN
        SELECT assoc_guid
          INTO l_assoc_guid
          FROM mgmt_target_assoc_defs
         WHERE assoc_def_name = p_assoc_name
           AND (source_target_type = p_source_target_type OR
                source_target_type = mgmt_assoc.ANY_TARGET_TYPE );
        RETURN l_assoc_guid;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          raise_application_error( mgmt_global.assoc_def_does_not_exist_err,
                                  'Assoc def does not exist');
      END;
    ELSE
      BEGIN
        SELECT assoc_guid
          INTO l_assoc_guid
          FROM mgmt_target_assoc_defs
         WHERE assoc_def_name = p_assoc_name
           AND (scope_target_type = p_scope_target_type OR
                scope_target_type = mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE );
        RETURN l_assoc_guid;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          raise_application_error( mgmt_global.assoc_def_does_not_exist_err,
                                  'Assoc def does not exist');
      END;
    END IF;

END get_assoc_def_guid;

--Procedure to create a specified target association DEF
  PROCEDURE create_target_assoc_def
  (
    p_assoc_name           IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_type   IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_assoc_target_type    IN mgmt_target_assoc_defs.assoc_target_type%TYPE,
    p_scope_target_type    IN mgmt_target_assoc_defs.scope_target_type%TYPE
                                DEFAULT MGMT_ASSOC.GLOBAL_SCOPE_TARGET_TYPE,
    p_assoc_name_nlsid     IN mgmt_target_assoc_defs.name_nlsid%TYPE DEFAULT NULL,
    p_description          IN mgmt_target_assoc_defs.description%TYPE DEFAULT NULL,
    p_description_nlsid    IN mgmt_target_assoc_defs.description_nlsid%TYPE DEFAULT NULL,
    p_assoc_type           IN mgmt_target_assoc_defs.association_type%TYPE
                                DEFAULT MGMT_ASSOC.ASSOC_TYPE_RELATES_TO,
    p_cardinality          IN mgmt_target_assoc_defs.cardinality%TYPE
                                DEFAULT MGMT_ASSOC.OPTIONAL_MULTI_CARDINAL,
    p_view_prop            IN mgmt_target_assoc_defs.prop_view_priv%TYPE
                                DEFAULT MGMT_ASSOC.NO_VIEW_PROPAGATION
  )
  IS
    -- local variables
    l_assoc_guid mgmt_target_assoc_defs.assoc_guid%TYPE;
    l_description mgmt_target_assoc_defs.description%TYPE;
    l_assoc_name_nlsid mgmt_target_assoc_defs.description_nlsid%TYPE;
    l_description_nlsid mgmt_target_assoc_defs.description_nlsid%TYPE;
    l_count NUMBER(1) := 0;
  BEGIN
    IF (emdw_log.p_is_debug_set )THEN
       emdw_log.debug('create_target_assoc_def:Entry',MODULE_NAME);
    END IF;

-- check all the required parameters
    IF (p_assoc_name IS NULL) THEN
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
                              'Invalid parameters : NULL Association Name');
    END IF;

IF (p_source_target_type IS NULL) THEN
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
                              'Invalid parameters : NULL source target type');
    END IF;

IF (p_view_prop != MGMT_ASSOC.NO_VIEW_PROPAGATION   AND 
        p_view_prop != MGMT_ASSOC.ONE_LEVEL_VIEW_PROPAGATION AND 
        p_view_prop != MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION)  
    THEN
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
                              'Invalid parameters : Invalid Value for View Propagation');
    END IF;

IF (p_assoc_target_type IS NULL) THEN
      raise_application_error(mgmt_global.invalid_params_err,
        'Invalid parameters : NULL associated target type');
    END IF;

IF (emdw_log.p_is_debug_set )THEN
      emdw_log.debug('All required parameters are there',MODULE_NAME);
    END IF;

-- set default values
    IF (p_description IS NULL) THEN
      l_description := p_assoc_name;
    ELSE
      l_description := p_description;
    END IF;

IF (p_assoc_name_nlsid IS NULL) THEN
      l_assoc_name_nlsid := p_assoc_name || '_name';
    ELSE
      l_assoc_name_nlsid := p_assoc_name_nlsid;
    END IF;

IF (p_description_nlsid IS NULL) THEN
      l_description_nlsid := p_assoc_name || '_description';
    ELSE
      l_description_nlsid := p_description_nlsid;
    END IF;

IF (emdw_log.p_is_debug_set )THEN
      emdw_log.debug('Default values of the variables set',MODULE_NAME);
    END IF;

-- get guid for association
    l_assoc_guid := generate_assoc_def_guid(p_assoc_name,
                                            p_source_target_type,
                                            p_scope_target_type);

IF (emdw_log.p_is_debug_set)THEN
      emdw_log.debug('Got composite key guid',MODULE_NAME);
    END IF;

IF (p_scope_target_type = mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE) THEN
      BEGIN
        SELECT count(*)
          INTO l_count
          FROM mgmt_target_assoc_defs
         WHERE assoc_def_name = p_assoc_name
           AND source_target_type = mgmt_assoc.ANY_TARGET_TYPE;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          l_count := 0;
      END;
    END IF;

IF (l_count != 0) THEN
      IF (emdw_log.p_is_error_set)THEN
        emdw_log.error('Association name already exist for ANY TARGET TYPE',MODULE_NAME);
      END IF;
      raise_application_error(mgmt_global.assoc_already_exist_err,
        'Association name already exist for ANY TARGET TYPE');
    END IF;

BEGIN
      INSERT
        INTO mgmt_target_assoc_defs
             (assoc_guid, assoc_def_name, name_nlsid, description,
              description_nlsid,source_target_type, assoc_target_type,
              cardinality, association_type,scope_target_type,prop_view_priv )
      VALUES
             (l_assoc_guid, p_assoc_name, l_assoc_name_nlsid,l_description,
              l_description_nlsid,p_source_target_type, p_assoc_target_type,
              p_cardinality, p_assoc_type,p_scope_target_type,p_view_prop);

EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('Association name already exist',MODULE_NAME);
        END IF;

raise_application_error(mgmt_global.assoc_already_exist_err,
            'Association name already exist for specified source_target_type');
    END;

IF (emdw_log.p_is_debug_set)THEN
      emdw_log.debug('create_target_assoc_def:Exit',MODULE_NAME);
    END IF;

END create_target_assoc_def;

--Procedure to delete a specified Target Association Def
  PROCEDURE delete_target_assoc_def
  (
    p_assoc_name         IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_type IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_scope_target_type  IN mgmt_target_assoc_defs.scope_target_type%TYPE
                              DEFAULT mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE
  )
  IS
    l_assoc_guid mgmt_target_assoc_defs.assoc_guid%TYPE ;
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();

BEGIN

IF (emdw_log.p_is_debug_set) THEN
      emdw_log.debug('DELETE_TARGET_ASSOC_DEF:Entry',MODULE_NAME);
    END IF;

-- check all the required parameters
    IF (p_assoc_name IS NULL) THEN
      raise_application_error(mgmt_global.invalid_params_err,
                              'Invalid parameters : NULL Association Name');
    END IF;

IF (p_source_target_type IS NULL) THEN
      raise_application_error(mgmt_global.invalid_params_err,
                              'Invalid parameters : NULL source target type');
    END IF;

IF (emdw_log.p_is_debug_set)THEN
      emdw_log.debug('All required parameters are there',MODULE_NAME);
    END IF;

-- get assoc_def_guid
    BEGIN
      l_assoc_guid := get_assoc_def_guid(p_assoc_name,
                                         p_source_target_type,
                                         p_scope_target_type);
    EXCEPTION
      WHEN mgmt_global.assoc_def_does_not_exist THEN
        raise_application_error(mgmt_global.ASSOC_DEF_DOES_NOT_EXIST_ERR,
                              'Association Def does not exist');
        IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('Association Def does not exist',MODULE_NAME);
        END IF;

END;

BEGIN
      SELECT MGMT_TARGET_ASSOC_REC.new(assoc_guid,source_target_guid,
                                       assoc_target_guid,scope_target_guid,
                                       em_assoc.DELETE_ASSOC_OPER) BULK COLLECT
        INTO l_affected_targets
        FROM ( SELECT DISTINCT assoc_guid,source_target_guid,
                      assoc_target_guid, scope_target_guid
                 FROM mgmt_target_assocs
                WHERE assoc_guid = l_assoc_guid
             );

-- first delete all the associations for this asssociation def
      FOR rec in ( SELECT *
                     FROM mgmt_target_assocs
                    WHERE assoc_guid = l_assoc_guid
                    -- remove inferred relations
                      AND created_by != mgmt_assoc.CREATED_INFERRED

)
      LOOP
        delete_target_assoc_int(rec.assoc_guid,
                                rec.source_target_guid,
                                rec.assoc_target_guid,
                                rec.scope_target_guid);
      END LOOP ;

-- refresh flat assoc table
      IF (l_affected_targets.count != 0) THEN
        recreate_flat_target_list(l_affected_targets);
      END IF;

END;

-- delete all the error message for this association
    DELETE
      FROM mgmt_target_assoc_error
     WHERE assoc_guid = l_assoc_guid ;
    -- all the associations for the assoc def have been deleted,
    -- proceed to delete it
    BEGIN
      DELETE
        FROM mgmt_target_assoc_defs
       WHERE assoc_guid = l_assoc_guid;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        raise_application_error(mgmt_global.ASSOC_DEF_DOES_NOT_EXIST_ERR,
                                'Association Def does not exist');

IF (emdw_log.p_is_debug_set)THEN
            emdw_log.debug('Association Def does not exist',MODULE_NAME);
        END IF;

END;

IF (emdw_log.p_is_debug_set)THEN
      emdw_log.debug('DELETE_TARGET_ASSOC:Exit',MODULE_NAME);
    END IF;

END delete_target_assoc_def;

--Procedure to create a specified target associations
  -- Updates the assoc flat list
  -- Calls the callbacks
  PROCEDURE create_target_assoc
  (
    p_assoc_def_name       IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_name   IN mgmt_targets.target_name%TYPE,
    p_source_target_type   IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_assoc_target_name    IN mgmt_targets.target_name%TYPE,
    p_assoc_target_type    IN mgmt_target_assoc_defs.assoc_target_type%TYPE,
    p_scope_target_name    IN mgmt_targets.target_name%TYPE
                                DEFAULT ' ',
    p_scope_target_type    IN mgmt_targets.target_type%TYPE
                                DEFAULT MGMT_ASSOC.GLOBAL_SCOPE_TARGET_TYPE,
    p_is_editable          IN mgmt_target_assocs.is_editable%TYPE
                                DEFAULT MGMT_ASSOC.ASSOC_NOT_EDITABLE
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
    l_scope_target_name mgmt_targets.target_name%TYPE;
    l_scope_target_type mgmt_target_assoc_defs.assoc_target_type%TYPE;
  BEGIN
    IF (EMDW_LOG.p_is_debug_set)THEN
      EMDW_LOG.debug('CREATE_TARGET_ASSOC:Entry '||
                      ' assoc_def_name = ['||p_assoc_def_name ||
                      '] source_target_name = ['||p_source_target_name||
                      '] source_target_type = ['||p_source_target_type||
                      '] assoc_target_name  = ['||p_assoc_target_name ||
                      '] assoc_target_type  = ['||p_assoc_target_type ||
                      '] scope_target_name  = ['||p_scope_target_name ||
                      '] scope_target_type  = ['||p_scope_target_type ||
                      '] is_editable  = '      ||p_is_editable
                      ,MODULE_NAME);
    END IF;

-- check all the required parameters
    IF (p_source_target_name IS NULL) THEN
      raise_application_error(MGMT_GLOBAL.invalid_params_err,
                              'Invalid parameters : NULL Source Target Name');
    END IF;

IF (p_source_target_type IS NULL) THEN
      raise_application_error(MGMT_GLOBAL.invalid_params_err,
                              'Invalid parameters : NULL source target type');
    END IF;

IF (p_assoc_target_name IS NULL) THEN
      raise_application_error(mgmt_global.invalid_params_err,
                              'Invalid parameters : NULL Associated Target Name');
    END IF;

IF (p_assoc_target_type IS NULL ) THEN
      raise_application_error(mgmt_global.invalid_params_err,
                              'Invalid parameters : NULL Associated Target Type');
    END IF;

l_scope_target_type := p_scope_target_type ;

IF (LENGTH(LTRIM(RTRIM(p_scope_target_type))) IS NULL) THEN
      l_scope_target_type := MGMT_ASSOC.GLOBAL_SCOPE_TARGET_TYPE;

IF (EMDW_LOG.p_is_debug_set)THEN
        EMDW_LOG.debug('Global Scope Target Type', MODULE_NAME);
      END IF;
    END IF;

IF (emdw_log.p_is_debug_set)THEN
      emdw_log.debug('All required parameters are there',MODULE_NAME);
    END IF;

create_target_assoc_int( l_affected_targets,
                             p_assoc_def_name,
                             p_source_target_name,
                             p_source_target_type,
                             p_assoc_target_name,
                             p_assoc_target_type,
                             p_scope_target_name,
                             l_scope_target_type,
                             p_is_editable,
                             mgmt_assoc.CREATED_BY_END_USER
                           );

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_debug_set)THEN
      emdw_log.debug('CREATE_TARGET_ASSOC:Exit',MODULE_NAME);
    END IF;
  END create_target_assoc;

--Procedure to delete a specified target associations
  -- Updates the assoc flat list
  -- Calls the callbacks
  PROCEDURE delete_target_assoc
  (
    p_assoc_def_name      IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_name  IN mgmt_targets.target_name%TYPE,
    p_source_target_type  IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_assoc_target_name   IN mgmt_targets.target_name%TYPE,
    p_assoc_target_type   IN mgmt_target_assoc_defs.assoc_target_type%TYPE,
    p_scope_target_name   IN mgmt_targets.target_name%TYPE
                               DEFAULT mgmt_assoc.GLOBAL_SCOPE_TARGET_NAME,
    p_scope_target_type   IN mgmt_targets.target_type%TYPE
                               DEFAULT mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('DELETE_TARGET_ASSOC:Entry',MODULE_NAME);
    END IF;

-- check all the required parameters
    IF (p_source_target_name IS NULL) THEN
      raise_application_error(mgmt_global.invalid_params_err,
                              'Invalid parameters : NULL Source Target Name');
    END IF;

IF (p_source_target_type IS NULL) THEN
      raise_application_error(mgmt_global.invalid_params_err,
        'Invalid parameters : NULL source target type');
    END IF;

IF (p_assoc_target_name IS NULL) THEN
      raise_application_error(mgmt_global.invalid_params_err,
                              'Invalid parameters : NULL Associated Target Name');
    END IF;

IF (p_assoc_target_type IS NULL ) THEN
      raise_application_error(mgmt_global.invalid_params_err,
                              'Invalid parameters : NULL Associated Target Type');
    END IF;

IF (emdw_log.p_is_debug_set)THEN
      emdw_log.debug('All required parameters are there',MODULE_NAME);
    END IF;

delete_target_assoc_int(l_affected_targets,
                            p_assoc_def_name,
                            p_source_target_name,
                            p_source_target_type,
                            p_assoc_target_name,
                            p_assoc_target_type,
                            p_scope_target_name,
                            p_scope_target_type);

IF (l_affected_targets.count != 0)THEN
     recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('DELETE_TARGET_ASSOC:Exit',MODULE_NAME);
    END IF;

END delete_target_assoc; --DELETE_TARGET_ASSOC_INSTANCE

-- It will be called from EMD_LOADER, whenever Agent port number is changed
  PROCEDURE change_agent_guid
  (
    p_old_agent_guid   IN mgmt_targets.target_guid%TYPE,
    p_new_agent_guid   IN mgmt_targets.target_guid%TYPE
  )
  IS

BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('change_agent_guid:Entry',MODULE_NAME);
    END IF;

UPDATE mgmt_target_assocs
       SET source_target_guid = p_new_agent_guid
     WHERE source_target_guid = p_old_agent_guid;

UPDATE mgmt_target_assocs
       SET assoc_target_guid = p_new_agent_guid
     WHERE assoc_target_guid = p_old_agent_guid;

UPDATE mgmt_target_pending_assocs
       SET assoc_target_guid = p_new_agent_guid
     WHERE assoc_target_guid = p_old_agent_guid;

UPDATE mgmt_target_pending_assocs
       SET source_target_guid = p_new_agent_guid
     WHERE source_target_guid = p_old_agent_guid;

UPDATE mgmt_flat_target_assoc
       SET assoc_target_guid = p_new_agent_guid
     WHERE assoc_target_guid = p_old_agent_guid;

UPDATE mgmt_flat_target_assoc
       SET source_target_guid = p_new_agent_guid
     WHERE source_target_guid = p_old_agent_guid;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('change_agent_guid:Exit',MODULE_NAME);
    END IF;
  END change_agent_guid;

-- it will be called, whenever a target is added
  PROCEDURE update_pending_assoc
  (
    p_target_name IN mgmt_targets.target_name%TYPE,
    p_target_type IN mgmt_targets.target_type%TYPE,
    p_target_guid IN mgmt_targets.target_guid%TYPE
  )
  IS
    l_target_guid mgmt_targets.target_guid%TYPE;
    l_affected_targets MGMT_TARGET_ASSOC_LIST:= MGMT_TARGET_ASSOC_LIST();
    l_temp NUMBER;
    l_agent_guid mgmt_targets.target_guid%TYPE;
    l_agent_name mgmt_targets.target_name%TYPE;
    l_host_guid mgmt_targets.target_guid%TYPE;
    l_host_name mgmt_targets.target_name%TYPE;
    l_valid_association BOOLEAN := TRUE;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('update_pending_assoc:Entry p_target_name='||p_target_name||' ,p_target_type='
                    ||p_target_type||' ,p_target_guid='||p_target_guid ,MODULE_NAME);
    END IF;

-- Find the associations which are now completed
    -- Move those to mgmt_target_assoc table
    -- optimise this query
    FOR rec IN(SELECT pa.source_target_guid source_target_guid,
                      t1.target_name source_target_name,
                      t1.target_type source_target_type,
                      pa.assoc_target_guid assoc_target_guid,
                      t2.target_name assoc_target_name,
                      t2.target_type assoc_target_type,
                      pa.scope_target_guid scope_target_guid,
                      t3.target_name scope_target_name,
                      t3.target_type scope_target_type,
                      pa.is_editable is_editable,
                      pa.created_by created_by,
                      pa.assoc_guid assoc_guid,
                      d.source_target_type source_target_type_spec,
                      d.assoc_target_type assoc_target_type_spec,
                      d.scope_target_type scope_target_type_spec,
                      d.assoc_def_name assoc_name
                 FROM mgmt_target_pending_assocs pa,
                      mgmt_target_assoc_defs d,
                      mgmt_targets t1,
                      mgmt_targets t2,
                      mgmt_targets t3
                WHERE ( pa.source_target_guid = p_target_guid
                        OR pa.assoc_target_guid  = p_target_guid
                        OR pa.scope_target_guid  = p_target_guid
                      )
                  AND t1.target_guid = pa.source_target_guid
                  AND t2.target_guid = pa.assoc_target_guid
                  AND t3.target_guid = pa.scope_target_guid
                  AND d.assoc_guid = pa.assoc_guid
               UNION ALL
               SELECT pa.source_target_guid source_target_guid,
                      t1.target_name source_target_name,
                      t1.target_type source_target_type,
                      pa.assoc_target_guid assoc_target_guid,
                      t2.target_name assoc_target_name,
                      t2.target_type assoc_target_type,
                      pa.scope_target_guid scope_target_guid,
                      ' ' scope_target_name,
                      mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE scope_target_type,
                      pa.is_editable is_editable,
                      pa.created_by created_by,
                      pa.assoc_guid assoc_guid,
                      d.source_target_type source_target_type_spec,
                      d.assoc_target_type assoc_target_type_spec,
                      d.scope_target_type scope_target_type_spec,
                      d.assoc_def_name assoc_name
                 FROM mgmt_target_pending_assocs pa,
                      mgmt_target_assoc_defs d,
                      mgmt_targets t1,
                      mgmt_targets t2
                WHERE ( pa.source_target_guid = p_target_guid
                        OR pa.assoc_target_guid  = p_target_guid
                      )
                  AND t1.target_guid = pa.source_target_guid
                  AND t2.target_guid = pa.assoc_target_guid
                  AND pa.scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID
                  AND d.assoc_guid = pa.assoc_guid
              )
    LOOP
      IF (emdw_log.p_is_debug_set)THEN
        emdw_log.debug('moving pending assoc',MODULE_NAME);
      END IF;
    --check whether association conforms to assoc def 
    -- if not log an error message, don't do any thing 
    
    l_valid_association := TRUE;
    -- check whether target type matches with types mentioned in association def
    IF( rec.source_target_type_spec != rec.source_target_type AND
        rec.source_target_type_spec != mgmt_assoc.ANY_TARGET_TYPE)
    THEN
      IF (emdw_log.p_is_error_set)THEN
        emdw_log.error('Invalid parameters : Invalid Source Target Type',MODULE_NAME);
      END IF;
      l_valid_association := FALSE;
    END IF;

IF ( rec.assoc_target_type != rec.assoc_target_type_spec AND
         rec.assoc_target_type_spec != mgmt_assoc.ANY_TARGET_TYPE)
    THEN
      IF (emdw_log.p_is_error_set)THEN
        emdw_log.error('Invalid parameters : Invalid Associated Target Type',MODULE_NAME);
      END IF;
      l_valid_association := FALSE;
    END IF;

IF ( rec.scope_target_type != rec.scope_target_type_spec AND
         rec.scope_target_type_spec != mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE)
    THEN
      IF (emdw_log.p_is_error_set)THEN
        emdw_log.error('Invalid parameters : Invalid Scope Target Type',MODULE_NAME);
      END IF;
      l_valid_association := FALSE;
    END IF;

IF (emdw_log.p_is_debug_set AND l_valid_association = TRUE)THEN
      emdw_log.debug('All target types match with the target types'||
                     'specified in the association definition',MODULE_NAME);
    END IF;

IF (l_valid_association = TRUE) THEN
      create_target_assoc_int( rec.assoc_guid,
                               rec.source_target_guid,
                               rec.assoc_target_guid,
                               rec.scope_target_guid,
                               rec.is_editable,
                               rec.created_by
                             );
      l_affected_targets.extend;
      l_affected_targets(l_affected_targets.count)
        := MGMT_TARGET_ASSOC_REC.new(rec.assoc_guid,
                                     rec.source_target_guid,
                                     rec.assoc_target_guid,
                                     rec.scope_target_guid,
                                     em_assoc.CREATE_ASSOC_OPER);

DELETE
          FROM mgmt_target_pending_assocs
         WHERE assoc_guid = rec.assoc_guid
           AND source_target_guid = rec.source_target_guid
           AND assoc_target_guid  = rec.assoc_target_guid
           AND scope_target_guid  = rec.scope_target_guid;
      END IF;
    END LOOP;

-- if target is not an agent, create monitored-by association
    IF (p_target_type != mgmt_global.G_AGENT_TARGET_TYPE) THEN
      BEGIN
        SELECT 1
          INTO l_temp
          FROM mgmt_target_assocs
         WHERE source_target_guid = p_target_guid
           AND scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID
           AND assoc_guid = mgmt_assoc.g_monitored_by_guid
           AND created_by = mgmt_assoc.CREATED_BY_SYSTEM;

IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('Monitored by assoc already exist',MODULE_NAME);
        END IF;

EXCEPTION
        WHEN NO_DATA_FOUND THEN
          -- find the agent for this target
          BEGIN
            SELECT agent.target_guid, agent.target_name
              INTO l_agent_guid, l_agent_name
              FROM mgmt_targets target,
                   mgmt_targets agent
             WHERE target.target_guid = p_target_guid
               AND agent.emd_url = target.emd_url
               AND agent.target_type =mgmt_global.G_AGENT_TARGET_TYPE
               AND ROWNUM = 1;
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
              l_agent_guid := NULL;

IF (emdw_log.p_is_debug_set)THEN
                emdw_log.debug('Could not find agent',MODULE_NAME);
              END IF;
          END;

IF (l_agent_guid IS NOT NULL) THEN
            IF (emdw_log.p_is_debug_set)THEN
              emdw_log.debug('Found agent',MODULE_NAME);
            END IF;

create_target_assoc_int(mgmt_assoc.g_monitored_by_guid,
                                    p_target_guid,
                                    l_agent_guid,
                                    mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID,
                                    mgmt_assoc.ASSOC_NOT_EDITABLE,
                                    mgmt_assoc.CREATED_BY_SYSTEM);

l_affected_targets.extend;
            l_affected_targets(l_affected_targets.count)
                := MGMT_TARGET_ASSOC_REC.new(mgmt_assoc.g_monitored_by_guid,
                                             p_target_guid,
                                             l_agent_guid,
                                             mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID,
                                             em_assoc.CREATE_ASSOC_OPER);
          END IF;
      END;
    END IF;

-- if the target is not host, create hosted-by asssociation
    IF (p_target_type != mgmt_global.G_HOST_TARGET_TYPE) THEN
      BEGIN
        SELECT 1
          INTO l_temp
          FROM mgmt_target_assocs
         WHERE source_target_guid = p_target_guid
           AND scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID
           AND assoc_guid = mgmt_assoc.g_hosted_by_guid;

IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('Hosted by assoc already exist',MODULE_NAME);
        END IF;

EXCEPTION
        WHEN NO_DATA_FOUND THEN

SELECT host_name
            INTO l_host_name
            FROM mgmt_targets
           WHERE target_guid = p_target_guid ;

IF (LENGTH(LTRIM(RTRIM(l_host_name))) IS NOT NULL) THEN
            create_target_assoc_int(l_affected_targets,
                                    mgmt_assoc.ASSOC_DEF_HOSTED_BY,
                                    p_target_name,
                                    p_target_type,
                                    l_host_name,
                                    mgmt_global.G_HOST_TARGET_TYPE,
                                    mgmt_assoc.GLOBAL_SCOPE_TARGET_NAME,
                                    mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE,
                                    mgmt_assoc.ASSOC_NOT_EDITABLE,
                                    mgmt_assoc.CREATED_BY_SYSTEM);
          END IF;
      END;
    END IF;

-- BUG : 5851571
    -- We have seen the cases in system where hosted_by association does not 
    -- exists for AGENT target type. This cud be due to improper cleanup of Agent.
    -- Delete all target of given Agent except AGENT itself. Now re-point this Agent
    -- to same OMS. Since this time Agent target will go under target update code path
    -- callback won't get executed and hosted_by for agent will not exists.
    --
    -- If added target type is HOST the chk for hosted_by with Agent. 
    -- If it does not exists then Add it.
    --
    IF (p_target_type = mgmt_global.G_HOST_TARGET_TYPE) THEN
      -- find the agent for this target
      BEGIN
        SELECT agent.target_guid, agent.target_name, target.host_name
          INTO l_agent_guid, l_agent_name, l_host_name
          FROM mgmt_targets target,
               mgmt_targets agent
         WHERE target.target_guid = p_target_guid
           AND agent.emd_url = target.emd_url
           AND agent.target_type =mgmt_global.G_AGENT_TARGET_TYPE
           AND ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          l_agent_guid := NULL;

IF (emdw_log.p_is_debug_set)THEN
             emdw_log.debug('Could not find agent',MODULE_NAME);
          END IF;
      END;

IF (l_agent_guid IS NOT NULL) THEN
        IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('Found agent',MODULE_NAME);
        END IF;

BEGIN
          SELECT 1
              INTO l_temp
            FROM mgmt_target_assocs
           WHERE source_target_guid = l_agent_guid
             AND assoc_target_guid = p_target_guid
             AND scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID
             AND assoc_guid = mgmt_assoc.g_hosted_by_guid;
  
          IF (emdw_log.p_is_debug_set)THEN
            emdw_log.debug('Hosted by assoc already exist',MODULE_NAME);
          END IF;
  
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            IF (LENGTH(LTRIM(RTRIM(l_host_name))) IS NOT NULL) THEN
              create_target_assoc_int(l_affected_targets,
                                      mgmt_assoc.ASSOC_DEF_HOSTED_BY,
                                      l_agent_name,
                                      mgmt_global.G_AGENT_TARGET_TYPE,
                                      l_host_name,
                                      mgmt_global.G_HOST_TARGET_TYPE,
                                      mgmt_assoc.GLOBAL_SCOPE_TARGET_NAME,
                                      mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE,
                                      mgmt_assoc.ASSOC_NOT_EDITABLE,
                                      mgmt_assoc.CREATED_BY_SYSTEM);
            END IF;
        END ;

END IF; -- (l_agent_guid IS NOT NULL)

END IF; -- p_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE

-- refresh flat assoc table
    IF (l_affected_targets.count != 0) THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('update_pending_assoc:Exit',MODULE_NAME);
    END IF;
  END update_pending_assoc;

-- it does a bulk entry of memberships into MGMT_TARGET_ASSOCS table
  -- called by loader from end_memberships_rowset
  -- it should not throw any exception
  PROCEDURE insert_memberships
  (
    p_memberships IN MGMT_MEMBER_ASSOC_LIST
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
    l_assoc_def_name mgmt_target_assoc_defs.assoc_def_name%TYPE;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('BULK:memberships_insert:Entry',MODULE_NAME);
    END IF;

l_assoc_def_name := mgmt_assoc.ASSOC_DEF_CONTAINS;

FOR i IN 1..p_memberships.count
    LOOP
    BEGIN
      create_target_assoc_int(l_affected_targets,
                              l_assoc_def_name,
                              p_memberships(i).source_target_name,
                              p_memberships(i).source_target_type,
                              p_memberships(i).assoc_target_name,
                              p_memberships(i).assoc_target_type);

EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          NULL; -- ignore duplicate memberships
    END;
    END LOOP;

--update flat association table
    IF (l_affected_targets.count != 0) THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('BULK:memberships_insert:Exit',MODULE_NAME);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END insert_memberships;

PROCEDURE insert_memberships
  (
    p_source_target_name IN mgmt_targets.target_name%TYPE,
    p_source_target_type IN mgmt_targets.target_type%TYPE,
    p_source_target_guid IN mgmt_targets.target_guid%TYPE,
    p_assoc_target_name  IN mgmt_targets.target_name%TYPE,
    p_assoc_target_type  IN mgmt_targets.target_type%TYPE,
    p_assoc_target_guid  IN mgmt_targets.target_guid%TYPE
  )
  IS
    l_assoc_def_name mgmt_target_assoc_defs.assoc_def_name%TYPE;
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('memberships_insert:Entry',MODULE_NAME);
    END IF;

l_assoc_def_name := mgmt_assoc.ASSOC_DEF_CONTAINS;

BEGIN
      create_target_assoc_int(l_affected_targets,
                              l_assoc_def_name,
                              p_source_target_name,
                              p_source_target_type,
                              p_assoc_target_name,
                              p_assoc_target_type);

--update flat association table
      IF (l_affected_targets.count != 0) THEN
        recreate_flat_target_list(l_affected_targets);
      END IF;
    EXCEPTION
      WHEN mgmt_global.assoc_already_exist THEN
        NULL;
    END;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('memberships_insert:Exit',MODULE_NAME);
    END IF;
  END insert_memberships;

PROCEDURE delete_memberships
  (
    p_source_target_name IN mgmt_targets.target_name%TYPE,
    p_source_target_type IN mgmt_targets.target_type%TYPE,
    p_source_target_guid IN mgmt_targets.target_guid%TYPE,
    p_assoc_target_name  IN mgmt_targets.target_name%TYPE,
    p_assoc_target_type  IN mgmt_targets.target_type%TYPE,
    p_assoc_target_guid  IN mgmt_targets.target_guid%TYPE
  )
  IS
    l_assoc_def_name mgmt_target_assoc_defs.assoc_def_name%TYPE;
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('memberships_delete:Entry',MODULE_NAME);
    END IF;
    -- delete the row from the table

l_assoc_def_name := mgmt_assoc.ASSOC_DEF_CONTAINS;

delete_target_assoc_int(l_affected_targets,
                            l_assoc_def_name,
                            p_source_target_name,
                            p_source_target_type,
                            p_assoc_target_name,
                            p_assoc_target_type);

--update flat association table
    IF (l_affected_targets.count != 0) THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('memberships_delete:Exit',MODULE_NAME);
    END IF;
  END delete_memberships;

--   When a target is reloacted from one agent to another one ,
  --   system defined association for that target are redefined.
  PROCEDURE handle_relocated_target
  (
    p_target_name   IN mgmt_targets.target_name%TYPE,
    p_target_type   IN mgmt_targets.target_type%TYPE,
    p_old_agent_url IN mgmt_targets.EMD_URL%TYPE,
    p_new_agent_url IN mgmt_targets.EMD_URL%TYPE
  )
  AS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
    l_old_agent_name mgmt_targets.target_name%TYPE ;
    l_new_agent_name mgmt_targets.target_name%TYPE ;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('hande_relocated_target:Entry',MODULE_NAME);
    END IF;

BEGIN
      SELECT target_name
        INTO l_old_agent_name
        FROM mgmt_targets
       WHERE emd_url = p_old_agent_url
         AND target_type = mgmt_global.G_AGENT_TARGET_TYPE;

SELECT target_name
        INTO l_new_agent_name
        FROM mgmt_targets
       WHERE emd_url = p_new_agent_url
         AND target_type = mgmt_global.G_AGENT_TARGET_TYPE;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('Sepcified agents does not exist ',MODULE_NAME);
        END IF;

RETURN; -- sepcified agents does not exist
    END;

delete_target_assoc_int(l_affected_targets,
                            mgmt_assoc.ASSOC_DEF_MONITORED_BY,
                            p_target_name,
                            p_target_type,
                            l_old_agent_name,
                            mgmt_global.G_AGENT_TARGET_TYPE);

create_target_assoc_int(l_affected_targets,
                            mgmt_assoc.ASSOC_DEF_MONITORED_BY,
                            p_target_name,
                            p_target_type,
                            l_new_agent_name,
                            mgmt_global.G_AGENT_TARGET_TYPE);
    --update flat association table
    IF (l_affected_targets.count != 0) THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('handle_relocated_target:Exit',MODULE_NAME);
    END IF;
  END handle_relocated_target;

-- Add a property to the specified association
  PROCEDURE add_assoc_property
  (
    p_assoc_def_name     IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_name IN mgmt_targets.target_name%TYPE,
    p_source_target_type IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_assoc_target_name  IN mgmt_targets.target_name%TYPE,
    p_assoc_target_type  IN mgmt_target_assoc_defs.assoc_target_type%TYPE,
    p_scope_target_name  IN mgmt_targets.target_name%TYPE,
    p_scope_target_type  IN mgmt_target_assoc_defs.scope_target_type%TYPE,
    p_property_name      IN mgmt_target_assoc_prop_defs.property_name%TYPE,
    p_property_value     IN mgmt_target_assoc_prop.property_value%TYPE
  )
  IS
    l_pending BOOLEAN := FALSE ;
    l_assoc_guid mgmt_target_assocs.assoc_guid%TYPE;
    l_source_target_guid mgmt_target_assocs.source_target_guid%TYPE;
    l_assoc_target_guid mgmt_target_assocs.assoc_target_guid%TYPE;
    l_scope_target_guid mgmt_target_assocs.scope_target_guid%TYPE;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('add_assoc_property:Entry',MODULE_NAME);
    END IF;

BEGIN
      l_assoc_guid := get_assoc_def_guid(p_assoc_def_name,p_source_target_type, p_scope_target_type);
    EXCEPTION
      WHEN mgmt_global.assoc_def_does_not_exist THEN
        raise_application_error(MGMT_GLOBAL.ASSOC_DEF_DOES_NOT_EXIST_ERR,
                                'The specified association def does not exist');
    END;

get_sure_target_guid(p_source_target_name,
                         p_source_target_type,
                         l_source_target_guid,
                         l_pending);

get_sure_target_guid(p_assoc_target_name,
                         p_assoc_target_type,
                         l_assoc_target_guid,
                         l_pending);

IF (p_scope_target_type = mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE) THEN
        l_scope_target_guid := mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID;
    ELSE
      get_sure_target_guid(p_scope_target_name,
                           p_scope_target_type,
                           l_scope_target_guid, 
                           l_pending);
    END IF;

IF( l_pending = FALSE) THEN
      BEGIN
        SELECT assoc_guid 
          INTO l_assoc_guid 
          FROM mgmt_target_assocs
         WHERE assoc_guid = l_assoc_guid
           AND source_target_guid = l_source_target_guid
           AND scope_target_guid = l_scope_target_guid
           AND assoc_target_guid = l_assoc_target_guid;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN 
          raise_application_error(mgmt_global.invalid_params_err,
                                  'Invalid parameters : Specified Association does not exist');
      END;
    ELSE
      BEGIN
        SELECT assoc_guid 
          INTO l_assoc_guid 
          FROM mgmt_target_pending_assocs
         WHERE assoc_guid = l_assoc_guid
           AND source_target_guid = l_source_target_guid
           AND scope_target_guid = l_scope_target_guid
           AND assoc_target_guid = l_assoc_target_guid;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN 
          raise_application_error(mgmt_global.invalid_params_err,
                                  'Invalid parameters : Specified Association does not exist');
      END;   
    END IF;

MERGE
     INTO mgmt_target_assoc_prop p
    USING (SELECT l_assoc_guid assoc_guid,
                  l_source_target_guid source_target_guid,
                  l_assoc_target_guid assoc_target_guid,
                  l_scope_target_guid scope_target_guid,
                  p_property_name property_name FROM DUAL
           ) v
       ON (p.assoc_guid = v.assoc_guid AND
           p.source_target_guid = v.source_target_guid AND
           p.assoc_target_guid = v.assoc_target_guid AND
           p.scope_target_guid = v.scope_target_guid AND
           p.property_name = v.property_name
          )
     WHEN MATCHED THEN
       UPDATE SET p.property_value = p_property_value
     WHEN NOT MATCHED THEN
       INSERT (assoc_guid,source_target_guid,assoc_target_guid,scope_target_guid,
               property_name,property_value)
      VALUES (l_assoc_guid,l_source_target_guid,l_assoc_target_guid,l_scope_target_guid,
              p_property_name,p_property_value);

-- DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows merged.');

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('add_assoc_property:Exit',MODULE_NAME);
    END IF;
  END add_assoc_property;

-- modify a property for the specified association
  PROCEDURE modify_assoc_property
  (
    p_assoc_def_name     IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_name IN mgmt_targets.target_name%TYPE,
    p_source_target_type IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_assoc_target_name  IN mgmt_targets.target_name%TYPE,
    p_assoc_target_type  IN mgmt_target_assoc_defs.assoc_target_type%TYPE,
    p_scope_target_name  IN mgmt_targets.target_name%TYPE,
    p_scope_target_type  IN mgmt_target_assoc_defs.scope_target_type%TYPE,
    p_property_name      IN mgmt_target_assoc_prop_defs.property_name%TYPE,
    p_property_value     IN mgmt_target_assoc_prop.property_value%TYPE
  )
  IS
    l_pending BOOLEAN := FALSE;
    l_assoc_guid mgmt_target_assocs.assoc_guid%TYPE;
    l_source_target_guid mgmt_target_assocs.source_target_guid%TYPE;
    l_assoc_target_guid mgmt_target_assocs.assoc_target_guid%TYPE;
    l_scope_target_guid mgmt_target_assocs.scope_target_guid%TYPE;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('modify_assoc_property:Entry',MODULE_NAME);
    END IF;

BEGIN
      l_assoc_guid := get_assoc_def_guid(p_assoc_def_name,p_source_target_type, p_scope_target_type);
    EXCEPTION
      WHEN mgmt_global.assoc_def_does_not_exist THEN
        raise_application_error(MGMT_GLOBAL.ASSOC_DEF_DOES_NOT_EXIST_ERR,
                                'The specified association def does not exist');
    END;

get_sure_target_guid(p_source_target_name,
                         p_source_target_type,
                         l_source_target_guid,
                         l_pending);
     
    get_sure_target_guid(p_assoc_target_name,
                         p_assoc_target_type,
                         l_assoc_target_guid,
                         l_pending);

IF (p_scope_target_type = mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE) THEN
        l_scope_target_guid := mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID;
    ELSE
      get_sure_target_guid(p_scope_target_name,
                           p_scope_target_type,
                           l_scope_target_guid,
                           l_pending);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('modify_assoc_property:Exit',MODULE_NAME);
    END IF;
  END modify_assoc_property;

PROCEDURE delete_assoc_property
  (
    p_assoc_def_name     IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_name IN mgmt_targets.target_name%TYPE,
    p_source_target_type IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_assoc_target_name  IN mgmt_targets.target_name%TYPE,
    p_assoc_target_type  IN mgmt_target_assoc_defs.assoc_target_type%TYPE,
    p_scope_target_name  IN mgmt_targets.target_name%TYPE,
    p_scope_target_type  IN mgmt_target_assoc_defs.scope_target_type%TYPE,
    p_property_name      IN mgmt_target_assoc_prop_defs.property_name%TYPE
  )
  IS
    l_assoc_guid mgmt_target_assocs.assoc_guid%TYPE;
    l_source_target_guid mgmt_target_assocs.source_target_guid%TYPE;
    l_assoc_target_guid mgmt_target_assocs.assoc_target_guid%TYPE;
    l_scope_target_guid mgmt_target_assocs.scope_target_guid%TYPE;
    l_pending BOOLEAN;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_assoc_property:Exit',MODULE_NAME);
    END IF;

BEGIN
      l_assoc_guid := get_assoc_def_guid(p_assoc_def_name,p_source_target_type, p_scope_target_type);
    EXCEPTION
      WHEN mgmt_global.assoc_def_does_not_exist THEN
        raise_application_error(MGMT_GLOBAL.ASSOC_DEF_DOES_NOT_EXIST_ERR,
                                'The specified association def does not exist');
    END;

get_sure_target_guid(p_source_target_name,
                         p_source_target_type,
                         l_source_target_guid,
                         l_pending);

get_sure_target_guid(p_assoc_target_name,
                         p_assoc_target_type,
                         l_assoc_target_guid,
                         l_pending);

IF (p_scope_target_type = mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE) THEN
        l_scope_target_guid := mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID;
    ELSE
        get_sure_target_guid(p_scope_target_name,
                             p_scope_target_type,
                             l_scope_target_guid,
                             l_pending);
    END IF;

DELETE
      FROM mgmt_target_assoc_prop
     WHERE assoc_guid = l_assoc_guid
       AND source_target_guid = l_source_target_guid
       AND assoc_target_guid = l_assoc_target_guid
       AND scope_target_guid = l_scope_target_guid
       AND property_name = p_property_name;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_assoc_property:Exit',MODULE_NAME);
    END IF;
  END delete_assoc_property;

PROCEDURE create_target_assoc_int
  (
    p_affected_targets     IN OUT MGMT_TARGET_ASSOC_LIST,
    p_assoc_guid           IN mgmt_target_assoc_defs.assoc_guid%TYPE,
    p_source_target_guid   IN mgmt_targets.target_guid%TYPE,
    p_assoc_target_guid    IN mgmt_targets.target_guid%TYPE,
    p_scope_target_guid    IN mgmt_targets.target_guid%TYPE
                                      DEFAULT mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID,
    p_is_editable          IN mgmt_target_assocs.is_editable%TYPE
                                      DEFAULT mgmt_assoc.ASSOC_NOT_EDITABLE,
    p_created_by           IN mgmt_target_assocs.created_by%TYPE
                                      DEFAULT mgmt_assoc.CREATED_BY_END_USER
  )
  IS
    l_assoc BOOLEAN := TRUE;
    l_assoc_guid mgmt_target_assocs.assoc_guid%TYPE;
    l_source_target_guid mgmt_target_assocs.source_target_guid%TYPE;
    l_assoc_target_guid  mgmt_target_assocs.assoc_target_guid%TYPE;
    l_scope_target_guid  mgmt_target_assocs.scope_target_guid%TYPE;
    l_source_target_type mgmt_target_assoc_defs.source_target_type%TYPE;
    l_assoc_target_type  mgmt_target_assoc_defs.assoc_target_type%TYPE;
    l_scope_target_type  mgmt_target_assoc_defs.scope_target_type%TYPE;
    l_source_target_type_spec mgmt_target_assoc_defs.source_target_type%TYPE;
    l_assoc_target_type_spec  mgmt_target_assoc_defs.assoc_target_type%TYPE;
    l_scope_target_type_spec  mgmt_target_assoc_defs.scope_target_type%TYPE;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assoc:Entry',MODULE_NAME);
    END IF;

BEGIN
      SELECT source_target_type, assoc_target_type, scope_target_type
        INTO l_source_target_type,l_assoc_target_type, l_scope_target_type
        FROM mgmt_target_assoc_defs
       WHERE assoc_guid = p_assoc_guid;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN ; -- do it for time being, will fix once agent team fixes it.
        raise_application_error(MGMT_GLOBAL.ASSOC_DEF_DOES_NOT_EXIST_ERR,
                                'The specified association def does not exist');

IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('The specified association def does not exist',MODULE_NAME);
        END IF;
    END;

BEGIN
      SELECT target_type
        INTO l_source_target_type_spec 
        FROM mgmt_targets 
       WHERE target_guid = p_source_target_guid ;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_assoc := FALSE;
    END;

BEGIN
      SELECT target_type
        INTO l_assoc_target_type_spec 
        FROM mgmt_targets 
       WHERE target_guid = p_assoc_target_guid; 
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_assoc := FALSE;
    END;

IF (p_scope_target_guid != mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID) THEN
      BEGIN
        SELECT target_type
          INTO l_scope_target_type_spec 
          FROM mgmt_targets 
         WHERE target_guid = p_scope_target_guid ;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          l_assoc := FALSE;
      END;
    ELSE
      l_scope_target_type_spec := mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE;
    END IF;

-- check whether target type matches with types mentioned in association def
    IF( l_source_target_type != l_source_target_type_spec AND
        l_source_target_type != mgmt_assoc.ANY_TARGET_TYPE)
    THEN
      raise_application_error(mgmt_global.invalid_params_err,
        'Invalid parameters : Invalid Source Target Type');

IF (emdw_log.p_is_error_set)THEN
        emdw_log.error('Invalid parameters : Invalid Source Target Type',MODULE_NAME);
      END IF;
    END IF;

IF ( l_assoc_target_type != l_assoc_target_type_spec AND
         l_assoc_target_type != mgmt_assoc.ANY_TARGET_TYPE)
    THEN
      raise_application_error(mgmt_global.invalid_params_err,
                              'Invalid parameters : Invalid Associated Target Type');

IF (emdw_log.p_is_error_set)THEN
        emdw_log.error('Invalid parameters : Invalid Associated Target Type',MODULE_NAME);
      END IF;
    END IF;

IF ( l_scope_target_type != l_scope_target_type_spec AND
         l_scope_target_type != mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE)
    THEN
      raise_application_error(mgmt_global.invalid_params_err,
                              'Invalid parameters : Invalid Scope Target Type');

IF (emdw_log.p_is_error_set)THEN
        emdw_log.error('Invalid parameters : Invalid Scope Target Type',MODULE_NAME);
      END IF;
    END IF;

IF (emdw_log.p_is_debug_set)THEN
      emdw_log.debug('All target types match with the target types'||
                     'specified in the association definition',MODULE_NAME);
    END IF;

IF( l_assoc = TRUE) THEN
      create_target_assoc_int(p_assoc_guid,
                              p_source_target_guid,
                              p_assoc_target_guid,
                              p_scope_target_guid,
                              p_is_editable,
                              p_created_by);

p_affected_targets.extend;
      p_affected_targets(p_affected_targets.count)
                             := MGMT_TARGET_ASSOC_REC.new(p_assoc_guid,
                                                          p_source_target_guid,
                                                          p_assoc_target_guid,
                                                          p_scope_target_guid,
                                                          em_assoc.CREATE_ASSOC_OPER);

ELSE -- its a pending association
      BEGIN
        INSERT
          INTO mgmt_target_pending_assocs
               (assoc_guid, source_target_guid, assoc_target_guid, scope_target_guid,
                is_editable, created_by)
        VALUES
               (p_assoc_guid, p_source_target_guid,p_assoc_target_guid,p_scope_target_guid,
                p_is_editable, p_created_by);

EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
        NULL; --if it already exists, thats ok 
      END;
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assoc_int:Exit',MODULE_NAME);
    END IF;
  END create_target_assoc_int;

PROCEDURE create_target_assoc
  (
    p_assoc_guid         IN mgmt_target_assocs.assoc_guid%TYPE,
    p_source_target_guid IN mgmt_target_assocs.source_target_guid%TYPE,
    p_assoc_target_guid  IN mgmt_target_assocs.assoc_target_guid%TYPE,
    p_scope_target_guid  IN mgmt_target_assocs.scope_target_guid%TYPE
                              DEFAULT mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID,
    p_is_editable        IN mgmt_target_assocs.is_editable%TYPE
                              DEFAULT mgmt_assoc.ASSOC_NOT_EDITABLE,
    p_created_by         IN mgmt_target_assocs.created_by%TYPE
                              DEFAULT mgmt_assoc.CREATED_BY_END_USER
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assoc:Entry',MODULE_NAME);
    END IF;

create_target_assoc_int(l_affected_targets,
                            p_assoc_guid,
                            p_source_target_guid,
                            p_assoc_target_guid,
                            p_scope_target_guid,
                            p_is_editable,
                            p_created_by);

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assoc:Exit',MODULE_NAME);
    END IF;
  END  create_target_assoc;

PROCEDURE delete_target_assoc
  (
    p_assoc_guid           IN mgmt_target_assocs.assoc_guid%TYPE,
    p_source_target_guid   IN mgmt_target_assocs.source_target_guid%TYPE,
    p_assoc_target_guid    IN mgmt_target_assocs.assoc_target_guid%TYPE,
    p_scope_target_guid    IN mgmt_target_assocs.scope_target_guid%TYPE
                                DEFAULT mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assoc:Entry',MODULE_NAME);
    END IF;
 
    delete_target_assoc_int(l_affected_targets,
                            p_assoc_guid,
                            p_source_target_guid,
                            p_assoc_target_guid,
                            p_scope_target_guid);

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;  
 
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('detele_target_assoc:Exit',MODULE_NAME);
    END IF;
  END delete_target_assoc;

-- Creates a list of associations of the specified association type.
  PROCEDURE create_target_assocs
  (
    p_assoc_def_name       IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_name   IN mgmt_targets.target_name%TYPE,
    p_source_target_type   IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_assoc_targets_list   IN SMP_EMD_NVPAIR_ARRAY,
    p_is_editable          IN mgmt_target_assocs.is_editable%TYPE
                                DEFAULT MGMT_ASSOC.ASSOC_NOT_EDITABLE,
    p_created_by           IN mgmt_target_assocs.created_by%TYPE
                                DEFAULT mgmt_assoc.CREATED_BY_END_USER,
    p_suppress_error       IN BOOLEAN DEFAULT FALSE 
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assocs BULK WITHOUT SCOPE: Entry',MODULE_NAME);
    END IF;

FOR i in 1..p_assoc_targets_list.count
    LOOP
      BEGIN
        create_target_assoc_int(l_affected_targets,
                                p_assoc_def_name,
                                p_source_target_name,
                                p_source_target_type,
                                p_assoc_targets_list(i).name,
                                p_assoc_targets_list(i).value,
                                mgmt_assoc.GLOBAL_SCOPE_TARGET_NAME,
                                mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE,
                                p_is_editable,
                                p_created_by);
      EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          IF p_suppress_error = TRUE THEN
            NULL;
          ELSE
            RAISE;
          END IF;
      END;
    END LOOP;

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assocs:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;
  END create_target_assocs;

-- Creates a list of associations of the specified association type.
  PROCEDURE create_target_assocs
  (
    p_assoc_guid           IN mgmt_target_assocs.assoc_guid%TYPE,
    p_source_target_guid   IN mgmt_target_assocs.source_target_guid%TYPE,
    p_assoc_targets_list   IN MGMT_GUID_ARRAY,
    p_is_editable          IN mgmt_target_assocs.is_editable%TYPE
                                DEFAULT MGMT_ASSOC.ASSOC_NOT_EDITABLE,
    p_created_by           IN mgmt_target_assocs.created_by%TYPE
                                DEFAULT mgmt_assoc.CREATED_BY_END_USER,
    p_suppress_error       IN BOOLEAN DEFAULT FALSE 
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assocs BULK WITHOUT SCOPE: Entry',MODULE_NAME);
    END IF;

FOR i in 1..p_assoc_targets_list.count
    LOOP
      BEGIN
        create_target_assoc_int(l_affected_targets,
                                p_assoc_guid,
                                p_source_target_guid,
                                p_assoc_targets_list(i).guid,
                                mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID,
                                p_is_editable,
                                p_created_by);
      EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          IF p_suppress_error = TRUE THEN
            NULL;
          ELSE
            RAISE;
          END IF;
      END;
    END LOOP;

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assocs:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;
  END create_target_assocs;
  --
  -- Creates a list of associations of the specified association type
  -- with in a given scope target.
  -- Example: When system association is created, this API will be used
  -- to instantiate all the scoped associations. 
  --
  PROCEDURE create_target_assocs
  (
    p_assoc_def_name       IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_scope_target_name    IN mgmt_targets.target_name%TYPE,
    p_scope_target_type    IN mgmt_target_assoc_defs.scope_target_type%TYPE,
    p_assoc_list           IN MGMT_NVPAIR_TUPLE_LIST,
    p_is_editable          IN mgmt_target_assocs.is_editable%TYPE
                                DEFAULT MGMT_ASSOC.ASSOC_NOT_EDITABLE,
    p_created_by           IN mgmt_target_assocs.created_by%TYPE
                                DEFAULT mgmt_assoc.CREATED_BY_END_USER,
    p_suppress_error       IN BOOLEAN DEFAULT FALSE 
  )
  IS
   l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
  BEGIN
   IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assocs:BULK WITH SCOPE: Entry',MODULE_NAME);
    END IF;

FOR i in 1..p_assoc_list.count
    LOOP
      BEGIN
        create_target_assoc_int(l_affected_targets,
                                p_assoc_def_name,
                                p_assoc_list(i).name1,
                                p_assoc_list(i).value1,
                                p_assoc_list(i).name2,
                                p_assoc_list(i).value2,
                                p_scope_target_name,
                                p_scope_target_type,
                                p_is_editable,
                                p_created_by);
      EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          IF p_suppress_error = TRUE THEN
            NULL;
          ELSE
            RAISE;
          END IF;
      END;
    END LOOP;

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assocs:BULK WITH SCOPE: Exit',MODULE_NAME);
    END IF;

END create_target_assocs;

PROCEDURE create_target_assocs
  (
    p_assoc_guid           IN mgmt_target_assocs.assoc_guid%TYPE,
    p_scope_target_guid    IN mgmt_target_assocs.scope_target_guid%TYPE,
    p_assoc_list           IN MGMT_GUID_PAIR_ARRAY,
    p_is_editable          IN mgmt_target_assocs.is_editable%TYPE
                                DEFAULT MGMT_ASSOC.ASSOC_NOT_EDITABLE,
    p_created_by           IN mgmt_target_assocs.created_by%TYPE
                                DEFAULT mgmt_assoc.CREATED_BY_END_USER,
    p_suppress_error       IN BOOLEAN DEFAULT FALSE 
  )
  IS
   l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
  BEGIN
   IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assocs:BULK WITH SCOPE: Entry',MODULE_NAME);
    END IF;

FOR i in 1..p_assoc_list.count
    LOOP
      BEGIN
        create_target_assoc_int(l_affected_targets,
                                p_assoc_guid,
                                p_assoc_list(i).guid1,
                                p_assoc_list(i).guid2,
                                p_scope_target_guid,
                                p_is_editable,
                                p_created_by);
      EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          IF p_suppress_error = TRUE THEN
            NULL;
          ELSE
            RAISE;
          END IF;
      END;
    END LOOP;

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('create_target_assocs:BULK WITH SCOPE: Exit',MODULE_NAME);
    END IF;

END create_target_assocs;

--
  -- Replaces the existing associations of type "p_assoc_def_name" with the
  -- the new list.  If the new list is null, it will remove all the existing assocs. 
  --
  PROCEDURE set_target_assocs
  (
    p_assoc_def_name       IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_name   IN mgmt_targets.target_name%TYPE,
    p_source_target_type   IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_assoc_targets_list   IN SMP_EMD_NVPAIR_ARRAY,
    p_is_editable          IN mgmt_target_assocs.is_editable%TYPE
                                DEFAULT MGMT_ASSOC.ASSOC_NOT_EDITABLE
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
    l_assoc_guid mgmt_target_assoc_defs.assoc_guid%TYPE ;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('set_target_assocs:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;

-- get assoc_def_guid
    BEGIN
      l_assoc_guid := get_assoc_def_guid(p_assoc_def_name,
                                         p_source_target_type);
    EXCEPTION
      WHEN mgmt_global.assoc_def_does_not_exist THEN
        raise_application_error(mgmt_global.ASSOC_DEF_DOES_NOT_EXIST_ERR,
                              'Association Def does not exist');
        IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('Association Def does not exist',MODULE_NAME);
        END IF;

END;

-- delete the pending assocs 
    DELETE 
      FROM mgmt_target_pending_assocs 
     WHERE assoc_guid = l_assoc_guid 
       AND source_target_guid = mgmt_target.generate_target_guid(p_source_target_name,
                                                                 p_source_target_type)
       AND scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID;
                                                                                      
    -- first create all the assocs
    FOR i in 1..p_assoc_targets_list.count
    LOOP
      BEGIN
        create_target_assoc_int(l_affected_targets,
                                p_assoc_def_name,
                                p_source_target_name,
                                p_source_target_type,
                                p_assoc_targets_list(i).name,
                                p_assoc_targets_list(i).value,
                                mgmt_assoc.GLOBAL_SCOPE_TARGET_NAME,
                                mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE,
                                p_is_editable);
      EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          NULL; --if assoc exist thats ok
      END;
    END LOOP;
    
    -- delete the remaining assocs 
    FOR rec IN( SELECT a.source_target_guid source_target_guid,
                       a.assoc_target_guid assoc_target_guid,
                       a.scope_target_guid scope_target_guid
                  FROM mgmt_target_assocs a,
                       mgmt_targets ct
                 WHERE a.assoc_guid = l_assoc_guid 
                   AND ct.target_name = p_source_target_name 
                   AND ct.target_type = p_source_target_type
                   AND a.source_target_guid = ct.target_guid
                   AND a.SCOPE_TARGET_GUID = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID 
                   AND (a.created_by = mgmt_assoc.CREATED_BY_AGENT OR 
                        a.created_by = mgmt_assoc.CREATED_BY_END_USER)
               MINUS
                 SELECT ct.target_guid source_target_guid,
                        mt.target_guid assoc_target_guid,
                        mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID scope_target_guid
                   FROM TABLE (CAST(p_assoc_targets_list AS SMP_EMD_NVPAIR_ARRAY)) b,
                        mgmt_targets mt, mgmt_targets ct
                  WHERE mt.target_name = b.name
                    AND mt.target_type = b.value
                    AND ct.target_name = p_source_target_name
                    AND ct.target_type = p_source_target_type
              ) 
    LOOP
      delete_target_assoc_int(l_assoc_guid,
                              rec.source_target_guid,
                              rec.assoc_target_guid,
                              rec.scope_target_guid);
      l_affected_targets.extend;
      l_affected_targets(l_affected_targets.count)
                                 := MGMT_TARGET_ASSOC_REC.new(l_assoc_guid,
                                                              rec.source_target_guid,
                                                              rec.assoc_target_guid,
                                                              rec.scope_target_guid,
                                                              em_assoc.DELETE_ASSOC_OPER);
    END LOOP;

-- delete the remaining pending assocs 
   
    IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('set_target_assocs:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;
  END set_target_assocs;

PROCEDURE set_target_assocs
   (
     p_assoc_guid           IN mgmt_target_assocs.assoc_guid%TYPE,
     p_source_target_guid   IN mgmt_target_assocs.source_target_guid%TYPE,
     p_assoc_targets_list   IN MGMT_GUID_ARRAY,
     p_is_editable          IN mgmt_target_assocs.is_editable%TYPE
                                 DEFAULT MGMT_ASSOC.ASSOC_NOT_EDITABLE
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
    l_count NUMBER(1) ;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('set_target_assocs:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;

-- get assoc_def_guid
    BEGIN
      SELECT count(*)
        INTO l_count
        FROM mgmt_target_assoc_defs
       WHERE assoc_guid = p_assoc_guid;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        raise_application_error(mgmt_global.ASSOC_DEF_DOES_NOT_EXIST_ERR,
                              'Association Def does not exist');
        IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('Association Def does not exist',MODULE_NAME);
        END IF;

END;

-- delete the pending assocs 
    DELETE 
      FROM mgmt_target_pending_assocs 
     WHERE assoc_guid = p_assoc_guid 
       AND source_target_guid = p_source_target_guid
       AND scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID;
                                                                                      
    -- first create all the assocs
    FOR i in 1..p_assoc_targets_list.count
    LOOP
      BEGIN
        create_target_assoc_int(l_affected_targets,
                                p_assoc_guid,
                                p_source_target_guid,
                                p_assoc_targets_list(i).guid,
                                mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID,
                                p_is_editable);
      EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          NULL; --if assoc exist thats ok
      END;
    END LOOP;
    
    -- delete the remaining assocs 
    FOR rec IN( SELECT a.source_target_guid source_target_guid,
                       a.assoc_target_guid assoc_target_guid,
                       a.scope_target_guid scope_target_guid
                  FROM mgmt_target_assocs a
                 WHERE a.assoc_guid = p_assoc_guid 
                   AND a.source_target_guid = p_source_target_guid
                   AND a.SCOPE_TARGET_GUID = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID 
                   AND (a.created_by = mgmt_assoc.CREATED_BY_AGENT OR 
                        a.created_by = mgmt_assoc.CREATED_BY_END_USER)
               MINUS
                 SELECT p_source_target_guid source_target_guid,
                        b.guid assoc_target_guid,
                        mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID scope_target_guid
                   FROM TABLE (CAST(p_assoc_targets_list AS MGMT_GUID_ARRAY)) b
              ) 
    LOOP
      delete_target_assoc_int(p_assoc_guid,
                              rec.source_target_guid,
                              rec.assoc_target_guid,
                              rec.scope_target_guid);
      l_affected_targets.extend;
      l_affected_targets(l_affected_targets.count)
                                 := MGMT_TARGET_ASSOC_REC.new(p_assoc_guid,
                                                              rec.source_target_guid,
                                                              rec.assoc_target_guid,
                                                              rec.scope_target_guid,
                                                              em_assoc.DELETE_ASSOC_OPER);
    END LOOP;

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('set_target_assocs:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;
  END set_target_assocs;

--
  -- Replaces the existing associations of type "p_assoc_def_name" with the
  -- the new list. If the new list is null, should remove all the existing assocs.
  --
  PROCEDURE set_target_assocs
  (
    p_assoc_def_name       IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_scope_target_name    IN mgmt_targets.target_name%TYPE,
    p_scope_target_type    IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_assoc_list           IN MGMT_NVPAIR_TUPLE_LIST,
    p_is_editable          IN mgmt_target_assocs.is_editable%TYPE
                                DEFAULT MGMT_ASSOC.ASSOC_NOT_EDITABLE
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
    l_assoc_guid mgmt_target_assoc_defs.assoc_guid%TYPE ;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('set_target_assocs:BULK WITH SCOPE: Exit',MODULE_NAME);
    END IF;

-- get assoc_def_guid
    BEGIN
      l_assoc_guid := get_assoc_def_guid(p_assoc_def_name,' ',p_scope_target_type);
    EXCEPTION
      WHEN mgmt_global.assoc_def_does_not_exist THEN
        raise_application_error(mgmt_global.ASSOC_DEF_DOES_NOT_EXIST_ERR,
                              'Association Def does not exist');
        IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('Association Def does not exist',MODULE_NAME);
        END IF;

END;

-- delete the pending assocs 
    DELETE 
      FROM mgmt_target_pending_assocs 
     WHERE assoc_guid = l_assoc_guid 
       AND scope_target_guid = mgmt_target.generate_target_guid(p_scope_target_name,
                                                                p_scope_target_type);

-- first create all the assocs
    FOR i in 1..p_assoc_list.count
    LOOP
      BEGIN
        create_target_assoc_int(l_affected_targets,
                                p_assoc_def_name,
                                p_assoc_list(i).name1,
                                p_assoc_list(i).value1,
                                p_assoc_list(i).name2,
                                p_assoc_list(i).value2,
                                p_scope_target_name,
                                p_scope_target_type,
                                p_is_editable);
      EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          NULL; --if assoc exist thats ok
      END;
    END LOOP;
    
    -- delete the remaining assocs 
    FOR rec IN( SELECT a.source_target_guid source_target_guid,
                       a.assoc_target_guid assoc_target_guid,
                       a.scope_target_guid scope_target_guid
                  FROM mgmt_target_assocs a,
                       mgmt_targets st
                 WHERE a.assoc_guid = l_assoc_guid 
                   AND st.target_name = p_scope_target_name 
                   AND st.target_type = p_scope_target_type
                   AND a.scope_target_guid = st.target_guid
                   AND (a.created_by = mgmt_assoc.CREATED_BY_AGENT OR 
                        a.created_by = mgmt_assoc.CREATED_BY_END_USER)
               MINUS
                 SELECT ct.target_guid source_target_guid,
                        mt.target_guid assoc_target_guid,
                        st.target_guid scope_target_guid
                   FROM TABLE (CAST(p_assoc_list AS MGMT_NVPAIR_TUPLE_LIST)) b,
                        mgmt_targets mt, mgmt_targets ct,mgmt_targets st
                  WHERE mt.target_name = b.name2
                    AND mt.target_type = b.value2
                    AND ct.target_name = b.name1
                    AND ct.target_type = b.value1
                    AND st.target_name = p_scope_target_name
                    AND st.target_type = p_scope_target_type
              ) 
    LOOP
      delete_target_assoc_int(l_assoc_guid,
                              rec.source_target_guid,
                              rec.assoc_target_guid,
                              rec.scope_target_guid);
      l_affected_targets.extend;
      l_affected_targets(l_affected_targets.count)
                                 := MGMT_TARGET_ASSOC_REC.new(l_assoc_guid,
                                                              rec.source_target_guid,
                                                              rec.assoc_target_guid,
                                                              rec.scope_target_guid,
                                                              em_assoc.DELETE_ASSOC_OPER);
    END LOOP;

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('set_target_assocs:BULK WITH SCOPE: Exit',MODULE_NAME);
    END IF;
  END set_target_assocs;

PROCEDURE set_target_assocs
  (
    p_assoc_guid           IN mgmt_target_assocs.assoc_guid%TYPE,
    p_scope_target_guid    IN mgmt_target_assocs.source_target_guid%TYPE,
    p_assoc_list           IN MGMT_GUID_PAIR_ARRAY,
    p_is_editable          IN mgmt_target_assocs.is_editable%TYPE
                                DEFAULT MGMT_ASSOC.ASSOC_NOT_EDITABLE
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
    l_assoc_guid mgmt_target_assoc_defs.assoc_guid%TYPE ;
    l_count NUMBER(1) ;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('set_target_assocs:BULK WITH SCOPE: Exit',MODULE_NAME);
    END IF;

-- get assoc_def_guid
    BEGIN
      SELECT count(*)
        INTO l_count
        FROM mgmt_target_assoc_defs 
       WHERE assoc_guid = p_assoc_guid;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        raise_application_error(mgmt_global.ASSOC_DEF_DOES_NOT_EXIST_ERR,
                              'Association Def does not exist');
        IF (emdw_log.p_is_debug_set)THEN
          emdw_log.debug('Association Def does not exist',MODULE_NAME);
        END IF;

END;

-- delete the pending assocs 
    DELETE 
      FROM mgmt_target_pending_assocs 
     WHERE assoc_guid = p_assoc_guid 
       AND scope_target_guid = p_scope_target_guid;

-- first create all the assocs
    FOR i in 1..p_assoc_list.count
    LOOP
      BEGIN
        create_target_assoc_int(l_affected_targets,
                                p_assoc_guid,
                                p_assoc_list(i).guid1,
                                p_assoc_list(i).guid2,
                                p_scope_target_guid,
                                p_is_editable);
      EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          NULL; --if assoc exist thats ok
      END;
    END LOOP;
    
    -- delete the remaining assocs 
    FOR rec IN( SELECT a.source_target_guid source_target_guid,
                       a.assoc_target_guid assoc_target_guid,
                       a.scope_target_guid scope_target_guid
                  FROM mgmt_target_assocs a
                 WHERE a.assoc_guid = p_assoc_guid 
                   AND a.scope_target_guid = p_scope_target_guid
                   AND (a.created_by = mgmt_assoc.CREATED_BY_AGENT OR 
                        a.created_by = mgmt_assoc.CREATED_BY_END_USER)
               MINUS
                 SELECT /*+CARDINALITY(b 10)*/ b.guid1 source_target_guid,
                        b.guid2 assoc_target_guid,
                        p_scope_target_guid scope_target_guid
                   FROM TABLE (CAST(p_assoc_list AS MGMT_GUID_PAIR_ARRAY)) b
              ) 
    LOOP
      delete_target_assoc_int(p_assoc_guid,
                              rec.source_target_guid,
                              rec.assoc_target_guid,
                              rec.scope_target_guid);
      l_affected_targets.extend;
      l_affected_targets(l_affected_targets.count)
                                 := MGMT_TARGET_ASSOC_REC.new(p_assoc_guid,
                                                              rec.source_target_guid,
                                                              rec.assoc_target_guid,
                                                              rec.scope_target_guid,
                                                              em_assoc.DELETE_ASSOC_OPER);
    END LOOP;

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('set_target_assocs:BULK WITH SCOPE: Exit',MODULE_NAME);
    END IF;
  END set_target_assocs;

-- Deletes all the associations of the specified assoc def 
  -- for the given source target. This also allow deleting
  -- assocs of all types, by taking NULL for assocdefname
  -- parameter  
  -- 
  PROCEDURE delete_target_assocs
  (
    p_assoc_def_name       IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_source_target_name   IN mgmt_targets.target_name%TYPE,
    p_source_target_type   IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_assoc_targets_list   IN SMP_EMD_NVPAIR_ARRAY DEFAULT NULL,
    p_suppress_error       IN BOOLEAN DEFAULT FALSE
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
    l_assoc_guid mgmt_target_assoc_defs.assoc_guid%TYPE ;
    l_assoc_count NUMBER(4) := 0;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assoc:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;

IF (p_assoc_targets_list IS NOT NULL) THEN
      IF (p_assoc_targets_list.count = 0 ) THEN
        return ; 
      ELSE
        l_assoc_count := p_assoc_targets_list.count ;
      END IF;
    END IF;

FOR rec IN ( SELECT a.assoc_guid assoc_guid,
                        a.source_target_guid source_target_guid,
                        a.assoc_target_guid assoc_target_guid,
                        a.scope_target_guid scope_target_guid
                   FROM mgmt_target_assocs a,
                        mgmt_target_assoc_defs d,
                        mgmt_targets ct
                  WHERE d.assoc_def_name like nvl(p_assoc_def_name,'%')    
                    AND d.scope_target_type = mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE
                    AND ct.target_name = p_source_target_name
                    AND ct.target_type = p_source_target_type
                    AND a.source_target_guid = ct.target_guid
                    AND a.scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID
                    AND (a.created_by = mgmt_assoc.CREATED_BY_AGENT OR 
                         a.created_by = mgmt_assoc.CREATED_BY_END_USER)
                    AND a.assoc_guid = d.assoc_guid
                    AND (l_assoc_count = 0 OR 
                         a.assoc_target_guid IN (SELECT t.target_guid
                                                   FROM TABLE (CAST(p_assoc_targets_list AS SMP_EMD_NVPAIR_ARRAY)) b,
                                                        mgmt_targets t
                                                  WHERE t.target_name = b.name
                                                    AND t.target_type = b.value 
                                                )
                        )
                )
     LOOP
      BEGIN
        delete_target_assoc_int(rec.assoc_guid,
                                rec.source_target_guid,
                                rec.assoc_target_guid,
                                rec.scope_target_guid);
        l_affected_targets.extend;
        l_affected_targets(l_affected_targets.count)
                                 := MGMT_TARGET_ASSOC_REC.new(rec.assoc_guid,
                                                              rec.source_target_guid,
                                                              rec.assoc_target_guid,
                                                              rec.scope_target_guid,
                                                              em_assoc.DELETE_ASSOC_OPER);     
      EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          IF p_suppress_error = TRUE THEN
            NULL;
          ELSE
            RAISE;
          END IF;
      END;
    END LOOP;

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

-- delete all pending assocs 
    DELETE 
      FROM mgmt_target_pending_assocs 
     WHERE assoc_guid IN ( SELECT assoc_guid 
                             FROM mgmt_target_assoc_defs
                            WHERE assoc_def_name like nvl(p_assoc_def_name,'%')
                              AND scope_target_type = mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE)
       AND source_target_guid IN (SELECT target_guid 
                                    FROM mgmt_targets 
                                   WHERE target_name = p_source_target_name
                                     AND target_type = p_source_target_type)
       AND (l_assoc_count = 0  OR
            assoc_target_guid IN (SELECT mgmt_target.generate_target_guid(b.name,b.value)
                                    FROM TABLE (CAST(p_assoc_targets_list AS SMP_EMD_NVPAIR_ARRAY)) b)
           )
       AND scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID;
                               
    
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assocs:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;
  END delete_target_assocs;

PROCEDURE delete_target_assocs
  (
    p_assoc_guid         IN mgmt_target_assocs.assoc_guid%TYPE,
    p_source_target_guid IN mgmt_target_assocs.source_target_guid%TYPE,
    p_suppress_error     IN BOOLEAN DEFAULT FALSE
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
    l_assoc_guid mgmt_target_assoc_defs.assoc_guid%TYPE ;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assoc:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;

FOR rec IN ( SELECT a.assoc_guid assoc_guid,
                        a.source_target_guid source_target_guid,
                        a.assoc_target_guid assoc_target_guid,
                        a.scope_target_guid scope_target_guid
                   FROM mgmt_target_assocs a
                  WHERE a.assoc_guid like nvl(p_assoc_guid,a.assoc_guid)    
                    AND a.source_target_guid = p_source_target_guid
                    AND a.scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID
                    AND (a.created_by = mgmt_assoc.CREATED_BY_AGENT OR 
                         a.created_by = mgmt_assoc.CREATED_BY_END_USER)
                )
    LOOP
      BEGIN
        delete_target_assoc_int(rec.assoc_guid,
                                rec.source_target_guid,
                                rec.assoc_target_guid,
                                rec.scope_target_guid);
        l_affected_targets.extend;
        l_affected_targets(l_affected_targets.count)
                                 := MGMT_TARGET_ASSOC_REC.new(rec.assoc_guid,
                                                              rec.source_target_guid,
                                                              rec.assoc_target_guid,
                                                              rec.scope_target_guid,
                                                              em_assoc.DELETE_ASSOC_OPER);     
      EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          IF p_suppress_error = TRUE THEN
            NULL;
          ELSE
            RAISE;
          END IF;
      END;
    END LOOP;

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

-- delete all pending assocs 
    DELETE 
      FROM mgmt_target_pending_assocs 
      WHERE assoc_guid like nvl(p_assoc_guid,assoc_guid)
       AND source_target_guid = p_source_target_guid
       AND scope_target_guid = mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID;
    
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assocs:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;
  END delete_target_assocs;

-- 
  -- Deletes all the associations with the specified scope target type.
  -- his also allow deleting assocs of all types, by taking NULL for assocdefname 
  -- parameter  
  PROCEDURE delete_assocs_in_scope
  (
    p_assoc_def_name      IN mgmt_target_assoc_defs.assoc_def_name%TYPE,
    p_scope_target_name   IN mgmt_targets.target_name%TYPE,
    p_scope_target_type   IN mgmt_target_assoc_defs.source_target_type%TYPE,
    p_suppress_error      IN BOOLEAN DEFAULT FALSE
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
    l_assoc_guid mgmt_target_assoc_defs.assoc_guid%TYPE ;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assoc:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;

END;

FOR rec IN ( SELECT a.assoc_guid assoc_guid,
                        a.source_target_guid source_target_guid,
                        a.assoc_target_guid assoc_target_guid,
                        a.scope_target_guid scope_target_guid
                   FROM mgmt_target_assocs a,
                        mgmt_target_assoc_defs d,
                        mgmt_targets st 
                  WHERE d.assoc_def_name like nvl(p_assoc_def_name,'%')    
                    AND d.scope_target_type = p_scope_target_type
                    AND st.target_name = p_scope_target_name
                    AND st.target_type = p_scope_target_type
                    AND (a.created_by = mgmt_assoc.CREATED_BY_AGENT OR 
                         a.created_by = mgmt_assoc.CREATED_BY_END_USER)
                    AND a.scope_target_guid = st.target_guid
                    AND a.assoc_guid = d.assoc_guid
                )
    LOOP
      BEGIN
        delete_target_assoc_int(rec.assoc_guid,
                                rec.source_target_guid,
                                rec.assoc_target_guid,
                                rec.scope_target_guid);
        l_affected_targets.extend;
        l_affected_targets(l_affected_targets.count)
                                 := MGMT_TARGET_ASSOC_REC.new(rec.assoc_guid,
                                                              rec.source_target_guid,
                                                              rec.assoc_target_guid,
                                                              rec.scope_target_guid,
                                                              em_assoc.DELETE_ASSOC_OPER);      
      EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          IF p_suppress_error = TRUE THEN
            NULL;
          ELSE
            RAISE;
          END IF;
      END;
    END LOOP;

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

-- delete all pending assocs 
    DELETE 
      FROM mgmt_target_pending_assocs 
     WHERE assoc_guid IN ( SELECT assoc_guid 
                             FROM mgmt_target_assoc_defs
                            WHERE assoc_def_name like nvl(p_assoc_def_name,'%')
                              AND scope_target_type = p_scope_target_type)
       AND scope_target_guid IN (SELECT target_guid 
                                   FROM mgmt_targets 
                                  WHERE target_name = p_scope_target_name
                                    AND target_type = p_scope_target_type);
                               
    
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assocs:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;
  END delete_assocs_in_scope;

PROCEDURE delete_assocs_in_scope
  (
    p_assoc_guid        IN mgmt_target_assocs.assoc_guid%TYPE,
    p_scope_target_guid IN mgmt_target_assocs.scope_target_guid%TYPE,
    p_suppress_error    IN BOOLEAN DEFAULT FALSE
  )
  IS
    l_affected_targets MGMT_TARGET_ASSOC_LIST := MGMT_TARGET_ASSOC_LIST();
    l_assoc_guid mgmt_target_assoc_defs.assoc_guid%TYPE ;
    l_count NUMBER(1);
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assoc:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;

END;

FOR rec IN ( SELECT a.assoc_guid assoc_guid,
                        a.source_target_guid source_target_guid,
                        a.assoc_target_guid assoc_target_guid,
                        a.scope_target_guid scope_target_guid
                   FROM mgmt_target_assocs a
                  WHERE a.assoc_guid like nvl(p_assoc_guid,a.assoc_guid)    
                    AND (a.created_by = mgmt_assoc.CREATED_BY_AGENT OR 
                         a.created_by = mgmt_assoc.CREATED_BY_END_USER)
                    AND a.scope_target_guid = p_scope_target_guid
                )
    LOOP
      BEGIN
        delete_target_assoc_int(rec.assoc_guid,
                                rec.source_target_guid,
                                rec.assoc_target_guid,
                                rec.scope_target_guid);
        l_affected_targets.extend;
        l_affected_targets(l_affected_targets.count)
                                 := MGMT_TARGET_ASSOC_REC.new(rec.assoc_guid,
                                                              rec.source_target_guid,
                                                              rec.assoc_target_guid,
                                                              rec.scope_target_guid,
                                                              em_assoc.DELETE_ASSOC_OPER);      
      EXCEPTION
        WHEN mgmt_global.assoc_already_exist THEN
          IF p_suppress_error = TRUE THEN
            NULL;
          ELSE
            RAISE;
          END IF;
      END;
    END LOOP;

IF (l_affected_targets.count != 0)THEN
      recreate_flat_target_list(l_affected_targets);
    END IF;

-- delete all pending assocs 
    DELETE 
      FROM mgmt_target_pending_assocs 
     WHERE assoc_guid like nvl(p_assoc_guid,assoc_guid)
       AND scope_target_guid = p_scope_target_guid;
                               
    
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('delete_target_assocs:BULK WITHOUT SCOPE: Exit',MODULE_NAME);
    END IF;
  END delete_assocs_in_scope;

PROCEDURE add_member_from_loader
  (
     p_composite_target_name  IN mgmt_targets.target_name%TYPE ,
     p_composite_target_type  IN mgmt_targets.target_type%TYPE ,
     p_composite_target_guid  IN mgmt_targets.target_guid%TYPE DEFAULT NULL,
     p_member_target_name     IN mgmt_targets.target_name%TYPE ,
     p_member_target_type     IN mgmt_targets.target_type%TYPE ,
     p_member_target_guid     IN mgmt_targets.target_guid%TYPE DEFAULT NULL,
     p_association            IN varchar2 DEFAULT NULL
  )
  IS
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('add_member_from_loader:Entry',MODULE_NAME);
    END IF;

BEGIN
      create_target_assoc_int(g_loader_assoc_added,
                              mgmt_assoc.ASSOC_DEF_CONTAINS,
                              p_composite_target_name,
                              p_composite_target_type,
                              p_member_target_name,
                              p_member_target_type,
                              mgmt_assoc.GLOBAL_SCOPE_TARGET_NAME,
                              mgmt_assoc.GLOBAL_SCOPE_TARGET_TYPE,
                              mgmt_assoc.ASSOC_NOT_EDITABLE,
                              mgmt_assoc.CREATED_BY_AGENT);

EXCEPTION
      WHEN mgmt_global.assoc_already_exist THEN
        NULL ; --its ok if assoc already exist
    END;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('add_member_from_loader:Exit',MODULE_NAME);
    END IF;
  END add_member_from_loader;

PROCEDURE add_assoc_from_loader
  (
     p_assoc_guid         IN mgmt_target_assocs.assoc_guid%TYPE,
     p_source_target_guid IN mgmt_target_assocs.source_target_guid%TYPE,
     p_assoc_target_guid  IN mgmt_target_assocs.assoc_target_guid%TYPE,
     p_scope_target_guid  IN mgmt_target_assocs.scope_target_guid%TYPE DEFAULT mgmt_assoc.GLOBAL_SCOPE_TARGET_GUID,
     p_is_editable        IN mgmt_target_assocs.is_editable%TYPE DEFAULT mgmt_assoc.ASSOC_NOT_EDITABLE
  )
  IS
    l_assoc_guid mgmt_target_assocs.assoc_guid%TYPE;
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('add_assoc_from_loader:Entry',MODULE_NAME);
    END IF;

BEGIN
     -- we need this fix in case agent still send "member" associations
     -- Expected associations : 'contains'

IF (p_assoc_guid = generate_assoc_def_guid('member', 
                                                MGMT_ASSOC.ANY_TARGET_TYPE,
                                                MGMT_ASSOC.GLOBAL_SCOPE_TARGET_TYPE)
        ) 
     THEN
       l_assoc_guid := MGMT_ASSOC.g_contains_guid;
     ELSE
       l_assoc_guid := p_assoc_guid;
     END IF;

-- We shouldn't use any apis based just on guids as we loose information
      -- about targettype in these apis. It makes it impossible to check whether 
      -- association conforms to association def or not.
      -- In future, agent should also send target_name and target_type for all targets.
      -- otherwise we have to assume that agent side associations are conforming to 
      -- the association defs.
      create_target_assoc_int(g_loader_assoc_added,
                              l_assoc_guid,
                              p_source_target_guid,
                              p_assoc_target_guid,
                              p_scope_target_guid,
                              p_is_editable,
                              mgmt_assoc.CREATED_BY_AGENT);

EXCEPTION
      WHEN mgmt_global.assoc_already_exist THEN
        NULL ; --its ok if assoc already exist
    END;

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('add_assoc_from_loader:Exit',MODULE_NAME);
    END IF;
  END add_assoc_from_loader;

-- This procdere will be at the beginning of rowset from loader
  PROCEDURE start_assoc_rowset
  IS
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('start_assoc_rowset:Entry',MODULE_NAME);
    END IF;

g_loader_assoc_added := MGMT_TARGET_ASSOC_LIST();

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('start_assoc_rowset:Exit',MODULE_NAME);
    END IF;
  END start_assoc_rowset;

-- This procdere will be at the end of rowset from loader
  PROCEDURE end_assoc_rowset
  IS
  BEGIN
    IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('end_assoc_rowset:Entry',MODULE_NAME);
    END IF;

--update flat association table
    IF (g_loader_assoc_added.count != 0) THEN
      recreate_flat_target_list(g_loader_assoc_added);
    END IF;

g_loader_assoc_added := MGMT_TARGET_ASSOC_LIST();

IF (emdw_log.p_is_info_set)THEN
      emdw_log.info('end_assoc_rowset:Exit',MODULE_NAME);
    END IF;
  END end_assoc_rowset;

PROCEDURE  update_ref_count_in_flat_list
  (
    p_assocs IN  MGMT_TARGET_ASSOC_LIST
  )
  IS
  BEGIN

IF (emdw_log.p_is_info_set) THEN
      EMDW_LOG.info('update_ref_count_in_flat_list:Enter',MODULE_NAME); 
      FOR i in 1..p_assocs.count
      LOOP
        EMDW_LOG.info('op '||p_assocs(i).operation,MODULE_NAME); 
        EMDW_LOG.info('source '||p_assocs(i).source_target_guid,MODULE_NAME); 
        EMDW_LOG.info('assoc '||p_assocs(i).assoc_target_guid,MODULE_NAME); 
      END LOOP;
    END IF;

FOR rec IN (SELECT source_target_guid, assoc_target_guid, sum(nvl(ref_count,0)) ref_count, 
                       sum(membership_count) membership_count, sum(prop_count) prop_count
                  FROM (SELECT a.source_target_guid source_target_guid, 
                               a.assoc_target_guid assoc_target_guid , 
                               a.operation ref_count,
                               DECODE(a.assoc_guid,MGMT_ASSOC.g_contains_guid,1,0)*a.operation membership_count,
                               DECODE(a.prop_view_priv,MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION,1,0) *a.operation  prop_count
                          FROM TABLE (CAST(p_assocs AS MGMT_TARGET_ASSOC_LIST)) a
                         WHERE a.source_target_guid != a.assoc_target_guid
                           AND (a.prop_view_priv = MGMT_ASSOC.ONE_LEVEL_VIEW_PROPAGATION OR
                                a.prop_view_priv = MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION )
                        UNION ALL
                        SELECT b.source_target_guid source_target_guid,
                               a.assoc_target_guid assoc_target_guid,
                               b.operation*a.prop_count ref_count,
                               CASE
                                 WHEN a.is_membership = 1 AND (b.assoc_guid = MGMT_ASSOC.g_contains_guid)
                                   THEN a.membership_count*b.operation
                                     ELSE 0
                               END membership_count,
                               b.operation*a.prop_count prop_count
                          FROM mgmt_flat_target_assoc a,
                               TABLE (CAST(p_assocs AS MGMT_TARGET_ASSOC_LIST)) b 
                         WHERE a.source_target_guid = b.assoc_target_guid
                           AND b.prop_view_priv = MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION 
                            -- remove cyclical associations
                           AND a.assoc_target_guid != b.source_target_guid
                           AND a.prop_count > 0
                      ) 
                 GROUP BY source_target_guid, assoc_target_guid
               ) 
    LOOP

FOR rec2 IN ( SELECT source_target_guid , prop_count, membership_count
                      FROM mgmt_flat_target_assoc 
                     WHERE assoc_target_guid = rec.source_target_guid
                       AND prop_count > 0
                  )
      LOOP                                  
        MERGE 
         INTO mgmt_flat_target_assoc a
        USING (SELECT rec2.source_target_guid source_target_guid,
                      rec.assoc_target_guid assoc_target_guid
                 FROM DUAL
              )
           ON (a.assoc_target_guid = rec.assoc_target_guid AND
               a.source_target_guid = rec2.source_target_guid)
         WHEN MATCHED THEN
           UPDATE
              SET a.ref_count = a.ref_count + rec2.prop_count*rec.prop_count,
                  a.membership_count = a.membership_count + rec.membership_count*rec2.membership_count,
                  a.is_membership = decode((a.membership_count + rec.membership_count*rec2.membership_count),0,0,1),
                  a.prop_count = a.prop_count + rec2.prop_count*rec.prop_count
         WHEN NOT MATCHED THEN
           INSERT (source_target_guid, assoc_target_guid, is_membership, 
                   ref_count, membership_count, prop_count)
           VALUES (rec2.source_target_guid, rec.assoc_target_guid, decode(rec.membership_count*rec2.membership_count,0,0,1),
                   rec2.prop_count*rec.prop_count, rec.membership_count*rec2.membership_count, rec2.prop_count*rec.prop_count );

END LOOP;
    
      MERGE 
       INTO mgmt_flat_target_assoc a
      USING (SELECT rec.source_target_guid source_target_guid, 
                    rec.assoc_target_guid  assoc_target_guid
               FROM DUAL
            ) c
         ON (a.assoc_target_guid = c.assoc_target_guid AND
             a.source_target_guid = c.source_target_guid)
       WHEN MATCHED THEN
         UPDATE
            SET a.ref_count = a.ref_count + rec.ref_count,
                a.membership_count = a.membership_count + rec.membership_count,
                a.is_membership = decode((a.membership_count + rec.membership_count),0,0,1),
                a.prop_count = a.prop_count + rec.prop_count
       WHEN NOT MATCHED THEN
         INSERT (source_target_guid, assoc_target_guid, is_membership, 
                 ref_count, membership_count, prop_count)
         VALUES (c.source_target_guid, c.assoc_target_guid, decode(rec.membership_count,0,0,1),
                 rec.ref_count, rec.membership_count, rec.prop_count);
    END LOOP;
 
    IF (emdw_log.p_is_info_set) THEN
      EMDW_LOG.info(' update_ref_count_in_flat_list:Exit',MODULE_NAME); 
    END IF;

END  update_ref_count_in_flat_list;

-- To recreate all entry in mgmt_flat_target_assoc for given target
  -- To be used during upgrade of repository or if required to recreate all entry in flat table
  PROCEDURE recreate_flat_target_list 
  ( 
    p_target_guid mgmt_targets.target_guid%TYPE 
  ) 
  IS
  BEGIN

IF (emdw_log.p_is_info_set) THEN
      EMDW_LOG.info(' recreate_flat_assoc_list:Entry p_target_guid = '||p_target_guid , MODULE_NAME); 
    END IF;

-- NOT NULL checks
    EM_CHECK.check_not_null(p_target_guid, 'p_traget_guid');

linked_target_list := mgmt_assoc_target_guid_list();
    targetgraph_up(p_target_guid);

-- update target assoc count
    --update_assoc_count(p_target_guid);

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

END recreate_flat_target_list;

-- Internal function
  -- When a target is deleted then we are checking whether given target has only HOSTED BY association or not.
  -- If YES the return value is 1 else 0.

FUNCTION chk_agent_singular_target
  (
    p_assocs IN  MGMT_TARGET_ASSOC_LIST,
    p_target_guid IN mgmt_targets.target_guid%TYPE
  ) RETURN NUMBER
  IS
    l_cnt NUMBER := 0;
    l_cnt1 NUMBER := 0;
  BEGIN

IF (emdw_log.p_is_info_set) THEN
      EMDW_LOG.info(' chk_agent_singular_target:Entry',MODULE_NAME); 
    END IF;

-- chk the no of propagable assoc given target has
    SELECT COUNT(*)
      INTO l_cnt
      FROM TABLE(CAST(p_assocs as MGMT_TARGET_ASSOC_LIST)) b
     WHERE b.source_target_guid = p_target_guid
       AND EXISTS (SELECT 1
                     FROM mgmt_target_assoc_defs d
                    WHERE (d.prop_view_priv = MGMT_ASSOC.ONE_LEVEL_VIEW_PROPAGATION OR
                           d.prop_view_priv = MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION
                          )
                      AND b.assoc_guid = d.assoc_guid
                  ) ;

IF l_cnt = 1 THEN
      -- If no of propagable assoc is only ONE then check whether its a HOSTED BY with scope_target_guid as NULL
      SELECT COUNT(*)
        INTO l_cnt1
        FROM TABLE(CAST(p_assocs as MGMT_TARGET_ASSOC_LIST)) a
       WHERE a.source_target_guid = p_target_guid
         AND a.assoc_guid = EM_ASSOC.get_assoc_def_guid(MGMT_ASSOC.ASSOC_TYPE_HOSTED_BY, MGMT_ASSOC.ANY_TARGET_TYPE)
         AND a.scope_target_guid = MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID;

-- If YES the set the return flag
      IF l_cnt1 = 1 THEN

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

RETURN 1;

END IF;

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

RETURN 0;

END chk_agent_singular_target;

-- Internal procedure
  -- Procedure to update ref_count in mgmt_flat_target_assoc when a target is deleted and it has only HOSTED BY association.
  -- For target which has only HOSTED BY association this procedure will do the same task as update_ref_count_in_flat_list would do

PROCEDURE upd_ref_cnt_singular
  (
    p_assocs IN  MGMT_TARGET_ASSOC_LIST,
    p_target_guid IN mgmt_targets.target_guid%TYPE
  )
  IS
    l_host_guid mgmt_targets.target_guid%TYPE;
  BEGIN

IF (emdw_log.p_is_info_set) THEN
      EMDW_LOG.info('upd_ref_cnt_singular:Entry',MODULE_NAME); 
    END IF;

-- If given target is acting as source only for hosted by association
    -- reduce the ref_count and prop_count from all flat association of its parents and host by count, which parent and target has.

-- Get host guid from hosted by association where target is source
    SELECT assoc_target_guid
      INTO l_host_guid
      FROM TABLE(CAST(p_assocs as MGMT_TARGET_ASSOC_LIST))
     WHERE source_target_guid = p_target_guid
       AND assoc_guid = EM_ASSOC.get_assoc_def_guid(mgmt_assoc.ASSOC_TYPE_HOSTED_BY, mgmt_assoc.ANY_TARGET_TYPE)
       AND scope_target_guid = MGMT_ASSOC.GLOBAL_SCOPE_TARGET_GUID ;

UPDATE mgmt_flat_target_assoc a
       SET (ref_count, prop_count) = (SELECT a.ref_count - b.prop_count, a.prop_count - b.prop_count
                                        FROM mgmt_flat_target_assoc b
                                       WHERE b.assoc_target_guid = p_target_guid
                                         AND b.source_target_guid = a.source_target_guid
                                     )
     WHERE a.assoc_target_guid = l_host_guid
       AND EXISTS (SELECT 1 
                     FROM mgmt_flat_target_assoc c 
                    WHERE c.source_target_guid = a.source_target_guid 
                      AND c.assoc_target_guid = p_target_guid) ;

DELETE
      FROM mgmt_flat_target_assoc
     WHERE source_target_guid = p_target_guid
        OR assoc_target_guid = p_target_guid ;

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

END upd_ref_cnt_singular;

--recreate flat assoc entry
  PROCEDURE recreate_full_flat_assoc
  IS
    l_target_guid mgmt_targets.target_guid%TYPE;
    l_counter INTEGER :=0;
  BEGIN

DELETE
      FROM mgmt_target_assoc_status ;

INSERT
    INTO mgmt_target_assoc_status
      (target_guid, assoc_count)
    SELECT target_guid , -1
      FROM mgmt_targets ;

LOOP
      BEGIN
        SELECT target_guid
          INTO l_target_guid
          FROM mgmt_target_assoc_status
         WHERE assoc_count = -1
           AND ROWNUM = 1 ;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        EXIT;
      END;
  
      EM_ASSOC.recreate_flat_target_list(p_target_guid => l_target_guid );
      COMMIT;
    END LOOP;

-- Delete mgmt_target_temp_list after populating flat assoc data
    -- for all targets to avoid traversal of same target
    LOOP
      DELETE
        FROM mgmt_target_temp_list
       WHERE ROWNUM < mgmt_global.MAX_COMMIT;

l_counter := SQL%ROWCOUNT;

COMMIT;
       IF l_counter < mgmt_global.MAX_COMMIT THEN
         EXIT;
       END IF;
    END LOOP;

END recreate_full_flat_assoc;

END em_assoc;
/
show err

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de