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

CREATE TABLE [PARAMETER_DEFINITION] (
    [ID]                           decimal (38) NOT NULL
    , [SE_ID]                        decimal (38)    NOT NULL
    , [NAME]                         varchar(64)     NOT NULL
    , [TYPE]                         integer         NOT NULL
    , [AGG_FUNCTION]                 integer         NOT NULL
    , [DEFAULTVALUE]                 varchar(256)        NULL
    , [IS_LOCAL]                     integer         NOT NULL
    , [LINK_PD_ID]                   decimal (38)        NULL
    , [EXPORT_NAME]                  varchar(64)         NULL
    , [IS_LONG]                      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
    , [VALID_FROM]                 decimal (38) NOT NULL
    , [VALID_TO]                   decimal (38) NOT NULL
);
go
CREATE INDEX PK_PARAMETER_DEFINITION
ON [PARAMETER_DEFINITION]([ID]);
go
CREATE VIEW SCI_C_PARAMETER_DEFINITION AS
SELECT 
    ID 
    , [SE_ID]                        AS [SE_ID]
    , [NAME]                         AS [NAME]
    , CASE [TYPE] WHEN 10 THEN 'REFERENCE' WHEN 20 THEN 'CHILDREFERENCE' WHEN 30 THEN 'CONSTANT' WHEN 40 THEN 'RESULT' WHEN 50 THEN 'PARAMETER' WHEN 60 THEN 'EXPRESSION' WHEN 70 THEN 'IMPORT' WHEN 80 THEN 'DYNAMIC' WHEN 81 THEN 'DYNAMICVALUE' WHEN 90 THEN 'LOCAL_CONSTANT' WHEN 91 THEN 'RESOURCEREFERENCE' END AS [TYPE]
    , CASE [AGG_FUNCTION] WHEN 0 THEN 'NONE' WHEN 61 THEN 'AVG' WHEN 62 THEN 'COUNT' WHEN 63 THEN 'MIN' WHEN 64 THEN 'MAX' WHEN 65 THEN 'SUM' END AS [AGG_FUNCTION]
    , [DEFAULTVALUE]                 AS [DEFAULTVALUE]
    , CASE [IS_LOCAL] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_LOCAL]
    , [LINK_PD_ID]                   AS [LINK_PD_ID]
    , [EXPORT_NAME]                  AS [EXPORT_NAME]
    , CASE [IS_LONG] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_LONG]
    , [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 [PARAMETER_DEFINITION]
 WHERE VALID_TO = 9223372036854775807;
go
CREATE VIEW SCI_V_PARAMETER_DEFINITION AS
SELECT 
    ID 
    , [SE_ID]                        AS [SE_ID]
    , [NAME]                         AS [NAME]
    , CASE [TYPE] WHEN 10 THEN 'REFERENCE' WHEN 20 THEN 'CHILDREFERENCE' WHEN 30 THEN 'CONSTANT' WHEN 40 THEN 'RESULT' WHEN 50 THEN 'PARAMETER' WHEN 60 THEN 'EXPRESSION' WHEN 70 THEN 'IMPORT' WHEN 80 THEN 'DYNAMIC' WHEN 81 THEN 'DYNAMICVALUE' WHEN 90 THEN 'LOCAL_CONSTANT' WHEN 91 THEN 'RESOURCEREFERENCE' END AS [TYPE]
    , CASE [AGG_FUNCTION] WHEN 0 THEN 'NONE' WHEN 61 THEN 'AVG' WHEN 62 THEN 'COUNT' WHEN 63 THEN 'MIN' WHEN 64 THEN 'MAX' WHEN 65 THEN 'SUM' END AS [AGG_FUNCTION]
    , [DEFAULTVALUE]                 AS [DEFAULTVALUE]
    , CASE [IS_LOCAL] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_LOCAL]
    , [LINK_PD_ID]                   AS [LINK_PD_ID]
    , [EXPORT_NAME]                  AS [EXPORT_NAME]
    , CASE [IS_LONG] WHEN 1 THEN 'TRUE' WHEN 0 THEN 'FALSE' END AS [IS_LONG]
    , [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 [PARAMETER_DEFINITION];
go
