REM
REM Copyright (c) 2002, 2003, Oracle Corporation.  All rights reserved.  
REM
REM   Name
REM     cwm2awcr.sql
REM
REM   Description
REM     Creates new cwm2 metadata tables in support of the
REM     analytical workspace create facility.
REM
REM
REM   Notes
REM     Tables are new for Oracle 10i Release 1 and Oracle 9.2.0.2 release
REM
REM   MODIFIED  (MM/DD/YY)
REM    dbardwel   07/31/03 - Make Dimension and Cube Parm Value 4000 characters
REM    dbardwel   06/23/03 - 9.2.0.4.0 update to main
REM    cdalessi   04/28/03 - Separate triggers out to cwm2trg.sql
REM    cdalessi   02/07/03 - change to create or replace trigger
REM    mstasiew   12/23/02 -
REM    mstasiew   12/20/02 -
REM    awesley    12/17/02 -
REM    mstasiew   12/06/02 -
REM    awesley    11/26/02 -
REM    awesley    11/08/02 - change aggreagtion to agg, add Version_ID to CWM2$AWCubeAgg,
REM                          add unique keys, change measures to measure,
REM                          change LoadProcessType to LoadProceddCode, change lkup to lookup
REM                          change tbl to table
REM    dbardwel   10/15/02 -
REM    dbardwel   10/10/02 - Major updates for next OTN release and 10i
REM    dbardwel   09/11/02 - dbardwel_txn104130 adding global temporary tables for
REM                          AWM integration for SQL and OLAP enablement
REM    dbardwe    07/22/02 - dbardwel_txn103421
REM    dbardwe    07/18/02  Added some new columns for dimension/cube object prefix, etc.
REM    dbardwel   07/17/02  Added DimLoadFilter and CubeLoadFilter tables
REM    dbardwel   07/09/02  Created for release 10i
REM

REM
REM  First set of tables for AW Create for dimension
REM


create global temporary table OLAPSYS.CWM2$AWDimCreateAccess (
  RUNID#                  number,
  DIM_OWNER               varchar2(30),
  DIM_NAME                varchar2(30),
  ProcessAction           varchar2(30),
  Query_Text              CLOB)
  on commit preserve rows;


create global temporary table OLAPSYS.CWM2$AWCubeCreateAccess (
  RUNID#                  number,
  CUBE_OWNER              varchar2(30),
  CUBE_NAME               varchar2(30),
  ProcessAction           varchar2(30),
  Query_Text              CLOB)
  on commit preserve rows;


create table OLAPSYS.CWM2$AWDimLoad (
  IRID                    number not null,
  Dim_IRID                number not null,
  Version_ID              varchar2(10),
  Name                    varchar2(60),
  LoadType_IRID           number,
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWDimLoadFilter (
  IRID                    number not null,
  DimLoad_IRID            number not null,
  Lookup_Table_Owner      varchar2(30),
  Lookup_Table_Name       varchar2(30),
  FilterCondition         varchar2(4000),
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWDimLoadParm (
  IRID                    number not null,
  Name                    varchar2(16),
  DataType                varchar2(30),
  Default_Value           varchar2(30)
  );


create table OLAPSYS.CWM2$AWDimLoadParmValue (
  IRID                    number not null,
  DimLoad_IRID            number not null,
  ParmName_IRID           number not null,
  Value                   varchar2(4000),
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWDimLoadType (
  IRID                    number not null,
  Name                    varchar2(60),
  Description             varchar2(255)
  );


REM AW Create Cube Tracking tables

create table OLAPSYS.CWM2$AWCubeLoad (
  IRID                    number not null,
  Cube_IRID               number not null,
  Version_ID              varchar2(10),
  Name                    varchar2(60),
  LoadType_IRID    number,
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWCubeLoadFilter (
  IRID                    number not null,
  CubeLoad_IRID           number not null,
  Fact_Table_Owner        varchar2(30),
  Fact_Table_Name         varchar2(30),
  FilterCondition         varchar2(4000),
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWCubeLoadParm (
  IRID                    number not null,
  Name                    varchar2(16),
  DataType                varchar2(30),
  Default_Value           varchar2(30)
  );


create table OLAPSYS.CWM2$AWCubeLoadParmValue (
  IRID                    number not null,
  CubeLoad_IRID           number not null,
  ParmName_IRID           number not null,
  Value                   varchar2(4000),
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWCubeLoadType (
  IRID                    number not null,
  Name                    varchar2(60),
  Description             varchar2(255)
  );


create table OLAPSYS.CWM2$AWCubeLoadMeasure (
  IRID                    number not null,
  CubeLoad_IRID           number not null,
  Measure_IRID            number not null,
  Name                    varchar2(60),
  DisplayName             varchar2(60),
  Description             varchar2(4000),
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWCubeLoadAggPlan (
  IRID                    number not null,
  CubeLoad_IRID           number not null,
  CubeAgg_IRID            number not null,
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWCubeAgg (
  IRID                    number not null,
  Cube_IRID               number not null,
  Version_ID              varchar2(10),
  Name                    varchar2(60),
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWCubeAggLevel (
  IRID                    number not null,
  CubeAgg_IRID            number not null,
  Dim_IRID                number not null,
  Level_IRID              number not null,
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWCubeAggMeasure (
  IRID                    number not null,
  CubeAgg_IRID            number not null,
  Measure_IRID            number not null,
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWCompositeSpec (
  IRID                    number not null,
  Cube_IRID               number not null,
  Version_Id              varchar2(10),
  Name                    varchar2(30),
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWCompSpecMembership (
  IRID                    number not null,
  CompSpec_IRID           number not null,
  MemberType              varchar2(10),
  Name                    varchar2(30),
  SegWidth                number,
  CompSpec_Position       number,
  Dim_IRID                number,
  Composite_IRID          number,
  Composite_Position      number,
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );


create table OLAPSYS.CWM2$AWCubeCompPlan (
  IRID                    number not null,
  CubeLoad_IRID           number not null,
  CubeCompSpec_IRID       number not null,
  CreatedDate             date,
  UpdatedDate             date,
  LastChangeUser          varchar2(30)
  );