REM
REM Copyright (c) 2002, 2004, Oracle Corporation.  All rights reserved.  
REM
REM    NAME
REM      cwm2awao.sql
REM
REM    DESCRIPTION
REM      New for patch release 9.2.0.2.0
REM      These are the official objects that will allow the
REM      PL/SQL programs to fetch information out of the
REM      SYS.AWCREATE analytical workspace.
REM
REM    NOTES
REM
REM    MODIFIED   (MM/DD/YY)
REM       cdalessi 02/06/04 - 
REM       cdalessi 01/27/04 - 
REM       cdalessi 05/29/03 - Change create to create or replace type
REM       cdalessi 01/07/03 - 
REM       mstasiew 12/03/02 - mstasiew_txn104598
REM     cdalessi   12/30/02 - adjust table/type creation for replace
REM     dbardwel   09/17/02 - 
REM     dbardwel   08/19/02 - dbardwel_txn103971
REM     dbardwel   08/14/02 - dbardwel_txn103971 
REM
REM  Note: Need to add to SYS.AWCREATE a text dimension with
REM        name OLAP_SYS_RETURN_INFO
REM


DROP VIEW OLAP_SYS_AW_ACCESS_DIM_VIEW
/

DROP VIEW OLAP_SYS_AW_ACCESS_CUBE_VIEW
/

DROP TYPE OLAP_SYS_AW_ACCESS_TBL
/

DROP TYPE OLAP_SYS_AW_ACCESS_OBJ
/

CREATE OR REPLACE TYPE OLAP_SYS_AW_ACCESS_OBJ AS OBJECT (
  AW_DIM_ACCESS_INFO   VARCHAR2(4000),
  AW_CUBE_ACCESS_INFO  VARCHAR2(4000))
/


CREATE OR REPLACE TYPE OLAP_SYS_AW_ACCESS_TBL AS TABLE OF OLAP_SYS_AW_ACCESS_OBJ
/


CREATE OR REPLACE VIEW OLAP_SYS_AW_ACCESS_DIM_VIEW AS
  SELECT AW_DIM_ACCESS_INFO FROM
    TABLE(CAST(OLAP_TABLE('SYS.AWCREATE duration session',
                          'OLAPSYS.OLAP_SYS_AW_ACCESS_TBL',
                          '',
                          'DIMENSION AW_DIM_ACCESS_INFO FROM OLAP_SYS_RETURN_INFO')
                      AS OLAP_SYS_AW_ACCESS_TBL))
/


CREATE OR REPLACE VIEW OLAP_SYS_AW_ACCESS_CUBE_VIEW AS
  SELECT AW_CUBE_ACCESS_INFO FROM
    TABLE(CAST(OLAP_TABLE('SYS.AWCREATE duration session',
                          'OLAPSYS.OLAP_SYS_AW_ACCESS_TBL',
                          '',
                          'DIMENSION AW_CUBE_ACCESS_INFO FROM OLAP_SYS_RETURN_INFO')
                      AS OLAP_SYS_AW_ACCESS_TBL))
/



REM
REM This second set is for larger access of CWM2 OLAP API calls that
REM are generated at the Express SPL layer that need to be accessed by
REM the calling PL/SQL layer. This is needed for OLAP enablement for
REM accessing AW that have been created.
REM

DROP VIEW OLAP_SYS_AW_ENABLE_ACCESS_VIEW
/

DROP TYPE OLAP_SYS_AW_ENABLE_ACCESS_TBL
/

DROP TYPE OLAP_SYS_AW_ENABLE_ACCESS_OBJ
/

CREATE OR REPLACE TYPE OLAP_SYS_AW_ENABLE_ACCESS_OBJ AS OBJECT (
  AW_ACCESS_INFO VARCHAR2(4000))
/

CREATE OR REPLACE TYPE OLAP_SYS_AW_ENABLE_ACCESS_TBL AS TABLE OF OLAP_SYS_AW_ENABLE_ACCESS_OBJ
/

CREATE OR REPLACE VIEW OLAP_SYS_AW_ENABLE_ACCESS_VIEW AS
  SELECT AW_ACCESS_INFO FROM
    TABLE(CAST(OLAP_TABLE('SYS.AWCREATE duration session',
                          'OLAPSYS.OLAP_SYS_AW_ENABLE_ACCESS_TBL',
                          '',
                          'DIMENSION AW_ACCESS_INFO FROM OLAP_SYS_RETURN_INFO')
                      AS OLAP_SYS_AW_ENABLE_ACCESS_TBL))
/



REM Grants below may not be needed as this is done within the
REM context of a PL/SQL program with OLAPSYS's privileges.

GRANT SELECT ON OLAP_SYS_AW_ACCESS_DIM_VIEW TO PUBLIC
/

GRANT SELECT ON OLAP_SYS_AW_ACCESS_CUBE_VIEW TO PUBLIC
/

GRANT SELECT ON OLAP_SYS_AW_ENABLE_ACCESS_VIEW TO PUBLIC
/

CREATE OR REPLACE PUBLIC SYNONYM OLAP_SYS_AW_ACCESS_CUBE_VIEW for olapsys.OLAP_SYS_AW_ACCESS_CUBE_VIEW
/

CREATE OR REPLACE PUBLIC SYNONYM OLAP_SYS_AW_ACCESS_DIM_VIEW for olapsys.OLAP_SYS_AW_ACCESS_DIM_VIEW
/

CREATE OR REPLACE PUBLIC SYNONYM OLAP_SYS_AW_ENABLE_ACCESS_VIEW for olapsys.OLAP_SYS_AW_ENABLE_ACCESS_VIEW
/