--Order 20015262.60

---SORTEXXDEVICECONFIG table---

CREATE SEQUENCE SEQ_SORTEXXDEVICECONFIG
START WITH 1
INCREMENT BY 1;

CREATE TABLE SORTEXXDEVICECONFIG
(
	SORTEXXDEVICECONFIG_ID NUMBER NOT NULL ,
	PLANT_ID             NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(40) NOT NULL ,
	IPNUMBER             VARCHAR2(40) DEFAULT  0  NOT NULL ,
	PORT                 NUMBER DEFAULT  5000  NOT NULL ,
	SENSORSATTACHED      VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField222 CHECK (SENSORSATTACHED IN ('Y', 'N')),
	SENSORPOLLINTERVAL   NUMBER DEFAULT  50  NOT NULL  CONSTRAINT  SensorPollIntervalid1_1 CHECK (SENSORPOLLINTERVAL BETWEEN 50 AND 200),
	SENSORDELAYTIME      NUMBER DEFAULT  5  NOT NULL  CONSTRAINT  SensorDelayTimeValid1_1 CHECK (SENSORDELAYTIME IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)),
	WARNINGLIGHTATTACHED VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField223 CHECK (WARNINGLIGHTATTACHED IN ('Y', 'N')),
	NOOFSIMULTANEOUSITEMS NUMBER DEFAULT  1  NOT NULL  CONSTRAINT  NoOfSimultaneousValid1_1 CHECK (NOOFSIMULTANEOUSITEMS IN (1, 2)),
	CONFIRMMODEDEFON     VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField224 CHECK (CONFIRMMODEDEFON IN ('Y', 'N')),
	USERMAYCHANGEMODE    VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField225 CHECK (USERMAYCHANGEMODE IN ('Y', 'N')),
	DEBUGMODE            VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField226 CHECK (DEBUGMODE IN ('Y', 'N')),
	GREENWORKSTATION_ID  NUMBER NULL ,
	REDWORKSTATION_ID    NUMBER NULL ,
	YELLOWWORKSTATION_ID NUMBER NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN SORTEXXDEVICECONFIG.SENSORPOLLINTERVAL IS 'From 50  to 200';

COMMENT ON COLUMN SORTEXXDEVICECONFIG.SENSORDELAYTIME IS '0 =0 ms
1 = 1 ms
2 = 2 ms
3 = 5 ms (default for Counting Frame)
4 = 10 ms
5 = 20 ms (default for IN-SorTexx and OUT-SorTexx)
6 = 50 ms
7 = 100 ms
8 = 200 ms
9 = 500 ms';

CREATE INDEX XIF1SORTEXDEVICECONFIG ON SORTEXXDEVICECONFIG
(PLANT_ID   ASC);

CREATE INDEX XIF2SORTEXDEVICECONFIG ON SORTEXXDEVICECONFIG
(GREENWORKSTATION_ID   ASC);

CREATE INDEX XIF3SORTEXDEVICECONFIG ON SORTEXXDEVICECONFIG
(REDWORKSTATION_ID   ASC);

CREATE INDEX XIF4SORTEXDEVICECONFIG ON SORTEXXDEVICECONFIG
(YELLOWWORKSTATION_ID   ASC);

ALTER TABLE SORTEXXDEVICECONFIG
ADD CONSTRAINT  XPKSORTEXDEVICECONFIG PRIMARY KEY (SORTEXXDEVICECONFIG_ID);

ALTER TABLE SORTEXXDEVICECONFIG
ADD CONSTRAINT SORTEXXDEVICECONFIG_PLANT FOREIGN KEY (PLANT_ID) REFERENCES PLANT (PLANT_ID);

ALTER TABLE SORTEXXDEVICECONFIG
ADD CONSTRAINT SORTEXXDEVICECONFIG_GREENWS FOREIGN KEY (GREENWORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE SET NULL;

ALTER TABLE SORTEXXDEVICECONFIG
ADD CONSTRAINT SORTEXXDEVICECONFIG_REDWS FOREIGN KEY (REDWORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE SET NULL;

ALTER TABLE SORTEXXDEVICECONFIG
ADD CONSTRAINT SORTEXXDEVICECONFIG_YELLOWWS FOREIGN KEY (YELLOWWORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE SET NULL;


---SORTEXXDEVCOMPARTMENT table---
CREATE SEQUENCE SEQ_SORTEXXDEVCOMPARTMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE SORTEXXDEVCOMPARTMENT
(
	SORTEXXDEVCOMPARTMENT_ID NUMBER NOT NULL ,
	SORTEXXDEVICECONFIG_ID NUMBER NOT NULL ,
	BOXNUMBER            NUMBER DEFAULT  1  NOT NULL ,
	RACKNUMBER           NUMBER DEFAULT  1  NOT NULL ,
	COMPARTMENTNUMBER    NUMBER DEFAULT  1  NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

CREATE UNIQUE INDEX XAK1SORTEXXDEVCOMPARTMENT ON SORTEXXDEVCOMPARTMENT
(SORTEXXDEVICECONFIG_ID   ASC,BOXNUMBER   ASC);

CREATE UNIQUE INDEX XAK2SORTEXXDEVCOMPARTMENT ON SORTEXXDEVCOMPARTMENT
(SORTEXXDEVICECONFIG_ID   ASC,RACKNUMBER   ASC,COMPARTMENTNUMBER   ASC);

CREATE INDEX XIF1SORTEXXDEVCOMPARTMENT ON SORTEXXDEVCOMPARTMENT
(SORTEXXDEVICECONFIG_ID   ASC);

ALTER TABLE SORTEXXDEVCOMPARTMENT
ADD CONSTRAINT  XPKSORTEXXDEVCOMPARTMENT PRIMARY KEY (SORTEXXDEVCOMPARTMENT_ID);

ALTER TABLE SORTEXXDEVCOMPARTMENT
ADD CONSTRAINT SORTEXXDEVCOMPARTMENT_SORTEXXD FOREIGN KEY (SORTEXXDEVICECONFIG_ID) REFERENCES SORTEXXDEVICECONFIG (SORTEXXDEVICECONFIG_ID) ON DELETE CASCADE;