--Order 20015919
---STERILIZATIONRUN table---
ALTER TABLE STERILIZATIONRUN ADD (CHARGETYPE NUMBER DEFAULT 0 NOT NULL);
ALTER TABLE STERILIZATIONRUN ADD (COOLDOWNSTARTTIME DATE NULL);
ALTER TABLE STERILIZATIONRUN ADD (COOLDOWNENDTIME DATE NULL);

COMMENT ON COLUMN STERILIZATIONRUN.STATUS
  IS '0 = Ready for Sterilization
1 = Sterilized
2 = Rejected
3 = Recalled
4 = Approved
5 = Docked
6 = Completed';
COMMENT ON COLUMN STERILIZATIONRUN.CHARGETYPE
  IS '0 = ORS Charge
1 = Cleanroom Charge
';

ALTER TABLE STERILIZATIONRUN
  DROP CONSTRAINT STATUS_VALID2;
ALTER TABLE STERILIZATIONRUN
  ADD CONSTRAINT STATUS_VALID2
  CHECK (STATUS IN (0,1, 2, 3, 4, 5, 6));

ALTER TABLE STERILIZATIONRUN
  ADD CONSTRAINT CHARGETYPE_VALID1_1
  CHECK (CHARGETYPE IN (0, 1));

---STATUS table---

---STERILIZER table---
ALTER TABLE STERILIZER ADD (WORKSTATION_ID NUMBER NULL);
ALTER TABLE STERILIZER ADD (BUSINESSUNIT_ID NUMBER NULL);

CREATE INDEX XIF2STERILIZER ON STERILIZER (WORKSTATION_ID);
CREATE INDEX XIF3STERILIZER ON STERILIZER (BUSINESSUNIT_ID);

ALTER TABLE STERILIZER
  ADD CONSTRAINT STERILIZER_WORKSTATION FOREIGN KEY (WORKSTATION_ID)
  REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE SET NULL;
ALTER TABLE STERILIZER
  ADD CONSTRAINT STERILIZER_BU FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;


---CARTTYPE table---
ALTER TABLE CARTTYPE ADD CATEGORY NUMBER DEFAULT 0 NOT NULL;

COMMENT ON COLUMN CARTTYPE.CATEGORY
  IS '0 = OR Cart
1 = Cleanroom Cart
';

ALTER TABLE CARTTYPE
  ADD CONSTRAINT CATEGORY_VALID1_1
  CHECK (CATEGORY IN (0, 1));

---CART table---
ALTER TABLE CART ADD (STERILIZATIONRUN_ID NUMBER NULL);

CREATE INDEX XIF5CART ON CART (STERILIZATIONRUN_ID);

ALTER TABLE CART
  ADD CONSTRAINT CART_STERILIZATIONRUN FOREIGN KEY (STERILIZATIONRUN_ID)
  REFERENCES STERILIZATIONRUN (STERILIZATIONRUN_ID) ON DELETE SET NULL;

---USERGROUP table---
ALTER TABLE USERGROUP ADD (FULLACCESSSTERILIZATIONRUNS VARCHAR2(1) DEFAULT 'N' NOT NULL);
 
ALTER TABLE USERGROUP
  ADD CONSTRAINT YN_VALIDATIONFIELD122
  CHECK (FULLACCESSSTERILIZATIONRUNS IN ('Y','N'));

---ORLABELSPERWORKSTATION table---
ALTER TABLE ORLABELSPERWORKSTATION ADD (STERILIZATIONRUN_ID NUMBER DEFAULT 0 NOT NULL);

ALTER TABLE ORLABELSPERWORKSTATION
  DROP CONSTRAINT XPKORLABELSPERWORKSTATION CASCADE;

DROP INDEX XPKORLABELSPERWORKSTATION;

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

---REASONCODE table---
ALTER TABLE REASONCODE ADD (CANCELORPACKDELNOTE VARCHAR2(1) DEFAULT 'N' NOT NULL);

COMMENT ON COLUMN REASONCODE.CANCELORPACKDELNOTE
  IS 'Can only be set to Yes if ORPackReason = ‘Y’. There can only be 1 Reason code for which this is set to Y';

ALTER TABLE REASONCODE
  ADD CONSTRAINT YN_VALIDATIONFIELD123
  CHECK (CANCELORPACKDELNOTE IN ('Y', 'N'));

---insert process scheduler---
insert into process(process_id,description,active) values ('123','Auto Complete Cleanroom Charges','Y');
commit;