Rem drv: <migrate type= "data_upgrade" version= "10.2.0.1"/>
Rem
Rem $Header: notification_data_upgrade.sql 02-sep-2005.13:55:29 yaofeng Exp $
Rem
Rem notification_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      notification_data_upgrade.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    yaofeng     08/31/05 - target type change 
Rem    dcawley     07/14/05 - Created
Rem

@@&&EM_SQL_ROOT/core/v102020/notification_init.sql

-- upgrade the rules with metric "Targets not providing data"

BEGIN

  DECLARE 
    l_count NUMBER;
    l_rule_names SMP_EMD_STRING_ARRAY;
    l_owners SMP_EMD_STRING_ARRAY;
  
  BEGIN

    -- get all rules with this metric

    SELECT DISTINCT  rule_name, owner
      BULK COLLECT INTO l_rule_names, l_owners
      FROM MGMT_NOTIFY_RULE_CONFIGS
     WHERE target_type = mgmt_global.G_REPOSITORY_TARGET_TYPE
       AND metric_name = 'Targets_not_uploading'
       AND metric_column = 'targets';

    FOR i IN 1..l_rule_names.COUNT
    LOOP

      -- check how many metrics this rule has

      SELECT count(*)
        INTO l_count
        FROM MGMT_NOTIFY_RULE_CONFIGS
       WHERE rule_name = l_rule_names(i)
         AND owner = l_owners(i);
 
      IF (l_count = 1)
      THEN 

        -- simple case -> change the target type and name

        UPDATE MGMT_NOTIFY_RULE_CONFIGS
           SET target_type = mgmt_global.G_AGENT_TARGET_TYPE,
               target_name = '%',
               metric_name = 'Targets_not_uploading',
               metric_column = 'count'
         WHERE rule_name = l_rule_names(i)
           AND owner = l_owners(i);

      ELSE

        -- multiple metrics -> remove this metric

        DELETE FROM  MGMT_NOTIFY_RULE_CONFIGS
         WHERE rule_name = l_rule_names(i)
           AND owner = l_owners(i)
           AND metric_name = 'Targets_not_uploading'
           AND metric_column = 'targets';

      END IF;

    END LOOP;

    COMMIT;

    EXCEPTION
      WHEN OTHERS
      THEN NULL;

  END;

END;
/