Rem
Rem $Header: webapp_upgrade_pkgbody.sql 02-oct-2005.11:09:24 mfidanbo Exp $
Rem
Rem webapp_upgrade_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      webapp_upgrade_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    mfidanbo    09/26/05 - mark broken targets as unbroken 
Rem    rmarripa    08/22/05 - associate all other beacons in addition to newly 
Rem                           created beacon 
Rem    rmarripa    08/17/05 - handle errors 
Rem    afontana    07/08/05 - insert duration 
Rem    afontana    07/07/05 - do not close severities when migrating local 
Rem                           beacon 
Rem    rmarripa    06/17/05 - do not assume 3.0 targets as upgraded targets 
Rem    rmarripa    05/13/05 - sync also beacon watch list items 
Rem    rmarripa    05/04/05 - add beacons sync 
Rem    rmarripa    12/24/04 - rmarripa_webapp_upgrade_changes
Rem    rmarripa    12/22/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY webapp_upgrade_job AS

FUNCTION GET_UPGRADES RETURN MGMT_BCN_10GR2_UPGRADE_KEY_SET;

PROCEDURE INSERT_COMPOSITE_KEYS(p_changes IN MGMT_BCN_10GR2_UPGRADE_KEY_SET);
 
PROCEDURE MIGRATE_NOTIFY_RULE_CONFIGS(p_target IN RAW, 
                                      p_beacon IN VARCHAR2);

PROCEDURE MIGRATE_CURRENT_METRICS(p_target IN RAW, p_beacon IN VARCHAR2);

PROCEDURE MIGRATE_VIOLATIONS(p_target IN RAW, 
                             p_beacon IN VARCHAR2);

PROCEDURE MIGRATE_THRESHOLDS(p_target IN RAW, 
                             p_beacon IN VARCHAR2);


PROCEDURE INSERT_TARGET_KEYS(p_target IN RAW, 
                                           p_beacon IN VARCHAR2);



--Check if the upgrade done already and if the target exists
FUNCTION CONTINUE_UPGRADE(tgt_name IN VARCHAR2,
                          tgt_type IN VARCHAR2 ) RETURN NUMBER
IS
    l_guid mgmt_targets.target_guid%TYPE;
    prop_val mgmt_target_properties.property_value%TYPE;
BEGIN
  BEGIN
    SELECT target_guid
      INTO l_guid
      FROM mgmt_targets 
     WHERE target_name= tgt_name
       AND target_type = tgt_type;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN -1; -- target deleted
  END;

  BEGIN
    SELECT property_value
      INTO prop_val
      FROM MGMT_TARGET_PROPERTIES
     WHERE target_guid = l_guid
       AND property_name = 'Upgraded';
  EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN 1; -- continue upgrade
  END;
  
  --Target already upgraded. Stop upgrade
  
  return -1;

  EXCEPTION WHEN OTHERS THEN --dont stop upgrade
    return -1;
END CONTINUE_UPGRADE;

PROCEDURE UPDATE_TARGET_METADATA(tgt_id IN RAW)
IS
BEGIN
    DELETE 
      FROM mgmt_bcn_target 
     WHERE target_guid = tgt_id
       AND beacon_target_guid = tgt_id;

    UPDATE mgmt_targets 
       SET emd_url = null, host_name = null, type_meta_ver = '3.0' 
     WHERE target_guid = tgt_id;

    BEGIN
      INSERT INTO MGMT_TARGET_PROPERTIES (target_guid, property_name, property_value)
             VALUES (tgt_id, 'Upgraded', '1');
    EXCEPTION WHEN OTHERS THEN
      NULL; -- dup_val_on_index should never happen as we check for that. even if it happens we ignore
    END;

END UPDATE_TARGET_METADATA;


--------------------------------------------------------------------------
PROCEDURE CLOSE_BCN_SEVS ( target_in IN RAW, beacon_in IN RAW, 
                           beacon_name IN VARCHAR2 )
IS

  CURSOR v_sev_cur(tgt RAW, bcn RAW) IS
    SELECT s.metric_guid, s.key_value, s.collection_timestamp
      FROM MGMT_CURRENT_SEVERITY s,
           MGMT_TARGETS t,
           MGMT_METRICS_COMPOSITE_KEYS k
     WHERE s.target_guid = tgt 
       AND t.target_guid = bcn
       AND k.target_guid = tgt 
       AND ( s.severity_code = mgmt_global.G_SEVERITY_WARNING
           OR s.severity_code = mgmt_global.G_SEVERITY_CRITICAL )
       AND s.key_value = HEXTORAW(k.composite_key)
       AND k.key_part2_value = DECODE(tgt, bcn, emd_bcntxn.p_local_target_name, t.target_name);

  v_tgt_ts     DATE;
  v_tz_region  MGMT_TARGETS.timezone_region%TYPE;
  v_more       BOOLEAN;
  v_sev        v_sev_cur%ROWTYPE;
  v_ts         DATE;

BEGIN

  SELECT timezone_region
    INTO v_tz_region
    FROM MGMT_TARGETS
   WHERE target_guid = target_in
     AND timezone_region IS NOT NULL;

  v_tgt_ts :=  MGMT_GLOBAL.SYSDATE_TZRGN(v_tz_region);

  v_more := TRUE;
  OPEN v_sev_cur(target_in, beacon_in);
  WHILE v_more LOOP
    FETCH v_sev_cur INTO v_sev;
    v_more := v_sev_cur%FOUND;
    IF v_more THEN
      v_ts := GREATEST(v_tgt_ts, v_sev.collection_timestamp + 1/1440);
      INSERT INTO MGMT_SEVERITY
        ( target_guid, metric_guid, key_value, collection_timestamp, 
          severity_code, message, severity_duration)
      VALUES
        ( target_in, v_sev.metric_guid, v_sev.key_value,
          v_ts, mgmt_global.G_SEVERITY_CLEAR,
          'Beacon ' || beacon_name || 
          ' no longer monitors the web application.  Clearing all severities from the beacon.', v_ts - v_sev.collection_timestamp);
    END IF;
  END LOOP;
  IF v_sev_cur%ISOPEN THEN
    CLOSE v_sev_cur;
  END IF;

EXCEPTION  
  WHEN OTHERS THEN
    IF v_sev_cur%ISOPEN THEN
      CLOSE v_sev_cur;
    END IF;
    RAISE;

END CLOSE_BCN_SEVS;


--------------------------------------------------------------------------
PROCEDURE CLOSE_METRIC_ERRORS (target_in IN RAW, 
                               beacon_in IN RAW)
IS

  CURSOR v_err_cur(tgt RAW, bcn RAW) IS
    SELECT e.agent_guid, e.metric_guid, e.coll_name, 
           e.collection_timestamp, e.metric_error_type
      FROM mgmt_current_metric_errors e, mgmt_targets b, mgmt_targets a
     WHERE b.target_guid = bcn
       AND a.emd_url = b.emd_url
       AND a.target_type = 'oracle_emd'
       AND e.target_guid = tgt
       AND e.agent_guid = a.target_guid;

  v_tgt_ts     DATE;
  v_tz_region  MGMT_TARGETS.timezone_region%TYPE;
  v_more       BOOLEAN;
  v_err        v_err_cur%ROWTYPE;
  v_ts         DATE;

BEGIN

  SELECT timezone_region
    INTO v_tz_region
    FROM MGMT_TARGETS
   WHERE target_guid = target_in
     AND timezone_region IS NOT NULL;

  v_tgt_ts :=  MGMT_GLOBAL.SYSDATE_TZRGN(v_tz_region);

  v_more := TRUE;
  OPEN v_err_cur(target_in, beacon_in);
  WHILE v_more LOOP
    FETCH v_err_cur INTO v_err;
    v_more := v_err_cur%FOUND;
    IF v_more THEN
      v_ts := GREATEST(v_tgt_ts, v_err.collection_timestamp + 1/1440);
      INSERT INTO MGMT_METRIC_ERRORS
        ( target_guid, metric_guid, coll_name, agent_guid, 
          collection_timestamp, metric_error_message, metric_error_type )
      VALUES
        ( target_in, v_err.metric_guid, v_err.coll_name,
          v_err.agent_guid, v_ts, NULL, v_err.metric_error_type );
    END IF;
  END LOOP;
  IF v_err_cur%ISOPEN THEN
    CLOSE v_err_cur;
  END IF;

EXCEPTION  
  WHEN OTHERS THEN
    IF v_err_cur%ISOPEN THEN
      CLOSE v_err_cur;
    END IF;
    RAISE;

END CLOSE_METRIC_ERRORS;

--------------------------------------------------------------------------
PROCEDURE REMOVE_LOCAL( tgt_name IN VARCHAR2,
                        tgt_type IN VARCHAR2)
IS 
    l_tgt_id mgmt_targets.target_guid%TYPE;
    l_avail mgmt_bcn_target.PARTICIPATES_AVAIL%TYPE;
    bcns MGMT_BCN_ASSOC_ARRAY := null;
BEGIN
    l_tgt_id := mgmt_target.get_target_guid(tgt_name, tgt_type);
    
    INSERT INTO
     mgmt_target_properties (target_guid, property_name, property_type, property_value)
    VALUES (l_tgt_id, UPGRADE_ASSOC_BEACON, 'HIDDEN', NO_UPGRADE_ASSOC_BEACON);

    --remove the local beacon 
    close_bcn_sevs( l_tgt_id, l_tgt_id, emd_bcntxn.p_local_target_name );
    close_metric_errors( l_tgt_id, l_tgt_id);

    -- Upgrade current metrics
    MIGRATE_CURRENT_METRICS(l_tgt_id, NULL);

    -- Key value cleanup from severity and notification history
    MIGRATE_NOTIFY_RULE_CONFIGS(l_tgt_id, NULL);

    MIGRATE_VIOLATIONS(l_tgt_id, NULL);

    MIGRATE_THRESHOLDS(l_tgt_id, NULL);
 
    -- update metadata is the final step 
    update_target_metadata(l_tgt_id);
    
END REMOVE_LOCAL;

PROCEDURE REPLACE_LOCAL_WITH_BEACON( tgt_name IN VARCHAR2,
                                     tgt_type IN VARCHAR2,
                                     beacon_name IN VARCHAR2,
                                     beacon_type IN VARCHAR2 )
IS 
    l_tgt_id mgmt_targets.target_guid%TYPE;
    l_avail mgmt_bcn_target.PARTICIPATES_AVAIL%TYPE;
    bcns MGMT_BCN_ASSOC_ARRAY := MGMT_BCN_ASSOC_ARRAY();
    l_bcn_id mgmt_targets.target_guid%TYPE;
    l_cnt INTEGER;
BEGIN
    IF ( beacon_name IS NULL ) OR ( beacon_type IS NULL ) OR 
       ( beacon_type  != MGMT_GLOBAL.G_BEACON_TARGET_TYPE ) THEN
        return;
    END IF;
    
    l_tgt_id := mgmt_target.get_target_guid(tgt_name, tgt_type);
    l_bcn_id := mgmt_target.get_target_guid(beacon_name, beacon_type);
    
    --Replace the thresholds in the mgmt_admin_metric_thresholds table
    --

    l_cnt := 1;
    -- Collect the list of beacons that are currently associated 
    -- and check local beacon settings 
    FOR rec IN ( SELECT bcntgt.target_name, bcntgt.target_type, bcn.target_guid, bcn.beacon_target_guid, bcn.PARTICIPATES_AVAIL 
          FROM mgmt_bcn_target bcn, mgmt_targets bcntgt
         WHERE bcn.target_guid = l_tgt_id 
           AND bcn.beacon_target_guid = bcntgt.target_guid) 
    LOOP 
        bcns.extend;
        IF ( rec.beacon_target_guid = rec.target_guid) THEN 
            -- set the local beacon flag to 'Y' and use the beacon name passed in 
            bcns(l_cnt) := MGMT_BCN_ASSOC(beacon_name, beacon_type, rec.participates_avail, 'Y');
        ELSE 
            bcns(l_cnt) := MGMT_BCN_ASSOC(rec.target_name, rec.target_type, rec.participates_avail, 'N');
        END IF;
        l_cnt := l_cnt + 1;
    END LOOP;
    emd_bcntxn.emd_bcn_associate_beacons( tgt_name, tgt_type, bcns);
    
    INSERT INTO 
     mgmt_target_properties (target_guid, property_name, property_type, property_value)
    VALUES (l_tgt_id, UPGRADE_ASSOC_BEACON, 'HIDDEN', rawtohex(l_bcn_id) );

    --remove the local beacon 
    close_bcn_sevs( l_tgt_id, l_tgt_id, emd_bcntxn.p_local_target_name );

    -- Insert the new key values.
    INSERT_TARGET_KEYS(l_tgt_id, beacon_name);

    -- Upgrade current metrics
    MIGRATE_CURRENT_METRICS(l_tgt_id, beacon_name);

    -- Key value cleanup from severity and notification history
    MIGRATE_NOTIFY_RULE_CONFIGS(l_tgt_id, beacon_name);

    MIGRATE_VIOLATIONS(l_tgt_id, beacon_name);

    MIGRATE_THRESHOLDS(l_tgt_id, beacon_name);
 
    --close metric errors, but not severities   	
    close_metric_errors( l_tgt_id, l_tgt_id);
 
    -- update metadata is the final step 
    update_target_metadata(l_tgt_id);
    
END REPLACE_LOCAL_WITH_BEACON;


PROCEDURE SYNC_ASSOC_BEACONS( tgt_name IN VARCHAR2,
                              tgt_type IN VARCHAR2)
IS
l_tgt_id RAW(16):= NULL;
l_job_id RAW(16):= NULL;
BEGIN
    IF ( tgt_name IS NULL ) OR ( tgt_type IS NULL ) OR 
       ( ( tgt_type != MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE ) AND 
         ( tgt_type != MGMT_GLOBAL.G_BEACON_TARGET_TYPE) ) THEN
        IF( EMDW_LOG.P_IS_ERROR_SET ) THEN 
            emdw_log.error('Invalid target name or type is null or not an applicable target type. '||tgt_name||', '||tgt_type, MODULE_NAME);
        END IF;
        return ;
    END IF;
    
    l_tgt_id := mgmt_target.get_target_guid ( tgt_name, tgt_type);
   
    IF ( tgt_type = MGMT_GLOBAL.G_BEACON_TARGET_TYPE) THEN
        MGMT_GENSVC_UPDBCN.SUBMIT_SYNC_JOB( tgt_name, tgt_type, tgt_name,
                             'Sync Beacon', l_job_id );
        IF ( EMDW_LOG.P_IS_INFO_SET ) THEN
            emdw_log.info('Job Id for Sync job for target' ||tgt_name||', '||tgt_type||'; Beacon '||tgt_name|| 'is '||RAWTOHEX(l_job_id) , MODULE_NAME);
        END IF;
    ELSE
        FOR sub_rec in ( SELECT tgt.target_name as beacon_name
                       FROM mgmt_bcn_target tgt_bcn, mgmt_targets tgt
                      WHERE tgt_bcn.target_guid = l_tgt_id
                        AND tgt_bcn.target_guid <> tgt_bcn.beacon_target_guid
                        AND tgt_bcn.beacon_target_guid = tgt.target_guid
                        AND tgt.target_type=MGMT_GLOBAL.G_BEACON_TARGET_TYPE)
        LOOP
            MGMT_GENSVC_UPDBCN.SUBMIT_SYNC_JOB( tgt_name, tgt_type, sub_rec.beacon_name, 
                                 'Sync Beacon', l_job_id );
            IF ( EMDW_LOG.P_IS_INFO_SET ) THEN
                emdw_log.info('Job Id for Sync job for target' ||tgt_name||', '||tgt_type||'; Beacon '||sub_rec.beacon_name|| 'is '||RAWTOHEX(l_job_id) , MODULE_NAME);
            END IF;
        END LOOP;
    END IF;
    RETURN;
END SYNC_ASSOC_BEACONS;


PROCEDURE UPGRADE_TARGET_DATA( tgt_name IN VARCHAR2,
                               tgt_type IN VARCHAR2,
                               beacon_name IN VARCHAR2,
                               beacon_type IN VARCHAR2)
IS 
   l_tgt_upgraded NUMBER ; 
BEGIN

    IF ( tgt_name IS NULL ) OR ( tgt_type IS NULL ) OR 
       ( tgt_type != MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE ) THEN
        return ;
    END IF;
   
    --check if the upgrade is done already, by checking the emd_url
    -- field of the target
    l_tgt_upgraded := continue_upgrade(tgt_name, tgt_type);
     
    IF ( l_tgt_upgraded <= 0 ) THEN 
        --Target already upgrded or deleted
        RETURN;
    END IF;

    IF ( beacon_name IS NOT NULL ) AND ( beacon_type IS NOT NULL ) THEN 
        replace_local_with_beacon(tgt_name, tgt_type, beacon_name, beacon_type);
    ELSE
        remove_local(tgt_name, tgt_type);
    END IF;

    -- sync all associated beacons
    sync_assoc_beacons(tgt_name, tgt_type);
  
    return;
END UPGRADE_TARGET_DATA;


FUNCTION CORRECT_BCN_VALUE(p_target IN RAW)
RETURN VARCHAR2
IS
  v_bcn VARCHAR2(256);
BEGIN

  SELECT t.TARGET_NAME
    INTO v_bcn
    FROM MGMT_TARGET_PROPERTIES p,
         MGMT_TARGETS t
   WHERE p.TARGET_GUID = p_target
     AND p.PROPERTY_NAME = UPGRADE_ASSOC_BEACON
     AND t.TARGET_GUID = HEXTORAW(p.PROPERTY_VALUE)
     AND t.TARGET_TYPE = 'oracle_beacon';
 
  RETURN v_bcn;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN '-';

END CORRECT_BCN_VALUE;

--
-- Generates the new beacon key value, given the
-- target and txn name.
--
FUNCTION GENERATE_KEY_VALUE(p_target IN RAW,
                            p_txn_name IN VARCHAR2,
                            p_beacon_name IN VARCHAR2 DEFAULT NULL)
  RETURN RAW
IS
  v_bcn VARCHAR2(256);
BEGIN

  IF p_beacon_name IS NULL THEN
    v_bcn := CORRECT_BCN_VALUE(p_target);
  ELSE
    v_bcn := p_beacon_name;
  END IF;

  RETURN mgmt_global.get_composite_key_guid(
                 SMP_EMD_STRING_ARRAY(p_txn_name, v_bcn));
END GENERATE_KEY_VALUE;


--
-- Generate the key value the should replace
-- the supplied old key value (which refers to a local beacon).
--   Returns old key if no change should be made.
--
-- The invoking procedure should not pass in targets
-- that are not websites.
--
FUNCTION ALTERED_KEY_VALUE(p_target IN RAW,
                           p_old_key IN VARCHAR2,
                           p_beacon_name IN VARCHAR2 DEFAULT NULL)
  RETURN RAW
IS
  v_txn VARCHAR2(256);
BEGIN

  SELECT ck.KEY_PART1_VALUE
    INTO v_txn
    FROM MGMT_METRICS_COMPOSITE_KEYS ck
   WHERE ck.TARGET_GUID = p_target
     AND ck.COMPOSITE_KEY = p_old_key
     AND ck.KEY_PART2_VALUE = '-';

  RETURN GENERATE_KEY_VALUE(p_target, v_txn, p_beacon_name);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN HEXTORAW(p_old_key);

END ALTERED_KEY_VALUE;

--
-- Get all the keys that need to be 'upgraded'
--
FUNCTION GET_UPGRADES RETURN MGMT_BCN_10GR2_UPGRADE_KEY_SET
IS
  v_upgrades MGMT_BCN_10GR2_UPGRADE_KEY_SET;
BEGIN

  SELECT MGMT_BCN_10GR2_UPGRADE_KEY(keys.guid, keys.old_key, keys.new_key)
  BULK COLLECT INTO v_upgrades
  FROM (SELECT ck.TARGET_GUID guid,
               ck.COMPOSITE_KEY old_key, 
               GENERATE_KEY_VALUE(ck.TARGET_GUID, ck.KEY_PART1_VALUE, NULL) new_key

          FROM MGMT_METRICS_COMPOSITE_KEYS ck,
               MGMT_TARGETS t
         WHERE ck.TARGET_GUID = t.TARGET_GUID
           AND t.TARGET_TYPE = 'website'
           AND ck.KEY_PART2_VALUE = '-') keys;

  RETURN v_upgrades;

END GET_UPGRADES;

--
-- Insert all the new keys into MGMT_METRICS_COMPOSITE_KEYS,
-- checking to make sure we don't insert duplicates.
--
PROCEDURE INSERT_COMPOSITE_KEYS(p_changes IN MGMT_BCN_10GR2_UPGRADE_KEY_SET)
IS
BEGIN

    INSERT ALL INTO MGMT_METRICS_COMPOSITE_KEYS
             (COMPOSITE_KEY,
              TARGET_GUID,
              KEY_PART1_VALUE,
              KEY_PART2_VALUE,
              KEY_PART3_VALUE,
              KEY_PART4_VALUE,
              KEY_PART5_VALUE)
      (SELECT keys.new_key,
              ck.TARGET_GUID,
              ck.KEY_PART1_VALUE,
              CORRECT_BCN_VALUE(ck.TARGET_GUID),
              ck.KEY_PART3_VALUE,
              ck.KEY_PART4_VALUE,
              ck.KEY_PART5_VALUE
         FROM MGMT_METRICS_COMPOSITE_KEYS ck,
              TABLE(CAST(p_changes AS MGMT_BCN_10GR2_UPGRADE_KEY_SET)) keys
        WHERE ck.TARGET_GUID = keys.tgt_guid
          AND ck.COMPOSITE_KEY = keys.old_key
          AND (keys.new_key, ck.TARGET_GUID) NOT IN 
                                  (SELECT COMPOSITE_KEY, TARGET_GUID 
                                   FROM MGMT_METRICS_COMPOSITE_KEYS));

END INSERT_COMPOSITE_KEYS;


--
-- Update the 1 Day rollup tables with the new keys
--
PROCEDURE MIGRATE_1DAY(p_changes IN MGMT_BCN_10GR2_UPGRADE_KEY_SET)
IS
BEGIN

  UPDATE MGMT_METRICS_1DAY m
     SET KEY_VALUE = (SELECT keys.new_key
                        FROM TABLE(CAST(p_changes AS MGMT_BCN_10GR2_UPGRADE_KEY_SET)) keys
                       WHERE m.TARGET_GUID = keys.tgt_guid
                         AND m.KEY_VALUE = keys.old_key)
   WHERE (m.TARGET_GUID, m.KEY_VALUE) IN 
          (SELECT tgt_guid, old_key
            FROM TABLE(CAST(p_changes AS MGMT_BCN_10GR2_UPGRADE_KEY_SET)));

END MIGRATE_1DAY;


--
-- Update the 1 Hour rollup tables with the new keys
--
PROCEDURE MIGRATE_1HOUR(p_changes IN MGMT_BCN_10GR2_UPGRADE_KEY_SET)
IS
BEGIN
 
  UPDATE MGMT_METRICS_1HOUR m
     SET KEY_VALUE = (SELECT keys.new_key
                        FROM TABLE(CAST(p_changes AS MGMT_BCN_10GR2_UPGRADE_KEY_SET)) keys
                       WHERE m.TARGET_GUID = keys.tgt_guid
                         AND m.KEY_VALUE = keys.old_key)
   WHERE (m.TARGET_GUID, m.KEY_VALUE) IN 
          (SELECT tgt_guid, old_key
            FROM TABLE(CAST(p_changes AS MGMT_BCN_10GR2_UPGRADE_KEY_SET)));

END MIGRATE_1HOUR;


--
-- Update the raw metric tables with the new keys
--
PROCEDURE MIGRATE_RAW(p_changes IN MGMT_BCN_10GR2_UPGRADE_KEY_SET)
IS
BEGIN

  UPDATE MGMT_METRICS_RAW m
     SET KEY_VALUE = (SELECT keys.new_key
                        FROM TABLE(CAST(p_changes AS MGMT_BCN_10GR2_UPGRADE_KEY_SET)) keys
                       WHERE m.TARGET_GUID = keys.tgt_guid
                         AND m.KEY_VALUE = keys.old_key)
   WHERE (m.TARGET_GUID, m.KEY_VALUE) IN 
          (SELECT tgt_guid, old_key
            FROM TABLE(CAST(p_changes AS MGMT_BCN_10GR2_UPGRADE_KEY_SET)));

END MIGRATE_RAW;

--
-- Migrate all historical metric tables (not current) to the proper keys
--
PROCEDURE MIGRATE_METRIC_HISTORY
IS
  v_keys MGMT_BCN_10GR2_UPGRADE_KEY_SET;
BEGIN

  v_keys := GET_UPGRADES();
  INSERT_COMPOSITE_KEYS(v_keys);
  MIGRATE_1DAY(v_keys);
  MIGRATE_1HOUR(v_keys);
  MIGRATE_RAW(v_keys);

END MIGRATE_METRIC_HISTORY;

--
-- Migrate the notification rules for a given target.
--
PROCEDURE MIGRATE_NOTIFY_RULE_CONFIGS(p_target IN RAW, p_beacon IN VARCHAR2)
IS
  tgt_name MGMT_TARGETS.target_name%TYPE;
  tgt_type MGMT_TARGETS.target_type%TYPE;
BEGIN

  BEGIN
    SELECT target_name, target_type 
      INTO tgt_name, tgt_type 
      FROM mgmt_targets 
     WHERE target_guid = p_target;
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
      RETURN;
  END;

  IF p_beacon IS NULL THEN
    DELETE FROM MGMT_NOTIFY_RULE_CONFIGS n
     WHERE ( (n.TARGET_NAME = tgt_name AND n.TARGET_TYPE = tgt_type) 
              OR (n.TARGET_GUID = p_target) )
       AND ((KEY_PART_2 = '-' ) 
           OR KEY_VALUE IN ( SELECT COMPOSITE_KEY
                            FROM MGMT_METRICS_COMPOSITE_KEYS
                           WHERE TARGET_GUID = p_target
                             AND KEY_PART2_VALUE = '-'));
    RETURN;
  END IF;

  BEGIN
    UPDATE MGMT_NOTIFY_RULE_CONFIGS n
       SET KEY_VALUE = ALTERED_KEY_VALUE(p_target, n.KEY_VALUE, p_beacon),
           KEY_PART_2 = p_beacon
     WHERE ( (n.TARGET_NAME = tgt_name AND n.TARGET_TYPE = tgt_type) 
              OR (n.TARGET_GUID = p_target) )
       AND ((KEY_PART_2 = '-' ) 
           OR KEY_VALUE IN ( SELECT COMPOSITE_KEY
                            FROM MGMT_METRICS_COMPOSITE_KEYS
                           WHERE TARGET_GUID = p_target
                             AND KEY_PART2_VALUE = '-'));
  EXCEPTION 
    WHEN DUP_VAL_ON_INDEX THEN 
      NULL;
  END;
END MIGRATE_NOTIFY_RULE_CONFIGS;


--
-- Migrate the alert history for a given target.
--
PROCEDURE MIGRATE_CURRENT_METRICS(p_target IN RAW, p_beacon IN VARCHAR2)
IS
BEGIN

  IF p_beacon IS NULL THEN
    DELETE FROM MGMT_CURRENT_METRICS
    WHERE TARGET_GUID = p_target
      AND KEY_VALUE IN (SELECT COMPOSITE_KEY
                          FROM MGMT_METRICS_COMPOSITE_KEYS
                         WHERE TARGET_GUID = p_target
                           AND KEY_PART2_VALUE = '-');
    RETURN;
  END IF;

  BEGIN
    UPDATE MGMT_CURRENT_METRICS v
       SET KEY_VALUE = ALTERED_KEY_VALUE(v.TARGET_GUID, v.KEY_VALUE, p_beacon)
     WHERE v.TARGET_GUID = p_target
       AND KEY_VALUE IN (SELECT COMPOSITE_KEY
                            FROM MGMT_METRICS_COMPOSITE_KEYS
                           WHERE TARGET_GUID = p_target
                             AND KEY_PART2_VALUE = '-');
  EXCEPTION 
    WHEN DUP_VAL_ON_INDEX THEN 
      NULL;
  END;

END MIGRATE_CURRENT_METRICS;

--
-- Migrate the alert history for a given target.
--
PROCEDURE MIGRATE_VIOLATIONS(p_target IN RAW, p_beacon IN VARCHAR2)
IS
BEGIN

  IF p_beacon IS NULL THEN
    DELETE FROM MGMT_VIOLATIONS
    WHERE TARGET_GUID = p_target
      AND KEY_VALUE IN (SELECT COMPOSITE_KEY
                          FROM MGMT_METRICS_COMPOSITE_KEYS
                         WHERE TARGET_GUID = p_target
                           AND KEY_PART2_VALUE = '-');
    RETURN;
  END IF;

  BEGIN
    UPDATE MGMT_VIOLATIONS v
       SET KEY_VALUE = ALTERED_KEY_VALUE(v.TARGET_GUID, v.KEY_VALUE, p_beacon)
     WHERE v.TARGET_GUID = p_target
       AND KEY_VALUE IN (SELECT COMPOSITE_KEY
                            FROM MGMT_METRICS_COMPOSITE_KEYS
                           WHERE TARGET_GUID = p_target
                             AND KEY_PART2_VALUE = '-');
  EXCEPTION 
    WHEN DUP_VAL_ON_INDEX THEN 
      NULL;
  END;

END MIGRATE_VIOLATIONS;

PROCEDURE MIGRATE_THRESHOLDS(p_target IN RAW, p_beacon IN VARCHAR2)
IS
BEGIN

  -- Delete thresholds from mgmt_metric_thresholds table
  -- regardless of replace or remove beacon. In case of 
  -- replace beacon, new thresholds will be populated by 
  -- agent. 
  DELETE FROM MGMT_METRIC_THRESHOLDS
  WHERE TARGET_GUID = p_target
    AND KEY_VALUE IN (SELECT COMPOSITE_KEY
                        FROM MGMT_METRICS_COMPOSITE_KEYS
                       WHERE TARGET_GUID = p_target
                         AND KEY_PART2_VALUE = '-');

  IF p_beacon IS NULL THEN
    DELETE FROM MGMT_ADMIN_METRIC_THRESHOLDS
    WHERE TARGET_GUID = p_target
      AND KEY_VALUE IN (SELECT COMPOSITE_KEY
                          FROM MGMT_METRICS_COMPOSITE_KEYS
                         WHERE TARGET_GUID = p_target
                           AND KEY_PART2_VALUE = '-');
    RETURN;
  END IF;

  BEGIN
    UPDATE MGMT_ADMIN_METRIC_THRESHOLDS v
       SET KEY_VALUE = ALTERED_KEY_VALUE(v.TARGET_GUID, v.KEY_VALUE, p_beacon)
     WHERE v.TARGET_GUID = p_target
       AND KEY_VALUE IN (SELECT COMPOSITE_KEY
                            FROM MGMT_METRICS_COMPOSITE_KEYS
                           WHERE TARGET_GUID = p_target
                             AND KEY_PART2_VALUE = '-');
  EXCEPTION 
    WHEN DUP_VAL_ON_INDEX THEN 
      NULL;
  END;

END MIGRATE_THRESHOLDS;

PROCEDURE INSERT_TARGET_KEYS(p_target IN RAW, 
                                           p_beacon IN VARCHAR2)
IS
  cursor keys_cur ( tgt_guid RAW) IS
    SELECT TARGET_GUID, COMPOSITE_KEY, KEY_PART1_VALUE, KEY_PART2_VALUE,
           KEY_PART3_VALUE, KEY_PART4_VALUE, KEY_PART5_VALUE
      FROM MGMT_METRICS_COMPOSITE_KEYS
     WHERE TARGET_GUID = tgt_guid
       AND KEY_PART2_VALUE = '-';

  comp_key MGMT_METRICS_COMPOSITE_KEYS.COMPOSITE_KEY%TYPE;
BEGIN

  IF ( p_beacon IS NULL ) THEN 
    RETURN;
  END IF;


  FOR ck in keys_cur ( p_target ) LOOP

      comp_key := GENERATE_KEY_VALUE(ck.target_guid, ck.KEY_PART1_VALUE, p_beacon); 

    BEGIN 
      INSERT
        INTO MGMT_METRICS_COMPOSITE_KEYS
             (COMPOSITE_KEY,
              TARGET_GUID,
              KEY_PART1_VALUE,
              KEY_PART2_VALUE,
              KEY_PART3_VALUE,
              KEY_PART4_VALUE,
              KEY_PART5_VALUE)
      VALUES  (comp_key,
              ck.TARGET_GUID,
              ck.KEY_PART1_VALUE,
              p_beacon,
              ck.KEY_PART3_VALUE,
              ck.KEY_PART4_VALUE,
              ck.KEY_PART5_VALUE);
    EXCEPTION 
      WHEN DUP_VAL_ON_INDEX THEN 
        NULL;
    END;
  END LOOP;

END INSERT_TARGET_KEYS;

END webapp_upgrade_job;
/
show errors;