--Order 20013125.60

---CLEANROOMBOXLOCATION table---

CREATE TABLE CLEANROOMBOXLOCATION
(
	WORKSTATION_ID       NUMBER NOT NULL ,
	LOCATION             NUMBER NOT NULL ,
	CONTAINER_ID         NUMBER NULL ,
	STATUS               NUMBER DEFAULT  0  NOT NULL ,
	PRODUCT_ID           NUMBER NULL ,
	PACKINGNOTE_ID       NUMBER NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN CLEANROOMBOXLOCATION.STATUS IS '0 = Available / Empty
1 = Occupied / Box loading
2 = Available / Box completed
';

CREATE UNIQUE INDEX XPKCLEANROOMBOXLOCATION ON CLEANROOMBOXLOCATION
(WORKSTATION_ID   ASC,LOCATION   ASC);

CREATE INDEX XIF1CLEANROOMBOXLOCATION ON CLEANROOMBOXLOCATION
(WORKSTATION_ID   ASC);

CREATE INDEX XIF2CLEANROOMBOXLOCATION ON CLEANROOMBOXLOCATION
(CONTAINER_ID   ASC);

CREATE INDEX XIF3CLEANROOMBOXLOCATION ON CLEANROOMBOXLOCATION
(PRODUCT_ID   ASC);

CREATE INDEX XIF4CLEANROOMBOXLOCATION ON CLEANROOMBOXLOCATION
(PACKINGNOTE_ID   ASC);

ALTER TABLE CLEANROOMBOXLOCATION
ADD CONSTRAINT  XPKCLEANROOMBOXLOCATION PRIMARY KEY (WORKSTATION_ID,LOCATION);

ALTER TABLE CLEANROOMBOXLOCATION
ADD CONSTRAINT CLEANROOMBOXLOCATION_WS FOREIGN KEY (WORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE CASCADE;

ALTER TABLE CLEANROOMBOXLOCATION
ADD CONSTRAINT CLEANROOMBOXLOCATION_CONTAINER FOREIGN KEY (CONTAINER_ID) REFERENCES CONTAINER (CONTAINER_ID) ON DELETE SET NULL;

ALTER TABLE CLEANROOMBOXLOCATION
ADD CONSTRAINT CLEANROOMBOXLOCATION_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID) ON DELETE SET NULL;

ALTER TABLE CLEANROOMBOXLOCATION
ADD CONSTRAINT CLEANROOMBOXLOCATION_PACKINGNO FOREIGN KEY (PACKINGNOTE_ID) REFERENCES PACKINGNOTE (PACKINGNOTE_ID) ON DELETE SET NULL;

---PLANT table---

ALTER TABLE PLANT
	ADD (LOGO BLOB NULL);

---PACKINGNOTEBOX table---

ALTER TABLE PACKINGNOTEBOX
	ADD (CONTAINER_ID NUMBER NULL);

CREATE INDEX XIF2PACKINGNOTEBOX ON PACKINGNOTEBOX
(CONTAINER_ID   ASC);

ALTER TABLE PACKINGNOTEBOX
ADD CONSTRAINT PACKINGNOTEBOX_CONTAINER FOREIGN KEY (CONTAINER_ID) REFERENCES CONTAINER (CONTAINER_ID) ON DELETE SET NULL;