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

CREATE TABLE [HIERARCHY_INSTANCE] (
    [ID]                           decimal (38) NOT NULL
    , [PARENT_ID]                    decimal (38)    NOT NULL
    , [CHILD_ID]                     decimal (38)    NOT NULL
    , [SH_ID]                        decimal (38)    NOT NULL
    , [NICE]                         integer         NOT NULL
    , [CHILD_ESD_ID]                 decimal (38)        NULL
    , [CHILD_ES_PREFERENCE]          integer             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_HIERARCHY_INSTANCE
ON [HIERARCHY_INSTANCE]([ID]);
go
CREATE VIEW SCI_HIERARCHY_INSTANCE AS
SELECT 
    ID 
    , [PARENT_ID]                    AS [PARENT_ID]
    , [CHILD_ID]                     AS [CHILD_ID]
    , [SH_ID]                        AS [SH_ID]
    , [NICE]                         AS [NICE]
    , [CHILD_ESD_ID]                 AS [CHILD_ESD_ID]
    , [CHILD_ES_PREFERENCE]          AS [CHILD_ES_PREFERENCE]
    , [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 [HIERARCHY_INSTANCE];
go
CREATE TABLE ARC_HIERARCHY_INSTANCE (
    ID                             decimal (38) NOT NULL
    , [PARENT_ID]                    decimal (38)     NULL
    , [CHILD_ID]                     decimal (38)     NULL
    , [SH_ID]                        decimal (38)     NULL
    , [NICE]                         integer          NULL
    , [CHILD_ESD_ID]                 decimal (38)     NULL
    , [CHILD_ES_PREFERENCE]          integer          NULL
    , [SE_VERSION]                   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
