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

CREATE TABLE "INTERVALL" (
    "ID"                           NUMBER (38) PRIMARY KEY
    , "NAME"                         VARCHAR2 (64 CHAR)   NOT NULL
    , "OWNER_ID"                     NUMBER (38)          NOT NULL
    , "START_TIME"                   NUMBER (38)              NULL
    , "END_TIME"                     NUMBER (38)              NULL
    , "DELAY"                        NUMBER (38)              NULL
    , "BASE_INTERVAL"                NUMBER (38)              NULL
    , "BASE_INTERVAL_MULTIPLIER"     NUMBER (38)              NULL
    , "DURATION"                     NUMBER (38)              NULL
    , "DURATION_MULTIPLIER"          NUMBER (38)              NULL
    , "SYNC_TIME"                    NUMBER (38)          NOT NULL
    , "IS_INVERSE"                   NUMBER (1)           NOT NULL
    , "IS_MERGE"                     NUMBER (1)           NOT NULL
    , "EMBEDDED_INT_ID"              NUMBER (38)              NULL
    , "SE_ID"                        NUMBER (38)              NULL
    , "OBJ_ID"                       NUMBER (38)              NULL
    , "OBJ_TYPE"                     NUMBER (38)              NULL
    , "CREATOR_U_ID"                 NUMBER (38)          NOT NULL
    , "CREATE_TS"                    NUMBER (38)          NOT NULL
    , "CHANGER_U_ID"                 NUMBER (38)          NOT NULL
    , "CHANGE_TS"                    NUMBER (38)          NOT NULL
);
CREATE OR REPLACE VIEW SCI_INTERVALL AS
SELECT 
    ID 
    , "NAME"                         AS "NAME"
    , "OWNER_ID"                     AS "OWNER_ID"
    , pkg_time.gmt2local("START_TIME") AS "START_TIME"
    , pkg_time.gmt2local("END_TIME") AS "END_TIME"
    , "DELAY"                        AS "DELAY"
    , DECODE (BASE_INTERVAL, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS "BASE_INTERVAL"
    , "BASE_INTERVAL_MULTIPLIER"     AS "BASE_INTERVAL_MULTIPLIER"
    , DECODE (DURATION, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS "DURATION"
    , "DURATION_MULTIPLIER"          AS "DURATION_MULTIPLIER"
    , pkg_time.gmt2local("SYNC_TIME") AS "SYNC_TIME"
    , DECODE (IS_INVERSE, 1, 'TRUE', 0, 'FALSE') AS "IS_INVERSE"
    , DECODE (IS_MERGE, 1, 'TRUE', 0, 'FALSE') AS "IS_MERGE"
    , "EMBEDDED_INT_ID"              AS "EMBEDDED_INT_ID"
    , "SE_ID"                        AS "SE_ID"
    , "OBJ_ID"                       AS "OBJ_ID"
    , DECODE (OBJ_TYPE, 25, 'DISTRIBUTION', 8, 'USER', 9, 'JOB_DEFINITION', 11, 'RESOURCE', 15, 'SCOPE', 16, 'TRIGGER', 18, 'EVENT', 19, 'INTERVAL', 20, 'SCHEDULE', 22, 'SCHEDULED_EVENT', 28, 'RESOURCE_TEMPLATE', 88, 'INTERVAL_DISPATCHER') AS "OBJ_TYPE"
    , "CREATOR_U_ID"                 AS "CREATOR_U_ID"
    , pkg_time.gmt2local("CREATE_TS") AS "CREATE_TS"
    , "CHANGER_U_ID"                 AS "CHANGER_U_ID"
    , pkg_time.gmt2local("CHANGE_TS") AS "CHANGE_TS"
  FROM "INTERVALL";
