-- ABS-36042

---------------- DELIVERYNOTEREMARK ----------------
CREATE SEQUENCE SEQ_DELIVERYNOTEREMARK
START WITH 1
INCREMENT BY 1;

CREATE TABLE DELIVERYNOTEREMARK
( DELIVERYNOTEREMARK_ID NUMBER NOT NULL,
  PLANT_ID              NUMBER NOT NULL,
  DATEACTIVE            DATE NOT NULL,
  DATEINACTIVE          DATE,
  SYSTEMUSER_ID         NUMBER NOT NULL,
  TIMESTAMP             DATE NOT NULL);
  
ALTER TABLE DELIVERYNOTEREMARK
  ADD CONSTRAINT XPKDELIVERYNOTEREMARK PRIMARY KEY (DELIVERYNOTEREMARK_ID);

CREATE INDEX XIF1DELIVERYNOTEREMARK ON DELIVERYNOTEREMARK (PLANT_ID ASC);

ALTER TABLE DELIVERYNOTEREMARK
  ADD CONSTRAINT PLANT_ID FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID);
  
---------------- DELIVERYNOTEREMARK_DESC ----------------
CREATE TABLE DELIVERYNOTEREMARK_DESC
( DELIVERYNOTEREMARK_ID NUMBER NOT NULL,
  LANGUAGE_ID           NUMBER NOT NULL,
  DESCRIPTION           VARCHAR2(300) NOT NULL,
  SYSTEMUSER_ID         NUMBER NOT NULL,
  TIMESTAMP             DATE NOT NULL);
  
ALTER TABLE DELIVERYNOTEREMARK_DESC
  ADD CONSTRAINT XPKDELIVERYNOTEREMARK_DESC PRIMARY KEY (DELIVERYNOTEREMARK_ID, LANGUAGE_ID);

CREATE INDEX XIF1DELIVERYNOTEREMARK_DESC ON DELIVERYNOTEREMARK_DESC (DELIVERYNOTEREMARK_ID ASC);
CREATE INDEX XIF2DELIVERYNOTEREMARK_DESC ON DELIVERYNOTEREMARK_DESC (LANGUAGE_ID ASC);
  
ALTER TABLE DELIVERYNOTEREMARK_DESC
  ADD CONSTRAINT DNREMARK_DESC_DNREMARK FOREIGN KEY (DELIVERYNOTEREMARK_ID)
  REFERENCES DELIVERYNOTEREMARK (DELIVERYNOTEREMARK_ID) ON DELETE CASCADE;
ALTER TABLE DELIVERYNOTEREMARK_DESC
  ADD CONSTRAINT DNREMARK_DESC_LANGUAGE FOREIGN KEY (LANGUAGE_ID) 
  REFERENCES LANGUAGES (LANGUAGE_ID);
  
---------------- PACKINGNOTE ----------------
ALTER TABLE PACKINGNOTE ADD (DELIVERYNOTEREMARK_ID NUMBER);

CREATE INDEX XIF19PACKINGNOTE ON PACKINGNOTE (DELIVERYNOTEREMARK_ID ASC);

ALTER TABLE PACKINGNOTE
  ADD CONSTRAINT PACKINGNOTE_DNREMARK FOREIGN KEY (DELIVERYNOTEREMARK_ID)
  REFERENCES DELIVERYNOTEREMARK (DELIVERYNOTEREMARK_ID);
