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

CREATE TABLE "DEPENDENCY_INSTANCE" (
    "ID"                           NUMBER (38) PRIMARY KEY
    , "DD_ID"                        NUMBER (38)          NOT NULL
    , "DEPENDENT_ID"                 NUMBER (38)          NOT NULL
    , "DEPENDENT_ID_ORIG"            NUMBER (38)          NOT NULL
    , "DEPENDENCY_OPERATION"         NUMBER (38)          NOT NULL
    , "REQUIRED_ID"                  NUMBER (38)          NOT NULL
    , "REQUIRED_SE_ID"               NUMBER (38)              NULL
    , "STATE"                        NUMBER (38)          NOT NULL
    , "IGNORE"                       NUMBER (38)          NOT NULL
    , "DI_ID_ORIG"                   NUMBER (38)          NOT NULL
    , "SE_VERSION"                   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_DEPENDENCY_INSTANCE AS
SELECT 
    ID 
    , "DD_ID"                        AS "DD_ID"
    , "DEPENDENT_ID"                 AS "DEPENDENT_ID"
    , "DEPENDENT_ID_ORIG"            AS "DEPENDENT_ID_ORIG"
    , DECODE (DEPENDENCY_OPERATION, 1, 'AND', 2, 'OR') AS "DEPENDENCY_OPERATION"
    , "REQUIRED_ID"                  AS "REQUIRED_ID"
    , "REQUIRED_SE_ID"               AS "REQUIRED_SE_ID"
    , DECODE (STATE, 0, 'OPEN', 1, 'FULFILLED', 2, 'FAILED', 3, 'BROKEN', 4, 'DEFERRED', 8, 'CANCELLED') AS "STATE"
    , DECODE (IGNORE, 0, 'NO', 1, 'YES', 2, 'RECURSIVE') AS "IGNORE"
    , "DI_ID_ORIG"                   AS "DI_ID_ORIG"
    , "SE_VERSION"                   AS "SE_VERSION"
    , "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 "DEPENDENCY_INSTANCE";
CREATE TABLE ARC_DEPENDENCY_INSTANCE (
    ID                             NUMBER (38) NOT NULL
    , "DD_ID"                        NUMBER (38)           NULL
    , "DEPENDENT_ID"                 NUMBER (38)           NULL
    , "DEPENDENT_ID_ORIG"            NUMBER (38)           NULL
    , "DEPENDENCY_OPERATION"         NUMBER (38)           NULL
    , "REQUIRED_ID"                  NUMBER (38)           NULL
    , "REQUIRED_SE_ID"               NUMBER (38)           NULL
    , "STATE"                        NUMBER (38)           NULL
    , "IGNORE"                       NUMBER (38)           NULL
    , "DI_ID_ORIG"                   NUMBER (38)           NULL
    , "SE_VERSION"                   NUMBER (38)           NULL
    , "CREATOR_U_ID"                 NUMBER (38)           NULL
    , "CREATE_TS"                    NUMBER (38)           NULL
    , "CHANGER_U_ID"                 NUMBER (38)           NULL
    , "CHANGE_TS"                    NUMBER (38)           NULL
);
