-- order 20011843 (Extra requirements regarding sorting system control Jensen (a))

------------------------PRODUCTSORTGROUP TABLE---------------------------

CREATE SEQUENCE SEQ_PRODUCTSORTGROUP
START WITH 1
INCREMENT BY 1;

CREATE TABLE PRODUCTSORTGROUP
(
	PRODUCTSORTGROUP_ID  NUMBER NOT NULL ,
	CODE                 VARCHAR2(6) NOT NULL ,
	DESCRIPTION          VARCHAR2(40) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE PRODUCTSORTGROUP
ADD CONSTRAINT  XPKPRODUCTSORTGROUP PRIMARY KEY (PRODUCTSORTGROUP_ID);

CREATE UNIQUE INDEX XAK1PRODUCTSORTGROUP ON PRODUCTSORTGROUP
(CODE   ASC);


------------------------PRODUCTSORTGROUPPERPLANT TABLE---------------------------

CREATE TABLE PRODUCTSORTGROUPPERPLANT
(
	PRODUCTSORTGROUP_ID  NUMBER NOT NULL ,
	PLANT_ID             NUMBER NOT NULL ,
	SEQUENCENUMBER       NUMBER(3) NOT NULL  CONSTRAINT  SEQUENCENUMBER_valid2 CHECK (SEQUENCENUMBER BETWEEN 0 AND 999),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE PRODUCTSORTGROUPPERPLANT
ADD CONSTRAINT  XPKPRODUCTSORTGROUPPERPLANT PRIMARY KEY (PRODUCTSORTGROUP_ID,PLANT_ID);

CREATE  INDEX XIF1PRODUCTSORTGROUPPERPLANT ON PRODUCTSORTGROUPPERPLANT
(PRODUCTSORTGROUP_ID   ASC);

ALTER TABLE PRODUCTSORTGROUPPERPLANT
ADD CONSTRAINT PRODUCTSORTGRPLANT_PRODSORTGR  FOREIGN KEY (PRODUCTSORTGROUP_ID) REFERENCES PRODUCTSORTGROUP (PRODUCTSORTGROUP_ID) ON DELETE CASCADE;

CREATE UNIQUE INDEX XIF2PRODUCTSORTGROUPPERPLANT ON PRODUCTSORTGROUPPERPLANT
(PLANT_ID   ASC);

ALTER TABLE PRODUCTSORTGROUPPERPLANT
ADD CONSTRAINT PRODUCTSORTGROUPPLANT_PLANT  FOREIGN KEY (PLANT_ID) REFERENCES PLANT (PLANT_ID);

CREATE UNIQUE INDEX XAK1PRODUCTSORTGROUPPERPLANT ON PRODUCTSORTGROUPPERPLANT
(PLANT_ID   ASC,SEQUENCENUMBER   ASC);


------------------------PRODUCT TABLE---------------------------

ALTER TABLE PRODUCT
	ADD (PRODUCTSORTGROUP_ID  NUMBER NULL);

CREATE  INDEX XIF31PRODUCT ON PRODUCT
(PRODUCTSORTGROUP_ID   ASC); 


ALTER TABLE PRODUCT
ADD CONSTRAINT PRODUCT_PRODUCTSORTGROUP  FOREIGN KEY (PRODUCTSORTGROUP_ID) REFERENCES PRODUCTSORTGROUP (PRODUCTSORTGROUP_ID);


------------------------SORTINGDEFINITION TABLE---------------------------
BEGIN
EXECUTE IMMEDIATE ('ALTER TABLE SORTINGDEFINITION DROP CONSTRAINT SORTFIELD_valid');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

ALTER TABLE SORTINGDEFINITION
  ADD CONSTRAINT SORTFIELD_valid  CHECK (SORTFIELD IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14));

COMMENT ON COLUMN SORTINGDEFINITION.SORTFIELD IS '
1=Department Number
2=Wearer Number
3=Wearer Name
4=Customer Employee Number
5=Bank/Locker
6=Gender
7=Product Code
8=Size Code
9=Delivery Point
10=Garment Dispenser Code
11=Customer / Laundry owned
12=Ordernumber
13=Bank
14=ProductSortGroup';














