Rem
Rem $Header: cwmlite/admin/onevddau.sql /main/6 2008/07/28 13:25:50 glyon Exp $
Rem
Rem onevddau.sql
Rem
Rem  Copyright (c) Oracle Corporation 2000, 2001. All Rights Reserved.
Rem
Rem    NAME
Rem      onevddau.sql - dba$olap_dim_attr_uses
Rem
Rem    DESCRIPTION
Rem      Uses of level attributes within a dimension attribute.
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/28/01 - B1665105 : should not select garbage level attrib
Rem    dthompso    01/22/01 - B1503215 : fix duplicates
Rem    dthompso    05/30/00 - B1314666 : Correct duplicate entries
Rem    dthompso    05/03/00 - Add level name
Rem    dthompso    04/26/00 - Initial Version
Rem    dthompso    01/00/00 - Created
Rem

CREATE OR REPLACE VIEW dba$olap_dim_attr_uses AS SELECT
/*+ ORDERED */
  u.username owner
, d.name dimension_name
, dat.physicalname dim_attribute_name
, lvl.physicalname level_name
, lat.name lvl_attribute_name
FROM 
  dba_users u       
, sys.obj$ d 
, cwm$dimensionattribute dat
, cwm$itemuse use1
, cwm$itemuse use2
, cwm$levelattribute lat
, cwm$level lvl 
, sys.dimlevel$ l
, sys.dimattr$ a
, sys.col$ c
WHERE u.user_id = d.owner#
AND d.type# = 43 /* DIMENSION */
AND d.obj# = dat.itemcontainer_irid
AND dat.irid = use1.mappable_irid
AND use1.operation_irid = use2.operation_irid_1
AND use2.mappable_irid = lat.irid
AND lat.itemcontainer_irid = lvl.irid
AND lvl.dimension_irid = l.dimobj#
AND lvl.physicalname = l.levelname    
AND l.dimobj# = a.dimobj#
AND l.levelid# = a.levelid# 
AND a.detailobj# = c.obj#
AND a.col# = c.col#  
AND lat.type_irid = c.obj#
AND lat.physicalname = c.name
WITH READ ONLY
/