Rem drv: <migrate type="schema_upgrade" version="10.2.0.4"/>
Rem
Rem $Header: ecm_schema_upgrade.sql 17-apr-2007.06:17:59 denath Exp $
Rem
Rem ecm_schema_upgrade.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_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    denath      04/13/07 - Bug fix 5479717. changed definition of
Rem                           mgmt$cpf_homes_info view for performance.
Rem    denath      04/13/07 - Created
Rem

--View Name: MGMT$CPF_HOMES_INFO
--Description: Gets the CPF Specific Oracle Home Information
CREATE OR REPLACE VIEW MGMT$CPF_HOMES_INFO
AS
select  /*+ ORDERED */
        cont.container_guid                            as home_id,
        tgt.target_guid                                as host_target_guid,
        tgt.target_name                                as home_host,
        cont.container_location                        as home_directory,
        cont.container_name                            as home_name,
        decode(prop.property_value,
               null, decode(cont.oui_platform,
                            610,    212,
                            87,     87,
                            197,    197,
                            2,      59,
                            211,    209,
                            110,    214,
                            46,     46,
                            912,    912,
                            208,    208,
                            453,    453,
                            23,     23,
                            173,    173
                           ),
                prop.property_value)                   as home_aru_platform,
        decode(prop.property_value,
               null, 'OUI',
               'ARU')                                  as home_platform_type,
        summ.map_target_type                           as home_target_type,
        summ.comp_version                              as home_version,
        replace(host.address_length_in_bits,
                '-bit', '')                            as home_host_bit_length
from
        mgmt_targets                                   tgt,
        mgmt_ecm_snapshot                              snap,
        mgmt_inv_container                             cont,
        mgmt_inv_summary                               summ,
        mgmt_inv_container_property                    prop,
        mgmt_hc_os_summary                             host
where
        tgt.target_type = 'host'                       and
        tgt.target_type = snap.target_type             and
        tgt.target_name = snap.target_name             and
        snap.is_current = 'Y'                          and
        snap.snapshot_type = 'host_configuration'      and
        snap.snapshot_guid = cont.snapshot_guid        and
        host.snapshot_guid = snap.snapshot_guid        and
        cont.container_type = 'O'                      and
        cont.container_guid = summ.container_guid      and
        cont.container_guid = prop.container_guid (+)  and
        'ARU_PLATFORM_ID' = prop.property_name (+)     and
        snap.snapshot_guid = summ.snapshot_guid;