--Order 20013935.110

--UNIQUEITEMRETURNRENT table---

CREATE SEQUENCE SEQ_UNIQUEITEMRETURNRENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE UNIQUEITEMRETURNRENT
(
	UNIQUEITEMRETURNRENT_ID NUMBER NOT NULL ,
	UNIQUEITEM_LINK_ID   NUMBER NULL ,
	RETURNRENTTYPE       NUMBER DEFAULT  1  NOT NULL  CONSTRAINT  RETURNRENTTYPE_valid1_1 CHECK (RETURNRENTTYPE IN (1, 2)),
	CUSTOMER_LINK_ID     NUMBER NOT NULL ,
	WEAREREMPLOYMENT_LINK_ID NUMBER NOT NULL ,
	PRODUCT_LINK_ID      NUMBER NOT NULL ,
	SIZEDEFINITION_LINK_ID NUMBER NOT NULL ,
	FINISHINGMETHOD_ID   NUMBER NULL ,
	OUTOFCIRCDATE        DATE NOT NULL ,
	ENDDATERETURNRENT    DATE NOT NULL ,
	RENTYEAR             NUMBER NOT NULL ,
	RENTWEEK             NUMBER NOT NULL ,
	SYSTEMUSER_ID	     NUMBER NOT NULL ,
        TIMESTAMP            DATE NOT NULL
);

COMMENT ON COLUMN UNIQUEITEMRETURNRENT.RETURNRENTTYPE IS '1 = Return Rent
2 = Minimum Rent';

COMMENT ON COLUMN UNIQUEITEMRETURNRENT.OUTOFCIRCDATE IS 'Date the item was taken out of circulation';

COMMENT ON COLUMN UNIQUEITEMRETURNRENT.ENDDATERETURNRENT IS 'Last day, the return rent may be charged for the item';

COMMENT ON COLUMN UNIQUEITEMRETURNRENT.RENTYEAR IS 'Year for which the return rent must be charged (to be used for SQ process)';

COMMENT ON COLUMN UNIQUEITEMRETURNRENT.RENTWEEK IS 'Week for which the return rent must be charged (to be used for SQ process)';

CREATE UNIQUE INDEX XPKUNIQUEITEMRETURNRENT ON UNIQUEITEMRETURNRENT
(UNIQUEITEMRETURNRENT_ID   ASC);

CREATE UNIQUE INDEX XAK1UNIQUEITEMRETURNRENT ON UNIQUEITEMRETURNRENT
(CUSTOMER_LINK_ID   ASC,WEAREREMPLOYMENT_LINK_ID   ASC,PRODUCT_LINK_ID   ASC,SIZEDEFINITION_LINK_ID   ASC,FINISHINGMETHOD_ID   ASC,UNIQUEITEM_LINK_ID   ASC,RETURNRENTTYPE   ASC,RENTYEAR   ASC,RENTWEEK   ASC);

CREATE INDEX XIF1UNIQUEITEMRETURNRENT ON UNIQUEITEMRETURNRENT
(UNIQUEITEM_LINK_ID   ASC);

CREATE INDEX XIF2UNIQUEITEMRETURNRENT ON UNIQUEITEMRETURNRENT
(CUSTOMER_LINK_ID   ASC);

CREATE INDEX XIF3UNIQUEITEMRETURNRENT ON UNIQUEITEMRETURNRENT
(WEAREREMPLOYMENT_LINK_ID   ASC);

CREATE INDEX XIF4UNIQUEITEMRETURNRENT ON UNIQUEITEMRETURNRENT
(PRODUCT_LINK_ID   ASC);

CREATE INDEX XIF5UNIQUEITEMRETURNRENT ON UNIQUEITEMRETURNRENT
(SIZEDEFINITION_LINK_ID   ASC);

CREATE INDEX XIF6UNIQUEITEMRETURNRENT ON UNIQUEITEMRETURNRENT
(FINISHINGMETHOD_ID   ASC);

ALTER TABLE UNIQUEITEMRETURNRENT
ADD CONSTRAINT  XPKUNIQUEITEMRETURNRENT PRIMARY KEY (UNIQUEITEMRETURNRENT_ID);

ALTER TABLE UNIQUEITEMRETURNRENT
ADD CONSTRAINT UNIQUEITEM_LINK_UIRETURNRENT FOREIGN KEY (UNIQUEITEM_LINK_ID) REFERENCES UNIQUEITEM_LINK (UNIQUEITEM_LINK_ID);

ALTER TABLE UNIQUEITEMRETURNRENT
ADD CONSTRAINT CUSTOMER_LINK_UIRETURNRENT FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);

ALTER TABLE UNIQUEITEMRETURNRENT
ADD CONSTRAINT WE_LINK_UIRETURNRENT FOREIGN KEY (WEAREREMPLOYMENT_LINK_ID) REFERENCES WEAREREMPLOYMENT_LINK (WEAREREMPLOYMENT_LINK_ID);

ALTER TABLE UNIQUEITEMRETURNRENT
ADD CONSTRAINT PRODUCT_LINK_UIRETURNRENT FOREIGN KEY (PRODUCT_LINK_ID) REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);

ALTER TABLE UNIQUEITEMRETURNRENT
ADD CONSTRAINT SD_LINK_UIRETURNRENT FOREIGN KEY (SIZEDEFINITION_LINK_ID) REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);

ALTER TABLE UNIQUEITEMRETURNRENT
ADD CONSTRAINT FM_UIRETURNRENT FOREIGN KEY (FINISHINGMETHOD_ID) REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID) ON DELETE SET NULL;


---SQITEMINCIRCULATION table---

ALTER TABLE SQITEMINCIRCULATION
	ADD (QTYRENTEDRETURNRENT NUMBER NULL);

ALTER TABLE SQITEMINCIRCULATION
	ADD (VARIATIONQTYRENTEDRETURNRENT NUMBER NULL);


---CUSTOMER table---

ALTER TABLE CUSTOMER
	ADD (ALLOWCHARGERETURNRENT NUMBER  DEFAULT 0 NOT NULL CONSTRAINT ALLOWCHARGERETURNRENT_valid1_1 CHECK (ALLOWCHARGERETURNRENT IN (0, 1)));

ALTER TABLE CUSTOMER
	ADD (RETURNRENTWEEKS NUMBER  DEFAULT 0 NOT NULL);

COMMENT ON COLUMN CUSTOMER.RETURNRENTWEEKS IS 'This attributes indicates the number of weeks that still rent may be charged after the item has been taken out of circulation.';

COMMENT ON COLUMN CUSTOMER.ALLOWCHARGERETURNRENT IS 'Flag that indicates whether return rent should be charged for this customer. Possible values:
0 = Do not charge return rent
1 = Do charge return rent
';