-- order 502421
      /*
      ACTION is CREATE Table TREATMENTCHECKSCHEME
      */

CREATE SEQUENCE SEQ_TREATMENTCHECKSCHEME
START WITH 1
INCREMENT BY 1;

CREATE TABLE TREATMENTCHECKSCHEME (
       TREATMENTCHECKSCHEME_ID NUMBER NOT NULL,
       CODE                 VARCHAR2(6) NOT NULL,
       DESCRIPTION          VARCHAR2(30) NOT NULL,
       ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew180
                                          CHECK (ACTIVE IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1TREATMENTCHECKSCHEME ON TREATMENTCHECKSCHEME
(
       CODE                           ASC
);


ALTER TABLE TREATMENTCHECKSCHEME
       ADD  ( CONSTRAINT XPKTREATMENTCHECKSCHEME PRIMARY KEY (
              TREATMENTCHECKSCHEME_ID) ) ;

      /*
      ACTION is CREATE Table TREATMENTCHECKSCHEMEDETAIL
      */

CREATE SEQUENCE SEQ_TREATMENTCHECKSCHEMEDETAIL
START WITH 1
INCREMENT BY 1;

CREATE TABLE TREATMENTCHECKSCHEMEDETAIL (
       TREATMENTCHECKSCHEMEDETAIL_ID NUMBER NOT NULL,
       TREATMENTCHECKSCHEME_ID NUMBER NOT NULL,
       SCHEMETYPE           NUMBER(1) DEFAULT 0 NOT NULL
                                   CONSTRAINT SchemeType_valid
                                          CHECK (SCHEMETYPE IN (0, 1)),
       WASHES               NUMBER(4) DEFAULT 0 NOT NULL
                                   CONSTRAINT Washes_valid
                                          CHECK (WASHES BETWEEN 0 AND 9999),
       FROMWASHES           NUMBER(4) DEFAULT 0 NOT NULL
                                   CONSTRAINT Washes_valid2
                                          CHECK (FROMWASHES BETWEEN 0 AND 9999),
       TOWASHES             NUMBER(4) DEFAULT 0 NOT NULL
                                   CONSTRAINT Washes_valid3
                                          CHECK (TOWASHES BETWEEN 0 AND 9999),
       REPEATWASHES         NUMBER(4) DEFAULT 0 NOT NULL
                                   CONSTRAINT Washes_valid4
                                          CHECK (REPEATWASHES BETWEEN 0 AND 9999),
       CHECKPERCENTAGE      NUMBER(3) DEFAULT 100 NOT NULL
                                   CONSTRAINT CheckPercentage_valid
                                          CHECK (CHECKPERCENTAGE BETWEEN 0 AND 100),
       FLAG_ID              NUMBER NOT NULL,
       TREATMENTTYPE_ID     NUMBER NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL       
);

COMMENT ON COLUMN TREATMENTCHECKSCHEMEDETAIL.SCHEMETYPE IS '0 = Fixed
1 = Repeated';

CREATE INDEX XIF1TREATMENTCHECKSCHEMEDETAIL ON TREATMENTCHECKSCHEMEDETAIL
(
       TREATMENTCHECKSCHEME_ID        ASC
);

CREATE INDEX XIF2TREATMENTCHECKSCHEMEDETAIL ON TREATMENTCHECKSCHEMEDETAIL
(
       FLAG_ID                        ASC
);

CREATE INDEX XIF3TREATMENTCHECKSCHEMEDETAIL ON TREATMENTCHECKSCHEMEDETAIL
(
       TREATMENTTYPE_ID               ASC
);

CREATE INDEX XIE1TREATMENTCHECKSCHEMEDETAIL ON TREATMENTCHECKSCHEMEDETAIL
(
       TREATMENTCHECKSCHEME_ID        ASC,
       SCHEMETYPE                     ASC,
       WASHES                         ASC,
       FROMWASHES                     ASC
);


ALTER TABLE TREATMENTCHECKSCHEMEDETAIL
       ADD  ( CONSTRAINT XPKTREATMENTCHECKSCHEMEDETAIL PRIMARY KEY (
              TREATMENTCHECKSCHEMEDETAIL_ID) ) ;  

ALTER TABLE TREATMENTCHECKSCHEMEDETAIL ADD CONSTRAINT WASHES_CONSTRAINT
CHECK ((WASHES=0 and SCHEMETYPE=1) or (WASHES>0 and SCHEMETYPE=0));

ALTER TABLE TREATMENTCHECKSCHEMEDETAIL ADD CONSTRAINT FROMWASHES_CONSTRAINT
CHECK ((FROMWASHES>0 and SCHEMETYPE=1) or (FROMWASHES=0 and SCHEMETYPE=0));

ALTER TABLE TREATMENTCHECKSCHEMEDETAIL ADD CONSTRAINT TOWASHES_CONSTRAINT
CHECK ((TOWASHES>=FROMWASHES and SCHEMETYPE=1) or (TOWASHES=0 and SCHEMETYPE=0));

ALTER TABLE TREATMENTCHECKSCHEMEDETAIL ADD CONSTRAINT REPEATWASHES_CONSTRAINT
CHECK ((REPEATWASHES>0 and SCHEMETYPE=1) or (REPEATWASHES=0 and SCHEMETYPE=0));
    
      /*
      ACTION is CREATE Table PACKCONTENTSALTERNATIVE
      */

CREATE SEQUENCE SEQ_PACKCONTENTSALTERNATIVE
START WITH 1
INCREMENT BY 1;

CREATE TABLE PACKCONTENTSALTERNATIVE (
       PACKCONTENTSALTERNATIVE_ID NUMBER NOT NULL,
       PACKCONTENTS_ID      NUMBER NOT NULL,
       SEQUENCENUMBER       NUMBER(1) NOT NULL,
       PRODUCT_ID           NUMBER NOT NULL,
       SIZEDEFINITION_ID    NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1PACKCONTENTSALTERNATIVE ON PACKCONTENTSALTERNATIVE
(
       PACKCONTENTS_ID                ASC
);

CREATE INDEX XIF2PACKCONTENTSALTERNATIVE ON PACKCONTENTSALTERNATIVE
(
       PRODUCT_ID                     ASC
);

CREATE INDEX XIF3PACKCONTENTSALTERNATIVE ON PACKCONTENTSALTERNATIVE
(
       SIZEDEFINITION_ID              ASC
);


ALTER TABLE PACKCONTENTSALTERNATIVE
       ADD  ( CONSTRAINT XPKPACKCONTENTSALTERNATIVE PRIMARY KEY (
              PACKCONTENTSALTERNATIVE_ID) ) ;


      /*
      CHANGE REPORT for Table PACKCONTENTS
          - Adding column UNIQUELYCODED
          - Adding column DATEACTIVE
          - Adding column DATEINACTIVE
      ACTION is ALTER or modify Table PACKCONTENTS
      */

ALTER TABLE PACKCONTENTS ADD (UNIQUELYCODED        VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew181
                                          CHECK (UNIQUELYCODED IN ('Y', 'N')));


ALTER TABLE PACKCONTENTS ADD (DATEACTIVE           DATE NULL);


ALTER TABLE PACKCONTENTS ADD (DATEINACTIVE         DATE NULL);

update packcontents pc 
set pc.dateactive = pc.timestamp;

alter table packcontents modify (dateactive not null);

update packcontents pc 
set pc.dateinactive = to_date('31-12-2099','dd-mm-yyyy');

alter table packcontents modify (dateinactive not null);

drop index XAK3PACKCONTENTS;

create unique index XAK3PACKCONTENTS on PACKCONTENTS
(PACK_ID, PACKSEQUENCE, PRODUCT_ID, SIZEDEFINITION_ID, FINISHINGMETHOD_ID,DATEACTIVE);


      
      /*
      CHANGE REPORT for Table PRODUCT
          - Adding a new relationship PROD_TREATMENTCHECKSCHEME_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK PROD_TREATMENTCHECKSCHEME_ID of table PRODUCT, which references table TREATMENTCHECKSCHEME).
          - Adding column PACKTYPE
          - Adding column TREATMENTCHECKSCHEME_ID
          - Adding column LOTNUMBERREQUIRED
          - Adding column ORPACKCONTENTSALLOWED
          - Adding index XIF27PRODUCT
      ACTION is ALTER or modify Table PRODUCT
      */

COMMENT ON COLUMN PRODUCT.PACK IS 'This attribute has become obsolete, but for existing reports and functionality it will be maintained.';

ALTER TABLE PRODUCT ADD (PACKTYPE             NUMBER(1) DEFAULT 0 NOT NULL
                                   CONSTRAINT PackType_valid
                                          CHECK (PACKTYPE IN (0, 1, 2)));

COMMENT ON COLUMN PRODUCT.PACKTYPE IS '0 = No pack
1 = Non-OR Pack
2 = OR Pack';

COMMENT ON COLUMN PRODUCT.MAXWASHES IS 'This attribute is not used anymore, replaced with TreatmentCheckScheme';

ALTER TABLE PRODUCT ADD (TREATMENTCHECKSCHEME_ID NUMBER NULL);


ALTER TABLE PRODUCT ADD (LOTNUMBERREQUIRED    VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew182
                                          CHECK (LOTNUMBERREQUIRED IN ('Y', 'N')
    ));


ALTER TABLE PRODUCT ADD (ORPACKCONTENTSALLOWED NUMBER(1) DEFAULT 0 NOT NULL
                                   CONSTRAINT OrPackContentsAllowed_valid
                                          CHECK (ORPACKCONTENTSALLOWED IN (0, 1,
     2, 3)));

COMMENT ON COLUMN PRODUCT.ORPACKCONTENTSALLOWED IS '0 = Not allowed as pack contents
1 = Non-sterile pack contents allowed
2 = Sterile pack contents allowed
3 = Both sterile and non-sterile pack contents allowed.';


CREATE INDEX XIF27PRODUCT ON PRODUCT
(
       TREATMENTCHECKSCHEME_ID        ASC
);

      
      /*
      CHANGE REPORT for Table PRODUCTLISTPERCUSTOMER
          - Adding a new relationship PLPC_DELIVERYLABELLAYOUT_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK PLPC_DELIVERYLABELLAYOUT_ID of table PRODUCTLISTPERCUSTOMER, which references table LABELLAYOUT).
          - Adding a new relationship PLPC_TREATMENTCHECKSCHEME_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK PLPC_TREATMENTCHECKSCHEME_ID of table PRODUCTLISTPERCUSTOMER, which references table TREATMENTCHECKSCHEME).
          - Adding column TREATMENTCHECKSCHEME_ID
          - Adding column DELIVERYLABELLAYOUT_ID
          - Adding index XIF16PRODUCTLISTPERCUSTOMER
          - Adding index XIF17PRODUCTLISTPERCUSTOMER
      ACTION is ALTER or modify Table PRODUCTLISTPERCUSTOMER
      */

ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (TREATMENTCHECKSCHEME_ID NUMBER NULL);


ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (DELIVERYLABELLAYOUT_ID NUMBER NULL);

CREATE INDEX XIF16PRODUCTLISTPERCUSTOMER ON PRODUCTLISTPERCUSTOMER
(
       TREATMENTCHECKSCHEME_ID        ASC
);

CREATE INDEX XIF17PRODUCTLISTPERCUSTOMER ON PRODUCTLISTPERCUSTOMER
(
       DELIVERYLABELLAYOUT_ID         ASC
);


      /*
      ACTION is CREATE Table PRODUCTORPACK
      */

CREATE TABLE PRODUCTORPACK (
       PRODUCT_ID           NUMBER NOT NULL,
       STERILE              NUMBER(1) DEFAULT 0 NOT NULL
                                   CONSTRAINT Sterile_valid
                                          CHECK (STERILE IN (0, 1)),
       TREATMENTTYPE_ID     NUMBER NULL,
       EXPIRATIONCODE_ID    NUMBER NULL,
       MINACTIVESTOCKLEVEL  NUMBER(4) DEFAULT 0 NOT NULL
                                   CONSTRAINT MinActiveStockLevel_valid
                                          CHECK (MINACTIVESTOCKLEVEL BETWEEN 0 AND 9999),
       ASSEMBLYLABELLAYOUT_ID NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

COMMENT ON COLUMN PRODUCTORPACK.STERILE IS '0 = Not sterile
1 = Sterile';

CREATE INDEX XIF2PRODUCTORPACK ON PRODUCTORPACK
(
       TREATMENTTYPE_ID               ASC
);

CREATE INDEX XIF3PRODUCTORPACK ON PRODUCTORPACK
(
       EXPIRATIONCODE_ID              ASC
);

CREATE INDEX XIF4PRODUCTORPACK ON PRODUCTORPACK
(
       ASSEMBLYLABELLAYOUT_ID         ASC
);


ALTER TABLE PRODUCTORPACK
       ADD  ( CONSTRAINT XPKPRODUCTORPACK PRIMARY KEY (PRODUCT_ID) ) ;

ALTER TABLE PACKCONTENTSALTERNATIVE
       ADD  ( CONSTRAINT PACKCONTENTSALTERNATIVE_SIZEDE
              FOREIGN KEY (SIZEDEFINITION_ID)
                             REFERENCES SIZEDEFINITION ) ;


ALTER TABLE PACKCONTENTSALTERNATIVE
       ADD  ( CONSTRAINT PACKCONTENTSALTERNATIVE_PRODUC
              FOREIGN KEY (PRODUCT_ID)
                             REFERENCES PRODUCT ) ;


ALTER TABLE PACKCONTENTSALTERNATIVE
       ADD  ( CONSTRAINT PACKCONTENTSALTERNATIVE_PACKCO
              FOREIGN KEY (PACKCONTENTS_ID)
                             REFERENCES PACKCONTENTS
                             ON DELETE CASCADE ) ;


ALTER TABLE TREATMENTCHECKSCHEMEDETAIL
       ADD  ( CONSTRAINT TREATMENTCHECKSCHEMEDETAIL_TRE
              FOREIGN KEY (TREATMENTTYPE_ID)
                             REFERENCES TREATMENTTYPE ) ;


ALTER TABLE TREATMENTCHECKSCHEMEDETAIL
       ADD  ( CONSTRAINT TREATMENTCHECKSCHEMEDETAIL_FLA
              FOREIGN KEY (FLAG_ID)
                             REFERENCES FLAG ) ;


ALTER TABLE TREATMENTCHECKSCHEMEDETAIL
       ADD  ( CONSTRAINT TREATMENTCHECKSCHEMEDETAIL_TR
              FOREIGN KEY (TREATMENTCHECKSCHEME_ID)
                             REFERENCES TREATMENTCHECKSCHEME
                             ON DELETE CASCADE ) ;


ALTER TABLE PRODUCT
       ADD  ( CONSTRAINT PROD_TREATMENTCHECKSCHEME_ID
              FOREIGN KEY (TREATMENTCHECKSCHEME_ID)
                             REFERENCES TREATMENTCHECKSCHEME ) ;


ALTER TABLE PRODUCTLISTPERCUSTOMER
       ADD  ( CONSTRAINT PLPC_DELIVERYLABELLAYOUT_ID
              FOREIGN KEY (DELIVERYLABELLAYOUT_ID)
                             REFERENCES LABELLAYOUT ) ;


ALTER TABLE PRODUCTLISTPERCUSTOMER
       ADD  ( CONSTRAINT PLPC_TREATMENTCHECKSCHEME_ID
              FOREIGN KEY (TREATMENTCHECKSCHEME_ID)
                             REFERENCES TREATMENTCHECKSCHEME ) ;


ALTER TABLE PRODUCTORPACK
       ADD  ( CONSTRAINT PRODUCTORPACK_ASSEMBLYLABELLAY
              FOREIGN KEY (ASSEMBLYLABELLAYOUT_ID)
                             REFERENCES LABELLAYOUT ) ;


ALTER TABLE PRODUCTORPACK
       ADD  ( CONSTRAINT PRODUCTORPACK_EXPIRATIONCODE_I
              FOREIGN KEY (EXPIRATIONCODE_ID)
                             REFERENCES EXPIRATIONCODE ) ;


ALTER TABLE PRODUCTORPACK
       ADD  ( CONSTRAINT PRODUCTORPACK_TREATMENTTYPE_ID
              FOREIGN KEY (TREATMENTTYPE_ID)
                             REFERENCES TREATMENTTYPE ) ;


ALTER TABLE PRODUCTORPACK
       ADD  ( CONSTRAINT PRODUCTORPACK_PRODUCT_ID
              FOREIGN KEY (PRODUCT_ID)
                             REFERENCES PRODUCT
                             ON DELETE CASCADE ) ;

ALTER TABLE PRODUCTORPACK ADD CONSTRAINT PRODUCTORPACK_CONSTRAINT
CHECK ((STERILE = 0 AND TREATMENTTYPE_ID IS NULL AND EXPIRATIONCODE_ID IS NULL) OR 
(STERILE <> 0 AND TREATMENTTYPE_ID IS NOT NULL AND EXPIRATIONCODE_ID IS NOT NULL));

