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

CREATE TABLE [SUBMITTED_ENTITY_STATS] (
    [ID]                           decimal (38) NOT NULL
    , [SME_ID]                       decimal (38)    NOT NULL
    , [RERUN_SEQ]                    integer         NOT NULL
    , [SCOPE_ID]                     decimal (38)        NULL
    , [JOB_ESD_ID]                   decimal (38)        NULL
    , [EXIT_CODE]                    integer             NULL
    , [COMMANDLINE]                  varchar(512)        NULL
    , [WORKDIR]                      varchar(512)        NULL
    , [LOGFILE]                      varchar(512)        NULL
    , [ERRLOGFILE]                   varchar(512)        NULL
    , [EXTPID]                       varchar(32)         NULL
    , [SYNC_TS]                      decimal (38)        NULL
    , [RESOURCE_TS]                  decimal (38)        NULL
    , [RUNNABLE_TS]                  decimal (38)        NULL
    , [START_TS]                     decimal (38)        NULL
    , [FINISH_TS]                    decimal (38)        NULL
    , [CREATOR_U_ID]                 decimal (38)    NOT NULL
    , [CREATE_TS]                    decimal (38)    NOT NULL
    , [CHANGER_U_ID]                 decimal (38)    NOT NULL
    , [CHANGE_TS]                    decimal (38)    NOT NULL
);
go
CREATE UNIQUE INDEX PK_SUBMITTED_ENTITY_STATS
ON [SUBMITTED_ENTITY_STATS]([ID]);
go
CREATE 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]
    , dateadd(ss, round(([SYNC_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [SYNC_TS]
    , dateadd(ss, round(([RESOURCE_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [RESOURCE_TS]
    , dateadd(ss, round(([RUNNABLE_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [RUNNABLE_TS]
    , dateadd(ss, round(([START_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [START_TS]
    , dateadd(ss, round(([FINISH_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [FINISH_TS]
    , [CREATOR_U_ID]                 AS [CREATOR_U_ID]
    , dateadd(ss, round(([CREATE_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [CREATE_TS]
    , [CHANGER_U_ID]                 AS [CHANGER_U_ID]
    , dateadd(ss, round(([CHANGE_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [CHANGE_TS]
  FROM [SUBMITTED_ENTITY_STATS];
go
CREATE TABLE ARC_SUBMITTED_ENTITY_STATS (
    ID                             decimal (38) NOT NULL
    , [SME_ID]                       decimal (38)     NULL
    , [RERUN_SEQ]                    integer          NULL
    , [SCOPE_ID]                     decimal (38)     NULL
    , [JOB_ESD_ID]                   decimal (38)     NULL
    , [EXIT_CODE]                    integer          NULL
    , [COMMANDLINE]                  varchar(512)     NULL
    , [WORKDIR]                      varchar(512)     NULL
    , [LOGFILE]                      varchar(512)     NULL
    , [ERRLOGFILE]                   varchar(512)     NULL
    , [EXTPID]                       varchar(32)      NULL
    , [SYNC_TS]                      decimal (38)     NULL
    , [RESOURCE_TS]                  decimal (38)     NULL
    , [RUNNABLE_TS]                  decimal (38)     NULL
    , [START_TS]                     decimal (38)     NULL
    , [FINISH_TS]                    decimal (38)     NULL
    , [CREATOR_U_ID]                 decimal (38)     NULL
    , [CREATE_TS]                    decimal (38)     NULL
    , [CHANGER_U_ID]                 decimal (38)     NULL
    , [CHANGE_TS]                    decimal (38)     NULL
);
go
