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

CREATE TABLE [RESOURCE_REQUIREMENT] (
    [ID]                           decimal (38) NOT NULL
    , [NR_ID]                        decimal (38)    NOT NULL
    , [SE_ID]                        decimal (38)    NOT NULL
    , [AMOUNT]                       integer             NULL
    , [KEEP_MODE]                    integer         NOT NULL
    , [IS_STICKY]                    integer         NOT NULL
    , [STICKY_NAME]                  varchar(64)         NULL
    , [STICKY_PARENT]                decimal (38)        NULL
    , [RSMP_ID]                      decimal (38)        NULL
    , [EXPIRED_AMOUNT]               integer             NULL
    , [EXPIRED_BASE]                 integer             NULL
    , [IGNORE_ON_RERUN]              integer         NOT NULL
    , [LOCKMODE]                     integer             NULL
    , [CONDITION]                    varchar(1024)       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_RESOURCE_REQUIREMENT
ON [RESOURCE_REQUIREMENT]([ID]);
go
CREATE VIEW SCI_C_RESOURCE_REQUIREMENT AS
SELECT 
    ID 
    , [NR_ID]                        AS [NR_ID]
    , [SE_ID]                        AS [SE_ID]
    , [AMOUNT]                       AS [AMOUNT]
    , CASE [KEEP_MODE] WHEN 0 THEN 'NOKEEP' WHEN 1 THEN 'KEEP' WHEN 2 THEN 'KEEP_FINAL' END AS [KEEP_MODE]
    , CASE [IS_STICKY] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_STICKY]
    , [STICKY_NAME]                  AS [STICKY_NAME]
    , [STICKY_PARENT]                AS [STICKY_PARENT]
    , [RSMP_ID]                      AS [RSMP_ID]
    , [EXPIRED_AMOUNT]               AS [EXPIRED_AMOUNT]
    , CASE [EXPIRED_BASE] WHEN 0 THEN 'MINUTE' WHEN 1 THEN 'HOUR' WHEN 2 THEN 'DAY' WHEN 3 THEN 'WEEK' WHEN 4 THEN 'MONTH' WHEN 5 THEN 'YEAR' END AS [EXPIRED_BASE]
    , CASE [IGNORE_ON_RERUN] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IGNORE_ON_RERUN]
    , CASE [LOCKMODE] WHEN 255 THEN 'N' WHEN 0 THEN 'X' WHEN 2 THEN 'SX' WHEN 4 THEN 'S' WHEN 6 THEN 'SC' END AS [LOCKMODE]
    , [CONDITION]                    AS [CONDITION]
    , [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 [RESOURCE_REQUIREMENT]
 WHERE VALID_TO = 9223372036854775807;
go
CREATE VIEW SCI_V_RESOURCE_REQUIREMENT AS
SELECT 
    ID 
    , [NR_ID]                        AS [NR_ID]
    , [SE_ID]                        AS [SE_ID]
    , [AMOUNT]                       AS [AMOUNT]
    , CASE [KEEP_MODE] WHEN 0 THEN 'NOKEEP' WHEN 1 THEN 'KEEP' WHEN 2 THEN 'KEEP_FINAL' END AS [KEEP_MODE]
    , CASE [IS_STICKY] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_STICKY]
    , [STICKY_NAME]                  AS [STICKY_NAME]
    , [STICKY_PARENT]                AS [STICKY_PARENT]
    , [RSMP_ID]                      AS [RSMP_ID]
    , [EXPIRED_AMOUNT]               AS [EXPIRED_AMOUNT]
    , CASE [EXPIRED_BASE] WHEN 0 THEN 'MINUTE' WHEN 1 THEN 'HOUR' WHEN 2 THEN 'DAY' WHEN 3 THEN 'WEEK' WHEN 4 THEN 'MONTH' WHEN 5 THEN 'YEAR' END AS [EXPIRED_BASE]
    , CASE [IGNORE_ON_RERUN] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IGNORE_ON_RERUN]
    , CASE [LOCKMODE] WHEN 255 THEN 'N' WHEN 0 THEN 'X' WHEN 2 THEN 'SX' WHEN 4 THEN 'S' WHEN 6 THEN 'SC' END AS [LOCKMODE]
    , [CONDITION]                    AS [CONDITION]
    , [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 [RESOURCE_REQUIREMENT];
go
