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

CREATE TABLE "SYSTEM_MESSAGE" (
    "ID"                           NUMBER (38) PRIMARY KEY
    , "MSG_TYPE"                     NUMBER (38)          NOT NULL
    , "SME_ID"                       NUMBER (38)          NOT NULL
    , "MASTER_ID"                    NUMBER (38)          NOT NULL
    , "OPERATION"                    NUMBER (38)          NOT NULL
    , "IS_MANDATORY"                 NUMBER (1)           NOT NULL
    , "REQUEST_U_ID"                 NUMBER (38)          NOT NULL
    , "REQUEST_TS"                   NUMBER (38)          NOT NULL
    , "REQUEST_MSG"                  VARCHAR2 (512 CHAR)      NULL
    , "ADDITIONAL_LONG"              NUMBER (38)              NULL
    , "ADDITIONAL_BOOL"              NUMBER (1)               NULL
    , "SECOND_LONG"                  NUMBER (38)              NULL
    , "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_SYSTEM_MESSAGE AS
SELECT 
    ID 
    , DECODE (MSG_TYPE, 1, 'APPROVAL') AS "MSG_TYPE"
    , "SME_ID"                       AS "SME_ID"
    , "MASTER_ID"                    AS "MASTER_ID"
    , DECODE (OPERATION, 1, 'CANCEL', 2, 'RERUN', 3, 'ENABLE', 4, 'SET_STATE', 5, 'IGN_DEPENDENCY', 6, 'IGN_RESOURCE', 7, 'CLONE', 11, 'MODIFY_PARAMETER', 12, 'KILL', 23, 'DISABLE', 13, 'SET_JOB_STATE') AS "OPERATION"
    , DECODE (IS_MANDATORY, 1, 'TRUE', 0, 'FALSE') AS "IS_MANDATORY"
    , "REQUEST_U_ID"                 AS "REQUEST_U_ID"
    , pkg_time.gmt2local("REQUEST_TS") AS "REQUEST_TS"
    , "REQUEST_MSG"                  AS "REQUEST_MSG"
    , "ADDITIONAL_LONG"              AS "ADDITIONAL_LONG"
    , DECODE (ADDITIONAL_BOOL, 1, 'TRUE', 0, 'FALSE') AS "ADDITIONAL_BOOL"
    , "SECOND_LONG"                  AS "SECOND_LONG"
    , "COMMENT"                      AS "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 "SYSTEM_MESSAGE";
