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

CREATE TABLE "AUDIT_TRAIL" (
    "ID"                           NUMBER (38) PRIMARY KEY
    , "USER_ID"                      NUMBER (38)          NOT NULL
    , "TS"                           NUMBER (38)          NOT NULL
    , "TXID"                         NUMBER (38)          NOT NULL
    , "ACTION"                       NUMBER (38)          NOT NULL
    , "OBJECT_TYPE"                  NUMBER (38)          NOT NULL
    , "OBJECT_ID"                    NUMBER (38)          NOT NULL
    , "ORIGIN_ID"                    NUMBER (38)          NOT NULL
    , "IS_SET_WARNING"               NUMBER (1)           NOT NULL
    , "ACTION_INFO"                  VARCHAR2 (1024 CHAR)     NULL
    , "INFO_ID"                      NUMBER (38)              NULL
    , "ACTION_COMMENT"               VARCHAR2 (1024 CHAR)     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_AUDIT_TRAIL AS
SELECT 
    ID 
    , "USER_ID"                      AS "USER_ID"
    , pkg_time.gmt2local("TS") AS "TS"
    , "TXID"                         AS "TXID"
    , DECODE (ACTION, 1, 'RERUN', 2, 'RERUN_RECURSIVE', 3, 'CANCEL', 4, 'SUSPEND', 5, 'RESUME', 6, 'SET_STATE', 7, 'SET_EXIT_STATE', 8, 'IGNORE_DEPENDENCY', 9, 'IGNORE_DEP_RECURSIVE', 10, 'IGNORE_RESOURCE', 11, 'KILL', 12, 'ALTER_RUN_PROGRAM', 13, 'ALTER_RERUN_PROGRAM', 14, 'COMMENT_JOB', 15, 'SUBMITTED', 16, 'TRIGGER_FAILED', 17, 'TRIGGER_SUBMIT', 18, 'JOB_RESTARTABLE', 19, 'CHANGE_PRIORITY', 20, 'RENICE', 21, 'SUBMIT_SUSPENDED', 22, 'IGNORE_NAMED_RESOURCE', 23, 'TIMEOUT', 24, 'SET_RESOURCE_STATE', 25, 'JOB_IN_ERROR', 26, 'CLEAR_WARNING', 27, 'SET_WARNING', 28, 'JOB_UNREACHABLE', 29, 'SET_PARAMETERS', 30, 'DISABLE', 31, 'ENABLE', 32, 'CLONE', 33, 'APPROVE', 34, 'REJECT', 35, 'APPROVAL_REQUEST', 36, 'REVIEW_REQUEST', 37, 'KILL_RECURSIVE') AS "ACTION"
    , DECODE (OBJECT_TYPE, 17, 'JOB') AS "OBJECT_TYPE"
    , "OBJECT_ID"                    AS "OBJECT_ID"
    , "ORIGIN_ID"                    AS "ORIGIN_ID"
    , DECODE (IS_SET_WARNING, 1, 'TRUE', 0, 'FALSE') AS "IS_SET_WARNING"
    , "ACTION_INFO"                  AS "ACTION_INFO"
    , "INFO_ID"                      AS "INFO_ID"
    , "ACTION_COMMENT"               AS "ACTION_COMMENT"
    , "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 "AUDIT_TRAIL";
CREATE TABLE ARC_AUDIT_TRAIL (
    ID                             NUMBER (38) NOT NULL
    , "USER_ID"                      NUMBER (38)           NULL
    , "TS"                           NUMBER (38)           NULL
    , "TXID"                         NUMBER (38)           NULL
    , "ACTION"                       NUMBER (38)           NULL
    , "OBJECT_TYPE"                  NUMBER (38)           NULL
    , "OBJECT_ID"                    NUMBER (38)           NULL
    , "ORIGIN_ID"                    NUMBER (38)           NULL
    , "IS_SET_WARNING"               NUMBER (1)            NULL
    , "ACTION_INFO"                  VARCHAR2 (1024 CHAR)  NULL
    , "INFO_ID"                      NUMBER (38)           NULL
    , "ACTION_COMMENT"               VARCHAR2 (1024 CHAR)  NULL
    , "CREATOR_U_ID"                 NUMBER (38)           NULL
    , "CREATE_TS"                    NUMBER (38)           NULL
    , "CHANGER_U_ID"                 NUMBER (38)           NULL
    , "CHANGE_TS"                    NUMBER (38)           NULL
);
