Rem drv: <migrate type="data_upgrade" version="10.2.0.3" pos="-"/>
Rem
Rem $Header: template_data_upgrade.sql 23-jul-2007.23:39:35 rkpandey Exp $
Rem
Rem template_data_upgrade.sql
Rem
Rem Copyright (c) 2006, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      template_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    rkpandey    07/23/07 - Backport rkpandey_bug-6167316 from main
Rem    rpinnama    05/17/06 - Drop thresholds with blank collection name 
Rem    rpinnama    04/04/06 - Created
Rem

PROMPT Begin Data fix for bug 5055727 
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

DECLARE
  l_host_avail_metric_guid mgmt_metrics.metric_guid%TYPE;
  l_coll_names             mgmt_medium_string_array;
  l_upd_cnt                integer;
  l_upd_cnt_2              integer;
  l_upd_cnt_3              integer;
BEGIN

  -- Get all policies with multiple collection names
  FOR crec IN (
                SELECT t.target_name, t.target_type, t.target_guid, m.metric_name, m.metric_column,
                       p.policy_guid, p.policy_name
                  FROM mgmt_targets t, mgmt_metrics m, mgmt_policies p
                 WHERE t.target_type = m.target_type
                   AND t.type_meta_ver = m.type_meta_ver
                   AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
                   AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
                   AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
                   AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
                   AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
                   AND m.metric_column = ' '
                   AND m.keys_from_mult_colls = 0
                   AND m.metric_guid = p.metric_guid
                   AND (t.target_guid,p.policy_guid) IN
                  (SELECT pa.object_guid, pa.policy_guid
                          FROM MGMT_POLICY_ASSOC pa
                         WHERE object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET
                         GROUP BY pa.object_guid, pa.policy_guid
                        HAVING COUNT(*) > 1) 
               )
  LOOP

    IF ( (crec.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) AND
         (crec.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME) ) THEN
      -- For host targets, if there are duplicate thresholds for
      -- Response metric, delete the threshold with empty collection name
      l_coll_names := MGMT_MEDIUM_STRING_ARRAY(' ');
    ELSE
      l_coll_names := MGMT_MEDIUM_STRING_ARRAY(crec.metric_name, ' ');
    END IF;


    -- DBMS_OUTPUT.PUT_LINE('Tnm = ' || crec.target_name || 
    --                      ' Ttyp =' || crec.target_type ||
    --                      ' Mnm =' || crec.metric_name ||
    --                      ' Mcol = ' || crec.metric_column ||
    --                      ' Pnm = ' || crec.policy_name ||
    --                      ' Coll =' || l_coll_names(1));


      -- Delete from mgmt_policy_assoc_cfg_params
      DELETE FROM mgmt_policy_assoc_cfg_params
       WHERE object_guid = crec.target_guid
         AND policy_guid = crec.policy_guid
         AND coll_name IN (SELECT * FROM TABLE(CAST(l_coll_names AS MGMT_MEDIUM_STRING_ARRAY)));

      -- Delete from mgmt_policy_assoc_cfg
      DELETE FROM mgmt_policy_assoc_cfg
       WHERE object_guid = crec.target_guid
         AND policy_guid = crec.policy_guid
         AND coll_name IN (SELECT * FROM TABLE(CAST(l_coll_names AS MGMT_MEDIUM_STRING_ARRAY)));

      -- Delete from mgmt_policy_assoc
      DELETE FROM mgmt_policy_assoc
       WHERE object_guid = crec.target_guid
         AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET
         AND policy_guid = crec.policy_guid
         AND coll_name IN (SELECT * FROM TABLE(CAST(l_coll_names AS MGMT_MEDIUM_STRING_ARRAY)));

      -- Delete from mgmt_collection_metric_tasks
      DELETE FROM mgmt_collection_metric_tasks
       WHERE target_guid = crec.target_guid
         AND coll_name IN (SELECT * FROM TABLE(CAST(l_coll_names AS MGMT_MEDIUM_STRING_ARRAY)));

      -- Delete from mgmt_collections
      DELETE FROM mgmt_collections
       WHERE object_guid = crec.target_guid
         AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET
         AND coll_name IN (SELECT * FROM TABLE(CAST(l_coll_names AS MGMT_MEDIUM_STRING_ARRAY)));

      COMMIT;

  END LOOP;


  -- Fix collection names for host/Response metric
  -- Get host Response/Status metric policy guid

  BEGIN
    SELECT DISTINCT metric_guid INTO l_host_avail_metric_guid
      FROM mgmt_metrics
     WHERE target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
       AND metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
       AND metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN;
    EXCEPTION
      WHEN OTHERS THEN
        l_host_avail_metric_guid := NULL;
  END;
  -- DBMS_OUTPUT.PUT_LINE('Host resp stat guid = ' || l_host_avail_metric_guid);

  IF (l_host_avail_metric_guid IS NOT NULL) THEN

    FOR hrec IN (SELECT target_guid 
                   FROM mgmt_targets
                  WHERE target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
                  ORDER BY target_guid)
    LOOP

      -- Update the coll_name to Response for all hosts independent of object type
      UPDATE mgmt_policy_assoc_cfg_params
         SET coll_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
       WHERE policy_guid = l_host_avail_metric_guid
         AND object_guid = hrec.target_guid
         AND coll_name = ' ';

      l_upd_cnt_3 := SQL%ROWCOUNT;

      -- Update mgmt_policy_assoc_cfg
      UPDATE mgmt_policy_assoc_cfg
         SET coll_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
       WHERE policy_guid = l_host_avail_metric_guid
         AND object_guid = hrec.target_guid
         AND coll_name = ' ';

      l_upd_cnt_2 := SQL%ROWCOUNT;

      -- Update mgmt_policy_assoc
      UPDATE mgmt_policy_assoc
         SET coll_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
       WHERE policy_guid = l_host_avail_metric_guid
         AND object_guid = hrec.target_guid
         AND coll_name = ' ';

      l_upd_cnt := SQL%ROWCOUNT;

    END LOOP;

    -- DBMS_OUTPUT.PUT_LINE('Host resp coll update cnt = ' || l_upd_cnt || 
    --                      ' cnt 2 = ' || l_upd_cnt_2 ||
    --                      ' cnt 3 = ' || l_upd_cnt_3 );

    COMMIT;
  END IF;

END;
/


PROMPT End Data fix for bug 5055727 
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;