--Order 20015731.60

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

COMMENT ON COLUMN FINISHINGMETHOD.COLOR
  IS 'Color code written as hexdecimal RGB-value, i.e. FF0000 is red';


--Order 20015916.60
---DELIVERYVARIATIONS table---
ALTER TABLE DELIVERYVARIATIONS ADD (ADDTOEXISTINGPN VARCHAR2(1) DEFAULT 'N' NOT NULL);

COMMENT ON COLUMN DELIVERYVARIATIONS.ADDTOEXISTINGPN
  IS 'Option Y is only used for OR Packs.';

ALTER TABLE DELIVERYVARIATIONS
  ADD CONSTRAINT YN_VALIDATIONFIELD125
  CHECK (ADDTOEXISTINGPN IN ('Y', 'N'));

---BUSINESSUNIT table---
ALTER TABLE BUSINESSUNIT ADD (ORPLANTDEPARTMENT_ID NUMBER NULL);

CREATE INDEX XIF10BUSINESSUNIT ON BUSINESSUNIT (ORPLANTDEPARTMENT_ID);

ALTER TABLE BUSINESSUNIT
  ADD CONSTRAINT BUSINESSUNIT_PLANTDEPARTMENT FOREIGN KEY (ORPLANTDEPARTMENT_ID)
  REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID) ON DELETE SET NULL;

---ORDELIVERBUSINESSUNIT table---
ALTER TABLE ORDELIVERBUSINESSUNIT ADD (SERVICEDAYS NUMBER DEFAULT 1 NOT NULL);

ALTER TABLE ORDELIVERBUSINESSUNIT
  ADD CONSTRAINT ServiceDays_Valid1_2
  CHECK (SERVICEDAYS BETWEEN 1 AND 9);

insert into process(process_id,description,active) values ('122','Add OR Delivery Variations to Existing Packing Notes','Y');
commit;


--Order 20015918.60
---LABELLAYOUT table---
COMMENT ON COLUMN LABELLAYOUT.LABELTYPE
  IS '1 = Code label
2 = Name label
3 = Client label
4 = OR Assembly Label
5 = OR Delivery Label
6 = OR Production Label
7 = OR Cleanroom Label';

---BUSINESSUNIT table---
ALTER TABLE BUSINESSUNIT ADD (ORPRODUCTIONLABELLAYOUT_ID NUMBER NULL);

CREATE INDEX XIF11BUSINESSUNIT ON BUSINESSUNIT (ORPRODUCTIONLABELLAYOUT_ID);

ALTER TABLE BUSINESSUNIT
  ADD CONSTRAINT BUSINESSUNIT_ORPRODUCTIONLABEL FOREIGN KEY (ORPRODUCTIONLABELLAYOUT_ID)
  REFERENCES LABELLAYOUT (LABELLAYOUT_ID) ON DELETE SET NULL;

---PACKINGNOTELINE table---
ALTER TABLE PACKINGNOTELINE ADD (ASSEMBLEDQUANTITY NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN PACKINGNOTELINE.ASSEMBLEDQUANTITY
  IS 'Used in ORS for number of OR packs assembled.';

---UNIQUEORPACK table---
ALTER TABLE UNIQUEORPACK ADD (ASSEMBLYPACKINGNOTELINE_ID NUMBER NULL);

CREATE INDEX XIF12UNIQUEORPACK ON UNIQUEORPACK (ASSEMBLYPACKINGNOTELINE_ID);

ALTER TABLE UNIQUEORPACK
  ADD CONSTRAINT UNIQUEORPACK_ASSEMBLYPNL FOREIGN KEY (ASSEMBLYPACKINGNOTELINE_ID)
  REFERENCES PACKINGNOTELINE (PACKINGNOTELINE_ID) ON DELETE SET NULL;

---PRINTQUEUE table---
COMMENT ON COLUMN PRINTQUEUE.LABELTYPE
  IS '
1=Unique id   (fill UniqueItem_Link_ID)
2=Wearer    (fill WearerEmployment_Link_ID)
3=Wearer inventory (fill WearerEmpl.+Product+Size)
4=Used
5=Used
6=OR Assembly Label
7=OR Delivery Label
8=Stock Reception Label
9=OR Production Label
10=OR Cleanroom Label
';

---ORDELIVERBUSINESSUNIT table---
ALTER TABLE ORDELIVERBUSINESSUNIT ADD (ORPLANTDEPARTMENT_ID NUMBER NULL);

CREATE INDEX XIF3ORDELIVERBUSINESSUNIT ON ORDELIVERBUSINESSUNIT (ORPLANTDEPARTMENT_ID);

ALTER TABLE ORDELIVERBUSINESSUNIT
  ADD CONSTRAINT ORDELIVERBUSINESSUNIT_PLANTDEP FOREIGN KEY (ORPLANTDEPARTMENT_ID)
  REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID) ON DELETE SET NULL;