REM This script flags objects to be reloaded for 4_9 Upgrade
REM UPGRADE SCHEDULE FOR PATCHES IN CIO4012.M94 
REM -------------------------------------------
REM  
REM PATCH           OBJECT                   OBJECT         
REM NUMBER AUTHOR   NAME                     TYPE          ACTION 
REM ------ -------- ------------------------ ------------- -------
REM 001    TSCOTT   JR_WASTEBASKET           PACKAGE BODY  REPLACE 
REM 002    STLYON   RMMAN                    PACKAGE BODY  REPLACE 
REM                 CIHOKY                   PACKAGE BODY  REPLACE
REM 003    STLYON   CIIUTL                   PACKAGE BODY  REPLACE 
REM 004    STLYON   CIHCOL                   PACKAGE BODY  REPLACE 
REM 005    STLYON   CIHRELEND                PACKAGE BODY  REPLACE 
REM 007    STLYON   CIHFUN                   PACKAGE BODY  REPLACE 
REM 008    KPARTHAS CDWP                     PACKAGE BODY  REPLACE
REM                 ODWA_STC                 PACKAGE       DROP
REM                 ODWA_WSF                 PACKAGE       DROP
REM 009    STLYON   SDD_TAB                  VIEW          REPLACE
REM                 CI_TABLE_DEFINITIONS     VIEW          REPLACE
REM                 CI_RELATION_DEFINITIONS  VIEW          REPLACE
REM                 SDD_TAB_D                TRIGGER       REPLACE
REM                 SDD_TAB_VER              FUNCTION      REPLACE
REM                 CIOTABLE_DEFINITION      PACKAGE SPEC  REPLACE 
REM 		    CIOTABLE_DEFINITION      PACKAGE BODY  REPLACE 
REM	   	    CIORELATION_DEFINITION   PACKAGE BODY  REPLACE 
REM 		    CIHFCO                   PACKAGE BODY  REPLACE 
REM 		    CIHTBL                   PACKAGE BODY  REPLACE 
REM 010   CHEAPS    CIHAPPXXX                PACKAGE BODY  REPLACE 
REM 012   KPARTHAS  CIHTBL                   PACKAGE BODY  REPLACE 
REM 013   STLYON    CIOUNIQUE_KEY_CONSTRAINT PACKAGE BODY  REPLACE 
REM 015   KPARTHAS  RMMTXI                   PACKAGE BODY  REPLACE 
REM 017   TSCOTT    CIHFUN                   PACKAGE BODY  REPLACE 
REM 020   RJCLARK   JR_RM_INS                PACKAGE BODY  REPLACE 
REM 021   KPARTHAS  CDWP_FIL                 PACKAGE BODY  REPLACE 
REM                 ODWA_FOL                 PACKAGE BODY  REPLACE 
REM                 ODWACHCK                 PACKAGE BODY  REPLACE 
REM                 ODWAPRIV                 PACKAGE BODY  REPLACE 
REM                 ODWAROBS                 PACKAGE BODY  REPLACE 
REM                 ODWASRCH                 PACKAGE BODY  REPLACE 
REM                 ODWAUPLD                 PACKAGE BODY  REPLACE 
REM 022   KPARTHAS  CIEVAL                   PACKAGE BODY  REPLACE 
REM 023   STLYON    CK_REP_UTIL              PACKAGE BODY  REPLACE 
REM
REM UPDATES THE OBJECTS IN THE FOLLOWING STAGES
REM CKPACCOM -- already loaded
REM CKPACK   -- already loaded
REM CKROBPAC -- already loaded
REM JRPACK   -- already loaded
REM CKVIEW   -- already loaded
REM JRVIEW   -- already loaded
REM JRFUN    -- new for 4_9 MR11

DECLARE
   
     procedure add ( l_table IN varchar2
                   , l_add   IN varchar2
                   , l_default IN varchar2 default NULL ) is
        csr               NUMBER;
        col_exist         EXCEPTION;
        tab_exist         EXCEPTION;
        PRAGMA EXCEPTION_INIT(col_exist, -01430);
        PRAGMA EXCEPTION_INIT(tab_exist, -00942);  
     begin
        csr := DBMS_SQL.OPEN_CURSOR;
        dbms_output.put_line('Adding    '||l_table||'.'||l_add);
        DBMS_SQL.PARSE(csr,'alter table '||l_table||' add ( '||l_add||' )',DBMS_SQL.V7);
        DBMS_SQL.CLOSE_CURSOR(csr);
     exception
        when col_exist then
           NULL;
           DBMS_SQL.CLOSE_CURSOR(csr);
        when tab_exist then
           NULL;
           DBMS_SQL.CLOSE_CURSOR(csr);
        when others then
           DBMS_SQL.CLOSE_CURSOR(csr);
           DBMS_OUTPUT.PUT_LINE ('...ERROR: '||sqlerrm);
     end;
  
     procedure modify ( l_table IN varchar2
                      , l_mod   IN varchar2 ) is
        csr                   NUMBER;
        is_already_not_null   EXCEPTION;
        tab_exist             EXCEPTION;
        PRAGMA EXCEPTION_INIT(is_already_not_null, -01442);
        PRAGMA EXCEPTION_INIT(tab_exist, -00942);
     begin
        csr := DBMS_SQL.OPEN_CURSOR;
        dbms_output.put_line('Modifying '||l_table||'.'||l_mod);
        DBMS_SQL.PARSE(csr,'alter table '||l_table||' modify ( '||l_mod||' )',DBMS_SQL.V7);
        DBMS_SQL.CLOSE_CURSOR(csr);
     exception
        when is_already_not_null then
           NULL;
           DBMS_SQL.CLOSE_CURSOR(csr);
        when tab_exist then
           NULL;
           DBMS_SQL.CLOSE_CURSOR(csr);
        when others then
           DBMS_SQL.CLOSE_CURSOR(csr);
           DBMS_OUTPUT.PUT_LINE ('...ERROR: '||sqlerrm);
     end;
  
     procedure set_default ( l_table IN varchar2
                      , l_col   IN varchar2 
                      , l_default IN varchar2) is
        csr                   NUMBER;
        is_already_not_null   EXCEPTION;
        tab_exist             EXCEPTION;
  --      PRAGMA EXCEPTION_INIT(is_already_not_null, -01442);
  --      PRAGMA EXCEPTION_INIT(tab_exist, -00942);
     begin
        csr := DBMS_SQL.OPEN_CURSOR;
        dbms_output.put_line('updating '||l_table||'.'||l_col);
        DBMS_SQL.PARSE(csr,'update '||l_table||' set '||l_col||' = '''||l_default||'''',DBMS_SQL.V7);
        DBMS_SQL.CLOSE_CURSOR(csr);
     end;

BEGIN

  -- UPDATE FOR PACKAGE SPEC
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE  CI_TYPE    = 'PACKAGE'
  AND    CI_NAME    = 'CIOTABLE_DEFINITION';     

  -- UPDATE FOR PACKAGE BODIES
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE  CI_TYPE    = 'PACKAGE BODY'
  AND    CI_NAME IN ( 'JR_WASTEBASKET'          
                    , 'RMMAN'                   
                    , 'CIHOKY'                  
                    , 'CIIUTL'                  
                    , 'CIHCOL'                  
                    , 'CIHRELEND'               
                    , 'CIHFUN'                  
                    , 'CIOTABLE_DEFINITION'     
                    , 'CIOTABLE_DEFINITION'     
                    , 'CIORELATION_DEFINITION'  
                    , 'CIHFCO'                  
                    , 'CIHTBL'                  
                    , 'CIHAPPXXX'               
                    , 'CIHTBL'                  
                    , 'CIOUNIQUE_KEY_CONSTRAINT'
                    , 'RMMTXI'                  
                    , 'CIHFUN'                  
                    , 'CDWP'                    
                    , 'JR_RM_INS'               
                    , 'CDWP_FIL'                
                    , 'ODWA_FOL'                
                    , 'ODWACHCK'                
                    , 'ODWAPRIV'                
                    , 'ODWAROBS'                
                    , 'ODWASRCH'                
                    , 'ODWAUPLD'                
                    , 'CIEVAL'                  
                    , 'CK_REP_UTIL' );  
                
  -- UPDATE FOR VIEWS
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE  CI_TYPE    = 'VIEW'
  AND    CI_NAME IN ( 'SDD_TAB'                                   
                    , 'CI_TABLE_DEFINITIONS'                   
		    ,  'CI_RELATION_DEFINITIONS' );
		 

  -- UPDATE FOR TRIGGERS
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE  CI_TYPE    = 'TRIGGER'
  AND    CI_NAME IN ( 'SDD_TAB_D' );	
  
  -- UPDATE FOR FUNCTION
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE  CI_TYPE    = 'FUNCTION'
  AND    CI_NAME IN ( 'SDD_TAB_VER' );		       
		      
		     
  -- UPDATE FOR TABLES
  -- N/A

  -- UPDATE FOR INDEXES
  -- N/A

  -- UPDATE FOR CONSTRAINTS
  -- N/A

  -- Update for verify of stage
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'Y'
  WHERE  CI_STAGE   = 'CKUPD4_9';

  COMMIT;
  
  -- drop obsolete ROB packages
  
  begin 
    execute immediate ('drop package ODWA_STC');
  exception
    when others then null; -- may have already been dropped
  end;
  
  begin
    execute immediate ('drop package ODWA_WSF');
  exception
    when others then null; -- may have already been dropped
  end;
  
  -- M94.009
  -- Add new property I$SDD_TAB,DATA_PERSISTENCE VARCHAR2(11) NOT NULL DEFAULT 'PERMANENT'
  
  -- disable all triggers on I$SDD_TAB
  execute immediate ('alter table i$sdd_tab disable all triggers');
  
  -- Add the column
  add('I$SDD_TAB', 'DATA_PERSISTENCE VARCHAR2(11) DEFAULT ''PERMANENT'''); 

  -- update column with default value
  set_default('I$SDD_TAB', 'DATA_PERSISTENCE', 'PERMANENT');

  -- Make the column NOT NULL
  modify('I$SDD_TAB', 'DATA_PERSISTENCE VARCHAR2(11) DEFAULT ''PERMANENT'' NOT NULL' );
  
  --Enable all triggers
  execute immediate ('alter table i$sdd_tab enable all triggers');  
  
  
EXCEPTION

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

END;
/
