--order 502334

---BUSINESSUNIT

ALTER TABLE BUSINESSUNIT ADD ( ORSERVICINGBU NUMBER(1) DEFAULT 0 NOT NULL );

ALTER TABLE BUSINESSUNIT ADD ( ORCHARGENUMBERLENGTH NUMBER NULL );

ALTER TABLE BUSINESSUNIT ADD ( ORCHARGENUMBERPREFIX VARCHAR2(6) NULL );

COMMENT ON COLUMN BUSINESSUNIT.ORSERVICINGBU IS '0 = No OR servicing business unit (default)
1 = OR servicing business unit';

CREATE TABLE ORDELIVERBUSINESSUNIT
(
	ORBUSINESSUNIT_ID NUMBER NOT NULL,
	DELIVERTOBUSINESSUNIT_ID NUMBER NOT NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);


CREATE INDEX XIF1ORDELIVERBUSINESSUNIT ON ORDELIVERBUSINESSUNIT
(
	ORBUSINESSUNIT_ID ASC	
);

CREATE INDEX XIF2ORDELIVERBUSINESSUNIT ON ORDELIVERBUSINESSUNIT
(
	DELIVERTOBUSINESSUNIT_ID ASC	
);


ALTER TABLE ORDELIVERBUSINESSUNIT
       ADD  ( CONSTRAINT XPKORDELIVERBUSINESSUNIT PRIMARY KEY (
              ORBUSINESSUNIT_ID, DELIVERTOBUSINESSUNIT_ID) ) ;

ALTER TABLE ORDELIVERBUSINESSUNIT
       ADD  ( CONSTRAINT ORDELIVERBUSINESSUNIT_BUSI
              FOREIGN KEY (ORBUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT) ;

ALTER TABLE ORDELIVERBUSINESSUNIT
       ADD  ( CONSTRAINT ORDELIVERBUSINESSUNIT_BUSINESS
              FOREIGN KEY (DELIVERTOBUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT
				ON DELETE CASCADE) ;

---LABELLAYOUT

      /*
      CHANGE REPORT for Table LABELLAYOUT
          - Adding column STERILE
          - Adding column definition to STERILE
      ACTION is ALTER or modify Table LABELLAYOUT
      */

ALTER TABLE LABELLAYOUT ADD (STERILE  VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew171
                                          CHECK (STERILE IN ('Y', 'N')));

COMMENT ON COLUMN LABELLAYOUT.LABELTYPE IS '1 = Code label
2 = Name label
3 = Client label
4 = OR Assembly Label
5 = OR Delivery Label';

COMMENT ON COLUMN LABELLAYOUT.STERILE IS 'Option is only used in combination with labeltype 4 and 5';

---FLAG

      /*
      CHANGE REPORT for Table FLAG
          - Adding column PROHIBITDELIVERY
      ACTION is ALTER or modify Table FLAG
      */

ALTER TABLE FLAG ADD (PROHIBITDELIVERY     VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew172
                                          CHECK (PROHIBITDELIVERY IN ('Y', 'N')));

 update flag f set f.prohibitdelivery = 'Y'
 where f.flag_id =
       (select max(af.flag_id)
          from automaticflag af
         where af.flag_id = f.flag_id
           and af.automaticflag_id in (1, 2, 7, 12, 17, 18));

---REASONCODE
      
      /*
      CHANGE REPORT for Table REASONCODE
          - Adding column ORPACKREASON
      ACTION is ALTER or modify Table REASONCODE
      */

ALTER TABLE REASONCODE ADD (ORPACKREASON         VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew173
                                          CHECK (ORPACKREASON IN ('Y', 'N')));

---WASHINGPROCESS

      /*
      CHANGE REPORT for Table WASHINGPROCESS
          - Adding column WASHINGPROCESSTYPE
          - Adding column definition to WASHINGPROCESSTYPE
      ACTION is ALTER or modify Table WASHINGPROCESS
      */

ALTER TABLE WASHINGPROCESS ADD (WASHINGPROCESSTYPE   NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT WashingProcessType_Valid
                                          CHECK (WASHINGPROCESSTYPE IN (0, 1)));

COMMENT ON COLUMN WASHINGPROCESS.WASHINGPROCESSTYPE IS '0 = Standard
1 = Rewash';

---WORKSTATION

ALTER TABLE WORKSTATION ADD ( OUTSCANORPACKS NUMBER DEFAULT 0 NOT NULL
				CONSTRAINT OUTSCANORPACKS_Valid
                                          CHECK (OUTSCANORPACKS  IN (0, 1, 2, 3)) );

COMMENT ON COLUMN WORKSTATION.OUTSCANORPACKS IS '0 = Disabled, outscan OR packs not possible
1 = Enabled, allow to scan out only ordered packs
2 = Enabled, allow to scan out only ordered packs and more than ordered
3 = Enabled, allow to scan out ordered packs and more than ordered, and also not ordered packs.';

---STAY

      /*
      CHANGE REPORT for Table STATUS
          - Changing column definition of STAYTYPE
      ACTION is ALTER or modify Table STAY
      */

COMMENT ON COLUMN STAY.STAYTYPE IS 'Possible stay types are:
1= Garment stays
2= Container stays
3= OR-pack stays

Note that the staytypes match with the flagtypes.';

insert into stay
  (stay_id, staytype, staynr, factorydescription, systemuser_id, timestamp)
values
  (seq_stay.nextval, 3, 10, 'InScan', getminsystemuser(), sysdate);
insert into stay
  (stay_id, staytype, staynr, factorydescription, systemuser_id, timestamp)
values
  (seq_stay.nextval, 3, 20, 'OutScan/At Customer', getminsystemuser(), sysdate);
insert into stay
  (stay_id, staytype, staynr, factorydescription, systemuser_id, timestamp)
values
  (seq_stay.nextval, 3, 30, 'Assemble pack', getminsystemuser(), sysdate);
insert into stay
  (stay_id, staytype, staynr, factorydescription, systemuser_id, timestamp)
values
  (seq_stay.nextval, 3, 35, 'OR Load', getminsystemuser(), sysdate);
insert into stay
  (stay_id, staytype, staynr, factorydescription, systemuser_id, timestamp)
values
  (seq_stay.nextval, 3, 40, 'Confirm Sterilization', getminsystemuser(), sysdate);

insert into stay_desc
  (stay_id, language_id, description, systemuser_id, timestamp)
  select s.stay_id,
         l.language_id,
         s.factorydescription,
         s.systemuser_id,
         s.timestamp
    from stay s,languages l
    where s.staytype = 3 and s.staynr in (10,20,30,35,40);

---SYSTEMUSER

ALTER TABLE SYSTEMUSER ADD ( EMPLOYEE_ID NUMBER NULL );

CREATE INDEX XIF1SYSTEMUSER ON SYSTEMUSER
(
	EMPLOYEE_ID ASC	
);

ALTER TABLE SYSTEMUSER 
       ADD  ( CONSTRAINT SYSTEMUSER_EMPLOYEE
              FOREIGN KEY (EMPLOYEE_ID)
                             REFERENCES EMPLOYEE) ;

---AUTOMATICFLAG

insert into automaticflag
          (automaticflag_id, event, systemuser_id, timestamp, selectable)
        values
          (19, 'Rewash', getminsystemuser(), sysdate, 'Y');

---UNIQUEIDCONFIGURATION
      
      /*
      CHANGE REPORT for Table UNIQUEIDCONFIGURATION
          - Changing column definition of PACKAGETYPE
      ACTION is ALTER or modify Table UNIQUEIDCONFIGURATION
      */

COMMENT ON COLUMN UNIQUEIDCONFIGURATION.PACKAGETYPE IS '1 = Garment
2 = Storage Location
3 = Container
4 = Bundle (only available for module license)
5 = Residential/domestic
6 = OR Packs
7 = Carts';

---TREATMENTTYPE

      /*
      CHANGE REPORT for Table TREATMENTTYPE
          - Adding column COLOR
          - Adding column TREATMENTTYPETYPE
          - Adding column definition to TREATMENTTYPETYPE
          - Adding column SORTCODE
          - Adding column STERILIZATIONTIME
          - Adding column STERILIZATIONTEMPERATURE
          - Adding column COOLDOWNTIME
          - Adding column ALLOWRESTERILIZATION
      ACTION is ALTER or modify Table TREATMENTTYPE
      */

ALTER TABLE TREATMENTTYPE ADD (COLOR VARCHAR2(6) NULL);


ALTER TABLE TREATMENTTYPE ADD (TREATMENTTYPETYPE    NUMBER(2) DEFAULT 0 NOT NULL
                                   CONSTRAINT TreatmentType_Valid
                                          CHECK (TREATMENTTYPETYPE IN (0, 1)));


COMMENT ON COLUMN TREATMENTTYPE.TREATMENTTYPETYPE IS '0 = Not defined
1 = Sterilization Process';


ALTER TABLE TREATMENTTYPE ADD (SORTCODE             VARCHAR2(3) NULL);


ALTER TABLE TREATMENTTYPE ADD (STERILIZATIONTIME    NUMBER(4) DEFAULT 0 NOT NULL);


ALTER TABLE TREATMENTTYPE ADD (STERILIZATIONTEMPERATURE NUMBER(6,2) DEFAULT 0.00 NOT NULL);


ALTER TABLE TREATMENTTYPE ADD (COOLDOWNTIME         NUMBER(4) DEFAULT 0 NOT NULL);


ALTER TABLE TREATMENTTYPE ADD (ALLOWRESTERILIZATION VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew174
                                          CHECK (ALLOWRESTERILIZATION IN ('Y', 'N')));
