--ABS-91787 / ABS-104651
CREATE SEQUENCE SEQ_DELIVERYPOINTIMAGE
START WITH 1
INCREMENT BY 1;

--- DELIVERYPOINTIMAGE
CREATE TABLE DELIVERYPOINTIMAGE
(

  DELIVERYPOINTIMAGE_ID NUMBER NOT NULL,
  DELIVERYPOINT_ID NUMBER NOT NULL,
  IMAGENAME VARCHAR2(60) NOT NULL,
  DESCRIPTION VARCHAR2(80) NULL,
  IMAGE BLOB NOT NULL,
  EMPLOYEE_ID NUMBER NULL,
  SYSTEMUSER_ID  NUMBER NOT NULL,
  TIMESTAMP      DATE NOT NULL
);

COMMENT ON COLUMN DELIVERYPOINTIMAGE.EMPLOYEE_ID
  IS 'Only filled when added via RouteAssistant';

ALTER TABLE DELIVERYPOINTIMAGE
  ADD CONSTRAINT XPKDELIVERYPOINTIMAGE PRIMARY KEY (DELIVERYPOINTIMAGE_ID);

CREATE UNIQUE INDEX XAK1DELIVERYPOINTIMAGE ON DELIVERYPOINTIMAGE
(DELIVERYPOINT_ID   ASC, IMAGENAME  ASC);

CREATE INDEX XIF1DELIVERYPOINTIMAGE ON DELIVERYPOINTIMAGE(DELIVERYPOINT_ID  ASC);

CREATE INDEX XIF2DELIVERYPOINTIMAGE ON DELIVERYPOINTIMAGE(EMPLOYEE_ID  ASC);

ALTER TABLE DELIVERYPOINTIMAGE
  ADD CONSTRAINT FK_DELPOINTIMG_DELPONIT FOREIGN KEY (DELIVERYPOINT_ID)
  REFERENCES DELIVERYPOINT(DELIVERYPOINT_ID) ON DELETE CASCADE;

ALTER TABLE DELIVERYPOINTIMAGE
  ADD CONSTRAINT FK_DELPOINTIMG_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID)
  REFERENCES EMPLOYEE(EMPLOYEE_ID) ON DELETE SET NULL;







