-- ABS-35938

---------------- CUSTOMER ----------------
ALTER TABLE CUSTOMER ADD (RETURNSAMECONTAINER VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD312 CHECK (RETURNSAMECONTAINER IN ('Y','N')),
                          OWNBATCH VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD313 CHECK (OWNBATCH IN ('Y','N')));
                          
---------------- CUSTOMERCONTAINERTYPE ----------------
CREATE SEQUENCE SEQ_CUSTOMERCONTAINERTYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERCONTAINERTYPE
( CUSTOMERCONTAINERTYPE_ID NUMBER NOT NULL,
  CUSTOMER_ID              NUMBER NOT NULL,
  CONTAINERTYPE_ID         NUMBER NOT NULL,
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
);

ALTER TABLE CUSTOMERCONTAINERTYPE
  ADD CONSTRAINT XPKCUSTOMERCONTAINERTYPE PRIMARY KEY (CUSTOMERCONTAINERTYPE_ID);

CREATE UNIQUE INDEX XAK1CUSTOMERCONTAINERTYPE ON CUSTOMERCONTAINERTYPE (CUSTOMER_ID ASC, CONTAINERTYPE_ID ASC);

CREATE INDEX XIF1CUSTOMERCONTAINERTYPE ON CUSTOMERCONTAINERTYPE (CUSTOMER_ID ASC);

CREATE INDEX XIF2CUSTOMERCONTAINERTYPE ON CUSTOMERCONTAINERTYPE (CONTAINERTYPE_ID ASC);

ALTER TABLE CUSTOMERCONTAINERTYPE
  ADD CONSTRAINT CUSTOMERCONTAINERTYPE_CUST FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE CUSTOMERCONTAINERTYPE
  ADD CONSTRAINT CUSTOMERCONTAINERTYPE_TYPE FOREIGN KEY (CONTAINERTYPE_ID)
  REFERENCES CONTAINERTYPE (CONTAINERTYPE_ID);
