-- ABS-47292/ ABS-114625

--------- CUSTOMERCONTAINERRENTPRICE  -------------

CREATE UNIQUE INDEX XAK1CUSTOMERCONTAINERRENTPRICE ON CUSTOMERCONTAINERRENTPRICE (CONTAINERBILLPERCUSTOMER_ID ASC, STARTDATE ASC);

--------- CONTAINERRENT TABLE -------------
CREATE INDEX XIE1CONTAINERRENT ON CONTAINERRENT (CONTAINERBILLPERCUSTOMER_ID ASC, RETURNDATE ASC);


--------- CONTAINERRENTDETAILS TABLE -------------

CREATE TABLE CONTAINERRENTDETAILS
(
  CONTAINERRENT_ID            NUMBER NOT NULL,
  CHARGEDATE                  DATE NOT NULL,
  WEEKSINUSE                  NUMBER DEFAULT 0 NOT NULL,
  FREEOFCHARGEWEEK            VARCHAR2(1) DEFAULT 'N' NOT NULL
                              CONSTRAINT CHK_CONTAINERRENTDET_FREEOFCH CHECK (FREEOFCHARGEWEEK IN ('Y', 'N')),
  INVOICEHEADER_ID            NUMBER NULL,
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL
)
;

ALTER TABLE CONTAINERRENTDETAILS
  ADD CONSTRAINT XPKCONTAINERRENTDETAILS PRIMARY KEY (CONTAINERRENT_ID, CHARGEDATE);

CREATE INDEX XIF1CONTAINERRENTDETAILS ON CONTAINERRENTDETAILS (CONTAINERRENT_ID ASC);
CREATE INDEX XIF2CONTAINERRENTDETAILS ON CONTAINERRENTDETAILS (INVOICEHEADER_ID ASC);

ALTER TABLE CONTAINERRENTDETAILS
  ADD CONSTRAINT FK_CONTAINERRENTDET_CR FOREIGN KEY (CONTAINERRENT_ID)
  REFERENCES CONTAINERRENT (CONTAINERRENT_ID) ON DELETE CASCADE;
  
ALTER TABLE CONTAINERRENTDETAILS
  ADD CONSTRAINT FK_CONTAINERRENTDET_IH FOREIGN KEY (INVOICEHEADER_ID)
  REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE CASCADE;


--------- INVOICELINECONTAINER TABLE -------------
ALTER TABLE INVOICELINECONTAINER
  DROP CONSTRAINT FK_INVOICELINECONTAINER_IL;
  
ALTER TABLE INVOICELINECONTAINER
  ADD CONSTRAINT FK_INVOICELINECONTAINER_ILV FOREIGN KEY (INVOICELINE_ID)
  REFERENCES INVOICELINEVARIOUS (INVOICELINE_ID) ON DELETE CASCADE;