Rem
Rem $Header: website_target_pkgbody.sql 03-jun-2008.12:16:39 jashukla Exp $
Rem
Rem website_target_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      website_target_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED (MM/DD/YY)
Rem    jashukla  06/03/08 - Bug 7144619 remove internal identifiers
Rem    shnavane  07/17/07 - Fix bug #5970781
Rem    shnavane  07/23/07 - Backport shnavane_bug-5970781 from main
Rem    yxie      11/21/06 - XbranchMerge yxie_bug5527422 from main
Rem    yxie      11/09/06 - XbranchMerge yxie_bug5567723 from main
Rem    yxie      12/08/05 - Backport yxie_bug-4634418 from main 
Rem    yxie      09/26/06 - fix bug 5567723
Rem    yxie      11/07/05 - fix bug 4634418
Rem    yxie      07/22/05 - add support for generic_redundancy group
Rem    yxie      05/27/05 - fix bug 4390544
Rem    eporter   12/15/04 - Replace mgmt_rt_region_members table 
Rem    snakai    09/27/04 - update type_meta_ver
Rem    dcawley   07/07/04 - Increase user name size 
Rem    snakai    11/15/03 - increment website type_meta_ver 
Rem    rmarripa  10/31/03 - 
Rem    rmarripa  10/30/03 - cleanup getWebsiteData to get monitoring agent 
Rem    streddy   07/11/03 - Use NEW() to create MGMT_THRESHOLD objects
Rem    rmarripa  04/01/03 - get agent target name
Rem    rmarripa  03/28/03 - change query in getEMDHostAndURLs
Rem    rmarripa  03/19/03 - .
Rem    rmarripa  03/19/03 - change getEMDHostAndURLs
Rem    asawant   07/09/02 - Fixing fetch of hostname
Rem    skini     07/12/02 - target_name column size change
Rem    rmarripa  06/24/02 - add error code and description
Rem    rmarripa  06/14/02 - remove rep url and region thresholds
Rem    rpinnama  05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama  05/15/02 - Restructured.
Rem    dcawley   03/14/02 - New Collection APIs.
Rem    edemembe  01/11/02 - Putting check around metric definition.
Rem    rmarripa  01/08/02 - Create target_guid to insert into targets table.
Rem    edemembe  01/08/02 - Metrics are now target independent
Rem    edemembe  12/27/01 - Removing target name/type and metric name/column references
Rem    rmarripa  12/19/01 - Add procedure to get the emdHost and emdURL for selected targets.
Rem    rmarripa  12/14/01 - Add a procedure to get URL base.
Rem    rmarripa  11/06/01 - To add numofoccurences to MGMT_THRESHOLDS object.
Rem    rmarripa  10/30/01 - To filter out website target types in available targets.
Rem    rmarripa  10/23/01 - To change the store metric values to 'Y'/'N'.
Rem    rmarripa  09/25/01 - To change the metric name to Region Latency,.
Rem    rmarripa  09/17/01 - updateWebsite:Do not throw an exception for empty member target list in edit mode
Rem    asawant   09/07/01 - Time to timezone conversion.
Rem    lgloyd    09/06/01 - store emd url with target
Rem    asawant   08/28/01 - Fixing deleteCompTarget and checkForDeletion
Rem    asawant   08/24/01 - Updating with API changes and bug fixes.
Rem    lgloyd    08/16/01 - Creation.
Rem

CREATE OR REPLACE PACKAGE BODY mgmt_website_target AS

  -- Define package global constants
  -- The display name the website target
  WEBSITE_DISPLAY_TYPE CONSTANT VARCHAR2(10) := 'Website';

-- PROCEDURE addWebsite()
-- PURPOSE : to add or update a website target and it's related metadata in the
--   database.
-- INPUT PARAMETERS:
--   v_target_name : the composite target name
--   v_target_type : the composite target type
--   v_monitoring_host : the name of the monitoring host
--   v_member_targets : a table with all the member target names and their types
--   v_properties : a table with all the properties and their respective values

PROCEDURE addWebsite
(
  v_target_name        IN  VARCHAR2,
  v_target_type        IN  VARCHAR2,
  v_monitoring_host    IN  VARCHAR2,
  v_monitoring_url     IN  VARCHAR2,
  v_member_targets     IN  SMP_EMD_NVPAIR_ARRAY,
  v_properties         IN  MGMT_TARGET_PROPERTY_LIST,
  v_result             OUT INTEGER,
  v_err_desc           OUT VARCHAR2

) IS
  l_target_guid raw(16);
  l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();

BEGIN

  -- If member targets are empty then throw an exception
  IF ((v_member_targets IS NULL) OR (v_member_targets.COUNT = 0)) THEN
    v_result := EMD_BCNTXN.p_err_empty_mem_tgt_list;
    v_err_desc :='Membet target list is empty.';
    RETURN;
  END IF;

  -- If Monitoring url is null then return error
  IF (v_monitoring_url IS NULL) THEN
    v_result := EMD_BCNTXN.p_err_agent_url_invalid;
    v_err_desc :='Invalid monitoring agent URL';
    RETURN;
  END IF;
  mgmt_target.add_target(v_target_name, v_target_type, v_monitoring_host, 
           v_properties, NULL, v_member_targets, NULL, v_target_name,
           v_monitoring_url, TO_CHAR(SYSTIMESTAMP,'TZR'), 
           MGMT_GLOBAL.G_MON_MODE_DEFAULT, '3.0'); 

  v_result := EMD_BCNTXN.p_bcn_success;

EXCEPTION
  WHEN  MGMT_GLOBAL.insufficient_privileges THEN
    v_result := EMD_BCNTXN.p_err_crttgt_fullmem_priv_req;
    v_err_desc :='CREATE_TARGET privilege and FULL target privilegtes on member targets are required to create website target';

  WHEN MGMT_GLOBAL.target_does_not_exist THEN
    v_result := EMD_BCNTXN.p_bcn_err_tgtnotfound;
    v_err_desc :='Host not found';

  WHEN OTHERS THEN
    v_err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50);
    v_result := EMD_BCNTXN.p_bcn_err_oraerr;

END addWebsite;


-- PROCEDURE getWebsiteData()
-- PURPOSE : to retrieve a website targets data from the DB.
-- INPUT PARAMETERS:
--   v_target_name : the composite target name
--   v_target_type : the composite target type
-- OUTPUT PARAMETERS:
--   v_all_target_types : all types available in mgmt_target_memberships
--   v_member_targets : a table with all the member target names and their types
--   v_properties : a table with all the properties and their respective values
--   v_monitoring_host : the name of the monitoring host (
--   v_emd_url : emd url of the monitoring host
--   v_success : the result of the operation of the procedure ('true' if success
--     and 'false' if failure)
--   v_err_code : numeric code representing the failure, valid codes are:
--     1 - unexpected exception. The err_msg carries the exception number and
--         related text msg as returned from the DB.
--     2 - No Website Target with the given name was found in the repository
--   v_err_msg : a text message associated with the error
PROCEDURE getWebsiteData
(
  v_target_name        IN  VARCHAR2,
  v_target_type        IN  VARCHAR2,
  v_agent_tgt_name     OUT VARCHAR2,
  v_all_target_types   OUT SMP_EMD_NVPAIR_ARRAY,
  v_basic_info         OUT MGMT_TARGET.CURSOR_TYPE,
  v_properties         OUT MGMT_TARGET.CURSOR_TYPE,
  v_member_targets     OUT MGMT_TARGET.CURSOR_TYPE
) IS
  l_target_guid      RAW(16);
  l_comp_target_info MGMT_TARGET.CURSOR_TYPE;
  l_cred_info        MGMT_TARGET.CURSOR_TYPE;
  l_emd_url          MGMT_TARGETS.EMD_URL%TYPE;
  l_index            NUMBER;
  l_length           NUMBER;
BEGIN

  -- initialize out params
  --v_success := 'true';
  --v_err_code := ' ';
  --v_err_msg := ' ';

  -- Get all existing target types
  SELECT SMP_EMD_NVPAIR(target_type, type_display_name)
    BULK COLLECT INTO v_all_target_types
    FROM
      (
        SELECT DISTINCT target_type, type_display_name
          FROM mgmt_targets
             WHERE target_type != 'website' 
               and target_type != 'oracle_beacon'
               and is_group != 1
          ORDER BY type_display_name ASC
      );

  mgmt_target.get_target_info(v_target_name, v_target_type, v_basic_info,
           v_properties, l_comp_target_info, v_member_targets, l_cred_info);

  SELECT emd_url INTO l_emd_url
    FROM mgmt_targets 
   WHERE target_name = v_target_name 
     AND target_type = v_target_type;
 
  BEGIN
    SELECT target_name INTO v_agent_tgt_name
      FROM mgmt_targets
     WHERE target_type = 'oracle_emd' 
       AND emd_url = l_emd_url;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_index := instr(l_emd_url, '://');
      IF ( l_index = 0 ) THEN
        l_index := instr(l_emd_url, '/');
        IF ( l_index = 0 ) THEN
          v_agent_tgt_name := l_emd_url;
        ELSE
          v_agent_tgt_name := substr(l_emd_url, 1, l_index-1);
        END IF;
      ELSIF( ( l_index > 0 ) AND (l_index <= 6 ) ) THEN
        l_index := l_index+3;
        l_length := instr(l_emd_url, '/', l_index);
        IF ( l_length = 0 ) THEN
          v_agent_tgt_name := substr( l_emd_url, l_index);
        ELSE
          l_length := l_length - l_index;
          v_agent_tgt_name := substr( l_emd_url, l_index, l_length );
        END IF;
      END IF;
  END;      
         
  IF l_comp_target_info%ISOPEN THEN
    CLOSE l_comp_target_info;
  END IF;
  IF l_cred_info%ISOPEN THEN
    CLOSE l_cred_info;
  END IF;

EXCEPTION
  -- Catch all unhandled exceptions and report them with err_code 1
  WHEN OTHERS THEN
    IF v_basic_info%ISOPEN THEN
      CLOSE v_basic_info;
    END IF;
    IF v_properties%ISOPEN THEN
      CLOSE v_properties;
    END IF;
    IF l_comp_target_info%ISOPEN THEN
      CLOSE l_comp_target_info;
    END IF;
    IF v_member_targets%ISOPEN THEN
      CLOSE v_member_targets;
    END IF;
    IF l_cred_info%ISOPEN THEN
      CLOSE l_cred_info;
    END IF;
    raise;
    --v_success := 'false';
    --v_err_code := '1';
    --v_err_msg := SQLERRM();

END getWebsiteData;


-- PROCEDURE getURLBase()
-- PURPOSE : to retrieve a target's URL base which 
--   is defined as the target property.
-- INPUT PARAMETERS:
--   v_target_guid : the target guid
-- OUTPUT PARAMETERS:
--   v_urlbase : URL base of the target.
PROCEDURE getURLBase
(
  v_target_guid        IN  VARCHAR2,
  v_urlbase            OUT VARCHAR2
) IS
  l_protocol mgmt_target_properties.property_value%TYPE := NULL;
  l_machine_name mgmt_target_properties.property_value%TYPE := NULL;
  l_port mgmt_target_properties.property_value%TYPE := NULL;
  l_properties SMP_EMD_NVPAIR_ARRAY;
  l_property_name mgmt_target_properties.property_name%TYPE;

BEGIN
   -- get website properties
  BEGIN
    SELECT SMP_EMD_NVPAIR( TRIM(property_name) , TRIM( property_value) )
      BULK COLLECT INTO l_properties
      FROM mgmt_target_properties
      WHERE target_guid = v_target_guid 
      AND ( ( property_name = 'HTTPProtocol' )
         OR ( property_name = 'HTTPMachine' )
         OR ( property_name = 'HTTPPort' ) );
  END;

  DBMS_OUTPUT.PUT_LINE('Number of rows retrieved : ' || l_properties.COUNT );
  FOR i IN 1..l_properties.COUNT LOOP
      l_property_name := l_properties(i).name;

      DBMS_OUTPUT.PUT_LINE('l_properties[i].name: ' || l_property_name );
      DBMS_OUTPUT.PUT_LINE('l_properties[i].value : ' ||l_properties(i).value);

      IF ( l_property_name = 'HTTPProtocol' ) THEN
        l_protocol := l_properties(i).value;
      ELSIF ( l_property_name = 'HTTPMachine' ) THEN
   		l_machine_name := l_properties(i).value;
	  ELSIF ( l_property_name = 'HTTPPort' ) THEN
        l_port := l_properties(i).value;
	  END IF;
  
  END LOOP;

  IF( ( l_protocol IS NOT NULL ) AND ( l_machine_name IS NOT NULL ) AND 
                                          ( l_port IS NOT NULL ) ) THEN
    v_urlbase := l_protocol || '://' || l_machine_name || ':' || l_port ;
  END IF;

  RETURN;

END getURLBase;

  

-- PROCEDURE getEMDHostAndURLs()
-- PURPOSE : to retrieve a target's emd host and emd URLs given a list 
-- 	of target name and type. 
-- INPUT PARAMETERS:
--   v_target_name_type_arr : Array of target name and type 
-- OUTPUT PARAMETERS:
--   v_emdHostURLArr : Array of emd host and URL for the given targets.
PROCEDURE getEMDHostAndURLs
(
  v_tgt_nametype_arr IN  SMP_EMD_NVPAIR_ARRAY,
  v_emdHostURLArr    OUT SMP_EMD_NVPAIR_ARRAY
) IS
  currIndex INTEGER;

BEGIN

   -- get EMD host and URL for the given targets
  BEGIN

    SELECT SMP_EMD_NVPAIR(tgt.emd_url, tgt.target_name)
      BULK COLLECT INTO v_emdHostURLArr
      FROM mgmt_targets tgt,
           (
             SELECT DISTINCT tgts.emd_url emd_url
               FROM mgmt_targets tgts,
                    TABLE(CAST(v_tgt_nametype_arr AS SMP_EMD_NVPAIR_ARRAY)) sel
               WHERE tgts.target_name = TRIM(sel.name)
                 AND tgts.target_type = TRIM(sel.value)
           ) cho
      WHERE tgt.target_type = 'oracle_emd'
        AND tgt.emd_url = cho.emd_url;


  EXCEPTION
    WHEN OTHERS THEN RAISE;
  END;
END getEmdHostAndURLs;


-- PROCEDURE addRegionForTarget()
-- PURPOSE : this procedure is of internal use to the package only, and is used
-- by the testPack() function. It's purpose is to add a region in the database
-- for a given target.
-- INPUT: none
-- OUTPUT: none
PROCEDURE addRegionForTarget
(
  v_target_name IN VARCHAR2, 
  v_target_type IN VARCHAR2,
  v_region_name IN VARCHAR2,
  v_region_member IN VARCHAR2,
  v_region_min_ip IN NUMBER,
  v_region_max_ip IN NUMBER
)
IS
  l_target_guid RAW(16);
  l_region_guid RAW(16);
  l_add_result  INTEGER;
BEGIN

  -- Get target guid
  SELECT target_guid INTO l_target_guid
    FROM mgmt_targets
    WHERE target_name = v_target_name
      AND target_type = v_target_type;

  BEGIN
    SELECT region_guid INTO l_region_guid
      FROM mgmt_rt_regions
      WHERE target_guid = l_target_guid
        AND region_name = v_region_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- Insert region definition
      INSERT INTO mgmt_rt_regions(target_guid, region_name, description)
        VALUES (l_target_guid, v_region_name, 'none');

      -- Now get the region guid
      SELECT region_guid INTO l_region_guid
        FROM mgmt_rt_regions
        WHERE target_guid = l_target_guid
          AND region_name = v_region_name;
  END;

  -- Insert region component
  l_add_result := EMD_MNTR_USER.addRegionEntry(l_region_guid, v_region_member,
                                  v_region_min_ip, v_region_min_ip);

END addRegionForTarget;

PROCEDURE testGetHostEMDURL IS
  l_memb_tgt1 VARCHAR2(256);
  l_memb_type1 VARCHAR2(256);
  l_memb_tgt2 VARCHAR2(256);
  l_memb_type2 VARCHAR2(256);
  l_member_targets SMP_EMD_NVPAIR_ARRAY;
  host_agent_arr SMP_EMD_NVPAIR_ARRAY;
begin
  l_memb_tgt1 := '/fs1/path1/OC4J_EM';
  l_memb_type1 := 'oc4j';
  l_memb_tgt2 := '/fs1/patch1/HTTP_Server';
  l_memb_type2 := 'oracle_apache';
  l_member_targets := SMP_EMD_NVPAIR_ARRAY (   
                        SMP_EMD_NVPAIR(l_memb_tgt1, l_memb_type1), 
                        SMP_EMD_NVPAIR(l_memb_tgt2, l_memb_type2) ); 
  mgmt_website_target.getEMDHostAndURLs(l_member_targets, host_agent_arr);
  
  if ( host_agent_arr IS null ) then 
    DBMS_OUTPUT.PUT_LINE('agent target array is null');
  else
    DBMS_OUTPUT.PUT_LINE('agent target array count'||host_agent_arr.count);
  end if; 
  for i in 1..host_agent_arr.count LOOP
      DBMS_OUTPUT.PUT_LINE('EMD Target ' ||host_agent_arr(i).value);
      DBMS_OUTPUT.PUT_LINE('EMD URL ' || host_agent_arr(i).name );
  end loop;
EXCEPTION
  -- Catch all unhandled exceptions and report them
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM());
END testGetHostEMDURL;


-- PROCEDURE testPack()
-- PURPOSE : this procedure is meant only to test the functionality of the
-- updateWebsite() and getWebsiteData() procedures. It does not change the state
-- of any schema objects (i.e. no rows are added or deleted from any table).
-- Currently, the functionality has to be validated by the user based on the
-- output displayed by SQLPLUS (or similar) after the run. If using SQLPLUS
-- please issue a 'set serveroutput on size 10000' before calling the test
-- procedure.
-- INPUT: none
-- OUTPUT: none
PROCEDURE testPack IS
  out_v_success VARCHAR2(200);
  out_v_err_code VARCHAR2(200);
  out_v_err_msg VARCHAR2(200);
  l_target_name MGMT_TARGETS.target_name%TYPE;
  l_target_type MGMT_TARGETS.target_type%TYPE;
  l_memb_tgt VARCHAR2(30);
  l_memb_type VARCHAR2(30);
  l_memb_tguid RAW(16);
  l_member_targets SMP_EMD_NVPAIR_ARRAY;
  l_properties MGMT_TARGET_PROPERTY_LIST;
  l_monitoring_host VARCHAR2(30);
  l_emd_url VARCHAR2(30);
  l_thresholds MGMT_THRESHOLDS;
  l_save_event_data VARCHAR2(5);
  out_err INTEGER;
BEGIN

  SAVEPOINT testPackSavepoint;

  l_memb_tgt := 'mgmt_website_tst_target';
  l_memb_type := 'mgmt_website_tst_target_type';
  l_memb_tguid := dbms_obfuscation_toolkit.md5(
		                input => utl_raw.cast_to_raw(
	                    l_memb_type||';'||l_memb_tgt));
  l_target_name := 'comp_mgmt_website_tst_target';
  l_target_type := 'website';

  DBMS_OUTPUT.PUT_LINE('Add a target to mgmt_targets');
  INSERT INTO mgmt_targets (target_name, target_type, target_guid )
    VALUES (l_memb_tgt, l_memb_type, l_memb_tguid);

  SAVEPOINT testPackSavepoint_1;

  l_member_targets := SMP_EMD_NVPAIR_ARRAY
    (
      SMP_EMD_NVPAIR(l_memb_tgt, l_memb_type)
    );
  l_properties := MGMT_TARGET_PROPERTY_LIST    (
      MGMT_TARGET_PROPERTY('HTTPProtocol','abcd', 'http'),
      MGMT_TARGET_PROPERTY('HTTPMachine','abcd', 'my_host'),
      MGMT_TARGET_PROPERTY('HTTPPort','abcd', '0000'),
      MGMT_TARGET_PROPERTY('HTTPPath','abcd', '/some_path'),
      MGMT_TARGET_PROPERTY('HTTPQuery','abcd', '?some_query')
    );

  l_monitoring_host := 'some_host';
  l_thresholds := MGMT_THRESHOLDS
    (
      MGMT_THRESHOLD.NEW('region1', 'LATENCY', 0, '20', 0, '30', 1),
      MGMT_THRESHOLD.NEW('region2', 'LATENCY', 0, '20', 0, '30', 1),
      MGMT_THRESHOLD.NEW('region3', 'LATENCY', 0, '20', 0, '30', 1)
    );
  l_save_event_data := 'Y';
  
  DBMS_OUTPUT.PUT_LINE('Force failure (based on empty list of targets)');
  addWebsite (l_target_name, l_target_type, l_monitoring_host, 
   l_emd_url,  NULL, l_properties, out_err, out_v_err_msg );
  --DBMS_OUTPUT.PUT_LINE('SUCESSS CODE: ' || out_v_success);
  DBMS_OUTPUT.PUT_LINE('ERROR CODE: ' || out_v_err_code);
  DBMS_OUTPUT.PUT_LINE('Message: ' || out_v_err_msg);

  DBMS_OUTPUT.PUT_LINE('Rolling back...');
  ROLLBACK TO testPackSavepoint_1;

  DBMS_OUTPUT.PUT_LINE('Succeed with the call.');
  addWebsite (l_target_name, l_target_type, l_monitoring_host, 
    l_emd_url, l_member_targets, l_properties, out_err, out_v_err_msg);
  --DBMS_OUTPUT.PUT_LINE('SUCESSS CODE: ' || out_v_success);
  DBMS_OUTPUT.PUT_LINE('ERROR CODE: ' || out_v_err_code);
  DBMS_OUTPUT.PUT_LINE('Message: ' || out_v_err_msg);

  DBMS_OUTPUT.PUT_LINE('Force failure (based on the same target_name)');
  addWebsite (l_target_name, l_target_type, l_monitoring_host, 
    l_emd_url, l_member_targets, l_properties, out_err, out_v_err_msg);
  --DBMS_OUTPUT.PUT_LINE('SUCESSS CODE: ' || out_v_success);
  DBMS_OUTPUT.PUT_LINE('ERROR CODE: ' || out_v_err_code);
  DBMS_OUTPUT.PUT_LINE('Message: ' || out_v_err_msg);

  DBMS_OUTPUT.PUT_LINE('Add some (not all) regions');
  addRegionForTarget(l_target_name, l_target_type, 'region1', 
    '1.1.1', EMD_MNTR_USER.ipSubnet2Num('1.1.1.0'), 
    EMD_MNTR_USER.ipSubnet2Num('1.1.1.255'));
  addRegionForTarget(l_target_name, l_target_type, 'region2', 
    '2.2.2', EMD_MNTR_USER.ipSubnet2Num('2.2.2.0'), 
    EMD_MNTR_USER.ipSubnet2Num('2.2.2.255'));
  addRegionForTarget(l_target_name, l_target_type, 'region2', 
    '2.2.21', EMD_MNTR_USER.ipSubnet2Num('2.2.21.0'), 
    EMD_MNTR_USER.ipSubnet2Num('2.2.21.255'));

  DBMS_OUTPUT.PUT_LINE('Force failure on update (incorrect list of regions)');
  --updateWebsite (l_target_name, l_target_type, l_member_targets,
    --l_properties, l_monitoring_host, l_emd_url, 1, l_thresholds, l_save_event_data, 
    --out_v_success, out_v_err_code, out_v_err_msg);
  --DBMS_OUTPUT.PUT_LINE('SUCESSS CODE: ' || out_v_success);
  --DBMS_OUTPUT.PUT_LINE('ERROR CODE: ' || out_v_err_code);
  --DBMS_OUTPUT.PUT_LINE('Message: ' || out_v_err_msg);

  DBMS_OUTPUT.PUT_LINE('Add remaining list of regions.');
  addRegionForTarget(l_target_name, l_target_type, 'region3', 
    '3.3.3', EMD_MNTR_USER.ipSubnet2Num('3.3.3.0'), 
    EMD_MNTR_USER.ipSubnet2Num('3.3.3.255'));
  addRegionForTarget(l_target_name, l_target_type, 'region3', 
    '3.3.31', EMD_MNTR_USER.ipSubnet2Num('3.3.31.0'), 
    EMD_MNTR_USER.ipSubnet2Num('3.3.31.255'));

  DBMS_OUTPUT.PUT_LINE('Finally... v_success on update!');
  --updateWebsite (l_target_name, l_target_type, l_member_targets,
    --l_properties, l_monitoring_host, l_emd_url, 1, l_thresholds, l_save_event_data, 
    --out_v_success, out_v_err_code, out_v_err_msg);
  --DBMS_OUTPUT.PUT_LINE('SUCESSS CODE: ' || out_v_success);
  --DBMS_OUTPUT.PUT_LINE('ERROR CODE: ' || out_v_err_code);
  --DBMS_OUTPUT.PUT_LINE('Message: ' || out_v_err_msg);

  DBMS_OUTPUT.PUT_LINE('Start storing data');
  --updateWebsite (l_target_name, l_target_type, l_member_targets,
    --l_properties, l_monitoring_host, l_emd_url, 1, l_thresholds, 1, 
    --out_v_success, out_v_err_code, out_v_err_msg);
  --DBMS_OUTPUT.PUT_LINE('SUCESSS CODE: ' || out_v_success);
  --DBMS_OUTPUT.PUT_LINE('ERROR CODE: ' || out_v_err_code);
  --DBMS_OUTPUT.PUT_LINE('Message: ' || out_v_err_msg);

  DBMS_OUTPUT.PUT_LINE('Stop storing data');
  --updateWebsite (l_target_name, l_target_type, l_member_targets,
    --l_properties, l_monitoring_host, l_emd_url, 1, l_thresholds, 0, 
    --out_v_success, out_v_err_code, out_v_err_msg);
  --DBMS_OUTPUT.PUT_LINE('SUCESSS CODE: ' || out_v_success);
  --DBMS_OUTPUT.PUT_LINE('ERROR CODE: ' || out_v_err_code);
  --DBMS_OUTPUT.PUT_LINE('Message: ' || out_v_err_msg);

  DECLARE
    allTgtTypes SMP_EMD_NVPAIR_ARRAY;
    memberTargets SMP_EMD_NVPAIR_ARRAY;
    representativeURL VARCHAR2(120);
  BEGIN
    DBMS_OUTPUT.PUT_LINE('allTgtTypes.COUNT : ' || allTgtTypes.COUNT);
    FOR i IN 1..allTgtTypes.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('allTgtTypes[i].name : ' || allTgtTypes(i).name);
      DBMS_OUTPUT.PUT_LINE('allTgtTypes[i].value : ' || allTgtTypes(i).value);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('l_monitoring_host: ' || l_monitoring_host);
    DBMS_OUTPUT.PUT_LINE('l_thresholds.COUNT: ' || l_thresholds.COUNT);
    FOR i IN 1..l_thresholds.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('l_thresholds[i].region : ' || 
        l_thresholds(i).key_column_value);
      DBMS_OUTPUT.PUT_LINE('l_thresholds[i].column : ' || 
        l_thresholds(i).metric_column);
      DBMS_OUTPUT.PUT_LINE('l_thresholds[i].warning_operator: ' || 
        l_thresholds(i).warning_operator);
      DBMS_OUTPUT.PUT_LINE('l_thresholds[i].warning_threshold: ' || 
        l_thresholds(i).warning_threshold);
      DBMS_OUTPUT.PUT_LINE('l_thresholds[i].critical_operator: ' || 
        l_thresholds(i).critical_operator);
      DBMS_OUTPUT.PUT_LINE('l_thresholds[i].critical_threshold : ' || 
        l_thresholds(i).critical_threshold);
      DBMS_OUTPUT.PUT_LINE('l_thresholds[i].num_occurrences : ' || 
        l_thresholds(i).num_occurrences);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('SUCESSS CODE: ' || out_v_success);
    DBMS_OUTPUT.PUT_LINE('ERROR CODE: ' || out_v_err_code);
    DBMS_OUTPUT.PUT_LINE('Message: ' || out_v_err_msg);
  END;

  DBMS_OUTPUT.PUT_LINE('Rolling back.');
  ROLLBACK TO testPackSavepoint;

EXCEPTION
  -- Catch all unhandled exceptions and report them
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM());
    DBMS_OUTPUT.PUT_LINE('Rolling back.');
    ROLLBACK TO testPackSavepoint;
END testPack;

--
-- PROCEDURE: key_del_callback
-- PURPOSE:
--   callback when a depends_on association between
--   a key component and website is deleted
--
PROCEDURE key_del_callback(p_assoc_def_name IN VARCHAR2,
                           p_source_target_name IN VARCHAR2,
                           p_source_target_type IN VARCHAR2,
                           p_assoc_target_name IN VARCHAR2,
                           p_assoc_target_type IN VARCHAR2,
                           p_scope_target_name IN VARCHAR2,
                           p_scope_target_type IN VARCHAR2)
IS

-- cursor to get the member targets of the cluster that has eum/e2e association support
CURSOR eume2e_targets_cur (p_cluster_tgt_name IN VARCHAR2, p_cluster_tgt_type IN VARCHAR2) IS
    SELECT m.member_target_name, m.member_target_type, d.assoc_def_name 
    FROM   mgmt_target_memberships m, eume2e_assocs_lookup l, mgmt_target_assoc_defs d
    WHERE  m.composite_target_type=p_cluster_tgt_type 
      AND  m.composite_target_name=p_cluster_tgt_name
      AND  m.member_target_type = l.target_type
      AND  l.assoc_guid = d.assoc_guid;
BEGIN

  --emdw_log.set_session_trace_level(3);

  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('key_del_callback: Enter', MODULE_NAME) ; 
  END IF ;

  -- check if the source target supports eum/e2e association
  IF (NOT ((is_eume2e_supported(p_source_target_type, ASSOC_DEF_SUPPORTS_EUM_ON) AND supports_eume2e_assoc(p_assoc_target_type, ASSOC_DEF_SUPPORTS_EUM_ON)) 
           OR
           (is_eume2e_supported(p_source_target_type, ASSOC_DEF_SUPPORTS_E2E_ON) AND supports_eume2e_assoc(p_assoc_target_type, ASSOC_DEF_SUPPORTS_E2E_ON))))
  THEN
    RETURN;
  END IF;

  -- if the assoc target is a cluster, delete the eum/e2e association between its members and the source target
  IF (MGMT_TARGET.get_type_property(p_assoc_target_type,MGMT_GLOBAL.G_IS_CLUSTER_PROP,0)=1) THEN
    FOR sub_rec IN eume2e_targets_cur(p_assoc_target_name, p_assoc_target_type)
    LOOP
      IF (is_eume2e_supported(p_source_target_type, sub_rec.assoc_def_name)) THEN
        -- before deleting the association check if it's key component of the website itself
        IF (NOT (other_eume2e_assoc(sub_rec.member_target_name, sub_rec.member_target_type, p_source_target_name, p_source_target_type, sub_rec.assoc_def_name, 1))) THEN
          BEGIN
            MGMT_ASSOC.delete_target_assoc(sub_rec.assoc_def_name, 
                                           p_source_target_name, 
                                           p_source_target_type,
                                           sub_rec.member_target_name,
                                           sub_rec.member_target_type);
          EXCEPTION
            WHEN mgmt_global.assoc_does_not_exist THEN
              IF EMDW_LOG.P_IS_WARN_SET 
              THEN
                EMDW_LOG.WARN('ASSOCIATION '||sub_rec.assoc_def_name||' BETWEEN '||p_source_target_name||' AND '||sub_rec.member_target_name||' DOES NOT EXIST', MODULE_NAME); 
              END IF ;
            WHEN OTHERS THEN
              MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'KEY_DEL_CALLBACK Exception: ' || SQLERRM);    
          END;
        END IF;
      END IF;
    END LOOP;
  ELSE
    -- if assoc target supports eum association, then check if it is associated with
    -- the same source target the same eum association as a member of a key cluster, 
    -- if not, delete this association
    IF (is_eume2e_supported(p_source_target_type, ASSOC_DEF_SUPPORTS_EUM_ON) AND supports_eume2e_assoc(p_assoc_target_type, ASSOC_DEF_SUPPORTS_EUM_ON)) THEN
      IF (NOT (other_eume2e_assoc(p_assoc_target_name, p_assoc_target_type, p_source_target_name, p_source_target_type, ASSOC_DEF_SUPPORTS_EUM_ON, 0))) THEN
        BEGIN
          MGMT_ASSOC.delete_target_assoc(ASSOC_DEF_SUPPORTS_EUM_ON, 
                                         p_source_target_name, 
                                         p_source_target_type,
                                         p_assoc_target_name,
                                         p_assoc_target_type);
        EXCEPTION
          WHEN mgmt_global.assoc_does_not_exist THEN
            IF EMDW_LOG.P_IS_WARN_SET 
            THEN
              EMDW_LOG.WARN('ASSOCIATION '||ASSOC_DEF_SUPPORTS_EUM_ON||' BETWEEN '||p_source_target_name||' AND '||p_assoc_target_name||' DOES NOT EXIST', MODULE_NAME); 
            END IF ;
          WHEN OTHERS THEN
            MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'KEY_DEL_CALLBACK Exception: ' || SQLERRM);    
        END;
      END IF;
    END IF;

    -- if assoc target supports e2e association, then check if it is associated with
    -- the same source target the same e2e association as a member of a key cluster, 
    -- if not, delete this association
    IF (is_eume2e_supported(p_source_target_type, ASSOC_DEF_SUPPORTS_E2E_ON) AND supports_eume2e_assoc(p_assoc_target_type, ASSOC_DEF_SUPPORTS_E2E_ON)) THEN
      IF (NOT (other_eume2e_assoc(p_assoc_target_name, p_assoc_target_type, p_source_target_name, p_source_target_type, ASSOC_DEF_SUPPORTS_E2E_ON, 0))) THEN
        BEGIN
          MGMT_ASSOC.delete_target_assoc(ASSOC_DEF_SUPPORTS_E2E_ON, 
                                         p_source_target_name, 
                                         p_source_target_type,
                                         p_assoc_target_name,
                                         p_assoc_target_type);
        EXCEPTION
          WHEN mgmt_global.assoc_does_not_exist THEN
            IF EMDW_LOG.P_IS_WARN_SET 
            THEN
              EMDW_LOG.WARN('ASSOCIATION '||ASSOC_DEF_SUPPORTS_E2E_ON||' BETWEEN '||p_source_target_name||' AND '||p_assoc_target_name||' DOES NOT EXIST', MODULE_NAME); 
            END IF ;
          WHEN OTHERS THEN
            MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'KEY_DEL_CALLBACK Exception: ' || SQLERRM);    
        END;
      END IF;
    END IF;

  END IF;
  
  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('key_del_callback: Exit', MODULE_NAME) ; 
  END IF ;

EXCEPTION
  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'KEY_DEL_CALLBACK Exception: ' || SQLERRM);    

END key_del_callback;

--
-- PROCEDURE: cluster_mem_del_callback
-- PURPOSE:
--   callback when a contains association between
--   a cluster key component of a Web Application and 
--   the cluster member is deleted
--
PROCEDURE cluster_mem_del_callback(p_assoc_def_name IN VARCHAR2,
                                   p_source_target_name IN VARCHAR2,
                                   p_source_target_type IN VARCHAR2,
                                   p_assoc_target_name IN VARCHAR2,
                                   p_assoc_target_type IN VARCHAR2,
                                   p_scope_target_name IN VARCHAR2,
                                   p_scope_target_type IN VARCHAR2)
IS

-- cursor to get the ancestor of the cluster target
CURSOR cluster_ancestor_cur (p_cluster_tgt_name IN VARCHAR2, p_cluster_tgt_type IN VARCHAR2) IS
    SELECT src_t.target_name, src_t.target_type
    FROM   mgmt_target_assocs a, mgmt_targets assoc_t, mgmt_targets src_t
    WHERE  assoc_t.target_name = p_cluster_tgt_name
      AND  assoc_t.target_type = p_cluster_tgt_type
      AND  assoc_t.target_guid = a.assoc_target_guid
      AND  a.assoc_guid = MGMT_ASSOC.g_depends_on_guid
      AND  src_t.target_guid = a.source_target_guid;

BEGIN

  --emdw_log.set_session_trace_level(3);

  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('cluster_mem_del_callback: Enter', MODULE_NAME) ; 
  END IF ;

  -- loop through all of this cluster's source targets
  FOR sub_rec IN cluster_ancestor_cur(p_source_target_name, p_source_target_type)
    LOOP
      IF (is_eume2e_supported(sub_rec.target_type, ASSOC_DEF_SUPPORTS_EUM_ON) AND supports_eume2e_assoc(p_assoc_target_type, ASSOC_DEF_SUPPORTS_EUM_ON)) THEN
        IF (NOT (other_eume2e_assoc(p_assoc_target_name, p_assoc_target_type, sub_rec.target_name, sub_rec.target_type, ASSOC_DEF_SUPPORTS_EUM_ON, 1))) THEN
          BEGIN
            MGMT_ASSOC.delete_target_assoc(ASSOC_DEF_SUPPORTS_EUM_ON, 
                                           sub_rec.target_name, 
                                           sub_rec.target_type,
                                           p_assoc_target_name,
                                           p_assoc_target_type);
          EXCEPTION
            WHEN mgmt_global.assoc_does_not_exist THEN
              IF EMDW_LOG.P_IS_WARN_SET 
              THEN
                EMDW_LOG.WARN('ASSOCIATION '||ASSOC_DEF_SUPPORTS_EUM_ON||' BETWEEN '||sub_rec.target_name||' AND '||p_assoc_target_name||' DOES NOT EXIST', MODULE_NAME); 
              END IF ;
            WHEN OTHERS THEN
              MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'CLUSTER_MEM_DEL_CALLBACK Exception: ' || SQLERRM);  
          END;
        END IF;
      END IF;

      IF (is_eume2e_supported(sub_rec.target_type, ASSOC_DEF_SUPPORTS_E2E_ON) AND supports_eume2e_assoc(p_assoc_target_type, ASSOC_DEF_SUPPORTS_E2E_ON)) THEN
        IF (NOT (other_eume2e_assoc(p_assoc_target_name, p_assoc_target_type, sub_rec.target_name, sub_rec.target_type, ASSOC_DEF_SUPPORTS_E2E_ON, 1))) THEN
          BEGIN
            MGMT_ASSOC.delete_target_assoc(ASSOC_DEF_SUPPORTS_E2E_ON, 
                                           sub_rec.target_name, 
                                           sub_rec.target_type,
                                           p_assoc_target_name,
                                           p_assoc_target_type);
          EXCEPTION
            WHEN mgmt_global.assoc_does_not_exist THEN
              IF EMDW_LOG.P_IS_WARN_SET 
              THEN
                EMDW_LOG.WARN('ASSOCIATION '||ASSOC_DEF_SUPPORTS_E2E_ON||' BETWEEN '||sub_rec.target_name||' AND '||p_assoc_target_name||' DOES NOT EXIST', MODULE_NAME); 
              END IF ;
            WHEN OTHERS THEN
              MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'CLUSTER_MEM_DEL_CALLBACK Exception: ' || SQLERRM);  
          END;
        END IF;
      END IF;
    END LOOP;

  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('cluster_mem_del_callback: Exit', MODULE_NAME) ; 
  END IF ;

EXCEPTION
  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'CLUSTER_MEM_DEL_CALLBACK Exception: ' || SQLERRM);  

END cluster_mem_del_callback;


--
-- PROCEDURE: cluster_mem_add_callback
-- PURPOSE:
--   callback when a contains association between
--   a cluster key component of a Web Application and 
--   the cluster member is created
--
PROCEDURE cluster_mem_add_callback(p_assoc_def_name IN VARCHAR2,
                                   p_source_target_name IN VARCHAR2,
                                   p_source_target_type IN VARCHAR2,
                                   p_assoc_target_name IN VARCHAR2,
                                   p_assoc_target_type IN VARCHAR2,
                                   p_scope_target_name IN VARCHAR2,
                                   p_scope_target_type IN VARCHAR2)
IS
-- cursor to get the ancestor of the cluster target
CURSOR cluster_ancestor_cur (p_cluster_tgt_name IN VARCHAR2, p_cluster_tgt_type IN VARCHAR2) IS
    SELECT src_t.target_name, src_t.target_type
    FROM   mgmt_target_assocs a, mgmt_targets assoc_t, mgmt_targets src_t
    WHERE  assoc_t.target_name = p_cluster_tgt_name
      AND  assoc_t.target_type = p_cluster_tgt_type
      AND  assoc_t.target_guid = a.assoc_target_guid
      AND  a.assoc_guid = MGMT_ASSOC.g_depends_on_guid
      AND  src_t.target_guid = a.source_target_guid;
  
BEGIN

  --emdw_log.set_session_trace_level(3);

  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('cluster_mem_add_callback: Enter', MODULE_NAME) ; 
  END IF ;

  -- loop through all of this cluster's source targets
  FOR sub_rec IN cluster_ancestor_cur(p_source_target_name, p_source_target_type)
    LOOP
      IF (is_eume2e_supported(sub_rec.target_type, ASSOC_DEF_SUPPORTS_EUM_ON) AND supports_eume2e_assoc(p_assoc_target_type, ASSOC_DEF_SUPPORTS_EUM_ON)) THEN
        BEGIN
          MGMT_ASSOC.create_target_assoc(ASSOC_DEF_SUPPORTS_EUM_ON, 
                                         sub_rec.target_name, 
                                         sub_rec.target_type,
                                         p_assoc_target_name,
                                         p_assoc_target_type);
        EXCEPTION
          WHEN mgmt_global.assoc_already_exist THEN
            IF EMDW_LOG.P_IS_WARN_SET 
            THEN
              EMDW_LOG.WARN('ASSOCIATION '||ASSOC_DEF_SUPPORTS_EUM_ON||' BETWEEN '||sub_rec.target_name||' AND '||p_assoc_target_name||' ALREADY EXISTS', MODULE_NAME); 
            END IF ;
          WHEN OTHERS THEN
            MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'CLUSTER_MEM_ADD_CALLBACK Exception: ' || SQLERRM); 
        END;
      END IF;

      IF (is_eume2e_supported(sub_rec.target_type, ASSOC_DEF_SUPPORTS_E2E_ON) AND supports_eume2e_assoc(p_assoc_target_type, ASSOC_DEF_SUPPORTS_E2E_ON)) THEN
        BEGIN
          MGMT_ASSOC.create_target_assoc(ASSOC_DEF_SUPPORTS_E2E_ON, 
                                         sub_rec.target_name, 
                                         sub_rec.target_type,
                                         p_assoc_target_name,
                                         p_assoc_target_type);
        EXCEPTION
          WHEN mgmt_global.assoc_already_exist THEN
            IF EMDW_LOG.P_IS_WARN_SET 
            THEN
              EMDW_LOG.WARN('ASSOCIATION '||ASSOC_DEF_SUPPORTS_E2E_ON||' BETWEEN '||sub_rec.target_name||' AND '||p_assoc_target_name||' ALREADY EXISTS', MODULE_NAME); 
            END IF ;
          WHEN OTHERS THEN
            MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'CLUSTER_MEM_ADD_CALLBACK Exception: ' || SQLERRM); 
        END;
      END IF;
    END LOOP;

  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('cluster_mem_add_callback: Exit', MODULE_NAME) ; 
  END IF ;

EXCEPTION
  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'CLUSTER_MEM_ADD_CALLBACK Exception: ' || SQLERRM);  

END cluster_mem_add_callback;


--
-- PROCEDURE: del_key_cluster_callback
-- PURPOSE:
--   callback when a oc4j_cluster, oracle_apache_cluster
--   or webcache_cluster is deleted (right before the
--   cluster target is deleted)
--

PROCEDURE del_key_cluster_callback(p_cluster_name IN VARCHAR2,
                                   p_cluster_type IN VARCHAR2,
                                   p_cluster_guid IN RAW)

IS
-- cursor to get the ancestor of the cluster target
CURSOR cluster_ancestor_cur (p_cluster_guid IN RAW) IS
    SELECT src_t.target_name, src_t.target_type
    FROM   mgmt_target_assocs a, mgmt_targets src_t
    WHERE  a.assoc_target_guid = p_cluster_guid
      AND  a.assoc_guid = MGMT_ASSOC.g_depends_on_guid
      AND  src_t.target_guid = a.source_target_guid;

BEGIN

  --emdw_log.set_session_trace_level(3);

  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('del_key_cluster_callback: Enter', MODULE_NAME) ; 
  END IF ;

  -- for each of the cluster's source target, calls the procedure that gets called back
  -- when the depends_on association between the cluster and its parents is removed
  FOR sub_rec IN cluster_ancestor_cur(p_cluster_guid)
    LOOP
      key_del_callback(MGMT_ASSOC.ASSOC_DEF_DEPENDS_ON,
                       sub_rec.target_name,
                       sub_rec.target_type,
                       p_cluster_name,
                       p_cluster_type, 
                       NULL,
                       NULL);
    END LOOP;

  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('del_key_cluster_callback: Exit', MODULE_NAME) ; 
  END IF ;

EXCEPTION
  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'DEL_KEY_CLUSTER_CALLBACK Exception: ' || SQLERRM);  

END del_key_cluster_callback;

--
-- PROCEDURE: apps_key_del_callback
-- PURPOSE:
--   callback when a depends_on association between
--   a key component and apps r12 specific forms service is deleted
--
PROCEDURE apps_key_del_callback(p_assoc_def_name IN VARCHAR2,
                                p_source_target_name IN VARCHAR2,
                                p_source_target_type IN VARCHAR2,
                                p_assoc_target_name IN VARCHAR2,
                                p_assoc_target_type IN VARCHAR2,
                                p_scope_target_name IN VARCHAR2,
                                p_scope_target_type IN VARCHAR2)
IS

  l_source_target_guid RAW(16);
  l_assoc_target_guid RAW(16);

  -- cursor to get the member targets of the group that has eum/e2e association support
  CURSOR member_cur (p_group_tgt_guid IN RAW) IS
    SELECT assoc_t.target_name, assoc_t.target_type, assoc_t.target_guid
    FROM   mgmt_flat_target_assoc flat_assoc, 
           mgmt_targets assoc_t, 
           eume2e_assocs_lookup l
    WHERE  flat_assoc.source_target_guid = p_group_tgt_guid
      AND  flat_assoc.assoc_target_guid = assoc_t.target_guid
      AND  flat_assoc.is_membership = 1
      AND  assoc_t.target_type = l.target_type
      AND  l.is_group = 0
      AND  l.assoc_guid = supports_eum_on_guid;
BEGIN
  
  --emdw_log.set_session_trace_level(3);

  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('apps_key_del_callback: Enter', MODULE_NAME) ; 
  END IF ;

  IF ((( p_source_target_name IS NULL ) OR (p_source_target_type IS NULL ))
      OR
      (( p_assoc_target_name IS NULL ) OR (p_assoc_target_type IS NULL ))) 
  THEN
        return;
  END IF;

  -- check if the source target supports eum/e2e association
  IF (NOT (is_eume2e_supported(p_source_target_type, ASSOC_DEF_SUPPORTS_EUM_ON) AND supports_eume2e_assoc(p_assoc_target_type, ASSOC_DEF_SUPPORTS_EUM_ON)))
  THEN
    RETURN;
  END IF;

  SELECT target_guid 
    INTO l_source_target_guid
    FROM mgmt_targets
   WHERE target_name = p_source_target_name
     AND target_type = p_source_target_type;

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

  -- if the assoc target is a group type, delete the eum/e2e association between its members and the source target
  IF (IS_GROUP(p_assoc_target_type)) THEN
    FOR sub_rec IN member_cur(l_assoc_target_guid)
    LOOP
      IF (eume2e_assoc_exists(l_source_target_guid, sub_rec.target_guid, supports_eum_on_guid)) THEN
        -- before deleting the association check if it's key component of the website itself
        IF (NOT (is_direct_key(l_source_target_guid, sub_rec.target_guid) OR is_key_member(l_source_target_guid, sub_rec.target_guid))) THEN
          BEGIN
            MGMT_ASSOC.delete_target_assoc(ASSOC_DEF_SUPPORTS_EUM_ON, 
                                           p_source_target_name, 
                                           p_source_target_type,
                                           sub_rec.target_name,
                                           sub_rec.target_type);
          EXCEPTION
            WHEN mgmt_global.assoc_does_not_exist THEN
              IF EMDW_LOG.P_IS_WARN_SET 
              THEN
                EMDW_LOG.WARN('ASSOCIATION '||ASSOC_DEF_SUPPORTS_EUM_ON||' BETWEEN '||p_source_target_name||' AND '||sub_rec.target_name||' DOES NOT EXIST', MODULE_NAME); 
              END IF ;
            WHEN OTHERS THEN
              MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'APPS_KEY_DEL_CALLBACK Exception: ' || SQLERRM);    
          END;
        END IF;
      END IF;
    END LOOP;
  ELSE
    -- if assoc target supports eum association, then check if it is associated with
    -- the same source target the same eum association as a member of a key cluster, 
    -- if not, delete this association
    IF (eume2e_assoc_exists(l_source_target_guid, l_assoc_target_guid, supports_eum_on_guid)) THEN
      IF (NOT (is_key_member(l_source_target_guid, l_assoc_target_guid))) THEN
        BEGIN
          MGMT_ASSOC.delete_target_assoc(ASSOC_DEF_SUPPORTS_EUM_ON, 
                                         p_source_target_name, 
                                         p_source_target_type,
                                         p_assoc_target_name,
                                         p_assoc_target_type);
        EXCEPTION
          WHEN mgmt_global.assoc_does_not_exist THEN
            IF EMDW_LOG.P_IS_WARN_SET 
            THEN
              EMDW_LOG.WARN('ASSOCIATION '||ASSOC_DEF_SUPPORTS_EUM_ON||' BETWEEN '||p_source_target_name||' AND '||p_assoc_target_name||' DOES NOT EXIST', MODULE_NAME); 
            END IF ;
          WHEN OTHERS THEN
            MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'APPS_KEY_DEL_CALLBACK Exception: ' || SQLERRM);    
        END;
      END IF;
    END IF;

  END IF;
  
  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('apps_key_del_callback: Exit', MODULE_NAME) ; 
  END IF ;

EXCEPTION
  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'APPS_KEY_DEL_CALLBACK Exception: ' || SQLERRM);    

END apps_key_del_callback;

--
-- PROCEDURE: apps_cluster_mem_del_callback
-- PURPOSE:
--   callback when a contains association between
--   a apps specific group key component of apps forms service and 
--   the group member is deleted
--
PROCEDURE apps_cluster_mem_del_callback(p_assoc_def_name IN VARCHAR2,
                                        p_source_target_name IN VARCHAR2,
                                        p_source_target_type IN VARCHAR2,
                                        p_assoc_target_name IN VARCHAR2,
                                        p_assoc_target_type IN VARCHAR2,
                                        p_scope_target_name IN VARCHAR2,
                                        p_scope_target_type IN VARCHAR2)
IS
  l_assoc_target_guid RAW(16);

  -- cursor to get the member targets of the cluster that has eum/e2e association support
  CURSOR member_cur (p_target_guid IN RAW) IS
    SELECT member_t.target_name, member_t.target_type,
           member_t.target_guid
      FROM mgmt_flat_target_assoc flat_assoc, 
           mgmt_targets member_t,
           eume2e_assocs_lookup l
     WHERE flat_assoc.source_target_guid = p_target_guid
       AND flat_assoc.assoc_target_guid = member_t.target_guid
       AND flat_assoc.is_membership = 1
       AND member_t.target_type = l.target_type
       AND l.is_group = 0
       AND l.assoc_guid = supports_eum_on_guid;

  -- All service targets that have eum/e2e association with the passed in target
  CURSOR service_cur (p_assoc_tgt_guid IN RAW) IS
    SELECT svc_t.target_name, svc_t.target_type, svc_t.target_guid
      FROM mgmt_target_assocs assoc, 
           mgmt_targets svc_t, 
           mgmt_targets ins_t, 
           eume2e_assocs_lookup l
     WHERE assoc.source_target_guid = svc_t.target_guid
       AND assoc.assoc_target_guid = ins_t.target_guid
       AND ins_t.target_guid = p_assoc_tgt_guid
       AND ins_t.target_type = l.target_type
       AND assoc.assoc_guid = supports_eum_on_guid;
BEGIN

  --emdw_log.set_session_trace_level(3);

  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('apps_cluster_mem_del_callback: Enter', MODULE_NAME) ; 
  END IF ;

  IF ((( p_source_target_name IS NULL ) OR (p_source_target_type IS NULL ))
      OR
      (( p_assoc_target_name IS NULL ) OR (p_assoc_target_type IS NULL ))) 
  THEN
        return;
  END IF;

  IF (NOT (supports_eume2e_assoc(p_source_target_type, ASSOC_DEF_SUPPORTS_EUM_ON) AND supports_eume2e_assoc(p_assoc_target_type, ASSOC_DEF_SUPPORTS_EUM_ON)))
  THEN
    RETURN;
  END IF;

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

  -- loop through all of this cluster's source targets
  IF (IS_GROUP(p_assoc_target_type)) THEN
    FOR sub_rec IN member_cur(l_assoc_target_guid)
    LOOP
      FOR sub_rec_svc IN service_cur (sub_rec.target_guid)
      LOOP
        IF (NOT (is_direct_key(sub_rec_svc.target_guid, sub_rec.target_guid) OR is_key_member(sub_rec_svc.target_guid, sub_rec.target_guid))) THEN
          BEGIN
            MGMT_ASSOC.delete_target_assoc(ASSOC_DEF_SUPPORTS_EUM_ON,
                                           sub_rec_svc.target_name, 
                                           sub_rec_svc.target_type,
                                           sub_rec.target_name,
                                           sub_rec.target_type);
            EXCEPTION
              WHEN mgmt_global.assoc_does_not_exist THEN
               IF EMDW_LOG.P_IS_WARN_SET 
               THEN
                 EMDW_LOG.WARN('ASSOCIATION '||ASSOC_DEF_SUPPORTS_EUM_ON||' BETWEEN '||sub_rec_svc.target_name||' AND '||sub_rec.target_name||' DOES NOT EXIST', MODULE_NAME); 
               END IF ;
             WHEN OTHERS THEN
               MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'APPS_CLUSTER_MEM_DEL_CALLBACK Exception: ' || SQLERRM);  
          END;
        END IF;
      END LOOP;
    END LOOP;
  ELSE
    FOR sub_rec_svc IN service_cur (l_assoc_target_guid)
    LOOP
      IF (NOT (is_direct_key(sub_rec_svc.target_guid, l_assoc_target_guid) OR is_key_member(sub_rec_svc.target_guid, l_assoc_target_guid))) THEN
        BEGIN
          MGMT_ASSOC.delete_target_assoc(ASSOC_DEF_SUPPORTS_EUM_ON,
                                         sub_rec_svc.target_name, 
                                         sub_rec_svc.target_type,
                                         p_assoc_target_name,
                                         p_assoc_target_type);
          EXCEPTION
            WHEN mgmt_global.assoc_does_not_exist THEN
             IF EMDW_LOG.P_IS_WARN_SET 
             THEN
               EMDW_LOG.WARN('ASSOCIATION '||ASSOC_DEF_SUPPORTS_EUM_ON||' BETWEEN '||sub_rec_svc.target_name||' AND '||p_assoc_target_name||' DOES NOT EXIST', MODULE_NAME); 
             END IF ;
            WHEN OTHERS THEN
             MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'APPS_CLUSTER_MEM_DEL_CALLBACK Exception: ' || SQLERRM);  
        END;
      END IF;
    END LOOP;
  END IF;

  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('apps_cluster_mem_del_callback: Exit', MODULE_NAME) ; 
  END IF ;

EXCEPTION
  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'APPS_CLUSTER_MEM_DEL_CALLBACK Exception: ' || SQLERRM);  

END apps_cluster_mem_del_callback;


--
-- PROCEDURE: apps_cluster_mem_add_callback
-- PURPOSE:
--   callback when a contains association between
--   a cluster key component of a Web Application and 
--   the cluster member is created
--
PROCEDURE apps_cluster_mem_add_callback(p_assoc_def_name IN VARCHAR2,
                                        p_source_target_name IN VARCHAR2,
                                        p_source_target_type IN VARCHAR2,
                                        p_assoc_target_name IN VARCHAR2,
                                        p_assoc_target_type IN VARCHAR2,
                                        p_scope_target_name IN VARCHAR2,
                                        p_scope_target_type IN VARCHAR2)
IS

  l_source_target_guid RAW(16);
  l_assoc_target_guid RAW(16);
  l_members_list SMP_EMD_NVPAIR_ARRAY;
  l_member_guid RAW(16);
  
  -- Cursor to find the service targets that depend on this target
  -- and the service targets that depend on the parents of this target
  -- Note that the parents need to be the eum/e2e enabled group target types
  CURSOR parent_service_cur (p_target_guid IN RAW) IS
    SELECT svc_t.target_name, svc_t.target_type, svc_t.target_guid
      FROM mgmt_target_assocs a, 
           mgmt_targets svc_t
     WHERE a.assoc_target_guid = p_target_guid
       AND a.assoc_guid = MGMT_ASSOC.g_depends_on_guid
       AND a.source_target_guid = svc_t.target_guid
   UNION
    SELECT svc_t2.target_name, svc_t2.target_type, svc_t2.target_guid
    FROM   mgmt_target_assocs assoc, 
           mgmt_flat_target_assoc flat_assoc,
           mgmt_targets svc_t2, 
           eume2e_assocs_lookup l,
           mgmt_targets parent_t
    WHERE  flat_assoc.assoc_target_guid = p_target_guid
      AND  flat_assoc.is_membership = 1
      AND  flat_assoc.source_target_guid = assoc.assoc_target_guid
      AND  assoc.assoc_guid = MGMT_ASSOC.g_depends_on_guid
      AND  assoc.source_target_guid = svc_t2.target_guid
      AND  assoc.assoc_target_guid = parent_t.target_guid
      AND  parent_t.target_type = l.target_type;

  -- Cursor to find all eum/e2e instance targets that are contained by this target
  CURSOR member_target_cur (p_target_guid IN RAW) IS
    SELECT SMP_EMD_NVPAIR(member_t.target_name, member_t.target_type)
      FROM mgmt_flat_target_assoc flat_assoc, 
           mgmt_targets member_t,
           eume2e_assocs_lookup l
     WHERE flat_assoc.source_target_guid = p_target_guid
       AND flat_assoc.assoc_target_guid = member_t.target_guid
       AND flat_assoc.is_membership = 1
       AND member_t.target_type = l.target_type
       AND l.is_group = 0
       AND l.assoc_guid = supports_eum_on_guid;

BEGIN

  --emdw_log.set_session_trace_level(3);

  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('apps_cluster_mem_add_callback: Enter', MODULE_NAME) ; 
  END IF ;

  IF ((( p_source_target_name IS NULL ) OR (p_source_target_type IS NULL ))
      OR
      (( p_assoc_target_name IS NULL ) OR (p_assoc_target_type IS NULL ))) 
  THEN
        return;
  END IF;

  IF (NOT (supports_eume2e_assoc(p_source_target_type, ASSOC_DEF_SUPPORTS_EUM_ON) AND supports_eume2e_assoc(p_assoc_target_type, ASSOC_DEF_SUPPORTS_EUM_ON)))
  THEN
    RETURN;
  END IF;

  SELECT target_guid 
    INTO l_source_target_guid
    FROM mgmt_targets
   WHERE target_name = p_source_target_name
     AND target_type = p_source_target_type;

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

  -- loop through all of this cluster's source targets
  IF (IS_GROUP(p_assoc_target_type)) THEN

    OPEN member_target_cur(l_assoc_target_guid);
    FETCH member_target_cur BULK COLLECT INTO l_members_list;
    CLOSE member_target_cur;

    IF(NOT l_members_list.EXISTS(1)) THEN
      RETURN;
    END IF;

    FOR sub_rec_svc IN parent_service_cur(l_source_target_guid)
    LOOP
      FOR i IN l_members_list.FIRST..l_members_list.LAST 
      LOOP
        SELECT target_guid
          INTO l_member_guid
          FROM mgmt_targets
         WHERE target_name = l_members_list(i).name
           AND target_type = l_members_list(i).value;

        IF ((NOT (eume2e_assoc_exists(sub_rec_svc.target_guid, l_member_guid, supports_eum_on_guid)))
            AND (IS_EUME2E_SUPPORTED(sub_rec_svc.target_type, ASSOC_DEF_SUPPORTS_EUM_ON))) THEN
          BEGIN
            MGMT_ASSOC.create_target_assoc(ASSOC_DEF_SUPPORTS_EUM_ON, 
                                           sub_rec_svc.target_name, 
                                           sub_rec_svc.target_type,
                                           l_members_list(i).name, 
                                           l_members_list(i).value);
          EXCEPTION
            WHEN mgmt_global.assoc_already_exist THEN
              IF EMDW_LOG.P_IS_WARN_SET 
              THEN
                EMDW_LOG.WARN('ASSOCIATION '||ASSOC_DEF_SUPPORTS_EUM_ON||' BETWEEN '||sub_rec_svc.target_name||' AND '||l_members_list(i).name||' ALREADY EXISTS', MODULE_NAME); 
              END IF ;
            WHEN OTHERS THEN
              MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'APPS_CLUSTER_MEM_ADD_CALLBACK Exception: ' || SQLERRM); 
          END;    
        END IF;
      END LOOP;
    END LOOP;
  ELSE
    FOR sub_rec_svc IN parent_service_cur(l_source_target_guid)
    LOOP
      IF ((NOT (eume2e_assoc_exists(sub_rec_svc.target_guid, l_assoc_target_guid, supports_eum_on_guid)))
          AND (IS_EUME2E_SUPPORTED(sub_rec_svc.target_type, ASSOC_DEF_SUPPORTS_EUM_ON))) THEN
        BEGIN
          MGMT_ASSOC.create_target_assoc(ASSOC_DEF_SUPPORTS_EUM_ON, 
                                         sub_rec_svc.target_name, 
                                         sub_rec_svc.target_type,
                                         p_assoc_target_name, 
                                         p_assoc_target_type);
        EXCEPTION
          WHEN mgmt_global.assoc_already_exist THEN
            IF EMDW_LOG.P_IS_WARN_SET 
            THEN
              EMDW_LOG.WARN('ASSOCIATION '||ASSOC_DEF_SUPPORTS_EUM_ON||' BETWEEN '||sub_rec_svc.target_name||' AND '||p_assoc_target_name||' ALREADY EXISTS', MODULE_NAME); 
            END IF ;
          WHEN OTHERS THEN
            MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'APPS_CLUSTER_MEM_ADD_CALLBACK Exception: ' || SQLERRM); 
        END;    
      END IF;   
    END LOOP;
  END IF;


  IF EMDW_LOG.P_IS_INFO_SET 
  THEN
    EMDW_LOG.INFO('apps_cluster_mem_add_callback: Exit', MODULE_NAME) ; 
  END IF ;

EXCEPTION
  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'APPS_CLUSTER_MEM_ADD_CALLBACK Exception: ' || SQLERRM);  

END apps_cluster_mem_add_callback;
-- Callback method which removes the EM page customization for a website
--
--
PROCEDURE WEBSITE_EMCUSTOM_DEL_CALLBACK( p_target_name IN VARCHAR2,
                                                 p_target_type VARCHAR2,
                                                 p_target_guid RAW )
IS
BEGIN
        IF EMDW_LOG.P_IS_INFO_SET
        THEN
                EMDW_LOG.INFO('WEBSITE_EMCUSTOM_DEL_CALLBACK: Enter', MODULE_NAME) ;
        END IF ;        

        -- EM Page customization data removal
        MGMT_PAGE_CUSTMZN.remove_page_customzn_condns (
                         p_page_name => 'GENERAL_PAGE',
                         p_customizations => SMP_EMD_NVPAIR_ARRAY(SMP_EMD_NVPAIR('ALLOW_MEMBER_EDIT','false')),
                         p_conditions => SMP_EMD_NVPAIR_ARRAY(SMP_EMD_NVPAIR('type','website'),
                                         SMP_EMD_NVPAIR('target',p_target_name) ) );
        IF EMDW_LOG.P_IS_INFO_SET
        THEN
                EMDW_LOG.INFO('WEBSITE_EMCUSTOM_DEL_CALLBACK: Exit', MODULE_NAME) ;
        END IF ;

EXCEPTION
  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'WEBSITE_EMCUSTOM_DEL_CALLBACK Exception: ' || SQLERRM);  
END WEBSITE_EMCUSTOM_DEL_CALLBACK;



-- PROCEDURE: DEL_OLD_EUME2E_ASSOCS
--
-- PURPOSE
--   Remove old EUM/E2E associations
-- PARAMETERS
--    p_target_name : target name
--    p_target_type : type
--    p_assoc_def_name : supports_eum_on or supports_e2e_on association
--
PROCEDURE DEL_OLD_EUME2E_ASSOCS(p_target_name IN VARCHAR2,
                                p_target_type IN VARCHAR2,
                                p_assoc_def_name IN VARCHAR2)
IS

  l_target_guid RAW(16);

  CURSOR old_eume2e_assoc_cur (p_src_tgt_guid IN RAW, p_assoc_def_name IN VARCHAR2) IS
    SELECT assoc_t.target_name, assoc_t.target_type
      FROM mgmt_target_assocs a, 
           mgmt_targets assoc_t, 
           mgmt_target_assoc_defs d
     WHERE a.source_target_guid = p_src_tgt_guid
       AND a.assoc_target_guid = assoc_t.target_guid
       AND a.assoc_guid = d.assoc_guid
       AND d.assoc_def_name = p_assoc_def_name;

BEGIN

    --emdw_log.set_session_trace_level(3);

    IF EMDW_LOG.P_IS_INFO_SET 
    THEN
      EMDW_LOG.INFO('del_old_eume2e_assocs: Enter', MODULE_NAME) ; 
    END IF ;
         
    IF ( p_target_name IS NULL ) OR (p_target_type IS NULL ) THEN
        return;
    END IF;

    IF (NOT (IS_EUME2E_SUPPORTED(p_target_type, p_assoc_def_name))) THEN
        return;
    END IF;

    SELECT target_guid 
      INTO l_target_guid
      FROM mgmt_targets
     WHERE target_name = p_target_name
       AND target_type = p_target_type;

    FOR sub_rec IN old_eume2e_assoc_cur(l_target_guid, p_assoc_def_name)
    LOOP
      BEGIN
        MGMT_ASSOC.delete_target_assoc(p_assoc_def_name, 
                                       p_target_name,
                                       p_target_type,
                                       sub_rec.target_name,
                                       sub_rec.target_type);
      EXCEPTION
        WHEN mgmt_global.assoc_does_not_exist THEN
          IF EMDW_LOG.P_IS_WARN_SET 
          THEN
            EMDW_LOG.WARN('ASSOCIATION '||p_assoc_def_name||' BETWEEN '||p_target_name||' AND '||sub_rec.target_name||' DOES NOT EXIST', MODULE_NAME); 
          END IF ;
        WHEN OTHERS THEN
          MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'DEL_OLD_EUME2E_ASSOCS Exception: ' || SQLERRM);  
      END;
        
    END LOOP;    

    IF EMDW_LOG.P_IS_INFO_SET 
    THEN
      EMDW_LOG.INFO('del_old_eume2e_assocs: Exit', MODULE_NAME) ; 
    END IF ;

EXCEPTION
  WHEN OTHERS THEN
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'DEL_OLD_EUME2E_ASSOCS Exception: ' || SQLERRM);  

END DEL_OLD_EUME2E_ASSOCS;

-- PROCEDURE: ADD_EUME2E_ASSOCIATIONS
--
-- PURPOSE
--   Creates EUM/E2E associations
-- PARAMETERS
--    p_target_name    : target name
--    p_target_type    : type
--    p_assoc_def_name : supports_eum_on or supports_e2e_on association
--
PROCEDURE ADD_EUME2E_ASSOCIATIONS(p_target_name IN VARCHAR2,
                                  p_target_type IN VARCHAR2,
                                  p_assoc_def_name IN VARCHAR2)
IS

  l_target_guid RAW(16);

  l_assoc_guid RAW(16);
  
  CURSOR eume2e_assoc_cur (p_src_tgt_guid IN RAW, p_assoc_guid IN RAW) IS
    SELECT assoc_t.target_name, assoc_t.target_type, assoc_t.target_guid
      FROM mgmt_target_assocs a, 
           mgmt_targets assoc_t,
           eume2e_assocs_lookup l
     WHERE a.source_target_guid = p_src_tgt_guid
       AND a.assoc_target_guid = assoc_t.target_guid
       AND a.assoc_guid = MGMT_ASSOC.g_depends_on_guid
       AND assoc_t.target_type = l.target_type
       AND l.assoc_guid = p_assoc_guid
       AND l.is_group = 0
   UNION
    SELECT c_m.member_target_name, c_m.member_target_type, c_m.member_target_guid as target_guid
      FROM mgmt_target_memberships c_m, 
           eume2e_assocs_lookup c_l
     WHERE c_m.member_target_type = c_l.target_type
       AND c_l.assoc_guid = p_assoc_guid
       AND c_l.is_group = 0
       AND c_m.composite_target_guid IN 
             (SELECT assoc_t2.target_guid  
              FROM   mgmt_target_assocs a2, 
                     mgmt_targets assoc_t2,
                     eume2e_assocs_lookup l2
              WHERE a2.source_target_guid = p_src_tgt_guid
                AND a2.assoc_target_guid = assoc_t2.target_guid
                AND a2.assoc_guid = MGMT_ASSOC.g_depends_on_guid
                AND assoc_t2.target_type = l2.target_type
                AND l2.is_group = 1)
   UNION
    SELECT assoc_t.target_name, assoc_t.target_type, assoc_t.target_guid
      FROM mgmt_flat_target_assoc flat_assoc, 
           eume2e_assocs_lookup c_l,
           mgmt_targets assoc_t
     WHERE flat_assoc.assoc_target_guid = assoc_t.target_guid
       AND assoc_t.target_type = c_l.target_type
       AND c_l.assoc_guid = p_assoc_guid
       AND c_l.is_group = 0
       AND flat_assoc.is_membership = 1
       AND flat_assoc.source_target_guid IN 
             (SELECT a2.assoc_target_guid  
              FROM   mgmt_target_assocs a2,
                     mgmt_targets assoc_t2,
                     eume2e_assocs_lookup l2
              WHERE a2.source_target_guid = p_src_tgt_guid
                AND a2.assoc_guid = MGMT_ASSOC.g_depends_on_guid
                AND a2.assoc_target_guid = assoc_t2.target_guid
                AND assoc_t2.target_type = l2.target_type
                AND l2.is_group = 1
                AND l2.expand_group = 1);
       
BEGIN

    --emdw_log.set_session_trace_level(3);

    IF EMDW_LOG.P_IS_INFO_SET 
    THEN
      EMDW_LOG.INFO('add_eume2e_associations: Enter', MODULE_NAME) ; 
    END IF ;
         
    IF ( p_target_name IS NULL ) OR (p_target_type IS NULL ) THEN
        return;
    END IF;

    IF (NOT (IS_EUME2E_SUPPORTED(p_target_type, p_assoc_def_name))) THEN
        return;
    END IF;

    SELECT target_guid 
      INTO l_target_guid
      FROM mgmt_targets
     WHERE target_name = p_target_name
       AND target_type = p_target_type;

    SELECT assoc_guid
      INTO l_assoc_guid
      FROM mgmt_target_assoc_defs
     WHERE assoc_def_name = p_assoc_def_name;

    FOR sub_rec IN eume2e_assoc_cur(l_target_guid, l_assoc_guid)
    LOOP
      IF (NOT (eume2e_assoc_exists(l_target_guid, sub_rec.target_guid, l_assoc_guid))) THEN
        BEGIN
          MGMT_ASSOC.create_target_assoc(p_assoc_def_name, 
                                         p_target_name,
                                         p_target_type,
                                         sub_rec.target_name,
                                         sub_rec.target_type);
        EXCEPTION
          WHEN mgmt_global.assoc_already_exist THEN
            IF EMDW_LOG.P_IS_WARN_SET 
            THEN
              EMDW_LOG.WARN('ASSOCIATION '||p_assoc_def_name||' BETWEEN '||p_target_name||' AND '||sub_rec.target_name||' ALREADY EXISTS', MODULE_NAME); 
            END IF ;
          WHEN OTHERS THEN
            MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'ADD_EUME2E_ASSOCIATIONS Exception: ' || SQLERRM); 
        END;

      END IF;
    END LOOP;    

    IF EMDW_LOG.P_IS_INFO_SET 
    THEN
      EMDW_LOG.INFO('add_eume2e_associations: Exit', MODULE_NAME) ; 
    END IF ;

EXCEPTION
  WHEN OTHERS THEN 
    MGMT_LOG.LOG_ERROR(MODULE_NAME, NULL, 'ADD_EUME2E_ASSOCIATIONS Exception: ' || SQLERRM);

END ADD_EUME2E_ASSOCIATIONS;

--
-- FUNCTION: IS_EUME2E_SUPPORTED
-- PURPOSE:
--   Determines if the specified service type supports EUM/E2E associations
-- IN PARAMETERS
--   p_service_type:   Service type.
--   p_assoc_def_name: supports_eum_on or supports_e2e_on association
-- RETURN
--    TRUE if yes; FALSE otherwise
--
FUNCTION IS_EUME2E_SUPPORTED(p_service_type IN VARCHAR2,
                             p_assoc_def_name IN VARCHAR2)
                    RETURN BOOLEAN
IS
  l_assoc_prop_name VARCHAR2(200);
  l_is_supported NUMBER;
BEGIN
  IF (p_assoc_def_name = ASSOC_DEF_SUPPORTS_EUM_ON) THEN
    l_assoc_prop_name := SUPPORTS_EUM_PROP;
  ELSIF (p_assoc_def_name = ASSOC_DEF_SUPPORTS_E2E_ON) THEN
    l_assoc_prop_name := SUPPORTS_E2E_PROP;
  ELSE
    return FALSE;
  END IF;

  SELECT count(1)
    INTO l_is_supported
    FROM mgmt_type_properties
   WHERE target_type = p_service_type
     AND property_name = l_assoc_prop_name
     AND property_value = '1';

  IF (l_is_supported > 0)
  THEN
    return TRUE;
  ELSE
    return FALSE;
  END IF;
 
END IS_EUME2E_SUPPORTED;

--
-- FUNCTION: SUPPORTS_EUME2E_ASSOC
-- PURPOSE:
--   check if a target type supports eum/e2e association
--   returns TRUE if it does, returns FALSE otherwise
--
FUNCTION SUPPORTS_EUME2E_ASSOC(p_target_type IN VARCHAR2,
                               p_assoc_def_name IN VARCHAR2)
                    RETURN BOOLEAN
IS
  l_support NUMBER;
  l_assoc_guid RAW(16);
BEGIN

  IF (p_assoc_def_name = ASSOC_DEF_SUPPORTS_EUM_ON) THEN
    l_assoc_guid := supports_eum_on_guid;
  ELSIF (p_assoc_def_name = ASSOC_DEF_SUPPORTS_E2E_ON) THEN
    l_assoc_guid := supports_e2e_on_guid;
  ELSE
    return FALSE;
  END IF;

  SELECT count(1)
    INTO l_support
    FROM eume2e_assocs_lookup
   WHERE target_type = p_target_type
     AND assoc_guid = l_assoc_guid;
  
  IF (l_support > 0) THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

END SUPPORTS_EUME2E_ASSOC;

--
-- FUNCTION: IS_GROUP
-- PURPOSE:
--   Determines if the specified target type is a group, cluster, or aggregate
-- IN PARAMETERS
--   p_target_type: type of the target
-- RETURN
--    TRUE if yes; FALSE otherwise
--
FUNCTION IS_GROUP(p_target_type IN VARCHAR2)
                    RETURN BOOLEAN
IS
  l_is_group NUMBER;
BEGIN
  SELECT count(1)
    INTO l_is_group
    FROM mgmt_type_properties
   WHERE target_type = p_target_type
     AND property_name IN (MGMT_GLOBAL.G_IS_CLUSTER_PROP, MGMT_GLOBAL.G_IS_AGGREGATE_PROP, MGMT_GLOBAL.G_IS_GROUP_PROP, MGMT_GLOBAL.G_IS_COMPOSITE_PROP)
     AND property_value = '1';

  IF (l_is_group > 0)
  THEN
    return TRUE;
  ELSE
    return FALSE;
  END IF;
 
END IS_GROUP;


--
-- FUNCTION: IS_DIRECT_KEY
-- PURPOSE:
--   check if the associate target is a direct key component
--   of the source target
-- 
FUNCTION IS_DIRECT_KEY(p_source_target_guid IN RAW,
                       p_assoc_target_guid IN RAW)
                    RETURN BOOLEAN
IS
  is_direct_key NUMBER;
BEGIN
  SELECT count(1) 
    INTO is_direct_key
    FROM MGMT_TARGET_ASSOCS assoc
   WHERE assoc.source_target_guid = p_source_target_guid
     AND assoc.assoc_target_guid = p_assoc_target_guid
     AND assoc.assoc_guid = MGMT_ASSOC.g_depends_on_guid;

IF (is_direct_key > 0) THEN
  RETURN TRUE;
ELSE
  RETURN FALSE;
END IF;

END IS_DIRECT_KEY;


--
-- FUNCTION: IS_KEY_MEMBER
-- PURPOSE:
--   check if the associate target is a member of a direct key component
--   of the source target
-- 
FUNCTION IS_KEY_MEMBER(p_source_target_guid IN RAW,
                       p_assoc_target_guid in RAW)
                    RETURN BOOLEAN
IS
  is_key_member NUMBER;
BEGIN
    SELECT count(1)
      INTO is_key_member
      FROM mgmt_flat_target_assoc flat_assoc
     WHERE flat_assoc.assoc_target_guid = p_assoc_target_guid
       AND flat_assoc.is_membership = 1
       AND flat_assoc.source_target_guid IN
             (SELECT assoc_t2.target_guid
              FROM   mgmt_target_assocs a2,
                     mgmt_targets assoc_t2,
                     eume2e_assocs_lookup l2
              WHERE a2.source_target_guid = p_source_target_guid
                AND a2.assoc_guid = MGMT_ASSOC.g_depends_on_guid
                AND a2.assoc_target_guid = assoc_t2.target_guid
                AND assoc_t2.target_type = l2.target_type);

IF (is_key_member > 0) THEN
  RETURN TRUE;
ELSE
  RETURN FALSE;
END IF;

END IS_KEY_MEMBER;


--
-- FUNCTION: EUME2E_ASSOC_EXISTS
-- PURPOSE:
--   check if the associate target is already associated with
--   the source target the eum/e2e association
-- 
FUNCTION EUME2E_ASSOC_EXISTS(p_source_target_guid IN RAW,
                             p_assoc_target_guid IN RAW,
                             p_assoc_guid IN RAW)
                    RETURN BOOLEAN
IS
  assoc_exists NUMBER;
BEGIN
    SELECT count(1)
      INTO assoc_exists
      FROM mgmt_target_assocs assoc
     WHERE assoc.source_target_guid = p_source_target_guid
       AND assoc.assoc_target_guid = p_assoc_target_guid
       AND assoc.assoc_guid = p_assoc_guid;

IF (assoc_exists > 0) THEN
  RETURN TRUE;
ELSE
  RETURN FALSE;
END IF;

END EUME2E_ASSOC_EXISTS;


--
-- FUNCTION: other_eume2e_assoc
-- PURPOSE:
--   check if a target is associated with the same website
--   in the same eum/e2e association in another way
--   Example: If the associated target as a member of a key
--            cluster is associated with the website with eum 
--            association, check if it is also a key component
--            of the website itself which may also associate 
--            it with the website with eum association. In this
--            case removing either the member of the cluster
--            or key component association should not remove
--            the eum association
--          
-- 

FUNCTION OTHER_EUME2E_ASSOC(p_assoc_target_name IN VARCHAR2, 
                            p_assoc_target_type IN VARCHAR2, 
                            p_src_target_name IN VARCHAR2, 
                            p_src_target_type IN VARCHAR2, 
                            p_assoc_def_name IN VARCHAR2,
                            p_is_cluster IN NUMBER)
                    RETURN BOOLEAN
IS
  has_other_eume2e_assoc NUMBER;
BEGIN

  -- check if the assoc_target is a also member of a key cluster
  IF (p_is_cluster = 0) THEN
    SELECT count(c_t.target_guid)
      INTO has_other_eume2e_assoc
      FROM mgmt_target_memberships c_m, mgmt_targets c_t,
           eume2e_assocs_lookup l, mgmt_target_assoc_defs d
     WHERE c_m.member_target_guid = c_t.target_guid
       AND c_m.member_target_name = p_assoc_target_name
       AND c_m.member_target_type = p_assoc_target_type
         AND c_m.member_target_type = l.target_type
         AND l.assoc_guid = d.assoc_guid
         AND d.assoc_def_name = p_assoc_def_name
       AND c_m.composite_target_guid IN
             (SELECT assoc_t2.target_guid  
              FROM   mgmt_target_assocs a2, mgmt_targets assoc_t2,
                     mgmt_targets src_t2, eume2e_assocs_lookup l2, 
                     mgmt_type_properties tp2, mgmt_target_assoc_defs d2
              WHERE src_t2.target_name = p_src_target_name 
                AND src_t2.target_type = p_src_target_type
                AND a2.source_target_guid = src_t2.target_guid
                AND a2.assoc_target_guid = assoc_t2.target_guid
                AND a2.assoc_guid = MGMT_ASSOC.g_depends_on_guid
                AND assoc_t2.target_type = l2.target_type
                AND l2.assoc_guid = d2.assoc_guid
                AND d2.assoc_def_name = p_assoc_def_name
                AND tp2.target_type = assoc_t2.target_type
                AND tp2.property_name = MGMT_GLOBAL.G_IS_CLUSTER_PROP
                AND tp2.property_value = '1');
  -- check if the assoc_target is also a key component of the source target
  ELSE
     SELECT count(assoc_t.target_guid)
       INTO has_other_eume2e_assoc
       FROM mgmt_target_assocs a, mgmt_targets assoc_t,
            mgmt_targets src_t
      WHERE src_t.target_name = p_src_target_name 
        AND src_t.target_type = p_src_target_type
        AND a.source_target_guid = src_t.target_guid
        AND a.assoc_target_guid = assoc_t.target_guid
        AND a.assoc_guid = MGMT_ASSOC.g_depends_on_guid
        AND assoc_t.target_name = p_assoc_target_name
        AND assoc_t.target_type = p_assoc_target_type;
  END IF;

  IF (has_other_eume2e_assoc > 0) THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

END OTHER_EUME2E_ASSOC;

--
-- Create supports_eum_on_guid and supports_e2e_on_guid
--   
BEGIN
  -- Create supports_eum_on_guid
  BEGIN
    supports_eum_on_guid  := MGMT_ASSOC.get_assoc_def_guid(ASSOC_DEF_SUPPORTS_EUM_ON,MGMT_ASSOC.ANY_TARGET_TYPE);
  EXCEPTION
    WHEN mgmt_global.assoc_def_does_not_exist THEN
      MGMT_ASSOC.create_target_assoc_def(
                             p_assoc_name =>ASSOC_DEF_SUPPORTS_EUM_ON,
                             p_source_target_type =>MGMT_ASSOC.ANY_TARGET_TYPE,
                             p_assoc_target_type =>MGMT_ASSOC.ANY_TARGET_TYPE,
                             p_assoc_type => ASSOC_TYPE_SUPPORTS_EUM_ON,
                             p_prop_view_priv => MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION );

      supports_eum_on_guid  := MGMT_ASSOC.get_assoc_def_guid(ASSOC_DEF_SUPPORTS_EUM_ON, MGMT_ASSOC.ANY_TARGET_TYPE);
  END;
 
  -- Create supports_e2e_on_guid
  BEGIN
    supports_e2e_on_guid  := MGMT_ASSOC.get_assoc_def_guid(ASSOC_DEF_SUPPORTS_E2E_ON,MGMT_ASSOC.ANY_TARGET_TYPE);
  EXCEPTION
    WHEN mgmt_global.assoc_def_does_not_exist THEN
      MGMT_ASSOC.create_target_assoc_def(
                              p_assoc_name =>ASSOC_DEF_SUPPORTS_E2E_ON,
                              p_source_target_type =>MGMT_ASSOC.ANY_TARGET_TYPE,
                              p_assoc_target_type =>MGMT_ASSOC.ANY_TARGET_TYPE,
                              p_assoc_type => ASSOC_TYPE_SUPPORTS_E2E_ON,
                              p_prop_view_priv => MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION );

      supports_e2e_on_guid  := MGMT_ASSOC.get_assoc_def_guid(ASSOC_DEF_SUPPORTS_E2E_ON, MGMT_ASSOC.ANY_TARGET_TYPE);
  END;

  -- Create supports_forms_on_guid
  BEGIN
    supports_forms_on_guid  := MGMT_ASSOC.get_assoc_def_guid(ASSOC_DEF_SUPPORTS_FORMS_ON,MGMT_ASSOC.ANY_TARGET_TYPE);
  EXCEPTION
    WHEN mgmt_global.assoc_def_does_not_exist THEN
      MGMT_ASSOC.create_target_assoc_def(
                              p_assoc_name =>ASSOC_DEF_SUPPORTS_FORMS_ON,
                              p_source_target_type =>MGMT_ASSOC.ANY_TARGET_TYPE,
                              p_assoc_target_type =>MGMT_ASSOC.ANY_TARGET_TYPE,
                              p_assoc_type => ASSOC_TYPE_SUPPORTS_FORMS_ON,
                              p_prop_view_priv => MGMT_ASSOC.ALL_LEVEL_VIEW_PROPAGATION );

      supports_forms_on_guid  := MGMT_ASSOC.get_assoc_def_guid(ASSOC_DEF_SUPPORTS_FORMS_ON, MGMT_ASSOC.ANY_TARGET_TYPE);
  END;

END mgmt_website_target;
/
show errors;