Rem
Rem
Rem onemratt.sql
Rem
Rem  Copyright (c) Oracle Corporation 2001. All Rights Reserved.
Rem
Rem    NAME
Rem      onemratt.sql - all$olapmr_attributes
Rem
Rem    DESCRIPTION
Rem      Attributes.
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_attributes AS SELECT 
  u.username                 dim_owner,
  d.name                 dim_name,
  dat.PHYSICALNAME       dim_attribute_name,
  l.levelname            levelname,
  decode(c.type#, 2,
  decode(c.scale,NULL, 
  decode(c.precision#, NULL, 'DOUBLE', 'FLOAT'),
         'DOUBLE'),
  8, 'DOUBLE',
  12, 'DATE', 
  'STRING')              dtype,
  cu.username                col_owner,
  ct.name                col_table,
  c.name                 col_name
FROM
  dba_users              u,
  sys.obj$               d,
  sys.dimlevel$          l,
  sys.dimattr$           a,
  sys.col$               c,
  sys.obj$               ct,
  dba_users              cu,
  cwm$levelattribute     lat,
  cwm$itemuse            iu1,
  cwm$itemuse            iu2,
  cwm$dimensionattribute dat
WHERE u.user_id = d.owner# AND
      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
      d.obj# = l.dimobj# AND
      l.dimobj# = a.dimobj# AND
      l.levelid# = a.levelid# AND
      a.detailobj# = c.obj# AND
      a.col# = c.col# AND
      c.obj# = ct.obj# AND
      ct.owner# = cu.user_id AND
      c.obj# = lat.type_irid AND
      c.name = lat.physicalname AND
      lat.irid = iu2.mappable_irid AND
      iu2.operation_irid_1 = iu1.operation_irid AND
      iu1.mappable_irid = dat.irid
ORDER BY
      u.username, d.name, dat.physicalname
/