--Order 20012751.60


----SORTINGDEFINITIONRS----

CREATE SEQUENCE SEQ_SORTINGDEFINITIONRS
START WITH 1
INCREMENT BY 1;

CREATE TABLE SORTINGDEFINITIONRS
(
	SORTINGDEFINITIONRS_ID NUMBER NOT NULL ,
	CUSTOMER_ID          NUMBER NULL ,
	PLANT_ID             NUMBER NULL ,
	PRIORITY             NUMBER NOT NULL ,
	SORTFIELD            NUMBER NOT NULL  CONSTRAINT  SortField_valid1_1 CHECK (SORTFIELD IN (1, 2, 3, 4, 5, 6, 7, 8)),
	STACKCHANGE          VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField69 CHECK (STACKCHANGE IN ('Y', 'N')),
	SORTDESCENDING       VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField70 CHECK (SORTDESCENDING IN ('Y', 'N')),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN SORTINGDEFINITIONRS.SORTFIELD IS 'Predefined hardcoded values:
1	Department Number
2	Client Number
3	Client Name
4	Room number
5	Product Code
6	product sort group
7	Delivery point code
8	Client delivery note number';


CREATE  INDEX XIF1SORTINGDEFINITIONRS ON SORTINGDEFINITIONRS
(CUSTOMER_ID   ASC);

CREATE  INDEX XIF2SORTINGDEFINITIONRS ON SORTINGDEFINITIONRS
(PLANT_ID   ASC);

CREATE UNIQUE INDEX XAK1SORTINGDEFINITIONRS ON SORTINGDEFINITIONRS
(CUSTOMER_ID   ASC,PLANT_ID   ASC,PRIORITY   ASC);

ALTER TABLE SORTINGDEFINITIONRS
ADD CONSTRAINT  XPKSORTINGDEFINITIONRS PRIMARY KEY (SORTINGDEFINITIONRS_ID);

ALTER TABLE SORTINGDEFINITIONRS
ADD CONSTRAINT SORTINGDEFRS_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE SORTINGDEFINITIONRS
ADD CONSTRAINT SORTINGDEFRS_PLANT FOREIGN KEY (PLANT_ID) REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;


----SORTINGIMPORTDEFINITION-----

COMMENT ON COLUMN SORTINGIMPORTDEFINITION.TRANSACTIONTYPE IS '1=Inscan
2=Outscan
3=Repair scan
4=Rewash scan
6=Checkpoint scan
7=Sort scan
9 = Soil receiving (PWB)
10 = Outscan/sort (PWB)
11 = Rewash (PWB)
12 = Presort (PWB)';

----CLIENTDELIVERYNOTE----

COMMENT ON COLUMN CLIENTDELIVERYNOTE.ORIGIN IS '1 = Manual created
2 = Soil receiving scan per Wash
3 = Soil receiving scan in Bulk
4 = Outscan/sort
5 = Conversion
6 = Soil receiving - return to owner
7 = Generate label
8 = Correction ticket
9 = Sorting system';

----BATCH----

COMMENT ON COLUMN BATCH.ORIGIN IS '1 = Manual created batch maintenance
2 = Manual created soil receiving
3 = Generated at soil receiving
4 = Manual created outscan/sort
5 = Conversion
6 = Generate label
7 = Generated at sort device scan
8 = Correction note
9 = Sorting System';
