REM
REM $Header: \\\\ukst76\\rcsroot.7_0\\ckrau\\schema\\RCS\\ckview.sql 1.18 1999/01/12 11:30:19 aobhrai Exp $ ckview.sql Copyr (c) 1992, 1989 Oracle
REM
REM History
REM 20-06-95 AOBHRAI new column TABLE_TYPE2 for CIW_RELATION_DEFINITION (288031)
REM 10-04-97 AOBHRAI Patch for #474252 applied 
REM 11-09-97 AOBHRAI added new view ci_xmd_general_modules_sqs (549788)
REM 26-01-97 AOBHRAI Changes to Wide Views required for DDT (616572)
REM 13-05-98 AOBHRAI Added NLS Preference Views (648919)
REM
REM ci_parameters		used by Reporting Utility	Added for 2.03(7.0.0)
REM ci_reports			used by Reporting Utility	Added for 2.03(7.0.0)
REM ci_rpt_groups 		used by Reporting Utility	Added for 2.03(7.0.0)
REM ciw_constraints		used by DDL and DDW 		
REM ciw_relation_definitions	used by DDL and DDW 	
REM rm_text_lines		used by API
REM ci_xmd_general_modules_sqs  used by Matrix Diagrammer	Added for 2.0.10 (7.02)
REM cdi_nls_valid_prefs         used by DOM, DE                 Added for 2.0.20.2 (648919)  
REM cdi_nls_default_prefs       used by DOM, DE                 Added for 2.0.20.2 (648919)

prompt Creating View CI_REPORTS
create or replace FORCE view ci_reports as
  ( select
  	rpt.rpt_id           id,
  	nr.rpt_orientation   orientation,
  	nr.ck_user_defined   user_defined,
  	nr.rpt_name          name,
  	nr.rpt_title         title,
  	nr.rpt_purpose       purpose,
  	nr.rpt_needapp       needapp,
   	nr.rpt_status        status,
        rpt.rpt_help_id      helpid,
        rpt.ck_user          ck_user
    from ck_nls_reports      nr,
         ck_reports          rpt,
         rm_repositories     r
    where nr.nls_language = r.nls_language
           and nr.rpt_id = rpt.rpt_id
           and rpt.ck_user in (user,'SYS')
)
/
 
prompt Creating View CI_RPT_GROUPS
create or replace FORCE view ci_rpt_groups as
  ( select     
        nrg.rpg_id         id,
        rg.ck_user_defined user_defined,
        rg.rpg_sequence    sequence,
        nrg.rpg_name       name, 
        nrg.rpg_type       type,       
        rg.rpg_help_id     helpid,
        rg.ck_user         ck_user
    from ck_nls_report_groups   nrg,
         ck_report_groups        rg,
         rm_repositories     r
    where nrg.nls_language = r.nls_language
          and nrg.rpg_id = rg.rpg_id
          and rg.ck_user in (user,'SYS')
)
/

prompt Creating View CI_PARAMETERS
create or replace FORCE view ci_parameters as
  ( select nrp.param_id               id,
  	   rp.param_name              name,
  	   rp.ck_user_defined         user_defined,
           rp.param_lov_id	      lov_id,	
  	   rp.param_datatype          datatype,
 	   nrp.param_prompt           prompt,
           nrp.param_hint             hint,
  	   nrp.param_domain           domain,
  	   nrp.param_case             casemode,	  
           nrp.param_wild             wild,
           rp.ck_user                 ck_user
      from ck_nls_parameters   nrp,
           ck_report_parameters       rp,
           rm_repositories     r
     where nrp.nls_language = r.nls_language
	and nrp.param_id = rp.param_id
	and rp.ck_user in (user,'SYS')
)
/

REM #474252 applied
PROMPT Creating View RM_TEXT_LINES
create or replace FORCE VIEW rm_text_lines AS
SELECT txt_ref 
,      txt_seq 
,      txt_type
,      txt_text
,      txt_notm
from cdi_text;

REM WIDE VIEWS:

prompt Creating View CIW_CONSTRAINTS
create or replace FORCE VIEW ciw_constraints AS
SELECT sdd_el.el_flag1           arc_mandatory 
      ,sdd_el.el_seq3            arc_number 
      ,sdd_el.el_user_changed_by changed_by
      ,sdd_el.el_occur_type      constraint_type
      ,sdd_el.el_status          complete_flag
      ,sdd_el.el_user_created_by created_by
      ,sdd_el.el_date_changed    date_changed
      ,sdd_el.el_date_created    date_created
      ,sdd_el.el_type_of         element_type_name
      ,sdd_el.el_purp            error_message
      ,sdd_el.el_char1           exception_table
      ,sdd_el.el_flag3           fk_cascade_delete 
      ,sdd_el.el_flag4           fk_cascade_update 
      ,sdd_el.el_flag2           fk_transferable 
      ,sdd_el.el_srce_type       for_joining_to 
      ,sdd_el.el_2nd_within_id   foreign_table_reference 
      ,sdd_el.el_id              id
      ,sdd_el.el_switches        implementation_level 
      ,sdd_el.el_flag5           key_updateable 
      ,sdd_el.el_flag6           mandatory_flag 
      ,sdd_el.el_name            name
      ,sdd_el.el_notm            number_of_times_modified
      ,sdd_el.el_3rd_within_id   relationship_end_reference 
      ,sdd_el.el_comment         remark 
      ,sdd_el.el_task_code       enabled_flag 
      ,sdd_el.el_4th_within_id   unique_identifier_reference
      ,sdd_el.el_within_id       table_reference
      ,sdd_el.el_ty              types
      ,nvl(sdd_el.el_4th_within_id,sdd_el.el_5th_within_id) unique_key_reference
      ,sdd_el.el_usrx0 user_defined_property_0
      ,sdd_el.el_usrx1 user_defined_property_1
      ,sdd_el.el_usrx2 user_defined_property_2
      ,sdd_el.el_usrx3 user_defined_property_3
      ,sdd_el.el_usrx4 user_defined_property_4
      ,sdd_el.el_usrx5 user_defined_property_5
      ,sdd_el.el_usrx6 user_defined_property_6
      ,sdd_el.el_usrx7 user_defined_property_7
      ,sdd_el.el_usrx8 user_defined_property_8
      ,sdd_el.el_usrx9 user_defined_property_9
      ,decode( sdd_el.el_occur_type
             , 'PRIMARY', 'PU'
             , 'UNIQUE',  'PU'
             , 'FOREIGN', 'F'
             , 'CHECK',   'C') constraint_type2 
FROM   sdd_elements sdd_el
WHERE  sdd_el.el_type_of = 'OCO'
  AND  sdd_el.el_occur_type IN
       ( 'PRIMARY', 'UNIQUE', 'FOREIGN','CHECK' )
WITH CHECK OPTION
/

PROMPT Creating View CIW_RELATION_DEFINITION
create or replace FORCE VIEW ciw_relation_definitions
(
     table_type
    ,cluster_reference
    ,remark
    ,create_status
    ,date_created
    ,date_changed
    ,created_by
    ,changed_by
    ,authority
    ,record_file_reference
    ,record_size
    ,record_format
    ,alias
    ,id
    ,name
    ,column_prefix
    ,initial_number_of_rows
    ,maximum_number_of_rows
    ,element_type_name
    ,application_system_owned_by
    ,display_title
    ,database_type
    ,storage_definition_reference
    ,tablespace_reference
    ,percent_free
    ,percent_used
    ,initial_transaction
    ,maximum_transaction
    ,locking_sequence
    ,journal_flag
    ,select_text_flag
    ,database_reference
    ,refresh_type
    ,refresh_start
    ,refresh_next
    ,cached
    ,min_partitions
    ,parallel
    ,max_scans
    ,all_databases_ind
	,datawarehouse_type
    ,table_type2
    ,user_defined_property_0
    ,user_defined_property_9
    ,user_defined_property_8
    ,user_defined_property_7
    ,user_defined_property_6
    ,user_defined_property_5
    ,user_defined_property_4
    ,user_defined_property_3
    ,user_defined_property_2
    ,user_defined_property_1
)
AS SELECT
     EL_OCCUR_TYPE
    ,EL_NJ_ID
    ,EL_COMMENT
    ,EL_STATUS
    ,EL_DATE_CREATED
    ,EL_DATE_CHANGED
    ,EL_USER_CREATED_BY
    ,EL_USER_CHANGED_BY
    ,EL_AUTHORITY
    ,EL_WITHIN_ID
    ,EL_REC_SIZE
    ,EL_REC_FORM
    ,EL_SHORT_NAME
    ,EL_ID
    ,EL_NAME
    ,EL_COL_PREF
    ,EL_INIT_VOL
    ,EL_MAX_VOL
    ,EL_TYPE_OF
    ,EL_ELEM_OWNED_BY
    ,EL_DEF_SCR_PROMPT
    ,EL_FLAG6
    ,EL_4TH_WITHIN_ID
    ,EL_2ND_WITHIN_ID
    ,EL_INIT_PERC_USED
    ,EL_MAX_PERC_USED
    ,EL_SEQ
    ,EL_SEQ2
    ,EL_SEQ3
    ,EL_FLAG7
    ,EL_SWITCHES
    ,EL_5TH_WITHIN_ID
    ,EL_GR_FREQ
    ,EL_INIT_VAL
    ,EL_CONDITION
    ,EL_FLAG1
    ,EL_MAX_LEN
    ,EL_FLAG2
    ,EL_MAX_LEN2
    ,EL_FLAG8
	,EL_CLASSN
    ,decode(EL_OCCUR_TYPE
            ,'RECORD','REC'
            ,'TABLE','TBL'
            ,'SNAP','SNP'
            ,'VIEW', 'VW')
    ,EL_USRX0
    ,EL_USRX9
    ,EL_USRX8
    ,EL_USRX7
    ,EL_USRX6
    ,EL_USRX5
    ,EL_USRX4
    ,EL_USRX3
    ,EL_USRX2
    ,EL_USRX1
FROM
    sdd_elements
 WHERE  EL_TYPE_OF = 'TAB'
;

prompt Creating View CIW_DB_OBJECT_IMPLEMENTATIONS

create or replace FORCE VIEW ciw_db_object_implementations AS
SELECT el.el_id              id
     , el.el_type_of         element_type_name
     , el.el_within_id       database_user_reference 
     , el.el_occur_type      object_implementation_type
     , el.el_2nd_within_id   table_definition_reference 
     , el.el_3rd_within_id   tablespace_reference 
     , el.el_4th_within_id   storage_definition_reference
     , el.el_init_perc_used  percent_free 
     , el.el_2nd_within_id   sequence_reference 
FROM   sdd_elements el
WHERE  el.el_type_of = 'DOI'
AND    el.el_occur_type IN ( 'TBI', 'SQI' )
/


prompt Creating View CI_XMD_GENERAL_MODULES_SQS
create or replace FORCE VIEW ci_xmd_general_modules_sqs AS 
SELECT m.*
,      mc.id module_component_id
,      sq.id sql_query_set_id
FROM   ci_general_modules             m
,      ci_module_component_inclusions mci
,      ci_module_components           mc
,      ci_sql_query_sets              sq
WHERE  m.id  = mci.general_module_reference
AND    mc.id = mci.module_component_reference
AND    mc.id = sq.module_component_reference (+)
UNION
SELECT mg.*
,      to_number(null) module_component_id
,      to_number(null) sql_query_set_id
FROM   ci_general_modules mg
/

prompt Creating View CDI_NLS_VALID_PREFS
create or replace FORCE view CDI_NLS_VALID_PREFS AS
select
     VP_DOMAIN              
,    VP_VALUE               
,    VP_MEANING             
,    VP_ABBREVIATION        
from CDI_VALID_PREFS
where NLS_LANG in ( select NLS_LANGUAGE
                    from   RM$REPOSITORIES )
/

REM 849705 Added missing columns to CDI_NLS_DEFAULT_PREFS

prompt Creating View CDI_NLS_DEFAULT_PREFS
create or replace FORCE VIEW CDI_NLS_DEFAULT_PREFS AS
select 
     P_SNAME                
,    P_CGVERSION            
,    PF_SEQ                 
,    DUP_SNAME  
,    DESCRIPTION          
,    VALUE                  
,    DUP_INTERNAL           
,    DUP_TYPE               
,    DESCRIPTION_MSGID      
,    MANDATORY              
,    CHR_DOMAIN             
,    CHR_MAXLEN             
,    NUM_HIVAL              
,    NUM_LOVAL              
,    DOM                    
,    TAB                    
,    CON                    
,    COL                    
,    MOD                    
,    DTU                    
,    CONU                   
,    IGRP                   
,    DCU                    
,    ABT                    
,    VAT                    
,    OBT                    
,    TME                    
,    ARL                    
,    TAT                    
,    TOP                    
,    OPP                    
,    VVA                    
,    ABTABT                 
,    TCO                    
,    GES                    
,    PCK  
,    LOV 
,    SUB
,    ACI                  
from CDI_DFLT_USER_PREF_USAGES
where NLS_LANG in ( select NLS_LANGUAGE
                    from   RM$REPOSITORIES )
/

prompt Creating View CDI_NLS_CLASS_TYPES 
create or replace FORCE VIEW CDI_NLS_CLASS_TYPES AS
select LANGUAGE_NAME          
,      TYPE_NAME              
,      GENERATE_FLAG          
,      TEMPLATE_FILE          
,      FILE_FILTER            
,      SEQ                    
,      DEFAULT_FILE_NAME      
,      OPEN_BEGIN_MARKER      
,      CLOSE_BEGIN_MARKER     
,      OPEN_END_MARKER        
,      CLOSE_END_MARKER
,      FILE_TYPE                       
from CDI_CLASS_SET_CODE_TYPES 
where NLS_LANG in ( select NLS_LANGUAGE
                    from   RM$REPOSITORIES )
/

prompt Creating View CDI_NLS_CODE_TYPES
create or replace FORCE VIEW CDI_NLS_CODE_TYPES AS
select LANGUAGE_NAME          
,      TYPE_NAME              
,      GEN_TEMPLATE_FILE      
,      REGEN_TEMPLATE_FILE
,      OPEN_BEGIN_MARKER      
,      CLOSE_BEGIN_MARKER     
,      END_MARKER             
,      FILE_FILTER            
,      SEQ                    
,      REGISTRY_NAME          
,      DEFAULT_FILE_NAME      
,      CLOSE_END_MARKER
,      SECTION_TYPE
,      FILE_TYPE       
from CDI_CODE_SECTION_TYPES 
where NLS_LANG in ( select NLS_LANGUAGE
                    from   RM$REPOSITORIES )
/

prompt Creating View CDI_NLS_GENERATION_TARGETS
create or replace FORCE VIEW CDI_NLS_GENERATION_TARGETS AS
select  NAME                   
,       SEQ                    
,       LANGUAGE               
,       REGISTRY_NAME          
,       MEMBER_BEGIN_MARKER
,       CLOSE_MEMBER_MARKER
,       MEMBER_END_MARKER      
from CDI_GENERATION_TARGETS
where NLS_LANG in ( select NLS_LANGUAGE
                    from   RM$REPOSITORIES )
/


