REM This script flags objects to be reloaded for 410 Upgrade
REM UPGRADE SCHEDULE FOR PATCHES IN CIO4012.M95 
REM -------------------------------------------
REM  
REM PATCH           OBJECT                      OBJECT         
REM NUMBER AUTHOR   NAME                        TYPE          ACTION 
REM ------ -------- --------------------------- ------------- -------
REM 001    STLYON   
REM 002    STLYON   CIOSEQUENCE_IMPLEMENTATION  PACKAGE BODY  REPLACE
REM 003    STLYON   CIHDFL                      PACKAGE BODY  REPLACE    
REM 004    STLYON   CIOORACLE_DATABASE          PACKAGE BODY  REPLACE
REM 005    JEWILLIA 
REM 006    STLYON   CK_UTIL                     PACKAGE BODY  REPLACE
REM 007    KPARTHAS CDWP_BR                     PACKAGE_BODY  REPLACE
REM 007    KPARTHAS CIMFUN                      PACKAGE_BODY  REPLACE
REM 007    KPARTHAS CIHMTI                      PACKAGE_BODY  REPLACE
REM 007    KPARTHAS CK_FDTREE                   PACKAGE_BODY  REPLACE
REM 007    KPARTHAS (all views)                               REPLACE
REM 007    KPARTHAS (policies)                                NEW
REM 009    STLYON   CIIUTL                      PACKAGE_BODY  REPLACE
REM 013    STLYON   JR_DEPENDENCY_IMP           PACKAGE_BODY  REPLACE
REM 017    KPARTHAS ROB_MSG                     PACKAGE SPEC  REPLACE
REM
REM UPDATES THE OBJECTS IN THE FOLLOWING STAGES
REM CKPACK   -- already loaded

BEGIN

  -- UPDATE FOR PACKAGE SPEC
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE  CI_TYPE    = 'PACKAGE'
  AND    CI_NAME IN ( 'ROB_MSG');

  
  -- UPDATE FOR PACKAGE BODIES
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE  CI_TYPE    = 'PACKAGE BODY'
  AND    CI_NAME IN ( 'CIOSEQUENCE_IMPLEMENTATION'          
                    , 'CIHDFL'                   
                    , 'CIOORACLE_DATABASE'
                    , 'CK_UTIL'
                    , 'CDWP_BR'
                    , 'CIMFUN'
                    , 'CIHMTI'
                    , 'CK_FDTREE'
                    , 'CIIUTL'
                    , 'JR_DEPENDENCY_IMP'
                    , 'JR_WASTEBASKET'
                    , 'ROB_MSG');  
                
  -- UPDATE FOR VIEWS
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE CI_STAGE='JRVIEW' 
  AND CI_NAME IN (SELECT CI_NAME 
                  FROM CK_INSTALLED_OBJECTS 
                  WHERE CI_STAGE = 'JRPOLICY');

  -- UPDATE FOR POLICIES
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE CI_STAGE='JRPOLICY';
  

  -- UPDATE FOR TRIGGERS
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE  CI_TYPE = 'TRIGGER' 
  AND CI_STAGE = 'JRTRIG' 
  AND CI_TABLE_IRID IN (SELECT CI_TABLE_IRID 
                        FROM CK_INSTALLED_OBJECTS 
                        WHERE CI_STAGE='JRPOLICY');
                        
  -- UPDATE FOR FUNCTION
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE  CI_TYPE = 'FUNCTION' 
  AND    CI_NAME LIKE '%POLICY';

		     
  -- 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   = 'CKUPD410';

  COMMIT;
  
EXCEPTION

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

END;
/
