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

CREATE TABLE [OBJECT_EVENT] (
    [ID]                           decimal (38) NOT NULL
    , [TR_ID]                        decimal (38)        NULL
    , [OI_ID]                        decimal (38)    NOT NULL
    , [EVENT_TYPE]                   integer         NOT NULL
    , [SME_ID]                       decimal (38)    NOT NULL
    , [SE_ID]                        decimal (38)    NOT NULL
    , [SUBMIT_TS]                    decimal (38)        NULL
    , [FINAL_TS]                     decimal (38)        NULL
    , [FINAL_ESD_ID]                 decimal (38)        NULL
    , [MAIN_SME_ID]                  decimal (38)        NULL
    , [MAIN_SE_ID]                   decimal (38)        NULL
    , [MAIN_FINAL_TS]                decimal (38)        NULL
    , [MAIN_FINAL_ESD_ID]            decimal (38)        NULL
    , [SE_VERSION]                   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
);
go
CREATE UNIQUE INDEX PK_OBJECT_EVENT
ON [OBJECT_EVENT]([ID]);
go
CREATE VIEW SCI_OBJECT_EVENT AS
SELECT 
    ID 
    , [TR_ID]                        AS [TR_ID]
    , [OI_ID]                        AS [OI_ID]
    , CASE [EVENT_TYPE] WHEN 1 THEN 'CREATE' WHEN 2 THEN 'CHANGE' WHEN 3 THEN 'DELETE' END AS [EVENT_TYPE]
    , [SME_ID]                       AS [SME_ID]
    , [SE_ID]                        AS [SE_ID]
    , dateadd(ss, round(([SUBMIT_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [SUBMIT_TS]
    , dateadd(ss, round(([FINAL_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [FINAL_TS]
    , [FINAL_ESD_ID]                 AS [FINAL_ESD_ID]
    , [MAIN_SME_ID]                  AS [MAIN_SME_ID]
    , [MAIN_SE_ID]                   AS [MAIN_SE_ID]
    , dateadd(ss, round(([MAIN_FINAL_TS] % 1125899906842624)/1000, 0), '1970-01-01') AS [MAIN_FINAL_TS]
    , [MAIN_FINAL_ESD_ID]            AS [MAIN_FINAL_ESD_ID]
    , [SE_VERSION]                   AS [SE_VERSION]
    , [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 [OBJECT_EVENT];
go
