-- Usage @copyTablesFromBackupAccount.sql
-- Restore the following ODMRSYS tables from <backup account>
--   ODMR$PROJECTS  (project data)
--   ODMR$WORKFLOWS (workflow data)
--   ODMR$REPOSITORY_PROPERTIES (repository properties, may contain user entered data)
--   ODMR$WF_LOG (workflow event log history)
--   ODMR$USER_GRANTS_BACKUP (backup of all users account names that were granted access to ODMRSYS. This was generated by the createusersgrantbackup.sql script.
-- Example: @copyTablesFromBackupAccount.sql <backup account>

WHENEVER SQLERROR EXIT SQL.SQLCODE;

EXECUTE dbms_output.put_line('Start restore ODMRSYS tables from backup account. ' || systimestamp);

DEFINE TEMP_ACCT = &&1

DELETE ODMRSYS.ODMR$WORKFLOW_JOBS;
DELETE ODMRSYS.ODMR$WORKFLOWS;
DELETE ODMRSYS.ODMR$WF_LOG;
DELETE ODMRSYS.ODMR$PROJECTS;
DELETE ODMRSYS.ODMR$REPOSITORY_PROPERTIES;

INSERT INTO ODMRSYS.ODMR$PROJECTS
SELECT 
  USER_NAME,
  PROJECT_ID,
  PROJECT_NAME,
  CREATION_TIME,
  LAST_UPDATED_TIME,
  COMMENTS
  FROM "&TEMP_ACCT"."ODMR$PROJECTS_BK";

BEGIN
   DECLARE
      PROJ_KEY_MAX       NUMBER := 0;
      PROJ_KEY_CURRVAL   NUMBER := 0;
   BEGIN
    SELECT MAX (PROJECT_ID) INTO PROJ_KEY_MAX FROM ODMRSYS.ODMR$PROJECTS;
    IF (PROJ_KEY_MAX IS NOT NULL) THEN
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ODMRSYS.ODMR$PROJECT_ID_SEQ INCREMENT BY ' || PROJ_KEY_MAX;
      SELECT ODMRSYS.ODMR$PROJECT_ID_SEQ.NEXTVAL INTO PROJ_KEY_CURRVAL FROM DUAL;
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ODMRSYS.ODMR$PROJECT_ID_SEQ INCREMENT BY 1';
    END IF;
  END;
END;
/

INSERT INTO ODMRSYS.ODMR$WORKFLOWS
SELECT
  WORKFLOW_ID,
  WORKFLOW_NAME,
  WORKFLOW_DATA,
  CHAIN_NAME,
  RUN_MODE,
  STATUS,
  CREATION_TIME,
  LAST_UPDATED_TIME,
  COMMENTS,
  PROJECT_ID
  FROM "&TEMP_ACCT"."ODMR$WORKFLOWS_BK";

BEGIN
   DECLARE
      WF_KEY_MAX       NUMBER := 0;
      WF_KEY_CURRVAL   NUMBER := 0;
   BEGIN
    SELECT MAX (WORKFLOW_ID) INTO WF_KEY_MAX FROM ODMRSYS.ODMR$WORKFLOWS;
    IF (WF_KEY_MAX IS NOT NULL) THEN
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ODMRSYS.ODMR$WORKFLOW_ID_SEQ INCREMENT BY ' || WF_KEY_MAX;
      SELECT ODMRSYS.ODMR$WORKFLOW_ID_SEQ.NEXTVAL INTO WF_KEY_CURRVAL FROM DUAL;
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ODMRSYS.ODMR$WORKFLOW_ID_SEQ INCREMENT BY 1';
    END IF;
  END;
END;
/

INSERT INTO ODMRSYS.ODMR$WORKFLOW_JOBS
SELECT
  WORKFLOW_JOB_ID,
  CREATION_TIME,
  WORKFLOW_ID
  FROM "&TEMP_ACCT"."ODMR$WORKFLOW_JOBS_BK";

INSERT INTO ODMRSYS.ODMR$WF_LOG
SELECT
  LOG_ID,
  USER_NAME,
  JOB_NAME,
  PROJ_NAME,
  PROJ_ID,
  WF_NAME,
  WF_ID,
  NODE_NAME,
  NODE_ID,
  SUBNODE_NAME,
  SUBNODE_ID,
  LOG_TIMESTAMP,
  LOG_DURATION,
  LOG_TYPE,
  LOG_SUBTYPE,
  LOG_TASK,
  LOG_MESSAGE,
  LOG_MESSAGE_DETAILS
  FROM "&TEMP_ACCT"."ODMR$WF_LOG_BK";

BEGIN
   DECLARE
      LOG_KEY_MAX       NUMBER := 0;
      LOG_KEY_CURRVAL   NUMBER := 0;
   BEGIN
    SELECT MAX (LOG_ID) INTO LOG_KEY_MAX FROM ODMRSYS.ODMR$WF_LOG;
    IF (LOG_KEY_MAX IS NOT NULL) THEN
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ODMRSYS.ODMR$LOG_ID_SEQ INCREMENT BY ' || LOG_KEY_MAX;
      SELECT ODMRSYS.ODMR$LOG_ID_SEQ.NEXTVAL INTO LOG_KEY_CURRVAL FROM DUAL;
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ODMRSYS.ODMR$LOG_ID_SEQ INCREMENT BY 1';
    END IF;
  END;
END;
/

INSERT INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES
SELECT
  PROPERTY_NAME,
  PROPERTY_NUM_VALUE,
  PROPERTY_STR_VALUE,
  "COMMENT"
  FROM "&TEMP_ACCT"."ODMR$REPOSITORY_PROPERTIES_BK";

CREATE TABLE ODMRSYS.ODMR$USER_GRANTS_BACKUP
AS SELECT * FROM "&TEMP_ACCT"."ODMR$USER_GRANTS_BACKUP_BK";

COMMIT;

EXECUTE dbms_output.put_line('Finished restore ODMRSYS tables from backup account. ' || systimestamp);
