-- 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;
