Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\ord\im\admin\ordcrtbl.plb

create or replace type ORDSYS.ORD_STR_LIST as table  of varchar2(2000 CHAR);
/
create or replace type ORDSYS.ORD_NUM_LIST as table  of  integer; 
/
grant execute on ORDSYS.ORD_STR_LIST to public; 
grant execute on ORDSYS.ORD_NUM_LIST to public;
alter session set current_schema=ORDDATA;
CREATE TABLE ORDDATA.ORDDCM_INTERNAL_TAGS (
  TAG              VARCHAR2(8 CHAR) NOT NULL,   
  DEFINER_NAME     VARCHAR2(64 CHAR)  DEFAULT 'DICOM'  NOT NULL,
  CONSTRAINT ORDDCM_INTERNAL_TAGS_PK PRIMARY KEY (TAG, DEFINER_NAME),
  CONSTRAINT ORDDCM_INTERNAL_TAGS_CK CHECK( definer_name in ('DICOM', 'ORACLE'))
);
CREATE TABLE ORDDATA.ORDDCM_DOC_TYPES (
   DOC_TYPE_ID         NUMBER NOT NULL
  ,DOC_TYPE            VARCHAR2(100)  NOT NULL
  ,SCHEMA_URL          VARCHAR2(700)  NOT NULL
  ,DOC_TYPE_DSC        VARCHAR2(4000) NOT NULL
  ,CONSTRAINT DOC_TYPES_PK PRIMARY KEY(DOC_TYPE_ID)
  ,CONSTRAINT DOC_TYPES_UK UNIQUE(DOC_TYPE)
);
CREATE TABLE ORDDATA.ORDDCM_INSTALL_DOCS (
   DOC_TYPE_ID   NUMBER NOT NULL  
  ,DOC_NAME      VARCHAR2(100) NOT NULL
  ,DOC_TYPE      VARCHAR2(100) NOT NULL  
  ,LOAD_ORDER    NUMBER NOT NULL
  ,CONSTRAINT ORDDCM_I_DOCS_PK  PRIMARY KEY (DOC_NAME)
  ,CONSTRAINT ORDDCM_I_DOCS_FK1 FOREIGN KEY (DOC_TYPE_ID)
              REFERENCES ORDDATA.ORDDCM_DOC_TYPES (DOC_TYPE_ID)
  ,CONSTRAINT ORDDCM_I_DOCS_FK2 FOREIGN KEY (DOC_TYPE)
              REFERENCES ORDDATA.ORDDCM_DOC_TYPES (DOC_TYPE)
);
CREATE TABLE ORDDATA.ORDDCM_DOCS (
     DOC_ID           INTEGER   
    ,DOC_NAME         VARCHAR2(100 CHAR) NOT NULL 
    ,DOC_TYPE_ID      NUMBER NOT NULL  
    ,DOC_CONTENT      SYS.XMLTYPE  NOT NULL 
    ,ORACLE_INSTALL   NUMBER(1) DEFAULT 0 NOT NULL
    ,CREATE_DATE      DATE    DEFAULT SYSDATE NOT NULL
    ,CONSTRAINT ORDDCM_DOCS_PK PRIMARY KEY (DOC_ID)
    ,CONSTRAINT ORDDCM_DOCS_UK1 UNIQUE (DOC_NAME)
    ,CONSTRAINT ORDDCM_DOCS_FK1 FOREIGN KEY (DOC_TYPE_ID)
              REFERENCES ORDDATA.ORDDCM_DOC_TYPES (DOC_TYPE_ID)
);
CREATE SEQUENCE ORDDATA.ORDDCM_DOCS_ID_SEQ NOCACHE ORDER;
CREATE TABLE ORDDATA.ORDDCM_DOC_REFS
(  DOC_ID           INTEGER NOT NULL REFERENCES ORDDATA.ORDDCM_DOCS(DOC_ID)
  ,REF_BY_DOC_ID    INTEGER  NOT NULL  REFERENCES ORDDATA.ORDDCM_DOCS(DOC_ID)
  ,DOC_NAME         VARCHAR2(100) NOT NULL REFERENCES ORDDATA.ORDDCM_DOCS(DOC_NAME)
  ,REF_BY_DOC_NAME  VARCHAR2(100) NOT NULL REFERENCES ORDDATA.ORDDCM_DOCS(DOC_NAME)
 ,CONSTRAINT ORDDCM_DOC_REFS_PK  PRIMARY KEY  (DOC_ID, REF_BY_DOC_ID)
 ,CONSTRAINT ORDDCM_DOC_REFS_UK  UNIQUE  (DOC_NAME, REF_BY_DOC_NAME)
);
CREATE TABLE ORDDATA.ORDDCM_VR_DT_MAP (
  VR_NUM  INTEGER      NOT NULL  
 ,VR      VARCHAR2(5)  NOT NULL 
 ,DT      VARCHAR2(32) NOT NULL
 ,CONSTRAINT ORDDCM_VR_DT_MAP_PK1  PRIMARY KEY  (VR)
);
CREATE TABLE ORDDATA.ORDDCM_STD_ATTRS (
  SA_ID            INTEGER
  ,TAG             VARCHAR2(8) NOT NULL
  ,NAME            VARCHAR2(128 CHAR) NOT NULL
  ,VR              VARCHAR2(5)
  ,VM              VARCHAR2(128)
  ,ISRETIRED       NUMBER(1)  DEFAULT 0 NOT NULL
  ,ISWILDCARD      NUMBER(1)  DEFAULT 0 NOT NULL
  ,DEFINER_NAME    VARCHAR2(5)  DEFAULT 'DICOM'  NOT NULL
  ,DEFINER_UID     VARCHAR2(128) DEFAULT '1.2.840.10008.1' NOT NULL
  ,DOC_ID          INTEGER NOT NULL
  ,CONSTRAINT ORDDCM_SD_ATTRS_PK PRIMARY KEY (SA_ID)
  ,CONSTRAINT ORDDCM_SD_ATTRS_FK1 FOREIGN KEY (VR)
              REFERENCES ORDDATA.ORDDCM_VR_DT_MAP (VR)
  ,CONSTRAINT ORDDCM_SD_ATTRS_FK2 FOREIGN KEY (DOC_ID)
              REFERENCES ORDDATA.ORDDCM_DOCS (DOC_ID)
);
create SEQUENCE  ORDDATA.ORDDCM_STD_A_SA_ID_SEQ NOCACHE ORDER;
create UNIQUE INDEX ORDDATA.ORDDCM_STD_A_IDX2 on ORDDATA.ORDDCM_STD_ATTRS(tag,name);
CREATE TABLE ORDDATA.ORDDCM_PRV_ATTRS (
   PA_ID                 INTEGER NOT NULL 
  ,TAG                   VARCHAR2(8)
  ,DEFINER_NAME          VARCHAR2(64 CHAR)  NOT NULL
  ,NAME                  VARCHAR2(128 CHAR) NOT NULL
  ,START_TAG             VARCHAR2(8 CHAR)
  ,END_TAG               VARCHAR2(8 CHAR)
  ,DEFINER_UID           VARCHAR2(128)
  ,DOC_ID                INTEGER  NOT NULL 
  ,VR                    VARCHAR2(5)
  ,VM                    VARCHAR2(128) 
  ,ISRETIRED             NUMBER(1)   DEFAULT 0 NOT NULL
  ,ISWILDCARD            NUMBER(1)  DEFAULT 0 NOT NULL
,CONSTRAINT ORDDCM_PRV_ATTRS_PK PRIMARY KEY (PA_ID)  
,CONSTRAINT ORDDCM_PRV_ATTRS_FK1 FOREIGN KEY (DOC_ID) 
               REFERENCES ORDDATA.ORDDCM_DOCS(DOC_ID)
,CONSTRAINT ORDDCM_PRV_ATTRS_FK2 FOREIGN KEY (VR)
              REFERENCES ORDDATA.ORDDCM_VR_DT_MAP (VR)
);
create SEQUENCE ORDDATA.orddcm_prv_a_pa_id_SEQ NOCACHE ORDER;
CREATE INDEX ORDDATA.orddcm_prv_a_idx1 on ORDDATA.ORDDCM_prv_attrs(tag);
CREATE INDEX ORDDATA.orddcm_prv_a_idx2 on ORDDATA.ORDDCM_prv_attrs(definer_name);
CREATE TABLE ORDDATA.ORDDCM_DICT_ATTRS (
  DA_ID  INTEGER
, SA_ID  INTEGER
, PA_ID  INTEGER  
,CONSTRAINT ORDDCM_DICT_ATTRS_PK  PRIMARY KEY (DA_ID)
,CONSTRAINT ORDDCM_DICT_ATTRS_FK1 FOREIGN KEY (SA_ID) 
     REFERENCES ORDDATA.ORDDCM_STD_ATTRS(SA_ID)
,CONSTRAINT ORDDCM_DICT_ATTRS_FK2  FOREIGN KEY (PA_ID)
   REFERENCES ORDDATA.ORDDCM_PRV_ATTRS(PA_ID)
,CONSTRAINT ORDDCM_DICT_ATTRS_UK1  UNIQUE (SA_ID)
,CONSTRAINT ORDDCM_DICT_ATTRS_UK2  UNIQUE (PA_ID)
);
create SEQUENCE ORDDATA.orddcm_dict_a_da_id_SEQ NOCACHE ORDER;
CREATE TABLE ORDDATA.ORDDCM_CT_DAREFS (
  DA_ID  INTEGER NOT NULL
 ,DOC_ID INTEGER NOT NULL
 ,CONSTRAINT ORDDCM_CT_DAREFS_FK1 FOREIGN KEY (DA_ID)
            REFERENCES ORDDATA.ORDDCM_DICT_ATTRS(DA_ID)
 ,CONSTRAINT ORDDCM_CT_DAREFS_FK2 FOREIGN KEY (DOC_ID)
            REFERENCES ORDDATA.ORDDCM_DOCS(DOC_ID)
 ,CONSTRAINT ORDDCM_CT_DAREFS_UK UNIQUE (DA_ID, DOC_ID)
);
CREATE TABLE ORDDATA.ORDDCM_MAPPING_DOCS
(  DOC_ID              INTEGER  NOT NULL 
  ,ROOT_TAG            VARCHAR2(128 CHAR) NOT NULL
  ,METADATA_NS         VARCHAR2(700 CHAR)  
  ,MAPPED_ELEM         VARCHAR2(128 CHAR) 
  ,UNMAPPED_ELEM       VARCHAR2(128 CHAR) 
  ,XSLT                SYS.XMLTYPE 
  ,IS_COVERED          NUMBER(1) NOT NULL
 ,CONSTRAINT ORDDCM_MAPPING_DOCS_PK  PRIMARY KEY (DOC_ID)
 ,CONSTRAINT ORDDCM_MAPPING_DOCS_FK1  FOREIGN KEY (DOC_ID) 
             REFERENCES ORDDATA.ORDDCM_DOCS(DOC_ID)
);
CREATE TABLE ORDDATA.ORDDCM_MAPPED_PATHS (
   MPID                  INTEGER NOT NULL
  ,ATTR_TAG              VARCHAR2(1999)   NOT NULL
  ,DOC_ID                INTEGER NOT NULL
  ,REL_PATH              VARCHAR2(1999 CHAR) NOT NULL
  ,OCCURS                NUMBER(1) DEFAULT 0 NOT NULL
  ,NOT_EMPTY             NUMBER(1) DEFAULT 0 NOT NULL
  ,WRITE_TAG             NUMBER(1) DEFAULT 0 NOT NULL   
  ,WRITE_DEFINER         NUMBER(1) DEFAULT 0 NOT NULL
  ,WRITE_NAME            NUMBER(1) DEFAULT 0 NOT NULL  
  ,WRITE_RAW_VALUE       NUMBER(1) DEFAULT 0 NOT NULL
  ,CONSTRAINT ORDDCM_MAPPED_PATHS_PK1 PRIMARY KEY(MPID)
  ,CONSTRAINT ORDDCM_MAPPED_PATHS_FK2 FOREIGN KEY (DOC_ID) 
              REFERENCES ORDDATA.ORDDCM_MAPPING_DOCS(DOC_ID)
);
CREATE SEQUENCE ORDDATA.orddcm_mpd_p_mpid_seq NOCACHE ORDER; 
declare
  max_keylen_ex exception;
  pragma exception_init(max_keylen_ex, -1450);
begin
 begin
  execute immediate 'alter table ORDDATA.orddcm_mapped_paths add constraint orddcm_mapped_paths_uk1 unique(DOC_ID, ATTR_TAG)';
  exception
  when max_keylen_ex then
    null;
 end;
end;
/
CREATE TABLE ORDDATA.ORDDCM_ANON_RULE_TYPES (
   RULE_TYPE_ID     INTEGER NOT NULL
  ,RULE_TYPE        VARCHAR2(30) NOT NULL
  ,CONSTRAINT ORDDCM_ANON_R_TYPES_PK1 PRIMARY KEY (RULE_TYPE_ID)
  ,CONSTRAINT ORDDCM_ANON_R_TYPES_CK1 CHECK 
      (RULE_TYPE_ID IN (10, 11, 12) )   
  ,CONSTRAINT ORDDCM_ANON_R_TYPES_CK2 
      CHECK (RULE_TYPE  IN ('PRIVATE_ATTRIBUTES' 
                            , 'UNDEFINED_STANDARD_ATTRIBUTES' 
                            , 'UNDEFINED_PRIVATE_ATTRIBUTES')) );
CREATE TABLE ORDDATA.ORDDCM_ANON_ACTION_TYPES (
   ACTION_TYPE_ID    number NOT NULL
  ,ACTION_TYPE   VARCHAR2(30) NOT NULL
  ,CONSTRAINT ORDDCM_ANON_A_TYPES_PK1 PRIMARY KEY (ACTION_TYPE_ID)
  ,CONSTRAINT ORDDCM_ANON_A_TYPES_CK1 CHECK 
      (ACTION_TYPE_ID IN (1, 2, 3, 4) ) 
  ,CONSTRAINT ORDDCM_ANON_A_TYPES_CK2 
      CHECK (ACTION_TYPE IN ('none' , 'remove' , 'replace', 'encrypt') ) );
CREATE TABLE ORDDATA.ORDDCM_ANON_RULES  (
    DOC_ID                INTEGER   NOT NULL
   ,RULE_TYPE_ID          NUMBER    NOT NULL
   ,ANON_ACTION_TYPE_ID   NUMBER NOT NULL
   ,ANON_VALUE            VARCHAR2(128 CHAR)   
   ,CONSTRAINT ORDDCM_ANON_RULES_PK1 PRIMARY KEY (DOC_ID, RULE_TYPE_ID) 
   ,CONSTRAINT ORDDCM_ANON_RULES_FK1 FOREIGN KEY(DOC_ID) 
              REFERENCES ORDDATA.ORDDCM_DOCS(DOC_ID) 
   ,CONSTRAINT ORDDCM_ANON_RULES_FK2 FOREIGN KEY(RULE_TYPE_ID)
              REFERENCES ORDDATA.ORDDCM_ANON_RULE_TYPES(RULE_TYPE_ID) 
   ,CONSTRAINT ORDDCM_ANON_RULES_FK3 FOREIGN KEY(ANON_ACTION_TYPE_ID)
              REFERENCES ORDDATA.ORDDCM_ANON_ACTION_TYPES(ACTION_TYPE_ID) 
   ,CONSTRAINT ORDDCM_ANON_RULES_CK1 CHECK 
        (ANON_ACTION_TYPE_ID  in (1, 2, 3) ) );
CREATE TABLE ORDDATA.ORDDCM_ANON_ATTRS  (
    AAID                 INTEGER NOT NULL
   ,DOC_ID               INTEGER NOT NULL
   ,TAG                  VARCHAR2(1999) NOT NULL
   ,ANON_ACTION_TYPE_ID  NUMBER  NOT NULL
   ,ANON_VALUE           VARCHAR2(128 CHAR)
   ,TAG_DESC             VARCHAR2(1999 CHAR)
   ,CONSTRAINT ORDDCM_ANON_ATTRS_PK1 PRIMARY KEY (AAID)
   ,CONSTRAINT ORDDCM_ANON_ATTRS_FK1 FOREIGN KEY (DOC_ID)
        REFERENCES ORDDATA.ORDDCM_DOCS(DOC_ID)
   ,CONSTRAINT ORDDCM_ANON_ATTRS_FK3 FOREIGN KEY (ANON_ACTION_TYPE_ID) 
       REFERENCES ORDDATA.ORDDCM_ANON_ACTION_TYPES(ACTION_TYPE_ID)
   ,CONSTRAINT ORDDCM_ANON_ATTRS_CK1 CHECK 
        (ANON_ACTION_TYPE_ID  in (1, 2, 3) ) );
CREATE SEQUENCE ORDDATA.orddcm_anon_attrs_seq NOCACHE ORDER;
declare
  max_keylen_ex exception;
  pragma exception_init(max_keylen_ex, -1450);
begin
 begin
  execute immediate 'alter table ORDDATA.orddcm_anon_attrs add constraint orddcm_anon_attrs_uk1 unique(DOC_ID, TAG)';
  exception
  when max_keylen_ex then
    null;
 end;
end;
/
CREATE TABLE ORDDATA.ORDDCM_PREFS_LOOKUP (
    PARAM_NAME                VARCHAR2(64)  PRIMARY KEY
   ,PARAM_TYPE                VARCHAR2(15) NOT NULL
   ,MIN_VAL                   NUMBER
   ,MAX_VAL                   NUMBER
   ,DEFAULT_NUMBER_VAL        NUMBER
   ,DEFAULT_STR_VAL           VARCHAR2(1999)
   ,DEFAULT_STR_VALUES        ORDSYS.ORD_STR_LIST
   ,VALID_STR_VALUES          ORDSYS.ORD_STR_LIST
   ,PARAM_DESC                VARCHAR2(4000)
)  NESTED TABLE DEFAULT_STR_VALUES STORE AS  ORDDCM_PREFS_DEF_VALUES_TAB
, NESTED TABLE VALID_STR_VALUES  STORE AS ORDDCM_PREFS_VALID_VALUES_TAB;
CREATE TABLE ORDDATA.ORDDCM_RT_PREF_PARAMS (
    PPID       INTEGER NOT NULL
   ,NAME       VARCHAR2(64) NOT NULL
   ,VALUE      VARCHAR2(1999 CHAR)  NOT NULL
   ,DOC_ID     INTEGER  NOT NULL
   ,PARAM_DESC VARCHAR2(1999 CHAR) 
   ,CONSTRAINT ORDDCM_PREF_PARAMS_PK1 PRIMARY KEY(PPID)
   ,CONSTRAINT ORDDCM_PREF_PARAMS_FK1 FOREIGN KEY (DOC_ID) 
               REFERENCES ORDDATA.ORDDCM_DOCS(DOC_ID)
   ,CONSTRAINT ORDDCM_PREF_PARAMS_UK1 unique(NAME) 
);
CREATE SEQUENCE ORDDATA.orddcm_rt_pref_p_ppid_seq NOCACHE ORDER;
CREATE TABLE ORDDATA.ORDDCM_UID_DEFS (
   UDID               INTEGER 
  ,DICOM_UID          VARCHAR2(128)  NOT NULL
  ,DOC_ID             INTEGER  NOT NULL
  ,NAME               VARCHAR2(128 CHAR)  NOT NULL
  ,CLASSIFICATION     VARCHAR2(64)  NOT NULL   
  ,ISLE               NUMBER(1) DEFAULT 1  NOT NULL
  ,ISEVR              NUMBER(1) DEFAULT 1  NOT NULL
  ,ISCOMPRESSED       NUMBER(1) DEFAULT 1  NOT NULL
  ,ISRETIRED          NUMBER(1) DEFAULT 0  NOT NULL
  ,CONTENTTYPE        VARCHAR2(64)  DEFAULT 'IMAGE' NOT NULL
  ,UID_DESC           VARCHAR2(1999 CHAR)  
  ,CONSTRAINT ORDDCM_UID_DEFS_PK  PRIMARY KEY (UDID)
  ,CONSTRAINT ORDDCM_UID_DEFS_UK1 UNIQUE (DICOM_UID)
  ,CONSTRAINT ORDDCM_UID_DEFS_FK1 FOREIGN KEY (DOC_ID) 
            REFERENCES ORDDATA.ORDDCM_DOCS(DOC_ID)
);
CREATE SEQUENCE ORDDATA.orddcm_uid_defs_udid_seq NOCACHE ORDER;
CREATE TABLE ORDDATA.ORDDCM_STORED_TAGS (
   STID                  INTEGER NOT NULL
  ,ATTR_TAG              VARCHAR2(1999)  NOT NULL
  ,DOC_ID                INTEGER NOT NULL
  ,CONSTRAINT ORDDCM_STORED_TAGS_PK1 PRIMARY KEY(STID)
  ,CONSTRAINT ORDDCM_STORED_TAGS_FK1 FOREIGN KEY (DOC_ID) 
            REFERENCES ORDDATA.ORDDCM_DOCS(DOC_ID)
);
CREATE SEQUENCE ORDDATA.orddcm_storedtags_stid_seq NOCACHE ORDER; 
declare
  max_keylen_ex exception;
  pragma exception_init(max_keylen_ex, -1450);
begin
 begin
  execute immediate 'alter table ORDDATA.ORDDCM_STORED_TAGS add constraint orddcm_stored_tags_uk1 unique(ATTR_TAG, DOC_ID)';
  exception
  when max_keylen_ex then
    null;
 end;
end;
/
CREATE TABLE ORDDATA.ORDDCM_DATA_MODEL (
  MODEL_ID     INTEGER NOT NULL
 ,MODEL_NAME   VARCHAR2(100)  DEFAULT 'DEFAULT'
 ,ISRETIRED    NUMBER   DEFAULT 0
 ,CONSTRAINT ORDDCM_DATA_MODEL_PK PRIMARY KEY (MODEL_ID)
);
CREATE SEQUENCE ORDDATA.orddcm_data_model_SEQ NOCACHE ORDER;
CREATE TABLE ORDDATA.ORDDCM_CT_PRED
(
  PID            INTEGER
  ,FPID          INTEGER
  ,POS           NUMBER(3)     NOT NULL
  ,REF_PID       INTEGER              
  ,OP            NUMBER(3)
  ,DESCRIPTION   VARCHAR2(1999 CHAR)
  ,CONSTRAINT  ORDDCM_CT_PRED_PK  PRIMARY KEY (PID)
  ,CONSTRAINT   ORDDCM_CT_PRED_FK1 FOREIGN KEY (FPID) 
            REFERENCES ORDDATA.ORDDCM_CT_PRED(PID)
  ,CONSTRAINT ORDDCM_CT_PRED_FK2 FOREIGN KEY (REF_PID) 
            REFERENCES ORDDATA.ORDDCM_CT_PRED(PID)  
);
CREATE SEQUENCE ORDDATA.ORDDCM_CT_PRED_SEQ NOCACHE ORDER;
CREATE TABLE ORDDATA.ORDDCM_CT_PRED_OPRD
(
  PID           INTEGER, 
  POS           NUMBER(3),
  OPERAND       SYS.XMLTYPE,
  CONSTRAINT ORDDCM_CT_PO_PK UNIQUE(PID, POS),
  CONSTRAINT ORDDCM_CT_PO_FK FOREIGN KEY (PID)
        REFERENCES ORDDATA.ORDDCM_CT_PRED(PID)
)
;
CREATE TABLE ORDDATA.ORDDCM_CT_PRED_SET
(
  PSID          INTEGER,
  NAME          VARCHAR2(64 CHAR),
  PSTYPE        NUMBER(1) NOT NULL,
  PID           INTEGER, 
  STATUS        NUMBER(1),
  SUPER         INTEGER,                
  DOC_ID        INTEGER,
  DESCRIPTION   VARCHAR2(1999 CHAR),
  CONSTRAINT ORDDCM_CT_PS_PK PRIMARY KEY (PSID),
  CONSTRAINT ORDDCM_CT_PS_FK1 FOREIGN KEY (PID)
        REFERENCES ORDDATA.ORDDCM_CT_PRED(PID),
  CONSTRAINT ORDDCM_CT_PS_FK2 FOREIGN KEY (SUPER)
        REFERENCES ORDDATA.ORDDCM_CT_PRED_SET(PSID),
  CONSTRAINT ORDDCM_CT_PS_FK3 FOREIGN KEY (DOC_ID)
        REFERENCES ORDDATA.ORDDCM_DOCS(DOC_ID),
  CONSTRAINT ORDDCM_CT_PS_UQ UNIQUE (NAME, PSTYPE, SUPER)
);
CREATE SEQUENCE ORDDATA.ORDDCM_CT_PRED_SET_SEQ NOCACHE ORDER;
CREATE TABLE ORDDATA.ORDDCM_CT_MACRO_PAR
(
  PSID          INTEGER,
  PARNAME       VARCHAR2(128 CHAR),
  CONSTRAINT ORDDCM_CT_MP_PK UNIQUE(PSID, PARNAME),
  CONSTRAINT ORDDCM_CT_MP_FK FOREIGN KEY(PSID)
        REFERENCES ORDDATA.ORDDCM_CT_PRED_SET(PSID)
);
CREATE TABLE ORDDATA.ORDDCM_CT_MACRO_DEP
(
  PSID          INTEGER,
  DEP_PSID      INTEGER,
  CONSTRAINT ORDDCM_CT_MD_FK1 FOREIGN KEY(PSID)
        REFERENCES ORDDATA.ORDDCM_CT_PRED_SET(PSID),
  CONSTRAINT ORDDCM_CT_MD_FK2 FOREIGN KEY(DEP_PSID)
        REFERENCES ORDDATA.ORDDCM_CT_PRED_SET(PSID),
  CONSTRAINT ORDDCM_CT_MD_UQ UNIQUE(PSID, DEP_PSID)
);
CREATE TABLE ORDDATA.ORDDCM_CT_PRED_PAR
(
  PID           INTEGER,
  PARNAME       VARCHAR2(128 CHAR),
  PARVAL        VARCHAR2(1999 CHAR),
  CONSTRAINT ORDDCM_CT_PP_PK UNIQUE(PID, PARNAME),
  CONSTRAINT ORDDCM_CT_PP_FK FOREIGN KEY(PID)
         REFERENCES ORDDATA.ORDDCM_CT_PRED(PID)
);
CREATE TABLE ORDDATA.ORDDCM_CT_ACTION
(
  PID           INTEGER,
  EVENT         VARCHAR2(20 CHAR),
  ACTION        VARCHAR2(20 CHAR),
  DESCRIPTION   VARCHAR2(1999 CHAR),
  CONSTRAINT ORDDCM_CT_A_PK UNIQUE(PID, EVENT, ACTION),
  CONSTRAINT ORDDCM_CT_A_FK1 FOREIGN KEY (PID)
    REFERENCES ORDDATA.ORDDCM_CT_PRED(PID)
);
CREATE TABLE ORDDATA.ORDDCM_CT_LOCATORPATHS
(
  DOC_ID          INTEGER NOT NULL,
  PSID            INTEGER NOT NULL,
  LOCATOR_PATH    VARCHAR2(1999 CHAR) NOT NULL,
  HAS_MACRO_PARAM  NUMBER(1) NOT NULL,
  IS_COVERED       NUMBER(1),
  CONSTRAINT ORDDCM_CT_LP_FK1 FOREIGN KEY (DOC_ID)
        REFERENCES ORDDATA.ORDDCM_DOCS(DOC_ID),
  CONSTRAINT ORDDCM_CT_LP_FK2 FOREIGN KEY (PSID)
    REFERENCES ORDDATA.ORDDCM_CT_PRED_SET(PSID)
);
CREATE TABLE ORDDATA.ORDDCM_CT_VLD_MSG
(
  SOP_INSTANCE_UID      VARCHAR2(128 CHAR),
  RULE_NAME             VARCHAR2(64 CHAR) NOT NULL,
  MESSAGE               VARCHAR2(1999 CHAR),
  MSG_TYPE              VARCHAR2(20 CHAR) NOT NULL,
  MSG_TIME              TIMESTAMP NOT NULL, 
  USER_NAME             VARCHAR2(64) NOT NULL
);
CREATE INDEX ORDDATA.ORDDCM_CT_VM_ID_IDX on ORDDATA.ORDDCM_CT_VLD_MSG(SOP_INSTANCE_UID);
CREATE INDEX ORDDATA.ORDDCM_CT_VM_RN_IDX on ORDDATA.ORDDCM_CT_VLD_MSG(RULE_NAME);
CREATE INDEX ORDDATA.ORDDCM_CT_VM_MT_IDX on ORDDATA.ORDDCM_CT_VLD_MSG(MSG_TYPE);
CREATE INDEX ORDDATA.ORDDCM_CT_VM_UN_IDX on ORDDATA.ORDDCM_CT_VLD_MSG(USER_NAME);
INSERT INTO ORDDATA.orddcm_doc_types 
   values (21, 'STANDARD_DICTIONARY'
           , 'http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0'
           , 'DICOM Standard Data Dictionary');
INSERT INTO ORDDATA.orddcm_doc_types 
   values (22,  'PRIVATE_DICTIONARY'
           , 'http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0'
           , 'Private Data Dictionary');
INSERT INTO ORDDATA.orddcm_doc_types 
   values ( 23,  'MAPPING'
           , 'http://xmlns.oracle.com/ord/dicom/mapping_1_0'
           , 'Mapping document');
INSERT INTO ORDDATA.orddcm_doc_types 
   values (  24,'ANONYMITY'
           , 'http://xmlns.oracle.com/ord/dicom/anonymity_1_0'
           , 'Anonymity document');
INSERT INTO ORDDATA.orddcm_doc_types 
   values (  25, 'PREFERENCE'
           , 'http://xmlns.oracle.com/ord/dicom/preference_1_0'
           , 'Preference document');
INSERT INTO ORDDATA.orddcm_doc_types 
   values (  26, 'UID_DEFINITION'
           , 'http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0'
           , 'DICOM UID definition document');
INSERT INTO ORDDATA.orddcm_doc_types 
   values ( 27, 'CONSTRAINT'
           , 'http://xmlns.oracle.com/ord/dicom/constraint_1_0'
           , 'Constraint document');
INSERT INTO ORDDATA.orddcm_doc_types 
   values ( 28, 'STORED_TAG_LIST'
           , 'http://xmlns.oracle.com/ord/dicom/attributeTag_1_0'
           , 'Stored tag list document');
insert into ORDDATA.orddcm_install_docs 
   values (21,'ordcmsd.xml', 'STANDARD_DICTIONARY', 1);
insert into ORDDATA.orddcm_install_docs 
   values (22, 'ordcmpv.xml',  'PRIVATE_DICTIONARY', 2);
insert into ORDDATA.orddcm_install_docs 
   values (23, 'ordcmmp.xml',  'MAPPING', 3);
insert into ORDDATA.orddcm_install_docs 
  values (24,'ordcman.xml',  'ANONYMITY', 4);
insert into ORDDATA.orddcm_install_docs 
  values (25, 'ordcmpf.xml',  'PREFERENCE', 5);
insert into ORDDATA.orddcm_install_docs 
  values (26, 'ordcmui.xml',  'UID_DEFINITION', 6);
insert into ORDDATA.orddcm_install_docs 
   values (27, 'ordcmcmc.xml',  'CONSTRAINT', 7);
insert into ORDDATA.orddcm_install_docs 
   values (27, 'ordcmcmd.xml',  'CONSTRAINT', 8);
insert into ORDDATA.orddcm_install_docs 
   values (27,'ordcmct.xml', 'CONSTRAINT', 9);
insert into ORDDATA.orddcm_anon_rule_types
  values ( 10,  'PRIVATE_ATTRIBUTES');
insert into ORDDATA.orddcm_anon_rule_types
  values ( 11,  'UNDEFINED_STANDARD_ATTRIBUTES');
insert into ORDDATA.orddcm_anon_rule_types
  values ( 12,  'UNDEFINED_PRIVATE_ATTRIBUTES');
insert into ORDDATA.orddcm_anon_action_types
  values ( 1,  'none');
insert into ORDDATA.orddcm_anon_action_types
  values ( 2,  'remove');
insert into ORDDATA.orddcm_anon_action_types
  values ( 3,  'replace');
insert into ORDDATA.orddcm_anon_action_types
  values ( 4,  'encrypt');
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
   values ( 'XML_SKIP_ATTR', 
            'INTEGER',
             128,
             32767,
             512,
             Null,
             null,
             null);
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
   values ('AVG_ATTR_NUM', 
           'INTEGER',
            20,
            2000,
            200, 
            null,
            null, 
            null);      
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
 values ('CONFORMANCE_LEVEL', 
         'VARCHAR2',null,null,null,
         'ignoreException', 
         null, 
         ORDSYS.ORD_STR_LIST('leastConform', 'ignoreException','mostConform'));
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
 values ('IGNORED_EXP_LIST',
         'VARCHAR2',
         null,null,null,null,
         ORDSYS.ORD_STR_LIST('MISSING_ATTR',
                      'INVALID_LENGTH',
                      'MISSING_MAGIC',
                      'MISSING_HEADER',
                      'INVALID_VR',
                      'INVALID_VM',
                      'PARSE_ERR'),
        ORDSYS.ORD_STR_LIST('MISSING_MAGIC',
                     'MISSING_HEADER',
                     'MISSING_ATTR',
                     'FAULTY_VALUE', 
                     'INVALID_LENGTH', 
                     'INVALID_VM', 
                     'INVALID_VR',  
                     'UNSUPPORT_VALUE',
                     'UNDEFINED_VALUE', 
                     'NOT_AN_IMAGE',
                     'PARSE_ERR'));
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
 values ('OUTPUT_RAW_VALUE',
         'INTEGER',
         -1,
         32767,
         0,
         Null,
         null,
         null);
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
values ('LOGGING_LEVEL',
        'VARCHAR2',
         NULL,NULL,NULL,
        'warning',
         NULL,
        ORDSYS.ORD_STR_LIST('debug','conformance','warning','error','none'));  
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
values ('VALIDATE_METADATA',
        'BOOLEAN',
         0,1,0,
        null,null,null);                                
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
values ('EXP_IF_NULL_ATTR_IN_CONSTRAINT',
        'BOOLEAN',
         0,1,0,
        null,null,null);                                
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
values ('MANDATE_ATTR_TAGS_IN_STL',
        'BOOLEAN',
         0,1,0,
        null,null,null);
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
values ('MAX_RECURSION_DEPTH',
        'INTEGER',
         1,32767,16,
        null,null,null);                                
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
values ('SQ_WRITE_LEN',
        'BOOLEAN',
         0,1,1,
        null,null,null);
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
values ('SPECIFIC_CHARACTER_SET',
        'VARCHAR2',
         NULL,NULL,NULL,
        'ASCII',
         NULL,
        ORDSYS.ORD_STR_LIST('ASCII', 'ISO_IR 100', 'ISO_IR 101', 'ISO_IR 109', 
                     'ISO_IR 110', 'ISO_IR 144', 'ISO_IR 127', 'ISO_IR 126',
                     'ISO_IR 138', 'ISO_IR 148', 'ISO_IR 13', 'ISO_IR 166',
                     'ISO_IR 192', 'GB18030' ));
insert into ORDDATA.orddcm_prefs_lookup 
     ( param_name, 
       param_type, 
       min_val, 
       max_val, 
       default_number_val, 
       default_str_val, 
       default_str_values, 
       valid_str_values ) 
values ('BINARY_SKIP_INVALID_ATTR',
        'BOOLEAN',
         0,1,0,
        null,null,null);
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00020000', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00020002', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00020003', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00020010', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00080005', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00080070', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00209072', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00280008', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00280009', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('0028000A', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00280012', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00280034', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00280106', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00280107', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00280108', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00280109', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00280110', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00280111', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00280120', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00281050', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00281051', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00281052', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00281053', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00281054', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00282110', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00282112', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00282114', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00283000', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00283002', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00283004', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00283006', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00283010', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00283110', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00286010', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00286020', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00289108', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00289110', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('00289145', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('FFFEE000', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('FFFEE00D', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('FFFEE0DD', 'DICOM');
insert into ORDDATA.ORDDCM_INTERNAL_TAGS values ('FFFF1002', 'ORACLE');
insert into ORDDATA.orddcm_vr_dt_map values(0,  'EX', 'EXCEPTION_TYPE' );
insert into ORDDATA.orddcm_vr_dt_map values(1,  'AE', 'APPLICATION_ENTITY');
insert into ORDDATA.orddcm_vr_dt_map values(2,  'AS', 'AGE_STRING'); 
insert into ORDDATA.orddcm_vr_dt_map values(3,  'AT', 'ATTRIBUTE_TAG');
insert into ORDDATA.orddcm_vr_dt_map values(4,  'CS', 'CODE_STRING' );
insert into ORDDATA.orddcm_vr_dt_map values(5,  'DA', 'DATE' );
insert into ORDDATA.orddcm_vr_dt_map values(6,  'DS', 'DECIMAL_STRING' );
insert into ORDDATA.orddcm_vr_dt_map values(7,  'DT',  'DATE_TIME' );
insert into ORDDATA.orddcm_vr_dt_map values(8,  'FL', 'FLOAT_SINGLE' );
insert into ORDDATA.orddcm_vr_dt_map values(9,  'FD', 'FLOAT_DOUBLE' );
insert into ORDDATA.orddcm_vr_dt_map values(10, 'IS', 'INTEGER_STRING');
insert into ORDDATA.orddcm_vr_dt_map values(11, 'LO', 'LONG_STRING' );
insert into ORDDATA.orddcm_vr_dt_map values(12, 'LT', 'LONG_TEXT' );
insert into ORDDATA.orddcm_vr_dt_map values(13, 'OB', 'OTHER_BYTE' );
insert into ORDDATA.orddcm_vr_dt_map values(14, 'OF', 'OTHER_FLOAT');
insert into ORDDATA.orddcm_vr_dt_map values(15, 'OW', 'OTHER_WORD');
insert into ORDDATA.orddcm_vr_dt_map values(16, 'PN', 'PERSON_NAME');
insert into ORDDATA.orddcm_vr_dt_map values(17, 'SH', 'SHORT_STRING');
insert into ORDDATA.orddcm_vr_dt_map values(18, 'SL', 'SIGNED_LONG');
insert into ORDDATA.orddcm_vr_dt_map values(19, 'SQ', 'SEQUENCE');
insert into ORDDATA.orddcm_vr_dt_map values(20, 'SS', 'SIGNED_SHORT' );
insert into ORDDATA.orddcm_vr_dt_map values(21, 'ST', 'SHORT_TEXT' );
insert into ORDDATA.orddcm_vr_dt_map values(22, 'TM', 'TIME' );
insert into ORDDATA.orddcm_vr_dt_map values(23, 'UI', 'UNIQUE_ID');
insert into ORDDATA.orddcm_vr_dt_map values(24, 'UL', 'UNSIGNED_LONG');
insert into ORDDATA.orddcm_vr_dt_map values(25, 'UN', 'UNKNOWN');
insert into ORDDATA.orddcm_vr_dt_map values(26, 'US', 'UNSIGNED_SHORT');
insert into ORDDATA.orddcm_vr_dt_map values(27, 'UT', 'UNLIMITED_TEXT');
insert into ORDDATA.orddcm_vr_dt_map values(20, 'USS', 'SIGNED_SHORT');
insert into ORDDATA.orddcm_vr_dt_map values(15, 'OWB', 'OTHER_WORD');
insert into ORDDATA.orddcm_vr_dt_map values(0, 'EXP', 'EXCEPTION_TYPE');
insert into ORDDATA.orddcm_vr_dt_map values(0,'EXTUN', 'EXTENDED_TYPE');
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_DOCS_TMP
(    DOC_ID           INTEGER   NOT NULL
    ,DOC_NAME         VARCHAR2(100 CHAR) NOT NULL 
    ,DOC_TYPE_ID      NUMBER NOT NULL  
    ,DOC_CONTENT      SYS.XMLType  NOT NULL 
    ,ORACLE_INSTALL   NUMBER(1) DEFAULT 0 NOT NULL
    ,CREATE_DATE      DATE    DEFAULT SYSDATE NOT NULL
) on commit preserve rows;
CREATE INDEX ORDDATA.ORDDCM_DOCS_GT_IDX1 on ORDDATA.ORDDCM_DOCS_TMP(DOC_ID);
CREATE INDEX ORDDATA.ORDDCM_DOCS_GT_IDX2 on ORDDATA.ORDDCM_DOCS_TMP(DOC_TYPE_ID);
CREATE INDEX ORDDATA.ORDDCM_DOCS_GT_IDX3 on ORDDATA.ORDDCM_DOCS_TMP(ORACLE_INSTALL);
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_STD_ATTRS_TMP
( SA_ID           INTEGER  NOT NULL
  ,TAG             VARCHAR2(8) NOT NULL
  ,NAME            VARCHAR2(128 CHAR) NOT NULL
  ,VR              VARCHAR2(5)
  ,VM              VARCHAR2(128)
  ,ISRETIRED       NUMBER(1)  DEFAULT 0 NOT NULL
  ,ISWILDCARD      NUMBER(1)  DEFAULT 0 NOT NULL
  ,DEFINER_NAME    VARCHAR2(5)  DEFAULT 'DICOM'  NOT NULL
  ,DEFINER_UID     VARCHAR2(128) DEFAULT '1.2.840.10008.1' NOT NULL
  ,DOC_ID          INTEGER NOT NULL
) on commit preserve rows;
CREATE unique INDEX ORDDATA.ORDDCM_STD_AGT_IDX1 on ORDDATA.ORDDCM_STD_ATTRS_TMP(SA_ID);
CREATE INDEX ORDDATA.ORDDCM_STD_AGT_IDX2 on ORDDATA.ORDDCM_STD_ATTRS_TMP(tag);
CREATE INDEX ORDDATA.ORDDCM_STD_AGT_IDX3 on ORDDATA.ORDDCM_STD_ATTRS_TMP(name);
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_PRV_ATTRS_TMP
( PA_ID                 INTEGER NOT NULL 
  ,TAG                   VARCHAR2(8)
  ,DEFINER_NAME          VARCHAR2(64 CHAR)  NOT NULL
  ,NAME                  VARCHAR2(128 CHAR) NOT NULL
  ,START_TAG             VARCHAR2(8 CHAR)
  ,END_TAG               VARCHAR2(8 CHAR)
  ,DEFINER_UID           VARCHAR2(128)
  ,DOC_ID                INTEGER  NOT NULL 
  ,VR                    VARCHAR2(5)
  ,VM                    VARCHAR2(128) 
  ,ISRETIRED             NUMBER(1)   DEFAULT 0 NOT NULL
  ,ISWILDCARD            NUMBER(1)  DEFAULT 0 NOT NULL
) on commit preserve rows;
CREATE INDEX ORDDATA.ORDDCM_PRV_AGT_IDX1 on ORDDATA.ORDDCM_PRV_ATTRS_TMP(TAG);
CREATE INDEX ORDDATA.ORDDCM_PRV_AGT_IDX2 on ORDDATA.ORDDCM_PRV_ATTRS_TMP(DEFINER_NAME);
CREATE unique index ORDDATA.ORDDCM_PRV_AGT_IDX3 on ORDDATA.ORDDCM_PRV_ATTRS_TMP(PA_ID);
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_DICT_ATTRS_TMP
( DA_ID  INTEGER  PRIMARY KEY
, SA_ID  INTEGER
, PA_ID  INTEGER 
) on commit preserve rows;
CREATE INDEX ORDDATA.ORDDCM_DICT_ATTRS_GT_IDX2 on ORDDATA.ORDDCM_DICT_ATTRS_TMP(SA_ID);
CREATE INDEX ORDDATA.ORDDCM_DICT_ATTRS_GT_IDX3 on ORDDATA.ORDDCM_DICT_ATTRS_TMP(PA_ID);
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_DOC_REFS_TMP
(  DOC_ID           INTEGER NOT NULL 
  ,REF_BY_DOC_ID    INTEGER  NOT NULL  
  ,DOC_NAME         VARCHAR2(100) NOT NULL
  ,REF_BY_DOC_NAME  VARCHAR2(100) NOT NULL
) on commit preserve rows;
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_CT_DAREFS_TMP (
  DA_ID  INTEGER NOT NULL
 ,DOC_ID INTEGER NOT NULL
) on commit preserve rows;
CREATE INDEX ORDDATA.ORDDCM_CT_DAREFS_GT_IDX1 on ORDDATA.ORDDCM_CT_DAREFS_TMP(DA_ID); 
CREATE INDEX ORDDATA.ORDDCM_CT_DAREFS_GT_IDX2 on ORDDATA.ORDDCM_CT_DAREFS_TMP(DOC_ID); 
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_MAPPING_DOCS_TMP
(  DOC_ID              INTEGER  NOT NULL 
  ,ROOT_TAG            VARCHAR2(128 CHAR) NOT NULL
  ,METADATA_NS         VARCHAR2(700 CHAR)  
  ,MAPPED_ELEM         VARCHAR2(128 CHAR) 
  ,UNMAPPED_ELEM       VARCHAR2(128 CHAR) 
  ,XSLT                SYS.XMLTYPE 
  ,IS_COVERED          NUMBER(1)  NOT NULL 
) on commit preserve rows;
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_MAPPED_PATHS_TMP
(  MPID                  INTEGER NOT NULL
  ,ATTR_TAG              VARCHAR2(1999)   NOT NULL
  ,DOC_ID                INTEGER NOT NULL
  ,REL_PATH              VARCHAR2(1999 CHAR) NOT NULL
  ,OCCURS                NUMBER(1) DEFAULT 0 NOT NULL
  ,NOT_EMPTY             NUMBER(1) DEFAULT 0 NOT NULL
  ,WRITE_TAG             NUMBER(1) DEFAULT 0 NOT NULL   
  ,WRITE_DEFINER         NUMBER(1) DEFAULT 0 NOT NULL
  ,WRITE_NAME            NUMBER(1) DEFAULT 0 NOT NULL  
  ,WRITE_RAW_VALUE       NUMBER(1) DEFAULT 0 NOT NULL
) on commit preserve rows;
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_ANON_RULES_TMP
(   DOC_ID                INTEGER   NOT NULL
   ,RULE_TYPE_ID          NUMBER    NOT NULL
   ,ANON_ACTION_TYPE_ID   NUMBER NOT NULL
   ,ANON_VALUE            VARCHAR2(128 CHAR)   
) on commit preserve rows;
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_ANON_ATTRS_TMP
(   AAID                 INTEGER NOT NULL
   ,DOC_ID               INTEGER NOT NULL
   ,TAG                  VARCHAR2(1999) NOT NULL
   ,ANON_ACTION_TYPE_ID  NUMBER  NOT NULL
   ,ANON_VALUE           VARCHAR2(128 CHAR)
   ,TAG_DESC             VARCHAR2(1999 CHAR)
) on commit preserve rows;
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_RT_PREF_PARAMS_TMP
(
    PPID       INTEGER NOT NULL
   ,NAME       VARCHAR2(64) NOT NULL
   ,VALUE      VARCHAR2(1999 CHAR)  NOT NULL
   ,DOC_ID     INTEGER  NOT NULL
   ,PARAM_DESC VARCHAR2(1999 CHAR) 
) on commit preserve rows;
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_UID_DEFS_TMP
(  UDID               INTEGER 
  ,DICOM_UID          VARCHAR2(128)  NOT NULL
  ,DOC_ID             INTEGER  NOT NULL
  ,NAME               VARCHAR2(128 CHAR)  NOT NULL
  ,CLASSIFICATION     VARCHAR2(64)  NOT NULL   
  ,ISLE               NUMBER(1) DEFAULT 1  NOT NULL
  ,ISEVR              NUMBER(1) DEFAULT 1  NOT NULL
  ,ISCOMPRESSED       NUMBER(1) DEFAULT 1  NOT NULL
  ,ISRETIRED          NUMBER(1) DEFAULT 0  NOT NULL
  ,CONTENTTYPE        VARCHAR2(64)  DEFAULT 'IMAGE' NOT NULL
  ,UID_DESC           VARCHAR2(1999 CHAR)  
 ) on commit preserve rows;
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_STORED_TAGS_TMP
(  STID                  INTEGER         NOT NULL
  ,ATTR_TAG              VARCHAR2(1999)  NOT NULL
  ,DOC_ID                INTEGER         NOT NULL
) on commit preserve rows;
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_CT_PRED_TMP
(
  PID           INTEGER      PRIMARY KEY,
  FPID          INTEGER,              
  POS           NUMBER(3)     NOT NULL,
  REF_PID       INTEGER ,             
  OP            NUMBER(3),
  DESCRIPTION   VARCHAR2(1999 CHAR)
) on commit preserve rows;
CREATE INDEX ORDDATA.ORDDCM_CT_PGT_FPID_IDX on ORDDATA.ORDDCM_CT_PRED_TMP(FPID);
CREATE INDEX ORDDATA.ORDDCM_CT_PGT_REF_IDX on ORDDATA.ORDDCM_CT_PRED_TMP(REF_PID);
CREATE INDEX ORDDATA.ORDDCM_CT_PGT_OP_IDX on ORDDATA.ORDDCM_CT_PRED_TMP(OP);
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_CT_PRED_OPRD_TMP
( PID           INTEGER, 
  POS           NUMBER(3),
  OPERAND       SYS.XMLTYPE
) on commit preserve rows;
CREATE INDEX ORDDATA.ORDDCM_CT_POGT_PID_IDX on ORDDATA.ORDDCM_CT_PRED_OPRD_TMP(PID);
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_CT_PRED_SET_TMP
( PSID          INTEGER PRIMARY KEY,
  NAME          VARCHAR2(64 CHAR),
  PSTYPE        NUMBER(1) NOT NULL,
  PID           INTEGER, 
  STATUS        NUMBER(1),
  SUPER         INTEGER,                
  DOC_ID        INTEGER,
  DESCRIPTION   VARCHAR2(1999 CHAR)
) on commit preserve rows;
CREATE INDEX ORDDATA.ORDDCM_CT_PSGT_PID_IDX on ORDDATA.ORDDCM_CT_PRED_SET_TMP(PID);
CREATE INDEX ORDDATA.ORDDCM_CT_PSGT_SPR_IDX on ORDDATA.ORDDCM_CT_PRED_SET_TMP(SUPER);
CREATE INDEX ORDDATA.ORDDCM_CT_PSGT_NAME_IDX on ORDDATA.ORDDCM_CT_PRED_SET_TMP(NAME);
CREATE INDEX ORDDATA.ORDDCM_CT_PSGT_TYPE_IDX on ORDDATA.ORDDCM_CT_PRED_SET_TMP(PSTYPE);
CREATE INDEX ORDDATA.ORDDCM_CT_PSGT_STATUS_IDX on ORDDATA.ORDDCM_CT_PRED_SET_TMP(STATUS);
CREATE INDEX ORDDATA.ORDDCM_CT_PSGT_DOCID_IDX on ORDDATA.ORDDCM_CT_PRED_SET_TMP(DOC_ID);
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_CT_PRED_PAR_TMP
( PID           INTEGER, 
  PARNAME       VARCHAR2(128 CHAR),
  PARVAL        VARCHAR2(1999 CHAR)
) on commit preserve rows;
CREATE INDEX ORDDATA.ORDDCM_CT_PPGT_PID_IDX on ORDDATA.ORDDCM_CT_PRED_PAR_TMP(PID);
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_CT_LOCATORPATHS_TMP
(
  DOC_ID          INTEGER NOT NULL,
  PSID            INTEGER NOT NULL,
  LOCATOR_PATH    VARCHAR2(1999 CHAR) NOT NULL,
  HAS_MACRO_PARAM  NUMBER(1) NOT NULL,
  IS_COVERED      NUMBER(1)
) on commit preserve rows;
CREATE INDEX ORDDATA.ORDDCM_CT_LPT_DID_IDX On ORDDATA.ORDDCM_CT_LOCATORPATHS_TMP(DOC_ID);
CREATE INDEX ORDDATA.ORDDCM_CT_LPT_PSID_IDX On ORDDATA.ORDDCM_CT_LOCATORPATHS_TMP(PSID);
CREATE GLOBAL TEMPORARY TABLE ORDDATA.ORDDCM_CT_ACTION_TMP
( PID           INTEGER,
  EVENT         VARCHAR2(20 CHAR),
  ACTION        VARCHAR2(20 CHAR),
  DESCRIPTION   VARCHAR2(1999 CHAR)
) on commit preserve rows;
CREATE INDEX ORDDATA.ORDDCM_CT_AGT_PID_IDX on ORDDATA.ORDDCM_CT_ACTION_TMP(PID);
CREATE TABLE ORDDATA.ORDDCM_DOCS_WRK(
     DOC_ID           INTEGER   
    ,DOC_NAME         VARCHAR2(100 CHAR) NOT NULL 
    ,DOC_TYPE_ID      NUMBER NOT NULL  
    ,DOC_CONTENT      SYS.XMLType  NOT NULL 
    ,ORACLE_INSTALL   NUMBER(1) DEFAULT 0 NOT NULL
    ,CREATE_DATE      DATE    DEFAULT SYSDATE NOT NULL
    ,CONSTRAINT ORDDCM_DOCS_W_PK PRIMARY KEY (DOC_ID)
    ,CONSTRAINT ORDDCM_DOCS_W_UK1 UNIQUE (DOC_NAME)
    ,CONSTRAINT ORDDCM_DOCS_W_FK1 FOREIGN KEY (DOC_TYPE_ID)
              REFERENCES ORDDATA.ORDDCM_DOC_TYPES (DOC_TYPE_ID)
);
CREATE TABLE ORDDATA.ORDDCM_DOC_REFS_WRK
(  DOC_ID           INTEGER NOT NULL REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_ID)
  ,REF_BY_DOC_ID    INTEGER  NOT NULL  REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_ID)
  ,DOC_NAME         VARCHAR2(100) NOT NULL REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_NAME)
  ,REF_BY_DOC_NAME  VARCHAR2(100) NOT NULL REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_NAME)
 ,CONSTRAINT ORDDCM_DOC_REFS_W_PK  PRIMARY KEY  (DOC_ID, REF_BY_DOC_ID)
 ,CONSTRAINT ORDDCM_DOC_REFS_W_UK  UNIQUE  (DOC_NAME, REF_BY_DOC_NAME)
);
CREATE TABLE ORDDATA.ORDDCM_STD_ATTRS_WRK (
  SA_ID            INTEGER
  ,TAG             VARCHAR2(8) NOT NULL
  ,NAME            VARCHAR2(128 CHAR) NOT NULL
  ,VR              VARCHAR2(5)
  ,VM              VARCHAR2(128)
  ,ISRETIRED       NUMBER(1)  DEFAULT 0 NOT NULL
  ,ISWILDCARD      NUMBER(1)  DEFAULT 0 NOT NULL
  ,DEFINER_NAME    VARCHAR2(5)  DEFAULT 'DICOM'  NOT NULL
  ,DEFINER_UID     VARCHAR2(128) DEFAULT '1.2.840.10008.1' NOT NULL
  ,DOC_ID          INTEGER NOT NULL
  ,CONSTRAINT ORDDCM_SD_ATTRS_W_PK PRIMARY KEY (SA_ID)
  ,CONSTRAINT ORDDCM_SD_ATTRS_W_FK1 FOREIGN KEY (VR)
              REFERENCES ORDDATA.ORDDCM_VR_DT_MAP (VR)
  ,CONSTRAINT ORDDCM_SD_ATTRS_W_FK2 FOREIGN KEY (DOC_ID)
              REFERENCES ORDDATA.ORDDCM_DOCS_WRK (DOC_ID)
);
create UNIQUE INDEX ORDDATA.orddcm_std_a_w_idx2 on ORDDATA.ORDDCM_std_attrs_wrk(tag,name);
CREATE TABLE ORDDATA.ORDDCM_PRV_ATTRS_WRK (
   PA_ID                 INTEGER NOT NULL 
  ,TAG                   VARCHAR2(8)
  ,DEFINER_NAME          VARCHAR2(64 CHAR)  NOT NULL
  ,NAME                  VARCHAR2(128 CHAR) NOT NULL
  ,START_TAG             VARCHAR2(8 CHAR)
  ,END_TAG               VARCHAR2(8 CHAR)
  ,DEFINER_UID           VARCHAR2(128)
  ,DOC_ID                INTEGER  NOT NULL 
  ,VR                    VARCHAR2(5)
  ,VM                    VARCHAR2(128) 
  ,ISRETIRED             NUMBER(1)   DEFAULT 0 NOT NULL
  ,ISWILDCARD            NUMBER(1)  DEFAULT 0 NOT NULL
,CONSTRAINT ORDDCM_PRV_ATTRS_W_PK PRIMARY KEY (PA_ID)  
,CONSTRAINT ORDDCM_PRV_ATTRS_W_FK1 FOREIGN KEY (DOC_ID) 
               REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_ID)
,CONSTRAINT ORDDCM_PRV_ATTRS_W_FK2 FOREIGN KEY (VR)
              REFERENCES ORDDATA.ORDDCM_VR_DT_MAP (VR)
);
CREATE TABLE ORDDATA.ORDDCM_DICT_ATTRS_WRK (
  DA_ID  INTEGER
, SA_ID  INTEGER
, PA_ID  INTEGER  
,CONSTRAINT ORDDCM_DICT_ATTRS_W_PK  PRIMARY KEY (DA_ID)
,CONSTRAINT ORDDCM_DICT_ATTRS_W_FK1 FOREIGN KEY (SA_ID) 
     REFERENCES ORDDATA.ORDDCM_STD_ATTRS_WRK(SA_ID)
,CONSTRAINT ORDDCM_DICT_ATTRS_W_FK2  FOREIGN KEY (PA_ID)
   REFERENCES ORDDATA.ORDDCM_PRV_ATTRS_WRK(PA_ID)
,CONSTRAINT ORDDCM_DICT_ATTRS_W_UK1  UNIQUE (SA_ID)
,CONSTRAINT ORDDCM_DICT_ATTRS_W_UK2  UNIQUE (PA_ID)
);
CREATE TABLE ORDDATA.ORDDCM_CT_DAREFS_WRK (
  DA_ID  INTEGER NOT NULL
 ,DOC_ID INTEGER NOT NULL
 ,CONSTRAINT ORDDCM_CT_DAREFS_W_FK1 FOREIGN KEY (DA_ID)
            REFERENCES ORDDATA.ORDDCM_DICT_ATTRS_WRK(DA_ID)
 ,CONSTRAINT ORDDCM_CT_DAREFS_W_FK2 FOREIGN KEY (DOC_ID)
            REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_ID)
 ,CONSTRAINT ORDDCM_CT_DAREFS_W_UK UNIQUE (DA_ID, DOC_ID)
);
CREATE TABLE ORDDATA.ORDDCM_MAPPING_DOCS_WRK
(  DOC_ID              INTEGER  NOT NULL 
  ,ROOT_TAG            VARCHAR2(128 CHAR) NOT NULL
  ,METADATA_NS         VARCHAR2(700 CHAR)  
  ,MAPPED_ELEM         VARCHAR2(128 CHAR) 
  ,UNMAPPED_ELEM       VARCHAR2(128 CHAR) 
  ,XSLT                SYS.XMLTYPE 
  ,IS_COVERED          NUMBER(1)  NOT NULL
 ,CONSTRAINT ORDDCM_MAPPING_DOCS_W_PK  PRIMARY KEY (DOC_ID)
 ,CONSTRAINT ORDDCM_MAPPING_DOCS_W_FK1  FOREIGN KEY (DOC_ID) 
             REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_ID)
);
CREATE TABLE ORDDATA.ORDDCM_MAPPED_PATHS_WRK (
   MPID                  INTEGER NOT NULL
  ,ATTR_TAG              VARCHAR2(1999)   NOT NULL
  ,DOC_ID                INTEGER NOT NULL
  ,REL_PATH              VARCHAR2(1999 CHAR) NOT NULL
  ,OCCURS                NUMBER(1) DEFAULT 0 NOT NULL
  ,NOT_EMPTY             NUMBER(1) DEFAULT 0 NOT NULL
  ,WRITE_TAG             NUMBER(1) DEFAULT 0 NOT NULL   
  ,WRITE_DEFINER         NUMBER(1) DEFAULT 0 NOT NULL
  ,WRITE_NAME            NUMBER(1) DEFAULT 0 NOT NULL  
  ,WRITE_RAW_VALUE       NUMBER(1) DEFAULT 0 NOT NULL
  ,CONSTRAINT ORDDCM_MAPPED_PATHS_W_PK1 PRIMARY KEY(MPID)
  ,CONSTRAINT ORDDCM_MAPPED_PATHS_W_FK2 FOREIGN KEY (DOC_ID) 
              REFERENCES ORDDATA.ORDDCM_MAPPING_DOCS_WRK(DOC_ID)
);
declare
  max_keylen_ex exception;
  pragma exception_init(max_keylen_ex, -1450);
begin
 begin
  execute immediate 'alter table ORDDATA.orddcm_mapped_paths_wrk add constraint orddcm_mapped_paths_w_uk1 unique(DOC_ID, ATTR_TAG)';
  exception
  when max_keylen_ex then
    null;
 end;
end;
/
CREATE TABLE ORDDATA.ORDDCM_ANON_RULES_WRK  (
    DOC_ID                INTEGER   NOT NULL
   ,RULE_TYPE_ID          NUMBER    NOT NULL
   ,ANON_ACTION_TYPE_ID   NUMBER NOT NULL
   ,ANON_VALUE            VARCHAR2(128 CHAR)   
   ,CONSTRAINT ORDDCM_ANON_RULES_W_PK1 PRIMARY KEY (DOC_ID, RULE_TYPE_ID) 
   ,CONSTRAINT ORDDCM_ANON_RULES_W_FK1 FOREIGN KEY(DOC_ID) 
              REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_ID) 
   ,CONSTRAINT ORDDCM_ANON_RULES_W_FK2 FOREIGN KEY(RULE_TYPE_ID)
              REFERENCES ORDDATA.ORDDCM_ANON_RULE_TYPES(RULE_TYPE_ID) 
   ,CONSTRAINT ORDDCM_ANON_RULES_W_FK3 FOREIGN KEY(ANON_ACTION_TYPE_ID)
              REFERENCES ORDDATA.ORDDCM_ANON_ACTION_TYPES(ACTION_TYPE_ID) 
   ,CONSTRAINT ORDDCM_ANON_RULES_W_CK1 CHECK 
        (ANON_ACTION_TYPE_ID  in (1, 2, 3) ) );
CREATE TABLE ORDDATA.ORDDCM_ANON_ATTRS_WRK  (
    AAID                 INTEGER NOT NULL
   ,DOC_ID               INTEGER NOT NULL
   ,TAG                  VARCHAR2(1999) NOT NULL
   ,ANON_ACTION_TYPE_ID  NUMBER  NOT NULL
   ,ANON_VALUE           VARCHAR2(128 CHAR)
   ,TAG_DESC             VARCHAR2(1999 CHAR)
   ,CONSTRAINT ORDDCM_ANON_ATTRS_W_PK1 PRIMARY KEY (AAID)
   ,CONSTRAINT ORDDCM_ANON_ATTRS_W_FK1 FOREIGN KEY (DOC_ID)
        REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_ID)
   ,CONSTRAINT ORDDCM_ANON_ATTRS_W_FK3 FOREIGN KEY (ANON_ACTION_TYPE_ID) 
       REFERENCES ORDDATA.ORDDCM_ANON_ACTION_TYPES(ACTION_TYPE_ID)
   ,CONSTRAINT ORDDCM_ANON_ATTRS_W_CK1 CHECK 
        (ANON_ACTION_TYPE_ID  in (1, 2, 3) ) );
declare
  max_keylen_ex exception;
  pragma exception_init(max_keylen_ex, -1450);
begin
 begin
  execute immediate 'alter table ORDDATA.orddcm_anon_attrs_wrk add constraint orddcm_anon_attrs_w_uk1 unique(DOC_ID, TAG)';
  exception
  when max_keylen_ex then
    null;
 end;
end;
/
CREATE TABLE ORDDATA.ORDDCM_RT_PREF_PARAMS_WRK (
    PPID       INTEGER NOT NULL
   ,NAME       VARCHAR2(64) NOT NULL
   ,VALUE      VARCHAR2(1999 CHAR)  NOT NULL
   ,DOC_ID     INTEGER  NOT NULL
   ,PARAM_DESC VARCHAR2(1999 CHAR) 
   ,CONSTRAINT ORDDCM_PREF_PARAMS_W_PK1 PRIMARY KEY(PPID)
   ,CONSTRAINT ORDDCM_PREF_PARAMS_W_FK1 FOREIGN KEY (DOC_ID) 
               REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_ID)
   ,CONSTRAINT ORDDCM_PREF_PARAMS_W_UK1 unique (NAME) 
);
CREATE TABLE ORDDATA.ORDDCM_UID_DEFS_WRK (
   UDID               INTEGER 
  ,DICOM_UID          VARCHAR2(128)  NOT NULL
  ,DOC_ID             INTEGER  NOT NULL
  ,NAME               VARCHAR2(128 CHAR)  NOT NULL
  ,CLASSIFICATION     VARCHAR2(64)  NOT NULL   
  ,ISLE               NUMBER(1) DEFAULT 1  NOT NULL
  ,ISEVR              NUMBER(1) DEFAULT 1  NOT NULL
  ,ISCOMPRESSED       NUMBER(1) DEFAULT 1  NOT NULL
  ,ISRETIRED          NUMBER(1) DEFAULT 0  NOT NULL
  ,CONTENTTYPE        VARCHAR2(64)  DEFAULT 'IMAGE' NOT NULL
  ,UID_DESC           VARCHAR2(1999 CHAR)  
  ,CONSTRAINT ORDDCM_UID_DEFS_W_PK  PRIMARY KEY (UDID)
  ,CONSTRAINT ORDDCM_UID_DEFS_W_UK1 UNIQUE (DICOM_UID)
  ,CONSTRAINT ORDDCM_UID_DEFS_W_FK1 FOREIGN KEY (DOC_ID) 
            REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_ID)
);
CREATE TABLE ORDDATA.ORDDCM_STORED_TAGS_WRK (
   STID                  INTEGER NOT NULL
  ,ATTR_TAG              VARCHAR2(1999)  NOT NULL
  ,DOC_ID                INTEGER NOT NULL
  ,CONSTRAINT ORDDCM_STORED_TAGS_W_PK1 PRIMARY KEY(STID)
  ,CONSTRAINT ORDDCM_STORED_TAGS_W_FK1 FOREIGN KEY (DOC_ID) 
            REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_ID)
);
declare
  max_keylen_ex exception;
  pragma exception_init(max_keylen_ex, -1450);
begin
 begin
  execute immediate 'alter table ORDDATA.orddcm_stored_tags_wrk add constraint orddcm_stored_tags_w_uk1 unique(ATTR_TAG, DOC_ID)';
  exception
  when max_keylen_ex then
    null;
 end;
end;
/
CREATE TABLE ORDDATA.ORDDCM_DATA_MODEL_WRK (
  MODEL_ID     INTEGER NOT NULL
 ,MODEL_NAME   VARCHAR2(100)  DEFAULT 'DEFAULT'
 ,ISRETIRED    NUMBER  DEFAULT 0
 ,CONSTRAINT ORDDCM_DATA_MODEL_W_PK PRIMARY KEY (MODEL_ID)
);
CREATE TABLE ORDDATA.ORDDCM_CT_PRED_WRK
(
  PID            INTEGER
  ,FPID          INTEGER
  ,POS           NUMBER(3)     NOT NULL
  ,REF_PID       INTEGER              
  ,OP            NUMBER(3)
  ,DESCRIPTION   VARCHAR2(1999 CHAR)
  ,CONSTRAINT  ORDDCM_CT_PRED_W_PK  PRIMARY KEY (PID)
  ,CONSTRAINT   ORDDCM_CT_PRED_W_FK1 FOREIGN KEY (FPID) 
            REFERENCES ORDDATA.ORDDCM_CT_PRED_WRK(PID)
  ,CONSTRAINT ORDDCM_CT_PRED_W_FK2 FOREIGN KEY (REF_PID) 
            REFERENCES ORDDATA.ORDDCM_CT_PRED_WRK(PID)  
);
CREATE INDEX ORDDATA.ORDDCM_CT_PW_FPID_IDX on ORDDATA.ORDDCM_CT_PRED_WRK(FPID);
CREATE INDEX ORDDATA.ORDDCM_CT_PW_REF_IDX on ORDDATA.ORDDCM_CT_PRED_WRK(REF_PID);
CREATE INDEX ORDDATA.ORDDCM_CT_PW_OP_IDX on ORDDATA.ORDDCM_CT_PRED_WRK(OP);
CREATE TABLE ORDDATA.ORDDCM_CT_PRED_OPRD_WRK
(
  PID           INTEGER, 
  POS           NUMBER(3),
  OPERAND       SYS.XMLTYPE,
  CONSTRAINT ORDDCM_CT_PO_W_PK UNIQUE(PID, POS),
  CONSTRAINT ORDDCM_CT_PO_W_FK FOREIGN KEY (PID)
        REFERENCES ORDDATA.ORDDCM_CT_PRED_WRK(PID)
);
CREATE INDEX ORDDATA.ORDDCM_CT_POW_PID_IDX on ORDDATA.ORDDCM_CT_PRED_OPRD_WRK(PID);
CREATE TABLE ORDDATA.ORDDCM_CT_PRED_SET_WRK
(
  PSID          INTEGER,
  NAME          VARCHAR2(64 CHAR),
  PSTYPE        NUMBER(1) NOT NULL,
  PID           INTEGER, 
  STATUS        NUMBER(1),
  SUPER         INTEGER,                
  DOC_ID        INTEGER,
  DESCRIPTION   VARCHAR2(1999 CHAR),
  CONSTRAINT ORDDCM_CT_PS_W_PK PRIMARY KEY (PSID),
  CONSTRAINT ORDDCM_CT_PS_W_FK1 FOREIGN KEY (PID)
        REFERENCES ORDDATA.ORDDCM_CT_PRED_WRK(PID),
  CONSTRAINT ORDDCM_CT_PS_W_FK2 FOREIGN KEY (SUPER)
        REFERENCES ORDDATA.ORDDCM_CT_PRED_SET_WRK(PSID),
  CONSTRAINT ORDDCM_CT_PS_W_FK3 FOREIGN KEY (DOC_ID)
        REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_ID),
  CONSTRAINT ORDDCM_CT_PS_W_UQ UNIQUE (NAME, PSTYPE, SUPER)
);
CREATE INDEX ORDDATA.ORDDCM_CT_PSW_PID_IDX on ORDDATA.ORDDCM_CT_PRED_SET_WRK(PID);
CREATE INDEX ORDDATA.ORDDCM_CT_PSW_SPR_IDX on ORDDATA.ORDDCM_CT_PRED_SET_WRK(SUPER);
CREATE INDEX ORDDATA.ORDDCM_CT_PSW_NAME_IDX on ORDDATA.ORDDCM_CT_PRED_SET_WRK(NAME);
CREATE INDEX ORDDATA.ORDDCM_CT_PSW_TYPE_IDX on ORDDATA.ORDDCM_CT_PRED_SET_WRK(PSTYPE);
CREATE INDEX ORDDATA.ORDDCM_CT_PSW_STATUS_IDX on ORDDATA.ORDDCM_CT_PRED_SET_WRK(STATUS);
CREATE INDEX ORDDATA.ORDDCM_CT_PSW_DOCID_IDX on ORDDATA.ORDDCM_CT_PRED_SET_WRK(DOC_ID);
CREATE TABLE ORDDATA.ORDDCM_CT_MACRO_PAR_WRK
(
  PSID          INTEGER,
  PARNAME       VARCHAR2(128 CHAR),
  CONSTRAINT ORDDCM_CT_MP_W_PK UNIQUE(PSID, PARNAME),
  CONSTRAINT ORDDCM_CT_MP_W_FK FOREIGN KEY(PSID)
        REFERENCES ORDDATA.ORDDCM_CT_PRED_SET_WRK(PSID)
);
CREATE INDEX ORDDATA.ORDDCM_CT_MPW_PSID_IDX on ORDDATA.ORDDCM_CT_MACRO_PAR_WRK(PSID);
CREATE TABLE ORDDATA.ORDDCM_CT_MACRO_DEP_WRK
(
  PSID          INTEGER,
  DEP_PSID      INTEGER,
  CONSTRAINT ORDDCM_CT_MD_W_FK1 FOREIGN KEY(PSID)
        REFERENCES ORDDATA.ORDDCM_CT_PRED_SET_WRK(PSID),
  CONSTRAINT ORDDCM_CT_MD_W_FK2 FOREIGN KEY(DEP_PSID)
        REFERENCES ORDDATA.ORDDCM_CT_PRED_SET_WRK(PSID),
  CONSTRAINT ORDDCM_CT_MD_W_UQ UNIQUE(PSID, DEP_PSID)
);
CREATE INDEX ORDDATA.ORDDCM_CT_MDW_PSID_IDX on ORDDATA.ORDDCM_CT_MACRO_DEP_WRK(PSID);
CREATE INDEX ORDDATA.ORDDCM_CT_MDW_DPSID_IDX on ORDDATA.ORDDCM_CT_MACRO_DEP_WRK(DEP_PSID);
CREATE TABLE ORDDATA.ORDDCM_CT_PRED_PAR_WRK
(
  PID           INTEGER,
  PARNAME       VARCHAR2(128 CHAR),
  PARVAL        VARCHAR2(1999 CHAR),
  CONSTRAINT ORDDCM_CT_PP_W_PK UNIQUE(PID, PARNAME),
  CONSTRAINT ORDDCM_CT_PP_W_FK FOREIGN KEY(PID)
         REFERENCES ORDDATA.ORDDCM_CT_PRED_WRK(PID)
);
CREATE INDEX ORDDATA.ORDDCM_CT_PPW_PID_IDX on ORDDATA.ORDDCM_CT_PRED_PAR_WRK(PID);
CREATE TABLE ORDDATA.ORDDCM_CT_LOCATORPATHS_WRK
(
  DOC_ID          INTEGER NOT NULL,
  PSID            INTEGER NOT NULL,
  LOCATOR_PATH    VARCHAR2(1999 CHAR) NOT NULL,
  HAS_MACRO_PARAM  NUMBER(1) NOT NULL,
  IS_COVERED      NUMBER(1),
  CONSTRAINT ORDDCM_CT_LP_W_FK1 FOREIGN KEY (DOC_ID)
        REFERENCES ORDDATA.ORDDCM_DOCS_WRK(DOC_ID),
  CONSTRAINT ORDDCM_CT_LP_W_FK2 FOREIGN KEY (PSID)
    REFERENCES ORDDATA.ORDDCM_CT_PRED_SET_WRK(PSID)
);
CREATE INDEX ORDDATA.ORDDCM_CT_LPW_DID_IDX On ORDDATA.ORDDCM_CT_LOCATORPATHS_WRK(DOC_ID);
CREATE INDEX ORDDATA.ORDDCM_CT_LPW_PSID_IDX On ORDDATA.ORDDCM_CT_LOCATORPATHS_WRK(PSID);
CREATE TABLE ORDDATA.ORDDCM_CT_ACTION_WRK
(
  PID           INTEGER,
  EVENT         VARCHAR2(20 CHAR),
  ACTION        VARCHAR2(20 CHAR),
  DESCRIPTION   VARCHAR2(1999 CHAR),
  CONSTRAINT ORDDCM_CT_A_W_W_PK UNIQUE(PID, EVENT, ACTION),
  CONSTRAINT ORDDCM_CT_A_W_FK1 FOREIGN KEY (PID)
    REFERENCES ORDDATA.ORDDCM_CT_PRED_WRK(PID)
);
CREATE INDEX ORDDATA.ORDDCM_CT_AW_PID_IDX on ORDDATA.ORDDCM_CT_ACTION_WRK(PID);
grant all on  ORDDATA.ORDDCM_ANON_ACTION_TYPES to ORDSYS;
grant all on         ORDDATA.ORDDCM_ANON_ATTRS to ORDSYS;
grant all on     ORDDATA.ORDDCM_ANON_ATTRS_TMP to ORDSYS;
grant all on     ORDDATA.ORDDCM_ANON_ATTRS_WRK to ORDSYS;
grant all on         ORDDATA.ORDDCM_ANON_RULES to ORDSYS;
grant all on     ORDDATA.ORDDCM_ANON_RULES_TMP to ORDSYS;
grant all on     ORDDATA.ORDDCM_ANON_RULES_WRK to ORDSYS;
grant all on    ORDDATA.ORDDCM_ANON_RULE_TYPES to ORDSYS;
grant all on    ORDDATA.ORDDCM_CT_LOCATORPATHS to ORDSYS;
grant all on ORDDATA.ORDDCM_CT_LOCATORPATHS_TMP to ORDSYS;
grant all on ORDDATA.ORDDCM_CT_LOCATORPATHS_WRK to ORDSYS;
grant all on          ORDDATA.ORDDCM_CT_ACTION to ORDSYS;
grant all on      ORDDATA.ORDDCM_CT_ACTION_TMP to ORDSYS;
grant all on      ORDDATA.ORDDCM_CT_ACTION_WRK to ORDSYS;
grant all on          ORDDATA.ORDDCM_CT_DAREFS to ORDSYS;
grant all on      ORDDATA.ORDDCM_CT_DAREFS_WRK to ORDSYS;
grant all on      ORDDATA.ORDDCM_CT_DAREFS_TMP to ORDSYS;
grant all on       ORDDATA.ORDDCM_CT_MACRO_DEP to ORDSYS;
grant all on   ORDDATA.ORDDCM_CT_MACRO_DEP_WRK to ORDSYS;
grant all on       ORDDATA.ORDDCM_CT_MACRO_PAR to ORDSYS;
grant all on   ORDDATA.ORDDCM_CT_MACRO_PAR_WRK to ORDSYS;
grant all on            ORDDATA.ORDDCM_CT_PRED to ORDSYS;
grant all on       ORDDATA.ORDDCM_CT_PRED_OPRD to ORDSYS;
grant all on   ORDDATA.ORDDCM_CT_PRED_OPRD_TMP to ORDSYS;
grant all on   ORDDATA.ORDDCM_CT_PRED_OPRD_WRK to ORDSYS;
grant all on        ORDDATA.ORDDCM_CT_PRED_PAR to ORDSYS;
grant all on    ORDDATA.ORDDCM_CT_PRED_PAR_TMP to ORDSYS;
grant all on    ORDDATA.ORDDCM_CT_PRED_PAR_WRK to ORDSYS;
grant all on        ORDDATA.ORDDCM_CT_PRED_SET to ORDSYS;
grant all on    ORDDATA.ORDDCM_CT_PRED_SET_TMP to ORDSYS;
grant all on    ORDDATA.ORDDCM_CT_PRED_SET_WRK to ORDSYS;
grant all on        ORDDATA.ORDDCM_CT_PRED_TMP to ORDSYS;
grant all on        ORDDATA.ORDDCM_CT_PRED_WRK to ORDSYS;
grant all on         ORDDATA.ORDDCM_CT_VLD_MSG to ORDSYS;
grant all on         ORDDATA.ORDDCM_DATA_MODEL to ORDSYS;
grant all on     ORDDATA.ORDDCM_DATA_MODEL_WRK to ORDSYS;
grant all on         ORDDATA.ORDDCM_DICT_ATTRS to ORDSYS;
grant all on     ORDDATA.ORDDCM_DICT_ATTRS_TMP to ORDSYS;
grant all on     ORDDATA.ORDDCM_DICT_ATTRS_WRK to ORDSYS;
grant all on               ORDDATA.ORDDCM_DOCS to ORDSYS;
grant all on           ORDDATA.ORDDCM_DOCS_TMP to ORDSYS;
grant all on           ORDDATA.ORDDCM_DOCS_WRK to ORDSYS;
grant all on           ORDDATA.ORDDCM_DOC_REFS to ORDSYS;
grant all on       ORDDATA.ORDDCM_DOC_REFS_TMP to ORDSYS;
grant all on       ORDDATA.ORDDCM_DOC_REFS_WRK to ORDSYS;
grant all on          ORDDATA.ORDDCM_DOC_TYPES to ORDSYS;
grant all on       ORDDATA.ORDDCM_INSTALL_DOCS to ORDSYS;
grant all on      ORDDATA.ORDDCM_INTERNAL_TAGS to ORDSYS;
grant all on       ORDDATA.ORDDCM_MAPPED_PATHS to ORDSYS;
grant all on   ORDDATA.ORDDCM_MAPPED_PATHS_TMP to ORDSYS;
grant all on   ORDDATA.ORDDCM_MAPPED_PATHS_WRK to ORDSYS;
grant all on       ORDDATA.ORDDCM_MAPPING_DOCS to ORDSYS;
grant all on   ORDDATA.ORDDCM_MAPPING_DOCS_TMP to ORDSYS;
grant all on   ORDDATA.ORDDCM_MAPPING_DOCS_WRK to ORDSYS;
grant all on       ORDDATA.ORDDCM_PREFS_LOOKUP to ORDSYS;
grant all on          ORDDATA.ORDDCM_PRV_ATTRS to ORDSYS;
grant all on      ORDDATA.ORDDCM_PRV_ATTRS_TMP to ORDSYS;
grant all on      ORDDATA.ORDDCM_PRV_ATTRS_WRK to ORDSYS;
grant all on     ORDDATA.ORDDCM_RT_PREF_PARAMS to ORDSYS;
grant all on ORDDATA.ORDDCM_RT_PREF_PARAMS_TMP to ORDSYS;
grant all on ORDDATA.ORDDCM_RT_PREF_PARAMS_WRK to ORDSYS;
grant all on          ORDDATA.ORDDCM_STD_ATTRS to ORDSYS;
grant all on      ORDDATA.ORDDCM_STD_ATTRS_TMP to ORDSYS;
grant all on      ORDDATA.ORDDCM_STD_ATTRS_WRK to ORDSYS;
grant all on           ORDDATA.ORDDCM_UID_DEFS to ORDSYS;
grant all on       ORDDATA.ORDDCM_UID_DEFS_TMP to ORDSYS;
grant all on       ORDDATA.ORDDCM_UID_DEFS_WRK to ORDSYS;
grant all on        ORDDATA.ORDDCM_STORED_TAGS to ORDSYS;
grant all on    ORDDATA.ORDDCM_STORED_TAGS_WRK to ORDSYS;
grant all on    ORDDATA.ORDDCM_STORED_TAGS_TMP to ORDSYS;
grant all on          ORDDATA.ORDDCM_VR_DT_MAP to ORDSYS;
grant all on         ORDDATA.ORDDCM_DOCS_ID_SEQ  to ORDSYS;
grant all on     ORDDATA.ORDDCM_STD_A_SA_ID_SEQ  to ORDSYS;
grant all on     ORDDATA.ORDDCM_PRV_A_PA_ID_SEQ  to ORDSYS;
grant all on    ORDDATA.ORDDCM_DICT_A_DA_ID_SEQ  to ORDSYS;
grant all on      ORDDATA.ORDDCM_MPD_P_MPID_SEQ  to ORDSYS; 
grant all on      ORDDATA.ORDDCM_ANON_ATTRS_SEQ  to ORDSYS;
grant all on  ORDDATA.ORDDCM_RT_PREF_P_PPID_SEQ  to ORDSYS;
grant all on   ORDDATA.ORDDCM_UID_DEFS_UDID_SEQ  to ORDSYS;
grant all on      ORDDATA.ORDDCM_DATA_MODEL_seq  to ORDSYS;
grant all on         ORDDATA.ORDDCM_CT_PRED_SEQ  to ORDSYS;
grant all on     ORDDATA.ORDDCM_CT_PRED_SET_SEQ  to ORDSYS;
grant all on ORDDATA.ORDDCM_STOREDTAGS_STID_SEQ  to ORDSYS;
alter session set current_schema=ORDSYS;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de