--ABSN-2121

--LAUNDRYBAG TABLE
CREATE SEQUENCE SEQ_LAUNDRYBAG
START WITH 1
INCREMENT BY 1;

CREATE TABLE LAUNDRYBAG
(
LAUNDRYBAG_ID					NUMBER NOT NULL,
BUSINESSUNIT_ID					NUMBER NOT NULL,
LAUNDRYBAGTYPE					NUMBER DEFAULT 0 NOT NULL,
IDCODE							VARCHAR2(24) NOT NULL,
MAXWEIGHT						NUMBER NOT NULL,						
SYSTEMUSER_ID					NUMBER NOT NULL,
TIMESTAMP						DATE NOT NULL
);

ALTER TABLE LAUNDRYBAG ADD CONSTRAINT XPKLAUNDRYBAG PRIMARY KEY (LAUNDRYBAG_ID);

CREATE UNIQUE INDEX XAK1LAUNDRYBAG ON LAUNDRYBAG (BUSINESSUNIT_ID ASC,LAUNDRYBAGTYPE,IDCODE ASC);
CREATE INDEX XIF1LAUNDRYBAG ON LAUNDRYBAG (BUSINESSUNIT_ID ASC);
  
ALTER TABLE LAUNDRYBAG
	ADD CONSTRAINT FK_LAUNDRYBAG_BUSINESSUNIT_ID FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT(BUSINESSUNIT_ID);

COMMENT ON COLUMN LAUNDRYBAG.LAUNDRYBAGTYPE IS '0 = Reject Bag';

--LAUNDRYBAGDELIVERY TABLE
CREATE SEQUENCE SEQ_LAUNDRYBAGDELIVERY
START WITH 1
INCREMENT BY 1;

CREATE TABLE LAUNDRYBAGDELIVERY
(
LAUNDRYBAGDELIVERY_ID			NUMBER NOT NULL,
LAUNDRYBAG_ID					NUMBER NOT NULL,
DISTRIBUTIONPOINT_ID			NUMBER NOT NULL,
STATUS_ID						NUMBER NOT NULL,
ENTRYDATE						DATE NOT NULL,
WEBUSER_ID						NUMBER NULL,
EXPECTEDPICKUPDATE				DATE NOT NULL,
REALPICKUPDATE					DATE NULL,
PICKUPSYSTEMUSER_ID				NUMBER NULL,
CONFIRMTIMESTAMP				DATE NULL,
CONFIRMSYSTEMUSER_ID			NUMBER NULL,
PROCESSINGTIMESTAMP				DATE NULL,
PROCESSINGSYSTEMUSER_ID			NUMBER NULL,
WORKSTATION_ID					NUMBER NULL,
RECEIVEWEIGHT           		NUMBER NULL,
SYSTEMUSER_ID					NUMBER NOT NULL,
TIMESTAMP						DATE NOT NULL
);

ALTER TABLE LAUNDRYBAGDELIVERY ADD CONSTRAINT XPKLAUNDRYBAGDELIVERY PRIMARY KEY (LAUNDRYBAGDELIVERY_ID);

CREATE UNIQUE INDEX XAK1LAUNDRYBAGDELIVERY ON LAUNDRYBAGDELIVERY (LAUNDRYBAG_ID ASC,ENTRYDATE ASC);
CREATE INDEX XIF1LAUNDRYBAGDELIVERY ON LAUNDRYBAGDELIVERY (LAUNDRYBAG_ID ASC);
CREATE INDEX XIF2LAUNDRYBAGDELIVERY ON LAUNDRYBAGDELIVERY (DISTRIBUTIONPOINT_ID ASC);
CREATE INDEX XIF3LAUNDRYBAGDELIVERY ON LAUNDRYBAGDELIVERY (STATUS_ID ASC);
CREATE INDEX XIF4LAUNDRYBAGDELIVERY ON LAUNDRYBAGDELIVERY (WEBUSER_ID ASC);
CREATE INDEX XIF5LAUNDRYBAGDELIVERY ON LAUNDRYBAGDELIVERY (PICKUPSYSTEMUSER_ID ASC);
CREATE INDEX XIF6LAUNDRYBAGDELIVERY ON LAUNDRYBAGDELIVERY (CONFIRMSYSTEMUSER_ID ASC);
CREATE INDEX XIF7LAUNDRYBAGDELIVERY ON LAUNDRYBAGDELIVERY (PROCESSINGSYSTEMUSER_ID ASC);
CREATE INDEX XIF8LAUNDRYBAGDELIVERY ON LAUNDRYBAGDELIVERY (WORKSTATION_ID ASC);
  
ALTER TABLE LAUNDRYBAGDELIVERY
	ADD CONSTRAINT FK_LAUNDRYBAGDEL_LAUNDRYBAG_ID FOREIGN KEY (LAUNDRYBAG_ID) REFERENCES LAUNDRYBAG(LAUNDRYBAG_ID) ON DELETE CASCADE;
ALTER TABLE LAUNDRYBAGDELIVERY
	ADD CONSTRAINT FK_LAUNDRYBAGDEL_DISTRNPOINT_ID FOREIGN KEY (DISTRIBUTIONPOINT_ID) REFERENCES DISTRIBUTIONPOINT(DISTRIBUTIONPOINT_ID) ON DELETE CASCADE;
ALTER TABLE LAUNDRYBAGDELIVERY
	ADD CONSTRAINT FK_LAUNDRYBAGDEL_STATUS_ID FOREIGN KEY (STATUS_ID) REFERENCES STATUS(STATUS_ID);
ALTER TABLE LAUNDRYBAGDELIVERY
	ADD CONSTRAINT FK_LAUNDRYBAGDEL_WEBUSER_ID FOREIGN KEY (WEBUSER_ID) REFERENCES WEBUSER(WEBUSER_ID) ON DELETE SET NULL;
ALTER TABLE LAUNDRYBAGDELIVERY
	ADD CONSTRAINT FK_LAUNDRYBAGDEL_PICKUPSYSTEMU_ID FOREIGN KEY (PICKUPSYSTEMUSER_ID) REFERENCES SYSTEMUSER(SYSTEMUSER_ID) ON DELETE SET NULL;
ALTER TABLE LAUNDRYBAGDELIVERY
	ADD CONSTRAINT FK_LAUNDRYBAGDEL_CONFSYSTEMU_ID FOREIGN KEY (CONFIRMSYSTEMUSER_ID) REFERENCES SYSTEMUSER(SYSTEMUSER_ID) ON DELETE SET NULL;
ALTER TABLE LAUNDRYBAGDELIVERY
	ADD CONSTRAINT FK_LAUNDRYBAGDEL_PRINGSYSTEMU_ID FOREIGN KEY (PROCESSINGSYSTEMUSER_ID) REFERENCES SYSTEMUSER(SYSTEMUSER_ID) ON DELETE SET NULL;
ALTER TABLE LAUNDRYBAGDELIVERY
	ADD CONSTRAINT FK_LAUNDRYBAGDEL_WORKSTATION_ID FOREIGN KEY (WORKSTATION_ID) REFERENCES WORKSTATION(WORKSTATION_ID) ON DELETE SET NULL;

COMMENT ON COLUMN LAUNDRYBAGDELIVERY.RECEIVEWEIGHT IS 'Actual weight registered at the laundry (via receive reject bags). Optional.';

--LAUNDRYBAGDELIVERYCONTENTS TABLE
CREATE SEQUENCE SEQ_LAUNDRYBAGDELIVERYCONTENTS
START WITH 1
INCREMENT BY 1;

CREATE TABLE LAUNDRYBAGDELIVERYCONTENTS
(
LAUNDRYBAGDELIVERYCONTENTS_ID	NUMBER NOT NULL,
LAUNDRYBAGDELIVERY_ID			NUMBER NOT NULL,			
PRODUCT_ID						NUMBER NOT NULL,
SIZEDEFINITION_ID				NUMBER NOT NULL,
FINISHINGMETHOD_ID				NUMBER NOT NULL,
THEORETICALWEIGHT				NUMBER NOT NULL,
SYSTEMUSER_ID					NUMBER NOT NULL,
TIMESTAMP						DATE NOT NULL
);

ALTER TABLE LAUNDRYBAGDELIVERYCONTENTS ADD CONSTRAINT XPKLAUNDRYBAGDELIVERYCONTENTS PRIMARY KEY (LAUNDRYBAGDELIVERYCONTENTS_ID);

CREATE UNIQUE INDEX XAK1LAUNDRYBAGDELIVERYCONTENTS ON LAUNDRYBAGDELIVERYCONTENTS (LAUNDRYBAGDELIVERY_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, FINISHINGMETHOD_ID ASC);
CREATE INDEX XIF1LAUNDRYBAGDELIVERYCONTENTS ON LAUNDRYBAGDELIVERYCONTENTS (LAUNDRYBAGDELIVERY_ID ASC);
CREATE INDEX XIF2LAUNDRYBAGDELIVERYCONTENTS ON LAUNDRYBAGDELIVERYCONTENTS (PRODUCT_ID ASC);
CREATE INDEX XIF3LAUNDRYBAGDELIVERYCONTENTS ON LAUNDRYBAGDELIVERYCONTENTS (SIZEDEFINITION_ID ASC);
CREATE INDEX XIF4LAUNDRYBAGDELIVERYCONTENTS ON LAUNDRYBAGDELIVERYCONTENTS (FINISHINGMETHOD_ID ASC);
  
ALTER TABLE LAUNDRYBAGDELIVERYCONTENTS
	ADD CONSTRAINT FK_LAUNDRYBAGDELCON_LADRYBAGDEL_ID FOREIGN KEY (LAUNDRYBAGDELIVERY_ID) REFERENCES LAUNDRYBAGDELIVERY(LAUNDRYBAGDELIVERY_ID) ON DELETE CASCADE;
ALTER TABLE LAUNDRYBAGDELIVERYCONTENTS
	ADD CONSTRAINT FK_LAUNDRYBAGDELCON_PRODUCT_ID FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(PRODUCT_ID);
ALTER TABLE LAUNDRYBAGDELIVERYCONTENTS
	ADD CONSTRAINT FK_LAUNDRYBAGDELCON_SIZEDEF_ID FOREIGN KEY (SIZEDEFINITION_ID) REFERENCES SIZEDEFINITION(SIZEDEFINITION_ID);
ALTER TABLE LAUNDRYBAGDELIVERYCONTENTS
	ADD CONSTRAINT FK_LAUNDRYBAGDELCON_FINMETHOD_ID FOREIGN KEY (FINISHINGMETHOD_ID) REFERENCES FINISHINGMETHOD(FINISHINGMETHOD_ID);
	
--LAUNDRYBAGDELCONTWEBCOUNT TABLE
CREATE SEQUENCE SEQ_LAUNDRYBAGDELCONTWEBCOUNT
START WITH 1
INCREMENT BY 1;

CREATE TABLE LAUNDRYBAGDELCONTWEBCOUNT
(
LAUNDRYBAGDELCONTWEBCOUNT_ID	NUMBER NOT NULL,
LAUNDRYBAGDELIVERYCONTENTS_ID	NUMBER NOT NULL,
REJECTREASON_ID					NUMBER NOT NULL,
REJECTCOUNT						NUMBER NOT NULL,
SYSTEMUSER_ID					NUMBER NOT NULL,
TIMESTAMP						DATE NOT NULL
);

ALTER TABLE LAUNDRYBAGDELCONTWEBCOUNT ADD CONSTRAINT XPKLAUNDRYBAGDELCONTWEBCOUNT PRIMARY KEY (LAUNDRYBAGDELCONTWEBCOUNT_ID);

CREATE UNIQUE INDEX XAK1LAUNDRYBAGDELCONTWEBCOUNT ON LAUNDRYBAGDELCONTWEBCOUNT (LAUNDRYBAGDELIVERYCONTENTS_ID ASC, REJECTREASON_ID ASC);
CREATE INDEX XIF1LAUNDRYBAGDELCONTWEBCOUNT ON LAUNDRYBAGDELCONTWEBCOUNT (LAUNDRYBAGDELIVERYCONTENTS_ID ASC);
CREATE INDEX XIF2LAUNDRYBAGDELCONTWEBCOUNT ON LAUNDRYBAGDELCONTWEBCOUNT (REJECTREASON_ID ASC);
  
ALTER TABLE LAUNDRYBAGDELCONTWEBCOUNT
	ADD CONSTRAINT FK_LBDELCONTWEBCOUNT_LBDELCONTENTS_ID FOREIGN KEY (LAUNDRYBAGDELIVERYCONTENTS_ID) REFERENCES LAUNDRYBAGDELIVERYCONTENTS(LAUNDRYBAGDELIVERYCONTENTS_ID) ON DELETE CASCADE;
ALTER TABLE LAUNDRYBAGDELCONTWEBCOUNT
	ADD CONSTRAINT FK_LBDELCONTWEBCOUNT_REJECTREASON_ID FOREIGN KEY (REJECTREASON_ID) REFERENCES REJECTREASON(REJECTREASON_ID);
	
--LAUNDRYBAGDELCONTCONFIRMCOUNT TABLE
CREATE SEQUENCE SEQ_LAUNDRYBAGDELCONTCONFIRMCOUNT
START WITH 1
INCREMENT BY 1;

CREATE TABLE LAUNDRYBAGDELCONTCONFIRMCOUNT
(
LAUNDRYBAGDELCONTCONFCOUNT_ID	NUMBER NOT NULL,
LAUNDRYBAGDELIVERYCONTENTS_ID	NUMBER NOT NULL,
REJECTREASON_ID					NUMBER NOT NULL,
REJECTCOUNT						NUMBER NOT NULL,
SYSTEMUSER_ID					NUMBER NOT NULL,
TIMESTAMP						DATE NOT NULL
);

ALTER TABLE LAUNDRYBAGDELCONTCONFIRMCOUNT ADD CONSTRAINT XPKLAUNDRYBAGDELCONTCONFIRMCOUNT PRIMARY KEY (LAUNDRYBAGDELCONTCONFCOUNT_ID);

CREATE UNIQUE INDEX XAK1LAUNDRYBAGDELCONTCONFIRMCOUNT ON LAUNDRYBAGDELCONTCONFIRMCOUNT (LAUNDRYBAGDELIVERYCONTENTS_ID ASC, REJECTREASON_ID ASC);
CREATE INDEX XIF1LAUNDRYBAGDELCONTCONFIRMCOUNT ON LAUNDRYBAGDELCONTCONFIRMCOUNT (LAUNDRYBAGDELIVERYCONTENTS_ID ASC);
CREATE INDEX XIF2LAUNDRYBAGDELCONTCONFIRMCOUNT ON LAUNDRYBAGDELCONTCONFIRMCOUNT (REJECTREASON_ID ASC);
  
ALTER TABLE LAUNDRYBAGDELCONTCONFIRMCOUNT
	ADD CONSTRAINT FK_LBDELCONTCONCOUNT_LBDELCONTENTS_ID FOREIGN KEY (LAUNDRYBAGDELIVERYCONTENTS_ID) REFERENCES LAUNDRYBAGDELIVERYCONTENTS(LAUNDRYBAGDELIVERYCONTENTS_ID) ON DELETE CASCADE;
ALTER TABLE LAUNDRYBAGDELCONTCONFIRMCOUNT
	ADD CONSTRAINT FK_LBDELCONTCONCOUNT_REJECTREASON_ID FOREIGN KEY (REJECTREASON_ID) REFERENCES REJECTREASON(REJECTREASON_ID);
	
--UNIQUEIDCONFIGURATION TABLE
ALTER TABLE UNIQUEIDCONFIGURATION DROP CONSTRAINT PACKAGETYPE_VALID;

ALTER TABLE UNIQUEIDCONFIGURATION ADD CONSTRAINT CHK_UNIQUEIDCONFIG_PACKAGETYPE CHECK (PACKAGETYPE IN (1,2,3,4,5,6,7,8,9,10,11));

COMMENT ON COLUMN UNIQUEIDCONFIGURATION.PACKAGETYPE IS '1 = Product
														2 = Storage Location
														3 = Container
														4 = Bundle (only available for module license)
														5 = Residential/domestic
														6 = OR Packs
														7 = Carts
														8 = Bags
														9 = Packers
														10 = Curtains
														11 = Reject Bags';

--STATUS TABLE
COMMENT ON COLUMN STATUS.STATUSTYPE
  IS 'IN - Invoice
PN - Packing Note / Delivery Note
WN - Weighing Note
ST - Statement
RB - Remit batch
WO - Work Order
UI - Unique item
SR - Straight Even Return
GD - Garment Dispenser
PO - Purchase Order
BN - Unique Bundle
UR - Unique Residential Item
PL - Pick List
MO - Mounting Order
CA - Cart
OR - OR Pack
SL - Shipment List
CO - Contract
CN - Consignment Note
US - Unique Item Service
RJ - Reject Bag
';

ALTER TABLE STATUS DROP CONSTRAINT CHK_STATUS_STATUSTYPE;

ALTER TABLE STATUS
  ADD CONSTRAINT CHK_STATUS_STATUSTYPE
  CHECK (STATUSTYPE IN ('IN', 'MO', 'PN', 'WN', 'ST', 'RB', 'WO', 'UI', 'SR', 'GD', 'PO', 'BN', 'UR', 'PL', 'CA', 'OR', 'SL', 'CO', 'CN', 'US', 'RJ'));