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 - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem 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; 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; 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; 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; 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 (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; 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( 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); 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; -- 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; 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; -- 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; 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; 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