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

CREATE TABLE [EXIT_STATE] (
    [ID]                           decimal (38) NOT NULL
    , [PREFERENCE]                   integer         NOT NULL
    , [IS_FINAL]                     integer         NOT NULL
    , [IS_RESTARTABLE]               integer         NOT NULL
    , [IS_UNREACHABLE]               integer         NOT NULL
    , [IS_DISABLED]                  integer         NOT NULL
    , [IS_BROKEN]                    integer         NOT NULL
    , [IS_BATCH_DEFAULT]             integer         NOT NULL
    , [IS_DEPENDENCY_DEFAULT]        integer         NOT NULL
    , [ESP_ID]                       decimal (38)    NOT NULL
    , [ESD_ID]                       decimal (38)    NOT 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
    , [VALID_FROM]                 decimal (38) NOT NULL
    , [VALID_TO]                   decimal (38) NOT NULL
);
go
CREATE INDEX PK_EXIT_STATE
ON [EXIT_STATE]([ID]);
go
CREATE VIEW SCI_C_EXIT_STATE AS
SELECT 
    ID 
    , [PREFERENCE]                   AS [PREFERENCE]
    , CASE [IS_FINAL] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_FINAL]
    , CASE [IS_RESTARTABLE] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_RESTARTABLE]
    , CASE [IS_UNREACHABLE] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_UNREACHABLE]
    , CASE [IS_DISABLED] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_DISABLED]
    , CASE [IS_BROKEN] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_BROKEN]
    , CASE [IS_BATCH_DEFAULT] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_BATCH_DEFAULT]
    , CASE [IS_DEPENDENCY_DEFAULT] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_DEPENDENCY_DEFAULT]
    , [ESP_ID]                       AS [ESP_ID]
    , [ESD_ID]                       AS [ESD_ID]
    , [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 [EXIT_STATE]
 WHERE VALID_TO = 9223372036854775807;
go
CREATE VIEW SCI_V_EXIT_STATE AS
SELECT 
    ID 
    , [PREFERENCE]                   AS [PREFERENCE]
    , CASE [IS_FINAL] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_FINAL]
    , CASE [IS_RESTARTABLE] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_RESTARTABLE]
    , CASE [IS_UNREACHABLE] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_UNREACHABLE]
    , CASE [IS_DISABLED] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_DISABLED]
    , CASE [IS_BROKEN] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_BROKEN]
    , CASE [IS_BATCH_DEFAULT] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_BATCH_DEFAULT]
    , CASE [IS_DEPENDENCY_DEFAULT] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_DEPENDENCY_DEFAULT]
    , [ESP_ID]                       AS [ESP_ID]
    , [ESD_ID]                       AS [ESD_ID]
    , [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]
    , VALID_FROM
    , VALID_TO
  FROM [EXIT_STATE];
go
