-- Database Objects which share the same namespace
-- TAB (3 parts, not REC), OOT, PLM, DSY, OSQ
create or replace view ci_named_database_objects as
select irid
      ,irid pac_irid
      ,types
      ,element_type_name
      ,table_type occur_type
      ,name
  from sdd_tab
 where table_type != 'RECORD'
union all
select irid
      ,irid pac_irid
      ,types
      ,element_type_name
      ,ort_type occur_type
      ,name
  from sdd_ort
 where ort_type = 'OOT'
union all
select irid
      ,irid pac_irid
      ,types
      ,element_type_name
      ,module_type occur_type
      ,nvl(implementation_name, short_name)
  from sdd_mod smod
 where module_type = 'PLM'
   and not exists  /* Ignore clashes with PLMs that are inside a package */
       (select null
          from ci_plsql_modules plm
              ,ci_module_networks modmod
         where modmod.child_module_reference = smod.irid
           and modmod.parent_module_reference = plm.id
           and plm.plsql_module_type in ('PACKAGE','TYPE BODY'))     
union all
select irid
      ,irid pac_irid
      ,types
      ,element_type_name
      ,'OSQ' occur_type
      ,name
  from sdd_osq
union all (      -- This nasty construction - see below
select irid
      ,pac_ref pac_irid
      ,types
      ,element_type_name
      ,'DSY' occur_type
      ,name
  from sdd_dsy
 where scope != 'PUBLIC'
union
select irid
      ,irid pac_irid
      ,types
      ,element_type_name
      ,'QTD' occur_type
      ,name
  from sdd_qtd
)  
/
-- The nasty bracketed mess at the nd of this view is to work around an 8.0.5 problem. If tey were all
-- simply 'union all'ed together, a PL/SQL query on the view that would succeed, causes an
-- 'end-of-file on communiacation channel' error. It seems to be fixed on 8.1, so remove this
-- stuff when we can guarantee an 8.1 server. (and test the insert of a table!)

prompt Create view ci_entity_namespace_objects
create or replace view ci_entity_namespace_objects as
select irid
      ,types
      ,element_type_name
      ,name
      ,short_name
      ,plural
  from sdd_ent
union all
select irid
      ,types
      ,element_type_name
      ,name
      ,null
      ,null
  from sdd_syn
union all
select irid
      ,types
      ,element_type_name
      ,name
      ,null
      ,null
  from sdd_ter
/
-- SACs which have the same style of UID check
-- (name unique within APP) OCO, IND, TRG
prompt Create view ci_named_database_sacs
create or replace view ci_named_database_sacs as
select irid
      ,types
      ,element_type_name
      ,table_ref parent_reference
      ,name
  from sdd_oco
union all
select irid
      ,types
      ,element_type_name
      ,nvl(nvl(table_definition_ref, snapshot_definition_ref), cluster_ref) parent_reference
      ,name
  from sdd_ind
union all
select irid
      ,types
      ,element_type_name
      ,nvl(table_definition_ref, view_definition_ref) parent_reference
      ,name
  from sdd_trg
/

--View on the name space for the name uniqueness constraint on Entity Object Attribute
prompt Create view ci_ent_object_att_namespace
create or replace view ci_ent_object_att_namespace
  (irid, name, element_type_name, entity_object_ref, association_ref) as
select irid
      ,name
      ,element_type_name
      ,entity_object_ref
      ,to_number(null) association_ref
  from sdd_cat
  where element_type_name = 'EAT'
union all
select irid
      ,name
      ,element_type_name
      ,classifier_ref entity_object_ref
      ,to_number(null) association_ref
  from sdd_met
union all
select irid
      ,name
      ,element_type_name
      ,entity_object_ref
      ,association_ref
  from sdd_aen
/

--Database Users and Roles (GRP) share the same namespace
create or replace view ci_dbu_grp_namespace as
select irid, name, database_reference, types
  from ci_database_users
 union all
select irid, name, oracle_database_reference, types
  from ci_roles
/ 



