REM This file updates the Meta/Instance Data for 4.10 - 4.11 Upgrade
REM
REM PATCH 
REM NUMBER AUTHOR   CHANGE TYPE   DESCRIPTION
REM ------ -------- ------------- ---------------------------------------------------
REM 001    STLYON   META DATA     ADD MISSING ORACLE 10G SYSTEM PRIVILEGES
REM 002    STLYON   META DATA Two rows added to table CK_REPORT_GROUP_USAGES.
REM 003    STLYON   META DATA Update rows in RM_LINK_CUSTOMIZED_QUERY
REM 009    SKHWAJA  META DATA     Update RM_LINK_PROPERTIES and RM_PROPERTIES. 
REM 010    STLYON   INSTANCE DATA Update REF_VALUES.


BEGIN

  -- M96.001
  -- Bug 3283252 : Add new 10g privileges
  -- Delete missing 10g SYSTEM_PRIVILEGES so we can rerun patch
  --
  delete REF_VALUES
  where  REF_DOMAIN  = 'SYSTEM_PRIVILEGES'
  and    REF_MEANING in (
  'EXEMPT IDENTITY POLICY',
  'SELECT ANY TRANSACTION' );

  -- Insert new 10g SYSTEM_PRIVILEGES
  --
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('EXMPT_ID_PLCY', 'SYSTEM_PRIVILEGES', 'EXEMPT IDENTITY POLICY');
  insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)values
  ('SEL_ANY_TX', 'SYSTEM_PRIVILEGES', 'SELECT ANY TRANSACTION');
 
  -- Bug 3283252 : Remove FLASHBACK ANY TRANSACTION
  delete rm$nls_data_type_values
  where  DID = 22489
  and    NLS_VALUE = 'FLASHBACK ANY TRANSACTION';

  -- Bug 3283252 : Add new 10g privileges
  -- Delete missing 10g SYSTEM_PRIVILEGES so we can rerun patch
  --
  delete rm$nls_data_type_values
  where  DID = 22489
  and    NLS_VALUE in (
  'EXEMPT IDENTITY POLICY',
  'SELECT ANY TRANSACTION'
   );

  -- 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,'EXMPT_ID_PLCY', 'EXEMPT IDENTITY POLICY');
  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,'SEL_ANY_TX', 'SELECT ANY TRANSACTION');


  -- Set rm$nls_data_type_values.SEQ for DID=22489 to be in NLS_VALUE alphabetic order
  declare
   s number := 10;
  begin
    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;
  end;

  -- M96.002
  -- Add ckabcost(id=11) report to Enterprise Modelling(id=25) and Business Function(id=29) groups
  -- Delete rows so that patch is re-runnable
  delete CK_REPORT_GROUP_USAGES 
  where RGU_RPT_ID = 11 
  and   RGU_RPG_ID in (25,29);

  insert into CK_REPORT_GROUP_USAGES
  ( RGU_RPT_ID, RGU_RPG_ID, RGU_SEQUENCE, CK_USER_DEFINED, CK_USER) values
  ( 11,         25,         36,           'N',             'SYS'  );

  insert into CK_REPORT_GROUP_USAGES
  ( RGU_RPT_ID, RGU_RPG_ID, RGU_SEQUENCE, CK_USER_DEFINED, CK_USER) values
  ( 11,         29,         16,           'N',             'SYS'  );
  
  -- M96.003
  update rm_link_customized_query
  set query_text = replace( query_text, '|| AR.name', '|| ENT.name' )
  where query_name='UIDS';

  -- M96.009
  --Update the row for DATABASE_REFERENCE property
  update i$rm_link_properties 
     set update_mode = 'O'
     where id = 20318;

  --Update the row for DATABASE_REFERENCE property
  update i$rm_properties 
     set update_mode = 'O'
     where id = 20318;

  -- M96.010
  -- Bug 1227831 : Allow a null precision with a non null scale for DEC[IMAL](*,s) and NUMBER(*,s)
  update ref_values
  set    ref_meaning = replace(ref_meaning, 'o3', 'oo')
  where  ref_code    in ('DEC', 'DECIMAL', 'NUMBER')
  and    ref_domain  in ('COLLECTION_SCALAR_DATATYPE', 
                         'DATATYPE_LARGE',
                         'DATATYPE', 'DATA_TYPE');

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