Rem drv: <migrate type="data_upgrade" version="10.2.0.2"/>
Rem $Header: provision_data_upgrade.sql 09-jan-2006.02:03:35 rattipal Exp $
Rem
Rem provision_data_upgrade.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      provision_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    rattipal    01/09/06 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

CREATE OR REPLACE VIEW mgmt_provision_asn_hw_search2
AS
SELECT
    hw.hw_guid, hw.hostname, hw.new_hostname, hw.purpose, hw.serial_number,
    hw.name, hw.description, tgt.current_asn_guid,
    tgt.component_urn, tgt.network_urn, tgt.status, asntgt.status as prov_status,
    summ.system_config, summ.mem, cpu.freq_in_mhz as freq, summ.cpu_count
FROM
    mgmt_prov_hardware hw, mgmt_prov_tgt_status tgt,
    mgmt_prov_asn_targets asntgt, mgmt$os_hw_summary summ, ecm$cpu_details cpu
WHERE
   tgt.current_asn_guid !=  ' '
AND
    hw.hw_guid = asntgt.prov_tgt_guid
AND
    tgt.prov_tgt_guid = asntgt.prov_tgt_guid
AND
    (tgt.current_asn_guid = asntgt.assignment_guid)
AND
    summ.host_name = hw.hostname
AND
    tgt.prov_target_type = 'hw'
AND
   cpu.ecm_snapshot_id =summ.snapshot_guid
UNION
SELECT
    hw.hw_guid, hw.hostname, hw.new_hostname, hw.purpose, hw.serial_number,
    hw.name, hw.description, tgt.current_asn_guid,
    tgt.component_urn, tgt.network_urn, tgt.status, 'STATE_Provisioned' as prov_status,
    summ.system_config, summ.mem, cpu.freq_in_mhz as freq, summ.cpu_count
FROM
    mgmt_prov_hardware hw, mgmt_prov_tgt_status tgt, mgmt$os_hw_summary summ, ecm$cpu_details cpu
WHERE
    hw.hw_guid = tgt.prov_tgt_guid
AND
    decode(rawtohex(tgt.current_asn_guid), '', 'EMPTY') = 'EMPTY'
AND
    summ.host_name = hw.hostname
AND
    tgt.prov_target_type = 'hw'
AND
   cpu.ecm_snapshot_id =summ.snapshot_guid
WITH READ ONLY;

commit;
show errors;