--creates tables/views/sequences/types
--initializes repository table entries
WHENEVER SQLERROR EXIT SQL.SQLCODE;

@@version.sql

EXECUTE dbms_output.put_line('Start creation of db objects (tables,views,types) in ODMRSYS ' || systimestamp);

CREATE TABLE ODMRSYS.ODMR$DEFAULT_STOPLISTS 
(
  WORD NVARCHAR2(2000) NOT NULL 
, LANGUAGE VARCHAR2(30) NOT NULL 
);

CREATE TABLE ODMRSYS.ODMR$DEBUG_LOG 
(
  WORKFLOW_JOB_ID VARCHAR2(128) 
, PROJECT_NAME VARCHAR2(128) 
, PROJECT_ID NUMBER 
, WORKFLOW_NAME VARCHAR2(128) 
, WORKFLOW_ID NUMBER 
, NODE_ID VARCHAR2(30) 
, NODE_NAME VARCHAR2(128) 
, SUB_NODE_ID VARCHAR2(30) 
, SUB_NODE_NAME VARCHAR2(128) 
, OUTPUT_MSG VARCHAR2(4000) 
, WORKFLOW_DATA CLOB 
, OUTPUT_CLOB CLOB 
, SESSION_USER VARCHAR2(128) 
, SESSION_ID NUMBER 
, LOG_TIMESTAMP TIMESTAMP(6)
) 
LOGGING 
PCTFREE 10 
PCTUSED 40 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  FREELISTS 1 
  FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT 
) 
LOB (WORKFLOW_DATA) STORE AS BASICFILE
( 
  ENABLE STORAGE IN ROW 
  CHUNK 8192 
  RETENTION 
  NOCACHE 
  LOGGING 
  STORAGE 
  ( 
    INITIAL 65536 
    NEXT 1048576 
    MINEXTENTS 1 
    MAXEXTENTS 2147483645 
    FREELISTS 1 
    FREELIST GROUPS 1 
    BUFFER_POOL DEFAULT 
  )  
) 	
LOB (OUTPUT_CLOB) STORE AS BASICFILE
( 
  ENABLE STORAGE IN ROW 
  CHUNK 8192 
  RETENTION 
  NOCACHE 
  LOGGING 
  STORAGE 
  ( 
    INITIAL 65536 
    NEXT 1048576 
    MINEXTENTS 1 
    MAXEXTENTS 2147483645 
    FREELISTS 1 
    FREELIST GROUPS 1 
    BUFFER_POOL DEFAULT 
  )  
);

CREATE TABLE ODMRSYS.ODMR$WORKFLOW_JOBS 
(
  WORKFLOW_JOB_ID VARCHAR2(128) NOT NULL 
, CREATION_TIME TIMESTAMP(6) NOT NULL 
, WORKFLOW_ID NUMBER NOT NULL 
, CONSTRAINT ODMR$WORKFLOW_JOBS_PK PRIMARY KEY 
  (
    WORKFLOW_JOB_ID 
  )
  ENABLE 
) 
LOGGING 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE OR REPLACE TYPE ODMRSYS.ODMR_OBJECT_NAMES 
is TABLE OF VARCHAR2(130)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_OBJECT_IDS 
is TABLE OF NUMBER
/

DECLARE
  db_ver  VARCHAR2(30);
BEGIN
  SELECT VERSION INTO db_ver FROM product_component_version WHERE product LIKE 'Oracle Database%' OR product like 'Personal Oracle Database %';
  IF (db_ver >= '11.2.0.4') THEN
    EXECUTE IMMEDIATE '
      CREATE TABLE ODMRSYS.ODMR$WORKFLOWS 
      (
      PROJECT_ID NUMBER NOT NULL 
      , WORKFLOW_ID NUMBER NOT NULL 
      , WORKFLOW_NAME VARCHAR2(128) NOT NULL 
      , WORKFLOW_DATA SYS.XMLTYPE 
      , CHAIN_NAME VARCHAR2(128) 
      , RUN_MODE VARCHAR2(30) 
      , RUN_NODES ODMRSYS.ODMR_OBJECT_NAMES 
      , STATUS VARCHAR2(30) NOT NULL
      , CREATION_TIME TIMESTAMP(6) NOT NULL 
      , LAST_UPDATED_TIME TIMESTAMP(6) 
      , JOB_SUBMITTED_TIME TIMESTAMP(6)
      , JOB_COMPLETED_TIME TIMESTAMP(6)
      , LAST_JOB_ID VARCHAR2(128)
      , JOB_STEPS_TOTAL NUMBER
      , JOB_STEPS_COMPLETED NUMBER
      , COMMENTS VARCHAR2(4000) 
      , CONSTRAINT ODMR$WORKFLOWS_PK PRIMARY KEY 
        (
          WORKFLOW_ID 
        )
        ENABLE 
      ) 
      LOGGING 
      PCTFREE 10 
      INITRANS 1 
      STORAGE 
      ( 
        INITIAL 65536 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        BUFFER_POOL DEFAULT 
      )
      NESTED TABLE RUN_NODES STORE AS ODMR_RUN_NODES_TAB
      XMLTYPE COLUMN WORKFLOW_DATA STORE AS SECUREFILE BINARY XML';
  ELSE
    EXECUTE IMMEDIATE '
      CREATE TABLE ODMRSYS.ODMR$WORKFLOWS 
      (
      PROJECT_ID NUMBER NOT NULL 
      , WORKFLOW_ID NUMBER NOT NULL 
      , WORKFLOW_NAME VARCHAR2(128) NOT NULL 
      , WORKFLOW_DATA SYS.XMLTYPE 
      , CHAIN_NAME VARCHAR2(128) 
      , RUN_MODE VARCHAR2(30) 
      , RUN_NODES ODMRSYS.ODMR_OBJECT_NAMES 
      , STATUS VARCHAR2(30) NOT NULL
      , CREATION_TIME TIMESTAMP(6) NOT NULL 
      , LAST_UPDATED_TIME TIMESTAMP(6) 
      , JOB_SUBMITTED_TIME TIMESTAMP(6)
      , JOB_COMPLETED_TIME TIMESTAMP(6)
      , LAST_JOB_ID VARCHAR2(128)
      , JOB_STEPS_TOTAL NUMBER
      , JOB_STEPS_COMPLETED NUMBER
      , COMMENTS VARCHAR2(4000) 
      , CONSTRAINT ODMR$WORKFLOWS_PK PRIMARY KEY 
        (
          WORKFLOW_ID 
        )
        ENABLE 
      ) 
      LOGGING 
      PCTFREE 10 
      INITRANS 1 
      STORAGE 
      ( 
        INITIAL 65536 
        MINEXTENTS 1 
        MAXEXTENTS 2147483645 
        BUFFER_POOL DEFAULT 
      )
      NESTED TABLE RUN_NODES STORE AS ODMR_RUN_NODES_TAB
      XMLTYPE COLUMN WORKFLOW_DATA store AS OBJECT RELATIONAL 
       XMLSCHEMA "http://xmlns.oracle.com/odmr11/odmr.xsd" ELEMENT "WorkflowProcess"';
  END IF;
END;
/

CREATE TABLE ODMRSYS.ODMR$WF_LOG 
(
  LOG_ID NUMBER NOT NULL 
, USER_NAME VARCHAR2(128) NOT NULL 
, JOB_NAME VARCHAR2(128)
, PROJ_NAME VARCHAR2(128)
, PROJ_ID NUMBER
, WF_NAME VARCHAR2(128)
, WF_ID NUMBER
, NODE_NAME VARCHAR2(128)
, NODE_ID VARCHAR2(30)
, SUBNODE_NAME VARCHAR2(128) 
, SUBNODE_ID VARCHAR2(30) 
, LOG_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE NOT NULL 
, LOG_DURATION INTERVAL DAY(3) TO SECOND(0) 
, LOG_TYPE VARCHAR2(30) NOT NULL 
, LOG_SUBTYPE VARCHAR2(30) NOT NULL 
, LOG_TASK VARCHAR2(30) 
, LOG_MESSAGE NVARCHAR2(2000)
, LOG_MESSAGE_DETAILS VARCHAR2(4000) 
, CONSTRAINT ODMR$WF_LOG_PK PRIMARY KEY
  (
    LOG_ID 
  )
  ENABLE 
) 
LOGGING 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

ALTER TABLE "ODMRSYS"."ODMR$WF_LOG" 
ADD CONSTRAINT WF_LOG_NOT_NULL CHECK ((PROJ_NAME||PROJ_ID||WF_NAME||WF_ID) IS NOT NULL);

CREATE TABLE ODMRSYS.ODMR$WF_JOB_ARGS 
(
  CHAIN_STEP_NAME VARCHAR2(128) NOT NULL 
, ARG_NAME VARCHAR2(60) NOT NULL 
, ARG_DATA_TYPE VARCHAR2(30) NOT NULL 
, ARG_STR_VALUE VARCHAR2(4000) 
, ARG_NUM_VALUE NUMBER 
, WORKFLOW_JOB_ID VARCHAR2(128) NOT NULL 
, CONSTRAINT ODMR$WF_JOB_ARGS_PK PRIMARY KEY 
  (
    WORKFLOW_JOB_ID 
  , CHAIN_STEP_NAME 
  , ARG_NAME 
  )
  ENABLE 
) 
LOGGING 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE ODMRSYS.ODMR$REPOSITORY_PROPERTIES 
(
  PROPERTY_NAME VARCHAR2(128) NOT NULL 
, PROPERTY_NUM_VALUE NUMBER 
, PROPERTY_STR_VALUE VARCHAR2(128) 
, "COMMENT" VARCHAR2(4000) 
, CONSTRAINT ODMR$REPOSITORY_PROPERTIE_PK PRIMARY KEY 
  (
    PROPERTY_NAME 
  )
  ENABLE 
) 
LOGGING 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE ODMRSYS.ODMR$WORKFLOW_RUN_NODES
(
  WORKFLOW_JOB_ID VARCHAR2(128) NOT NULL
, NODE_ID VARCHAR2(30) NOT NULL
, SUBNODE_ID VARCHAR2(30)
, CONSTRAINT ODMR$WORKFLOW_RUN_NODES_UNIQUE UNIQUE
  (
    WORKFLOW_JOB_ID,
    NODE_ID,
    SUBNODE_ID
  )
  ENABLE 
) 
LOGGING 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

INSERT ALL
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('MAX_TARGET_DISTINCT_COUNT', 1000, 'Maximum number of distinct target values allowed in Classificaton Build Node.')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('UNIQUE_PERCENTAGE', 97, 'This setting has been deprecated')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('MAX_DISTINCT_CAT_CUTOFF', 200, 'Used by heuristic rules of the build and predictive query nodes to exclude categorical attributes when their unique value counts > cutoff')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('PERCENT_UNI_THRESHOLD_CAT', 80, 'Used by heuristic rules of the build and predictive query nodes to exclude categorical attributes when their unique value percents > cutoff')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('CUTOFF_FOR_NUMBER_FOR_CAT', 5, 'Used by heuristic rules of the build and predictive query nodes to change attributes mining type from numerical to categorical when their unique value counts > cutoff')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('SPARSITY_CUTOFF', 90, 'This setting has been deprecated')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('MAX_DISTINCT_STRAT_SAMPLE', 1000, 'Maximum number of distinct values allowed for the selected startified column in Sample Node.')
       --INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE) VALUES ('MAX_WORKFLOW_LOG_COUNT', 100000)
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('VERSION', '&REPOSITORY_VERSION', 'Version of the Repository')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('MAX_NUM_THREADS', 10, 'Maximum number of parallel model builds can occur within the repository.')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('THREAD_WAIT_TIME', 5, 'When MAX_NUM_THREADS is reached, further Build process will be put on queue until parallel model build count < MAX_NUM_THREADS. This setting (in seconds) determines how often to check for parallel model build count.')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('MAX_THREAD_WAIT', NULL, 'The timeout (in seconds) for Build process that has been put on queue. If NULL, no timeout will occur.')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('WORKFLOW_JOB_CLASS', 'DEFAULT_JOB_CLASS', 'Job class used to run the workflow.')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('REPOSITORY_STATUS', 'NOT_LOADED', 'Values: NOT_LOADED, LOADED')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('DEBUG_LOG', 'FALSE', 'Triggers log entries to be inserted into the debug log table.')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('SQL_TRACE', 'FALSE', 'Triggers SQL Trace files to be generated for worklow programs running in Oracle Scheduler.')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('SQL_TRACE_ID', 'LONG', 'Values: LONG, SHORT. Adds job specific trace ids to the sql trace file for better identification.')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('VALIDATE_WORKFLOW', 'FALSE', 'Values: TRUE, FALSE. Validate workflow during save operation.')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('WF_VERSION', '&WORKFLOW_VERSION', 'Supported workflow version')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('PARALLEL_QUERY_ON_ALLOWED', 'TRUE', 'If FALSE, the repository implementation will not honor the inserting of parallel query on.')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('PARALLEL_QUERY_OFF_ALLOWED', 'TRUE', 'If FALSE, the repository implementation will not honor the inserting of parallel query off.')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('POLLING_IDLE_RATE', 0, 'Rate (in milliseconds) at which the Data Miner client will poll the database when there are no workflows detected running')
       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')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('POLLING_ACTIVE_RATE', 0, 'Rate (in milliseconds) at which the Data Miner client will poll the database when there are workflows detected running')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('POLLING_COMPLETED_WINDOW', 0, 'The window of time (in hours) to include completed workflows in the polling query result')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('PURGE_WORKFLOW_SCHEDULER_OBJS', 'TRUE', 'Purges (TRUE or FALSE) old Oracle Scheduler objects generated by the running of Data Miner workflows')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_NUM_VALUE, "COMMENT") VALUES ('PURGE_WORKFLOW_EVENT_LOG', 2, 'Controls how many workflows runs are preserved for each workflow in the event log. The older workflows events are purged to keep within this limit')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('IN_MEMORY_ALLOWED', 'TRUE', 'If FALSE, the repository implementation will not honor the In Memory Database Option.')
       INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") VALUES ('WORKFLOW_JOB_CLASS_ALLOWED', 'TRUE', 'If FALSE, the repository implementation will not honor the user specified job class used for the workflow job run.')
SELECT * FROM DUAL;

DECLARE
  v_id NUMBER;
  v_string_size VARCHAR2(30);
BEGIN
  INSERT INTO ODMRSYS.ODMR$REPOSITORY_PROPERTIES (PROPERTY_NAME, PROPERTY_STR_VALUE, "COMMENT") 
  VALUES ('MAX_STRING_SIZE', 'STANDARD', 'Current value of the MAX_STRING_SIZE parameter, which is used to control maximum size of VARCHAR2, NVARCHAR2, and RAW data types');
  BEGIN
    --SELECT value INTO v_string_size FROM v$parameter WHERE name = 'max_string_size';
    v_id := dbms_utility.get_parameter_value('max_string_size', v_id, v_string_size);
  EXCEPTION WHEN OTHERS THEN
    v_string_size := 'STANDARD';
  END;
  UPDATE ODMRSYS.ODMR$REPOSITORY_PROPERTIES SET PROPERTY_STR_VALUE = v_string_size WHERE PROPERTY_NAME = 'MAX_STRING_SIZE';
END;
/

-- Translation messages table
CREATE TABLE ODMRSYS.ODMR$MESSAGES 
(
  MESSAGE_ID NUMBER NOT NULL 
, LANGUAGE_ID VARCHAR2(5) 
, MESSAGE NVARCHAR2(2000) 
, CONSTRAINT ODMR$MESSAGES_PK PRIMARY KEY 
  (
    MESSAGE_ID, LANGUAGE_ID 
  )
  ENABLE 
) 
LOGGING 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);


CREATE INDEX ODMR$MESSAGES_MSG_ID_INDEX ON ODMRSYS.ODMR$MESSAGES (MESSAGE_ID);

CREATE INDEX ODMR$MESSAGES_LANG_ID_INDEX ON ODMRSYS.ODMR$MESSAGES (LANGUAGE_ID);
-- end of Translation messages table

CREATE TABLE ODMRSYS.ODMR$PROJECTS 
(
  USER_NAME VARCHAR2(128) NOT NULL 
, PROJECT_ID NUMBER NOT NULL 
, PROJECT_NAME VARCHAR2(128) NOT NULL 
, CREATION_TIME TIMESTAMP(6) NOT NULL 
, LAST_UPDATED_TIME TIMESTAMP(6) 
, COMMENTS VARCHAR2(4000) 
, CONSTRAINT ODMR$PROJECTS_PK PRIMARY KEY 
  (
    PROJECT_ID 
  )
  ENABLE 
) 
LOGGING 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

ALTER TABLE ODMRSYS.ODMR$WORKFLOW_JOBS
ADD CONSTRAINT ODMR$WORKFLOW_JOBS_UK1 UNIQUE 
(
  WORKFLOW_ID 
, WORKFLOW_JOB_ID 
)
ENABLE;

ALTER TABLE ODMRSYS.ODMR$WORKFLOWS
ADD CONSTRAINT ODMR$WORKFLOWS_UK1 UNIQUE 
(
  PROJECT_ID 
, WORKFLOW_NAME 
)
ENABLE;

ALTER TABLE ODMRSYS.ODMR$PROJECTS
ADD CONSTRAINT ODMR$PROJECTS_UK1 UNIQUE 
(
  USER_NAME 
, PROJECT_ID 
)
ENABLE;

ALTER TABLE ODMRSYS.ODMR$WORKFLOW_JOBS
ADD CONSTRAINT ODMR$WORKFLOW_JOBS_FK FOREIGN KEY
(
  WORKFLOW_ID 
)
REFERENCES ODMRSYS.ODMR$WORKFLOWS
(
  WORKFLOW_ID  
)
ON DELETE CASCADE ENABLE;

ALTER TABLE ODMRSYS.ODMR$WORKFLOWS
ADD CONSTRAINT ODMR$WORKFLOWS_FK FOREIGN KEY
(
  PROJECT_ID 
)
REFERENCES ODMRSYS.ODMR$PROJECTS
(
  PROJECT_ID  
)
ON DELETE CASCADE ENABLE;

ALTER TABLE ODMRSYS.ODMR$WF_JOB_ARGS
ADD CONSTRAINT ODMR$WF_JOB_ARGS_FK FOREIGN KEY
(
  WORKFLOW_JOB_ID 
)
REFERENCES ODMRSYS.ODMR$WORKFLOW_JOBS
(
  WORKFLOW_JOB_ID  
)
ON DELETE CASCADE ENABLE;

ALTER TABLE ODMRSYS.ODMR$WORKFLOW_RUN_NODES
ADD CONSTRAINT ODMR$WORKFLOW_RUN_NODES_FK FOREIGN KEY
(
  WORKFLOW_JOB_ID
)
REFERENCES ODMRSYS.ODMR$WORKFLOW_JOBS
(
  WORKFLOW_JOB_ID 
)
ON DELETE CASCADE ENABLE;

CREATE OR REPLACE VIEW ODMRSYS.ODMR_USER_DEFAULT_STOPLISTS AS SELECT 
    WORD, LANGUAGE
FROM
  ODMRSYS.ODMR$DEFAULT_STOPLISTS;

CREATE OR REPLACE VIEW ODMRSYS.ODMR_ALL_PROJECT_WORKFLOW AS SELECT * FROM
(
  WITH WF_JOBS AS
  (
  SELECT WORKFLOW_ID, WORKFLOW_JOB_ID, CREATION_TIME
    FROM (
      SELECT t.*, row_number () OVER (partition by WORKFLOW_ID ORDER BY CREATION_TIME DESC) rid
      FROM ODMRSYS.ODMR$WORKFLOW_JOBS t )
    WHERE rid = 1
  ),
  CHAIN_JOBS AS
  (
  SELECT JOB_NAME, STATE FROM ALL_SCHEDULER_JOBS WHERE JOB_TYPE = 'CHAIN'
  )
  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,
      CASE UPPER(NVL(CHAIN_JOBS.STATE, 'completed'))
      WHEN 'DISABLED' THEN 'FAILED'
      WHEN 'SCHEDULED' THEN 'SCHEDULED'
      WHEN 'RUNNING' THEN 'RUNNING'
      WHEN 'COMPLETED' THEN 'SUCCEEDED'
      WHEN 'STOPPED' THEN 'STOPPED'
      WHEN 'BROKEN' THEN 'FAILED'
      WHEN 'RETRY SCHEDULED' THEN 'SCHEDULED'
      WHEN 'SUCCEEDED' THEN 'SUCCEEDED'
      WHEN 'CHAIN_STALLED' THEN 'FAILED'
      ELSE CHAIN_JOBS.STATE END "STATUS"
  FROM
      WF_JOBS,
      ODMRSYS.ODMR$WORKFLOWS,
      ODMRSYS.ODMR$PROJECTS,
      CHAIN_JOBS
  WHERE
      ODMR$PROJECTS.PROJECT_ID = ODMR$WORKFLOWS.PROJECT_ID (+)
      AND ODMR$WORKFLOWS.WORKFLOW_ID = WF_JOBS.WORKFLOW_ID (+)
      AND WF_JOBS.WORKFLOW_JOB_ID = CHAIN_JOBS.JOB_NAME (+)
);

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')
);
/*
    Mapping (Scheduler to ODM'r)
    disabled          FAILED
    scheduled         SCHEDULED
    running           RUNNING
    completed         SUCCEEDED
    stopped           STOPPED
    broken            FAILED
    retry scheduled   SCHEDULED
    succeeded         SUCCEEDED
    chain_stalled     FAILED
*/
CREATE OR REPLACE VIEW ODMRSYS.ODMR_ALL_WORKFLOW_JOBS AS SELECT * FROM
(
  WITH WF_JOBS AS
  (
  SELECT WORKFLOW_ID, WORKFLOW_JOB_ID, CREATION_TIME FROM ODMRSYS.ODMR$WORKFLOW_JOBS
  ),
  CHAIN_JOBS AS
  (
  SELECT JOB_NAME, STATE FROM ALL_SCHEDULER_JOBS WHERE JOB_TYPE = 'CHAIN'
  )
  SELECT
      ODMR$PROJECTS.USER_NAME,
      ODMR$PROJECTS.PROJECT_ID,
      ODMR$PROJECTS.PROJECT_NAME,
      ODMR$WORKFLOWS.WORKFLOW_NAME,
      ODMR$WORKFLOWS.CHAIN_NAME "WORKFLOW_CHAIN_NAME",
      WF_JOBS.WORKFLOW_ID,
      WF_JOBS.WORKFLOW_JOB_ID "WORKFLOW_JOB_NAME",
      WF_JOBS.CREATION_TIME "JOB_CREATION_TIME",
      CASE UPPER(NVL(CHAIN_JOBS.STATE, 'completed'))
      WHEN 'DISABLED' THEN 'FAILED'
      WHEN 'SCHEDULED' THEN 'SCHEDULED'
      WHEN 'RUNNING' THEN 'RUNNING'
      WHEN 'COMPLETED' THEN 'SUCCEEDED'
      WHEN 'STOPPED' THEN 'STOPPED'
      WHEN 'BROKEN' THEN 'FAILED'
      WHEN 'RETRY SCHEDULED' THEN 'SCHEDULED'
      WHEN 'SUCCEEDED' THEN 'SUCCEEDED'
      WHEN 'CHAIN_STALLED' THEN 'FAILED'
      ELSE CHAIN_JOBS.STATE END "WORKFLOW_STATUS"
  FROM
      WF_JOBS,
      ODMRSYS.ODMR$WORKFLOWS,
      ODMRSYS.ODMR$PROJECTS,
      CHAIN_JOBS
  WHERE
      ODMR$PROJECTS.PROJECT_ID = ODMR$WORKFLOWS.PROJECT_ID (+)
      AND ODMR$WORKFLOWS.WORKFLOW_ID = WF_JOBS.WORKFLOW_ID (+)
      AND WF_JOBS.WORKFLOW_JOB_ID = CHAIN_JOBS.JOB_NAME (+)
);

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')
);

CREATE OR REPLACE VIEW ODMRSYS.ODMR_ALL_WORKFLOW_LOG AS SELECT 
    LOG_ID,
    USER_NAME,
    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$WF_LOG;
    
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');

CREATE OR REPLACE VIEW ODMRSYS.ODMR_MESSAGES AS SELECT 
  MESSAGE_ID,
  LANGUAGE_ID,
  MESSAGE 
FROM ODMRSYS.ODMR$MESSAGES;

CREATE OR REPLACE VIEW ODMRSYS.ODMR_REPOSITORY_PROPERTIES AS SELECT 
  PROPERTY_NAME,
  PROPERTY_NUM_VALUE,
  PROPERTY_STR_VALUE,
  "COMMENT" 
FROM ODMRSYS.ODMR$REPOSITORY_PROPERTIES;

CREATE OR REPLACE VIEW ODMRSYS.ODMR_ALL_WORKFLOW_RUNNING AS SELECT "USER_NAME", "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
(
  WITH
  s AS (SELECT
          OWNER "USER_NAME",
          JOB_NAME "WF_JOB_NAME",
          NULL "LOG_DATE",
          STEP_JOB_LOG_ID "LOG_ID",
          CASE WHEN SUBSTR(STEP_NAME, 1, ((INSTR(STEP_NAME, '$', 1, 1)) - LENGTH('$'))) IS NULL THEN
                 STEP_NAME
               ELSE SUBSTR(STEP_NAME, 1, ((INSTR(STEP_NAME, '$', 1, 1)) - LENGTH('$')))
               END "NODE_ID",
          CASE WHEN SUBSTR(STEP_NAME, 1, ((INSTR(STEP_NAME, '$', 1, 1)) - LENGTH('$'))) IS NOT NULL THEN
                 SUBSTR(STEP_NAME, (INSTR(STEP_NAME, '$', 1, 1))+LENGTH('$'), LENGTH(STEP_NAME) - (INSTR(STEP_NAME, '$', 1, 1)))
               ELSE NULL
               END "SUBNODE_ID",
          CASE WHEN STATE='NOT_STARTED'  THEN '00000001'
               WHEN STATE='SCHEDULED'    THEN '00000010'
               WHEN STATE='RUNNING'      THEN '00000100'
               WHEN STATE='PAUSED'       THEN '00001000'
               WHEN STATE='SUCCEEDED'    THEN '00010000'
               WHEN STATE='FAILED'       THEN '00100000'
               WHEN STATE='STOPPED'      THEN '01000000'
               WHEN STATE='STALLED'      THEN '10000000'
               END "ENCODE_STATUS",
          STATE "SUBNODE_STATUS",
          START_DATE "NODE_START_TIME",
          DURATION "NODE_RUN_TIME",
          ERROR_CODE "ERROR_CODE",
          NULL "LOG_MESSAGE"
        FROM ALL_SCHEDULER_RUNNING_CHAINS WHERE STEP_NAME IS NOT NULL),
  g AS (SELECT WF_JOB_NAME, NODE_ID, sys.mvaggrawbitor(ENCODE_STATUS) "NODE_STATUS"
        FROM s GROUP BY WF_JOB_NAME, NODE_ID)
  SELECT
    s.USER_NAME,
    w.PROJECT_ID,
    w.WORKFLOW_ID,
    s.WF_JOB_NAME,
    s.LOG_DATE,
    s.LOG_ID,
    s.NODE_ID,
    s.SUBNODE_ID,
    /*
    CASE 
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00000001') = '00000001' THEN 'NOT_STARTED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00000010') = '00000010' THEN 'SCHEDULED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00001000') = '00001000' THEN 'PAUSED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '01000000') = '01000000' THEN 'STOPPED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '10000000') = '10000000' THEN 'STALLED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00100000') = '00100000' THEN 'FAILED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00000100') = '00000100' THEN 'RUNNING'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00010000') = '00010000' THEN 'SUCCEEDED'
    */
    CASE 
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00000100') = '00000100' THEN 'RUNNING'
        WHEN utl_raw.bit_or(g.NODE_STATUS, '00010000') = '00010000' THEN 'SUCCEEDED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00100000') = '00100000' THEN 'FAILED'
        WHEN utl_raw.bit_or(g.NODE_STATUS, '00000001') = '00000001' THEN 'NOT_STARTED'
        WHEN utl_raw.bit_or(g.NODE_STATUS, '00000010') = '00000010' THEN 'SCHEDULED'
        WHEN utl_raw.bit_or(g.NODE_STATUS, '00001000') = '00001000' THEN 'PAUSED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '01000000') = '01000000' THEN 'STOPPED'
        WHEN utl_raw.bit_or(g.NODE_STATUS, '10000000') = '10000000' THEN 'STALLED'
    ELSE 'UNKNOWN' END "NODE_STATUS",
    CASE WHEN SUBNODE_ID IS NOT NULL
         THEN s.SUBNODE_STATUS
         ELSE NULL END "SUBNODE_STATUS",
    s.NODE_START_TIME,
    s.NODE_RUN_TIME,
    s.ERROR_CODE,
    s.LOG_MESSAGE
  FROM s, g, ODMRSYS.ODMR_ALL_WORKFLOW_JOBS w
  WHERE s.WF_JOB_NAME = g.WF_JOB_NAME AND s.WF_JOB_NAME = w.WORKFLOW_JOB_NAME AND s.NODE_ID = g.NODE_ID
  ORDER BY LOG_DATE
);

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');

CREATE OR REPLACE VIEW ODMRSYS.ODMR_ALL_WORKFLOW_COMPLETE AS SELECT "USER_NAME", "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
(
  with
  s AS (SELECT
          OWNER "USER_NAME",
          JOB_NAME "WF_JOB_NAME",
          LOG_DATE,
          LOG_ID,
          CASE WHEN SUBSTR(job_subname, 1, ((INSTR(job_subname, '$', 1, 1)) - LENGTH('$'))) IS NULL THEN
                 job_subname
               ELSE SUBSTR(job_subname, 1, ((INSTR(job_subname, '$', 1, 1)) - LENGTH('$')))
               END "NODE_ID",
          CASE WHEN SUBSTR(job_subname, 1, ((INSTR(job_subname, '$', 1, 1)) - LENGTH('$'))) IS NOT NULL THEN
                 SUBSTR(job_subname, (INSTR(job_subname, '$', 1, 1))+LENGTH('$'), LENGTH(job_subname) - (INSTR(job_subname, '$', 1, 1)))
               ELSE NULL
               END "SUBNODE_ID",
          CASE WHEN status='NOT_STARTED'  THEN '00000001'
               WHEN status='SCHEDULED'    THEN '00000010'
               WHEN status='RUNNING'      THEN '00000100'
               WHEN status='PAUSED'       THEN '00001000'
               WHEN status='SUCCEEDED'    THEN '00010000'
               WHEN status='FAILED'       THEN '00100000'
               WHEN status='STOPPED'      THEN '01000000'
               WHEN status='STALLED'      THEN '10000000'
               END "ENCODE_STATUS",
          STATUS "SUBNODE_STATUS",
          ACTUAL_START_DATE "NODE_START_TIME",
          RUN_DURATION "NODE_RUN_TIME",
          ERROR# "ERROR_CODE",
          CASE WHEN (INSTR(additional_info, ',', 1, 2)) > 0
               THEN LTRIM(SUBSTR(additional_info, (INSTR(additional_info, ',', 1, 2))+LENGTH(','), LENGTH(additional_info) - (INSTR(additional_info, ',', 1, 2))), ' ')
               ELSE NULL
          END "LOG_MESSAGE"
        FROM ALL_SCHEDULER_JOB_RUN_DETAILS WHERE job_subname IS NOT NULL),
  g AS (SELECT WF_JOB_NAME, NODE_ID, sys.mvaggrawbitor(ENCODE_STATUS) "NODE_STATUS"
        FROM s GROUP BY WF_JOB_NAME, NODE_ID)
  SELECT
    s.USER_NAME,
    w.PROJECT_ID,
    w.WORKFLOW_ID,
    s.WF_JOB_NAME,
    s.LOG_DATE,
    s.LOG_ID,
    s.NODE_ID,
    s.SUBNODE_ID,
    /*
    CASE 
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00000001') = '00000001' THEN 'NOT_STARTED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00000010') = '00000010' THEN 'SCHEDULED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00001000') = '00001000' THEN 'PAUSED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '01000000') = '01000000' THEN 'STOPPED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '10000000') = '10000000' THEN 'STALLED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00100000') = '00100000' THEN 'FAILED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00000100') = '00000100' THEN 'RUNNING'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00010000') = '00010000' THEN 'SUCCEEDED'
    */
    CASE 
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00000100') = '00000100' THEN 'RUNNING'
        WHEN utl_raw.bit_or(g.NODE_STATUS, '00010000') = '00010000' THEN 'SUCCEEDED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '00100000') = '00100000' THEN 'FAILED'
        WHEN utl_raw.bit_or(g.NODE_STATUS, '00000001') = '00000001' THEN 'NOT_STARTED'
        WHEN utl_raw.bit_or(g.NODE_STATUS, '00000010') = '00000010' THEN 'SCHEDULED'
        WHEN utl_raw.bit_or(g.NODE_STATUS, '00001000') = '00001000' THEN 'PAUSED'
        WHEN utl_raw.bit_and(g.NODE_STATUS, '01000000') = '01000000' THEN 'STOPPED'
        WHEN utl_raw.bit_or(g.NODE_STATUS, '10000000') = '10000000' THEN 'STALLED'
    ELSE 'UNKNOWN' END "NODE_STATUS",
    CASE WHEN SUBNODE_ID IS NOT NULL
         THEN s.SUBNODE_STATUS
         ELSE NULL END "SUBNODE_STATUS",
    s.NODE_START_TIME,
    s.NODE_RUN_TIME,
    s.ERROR_CODE,
    s.LOG_MESSAGE
  FROM s, g, ODMRSYS.ODMR_ALL_WORKFLOW_JOBS w
  WHERE s.WF_JOB_NAME = g.WF_JOB_NAME AND s.WF_JOB_NAME = w.WORKFLOW_JOB_NAME AND s.NODE_ID = g.NODE_ID
  ORDER BY LOG_DATE
);

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');

CREATE OR REPLACE VIEW ODMRSYS.ODMR_ALL_WORKFLOW_ALL AS (
  SELECT "USER_NAME","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 r
  UNION
  SELECT "USER_NAME","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 c WHERE c."WF_JOB_NAME"||c."NODE_ID"||c."SUBNODE_ID" NOT IN (SELECT n."WF_JOB_NAME"||n."NODE_ID"||n."SUBNODE_ID" FROM ODMRSYS.ODMR_ALL_WORKFLOW_RUNNING n)
);

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');

CREATE OR REPLACE VIEW ODMRSYS.ODMR_ALL_WORKFLOW_ALL_POLL AS SELECT * FROM
(
WITH A AS
(
  SELECT USER_NAME, WORKFLOW_JOB_ID "WF_JOB_NAME", PROJ_ID, WF_ID, NODE_ID, SUBNODE_ID, log_subtype, log_task,
  CASE WHEN NODE_ID = 'WF_START' THEN '11000001'
       WHEN log_type IS NULL THEN '00000000'
       WHEN log_type = 'INFO' AND log_subtype = 'START' THEN '10000001'
       WHEN log_type = 'WARN' AND log_subtype = 'START' THEN '10000010'
       WHEN log_type = 'ERR' AND log_subtype = 'START' THEN '10000100'
       WHEN log_type = 'INFO' AND log_subtype = 'END' THEN '01000001'
       WHEN log_type = 'WARN' AND log_subtype = 'END' THEN '01000010'
       WHEN log_type = 'ERR' AND log_subtype = 'END' THEN '01000100'
  ELSE NULL
  END "ENCODE_STATUS",
  CASE WHEN log_subtype = 'START' THEN
   log_timestamp
  ELSE null
  END "NODE_START_TIME",
  CASE WHEN log_subtype = 'END' THEN
   log_duration
  ELSE null
  END "NODE_RUN_TIME"
  FROM (    
    SELECT l.USER_NAME, n.WORKFLOW_JOB_ID, l.PROJ_ID, l.WF_ID, n.NODE_ID,n.SUBNODE_ID,
    l.log_subtype, l.log_task, l.log_type, l.log_duration, l.log_timestamp
    FROM 
    (
      SELECT DISTINCT WORKFLOW_JOB_ID, NODE_ID, 
      CASE WHEN SUBNODE_ID = 'E' THEN NULL
      WHEN SUBNODE_ID = 'X' THEN NULL
      ELSE SUBNODE_ID
      END "SUBNODE_ID",
      CASE WHEN SUBNODE_ID = 'E' THEN (NODE_ID||'$'||NULL)
      WHEN SUBNODE_ID = 'X' THEN (NODE_ID||'$'||NULL)
      ELSE (NODE_ID||'$'||SUBNODE_ID)
      END "NODE_KEY"
      FROM ODMRSYS.ODMR$WORKFLOW_RUN_NODES
    ) n, 
    (
      SELECT USER_NAME, JOB_NAME, PROJ_ID, WF_ID, NODE_ID, SUBNODE_ID, (NODE_ID||'$'||SUBNODE_ID) "NODE_KEY",
      log_subtype, log_task, log_type, log_duration, log_timestamp
      FROM ODMRSYS.ODMR$WF_LOG
    ) l
    WHERE n.WORKFLOW_JOB_ID = l.JOB_NAME (+) AND n.NODE_KEY = l.NODE_KEY (+)
  ) WHERE log_type IS NULL OR (log_subtype in ('START','END') AND log_task in ('WORKFLOW','NODE','SUBNODE'))
),
B AS (
  SELECT USER_NAME, WF_JOB_NAME, PROJ_ID, WF_ID, NODE_ID, SUBNODE_ID,
  MIN(NODE_START_TIME) NODE_START_TIME, MAX(NODE_RUN_TIME) NODE_RUN_TIME,
  sys.mvaggrawbitor(ENCODE_STATUS) "STATUS"
  FROM A
  group by USER_NAME, WF_JOB_NAME, PROJ_ID, WF_ID, NODE_ID, SUBNODE_ID
)
SELECT USER_NAME, WF_JOB_NAME, PROJ_ID, WF_ID, NODE_ID, SUBNODE_ID, NODE_START_TIME, NODE_RUN_TIME,
  CASE
      WHEN utl_raw.bit_or(STATUS, '00000000') = '00000000' THEN 'NOT_STARTED'
      WHEN utl_raw.bit_or(STATUS, '10000001') = '10000001' THEN 'RUNNING'
      WHEN utl_raw.bit_and(STATUS, '00000100') = '00000100' THEN 'FAILED'
      WHEN utl_raw.bit_or(STATUS, '11000001') = '11000001' THEN 'SUCCEEDED'
      WHEN utl_raw.bit_or(STATUS, '11000011') = '11000011' THEN 'SUCCEEDED'
  ELSE 'FAILED' END "NODE_STATUS",
  CASE
      WHEN SUBNODE_ID IS NULL THEN NULL
      WHEN utl_raw.bit_or(STATUS, '00000000') = '00000000' THEN 'NOT_STARTED'
      WHEN utl_raw.bit_or(STATUS, '10000001') = '10000001' THEN 'RUNNING'
      WHEN utl_raw.bit_and(STATUS, '00000100') = '00000100' THEN 'FAILED'
      WHEN utl_raw.bit_or(STATUS, '11000001') = '11000001' THEN 'SUCCEEDED'
      WHEN utl_raw.bit_or(STATUS, '11000011') = '11000011' THEN 'SUCCEEDED'
  ELSE 'FAILED' END "SUBNODE_STATUS"
FROM B
);

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; 

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 (+)
);

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')
);

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 (+)
);

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')
);

DECLARE
  DB_VER  VARCHAR2(30);
BEGIN
  SELECT VERSION INTO DB_VER FROM PRODUCT_COMPONENT_VERSION WHERE PRODUCT LIKE 'Oracle Database%' OR PRODUCT LIKE 'Personal Oracle Database %' ;
  IF (DB_VER >= '11.2.0.4' ) THEN
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ODMRSYS.ODMR_ALL_WORKFLOW_NODES
      AS
        SELECT P.USER_NAME, P.PROJECT_ID, P.PROJECT_NAME, X.WORKFLOW_NAME, X.WORKFLOW_ID, NODE_TYPE, NODE_ID, NODE_NAME, NODE_STATUS
        FROM ODMRSYS.ODMR$WORKFLOWS X, ODMRSYS.ODMR$PROJECTS P,
             XMLTABLE(XMLNAMESPACES(DEFAULT ''http://xmlns.oracle.com/odmr11''),
                      ''/WorkflowProcess/Nodes/*''
                      PASSING X.WORKFLOW_DATA
                      COLUMNS NODE_TYPE  VARCHAR2(30) PATH ''name()'',
                              NODE_ID    NUMBER  PATH ''@Id'',
                              NODE_NAME  VARCHAR2(128)  PATH ''@Name'',
                              NODE_STATUS VARCHAR2(10)  PATH ''@Status'')
        WHERE
          P.PROJECT_ID = X.PROJECT_ID (+)';
          
    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_ALL_WF_CLAS_TEST_RESULTS
      AS SELECT * FROM (
      WITH "N$10001" AS (
        SELECT 
        P.USER_NAME, P.PROJECT_ID, P.PROJECT_NAME, X.WORKFLOW_ID, X.WORKFLOW_NAME,
        NODE_TYPE, NODE_ID, NODE_NAME, NODE_STATUS,
        MODEL_ID, MODEL_NAME, MODEL_STATUS, RESULT_CREATIONDATE, TEST_METRICS, CONFUSION_MATRIX,
        RESULT_TYPE, RESULT_NAME, ROC_AREA, TARGET_VALUE
          FROM ODMRSYS.ODMR$WORKFLOWS X, ODMRSYS.ODMR$PROJECTS P,
               XMLTABLE(XMLNAMESPACES(DEFAULT ''http://xmlns.oracle.com/odmr11''),
                        ''/WorkflowProcess/Nodes/*''
                        PASSING X.WORKFLOW_DATA
                        COLUMNS NODE_TYPE  VARCHAR2(30) PATH ''name()'',
                                NODE_ID    NUMBER  PATH ''@Id'',
                                NODE_NAME  VARCHAR2(128)  PATH ''@Name'',
                                NODE_STATUS VARCHAR2(10)  PATH ''@Status'',
                                RESULTS XMLTYPE PATH ''Results/ClassificationResult'') XTAB,
               XMLTABLE(XMLNAMESPACES(DEFAULT ''http://xmlns.oracle.com/odmr11''),
                        ''/ClassificationResult''
                        PASSING XTAB.RESULTS
                        COLUMNS MODEL_ID    NUMBER  PATH ''@ModelId'',
                                MODEL_NAME  VARCHAR2(128)  PATH ''@Name'',
                                MODEL_STATUS VARCHAR2(10)  PATH ''@Status'',
                                RESULT_CREATIONDATE VARCHAR2(30)  PATH ''@CreationDate'',
                                TEST_METRICS VARCHAR2(128)  PATH ''TestMetrics/@Name'',
                                CONFUSION_MATRIX VARCHAR2(128)  PATH ''ConfusionMatrix/@Name'',
                                RESULTS XMLTYPE PATH ''*'') XTAB2,
               XMLTABLE(XMLNAMESPACES(DEFAULT ''http://xmlns.oracle.com/odmr11''),
                        ''/*/*''
                        PASSING XTAB2.RESULTS
                        COLUMNS RESULT_TYPE  VARCHAR2(30) PATH ''name()'',
                                RESULT_NAME  VARCHAR2(128)  PATH ''@Name'',
                                ROC_AREA     NUMBER  PATH ''@Area'',
                                TARGET_VALUE VARCHAR2(10)  PATH ''@TargetValue'')
        WHERE
          P.PROJECT_ID = X.PROJECT_ID (+)
      ),
      "N$10002" AS (
        SELECT DISTINCT
        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, CONFUSION_MATRIX
        FROM "N$10001"
      ),
      "N$10003" AS (
      SELECT WORKFLOW_ID, NODE_ID, MODEL_ID,
          CAST(COLLECT(DM_NESTED_CATEGORICAL(ID1, RESULT_STATS)) AS DM_NESTED_CATEGORICALS) LIFTS
          FROM
              (SELECT WORKFLOW_ID, NODE_ID, MODEL_ID, RESULT_TYPE,
              (TARGET_VALUE) ID1,
              STATS_MODE(RESULT_NAME) RESULT_STATS
              FROM "N$10001"  
              GROUP BY WORKFLOW_ID, NODE_ID , MODEL_ID, RESULT_TYPE, TARGET_VALUE HAVING RESULT_TYPE=''Lift'')
           GROUP BY WORKFLOW_ID, NODE_ID, MODEL_ID, RESULT_TYPE
      ),
      "N$10004" AS (
      SELECT WORKFLOW_ID, NODE_ID, MODEL_ID,
          CAST(COLLECT(DM_NESTED_CATEGORICAL(ID1, RESULT_STATS)) AS DM_NESTED_CATEGORICALS) ROCS
          FROM
              (SELECT WORKFLOW_ID, NODE_ID, MODEL_ID, RESULT_TYPE,
              (TARGET_VALUE) ID1,
              STATS_MODE(RESULT_NAME) RESULT_STATS
              FROM "N$10001"  
              GROUP BY WORKFLOW_ID, NODE_ID , MODEL_ID, RESULT_TYPE, TARGET_VALUE HAVING RESULT_TYPE=''ROC'')
           GROUP BY WORKFLOW_ID, NODE_ID, MODEL_ID, RESULT_TYPE
      ),
      "N$10005" AS (
      SELECT WORKFLOW_ID, NODE_ID, MODEL_ID,
          CAST(COLLECT(DM_NESTED_NUMERICAL(ID1, RESULT_STATS)) AS DM_NESTED_NUMERICALS) ROC_AREA
          FROM
              (SELECT WORKFLOW_ID, NODE_ID, MODEL_ID, RESULT_TYPE,
              (TARGET_VALUE) ID1,
              MAX(ROC_AREA) RESULT_STATS
              FROM "N$10001"  
              GROUP BY WORKFLOW_ID, NODE_ID , MODEL_ID, RESULT_TYPE, TARGET_VALUE HAVING RESULT_TYPE=''AreaUnderCurve'')
           GROUP BY WORKFLOW_ID, NODE_ID, MODEL_ID, RESULT_TYPE
      )
      SELECT
        A.USER_NAME, A.PROJECT_ID, A.PROJECT_NAME, A.WORKFLOW_ID, A.WORKFLOW_NAME,
        A.NODE_TYPE, A.NODE_ID, A.NODE_NAME, A.NODE_STATUS,
        A.MODEL_ID, A.MODEL_NAME, A.MODEL_STATUS, A.RESULT_CREATIONDATE, 
        A.TEST_METRICS, A.CONFUSION_MATRIX,
        B.LIFTS, C.ROCS, D.ROC_AREA
      FROM
        "N$10002" A, "N$10003" B, "N$10004" C, "N$10005" D
      WHERE A.WORKFLOW_ID = B.WORKFLOW_ID AND A.NODE_ID = B.NODE_ID AND A.MODEL_ID = B.MODEL_ID
      AND A.WORKFLOW_ID = C.WORKFLOW_ID AND A.NODE_ID = C.NODE_ID AND A.MODEL_ID = C.MODEL_ID
      AND A.WORKFLOW_ID = D.WORKFLOW_ID AND A.NODE_ID = D.NODE_ID AND A.MODEL_ID = D.MODEL_ID)';

    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_ALL_WF_REGR_TEST_RESULTS
      AS SELECT 
        P.USER_NAME, P.PROJECT_ID, P.PROJECT_NAME, X.WORKFLOW_ID, X.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$WORKFLOWS X, ODMRSYS.ODMR$PROJECTS P,
               XMLTABLE(XMLNAMESPACES(DEFAULT ''http://xmlns.oracle.com/odmr11''),
                        ''/WorkflowProcess/Nodes/*''
                        PASSING X.WORKFLOW_DATA
                        COLUMNS NODE_TYPE  VARCHAR2(30) PATH ''name()'',
                                NODE_ID    NUMBER  PATH ''@Id'',
                                NODE_NAME  VARCHAR2(128)  PATH ''@Name'',
                                NODE_STATUS VARCHAR2(10)  PATH ''@Status'',
                                RESULTS XMLTYPE PATH ''Results/RegressionResult'') XTAB,
               XMLTABLE(XMLNAMESPACES(DEFAULT ''http://xmlns.oracle.com/odmr11''),
                        ''/RegressionResult''
                        PASSING XTAB.RESULTS
                        COLUMNS MODEL_ID    NUMBER  PATH ''@ModelId'',
                                MODEL_NAME  VARCHAR2(128)  PATH ''@Name'',
                                MODEL_STATUS VARCHAR2(10)  PATH ''@Status'',
                                RESULT_CREATIONDATE VARCHAR2(30)  PATH ''@CreationDate'',
                                TEST_METRICS VARCHAR2(128)  PATH ''TestMetrics/@Name'',
                                RESIDUAL_PLOT VARCHAR2(128)  PATH ''ResidualPlot/@Name'') XTAB2
        WHERE
          P.PROJECT_ID = X.PROJECT_ID (+)';

    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_ALL_WF_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, CONFUSION_MATRIX,
        LIFTS, ROCS, ROC_AREA, NULL "RESIDUAL_PLOT"
      FROM
        ODMRSYS.ODMR_ALL_WF_CLAS_TEST_RESULTS
      UNION ALL
      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, NULL "CONFUSION_MATRIX",
        NULL "LIFTS", NULL "ROCS", NULL "ROC_AREA", RESIDUAL_PLOT
      FROM
        ODMRSYS.ODMR_ALL_WF_REGR_TEST_RESULTS';
        
    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_ALL_WORKFLOW_MODELS
      AS
        SELECT 
        P.USER_NAME, P.PROJECT_ID, P.PROJECT_NAME, X.WORKFLOW_ID, X.WORKFLOW_NAME,
        NODE_TYPE, NODE_ID, NODE_NAME, NODE_STATUS,
        MODEL_TYPE, MODEL_ID, MODEL_NAME, MODEL_STATUS, MODEL_CREATIONDATE
          FROM ODMRSYS.ODMR$WORKFLOWS X, ODMRSYS.ODMR$PROJECTS P,
               XMLTABLE(XMLNAMESPACES(DEFAULT ''http://xmlns.oracle.com/odmr11''),
                        ''/WorkflowProcess/Nodes/*''
                        PASSING X.WORKFLOW_DATA
                        COLUMNS NODE_TYPE  VARCHAR2(30) PATH ''name()'',
                                NODE_ID    NUMBER  PATH ''@Id'',
                                NODE_NAME  VARCHAR2(128)  PATH ''@Name'',
                                NODE_STATUS VARCHAR2(10)  PATH ''@Status'',
                                MODELDETAILS XMLTYPE PATH ''Models/*''),
               XMLTABLE(XMLNAMESPACES(DEFAULT ''http://xmlns.oracle.com/odmr11''),
                        ''/*''
                        PASSING MODELDETAILS
                        COLUMNS MODEL_TYPE  VARCHAR2(30)  PATH ''name()'',
                                MODEL_ID    NUMBER  PATH ''@Id'',
                                MODEL_NAME  VARCHAR2(128)  PATH ''@Name'',
                                MODEL_STATUS VARCHAR2(10)  PATH ''@Status'',
                                MODEL_CREATIONDATE VARCHAR2(30)  PATH ''@CreationDate'')
        WHERE
          P.PROJECT_ID = X.PROJECT_ID (+)';
          
    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;
EXCEPTION WHEN OTHERS THEN
  RAISE;
END;
/
COMMENT ON COLUMN ODMRSYS.ODMR$WORKFLOWS.RUN_MODE IS 'RUN, RERUN';

CREATE INDEX ODMR$WORKFLOWS_INDEX ON ODMRSYS.ODMR$WORKFLOWS (PROJECT_ID);

CREATE INDEX ODMR$WF_LOG_PROJ_NAME_INDEX ON ODMRSYS.ODMR$WF_LOG (PROJ_NAME);

CREATE INDEX ODMR$WF_LOG_WF_NAME_INDEX ON ODMRSYS.ODMR$WF_LOG (WF_NAME);

CREATE INDEX ODMR$WF_LOG_JOB_NAME_INDEX ON ODMRSYS.ODMR$WF_LOG (JOB_NAME);

CREATE INDEX ODMR$WF_LOG_TYPE_INDEX ON ODMRSYS.ODMR$WF_LOG (LOG_TYPE);

CREATE INDEX ODMR$WF_LOG_STYPE_INDEX ON ODMRSYS.ODMR$WF_LOG (LOG_SUBTYPE);

CREATE INDEX ODMR$WF_LOG_TASK_INDEX ON ODMRSYS.ODMR$WF_LOG (LOG_TASK);

CREATE INDEX ODMR$WF_LOG_NODE_ID_INDEX ON ODMRSYS.ODMR$WF_LOG (NODE_ID);

CREATE INDEX ODMR$WF_LOG_WF_ID_INDEX ON ODMRSYS.ODMR$WF_LOG (WF_ID);

CREATE INDEX ODMR$WF_LOG_TIMESTMP_INDEX ON ODMRSYS.ODMR$WF_LOG (LOG_TIMESTAMP);

CREATE SEQUENCE ODMRSYS.ODMR$WORKFLOW_OBJECT_NAME_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCACHE;

CREATE SEQUENCE ODMRSYS.ODMR$WORKFLOW_ID_SEQ INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCACHE;

CREATE SEQUENCE ODMRSYS.ODMR$PROJECT_ID_SEQ INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCACHE;

CREATE SEQUENCE ODMRSYS.ODMR$LOG_ID_SEQ INCREMENT BY 1 MAXVALUE 9999999999 MINVALUE 1 NOCACHE;

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_COST 
AS OBJECT
(
  PREDICTION  NUMBER,
  PROBABILITY NUMBER,
  COST        NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM 
AS OBJECT
(
  PREDICTION  NUMBER,
  PROBABILITY NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_CAT_COST 
AS OBJECT
(
  PREDICTION  VARCHAR2(128),
  PROBABILITY NUMBER,
  COST        NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_CAT 
AS OBJECT
(
  PREDICTION  VARCHAR2(128),
  PROBABILITY NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_COST 
AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_COST
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM 
AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_CAT_COST 
AS TABLE OF ODMRSYS.ODMR_PREDICTION_CAT_COST
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_CAT 
AS TABLE OF ODMRSYS.ODMR_PREDICTION_CAT
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_FEATURE_NUM 
AS OBJECT
(
  FEATURE_ID  NUMBER,
  VALUE       NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_FEATURE_SET_NUM 
AS TABLE OF ODMRSYS.ODMR_FEATURE_NUM
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_FEATURE_CAT 
AS OBJECT
(
  FEATURE_ID  VARCHAR2(128),
  VALUE       NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_FEATURE_SET_CAT 
AS TABLE OF ODMRSYS.ODMR_FEATURE_CAT
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_CLUSTER_NUM 
AS OBJECT
(
  CLUSTER_ID  NUMBER,
  PROBABILITY NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_CLUSTER_SET_NUM 
AS TABLE OF ODMRSYS.ODMR_CLUSTER_NUM
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_CLUSTER_CAT 
AS OBJECT
(
  CLUSTER_ID  VARCHAR2(128),
  PROBABILITY NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_CLUSTER_SET_CAT 
AS TABLE OF ODMRSYS.ODMR_CLUSTER_CAT
/

-- 12.1 Apply output column types
CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_COSTF
AS OBJECT
(
  PREDICTION  BINARY_FLOAT,
  PROBABILITY BINARY_DOUBLE,
  COST        BINARY_DOUBLE
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_COSTD
AS OBJECT
(
  PREDICTION  BINARY_DOUBLE,
  PROBABILITY BINARY_DOUBLE,
  COST        BINARY_DOUBLE
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_COSTN
AS OBJECT
(
  PREDICTION  NUMBER,
  PROBABILITY BINARY_DOUBLE,
  COST        BINARY_DOUBLE
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_F
AS OBJECT
(
  PREDICTION  BINARY_FLOAT,
  PROBABILITY BINARY_DOUBLE
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_D
AS OBJECT
(
  PREDICTION  BINARY_DOUBLE,
  PROBABILITY BINARY_DOUBLE
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_NUM_N
AS OBJECT
(
  PREDICTION  NUMBER,
  PROBABILITY BINARY_DOUBLE
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_CAT_COSTPD
AS OBJECT
(
  PREDICTION  VARCHAR2(128),
  PROBABILITY BINARY_DOUBLE,
  COST        BINARY_DOUBLE
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_CATPD
AS OBJECT
(
  PREDICTION  VARCHAR2(128),
  PROBABILITY BINARY_DOUBLE
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_COSTF
AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_COSTF
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_COSTD
AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_COSTD
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_COSTN
AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_COSTN
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_F
AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_F
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_D
AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_D
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_NUM_N
AS TABLE OF ODMRSYS.ODMR_PREDICTION_NUM_N
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_CAT_COSTPD
AS TABLE OF ODMRSYS.ODMR_PREDICTION_CAT_COSTPD
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_PREDICTION_SET_CATPD
AS TABLE OF ODMRSYS.ODMR_PREDICTION_CATPD
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_FEATURE_NUMVD
AS OBJECT
(
  FEATURE_ID  NUMBER,
  VALUE       BINARY_DOUBLE
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_FEATURE_SET_NUMVD
AS TABLE OF ODMRSYS.ODMR_FEATURE_NUMVD
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_FEATURE_CATVD
AS OBJECT
(
  FEATURE_ID  VARCHAR2(128),
  VALUE       BINARY_DOUBLE
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_FEATURE_SET_CATVD
AS TABLE OF ODMRSYS.ODMR_FEATURE_CATVD
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_CLUSTER_NUMPD
AS OBJECT
(
  CLUSTER_ID  NUMBER,
  PROBABILITY BINARY_DOUBLE
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_CLUSTER_SET_NUMPD
AS TABLE OF ODMRSYS.ODMR_CLUSTER_NUMPD
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_CLUSTER_CATPD
AS OBJECT
(
  CLUSTER_ID  VARCHAR2(128),
  PROBABILITY BINARY_DOUBLE
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_CLUSTER_SET_CATPD
AS TABLE OF ODMRSYS.ODMR_CLUSTER_CATPD
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_HISTOGRAM_POINT 
AS OBJECT
( 
  ATTRIBUTE_NAME        	VARCHAR2(35),
  ATTRIBUTE_VALUE       	VARCHAR2(4000),
  GROUPING_ATTRIBUTE_NAME       VARCHAR2(35),
  GROUPING_ATTRIBUTE_VALUE      VARCHAR2(4000),
  ATTRIBUTE_PERCENT             NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_HISTOGRAMS 
AS TABLE OF ODMRSYS.ODMR_HISTOGRAM_POINT
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_HISTOGRAM_POINT2
AS OBJECT
( 
  ATTRIBUTE_NAME        	VARCHAR2(130),
  ATTRIBUTE_VALUE       	VARCHAR2(4000),
  GROUPING_ATTRIBUTE_NAME       VARCHAR2(130),
  GROUPING_ATTRIBUTE_VALUE      VARCHAR2(4000),
  ATTRIBUTE_PERCENT             NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_HISTOGRAMS2
AS TABLE OF ODMRSYS.ODMR_HISTOGRAM_POINT2
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_HISTOGRAM_POINT_EX
AS OBJECT
(
ATTRIBUTE_NAME            VARCHAR2(35),
ATTRIBUTE_VALUE           VARCHAR2(32767),
GROUPING_ATTRIBUTE_NAME   VARCHAR2(35),
GROUPING_ATTRIBUTE_VALUE  VARCHAR2(32767),
ATTRIBUTE_PERCENT         NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_HISTOGRAMS_EX
AS TABLE OF ODMRSYS.ODMR_HISTOGRAM_POINT_EX
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_HISTOGRAM_POINT_EX2
AS OBJECT
(
ATTRIBUTE_NAME            VARCHAR2(130),
ATTRIBUTE_VALUE           VARCHAR2(32767),
GROUPING_ATTRIBUTE_NAME   VARCHAR2(130),
GROUPING_ATTRIBUTE_VALUE  VARCHAR2(32767),
ATTRIBUTE_PERCENT         NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_HISTOGRAMS_EX2
AS TABLE OF ODMRSYS.ODMR_HISTOGRAM_POINT_EX2
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_TRANSFORM_SETTING 
AS OBJECT
(
  XFORM_ID                NUMBER,
  SETTING_NAME            VARCHAR2(128),
  SETTING_STR_VALUE       VARCHAR2(4000),
  SETTING_NUM_VALUE       NUMBER
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_TRANSFORM_SETTINGS 
AS TABLE OF ODMRSYS.ODMR_TRANSFORM_SETTING
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_OBJECT_VALUES 
is TABLE OF VARCHAR2(32767 BYTE) -- Fixed Bug 24933312
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_NESTED_VARCHAR2 
is TABLE OF VARCHAR2(4000)
/
CREATE OR REPLACE TYPE ODMRSYS.ODMR_NESTED_NVARCHAR2 
is TABLE OF NVARCHAR2(4000)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_NESTED_VARCHAR2_EX
is TABLE OF VARCHAR2(32767)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_NESTED_NVARCHAR2_EX
is TABLE OF NVARCHAR2(16383)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_NESTED_CHAR
is TABLE OF CHAR(4000)
/
CREATE OR REPLACE TYPE ODMRSYS.ODMR_NESTED_NCHAR 
is TABLE OF NCHAR(4000)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_NODE_REFERENCE 
AS OBJECT
(
  PROJECT_ID              NUMBER,
  PROJECT_NAME            VARCHAR2(128),
  WORKFLOW_ID             NUMBER,
  WORKFLOW_NAME           VARCHAR2(128),
  NODE_NAME               VARCHAR2(128),
  NODE_ID                 NUMBER,
  NODE_TYPE               VARCHAR(30),
  NODE_STATUS             VARCHAR(30)
)
/

CREATE OR REPLACE TYPE ODMRSYS.ODMR_NODE_REFERENCE_SET 
is TABLE OF ODMRSYS.ODMR_NODE_REFERENCE
/

CREATE OR REPLACE
  TYPE ODMRSYS.ODMR_QUALIFIED_OBJECT_NAME
    IS
 /** Use to hold a fully qualified object name, such as a table, view or model
  *    Size is set to 128 to anticipate expanded object name size support.
  */
      OBJECT
      (
        user_name   VARCHAR2(128),
        object_name VARCHAR2(128)
      )
/
      
CREATE OR REPLACE
  TYPE ODMRSYS.ODMR_QUALIFIED_OBJECT_NAMES
    IS
  /** Table of fully qualified object names.
  */
      TABLE OF ODMRSYS.ODMR_QUALIFIED_OBJECT_NAME 
/


-- Create indexes on workflow data
@@createxmlschemaindexes.sql

EXECUTE dbms_output.put_line('Finshed creation of db objects (tables,views,types) in ODMRSYS ' || systimestamp);
