REM This script flags objects to be reloaded for MR2 Upgrade 

DECLARE 

BEGIN 

-- UPDATES THE OBJECTS IN THE FOLLOWING STAGES
-- CKTABS
-- CKINDX
-- JRPACK   -- already done
-- JRVIEW
-- CKPACCOM -- already done
-- CKPACK   -- already done
-- JRTRIG   -- already done
-- NOTE THE CI_AUDIT PACKAGES IS INSTALLED DURING CKPACCOM AND
-- CKTRIG, hence CKTRIG has not been included in this list


	-- UPDATE for TABLES
	UPDATE CK_INSTALLED_OBJECTS 
	SET CI_CREATED = 'R'
	WHERE  CI_TYPE = 'TABLE'
	AND    CI_NAME IN 
	( 'RM$VIOLATIONS'       
	, 'RM$DEFERRED_CHECKS'	
	, 'RM$DEFERRED_QUERIES'	
	, 'RM$ACTIVITIES');
	
	-- UPDATE for INDEXES	
	UPDATE CK_INSTALLED_OBJECTS 
	SET CI_CREATED = 'R'
	WHERE  CI_TYPE = 'INDEX'
	AND    CI_NAME IN 
	( 'RM$VIOLATIONS_ACTIVITY'		
	, 'RM$VIOLATIONS_ASSERTION'		
	, 'RM$VIOLATIONS_ELEMENT'		
	, 'RM$VIOLATIONS_PROPERTY'		
	, 'RM$DEFERRED_QUERIES_1'		
	, 'RM$DEFERRED_QUERIES_ELEMENT'
	, 'RM$ACTIVITIES_PARENT'		
	, 'RM$ACTIVITIES_CHILDREN'		
	, 'RM$ACTIVITIES_OPEN_CHILD'	
	, 'RM$ACTIVITIES_OPEN_PARENT'	
	, 'RM$DEFERRED_CHECKS_ASSERTION'
	, 'RM$DEFERRED_CHECKS_ELEMENT'	
	, 'RM$DEFERRED_CHECKS_ID'		
	, 'RM$DEFERRED_CHECKS_ACTIVITY');	
	
	-- UPDATE for VIEWS
	UPDATE CK_INSTALLED_OBJECTS 
	SET CI_CREATED = 'R'
	WHERE  CI_TYPE = 'VIEW'
	AND    CI_NAME IN 
	( 'JR_SYS_OP_GUID'		, 'SDD_FILPAC'
	, 'SDD_OBJECT_VERSIONS'		, 'SDD_FOLDERS'
	, 'SDD_CONFIGURATIONS'		, 'SDD_FOLDER_MEMBERS'
	, 'SDD_CONFIGURATION_MEMBERS'	, 'SDD_FSE'
	, 'SDD_WORKAREAS'		, 'SDD_FUN'
	, 'CK_APPLICATION_LOGIC'	, 'SDD_FUNATT'
	, 'RM_TEXT_LINES'		, 'SDD_FUNBUN'
	, 'SDD_NLS_MESSAGES'		, 'SDD_FUNENT'
	, 'SDD_NLS_PROMPTS'		, 'SDD_FUNEVT'
	, 'SDD_NLS_PROPLIST_ENTRIES'	, 'SDD_GRP'
	, 'SDD_NLS_RESOURCES'		, 'SDD_GRPGRP'
	, 'SDD_PROPERTY_LISTS'		, 'SDD_GRPMOD'
	, 'SDD_PROPLIST_ENTRIES'	, 'SDD_IGR'
	, 'SDD_A1'			, 'SDD_IND'
	, 'SDD_A10'			, 'SDD_INDCOL'
	, 'SDD_A11'			, 'SDD_INP'
	, 'SDD_A2'			, 'SDD_INS'
	, 'SDD_A20'			, 'SDD_ITE'
	, 'SDD_A21'			, 'SDD_ITM'
	, 'SDD_A23'			, 'SDD_LAN'
	, 'SDD_A24'			, 'SDD_LST'
	, 'SDD_A3'			, 'SDD_LVI'
	, 'SDD_A4'			, 'SDD_MAP'
	, 'SDD_A5'			, 'SDD_MCH'
	, 'SDD_A6'			, 'SDD_MCL'
	, 'SDD_A7'			, 'SDD_MCN'
	, 'SDD_A8'			, 'SDD_MEMCOM'
	, 'SDD_A9'			, 'SDD_MET'
	, 'SDD_ACI'			, 'SDD_METRUL'
	, 'SDD_ACR'			, 'SDD_MOD'
	, 'SDD_ACRACR'			, 'SDD_MODBUN'
	, 'SDD_ACRORG'			, 'SDD_MODFUN'
	, 'SDD_ACT'			, 'SDD_MODMOD'
	, 'SDD_ACTACT'			, 'SDD_MODPGD'
	, 'SDD_ACTE1'			, 'SDD_MPA'
	, 'SDD_ACTORG'			, 'SDD_MTI'
	, 'SDD_ACUACR'			, 'SDD_MUN'
	, 'SDD_ADR'			, 'SDD_MWF'
	, 'SDD_AEN'			, 'SDD_OCL'
	, 'SDD_AENEAT'			, 'SDD_OCO'
	, 'SDD_AEU'			, 'SDD_ODB'
	, 'SDD_AMCAMC'			, 'SDD_ODBODB'
	, 'SDD_AMCBOB'			, 'SDD_OKY'
	, 'SDD_AMCPRJ'			, 'SDD_ORG'
	, 'SDD_ARG'			, 'SDD_ORGPER'
	, 'SDD_ARGITE'			, 'SDD_ORS'
	, 'SDD_ASS'			, 'SDD_ORT'
	, 'SDD_ASSASS'			, 'SDD_OSP'
	, 'SDD_ATT'			, 'SDD_OSQ'
	, 'SDD_ATV'			, 'SDD_OST'
	, 'SDD_BAT'			, 'SDD_OSTOST'
	, 'SDD_BLI'			, 'SDD_OTB'
	, 'SDD_BOB'			, 'SDD_PER'
	, 'SDD_BOBINC'			, 'SDD_PERACR'
	, 'SDD_BRL'			, 'SDD_PERPER'
	, 'SDD_BRU'			, 'SDD_PGD'
	, 'SDD_BRUBLI'			, 'SDD_PRS'
	, 'SDD_BUN'			, 'SDD_PRU'
	, 'SDD_CAT'			, 'SDD_PSE'
	, 'SDD_CATAEN'			, 'SDD_PST'
	, 'SDD_CATEAT'			, 'SDD_PSTADC'
	, 'SDD_CCD'			, 'SDD_PVL'
	, 'SDD_CDE'			, 'SDD_QSU'
	, 'SDD_CLA'			, 'SDD_QTD'
	, 'SDD_CLAAEN'			, 'SDD_QUE'
	, 'SDD_CLACLA'			, 'SDD_QUI'
	, 'SDD_CLAEVC'			, 'SDD_RELEND'
	, 'SDD_CLAINC'			, 'SDD_RGP'
	, 'SDD_CMA'			, 'SDD_RPG'
	, 'SDD_CNU'			, 'SDD_RUL'
	, 'SDD_COB'			, 'SDD_RULRUL'
	, 'SDD_COL'			, 'SDD_SBA'
	, 'SDD_COM'			, 'SDD_SMI'
	, 'SDD_COMNOD'			, 'SDD_SOU'
	, 'SDD_COMODB'			, 'SDD_SPV'
	, 'SDD_COR'			, 'SDD_SQS'
	, 'SDD_CORI'			, 'SDD_SRG'
	, 'SDD_CQA'			, 'SDD_SRU'
	, 'SDD_CQACOL'			, 'SDD_STE'
	, 'SDD_CRU'			, 'SDD_STESTE'
	, 'SDD_CSG'			, 'SDD_SUD'
	, 'SDD_CST'			, 'SDD_SYN'
	, 'SDD_DAT'			, 'SDD_TAB'
	, 'SDD_DATCOM'			, 'SDD_TABENT'
	, 'SDD_DBU'			, 'SDD_TAG'
	, 'SDD_DBUGRP'			, 'SDD_TAGVAL'
	, 'SDD_DBUMOD'			, 'SDD_TBICOL'
	, 'SDD_DEU'			, 'SDD_TBP'
	, 'SDD_DFI'			, 'SDD_TER'
	, 'SDD_DFL'			, 'SDD_TMS'
	, 'SDD_DFLATT'			, 'SDD_TRA'
	, 'SDD_DFLDIT'			, 'SDD_TRG'
	, 'SDD_DIA'			, 'SDD_TRGCOL'
	, 'SDD_DIAGRAM_SEGMENTS'	, 'SDD_TSQ'
	, 'SDD_DIR'			, 'SDD_UCA'
	, 'SDD_DIT'			, 'SDD_UDE'
	, 'SDD_DOC'			, 'SDD_UDECLI'
	, 'SDD_DOCXXX'			, 'SDD_UDESUP'
	, 'SDD_DOG'			, 'SDD_UDS'
	, 'SDD_DOI'			, 'SDD_UEE'
	, 'SDD_DOM'			, 'SDD_UES'
	, 'SDD_DPR'			, 'SDD_UID'
	, 'SDD_DST'			, 'SDD_UPR'
	, 'SDD_DSTATT'			, 'SDD_URU'
	, 'SDD_DSTDIT'			, 'SDD_USC'
	, 'SDD_DSY'			, 'SDD_USCACR'
	, 'SDD_E1'			, 'SDD_USCE1'
	, 'SDD_E2'			, 'SDD_USCEPA'
	, 'SDD_E20'			, 'SDD_USCEVI'
	, 'SDD_E3'			, 'SDD_USCEVO'
	, 'SDD_E4'			, 'SDD_USCUSC'
	, 'SDD_E5'			, 'SDD_VAL'
	, 'SDD_E6'			, 'SDD_VALGAT'
	, 'SDD_ENT'			, 'SDD_VLE'
	, 'SDD_ENTBUN'			, 'SDD_VLEU'
	, 'SDD_ENTUIE'			, 'SDD_VLEVOA'
	, 'SDD_EOU'			, 'SDD_VLI'
	, 'SDD_ERU'			, 'SDD_VLU'
	, 'SDD_EVH'			, 'SDD_VOU'
	, 'SDD_EVR'			, 'SDD_VWTAB'
	, 'SDD_EVS'			, 'SDD_WAT'
	, 'SDD_EVT'			, 'SDD_WIN'
	, 'SDD_EXT'			, 'SDD_WIT'
	, 'SDD_FIL'			, 'SDD_WMT'
	, 'SDD_FILES'           		     );
	
	-- UPDATE for PACKAGES	
	UPDATE CK_INSTALLED_OBJECTS 	
	SET CI_CREATED = 'R'	
	WHERE  CI_TYPE = 'PACKAGE'	
	AND    CI_NAME = 'CI_AUDIT'; 	
	
	-- UPDATE for PACKAGE BODIES	
	UPDATE CK_INSTALLED_OBJECTS 		
	SET CI_CREATED = 'R'		
	WHERE  CI_TYPE = 'PACKAGE BODY'		
	AND    CI_NAME IN 
	( 'CI_AUDIT'				 		
	, 'CIOUE_PROPERTY'			
	, 'JR_VERSION'				
	, 'JR_DEPENDENCY_PURGE'		
	, 'JR_WORKAREA'				
	, 'JR_NAME'					
	, 'JR_UTIL'					
	, 'JR_GEN'					
	, 'JR_META'					
	, 'CIHANS'					
	, 'CIHDBI'					
	, 'CIHKBL'					
	, 'CIHMTI'					
	, 'CIHOOT'					
	, 'CIHORC'					
	, 'CIIUTL'					
	, 'CKOVWUTL'				
	, 'CIOUE_ELEMENT_TYPE'		
	, 'CIOUE_ASSOCIATION_TYPE'
	, 'CDAPI'					
	, 'RMMAC' );                 	
	
	-- UPDATE for TRIGGERS	
	UPDATE CK_INSTALLED_OBJECTS 		
	SET CI_CREATED = 'R'			
	WHERE  CI_TYPE = 'TRIGGER'		
        AND   (CI_NAME in 
                  ( 'SDD_FOLDERS_I'	
                  , 'SDD_WORKAREAS_I'	
                  , 'SDD_WORKAREAS_U'	
                  , 'SDD_CONFIGURATIONS_I'	
                  , 'SDD_CONFIGURATIONS_U'	
                  , 'CK_APPLICATION_LOGIC_D'	
                  , 'RM_TEXT_LINES_D')	
        OR     CI_NAME like 'SDD%D')	
        AND    CI_NAME NOT IN 
                  ( 'SDD_BRANCHES_D'
                  , 'SDD_CFG_AD'
                  , 'SDD_FOLDER_MEMBERS_CHKBIND');	
	
	-- Update for verify of stage
	UPDATE CK_INSTALLED_OBJECTS 
	SET    CI_CREATED = 'Y'
	WHERE  CI_STAGE   = 'CKUPDMR2';
	
	COMMIT;
	
EXCEPTION

  WHEN OTHERS THEN 

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

END;
/
