--ABS-163495

--REJECTREASON TABLE
CREATE SEQUENCE SEQ_REJECTREASON
START WITH 1
INCREMENT BY 1;

CREATE TABLE REJECTREASON(
REJECTREASON_ID       NUMBER NOT NULL,
REJECTREASONTYPE      NUMBER NOT NULL CONSTRAINT CHK_REJECTREASON_REJECTREASONTYPE CHECK (REJECTREASONTYPE IN (0, 1)),
CODE                  VARCHAR2(6) NOT NULL CONSTRAINT CHK_REJECTREASON_CODE CHECK (CODE = UPPER(CODE)),
SEQUENCENUMBER        NUMBER NOT NULL,
ACTION                NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_REJECTREASON_ACTION CHECK (ACTION IN (0, 1, 2)),
CREATIONSYSTEMUSER_ID NUMBER NOT NULL,
CREATIONTIMESTAMP     DATE DEFAULT SYSDATE NOT NULL,
SYSTEMUSER_ID         NUMBER NOT NULL,
TIMESTAMP             DATE DEFAULT SYSDATE NOT NULL);

ALTER TABLE REJECTREASON ADD CONSTRAINT XPKREJECTREASON PRIMARY KEY (REJECTREASON_ID);

CREATE UNIQUE INDEX XAK1REJECTREASON ON REJECTREASON (REJECTREASONTYPE ASC, CODE ASC);
CREATE UNIQUE INDEX XAK2REJECTREASON ON REJECTREASON (REJECTREASONTYPE ASC, SEQUENCENUMBER ASC);

COMMENT ON COLUMN REJECTREASON.REJECTREASONTYPE IS '0 = Service exception reason, 1 = Production reject';
COMMENT ON COLUMN REJECTREASON.ACTION IS '0 = No delivery, 1 = Deliver charged, 2 = Deliver free of charge';


--REJECTREASON_DESC TABLE
CREATE TABLE REJECTREASON_DESC(
REJECTREASON_ID NUMBER NOT NULL,
LANGUAGE_ID     NUMBER NOT NULL, 
DESCRIPTION     VARCHAR2(30) NOT NULL,
SYSTEMUSER_ID   NUMBER NOT NULL,
TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL);

ALTER TABLE REJECTREASON_DESC
ADD CONSTRAINT XPKREJECTREASON_DESC PRIMARY KEY (REJECTREASON_ID, LANGUAGE_ID);

CREATE INDEX XIF1REJECTREASON_DESC ON REJECTREASON_DESC (REJECTREASON_ID ASC);
CREATE INDEX XIF2REJECTREASON_DESC ON REJECTREASON_DESC (LANGUAGE_ID ASC);

ALTER TABLE REJECTREASON_DESC
ADD CONSTRAINT FK_RJCTREASONDESC_REJECTREASONID FOREIGN KEY (REJECTREASON_ID) REFERENCES REJECTREASON (REJECTREASON_ID) ON DELETE CASCADE;

ALTER TABLE REJECTREASON_DESC
ADD CONSTRAINT FK_RJCTREASONDESC_LANGUAGEID FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID);

--WORKSTATION TABLE
ALTER TABLE WORKSTATION ADD (SOILCOUNTMETHOD NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_WORKSTATION_SOILCOUNTMETHOD CHECK (SOILCOUNTMETHOD IN (0, 1, 2, 3, 4, 5, 6, 7)));
COMMENT ON COLUMN WORKSTATION.SOILCOUNTMETHOD IS '0 = Not applicable, 1 = All (soil + rejects + abused), 2 = Soil count (conformities) only, 3 = Rejects (service exceptions only), 4 = Soil count + rejects, 5 = Abused only, 6 = Soil count + abused, 7 = Rejects + abused';


--SOILCOUNTLINE TABLE
ALTER TABLE SOILCOUNTLINE ADD (PROCESSEDREJECTED NUMBER DEFAULT 0 NOT NULL, 
                               PROCESSEDABUSE    NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN SOILCOUNTLINE.PROCESSEDREJECTED IS 'Quantity rejected processed';
COMMENT ON COLUMN SOILCOUNTLINE.PROCESSEDABUSE IS 'Quantity abuse processed';

--SOILCOUNTLINEWS TABLE 
CREATE SEQUENCE SEQ_SOILCOUNTLINEWS
START WITH 1
INCREMENT BY 1; 

CREATE TABLE SOILCOUNTLINEWS(
SOILCOUNTLINEWS_ID NUMBER NOT NULL,
SOILCOUNTLINE_ID   NUMBER NOT NULL,
WORKSTATION_ID     NUMBER NOT NULL,
COUNTCONVERTED     NUMBER NULL,
COUNTKEYPAD        NUMBER NULL,
COUNTLIGHTFRAME    NUMBER NULL,
COUNTABUSED        NUMBER DEFAULT 0 NOT NULL,
CREATIONUSER_ID    NUMBER NOT NULL,
CREATIONTIMESTAMP  DATE DEFAULT SYSDATE NOT NULL,
SYSTEMUSER_ID      NUMBER NOT NULL,
TIMESTAMP          DATE NOT NULL);

ALTER TABLE SOILCOUNTLINEWS
ADD CONSTRAINT XPKSOILCOUNTLINEWS PRIMARY KEY (SOILCOUNTLINEWS_ID);

CREATE UNIQUE INDEX XAK1SOILCOUNTLINEWS ON SOILCOUNTLINEWS (SOILCOUNTLINE_ID ASC, WORKSTATION_ID ASC);

CREATE INDEX XIF1SOILCOUNTLINEWS ON SOILCOUNTLINEWS (SOILCOUNTLINE_ID ASC);
CREATE INDEX XIF2SOILCOUNTLINEWS ON SOILCOUNTLINEWS (WORKSTATION_ID ASC);

ALTER TABLE SOILCOUNTLINEWS
ADD CONSTRAINT FK_SOILCNTWS_SOILCOUNTLINE FOREIGN KEY (SOILCOUNTLINE_ID) REFERENCES SOILCOUNTLINE (SOILCOUNTLINE_ID) ON DELETE CASCADE;

ALTER TABLE SOILCOUNTLINEWS
ADD CONSTRAINT FK_SOILCNTWS_WORKSTATION FOREIGN KEY (WORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE CASCADE;


--SOILCOUNTLINENONCONF TABLE
CREATE SEQUENCE SEQ_SOILCOUNTLINENONCONF
START WITH 1
INCREMENT BY 1;

CREATE TABLE SOILCOUNTLINENONCONF (
SOILCOUNTLINENONCONF_ID    NUMBER NOT NULL,
SOILCOUNTLINEWS_ID         NUMBER NOT NULL,
REJECTREASON_ID            NUMBER NOT NULL,
QUANTITY                   NUMBER NOT NULL,
CREATIONUSER_ID     NUMBER NOT NULL,
CREATIONTIMESTAMP   DATE DEFAULT SYSDATE NOT NULL,
SYSTEMUSER_ID       NUMBER NOT NULL,
TIMESTAMP           DATE NOT NULL);

ALTER TABLE SOILCOUNTLINENONCONF
ADD CONSTRAINT XPKSOILCOUNTLINENONCONF PRIMARY KEY (SOILCOUNTLINENONCONF_ID);

CREATE UNIQUE INDEX XAK1SOILCOUNTLINENONCONF ON SOILCOUNTLINENONCONF (SOILCOUNTLINEWS_ID ASC, REJECTREASON_ID ASC);

CREATE INDEX XIF1SOILCOUNTLINENONCONF ON SOILCOUNTLINENONCONF (SOILCOUNTLINEWS_ID ASC);
CREATE INDEX XIF2SOILCOUNTLINENONCONF ON SOILCOUNTLINENONCONF (REJECTREASON_ID ASC);

ALTER TABLE SOILCOUNTLINENONCONF
ADD CONSTRAINT FK_SOILCTLINENCONF_SOILCOUNTLINEWS FOREIGN KEY (SOILCOUNTLINEWS_ID) REFERENCES SOILCOUNTLINEWS (SOILCOUNTLINEWS_ID) ON DELETE CASCADE;

ALTER TABLE SOILCOUNTLINENONCONF
ADD CONSTRAINT FK_SOILCTLINENCONF_REJECTREASON FOREIGN KEY (REJECTREASON_ID) REFERENCES REJECTREASON (REJECTREASON_ID);

--PACKINGNOTE TABLE
ALTER TABLE PACKINGNOTE ADD (FOLLOWUPFORREJECTNOTE_ID NUMBER DEFAULT NULL);

COMMENT ON COLUMN PACKINGNOTE.FOLLOWUPFORREJECTNOTE_ID IS 'Will only be filled in case of a follow-up note for rejected items (origin = 21) with the delivery note ID of the original note to which this current note is a follow-up. Otherwise, it is filled with null.';

ALTER TABLE PACKINGNOTE
DROP CONSTRAINT CHK_PACKINGNOTE_ORIGIN;

ALTER TABLE PACKINGNOTE
ADD CONSTRAINT CHK_PACKINGNOTE_ORIGIN CHECK (ORIGIN IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21));

COMMENT ON COLUMN PACKINGNOTE.ORIGIN IS '0 = Generated based on Order 1 = Manually added 2 = Generated based on Outscan 3 = Quantity Correction Ticket 4 = Stock Delivery Note 5 = Special Order 6 = Bulk Note 7 = Bin Note 8 = Transfer Weights 9 = Import from DW 10 = Mounting Order 11 = Sales Order 12 = Credit Note 13 = Residential Delivery Note 14 = MultiReadShipment 15 = Charge To Product Process 16 = Customer Packing Note 17 = Curtain outscan 18 = Cabinet Service 19 = BM6 Packing Note 20 = Truck Replenishment 21 = Rejects'; 

CREATE INDEX XIE18PACKINGNOTE ON PACKINGNOTE (NVL(FOLLOWUPFORREJECTNOTE_ID,0));

--PACKINGNOTELINENONCONF TABLE
CREATE TABLE PACKINGNOTELINENONCONF(
PACKINGNOTELINE_ID NUMBER NOT NULL,
REJECTREASON_ID    NUMBER NOT NULL,
QUANTITY           NUMBER NOT NULL,
SYSTEMUSER_ID      NUMBER NOT NULL,
TIMESTAMP          DATE NOT NULL);

ALTER TABLE PACKINGNOTELINENONCONF
ADD CONSTRAINT XPKPACKINGNOTELINENONCONF PRIMARY KEY (PACKINGNOTELINE_ID, REJECTREASON_ID);

CREATE INDEX XIF1PACKINGNOTELINENONCONF ON PACKINGNOTELINENONCONF (PACKINGNOTELINE_ID ASC);
CREATE INDEX XIF2PACKINGNOTELINENONCONF ON PACKINGNOTELINENONCONF (REJECTREASON_ID ASC);

ALTER TABLE PACKINGNOTELINENONCONF
ADD CONSTRAINT FK_PKNOTELINENONCONF_PKINGNOTELINE FOREIGN KEY (PACKINGNOTELINE_ID) REFERENCES PACKINGNOTELINE (PACKINGNOTELINE_ID) ON DELETE CASCADE;

ALTER TABLE PACKINGNOTELINENONCONF
ADD CONSTRAINT FK_PKNOTELINENONCONF_REJECTREASON FOREIGN KEY (REJECTREASON_ID) REFERENCES REJECTREASON (REJECTREASON_ID);

--PACKINGNOTELINE TABLE
ALTER TABLE PACKINGNOTELINE
DROP CONSTRAINT CHK_PACKINGNOTELINE_ORIGIN;

ALTER TABLE PACKINGNOTELINE
ADD CONSTRAINT CHK_PACKINGNOTELINE_ORIGIN CHECK (ORIGIN IN (0,1,2,3,4,9,10,11,12,13,15,16,17,21));

COMMENT ON COLUMN PACKINGNOTELINE.ORIGIN IS '0 = Generated based on Order 1 = Manually added 2 = Generated based on Outscan 3 = Quantity Correction Ticket 4 = Stock Delivery Note 9 = Import from DW 10 = Mounting Order 11 = Sales Order 12 = Credit Note 13 = Residential Delivery Note 15 = Charge To Product Process 16 = BM6 Ordering 17 = Truck Replenishment 21 = Rejects';

--REASONCODE TABLE
ALTER TABLE REASONCODE ADD (SERVICEEXCEPTIONFOC VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_REASONCODE_SERVICEEXCEPTIONFOC CHECK (SERVICEEXCEPTIONFOC IN ('Y', 'N')));

COMMENT ON COLUMN REASONCODE.SERVICEEXCEPTIONFOC IS 'Only used with module LINEN';
