REM This file updates the Meta/Instance Data for 4.9 - 4.10 Upgrade
REM
REM PATCH 
REM NUMBER AUTHOR   CHANGE TYPE   DESCRIPTION
REM ------ -------- ------------- ---------------------------------------------------
REM 001    STLYON   META DATA     ADD MISSING ORACLE 10G SYSTEM PRIVILEGES
REM 004    STLYON   META_DATA     ADDS ORACLE 10G OPTION IN SERVER GENERATOR DIALOG
REM 008    STLYON   META_DATA     ADDS row to SDD_RESERVED_NAMES for RN_RES_NAME ='CK_REPORT_FOLDERS'
REM 015    SKHWAJA  META_DATA     UPDATES rows in RM_LINK_CUSTOMIZED_QUERY

DECLARE
   s number := 10;   
BEGIN

  -- M95.001
  -- Bug 3283094 : Remove ADMINISTER SECURITY
  delete REF_VALUES
  where  REF_DOMAIN  = 'SYSTEM_PRIVILEGES'
  and    REF_MEANING = 'ADMINISTER SECURITY';


  -- Bug 3283252 : Add new 10g privileges
  -- Delete missing 10g SYSTEM_PRIVILEGES so we can insert them (in case patch run more than once)
  --
  delete REF_VALUES
  where  REF_DOMAIN  = 'SYSTEM_PRIVILEGES'
  and    REF_MEANING in (
  'ADMINISTER ANY SQL TUNING SET',
  'ADMINISTER SQL TUNING SET',
  'ADVISOR',
  'ALTER ANY SQL PROFILE',
  'ANALYZE ANY DICTIONARY',
  'CREATE ANY JOB',
  'CREATE ANY SQL PROFILE',
  'CREATE JOB',
  'DROP ANY SQL PROFILE',
  'EXECUTE ANY CLASS',
  'EXECUTE ANY PROGRAM',
  'EXPORT FULL DATABASE',
  'FLASHBACK ANY TRANSACTION',
  'IMPORT FULL DATABASE',
  'MANAGE SCHEDULER' );
  
  
  -- Insert new 10g SYSTEM_PRIVILEGES
  --
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('ADM_ANY_STS', 'SYSTEM_PRIVILEGES', 'ADMINISTER ANY SQL TUNING SET');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('ADM_STS', 'SYSTEM_PRIVILEGES', 'ADMINISTER SQL TUNING SET');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('ADVISOR', 'SYSTEM_PRIVILEGES', 'ADVISOR');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('ALT_ANY_SQL_PRFL', 'SYSTEM_PRIVILEGES', 'ALTER ANY SQL PROFILE');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('ANLYZ_ANY_DICT', 'SYSTEM_PRIVILEGES', 'ANALYZE ANY DICTIONARY');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('CRT_ANY_JOB', 'SYSTEM_PRIVILEGES', 'CREATE ANY JOB');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('CRT_ANY_SQL_PRFL', 'SYSTEM_PRIVILEGES', 'CREATE ANY SQL PROFILE');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('CRT_JOB', 'SYSTEM_PRIVILEGES', 'CREATE JOB');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('DRP_ANY_SQL_PRFL', 'SYSTEM_PRIVILEGES', 'DROP ANY SQL PROFILE');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('EXC_ANY_CLASS', 'SYSTEM_PRIVILEGES', 'EXECUTE ANY CLASS');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('EXC_ANY_PRG', 'SYSTEM_PRIVILEGES', 'EXECUTE ANY PROGRAM');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('EXP_FULL_DB', 'SYSTEM_PRIVILEGES', 'EXPORT FULL DATABASE');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('FLSHBCK_ANY_TX', 'SYSTEM_PRIVILEGES', 'FLASHBACK ANY TRANSACTION');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('IMP_FULL_DB', 'SYSTEM_PRIVILEGES', 'IMPORT FULL DATABASE');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('MNG_SCHEDULER', 'SYSTEM_PRIVILEGES', 'MANAGE SCHEDULER');
  

  -- Bug 3283094 : Remove ADMINISTER SECURITY
  delete rm$nls_data_type_values
  where  DID = 22489
  and    NLS_VALUE = 'ADMINISTER SECURITY';
  
  -- Bug 3283252 : Add new 10g privileges
  -- Delete missing 10g SYSTEM_PRIVILEGES so we can insert them (in case patch run more than once)
  --
  delete rm$nls_data_type_values
  where  DID = 22489
  and    NLS_VALUE in (
  'ADMINISTER ANY SQL TUNING SET',
  'ADMINISTER SQL TUNING SET',
  'ADVISOR',
  'ALTER ANY SQL PROFILE',
  'ANALYZE ANY DICTIONARY',
  'CREATE ANY JOB',
  'CREATE ANY SQL PROFILE',
  'CREATE JOB',
  'DROP ANY SQL PROFILE',
  'EXECUTE ANY CLASS',
  'EXECUTE ANY PROGRAM',
  'EXPORT FULL DATABASE',
  'FLASHBACK ANY TRANSACTION',
  'IMPORT FULL DATABASE',
  'MANAGE SCHEDULER' );
  
  
  -- Insert new 10g SYSTEM_PRIVILEGES
  --
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'ADM_ANY_STS', 'ADMINISTER ANY SQL TUNING SET');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'ADM_STS', 'ADMINISTER SQL TUNING SET');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'ADVISOR', 'ADVISOR');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'ALT_ANY_SQL_PRFL', 'ALTER ANY SQL PROFILE');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'ANLYZ_ANY_DICT', 'ANALYZE ANY DICTIONARY');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'CRT_ANY_JOB', 'CREATE ANY JOB');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'CRT_ANY_SQL_PRFL', 'CREATE ANY SQL PROFILE');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'CRT_JOB', 'CREATE JOB');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'DRP_ANY_SQL_PRFL', 'DROP ANY SQL PROFILE');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'EXC_ANY_CLASS', 'EXECUTE ANY CLASS');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'EXC_ANY_PRG', 'EXECUTE ANY PROGRAM');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'EXP_FULL_DB', 'EXPORT FULL DATABASE');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'FLSHBCK_ANY_TX', 'FLASHBACK ANY TRANSACTION');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'IMP_FULL_DB', 'IMPORT FULL DATABASE');
  insert into rm$nls_data_type_values (irid,  NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)values
  (jr_util.get_new_irid , 'US', 22489, null,'MNG_SCHEDULER', 'MANAGE SCHEDULER');


  -- Set rm$nls_data_type_values.SEQ for DID=22489 to be in NLS_VALUE alphabetic order
  for  C in (select nls_value from rm$nls_data_type_values where did=22489 order by nls_value )
   loop
     update rm$nls_data_type_values rm set rm.seq = s where did=22489 and rm.nls_value = C.nls_value;
     s := s + 10;
  end loop;


  -- M95.004
  -- 
  jr_rm_ins.set_product('CI');
  jr_rm_ins.set_product_code('CI');

  -- delete row so patch can be rerun
  delete rm$nls_data_type_values where did=22551 and dev_value='10';
  jr_rm_ins.rm_data_type_value_ins   -- generates a new IRID
 	( 22551          -- DID
          ,'10'          -- dev Value
          ,'Oracle 10g'  -- nls value
          ,null          -- Sequence
         );

  update rm$nls_data_type_values set seq = 1 where did=22551 and dev_value='7.1';
  update rm$nls_data_type_values set seq = 2 where did=22551 and dev_value='7.2';
  update rm$nls_data_type_values set seq = 3 where did=22551 and dev_value='7.3';
  update rm$nls_data_type_values set seq = 4 where did=22551 and dev_value='8';
  update rm$nls_data_type_values set seq = 5, nls_value = 'Oracle 8i' where did=22551 and dev_value='8.1';
  update rm$nls_data_type_values set seq = 6 where did=22551 and dev_value='9';
  update rm$nls_data_type_values set seq = 7 where did=22551 and dev_value='10';
  
  --M95.008
  --
  delete from sdd_reserved_names where rn_res_name = 'CK_REPORT_FOLDERS';
  
  insert into sdd_reserved_names values
  ('CK_REPORT_FOLDERS', 'TABLE', null, 'N', 'N', 'Y', 'Y', 'N', null, null);

  --M95.015
  --
  DELETE from RM_LINK_CUSTOMIZED_QUERY where query_name = 'ATT' ;

  INSERT into RM_LINK_CUSTOMIZED_QUERY (query_name, query_text )
  values
  ( 'ATT',
   'in ( select distinct irid from i$SDD_ENT
   where jr_vn.context(ivid) > 0
   connect by prior supertype_ref = irid
   start with irid = :C1 )' );


  -- Register 4_10 Upgrade in the RM$PATCHES RM$PATCHES table
  rmpatch.install('CI04012.4_10_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;
/
