--order 502640      
      /*
      CHANGE REPORT for Table UNIQUEORPACKSTERILE
          - Adding a new relationship UNIQUEORPACKSTERILE_STERILIZAT
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK UNIQUEORPACKSTERILE_STERILIZAT of table UNIQUEORPACKSTERILE, which references table EMPLOYEE).
          - Adding column STERILIZATIONRUN_ID
          - Adding column CART_ID
          - Changing definition of index XIF3UNIQUEORPACKSTERILE
          - Changing definition of index XIF5UNIQUEORPACKSTERILE
          - Changing definition of index XPKUNIQUEORPACKSTERILE
          - Changing primary key
          WARNING : Drop PK of table UNIQUEORPACKSTERILE will fail if there are any FKs that reference the table and are not on the DB Sync - Resolve Differences list.
      ACTION is ALTER or modify Table UNIQUEORPACKSTERILE
      */

DROP INDEX XIE1UNIQUEORPACKSTERILE;

DROP INDEX XIF2UNIQUEORPACKSTERILE;

DROP INDEX XIF3UNIQUEORPACKSTERILE;

DROP INDEX XIF5UNIQUEORPACKSTERILE;

ALTER TABLE UNIQUEORPACKSTERILE DROP CONSTRAINT UNIQUEORPACKSTERILE_STERILIZER;

ALTER TABLE UNIQUEORPACKSTERILE DROP CONSTRAINT UNIQUEORPACKSTERILE_REJECTREAS;

ALTER TABLE UNIQUEORPACKSTERILE DROP CONSTRAINT UNIQUEORPACKSTERILE_ORBUSINESS;


ALTER TABLE UNIQUEORPACKSTERILE DROP PRIMARY KEY CASCADE;

ALTER TABLE UNIQUEORPACKSTERILE DROP COLUMN CHARGENUMBER;

ALTER TABLE UNIQUEORPACKSTERILE DROP COLUMN STERILIZER_ID;

ALTER TABLE UNIQUEORPACKSTERILE DROP COLUMN REJECTREASONCODE_ID;

ALTER TABLE UNIQUEORPACKSTERILE DROP COLUMN ORBUSINESSUNIT_ID;


ALTER TABLE UNIQUEORPACKSTERILE ADD (STERILIZATIONRUN_ID  NUMBER NOT NULL);


ALTER TABLE UNIQUEORPACKSTERILE ADD (CART_ID          NUMBER NOT NULL);

CREATE INDEX XIF3UNIQUEORPACKSTERILE ON UNIQUEORPACKSTERILE
(
       STERILIZATIONRUN_ID            ASC
);

CREATE INDEX XIF5UNIQUEORPACKSTERILE ON UNIQUEORPACKSTERILE
(
       CART_ID            ASC
);


ALTER TABLE UNIQUEORPACKSTERILE
       ADD  ( CONSTRAINT XPKUNIQUEORPACKSTERILE PRIMARY KEY (
              UNIQUEITEM_ID, STERILIZATIONRUN_ID) ) ;

      
      /*
      ACTION is CREATE Table STERILIZATIONRUN
      */

CREATE SEQUENCE SEQ_STERILIZATIONRUN
START WITH 1
INCREMENT BY 1;

CREATE TABLE STERILIZATIONRUN (
       STERILIZATIONRUN_ID  NUMBER NOT NULL,
       BUSINESSUNIT_ID      NUMBER NOT NULL,
       CHARGENUMBER         NUMBER(15) NOT NULL,
       EMPLOYEE_ID          NUMBER NOT NULL,
       STERILIZER_ID        NUMBER NOT NULL,
       TREATMENTTYPE_ID     NUMBER NOT NULL,
       REJECTREASONCODE_ID  NUMBER NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1STERILIZATIONRUN ON STERILIZATIONRUN
(
       BUSINESSUNIT_ID                ,
       CHARGENUMBER                   
);

CREATE INDEX XIF1STERILIZATIONRUN ON STERILIZATIONRUN
(
       BUSINESSUNIT_ID                ASC
);

CREATE INDEX XIF2STERILIZATIONRUN ON STERILIZATIONRUN
(
       EMPLOYEE_ID                    ASC
);

CREATE INDEX XIF3STERILIZATIONRUN ON STERILIZATIONRUN
(
       STERILIZER_ID                  ASC
);

CREATE INDEX XIF4STERILIZATIONRUN ON STERILIZATIONRUN
(
       TREATMENTTYPE_ID               ASC
);

CREATE INDEX XIF5STERILIZATIONRUN ON STERILIZATIONRUN
(
       REJECTREASONCODE_ID            ASC
);


ALTER TABLE STERILIZATIONRUN
       ADD  ( CONSTRAINT XPKSTERILIZATIONRUN PRIMARY KEY (
              STERILIZATIONRUN_ID) ) ;


ALTER TABLE STERILIZATIONRUN
       ADD  ( CONSTRAINT STERILIZATIONRUN_REJECTREASONC
              FOREIGN KEY (REJECTREASONCODE_ID)
                             REFERENCES REASONCODE ) ;


ALTER TABLE STERILIZATIONRUN
       ADD  ( CONSTRAINT STERILIZATIONRUN_TREATMENTTYPE
              FOREIGN KEY (TREATMENTTYPE_ID)
                             REFERENCES TREATMENTTYPE ) ;


ALTER TABLE STERILIZATIONRUN
       ADD  ( CONSTRAINT STERILIZATIONRUN_STERILIZER_ID
              FOREIGN KEY (STERILIZER_ID)
                             REFERENCES STERILIZER ) ;


ALTER TABLE STERILIZATIONRUN
       ADD  ( CONSTRAINT STERILIZATIONRUN_EMPLOYEE_ID
              FOREIGN KEY (EMPLOYEE_ID)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE STERILIZATIONRUN
       ADD  ( CONSTRAINT STERILIZATIONRUN_BUSINESSUNIT_
              FOREIGN KEY (BUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT ) ;


ALTER TABLE UNIQUEORPACKSTERILE
       ADD  ( CONSTRAINT UNIQUEORPACKSTERILE_STERILIZER
              FOREIGN KEY (STERILIZATIONRUN_ID)
                             REFERENCES STERILIZATIONRUN ) ;

ALTER TABLE UNIQUEORPACKSTERILE
       ADD  ( CONSTRAINT UNIQUEORPACKSTERILE_CART
              FOREIGN KEY (CART_ID)
                             REFERENCES CART ) ;



