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

CREATE TABLE "SCHEDULING_ENTITY" (
    "ID"                           NUMBER (38) NOT NULL
    , "NAME"                         VARCHAR2 (64 CHAR)   NOT NULL
    , "FOLDER_ID"                    NUMBER (38)          NOT NULL
    , "OWNER_ID"                     NUMBER (38)          NOT NULL
    , "TYPE"                         NUMBER (38)          NOT NULL
    , "RUN_PROGRAM"                  VARCHAR2 (512 CHAR)      NULL
    , "RERUN_PROGRAM"                VARCHAR2 (512 CHAR)      NULL
    , "KILL_PROGRAM"                 VARCHAR2 (512 CHAR)      NULL
    , "WORKDIR"                      VARCHAR2 (512 CHAR)      NULL
    , "LOGFILE"                      VARCHAR2 (512 CHAR)      NULL
    , "TRUNC_LOG"                    NUMBER (1)               NULL
    , "ERRLOGFILE"                   VARCHAR2 (512 CHAR)      NULL
    , "TRUNC_ERRLOG"                 NUMBER (1)               NULL
    , "EXPECTED_RUNTIME"             NUMBER (38)              NULL
    , "EXPECTED_FINALTIME"           NUMBER (38)              NULL
    , "GET_EXPECTED_RUNTIME"         VARCHAR2 (32 CHAR)       NULL
    , "PRIORITY"                     NUMBER (38)          NOT NULL
    , "MIN_PRIORITY"                 NUMBER (38)              NULL
    , "AGING_AMOUNT"                 NUMBER (38)              NULL
    , "AGING_BASE"                   NUMBER (38)              NULL
    , "SUBMIT_SUSPENDED"             NUMBER (1)           NOT NULL
    , "RESUME_AT"                    VARCHAR2 (20 CHAR)       NULL
    , "RESUME_IN"                    NUMBER (38)              NULL
    , "RESUME_BASE"                  NUMBER (38)              NULL
    , "MASTER_SUBMITTABLE"           NUMBER (1)           NOT NULL
    , "TIMEOUT_AMOUNT"               NUMBER (38)              NULL
    , "TIMEOUT_BASE"                 NUMBER (38)              NULL
    , "TIMEOUT_STATE_ID"             NUMBER (38)              NULL
    , "SAME_NODE"                    NUMBER (1)               NULL
    , "GANG_SCHEDULE"                NUMBER (1)               NULL
    , "DEPENDENCY_OPERATION"         NUMBER (38)          NOT NULL
    , "ESMP_ID"                      NUMBER (38)              NULL
    , "ESP_ID"                       NUMBER (38)              NULL
    , "QA_ID"                        NUMBER (38)              NULL
    , "NE_ID"                        NUMBER (38)              NULL
    , "FP_ID"                        NUMBER (38)              NULL
    , "CANCEL_LEAD_FLAG"             NUMBER (1)           NOT NULL
    , "CANCEL_APPROVAL"              NUMBER (38)          NOT NULL
    , "RERUN_LEAD_FLAG"              NUMBER (1)           NOT NULL
    , "RERUN_APPROVAL"               NUMBER (38)          NOT NULL
    , "ENABLE_LEAD_FLAG"             NUMBER (1)           NOT NULL
    , "ENABLE_APPROVAL"              NUMBER (38)          NOT NULL
    , "SET_STATE_LEAD_FLAG"          NUMBER (1)           NOT NULL
    , "SET_STATE_APPROVAL"           NUMBER (38)          NOT NULL
    , "IGN_DEP_LEAD_FLAG"            NUMBER (1)           NOT NULL
    , "IGN_DEP_APPROVAL"             NUMBER (38)          NOT NULL
    , "IGN_RSS_LEAD_FLAG"            NUMBER (1)           NOT NULL
    , "IGN_RSS_APPROVAL"             NUMBER (38)          NOT NULL
    , "CLONE_LEAD_FLAG"              NUMBER (1)           NOT NULL
    , "CLONE_APPROVAL"               NUMBER (38)          NOT NULL
    , "EDIT_PARM_LEAD_FLAG"          NUMBER (1)           NOT NULL
    , "EDIT_PARM_APPROVAL"           NUMBER (38)          NOT NULL
    , "KILL_LEAD_FLAG"               NUMBER (1)           NOT NULL
    , "KILL_APPROVAL"                NUMBER (38)          NOT NULL
    , "SET_JOB_STATE_LEAD_FLAG"      NUMBER (1)           NOT NULL
    , "SET_JOB_STATE_APPROVAL"       NUMBER (38)          NOT NULL
    , "INHERIT_PRIVS"                NUMBER (38)          NOT 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_SCHEDULING_ENTITY AS
SELECT 
    ID 
    , "NAME"                         AS "NAME"
    , "FOLDER_ID"                    AS "FOLDER_ID"
    , "OWNER_ID"                     AS "OWNER_ID"
    , DECODE (TYPE, 1, 'JOB', 2, 'BATCH', 3, 'MILESTONE') AS "TYPE"
    , "RUN_PROGRAM"                  AS "RUN_PROGRAM"
    , "RERUN_PROGRAM"                AS "RERUN_PROGRAM"
    , "KILL_PROGRAM"                 AS "KILL_PROGRAM"
    , "WORKDIR"                      AS "WORKDIR"
    , "LOGFILE"                      AS "LOGFILE"
    , DECODE (TRUNC_LOG, 0, 'NOTRUNC', 1, 'TRUNC') AS "TRUNC_LOG"
    , "ERRLOGFILE"                   AS "ERRLOGFILE"
    , DECODE (TRUNC_ERRLOG, 0, 'NOTRUNC', 1, 'TRUNC') AS "TRUNC_ERRLOG"
    , "EXPECTED_RUNTIME"             AS "EXPECTED_RUNTIME"
    , "EXPECTED_FINALTIME"           AS "EXPECTED_FINALTIME"
    , "GET_EXPECTED_RUNTIME"         AS "GET_EXPECTED_RUNTIME"
    , "PRIORITY"                     AS "PRIORITY"
    , "MIN_PRIORITY"                 AS "MIN_PRIORITY"
    , "AGING_AMOUNT"                 AS "AGING_AMOUNT"
    , DECODE (AGING_BASE, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS "AGING_BASE"
    , DECODE (SUBMIT_SUSPENDED, 1, 'SUSPEND', 0, 'NOSUSPEND') AS "SUBMIT_SUSPENDED"
    , "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 (MASTER_SUBMITTABLE, 1, 'MASTER', 0, 'NOMASTER') AS "MASTER_SUBMITTABLE"
    , "TIMEOUT_AMOUNT"               AS "TIMEOUT_AMOUNT"
    , DECODE (TIMEOUT_BASE, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS "TIMEOUT_BASE"
    , "TIMEOUT_STATE_ID"             AS "TIMEOUT_STATE_ID"
    , DECODE (DEPENDENCY_OPERATION, 1, 'AND', 2, 'OR') AS "DEPENDENCY_OPERATION"
    , "ESMP_ID"                      AS "ESMP_ID"
    , "ESP_ID"                       AS "ESP_ID"
    , "NE_ID"                        AS "NE_ID"
    , "FP_ID"                        AS "FP_ID"
    , DECODE (CANCEL_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "CANCEL_LEAD_FLAG"
    , DECODE (CANCEL_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "CANCEL_APPROVAL"
    , DECODE (RERUN_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "RERUN_LEAD_FLAG"
    , DECODE (RERUN_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "RERUN_APPROVAL"
    , DECODE (ENABLE_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "ENABLE_LEAD_FLAG"
    , DECODE (ENABLE_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "ENABLE_APPROVAL"
    , DECODE (SET_STATE_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "SET_STATE_LEAD_FLAG"
    , DECODE (SET_STATE_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "SET_STATE_APPROVAL"
    , DECODE (IGN_DEP_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "IGN_DEP_LEAD_FLAG"
    , DECODE (IGN_DEP_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "IGN_DEP_APPROVAL"
    , DECODE (IGN_RSS_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "IGN_RSS_LEAD_FLAG"
    , DECODE (IGN_RSS_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "IGN_RSS_APPROVAL"
    , DECODE (CLONE_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "CLONE_LEAD_FLAG"
    , DECODE (CLONE_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "CLONE_APPROVAL"
    , DECODE (EDIT_PARM_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "EDIT_PARM_LEAD_FLAG"
    , DECODE (EDIT_PARM_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "EDIT_PARM_APPROVAL"
    , DECODE (KILL_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "KILL_LEAD_FLAG"
    , DECODE (KILL_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "KILL_APPROVAL"
    , DECODE (SET_JOB_STATE_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "SET_JOB_STATE_LEAD_FLAG"
    , DECODE (SET_JOB_STATE_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "SET_JOB_STATE_APPROVAL"
    , "INHERIT_PRIVS"                AS "INHERIT_PRIVS"
    , "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 "SCHEDULING_ENTITY"
 WHERE VALID_TO = 9223372036854775807;
CREATE OR REPLACE VIEW SCI_V_SCHEDULING_ENTITY AS
SELECT 
    ID 
    , "NAME"                         AS "NAME"
    , "FOLDER_ID"                    AS "FOLDER_ID"
    , "OWNER_ID"                     AS "OWNER_ID"
    , DECODE (TYPE, 1, 'JOB', 2, 'BATCH', 3, 'MILESTONE') AS "TYPE"
    , "RUN_PROGRAM"                  AS "RUN_PROGRAM"
    , "RERUN_PROGRAM"                AS "RERUN_PROGRAM"
    , "KILL_PROGRAM"                 AS "KILL_PROGRAM"
    , "WORKDIR"                      AS "WORKDIR"
    , "LOGFILE"                      AS "LOGFILE"
    , DECODE (TRUNC_LOG, 0, 'NOTRUNC', 1, 'TRUNC') AS "TRUNC_LOG"
    , "ERRLOGFILE"                   AS "ERRLOGFILE"
    , DECODE (TRUNC_ERRLOG, 0, 'NOTRUNC', 1, 'TRUNC') AS "TRUNC_ERRLOG"
    , "EXPECTED_RUNTIME"             AS "EXPECTED_RUNTIME"
    , "EXPECTED_FINALTIME"           AS "EXPECTED_FINALTIME"
    , "GET_EXPECTED_RUNTIME"         AS "GET_EXPECTED_RUNTIME"
    , "PRIORITY"                     AS "PRIORITY"
    , "MIN_PRIORITY"                 AS "MIN_PRIORITY"
    , "AGING_AMOUNT"                 AS "AGING_AMOUNT"
    , DECODE (AGING_BASE, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS "AGING_BASE"
    , DECODE (SUBMIT_SUSPENDED, 1, 'SUSPEND', 0, 'NOSUSPEND') AS "SUBMIT_SUSPENDED"
    , "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 (MASTER_SUBMITTABLE, 1, 'MASTER', 0, 'NOMASTER') AS "MASTER_SUBMITTABLE"
    , "TIMEOUT_AMOUNT"               AS "TIMEOUT_AMOUNT"
    , DECODE (TIMEOUT_BASE, 0, 'MINUTE', 1, 'HOUR', 2, 'DAY', 3, 'WEEK', 4, 'MONTH', 5, 'YEAR') AS "TIMEOUT_BASE"
    , "TIMEOUT_STATE_ID"             AS "TIMEOUT_STATE_ID"
    , DECODE (DEPENDENCY_OPERATION, 1, 'AND', 2, 'OR') AS "DEPENDENCY_OPERATION"
    , "ESMP_ID"                      AS "ESMP_ID"
    , "ESP_ID"                       AS "ESP_ID"
    , "NE_ID"                        AS "NE_ID"
    , "FP_ID"                        AS "FP_ID"
    , DECODE (CANCEL_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "CANCEL_LEAD_FLAG"
    , DECODE (CANCEL_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "CANCEL_APPROVAL"
    , DECODE (RERUN_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "RERUN_LEAD_FLAG"
    , DECODE (RERUN_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "RERUN_APPROVAL"
    , DECODE (ENABLE_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "ENABLE_LEAD_FLAG"
    , DECODE (ENABLE_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "ENABLE_APPROVAL"
    , DECODE (SET_STATE_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "SET_STATE_LEAD_FLAG"
    , DECODE (SET_STATE_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "SET_STATE_APPROVAL"
    , DECODE (IGN_DEP_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "IGN_DEP_LEAD_FLAG"
    , DECODE (IGN_DEP_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "IGN_DEP_APPROVAL"
    , DECODE (IGN_RSS_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "IGN_RSS_LEAD_FLAG"
    , DECODE (IGN_RSS_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "IGN_RSS_APPROVAL"
    , DECODE (CLONE_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "CLONE_LEAD_FLAG"
    , DECODE (CLONE_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "CLONE_APPROVAL"
    , DECODE (EDIT_PARM_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "EDIT_PARM_LEAD_FLAG"
    , DECODE (EDIT_PARM_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "EDIT_PARM_APPROVAL"
    , DECODE (KILL_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "KILL_LEAD_FLAG"
    , DECODE (KILL_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "KILL_APPROVAL"
    , DECODE (SET_JOB_STATE_LEAD_FLAG, 1, 'TRUE', 0, 'FALSE') AS "SET_JOB_STATE_LEAD_FLAG"
    , DECODE (SET_JOB_STATE_APPROVAL, 0, 'DEFAULT', 1, 'PARENT', 2, 'NO', 5, 'APPROVE', 4, 'REVIEW') AS "SET_JOB_STATE_APPROVAL"
    , "INHERIT_PRIVS"                AS "INHERIT_PRIVS"
    , "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 "SCHEDULING_ENTITY";
