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

CREATE TABLE "POOL" (
    "ID"                           NUMBER (38) PRIMARY KEY
    , "NR_ID"                        NUMBER (38)          NOT NULL
    , "SCOPE_ID"                     NUMBER (38)          NOT NULL
    , "OWNER_ID"                     NUMBER (38)          NOT NULL
    , "MANAGER_ID"                   NUMBER (38)              NULL
    , "DIST_ID"                      NUMBER (38)              NULL
    , "DEFINED_AMOUNT"               NUMBER (38)          NOT NULL
    , "AMOUNT"                       NUMBER (38)          NOT NULL
    , "FREE_AMOUNT"                  NUMBER (38)          NOT NULL
    , "CHILD_ALLOCATED"              NUMBER (38)          NOT NULL
    , "EVALUATION_CYCLE"             NUMBER (38)              NULL
    , "NEXT_EVALUATION_TIME"         NUMBER (38)          NOT NULL
    , "TAG"                          VARCHAR2 (64 CHAR)       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_POOL AS
SELECT 
    ID 
    , "NR_ID"                        AS "NR_ID"
    , "SCOPE_ID"                     AS "SCOPE_ID"
    , "OWNER_ID"                     AS "OWNER_ID"
    , "MANAGER_ID"                   AS "MANAGER_ID"
    , "DIST_ID"                      AS "DIST_ID"
    , "DEFINED_AMOUNT"               AS "DEFINED_AMOUNT"
    , "AMOUNT"                       AS "AMOUNT"
    , "FREE_AMOUNT"                  AS "FREE_AMOUNT"
    , "CHILD_ALLOCATED"              AS "CHILD_ALLOCATED"
    , "EVALUATION_CYCLE"             AS "EVALUATION_CYCLE"
    , "NEXT_EVALUATION_TIME"         AS "NEXT_EVALUATION_TIME"
    , "TAG"                          AS "TAG"
    , "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 "POOL";
