Rem
Rem onemrlev.sql
Rem
Rem  Copyright (c) Oracle Corporation 2001. All Rights Reserved.
Rem
Rem    NAME
Rem      onemrlev.sql - all$olapmr_dim_levels
Rem
Rem    DESCRIPTION
Rem      Metadata Reader Dimension Levels.
Rem
Rem    NOTES
Rem      
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glyon       06/24/08 - bug 7204558: eliminate references to sys.user$
Rem    mstasiew    10/31/01 - Add permission check for bug 1956854  
Rem                         - mstasiew_txn100542
Rem    cdalessi    07/06/01 - cdalessi_t50006
Rem    ddedonat    05/29/01 - Created
Rem

CREATE OR REPLACE VIEW all$olapmr_dim_levels AS SELECT /*+ORDERED*/
  u.username                         owner,
  d.name                             dimension_name,
  h.hiername                         hierarchy_name,
  dl.levelname                       level_name,
  NVL(lev.displayname,dl.levelname)  display_name,
  NVL(lev.description,dl.levelname)  description,
  hl.pos#                            pos
FROM 
  sys.obj$          d, 
  dba_users         u, 
  sys.hier$         h, 
  sys.hierlevel$    hl, 
  sys.dimlevel$     dl,
  sys.dimlevelkey$  k, 
  sys.obj$          t, 
  dba_users         tu, 
  cwm$level         lev
WHERE 
  d.type#      = 43 AND              /* DIMENSION */
 (   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
  u.user_id      = d.owner# AND
  d.obj#       = h.dimobj# AND
  d.obj#       = hl.dimobj# AND
  h.hierid#    = hl.hierid# AND
  hl.dimobj#   = dl.dimobj# AND
  hl.levelid#  = dl.levelid# AND
  dl.dimobj#   = k.dimobj# AND
  dl.levelid#  = k.levelid# AND
  k.keypos#    = 1 AND		/* Restrict composite keys to 1 rec */
  k.detailobj# = t.obj# AND
  t.owner#     = tu.user_id AND
  dl.dimobj#   = lev.dimension_irid (+) AND
  dl.levelname = lev.physicalname (+)    
ORDER BY
  owner ASC, 
  dimension_name ASC, 
  hierarchy_name ASC, 
  pos DESC
/