/* =============================================================================== */
/* Generated by KeepTool Database Reverse Engineer          (c) KeepTool GmbH 2006 */
/* SCHEMA: PDW_EXPRESS_STAGE@PDW.IPK-GATERSLEBEN.DE                                */
/* DATE	 :	11.10.2007                                                             */
/* =============================================================================== */

/* xxx please change connect string */
/* CONNECT SYS/<password>@PDW.IPK-GATERSLEBEN.DE */

/* =============================================================================== */
/* Create User                                                                     */
/* =============================================================================== */

-- DROP USER PDW_EXPRESS_STAGE CASCADE;
CREATE USER PDW_EXPRESS_STAGE
  IDENTIFIED BY <password>   /* xxx please change password */
/

ALTER USER PDW_EXPRESS_STAGE QUOTA UNLIMITED ON PDW_EXPRESS_STAGE
/

/* =============================================================================== */
/* Roles                                                                           */
/* =============================================================================== */

CREATE ROLE CKR_REPOS_MANAGER
/

/* =============================================================================== */
/* Grant System and Role Privileges                                                */
/* =============================================================================== */

GRANT CKR_REPOS_MANAGER TO PDW_EXPRESS_STAGE
/

GRANT CONNECT TO PDW_EXPRESS_STAGE
/

GRANT CREATE ANY CONTEXT TO PDW_EXPRESS_STAGE
/

GRANT CREATE CLUSTER TO PDW_EXPRESS_STAGE
/

GRANT CREATE DATABASE LINK TO PDW_EXPRESS_STAGE
/

GRANT CREATE DIMENSION TO PDW_EXPRESS_STAGE
/

GRANT CREATE INDEXTYPE TO PDW_EXPRESS_STAGE
/

GRANT CREATE LIBRARY TO PDW_EXPRESS_STAGE
/

GRANT CREATE MATERIALIZED VIEW TO PDW_EXPRESS_STAGE
/

GRANT CREATE OPERATOR TO PDW_EXPRESS_STAGE
/

GRANT CREATE PROCEDURE TO PDW_EXPRESS_STAGE
/

GRANT CREATE SEQUENCE TO PDW_EXPRESS_STAGE
/

GRANT CREATE SYNONYM TO PDW_EXPRESS_STAGE
/

GRANT CREATE TABLE TO PDW_EXPRESS_STAGE
/

GRANT CREATE TRIGGER TO PDW_EXPRESS_STAGE
/

GRANT CREATE TYPE TO PDW_EXPRESS_STAGE
/

GRANT CREATE VIEW TO PDW_EXPRESS_STAGE
/

/* =============================================================================== */
/* Consumer Group Privileges                                                       */
/* =============================================================================== */

/* =============================================================================== */
/* Grants received from other users                                                */
/* =============================================================================== */

/* xxx please change connect string */
/* CONNECT CTXSYS/<password>@PDW.IPK-GATERSLEBEN.DE */

GRANT EXECUTE
ON CTXSYS.CTX_DDL
TO PDW_EXPRESS_STAGE
/

GRANT EXECUTE
ON CTXSYS.CTX_DOC
TO PDW_EXPRESS_STAGE
/

/* xxx please change connect string */
/* CONNECT PDW_DATA_TEST/<password>@PDW.IPK-GATERSLEBEN.DE */

GRANT REFERENCES
ON PDW_DATA_TEST.T210_SEQUENCE
TO PDW_EXPRESS_STAGE
/

GRANT SELECT
ON PDW_DATA_TEST.T210_SEQUENCE
TO PDW_EXPRESS_STAGE WITH GRANT OPTION 
/

GRANT SELECT
ON PDW_DATA_TEST.T211_SEQ_SCL
TO PDW_EXPRESS_STAGE
/

GRANT SELECT
ON PDW_DATA_TEST.T212_SEQ_CLASS
TO PDW_EXPRESS_STAGE
/

GRANT SELECT
ON PDW_DATA_TEST.T219_SEQ_CULT_ORG
TO PDW_EXPRESS_STAGE
/

GRANT REFERENCES
ON PDW_DATA_TEST.T222_SEQ2SEQ
TO PDW_EXPRESS_STAGE
/

GRANT SELECT
ON PDW_DATA_TEST.T222_SEQ2SEQ
TO PDW_EXPRESS_STAGE WITH GRANT OPTION 
/

GRANT SELECT
ON PDW_DATA_TEST.T223_RELATION_TYPE
TO PDW_EXPRESS_STAGE
/

GRANT SELECT
ON PDW_DATA_TEST.T252_ORGANISM
TO PDW_EXPRESS_STAGE
/

GRANT SELECT
ON PDW_DATA_TEST.T270_FILE
TO PDW_EXPRESS_STAGE
/

GRANT SELECT
ON PDW_DATA_TEST.T290_SIMILARITY_BLAST
TO PDW_EXPRESS_STAGE
/

/* xxx please change connect string */
/* CONNECT REPOS_MANAGER/<password>@PDW.IPK-GATERSLEBEN.DE */

GRANT EXECUTE
ON REPOS_MANAGER.CKMIGFUN
TO PDW_EXPRESS_STAGE
/

GRANT SELECT
ON REPOS_MANAGER."I$RM_ELEMENT_TYPES"
TO PDW_EXPRESS_STAGE
/

GRANT EXECUTE
ON REPOS_MANAGER.JR_SYSTEM_UTIL
TO PDW_EXPRESS_STAGE
/

GRANT EXECUTE
ON REPOS_MANAGER.JR_UTIL
TO PDW_EXPRESS_STAGE
/

GRANT EXECUTE
ON REPOS_MANAGER.SDD_ACC_PRIV_LIST
TO PDW_EXPRESS_STAGE
/

GRANT SELECT
ON REPOS_MANAGER.SDD_DEPENDENCY_TYPES
TO PDW_EXPRESS_STAGE
/

GRANT SELECT
ON REPOS_MANAGER.SDD_GLOBAL_NAMES
TO PDW_EXPRESS_STAGE
/

GRANT EXECUTE
ON REPOS_MANAGER.SDD_SYS_PRIV_LIST
TO PDW_EXPRESS_STAGE
/

/* xxx please change connect string */
/* CONNECT SYS/<password>@PDW.IPK-GATERSLEBEN.DE */

GRANT EXECUTE
ON SYS.DBMS_RLS
TO PDW_EXPRESS_STAGE
/

GRANT EXECUTE
ON SYS.DEFAULT_CONSUMER_GROUP
TO PDW_EXPRESS_STAGE
/

GRANT EXECUTE
ON SYS.LOW_GROUP
TO PDW_EXPRESS_STAGE
/

/* xxx please change connect string */
/* CONNECT PDW_EXPRESS_STAGE/<password>@PDW.IPK-GATERSLEBEN.DE */

/* =============================================================================== */
/* Synonyms                                                                        */
/* =============================================================================== */

/* =============================================================================== */
/* Tables                                                                          */
/* =============================================================================== */

CREATE TABLE EDITING_SESSION_T
(
  ESE_ID NUMBER (38,0) NOT NULL,
  ESE_IMPORTDATE DATE DEFAULT sysdate NOT NULL,
  ESE_SUCCESSFUL CHAR (1) DEFAULT 'n' NOT NULL,
  ESE_DESC VARCHAR2 (4000),
  ESE_SOURCE_DESC VARCHAR2 (4000),
  ESE_USERNAME VARCHAR2 (400),
  ESE_RPL_ID_EXECUTION NUMBER (38,0)
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE EXTERNDB_T
(
  EDB_ID NUMBER (38,0) NOT NULL,
  EDB_NAME VARCHAR2 (400) NOT NULL,
  EDB_URL VARCHAR2 (4000) NOT NULL,
  EDB_DESC VARCHAR2 (4000),
  EDB_SHORTNAME VARCHAR2 (40)
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE OBJECTS_T
(
  OBJ_ID NUMBER (38,0) NOT NULL,
  OBJ_OJT_ID NUMBER (38,0) NOT NULL,
  OBJ_ESE_ID NUMBER (38,0) NOT NULL
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE OBJECT_TYPES_T
(
  OJT_ID NUMBER (38,0) NOT NULL,
  OJT_TCT_ID NUMBER (38,0) NOT NULL,
  OJT_NAME VARCHAR2 (40) NOT NULL,
  OJT_ABBREVIATION VARCHAR2 (3) NOT NULL,
  OJT_DESC VARCHAR2 (4000)
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE OBJ_FILES_T
(
  OFI_ID NUMBER (38,0) NOT NULL,
  OFI_OBJ_ID_FIL NUMBER (38,0) NOT NULL,
  OFI_FILE BFILE NOT NULL
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE OBJ_PROPERTIES_T
(
  OPR_ID NUMBER (38,0) NOT NULL,
  OPR_TRM_ID NUMBER (38,0) NOT NULL,
  OPR_OBJ_ID NUMBER (38,0) NOT NULL,
  OPR_OPR_ID NUMBER (38,0),
  OPR_ESE_ID NUMBER (38,0) NOT NULL,
  OPR_ESE_ID_OBSOLETE NUMBER (38,0)
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE PERMISSIONS_T
(
  OBJ_OBJ_ID NUMBER (38,0) NOT NULL,
  URS_URS_ID NUMBER (38,0) NOT NULL,
  ESE_ESE_ID NUMBER (38,0) NOT NULL
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE REPLACEMENTS_T
(
  RPL_ID NUMBER (38,0) NOT NULL,
  RPL_AUTHOR VARCHAR2 (400) NOT NULL,
  RPL_DESC VARCHAR2 (4000),
  RPL_CREATIONDATE DATE DEFAULT sysdate NOT NULL
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE RESULT_DATA_T
(
  RSD_ID NUMBER (38,0) NOT NULL,
  RSD_OBJ_ID_RST NUMBER (38,0) NOT NULL,
  RSD_OBJ_ID NUMBER (38,0) NOT NULL,
  RSD_VALUE NUMBER (38,18) NOT NULL
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE TERMS_T
(
  TRM_ID NUMBER (38,0) NOT NULL,
  TRM_EDB_ID NUMBER (38,0) DEFAULT 0 NOT NULL,
  TRM_TCT_ID NUMBER (38,0) NOT NULL,
  TRM_VALUE VARCHAR2 (4000) NOT NULL,
  TRM_DESC VARCHAR2 (4000),
  TRM_EDBACC VARCHAR2 (100) DEFAULT 'unreferenced' NOT NULL,
  TRM_IMPORTDATE DATE DEFAULT sysdate NOT NULL,
  TRM_ESE_ID NUMBER (38,0) NOT NULL
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE TRL_TYPE_T
(
  RTY_ID NUMBER (38,0) NOT NULL,
  RTY_NAME VARCHAR2 (40) NOT NULL,
  RTY_DESC VARCHAR2 (4000),
  RTY_SHORT VARCHAR2 (3) NOT NULL
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE TRM_CATEGORIES_T
(
  TCT_ID NUMBER (38,0) NOT NULL,
  TCT_TCT_ID NUMBER (38,0) DEFAULT 0,
  TCT_NAME VARCHAR2 (400) NOT NULL,
  TCT_DESC VARCHAR2 (4000) NOT NULL,
  TCT_MANY NUMBER (1,0) DEFAULT 0 NOT NULL,
  TCT_NOTNULL NUMBER (1,0) DEFAULT 0 NOT NULL
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE TRM_REPLACE_T
(
  TRL_ID NUMBER (38,0) NOT NULL,
  TRL_TRM_ID_SOURCE NUMBER (38,0) NOT NULL,
  TRL_DESC VARCHAR2 (4000),
  TRL_AUTHOR VARCHAR2 (400),
  TRL_RPL_ID NUMBER (38,0) NOT NULL,
  TRL_RTY_ID NUMBER (38,0) NOT NULL
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE TRM_TRL_T
(
  TRM_TRM_ID NUMBER (38,0) NOT NULL,
  TRL_TRL_ID NUMBER (38,0) NOT NULL,
  TRM_ORDER NUMBER (2,0) DEFAULT 0
)
NOCACHE
NOPARALLEL
LOGGING
/

CREATE TABLE USERS_T
(
  URS_ID NUMBER (38,0) NOT NULL,
  URS_NAME VARCHAR2 (40) NOT NULL
)
NOCACHE
NOPARALLEL
LOGGING
/

/* =============================================================================== */
/* Sequences                                                                       */
/* =============================================================================== */

CREATE SEQUENCE EDITING_SESSION_SEQ
  START WITH 0
  INCREMENT BY 1
  MAXVALUE 1E27
  MINVALUE 0
  NOCACHE
  NOCYCLE
  NOORDER
/

CREATE SEQUENCE EXTERNDB_SEQ
  START WITH 0
  INCREMENT BY 1
  MAXVALUE 1E27
  MINVALUE 0
  NOCACHE
  NOCYCLE
  NOORDER
/

CREATE SEQUENCE OBJECTFILE_SEQ
  START WITH 0
  INCREMENT BY 1
  MAXVALUE 1E27
  MINVALUE 0
  NOCACHE
  NOCYCLE
  NOORDER
/

CREATE SEQUENCE OBJECTTYPE_SEQ
  START WITH 0
  INCREMENT BY 1
  MAXVALUE 1E27
  MINVALUE 0
  NOCACHE
  NOCYCLE
  NOORDER
/

CREATE SEQUENCE OBJECT_SEQ
  START WITH 0
  INCREMENT BY 1
  MAXVALUE 1E27
  MINVALUE 0
  NOCACHE
  NOCYCLE
  NOORDER
/

CREATE SEQUENCE OBJ_PROPERTIES_SEQ
  START WITH 0
  INCREMENT BY 1
  MAXVALUE 1E27
  MINVALUE 0
  NOCACHE
  NOCYCLE
  NOORDER
/

CREATE SEQUENCE RESULTDATA_SEQ
  START WITH 0
  INCREMENT BY 1
  MAXVALUE 1E27
  MINVALUE 0
  NOCACHE
  NOCYCLE
  NOORDER
/

CREATE SEQUENCE TERMS_SEQ
  START WITH 0
  INCREMENT BY 1
  MAXVALUE 1E27
  MINVALUE 0
  NOCACHE
  NOCYCLE
  NOORDER
/

CREATE SEQUENCE TRM_CATERGORIES_SEQ
  START WITH 0
  INCREMENT BY 1
  MAXVALUE 1E27
  MINVALUE 0
  NOCACHE
  NOCYCLE
  NOORDER
/

CREATE SEQUENCE TRM_REPLACE_SEQ
  START WITH 0
  INCREMENT BY 1
  MAXVALUE 1E27
  MINVALUE 0
  NOCACHE
  NOCYCLE
  NOORDER
/

CREATE SEQUENCE USER_SEQ
  START WITH 0
  INCREMENT BY 1
  MAXVALUE 1E27
  MINVALUE 0
  NOCACHE
  NOCYCLE
  NOORDER
/

/* =============================================================================== */
/* Views                                                                           */
/* =============================================================================== */

CREATE OR REPLACE FORCE VIEW ALL_OBJECTS_ADD_V
(
  OBJ_ID, OJT_NAME, TCT_NAME, TRM_VALUE, EDB_NAME, TRM_EDBACC, OPR_ISOBSOLETE, 
  TRM_ID, TCT_ID, OBJ_ESE_ID, OJT_ID
)
AS 
(    SELECT
        OBJ_ID
        ,ojt_name
        ,TCT_NAME
        ,TRM_VALUE
        ,edb_name
        ,TRM_EDBACC
        ,nvl2(opr_ese_id_obsolete,1,0) as OPR_ISOBSOLETE
        , trm_id
        , tct_id
        , obj_Ese_id
        , ojt_id
    FROM
        OBJECTS_T
        ,terms_t
        , OBJ_PROPERTIES_T
        , OBJECT_TYPES_T
        , TRM_CATEGORIES_T
        ,EXTERNDB_T
    WHERE
        obj_id=opr_obj_id
        AND trm_id = opr_trm_id
        AND ojt_id = obj_ojt_id
        AND tct_id = trm_tct_id
        AND edb_id(+) = trm_edb_id
)
UNION ALL
 (    SELECT
        obj_id
        , 'RESULTSETS'       AS OJT_NAME
        , 'VALUE_COUNT'      AS TCT_NAME
        , COUNT (*)||'' AS TRM_VALUE
        , NULL               AS EDB_NAME
        , NULL               AS TRM_EDBACC
        , 0                  AS OPR_ISOBSOLETE
        , NULL               AS TRM_ID
        , NULL               AS TCT_ID
        , obj_Ese_id
        , 5 ojt_id
    FROM
        OBJECTS_T
        left outer JOIN RESULT_DATA_T   ON obj_id = rsd_obj_id_rst
    WHERE
        obj_ojt_id = 5 -- ResultSet
    GROUP BY
        obj_id
        ,obj_Ese_id
)
/

CREATE OR REPLACE FORCE VIEW ALL_OBJECTS_V
(
  OBJ_ID, OJT_NAME, TCT_NAME, TRM_VALUE, EDB_NAME, TRM_EDBACC, OPR_ISOBSOLETE, 
  TRM_ID, TCT_ID, OBJ_ESE_ID, OJT_ID, OPR_ESE_ID_OBSOLETE
)
AS 
SELECT
    OBJ_ID
    ,ojt_name
    ,TCT_NAME
    ,TRM_VALUE
    , edb_name
    ,TRM_EDBACC
    ,nvl2(OPR_ESE_ID_OBSOLETE,1,0) as OPR_ISOBSOLETE
    , trm_id
    , tct_id
    , obj_Ese_id
    , ojt_id
    , OPR_ESE_ID_OBSOLETE
FROM
    OBJECTS_T
    ,terms_t
    , OBJ_PROPERTIES_T
    , OBJECT_TYPES_T
    , TRM_CATEGORIES_T
    ,EXTERNDB_T
WHERE
    obj_id=opr_obj_id
    AND trm_id = opr_trm_id
    AND ojt_id = obj_ojt_id
    AND tct_id = trm_tct_id
    AND edb_id(+) = trm_edb_id
/

CREATE OR REPLACE FORCE VIEW ALL_TERMS_V
(
  TCT_NAME, TRM_VALUE, EDB_NAME, TRM_EDBACC, TRM_ID, TCT_ID, EDB_ID, 
  TRM_ESE_ID
)
AS 
SELECT
    --ojt_name
    TCT_NAME
    ,TRM_VALUE
    , edb_name
    ,TRM_EDBACC
    , trm_id
    , tct_id
--    , ojt_id
    , edb_id
    , trm_ese_id
FROM terms_t
join TRM_CATEGORIES_T on tct_id = trm_tct_id
--join OBJECT_TYPES_T on ojt_id = ojt_tct_id
left outer join EXTERNDB_T on edb_id = trm_edb_id
/

CREATE OR REPLACE FORCE VIEW ERR_FOREIGNKEYS_V
(
  TRM_ESE_ID, TCT_NAME, FKVALUE
)
AS 
select trm_ese_id, tct_name, fkValue from (
SELECT
    trm_ese_id
    ,t1.tct_name 
    --, t1.tct_id
    --, substr(t1.tct_name,5,3) AS abbr
     ,ojt_id 
    , to_number(trm_value) as fkValue
FROM
    TRM_CATEGORIES_T t1 
JOIN TRM_CATEGORIES_T t2 ON t1.tct_tct_id = t2.tct_id 
JOIN OBJECT_TYPES_T ON ojt_abbreviation = substr(t1.tct_name,5,3) 
JOIN terms_t ON trm_tct_id = t1.tct_id 
WHERE
    t2.tct_name='FOREIGNKEYS'
) setFK
left outer join (

select obj_ojt_id, obj_id from OBJECTS_T
) existsFK on setFK.ojt_id = existsFK.obj_ojt_id and setFK.fkValue = existsFK.obj_id
where existsFK.obj_id is null
/

CREATE OR REPLACE FORCE VIEW ERR_NOTNULL_V
(
  OJT_NAME, OJT_ID, OBJ_ID, TCT_ID, TCT_NAME, OBJ_ESE_ID
)
AS 
with notNullTct as (
    select tct_id,  tct_name, regexp_substr(substr(thepath,3),'[[:alnum:]]+') as mainCharacteristicsID,tct_notnull
    from (
        select 
        level thelevel,tct_id,  tct_name,  sys_connect_by_path(tct_id,'/') thepath,tct_notnull
        from TRM_CATEGORIES_T
        start with tct_id =0
        connect by prior tct_id = tct_tct_id and tct_id <> 62 and tct_id <> 66
    )
)
select "OJT_NAME","OJT_ID","OBJ_ID","TCT_ID","TCT_NAME","OBJ_ESE_ID" from (
select ojt_name, ojt_id, obj_id,  tct_id , tct_name, obj_ese_id
from notNullTct
join OBJECT_TYPES_T on MAINCHARACTERISTICSID = ojt_tct_id
join objects_t on ojt_id = obj_ojt_id
where tct_notnull = 1
minus
select ojt_name,  obj_ojt_id, obj_id,  trm_tct_id , tct_name, obj_ese_id
from objects_t
join OBJ_PROPERTIES_T on obj_id = opr_obj_id
join OBJECT_TYPES_T on obj_ojt_id = ojt_id
join terms_t on trm_id = opr_trm_id
join TRM_CATEGORIES_T on tct_id = trm_tct_id
)
/

CREATE OR REPLACE FORCE VIEW ERR_NOTUNIQUEIDENTIFIERS_V
(
  TRM_ID, TRM_EDB_ID, TRM_TCT_ID, TRM_VALUE, TRM_DESC, TRM_EDBACC, 
  TRM_IMPORTDATE, TRM_ESE_ID
)
AS 
SELECT
       "TRM_ID","TRM_EDB_ID","TRM_TCT_ID","TRM_VALUE","TRM_DESC","TRM_EDBACC","TRM_IMPORTDATE","TRM_ESE_ID"
    FROM
        terms_t trm
    WHERE
        trm_tct_id = (select tct_id from TRM_CATEGORIES_T where tct_name='OBJ_Identifier' )
    and
        trm.rowid NOT IN ( SELECT
                            MIN (rowid)
                        FROM
                            terms_t trmIn
                        WHERE
                            trm_tct_id = (select tct_id from TRM_CATEGORIES_T where tct_name='OBJ_Identifier' )
                        group by trm_value)
/

CREATE OR REPLACE FORCE VIEW SEQUENCE_MAPPING_V
(
  BIOSEQUENCE, MATCHSEQUENCE, TREE_LEVEL
)
AS 
select A1.t210_seq_id biosequence, A2.t210_seq_id matchsequence, tree_level from
  (  select regexp_substr(sys_connect_by_path(t222_t210_id_2, '/'), '\d+') tree_root, t222_t210_id_1 tree_node, level tree_level  from pdw_data_test.t222_seq2seq
    where connect_by_iscycle =0
    start with t222_t210_id_2 in (select t210_id from pdw_data_test.t210_sequence
                      join terms_t on t210_seq_id = trm_value)
    connect by nocycle prior t222_t210_id_1 = t222_t210_id_2  ) B
    join pdw_data_test.T210_SEQUENCE A1 on A1.t210_id = B.tree_root
    join pdw_data_test.T210_SEQUENCE A2 on A2.t210_id = B.tree_node 
/

CREATE OR REPLACE FORCE VIEW USR_PUBLIC_ARRAYS_V
(
  ARY_ID, ARY_IDENTIFIER, ARY_NAME, ARY_DESCRIPTION, ARRAYTECHNOLOGY_ID, 
  ARRAYMANUFACTURER_ID
)
AS 
SELECT
    OBJ_ID                                                               AS ARY_ID
    ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS ARY_Identifier
    ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL))                       AS ARY_Name
    ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL))                AS ARY_Description
    ,MAX(decode(TCT_NAME,'ArrayTechnology',TRM_ID,NULL))                   AS ArrayTechnology_ID
    ,MAX(decode(TCT_NAME,'ArrayManufacturer',TRM_ID,NULL))                 AS ArrayManufacturer_ID
FROM
    PDW_EXPRESS_STAGE.ALL_OBJECTS_V
JOIN PERMISSIONS_T on obj_obj_id=obj_id
JOIN USERS_T on urs_id=urs_urs_id
WHERE
    OJT_NAME = 'ARRAYS'
    AND OPR_ESE_ID_OBSOLETE IS NULL
    AND urs_name='public'
GROUP BY
    obj_id 
/

CREATE OR REPLACE FORCE VIEW USR_PUBLIC_BIOMATERIALS_V
(
  MAT_ID, MAT_IDENTIFIER, MAT_NAME, MAT_DESCRIPTION
)
AS 
SELECT
    OBJ_ID                                                               AS MAT_ID
    ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS MAT_Identifier
    ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL))                       AS MAT_Name
    ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL))                AS MAT_Description
FROM
    PDW_EXPRESS_STAGE.ALL_OBJECTS_V
JOIN PERMISSIONS_T on obj_obj_id=obj_id
JOIN USERS_T on urs_id=urs_urs_id
WHERE
    OJT_NAME = 'BIOMATERIALS'
    AND OPR_ESE_ID_OBSOLETE IS NULL
    AND urs_name='public'
GROUP BY
    obj_id
/

CREATE OR REPLACE FORCE VIEW USR_PUBLIC_BIOSEQUENCES_V
(
  SEQ_ID, SEQ_IDENTIFIER, SEQ_NAME, SEQ_DESCRIPTION, STRANDTYPE_ID, 
  CONTROLTYPE_ID, BIOSEQUENCETYPE_ID, SEQ_MART_ID, SEQ_MART_ORIGIN
)
AS 
SELECT
    OBJ_ID                                                               AS SEQ_ID
    ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS SEQ_Identifier
    ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL))                       AS SEQ_Name
    ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL))                AS SEQ_Description
    ,MAX(decode(TCT_NAME,'StrandType',TRM_ID,NULL))                        AS StrandType_ID
    ,MAX(decode(TCT_NAME,'ControlType',TRM_ID,NULL))                       AS ControlType_ID
    ,MAX(decode(TCT_NAME,'BioSequenceType',TRM_ID,NULL))                   AS BioSequenceType_ID
    ,MAX(decode(TCT_NAME,'OBJ_Identifier',TRM_value, NULL))                AS SEQ_MART_ID
    ,MAX(decode(TCT_NAME,'OBJ_IDENTIFIER',EDB_NAME, NULL))                 AS SEQ_MART_ORIGIN
FROM
    PDW_EXPRESS_STAGE.ALL_OBJECTS_V
JOIN PERMISSIONS_T on obj_obj_id=obj_id
JOIN USERS_T on urs_id=urs_urs_id
WHERE
    OJT_NAME = 'BIOSEQUENCES'
    AND OPR_ESE_ID_OBSOLETE IS NULL
    AND urs_name='public'
GROUP BY
    obj_id
/

CREATE OR REPLACE FORCE VIEW USR_PUBLIC_EXPERIMENTS_V
(
  EXP_ID, EXP_IDENTIFIER, EXP_NAME, EXP_DESCRIPTION, EXPERIMENTER_ID, 
  EXPERIMENTDESIGNTYPE_ID, DATASOURCE_ID, QUALITYCONTROLDESCRIPTION_ID, 
  LABORATORY_ID
)
AS 
SELECT
    OBJ_ID                                                               AS EXP_ID
    ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS EXP_Identifier
    ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL))                       AS EXP_Name
    ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL))                AS EXP_Description
    ,MAX(decode(TCT_NAME,'Experimenter',TRM_ID,NULL))                      AS Experimenter_ID
    ,MAX(decode(TCT_NAME,'ExperimentDesignType',TRM_ID,NULL))              AS ExperimentDesignType_ID
    ,MAX(decode(TCT_NAME,'DataSource',TRM_ID,NULL))                        AS DataSource_ID
    ,MAX(decode(TCT_NAME,'QualityControlDescription',TRM_ID,NULL))         AS QualityControlDescription_ID
    ,MAX(decode(TCT_NAME,'Laboratory',TRM_ID,NULL))                        AS Laboratory_ID
FROM
    PDW_EXPRESS_STAGE.ALL_OBJECTS_V
JOIN PERMISSIONS_T on obj_obj_id=obj_id
JOIN USERS_T on urs_id=urs_urs_id
WHERE
    OJT_NAME = 'EXPERIMENTS'
    AND OPR_ESE_ID_OBSOLETE IS NULL
    AND urs_name='public'
GROUP BY
    obj_id
/

CREATE OR REPLACE FORCE VIEW USR_PUBLIC_EXTERNDB_V
(
  EDB_ID, EDB_NAME, EDB_URL, EDB_DESC, EDB_SHORTNAME
)
AS 
SELECT
    EDB_ID
    , EDB_NAME
    , EDB_URL
    , EDB_DESC
    , EDB_SHORTNAME
FROM
    PDW_EXPRESS_STAGE.EXTERNDB_T
    join terms_t on edb_id=trm_edb_id
    join obj_properties_t on opr_trm_id=trm_id
    join objects_t on obj_id=opr_obj_id
    JOIN PERMISSIONS_T on obj_obj_id=obj_id
    JOIN USERS_T on urs_id=urs_urs_id
WHERE URS_NAME = 'public'
/

CREATE OR REPLACE FORCE VIEW USR_PUBLIC_FILES_V
(
  FIL_ID, FIL_IDENTIFIER, FIL_NAME, FIL_DESCRIPTION, FILENAME_ID
)
AS 
SELECT
    OBJ_ID                                                               AS FIL_ID
    ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS FIL_Identifier
    ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL))                       AS FIL_Name
    ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL))                AS FIL_Description
    ,MAX(decode(TCT_NAME,'FileName',TRM_ID,NULL))                          AS FileName_ID
FROM
    PDW_EXPRESS_STAGE.ALL_OBJECTS_V
    JOIN PERMISSIONS_T on obj_obj_id=obj_id
    JOIN USERS_T on urs_id=urs_urs_id
WHERE
    OJT_NAME = 'FILES'
    AND OPR_ESE_ID_OBSOLETE IS NULL
    AND urs_name='public'
GROUP BY
    obj_id
/

CREATE OR REPLACE FORCE VIEW USR_PUBLIC_HYBRIDISATIONS_V
(
  HYB_ID, HYB_IDENTIFIER, HYB_NAME, HYB_DESCRIPTION, HYB_EXP_ID, HYB_ARY_ID, 
  REPLICATEDESCRIPTIONTYPE_ID, REPLICATEINDEX
)
AS 
SELECT
    OBJ_ID                                                               AS HYB_ID
    ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS HYB_Identifier
    ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL))                       AS HYB_Name
    ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL))                AS HYB_Description
    ,MAX(decode(TCT_NAME,'HYB_EXP_ID',to_number(TRM_VALUE),NULL))          AS HYB_EXP_ID
    ,MAX(decode(TCT_NAME,'HYB_ARY_ID',to_number(TRM_VALUE),NULL))          AS HYB_ARY_ID
    ,MAX(decode(TCT_NAME,'ReplicateDescriptionType',TRM_ID,NULL))          AS ReplicateDescriptionType_ID
    ,MAX(decode(TCT_NAME,'ReplicateIndex',TRM_VALUE,NULL))                 AS ReplicateIndex
FROM
    PDW_EXPRESS_STAGE.ALL_OBJECTS_V
    JOIN PERMISSIONS_T on obj_obj_id=obj_id
    JOIN USERS_T on urs_id=urs_urs_id
WHERE
    OJT_NAME = 'HYBRIDISATIONS'
    AND OPR_ESE_ID_OBSOLETE IS NULL
    AND urs_name='public'
GROUP BY
    obj_id
/

CREATE OR REPLACE FORCE VIEW USR_PUBLIC_MAT_TRM_V
(
  TRM_TRM_ID, MAT_MAT_ID
)
AS 
SELECT
    opr_trm_id as trm_trm_id
    , opr_obj_id as mat_mat_id
FROM
    PDW_EXPRESS_STAGE.OBJ_PROPERTIES_T
    JOIN PDW_EXPRESS_STAGE.terms_t ON trm_id=opr_trm_id
    JOIN PDW_EXPRESS_STAGE.TRM_CATEGORIES_T ON tct_id = trm_tct_id
    JOIN PERMISSIONS_T on obj_obj_id=opr_obj_id
    JOIN USERS_T on urs_id=urs_urs_id
WHERE
    OPR_ese_id_OBSOLETE is null
    AND urs_name='public'
    AND tct_id IN ( SELECT
                        tct_id
                    FROM
                        PDW_EXPRESS_STAGE.TRM_CATEGORIES_T
                    WHERE
                        LEVEL >1 CONNECT BY PRIOR tct_id=tct_tct_id START WITH tct_id = (   SELECT
                                                                                                ojt_tct_id
                                                                                            FROM
                                                                                                PDW_EXPRESS_STAGE.OBJECT_TYPES_T
                                                                                            WHERE
                                                                                                ojt_name='BIOMATERIALS'
                        )
    )
/

CREATE OR REPLACE FORCE VIEW USR_PUBLIC_RESULTSETS_V
(
  RST_ID, RST_IDENTIFIER, RST_NAME, RST_DESCRIPTION, RST_FIL_ID, RST_MAT_ID, 
  RST_HYB_ID, SCALE_ID, RESULTSETTYPE_ID, LABELCOMPOUND_ID, 
  NORMALIZATIONDESCTYPE_ID
)
AS 
SELECT
    OBJ_ID                                                               AS RST_ID
    ,MAX(decode(TCT_NAME,'OBJ_Identifier',EDB_NAME||':'|| TRM_VALUE,NULL)) AS RST_Identifier
    ,MAX(decode(TCT_NAME,'OBJ_Name',TRM_VALUE,NULL))                       AS RST_Name
    ,MAX(decode(TCT_NAME,'OBJ_Description',TRM_VALUE,NULL))                AS RST_Description
    ,MAX(decode(TCT_NAME,'RST_FIL_ID',to_number(TRM_VALUE),NULL))          AS RST_FIL_ID
    ,MAX(decode(TCT_NAME,'RST_MAT_ID',to_number(TRM_VALUE),NULL))          AS RST_MAT_ID
    ,MAX(decode(TCT_NAME,'RST_HYB_ID',to_number(TRM_VALUE),NULL))          AS RST_HYB_ID
    ,MAX(decode(TCT_NAME,'Scale',TRM_ID,NULL))                             AS Scale_ID
    ,MAX(decode(TCT_NAME,'ResultSetType',TRM_ID,NULL))                     AS ResultSetType_ID
    ,MAX(decode(TCT_NAME,'LabelCompound',TRM_ID,NULL))                     AS LabelCompound_ID
    ,MAX(decode(TCT_NAME,'NormalizationDescriptionType',TRM_ID,NULL))      AS NormalizationDescType_ID
FROM
    PDW_EXPRESS_STAGE.ALL_OBJECTS_V
    JOIN PERMISSIONS_T on obj_obj_id=obj_id
    JOIN USERS_T on urs_id=urs_urs_id
WHERE
    OJT_NAME = 'RESULTSETS'
    AND OPR_ESE_ID_OBSOLETE IS NULL
    AND urs_name='public'
GROUP BY
    obj_id
/

CREATE OR REPLACE FORCE VIEW USR_PUBLIC_RST_RST_V
(
  RST_RST_ID_CHILD, RST_RST_ID_PARENT
)
AS 
SELECT
    opr_obj_id as rst_rst_id_child
    ,trm_value as rst_rst_id_PARENT
FROM
    PDW_EXPRESS_STAGE.OBJ_PROPERTIES_T
JOIN PDW_EXPRESS_STAGE.terms_t ON trm_id=opr_trm_id
JOIN PDW_EXPRESS_STAGE.TRM_CATEGORIES_T ON tct_id = trm_tct_id
    JOIN PERMISSIONS_T on obj_obj_id=opr_obj_id
    JOIN USERS_T on urs_id=urs_urs_id
WHERE
    OPR_ese_id_OBSOLETE is null
    AND urs_name='public'
    AND tct_id IN ( SELECT
                        tct_id
                    FROM
                        PDW_EXPRESS_STAGE.TRM_CATEGORIES_T
                    WHERE
                        tct_name='RST_RST_ID_PARENT'
                        )
/

CREATE OR REPLACE FORCE VIEW USR_PUBLIC_TERMS_V
(
  TRM_ID, TRM_EDB_ID, TRM_TCT_ID, TRM_VALUE, TRM_DESC, TRM_EDBACC, TCT_NAME, 
  EDB_NAME
)
AS 
SELECT
    TRM_ID
    , TRM_EDB_ID
    , TRM_TCT_ID
    , TRM_VALUE
    , TRM_DESC
    , TRM_EDBACC
    ,tct_name
    ,edb_name
FROM
    PDW_EXPRESS_STAGE.terms_t
join PDW_EXPRESS_STAGE.TRM_CATEGORIES_T on tct_id =trm_tct_id
left outer join PDW_EXPRESS_STAGE.EXTERNDB_T on edb_id = trm_edb_id
    join obj_properties_T on trm_id=opr_trm_id
    JOIN PERMISSIONS_T on obj_obj_id=opr_obj_id
    JOIN USERS_T on urs_id=urs_urs_id
where
-- only Term wich are not obsolete
exists ( select 1  from PDW_EXPRESS_STAGE.OBJ_PROPERTIES_T where opr_trm_id = trm_id and opr_ese_id_obsolete is null)
-- remove all ForeignKeys and ObjectAttributes
and trm_tct_id not in(
    SELECT tct_id
    FROM
        PDW_EXPRESS_STAGE.TRM_CATEGORIES_T
    WHERE
        tct_tct_id =(   SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_name='FOREIGNKEYS' )
    UNION ALL (

    SELECT tct_id
    FROM
        PDW_EXPRESS_STAGE.TRM_CATEGORIES_T
    WHERE
        tct_tct_id =(   SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_name='OBJECT_ATTR' )
    )
)
AND urs_name='public'
/

CREATE OR REPLACE FORCE VIEW USR_PUBLIC_TRM_CATEGORIES_V
(
  TCT_ID, TCT_TCT_ID, TCT_NAME, TCT_DESC
)
AS 
SELECT
    TCT_ID
    , TCT_TCT_ID
    , TCT_NAME
    , TCT_DESC
FROM
    PDW_EXPRESS_STAGE.TRM_CATEGORIES_T

where tct_id not in(
    SELECT tct_id
    FROM
        PDW_EXPRESS_STAGE.TRM_CATEGORIES_T
    WHERE
        tct_tct_id =(   SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_name='FOREIGNKEYS' )
    UNION ALL (

    SELECT tct_id
    FROM
        PDW_EXPRESS_STAGE.TRM_CATEGORIES_T
    WHERE
        tct_tct_id =(   SELECT tct_id FROM PDW_EXPRESS_STAGE.TRM_CATEGORIES_T WHERE tct_name='OBJECT_ATTR' )
    )
)
/

/* =============================================================================== */
/* Comments                                                                        */
/* =============================================================================== */

COMMENT ON COLUMN TRM_REPLACE_T.TRL_AUTHOR IS 
'deprecated'
/

COMMENT ON COLUMN TRM_REPLACE_T.TRL_DESC IS 
'deprecated 
'
/

/* =============================================================================== */
/* Indexes                                                                         */
/* =============================================================================== */

CREATE INDEX EXTERNDB_T_IDX
ON EXTERNDB_T
(
  EDB_ID ,
  EDB_NAME 
)
LOGGING
/

CREATE INDEX IDX_OBJECTS_T2
ON OBJECTS_T
(
  OBJ_ID ,
  OBJ_OJT_ID ,
  OBJ_ESE_ID 
)
LOGGING
/

CREATE INDEX IDX_OBJECT_T1
ON OBJECTS_T
(
  OBJ_OJT_ID 
)
LOGGING
/

CREATE INDEX OBJECTS_T_IDX
ON OBJECTS_T
(
  OBJ_ESE_ID 
)
LOGGING
/

CREATE INDEX IDX_OBJECT_TYPES_T1
ON OBJECT_TYPES_T
(
  OJT_TCT_ID 
)
LOGGING
/

CREATE INDEX OBJECT_TYPES_T_IDX
ON OBJECT_TYPES_T
(
  OJT_ID ,
  OJT_NAME 
)
LOGGING
/

CREATE INDEX IDX_OBJ_FILE1
ON OBJ_FILES_T
(
  OFI_OBJ_ID_FIL 
)
LOGGING
/

CREATE INDEX IDX_OBJ_PROPERTIES_T2
ON OBJ_PROPERTIES_T
(
  OPR_OBJ_ID 
)
LOGGING
/

CREATE INDEX IDX_OBJ_PROPERTIES_T4
ON OBJ_PROPERTIES_T
(
  OPR_OPR_ID 
)
LOGGING
/

CREATE INDEX IDX_OBJ_PROPERTIES_T6
ON OBJ_PROPERTIES_T
(
  OPR_ESE_ID 
)
LOGGING
/

CREATE INDEX IDX_OBJ_PROPERTIES_T7
ON OBJ_PROPERTIES_T
(
  OPR_ESE_ID_OBSOLETE 
)
LOGGING
/

CREATE INDEX IDX_OBJ_PROPERTIES_T8
ON OBJ_PROPERTIES_T
(
  OPR_TRM_ID 
)
LOGGING
/

CREATE INDEX IDX_PERMISSIONS_T1
ON PERMISSIONS_T
(
  URS_URS_ID 
)
LOGGING
/

CREATE INDEX IDX_PERMISSIONS_T2
ON PERMISSIONS_T
(
  ESE_ESE_ID 
)
LOGGING
/

CREATE INDEX IDX_RESULT_DATA_T1
ON RESULT_DATA_T
(
  RSD_OBJ_ID 
)
LOGGING
/

CREATE INDEX IDX_RESULT_DATA_T2
ON RESULT_DATA_T
(
  RSD_OBJ_ID_RST 
)
LOGGING
/

CREATE INDEX IDX_RESULT_DATA_T3
ON RESULT_DATA_T
(
  RSD_ID ,
  RSD_OBJ_ID_RST 
)
LOGGING
/

CREATE INDEX IDX_RESULT_DATA_T5
ON RESULT_DATA_T
(
  RSD_OBJ_ID_RST ,
  RSD_OBJ_ID 
)
LOGGING
/

CREATE INDEX RESULT_DATA_T_IDX6
ON RESULT_DATA_T
(
  RSD_OBJ_ID ,
  RSD_OBJ_ID_RST ,
  RSD_VALUE 
)
LOGGING
/

CREATE INDEX IDX_TERMS_T1
ON TERMS_T
(
  TRM_TCT_ID 
)
LOGGING
/

CREATE INDEX IDX_TERMS_T2
ON TERMS_T
(
  TRM_EDB_ID 
)
LOGGING
/

CREATE INDEX IDX_TERMS_T3
ON TERMS_T
(
  TRM_VALUE 
)
LOGGING
/

CREATE INDEX IDX_TERMS_T4
ON TERMS_T
(
  TRM_ESE_ID 
)
LOGGING
/

CREATE INDEX TERMS_T_IDX
ON TERMS_T
(
  TRM_ID ,
  TRM_EDB_ID ,
  TRM_TCT_ID ,
  TRM_VALUE ,
  TRM_EDBACC 
)
LOGGING
/

CREATE INDEX IDX_TRM_CATEGORIES_T1
ON TRM_CATEGORIES_T
(
  TCT_TCT_ID 
)
LOGGING
/

CREATE INDEX TRM_CATEGORIES_T_IDX
ON TRM_CATEGORIES_T
(
  TCT_ID ,
  TCT_NAME 
)
LOGGING
/

CREATE INDEX IDX_TRM_REPLACE_T1
ON TRM_REPLACE_T
(
  TRL_TRM_ID_SOURCE 
)
LOGGING
/

CREATE INDEX IDX_TRM_TRL_T1
ON TRM_TRL_T
(
  TRL_TRL_ID 
)
LOGGING
/

/* =============================================================================== */
/* Primary Key and Unique Constraints                                              */
/* =============================================================================== */

ALTER TABLE EDITING_SESSION_T ADD
(
  CONSTRAINT EDITING_SESSION_T_UQ
  UNIQUE
  (
    ESE_RPL_ID_EXECUTION
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE EDITING_SESSION_T ADD
(
  PRIMARY KEY
  (
    ESE_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE EXTERNDB_T ADD
(
  CONSTRAINT EXTERNDB_T_UQ
  UNIQUE
  (
    EDB_NAME
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE EXTERNDB_T ADD
(
  CONSTRAINT EXTERNDB_T_UQ2
  UNIQUE
  (
    EDB_SHORTNAME
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE EXTERNDB_T ADD
(
  PRIMARY KEY
  (
    EDB_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE OBJECTS_T ADD
(
  PRIMARY KEY
  (
    OBJ_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE OBJECT_TYPES_T ADD
(
  CONSTRAINT OBJECT_TYPES_T_UQ
  UNIQUE
  (
    OJT_NAME
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE OBJECT_TYPES_T ADD
(
  CONSTRAINT OBJECT_TYPES_T_UQ2
  UNIQUE
  (
    OJT_ABBREVIATION
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE OBJECT_TYPES_T ADD
(
  PRIMARY KEY
  (
    OJT_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE OBJ_FILES_T ADD
(
  PRIMARY KEY
  (
    OFI_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE OBJ_PROPERTIES_T ADD
(
  CONSTRAINT OBJ_PROPERTIES_T_UQ
  UNIQUE
  (
    OPR_OBJ_ID,
    OPR_TRM_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  DISABLE NOVALIDATE
)
/

ALTER TABLE OBJ_PROPERTIES_T ADD
(
  PRIMARY KEY
  (
    OPR_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE PERMISSIONS_T ADD
(
  PRIMARY KEY
  (
    OBJ_OBJ_ID,
    URS_URS_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE REPLACEMENTS_T ADD
(
  PRIMARY KEY
  (
    RPL_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE RESULT_DATA_T ADD
(
  CONSTRAINT IDX_RESULT_DATA_T4
  UNIQUE
  (
    RSD_ID,
    RSD_OBJ_ID
  )
  DEFERRABLE INITIALLY DEFERRED
  USING INDEX 
  ENABLE
)
/

ALTER TABLE RESULT_DATA_T ADD
(
  PRIMARY KEY
  (
    RSD_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE TERMS_T ADD
(
  PRIMARY KEY
  (
    TRM_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE TERMS_T ADD
(
  CONSTRAINT TERMS_T_UQ
  UNIQUE
  (
    TRM_VALUE,
    TRM_EDB_ID,
    TRM_TCT_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE TRL_TYPE_T ADD
(
  PRIMARY KEY
  (
    RTY_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE TRL_TYPE_T ADD
(
  CONSTRAINT TRL_TYPE_UQ
  UNIQUE
  (
    RTY_NAME
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE TRM_CATEGORIES_T ADD
(
  PRIMARY KEY
  (
    TCT_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE TRM_CATEGORIES_T ADD
(
  CONSTRAINT TRM_CATEGORIES_T_UQ
  UNIQUE
  (
    TCT_NAME
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE TRM_CATEGORIES_T ADD
(
  CONSTRAINT UN_TRM_CATEGORIES_1
  UNIQUE
  (
    TCT_NAME,
    TCT_TCT_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE TRM_REPLACE_T ADD
(
  PRIMARY KEY
  (
    TRL_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE TRM_REPLACE_T ADD
(
  CONSTRAINT TRM_REPLACE_T_UQ
  UNIQUE
  (
    TRL_TRM_ID_SOURCE,
    TRL_RPL_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE TRM_TRL_T ADD
(
  PRIMARY KEY
  (
    TRM_TRM_ID,
    TRL_TRL_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

ALTER TABLE USERS_T ADD
(
  PRIMARY KEY
  (
    URS_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  ENABLE
)
/

/* =============================================================================== */
/* Foreign Key Constraints                                                         */
/* =============================================================================== */

ALTER TABLE EDITING_SESSION_T ADD
(
  CONSTRAINT EDITING_SESSION_T_REPLACEME_FK
  FOREIGN KEY
  (
    ESE_RPL_ID_EXECUTION
  )
  REFERENCES REPLACEMENTS_T
  ( 
    RPL_ID
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE OBJECTS_T ADD
(
  CONSTRAINT OBJECTS_T_EDITING_SESSION_T_FK
  FOREIGN KEY
  (
    OBJ_ESE_ID
  )
  REFERENCES EDITING_SESSION_T
  ( 
    ESE_ID
  )
  ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED
  ENABLE
)
/

ALTER TABLE OBJECTS_T ADD
(
  FOREIGN KEY
  (
    OBJ_OJT_ID
  )
  REFERENCES OBJECT_TYPES_T
  ( 
    OJT_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE OBJECT_TYPES_T ADD
(
  FOREIGN KEY
  (
    OJT_TCT_ID
  )
  REFERENCES TRM_CATEGORIES_T
  ( 
    TCT_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE OBJ_FILES_T ADD
(
  FOREIGN KEY
  (
    OFI_OBJ_ID_FIL
  )
  REFERENCES OBJECTS_T
  ( 
    OBJ_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE OBJ_PROPERTIES_T ADD
(
  CONSTRAINT OBJ_PROPERTIES_T_EDITING_SE_FK
  FOREIGN KEY
  (
    OPR_ESE_ID
  )
  REFERENCES EDITING_SESSION_T
  ( 
    ESE_ID
  )
  ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED
  ENABLE
)
/

ALTER TABLE OBJ_PROPERTIES_T ADD
(
  CONSTRAINT OPR_T_ESE_FK_OBSOLOETE
  FOREIGN KEY
  (
    OPR_ESE_ID_OBSOLETE
  )
  REFERENCES EDITING_SESSION_T
  ( 
    ESE_ID
  )
  ON DELETE SET NULL
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE OBJ_PROPERTIES_T ADD
(
  FOREIGN KEY
  (
    OPR_TRM_ID
  )
  REFERENCES TERMS_T
  ( 
    TRM_ID
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE OBJ_PROPERTIES_T ADD
(
  FOREIGN KEY
  (
    OPR_OBJ_ID
  )
  REFERENCES OBJECTS_T
  ( 
    OBJ_ID
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE OBJ_PROPERTIES_T ADD
(
  FOREIGN KEY
  (
    OPR_OPR_ID
  )
  REFERENCES OBJ_PROPERTIES_T
  ( 
    OPR_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE PERMISSIONS_T ADD
(
  FOREIGN KEY
  (
    OBJ_OBJ_ID
  )
  REFERENCES OBJECTS_T
  ( 
    OBJ_ID
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE PERMISSIONS_T ADD
(
  FOREIGN KEY
  (
    URS_URS_ID
  )
  REFERENCES USERS_T
  ( 
    URS_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE PERMISSIONS_T ADD
(
  FOREIGN KEY
  (
    ESE_ESE_ID
  )
  REFERENCES EDITING_SESSION_T
  ( 
    ESE_ID
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE RESULT_DATA_T ADD
(
  FOREIGN KEY
  (
    RSD_OBJ_ID
  )
  REFERENCES OBJECTS_T
  ( 
    OBJ_ID
  )
  DEFERRABLE INITIALLY DEFERRED
  ENABLE
)
/

ALTER TABLE RESULT_DATA_T ADD
(
  FOREIGN KEY
  (
    RSD_OBJ_ID_RST
  )
  REFERENCES OBJECTS_T
  ( 
    OBJ_ID
  )
  ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED
  ENABLE
)
/

ALTER TABLE TERMS_T ADD
(
  FOREIGN KEY
  (
    TRM_TCT_ID
  )
  REFERENCES TRM_CATEGORIES_T
  ( 
    TCT_ID
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE TERMS_T ADD
(
  FOREIGN KEY
  (
    TRM_EDB_ID
  )
  REFERENCES EXTERNDB_T
  ( 
    EDB_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE TERMS_T ADD
(
  CONSTRAINT TERMS_T_EDITING_SESSION_T_FK
  FOREIGN KEY
  (
    TRM_ESE_ID
  )
  REFERENCES EDITING_SESSION_T
  ( 
    ESE_ID
  )
  ON DELETE CASCADE
  DEFERRABLE INITIALLY DEFERRED
  ENABLE
)
/

ALTER TABLE TRM_CATEGORIES_T ADD
(
  FOREIGN KEY
  (
    TCT_TCT_ID
  )
  REFERENCES TRM_CATEGORIES_T
  ( 
    TCT_ID
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE TRM_REPLACE_T ADD
(
  FOREIGN KEY
  (
    TRL_TRM_ID_SOURCE
  )
  REFERENCES TERMS_T
  ( 
    TRM_ID
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE TRM_REPLACE_T ADD
(
  CONSTRAINT TRM_REPLACE_T_REPLACE_T_FK
  FOREIGN KEY
  (
    TRL_RPL_ID
  )
  REFERENCES REPLACEMENTS_T
  ( 
    RPL_ID
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE TRM_REPLACE_T ADD
(
  CONSTRAINT TRM_REPLACE_T_TRL_TYPE_FK
  FOREIGN KEY
  (
    TRL_RTY_ID
  )
  REFERENCES TRL_TYPE_T
  ( 
    RTY_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE TRM_TRL_T ADD
(
  FOREIGN KEY
  (
    TRM_TRM_ID
  )
  REFERENCES TERMS_T
  ( 
    TRM_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE TRM_TRL_T ADD
(
  FOREIGN KEY
  (
    TRL_TRL_ID
  )
  REFERENCES TRM_REPLACE_T
  ( 
    TRL_ID
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

/* =============================================================================== */
/* Check Constraints                                                               */
/* =============================================================================== */

ALTER TABLE EDITING_SESSION_T ADD
(
  CONSTRAINT CC_EDITING_SESSION_T_SUCCESS
  CHECK 
  (
    ese_successful ='n' or ese_successful='y'
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE TERMS_T ADD
(
  CONSTRAINT CH_TERMS_1
  CHECK 
  (
    (trm_edbacc is null and trm_edb_id is null) or (trm_edbacc is not null and trm_edb_id is not null)
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  DISABLE NOVALIDATE
)
/

ALTER TABLE TRM_CATEGORIES_T ADD
(
  CHECK 
  (
    TCT_MANY = 1 or TCT_MANY =0
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

ALTER TABLE TRM_CATEGORIES_T ADD
(
  CHECK 
  (
    TCT_NOTNULL = 1 or TCT_NOTNULL =0
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

/* =============================================================================== */
/* Packages                                                                        */
/* =============================================================================== */

CREATE OR REPLACE 
PACKAGE Curator AS

    procedure executeReplacement (p_rpl_id number, p_ese_id number);



END;
/

CREATE OR REPLACE 
PACKAGE pe_build AS


  procedure rebuildAllViews;

  ---------------------------------------------------------
  -- drops all VIEWS with name OBJECT_NAME like'CV_%_V'
  ----------------------------------------------------------
  procedure dropCVViews;


  ------------------------------------------------------
  -- create VIEWS for terms_t
  -- Name Syntax: CV_<OJT_ABBREVIATION>_<TCT_NAME>_V
  ------------------------------------------------------
  procedure createCVViews;

  --------------------------------------------------
  --  calls first dropCVViews, then createCVViews
  --------------------------------------------------
  procedure rebuildCVViews;

  ------------------------------------------------------
  -- create VIEWS for OBJECTS_T
  -- Name Syntax: OBJ_<OJT_NAME>_V
  ------------------------------------------------------
  procedure createOBJViews;
  procedure dropOBJViews ;
  procedure rebuildOBJViews;


  function getMVQuery(p_ojt_name varchar2, p_plain int default 0) return clob;



END;
/

CREATE OR REPLACE 
PACKAGE pe_constraints AS

  /*
   * Mostly there are two kinds of procedures:
   * - Fist (without parameter) checks whole database
   * - Seconds checks daten of the given editing session
   */

  -----------------------------------------
  -- Checks all contraints listed below
  -----------------------------------------
  procedure checkAllConstraints;
  procedure checkConstraints(p_ese_id number);

  ----------------------------------------
  -- Checks the defined ForeignKeys
  -----------------------------------------
  procedure checkAllForeignKeys;
  procedure checkForeignKeys (p_ese_id number);

  -------------------------------------------
  -- Conrtrols the not null contraints
  -- defined in TRM_CATEGORIES
  ---------------------------------------------
  procedure checkAllNotNullConstraints;
  procedure checkNotNullConstraints (p_ese_id number);

  -------------------------------------------
  -- Checks column RESULT_DATA_T.RSD_OBJ_ID
  -- Controls that there is only one
  -- object_type referencd within a resultset
  -------------------------------------------
  procedure checkOneOjtPerResultSet(p_ese_id number);
  procedure checkOneOjtPerResultSet;

  ------------------------------------------
  -- Checks that there are no duplicate
  -- identiferes. Identifiers are equals if
  -- TRM_VALUE and TRM_EDB_ID are equals
  ------------------------------------------
  procedure checkUniqueIdentifiers(p_ese_id number);
  procedure checkAllUniqueIdentifiers(p_ese_id number);

  ---------------------------------------------
  -- Checks that a identifier is used only one time
  ----------------------------------------------
  procedure checkAllUniqueIdentifierRef;

END;
/

CREATE OR REPLACE 
PACKAGE PE_UTIL AS

    -----------------------------
    -- Deletes data of session.
    -- Uses separate Statements to
    -- avoid using of on 'delete cascade',
    -- because its to slow
    -----------------------------
    procedure deleteEditingSession(p_ese_id number);
END;
/

/* =============================================================================== */
/* Package Bodies                                                                  */
/* =============================================================================== */

CREATE OR REPLACE 
PACKAGE BODY Curator AS

    procedure executeReplacement (p_rpl_id number, p_ese_id number) as

    -- It is possible to store source term without target within
    -- Flag avoids that Terms without target are set to obsolete
    v_targetsFound number  :=0;

    v_opr_parent number := null;

    -- If ==1 then Entries in OBJ_PROPERTIES_T will be grouped using self reference
    v_groupingEnable int :=1;

    -- If ==1 then source relationship will be set to obsolete
    v_substitutionEnable int :=0;

begin

    -- get All Source Terms of replacement
    for rec_Trl in (
        select TRL_ID, TRL_TRM_ID_SOURCE, RTY_SHORT from TRM_REPLACE_T join TRL_TYPE_T on rty_id=trl_rty_id where TRM_REPLACE_T.TRL_RPL_ID =  p_rpl_id    )
    loop

        select case substr(rec_Trl.RTY_SHORT,2,2) when 'G' then 1 else 0 end  into v_groupingEnable from dual;
        select case substr(rec_Trl.RTY_SHORT,1,1) when 'R' then 1 else 0 end  into v_substitutionEnable from dual;

        -- get all Objects which have a relationship to rec_Trl.TRL_TRM_ID_SOURCE
        for rec_Obj in  (
                          select opr_obj_id
                          from OBJ_PROPERTIES_T
                          where opr_trm_id = rec_Trl.TRL_TRM_ID_SOURCE)
        loop

            v_opr_parent:=null; -- discard parent Id, because we have a new Object

            -- for each Object set new relationships to target term(s)
            for rec_Target in (select TRM_TRM_ID from TRM_TRL_T where TRL_TRL_ID = rec_Trl.TRL_ID)
            loop
                begin

                    --DBMS_OUTPUT.put_line('insert into OBJ_PROPERTIES_T (opr_trm_id, opr_ese_id, opr_obj_id,opr_opr_id) values ('||rec_Target.TRM_TRM_ID||','||p_ese_id||','||rec_Obj.opr_obj_id||',' ||v_opr_parent||')');

                    insert into OBJ_PROPERTIES_T (opr_trm_id, opr_ese_id, opr_obj_id,opr_opr_id)
                    values (rec_Target.TRM_TRM_ID,p_ese_id,rec_Obj.opr_obj_id, v_opr_parent);
                exception
                    when others then
                   -- if sqlcode = -00001  then
                        --DBMS_OUTPUT.put_line('doppelt:' || rec_Target.TRM_TRM_ID || ' ' || p_ese_id || ' ' || rec_Obj.opr_obj_id  || ' ' ||  v_opr_parent);
                        -- TODO get opr id of duplicate
                       -- null;
                   -- else
                        --raise_application_error(2200,sqlerrm);--sqlcode
                        DBMS_OUTPUT.put_line('error');
                        raise;
                  --  end if;
                end;

                if v_groupingEnable =1 then
                    -- current opr_id will be parent of the next
                    SELECT OBJ_PROPERTIES_SEQ.currval into v_opr_parent FROM DUAL;
                end if;

                v_targetsFound:=1; --note that there was a targetTerm
            end loop;

        end loop;

        -- Set relationship to TRL_TRM_ID_SOURCE to obsolete
        if v_targetsFound = 1 and v_substitutionEnable =1 then
            update OBJ_PROPERTIES_T
            set opr_ese_id_obsolete = p_ese_id
            where opr_trm_id = rec_Trl.TRL_TRM_ID_SOURCE;
        end if;

        v_targetsFound:=0;

    end loop;

end;



END;
/

CREATE OR REPLACE 
PACKAGE BODY pe_build AS


  procedure rebuildAllViews as
  begin
    rebuildOBJViews;
    rebuildCVViews;
  end;

  ------------------------------------------------------
  -- create VIEWS for terms_t
  ------------------------------------------------------
  procedure createCVViews as
      v_stmt varchar2(32767);
      v_counter number :=0;
  BEGIN

      FOR cur_ojt IN (SELECT
                          OJT_ID
                          , OJT_TCT_ID
                          , OJT_NAME
                          , OJT_ABBREVIATION
                      FROM
                          OBJECT_TYPES_T)
      loop

          for cur_tct in (
              select tct_id, tct_name
              from TRM_CATEGORIES_t
              where level >1
              connect by prior  tct_id = tct_tct_id
              start with TCT_ID =cur_ojt.OJT_TCT_ID
              order by TCT_NAME )
          loop

              v_stmt := 'create view CV_'|| cur_ojt.OJT_ABBREVIATION ||'_'||substr(cur_tct.tct_name,0,21) ||'_V as ' ||
              ' SELECT' ||
              '    TRM_ID' ||
              '    ,TRM_VALUE' ||
              '    ,edb_name ' ||
              ' FROM' ||
              '    terms_t' ||
              '    , EXTERNDB_T' ||
              ' WHERE' ||
              '    trm_tct_id =  ' || cur_tct.tct_id ||
              ' and   edb_id(+) = trm_edb_id';

              dbms_output.put_line(v_stmt);

              execute immediate v_stmt;

              v_counter:=v_counter+1;

          end loop;--cur_tct

      END    loop; --cur_ojt

      DBMS_Output.PUT_LINE(v_counter || ' CV_ views created.');

  END; --createCVViews


  --------------------------------------------------
  --  calls first dropCVViews, then createCVViews
  --------------------------------------------------
  procedure rebuildCVViews as
  begin

    dropCVViews;
    createCVViews;

  end; --rebuildCVViews


  --------------------------------------------------
  --  calls first dropOBJViews, then createOBJViews
  --------------------------------------------------
  procedure rebuildOBJViews as
  begin

    dropOBJViews;
    createOBJViews;

  end; --rebuildOBJViews



  procedure createOBJViews as
    v_stmt varchar (32767);
    v_query varchar (32767);
    v_counter number :=0;
  begin

    for ojt_cur in ( select ojt_id,ojt_name from OBJECT_TYPES_T )
    loop
        v_query := 'SELECT ' ||
                  '	OBJ_ID ' ||
                  '	,TCT_NAME ' ||
                  '	, TRM_VALUE ' ||
                  '	, nvl2(OPR_ESE_ID_OBSOLETE,1,0) as OPR_ISOBSOLETE' ||
                  '	, TRM_ID ' ||
                  '	,TCT_ID ' ||
                  '	,TRM_EDB_ID ' ||
                  '	,OBJ_ESE_ID  ' ||
                  'FROM OBJECTS_T ' ||
                  'JOIN OBJ_PROPERTIES_T ON obj_id = opr_obj_id  ' ||
                  'JOIN OBJECT_TYPES_T ON ojt_id = obj_ojt_id  ' ||
                  'JOIN terms_T ON trm_id = opr_trm_id  ' ||
                  'JOIN TRM_CATEGORIES_T ON tct_id = trm_tct_id  ' ||
                  'WHERE ojt_id = ' || ojt_cur.ojt_id ||
                  'ORDER BY OBJ_ID';

        v_stmt := 'Create view OBJ_'|| ojt_cur.ojt_name ||'_V as '|| v_query;

        dbms_output.put_line(v_stmt);

        execute immediate v_stmt;

        v_counter :=v_counter+1;

    end loop;

    DBMS_Output.PUT_LINE(v_counter || ' OBJ_ views created.');

  end;

  ---------------------------------------------------------
  -- drops all VIEWS with name OBJECT_NAME like'OBJ_%_V'
  ----------------------------------------------------------
  procedure dropOBJViews as
      v_stmt varchar2(32767);
      v_counter number :=0;
  BEGIN

      for cur_OBJECT_NAME in ( select OBJECT_NAME from user_objects
                              where OBJECT_TYPE='VIEW'
                              and  OBJECT_NAME like'OBJ_%_V')
      loop
           execute immediate 'drop view ' || cur_OBJECT_NAME.OBJECT_NAME;
           v_counter:=v_counter+1    ;
      end loop;

      DBMS_Output.PUT_LINE(v_counter || ' views dropped.');


  end; --dropOBJViews


  ---------------------------------------------------------
  -- drops all VIEWS with name OBJECT_NAME like'CV_%_V'
  ----------------------------------------------------------
  procedure dropCVViews as
      v_stmt varchar2(32767);
      v_counter number :=0;
  BEGIN

      for cur_OBJECT_NAME in ( select OBJECT_NAME from user_objects
                              where OBJECT_TYPE='VIEW'
                              and  OBJECT_NAME like'CV_%_V')
      loop
           execute immediate 'drop view ' || cur_OBJECT_NAME.OBJECT_NAME;
           v_counter:=v_counter+1    ;
      end loop;

      DBMS_Output.PUT_LINE(v_counter || ' views dropped.');


  end; --dropCVViews

  ---------------------------------------------------------
  -- returns query for materialized views
  ----------------------------------------------------------
  function getMVQuery(p_ojt_name varchar2, p_plain int default 0) return clob as
    v_value varchar2(50);
    v_ddl clob;
    v_ojt_abbreviation object_types_t.ojt_abbreviation%TYPE;
    v_colHeader varchar2(100);
  BEGIN

      SELECT ojt_abbreviation  into v_ojt_abbreviation FROM object_types_t WHERE ojt_name=p_ojt_name ;

      v_ddl:='SELECT OBJ_ID as ' || v_ojt_abbreviation || '_ID ' ;
--      v_ddl:='SELECT OBJ_ID as ID ' ;

      FOR cur_cat IN (
                      (  SELECT
                              TCT_NAME
                              , TCT_MANY
                              , 'OBJECT_ATTR' as parentCategory
                          FROM
                              TRM_CATEGORIES_T
                          WHERE
                            tct_tct_id =(   SELECT tct_id  FROM TRM_CATEGORIES_T  WHERE tct_name='OBJECT_ATTR')

                       )UNION ALL (

                          SELECT
                              TCT_NAME
                              , TCT_MANY
                              , 'FOREIGNKEYS' as parentCategory
                          FROM
                              TRM_CATEGORIES_T
                          WHERE
                              tct_tct_id =(   SELECT
                                                  tct_id
                                              FROM
                                                  TRM_CATEGORIES_T
                                              WHERE
                                                  tct_name='FOREIGNKEYS'
                              )
                          AND substr(tct_name,0,3) =  v_ojt_abbreviation

                        ) UNION ALL (

                              SELECT
                                  TCT_NAME
                                  , TCT_MANY
                                  , 'Characteristics' as parentCategory
                              FROM
                                  TRM_CATEGORIES_T
                              WHERE
                                  LEVEL =2
                              and p_ojt_name<>'BIOMATERIALS'
                              CONNECT BY PRIOR tct_id=tct_tct_id
                              START WITH tct_id = (   SELECT ojt_tct_id  FROM OBJECT_TYPES_T  WHERE ojt_name=p_ojt_name )
                          )
                      )
       loop

          -- ignore many to many relationship
          if cur_cat.tct_many = 0 then

              -- Define ColumnHeader
              if substr(cur_cat.tct_name,0,4) = 'OBJ_' then
--                v_colHeader:=   substr(cur_cat.tct_name,5);
                 v_colHeader:=   v_ojt_abbreviation ||substr(cur_cat.tct_name,4);

              elsif cur_cat.tct_name='ReplicateIndex' then
                  v_colHeader:='ReplicateIndex';

              elsif  p_plain =0 and cur_cat.tct_name='NormalizationDescriptionType' then
                  v_colHeader:='NormalizationDescType_ID';

              elsif  p_plain =0 and cur_cat.parentCategory ='Characteristics' then
                 v_colHeader:= cur_cat.tct_name || '_ID';

              else
                v_colHeader:= cur_cat.tct_name;

              end if;

              -- Define ColumnValue
              if cur_cat.tct_name = 'OBJ_Identifier' then
                v_value:= 'EDB_NAME||'':''|| TRM_VALUE';

              elsif cur_cat.tct_name='ReplicateIndex' then
                 v_value := 'TRM_VALUE';

              elsif  cur_cat.parentCategory = 'FOREIGNKEYS' then
                 v_value := 'to_number(TRM_VALUE)';

              elsif  p_plain =0 and cur_cat.parentCategory = 'Characteristics' then
                v_value :='TRM_ID';

              else
                  v_value := 'TRM_VALUE';
              end if;


              v_ddl := v_ddl || ' ,max(decode(TCT_NAME,''' || cur_cat.tct_name || ''',' || v_value || ',null)) as ' || v_colHeader;
          end if;
      END  loop;

      v_ddl:=v_ddl || ' from ALL_OBJECTS_V ';
      v_ddl:=v_ddl || ' where OJT_NAME = '''|| p_OJT_NAME ||''' ';
      v_ddl:=v_ddl || ' and OPR_ESE_ID_OBSOLETE is null';
      v_ddl:=v_ddl || ' group by obj_id';

      return v_ddl;

  --    dbms_output.put_line(v_ddl);

  END;






END;
/

CREATE OR REPLACE 
PACKAGE BODY pe_constraints AS




  procedure checkAllForeignKeys as
  begin
      checkForeignKeys(-1);
  end;

  procedure checkForeignKeys (p_ese_id number)as
    v_itemCount integer; -- number items which not exists
  begin

    for fkCat in (
      -- All ForeignKeyCatogories with Abbreviation of referenced Object
      SELECT
          t1.tct_name ,t1.tct_id, substr(t1.tct_name,5,3) as  abbr
      FROM
          TRM_CATEGORIES_T t1
      join TRM_CATEGORIES_T t2 on t1.tct_tct_id = t2.tct_id
      where t2.tct_name='FOREIGNKEYS')
    loop

          DBMS_output.put('Testing ForeignKey '|| fkCat.tct_name ||'... ');
          begin

              select count (*) into v_itemCount from (
                  -- All FK Values
                  select trm_value
                  from terms_t
                  where trm_tct_id=fkCat.tct_id
                  and (trm_ese_id = p_ese_id or p_ese_id = -1)
                  minus
                  -- Existing Object IDs
                  select to_char(obj_id )
                  from objects_t
                  join OBJECT_TYPES_T on ojt_id =obj_ojt_id
                  where ojt_abbreviation=fkCat.abbr
              );

              if v_itemCount = 0 then
                  DBMS_output.put_line('ok.');
              else
                  DBMS_output.put_line('failed. ('||v_itemCount||' objects not found).');
                  raise_application_error(-20000,v_itemCount|| ' value(s) for foreignkey '|| fkCat.tct_name ||' do not exists.');
              end if;

          end;

    end loop;

  end;


  procedure checkAllNotNullConstraints as
  begin
    checkNotNullConstraints(-1);
  end;

  procedure checkNotNullConstraints (p_ese_id number) as
--    p_ese_id number(38):=-1;
    v_helpCount number;
    v_exception number :=0;
  begin

      -- get all OBJECT_TYPES of Session
      for ojt_cur in (    SELECT
                              DISTINCT ojt_id ,ojt_name,ojt_abbreviation,ojt_tct_id
                          FROM
                              Objects_t
                          JOIN object_types_t ON ojt_id =obj_ojt_id
                          WHERE
                              obj_ese_id = p_ese_id or p_ese_id=-1 )
       loop


          -- get all NOT NULL Categories of current Object Type
          for tct_cur in (    SELECT
                                  tct_id,TCT_NAME
                              FROM
                                  TRM_CATEGORIES_T
                              wHERE
                                  LEVEL > 1
                              and tct_notnull = 1
                              AND substr(TCT_NAME,1,3 ) =ojt_cur.ojt_abbreviation
                               CONNECT BY PRIOR tct_id=tct_tct_id START WITH tct_id = (  SELECT
                                                                                              tct_id
                                                                                          FROM
                                                                                              TRM_CATEGORIES_T
                                                                                          WHERE
                                                                                              tct_name='FOREIGNKEYS'
                                                                                          )
                              union
                              SELECT
                                  tct_id,TCT_NAME
                              FROM
                                  TRM_CATEGORIES_T
                              wHERE
                                  LEVEL > 1
                              and tct_notnull = 1
                               CONNECT BY PRIOR tct_id=tct_tct_id START WITH tct_id in (  SELECT
                                                                                              tct_id
                                                                                          FROM
                                                                                              TRM_CATEGORIES_T
                                                                                          WHERE
                                                                                             tct_id=ojt_cur.ojt_tct_id
                                                                                          or tct_name='OBJECT_ATTR'
                                                                                          )
                              )

          loop

              dbms_output.put('Checking Not Null '||ojt_cur.ojt_name ||'.'|| tct_cur.tct_name ||'...');

              -- check if Keys are not null
              select count (*) into v_helpCount from (
              SELECT
                  distinct obj_id
              FROM
                  objects_t
              WHERE
                  obj_ojt_id = ojt_cur.ojt_id
              AND
                  ( obj_ese_id = p_ese_id or p_ese_id =-1)

              minus

              SELECT
                  distinct opr_obj_id
              FROM
                  TRM_CATEGORIES_T
              JOIN terms_t ON tct_id =trm_tct_id
              JOIN OBJ_PROPERTIES_T ON trm_id=opr_trm_id
              WHERE
                  tct_id = tct_cur.tct_id
              AND
                  OPR_ESE_ID_OBSOLETE IS NULL
              );

              if (v_helpCount =0) then
                  dbms_output.put_line('ok.');
              else
                  v_exception:=1;
                  dbms_output.put_line('failed. Violations:'|| v_helpCount);
              end if;

          end loop;--tct_cur

       end loop;--ojt_cur


       if v_exception=1 then
          raise_application_error(-20001,'Not NULL Contraints violated.');
       end if;

  end;



  procedure checkOneOjtPerResultSet(p_ese_id number) as
    v_errCnt integer:=0;
  begin

    dbms_output.PUT('Checking that there are no different Objects_Types within one ResultSet. ...');

    select count(*) into v_errCnt from (
      select
          rsd_obj_id_rst,count(distinct obj_ojt_id) as ct,obj_ese_id
      from RESULT_DATA_T
      join objects_t obj_seq on obj_seq.obj_id = rsd_obj_id
      join objects_t obj_rst on obj_rst.obj_id = rsd_obj_id_rst
      where obj_rst.obj_ese_id =p_ese_id or p_ese_id=-1
      group by rsd_obj_id_rst,obj_ese_id
      having count(distinct obj_ojt_id)>1
    );

    if v_errCnt>0 then
        dbms_output.PUT_LINE('failed.');
        raise_application_error(-20002,'Different Objects_Types within one ResultSet are not allowed.');
    else
        dbms_output.PUT_LINE('ok.');
    end if;

  end;

  procedure checkOneOjtPerResultSet as
  begin
    checkOneOjtPerResultSet(-1);
  end;

  procedure checkAllConstraints as
  begin
    checkAllForeignKeys();
    checkAllNotNullConstraints();
    checkOneOjtPerResultSet();
    checkAllUniqueIdentifierRef();
  end;

  procedure checkConstraints(p_ese_id number) as
  begin
    checkForeignKeys(p_ese_id);
    checkNotNullConstraints(p_ese_id);
    checkOneOjtPerResultSet(p_ese_id);
    checkAllUniqueIdentifierRef();
  end;

  procedure checkUniqueIdentifiers(p_ese_id number) as
  begin
    checkAllUniqueIdentifiers(-1);
  end;

  procedure checkAllUniqueIdentifiers(p_ese_id number) as
    v_count number;
  begin

    dbms_output.PUT_LINE('Checking for unique OBJ_Identifier.');

    SELECT
        count(trm_value) into v_count
    FROM
        terms_t trm
    WHERE
        trm_tct_id = (select tct_id from TRM_CATEGORIES_T where tct_name='OBJ_Identifier' )
    and (trm_ese_id = p_ese_id or p_ese_id = -1)
    and
        trm.rowid NOT IN ( SELECT
                            MIN (rowid)
                        FROM
                            terms_t trmIn
                        WHERE
                            trm_tct_id = (select tct_id from TRM_CATEGORIES_T where tct_name='OBJ_Identifier' )
                        group by trm_value
        );

    if v_count>0 then
        dbms_output.PUT_LINE('failed.');
        raise_application_error(-20002,'OBJ_Identifier are not unique. See View ERR_NotUniqueIdentifers_V for dedails.');
    else
        dbms_output.PUT_LINE('ok.');
    end if;
  end;


  procedure checkAllUniqueIdentifierRef as
    v_count number;
  begin

     dbms_output.PUT_LINE('Checking for unique OBJ_Identifier Refererence.');

    SELECT
        --opr_trm_id,
        count(opr_obj_id ) into v_count
    FROM
    OBJ_PROPERTIES_T
    where opr_trm_id  in (
            SELECT
                trm_id
            FROM
                TERMS_T
            WHERE
                TRM_TCT_ID = (  SELECT
                                    TCT_ID
                                FROM
                                    TRM_CATEGORIES_T
                                WHERE
                                    tct_name ='OBJ_Identifier'))
    group by opr_trm_id
    having count(opr_obj_id ) > 1;

    if v_count>0 then
        dbms_output.PUT_LINE('failed.');
        raise_application_error(-20002,'OBJ_Identifier Refererence is not unique.');
    else
        dbms_output.PUT_LINE('ok.');
    end if;

  end;


END;
/

CREATE OR REPLACE 
PACKAGE BODY PE_UTIL AS


    procedure deleteEditingSession(p_ese_id number) as
    begin


      dbms_output.PUT(to_char(sysdate,'HH:MM:SS') || ' Deleting OBJ_PROPERTIES_T ...');
      delete from OBJ_PROPERTIES_T where opr_ese_id = p_ese_id ;

      dbms_output.PUT_LINE(' done.');


      dbms_output.PUT_line(to_char(sysdate,'HH:MM:SS') || ' Deleting RESULT_DATA_T ...');
      for all_rst in    ( SELECT
                              obj_id
                          FROM
                              OBJECTS_T
                          WHERE
                              obj_ojt_id = 5
                              AND obj_ese_id = p_ese_id)
      loop
              --dbms_output.PUT_line(to_char(sysdate,'HH:MM:SS') || ' Deleting where RESULT_DATA_T.rsd_obj_id_rst = ' || all_rst.obj_id);
              DELETE FROM RESULT_DATA_T  WHERE rsd_obj_id_rst = all_rst.obj_id;
      end loop;
      dbms_output.PUT_LINE(' done.');


      dbms_output.PUT(to_char(sysdate,'HH:MM:SS') || ' Deleting OBJECTS_T ...');
      delete from OBJECTS_T where obj_ese_id = p_ese_id;
      dbms_output.PUT_LINE(' done.');


      dbms_output.PUT(to_char(sysdate,'HH:MM:SS') || ' Deleting terms_t ...');
      delete from terms_t where trm_ese_id = p_ese_id;
      dbms_output.PUT_LINE(' done.');


      dbms_output.PUT(to_char(sysdate,'HH:MM:SS') || ' Deleting EDITING_SESSION_T ...');
      delete from EDITING_SESSION_T where ese_id = p_ese_id;
      dbms_output.PUT_LINE(' done.');


    end;


END;
/

/* =============================================================================== */
/* Triggers                                                                        */
/* =============================================================================== */

CREATE OR REPLACE TRIGGER Trigger_EDITING_SESSION_T_1
BEFORE INSERT ON EDITING_SESSION_T
FOR EACH ROW
BEGIN
  SELECT EDITING_SESSION_SEQ.NEXTVAL
  INTO :NEW.ESE_ID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER BI_EXTERNDB_T 
BEFORE INSERT ON EXTERNDB_T 
FOR EACH ROW
BEGIN

  if :NEW.EDB_ID is null then
    SELECT EXTERNDB_SEQ.NEXTVAL
    INTO :NEW.EDB_ID
    FROM DUAL;
  end if;
END;
/

CREATE OR REPLACE TRIGGER BI_OBJECT_T
BEFORE INSERT ON OBJECTS_T
FOR EACH ROW
BEGIN
  SELECT OBJECT_SEQ.NEXTVAL
  INTO :NEW.OBJ_ID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER BI_OBJECT_TYPES_T
BEFORE INSERT ON OBJECT_TYPES_T
FOR EACH ROW
BEGIN

  if :NEW.OJT_ID is null then
    SELECT OBJECTTYPE_SEQ.NEXTVAL
    INTO :NEW.OJT_ID
    FROM DUAL;
  end if;
END;
/

CREATE OR REPLACE TRIGGER OBJ_FILE_BIR 
BEFORE  INSERT 
ON OBJ_FILES_T 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW
declare
  v_isfile integer;
BEGIN

  SELECT
      1 into v_isfile
  FROM
      OBJECT_TYPES_T
    JOIN OBJECTS_T
    ON obj_ojt_id = ojt_id
  WHERE
   obj_id = :new.OFI_OBJ_ID_FIL
  and OJT_ABBREVIATION ='FIL';


  exception
  when NO_DATA_FOUND then
     RAISE_APPLICATION_ERROR(-20200,'OFI_OBJ_ID_FIL must be of OBJECT_TYPE File');


END;
/

CREATE OR REPLACE TRIGGER Trigger_OBJ_FILE_1
BEFORE INSERT ON OBJ_FILES_T
FOR EACH ROW
BEGIN
  SELECT OBJECTFILE_SEQ.NEXTVAL
  INTO :NEW.OFI_ID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER BI_OBJ_PROPERTIES_T
BEFORE INSERT ON OBJ_PROPERTIES_T
FOR EACH ROW
BEGIN
  SELECT OBJ_PROPERTIES_SEQ.NEXTVAL
  INTO :NEW.OPR_ID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER BI_REPLACE_T
BEFORE INSERT ON REPLACEMENTS_T
FOR EACH ROW
BEGIN
  SELECT TRM_REPLACE_SEQ.NEXTVAL
  INTO :NEW.RPL_ID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER BI_RESULT_DATA_T
BEFORE INSERT ON RESULT_DATA_T
FOR EACH ROW
BEGIN
  SELECT RESULTDATA_SEQ.NEXTVAL
  INTO :NEW.RSD_ID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER RESULT_DATA_T_BIR 
BEFORE  INSERT 
ON RESULT_DATA_T 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW
declare
  v_isfile integer;
BEGIN

  SELECT
      1 into v_isfile
  FROM OBJECT_TYPES_T
  JOIN OBJECTS_T  ON obj_ojt_id = ojt_id
  WHERE obj_id = :new.RSD_OBJ_ID_RST
  and OJT_ABBREVIATION ='RST';

  exception
  when NO_DATA_FOUND then
     RAISE_APPLICATION_ERROR(-20200,'RSD_OBJ_ID_RST must be of OBJECT_TYPE ResultSet');

END;
/

CREATE OR REPLACE TRIGGER BI_TERMS_T
BEFORE INSERT ON TERMS_T
FOR EACH ROW
BEGIN

  if  :NEW.TRM_ID is null then
    SELECT TERMS_SEQ.NEXTVAL
    INTO :NEW.TRM_ID
    FROM DUAL;
  end if;
END;
/

CREATE OR REPLACE TRIGGER TERMS_T_BIUR 
BEFORE  INSERT OR UPDATE 
ON TERMS_T 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW
BEGIN
  if :new.trm_edb_id is null then
      :new.trm_edb_id :=0;
  end if;
END;
/

CREATE OR REPLACE TRIGGER BI_TRL_TYPE
BEFORE INSERT ON TRL_TYPE_T
FOR EACH ROW
BEGIN
  SELECT TRM_REPLACE_SEQ.NEXTVAL
  INTO :NEW.RTY_ID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER BI_TRM_CATEGORIES_T
BEFORE INSERT ON TRM_CATEGORIES_T
FOR EACH ROW
BEGIN

  if  :NEW.TCT_ID is null then
    SELECT TRM_CATERGORIES_SEQ.NEXTVAL
    INTO :NEW.TCT_ID
    FROM DUAL;
  end if;
END;
/

CREATE OR REPLACE TRIGGER Trigger_TRM_REPLACE_T_1
BEFORE INSERT ON TRM_REPLACE_T
FOR EACH ROW
BEGIN
  SELECT TRM_REPLACE_SEQ.NEXTVAL
  INTO :NEW.TRL_ID
  FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER Trigger_USERS_T_1
BEFORE INSERT ON USERS_T
FOR EACH ROW
BEGIN
  SELECT USER_SEQ.NEXTVAL
  INTO :NEW.URS_ID
  FROM DUAL;
END;
/

/* =============================================================================== */
/* Synonyms                                                                        */
/* =============================================================================== */

CREATE SYNONYM "RM$REPOSITORIES"
FOR REPOS_MANAGER."RM$REPOSITORIES"
/

CREATE SYNONYM TESTSYS
FOR PDW_EXPRESS_STAGE.OBJ_ARRAYS_V
/


