-- 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                     lvarchar(32)        NULL
    , UNRESOLVED_HANDLING            int8                NULL
    , SE_ID                          bigint          NOT NULL
    , CHILD_TAG                      lvarchar(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                      int8                NULL
    , MERGE_MODE                     int8            NOT NULL
    , STATE                          int8            NOT NULL
    , JOB_ESD_ID                     bigint              NULL
    , JOB_ESD_PREF                   int8                NULL
    , JOB_IS_FINAL                   integer         NOT NULL
    , JOB_IS_RESTARTABLE             integer         NOT NULL
    , FINAL_ESD_ID                   bigint              NULL
    , EXIT_CODE                      int8                NULL
    , COMMANDLINE                    lvarchar(512)       NULL
    , RR_COMMANDLINE                 lvarchar(512)       NULL
    , RERUN_SEQ                      int8            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                         int8            NOT NULL
    , WORKDIR                        lvarchar(512)       NULL
    , LOGFILE                        lvarchar(512)       NULL
    , ERRLOGFILE                     lvarchar(512)       NULL
    , PID                            lvarchar(32)        NULL
    , EXTPID                         lvarchar(32)        NULL
    , ERROR_MSG                      lvarchar(256)       NULL
    , KILL_ID                        bigint              NULL
    , KILL_EXIT_CODE                 int8                NULL
    , IS_SUSPENDED                   int8            NOT NULL
    , IS_SUSPENDED_LOCAL             integer             NULL
    , PRIORITY                       int8            NOT NULL
    , RAW_PRIORITY                   int8            NOT NULL
    , NICE                           int8            NOT NULL
    , NP_NICE                        int8            NOT NULL
    , MIN_PRIORITY                   int8            NOT NULL
    , AGING_AMOUNT                   int8            NOT NULL
    , PARENT_SUSPENDED               int8            NOT NULL
    , CHILD_SUSPENDED                int8            NOT NULL
    , WARN_COUNT                     int8            NOT NULL
    , WARN_LINK                      bigint              NULL
    , APPROVAL_MODE                  int8            NOT NULL
    , CHILD_APPROVAL_MODE            int8            NOT 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                  int8            NOT NULL
    , CNT_DEPENDENCY_WAIT            int8            NOT NULL
    , CNT_SYNCHRONIZE_WAIT           int8            NOT NULL
    , CNT_RESOURCE_WAIT              int8            NOT NULL
    , CNT_RUNNABLE                   int8            NOT NULL
    , CNT_STARTING                   int8            NOT NULL
    , CNT_STARTED                    int8            NOT NULL
    , CNT_RUNNING                    int8            NOT NULL
    , CNT_TO_KILL                    int8            NOT NULL
    , CNT_KILLED                     int8            NOT NULL
    , CNT_CANCELLED                  int8            NOT NULL
    , CNT_FINISHED                   int8            NOT NULL
    , CNT_FINAL                      int8            NOT NULL
    , CNT_BROKEN_ACTIVE              int8            NOT NULL
    , CNT_BROKEN_FINISHED            int8            NOT NULL
    , CNT_ERROR                      int8            NOT NULL
    , CNT_UNREACHABLE                int8            NOT NULL
    , CNT_RESTARTABLE                int8            NOT NULL
    , CNT_WARN                       int8            NOT NULL
    , CNT_PENDING                    int8            NOT NULL
    , IDLE_TS                        int8                NULL
    , IDLE_TIME                      int8                NULL
    , STATISTIC_TS                   int8                NULL
    , DEPENDENCY_WAIT_TIME           int8                NULL
    , SUSPEND_TIME                   int8                NULL
    , SYNC_TIME                      int8                NULL
    , RESOURCE_TIME                  int8                NULL
    , JOBSERVER_TIME                 int8                NULL
    , RESTARTABLE_TIME               int8                NULL
    , CHILD_WAIT_TIME                int8                NULL
    , OP_SUSRES_TS                   bigint              NULL
    , NPE_ID                         bigint              NULL
    , TIME_ZONE                      lvarchar(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 (ID,MASTER_ID,SUBMIT_TAG,UNRESOLVED_HANDLING,SE_ID,CHILD_TAG,SE_VERSION,OWNER_ID,PARENT_ID,SCOPE_ID,IS_STATIC,IS_DISABLED,OLD_STATE,MERGE_MODE,STATE,JOB_ESD_ID,JOB_ESD_PREF,JOB_IS_FINAL,JOB_IS_RESTARTABLE,FINAL_ESD_ID,EXIT_CODE,COMMANDLINE,RR_COMMANDLINE,RERUN_SEQ,IS_REPLACED,IS_CANCELLED,BASE_SME_ID,REASON_SME_ID,FIRE_SME_ID,FIRE_SE_ID,TR_ID,TR_SD_ID_OLD,TR_SD_ID_NEW,TR_SEQ,WORKDIR,LOGFILE,ERRLOGFILE,PID,EXTPID,ERROR_MSG,KILL_ID,KILL_EXIT_CODE,IS_SUSPENDED,IS_SUSPENDED_LOCAL,PRIORITY,RAW_PRIORITY,NICE,NP_NICE,MIN_PRIORITY,AGING_AMOUNT,PARENT_SUSPENDED,CHILD_SUSPENDED,WARN_COUNT,WARN_LINK,APPROVAL_MODE,CHILD_APPROVAL_MODE,SUBMIT_TS,RESUME_TS,SYNC_TS,RESOURCE_TS,RUNNABLE_TS,START_TS,FINSH_TS,FINAL_TS,IDLE_TIME,DEPENDENCY_WAIT_TIME,SUSPEND_TIME,SYNC_TIME,RESOURCE_TIME,JOBSERVER_TIME,RESTARTABLE_TIME,CHILD_WAIT_TIME,OP_SUSRES_TS,NPE_ID,TIME_ZONE,CREATOR_U_ID,CREATE_TS,CHANGER_U_ID,CHANGE_TS,PROCESS_TIME) 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
    , dbinfo ('utc_to_datetime', round(mod(SUBMIT_TS, 1125899906842624)/1000)) AS SUBMIT_TS
    , dbinfo ('utc_to_datetime', round(mod(RESUME_TS, 1125899906842624)/1000)) AS RESUME_TS
    , dbinfo ('utc_to_datetime', round(mod(SYNC_TS, 1125899906842624)/1000)) AS SYNC_TS
    , dbinfo ('utc_to_datetime', round(mod(RESOURCE_TS, 1125899906842624)/1000)) AS RESOURCE_TS
    , dbinfo ('utc_to_datetime', round(mod(RUNNABLE_TS, 1125899906842624)/1000)) AS RUNNABLE_TS
    , dbinfo ('utc_to_datetime', round(mod(START_TS, 1125899906842624)/1000)) AS START_TS
    , dbinfo ('utc_to_datetime', round(mod(FINSH_TS, 1125899906842624)/1000)) AS FINSH_TS
    , dbinfo ('utc_to_datetime', round(mod(FINAL_TS, 1125899906842624)/1000)) 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
    , dbinfo ('utc_to_datetime', round(mod(CREATE_TS, 1125899906842624)/1000)) AS CREATE_TS
    , CHANGER_U_ID                   AS CHANGER_U_ID
    , dbinfo ('utc_to_datetime', round(mod(CHANGE_TS, 1125899906842624)/1000)) AS CHANGE_TS
    , ((NVL(FINAL_TS, dbinfo ('utc_current') * 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                     lvarchar(32)     NULL
    , UNRESOLVED_HANDLING            int8             NULL
    , SE_ID                          bigint           NULL
    , CHILD_TAG                      lvarchar(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                      int8             NULL
    , MERGE_MODE                     int8             NULL
    , STATE                          int8             NULL
    , JOB_ESD_ID                     bigint           NULL
    , JOB_ESD_PREF                   int8             NULL
    , JOB_IS_FINAL                   integer          NULL
    , JOB_IS_RESTARTABLE             integer          NULL
    , FINAL_ESD_ID                   bigint           NULL
    , EXIT_CODE                      int8             NULL
    , COMMANDLINE                    lvarchar(512)    NULL
    , RR_COMMANDLINE                 lvarchar(512)    NULL
    , RERUN_SEQ                      int8             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                         int8             NULL
    , WORKDIR                        lvarchar(512)    NULL
    , LOGFILE                        lvarchar(512)    NULL
    , ERRLOGFILE                     lvarchar(512)    NULL
    , PID                            lvarchar(32)     NULL
    , EXTPID                         lvarchar(32)     NULL
    , ERROR_MSG                      lvarchar(256)    NULL
    , KILL_ID                        bigint           NULL
    , KILL_EXIT_CODE                 int8             NULL
    , IS_SUSPENDED                   int8             NULL
    , IS_SUSPENDED_LOCAL             integer          NULL
    , PRIORITY                       int8             NULL
    , RAW_PRIORITY                   int8             NULL
    , NICE                           int8             NULL
    , NP_NICE                        int8             NULL
    , MIN_PRIORITY                   int8             NULL
    , AGING_AMOUNT                   int8             NULL
    , PARENT_SUSPENDED               int8             NULL
    , CHILD_SUSPENDED                int8             NULL
    , WARN_COUNT                     int8             NULL
    , WARN_LINK                      bigint           NULL
    , APPROVAL_MODE                  int8             NULL
    , CHILD_APPROVAL_MODE            int8             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                  int8             NULL
    , CNT_DEPENDENCY_WAIT            int8             NULL
    , CNT_SYNCHRONIZE_WAIT           int8             NULL
    , CNT_RESOURCE_WAIT              int8             NULL
    , CNT_RUNNABLE                   int8             NULL
    , CNT_STARTING                   int8             NULL
    , CNT_STARTED                    int8             NULL
    , CNT_RUNNING                    int8             NULL
    , CNT_TO_KILL                    int8             NULL
    , CNT_KILLED                     int8             NULL
    , CNT_CANCELLED                  int8             NULL
    , CNT_FINISHED                   int8             NULL
    , CNT_FINAL                      int8             NULL
    , CNT_BROKEN_ACTIVE              int8             NULL
    , CNT_BROKEN_FINISHED            int8             NULL
    , CNT_ERROR                      int8             NULL
    , CNT_UNREACHABLE                int8             NULL
    , CNT_RESTARTABLE                int8             NULL
    , CNT_WARN                       int8             NULL
    , CNT_PENDING                    int8             NULL
    , IDLE_TS                        int8             NULL
    , IDLE_TIME                      int8             NULL
    , STATISTIC_TS                   int8             NULL
    , DEPENDENCY_WAIT_TIME           int8             NULL
    , SUSPEND_TIME                   int8             NULL
    , SYNC_TIME                      int8             NULL
    , RESOURCE_TIME                  int8             NULL
    , JOBSERVER_TIME                 int8             NULL
    , RESTARTABLE_TIME               int8             NULL
    , CHILD_WAIT_TIME                int8             NULL
    , OP_SUSRES_TS                   bigint           NULL
    , NPE_ID                         bigint           NULL
    , TIME_ZONE                      lvarchar(32)     NULL
    , CREATOR_U_ID                   bigint           NULL
    , CREATE_TS                      bigint           NULL
    , CHANGER_U_ID                   bigint           NULL
    , CHANGE_TS                      bigint           NULL
);
