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

CREATE TABLE "SUBMITTED_ENTITY_STATS" (
    "ID"                           NUMBER (38) PRIMARY KEY
    , "SME_ID"                       NUMBER (38)          NOT NULL
    , "RERUN_SEQ"                    NUMBER (38)          NOT NULL
    , "SCOPE_ID"                     NUMBER (38)              NULL
    , "JOB_ESD_ID"                   NUMBER (38)              NULL
    , "EXIT_CODE"                    NUMBER (38)              NULL
    , "COMMANDLINE"                  VARCHAR2 (512 CHAR)      NULL
    , "WORKDIR"                      VARCHAR2 (512 CHAR)      NULL
    , "LOGFILE"                      VARCHAR2 (512 CHAR)      NULL
    , "ERRLOGFILE"                   VARCHAR2 (512 CHAR)      NULL
    , "EXTPID"                       VARCHAR2 (32 CHAR)       NULL
    , "SYNC_TS"                      NUMBER (38)              NULL
    , "RESOURCE_TS"                  NUMBER (38)              NULL
    , "RUNNABLE_TS"                  NUMBER (38)              NULL
    , "START_TS"                     NUMBER (38)              NULL
    , "FINISH_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_SUBMITTED_ENTITY_STATS AS
SELECT 
    ID 
    , "SME_ID"                       AS "SME_ID"
    , "RERUN_SEQ"                    AS "RERUN_SEQ"
    , "SCOPE_ID"                     AS "SCOPE_ID"
    , "JOB_ESD_ID"                   AS "JOB_ESD_ID"
    , "EXIT_CODE"                    AS "EXIT_CODE"
    , "COMMANDLINE"                  AS "COMMANDLINE"
    , "WORKDIR"                      AS "WORKDIR"
    , "LOGFILE"                      AS "LOGFILE"
    , "ERRLOGFILE"                   AS "ERRLOGFILE"
    , "EXTPID"                       AS "EXTPID"
    , 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("FINISH_TS") AS "FINISH_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 "SUBMITTED_ENTITY_STATS";
CREATE TABLE ARC_SUBMITTED_ENTITY_STATS (
    ID                             NUMBER (38) NOT NULL
    , "SME_ID"                       NUMBER (38)           NULL
    , "RERUN_SEQ"                    NUMBER (38)           NULL
    , "SCOPE_ID"                     NUMBER (38)           NULL
    , "JOB_ESD_ID"                   NUMBER (38)           NULL
    , "EXIT_CODE"                    NUMBER (38)           NULL
    , "COMMANDLINE"                  VARCHAR2 (512 CHAR)   NULL
    , "WORKDIR"                      VARCHAR2 (512 CHAR)   NULL
    , "LOGFILE"                      VARCHAR2 (512 CHAR)   NULL
    , "ERRLOGFILE"                   VARCHAR2 (512 CHAR)   NULL
    , "EXTPID"                       VARCHAR2 (32 CHAR)    NULL
    , "SYNC_TS"                      NUMBER (38)           NULL
    , "RESOURCE_TS"                  NUMBER (38)           NULL
    , "RUNNABLE_TS"                  NUMBER (38)           NULL
    , "START_TS"                     NUMBER (38)           NULL
    , "FINISH_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
);
