--ABS-118111

ALTER TABLE WORKSTATIONEXTRAINFO ADD(TUBMANAGEMENT NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN WORKSTATIONEXTRAINFO.TUBMANAGEMENT IS 
    '0 = Not Active/Not Applicable
    1= Active ';
    
CREATE SEQUENCE SEQ_SORTINGRULE
START WITH 1
INCREMENT BY 1;

CREATE TABLE SORTINGRULE
(
    SORTINGRULE_ID  NUMBER NOT NULL,
    PLANT_ID NUMBER NOT NULL,
    PRIORITY NUMBER NOT NULL CONSTRAINT CHK_SORTINGRULE_PRIORITY CHECK (PRIORITY BETWEEN 0 AND 9999),
    DESCRIPTION  VARCHAR2(30) NOT NULL,
    SYSTEMUSER_ID NUMBER DEFAULT 0 NOT NULL,
    TIMESTAMP DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN SORTINGRULE.PRIORITY IS 
    'Between 0 and 9999 , 0 is used when moving.';

ALTER TABLE SORTINGRULE ADD CONSTRAINT XPKSORTINGRULE PRIMARY KEY (SORTINGRULE_ID);
CREATE UNIQUE INDEX XAK1_SORTINGRULE ON SORTINGRULE(PLANT_ID,PRIORITY);
CREATE INDEX XIF1SORTINGRULE ON SORTINGRULE(PLANT_ID);
ALTER TABLE SORTINGRULE ADD CONSTRAINT FK_SORTINGRULE_PLANT_ID FOREIGN KEY (PLANT_ID) REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;

CREATE TABLE SORTINGRULECOMPONENT
(
    SORTINGRULE_ID NUMBER NOT NULL,
    SORTFIELD NUMBER NOT NULL CONSTRAINT CHK_SORTINGRULECOMPONENT_SORTFIELD CHECK(SORTFIELD BETWEEN 1 AND 11),
    REFERENCE_ID NUMBER  NOT NULL,
    WEIGHTLIMIT  NUMBER(6,2),
    TOLERANCE NUMBER(5,2),
    SYSTEMUSER_ID  NUMBER DEFAULT 0 NOT NULL,
    TIMESTAMP DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN SORTINGRULECOMPONENT.SORTFIELD IS '1 - Washing Process
2 - Market Segment
3 - Debtor
4 - Del to Customer
5 - Department
6 - Consumption Point
7 - Finishing Method
8 - Product Fabric
9 - Product Color
10- Product Group
11- Weight Limit';
COMMENT ON COLUMN SORTINGRULECOMPONENT.WEIGHTLIMIT IS 'Filled for sortfield 11.';
COMMENT ON COLUMN SORTINGRULECOMPONENT.TOLERANCE IS 'Filled for sortfield 11 , percentage.';

ALTER TABLE SORTINGRULECOMPONENT ADD CONSTRAINT XPKSORTINGRULECOMPONENT PRIMARY KEY (SORTINGRULE_ID,SORTFIELD);
CREATE INDEX XIE1_SORTINGRULECOMPONENT ON SORTINGRULECOMPONENT(SORTFIELD,REFERENCE_ID);
CREATE INDEX XIF1SORTINGRULECOMPONENT  ON SORTINGRULECOMPONENT(SORTINGRULE_ID);
ALTER TABLE SORTINGRULECOMPONENT ADD CONSTRAINT FK_SORTINGRULECOMPONENT_SORTINGRULE_ID FOREIGN KEY(SORTINGRULE_ID) REFERENCES SORTINGRULE(SORTINGRULE_ID) ON DELETE CASCADE;

CREATE SEQUENCE SEQ_SORTTUB
START WITH 1
INCREMENT BY 1;

CREATE TABLE SORTTUB
(
    SORTTUB_ID NUMBER NOT NULL,
    WORKSTATION_ID NUMBER NOT NULL,
    TUBNUMBER NUMBER NOT NULL CONSTRAINT CHK_SORTTUB_TUBNUMBER CHECK (TUBNUMBER BETWEEN 1 AND 99),
    STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_SORTTUB_STATUS CHECK (STATUS IN (0,1,2)),
    SORTINGRULE_ID NUMBER,
    WEIGHT NUMBER(6,2) DEFAULT 0 NOT NULL,
    SYSTEMUSER_ID NUMBER DEFAULT 0 NOT NULL,
    TIMESTAMP DATE DEFAULT SYSDATE NOT NULL);
COMMENT ON COLUMN SORTTUB.STATUS IS '0 = New
                  1 = Sorting / in use
                  2 = In use for items without sort rule
                  Default 0.';
                    
ALTER TABLE SORTTUB ADD CONSTRAINT XPKSORTTUB  PRIMARY KEY(SORTTUB_ID);
CREATE UNIQUE INDEX XAK1_SORTTUB ON SORTTUB(WORKSTATION_ID,TUBNUMBER);
CREATE INDEX XIE1_SORTTUB ON SORTTUB(WORKSTATION_ID,TUBNUMBER,STATUS);
CREATE INDEX XIF1SORTTUB  ON SORTTUB(WORKSTATION_ID);
CREATE INDEX XIF2SORTTUB  ON SORTTUB(SORTINGRULE_ID);
ALTER TABLE SORTTUB ADD CONSTRAINT FK_SORTTUB_WORKSTATION_ID FOREIGN KEY(WORKSTATION_ID) REFERENCES WORKSTATION(WORKSTATION_ID) ON DELETE CASCADE;
ALTER TABLE SORTTUB ADD CONSTRAINT FK_SORTTUB_SORTINGRULE_ID FOREIGN KEY(SORTINGRULE_ID) REFERENCES SORTINGRULE(SORTINGRULE_ID) ON DELETE SET NULL;
ALTER TABLE SORTTUB ADD CONSTRAINT CHK_SORTTUB_STATUSSRTRULE CHECK ((STATUS =0) OR (STATUS = 1 AND SORTINGRULE_ID IS NOT NULL) OR (STATUS = 2 AND SORTINGRULE_ID IS NULL));

CREATE TABLE SORTTUBCONTENTS
(
    SORTTUB_ID NUMBER NOT NULL,
    UNIQUEITEM_ID NUMBER NOT NULL,
    SYSTEMUSER_ID NUMBER DEFAULT 0 NOT NULL,
    TIMESTAMP DATE DEFAULT SYSDATE NOT NULL); 
    
ALTER TABLE SORTTUBCONTENTS ADD CONSTRAINT XPKSORTTUBCONTENTS  PRIMARY KEY(SORTTUB_ID,UNIQUEITEM_ID);
CREATE INDEX XIF1SORTTUBCONTENTS  ON SORTTUBCONTENTS(SORTTUB_ID);
CREATE INDEX XIF2SORTTUBCONTENTS  ON SORTTUBCONTENTS(UNIQUEITEM_ID);
ALTER TABLE SORTTUBCONTENTS ADD CONSTRAINT FK_SORTTUBCONTENTS_SORTTUB_ID FOREIGN KEY(SORTTUB_ID) REFERENCES SORTTUB(SORTTUB_ID) ON DELETE CASCADE;
ALTER TABLE SORTTUBCONTENTS ADD CONSTRAINT FK_SORTTUBCONTENTS_UNIQUEITEM_ID FOREIGN KEY(UNIQUEITEM_ID) REFERENCES UNIQUEITEM(UNIQUEITEM_ID) ON DELETE CASCADE;

CREATE SEQUENCE SEQ_SORTTUBRELEASED
START WITH 1
INCREMENT BY 1;

CREATE TABLE SORTTUBRELEASED
(
    SORTTUBRELEASED_ID	NUMBER	NOT NULL,	
	WORKSTATION_ID	    NUMBER  NOT NULL,
	TUBNUMBER			NUMBER	NOT NULL CONSTRAINT SORTTUBRELEASED_TUBNUMBER CHECK(TUBNUMBER BETWEEN 1 AND 99),
	SORTINGRULE_ID	    NUMBER,
	WASHINGPROCESS_ID	NUMBER  NOT NULL,
	CLEANROOMBATCH_ID	NUMBER,
	RELEASEDATE		    DATE    NOT NULL,
	WEIGHT 				NUMBER(6,2) DEFAULT 0 NOT NULL,
	SYSTEMUSER_ID NUMBER DEFAULT 0 NOT NULL,
    TIMESTAMP DATE DEFAULT SYSDATE NOT NULL
	);
ALTER TABLE SORTTUBRELEASED ADD CONSTRAINT XPKSORTTUBRELEASED  PRIMARY KEY(SORTTUBRELEASED_ID);	
CREATE INDEX XIE1_SORTTUBRELEASED ON SORTTUBRELEASED(WORKSTATION_ID,TUBNUMBER);
CREATE INDEX XIE2_SORTTUBRELEASED ON SORTTUBRELEASED(RELEASEDATE);
CREATE INDEX XIF1SORTTUBRELEASED ON SORTTUBRELEASED(WORKSTATION_ID);
CREATE INDEX XIF2SORTTUBRELEASED ON SORTTUBRELEASED(SORTINGRULE_ID);
CREATE INDEX XIF3SORTTUBRELEASED ON SORTTUBRELEASED(WASHINGPROCESS_ID);
CREATE INDEX XIF4SORTTUBRELEASED ON SORTTUBRELEASED(CLEANROOMBATCH_ID);
ALTER TABLE SORTTUBRELEASED ADD CONSTRAINT FK_SORTTUBRELEASED_WORKSTATION_ID FOREIGN KEY (WORKSTATION_ID)REFERENCES WORKSTATION(WORKSTATION_ID) ON DELETE CASCADE;
ALTER TABLE SORTTUBRELEASED ADD CONSTRAINT FK_SORTTUBRELEASED_SORTINGRULE_ID FOREIGN KEY (SORTINGRULE_ID)REFERENCES SORTINGRULE(SORTINGRULE_ID) ON DELETE SET NULL;
ALTER TABLE SORTTUBRELEASED ADD CONSTRAINT FK_SORTTUBRELEASED_WASHINGPROCESS_ID FOREIGN KEY(WASHINGPROCESS_ID) REFERENCES WASHINGPROCESS(WASHINGPROCESS_ID) ON DELETE SET NULL;
ALTER TABLE SORTTUBRELEASED ADD CONSTRAINT FK_SORTTTUBRELEASED_CLEANROOMBATCH_ID FOREIGN KEY(CLEANROOMBATCH_ID)REFERENCES CLEANROOMBATCH(CLEANROOMBATCH_ID) ON DELETE SET NULL;	

CREATE TABLE SORTTUBRELEASEDCONTENTS
(
	SORTTUBRELEASED_ID NUMBER NOT NULL,
	UNIQUEITEM_ID NUMBER NOT NULL,
	SYSTEMUSER_ID NUMBER DEFAULT 0 NOT NULL,
    TIMESTAMP DATE DEFAULT SYSDATE NOT NULL
	);
ALTER TABLE SORTTUBRELEASEDCONTENTS ADD CONSTRAINT XPKSORTTUBRELEASEDCONTENTS PRIMARY KEY (SORTTUBRELEASED_ID,UNIQUEITEM_ID);
ALTER TABLE SORTTUBRELEASEDCONTENTS ADD CONSTRAINT FK_SORTTUBRELEASEDCONTENTS_SORTTUBRELEASED_ID FOREIGN KEY(SORTTUBRELEASED_ID) REFERENCES SORTTUBRELEASED(SORTTUBRELEASED_ID) ON DELETE CASCADE;
ALTER TABLE SORTTUBRELEASEDCONTENTS ADD CONSTRAINT FK_SORTTUBRELEASEDCONTENTS_UNIQUEITEM_ID FOREIGN KEY(UNIQUEITEM_ID) REFERENCES UNIQUEITEM(UNIQUEITEM_ID) ON DELETE CASCADE;

