REM This script flags objects to be reloaded for 4.11 Upgrade
REM UPGRADE SCHEDULE FOR PATCHES IN CIO4012.M96 
REM -------------------------------------------
REM  
REM PATCH           OBJECT                      OBJECT         
REM NUMBER AUTHOR   NAME                        TYPE          ACTION 
REM ------ -------- --------------------------- ------------- -------
REM 004    STLYON   CIHFUN                      PACKAGE BODY  REPLACE
REM 005    STLYON   JR_DEPENDENCY               PACKAGE BODY  REPLACE
REM 006    STLYON   JR_CHECK_REFS               PACKAGE BODY  REPLACE
REM 007    STLYON   JR_FOLDER                   PACKAGE BODY  REPLACE
REM 008    STLYON   JR_COPY                     PACKAGE BODY  REPLACE
REM 011    STLYON   JR_RULE                     PACKAGE BODY  REPLACE  
REM 012    STLYON   CDAPI                       PACKAGE BODY  REPLACE
REM 013    STLYON   SDD_MODULE_VERSION_POLICY   FUNCTION      REPLACE
REM UPDATES THE OBJECTS IN THE FOLLOWING STAGES
REM CKPACK   -- already loaded

DECLARE
  role_name VARCHAR2(50);
  csr       NUMBER;
BEGIN

  -- UPDATE FOR PACKAGE BODIES
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE  CI_TYPE    = 'PACKAGE BODY'
  AND    CI_NAME IN ( 'CIHFUN'
                     , 'JR_DEPENDENCY'
		     , 'JR_CHECK_REFS'
		     , 'JR_FOLDER'
		     , 'JR_COPY'
		     , 'JR_RULE'
		     , 'CDAPI'
		     , 'ODWA_FOL'
		     );  
                
  -- 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_NAME='SDD_MODULE_VERSION_POLICY'
			AND CI_TYPE = 'FUNCTION');
                        
  -- UPDATE FOR FUNCTION
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'R'
  WHERE  CI_TYPE = 'FUNCTION' 
  AND    CI_NAME = 'SDD_MODULE_VERSION_POLICY';

		     
  -- UPDATE FOR TABLES
  -- N/A

  -- UPDATE FOR INDEXES
  -- N/A

  -- UPDATE FOR CONSTRAINTS
  -- N/A

  -- UPDATE FOR GRANTS Bug 1228055
  -- N/A
  role_name := 'CKR_'||user;
  csr:=DBMS_SQL.OPEN_CURSOR;  
  DBMS_SQL.PARSE(csr,'REVOKE INSERT, UPDATE, DELETE ON SDW_USERS FROM '||role_name ,DBMS_SQL.V7);
  DBMS_SQL.CLOSE_CURSOR(csr);
  -- Update for verify of stage
  UPDATE CK_INSTALLED_OBJECTS
  SET    CI_CREATED = 'Y'
  WHERE  CI_STAGE   = 'CKUPD411';

  COMMIT;
  
EXCEPTION

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

END;
/
