ALTER session set current_schema = "ODMRSYS";

@@version.sql

DEFINE OLD_REPOSITORY_VERSION = '12.2.0.0.0'
DEFINE NEW_REPOSITORY_VERSION = '12.2.0.0.1'

EXECUTE dbms_output.put_line('Start repository upgrade from ' || '&OLD_REPOSITORY_VERSION' || ' to ' || '&NEW_REPOSITORY_VERSION' || '. ' || systimestamp);


DECLARE
  REPOS_VERSION VARCHAR2(30);
  DB_VER VARCHAR2(30);
  v_id NUMBER;
  v_string_size VARCHAR2(30);
BEGIN
  SELECT PROPERTY_STR_VALUE INTO REPOS_VERSION FROM ODMRSYS.ODMR$REPOSITORY_PROPERTIES WHERE PROPERTY_NAME = 'VERSION';
  SELECT VERSION INTO DB_VER FROM PRODUCT_COMPONENT_VERSION WHERE PRODUCT LIKE 'Oracle Database%' OR PRODUCT LIKE 'Personal Oracle Database %';
  IF ( repos_version = '&OLD_REPOSITORY_VERSION' ) THEN
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_PROJECT_WORKFLOW AS SELECT * FROM
      (
        SELECT
            PROJECT_ID,
            PROJECT_NAME,
            PJ_CREATION_TIME,
            PJ_LAST_UPDATED_TIME,
            PJ_COMMENTS,
            WORKFLOW_ID,
            WORKFLOW_NAME,
            WORKFLOW_DATA,
            CHAIN_NAME,
            WF_CREATION_TIME,
            WF_LAST_UPDATED_TIME,
            WF_COMMENTS,
            "STATUS"
        FROM
            ODMRSYS.ODMR_ALL_PROJECT_WORKFLOW
        WHERE
            ODMR_ALL_PROJECT_WORKFLOW.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')
      )';

    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_WORKFLOW_JOBS AS SELECT * FROM
      (
        SELECT
            PROJECT_ID,
            PROJECT_NAME,
            WORKFLOW_NAME,
            WORKFLOW_CHAIN_NAME,
            WORKFLOW_ID,
            WORKFLOW_JOB_NAME,
            JOB_CREATION_TIME,
            WORKFLOW_STATUS
        FROM
            ODMRSYS.ODMR_ALL_WORKFLOW_JOBS
        WHERE
            ODMR_ALL_WORKFLOW_JOBS.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')
      )';

    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_WORKFLOW_LOG AS SELECT 
          LOG_ID,
          JOB_NAME,
          PROJ_NAME,
          PROJ_ID,
          WF_NAME,
          WF_ID,
          NODE_ID,
          NODE_NAME,
          SUBNODE_NAME,
          SUBNODE_ID,
          LOG_TIMESTAMP,
          LOG_DURATION,
          LOG_TYPE,
          LOG_SUBTYPE,
          LOG_TASK,
          LOG_MESSAGE,
          LOG_MESSAGE_DETAILS
      FROM 
          ODMRSYS.ODMR_ALL_WORKFLOW_LOG
      WHERE
          ODMR_ALL_WORKFLOW_LOG.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';

    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_WORKFLOW_RUNNING 
      AS SELECT 
        "PROJECT_ID", 
        "WORKFLOW_ID",
        "WF_JOB_NAME",
        "LOG_DATE",
        "LOG_ID",
        "NODE_ID",
        "SUBNODE_ID",
        "NODE_STATUS",
        "SUBNODE_STATUS",
        "NODE_START_TIME",
        "NODE_RUN_TIME",
        "ERROR_CODE",
        "LOG_MESSAGE"
      FROM 
        ODMRSYS.ODMR_ALL_WORKFLOW_RUNNING
      WHERE
        ODMR_ALL_WORKFLOW_RUNNING.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';

    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_WORKFLOW_COMPLETE 
      AS SELECT
        "PROJECT_ID", 
        "WORKFLOW_ID",
        "WF_JOB_NAME",
        "LOG_DATE",
        "LOG_ID",
        "NODE_ID",
        "SUBNODE_ID",
        "NODE_STATUS",
        "SUBNODE_STATUS",
        "NODE_START_TIME",
        "NODE_RUN_TIME",
        "ERROR_CODE",
        "LOG_MESSAGE"
      FROM
        ODMRSYS.ODMR_ALL_WORKFLOW_COMPLETE
      WHERE
        ODMR_ALL_WORKFLOW_COMPLETE.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
    
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_WORKFLOW_ALL 
      AS SELECT
        "PROJECT_ID",
        "WORKFLOW_ID",
        "WF_JOB_NAME",
        "LOG_DATE",
        "LOG_ID",
        "NODE_ID",
        "SUBNODE_ID",
        "NODE_STATUS",
        "SUBNODE_STATUS",
        "NODE_START_TIME",
        "NODE_RUN_TIME",
        "ERROR_CODE",
        "LOG_MESSAGE"
      FROM
        ODMRSYS.ODMR_ALL_WORKFLOW_ALL
      WHERE
        ODMR_ALL_WORKFLOW_ALL.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';

    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_WORKFLOW_ALL_POLL
      AS SELECT
        USER_NAME, 
        WF_JOB_NAME, 
        PROJ_ID, 
        WF_ID, 
        NODE_ID, 
        SUBNODE_ID, 
        NODE_START_TIME, 
        NODE_RUN_TIME,
        NODE_STATUS, 
        SUBNODE_STATUS
      FROM
        ODMRSYS.ODMR_ALL_WORKFLOW_ALL_POLL
      WHERE
        ODMR_ALL_WORKFLOW_ALL_POLL.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'') OR ODMR_ALL_WORKFLOW_ALL_POLL.USER_NAME IS NULL'; 

    IF (DB_VER >= '11.2.0.4' ) THEN
      EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_WORKFLOW_NODES
        AS SELECT 
          PROJECT_ID, 
          PROJECT_NAME, 
          WORKFLOW_NAME, 
          WORKFLOW_ID, 
          NODE_TYPE, 
          NODE_ID, 
          NODE_NAME, 
          NODE_STATUS
        FROM 
          ODMRSYS.ODMR_ALL_WORKFLOW_NODES
        WHERE
          ODMR_ALL_WORKFLOW_NODES.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
  
      EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_WF_CLAS_TEST_RESULTS AS SELECT
          PROJECT_ID, 
          PROJECT_NAME, 
          WORKFLOW_ID, 
          WORKFLOW_NAME,
          NODE_TYPE, 
          NODE_ID, 
          NODE_NAME, 
          NODE_STATUS,
          MODEL_ID, 
          MODEL_NAME, 
          MODEL_STATUS, 
          RESULT_CREATIONDATE, 
          TEST_METRICS, 
          CONFUSION_MATRIX,
          LIFTS, 
          ROCS, 
          ROC_AREA
        FROM
          ODMRSYS.ODMR_ALL_WF_CLAS_TEST_RESULTS
        WHERE 
          ODMR_ALL_WF_CLAS_TEST_RESULTS.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
  
      EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_WF_REGR_TEST_RESULTS
        AS SELECT 
          USER_NAME, 
          PROJECT_ID, 
          PROJECT_NAME, 
          WORKFLOW_ID, 
          WORKFLOW_NAME,
          NODE_TYPE, 
          NODE_ID, 
          NODE_NAME, 
          NODE_STATUS,
          MODEL_ID, 
          MODEL_NAME, 
          MODEL_STATUS, 
          RESULT_CREATIONDATE, 
          TEST_METRICS, 
          RESIDUAL_PLOT
        FROM 
          ODMRSYS.ODMR_ALL_WF_REGR_TEST_RESULTS
        WHERE 
          ODMR_ALL_WF_REGR_TEST_RESULTS.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
  
      EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_WF_TEST_RESULTS
        AS SELECT 
          PROJECT_ID, PROJECT_NAME, WORKFLOW_ID, WORKFLOW_NAME,
          NODE_TYPE, NODE_ID, NODE_NAME, NODE_STATUS,
          MODEL_ID, MODEL_NAME, MODEL_STATUS, RESULT_CREATIONDATE, 
          TEST_METRICS, CONFUSION_MATRIX,
          LIFTS, ROCS, ROC_AREA, NULL "RESIDUAL_PLOT"
        FROM
          ODMRSYS.ODMR_ALL_WF_CLAS_TEST_RESULTS
        WHERE
          ODMR_ALL_WF_CLAS_TEST_RESULTS.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')
        UNION ALL
        SELECT 
          PROJECT_ID, PROJECT_NAME, WORKFLOW_ID, WORKFLOW_NAME,
          NODE_TYPE, NODE_ID, NODE_NAME, NODE_STATUS,
          MODEL_ID, MODEL_NAME, MODEL_STATUS, RESULT_CREATIONDATE,
          TEST_METRICS, NULL "CONFUSION_MATRIX",
          NULL "LIFTS", NULL "ROCS", NULL "ROC_AREA", RESIDUAL_PLOT
        FROM
          ODMRSYS.ODMR_ALL_WF_REGR_TEST_RESULTS
        WHERE
          ODMR_ALL_WF_REGR_TEST_RESULTS.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
  
      EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_WORKFLOW_MODELS
        AS SELECT 
          PROJECT_ID, 
          PROJECT_NAME, 
          WORKFLOW_ID, 
          WORKFLOW_NAME,
          NODE_TYPE, 
          NODE_ID, 
          NODE_NAME, 
          NODE_STATUS,
          MODEL_TYPE, 
          MODEL_ID, 
          MODEL_NAME, 
          MODEL_STATUS, 
          MODEL_CREATIONDATE
        FROM
          ODMRSYS.ODMR_ALL_WORKFLOW_MODELS
        WHERE
          ODMR_ALL_WORKFLOW_MODELS.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
    END IF;
    
    EXECUTE IMMEDIATE 'ALTER TABLE ODMRSYS.ODMR$WORKFLOWS ADD ( 
                         JOB_SUBMITTED_TIME TIMESTAMP(6),
                         JOB_COMPLETED_TIME TIMESTAMP(6),
                         LAST_JOB_ID VARCHAR2(128),
                         JOB_STEPS_TOTAL NUMBER,
                         JOB_STEPS_COMPLETED NUMBER
                       )'; 

    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_ALL_RUNNING_WORKFLOW AS SELECT * FROM
      (
       WITH a as
       (
         SELECT a.USER_NAME, a.JOB_NAME, a.PROJ_ID, a.WF_ID, a.NODE_ID,
         CASE
           WHEN a.LOG_SUBTYPE = ''START'' THEN ''10000001''
           WHEN a.LOG_SUBTYPE = ''END'' THEN ''01000001''
         END "ENCODE_STATUS"
         FROM ODMRSYS.ODMR$WF_LOG a
         WHERE a.LOG_SUBTYPE IN (''START'',''END'') AND a.LOG_TASK IN (''NODE'')
       ),
       b AS (
         SELECT a.USER_NAME, a.JOB_NAME, a.PROJ_ID, a.WF_ID, a.NODE_ID,
         sys.mvaggrawbitor(ENCODE_STATUS) "STATUS"
         FROM a
         group by USER_NAME, JOB_NAME, PROJ_ID, WF_ID, NODE_ID
       ),
       c AS (
         SELECT
           USER_NAME, JOB_NAME, WF_ID, listagg (NODE_ID, '' '')
         WITHIN GROUP
           (ORDER BY NODE_ID) RUNNING_NODES
         FROM
           b
         WHERE
           utl_raw.bit_or(STATUS, ''10000001'') = ''10000001'' -- make sure no END tag (i.e. node has not completed yet)
         GROUP BY
           USER_NAME, JOB_NAME, PROJ_ID, WF_ID
       ),
       d AS (
         SELECT
           USER_NAME, JOB_NAME, WF_ID, listagg (NODE_ID, '' '')
         WITHIN GROUP
           (ORDER BY NODE_ID) COMPLETED_NODES
         FROM
           b
         WHERE
           utl_raw.bit_and(STATUS, ''11000001'') = ''11000001'' -- make sure matching pair of BEGIN and END tags (i.e. node has completed)
         GROUP BY
           USER_NAME, JOB_NAME, PROJ_ID, WF_ID
       )
       SELECT DISTINCT b.USER_NAME, b.JOB_NAME, b.WF_ID, c.RUNNING_NODES, d.COMPLETED_NODES
       FROM b, c, d
       WHERE b.JOB_NAME = c.JOB_NAME (+) AND b.JOB_NAME = d.JOB_NAME (+)
      )';

    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_RUNNING_WORKFLOW AS SELECT * FROM
      (
        SELECT
          JOB_NAME, WF_ID, RUNNING_NODES, COMPLETED_NODES
        FROM
          ODMRSYS.ODMR_ALL_RUNNING_WORKFLOW
        WHERE
          ODMR_ALL_RUNNING_WORKFLOW.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')
      )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_USER_RUNNING_WORKFLOW FOR ODMRSYS.ODMR_USER_RUNNING_WORKFLOW';

    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_ALL_PROJECT_WORKFLOW2 AS SELECT * FROM
      (
        SELECT
            ODMR$PROJECTS.USER_NAME,
            ODMR$PROJECTS.PROJECT_ID,
            ODMR$PROJECTS.PROJECT_NAME,
            ODMR$PROJECTS.CREATION_TIME PJ_CREATION_TIME,
            ODMR$PROJECTS.LAST_UPDATED_TIME PJ_LAST_UPDATED_TIME,
            ODMR$PROJECTS.COMMENTS PJ_COMMENTS,
            ODMR$WORKFLOWS.WORKFLOW_ID,
            ODMR$WORKFLOWS.WORKFLOW_NAME,
            ODMR$WORKFLOWS.WORKFLOW_DATA,
            ODMR$WORKFLOWS.CHAIN_NAME,
            ODMR$WORKFLOWS.CREATION_TIME WF_CREATION_TIME,
            ODMR$WORKFLOWS.LAST_UPDATED_TIME WF_LAST_UPDATED_TIME,
            ODMR$WORKFLOWS.COMMENTS WF_COMMENTS,
            ODMR$WORKFLOWS.STATUS,
            ODMR$WORKFLOWS.JOB_SUBMITTED_TIME,
            ODMR$WORKFLOWS.JOB_COMPLETED_TIME,
            ODMR$WORKFLOWS.LAST_JOB_ID,
            ODMR$WORKFLOWS.JOB_STEPS_TOTAL,
            ODMR$WORKFLOWS.JOB_STEPS_COMPLETED
        FROM
            ODMRSYS.ODMR$PROJECTS,
            ODMRSYS.ODMR$WORKFLOWS
        WHERE
            ODMR$PROJECTS.PROJECT_ID = ODMR$WORKFLOWS.PROJECT_ID (+)
      )';

    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_PROJECT_WORKFLOW2 AS SELECT * FROM
      (
        SELECT
            PROJECT_ID,
            PROJECT_NAME,
            PJ_CREATION_TIME,
            PJ_LAST_UPDATED_TIME,
            PJ_COMMENTS,
            WORKFLOW_ID,
            WORKFLOW_NAME,
            WORKFLOW_DATA,
            CHAIN_NAME,
            WF_CREATION_TIME,
            WF_LAST_UPDATED_TIME,
            WF_COMMENTS,
            STATUS,
            JOB_SUBMITTED_TIME,
            JOB_COMPLETED_TIME,
            LAST_JOB_ID,
            JOB_STEPS_TOTAL,
            JOB_STEPS_COMPLETED
        FROM
            ODMRSYS.ODMR_ALL_PROJECT_WORKFLOW2
        WHERE
            ODMR_ALL_PROJECT_WORKFLOW2.USER_NAME = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')
      )';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ODMR_USER_PROJECT_WORKFLOW2 FOR ODMRSYS.ODMR_USER_PROJECT_WORKFLOW2';
      
    EXECUTE IMMEDIATE 'INSERT INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES (''POLLING_IDLE_ENABLED'', ''TRUE'', ''If FALSE, client will not perform automatic polling to update workflow job status unless a workflow loaded in editor is running'')';
    COMMIT;

    IF (DB_VER >= '12.1.0.2' ) THEN
      -- revoke SELECT rights
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_DEFAULT_STOPLISTS FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_PROJECT_WORKFLOW FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_WORKFLOW_JOBS FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_WORKFLOW_LOG FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_REPOSITORY_PROPERTIES FROM PUBLIC';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_WORKFLOW_RUNNING FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_WORKFLOW_COMPLETE FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_WORKFLOW_ALL FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_WORKFLOW_ALL_POLL FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_MESSAGES FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_WORKFLOW_NODES FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_WORKFLOW_MODELS FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_WF_CLAS_TEST_RESULTS FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_WF_REGR_TEST_RESULTS FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      BEGIN
        EXECUTE IMMEDIATE 'REVOKE SELECT ON ODMR_USER_WF_TEST_RESULTS FROM ODMRUSER';
        EXCEPTION WHEN OTHERS THEN NULL;
      END;

      -- grant READ rights instead (security requirement)
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_DEFAULT_STOPLISTS TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_PROJECT_WORKFLOW TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_JOBS TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_LOG TO ODMRUSER';
      -- Only grant to PUBLIC, allows ODMR to check for installation of repository
      -- from an account that does not have ODMRUSER role.
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_REPOSITORY_PROPERTIES TO PUBLIC';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_RUNNING TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_COMPLETE TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_ALL TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_ALL_POLL TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_MESSAGES TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_NODES TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_MODELS TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WF_CLAS_TEST_RESULTS TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WF_REGR_TEST_RESULTS TO ODMRUSER';    
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WF_TEST_RESULTS TO ODMRUSER';

      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_RUNNING_WORKFLOW TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_PROJECT_WORKFLOW2 TO ODMRUSER';      
    ELSE
      -- grant SELECT rights
      EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_RUNNING_WORKFLOW TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_PROJECT_WORKFLOW2 TO ODMRUSER';
    END IF;

    -- uptick the VERSION
    UPDATE ODMRSYS.ODMR$REPOSITORY_PROPERTIES SET PROPERTY_STR_VALUE = '&NEW_REPOSITORY_VERSION' WHERE PROPERTY_NAME = 'VERSION';
    COMMIT;  
    dbms_output.put_line('Repository version updated to ' || '&NEW_REPOSITORY_VERSION' || '.');
  ELSE
    dbms_output.put_line('Upgrade is not necessary.');
  END IF;
  EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line('Failure during upgrade: '||DBMS_UTILITY.FORMAT_ERROR_STACK());
   RAISE;
END;
/


EXECUTE dbms_output.put_line('End repository upgrade from ' || '&OLD_REPOSITORY_VERSION' || ' to ' || '&NEW_REPOSITORY_VERSION' || '. ' || systimestamp);
