-- ABS-30100

CREATE SEQUENCE SEQ_PICKEDUPCONTAINERS
START WITH 1
INCREMENT BY 1;

CREATE TABLE PICKEDUPCONTAINERS
(
  PICKEDUPCONTAINERS_ID     NUMBER NOT NULL,
  STOP_ID                   NUMBER NOT NULL,
  DISTRIBUTIONPOINT_LINK_ID NUMBER NOT NULL,
  PICKEDUPQUANTITY          NUMBER DEFAULT 0 NOT NULL,
  CONTAINERTYPE_ID          NUMBER NOT NULL,
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
);

ALTER TABLE PICKEDUPCONTAINERS
  ADD CONSTRAINT XPKPICKEDUPCONTAINERS_ID PRIMARY KEY (PICKEDUPCONTAINERS_ID);

ALTER TABLE PICKEDUPCONTAINERS
  ADD CONSTRAINT XAK1PICKEDUPCONTAINERS UNIQUE (STOP_ID, DISTRIBUTIONPOINT_LINK_ID, CONTAINERTYPE_ID);
  
ALTER TABLE PICKEDUPCONTAINERS
  ADD CONSTRAINT PICKEDUPCONTAINERS_DISTRIB FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID)
  REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);
  
ALTER TABLE PICKEDUPCONTAINERS
  ADD CONSTRAINT PICKEDUPCONTAINERS_STOP FOREIGN KEY (STOP_ID)
  REFERENCES STOP (STOP_ID);
  
ALTER TABLE PICKEDUPCONTAINERS
  ADD CONSTRAINT PICKEDUPCONTAINERS_TYPE FOREIGN KEY (CONTAINERTYPE_ID)
  REFERENCES CONTAINERTYPE (CONTAINERTYPE_ID);
