--ABS-85034 / ABS-88104

----------------------- 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
10 = Outscan/sort extra box';

UPDATE CLIENTDELIVERYNOTE CDN
SET CDN.ORIGIN=1 WHERE CDN.ORIGIN NOT IN (1,2,3,4,5,6,7,8,9,10);

ALTER TABLE CLIENTDELIVERYNOTE ADD CONSTRAINT CLIENTDELIVERYNOTE_ORIGIN CHECK (ORIGIN BETWEEN 1 AND 10);

------------------- CLIENTDELIVERYNOTELINK ---------------

CREATE TABLE CLIENTDELIVERYNOTELINK (MAINCLIENTDELIVERYNOTE_ID NUMBER NOT NULL,
                                     CLIENTDELIVERYNOTE_ID NUMBER NOT NULL,
                                     SYSTEMUSER_ID NUMBER NOT NULL,
                                     TIMESTAMP DATE NOT NULL);
COMMENT ON COLUMN CLIENTDELIVERYNOTELINK.MAINCLIENTDELIVERYNOTE_ID
IS 'Foreign key to ClientDeliveryNote table
Will only be filled with CDN with origin = 4 (Outscan / sort).';
COMMENT ON COLUMN CLIENTDELIVERYNOTELINK.CLIENTDELIVERYNOTE_ID 
IS 'Foreign key to ClientDeliveryNote table
Will only be filled with CDN with origin = 10 (Outscan / sort extra box)';

ALTER TABLE CLIENTDELIVERYNOTELINK ADD CONSTRAINT XPKCLIENTDELIVERYNOTELINK PRIMARY KEY(MAINCLIENTDELIVERYNOTE_ID, CLIENTDELIVERYNOTE_ID);

CREATE INDEX XIF1CLIENTDELIVERYNOTELINK ON CLIENTDELIVERYNOTELINK(MAINCLIENTDELIVERYNOTE_ID ASC);
CREATE INDEX XIF2CLIENTDELIVERYNOTELINK ON CLIENTDELIVERYNOTELINK(CLIENTDELIVERYNOTE_ID ASC);

ALTER TABLE CLIENTDELIVERYNOTELINK ADD CONSTRAINT FK_CLDELNOTELINK_MAINCLDELNOTE FOREIGN KEY(MAINCLIENTDELIVERYNOTE_ID)
REFERENCES CLIENTDELIVERYNOTE(CLIENTDELIVERYNOTE_ID) ON DELETE CASCADE;
ALTER TABLE CLIENTDELIVERYNOTELINK ADD CONSTRAINT FK_CLDELNOTELINK_CLDELNOTE FOREIGN KEY(CLIENTDELIVERYNOTE_ID)
REFERENCES CLIENTDELIVERYNOTE(CLIENTDELIVERYNOTE_ID) ON DELETE CASCADE;