--- ABS-117907 / ABS-122897

CREATE SEQUENCE SEQ_CONTAINERCHARGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTAINERCHARGE
(
    CONTAINERCHARGE_ID          NUMBER NOT NULL,
    CONTAINER_ID                NUMBER NOT NULL,
    CONTAINERBILLPERCUSTOMER_ID	NUMBER NOT NULL,
    DISTRIBUTIONPOINT_LINK_ID   NUMBER NOT NULL,
    CHARGECREATEDDATE           DATE NOT NULL,
    TYPE                        NUMBER  DEFAULT 1 NOT NULL CONSTRAINT CHK_CONTAINERCHARGE_TYPE CHECK (TYPE IN (1,2)),
    INVOICEHEADER_ID            NUMBER NULL,
    SYSTEMUSER_ID               NUMBER NOT NULL,
    TIMESTAMP                   DATE DEFAULT SYSDATE NOT NULL 
);


COMMENT ON COLUMN CONTAINERCHARGE.TYPE
  IS '1 = Delivery (default) 2 = Return';

ALTER TABLE CONTAINERCHARGE 
      ADD CONSTRAINT XPKCONTAINERCHARGE PRIMARY KEY (CONTAINERCHARGE_ID);

CREATE UNIQUE INDEX XAK1CONTAINERCHARGE ON CONTAINERCHARGE (CONTAINER_ID ASC, CHARGECREATEDDATE ASC, CONTAINERBILLPERCUSTOMER_ID ASC, TYPE ASC);
CREATE INDEX XIF1CONTAINERCHARGE ON CONTAINERCHARGE (CONTAINER_ID ASC);
CREATE INDEX XIF2CONTAINERCHARGE ON CONTAINERCHARGE (CONTAINERBILLPERCUSTOMER_ID ASC);
CREATE INDEX XIF3CONTAINERCHARGE ON CONTAINERCHARGE (INVOICEHEADER_ID ASC);
CREATE INDEX XIF4CONTAINERCHARGE ON CONTAINERCHARGE (DISTRIBUTIONPOINT_LINK_ID ASC);

ALTER TABLE CONTAINERCHARGE 
      ADD CONSTRAINT FK_CONTAINERCHARGE_CONTAINERID FOREIGN KEY (CONTAINER_ID)
      REFERENCES CONTAINER (CONTAINER_ID);
      
ALTER TABLE CONTAINERCHARGE 
      ADD CONSTRAINT FK_CONTAINERCHARGE_BILLCUST FOREIGN KEY (CONTAINERBILLPERCUSTOMER_ID)
      REFERENCES CONTAINERBILLPERCUSTOMER (CONTAINERBILLPERCUSTOMER_ID);
      
ALTER TABLE CONTAINERCHARGE 
      ADD CONSTRAINT FK_CONTAINERCHARGE_INVOICEH FOREIGN KEY (INVOICEHEADER_ID)
      REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE SET NULL;  

ALTER TABLE CONTAINERCHARGE 
      ADD CONSTRAINT FK_CONTAINERCHARGE_DP FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID)
      REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID) ON DELETE CASCADE;