Rem drv: <migrate type="data_upgrade" version="10.2.0.1"/>
Rem
Rem $Header: group_data_upgrade.sql 28-sep-2005.14:30:35 pmodi Exp $
Rem
Rem group_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      group_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    pmodi       09/28/05 - pmodi_bug-4636221
Rem    pmodi       09/28/05 - Created
Rem

-- Bug:4636221
-- This will delete all old collections (of summary metrics) for composite target type, which have been migrated as chart.
-- 

BEGIN
  FOR rec IN (SELECT coll_name , target_name, target_type
                FROM mgmt_collections, mgmt_targets
               WHERE object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET
                 AND object_guid = target_guid
                 AND target_type = MGMT_GLOBAL.G_COMPOSITE_TARGET_TYPE
                 AND coll_name IN ( SELECT metric_name 
                                      FROM mgmt_metrics
                                     WHERE target_type = MGMT_GLOBAL.G_COMPOSITE_TARGET_TYPE
                                       AND source = 'EMD_COMPOSITE_TARGETS.EVALUATE_SUMMARY_METRICS'
                                       AND metric_name != 'Summary Metrics') 
              )
  LOOP
                                      
    MGMT_COLLECTION.delete_collection(p_target_type  => rec.target_type,
                                      p_target_name  => rec.target_name,
                                      p_coll_name    => rec.coll_name) ;
  END LOOP;

END;
/
COMMIT;