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

CREATE TABLE TRIGGER_DEFINITION (
    ID                             NUMBER (38) NOT NULL
    , NAME                           VARCHAR2 (64 CHAR)   NOT NULL
    , FIRE_ID                        NUMBER (38)          NOT NULL
    , OBJECT_TYPE                    NUMBER (38)          NOT NULL
    , SE_ID                          NUMBER (38)          NOT NULL
    , MAIN_SE_ID                     NUMBER (38)              NULL
    , PARENT_SE_ID                   NUMBER (38)              NULL
    , IS_ACTIVE                      NUMBER (1)           NOT NULL
    , IS_INVERSE                     NUMBER (1)           NOT NULL
    , ACTION                         NUMBER (38)          NOT NULL
    , TYPE                           NUMBER (38)          NOT NULL
    , IS_MASTER                      NUMBER (1)           NOT NULL
    , IS_SUSPEND                     NUMBER (1)           NOT NULL
    , IS_CREATE                      NUMBER (1)               NULL
    , IS_CHANGE                      NUMBER (1)               NULL
    , IS_DELETE                      NUMBER (1)               NULL
    , IS_GROUP                       NUMBER (1)               NULL
    , RESUME_AT                      VARCHAR2 (20 CHAR)       NULL
    , RESUME_IN                      NUMBER (38)              NULL
    , RESUME_BASE                    NUMBER (38)              NULL
    , IS_WARN_ON_LIMIT               NUMBER (1)           NOT NULL
    , LIMIT_STATE                    NUMBER (38)              NULL
    , MAX_RETRY                      NUMBER (38)          NOT NULL
    , SUBMIT_OWNER_ID                NUMBER (38)              NULL
    , CONDITION                      VARCHAR2 (1024 CHAR)     NULL
    , CHECK_AMOUNT                   NUMBER (38)              NULL
    , CHECK_BASE                     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
    , VALID_FROM                   NUMBER (38) NOT NULL
    , VALID_TO                     NUMBER (38) NOT NULL
    , PRIMARY KEY (ID, VALID_TO)
);
CREATE OR REPLACE VIEW SCI_C_TRIGGER_DEFINITION AS
SELECT 
    ID 
    , NAME                           AS NAME
    , FIRE_ID                        AS FIRE_ID
    , DECODE (OBJECT_TYPE, 0, 'JOB_DEFINITION', 1, 'RESOURCE', 2, 'NAMED_RESOURCE', 3, 'OBJECT_MONITOR') AS OBJECT_TYPE
    , SE_ID                          AS SE_ID
    , MAIN_SE_ID                     AS MAIN_SE_ID
    , PARENT_SE_ID                   AS PARENT_SE_ID
    , DECODE (IS_ACTIVE, 1, 'TRUE', 0, 'FALSE') AS IS_ACTIVE
    , DECODE (IS_INVERSE, 1, 'TRUE', 0, 'FALSE') AS IS_INVERSE
    , DECODE (ACTION, 0, 'SUBMIT', 1, 'RERUN') AS ACTION
    , DECODE (TYPE, 0, 'IMMEDIATE_LOCAL', 2, 'BEFORE_FINAL', 3, 'AFTER_FINAL', 1, 'IMMEDIATE_MERGE', 4, 'FINISH_CHILD', 5, 'UNTIL_FINISHED', 6, 'UNTIL_FINAL', 7, 'WARNING') AS TYPE
    , DECODE (IS_MASTER, 1, 'MASTER', 0, 'NOMASTER') AS IS_MASTER
    , DECODE (IS_SUSPEND, 1, 'SUSPEND', 0, 'NOSUSPEND') AS IS_SUSPEND
    , DECODE (IS_CREATE, 1, 'TRUE', 0, 'FALSE') AS IS_CREATE
    , DECODE (IS_CHANGE, 1, 'TRUE', 0, 'FALSE') AS IS_CHANGE
    , DECODE (IS_DELETE, 1, 'TRUE', 0, 'FALSE') AS IS_DELETE
    , DECODE (IS_GROUP, 1, 'TRUE', 0, 'FALSE') AS IS_GROUP
    , RESUME_AT                      AS RESUME_AT
    , RESUME_IN                      AS RESUME_IN
    , DECODE (RESUME_BASE, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS RESUME_BASE
    , DECODE (IS_WARN_ON_LIMIT, 1, 'TRUE', 0, 'FALSE') AS IS_WARN_ON_LIMIT
    , LIMIT_STATE                    AS LIMIT_STATE
    , MAX_RETRY                      AS MAX_RETRY
    , SUBMIT_OWNER_ID                AS SUBMIT_OWNER_ID
    , CONDITION                      AS CONDITION
    , CHECK_AMOUNT                   AS CHECK_AMOUNT
    , DECODE (CHECK_BASE, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS CHECK_BASE
    , 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 TRIGGER_DEFINITION
 WHERE VALID_TO = 9223372036854775807;
CREATE OR REPLACE VIEW SCI_V_TRIGGER_DEFINITION AS
SELECT 
    ID 
    , NAME                           AS NAME
    , FIRE_ID                        AS FIRE_ID
    , DECODE (OBJECT_TYPE, 0, 'JOB_DEFINITION', 1, 'RESOURCE', 2, 'NAMED_RESOURCE', 3, 'OBJECT_MONITOR') AS OBJECT_TYPE
    , SE_ID                          AS SE_ID
    , MAIN_SE_ID                     AS MAIN_SE_ID
    , PARENT_SE_ID                   AS PARENT_SE_ID
    , DECODE (IS_ACTIVE, 1, 'TRUE', 0, 'FALSE') AS IS_ACTIVE
    , DECODE (IS_INVERSE, 1, 'TRUE', 0, 'FALSE') AS IS_INVERSE
    , DECODE (ACTION, 0, 'SUBMIT', 1, 'RERUN') AS ACTION
    , DECODE (TYPE, 0, 'IMMEDIATE_LOCAL', 2, 'BEFORE_FINAL', 3, 'AFTER_FINAL', 1, 'IMMEDIATE_MERGE', 4, 'FINISH_CHILD', 5, 'UNTIL_FINISHED', 6, 'UNTIL_FINAL', 7, 'WARNING') AS TYPE
    , DECODE (IS_MASTER, 1, 'MASTER', 0, 'NOMASTER') AS IS_MASTER
    , DECODE (IS_SUSPEND, 1, 'SUSPEND', 0, 'NOSUSPEND') AS IS_SUSPEND
    , DECODE (IS_CREATE, 1, 'TRUE', 0, 'FALSE') AS IS_CREATE
    , DECODE (IS_CHANGE, 1, 'TRUE', 0, 'FALSE') AS IS_CHANGE
    , DECODE (IS_DELETE, 1, 'TRUE', 0, 'FALSE') AS IS_DELETE
    , DECODE (IS_GROUP, 1, 'TRUE', 0, 'FALSE') AS IS_GROUP
    , RESUME_AT                      AS RESUME_AT
    , RESUME_IN                      AS RESUME_IN
    , DECODE (RESUME_BASE, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS RESUME_BASE
    , DECODE (IS_WARN_ON_LIMIT, 1, 'TRUE', 0, 'FALSE') AS IS_WARN_ON_LIMIT
    , LIMIT_STATE                    AS LIMIT_STATE
    , MAX_RETRY                      AS MAX_RETRY
    , SUBMIT_OWNER_ID                AS SUBMIT_OWNER_ID
    , CONDITION                      AS CONDITION
    , CHECK_AMOUNT                   AS CHECK_AMOUNT
    , DECODE (CHECK_BASE, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS CHECK_BASE
    , 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
    , VALID_FROM
    , VALID_TO
  FROM TRIGGER_DEFINITION;
