--ABSN-11441

--UNIQUEBUNDLE TABLE
ALTER TABLE UNIQUEBUNDLE ADD (EXPIRATIONDATE DATE NULL);
COMMENT ON COLUMN UNIQUEBUNDLE.STATUS_ID IS 'For the moment following statuses are in use: 10 - New 12 - Repair Needed 14 - Rewash Needed 16 - Ragging Needed 20 - Scanned out 22 - Returned Clean 24 - Used 26 - Expired';

--UNIQUEBUNDLEHISTORY TABLE
CREATE SEQUENCE SEQ_UNIQUEBUNDLEHISTORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE UNIQUEBUNDLEHISTORY(
UNIQUEBUNDLEHISTORY_ID     NUMBER NOT NULL,      
BUSINESSUNIT_ID            NUMBER NOT NULL,
UNIQUEBUNDLE_ID            NUMBER NOT NULL,
SCANTIMESTAMP              DATE NOT NULL,
TRANSACTIONTYPE_ID         NUMBER NOT NULL,
DISTRIBUTION_POINT_LINK_ID NUMBER DEFAULT NULL,
STATUS_ID                  NUMBER NOT NULL,
STAY_ID                    NUMBER NOT NULL,
PREVSTATUS_ID              NUMBER DEFAULT NULL,
PREVSTAY_ID                NUMBER DEFAULT NULL,
SCANSTATION                VARCHAR2(12) DEFAULT NULL,
DELIVERYNOTENUMBER         NUMBER DEFAULT NULL,
SYSTEMUSER_ID              NUMBER NOT NULL,
TIMESTAMP                  DATE NOT NULL
);

COMMENT ON COLUMN UNIQUEBUNDLEHISTORY.SCANTIMESTAMP IS 'Date/Time the unique bundle was scanned';
COMMENT ON COLUMN UNIQUEBUNDLEHISTORY.SCANSTATION IS 'Workstation where the scan was recorded';
COMMENT ON COLUMN UNIQUEBUNDLEHISTORY.DELIVERYNOTENUMBER IS 'Delivery Note Number on which the bundle is scanned out to a customer';

ALTER TABLE UNIQUEBUNDLEHISTORY ADD CONSTRAINT XPKUNIQUEBUNDLEHISTORY PRIMARY KEY (UNIQUEBUNDLEHISTORY_ID);

CREATE UNIQUE INDEX XAK1UNIQUEBUNDLEHISTORY ON UNIQUEBUNDLEHISTORY(BUSINESSUNIT_ID ASC, UNIQUEBUNDLE_ID ASC, SCANTIMESTAMP ASC, TRANSACTIONTYPE_ID ASC);

CREATE INDEX XIF1UNIQUEBUNDLEHISTORY ON UNIQUEBUNDLEHISTORY (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2UNIQUEBUNDLEHISTORY ON UNIQUEBUNDLEHISTORY (UNIQUEBUNDLE_ID ASC);
CREATE INDEX XIF3UNIQUEBUNDLEHISTORY ON UNIQUEBUNDLEHISTORY (TRANSACTIONTYPE_ID ASC);
CREATE INDEX XIF4UNIQUEBUNDLEHISTORY ON UNIQUEBUNDLEHISTORY (DISTRIBUTION_POINT_LINK_ID ASC);
CREATE INDEX XIF5UNIQUEBUNDLEHISTORY ON UNIQUEBUNDLEHISTORY (STATUS_ID ASC);
CREATE INDEX XIF6UNIQUEBUNDLEHISTORY ON UNIQUEBUNDLEHISTORY (STAY_ID ASC);
CREATE INDEX XIF7UNIQUEBUNDLEHISTORY ON UNIQUEBUNDLEHISTORY (PREVSTATUS_ID ASC);
CREATE INDEX XIF8UNIQUEBUNDLEHISTORY ON UNIQUEBUNDLEHISTORY (PREVSTAY_ID ASC);

ALTER TABLE UNIQUEBUNDLEHISTORY ADD CONSTRAINT FK_UNIQUEBUNDLEHISTORY_BUSINESSUNIT_ID FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
ALTER TABLE UNIQUEBUNDLEHISTORY ADD CONSTRAINT FK_UNIQUEBUNDLEHISTORY_UNIQUEBUNDLE_ID FOREIGN KEY (UNIQUEBUNDLE_ID) REFERENCES UNIQUEBUNDLE (UNIQUEBUNDLE_ID) ON DELETE CASCADE;
ALTER TABLE UNIQUEBUNDLEHISTORY ADD CONSTRAINT FK_UNQUEBNDLHSTORY_TRNSCTINTYPE_ID FOREIGN KEY (TRANSACTIONTYPE_ID) REFERENCES TRANSACTIONTYPE (TRANSACTIONTYPE_ID);
ALTER TABLE UNIQUEBUNDLEHISTORY ADD CONSTRAINT FK_UNQUEBNDLHSTORY_DSTRBTION_PNT_LNK_ID FOREIGN KEY (DISTRIBUTION_POINT_LINK_ID) REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);
ALTER TABLE UNIQUEBUNDLEHISTORY ADD CONSTRAINT FK_UNIQUEBUNDLEHISTORY_STATUS_ID FOREIGN KEY (STATUS_ID) REFERENCES STATUS (STATUS_ID);
ALTER TABLE UNIQUEBUNDLEHISTORY ADD CONSTRAINT FK_UNIQUEBUNDLEHISTORY_STAY_ID FOREIGN KEY (STAY_ID) REFERENCES STAY (STAY_ID);
ALTER TABLE UNIQUEBUNDLEHISTORY ADD CONSTRAINT FK_UNIQUEBUNDLEHISTORY_PREVSTATUS_ID FOREIGN KEY (PREVSTATUS_ID) REFERENCES STATUS (STATUS_ID);
ALTER TABLE UNIQUEBUNDLEHISTORY ADD CONSTRAINT FK_UNIQUEBUNDLEHISTORY_PREVSTAY_ID FOREIGN KEY (PREVSTAY_ID) REFERENCES STAY (STAY_ID);
