-- Copyright (C) 2001,2002 topIT Informationstechnologie GmbH
-- Copyright (C) 2003-2014 independIT Integrative Technologies GmbH

CREATE TABLE "SUBMITTED_ENTITY" (
    "ID"                           NUMBER (38) PRIMARY KEY
    , "ACCESS_KEY"                   NUMBER (38)          NOT NULL
    , "MASTER_ID"                    NUMBER (38)          NOT NULL
    , "SUBMIT_TAG"                   VARCHAR2 (32 CHAR)       NULL
    , "UNRESOLVED_HANDLING"          NUMBER (38)              NULL
    , "SE_ID"                        NUMBER (38)          NOT NULL
    , "CHILD_TAG"                    VARCHAR2 (70 CHAR)       NULL
    , "SE_VERSION"                   NUMBER (38)          NOT NULL
    , "OWNER_ID"                     NUMBER (38)          NOT NULL
    , "PARENT_ID"                    NUMBER (38)              NULL
    , "SCOPE_ID"                     NUMBER (38)              NULL
    , "IS_STATIC"                    NUMBER (1)           NOT NULL
    , "IS_DISABLED"                  NUMBER (1)           NOT NULL
    , "OLD_STATE"                    NUMBER (38)              NULL
    , "MERGE_MODE"                   NUMBER (38)          NOT NULL
    , "STATE"                        NUMBER (38)          NOT NULL
    , "JOB_ESD_ID"                   NUMBER (38)              NULL
    , "JOB_ESD_PREF"                 NUMBER (38)              NULL
    , "JOB_IS_FINAL"                 NUMBER (1)           NOT NULL
    , "JOB_IS_RESTARTABLE"           NUMBER (1)           NOT NULL
    , "FINAL_ESD_ID"                 NUMBER (38)              NULL
    , "EXIT_CODE"                    NUMBER (38)              NULL
    , "COMMANDLINE"                  VARCHAR2 (512 CHAR)      NULL
    , "RR_COMMANDLINE"               VARCHAR2 (512 CHAR)      NULL
    , "RERUN_SEQ"                    NUMBER (38)          NOT NULL
    , "IS_REPLACED"                  NUMBER (1)           NOT NULL
    , "IS_CANCELLED"                 NUMBER (1)               NULL
    , "BASE_SME_ID"                  NUMBER (38)              NULL
    , "REASON_SME_ID"                NUMBER (38)              NULL
    , "FIRE_SME_ID"                  NUMBER (38)              NULL
    , "FIRE_SE_ID"                   NUMBER (38)              NULL
    , "TR_ID"                        NUMBER (38)              NULL
    , "TR_SD_ID_OLD"                 NUMBER (38)              NULL
    , "TR_SD_ID_NEW"                 NUMBER (38)              NULL
    , "TR_SEQ"                       NUMBER (38)          NOT NULL
    , "WORKDIR"                      VARCHAR2 (512 CHAR)      NULL
    , "LOGFILE"                      VARCHAR2 (512 CHAR)      NULL
    , "ERRLOGFILE"                   VARCHAR2 (512 CHAR)      NULL
    , "PID"                          VARCHAR2 (32 CHAR)       NULL
    , "EXTPID"                       VARCHAR2 (32 CHAR)       NULL
    , "ERROR_MSG"                    VARCHAR2 (256 CHAR)      NULL
    , "KILL_ID"                      NUMBER (38)              NULL
    , "KILL_EXIT_CODE"               NUMBER (38)              NULL
    , "IS_SUSPENDED"                 NUMBER (38)          NOT NULL
    , "IS_SUSPENDED_LOCAL"           NUMBER (1)               NULL
    , "PRIORITY"                     NUMBER (38)          NOT NULL
    , "RAW_PRIORITY"                 NUMBER (38)          NOT NULL
    , "NICE"                         NUMBER (38)          NOT NULL
    , "NP_NICE"                      NUMBER (38)          NOT NULL
    , "MIN_PRIORITY"                 NUMBER (38)          NOT NULL
    , "AGING_AMOUNT"                 NUMBER (38)          NOT NULL
    , "PARENT_SUSPENDED"             NUMBER (38)          NOT NULL
    , "CHILD_SUSPENDED"              NUMBER (38)          NOT NULL
    , "WARN_COUNT"                   NUMBER (38)          NOT NULL
    , "WARN_LINK"                    NUMBER (38)              NULL
    , "APPROVAL_MODE"                NUMBER (38)          NOT NULL
    , "CHILD_APPROVAL_MODE"          NUMBER (38)          NOT NULL
    , "SUBMIT_TS"                    NUMBER (38)          NOT NULL
    , "RESUME_TS"                    NUMBER (38)              NULL
    , "SYNC_TS"                      NUMBER (38)              NULL
    , "RESOURCE_TS"                  NUMBER (38)              NULL
    , "RUNNABLE_TS"                  NUMBER (38)              NULL
    , "START_TS"                     NUMBER (38)              NULL
    , "FINSH_TS"                     NUMBER (38)              NULL
    , "FINAL_TS"                     NUMBER (38)              NULL
    , "CNT_SUBMITTED"                NUMBER (38)          NOT NULL
    , "CNT_DEPENDENCY_WAIT"          NUMBER (38)          NOT NULL
    , "CNT_SYNCHRONIZE_WAIT"         NUMBER (38)          NOT NULL
    , "CNT_RESOURCE_WAIT"            NUMBER (38)          NOT NULL
    , "CNT_RUNNABLE"                 NUMBER (38)          NOT NULL
    , "CNT_STARTING"                 NUMBER (38)          NOT NULL
    , "CNT_STARTED"                  NUMBER (38)          NOT NULL
    , "CNT_RUNNING"                  NUMBER (38)          NOT NULL
    , "CNT_TO_KILL"                  NUMBER (38)          NOT NULL
    , "CNT_KILLED"                   NUMBER (38)          NOT NULL
    , "CNT_CANCELLED"                NUMBER (38)          NOT NULL
    , "CNT_FINISHED"                 NUMBER (38)          NOT NULL
    , "CNT_FINAL"                    NUMBER (38)          NOT NULL
    , "CNT_BROKEN_ACTIVE"            NUMBER (38)          NOT NULL
    , "CNT_BROKEN_FINISHED"          NUMBER (38)          NOT NULL
    , "CNT_ERROR"                    NUMBER (38)          NOT NULL
    , "CNT_UNREACHABLE"              NUMBER (38)          NOT NULL
    , "CNT_RESTARTABLE"              NUMBER (38)          NOT NULL
    , "CNT_WARN"                     NUMBER (38)          NOT NULL
    , "CNT_PENDING"                  NUMBER (38)          NOT NULL
    , "IDLE_TS"                      NUMBER (38)              NULL
    , "IDLE_TIME"                    NUMBER (38)              NULL
    , "STATISTIC_TS"                 NUMBER (38)              NULL
    , "DEPENDENCY_WAIT_TIME"         NUMBER (38)              NULL
    , "SUSPEND_TIME"                 NUMBER (38)              NULL
    , "SYNC_TIME"                    NUMBER (38)              NULL
    , "RESOURCE_TIME"                NUMBER (38)              NULL
    , "JOBSERVER_TIME"               NUMBER (38)              NULL
    , "RESTARTABLE_TIME"             NUMBER (38)              NULL
    , "CHILD_WAIT_TIME"              NUMBER (38)              NULL
    , "OP_SUSRES_TS"                 NUMBER (38)              NULL
    , "NPE_ID"                       NUMBER (38)              NULL
    , "TIME_ZONE"                    VARCHAR2 (32 CHAR)       NULL
    , "CREATOR_U_ID"                 NUMBER (38)          NOT NULL
    , "CREATE_TS"                    NUMBER (38)          NOT NULL
    , "CHANGER_U_ID"                 NUMBER (38)          NOT NULL
    , "CHANGE_TS"                    NUMBER (38)          NOT NULL
);
CREATE OR REPLACE VIEW SCI_SUBMITTED_ENTITY AS
SELECT 
    ID 
    , "MASTER_ID"                    AS "MASTER_ID"
    , "SUBMIT_TAG"                   AS "SUBMIT_TAG"
    , DECODE (UNRESOLVED_HANDLING, 1, 'UH_IGNORE', 3, 'UH_SUSPEND', 2, 'UH_ERROR') AS "UNRESOLVED_HANDLING"
    , "SE_ID"                        AS "SE_ID"
    , "CHILD_TAG"                    AS "CHILD_TAG"
    , "SE_VERSION"                   AS "SE_VERSION"
    , "OWNER_ID"                     AS "OWNER_ID"
    , "PARENT_ID"                    AS "PARENT_ID"
    , "SCOPE_ID"                     AS "SCOPE_ID"
    , DECODE (IS_STATIC, 1, 'TRUE', 0, 'FALSE') AS "IS_STATIC"
    , DECODE (IS_DISABLED, 1, 'TRUE', 0, 'FALSE') AS "IS_DISABLED"
    , "OLD_STATE"                    AS "OLD_STATE"
    , DECODE (MERGE_MODE, 1, 'MERGE_LOCAL', 2, 'MERGE_GLOBAL', 3, 'NOMERGE', 4, 'FAILURE') AS "MERGE_MODE"
    , DECODE (STATE, 0, 'SUBMITTED', 1, 'DEPENDENCY_WAIT', 2, 'SYNCHRONIZE_WAIT', 3, 'RESOURCE_WAIT', 4, 'RUNNABLE', 5, 'STARTING', 6, 'STARTED', 7, 'RUNNING', 8, 'TO_KILL', 9, 'KILLED', 10, 'CANCELLED', 11, 'FINISHED', 12, 'FINAL', 13, 'BROKEN_ACTIVE', 14, 'BROKEN_FINISHED', 15, 'ERROR', 16, 'UNREACHABLE') AS "STATE"
    , "JOB_ESD_ID"                   AS "JOB_ESD_ID"
    , "JOB_ESD_PREF"                 AS "JOB_ESD_PREF"
    , DECODE (JOB_IS_FINAL, 1, 'TRUE', 0, 'FALSE') AS "JOB_IS_FINAL"
    , DECODE (JOB_IS_RESTARTABLE, 1, 'TRUE', 0, 'FALSE') AS "JOB_IS_RESTARTABLE"
    , "FINAL_ESD_ID"                 AS "FINAL_ESD_ID"
    , "EXIT_CODE"                    AS "EXIT_CODE"
    , "COMMANDLINE"                  AS "COMMANDLINE"
    , "RR_COMMANDLINE"               AS "RR_COMMANDLINE"
    , "RERUN_SEQ"                    AS "RERUN_SEQ"
    , DECODE (IS_REPLACED, 1, 'TRUE', 0, 'FALSE') AS "IS_REPLACED"
    , DECODE (IS_CANCELLED, 1, 'TRUE', 0, 'FALSE') AS "IS_CANCELLED"
    , "BASE_SME_ID"                  AS "BASE_SME_ID"
    , "REASON_SME_ID"                AS "REASON_SME_ID"
    , "FIRE_SME_ID"                  AS "FIRE_SME_ID"
    , "FIRE_SE_ID"                   AS "FIRE_SE_ID"
    , "TR_ID"                        AS "TR_ID"
    , "TR_SD_ID_OLD"                 AS "TR_SD_ID_OLD"
    , "TR_SD_ID_NEW"                 AS "TR_SD_ID_NEW"
    , "TR_SEQ"                       AS "TR_SEQ"
    , "WORKDIR"                      AS "WORKDIR"
    , "LOGFILE"                      AS "LOGFILE"
    , "ERRLOGFILE"                   AS "ERRLOGFILE"
    , "PID"                          AS "PID"
    , "EXTPID"                       AS "EXTPID"
    , "ERROR_MSG"                    AS "ERROR_MSG"
    , "KILL_ID"                      AS "KILL_ID"
    , "KILL_EXIT_CODE"               AS "KILL_EXIT_CODE"
    , DECODE (IS_SUSPENDED, 2, 'ADMINSUSPEND', 1, 'SUSPEND', 0, 'NOSUSPEND') AS "IS_SUSPENDED"
    , DECODE (IS_SUSPENDED_LOCAL, 1, 'TRUE', 0, 'FALSE') AS "IS_SUSPENDED_LOCAL"
    , "PRIORITY"                     AS "PRIORITY"
    , "RAW_PRIORITY"                 AS "RAW_PRIORITY"
    , "NICE"                         AS "NICE"
    , "NP_NICE"                      AS "NP_NICE"
    , "MIN_PRIORITY"                 AS "MIN_PRIORITY"
    , "AGING_AMOUNT"                 AS "AGING_AMOUNT"
    , "PARENT_SUSPENDED"             AS "PARENT_SUSPENDED"
    , "CHILD_SUSPENDED"              AS "CHILD_SUSPENDED"
    , "WARN_COUNT"                   AS "WARN_COUNT"
    , "WARN_LINK"                    AS "WARN_LINK"
    , "APPROVAL_MODE"                AS "APPROVAL_MODE"
    , "CHILD_APPROVAL_MODE"          AS "CHILD_APPROVAL_MODE"
    , pkg_time.gmt2local("SUBMIT_TS") AS "SUBMIT_TS"
    , pkg_time.gmt2local("RESUME_TS") AS "RESUME_TS"
    , pkg_time.gmt2local("SYNC_TS") AS "SYNC_TS"
    , pkg_time.gmt2local("RESOURCE_TS") AS "RESOURCE_TS"
    , pkg_time.gmt2local("RUNNABLE_TS") AS "RUNNABLE_TS"
    , pkg_time.gmt2local("START_TS") AS "START_TS"
    , pkg_time.gmt2local("FINSH_TS") AS "FINSH_TS"
    , pkg_time.gmt2local("FINAL_TS") AS "FINAL_TS"
    , "IDLE_TIME"                    AS "IDLE_TIME"
    , "DEPENDENCY_WAIT_TIME"         AS "DEPENDENCY_WAIT_TIME"
    , "SUSPEND_TIME"                 AS "SUSPEND_TIME"
    , "SYNC_TIME"                    AS "SYNC_TIME"
    , "RESOURCE_TIME"                AS "RESOURCE_TIME"
    , "JOBSERVER_TIME"               AS "JOBSERVER_TIME"
    , "RESTARTABLE_TIME"             AS "RESTARTABLE_TIME"
    , "CHILD_WAIT_TIME"              AS "CHILD_WAIT_TIME"
    , "OP_SUSRES_TS"                 AS "OP_SUSRES_TS"
    , "NPE_ID"                       AS "NPE_ID"
    , "TIME_ZONE"                    AS "TIME_ZONE"
    , "CREATOR_U_ID"                 AS "CREATOR_U_ID"
    , pkg_time.gmt2local("CREATE_TS") AS "CREATE_TS"
    , "CHANGER_U_ID"                 AS "CHANGER_U_ID"
    , pkg_time.gmt2local("CHANGE_TS") AS "CHANGE_TS"
    , ((NVL(FINAL_TS, (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - TO_DATE('19700101', 'YYYYDDSS')) * 24 * 60 * 60 * 1000) - SUBMIT_TS) / 1000) - DEPENDENCY_WAIT_TIME AS "PROCESS_TIME"
  FROM "SUBMITTED_ENTITY";
CREATE TABLE ARC_SUBMITTED_ENTITY (
    ID                             NUMBER (38) NOT NULL
    , "ACCESS_KEY"                   NUMBER (38)           NULL
    , "MASTER_ID"                    NUMBER (38)           NULL
    , "SUBMIT_TAG"                   VARCHAR2 (32 CHAR)    NULL
    , "UNRESOLVED_HANDLING"          NUMBER (38)           NULL
    , "SE_ID"                        NUMBER (38)           NULL
    , "CHILD_TAG"                    VARCHAR2 (70 CHAR)    NULL
    , "SE_VERSION"                   NUMBER (38)           NULL
    , "OWNER_ID"                     NUMBER (38)           NULL
    , "PARENT_ID"                    NUMBER (38)           NULL
    , "SCOPE_ID"                     NUMBER (38)           NULL
    , "IS_STATIC"                    NUMBER (1)            NULL
    , "IS_DISABLED"                  NUMBER (1)            NULL
    , "OLD_STATE"                    NUMBER (38)           NULL
    , "MERGE_MODE"                   NUMBER (38)           NULL
    , "STATE"                        NUMBER (38)           NULL
    , "JOB_ESD_ID"                   NUMBER (38)           NULL
    , "JOB_ESD_PREF"                 NUMBER (38)           NULL
    , "JOB_IS_FINAL"                 NUMBER (1)            NULL
    , "JOB_IS_RESTARTABLE"           NUMBER (1)            NULL
    , "FINAL_ESD_ID"                 NUMBER (38)           NULL
    , "EXIT_CODE"                    NUMBER (38)           NULL
    , "COMMANDLINE"                  VARCHAR2 (512 CHAR)   NULL
    , "RR_COMMANDLINE"               VARCHAR2 (512 CHAR)   NULL
    , "RERUN_SEQ"                    NUMBER (38)           NULL
    , "IS_REPLACED"                  NUMBER (1)            NULL
    , "IS_CANCELLED"                 NUMBER (1)            NULL
    , "BASE_SME_ID"                  NUMBER (38)           NULL
    , "REASON_SME_ID"                NUMBER (38)           NULL
    , "FIRE_SME_ID"                  NUMBER (38)           NULL
    , "FIRE_SE_ID"                   NUMBER (38)           NULL
    , "TR_ID"                        NUMBER (38)           NULL
    , "TR_SD_ID_OLD"                 NUMBER (38)           NULL
    , "TR_SD_ID_NEW"                 NUMBER (38)           NULL
    , "TR_SEQ"                       NUMBER (38)           NULL
    , "WORKDIR"                      VARCHAR2 (512 CHAR)   NULL
    , "LOGFILE"                      VARCHAR2 (512 CHAR)   NULL
    , "ERRLOGFILE"                   VARCHAR2 (512 CHAR)   NULL
    , "PID"                          VARCHAR2 (32 CHAR)    NULL
    , "EXTPID"                       VARCHAR2 (32 CHAR)    NULL
    , "ERROR_MSG"                    VARCHAR2 (256 CHAR)   NULL
    , "KILL_ID"                      NUMBER (38)           NULL
    , "KILL_EXIT_CODE"               NUMBER (38)           NULL
    , "IS_SUSPENDED"                 NUMBER (38)           NULL
    , "IS_SUSPENDED_LOCAL"           NUMBER (1)            NULL
    , "PRIORITY"                     NUMBER (38)           NULL
    , "RAW_PRIORITY"                 NUMBER (38)           NULL
    , "NICE"                         NUMBER (38)           NULL
    , "NP_NICE"                      NUMBER (38)           NULL
    , "MIN_PRIORITY"                 NUMBER (38)           NULL
    , "AGING_AMOUNT"                 NUMBER (38)           NULL
    , "PARENT_SUSPENDED"             NUMBER (38)           NULL
    , "CHILD_SUSPENDED"              NUMBER (38)           NULL
    , "WARN_COUNT"                   NUMBER (38)           NULL
    , "WARN_LINK"                    NUMBER (38)           NULL
    , "APPROVAL_MODE"                NUMBER (38)           NULL
    , "CHILD_APPROVAL_MODE"          NUMBER (38)           NULL
    , "SUBMIT_TS"                    NUMBER (38)           NULL
    , "RESUME_TS"                    NUMBER (38)           NULL
    , "SYNC_TS"                      NUMBER (38)           NULL
    , "RESOURCE_TS"                  NUMBER (38)           NULL
    , "RUNNABLE_TS"                  NUMBER (38)           NULL
    , "START_TS"                     NUMBER (38)           NULL
    , "FINSH_TS"                     NUMBER (38)           NULL
    , "FINAL_TS"                     NUMBER (38)           NULL
    , "CNT_SUBMITTED"                NUMBER (38)           NULL
    , "CNT_DEPENDENCY_WAIT"          NUMBER (38)           NULL
    , "CNT_SYNCHRONIZE_WAIT"         NUMBER (38)           NULL
    , "CNT_RESOURCE_WAIT"            NUMBER (38)           NULL
    , "CNT_RUNNABLE"                 NUMBER (38)           NULL
    , "CNT_STARTING"                 NUMBER (38)           NULL
    , "CNT_STARTED"                  NUMBER (38)           NULL
    , "CNT_RUNNING"                  NUMBER (38)           NULL
    , "CNT_TO_KILL"                  NUMBER (38)           NULL
    , "CNT_KILLED"                   NUMBER (38)           NULL
    , "CNT_CANCELLED"                NUMBER (38)           NULL
    , "CNT_FINISHED"                 NUMBER (38)           NULL
    , "CNT_FINAL"                    NUMBER (38)           NULL
    , "CNT_BROKEN_ACTIVE"            NUMBER (38)           NULL
    , "CNT_BROKEN_FINISHED"          NUMBER (38)           NULL
    , "CNT_ERROR"                    NUMBER (38)           NULL
    , "CNT_UNREACHABLE"              NUMBER (38)           NULL
    , "CNT_RESTARTABLE"              NUMBER (38)           NULL
    , "CNT_WARN"                     NUMBER (38)           NULL
    , "CNT_PENDING"                  NUMBER (38)           NULL
    , "IDLE_TS"                      NUMBER (38)           NULL
    , "IDLE_TIME"                    NUMBER (38)           NULL
    , "STATISTIC_TS"                 NUMBER (38)           NULL
    , "DEPENDENCY_WAIT_TIME"         NUMBER (38)           NULL
    , "SUSPEND_TIME"                 NUMBER (38)           NULL
    , "SYNC_TIME"                    NUMBER (38)           NULL
    , "RESOURCE_TIME"                NUMBER (38)           NULL
    , "JOBSERVER_TIME"               NUMBER (38)           NULL
    , "RESTARTABLE_TIME"             NUMBER (38)           NULL
    , "CHILD_WAIT_TIME"              NUMBER (38)           NULL
    , "OP_SUSRES_TS"                 NUMBER (38)           NULL
    , "NPE_ID"                       NUMBER (38)           NULL
    , "TIME_ZONE"                    VARCHAR2 (32 CHAR)    NULL
    , "CREATOR_U_ID"                 NUMBER (38)           NULL
    , "CREATE_TS"                    NUMBER (38)           NULL
    , "CHANGER_U_ID"                 NUMBER (38)           NULL
    , "CHANGE_TS"                    NUMBER (38)           NULL
);
