Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      amdu920.sql - migration script for upgrading olap catalog
Rem
Rem    DESCRIPTION
Rem      Upgrade olap catalog from 9.2.0.x
Rem
Rem    NOTES
Rem
Rem      Current equivalent to a 9202 upgrade
Rem 
Rem    MODIFIED   (MM/DD/YY)
Rem       dbardwel 05/28/08 - Fix bug 6683296 by moving calling apsroles.sql above grant added for roles
Rem       glyon    06/22/07 - replace grants to public with roles
Rem    ddedonat    04/16/07 - add call to apsroles.sql to make sure olap_user role is created to fix 9.2 to 11g upgrade bug, 5980619 
Rem    awesley     09/21/06 - add call to amdu102.sql, removeing call to amdrelod, call @@cwm2iner.pls and @@cwm2iner.plb so 
Rem                           Validate_CWM2_Install will work
Rem    awesley     04/12/06 - change display_name in cwm2$mrall_hierdims to 240
Rem    awesley     07/05/05 - change display_name and plural_name to change displayname and pluralname
Rem    awesley     06/21/05 - change display name and plural name length 
Rem    cdalessi    05/17/05 - Break out 1 alter into 2.
Rem    dbardwel    04/25/05 - add one performance index
Rem    dbardwel    04/24/05 - Additional 3 columns to cwm2$mrall_facttblkeymaps bug 3996129
Rem    cdalessi    03/15/05 - Drop obsolete package
Rem    cdalessi    02/07/05 - Drop obsolete view.
Rem    cdalessi    12/02/04 - Move olap_user grant to apsroles.sql 
Rem    cdalessi    10/06/04 - Move validation to SYS 
Rem    dbardwel    09/29/04 - 
Rem    cdalessi    09/17/04 - 
Rem    dbardwel    09/15/04 - Drop public synonym and package dbms_aw_utilities for 10.2
Rem                           also increase mr_refresh property value column to varchar2(4000).
Rem    cdalessi    09/07/04 - 
Rem    cdalessi    08/03/04 - Merge sub-files up and revamp
Rem    cdalessi    04/27/04 - Add some missing grants
Rem    cdalessi    04/23/04 - Remove references to all_aw_numbers
Rem    cdalessi    12/30/03 - 
Rem    cdalessi    12/30/03 - Move stuff from amdu9204.sql 
Rem    cdalessi    11/03/03 - fix banner
Rem    cdalessi    09/02/03 - 
Rem    cdalessi    08/29/03 - Rearrange relod and add MR table truncate 
Rem    cdalessi    07/29/03 - Migration fixes and grants
Rem    cdalessi    05/29/03 - Refocus version checks.
Rem    cdalessi    02/17/03 - support for multiple 920 versions and patch releases
Rem    cdalessi    12/09/02 - 10i migration changes
Rem    cdalessi    11/19/02 - Remove ? notation; replace with @@ notation
Rem    cdalessi    10/18/02 - cdalessi_txn103996
Rem    cdalessi    10/14/02 - 
Rem    cdalessi    08/15/02 - Creation

drop package sys.cwm2_olap_dbms_aw_lib;
grant execute on sys.dbms_aw_lib to olapsys;
grant create job to OLAP_DBA;

alter session set current_schema = olapsys;

execute sys.dbms_registry.upgrading('AMD' ,'OLAP Catalog' ,'cwm2_olap_installer.Validate_CWM2_Install' ,'OLAPSYS');

create table cwm2$awcubeload (
    aggregationindicator varchar2(30),
    createddate date,
    cube_irid number,
    irid number not null,
    lastchangeuser varchar2(30),
    loadprocesscode_irid number,
    measureloadindicator varchar2(30),
    name varchar2(60),
    updateddate date ) ;

create table cwm2$awcubeloadfilter (
    cubeload_irid number,
    facttblname varchar2(30),
    facttblowner varchar2(30),
    filtercondition varchar2(4000),
    irid number not null ) ;

create table cwm2$awdimload (
    createddate date,
    dim_irid number,
    irid number not null,
    lastchangeuser varchar2(30),
    loadprocesscode_irid number,
    name varchar2(60),
    uniquekeys varchar2(1),
    updateddate date ) ;

create table cwm2$awdimloadfilter (
    dimload_irid number,
    filtercondition varchar2(4000),
    irid number not null,
    lkuptblname varchar2(30),
    lkuptblowner varchar2(30) ) ;

create table cwm2$awprocessstate (
    description varchar2(255),
    irid number not null,
    name varchar2(60) ) ;

create table cwm2$awsqlgencodes (
    description varchar2(255),
    irid number not null,
    name varchar2(60) ) ;

drop table cwm2$awcubeconfig cascade constraints;
drop table cwm2$awcubeloadmeasures cascade constraints;
drop table cwm2$awcubeloadprocess cascade constraints;
drop table cwm2$awcubeloaduse cascade constraints;
drop table cwm2$awcubeprocess cascade constraints;
drop table cwm2$awcubetuples cascade constraints;
drop table cwm2$awdimconfig cascade constraints;
drop table cwm2$awdimloadprocess cascade constraints;
drop table cwm2$awdimloaduse cascade constraints;
drop table cwm2$awdimprocess cascade constraints;
drop table cwm2$awdimsqlgetlog cascade constraints;
drop table cwm2$awlogicalattruse cascade constraints;
drop table cwm2$awlogicaldimuse cascade constraints;
drop table cwm2$awlogicalhieruse cascade constraints;
drop table cwm2$awlogicalleveluse cascade constraints;
drop table cwm2$awlogicalmeasuse cascade constraints;
drop table cwm2$awlogicalobjlimitsetuse cascade constraints;
drop table cwm2$awphysicalobj cascade constraints;
drop table cwm2$awphysicalobjext cascade constraints;
drop table cwm2$awphysicalobjprops cascade constraints;
drop table cwm2$awphysicalobjrelatedobjs cascade constraints;
drop table cwm2$awprocessstate cascade constraints;
drop table cwm2$awsqlgencodes cascade constraints;

create table cwm2$mrall_catalog_entity_uses (
    catalog_id number,
    child_entity_name varchar2(240),
    entity_name varchar2(240),
    entity_owner varchar2(240),
    id number,
    version_id char(4) ) ;

create table cwm2$mrall_catalogs (
    catalog_id number,
    catalog_name varchar2(240),
    description varchar2(4000),
    parent_catalog_id number ) ;

create table cwm2$mrall_cube_measures (
    cube_name varchar2(240),
    description varchar2(4000),
    display_name varchar2(240),
    id number,
    measure_name varchar2(240),
    owner varchar2(240),
    version_id char(4) ) ;

create table cwm2$mrall_descriptors (
    description varchar2(4000),
    descriptor_id number(38),
    descriptor_type varchar2(240),
    descriptor_value varchar2(240) ) ;

create table cwm2$mrall_dim_attributes (
    attribute_name varchar2(240),
    desc_id number(38),
    description varchar2(4000),
    dimension_name varchar2(30),
    display_name varchar2(240),
    id number,
    owner varchar2(30),
    version_id char(4) ) ;

create table cwm2$mrall_dim_hier_level_uses (
    child_level_name varchar2(30),
    dimension_name varchar2(30),
    hierarchy_name varchar2(30),
    id number,
    owner varchar2(30),
    parent_level_name varchar2(30),
    position number,
    version_id char(4) ) ;

create table cwm2$mrall_dim_hiers (
    description varchar2(4000),
    dimension_name varchar2(30),
    display_name varchar2(240),
    hierarchy_name varchar2(30),
    id number,
    is_default varchar2(1),
    owner varchar2(30),
    solved_code varchar2(2),
    version_id char(4) ) ;

create table cwm2$mrall_dim_level_attr_maps (
    attribute_name varchar2(240),
    column_name varchar2(30),
    dimension_name varchar2(30),
    dtype varchar2(10),
    hierarchy_name varchar2(30),
    id number,
    level_name varchar2(30),
    lvl_attribute_name varchar2(240),
    owner varchar2(30),
    table_name varchar2(30),
    table_owner varchar2(30),
    version_id char(4) ) ;

create table cwm2$mrall_entity_desc_uses (
    child_entity_name varchar2(240),
    descriptor_id number,
    entity_name varchar2(240),
    entity_owner varchar2(240),
    id number,
    secondary_child_entity_name varchar2(30),
    version_id char(4) ) ;

create table cwm2$mrall_facttblfctmaps (
    column_name varchar2(30),
    cube_name varchar2(30),
    data_type number,
    dim_hier_combo_id number,
    fact_table_name varchar2(30),
    fact_table_owner varchar2(30),
    id number,
    measure_name varchar2(30),
    owner varchar2(30),
    version_id char(4) ) ;

create table cwm2$mrall_facttblkeymaps (
    column_name varchar2(30),
    column_position number,
    cube_name varchar2(30),
    dim_hier_combo_id number,
    dimension_keymap_type number,
    dimension_name varchar2(30),
    dimension_owner varchar2(30),
    fact_table_name varchar2(30),
    fact_table_owner varchar2(30),
    gid_column_name varchar2(30),
    hierarchy_name varchar2(30),
    id number,
    mv_summarycode varchar2(2),
    owner varchar2(30),
    version_id char(4) ) ;

create table cwm2$mrall_hier_custom_sort (
    column_name varchar2(30),
    dimension_name varchar2(30),
    hierarchy_name varchar2(30),
    id number,
    null_order varchar2(5),
    owner varchar2(30),
    position number,
    sort_order varchar2(4),
    sort_pos number,
    table_name varchar2(30),
    table_owner varchar2(30),
    version_id char(4) ) ;

create table cwm2$mrall_hierdim_keycol_map (
    column_name varchar2(30),
    column_position number,
    description varchar2(4000),
    dimension_name varchar2(30),
    display_name varchar2(240),
    hierarchy_name varchar2(30),
    hierarchy_position number,
    id number,
    level_name varchar2(30),
    owner varchar2(30),
    table_name varchar2(30),
    table_owner varchar2(30),
    version_id char(4) ) ;

create table cwm2$mrall_hierdims (
    default_display_hierarchy varchar2(30),
    description varchar2(2000),
    descriptor_value varchar2(30),
    dimension_name varchar2(240),
    display_name varchar2(240),
    id number,
    owner varchar2(30),
    plural_name varchar2(30),
    version_id char(4) ) ;

create table cwm2$mrall_join_key_col_uses (
    child_level_name varchar2(30),
    column_name varchar2(30),
    dimension_name varchar2(30),
    hierarchy_name varchar2(30),
    id number,
    join_key_type varchar2(30),
    owner varchar2(30),
    position number,
    table_name varchar2(30),
    table_owner varchar2(30),
    version_id char(4) ) ;

create table cwm2$mrall_listdims (
    column_name varchar2(30),
    column_position number,
    description varchar2(4000),
    descriptor_value varchar2(30),
    dimension_name varchar2(30),
    display_name varchar2(240),
    id number,
    owner varchar2(30),
    plural_name varchar2(240),
    table_name varchar2(30),
    table_owner varchar2(30),
    version_id char(4) ) ;

create table cwm2$mrfacttblfctmaps (
    column_name varchar2(30),
    cube_name varchar2(240),
    data_type varchar2(30),
    fact_table_name varchar2(30),
    fact_table_owner varchar2(30),
    id number,
    measure_name varchar2(240),
    owner varchar2(240),
    version_id char(4) ) ;

create table cwm2$mrfacttblkeymaps (
    column_name varchar2(30),
    column_position number,
    cube_name varchar2(240),
    dimension_name varchar2(30),
    dimension_owner varchar2(30),
    fact_table_name varchar2(30),
    fact_table_owner varchar2(30),
    id number,
    level_name varchar2(30),
    mv_summary_code varchar2(2),
    owner varchar2(240),
    version_id char(4) ) ;

create global temporary table olap_session_cubes (
    id number,
    version_id char(4) ) ;

create global temporary table olap_session_dims (
    id number,
    version_id char(4) ) ;

create global temporary table olap_session_objects (
    id number,
    objtype char(4),
    version_id char(4) ) ;

begin
  execute immediate 'alter table cwm2$awdimload drop constraint cwm2$awdimload_pk';
  exception when others then null;
end;
/
begin
  execute immediate 'alter table cwm2$awdimload add ( constraint cwm2$awdimload_pk primary key ( irid ) )' ;
  exception when others then null;
end;
/

alter table cwm2$awdimloadfilter drop constraint cwm2$awdimloadfilter_pk;
alter table cwm2$awdimloadfilter add ( constraint cwm2$awdimloadfilter_pk primary key ( irid ) ) ;

begin
  execute immediate 'alter table cwm2$awcubeload drop constraint cwm2$awcubeload_pk';
  exception when others then null;
end;
/
begin
  execute immediate 'alter table cwm2$awcubeload add ( constraint cwm2$awcubeload_pk primary key ( irid ) )';
  exception when others then null;
end;
/

alter table cwm2$awcubeloadfilter drop constraint cwm2$awcubeloadfilter_pk;
alter table cwm2$awcubeloadfilter add ( constraint cwm2$awcubeloadfilter_pk primary key ( irid ) ) ;

begin
  execute immediate 'alter table cwm2$awcubeload set unused (aggregationindicator,loadprocesscode_irid,measureloadindicator)';
  exception when others then null;
end;
/

alter table cwm2$awcubeload modify (
    cube_irid number not null
);
alter table cwm2$awcubeload add (
    loadtype_irid number,
    version_id varchar2(10)
);

begin
  execute immediate 'alter table cwm2$awcubeloadfilter set unused (facttblname,facttblowner)';
  exception when others then null;
end;
/

alter table cwm2$awcubeloadfilter modify (
    cubeload_irid number not null
);

alter table cwm2$awcubeloadfilter add (
    createddate date,
    fact_table_name varchar2(30),
    fact_table_owner varchar2(30),
    lastchangeuser varchar2(30),
    updateddate date
);

begin
  execute immediate 'alter table cwm2$awdimload set unused (loadprocesscode_irid,uniquekeys)';
  exception when others then null;
end;
/


alter table cwm2$awdimload modify (
    dim_irid number not null
);
alter table cwm2$awdimload add (
    loadtype_irid number,
    version_id varchar2(10)
);

begin
  execute immediate 'alter table cwm2$awdimloadfilter set unused (lkuptblname,lkuptblowner)';
  exception when others then null;
end;
/

alter table cwm2$awdimloadfilter modify (
    dimload_irid number not null
);
alter table cwm2$awdimloadfilter add (
    createddate date,
    lastchangeuser varchar2(30),
    lookup_table_name varchar2(30),
    lookup_table_owner varchar2(30),
    updateddate date
);

alter table cwm2$mrall_cube_measures add (
    shortdescription varchar2(240)
);

alter table cwm2$mrall_dim_attributes add (
    shortdescription varchar2(240)
);

alter table cwm2$mrall_dim_hiers add (
    shortdescription varchar2(240)
);

alter table cwm2$mrall_hierdim_keycol_map add (
    shortdescription varchar2(240)
);

alter table cwm2$mrall_hierdims add (
    shortdescription varchar2(240)
);

alter table cwm2$mrall_listdims add (
    shortdescription varchar2(240)
);

create table cwm2$_aw_next_perm_cust_meas (
    aw_access_view_name varchar2(61),
    num_cust_measure varchar2(30),
    text_cust_measure varchar2(30) ) ;

create global temporary table cwm2$_aw_next_temp_cust_meas (
    aw_access_view_name varchar2(61),
    num_cust_measure varchar2(30),
    text_cust_measure varchar2(30) ) on commit preserve rows ;

create table cwm2$_aw_perm_cust_meas_map (
    aw_access_view_name varchar2(61) not null,
    aw_measure_name varchar2(64),
    cust_adt_column varchar2(30) not null,
    sessionid varchar2(10),
    username varchar2(30),
    workspace_name varchar2(61) ) ;

create global temporary table cwm2$_aw_temp_cust_meas_map (
    aw_access_view_name varchar2(61) not null,
    aw_measure_name varchar2(64),
    cust_adt_column varchar2(30) not null,
    sessionid varchar2(10),
    username varchar2(30),
    workspace_name varchar2(61) ) on commit preserve rows ;

create global temporary table cwm2$_temp_values (
    int_var number(30),
    num_var number(36,6),
    text_var varchar2(255) ) on commit preserve rows ;

create table cwm2$awcompositespec (
    createddate date,
    cube_irid number not null,
    irid number not null,
    lastchangeuser varchar2(30),
    name varchar2(30),
    updateddate date,
    version_id varchar2(10) ) ;

create table cwm2$awcompspecmembership (
    composite_irid number,
    composite_position number,
    compspec_irid number not null,
    compspec_position number,
    createddate date,
    dim_irid number,
    irid number not null,
    lastchangeuser varchar2(30),
    membertype varchar2(10),
    name varchar2(30),
    segwidth number,
    updateddate date ) ;

create table cwm2$awcubeagg (
    createddate date,
    cube_irid number not null,
    irid number not null,
    lastchangeuser varchar2(30),
    name varchar2(60),
    updateddate date,
    version_id varchar2(10) ) ;

create table cwm2$awcubeagglevel (
    createddate date,
    cubeagg_irid number not null,
    dim_irid number not null,
    irid number not null,
    lastchangeuser varchar2(30),
    level_irid number not null,
    updateddate date ) ;

create table cwm2$awcubeaggmeasure (
    createddate date,
    cubeagg_irid number not null,
    irid number not null,
    lastchangeuser varchar2(30),
    measure_irid number not null,
    updateddate date ) ;

create table cwm2$awcubecompplan (
    createddate date,
    cubecompspec_irid number not null,
    cubeload_irid number not null,
    irid number not null,
    lastchangeuser varchar2(30),
    updateddate date ) ;

create global temporary table cwm2$awcubecreateaccess (
    cube_name varchar2(30),
    cube_owner varchar2(30),
    processaction varchar2(30),
    query_text clob,
    runid# number ) on commit preserve rows ;

create table cwm2$awcubeloadaggplan (
    createddate date,
    cubeagg_irid number not null,
    cubeload_irid number not null,
    irid number not null,
    lastchangeuser varchar2(30),
    updateddate date ) ;

create table cwm2$awcubeloadmeasure (
    createddate date,
    cubeload_irid number not null,
    displayname varchar2(60),
    irid number not null,
    lastchangeuser varchar2(30),
    measure_irid number not null,
    name varchar2(60),
    updateddate date ) ;

create table cwm2$awcubeloadparm (
    datatype varchar2(30),
    default_value varchar2(30),
    irid number not null,
    name varchar2(16) ) ;

create table cwm2$awcubeloadparmvalue (
    createddate date,
    cubeload_irid number not null,
    irid number not null,
    lastchangeuser varchar2(30),
    parmname_irid number not null,
    updateddate date,
    value varchar2(30) ) ;

create table cwm2$awcubeloadtype (
    description varchar2(255),
    irid number not null,
    name varchar2(60) ) ;

create global temporary table cwm2$awdimcreateaccess (
    dim_name varchar2(30),
    dim_owner varchar2(30),
    processaction varchar2(30),
    query_text clob,
    runid# number ) on commit preserve rows ;

create table cwm2$awdimloadparm (
    datatype varchar2(30),
    default_value varchar2(30),
    irid number not null,
    name varchar2(16) ) ;

create table cwm2$awdimloadparmvalue (
    createddate date,
    dimload_irid number not null,
    irid number not null,
    lastchangeuser varchar2(30),
    parmname_irid number not null,
    updateddate date,
    value varchar2(30) ) ;

create table cwm2$awdimloadtype (
    description varchar2(255),
    irid number not null,
    name varchar2(60) ) ;

create table cwm2$awviews (
    createddate date,
    irid number not null,
    lastchangeuser varchar2(30),
    rowtocellcol_id number,
    table_id number not null,
    updateddate date,
    view_name varchar2(30) not null,
    view_owner varchar2(30) not null ) ;

create table cwm2$mrall_awviews (
    rowtocellcol_name varchar2(30),
    view_name varchar2(30),
    view_owner varchar2(30) ) ;

begin
  execute immediate 'alter table cwm2$awdimloadparmvalue add ( constraint cwm2$awdimloadparmvalue_pk primary key (irid) )' ;
  exception when others then null;
end;
/

begin
  execute immediate 'alter table cwm2$awdimloadparm add ( constraint cwm2$awdimloadparm_pk primary key (irid) )';
  exception when others then null;
end;
/

begin
  execute immediate 'alter table cwm2$awdimloadtype add ( constraint cwm2$awdimloadtype_pk primary key (irid) )';
  exception when others then null;
end;
/

begin
  execute immediate 'alter table cwm2$awcubeloadparmvalue add ( constraint cwm2$awcubeloadparmvalue_pk primary key (irid) )';
  exception when others then null;
end;
/

begin
  execute immediate 'alter table cwm2$awcubeloadparm add ( constraint cwm2$awcubeloadparm_pk primary key (irid) )';
  exception when others then null;
end;
/

begin
  execute immediate 'alter table cwm2$awcubeloadtype add ( constraint cwm2$awcubeloadtype_pk primary key (irid) )';
  exception when others then null;
end;
/

begin
  execute immediate 'alter table cwm2$awcubeloadmeasure add ( constraint cwm2$awcubeloadmeasure_pk primary key (irid) )';
  exception when others then null;
end;
/

begin
 execute immediate 'alter table cwm2$awcubeloadaggplan add ( constraint cwm2$awcubeloadaggplan_pk primary key (irid) )';
  exception when others then null;
end;
/

begin
  execute immediate 'alter table cwm2$awcubeagg add ( constraint cwm2$awcubeagg_pk primary key (irid) )';
  exception when others then null;
end;
/

begin
  execute immediate 'alter table cwm2$awcubeagglevel add ( constraint cwm2$awcubeagglevel_pk primary key (irid) )';
  exception when others then null;
end;
/

begin
  execute immediate 'alter table cwm2$awcubeaggmeasure add ( constraint cwm2$awcubeaggmeasure_pk primary key (irid) )';
  exception when others then null;
end;
/

begin
  execute immediate 'alter table cwm2$awcompositespec add ( constraint cwm2$awcompositespec_pk primary key (irid) )';
  exception when others then null;
end;
/

begin
  execute immediate 'alter table cwm2$awcompspecmembership add ( constraint cwm2$awcompspecmembership_pk primary key (irid) )';
  exception when others then null;
end;
/

begin
  execute immediate 'alter table cwm2$awcubecompplan add ( constraint cwm2$awcubecompplan_pk primary key (irid) )';
  exception when others then null;
end;
/


alter table cwm2$awdimloadfilter drop constraint cwm2$awdimloadfilter_name;
alter table cwm2$awdimloadfilter add ( constraint cwm2$awdimloadfilter_id unique ( dimload_irid, lookup_table_owner, lookup_table_name ) ) ;
alter table cwm2$awdimloadparmvalue drop constraint cwm2$awdimloadparmvalue_name;
alter table cwm2$awdimloadparmvalue add ( constraint cwm2$awdimloadparmvalue_id unique ( dimload_irid, parmname_irid ) ) ;
alter table cwm2$awcubeloadfilter drop constraint cwm2$awcubeloadfilter_name;
alter table cwm2$awcubeloadfilter add ( constraint cwm2$awcubeloadfilter_id unique ( cubeload_irid, fact_table_owner, fact_table_name ) ) ;
alter table cwm2$awcubeloadparmvalue drop constraint cwm2$awcubeloadparmvalue_name;
alter table cwm2$awcubeloadparmvalue add ( constraint cwm2$awcubeloadparmvalue_id unique ( cubeload_irid, parmname_irid ) ) ;
alter table cwm2$awcubeagglevel drop constraint cwm2$awcubeagglevel_name;
alter table cwm2$awcubeagglevel add ( constraint cwm2$awcubeagglevel_id unique ( cubeagg_irid, dim_irid, level_irid ) ) ;
alter table cwm2$awcubeaggmeasure drop constraint cwm2$awcubeaggmeasure_name;
alter table cwm2$awcubeaggmeasure add ( constraint cwm2$awcubeaggmeasure_id unique ( cubeagg_irid, measure_irid ) ) ;
alter table cwm2$awcompositespec drop constraint cwm2$awcompositespec_name;
alter table cwm2$awcompositespec add ( constraint cwm2$awcompositespec_id unique ( cube_irid ,version_id, name ) ) ;
alter table cwm2$awcompspecmembership drop constraint cwm2$awcompspecmember_name;
alter table cwm2$awdimloadfilter add constraint cwm2$awdimloadfilter_fk1 foreign key ( dimload_irid ) references cwm2$awdimload ( irid ) on delete cascade ;
alter table cwm2$awdimloadparmvalue add constraint cwm2$awdimloadparmvalue_fk1 foreign key ( dimload_irid ) references cwm2$awdimload ( irid ) on delete cascade ;
alter table cwm2$awcubeloadfilter add constraint cwm2$awcubeloadfilter_fk1 foreign key ( cubeload_irid ) references cwm2$awcubeload ( irid ) on delete cascade ;
alter table cwm2$awcubeloadparmvalue add constraint cwm2$awcubeloadparmvalue_fk1 foreign key ( cubeload_irid ) references cwm2$awcubeload ( irid ) on delete cascade ;
alter table cwm2$awcubeloadmeasure add constraint cwm2$awcubeloadmeasure_fk foreign key ( cubeload_irid ) references cwm2$awcubeload ( irid ) on delete cascade ;
alter table cwm2$awcubeagglevel add constraint cwm2$awcubeagglevel_fk1 foreign key ( cubeagg_irid ) references cwm2$awcubeagg ( irid ) on delete cascade ;
alter table cwm2$awcubeaggmeasure add constraint cwm2$awcubeaggmeasure_fk1 foreign key ( cubeagg_irid ) references cwm2$awcubeagg ( irid ) on delete cascade ;
alter table cwm2$awcubeloadaggplan add constraint cwm2$awcubeloadaggplan_fk1 foreign key ( cubeload_irid ) references cwm2$awcubeload ( irid ) on delete cascade ;
alter table cwm2$awcubeloadaggplan add constraint cwm2$awcubeloadaggplan_fk2 foreign key ( cubeagg_irid ) references cwm2$awcubeagg ( irid ) on delete cascade ;
alter table cwm2$awcompspecmembership add constraint cwm2$awcompspecmembership_fk1 foreign key ( compspec_irid ) references cwm2$awcompositespec ( irid ) on delete cascade ;
alter table cwm2$awcompspecmembership add constraint cwm2$awcompspecmembership_fk2 foreign key ( composite_irid ) references cwm2$awcompspecmembership ( irid ) on delete cascade ;
alter table cwm2$awcubecompplan add constraint cwm2$awcubecompplan_fk1 foreign key ( cubeload_irid ) references cwm2$awcubeload ( irid ) on delete cascade ;
alter table cwm2$awcubecompplan add constraint cwm2$awcubecompplan_fk2 foreign key ( cubecompspec_irid ) references cwm2$awcompositespec ( irid ) on delete cascade ;
alter table cwm2$awviews add ( constraint cwm2$awviews_tblunq unique ( table_id ) ) ;
alter table cwm2$awviews add ( constraint cwm2$awviews_tblunq2 unique ( view_owner, view_name ) ) ;

create index cwm2$_cust_meas_view_name on cwm2$_aw_next_perm_cust_meas ( aw_access_view_name ) ;
create index cwm2$_view_and_cust_meas_col on cwm2$_aw_perm_cust_meas_map ( aw_access_view_name, cust_adt_column ) ;
grant select, insert, update, delete on cwm2$_aw_next_temp_cust_meas to public;
grant select, insert, update, delete on cwm2$_aw_temp_cust_meas_map to public;
grant select, insert, update, delete on cwm2$_temp_values to public;

alter table cwm2$awcubeloadmeasure add (
    description varchar2(4000)
); 

alter table cwm2$awviews add (
    aw_name varchar2(30),
    aw_owner varchar2(30)
); 

alter table cwm2$factkeydimhiermap add (
    aggoperator_irid number,
    aggorder number,
    aggweightcol_id number,
    aggweighttbl_id number
); 

alter table cwm2$mrall_awviews add (
    aw_name varchar2(30),
    aw_owner varchar2(30)
); 

truncate table cwm2$mrall_dim_level_attr_maps; 

alter table cwm2$mrall_dim_level_attr_maps modify (
    dtype varchar2(106)
); 
alter table cwm2$mrall_dim_level_attr_maps add (
    data_length number,
    data_precision number,
    olap_api_data_type number
); 

truncate table cwm2$mrall_facttblfctmaps;

alter table cwm2$mrall_facttblfctmaps modify (
    data_type varchar2(106)
); 
alter table cwm2$mrall_facttblfctmaps add (
    data_length number,
    data_precision number,
    olap_api_data_type number
); 

alter table cwm2$mrall_facttblkeymaps add (
    column_data_length number,
    column_data_precision number,
    column_data_type varchar2(106),
    gid_column_data_length number,
    gid_column_data_precision number,
    gid_column_data_type varchar2(106)
); 

alter table cwm2$mrall_facttblkeymaps add (
    table_id number,
    column_id number,
    gidcolumn_id number
); 

alter table cwm2$mrall_hier_custom_sort add (
    data_length number,
    data_precision number,
    data_type varchar2(106)
); 

alter table cwm2$mrall_hierdim_keycol_map add (
    data_length number,
    data_precision number,
    data_type varchar2(106)
); 

alter table cwm2$mrall_listdims add (
    data_length number,
    data_precision number,
    data_type varchar2(106)
); 

alter table cwm2$mrall_listdims add (
    level_name varchar2(30)
); 

truncate table cwm2$mrfacttblfctmaps;

alter table cwm2$mrfacttblfctmaps modify (
    data_type varchar2(106)
); 
alter table cwm2$mrfacttblfctmaps add (
    data_length number,
    data_precision number,
    olap_api_data_type number
); 

create table cwm2$awviewcols ( 
    awobject varchar2(2000),
    awviews_irid number not null,
    column_id number,
    createddate date,
    irid number not null,
    lastchangeuser varchar2(30),
    updateddate date ) ;

create table cwm2$mrall_awviewcols ( 
    awobject varchar2(2000),
    column_name varchar2(30),
    view_name varchar2(30),
    view_owner varchar2(30) ) ;

create table cwm2$mrall_cwm1_aggop ( 
    column_name varchar2(30),
    cube_name varchar2(30),
    dimension_name varchar2(30),
    dimension_owner varchar2(30),
    func_name varchar2(240),
    id number,
    measure_name varchar2(30),
    owner varchar2(30),
    table_name varchar2(30),
    table_owner varchar2(30),
    version_id char(4) ) ;

create table cwm2$mrall_cwm1_aggord ( 
    cube_name varchar2(30),
    dimension_name varchar2(30),
    dimension_owner varchar2(30),
    id number,
    owner varchar2(30),
    position number,
    version_id char(4) ) ;

create table cwm2$mrall_olap2_agg_uses ( 
    aggregation_name varchar2(240),
    aggregation_order number,
    column_name varchar2(30),
    cube_name varchar2(30),
    dim_hier_combo_id number,
    dimension_name varchar2(30),
    dimension_owner varchar2(30),
    hierarchy_name varchar2(30),
    id number,
    owner varchar2(30),
    table_name varchar2(30),
    table_owner varchar2(30),
    version_id char(4) ) ;

@@cwm2mrat.sql

create sequence olapsys.cwm2_olap_enableseq
    increment by 1
    start with 1
    minvalue 1
    maxvalue 9999
    cycle
    nocache
    order;

alter table cwm2$dimension add constraint cwm2$dimension_name unique ( owner, name ) ;
alter table cwm2$dimensionattribute add constraint cwm2$dimensionattribute_name unique ( dimension_irid, name ) ;
alter table cwm2$hierarchy add constraint cwm2$hierarchy_name unique ( dimension_irid, name ) ;
alter table cwm2$level add constraint cwm2$level_name unique ( dimension_irid, name ) ;
alter table cwm2$levelattribute add constraint cwm2$levelattribute_name unique ( level_irid, name ) ;
alter table cwm2$cube add constraint cwm2$cube_name unique ( owner, name ) ;
alter table cwm2$measure add constraint cwm2$measure_name unique ( cube_irid, name ) ;
alter table cwm2$awdimload drop constraint cwm2$awdimload_name ;
alter table cwm2$awdimload add constraint cwm2$awdimload_name unique ( dim_irid, name ) ;
alter table cwm2$awcubeload drop constraint cwm2$awcubeload_name ;
alter table cwm2$awcubeload add constraint cwm2$awcubeload_name unique ( cube_irid, name ) ;
alter table cwm2$awcubeloadmeasure add constraint cwm2$awcubeloadmeasure_name unique ( cubeload_irid, name ) ;
alter table cwm2$awcubeloadaggplan add constraint cwm2$awcubeloadaggplan_name unique ( cubeload_irid, cubeagg_irid ) ;
alter table cwm2$awcubeagg drop constraint cwm2$awcubeagg_name ;
alter table cwm2$awcubeagg add constraint cwm2$awcubeagg_name unique ( cube_irid, name ) ;
alter table cwm2$awcubecompplan add constraint cwm2$awcubecompplan_name unique ( cubeload_irid, cubecompspec_irid ) ;

begin
  execute immediate 'alter table cwm2$awviews add constraint cwm2$awviewpk primary key (irid)';
  exception when others then null;
end;
/

alter table cwm2$awcubeloadparmvalue modify (
    value varchar2(4000)
); 
alter table cwm2$awdimloadparmvalue modify (
    value varchar2(4000)
); 

alter table cwm2$awcompspecmembership drop constraint cwm2$awcompspecmembership_id ;
alter table cwm2$awcompspecmembership add ( constraint cwm2$awcompspecmembership_id unique ( compspec_irid ,membertype ,name ) ) ;

grant select on sys.view$ to olapsys;
grant select on sys.syn$ to olapsys;
grant create any synonym to olapsys;
grant drop any synonym to olapsys;

alter table cwm2$mrall_cube_measures add (
    cube_description varchar2(4000)
);

alter table cwm2$mrall_facttblkeymaps add (
    level_name varchar2(30)
); 

alter table cwm2$mrall_hierdims modify (
    display_name varchar2(240),
    plural_name varchar2(240)
); 

alter table cwm2$awcubeload drop unused columns;
alter table cwm2$awcubeloadfilter drop unused columns;
alter table cwm2$awdimload drop unused columns;
alter table cwm2$awdimloadfilter drop unused columns;

create table cwm2$classificationvaluepair ( 
    irid number not null,
    classentry_irid number not null,
    parametername varchar2(80),
    parametervalue varchar2(4000),
    parametervalue2 varchar2(4000),
    parametervalue3 varchar2(4000),
    parametervalue4 varchar2(4000),
    position number,
    createddate date,
    updateddate date,
    lastchangeuser varchar2(30) ) ;

create table cwm2$mrall_entity_ext_parms ( 
    descriptor_id number(38),
    descriptor_name varchar2(240),
    entity_owner varchar2(240),
    entity_name varchar2(240),
    child_entity_name varchar2(30),
    secondary_child_entity_name varchar2(30),
    parameter_name varchar2(80),
    parameter_value varchar2(4000),
    parameter_value2 varchar2(4000),
    parameter_value3 varchar2(4000),
    parameter_value4 varchar2(4000),
    position number,
    version_id char(4),
    id number ) ;

create table cwm2$mrall_entity_parameters ( 
    descriptor_id number(38),
    descriptor_name varchar2(240),
    entity_owner varchar2(240),
    entity_name varchar2(240),
    child_entity_name varchar2(240),
    secondary_child_entity_name varchar2(30),
    parameter_name varchar2(80),
    parameter_value varchar2(4000),
    version_id char(4),
    id number ) ;

create table xml_load_log (
    xml_loadid number not null,
    xml_recordid number not null,
    xml_date date not null,
    xml_aw varchar2(80),
    xml_message varchar2(2000),
    primary key (xml_loadid, xml_recordid)) ;

create table xml_load_records ( 
    xml_loadid number not null,
    xml_recordid number not null,
    xml_recordtext varchar2(2000),
    primary key ( xml_loadid, xml_recordid)) ;

create sequence xml_loadid_sequence;

alter table cwm2$classificationvaluepair add constraint cwm2$classvaluepair_fk foreign key ( classentry_irid ) references cwm$classificationentry ( irid ) on delete cascade ;
alter table cwm2$hiercustomsort add constraint cwm2$hiercustomsort_name unique ( tablename_id, columnname_id );
alter table cwm2$factdimhiertplsdtl add constraint cwm2$factdimh_tplsdtl_hier_fk foreign key (hier_irid) references olapsys.cwm2$hierarchy (irid) on delete cascade;
alter table cwm2$factkeydimhierlvlmap add constraint cwm2$factkeyd_h_l_map_lvl_fk foreign key (level_irid) references olapsys.cwm2$level (irid) on delete cascade;
alter table cwm2$factkeydimhierlvlmap add constraint cwm2$factkeyd_h_l_map_hier_fk foreign key (hierarchy_irid) references olapsys.cwm2$hierarchy (irid) on delete cascade;
alter table olapsys.cwm2$factkeydimhiermap add constraint cwm2$factkeydimhiermap_dim_fk foreign key (dimension_irid) references olapsys.cwm2$dimension (irid) on delete cascade;
alter table cwm2$awviewcols drop constraint cwm2$awviewcolsfk;

Rem call apsroles.sql to create olap_user role - need to do this before the grant below - bug 6683296
@@apsroles.sql

grant select on sys.aw_prop$ to olapsys with grant option;
grant select on sys.dba_tables to olapsys;
grant select on sys.dba_constraints to olapsys;
grant select on sys.dba_objects to olapsys;
grant create any dimension to olapsys;
grant drop any dimension to olapsys;
grant select on sys.all_aws to olapsys with grant option;
grant select on sys.all_aw_prop_name to olapsys with grant option;
grant select on sys.view$ to olapsys;
grant select on sys.syn$ to olapsys;
grant create any synonym to olapsys;
grant drop any synonym to olapsys;
grant select,insert,update,delete on system.mview$_adv_index to olapsys;
grant select,insert,update,delete on system.mview$_adv_partition to olapsys;
grant select,insert,update,delete on xml_load_log to olap_user, olap_dba;
grant select,insert,update,delete on xml_load_records to olap_user, olap_dba;

drop package cwm2_olap_aw_access;
drop package cwm2_olap_aw_create;
drop package cwm2_olap_aw_map;
drop package cwm2_olap_aw_object;
drop package cwm2_olap_aw_utility;
drop package cwm2_olap_enable_utility;
drop library cwm2_olap_message_library;
drop type t_mesg_table;
drop type t_mesg_row;
drop view all$aw_load_cube_agg;
drop view all$olap2_aw_map_attr_use;
drop view all$olap2_aw_map_dim_use;
drop view all$olap2_aw_map_meas_use;
drop view odm_olap2_dim_hier_level_uses;

drop public synonym all_olap2_aw_load_cube_agg;
drop public synonym all_olap2_aw_load_cube_filt;
drop public synonym all_olap2_aw_load_cube_meas;
drop public synonym all_olap2_aw_load_dim;
drop public synonym all_olap2_aw_load_dim_filt;
drop public synonym all_olap2_aw_map_attr_use;
drop public synonym all_olap2_aw_map_dim_use;
drop public synonym all_olap2_aw_map_hier_use;
drop public synonym all_olap2_aw_map_level_use;
drop public synonym all_olap2_aw_map_meas_use;
drop public synonym all_olap2_aw_phys_obj_ext;
drop public synonym all_olap2_aw_phys_obj_rel_obj;
drop public synonym all_olap_os_child_instances;
drop public synonym all_olap_os_osa_privileges;
drop public synonym cwm2_olap_aw_access;
drop public synonym cwm2_olap_aw_create;
drop public synonym cwm2_olap_aw_map;
drop public synonym cwm2_olap_aw_object;
drop public synonym cwm2_olap_aw_utility;
drop public synonym cwm_olap_server_list;
drop public synonym dba_olap_os_child_instances;
drop public synonym dba_olap_os_osa_privileges;

alter table MRAC_OLAP2_AW_PHYS_OBJ_PROP_T modify (
   AW_PROP_VALUE varchar2(4000)
);

alter table cwm2$mrall_listdims add (
  level_name varchar2(30)
);

-- One more for BUG 3996129
create index cwm2$factdimhiertplsdtl_ih on olapsys.cwm2$FactDimHierTplsDtl (HIER_IRID);



alter table olapsys.CwM2$Dimension modify (
    displayname varchar2(90),
    pluralname varchar2(90)
); 

alter table olapsys.CwM2$Level modify (
    displayname varchar2(90),
    pluralname varchar2(90)
); 

alter table olapsys.CwM2$Hierarchy modify (
    displayname varchar2(90)
); 

alter table olapsys.CwM2$DimensionAttribute modify (
    displayname varchar2(90)
); 

alter table olapsys.CwM2$LevelAttribute modify (
    displayname varchar2(90)
); 

alter table olapsys.CwM2$Cube modify (
    displayname varchar2(90)
); 

alter table olapsys.CwM2$Measure modify (
    displayname varchar2(90)
); 


@@cwm2awtm.sql
@@cwm2sed2.sql

drop public synonym dbms_aw_utilities;
drop package dbms_aw_utilities;
drop public synonym cwm2_olap_installer;
drop package cwm2_olap_installer;

Rem @@amdrelod.sql  

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

alter session set current_schema = sys;

@@cwm2iner.pls
@@cwm2iner.plb
execute sys.cwm2_olap_installer.Validate_CWM2_Install;

@@amdu101.sql