--------------- SUPPLIERDELDAY --------------- 
CREATE SEQUENCE SEQ_SUPPLIERDELDAY
START WITH 1
INCREMENT BY 1;

CREATE TABLE SUPPLIERDELDAY
( SUPPLIERDELDAY_ID NUMBER NOT NULL,
  SUPPLIER_ID       NUMBER NOT NULL,
  DELIVERYDAYS      NUMBER DEFAULT 1 NOT NULL
                    CONSTRAINT DELIVERYDAYS_VALID1_1 CHECK (DELIVERYDAYS BETWEEN 0 AND 999),
  SYSTEMUSER_ID     NUMBER NOT NULL,
  TIMESTAMP         DATE NOT NULL);

ALTER TABLE SUPPLIERDELDAY
  ADD CONSTRAINT XPKSUPPLIERDELDAY PRIMARY KEY (SUPPLIERDELDAY_ID);
  
CREATE UNIQUE INDEX XAK1SUPPLIERDELDAY ON SUPPLIERDELDAY (SUPPLIER_ID ASC, DELIVERYDAYS ASC);
  
CREATE INDEX XIF1SUPPLIERDELDAY ON SUPPLIERDELDAY (SUPPLIER_ID ASC);

ALTER TABLE SUPPLIERDELDAY
  ADD CONSTRAINT SUPPLIERDELDAY_SUPPLIER FOREIGN KEY (SUPPLIER_ID)
  REFERENCES SUPPLIER (SUPPLIER_ID) ON DELETE CASCADE;

--------------- SUPPLIER --------------- 
ALTER TABLE SUPPLIER ADD (DEFAULTSUPPLIERDELDAY_ID NUMBER NULL);

CREATE INDEX XIF7SUPPLIER ON SUPPLIER (DEFAULTSUPPLIERDELDAY_ID ASC);

ALTER TABLE SUPPLIER
  ADD CONSTRAINT SUPPLIER_SUPPLIERDELDAY FOREIGN KEY (DEFAULTSUPPLIERDELDAY_ID)
  REFERENCES SUPPLIERDELDAY (SUPPLIERDELDAY_ID);

--------------- AGGSUPPLIERORDERINGTERMS --------------- 
ALTER TABLE AGGSUPPLIERORDERINGTERMS ADD (SUPPLIERDELDAY_ID NUMBER NULL);

CREATE INDEX XIF4AGGSUPPLIERORDERINGTERMS ON AGGSUPPLIERORDERINGTERMS (SUPPLIERDELDAY_ID ASC);

ALTER TABLE AGGSUPPLIERORDERINGTERMS
  ADD CONSTRAINT AGGSUPPLORDERTERMS_SUPPDELDAY FOREIGN KEY (SUPPLIERDELDAY_ID)
  REFERENCES SUPPLIERDELDAY (SUPPLIERDELDAY_ID) ON DELETE SET NULL;
  
--------------- AGGSUPPLIERPRICETERMS --------------- 
ALTER TABLE AGGSUPPLIERPRICETERMS ADD (SUPPLIERDELDAY_ID NUMBER NULL);

CREATE INDEX XIF4AGGSUPPLIERPRICETERMS ON AGGSUPPLIERPRICETERMS (SUPPLIERDELDAY_ID ASC);

ALTER TABLE AGGSUPPLIERPRICETERMS
  ADD CONSTRAINT AGGSUPPPRICETERMS_SUPPDELDAY FOREIGN KEY (SUPPLIERDELDAY_ID)
  REFERENCES SUPPLIERDELDAY (SUPPLIERDELDAY_ID) ON DELETE SET NULL;
