--ABSN-4000

--WEIGHINGCATEGPERCUSTOMER TABLE
ALTER TABLE WEIGHINGCATEGPERCUSTOMER ADD (USEDFORREJECTBAGS NUMBER DEFAULT '0' NOT NULL CONSTRAINT CHK_WCATPERCUST_USEDFORREJECTBAGS CHECK (USEDFORREJECTBAGS IN (0,1)));

COMMENT ON COLUMN WEIGHINGCATEGPERCUSTOMER.USEDFORREJECTBAGS 
IS '0 = Not used for reject bags or not applicable.
1 = Used for Reject Bags (weight correction).
Option 1 can only be set if WeightClean = ‘Y’';


--LAUNDRYBAGDELIVERY TABLE
ALTER TABLE LAUNDRYBAGDELIVERY ADD (RECEIVETIMESTAMP DATE NULL,
RECEIVESYSTEMUSER_ID NUMBER NULL);

CREATE INDEX XIF9LAUNDRYBAGDELIVERY ON LAUNDRYBAGDELIVERY (RECEIVESYSTEMUSER_ID ASC);

ALTER TABLE LAUNDRYBAGDELIVERY
	ADD CONSTRAINT FK_LAUNDRYBAGDEL_RECSYSMUS_ID FOREIGN KEY (RECEIVESYSTEMUSER_ID) REFERENCES SYSTEMUSER(SYSTEMUSER_ID) ON DELETE SET NULL;

COMMENT ON COLUMN LAUNDRYBAGDELIVERY.RECEIVETIMESTAMP IS 'Received / scanned in the Laundry.';	
	
	
--LAUNDRYBAGDELCONTCONFIRMCOUNT TABLE
ALTER TABLE LAUNDRYBAGDELCONTCONFIRMCOUNT ADD (PACKINGNOTELINE_ID NUMBER NULL);

CREATE INDEX XIF3LAUNDRYBAGDELCONTCONFIRMCOUNT ON LAUNDRYBAGDELCONTCONFIRMCOUNT (PACKINGNOTELINE_ID ASC);

ALTER TABLE LAUNDRYBAGDELCONTCONFIRMCOUNT 
	ADD CONSTRAINT FK_LBDELCONTCONCOUNT_PACKNL_ID FOREIGN KEY (PACKINGNOTELINE_ID) REFERENCES PACKINGNOTELINE(PACKINGNOTELINE_ID) ON DELETE SET NULL;

COMMENT ON COLUMN LAUNDRYBAGDELCONTCONFIRMCOUNT.PACKINGNOTELINE_ID IS 'Can be correction ticket or packing / delivery note';


--LAUNDRYBAGMESSAGE TABLE
CREATE TABLE LAUNDRYBAGMESSAGE
(
LAUNDRYBAGDELIVERY_ID			NUMBER NOT NULL,
MESSAGE_ID						NUMBER NOT NULL,
SYSTEMUSER_ID					NUMBER NOT NULL,
TIMESTAMP						DATE NOT NULL
);

ALTER TABLE LAUNDRYBAGMESSAGE ADD CONSTRAINT XPKLAUNDRYBAGMESSAGE PRIMARY KEY (LAUNDRYBAGDELIVERY_ID,MESSAGE_ID);

CREATE INDEX XIF1LAUNDRYBAGMESSAGE ON LAUNDRYBAGMESSAGE (LAUNDRYBAGDELIVERY_ID ASC);
CREATE INDEX XIF2LAUNDRYBAGMESSAGE ON LAUNDRYBAGMESSAGE (MESSAGE_ID ASC);
                    
ALTER TABLE LAUNDRYBAGMESSAGE
	ADD CONSTRAINT FK_LAUNDRYBAGMESSAGE_LAUNDRYBAGDEL_ID FOREIGN KEY (LAUNDRYBAGDELIVERY_ID) REFERENCES LAUNDRYBAGDELIVERY(LAUNDRYBAGDELIVERY_ID) ON DELETE CASCADE;
ALTER TABLE LAUNDRYBAGMESSAGE
	ADD CONSTRAINT FK_LAUNDRYBAGMESSAGE_MESSAGE_ID FOREIGN KEY (MESSAGE_ID) REFERENCES MESSAGE(MESSAGE_ID) ON DELETE CASCADE;
