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

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

Rem drv: <create type="pkgbodys" pos="gensvc/gensvc_pkgbodys.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/system/system_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2009/02/26 20:39:36 jsadras Exp $
Rem
Rem system_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      system_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jsadras     11/19/08 - fix perf issues
Rem    gan         12/02/05 - Backport gan_bug-4669119 from main 
Rem    neearora    08/01/05 - Bug 4525467. modified create_system to accept
Rem                           type_meta_ver
Rem    gan         07/25/05 - bug 4503623 
Rem    gan         07/21/05 - check depends-on property 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gan         03/22/05 - check containment loop 
Rem    ramalhot    03/11/05 - create/modify system now uses add/modify 
Rem                           aggregate target 
Rem    neearora    03/01/05 - added owner input parameter in modify_system and 
Rem                           create_system procedure 
Rem    neearora    02/23/05 - Added new procedure upsert_system 
Rem    gan         02/04/05 - is_aggregate_service
Rem    ramalhot    02/02/05 - changed signature for system_pre_delete_callback 
Rem    ramalhot    01/17/05 - assoc_def_member --> assoc_def_contains 
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    gan         12/02/04 - handle member assoc 
Rem    gan         12/01/04 - remove member assoc also 
Rem    gan         12/01/04 - exclude aggregate service 
Rem    gan         10/14/04 - add pre target deletion cb 
Rem    gan         10/13/04 - code refactoring 
Rem    gan         10/06/04 - move some function tto util 
Rem    gan         10/05/04 - gan_system_api_1
Rem    gan         10/01/04 - Created
Rem

CREATE or REPLACE PACKAGE BODY EM_SYSTEM AS

P_IS_BYPASS_CALLBACK BOOLEAN := FALSE;

PROCEDURE cleanup_dep_detail(
                 p_impacted_services OUT MGMT_GUID_ARRAY)
AS
l_services MGMT_GUID_ARRAY;
l_lock_targets MGMT_TARGET_GUID_ARRAY;
l_dep_targets mgmt_target_guid_array;
l_services_ret MGMT_GUID_ARRAY;
l_service_name mgmt_targets.target_name%TYPE;
l_dep_target_name mgmt_targets.target_name%TYPE;

l_assoc_records mgmt_member_assoc_list;
l_assoc_guids mgmt_guid_array;
l_assoc_def mgmt_target_assoc_defs.assoc_def_name%TYPE;
BEGIN
    IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('cleanup_dep_detail:Enter', G_MODULE_NAME);
    END IF;
   em_rep_metric.cleanup_member_assoc_dep(l_services);
-- clean up dependes on/member assocs if a target is not a member of the 
-- system anymore
   SELECT mgmt_member_assoc_rec.new(source_tgt.target_name,
                            source_tgt.target_type, source_tgt.target_guid,
                            assoc_tgt.target_name, assoc_tgt.target_type,
                            assoc_tgt.target_guid),
          mgmt_guid_obj(dep_assoc.assoc_guid, NULL)
    BULK COLLECT INTO l_assoc_records, l_assoc_guids
   FROM mgmt_target_assocs dep_assoc, mgmt_target_assocs runs_assoc,
        mgmt_targets source_tgt, mgmt_targets assoc_tgt
     WHERE dep_assoc.source_target_guid = source_tgt.target_guid
       AND dep_assoc.assoc_target_guid = assoc_tgt.target_guid
       AND dep_assoc.source_target_guid = runs_assoc.source_target_guid
       AND ((dep_assoc.assoc_guid = mgmt_assoc.g_depends_on_guid
             AND EXISTS (SELECT 1 
                           FROM mgmt_target_assoc_prop
                          WHERE assoc_guid = mgmt_assoc.g_depends_on_guid
                            AND source_target_guid = dep_assoc.source_target_guid
                            AND assoc_target_guid = dep_assoc.assoc_target_guid
                            AND property_name = mgmt_service.G_SYSTEM_DEPENDS_ON_PROP
                            AND property_value = '1'
                        )
             )
            OR dep_assoc.assoc_guid = mgmt_assoc.g_contains_guid)
       AND runs_assoc.assoc_guid = mgmt_assoc.g_runs_on_guid 
       AND dep_assoc.assoc_target_guid NOT IN (
               SELECT assoc_target_guid
               FROM mgmt_flat_target_assoc
               WHERE is_membership = 1
                 AND source_target_guid = runs_assoc.assoc_target_guid
          )
      AND NOT EXISTS
          (SELECT 1 
             FROM mgmt_type_properties p
            WHERE p.target_type = source_tgt.target_type
              AND p.property_name = mgmt_global.G_IS_AGGREGATE_SERVICE_PROP
              AND p.property_value = '1'
         );

IF l_assoc_records IS NOT NULL  THEN
     FOR indx IN 1..l_assoc_records.COUNT 
     LOOP
       IF l_assoc_guids(indx).guid = mgmt_assoc.g_depends_on_guid THEN
          l_assoc_def := mgmt_assoc.assoc_def_depends_on;
       ELSE
          l_assoc_def := MGMT_ASSOC.ASSOC_DEF_CONTAINS;
       END IF;
       IF emdw_log.P_IS_DEBUG_SET THEN
          emdw_log.debug('cleanup_dep_detail:Remove assoc '
                        || l_assoc_def 
                        || ' for source (' 
                        || l_assoc_records(indx).source_target_name 
                        || ', ' || l_assoc_records(indx).source_target_type
                        || '), and assoc target (' 
                        || l_assoc_records(indx).assoc_target_name
                        || ', ' || l_assoc_records(indx).assoc_target_type
                        || ') ', G_MODULE_NAME);
       END IF;
       mgmt_assoc.delete_target_assoc(l_assoc_def,
                            l_assoc_records(indx).source_target_name,
                            l_assoc_records(indx).source_target_type,
                            l_assoc_records(indx).assoc_target_name,
                            l_assoc_records(indx).assoc_target_type);
     END LOOP;
   END IF;

-- merge two services and targets list
 
   
    SELECT mgmt_guid_obj(target_guid, target_name)
      BULK COLLECT INTO l_services_ret
    FROM mgmt_targets
    WHERE target_guid IN ( 
       SELECT UNIQUE guid
        FROM TABLE(CAST(l_services as MGMT_GUID_ARRAY))
       UNION
       SELECT UNIQUE source_target_guid
       FROM TABLE(CAST(l_assoc_records as MGMT_MEMBER_ASSOC_LIST))
    );

p_impacted_services := l_services_ret;
    IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('cleanup_dep_detail:Exit', G_MODULE_NAME);
    END IF;

END cleanup_dep_detail;

PROCEDURE check_system(p_system_type IN VARCHAR2)
AS
BEGIN
  IF is_system(p_system_type) = 0 THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
        'The specified target type is not a system type, type =  ' 
           || p_system_type);
  END IF;
END check_system;

PROCEDURE remove_scoped_assoc(p_system_name IN VARCHAR2,
                              p_system_type IN VARCHAR2,
                              p_members_del IN SMP_EMD_NVPAIR_ARRAY)
AS
BEGIN
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('remove_scoped_assoc:Enter', G_MODULE_NAME);
  END IF;

IF emdw_log.P_IS_DEBUG_SET THEN
     FOR indx IN 1..p_members_del.COUNT
     LOOP
       emdw_log.debug('remove_scoped_assoc: remove assoc for target ' 
                      || p_members_del(indx).name || ' scoped by '
                      || p_system_name, G_MODULE_NAME);
     END LOOP;
   END IF; 
   
   FOR rec IN (SELECT /*+CARDINALITY(dels 5) */ 
                      UNIQUE d.assoc_def_name, t1.target_name src_target_name,
                      t1.target_type src_target_type, 
                      t2.target_name assoc_target_name,
                      t2.target_type assoc_target_type,
                      t3.target_name scope_target_name,
                      t3.target_type scope_target_type
               FROM mgmt_target_assoc_defs d,
                    mgmt_target_assocs a,
                    mgmt_targets t1,
                    mgmt_targets t2,
                    mgmt_targets t3,
                    TABLE(CAST(p_members_del AS SMP_EMD_NVPAIR_ARRAY)) dels
               WHERE d.assoc_guid = a.assoc_guid
                 AND a.scope_target_guid = t3.target_guid
                 AND t3.target_name = p_system_name
                 AND t3.target_type = p_system_type
                 AND a.source_target_guid = t1.target_guid
                 AND a.assoc_target_guid = t2.target_guid
                 AND ((t1.target_name = dels.name
                       AND t1.target_type = dels.value)
                     OR ( t2.target_name = dels.name
                         AND t2.target_type = dels.value )
                     )
                )
    LOOP
       IF emdw_log.P_IS_DEBUG_SET THEN
          emdw_log.debug('remove_scoped_assoc: remove assoc for ' || 
                         ' assoc def ' || rec.assoc_def_name
                         || ' src target ' || rec.src_target_name ||
                         ' assoc target ' || rec.assoc_target_name ||
                         ' scope target ' || rec.scope_target_name,
                         G_MODULE_NAME);
       END IF;
       mgmt_assoc.delete_target_assoc(rec.assoc_def_name,
                                      rec.src_target_name,
                                      rec.src_target_type,
                                      rec.assoc_target_name,
                                      rec.assoc_target_type,
                                      rec.scope_target_name,
                                      rec.scope_target_type);
    END LOOP;
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('remove_scoped_assoc:Exit', G_MODULE_NAME);
  END IF;

END remove_scoped_assoc;

-- check if a service is added to its system
procedure check_loop_containment(p_system_name IN VARCHAR2,
                                 p_system_type IN VARCHAR2,
                                 p_members_to_add IN SMP_EMD_NVPAIR_ARRAY)
as
l_loop_services SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
l_services VARCHAR2(4000);
begin
    IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('check_loop_containment:Enter', G_MODULE_NAME);
    END IF;

SELECT smp_emd_nvpair(t.target_name, t.target_type)
     BULK COLLECT INTO l_loop_services
     FROM mgmt_targets t, mgmt_target_assocs a, mgmt_targets system,
          TABLE(CAST(p_members_to_add as SMP_EMD_NVPAIR_ARRAY)) new_members
    WHERE t.target_guid = a.source_target_guid
      AND a.assoc_guid = mgmt_assoc.g_runs_on_guid
      AND a.assoc_target_guid = system.target_guid
      AND system.target_name = p_system_name
      AND system.target_type = p_system_type
      AND new_members.name = t.target_name
      AND new_members.value = t.target_type;

IF l_loop_services.COUNT > 0 THEN
       FOR indx IN 1..l_loop_services.COUNT
       LOOP 
         l_services := l_services || l_loop_services(indx).name||':'
                       ||l_loop_services(indx).value||' ';
       END LOOP;
       IF emdw_log.p_is_debug_set THEN
         emdw_log.debug('check_loop_containment: Violation services are ' 
                        || l_services , G_MODULE_NAME);
       END IF;
       raise_application_error(mgmt_global.LOOP_CONTAINMENT_ERR,
                               mgmt_global.LOOP_CONTAINMENT_ERR_M ||
                   ' Service details: ' || l_services ||
                   ' System details: ' || p_system_name || ':' 
                                       || p_system_type);
    END IF;

IF emdw_log.P_IS_DEBUG_SET THEN
      emdw_log.debug('check_loop_containment:Exit', G_MODULE_NAME);
    END IF;
end check_loop_containment;

--------------------------END PRIVATE PROCEDURES-----------------------------
PROCEDURE CREATE_SYSTEM
(
  P_SYSTEM_NAME     IN VARCHAR2,
  P_SYSTEM_TYPE     IN VARCHAR2,
  P_MEMBERS         IN SMP_EMD_NVPAIR_ARRAY,
  p_timezone_region IN VARCHAR2,
  p_owner           IN VARCHAR2 DEFAULT NULL,
  p_type_meta_ver   IN VARCHAR2 DEFAULT '1.0'
)
AS
l_current_user VARCHAR2(256);
l_target_guid mgmt_targets.target_guid%TYPE;
BEGIN
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('create_system:Enter', G_MODULE_NAME);
  END IF;

em_check.check_not_null(p_system_name, 'p_system_name');
    check_system(p_system_type);

EM_TARGET.add_aggregate_target(p_target_guid => l_target_guid,
                                   p_target_name => p_system_name,
                                   p_target_type => p_system_type,
                                   p_member_targets => p_members,
                                   p_target_owner => p_owner,
                                   p_tz_rgn => p_timezone_region,
                                   p_required_member_priv => MGMT_USER.VIEW_TARGET,
                                   p_type_meta_ver => p_type_meta_ver
                                  );

IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('create_system:Exit', G_MODULE_NAME);
  END IF;

END CREATE_SYSTEM;

PROCEDURE MODIFY_SYSTEM(P_SYSTEM_NAME IN VARCHAR2,
                        P_SYSTEM_TYPE IN VARCHAR2,
                        P_MEMBERS_ADD IN SMP_EMD_NVPAIR_ARRAY,
                        P_MEMBERS_DEL IN SMP_EMD_NVPAIR_ARRAY,
                        p_owner IN VARCHAR2 DEFAULT NULL)
AS
  l_target_guid mgmt_targets.target_guid%TYPE; 
BEGIN
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('modify_system:Enter', G_MODULE_NAME);
  END IF;

IF emdw_log.P_IS_DEBUG_SET THEN
      IF p_members_add is not null THEN
         emdw_log.debug('modify_system: add members for ' 
               || p_system_name, G_MODULE_NAME);
         FOR indx IN 1..p_members_add.COUNT
         LOOP
           emdw_log.debug('target_name ' || p_members_add(indx).name
                       || ' type ' || p_members_add(indx).value,
                       G_MODULE_NAME);
         END LOOP;
      END IF;
      IF p_members_del is not null THEN
         emdw_log.debug('modify_system: del members for ' 
               || p_system_name, G_MODULE_NAME);
         FOR indx IN 1..p_members_del.COUNT
         LOOP
           emdw_log.debug('target_name ' || p_members_del(indx).name
                       || ' type ' || p_members_del(indx).value,
                       G_MODULE_NAME);
         END LOOP;
      END IF;
    END IF;

em_check.check_not_null(p_system_name, 'p_system_name');
    check_system(p_system_type);
    check_loop_containment(p_system_name, p_system_type, 
                             p_members_add);
    
    EM_TARGET.modify_aggregate_target(p_target_guid => l_target_guid,
                                      p_target_name => p_system_name,
                                      p_target_type => p_system_type,
                                      p_member_targets_to_add => p_members_add,
                                      p_member_targets_to_remove => p_members_del,
                                      p_target_owner => p_owner,
                                      p_required_target_priv => MGMT_USER.OPERATOR_TARGET
                                     );

--  remove scoped assoc for p_members_del
    IF p_members_del is NOT NULL and p_members_del.COUNT > 0 THEN
       remove_scoped_assoc(p_system_name, p_system_type,
                          p_members_del);
    END IF;
   
    -- service metric processing in assoc callback
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('modify_system:Exit', G_MODULE_NAME);
  END IF;

END MODIFY_SYSTEM;

PROCEDURE MODIFY_SYSTEM(p_system_name IN VARCHAR2, 
                        p_system_type IN VARCHAR2,
                        p_new_members IN SMP_EMD_NVPAIR_ARRAY,
                        p_owner IN VARCHAR2 DEFAULT NULL)
AS
l_members_del SMP_EMD_NVPAIR_ARRAY;
l_members_add SMP_EMD_NVPAIR_ARRAY;
l_new_members SMP_EMD_NVPAIR_ARRAY := p_new_members;
BEGIN
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('modify_system:Enter', G_MODULE_NAME);
  END IF;

em_check.check_not_null(p_system_name, 'p_system_name');
    check_system(p_system_type);

IF p_new_members IS NULL THEN
      l_new_members := SMP_EMD_NVPAIR_ARRAY();
   END IF;

SELECT SMP_EMD_NVPAIR(target_name, target_type)
     BULK COLLECT INTO l_members_del
   FROM ( SELECT t.target_name, t.target_type
          FROM mgmt_target_assocs a,
               mgmt_targets t,
               mgmt_targets src_t
          WHERE a.assoc_guid = mgmt_assoc.g_contains_guid
            AND a.source_target_guid = src_t.target_guid
            AND src_t.target_name = p_system_name
            AND src_t.target_type = p_system_type
            AND a.assoc_target_guid = t.target_guid
         MINUS
         SELECT name target_name, value target_type
         FROM TABLE(CAST(l_new_members AS SMP_EMD_NVPAIR_ARRAY)) members
       );

SELECT SMP_EMD_NVPAIR(target_name, target_type)
     BULK COLLECT INTO l_members_add
   FROM ( SELECT name target_name, value target_type
         FROM TABLE(CAST(l_new_members AS SMP_EMD_NVPAIR_ARRAY)) members
         MINUS
         SELECT t.target_name, t.target_type
          FROM mgmt_target_assocs a,
               mgmt_targets t,
               mgmt_targets src_t
          WHERE a.assoc_guid = mgmt_assoc.g_contains_guid
            AND a.source_target_guid = src_t.target_guid
            AND src_t.target_name = p_system_name
            AND src_t.target_type = p_system_type
            AND a.assoc_target_guid = t.target_guid
       );

modify_system(p_system_name, p_system_type,
                 l_members_add, l_members_del,p_owner);

IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('modify_system:Exit', G_MODULE_NAME);
  END IF;

END MODIFY_SYSTEM;
 
PROCEDURE DELETE_SYSTEM(P_SYSTEM_NAME IN VARCHAR2, P_SYSTEM_TYPE IN VARCHAR2)
AS
BEGIN
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('delete_system:Enter', G_MODULE_NAME);
  END IF;

em_check.check_not_null(p_system_name, 'p_system_name');
    check_system(p_system_type);

MGMT_ADMIN.delete_target(p_system_name, p_system_type);
    -- service metric processing in assoc callback
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('delete_system:Exit', G_MODULE_NAME);
  END IF;

END delete_system;

FUNCTION is_system(p_target_type IN VARCHAR2)
RETURN NUMBER
AS
l_is_system NUMBER := 0;
BEGIN
  SELECT DECODE(property_value, '1', 1, 0)
    INTO l_is_system
    FROM mgmt_type_properties
   WHERE target_type = p_target_type
     AND property_name = MGMT_GLOBAL.G_IS_SYSTEM_PROP;

return l_is_system;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
   -- if the property is not defined, then it is not a system
    return 0;
END is_system;

PROCEDURE member_deletion_callback(p_assoc_def_name IN VARCHAR2,
                              p_source_target_name IN VARCHAR2,
                              p_source_target_type IN VARCHAR2,
                              p_assoc_target_name IN VARCHAR2,
                              p_assoc_target_type IN VARCHAR2,
                              p_scope_target_name IN VARCHAR2,
                              p_scope_target_type IN VARCHAR2)
AS
l_assoc_target_in NUMBER;
l_services MGMT_GUID_ARRAY;
BEGIN
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('member_deletion_callback:Enter, source '
                 || '(' || p_source_target_name
                 || ',' || p_source_target_type || '), assoc ('
                 || p_assoc_target_name || ',' 
                 || p_assoc_target_type || ')', G_MODULE_NAME);
    emdw_log.debug('member_deletion_callback:Member of  '
                 || 'system ('
                 || p_source_target_name || ',' 
                 || p_source_target_type || ') are', G_MODULE_NAME);
    
    FOR rec IN (SELECT t.target_name, t.target_type
                 FROM mgmt_flat_target_assoc fa, mgmt_targets t,
                      mgmt_targets t1
                 WHERE fa.source_target_guid = t1.target_guid
                  AND  t1.target_name = p_source_target_name
                  AND  t1.target_type = p_source_target_type
                  AND t.target_guid = fa.assoc_target_guid
                  AND fa.is_membership = 1)
   LOOP
     emdw_log.debug('member_deletion_callback: ('
               || rec.target_name
               || ', ' || rec.target_type || ')'
               , G_MODULE_NAME);
   END LOOP;
  END IF;

IF (is_system(p_source_target_type)  = 0 
      AND  mgmt_group.is_group(p_source_target_type) = 0)
  THEN
     IF emdw_log.P_IS_DEBUG_SET THEN
        emdw_log.debug('member_deletion_callback: source is not a system or '
                      || ' group, just return ', G_MODULE_NAME);
     END IF;
     RETURN;
  END IF;

IF NOT P_IS_BYPASS_CALLBACK THEN
    SELECT count(*) INTO l_assoc_target_in
    FROM mgmt_metric_dependency_details
    WHERE dep_target_guid = 
     (SELECT target_guid 
      FROM mgmt_targets
      WHERE target_name = p_assoc_target_name
        AND target_type = p_assoc_target_type);
    IF l_assoc_target_in > 0 THEN
      cleanup_dep_detail(l_services);
      -- WRAPPER EVAL API WILL set sev of metric to error when eval it and id
      -- the metric doesn't have any dependency detail
    END IF;
  END IF;

IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('member_deletion_callback:Exit', G_MODULE_NAME);
  END IF;
 
END member_deletion_callback;

PROCEDURE runs_on_deletion_callback(p_assoc_def_name IN VARCHAR2,
                              p_source_target_name IN VARCHAR2,
                              p_source_target_type IN VARCHAR2,
                              p_assoc_target_name IN VARCHAR2,
                              p_assoc_target_type IN VARCHAR2,
                              p_scope_target_name IN VARCHAR2,
                              p_scope_target_type IN VARCHAR2)
AS
l_lock_targets MGMT_TARGET_GUID_ARRAY;
l_source_guid RAW(16);
l_assoc_guid RAW(16);
BEGIN
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('runs_on_deletion_callback:Enter  source '
                 || '(' || p_source_target_name
                 || ',' || p_source_target_type || '), assoc ('
                 || p_assoc_target_name || ',' 
                 || p_assoc_target_type || ')', G_MODULE_NAME);
  END IF;
  IF mgmt_service.is_aggregate_service(p_source_target_type) = 0
  THEN
   IF emdw_log.p_is_debug_set THEN
     emdw_log.debug('runs_on_deletion_callback:remove all metric '
                   || ' dependency for target ' ||
                   p_source_target_name, G_MODULE_NAME);
     emdw_log.debug('member_deletion_callback:Member of  '
                 || 'system ('
                 || p_assoc_target_name || ',' 
                 || p_assoc_target_type || ') are', G_MODULE_NAME);
    
      FOR rec IN (SELECT t.target_name, t.target_type
                 FROM mgmt_flat_target_assoc fa, mgmt_targets t,
                      mgmt_targets t1
                 WHERE fa.source_target_guid = t1.target_guid
                  AND  t1.target_name = p_assoc_target_name
                  AND  t1.target_type = p_assoc_target_type
                  AND t.target_guid = fa.assoc_target_guid
                  AND fa.is_membership = 1)
     LOOP
       emdw_log.debug('member_deletion_callback: ('
               || rec.target_name
               || ', ' || rec.target_type || ')'
               , G_MODULE_NAME);
     END LOOP;

END IF;
   SELECT target_guid INTO l_source_guid
    FROM mgmt_targets
    WHERE target_name = p_source_target_name
      AND target_type = p_source_target_type;

SELECT target_guid INTO l_assoc_guid
    FROM mgmt_targets
    WHERE target_name = p_assoc_target_name
      AND target_type = p_assoc_target_type;

em_rep_metric.cleanup_runs_on_assoc_dep(l_source_guid, l_assoc_guid);

--  remove all depends on assoc for this service
   IF emdw_log.P_IS_DEBUG_SET THEN
       emdw_log.debug('runs_on_deletion_callback: Delete depends on assoc '
                || 'for target ' || p_source_target_name
                || ' of type ' || p_source_target_type, G_MODULE_NAME);
   END IF;

FOR deps IN (SELECT t.target_name, t.target_type
                  FROM mgmt_target_assocs a,
                       mgmt_target_assoc_prop p,
                       mgmt_targets t
                  WHERE a.assoc_guid = p.assoc_guid
                    AND a.source_target_guid = p.source_target_guid
                    AND a.assoc_target_guid  = p.assoc_target_guid
                    AND a.assoc_guid = mgmt_assoc.g_depends_on_guid
                    AND p.property_name = mgmt_service.g_system_depends_on_prop
                    AND p.property_value = '1'
                    AND t.target_guid = a.assoc_target_guid
                    AND a.source_target_guid = l_source_guid
                ) 
   LOOP                   
        mgmt_assoc.delete_target_assoc(mgmt_assoc.assoc_def_depends_on,
                                       p_source_target_name, 
                                       p_source_target_type,
                                       deps.target_name,
                                       deps.target_type);
   END LOOP;
  END IF;
  --  remove member assoc for the service if assoc target is from the system
   FOR rec IN (SELECT source_tgt.target_name source_target_name,
                      source_tgt.target_type source_target_type,
                      assoc_tgt.target_name assoc_target_name,
                      assoc_tgt.target_type assoc_target_type,
                      assoc_tgt.target_guid
                 FROM mgmt_target_assocs mem_assoc,
                      mgmt_targets source_tgt,
                      mgmt_targets assoc_tgt
                WHERE mem_assoc.source_target_guid = source_tgt.target_guid
                  AND mem_assoc.assoc_target_guid = assoc_tgt.target_guid
                  AND mem_assoc.source_target_guid = l_source_guid
                  AND mem_assoc.assoc_guid = mgmt_assoc.g_contains_guid
                  AND mem_assoc.assoc_target_guid  IN (
                                 SELECT assoc_target_guid
                                   FROM mgmt_flat_target_assoc
                                  WHERE is_membership = 1
                                    AND source_target_guid = l_assoc_guid
                      ))
     LOOP
       IF emdw_log.P_IS_DEBUG_SET THEN
          emdw_log.debug('Runs_on_deletion_callback:Remove member assoc '
                        || ' for source (' 
                        || rec.source_target_name 
                        || ', ' || rec.source_target_type
                        || '), and assoc target (' 
                        || rec.assoc_target_name
                        || ', ' || rec.assoc_target_type
                        || ') ', G_MODULE_NAME);
       END IF;
       mgmt_assoc.delete_target_assoc(MGMT_ASSOC.ASSOC_DEF_CONTAINS,
                            rec.source_target_name,
                            rec.source_target_type,
                            rec.assoc_target_name,
                            rec.assoc_target_type);
        
     END LOOP;
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('runs_on_deletion_callback:Exit', G_MODULE_NAME);
  END IF;
END runs_on_deletion_callback;

PROCEDURE system_pre_del_callback(p_target_name IN VARCHAR2,
                                  p_target_type IN VARCHAR2,
                                  p_target_guid In RAW)
AS
BEGIN
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('system_pre_del_callback:Enter. Target (' 
                 || p_target_name || ', ' 
                 || p_target_type || ')', G_MODULE_NAME);
  END IF;
  
  IF is_system(p_target_type) = 1 THEN
    FOR rec IN (SELECT t.target_name, t.target_type
                FROM mgmt_targets t, mgmt_target_assocs a, mgmt_targets system
                WHERE t.target_guid = a.source_target_guid
                  AND a.assoc_guid = mgmt_assoc.g_runs_on_guid
                  AND a.assoc_target_guid = system.target_guid
                  AND system.target_name = p_target_name
                  AND system.target_type = p_target_type)
    LOOP
      IF emdw_log.P_IS_DEBUG_SET THEN
        emdw_log.debug('system_pre_del_callback:Remove runs-on assoc between' 
                 || ' system ' || p_target_name || ' and ' 
                 || rec.target_name, G_MODULE_NAME);
      END IF;
      runs_on_deletion_callback(mgmt_assoc.ASSOC_DEF_RUNS_ON,
                               rec.target_name, rec.target_type,
                               p_target_name, p_target_type,
                               NULL, NULL);

END LOOP;
  END IF;
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('system_pre_del_callback:Exit', G_MODULE_NAME);
  END IF;

END system_pre_del_callback;

FUNCTION get_impacted_services(p_system_guid IN RAW,
                               p_targets IN MGMT_GUID_ARRAY)
RETURN MGMT_GUID_ARRAY
AS
l_ret_services MGMT_GUID_ARRAY;
l_targets MGMT_GUID_ARRAY := p_targets;
BEGIN
  IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('get_impacted_service:Enter', G_MODULE_NAME);
  END IF;

P_IS_BYPASS_CALLBACK := TRUE;
  SAVEPOINT check_service;
  -- remove the member assoc between targets and system
  IF l_targets IS NULL THEN
    SELECT mgmt_guid_obj(a.assoc_target_guid, t.target_name)
     BULK COLLECT INTO l_targets
    FROM mgmt_target_assocs a, mgmt_targets t
    WHERE a.assoc_guid = mgmt_assoc.g_contains_guid
      AND a.source_target_guid = p_system_guid
      AND t.target_guid = a.assoc_target_guid;
  END IF;
  
  -- [TODO] use bulk api
  FOR rec IN (SELECT t.target_name source_target_name, 
                     t.target_type source_target_type,
                     t1.target_name assoc_target_name,
                     t1.target_type assoc_target_type
              FROM mgmt_targets t,
                   mgmt_targets t1, 
                 TABLE(CAST(l_targets as MGMT_GUID_ARRAY)) members
              WHERE t.target_guid = p_system_guid
                AND t1.target_guid = members.guid)
  LOOP
     mgmt_assoc.delete_target_assoc(MGMT_ASSOC.ASSOC_DEF_CONTAINS,
                        rec.source_target_name, rec.source_target_type,
                        rec.assoc_target_name, rec.assoc_target_type);
  END LOOP;
  
  cleanup_dep_detail(l_ret_services);       
  ROLLBACK  TO check_service;

P_IS_BYPASS_CALLBACK := FALSE;

IF emdw_log.P_IS_DEBUG_SET THEN
    emdw_log.debug('get_impacted_service:Exit', G_MODULE_NAME);
  END IF;

RETURN l_ret_services;
EXCEPTION
  WHEN OTHERS THEN
    P_IS_BYPASS_CALLBACK := FALSE;
    emdw_log.error('get_impacted_service:Exit with exception ' || SQLERRM,
                       G_MODULE_NAME);
    RAISE;
END get_impacted_services;

--
-- PROCEDURE: upsert_system
-- PURPOSE
--   this procedure will be used by emctl to create/update the system. If the system with the
--   given name and type is not present then it will create a new system otherwise it will update 
--  the existing system.
PROCEDURE upsert_system(p_upsert IN NUMBER,
                        p_system_name IN VARCHAR2,
                        p_system_type IN VARCHAR2,
                        p_members IN SMP_EMD_NVPAIR_ARRAY,
                        p_timezone_region IN VARCHAR2) 
AS
l_upsert NUMBER := p_upsert;
BEGIN
  IF emdw_log.P_IS_DEBUG_SET THEN
     emdw_log.debug('UPSERT_SYSTEM:Enter', G_MODULE_NAME);
  END IF;

BEGIN
    create_system(p_system_name, p_system_type, p_members, p_timezone_region);
  EXCEPTION
    WHEN MGMT_GLOBAL.TARGET_ALREADY_EXISTS 
      THEN
        IF (l_upsert = 1) THEN
          modify_system(p_system_name => p_system_name,
                        p_system_type => p_system_type,
                        p_members_add => p_members,
                        p_members_del => null);
        ELSE
          RAISE;
        END IF;
  END;

IF emdw_log.P_IS_DEBUG_SET THEN
     emdw_log.debug('UPSERT_SYSTEM:Exit', G_MODULE_NAME);
  END IF;

END upsert_system;

END;
/
show errors;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de