Rem drv: <migrate type="data_upgrade" version="10.2.0.1" pos="collections/collections_data_upgrade.sql+"/>
Rem
Rem $Header: ecm_data_upgrade.sql 23-nov-2005.23:49:52 abhalla Exp $
Rem
Rem ecm_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_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    abhalla     11/23/05 - Backport abhalla_cpfbugs from main 
Rem    abhalla     11/08/05 - adding code for Auto enabling existing targets 
Rem                           for CPF 
Rem    abhalla     10/01/05 - calling ecm_aru_map 
Rem    pdasika     09/16/05 - Changing SETUP_CPF_ADV_HOME_PATCH to RUN_SETUP_CPF_ADV_HOME_PATCH 
Rem    asaraswa    08/25/05 - asaraswa_bug-4571497
Rem    asaraswa    08/24/05 - Created
Rem


Rem convert 10.1.0.2.0 host configuration collection types to 10.2.0.1.0 types
DECLARE
  l_num_rows   NUMBER := 0;
  l_max_commit NUMBER := 3000;

BEGIN
  BEGIN
    LOOP
      UPDATE MGMT_DELTA_IDS
       SET COLLECTION_TYPE =
        CASE COLLECTION_TYPE
          WHEN 'MGMT_HC_HARDWARE_MASTER'   THEN 'ECM$HIST_HARDWARE'
          WHEN 'MGMT_HC_CPU_DETAILS'       THEN 'ECM$HIST_CPU_DETAILS'
          WHEN 'MGMT_HC_IOCARD_DETAILS'    THEN 'ECM$HIST_IOCARD_DETAILS'
          WHEN 'MGMT_HC_NIC_DETAILS'       THEN 'ECM$HIST_NIC_DETAILS'
          WHEN 'MGMT_HC_OS_SUMMARY'        THEN 'ECM$HIST_OS_SUMMARY'
          WHEN 'MGMT_HC_OS_COMPONENTS'     THEN 'ECM$HIST_OS_COMPONENTS'
          WHEN 'MGMT_HC_OS_PROPERTIES'     THEN 'ECM$HIST_OS_PROPERTIES'
          WHEN 'MGMT_HC_FS_MOUNT_DETAILS'  THEN 'ECM$HIST_FS_MOUNT_DETAILS'
          WHEN 'MGMT_HC_VENDOR_SW_SUMMARY' THEN 'ECM$HIST_OS_REGISTERED_SW'
          WHEN 'MGMT_INV_COMPONENT'        THEN 'ECM$HIST_INV_COMPONENTS'
          WHEN 'MGMT_INV_PATCHSET'         THEN 'ECM$HIST_INV_PATCHSETS'
          WHEN 'MGMT_INV_PATCH'            THEN 'ECM$HIST_INV_PATCHES'
        END
       WHERE COLLECTION_TYPE IN (
         'MGMT_HC_HARDWARE_MASTER',
         'MGMT_HC_CPU_DETAILS',
         'MGMT_HC_IOCARD_DETAILS',
         'MGMT_HC_NIC_DETAILS',
         'MGMT_HC_OS_SUMMARY',
         'MGMT_HC_OS_COMPONENTS',
         'MGMT_HC_OS_PROPERTIES',
         'MGMT_HC_FS_MOUNT_DETAILS',
         'MGMT_HC_VENDOR_SW_SUMMARY',
         'MGMT_INV_COMPONENT',
         'MGMT_INV_PATCHSET',
         'MGMT_INV_PATCH' )
       AND ROWNUM <= l_max_commit;

      l_num_rows := SQL%ROWCOUNT;

      COMMIT;

      EXIT WHEN l_num_rows < l_max_commit;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;
  BEGIN
    LOOP
      UPDATE MGMT_DELTA_IDS
       SET COLLECTION_TYPE = 'ECM$HIST_HARDWARE',
           KEY_STRING = ' 0 ' || KEY_STRING
       WHERE COLLECTION_TYPE = 'MGMT_HC_SYSTEM_SUMMARY'
       AND ROWNUM <= l_max_commit;

      l_num_rows := SQL%ROWCOUNT;

      COMMIT;

      EXIT WHEN l_num_rows < l_max_commit;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;
END;
/

--populate patches column of mgmt_hc_os_summary

DECLARE
l_counter NUMBER := 0;
  TYPE SnapTab is TABLE OF mgmt_ecm_snapshot.snapshot_guid%TYPE;
  CURSOR c1 is 
    SELECT 
      snapshot_guid
    FROM   
      mgmt_ecm_snapshot 
    WHERE 
      snapshot_type = 'host_configuration'
      and target_type = 'host';
  guids SnapTab;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 BULK COLLECT INTO guids LIMIT MGMT_GLOBAL.MAX_COMMIT;
    FORALL i in 1 .. guids.count
      UPDATE mgmt_hc_os_summary
      SET patches =
        (
          SELECT count(*)
          FROM mgmt_hc_os_components c
          WHERE c.snapshot_guid = guids(i)
            and c.type='Patch'
        )
      WHERE
        snapshot_guid = guids(i);
    COMMIT;
    EXIT WHEN c1%NOTFOUND;
  END LOOP;
COMMIT;
END;
/

@&EM_SQL_ROOT/core/v102020/ecm/ecm_aru_info_load.sql
@&EM_SQL_ROOT/core/v102020/ecm/ecm_aru_map.sql
@&EM_SQL_ROOT/core/v102020/ecm/patch/critical/delete_cpf_policy.sql
@&EM_SQL_ROOT/core/v102020/ecm/patch/critical/create_cpf_policy.sql

--Establishing associations with CPF Policy for existing targets 
BEGIN
 MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
          p_target_type => 'host',
          p_policy_name => 'Critical Patch Advisories for Oracle Homes' );
   COMMIT;
END;
/ 

-- Populate MGMT_BUG_ADV_HOME_PATCH with new data
BEGIN
   CPF_POLICY.RUN_CPF_POLICY_EVALUATION();
   COMMIT;
END;
/