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

CREATE TABLE SUBMITTED_ENTITY (
    ID                             bigint NOT NULL
    , ACCESS_KEY                     bigint          NOT NULL
    , MASTER_ID                      bigint          NOT NULL
    , SUBMIT_TAG                     varchar(32)         NULL
    , UNRESOLVED_HANDLING            integer             NULL
    , SE_ID                          bigint          NOT NULL
    , CHILD_TAG                      varchar(70)         NULL
    , SE_VERSION                     bigint          NOT NULL
    , OWNER_ID                       bigint          NOT NULL
    , PARENT_ID                      bigint              NULL
    , SCOPE_ID                       bigint              NULL
    , IS_STATIC                      integer         NOT NULL
    , IS_DISABLED                    integer         NOT NULL
    , OLD_STATE                      integer             NULL
    , MERGE_MODE                     integer         NOT NULL
    , STATE                          integer         NOT NULL
    , JOB_ESD_ID                     bigint              NULL
    , JOB_ESD_PREF                   integer             NULL
    , JOB_IS_FINAL                   integer         NOT NULL
    , JOB_IS_RESTARTABLE             integer         NOT NULL
    , FINAL_ESD_ID                   bigint              NULL
    , EXIT_CODE                      integer             NULL
    , COMMANDLINE                    varchar(512)        NULL
    , RR_COMMANDLINE                 varchar(512)        NULL
    , RERUN_SEQ                      integer         NOT NULL
    , IS_REPLACED                    integer         NOT NULL
    , IS_CANCELLED                   integer             NULL
    , BASE_SME_ID                    bigint              NULL
    , REASON_SME_ID                  bigint              NULL
    , FIRE_SME_ID                    bigint              NULL
    , FIRE_SE_ID                     bigint              NULL
    , TR_ID                          bigint              NULL
    , TR_SD_ID_OLD                   bigint              NULL
    , TR_SD_ID_NEW                   bigint              NULL
    , TR_SEQ                         integer         NOT NULL
    , WORKDIR                        varchar(512)        NULL
    , LOGFILE                        varchar(512)        NULL
    , ERRLOGFILE                     varchar(512)        NULL
    , PID                            varchar(32)         NULL
    , EXTPID                         varchar(32)         NULL
    , ERROR_MSG                      varchar(256)        NULL
    , KILL_ID                        bigint              NULL
    , KILL_EXIT_CODE                 integer             NULL
    , IS_SUSPENDED                   integer         NOT NULL
    , IS_SUSPENDED_LOCAL             integer             NULL
    , PRIORITY                       integer         NOT NULL
    , RAW_PRIORITY                   integer         NOT NULL
    , NICE                           integer         NOT NULL
    , NP_NICE                        integer         NOT NULL
    , MIN_PRIORITY                   integer         NOT NULL
    , AGING_AMOUNT                   integer         NOT NULL
    , PARENT_SUSPENDED               integer         NOT NULL
    , CHILD_SUSPENDED                integer         NOT NULL
    , WARN_COUNT                     integer         NOT NULL
    , WARN_LINK                      bigint              NULL
    , SUBMIT_TS                      bigint          NOT NULL
    , RESUME_TS                      bigint              NULL
    , SYNC_TS                        bigint              NULL
    , RESOURCE_TS                    bigint              NULL
    , RUNNABLE_TS                    bigint              NULL
    , START_TS                       bigint              NULL
    , FINSH_TS                       bigint              NULL
    , FINAL_TS                       bigint              NULL
    , CNT_SUBMITTED                  integer         NOT NULL
    , CNT_DEPENDENCY_WAIT            integer         NOT NULL
    , CNT_SYNCHRONIZE_WAIT           integer         NOT NULL
    , CNT_RESOURCE_WAIT              integer         NOT NULL
    , CNT_RUNNABLE                   integer         NOT NULL
    , CNT_STARTING                   integer         NOT NULL
    , CNT_STARTED                    integer         NOT NULL
    , CNT_RUNNING                    integer         NOT NULL
    , CNT_TO_KILL                    integer         NOT NULL
    , CNT_KILLED                     integer         NOT NULL
    , CNT_CANCELLED                  integer         NOT NULL
    , CNT_FINISHED                   integer         NOT NULL
    , CNT_FINAL                      integer         NOT NULL
    , CNT_BROKEN_ACTIVE              integer         NOT NULL
    , CNT_BROKEN_FINISHED            integer         NOT NULL
    , CNT_ERROR                      integer         NOT NULL
    , CNT_UNREACHABLE                integer         NOT NULL
    , CNT_RESTARTABLE                integer         NOT NULL
    , CNT_WARN                       integer         NOT NULL
    , CNT_PENDING                    integer         NOT NULL
    , IDLE_TS                        integer             NULL
    , IDLE_TIME                      integer             NULL
    , STATISTIC_TS                   integer             NULL
    , DEPENDENCY_WAIT_TIME           integer             NULL
    , SUSPEND_TIME                   integer             NULL
    , SYNC_TIME                      integer             NULL
    , RESOURCE_TIME                  integer             NULL
    , JOBSERVER_TIME                 integer             NULL
    , RESTARTABLE_TIME               integer             NULL
    , CHILD_WAIT_TIME                integer             NULL
    , OP_SUSRES_TS                   bigint              NULL
    , NPE_ID                         bigint              NULL
    , TIME_ZONE                      varchar(32)         NULL
    , CREATOR_U_ID                   bigint          NOT NULL
    , CREATE_TS                      bigint          NOT NULL
    , CHANGER_U_ID                   bigint          NOT NULL
    , CHANGE_TS                      bigint          NOT NULL
);
CREATE UNIQUE INDEX PK_SUBMITTED_ENTITY
ON SUBMITTED_ENTITY(ID);
CREATE 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
    , timestamp('1970-01-01-00.00.00') + int(mod(SUBMIT_TS, 1125899906842624)/1000) seconds AS SUBMIT_TS
    , timestamp('1970-01-01-00.00.00') + int(mod(RESUME_TS, 1125899906842624)/1000) seconds AS RESUME_TS
    , timestamp('1970-01-01-00.00.00') + int(mod(SYNC_TS, 1125899906842624)/1000) seconds AS SYNC_TS
    , timestamp('1970-01-01-00.00.00') + int(mod(RESOURCE_TS, 1125899906842624)/1000) seconds AS RESOURCE_TS
    , timestamp('1970-01-01-00.00.00') + int(mod(RUNNABLE_TS, 1125899906842624)/1000) seconds AS RUNNABLE_TS
    , timestamp('1970-01-01-00.00.00') + int(mod(START_TS, 1125899906842624)/1000) seconds AS START_TS
    , timestamp('1970-01-01-00.00.00') + int(mod(FINSH_TS, 1125899906842624)/1000) seconds AS FINSH_TS
    , timestamp('1970-01-01-00.00.00') + int(mod(FINAL_TS, 1125899906842624)/1000) seconds 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
    , timestamp('1970-01-01-00.00.00') + int(mod(CREATE_TS, 1125899906842624)/1000) seconds AS CREATE_TS
    , CHANGER_U_ID                   AS CHANGER_U_ID
    , timestamp('1970-01-01-00.00.00') + int(mod(CHANGE_TS, 1125899906842624)/1000) seconds AS CHANGE_TS
    , ((NVL(FINAL_TS, TIMESTAMPDIFF(2, CURRENT TIMESTAMP - CURRENT TIMEZONE - TIMESTAMP('1970-01-01-00.00.00')) * 1000) - SUBMIT_TS) / 1000) - DEPENDENCY_WAIT_TIME AS PROCESS_TIME
  FROM SUBMITTED_ENTITY;
CREATE TABLE ARC_SUBMITTED_ENTITY (
    ID                             bigint NOT NULL
    , ACCESS_KEY                     bigint           NULL
    , MASTER_ID                      bigint           NULL
    , SUBMIT_TAG                     varchar(32)      NULL
    , UNRESOLVED_HANDLING            integer          NULL
    , SE_ID                          bigint           NULL
    , CHILD_TAG                      varchar(70)      NULL
    , SE_VERSION                     bigint           NULL
    , OWNER_ID                       bigint           NULL
    , PARENT_ID                      bigint           NULL
    , SCOPE_ID                       bigint           NULL
    , IS_STATIC                      integer          NULL
    , IS_DISABLED                    integer          NULL
    , OLD_STATE                      integer          NULL
    , MERGE_MODE                     integer          NULL
    , STATE                          integer          NULL
    , JOB_ESD_ID                     bigint           NULL
    , JOB_ESD_PREF                   integer          NULL
    , JOB_IS_FINAL                   integer          NULL
    , JOB_IS_RESTARTABLE             integer          NULL
    , FINAL_ESD_ID                   bigint           NULL
    , EXIT_CODE                      integer          NULL
    , COMMANDLINE                    varchar(512)     NULL
    , RR_COMMANDLINE                 varchar(512)     NULL
    , RERUN_SEQ                      integer          NULL
    , IS_REPLACED                    integer          NULL
    , IS_CANCELLED                   integer          NULL
    , BASE_SME_ID                    bigint           NULL
    , REASON_SME_ID                  bigint           NULL
    , FIRE_SME_ID                    bigint           NULL
    , FIRE_SE_ID                     bigint           NULL
    , TR_ID                          bigint           NULL
    , TR_SD_ID_OLD                   bigint           NULL
    , TR_SD_ID_NEW                   bigint           NULL
    , TR_SEQ                         integer          NULL
    , WORKDIR                        varchar(512)     NULL
    , LOGFILE                        varchar(512)     NULL
    , ERRLOGFILE                     varchar(512)     NULL
    , PID                            varchar(32)      NULL
    , EXTPID                         varchar(32)      NULL
    , ERROR_MSG                      varchar(256)     NULL
    , KILL_ID                        bigint           NULL
    , KILL_EXIT_CODE                 integer          NULL
    , IS_SUSPENDED                   integer          NULL
    , IS_SUSPENDED_LOCAL             integer          NULL
    , PRIORITY                       integer          NULL
    , RAW_PRIORITY                   integer          NULL
    , NICE                           integer          NULL
    , NP_NICE                        integer          NULL
    , MIN_PRIORITY                   integer          NULL
    , AGING_AMOUNT                   integer          NULL
    , PARENT_SUSPENDED               integer          NULL
    , CHILD_SUSPENDED                integer          NULL
    , WARN_COUNT                     integer          NULL
    , WARN_LINK                      bigint           NULL
    , SUBMIT_TS                      bigint           NULL
    , RESUME_TS                      bigint           NULL
    , SYNC_TS                        bigint           NULL
    , RESOURCE_TS                    bigint           NULL
    , RUNNABLE_TS                    bigint           NULL
    , START_TS                       bigint           NULL
    , FINSH_TS                       bigint           NULL
    , FINAL_TS                       bigint           NULL
    , CNT_SUBMITTED                  integer          NULL
    , CNT_DEPENDENCY_WAIT            integer          NULL
    , CNT_SYNCHRONIZE_WAIT           integer          NULL
    , CNT_RESOURCE_WAIT              integer          NULL
    , CNT_RUNNABLE                   integer          NULL
    , CNT_STARTING                   integer          NULL
    , CNT_STARTED                    integer          NULL
    , CNT_RUNNING                    integer          NULL
    , CNT_TO_KILL                    integer          NULL
    , CNT_KILLED                     integer          NULL
    , CNT_CANCELLED                  integer          NULL
    , CNT_FINISHED                   integer          NULL
    , CNT_FINAL                      integer          NULL
    , CNT_BROKEN_ACTIVE              integer          NULL
    , CNT_BROKEN_FINISHED            integer          NULL
    , CNT_ERROR                      integer          NULL
    , CNT_UNREACHABLE                integer          NULL
    , CNT_RESTARTABLE                integer          NULL
    , CNT_WARN                       integer          NULL
    , CNT_PENDING                    integer          NULL
    , IDLE_TS                        integer          NULL
    , IDLE_TIME                      integer          NULL
    , STATISTIC_TS                   integer          NULL
    , DEPENDENCY_WAIT_TIME           integer          NULL
    , SUSPEND_TIME                   integer          NULL
    , SYNC_TIME                      integer          NULL
    , RESOURCE_TIME                  integer          NULL
    , JOBSERVER_TIME                 integer          NULL
    , RESTARTABLE_TIME               integer          NULL
    , CHILD_WAIT_TIME                integer          NULL
    , OP_SUSRES_TS                   bigint           NULL
    , NPE_ID                         bigint           NULL
    , TIME_ZONE                      varchar(32)      NULL
    , CREATOR_U_ID                   bigint           NULL
    , CREATE_TS                      bigint           NULL
    , CHANGER_U_ID                   bigint           NULL
    , CHANGE_TS                      bigint           NULL
);
