REM This file updates the Meta Data tables for MR1

DECLARE

BEGIN 

    
	-- M31.002
	-- remove test instance data from global name domains.
	-- bug fix 1268098.
    dbms_output.put_line ('Applying Meta Data Patch M31.002');   

	delete from sdd_nls_global_name_domains
	where ivid=2260030411875292503268219503746859311;

	delete from sdd_global_name_domains
	where ivid=2260030411256322483625529366297297199;

    dbms_output.put_line ('M31.002: Complete'); 

	-- M31.003
	-- 1308578 RELATIVE TAB STOP EDITOR FIELDS MESSED UP WHEN FIELD END<START (AS SET IN PP)
	
	dbms_output.put_line ('Applying Meta Data Patch M31.003'); 
	
	-- RM$ASSERTIONS

	delete from rm$assertions where id = 81613;

	insert into rm$assertions (id, expression, types, enabled, warning, generated, is_uid_or_elm, deferred, product, facility, name, 
							   defined_against, code, classification, message_usage) 
	values
	(81613, 81643,40,'Y','N','Y','E','N', 'CI', 'CDA', 'CIHUBIO_ENDTAB',4916, 2212, 'EXP',81608);

	-- RM_EXPRESSIONS

	delete from rm_expressions where id = 81643;

	insert into rm_expressions (id, types, assertion, bind_context, syntax, text)
	values
	(81643, 62, 81613,4916, 'ELMTRAV',':end_tab is null && :start_tab is null  or nvl(:end_tab,9999999999) > nvl(:start_tab,0)');

	-- RM_EXPRESSION_PROPERTY_USAGES

	delete from rm_expression_property_usages where id in (81644, 81645, 81646, 81647);

	insert into rm_expression_property_usages (id, types, expression, exp_seq, property, trv_seq)
	values
	(81644, 63, 81643, 1, 15598, 1 );

	insert into rm_expression_property_usages (id, types, expression, exp_seq, property, trv_seq) 
	values
	(81645, 63, 81643, 2, 15597, 1 );

	insert into rm_expression_property_usages (id, types, expression, exp_seq, property, trv_seq)
	values
	(81646, 63, 81643, 3, 15598, 1 );

	insert into rm_expression_property_usages (id, types, expression, exp_seq, property, trv_seq)
	values
	(81647, 63, 81643, 4, 15597, 1 );

	-- RM_MESSAGE_USAGES

	delete from rm_message_usages where id = 81608;

	insert into rm_message_usages(id,types, name, nls_code, nls_utility)
	values
	(81608, 64, 'CIHUBIO_ENDTAB', 2212,'CDA');

	-- RM$NLS_MESSAGES

	delete from rm$nls_messages where irid = 565064068848595978259203068498823225;

	insert into rm$nls_messages
		   ( irid, 
			 lang_id,     utility,     code,      severity, text)
	values ( 565064068848595978259203068498823225,
			 'US',       'CDA',       2212,       1,        
			 '%0!s: Unbound Item''s END_TAB stop value must be higher than START_TAB stop.');

    dbms_output.put_line ('M31.003: Complete');   

	-- M31.006 
	-- 1296463 INCORRECT ERROR MESSAGE FOR SNAPSHOT IMPLEMENTATION IN WRONG SNAPSHOT REFRESH GR
    dbms_output.put_line ('Applying Meta Data Patch M31.006');   

	update rm$assertions set code = 1374 where name = 'CIHSNIE_GRP';

	update rm_message_usages set nls_code = 1374 where id = 69677;
	
	dbms_output.put_line ('M31.006: Complete'); 

	-- M31.007
	-- BUG 1278888 - GENERATOR PREFERENCES PRODUCT DROPDOWN
    dbms_output.put_line ('Applying Meta Data Patch M31.007'); 

	delete from cdi_products where sname = 'WSG'  and name in ('Web PL/SQL Generator','PL/SQL Web Generator') and version = '1.0';

	insert into cdi_products (sname, name, version) 
	values 
	('WSG', 'Web PL/SQL Generator', '1.0');

	delete from CDI_PRODUCT_FLAVOURS where p_sname = 'WSG' and description in ('Web PL/SQL Generator','PL/SQL Web Generator') 
	and p_cgversion = '1.0' and seq=1;

	insert into CDI_PRODUCT_FLAVOURS(p_sname, p_cgversion, seq, description)
	values 
	('WSG', '1.0', 1, 'Web PL/SQL Generator');

    dbms_output.put_line ('M31.007: Complete'); 
	
	-- M32.001
	-- 1000888 SYSTEM PRIVILEGES FOR ORACLE 8_1 MISSING
	
	dbms_output.put_line ('Applying Meta Data Patch M32.001'); 

	delete from ref_values 
	where ref_domain = ('SYSTEM_PRIVILEGES')
	and   ref_code in 
	( 'ALTR_ANY_INDXTYP'
	, 'ALTR_ANY_LIB'   
	, 'ALTR_OP'   
	, 'CRT_LIB'   
	, 'EXEC_ANY_LIB' 
	, 'EXT_ANY_TYPE'
	, 'EXT_TYPE' );       

	insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)
	values 
	('ALTR_ANY_INDXTYP', 'SYSTEM_PRIVILEGES', 'ALTER ANY INDEXTYPE');
	insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)
	values 
	('ALTR_ANY_LIB',     'SYSTEM_PRIVILEGES', 'ALTER ANY LIBRARY');
	insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)
	values 
	('ALTR_OP',          'SYSTEM_PRIVILEGES', 'ALTER OPERATOR');
	insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)
	values 
	('CRT_LIB',          'SYSTEM_PRIVILEGES', 'CREATE LIBRARY');
	insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)
	values 
	('EXEC_ANY_LIB',     'SYSTEM_PRIVILEGES', 'EXECUTE ANY LIBRARY');
	insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)
	values 
	('EXT_ANY_TYPE',     'SYSTEM_PRIVILEGES', 'EXTENDS ANY TYPE');
	insert into ref_values (REF_CODE, REF_DOMAIN, REF_MEANING)
	values 
	('EXT_TYPE',         'SYSTEM_PRIVILEGES', 'EXTENDS TYPE');

	delete from rm$nls_data_type_values where irid in 
	(  567929358032713727745133111273867321
	,  567929358032718450111615980919081017
	,  567929358032723172478098850564294713
	,  567929358032727894844581720209508409
	,  567929358032732617211064589854722105
	,  567929358032737339577547459499935801
	,  567929358032742061944030329145149497 );

	insert into rm$nls_data_type_values (irid, NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
	values
	(567929358032713727745133111273867321, 'US', 22489, 22,  'ALTR_ANY_INDXTYP',  'ALTER ANY INDEXTYPE');

	insert into rm$nls_data_type_values (irid, NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
	values
	(567929358032718450111615980919081017, 'US', 22489, 23,  'ALTR_ANY_LIB',      'ALTER ANY LIBRARY');

	insert into rm$nls_data_type_values (irid, NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
	values
	(567929358032723172478098850564294713, 'US', 22489, 95,  'ALTR_OP',           'ALTER OPERATOR');

	insert into rm$nls_data_type_values (irid, NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
	values
	(567929358032727894844581720209508409, 'US', 22489, 334, 'CRT_LIB',           'CREATE LIBRARY');

	insert into rm$nls_data_type_values (irid, NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
	values
	(567929358032732617211064589854722105, 'US', 22489, 656, 'EXEC_ANY_LIB',      'EXECUTE ANY LIBRARY');

	insert into rm$nls_data_type_values (irid, NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
	values
	(567929358032737339577547459499935801, 'US', 22489, 667, 'EXT_ANY_TYPE',      'EXTENDS ANY TYPE');

	insert into rm$nls_data_type_values (irid, NLS_LANGUAGE, DID, SEQ, DEV_VALUE, NLS_VALUE)
	values
	(567929358032742061944030329145149497, 'US', 22489, 668, 'EXT_TYPE',          'EXTENDS TYPE');

	update ref_values
	set ref_meaning   = 'DEQUEUE ANY QUEUE'
	where ref_meaning = 'DEQUEUE ANY'
	and ref_domain    = 'SYSTEM_PRIVILEGES';

	update rm$nls_data_type_values
	set nls_value   = 'DEQUEUE ANY QUEUE'
	where nls_value = 'DEQUEUE ANY'
	and did         = 22489;

	update ref_values
	set ref_meaning   = 'ENQUEUE ANY QUEUE'
	where ref_meaning = 'ENQUEUE ANY'
	and ref_domain    = 'SYSTEM_PRIVILEGES';

	update rm$nls_data_type_values
	set nls_value   = 'ENQUEUE ANY QUEUE'
	where nls_value = 'ENQUEUE ANY'
	and did         = 22489;

	update ref_values
	set ref_meaning   = 'MANAGE ANY QUEUE'
	where ref_meaning = 'MANAGE ANY'
	and ref_domain    = 'SYSTEM_PRIVILEGES';

	update rm$nls_data_type_values
	set nls_value   = 'MANAGE ANY QUEUE'
	where nls_value = 'MANAGE ANY'
	and did         = 22489;
	
	dbms_output.put_line ('M32.001: Complete'); 


	-- M33.004
	-- BUG 1271503 - API ALLOWS USER OBJECT INDEX STORAGE ON INDEX FROM A DIFFERENT TABLE

	dbms_output.put_line ('Applying Meta Data Patch M33.004'); 

	-- RM$ASSERTIONS

	delete from rm$assertions where id = 81689;

	insert into rm$assertions (id, expression, types, enabled, warning, generated,is_uid_or_elm, deferred, product, facility, name, 
							   defined_against, code, classification, message_usage) 
	values
	(81689, 81862,40,'Y','N','Y','E','Y', 'CI', 'CDA','CIHUIS_MATCHING_TABLES',4850, 2213, 'EXP',81688);

	-- RM_EXPRESSIONS

	delete from rm_expressions where id = 81862;

	insert into rm_expressions (id, types, assertion, bind_context, syntax, text)
	values
	(81862, 62, 81689, 4850, 'ELMTRAV',
	':INDEX_REFERENCE IS NULL OR :table_implementation_reference IS NULL OR cihuis.check_tables_match(:id,:table_implementation_reference.table_definition_reference)');


	-- RM_EXPRESSION_PROPERTY_USAGES

	delete from rm_expression_property_usages where id in (81863, 81864, 81865, 81866, 81867);

	insert into rm_expression_property_usages (id, types, expression, exp_seq,property, trv_seq)
	values
	(81863, 63, 81862, 1, 20357, 1 );

	insert into rm_expression_property_usages (id, types, expression, exp_seq, property, trv_seq)
	values
	(81864, 63, 81862, 2, 21952, 1 );

	insert into rm_expression_property_usages (id, types, expression, exp_seq, property, trv_seq)
	values
	(81865, 63, 81862, 3, 5279, 1 );

	insert into rm_expression_property_usages (id, types, expression, exp_seq, property, trv_seq)
	values
	(81866, 63, 81862, 4, 21952, 1 );

	insert into rm_expression_property_usages (id, types, expression, exp_seq, property, trv_seq)
	values
	(81867, 63, 81862, 4, 20378, 2 );

	-- RM_MESSAGE_USAGES

	delete from rm_message_usages where id = 81688;

	insert into rm_message_usages(id,types, name, nls_code, nls_utility)
	values
	(81688, 64, 'CIHUIS_MATCHING_TABLES', 2213,'CDA');

	-- RM$NLS_MESSAGES

	delete from rm$nls_messages where irid = 570799791301566029359918753584269369;

	insert into rm$nls_messages
		   ( irid,   
			 lang_id,     utility,     code,      severity, text)
	values (570799791301566029359918753584269369, 
			'US',        'CDA',       2213,   1,
			'%0!s: Index Storage Usage Table must match table index is being created on.'); 
			
	dbms_output.put_line ('M33.004: Complete'); 
	
	-- 1397020 Missing rows from SDD_RESERVED_NAMES 
	
	delete from sdd_reserved_names where rn_res_name in ( 'JRM_DEFERRED_CHECK', 
														  'JRM_POP',            
														  'JRM_VAL',            
														  'JR_GEN_VIEW',        
														  'JR_OBJECT',          
														  'JR_OBJECT_FK')
    and rn_type = 'PACKAGE';
	
 	insert into sdd_reserved_names 
	   ( RN_RES_NAME, RN_TYPE, RN_COMMENT, RN_GLOBAL, RN_TEMPORARY, RN_ENABLED, RN_USR_ACCESS, RN_CDAPI)
	values                         
	   ('JRM_DEFERRED_CHECK','PACKAGE','PACKAGE','N','N','Y','N','N');

 	insert into sdd_reserved_names 
	   ( RN_RES_NAME, RN_TYPE, RN_COMMENT, RN_GLOBAL, RN_TEMPORARY, RN_ENABLED, RN_USR_ACCESS, RN_CDAPI)
	values                         
	   ('JRM_POP','PACKAGE','PACKAGE','N','N','Y','N','N');

 	insert into sdd_reserved_names 
	   ( RN_RES_NAME, RN_TYPE, RN_COMMENT, RN_GLOBAL, RN_TEMPORARY, RN_ENABLED, RN_USR_ACCESS, RN_CDAPI)
	values                         
	   ('JRM_VAL','PACKAGE','PACKAGE','N','N','Y','N','N');
	
 	insert into sdd_reserved_names 
	   ( RN_RES_NAME, RN_TYPE, RN_COMMENT, RN_GLOBAL, RN_TEMPORARY, RN_ENABLED, RN_USR_ACCESS, RN_CDAPI)
	values                         
	   ('JR_GEN_VIEW','PACKAGE','PACKAGE','N','N','Y','N','N');
	
 	insert into sdd_reserved_names 
	   ( RN_RES_NAME, RN_TYPE, RN_COMMENT, RN_GLOBAL, RN_TEMPORARY, RN_ENABLED, RN_USR_ACCESS, RN_CDAPI)
	values                         
	   ('JR_OBJECT','PACKAGE','PACKAGE','N','N','Y','N','N');
	
 	insert into sdd_reserved_names 
	   ( RN_RES_NAME, RN_TYPE, RN_COMMENT, RN_GLOBAL, RN_TEMPORARY, RN_ENABLED, RN_USR_ACCESS, RN_CDAPI)
	values                         
	   ('JR_OBJECT_FK','PACKAGE','PACKAGE','N','N','Y','N','N');
	
	-- Register MR1 Upgrade in the RM$PATCHES RM$PATCHES table 

	rmpatch.install('CI04012.MR1_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;
/











