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

CREATE TABLE "KILL_JOB" (
    "ID"                           NUMBER (38) PRIMARY KEY
    , "SE_ID"                        NUMBER (38)          NOT NULL
    , "SE_VERSION"                   NUMBER (38)          NOT NULL
    , "SME_ID"                       NUMBER (38)          NOT NULL
    , "SCOPE_ID"                     NUMBER (38)          NOT NULL
    , "STATE"                        NUMBER (38)          NOT NULL
    , "EXIT_CODE"                    NUMBER (38)              NULL
    , "COMMANDLINE"                  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
    , "RUNNABLE_TS"                  NUMBER (38)              NULL
    , "START_TS"                     NUMBER (38)              NULL
    , "FINSH_TS"                     NUMBER (38)              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_KILL_JOB AS
SELECT 
    ID 
    , "SE_ID"                        AS "SE_ID"
    , "SE_VERSION"                   AS "SE_VERSION"
    , "SME_ID"                       AS "SME_ID"
    , "SCOPE_ID"                     AS "SCOPE_ID"
    , DECODE (STATE, 4, 'RUNNABLE', 5, 'STARTING', 6, 'STARTED', 7, 'RUNNING', 11, 'FINISHED', 13, 'BROKEN_ACTIVE', 14, 'BROKEN_FINISHED', 15, 'ERROR') AS "STATE"
    , "EXIT_CODE"                    AS "EXIT_CODE"
    , "COMMANDLINE"                  AS "COMMANDLINE"
    , "LOGFILE"                      AS "LOGFILE"
    , "ERRLOGFILE"                   AS "ERRLOGFILE"
    , "PID"                          AS "PID"
    , "EXTPID"                       AS "EXTPID"
    , "ERROR_MSG"                    AS "ERROR_MSG"
    , pkg_time.gmt2local("RUNNABLE_TS") AS "RUNNABLE_TS"
    , pkg_time.gmt2local("START_TS") AS "START_TS"
    , pkg_time.gmt2local("FINSH_TS") AS "FINSH_TS"
    , "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"
  FROM "KILL_JOB";
CREATE TABLE ARC_KILL_JOB (
    ID                             NUMBER (38) NOT NULL
    , "SE_ID"                        NUMBER (38)           NULL
    , "SE_VERSION"                   NUMBER (38)           NULL
    , "SME_ID"                       NUMBER (38)           NULL
    , "SCOPE_ID"                     NUMBER (38)           NULL
    , "STATE"                        NUMBER (38)           NULL
    , "EXIT_CODE"                    NUMBER (38)           NULL
    , "COMMANDLINE"                  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
    , "RUNNABLE_TS"                  NUMBER (38)           NULL
    , "START_TS"                     NUMBER (38)           NULL
    , "FINSH_TS"                     NUMBER (38)           NULL
    , "CREATOR_U_ID"                 NUMBER (38)           NULL
    , "CREATE_TS"                    NUMBER (38)           NULL
    , "CHANGER_U_ID"                 NUMBER (38)           NULL
    , "CHANGE_TS"                    NUMBER (38)           NULL
);
