/****************** PL/Generator from Quest Software ******************
||
||    File: plgdoir.vus
||  Author: Steven Feuerstein
||
|| Creates views for PL/Generator
||
|| This is a part of the PL/Generator Code library.
|| Copyright (C) 1998-1999 Quest Software, Inc.
|| All rights reserved.
||
|| For more information, call Quest Software at 1-800-REVEAL4
|| or check out our Web page: www.Quest Software.com
||
|| 10/1998 - Add oir_driver_source to support client side IO.
||
||    AND (P.owner != W.owner OR (P.owner = W.owner AND P.objid > 0))
||
******************************************************************/

DROP VIEW oir_global_wwrap
/
CREATE VIEW oir_global_wrap
AS
   SELECT W.driver,
          W.attrname orig,
          W.doc descrip,
          P.info prefix,
          S.info suffix      
     FROM plg_doir W,
          plg_doir P,
          plg_doir S
    WHERE W.owner = '*ALL'
      AND W.objname = '*ALL'
      AND W.objtype = 'ALIAS' 
      AND W.attrtype = 'WRAP ALIAS'
      AND W.infotype = '*ALL'
      AND W.infoseq = 1
      AND P.owner = W.owner
      AND P.objname = W.objname
      AND P.objtype = 'ALIAS' 
      AND P.attrtype = 'PREF ALIAS'
      AND P.infotype = W.infotype
      AND P.infoseq = W.infoseq
      AND P.attrname = W.attrname || 'PRE'
      AND S.owner =  W.owner
      AND S.objname = W.objname
      AND S.objtype = 'ALIAS' 
      AND S.attrtype = 'SUFF ALIAS'
      AND S.infotype = W.infotype
      AND S.infoseq = W.infoseq
      AND S.attrname = W.attrname || 'SUF'
/
GRANT SELECT ON oir_global_wrap TO PUBLIC;

DROP VIEW oir_schema_wrap
/
CREATE VIEW oir_schema_wrap
AS
   SELECT P.driver,
          P.owner sch,
          W.attrname orig,
          W.doc descrip,
          P.info prefix,
          S.info suffix      
     FROM plg_doir W,
          plg_doir P,
          plg_doir S
    WHERE W.owner = '*ALL'
      AND W.objname = '*ALL'
      AND W.objtype = 'ALIAS' 
      AND W.attrtype = 'WRAP ALIAS'
      AND W.infotype = '*ALL'
      AND W.infoseq = 1
      AND P.owner != W.owner
      AND P.objname = W.objname
      AND P.objtype = 'ALIAS' 
      AND P.attrtype = 'PREF ALIAS'
      AND P.infotype = W.infotype
      AND P.infoseq = W.infoseq
      AND P.attrname = W.attrname || 'PRE'
      AND S.owner != W.owner
      AND S.objname = W.objname
      AND S.objtype = 'ALIAS' 
      AND S.attrtype = 'SUFF ALIAS'
      AND S.infotype = W.infotype
      AND S.infoseq = W.infoseq
      AND S.attrname = W.attrname || 'SUF'
      AND P.owner = S.owner
/
GRANT SELECT ON oir_schema_wrap TO PUBLIC;

REM Additional index is needed!

DROP VIEW oir_table_wrap
/
CREATE VIEW oir_table_wrap
AS
   SELECT P.owner sch,
          P.objname tab,
          W.attrname orig,
          W.doc descrip,
          P.info prefix,
          S.info suffix      
     FROM plg_doir W,
          plg_doir P,
          plg_doir S
    WHERE W.owner = '*ALL'
      AND W.objname = '*ALL'
      AND W.objtype = 'ALIAS' 
      AND W.attrtype = 'WRAP ALIAS'
      AND W.infotype = '*ALL'
      AND W.infoseq = 1
      AND P.owner != W.owner
      AND P.objname != W.objname
      AND P.objtype = 'ALIAS' 
      AND P.attrtype = 'MISC ALIAS'
      AND P.infotype = W.infotype
      AND P.infoseq = W.infoseq
      AND P.attrname = W.attrname || 'PRE'
      AND S.owner !=  W.owner
      AND S.objname != W.objname
      AND S.objtype = 'ALIAS' 
      AND S.attrtype = 'MISC ALIAS'
      AND S.infotype = W.infotype
      AND S.infoseq = W.infoseq
      AND S.attrname = W.attrname || 'SUF'
      AND P.owner = S.owner
      AND P.objname = S.objname
/
GRANT SELECT ON oir_table_wrap TO PUBLIC;

DROP VIEW oir_driver
/
CREATE VIEW oir_driver
AS
   SELECT driver,
          objname name,
          info dbsrc,
          infotype just_like,
          doc descrip
     FROM plg_doir
    WHERE owner = '*ALL'
      AND objtype = 'DRIVER'
      AND attrtype = '*ALL'
      AND attrname = '*ALL'
      AND objname = driver
/
GRANT SELECT ON oir_driver TO PUBLIC;

DROP VIEW oir_driver_source
/
CREATE VIEW oir_driver_source
AS
   SELECT driver,
          info name,
          infotype type,
          attrtype inuse,
          infoseq seq,
          doc descrip
     FROM plg_doir
    WHERE owner = '*ALL'
      AND objname = '*ALL'
      AND objtype = 'DRIVERFILE'
/
GRANT SELECT ON oir_driver_source TO PUBLIC;

DROP VIEW oir_global_file
/
CREATE VIEW oir_global_file
AS
   SELECT owner sch,
          objname tab,
          attrname driver,
          info name,
          infotype ftype,
          DECODE (infotype, 
             0, 'Preparation',
             1, 'Compilable Code',
             2, 'Test Script',
             3, 'Documentation',
             4, 'HTML Documentation') ftypename,
          attrtype use_file,
          infoseq fnumber,
          doc descrip
     FROM plg_doir
    WHERE owner = '*ALL'
      AND objtype = 'DRIVERFILE'
      AND attrtype = '*ALL'
      AND attrname = '*ALL'
/
GRANT SELECT ON oir_global_file TO PUBLIC;

DROP VIEW oir_schema_file
/
CREATE VIEW oir_schema_file
AS
   SELECT owner sch,
          objname tab,
          attrname driver,
          info name,
          infotype ftype,
          DECODE (infotype, 
             0, 'Preparation',
             1, 'Compilable Code',
             2, 'Test Script',
             3, 'Documentation',
             4, 'HTML Documentation') ftypename,
          attrtype use_file,
          infoseq fnumber,
          doc descrip
     FROM plg_doir
    WHERE owner != '*ALL'
      AND objname = '*ALL'
      AND objtype = 'DRIVERFILE'
      AND attrtype = '*ALL'
      AND attrname = '*ALL'
/
GRANT SELECT ON oir_schema_file TO PUBLIC;

DROP VIEW oir_table_file
/
CREATE VIEW oir_table_file
AS
   SELECT owner sch,
          objname tab,
          attrname driver,
          info name,
          infotype ftype,
          DECODE (infotype, 
             0, 'Preparation',
             1, 'Compilable Code',
             2, 'Test Script',
             3, 'Documentation',
             4, 'HTML Documentation') ftypename,
          attrtype use_file,
          infoseq fnumber,
          doc descrip
     FROM plg_doir
    WHERE owner != '*ALL'
      AND objname != '*ALL'
      AND objtype = 'DRIVERFILE'
/
GRANT SELECT ON oir_table_file TO PUBLIC;

DROP VIEW oir_identifier
/
CREATE VIEW oir_identifier
AS
   SELECT driver,
          owner sch,
          objname tab,
          attrname orig,
          info subst,
          SUBSTR (infotype, 1, INSTR (infotype, CHR (8))-1) ident_query,
          SUBSTR (infotype, INSTR (infotype, CHR (8))+1) ident_list,
          doc descrip
     FROM plg_doir
    WHERE owner = '*ALL'
      AND objname = '*ALL'
      AND attrtype = 'IDENT ALIAS'
/
GRANT SELECT ON oir_identifier TO PUBLIC;

DROP VIEW oir_userprivs
/
CREATE VIEW oir_userprivs
AS
   SELECT driver,
          objname,
          info privilege
     FROM plg_doir
    WHERE objtype = 'PLGPRIV'
      AND attrname = USER
      AND owner = USER
/
GRANT SELECT ON oir_userprivs TO PUBLIC;

DROP VIEW oir_allprivs
/
CREATE VIEW oir_allprivs
AS
   SELECT driver,
          owner,
          objname,
          info privilege
     FROM plg_doir
    WHERE objtype = 'PLGPRIV'
      AND attrname = USER
/
GRANT SELECT ON oir_allprivs TO PUBLIC;

DROP VIEW oir_all_generations
/
CREATE VIEW oir_all_generations
AS
   SELECT driver,
          owner,
          objname,
          info target_schema,
          created_on,
          created_by,
          changed_on,
          changed_by
     FROM plg_doir
    WHERE objtype = 'REGGEN'
/
GRANT SELECT ON oir_all_generations TO PUBLIC;

DROP VIEW oir_user_generations
/
CREATE VIEW oir_user_generations
AS
   SELECT driver,
          owner,
          objname,
          info target_schema,
          created_on,
          created_by,
          changed_on,
          changed_by
     FROM plg_doir
    WHERE objtype = 'REGGEN'
      AND owner = USER
/
GRANT SELECT ON oir_user_generations TO PUBLIC;