REM This file updates the Meta Data tables for MR4

DECLARE

BEGIN 

    
	-- M65.001
	-- SERVER GENERATOR  PREFERENCE PARSER SHOULD BE DEFAULTED TO  N
	-- bug fix BUG 1956343 
  dbms_output.put_line ('Applying Meta Data Patch M65.001');   

  delete from cdi_dflt_user_pref_usages 
  where dup_sname = 'PARSER';

  INSERT INTO cdi_dflt_user_pref_usages
        (P_SNAME,       P_CGVERSION,    PF_SEQ,         DUP_SNAME,      VALUE,          DUP_INTERNAL,   DUP_TYPE,
        DESCRIPTION_MSGID,MANDATORY,    CHR_DOMAIN,     NLS_LANG,       DESCRIPTION)
  VALUES
        ('OSG',         '2.0',          1,              'PARSER',       'N',      'N', 'Generation - General',
        21209,           'Y',            'FLAG',    'US',        'Use Parsing Technology in order to Add Begin/End');

  dbms_output.put_line ('M65.001: Complete'); 

	-- M66.001
	-- Add support for 9i Datatypes. Including extending the length of the column to hold dataype from VARCHAR2(16) to VARCHAR2(35).
  -- Allow 9i as a database type. 
	-- bug fix BUG 1936545,
  --             1911007

  declare
     procedure add ( l_table IN varchar2
                   , l_add   IN varchar2 ) is
        csr               NUMBER;
        col_exist         EXCEPTION;
        tab_exist         EXCEPTION;
        PRAGMA EXCEPTION_INIT(col_exist, -01430);
        PRAGMA EXCEPTION_INIT(tab_exist, -00942);  
     begin
        csr := DBMS_SQL.OPEN_CURSOR;
        dbms_output.put_line('Adding    '||l_table||'.'||l_add);
        DBMS_SQL.PARSE(csr,'alter table '||l_table||' add ( '||l_add||' )',DBMS_SQL.V7);
        DBMS_SQL.CLOSE_CURSOR(csr);
     exception
        when col_exist then
           NULL;
           DBMS_SQL.CLOSE_CURSOR(csr);
        when tab_exist then
           NULL;
           DBMS_SQL.CLOSE_CURSOR(csr);
        when others then
           DBMS_SQL.CLOSE_CURSOR(csr);
           DBMS_OUTPUT.PUT_LINE ('...ERROR: '||sqlerrm);
     end;

     procedure modify ( l_table IN varchar2
                      , l_mod   IN varchar2 ) is
        csr                   NUMBER;
        is_already_not_null   EXCEPTION;
        tab_exist             EXCEPTION;
        PRAGMA EXCEPTION_INIT(is_already_not_null, -01442);
        PRAGMA EXCEPTION_INIT(tab_exist, -00942);
     begin
        csr := DBMS_SQL.OPEN_CURSOR;
        dbms_output.put_line('Modifying '||l_table||'.'||l_mod);
        DBMS_SQL.PARSE(csr,'alter table '||l_table||' modify ( '||l_mod||' )',DBMS_SQL.V7);
        DBMS_SQL.CLOSE_CURSOR(csr);
     exception
        when is_already_not_null then
           NULL;
           DBMS_SQL.CLOSE_CURSOR(csr);
        when tab_exist then
           NULL;
           DBMS_SQL.CLOSE_CURSOR(csr);
        when others then
           DBMS_SQL.CLOSE_CURSOR(csr);
           DBMS_OUTPUT.PUT_LINE ('...ERROR: '||sqlerrm);
     end;

  begin
     dbms_output.enable(1000000);

     update ck_installed_objects set ci_created = 'N' where ci_stage = 'CKALTMR4';

     -- Patch M66.001
   
     --  modify REF_CODE column of REF_VALUES from varchar2(16) to varchar2(35)
     modify ( 'REF_VALUES', 'REF_CODE varchar2(35)' );

     -- Modify I$SDD_COL definition column DATATYPE from varchar2(16) to varchar2(35)
     modify ( 'I$SDD_COL', 'DATATYPE varchar2(35)' );

     -- Modify I$SDD_DOM definition column DATATYPE from varchar2(16) to varchar2(35)
     modify ( 'I$SDD_DOM', 'DATATYPE varchar2(35)' );

     -- Modify I$SDD_ORT definition column SCALAR_DATATYPE from varchar2(16) to varchar2(35)
     -- This is to allow Oracle Collection Type (OCT) property SCALAR_DATATYPE to accept 9i datatypes
     modify ( 'I$SDD_ORT', 'SCALAR_DATATYPE varchar2(35)' );

     -- Modify I$SDD_CLA definition column DEFAULT_COLUMN_DATATYPE from varchar2(16) to varchar2(35)
     -- This is to allow Attribute Domain (ADC) property DEFAULT_COLUMN_DATATYPE to accept 9i datatypes
     -- This is a JDev element type which is not currently supported
     -- Modifications still made for internal consistency of Model
     modify ( 'I$SDD_CLA', 'DEFAULT_COLUMN_DATATYPE varchar2(35)' );

     -- Modify I$SDD_CCD definition column DATATYPE from varchar2(16) to varchar2(35)
     -- This is to allow Cluster Column (CCD) property DATATYPE to accept 9i datatypes
     modify ( 'I$SDD_CCD', 'DATATYPE varchar2(35)' );
     
     update ck_installed_objects set ci_created = 'Y' where ci_stage = 'CKALTMR4';

  END;  
  
  
  
  
  
  
  
  dbms_output.put_line ('Applying Meta Data Patch M66.001');   
  
  -- upd_rm_data_types.sql
  -- code required to update RM_DATA_TYPES 

  -- Update RM_DATA_TYPES where NAME = 'DATATYPE' (ID=22568)
  -- set DATA_LENGTH = 35 (from 16)
  update RM_DATA_TYPES
  set DATA_LENGTH = 35
  where ID = 22568 ;

  -- Update RM_DATA_TYPES where NAME = 'CLUSTER_COLUMN_DATATYPE' (ID=22568)
  -- set DATA_LENGTH = 35 (from 16)
  update RM_DATA_TYPES
  set DATA_LENGTH = 35
  where ID = 22396 ;
  
  -- ins_rm_data_type_values.sql
  -- code required to add rows to RM_DATA_TYPE_VALUES

  -- if patch is being rerun delete rows first to avoid ORA errors
  delete from RM$NLS_DATA_TYPE_VALUES where DEV_VALUE in ('INTERVAL YEAR TO MONTH', 'INTERVAL DAY TO SECOND', 'TIMESTAMP WITH LOCAL TIME ZONE', 'TIMESTAMP WITH TIME ZONE', 'UROWID');

  -- Add rows to RM$NLS_DATA_TYPE_VALUES for new datatypes
  -- DID = 22568 (DATATYPE)
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22568, null, 'INTERVAL YEAR TO MONTH', 'INTERVAL YEAR TO MONTH') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22568, null, 'INTERVAL DAY TO SECOND', 'INTERVAL DAY TO SECOND') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22568, null, 'TIMESTAMP WITH LOCAL TIME ZONE', 'TIMESTAMP WITH LOCAL TIME ZONE') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22568, null, 'TIMESTAMP WITH TIME ZONE', 'TIMESTAMP WITH TIME ZONE') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22568, null, 'UROWID', 'UROWID') ;

  -- Add rows to RM$NLS_DATA_TYPE_VALUES for new datatypes
  -- DID = 22424 (DATATYPE_LARGE)
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22424, null, 'INTERVAL YEAR TO MONTH', 'INTERVAL YEAR TO MONTH') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22424, null, 'INTERVAL DAY TO SECOND', 'INTERVAL DAY TO SECOND') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22424, null, 'TIMESTAMP WITH LOCAL TIME ZONE', 'TIMESTAMP WITH LOCAL TIME ZONE') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22424, null, 'TIMESTAMP WITH TIME ZONE', 'TIMESTAMP WITH TIME ZONE') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22424, null, 'UROWID', 'UROWID') ;

  -- Add rows to RM$NLS_DATA_TYPE_VALUES for new datatypes
  -- DID = 22482 (PLS_DATATYPE)
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22482, null, 'INTERVAL YEAR TO MONTH', 'INTERVAL YEAR TO MONTH') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22482, null, 'INTERVAL DAY TO SECOND', 'INTERVAL DAY TO SECOND') ; 
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22482, null, 'TIMESTAMP WITH LOCAL TIME ZONE', 'TIMESTAMP WITH LOCAL TIME ZONE') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22482, null, 'TIMESTAMP WITH TIME ZONE', 'TIMESTAMP WITH TIME ZONE') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22482, null, 'UROWID', 'UROWID') ;

  -- Add rows to RM$NLS_DATA_TYPE_VALUES for new datatypes
  -- DID = 22396 (CLUSTER_COLUMN_DATATYPE)
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22396, null, 'INTERVAL YEAR TO MONTH', 'INTERVAL YEAR TO MONTH') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22396, null, 'INTERVAL DAY TO SECOND', 'INTERVAL DAY TO SECOND') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22396, null, 'TIMESTAMP WITH LOCAL TIME ZONE', 'TIMESTAMP WITH LOCAL TIME ZONE') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22396, null, 'TIMESTAMP WITH TIME ZONE', 'TIMESTAMP WITH TIME ZONE') ;
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22396, null, 'UROWID', 'UROWID') ;

  -- upd_ref_values.sql
  -- code required to update REF_VALUES

  -- update existing TIMESTAMP so that it can accept a precision
  -- stored as property DECIMAL_PLACES as requested by Designer (Steve Lyon)
  update REF_VALUES
  set REF_MEANING = 'no (size = [m])'
  where REF_CODE = 'TIMESTAMP'
  and REF_DOMAIN = 'DATA_TYPE' ;

  -- upd_rm_meta_data.sql
  -- code required to update RM$PROPERTY_EXTENSIONS and RM_SQL_COLUMNS

  -- Update meta model data for COLUMN.DATATYPE 
  -- Update RM$PROPERTY_EXTENSIONS (COLUMN.DATATYPE : PID = 16794)
  update RM$PROPERTY_EXTENSIONS
  set LENGTH = 35
  where pid = 16794 ;

  -- Update RM_SQL_COLUMNS (I$SDD_COL.DATATYPE : TABLE_IRID = 384781276406159970429865297456941113)
  update RM_SQL_COLUMNS
  set DATA_LENGTH = 35
  where TABLE_IRID = 384781276406159970429865297456941113
  and name = 'DATATYPE' ;


  -- Update meta model data for DOMAIN.DATATYPE 
  -- Update RM$PROPERTY_EXTENSIONS (DOMAIN.DATATYPE : PID = 17275)
  update RM$PROPERTY_EXTENSIONS
  set LENGTH = 35
  where pid = 17275 ;

  -- Update RM_SQL_COLUMNS (I$SDD_DOM.DATATYPE : TABLE_IRID = 384781276414187993450743694320224313)
  update RM_SQL_COLUMNS
  set DATA_LENGTH = 35
  where TABLE_IRID = 384781276414187993450743694320224313
  and name = 'DATATYPE' ;


  -- Update meta model data for CLUSTER_COLUMN.DATATYPE 
  -- Update RM$PROPERTY_EXTENSIONS (CLUSTER_COLUMN.DATATYPE : PID = 17886)
  update RM$PROPERTY_EXTENSIONS
  set LENGTH = 35
  where pid = 17886 ;

  -- Update RM_SQL_COLUMNS (I$SDD_CCD.DATATYPE : TABLE_IRID = 384781276404280468569683178661890105)
  update RM_SQL_COLUMNS
  set DATA_LENGTH = 35
  where TABLE_IRID = 384781276404280468569683178661890105
  and name = 'DATATYPE' ;


  -- Update meta model data for ORACLE_COLLECTION_TYPE.SCALAR_DATATYPE 
  -- Update RM$PROPERTY_EXTENSIONS (ORACLE_COLLECTION_TYPE.SCALAR_DATATYPE : PID = 10069)
  update RM$PROPERTY_EXTENSIONS
  set LENGTH = 35
  where pid = 10069 ;

  -- Update RM_SQL_COLUMNS (I$SDD_ORT.DATATYPE : TABLE_IRID = 384781276433988876113416116701251641)
  update RM_SQL_COLUMNS
  set DATA_LENGTH = 35
  where TABLE_IRID = 384781276433988876113416116701251641
  and name = 'SCALAR_DATATYPE' ;

  -- Update meta model data for ATTRIBUTE_DOMAIN .DEFAULT_COLUMN_DATATYPE 
  -- Update RM$PROPERTY_EXTENSIONS (ATTRIBUTE_DOMAIN.DEFAULT_COLUMN_DATATYPE : PID =  19096)
  update RM$PROPERTY_EXTENSIONS
  set LENGTH = 35
  where pid =  19096 ;

  -- Update RM_SQL_COLUMNS (I$SDD_CLA.DEFAULT_COLUMN_DATATYPE : TABLE_IRID = 384781276404719648652590055666763833)
  update RM_SQL_COLUMNS
  set DATA_LENGTH = 35
  where TABLE_IRID = 384781276404719648652590055666763833
  and name = 'DEFAULT_COLUMN_DATATYPE' ;

  -- upd_odb_db_version.sql
  -- code required to add row to RM_DATA_TYPE_VALUES for Oracle 9i version

  -- if patch is being rerun delete rows first to avoid ORA errors
  delete from RM$NLS_DATA_TYPE_VALUES where DID = 22551 and NLS_VALUE = 'Oracle 9i' ;

  -- Add new row to RM_DATA_TYPE_VALUES table for new database version
  -- RM_DATA_TYPES.NAME = 'ODB_DB_VERSION' (ID = 22551)
  insert into RM$NLS_DATA_TYPE_VALUES (NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
    values  ('US', 22551, null, '9', 'Oracle 9i') ;
  
  dbms_output.put_line ('M66.001: Complete');   
  
 	-- M68.001
	-- new preference WEBPTH to FOrm Generator Web Deployment user preferences 
	-- bug fix BUG 1973089 
  
  delete from cdi_dflt_user_pref_usages
  where dup_sname = 'WEBPTH';

  insert into cdi_dflt_user_pref_usages
  (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
  ,ACI
  ,SUB
  ,LOV
  ,NLS_LANG)
  values
  (
  'CGSF', -- P_SNAME
  '5.0', -- P_CGVERSION
  1, -- PF_SEQ
  'WEBPTH', -- DUP_SNAME
  'Server-local form path', -- DESCRIPTION
  '/', -- VALUE
  'N', -- DUP_INTERNAL
  'WEB DEPLOYMENT', -- DUP_TYPE
  18078, -- DESCRIPTION_MSGID
  'N', -- MANDATORY
  '', -- CHR_DOMAIN
  240,  -- CHR_MAXLEN
  NULL,  -- NUM_HIVAL
  NULL,  -- NUM_LOVAL
  'N', -- DOM
  'N', -- TAB
  'N', -- CON
  'N', -- COL
  'Y', -- MOD
  'N', -- DTU
  'N', -- CONU
  'N', -- IGRP
  'N', -- DCU
  'N', -- ACI
  'N', -- SUB
  'N', -- LOV
  'US'); -- NLS_LANG
  
  dbms_output.put_line ('M68.001: Complete');     

 	-- M68.001
	-- Replace Missing Rule Parameters 
	-- bug fix BUG 1969465
  --             1969487
  
  declare 

  l_rule_irid number;

  begin

   -- Create New Parameters for RULE: LATEST_DIAGRAM_CONTENTS
   
   select irid 
   into l_rule_irid
   from i$sdd_rules
   where name = 'LATEST_DIAGRAM_CONTENTS';
   
   delete from i$sdd_rule_params
   where rule_irid = l_rule_irid;
   
   insert into i$sdd_rule_params
       ( rule_irid
       , param_seq
       , name
       , nls_name
       , type
       , notm
       , date_created
       , created_by
       )
   values
       ( l_rule_irid
       , 1
       , 'DIAGRAM'
       , 'Diagram Path'
       , 'OBJECT_NAME'
       , 0
       , SYSDATE
       , USER
      );
   
   insert into i$sdd_rule_params
      ( rule_irid
      , param_seq
      , name
      , nls_name
      , type
      , notm
      , date_created
      , created_by
      )
      values
      ( l_rule_irid
      , 2
      , 'BRANCH'
      , 'Branch Label'
      , 'BRANCH_NAME'
      , 0
      , SYSDATE
      , USER
      );

   -- Nullify the rule sql:
   update i$sdd_rules
   set rule_sql = NULL
   where name   = 'LATEST_DIAGRAM_CONTENTS';
      
   -- Create New Parameters for RULE: LATEST_CONFIG_CONTENTS
   begin
       select irid 
       into l_rule_irid
       from i$sdd_rules
       where name = 'LATEST_CONFIG_CONTENTS'
       and not exists (select rowid from i$sdd_rule_param_values where rule_irid = irid);
   exception when no_data_found then
        l_rule_irid := null;
   end;
   
   if l_rule_irid is not null then 
   
		delete from i$sdd_rule_params
		where rule_irid = l_rule_irid;

		insert into i$sdd_rule_params
			 ( rule_irid
			 , param_seq
			 , name
			 , nls_name
			 , type
			 , notm
			 , date_created
			 , created_by
			 )
		values
			 ( l_rule_irid
			 , 1
			 , 'BRANCH'
			 , 'Branch label'
			 , 'BRANCH_NAME'
			 , 0
			 , SYSDATE
			 , USER
			);

		insert into i$sdd_rule_params
			( rule_irid
			, param_seq
			, name
			, nls_name
			, type
			, notm
			, date_created
			, created_by
			)
			values
			( l_rule_irid
			, 2
			, 'CONFIGURATION'
			, 'Configuration'
			, 'OBJECT_NAME'
			, 0
			, SYSDATE
			, USER
			);

     end if;   
  end;   
  
  dbms_output.put_line ('M69.001: Complete');     
 
 
  -- insert new metadata for new table created in M53.001
  delete from sdd_reserved_names where rn_res_name = 'CK_TEMP_IRIDS';
  
  insert into sdd_reserved_names 
  ( RN_RES_NAME
  , RN_TYPE
  , RN_COMMENT
  , RN_GLOBAL
  , RN_TEMPORARY
  , RN_ENABLED
  , RN_USR_ACCESS
  , RN_CDAPI )
  values
  ( 'CK_TEMP_IRIDS', 'TABLE', 'TABLE', 'N', 'N', 'Y', 'Y', 'N'); 
   
 -- Register MR4 Upgrade in the RM$PATCHES RM$PATCHES table 

	rmpatch.install('CI04012.MR4_UPGRADE');
	
	rmpatch.installed_ok;

	commit;
	
EXCEPTION

	WHEN OTHERS THEN 
		 dbms_output.put_line('* Exception Raised  ');
		 dbms_output.put_line('...ERROR: '||substrb(sqlerrm,1,240));
		 rollback work;
		 RAISE;

END;
/











