--order 502422      
      /*
      ACTION is CREATE Table PLPCTREATMENTCHECKSCHEME
      */

CREATE TABLE PLPCTREATMENTCHECKSCHEME (
       TREATMENTCHECKSCHEMEDETAIL_ID NUMBER NOT NULL,
       PRODUCTLISTPERCUSTOMER_ID NUMBER NOT NULL,
       FACTOR               NUMBER(5,4) DEFAULT 0.0000 NOT NULL
                                   CONSTRAINT Factor_valid
                                          CHECK (FACTOR BETWEEN -1.0000 AND 1.0000),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1PLPCTREATMENTCHECKSCHEME ON PLPCTREATMENTCHECKSCHEME
(
       TREATMENTCHECKSCHEMEDETAIL_ID  ASC
);

CREATE INDEX XIF2PLPCTREATMENTCHECKSCHEME ON PLPCTREATMENTCHECKSCHEME
(
       PRODUCTLISTPERCUSTOMER_ID      ASC
);


ALTER TABLE PLPCTREATMENTCHECKSCHEME
       ADD  ( CONSTRAINT XPKPLPCTREATMENTCHECKSCHEME PRIMARY KEY (
              TREATMENTCHECKSCHEMEDETAIL_ID, 
              PRODUCTLISTPERCUSTOMER_ID) ) ;


ALTER TABLE PLPCTREATMENTCHECKSCHEME
       ADD  ( CONSTRAINT PLPCTREATMENTCHECKSCHEME_PRODU
              FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID)
                             REFERENCES PRODUCTLISTPERCUSTOMER
                             ON DELETE CASCADE ) ;


ALTER TABLE PLPCTREATMENTCHECKSCHEME
       ADD  ( CONSTRAINT PLPCTREATMENTCHECKSCHEME_TREAT
              FOREIGN KEY (TREATMENTCHECKSCHEMEDETAIL_ID)
                             REFERENCES TREATMENTCHECKSCHEMEDETAIL
                             ON DELETE CASCADE ) ;

      /*
      ACTION is CREATE Table ORLABELSPERWORKSTATION
      */

CREATE TABLE ORLABELSPERWORKSTATION (
       WORKSTATION_ID       NUMBER NOT NULL,
       PRODUCT_ID           NUMBER NOT NULL,
       SIZEDEFINITION_ID    NUMBER NOT NULL,
       QUANTITY             NUMBER(5) NOT NULL
                                   CONSTRAINT QuantityORLABELS_valid
                                          CHECK (QUANTITY BETWEEN 1 AND 99999)
);

CREATE INDEX XIF1ORLABELSPERWORKSTATION ON ORLABELSPERWORKSTATION
(
       WORKSTATION_ID                 ASC
);

CREATE INDEX XIF2ORLABELSPERWORKSTATION ON ORLABELSPERWORKSTATION
(
       PRODUCT_ID                     ASC
);

CREATE INDEX XIF3ORLABELSPERWORKSTATION ON ORLABELSPERWORKSTATION
(
       SIZEDEFINITION_ID              ASC
);


ALTER TABLE ORLABELSPERWORKSTATION
       ADD  ( CONSTRAINT XPKORLABELSPERWORKSTATION PRIMARY KEY (
              WORKSTATION_ID, PRODUCT_ID, SIZEDEFINITION_ID) ) ;

      
      /*
      CHANGE REPORT for Table RECEIVEINSTOCKHISTORY
          - Adding column LOTNUMBER
          - Adding index XIE6RECEIVEINSTOCKHISTORY
          - Adding index XIE7RECEIVEINSTOCKHISTORY
      ACTION is ALTER or modify Table RECEIVEINSTOCKHISTORY
      */

ALTER TABLE RECEIVEINSTOCKHISTORY ADD (LOTNUMBER            VARCHAR2(20) NULL);

CREATE INDEX XIE6RECEIVEINSTOCKHISTORY ON RECEIVEINSTOCKHISTORY
(
       PRODUCT_LINK_ID                ,
       SIZEDEFINITION_LINK_ID         ,
       LOTNUMBER                      
);

CREATE INDEX XIE7RECEIVEINSTOCKHISTORY ON RECEIVEINSTOCKHISTORY
(
       LOTNUMBER                      
);

      
      /*
      CHANGE REPORT for Table SCAN
          - Adding a new relationship SCAN_TREATMENTTYPE_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK SCAN_TREATMENTTYPE_ID of table SCAN, which references table E/318).
          - Adding a new relationship SCAN_WASHINGPROCESS_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK SCAN_WASHINGPROCESS_ID of table SCAN, which references table WASHINGPROCESS).
          - Adding column WASHINGPROCESS_ID
          - Adding column TREATMENTTYPE_ID
          - Adding index XIE3SCAN
          - Adding index XIF17SCAN
          - Adding index XIF5SCAN
          - Adding index XIF9SCAN
      ACTION is ALTER or modify Table SCAN
      */

ALTER TABLE SCAN ADD (WASHINGPROCESS_ID    NUMBER NULL);


ALTER TABLE SCAN ADD (TREATMENTTYPE_ID     NUMBER NULL);


CREATE INDEX XIF18SCAN ON SCAN
(
       TREATMENTTYPE_ID               ASC
);

CREATE INDEX XIF19SCAN ON SCAN
(
       WASHINGPROCESS_ID               ASC
);



ALTER TABLE ORLABELSPERWORKSTATION
       ADD  ( CONSTRAINT ORLABELSPERWORKSTATION_SIZEDEF
              FOREIGN KEY (SIZEDEFINITION_ID)
                             REFERENCES SIZEDEFINITION
                             ON DELETE CASCADE ) ;


ALTER TABLE ORLABELSPERWORKSTATION
       ADD  ( CONSTRAINT ORLABELSPERWORKSTATION_PRODUCT
              FOREIGN KEY (PRODUCT_ID)
                             REFERENCES PRODUCT
                             ON DELETE CASCADE ) ;


ALTER TABLE ORLABELSPERWORKSTATION
       ADD  ( CONSTRAINT ORLABELSPERWORKSTATION_WORKSTA
              FOREIGN KEY (WORKSTATION_ID)
                             REFERENCES WORKSTATION
                             ON DELETE CASCADE ) ;


ALTER TABLE SCAN
       ADD  ( CONSTRAINT SCAN_TREATMENTTYPE_ID
              FOREIGN KEY (TREATMENTTYPE_ID)
                             REFERENCES TREATMENTTYPE ) ;


ALTER TABLE SCAN
       ADD  ( CONSTRAINT SCAN_WASHINGPROCESS_ID
              FOREIGN KEY (WASHINGPROCESS_ID)
                             REFERENCES WASHINGPROCESS ) ;


      /*
      CHANGE REPORT for Table PLANTPRODUCTSETTINGS
          - Adding a new relationship PLANTPRODUCTSETTINGS_TREATMENT
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK PLANTPRODUCTSETTINGS_TREATMENT of table PLANTPRODUCTSETTINGS, which references table E/377).
          - Adding column TREATMENTCHECKSCHEME_ID
          - Adding index XIF3PLANTPRODUCTSETTINGS
      ACTION is ALTER or modify Table PLANTPRODUCTSETTINGS
      */

ALTER TABLE PLANTPRODUCTSETTINGS ADD (TREATMENTCHECKSCHEME_ID NUMBER NULL);

CREATE INDEX XIF3PLANTPRODUCTSETTINGS ON PLANTPRODUCTSETTINGS
(
       TREATMENTCHECKSCHEME_ID        ASC
);


ALTER TABLE PLANTPRODUCTSETTINGS
       ADD  ( CONSTRAINT PLANTPRODUCTSETTINGS_TREATMENT
              FOREIGN KEY (TREATMENTCHECKSCHEME_ID)
                             REFERENCES TREATMENTCHECKSCHEME ) ;

----------------------------
insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 64, 'Assemble pack scan', getminsystemuser(), sysdate from dual;

insert into transactiontype_desc
  (transactiontype_id, language_id, description, systemuser_id, timestamp)
  select transactiontype_id,
         getminlanguage(),
         factorydescription,
         systemuser_id,
         timestamp
    from transactiontype
   where transactiontype.transactiontype_id = 64;

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 600, 'Generate OR Pack label', getminsystemuser(), sysdate
    from dual;

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 610, 'Assemble pack', getminsystemuser(), sysdate from dual;

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 612, 'Quick Load', getminsystemuser(), sysdate from dual;

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 615, 'Assemble + load pack', getminsystemuser(), sysdate
    from dual;

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 620, 'Load pack', getminsystemuser(), sysdate from dual;

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 630, 'Sterilization - Approved', getminsystemuser(), sysdate
    from dual;

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 631, 'Sterilization - Rejected', getminsystemuser(), sysdate
    from dual;

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 632,
         'Sterilization - Rejected + cancel',
         getminsystemuser(),
         sysdate
    from dual;

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 633,
         'Sterilization - Rejected + unload',
         getminsystemuser(),
         sysdate
    from dual;

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 640, 'Outscan on delivery note', getminsystemuser(), sysdate
    from dual;

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
values
  (645, 'Delivery note cancelled', getminsystemuser(), sysdate);

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
values
  (650, 'Used scan', getminsystemuser(), sysdate);

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
values
  (660, 'Cancel pack', getminsystemuser(), sysdate);

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
values
  (670, 'Recall pack', getminsystemuser(), sysdate);

insert into transactiontype_desc
  (transactiontype_id, language_id, description, systemuser_id, timestamp)
  select transactiontype_id,
         getminlanguage(),
         factorydescription,
         systemuser_id,
         timestamp
    from transactiontype
   where transactiontype.transactiontype_id >= 600
     and transactiontype.transactiontype_id <= 670;


--
insert into stay
  (stay_id, staytype, staynr, factorydescription, systemuser_id, timestamp)
values
  (seq_stay.nextval, 1, 38, 'Treatment', getminsystemuser(), sysdate);

insert into stay_desc
  (stay_id, language_id, description, systemuser_id, timestamp)
  select s.stay_id, l.language_id, s.factorydescription, getminsystemuser(), s.timestamp
    from stay s,languages l
   where s.staytype = 1
     and s.staynr = 38;