/*ORDER 502223*/      
      /*
      ACTION is CREATE Table CHARACTERISTIC
      */

CREATE SEQUENCE SEQ_CHARACTERISTIC
START WITH 1
INCREMENT BY 1;

CREATE TABLE CHARACTERISTIC (
       CHARACTERISTIC_ID    NUMBER NOT NULL,
       CODE                 VARCHAR2(12) NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew110
                                          CHECK (ACTIVE IN ('Y', 'N')),
       DATATYPE             NUMBER NOT NULL,
       LENGTH               NUMBER NOT NULL
                                   CONSTRAINT Length_valid
                                          CHECK (LENGTH BETWEEN 1 AND 99),
       DECIMALS             NUMBER NOT NULL
                                   CONSTRAINT Decimals_valid2
                                          CHECK (DECIMALS BETWEEN 0 AND 9),
       MANDATORY            VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew111
                                          CHECK (MANDATORY IN ('Y', 'N')),
       KEEPVALUE            VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew112
                                          CHECK (KEEPVALUE IN ('Y', 'N')),
       MINVALUE             NUMBER NULL,
       MAXVALUE             NUMBER NULL,
       DEFNUMBERVALUE       NUMBER NULL,
       DEFSTRINGVALUE       VARCHAR2(99) NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

COMMENT ON COLUMN CHARACTERISTIC.DATATYPE IS '1 = Number
2 = String
3 = Date
4 = Scientific number';
CREATE UNIQUE INDEX XAK1CHARACTERISTIC ON CHARACTERISTIC
(
       CODE                           
);


ALTER TABLE CHARACTERISTIC
       ADD  ( CONSTRAINT XPKCHARACTERISTIC PRIMARY KEY (
              CHARACTERISTIC_ID) ) ;

      
      /*
      ACTION is CREATE Table CHARACTERISTIC_DESC
      */

CREATE TABLE CHARACTERISTIC_DESC (
       CHARACTERISTIC_ID    NUMBER NOT NULL,
       LANGUAGE_ID          NUMBER NOT NULL,
       DESCRIPTION          VARCHAR2(40) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1CHARACTERISTIC_DESC ON CHARACTERISTIC_DESC
(
       CHARACTERISTIC_ID              ASC
);

CREATE INDEX XIF2CHARACTERISTIC_DESC ON CHARACTERISTIC_DESC
(
       LANGUAGE_ID                    ASC
);

ALTER TABLE CHARACTERISTIC_DESC
  ADD CONSTRAINT XPKCHARACTERISTIC_DE PRIMARY KEY (CHARACTERISTIC_ID, LANGUAGE_ID);
      
      /*
      ACTION is CREATE Table CHARACTERISTICPREDEFVALUE
      */

CREATE SEQUENCE SEQ_CHARACTERISTICPREDEFVALUE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CHARACTERISTICPREDEFVALUE (
       CHARACTERISTICPREDEFVALUE_ID NUMBER NOT NULL,
       CHARACTERISTIC_ID    NUMBER NOT NULL,
       SEQUENCENUMBER       NUMBER NOT NULL
                                   CONSTRAINT Sequence_valid
                                          CHECK (SEQUENCENUMBER BETWEEN 1 AND 99),
       NUMBERVALUE          NUMBER NULL,
       STRINGVALUE          VARCHAR2(99) NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1CHARACTERISTICPREDEFVALUE ON CHARACTERISTICPREDEFVALUE
(
       CHARACTERISTIC_ID              ASC,
       SEQUENCENUMBER                 ASC
);

CREATE INDEX XIF1CHARACTERISTICPREDEFVALUE ON CHARACTERISTICPREDEFVALUE
(
       CHARACTERISTIC_ID              ASC
);


ALTER TABLE CHARACTERISTICPREDEFVALUE
       ADD  ( CONSTRAINT XPKCHARACTERISTICPREDEFVALUE PRIMARY KEY (
              CHARACTERISTICPREDEFVALUE_ID) ) ;

      
      /*
      ACTION is CREATE Table CHARACTERISTICSTEMPLATE
      */

CREATE SEQUENCE SEQ_CHARACTERISTICSTEMPLATE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CHARACTERISTICSTEMPLATE (
       CHARACTERISTICSTEMPLATE_ID NUMBER NOT NULL,
       CODE                 VARCHAR2(12) NOT NULL,
       TYPE                 NUMBER NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew113
                                          CHECK (ACTIVE IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

COMMENT ON COLUMN CHARACTERISTICSTEMPLATE.TYPE IS '1 = Treatment type';
CREATE UNIQUE INDEX XAK1CHARACTERISTICSTEMPLATE ON CHARACTERISTICSTEMPLATE
(
       CODE                           
);


ALTER TABLE CHARACTERISTICSTEMPLATE
       ADD  ( CONSTRAINT XPKCHARACTERISTICSTEMPLATE PRIMARY KEY (
              CHARACTERISTICSTEMPLATE_ID) ) ;

      
      /*
      ACTION is CREATE Table CHARACTERISTICSTEMPLATE_DESC
      */

CREATE TABLE CHARACTERISTICSTEMPLATE_DESC (
       CHARACTERISTICSTEMPLATE_ID NUMBER NOT NULL,
       LANGUAGE_ID          NUMBER NOT NULL,
       DESCRIPTION          VARCHAR2(40) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF3CHARACTERISTICSTEMPLATE_DE ON CHARACTERISTICSTEMPLATE_DESC
(
       CHARACTERISTICSTEMPLATE_ID     ASC
);

CREATE INDEX XIF4CHARACTERISTICSTEMPLATE_DE ON CHARACTERISTICSTEMPLATE_DESC
(
       LANGUAGE_ID                    ASC
);

ALTER TABLE CHARACTERISTICSTEMPLATE_DESC
  ADD CONSTRAINT XPKCHARACTERISTICSTEMPLATE_DE PRIMARY KEY (CHARACTERISTICSTEMPLATE_ID, LANGUAGE_ID);
      
      /*
      ACTION is CREATE Table CHARACTERISTICSTEMPLINE
      */

CREATE SEQUENCE SEQ_CHARACTERISTICSTEMPLINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CHARACTERISTICSTEMPLINE (
       CHARACTERISTICSTEMPLINE_ID NUMBER NOT NULL,
       CHARACTERISTICSTEMPLATE_ID NUMBER NOT NULL,
       CHARACTERISTIC_ID    NUMBER NOT NULL,
       SEQUENCENUMBER       NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1CHARACTERISTICSTEMPLINE ON CHARACTERISTICSTEMPLINE
(
       CHARACTERISTICSTEMPLATE_ID     ,
       CHARACTERISTIC_ID              
);

CREATE UNIQUE INDEX XAK2CHARACTERISTICSTEMPLINE ON CHARACTERISTICSTEMPLINE
(
       CHARACTERISTICSTEMPLATE_ID     ,
       SEQUENCENUMBER                 
);

CREATE INDEX XIF1CHARACTERISTICSTEMPLINE ON CHARACTERISTICSTEMPLINE
(
       CHARACTERISTICSTEMPLATE_ID     ASC
);

CREATE INDEX XIF2CHARACTERISTICSTEMPLINE ON CHARACTERISTICSTEMPLINE
(
       CHARACTERISTIC_ID              ASC
);


ALTER TABLE CHARACTERISTICSTEMPLINE
       ADD  ( CONSTRAINT XPKCHARACTERISTICSTEMPLINE PRIMARY KEY (
              CHARACTERISTICSTEMPLINE_ID) ) ;

      
      /*
      ACTION is CREATE Table TREATMENTTYPE
      */
CREATE SEQUENCE SEQ_TREATMENTTYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE TREATMENTTYPE (
       TREATMENTTYPE_ID     NUMBER NOT NULL,
       CODE                 VARCHAR2(12) NOT NULL,
       REGISTERBYDEFAULT    VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew114
                                          CHECK (REGISTERBYDEFAULT IN ('Y', 'N')),
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew115
                                          CHECK (ACTIVE IN ('Y', 'N')),
       LEGACYCODE           VARCHAR2(12) NULL,
       CHARACTERISTICSTEMPLATE_ID NUMBER NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1TREATMENTTYPE ON TREATMENTTYPE
(
       CODE                           
);

CREATE INDEX XIF1TREATMENTTYPE ON TREATMENTTYPE
(
       CHARACTERISTICSTEMPLATE_ID     ASC
);


ALTER TABLE TREATMENTTYPE
       ADD  ( CONSTRAINT XPKTREATMENTTYPE PRIMARY KEY (
              TREATMENTTYPE_ID) ) ;

 /*
      ACTION is CREATE Table TREATMENTTYPE_DESC
      */

CREATE TABLE TREATMENTTYPE_DESC (
       TREATMENTTYPE_ID     NUMBER NOT NULL,
       LANGUAGE_ID          NUMBER NOT NULL,
       DESCRIPTION          VARCHAR2(40) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1TREATMENTTYPE_DESC ON TREATMENTTYPE_DESC
(
       TREATMENTTYPE_ID               ASC
);

CREATE INDEX XIF2TREATMENTTYPE_DESC ON TREATMENTTYPE_DESC
(
       LANGUAGE_ID                    ASC
);

ALTER TABLE TREATMENTTYPE_DESC
       ADD  ( CONSTRAINT XPKTREATMENTTYPE_DESC PRIMARY KEY (
              TREATMENTTYPE_ID, LANGUAGE_ID) ) ;
      
      /*
      ACTION is CREATE Table TREATMENTSCAN
      */

CREATE SEQUENCE SEQ_TREATMENTSCAN
START WITH 1
INCREMENT BY 1;

CREATE TABLE TREATMENTSCAN (
       TREATMENTSCAN_ID     NUMBER NOT NULL,
       SCAN_ID              NUMBER NOT NULL,
       TREATMENTTYPE_ID     NUMBER NOT NULL,
       TREATMENTDATE        DATE NOT NULL,
       NOTES                VARCHAR2(1000) NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1TREATMENTSCAN ON TREATMENTSCAN
(
       SCAN_ID                        ASC
);

CREATE INDEX XIF2TREATMENTSCAN ON TREATMENTSCAN
(
       TREATMENTTYPE_ID               ASC
);


ALTER TABLE TREATMENTSCAN
       ADD  ( CONSTRAINT XPKTREATMENTSCAN PRIMARY KEY (
              TREATMENTSCAN_ID) ) ;

      
      /*
      ACTION is CREATE Table TREATMENTSCANCHARACTERISTIC
      */

CREATE TABLE TREATMENTSCANCHARACTERISTIC (
       TREATMENTSCAN_ID     NUMBER NOT NULL,
       CHARACTERISTIC_ID    NUMBER NOT NULL,
       NUMBERVALUE          NUMBER NULL,
       STRINGVALUE          VARCHAR2(99) NULL,
       DATEVALUE            DATE NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1TREATMENTSCANCHARACTERISTI ON TREATMENTSCANCHARACTERISTIC
(
       TREATMENTSCAN_ID               ASC
);

CREATE INDEX XIF2TREATMENTSCANCHARACTERISTI ON TREATMENTSCANCHARACTERISTIC
(
       CHARACTERISTIC_ID              ASC
);

ALTER TABLE TREATMENTSCANCHARACTERISTIC
       ADD  ( CONSTRAINT XPKTREATMENTSCANCHARACTERISTIC PRIMARY KEY (
              TREATMENTSCAN_ID, CHARACTERISTIC_ID) ) ;

      /*
      ACTION is CREATE Table TREATMENTTYPEPERBU
      */

CREATE SEQUENCE SEQ_TREATMENTTYPEPERBU
START WITH 1
INCREMENT BY 1;

CREATE TABLE TREATMENTTYPEPERBU (
       TREATMENTTYPEPERBU_ID NUMBER NOT NULL,
       BUSINESSUNIT_ID      NUMBER NOT NULL,
       TREATMENTTYPE_ID     NUMBER NOT NULL,
       SEQUENCENUMBER       NUMBER DEFAULT 1 NOT NULL
                                   CONSTRAINT Sequence_valid2
                                          CHECK (SEQUENCENUMBER >= 1),
       REGISTERBYDEFAULT    VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew116
                                          CHECK (REGISTERBYDEFAULT IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1TREATMENTTYPEPERBU ON TREATMENTTYPEPERBU
(
       BUSINESSUNIT_ID                ASC
);

CREATE INDEX XIF2TREATMENTTYPEPERBU ON TREATMENTTYPEPERBU
(
       TREATMENTTYPE_ID               ASC
);

CREATE UNIQUE INDEX XAK1TREATMENTTYPEPERBU ON TREATMENTTYPEPERBU
(
       BUSINESSUNIT_ID                ,
       TREATMENTTYPE_ID               
);

CREATE UNIQUE INDEX XAK2TREATMENTTYPEPERBU ON TREATMENTTYPEPERBU
(
       BUSINESSUNIT_ID                ,
       SEQUENCENUMBER                 
);

ALTER TABLE TREATMENTTYPEPERBU
       ADD  ( CONSTRAINT XPKTREATMENTTYPEPERBU PRIMARY KEY (
              TREATMENTTYPEPERBU_ID) ) ;

ALTER TABLE CHARACTERISTIC_DESC
       ADD  ( CONSTRAINT CHARACTERISTIC_D_LANGUAGE_ID
              FOREIGN KEY (LANGUAGE_ID)
                             REFERENCES LANGUAGES ) ;


ALTER TABLE CHARACTERISTIC_DESC
       ADD  ( CONSTRAINT CHARACTERISTIC_D_CHARACTERISTI
              FOREIGN KEY (CHARACTERISTIC_ID)
                             REFERENCES CHARACTERISTIC
                             ON DELETE CASCADE ) ;


ALTER TABLE CHARACTERISTICPREDEFVALUE
       ADD  ( CONSTRAINT CHARACTERISTICPREDEFVALUE_CHAR
              FOREIGN KEY (CHARACTERISTIC_ID)
                             REFERENCES CHARACTERISTIC
                             ON DELETE CASCADE ) ;

ALTER TABLE CHARACTERISTICSTEMPLATE_DESC
       ADD  ( CONSTRAINT CHARACTERISTICSTEMPLATE_D_LANG
              FOREIGN KEY (LANGUAGE_ID)
                             REFERENCES LANGUAGES ) ;


ALTER TABLE CHARACTERISTICSTEMPLATE_DESC
       ADD  ( CONSTRAINT CHARACTERISTICSTEMPLATE_D_CHAR
              FOREIGN KEY (CHARACTERISTICSTEMPLATE_ID)
                             REFERENCES CHARACTERISTICSTEMPLATE
                             ON DELETE CASCADE ) ;

ALTER TABLE CHARACTERISTICSTEMPLINE
       ADD  ( CONSTRAINT CHARACTERISTICSTEMPLINE_CH
              FOREIGN KEY (CHARACTERISTIC_ID)
                             REFERENCES CHARACTERISTIC ) ;


ALTER TABLE CHARACTERISTICSTEMPLINE
       ADD  ( CONSTRAINT CHARACTERISTICSTEMPLINE_CHAR
              FOREIGN KEY (CHARACTERISTICSTEMPLATE_ID)
                             REFERENCES CHARACTERISTICSTEMPLATE
                             ON DELETE CASCADE ) ;

ALTER TABLE TREATMENTTYPE
       ADD  ( CONSTRAINT TREATMENTTYPE_CHARACTERISTICST
              FOREIGN KEY (CHARACTERISTICSTEMPLATE_ID)
                             REFERENCES CHARACTERISTICSTEMPLATE ) ;

ALTER TABLE TREATMENTTYPE_DESC
       ADD  ( CONSTRAINT TREATMENTTYPE_D_LANGUAGE_ID
              FOREIGN KEY (LANGUAGE_ID)
                             REFERENCES LANGUAGES
                             ON DELETE CASCADE ) ;

ALTER TABLE TREATMENTTYPE_DESC
       ADD  ( CONSTRAINT TREATMENTTYPE_D_TREATMENTTYPE_
              FOREIGN KEY (TREATMENTTYPE_ID)
                             REFERENCES TREATMENTTYPE
                             ON DELETE CASCADE ) ;

ALTER TABLE TREATMENTSCAN
       ADD  ( CONSTRAINT TREATMENTSCAN_TREATMENTTYPE_ID
              FOREIGN KEY (TREATMENTTYPE_ID)
                             REFERENCES TREATMENTTYPE ) ;

ALTER TABLE TREATMENTSCAN
       ADD  ( CONSTRAINT TREATMENTSCAN_SCAN_ID
              FOREIGN KEY (SCAN_ID)
                             REFERENCES SCAN
                             ON DELETE CASCADE ) ;

ALTER TABLE TREATMENTSCANCHARACTERISTIC
       ADD  ( CONSTRAINT TREATMENTSCANCHARACTERISTIC_CH
              FOREIGN KEY (CHARACTERISTIC_ID)
                             REFERENCES CHARACTERISTIC ) ;


ALTER TABLE TREATMENTSCANCHARACTERISTIC
       ADD  ( CONSTRAINT TREATMENTSCANCHARACTERISTIC_TR
              FOREIGN KEY (TREATMENTSCAN_ID)
                             REFERENCES TREATMENTSCAN
                             ON DELETE CASCADE ) ;

ALTER TABLE TREATMENTTYPEPERBU
       ADD  ( CONSTRAINT TREATMENTTYPEPERBU_TREATMENTTY
              FOREIGN KEY (TREATMENTTYPE_ID)
                             REFERENCES TREATMENTTYPE
                             ON DELETE CASCADE ) ;


ALTER TABLE TREATMENTTYPEPERBU
       ADD  ( CONSTRAINT TREATMENTTYPEPERBU_BUSINESSUNI
              FOREIGN KEY (BUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT
                             ON DELETE CASCADE ) ;

















