-- 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
    , 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') 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
    , 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
    , 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
);
