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

CREATE TABLE "RESSOURCE" (
    "ID"                           NUMBER (38) PRIMARY KEY
    , "NR_ID"                        NUMBER (38)          NOT NULL
    , "SCOPE_ID"                     NUMBER (38)              NULL
    , "MASTER_ID"                    NUMBER (38)              NULL
    , "OWNER_ID"                     NUMBER (38)          NOT NULL
    , "LINK_ID"                      NUMBER (38)              NULL
    , "MANAGER_ID"                   NUMBER (38)              NULL
    , "TAG"                          VARCHAR2 (64 CHAR)       NULL
    , "RSD_ID"                       NUMBER (38)              NULL
    , "RSD_TIME"                     NUMBER (38)              NULL
    , "DEFINED_AMOUNT"               NUMBER (38)              NULL
    , "REQUESTABLE_AMOUNT"           NUMBER (38)              NULL
    , "AMOUNT"                       NUMBER (38)              NULL
    , "FREE_AMOUNT"                  NUMBER (38)              NULL
    , "IS_ONLINE"                    NUMBER (1)               NULL
    , "FACTOR"                       NUMBER                   NULL
    , "TRACE_INTERVAL"               NUMBER (38)              NULL
    , "TRACE_BASE"                   NUMBER (38)              NULL
    , "TRACE_BASE_MULTIPLIER"        NUMBER (38)          NOT NULL
    , "TD0_AVG"                      NUMBER               NOT NULL
    , "TD1_AVG"                      NUMBER               NOT NULL
    , "TD2_AVG"                      NUMBER               NOT NULL
    , "LW_AVG"                       NUMBER               NOT NULL
    , "LAST_EVAL"                    NUMBER (38)          NOT NULL
    , "LAST_WRITE"                   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
);
CREATE OR REPLACE VIEW SCI_RESSOURCE AS
SELECT 
    ID 
    , "NR_ID"                        AS "NR_ID"
    , "SCOPE_ID"                     AS "SCOPE_ID"
    , "MASTER_ID"                    AS "MASTER_ID"
    , "OWNER_ID"                     AS "OWNER_ID"
    , "LINK_ID"                      AS "LINK_ID"
    , "MANAGER_ID"                   AS "MANAGER_ID"
    , "TAG"                          AS "TAG"
    , "RSD_ID"                       AS "RSD_ID"
    , pkg_time.gmt2local("RSD_TIME") AS "RSD_TIME"
    , "DEFINED_AMOUNT"               AS "DEFINED_AMOUNT"
    , "REQUESTABLE_AMOUNT"           AS "REQUESTABLE_AMOUNT"
    , "AMOUNT"                       AS "AMOUNT"
    , "FREE_AMOUNT"                  AS "FREE_AMOUNT"
    , DECODE (IS_ONLINE, 1, 'TRUE', 0, 'FALSE') AS "IS_ONLINE"
    , "FACTOR"                       AS "FACTOR"
    , "TRACE_INTERVAL"               AS "TRACE_INTERVAL"
    , "TRACE_BASE"                   AS "TRACE_BASE"
    , "TRACE_BASE_MULTIPLIER"        AS "TRACE_BASE_MULTIPLIER"
    , "TD0_AVG"                      AS "TD0_AVG"
    , "TD1_AVG"                      AS "TD1_AVG"
    , "TD2_AVG"                      AS "TD2_AVG"
    , "LW_AVG"                       AS "LW_AVG"
    , pkg_time.gmt2local("LAST_EVAL") AS "LAST_EVAL"
    , pkg_time.gmt2local("LAST_WRITE") AS "LAST_WRITE"
    , "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 "RESSOURCE";
