Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" pos="admin_schema_upgrade.sql+"/> 
Rem
Rem $Header: assoc_schema_upgrade.sql 09-aug-2005.03:09:42 pmodi Exp $
Rem
Rem assoc_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      assoc_schema_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       08/09/05 - Chg in assoc creation script 
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - modify the version number for the header 
Rem    pmodi       04/13/05 - New columns in assoc flat table 
Rem    pmodi       02/16/05 - header correction
Rem    ramlhot     02/09/05 - created
Rem

Prompt Creating Associations Related Tables.. 

@@assoc_tables.sql

Prompt Creating Associations Related Types.. 

@@assoc_types.sql

Prompt Moving recards from mgmt_targets_memberships to mgmt_target_assocs table..
BEGIN

-- Insert memebrship only for those targets which exists in mgmt_targets
-- i.e both composite_target_guid and member_target_guid should exists in mgmt_targets
-- If either of composite_target_guid and member_target_guid does not exists in mgmt_targets
-- then association should be created in mgmt_target_pending_assocs
INSERT
  INTO mgmt_target_assocs
       (assoc_guid, source_target_guid, assoc_target_guid)
       SELECT 'CC038C4568AE2A331C948E492A0401DF',
              composite_target_guid,
              member_target_guid
         FROM mgmt_target_memberships
         WHERE EXISTS (SELECT 1
                         FROM mgmt_targets
                        WHERE target_guid = composite_target_guid )
           AND EXISTS (SELECT 1
                         FROM mgmt_targets
                        WHERE target_guid = member_target_guid );

UPDATE mgmt_target_assocs
   SET is_editable = 1,
       created_by =2
 WHERE source_target_guid in(SELECT target_guid
                               FROM mgmt_targets t,
                                    mgmt_type_properties p
                              WHERE p.property_name = 'is_composite'
                                AND p.target_type = t.target_type);                                   

-- Create pending associations
-- If either of composite_target_guid and member_target_guid does not exists
-- then create entry in mgmt_target_pending_assocs
INSERT
  INTO mgmt_target_pending_assocs
       (assoc_guid, source_target_guid, assoc_target_guid)
       SELECT 'CC038C4568AE2A331C948E492A0401DF',
              composite_target_guid,
              member_target_guid
         FROM mgmt_target_memberships
         WHERE NOT EXISTS (SELECT 1
                             FROM mgmt_targets
                            WHERE target_guid = composite_target_guid )
            OR NOT EXISTS (SELECT 1
                             FROM mgmt_targets
                            WHERE target_guid = member_target_guid );
END;
/

Prompt Moving recards from mgmt_flat_target_memberships to mgmt_flat_target_assoc table..
INSERT 
  INTO mgmt_flat_target_assoc
       (source_target_guid, assoc_target_guid, is_membership)
       SELECT composite_target_guid, member_target_guid, 1
         FROM mgmt_flat_target_memberships ;


Prompt Creating Indexes on Associations Related Tables.. 

@@assoc_indexes.sql

Prompt Droping old membership tables 
DROP TABLE mgmt_target_memberships ;
DROP TABLE mgmt_flat_target_memberships;