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

CREATE TABLE [NICE_PROFILE_ENTRY] (
    [ID]                           decimal (38) NOT NULL
    , [NP_ID]                        decimal (38)    NOT NULL
    , [PREFERENCE]                   integer         NOT NULL
    , [FOLDER_ID]                    decimal (38)        NULL
    , [IS_SUSPENDED]                 integer         NOT NULL
    , [RENICE]                       integer         NOT NULL
    , [IS_ACTIVE]                    integer         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_NICE_PROFILE_ENTRY
ON [NICE_PROFILE_ENTRY]([ID]);
go
CREATE VIEW SCI_NICE_PROFILE_ENTRY AS
SELECT 
    ID 
    , [NP_ID]                        AS [NP_ID]
    , [PREFERENCE]                   AS [PREFERENCE]
    , [FOLDER_ID]                    AS [FOLDER_ID]
    , CASE [IS_SUSPENDED] WHEN 0 THEN 'NOSUSPEND' WHEN 1 THEN 'SUSPEND' WHEN 2 THEN 'ADMINSUSPEND' END AS [IS_SUSPENDED]
    , [RENICE]                       AS [RENICE]
    , CASE [IS_ACTIVE] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_ACTIVE]
    , [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 [NICE_PROFILE_ENTRY];
go
