--Order 20014048.120

---CGPBATCH table---

CREATE SEQUENCE SEQ_CGPBATCH
START WITH 1
INCREMENT BY 1;

CREATE TABLE CGPBATCH
(
	CGPBATCH_ID          NUMBER NOT NULL ,
	WASHINGPROCESS_ID    NUMBER NULL ,
	SOILWORKSTATION_ID   NUMBER NOT NULL ,
	SOILSYSTEMUSER_ID    NUMBER DEFAULT  0  NOT NULL ,
	SOILTIMESTAMP        DATE NOT NULL ,
	WEIGHT               NUMBER DEFAULT  0  NOT NULL ,
	DISTRICT_ID          NUMBER NULL ,
	CLEANWORKSTATION_ID  NUMBER NULL ,
	CLEANSYSTEMUSER_ID   NUMBER NULL ,
	CLEANTIMESTAMP       DATE NULL ,
	CARTNUMBER           NUMBER NULL ,
	STATUS               NUMBER NOT NULL  CONSTRAINT  Status_valid5_1 CHECK (STATUS IN (0, 1, 2, 3)),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN CGPBATCH.SOILTIMESTAMP IS 'Timestamp on which the batch was created at soil scan.';

COMMENT ON COLUMN CGPBATCH.DISTRICT_ID IS 'Copied from the Workstation, the District is used to define the team. Can be empty for Hi-care.';

COMMENT ON COLUMN CGPBATCH.CARTNUMBER IS 'Hi-Care cart number';

COMMENT ON COLUMN CGPBATCH.STATUS IS '0 = Soil
1 = Dropped
2 = Sorted
3 = Packed';

ALTER TABLE CGPBATCH
ADD CONSTRAINT  XPKCGPBATCH PRIMARY KEY (CGPBATCH_ID);

CREATE INDEX XIF1CGPBATCH ON CGPBATCH
(WASHINGPROCESS_ID   ASC);

CREATE INDEX XIF2CGPBATCH ON CGPBATCH
(SOILWORKSTATION_ID   ASC);

CREATE INDEX XIF3CGPBATCH ON CGPBATCH
(DISTRICT_ID   ASC);

CREATE INDEX XIF4CGPBATCH ON CGPBATCH
(CLEANWORKSTATION_ID   ASC);

ALTER TABLE CGPBATCH
ADD CONSTRAINT CGPBATCH_WASHINGPROCESS FOREIGN KEY (WASHINGPROCESS_ID) REFERENCES WASHINGPROCESS (WASHINGPROCESS_ID);

ALTER TABLE CGPBATCH
ADD CONSTRAINT CGPBATCH_SOILWORKSTATION FOREIGN KEY (SOILWORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID);

ALTER TABLE CGPBATCH
ADD CONSTRAINT CGPBATCH_DISTRICT FOREIGN KEY (DISTRICT_ID) REFERENCES DISTRICT (DISTRICT_ID) ON DELETE CASCADE;

ALTER TABLE CGPBATCH
ADD CONSTRAINT CGPBATCH_CLEANWORKSTATION FOREIGN KEY (CLEANWORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID);

---CGPBATCHCONTENTS table---

CREATE TABLE CGPBATCHCONTENTS
(
	CGPBATCH_ID          NUMBER NOT NULL ,
	ROUTE_ID             NUMBER NOT NULL ,
	CUSTOMER_LINK_ID     NUMBER NOT NULL ,
	DELIVERYDATE         DATE NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN CGPBATCHCONTENTS.ROUTE_ID IS 'The Route_ID is used to determine the RouteNumber and the District (used as teamnumber)';

CREATE INDEX XIF1CGPBATCHCONTENTS ON CGPBATCHCONTENTS
(CGPBATCH_ID   ASC);

CREATE INDEX XIF2CGPBATCHCONTENTS ON CGPBATCHCONTENTS
(ROUTE_ID   ASC);

CREATE INDEX XIF3CGPBATCHCONTENTS ON CGPBATCHCONTENTS
(CUSTOMER_LINK_ID   ASC);

CREATE INDEX XIE1CGPBATCHCONTENTS ON CGPBATCHCONTENTS
(ROUTE_ID   ASC,DELIVERYDATE   ASC);

ALTER TABLE CGPBATCHCONTENTS
ADD CONSTRAINT  XPKCGPBATCHCONTENTS PRIMARY KEY (CGPBATCH_ID,CUSTOMER_LINK_ID);

ALTER TABLE CGPBATCHCONTENTS
ADD CONSTRAINT CGPCONTENTS_CGPBATCH FOREIGN KEY (CGPBATCH_ID) REFERENCES CGPBATCH (CGPBATCH_ID) ON DELETE CASCADE;

ALTER TABLE CGPBATCHCONTENTS
ADD CONSTRAINT CGPCONTENTS_ROUTE FOREIGN KEY (ROUTE_ID) REFERENCES ROUTE (ROUTE_ID) ON DELETE CASCADE;

ALTER TABLE CGPBATCHCONTENTS
ADD CONSTRAINT CGPCONTENTS_CUSTOMERLINK FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);


---WORKSTATION table---

ALTER TABLE WORKSTATION
	ADD (CGPTABLENUMBER NUMBER  DEFAULT 0 NOT NULL);

ALTER TABLE WORKSTATION
	ADD (CGPDISTRICT_ID NUMBER NULL);

ALTER TABLE WORKSTATION
	ADD (CGPHICARE NUMBER  DEFAULT 0 NOT NULL CONSTRAINT CGPHICARE_Valid1_1 CHECK (CGPHICARE IN (0, 1)));

ALTER TABLE WORKSTATION
	ADD (CGPBOXVOLUME NUMBER(15,6)  DEFAULT 0 NOT NULL);

ALTER TABLE WORKSTATION
	ADD (CGPHICARENROFBOXES NUMBER  DEFAULT 0 NOT NULL);

COMMENT ON COLUMN WORKSTATION.CGPDISTRICT_ID IS 'The district is used to define the team.';

COMMENT ON COLUMN WORKSTATION.CGPHICARE IS '0 = No Hi-Care allowed 
1 = Hi-Care allowed';

CREATE INDEX XIF8WORKSTATION ON WORKSTATION
(CGPDISTRICT_ID   ASC);

COMMENT ON COLUMN WORKSTATION.CGPTABLENUMBER IS 'This number is needed for addressing the WSP system.';

ALTER TABLE WORKSTATION
ADD CONSTRAINT WORKSTATION_CGPDISTRICT FOREIGN KEY (CGPDISTRICT_ID) REFERENCES DISTRICT (DISTRICT_ID) ON DELETE SET NULL;


--CUSTOMER table---

ALTER TABLE CUSTOMER
	ADD (CGPSORTDEFINITION NUMBER  DEFAULT 0 NOT NULL CONSTRAINT CGPSortDef_valid1_1 CHECK (CGPSORTDEFINITION IN (0, 1, 2, 3, 4, 5, 6, 7)));

ALTER TABLE CUSTOMER
	ADD (CGPSEAL NUMBER  DEFAULT 0 NOT NULL CONSTRAINT CGPSEAL_Valid1_1 CHECK (CGPSEAL IN (0, 1)));

ALTER TABLE CUSTOMER
	ADD (CGPCONTAINER NUMBER  DEFAULT 0 NOT NULL CONSTRAINT CGPContainer_valid1 CHECK (CGPCONTAINER IN (0, 1)));

COMMENT ON COLUMN CUSTOMER.CGPSORTDEFINITION IS '0 = Not applicable / Customer
1 = Department
2 = Cupboard
3 = Cupboard/Locker
4 = Wearer Number
5 = Wearer Name
6 = Product
7 = Product / Size';

COMMENT ON COLUMN CUSTOMER.CGPSEAL IS '0 = Not applicable / No seal
1 = Seal Customer';

COMMENT ON COLUMN CUSTOMER.CGPCONTAINER IS '0 = Not applicable / No container
1 = Container customer';


---ROUTECONTROL table---

ALTER TABLE ROUTECONTROL
	ADD (COLLECTWORKSTATION_ID NUMBER NULL);

CREATE INDEX XIF5ROUTECONTROL ON ROUTECONTROL
(COLLECTWORKSTATION_ID   ASC);

ALTER TABLE ROUTECONTROL
ADD CONSTRAINT ROUTECONTROL_WORKSTATION FOREIGN KEY (COLLECTWORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE SET NULL;


---PACKINGNOTE table---

ALTER TABLE PACKINGNOTE
	ADD (STORE VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField209 CHECK (STORE IN ('Y', 'N')));

ALTER TABLE PACKINGNOTE
	ADD (CGPWORKSTATION_ID NUMBER NULL);

CREATE INDEX XIF17PACKINGNOTE ON PACKINGNOTE
(CGPWORKSTATION_ID   ASC);

ALTER TABLE PACKINGNOTE
ADD CONSTRAINT PACKINGNOTE_CGPWORKSTATION FOREIGN KEY (CGPWORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE SET NULL;

---CLEANCOUNTDELIVERY table---

ALTER TABLE CLEANCOUNTDELIVERY
	ADD (WORKSTATION_ID NUMBER NULL);

CREATE INDEX XIF6CLEANCOUNTDELIVERY ON CLEANCOUNTDELIVERY
(WORKSTATION_ID   ASC);

ALTER TABLE CLEANCOUNTDELIVERY
ADD CONSTRAINT CLEANCOUNTDELIVERY_WORKSTATION FOREIGN KEY (WORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE SET NULL;

DROP INDEX XAK1CLEANCOUNTDELIVERY;

CREATE UNIQUE INDEX XAK1CLEANCOUNTDELIVERY ON CLEANCOUNTDELIVERY
(DISTRIBUTIONPOINT_ID   ASC,PRODUCT_ID   ASC,SIZEDEFINITION_ID   ASC,FINISHINGMETHOD_ID   ASC,PACKINGNOTE_ID   ASC,DELIVERYDATE   ASC,WORKSTATION_ID   ASC);

---CGPSORTBOX table---

CREATE SEQUENCE SEQ_CGPSORTBOX
START WITH 1
INCREMENT BY 1;

CREATE TABLE CGPSORTBOX
(
	CGPSORTBOX_ID        NUMBER NOT NULL ,
	WORKSTATION_ID       NUMBER NOT NULL ,
	CUSTOMER_ID          NUMBER NOT NULL ,
	CUSTOMERNUMBER       NUMBER NOT NULL ,
	SORTSTRING           VARCHAR2(24) NOT NULL ,
	BOXNUMBER            INTEGER NOT NULL  CONSTRAINT  BoxNumber_Valid1_1 CHECK (BOXNUMBER >= 1),
	STATUS               NUMBER DEFAULT  0 NOT NULL  CONSTRAINT  Status_valid3_4 CHECK (STATUS IN (0, 1)),
	VOLUME               NUMBER DEFAULT  0  NOT NULL ,
	ROUTE_ID             NUMBER NOT NULL ,
	ROUTENUMBER          NUMBER DEFAULT  0  NOT NULL ,
	STOPNUMBER           NUMBER DEFAULT  0  NOT NULL ,
	DELIVERYDATE         DATE NOT NULL ,
	CGPBATCH_ID          NUMBER NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN CGPSORTBOX.STATUS IS '0 = Sorting
1 = Packing';

ALTER TABLE CGPSORTBOX
ADD CONSTRAINT  XPKCGPSORTBOX PRIMARY KEY (CGPSORTBOX_ID);

CREATE INDEX XIF1CGPSORTBOX ON CGPSORTBOX
(WORKSTATION_ID   ASC);

CREATE INDEX XIF2CGPSORTBOX ON CGPSORTBOX
(CUSTOMER_ID   ASC);

CREATE INDEX XIF3CGPSORTBOX ON CGPSORTBOX
(ROUTE_ID   ASC);

CREATE INDEX XIF4CGPSORTBOX ON CGPSORTBOX
(CGPBATCH_ID   ASC);

CREATE INDEX XIE1CGPSORTBOX ON CGPSORTBOX
(WORKSTATION_ID   ASC,CUSTOMERNUMBER   ASC,SORTSTRING   ASC);

CREATE INDEX XIE2CGPSORTBOX ON CGPSORTBOX
(WORKSTATION_ID   ASC,ROUTENUMBER   ASC,STOPNUMBER   ASC,CUSTOMERNUMBER   ASC,BOXNUMBER   ASC);

CREATE UNIQUE INDEX XAK1CGPSORTBOX ON CGPSORTBOX
(WORKSTATION_ID   ASC,BOXNUMBER   ASC);

ALTER TABLE CGPSORTBOX
ADD CONSTRAINT CGPSORTBOX_WORKSTATION FOREIGN KEY (WORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE CASCADE;

ALTER TABLE CGPSORTBOX
ADD CONSTRAINT CGPSORTBOX_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID);

ALTER TABLE CGPSORTBOX
ADD CONSTRAINT CGPSORTBOX_ROUTE FOREIGN KEY (ROUTE_ID) REFERENCES ROUTE (ROUTE_ID);

ALTER TABLE CGPSORTBOX
ADD CONSTRAINT CGPSORTBOX_CGPBATCH FOREIGN KEY (CGPBATCH_ID) REFERENCES CGPBATCH (CGPBATCH_ID) ON DELETE CASCADE;


---CGPSORTBOXUNIQUEITEM table---

CREATE TABLE CGPSORTBOXUNIQUEITEM
(
	CGPSORTBOX_ID        NUMBER NOT NULL ,
	UNIQUEITEM_ID        NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

CREATE INDEX XIF1CGPSORTBOXUNIQUEITEM ON CGPSORTBOXUNIQUEITEM
(CGPSORTBOX_ID   ASC);

CREATE INDEX XIF2CGPSORTBOXUNIQUEITEM ON CGPSORTBOXUNIQUEITEM
(UNIQUEITEM_ID   ASC);

ALTER TABLE CGPSORTBOXUNIQUEITEM
ADD CONSTRAINT  XPKCGPSORTBOXUNIQUEITEM PRIMARY KEY (CGPSORTBOX_ID,UNIQUEITEM_ID);

ALTER TABLE CGPSORTBOXUNIQUEITEM
ADD CONSTRAINT CGPSORTBOXUI_CGPSORTBOX FOREIGN KEY (CGPSORTBOX_ID) REFERENCES CGPSORTBOX (CGPSORTBOX_ID) ON DELETE CASCADE;

ALTER TABLE CGPSORTBOXUNIQUEITEM
ADD CONSTRAINT CGPSORTBOXUI_UNIQUEITEM FOREIGN KEY (UNIQUEITEM_ID) REFERENCES UNIQUEITEM (UNIQUEITEM_ID) ON DELETE CASCADE;
