REM This file updates the Meta Data tables for 6i 4.3 (MR5)

DECLARE

BEGIN 


       	-- M86.001
    dbms_output.put_line ('Applying Meta Data Patch M86.001');   

       	update ref_values set ref_meaning='nn (size = m)'
        where ref_domain = 'PLS1_DATA_TYPE'
        and ref_code='RAW';

        update ck_report_parameter_usages set rpu_default='%'
        where  RPU_PARAM_ID=35;
        
    dbms_output.put_line ('M86.001: Complete'); 

        -- M85.004
    dbms_output.put_line ('Applying Meta Data Patch M85.004');   
        begin
	
	delete from cdi_dflt_user_pref_usages
	where P_SNAME = 'CGSF'
	and P_CGVERSION = '5.0'
	and PF_SEQ = 1
	and DUP_SNAME in ( 'BUTPEX', 'CBXPEX', 'PLSPEX' );
		
	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
	'BUTPEX', -- DUP_SNAME
	'Extra expansion for button', -- DESCRIPTION
	'0.2 in', -- VALUE
	'N', -- DUP_INTERNAL
	'LAYOUT - BUTTON', -- DUP_TYPE
	18071, -- DESCRIPTION_MSGID
	'Y', -- MANDATORY
	'', -- CHR_DOMAIN
	30,  -- 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
	
	
	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
	'CBXPEX', -- DUP_SNAME
	'Extra expansion for check box', -- DESCRIPTION
	'0.3 in', -- VALUE
	'N', -- DUP_INTERNAL
	'LAYOUT - CHECK BOX', -- DUP_TYPE
	18072, -- DESCRIPTION_MSGID
	'Y', -- MANDATORY
	'', -- CHR_DOMAIN
	30,  -- 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
	
	
	
	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
	'PLSPEX', -- DUP_SNAME
	'Extra expansion for pop list', -- DESCRIPTION
	'0.5 in', -- VALUE
	'N', -- DUP_INTERNAL
	'LAYOUT - POP LIST', -- DUP_TYPE
	18073, -- DESCRIPTION_MSGID
	'Y', -- MANDATORY
	'', -- CHR_DOMAIN
	30,  -- 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
	
	end;

    dbms_output.put_line ('M85.004: Complete'); 

	--  M88.001
	--  Updating RM_LINK_CUSTOMIZED_QUERY for ATT	
    dbms_output.put_line ('Applying Meta Data Patch M88.001');   

	DELETE from RM_LINK_CUSTOMIZED_QUERY where query_name = 'ATT' ;
	
	
	-- 
	--   Get Inherited Attributes - have to use base table for connect by
	-- 
    -- Bug 3539019 : Replace the view with the table name.
	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 )' ) ;

    dbms_output.put_line ('M88.001: Complete'); 
 	
	--  M88.002
	--  Inserting 9i DATATYPES into REF_VALUES	
    dbms_output.put_line ('Applying Meta Data Patch M88.002');   
	
	delete from REF_VALUES where REF_CODE in ('INTERVAL YEAR TO MONTH', 'INTERVAL DAY TO SECOND', 'TIMESTAMP WITH LOCAL TIME ZONE', 'TIMESTAMP WITH TIME ZONE', 'UROWID');
	
	-- Add rows to the REF_VALUES table for new datatype
	-- For each new 9i datatype add a row for REF_DOMAIN = 'DATATYPE', 'DATA_TYPE', 'DATAFORMAT', 'DATA_FORMAT', 'DATATYPE_LARGE'
	-- REF_DOMAIN = 'DATATYPE'
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL YEAR TO MONTH', 'DATATYPE', 'INTERVAL YEAR TO MONTH', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL DAY TO SECOND', 'DATATYPE', 'INTERVAL DAY TO SECOND', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH LOCAL TIME ZONE', 'DATATYPE', 'TIMESTAMP WITH LOCAL TIME ZONE', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH TIME ZONE', 'DATATYPE', 'TIMESTAMP WITH TIME ZONE', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('UROWID', 'DATATYPE', 'UROWID', null, null) ;
	
	-- REF_DOMAIN = 'DATA_TYPE'
	-- NB REF_MEANING defines the rules for length, precision, scale
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL YEAR TO MONTH', 'DATA_TYPE', 'no (size = [m])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL DAY TO SECOND', 'DATA_TYPE', 'oo (size = [m[,n]])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH LOCAL TIME ZONE', 'DATA_TYPE', 'no (size = [m])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH TIME ZONE', 'DATA_TYPE', 'no (size = [m])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('UROWID', 'DATA_TYPE', 'nn (no size)', 'ROWID', null) ;
	
	-- REF_DOMAIN = 'DATAFORMAT'
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL YEAR TO MONTH', 'DATAFORMAT', 'INTERVAL YEAR TO MONTH', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL DAY TO SECOND', 'DATAFORMAT', 'INTERVAL DAY TO SECOND', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH LOCAL TIME ZONE', 'DATAFORMAT', 'TIMESTAMP WITH LOCAL TIME ZONE', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH TIME ZONE', 'DATAFORMAT', 'TIMESTAMP WITH TIME ZONE', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('UROWID', 'DATAFORMAT', 'UROWID', null, null) ;
	
	-- REF_DOMAIN = 'DATA_FORMAT'
	-- NB REF_MEANING defines the rules for length, precision, scale
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL YEAR TO MONTH', 'DATA_FORMAT', 'no (size = [m])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL DAY TO SECOND', 'DATA_FORMAT', 'oo (size = [m[,n]])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH LOCAL TIME ZONE', 'DATA_FORMAT', 'no (size = [m])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH TIME ZONE', 'DATA_FORMAT', 'no (size = [m])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('UROWID', 'DATAFORMAT', 'nn (no size)', 'ROWID', null) ;
	
	-- REF_DOMAIN = 'DATATYPE_LARGE'
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL YEAR TO MONTH', 'DATATYPE_LARGE', 'INTERVAL YEAR TO MONTH', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL DAY TO SECOND', 'DATATYPE_LARGE', 'INTERVAL DAY TO SECOND', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH LOCAL TIME ZONE', 'DATATYPE_LARGE', 'TIMESTAMP WITH LOCAL TIME ZONE', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH TIME ZONE', 'DATATYPE_LARGE', 'TIMESTAMP WITH TIME ZONE', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('UROWID', 'DATATYPE_LARGE', 'UROWID', null, null) ;
	
	-- REF_DOMAIN = 'PLS1_DATA_TYPE' used by CIHARG call to ciiutl.adjust_datatype_sizes
	-- NB REF_MEANING defines the rules for length, precision, scale
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL YEAR TO MONTH', 'PLS1_DATA_TYPE', 'no (size = [m])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL DAY TO SECOND', 'PLS1_DATA_TYPE', 'oo (size = [m[,n]])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH LOCAL TIME ZONE', 'PLS1_DATA_TYPE', 'no (size = [m])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH TIME ZONE', 'PLS1_DATA_TYPE', 'no (size = [m])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('UROWID', 'PLS1_DATA_TYPE', 'nn (no size)', 'ROWID', null) ;
	
	-- REF_DOMAIN = 'PLS_DATA_TYPE' used by CIHPGD call to ciiutl.adjust_datatype_sizes
	-- NB REF_MEANING defines the rules for length, precision, scale
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL YEAR TO MONTH', 'PLS_DATA_TYPE', 'no (size = [m])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL DAY TO SECOND', 'PLS_DATA_TYPE', 'oo (size = [m[,n]])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH LOCAL TIME ZONE', 'PLS_DATA_TYPE', 'no (size = [m])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH TIME ZONE', 'PLS_DATA_TYPE', 'no (size = [m])', 'DATETIME', null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('UROWID', 'PLS_DATA_TYPE', 'nn (no size)', 'ROWID', null) ;
	
	
	-- REF_DOMAIN = 'CLUSTER_COLUMN_DATATYPE'
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL YEAR TO MONTH', 'CLUSTER_COLUMN_DATATYPE', 'INTERVAL YEAR TO MONTH', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('INTERVAL DAY TO SECOND', 'CLUSTER_COLUMN_DATATYPE', 'INTERVAL DAY TO SECOND', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH LOCAL TIME ZONE', 'CLUSTER_COLUMN_DATATYPE', 'TIMESTAMP WITH LOCAL TIME ZONE', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('TIMESTAMP WITH TIME ZONE', 'CLUSTER_COLUMN_DATATYPE', 'TIMESTAMP WITH TIME ZONE', null, null) ;
	insert into REF_VALUES (REF_CODE, REF_DOMAIN, REF_MEANING, REF_PARENT_CODE, REF_SYSTEM_USE)
	  values  ('UROWID', 'CLUSTER_COLUMN_DATATYPE', 'UROWID', null, null) ;
	
    dbms_output.put_line ('M88.002: Complete'); 

 	-- Register 4_3 Upgrade in the RM$PATCHES RM$PATCHES table 

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











