Rem
Rem $Header: cwmlite/admin/amde111.sql /main/1 2009/02/09 14:06:13 dbardwel Exp $
Rem
Rem amde111.sql
Rem
Rem Copyright (c) 2009, Oracle and/or its affiliates.All rights reserved. 
Rem
Rem    NAME
Rem      amde111.sql
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    dbardwel    02/07/09 - Created, this is the AMD component 11.2 -> 11.1 downgrade script
Rem

alter session set current_schema = olapsys;

execute sys.dbms_registry.downgrading('AMD');

grant select on sys.user$ to olapsys;

create or replace view olapsys.all$olap2_aws
(owner, aw, aw_number, aw_version, sf_version)
as
  select aws11g.owner, aws11g.aw_name, aws11g.aw_number, aws11g.aw_version, '11.1' sf_version
  from all_aws aws11g,
            (select /*+ ordered */ a.awseq#, r.rid, p.rowid, p.propname
              from sys.aw$ a, sys.aw_prop$ p, sys.obj$ o, sys.user$ u,
                   (select max(rowid) keep (dense_rank last order by gen#) rid
                    from sys.aw_prop$ group by awseq#, oid, propname) r
              where a.owner# = u.user#
                and o.owner# = a.owner#
                and o.name = 'AW$' || a.awname and o.type# = 2
                and a.awseq# = p.awseq#
                and p.propname  = 'AW$VERSION11.1'
                and p.rowid = r.rid
                and p.propval is not null) props11g
 where props11g.awseq# = aws11g.aw_number
union all
  select max(aws.owner) owner, max(aws.aw_name) aw, props1.awseq#,
         max(aws.aw_version) aw_version,
               (case when count(props1.awseq#) = 2 then '10.2'
                     when count(props1.awseq#) = 1 then '10.1.0.3'
                     else null end) sf_version
         from all_aws aws,
              (select /*+ ordered */ a.awseq#, r.rid, p.rowid, p.propname
               from sys.aw$ a, sys.aw_prop$ p, sys.obj$ o, sys.user$ u,
                    (select max(rowid) keep (dense_rank last order by gen#) rid
                     from sys.aw_prop$ group by awseq#, oid, propname) r
               where a.owner# = u.user#
                 and o.owner# = a.owner#
                 and o.name = 'AW$' || a.awname and o.type# = 2
                 and a.awseq# = p.awseq#
                 and p.propname in ('AW$VERSION10.2', 'AW$VERSION10.1.0.3')
                 and p.rowid = r.rid
                 and p.propval is not null) props1
  where props1.awseq# = aws.aw_number
  group by awseq#
union all
    select aws2.owner, aws2.aw_name aw, aws2.aw_number, aws2.aw_version, null sf_version
    from all_aws aws2
    where aws2.aw_number not in (
        select props2.awseq# from
         (select a.awseq# 
          from sys.aw$ a, sys.aw_prop$ p, sys.obj$ o, sys.user$ u,
             (select max(rowid) keep (dense_rank last order by gen#) rid
              from sys.aw_prop$ group by awseq#, oid, propname) r
          where a.owner# = u.user#
            and o.owner# = a.owner#
            and o.name = 'AW$' || a.awname and o.type# = 2
            and a.awseq# = p.awseq#
            and p.propname in ( 'AW$VERSION10.1.0.3', 'AW$VERSION11.1')
            and p.rowid = r.rid
            and p.propval is not null) props2
        group by awseq#)
/

grant select on olapsys.all$olap2_aws to public
/

create or replace public synonym ALL_OLAP2_AWS for olapsys.all$olap2_aws
/

@@cwm2awct.pls
@@cwm2awen.pls
@@cwm2mrrf.pls
@@cwm2awct.plb
@@cwm2awen.plb
@@cwm2mrrf.plb

execute sys.dbms_registry.downgraded('AMD', '11.1.0');

alter session set current_schema = sys;