-- ABS-24732/ ABS-25196

---PURCHASEREQUISITION table---
ALTER TABLE PURCHASEREQUISITION ADD (DELIVERYPOINT_ID NUMBER NULL,
                                     SUPPLIERDELIVERYDAYS NUMBER DEFAULT 1 NOT NULL
                                     CONSTRAINT SUPPLIERDELIVERYDAYS_VALID1_1 CHECK (SUPPLIERDELIVERYDAYS BETWEEN 0 AND 999)
                                     );

COMMENT ON COLUMN PURCHASEREQUISITION.DELIVERYPOINT_ID
  IS 'Will only be filled in case items are intended to be shipped to an ordering customer (ShipTo = 3)';

DROP INDEX XAK1PURCHASEREQUISITION;
CREATE UNIQUE INDEX XAK1PURCHASEREQUISITION ON PURCHASEREQUISITION (STOCKROOM_ID ASC, SUPPLIER_ID ASC, CUSTOMER_ID ASC
, CUSTOMEROWNED ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, REQUESTEDGRADE_ID ASC, MODIFICATION1_ID ASC
, MODIFICATION2_ID ASC, MODIFICATION3_ID ASC, CODEUNIQUELY ASC, PREWASH ASC, PURCHASECATEGORY ASC
, SHIPTO ASC, SHIPTOSTOCKROOM_ID ASC, BILLTOCOMPANY ASC, DELIVERYPOINT_ID ASC);

CREATE INDEX XIF16PURCHASEREQUISITION ON PURCHASEREQUISITION (DELIVERYPOINT_ID);
 
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_DELPOINT FOREIGN KEY (DELIVERYPOINT_ID)
  REFERENCES DELIVERYPOINT (DELIVERYPOINT_ID);

---BACKORDEREXECUTIONWOL table---
ALTER TABLE BACKORDEREXECUTIONWOL ADD (DELIVERYPOINT_ID NUMBER NULL);
 
COMMENT ON COLUMN BACKORDEREXECUTIONWOL.DELIVERYPOINT_ID
  IS 'Will only be filled in case items are intended to be shipped to an ordering customer (ShipTo = 3)';

CREATE INDEX XIF5BACKORDEREXECUTIONWOL ON BACKORDEREXECUTIONWOL (DELIVERYPOINT_ID);
 
ALTER TABLE BACKORDEREXECUTIONWOL
  ADD CONSTRAINT BACKORDEREXECUTIONWOL_DELPOINT FOREIGN KEY (DELIVERYPOINT_ID)
  REFERENCES DELIVERYPOINT (DELIVERYPOINT_ID);

---BACKORDEREXECUTIONWOLE table--- 
ALTER TABLE BACKORDEREXECUTIONWOLE ADD (DELIVERYPOINT_ID NUMBER NULL);
 
COMMENT ON COLUMN BACKORDEREXECUTIONWOLE.DELIVERYPOINT_ID
  IS 'Will only be filled in case items are intended to be shipped to an ordering customer (ShipTo = 3)';

CREATE INDEX XIF5BACKORDEREXECUTIONWOLE ON BACKORDEREXECUTIONWOLE (DELIVERYPOINT_ID);
 
ALTER TABLE BACKORDEREXECUTIONWOLE
  ADD CONSTRAINT BACKORDEREXECUTIONWOLE_DELP FOREIGN KEY (DELIVERYPOINT_ID)
  REFERENCES DELIVERYPOINT (DELIVERYPOINT_ID);
