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