Rem
Rem $Header: cwmlite/admin/onevalam.sql /main/4 2008/07/28 13:25:50 glyon Exp $
Rem
Rem onevalam.sql
Rem
Rem  Copyright (c) Oracle Corporation 2000, 2001. All Rights Reserved.
Rem
Rem    NAME
Rem      onevalam.sql - all$olap_dim_level_attr_maps
Rem
Rem    DESCRIPTION
Rem      Mapping of dimension level attributes to dimension table columns.
Rem
Rem    NOTES
Rem      
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glyon       06/24/08 - bug 7204558: eliminate references to sys.user$
Rem    dthompso    02/27/01 - change security implementation
Rem    dthompso    01/18/01 - B1503215 : fix duplicates
Rem    dthompso    04/26/00 - Initial Version
Rem    dthompso    01/00/00 - Created
Rem

CREATE OR REPLACE VIEW all$olap_dim_level_attr_maps AS SELECT 
  a.u_name owner
, a.d_name dimension_name
, a.l_levelname level_name
, nvl(la.lat_name, a.c_name) attribute_name
, a.c_name column_name
, 1 position
FROM 
 (SELECT 
    u.username u_name
  , d.name d_name
  , l.dimobj# l_dimobj#  
  , l.levelname l_levelname
  , c.obj# c_obj#
  , c.name c_name
 FROM
    dba_users u
  , sys.obj$ d
  , sys.dimlevel$ l
  , sys.dimattr$ a
  , sys.col$ c
  WHERE u.user_id = d.owner#
  AND d.type# = 43 /* DIMENSION */
  AND (   cwm$util.dimension_tables_visible(d.obj#) = 'Y'
        OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */
         (SELECT null FROM v$enabledprivs 
          WHERE priv_number IN (-47,-215,-216,-217)))
  AND d.obj# = l.dimobj#
  AND l.dimobj# = a.dimobj#
  AND l.levelid# = a.levelid# 
  AND a.detailobj# = c.obj#
  AND a.col# = c.col#) a
, (SELECT
    lat.name lat_name
    , lat.displayname lat_displayname
    , lat.description lat_description
    , lat.type_irid lat_type_irid
    , lat.physicalname lat_physicalname
    , lev.dimension_irid lev_dimension_irid
    , lev.physicalname lev_physicalname
  FROM cwm$level lev, cwm$levelattribute lat
  WHERE lev.irid = lat.itemcontainer_irid) la
WHERE a.l_dimobj# = la.lev_dimension_irid (+)
AND a.l_levelname = la.lev_physicalname (+)
AND a.c_obj# = la.lat_type_irid (+)
AND a.c_name = la.lat_physicalname (+)   
WITH READ ONLY
/