Rem
Rem $Header: cwmlite/admin/onevaflu.sql /main/4 2008/07/28 13:25:50 glyon Exp $
Rem
Rem onevaflu.sql
Rem
Rem  Copyright (c) Oracle Corporation 2000, 2001. All Rights Reserved.
Rem
Rem    NAME
Rem      onevaflu.sql - all$olap_fact_level_uses
Rem
Rem    DESCRIPTION
Rem      Mapping of each cube dimension use to the foreign key
Rem      relationship between fact and dimension tables. Also identifies
Rem      the Level considered to be the lowest level of aggregation.
Rem
Rem    NOTES
Rem      
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glyon       06/24/08 - bug 7204558: eliminate references to sys.user$
Rem    dthompso    05/22/01 - B1771957: Should not project rows if cube owner d
Rem    dthompso    02/27/01 - change security implementation
Rem    dthompso    04/26/00 - Initial Version
Rem    dthompso    01/00/00 - Created
Rem

CREATE OR REPLACE VIEW all$olap_fact_level_uses AS SELECT 
  sch.physicalname owner
, cub.physicalname cube_name
, cdu.dimension_owner
, cdu.dimension_name
, cdu.name dimension_alias
, flu.level_name
, fu.object_owner fact_table_owner
, fu.object_name fact_table_name
, flu.foreign_key_name
FROM
  dba_users u
, cwm$model sch
, cwm$cube cub
, cwm$cubedimensionuse cdu
, cwm$factleveluse flu
, cwm$facttablemap fm
, cwm$factuse fu
WHERE u.username = sch.physicalname
AND sch.irid = cub.datamodel_irid
AND (    cwm$util.fact_table_visible(cub.irid) = 'Y'
     OR EXISTS /* SELECT ANY TABLE */
       (SELECT null FROM v$enabledprivs 
        WHERE priv_number = -47))
AND cub.irid = cdu.cube_irid
AND cdu.irid = flu.cubedimensionuse_irid
AND fm.mapcontainer_irid = cub.irid
AND fu.operation_irid_1 = fm.irid
WITH READ ONLY
/