-- ABS-71901 / ABS-84779

------------- UNIQUEITEMLOSTINPLANT -------------------

CREATE SEQUENCE SEQ_UNIQUEITEMLOSTINPLANT
START WITH 1
INCREMENT BY 1;

CREATE TABLE UNIQUEITEMLOSTINPLANT (UNIQUEITEMLOSTINPLANT_ID NUMBER NOT NULL,
                                    UNIQUEITEM_LINK_ID NUMBER NOT NULL,
                                    LOSTDATE DATE NOT NULL,
                                    BUSINESSUNIT_ID NUMBER NOT NULL,
                                    INSCANDATE DATE NOT NULL,
                                    LOSSSTATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_UILOSTINPLANT_LOSSSTATUS CHECK(LOSSSTATUS IN(0,1,2)),
                                    LOSTATSTAY_ID NUMBER NOT NULL,
                                    RELATEDCUSTOMER_LINK_ID NUMBER NULL,
                                    FOUNDATDEPARTMENT_LINK_ID NUMBER NULL,
                                    FOUNDATSTAY_ID NUMBER NULL,
                                    FOUNDTIMESTAMP DATE NULL,
                                    SYSTEMUSER_ID NUMBER NOT NULL,
                                    TIMESTAMP DATE NOT NULL);
                                   
COMMENT ON COLUMN UNIQUEITEMLOSTINPLANT.LOSTDATE 
IS 'Date the item was marked as lost in plant';
COMMENT ON COLUMN UNIQUEITEMLOSTINPLANT.INSCANDATE
IS 'Last inscan date and time';
COMMENT ON COLUMN UNIQUEITEMLOSTINPLANT.INSCANDATE
IS '0 = Lost 
1 = Found
2 = Ragged while lost in plant
Default 0';

ALTER TABLE UNIQUEITEMLOSTINPLANT ADD CONSTRAINT XPK_UNIQUEITEMLOSTINPLANT PRIMARY KEY (UNIQUEITEMLOSTINPLANT_ID);

CREATE UNIQUE INDEX XAK1UNIQUEITEMLOSTINPLANT ON UNIQUEITEMLOSTINPLANT(UNIQUEITEMLOSTINPLANT_ID ASC, LOSTDATE ASC);

CREATE INDEX XIE1UNIQUEITEMLOSTINPLANT ON UNIQUEITEMLOSTINPLANT(BUSINESSUNIT_ID ASC, LOSTDATE ASC);
CREATE INDEX XIE2UNIQUEITEMLOSTINPLANT ON UNIQUEITEMLOSTINPLANT(BUSINESSUNIT_ID ASC, LOSSSTATUS ASC);
CREATE INDEX XIE3UNIQUEITEMLOSTINPLANT ON UNIQUEITEMLOSTINPLANT(UNIQUEITEM_LINK_ID ASC, LOSSSTATUS ASC);
CREATE INDEX XIE4UNIQUEITEMLOSTINPLANT ON UNIQUEITEMLOSTINPLANT(LOSTDATE ASC);
CREATE INDEX XIE5UNIQUEITEMLOSTINPLANT ON UNIQUEITEMLOSTINPLANT(TIMESTAMP ASC);

CREATE INDEX XIF1UNIQUEITEMLOSTINPLANT ON UNIQUEITEMLOSTINPLANT(UNIQUEITEM_LINK_ID ASC);
CREATE INDEX XIF2UNIQUEITEMLOSTINPLANT ON UNIQUEITEMLOSTINPLANT(BUSINESSUNIT_ID ASC);
CREATE INDEX XIF3UNIQUEITEMLOSTINPLANT ON UNIQUEITEMLOSTINPLANT(LOSTATSTAY_ID ASC);
CREATE INDEX XIF4UNIQUEITEMLOSTINPLANT ON UNIQUEITEMLOSTINPLANT(RELATEDCUSTOMER_LINK_ID ASC);
CREATE INDEX XIF5UNIQUEITEMLOSTINPLANT ON UNIQUEITEMLOSTINPLANT(FOUNDATDEPARTMENT_LINK_ID ASC);
CREATE INDEX XIF6UNIQUEITEMLOSTINPLANT ON UNIQUEITEMLOSTINPLANT(FOUNDATSTAY_ID ASC);


ALTER TABLE UNIQUEITEMLOSTINPLANT ADD CONSTRAINT FK_UILOSTINPLANT_UI_LINK_ID
FOREIGN KEY (UNIQUEITEM_LINK_ID)
REFERENCES UNIQUEITEM_LINK(UNIQUEITEM_LINK_ID);

ALTER TABLE UNIQUEITEMLOSTINPLANT ADD CONSTRAINT FK_UILOSTINPLANT_BUSINESSUNIT
FOREIGN KEY (BUSINESSUNIT_ID)
REFERENCES BUSINESSUNIT(BUSINESSUNIT_ID);

ALTER TABLE UNIQUEITEMLOSTINPLANT ADD CONSTRAINT FK_UILOSTINPLANT_LOSTATSTAY_ID
FOREIGN KEY (LOSTATSTAY_ID)
REFERENCES STAY(STAY_ID);

ALTER TABLE UNIQUEITEMLOSTINPLANT ADD CONSTRAINT FK_UILOSTINPLANT_RELCUST_LINK
FOREIGN KEY (RELATEDCUSTOMER_LINK_ID)
REFERENCES CUSTOMER_LINK(CUSTOMER_LINK_ID);

ALTER TABLE UNIQUEITEMLOSTINPLANT ADD CONSTRAINT FK_UILOSTINPLANT_FOUNDATDEP
FOREIGN KEY (FOUNDATDEPARTMENT_LINK_ID)
REFERENCES DEPARTMENT_LINK(DEPARTMENT_LINK_ID);

ALTER TABLE UNIQUEITEMLOSTINPLANT ADD CONSTRAINT FK_UILOSTINPLANT_FOUNDATSTAY
FOREIGN KEY (FOUNDATSTAY_ID)
REFERENCES STAY(STAY_ID);