Rem drv: <migrate type="data_upgrade" version="10.2.0.3" />
Rem
Rem $Header: provision_data_upgrade.sql 30-apr-2006.23:05:21 kashukla 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    kashukla    04/30/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_prov_suite_hw_cnt
AS
SELECT
    suite_inst_guid, sum(member_count) as member_count
FROM
(
    SELECT
        st.suite_inst_guid, SUM(clus.node_count) as member_count
    FROM
        mgmt_provision_clus_node_cnt clus, mgmt_prov_suite_inst_members st
    WHERE
        st.member_guid =  clus.cluster_guid and st.member_type = 'cluster'
    GROUP BY
        st.suite_inst_guid
    UNION ALL
    SELECT
        suite_inst_guid, count(*) as member_count
    FROM
        mgmt_prov_suite_inst_members
    WHERE
        member_type = 'hw'
    GROUP BY
        suite_inst_guid
)
GROUP BY suite_inst_guid
WITH READ ONLY;




CREATE OR REPLACE VIEW mgmt_provision_si_status
AS
SELECT
    si.suite_inst_guid, si.name, si.description, si.purpose,
    tgt.current_asn_guid, si.suite_urn, tgt.network_urn, tgt.status,
    smc.member_count
FROM
    mgmt_prov_suite_instance si
INNER JOIN
   mgmt_prov_tgt_status tgt
ON
   si.suite_inst_guid = tgt.prov_tgt_guid
AND
   tgt.prov_target_type = 'suiteInstance'
LEFT OUTER JOIN
   mgmt_prov_suite_hw_cnt smc
ON
   smc.suite_inst_guid = si.suite_inst_guid
WITH READ ONLY;

commit;
show errors;