-- ABS-28834

---IPOLINESDOLINE table---
CREATE SEQUENCE SEQ_IPOLINESDOLINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE IPOLINESDOLINE
(
  IPOLINESDOLINE_ID    NUMBER NOT NULL,
  PURCHASEORDERLINE_ID NUMBER NOT NULL,
  WORKORDERLINE_ID     NUMBER NOT NULL,
  SYSTEMUSER_ID        NUMBER NOT NULL,
  TIMESTAMP            DATE NOT NULL
)
;

ALTER TABLE IPOLINESDOLINE
  ADD CONSTRAINT XPKIPOLINESDOLINE PRIMARY KEY (IPOLINESDOLINE_ID);

CREATE UNIQUE INDEX XAK1IPOLINESDOLINE ON IPOLINESDOLINE (PURCHASEORDERLINE_ID ASC, WORKORDERLINE_ID ASC);

CREATE INDEX XIF1IPOLINESDOLINE ON IPOLINESDOLINE (PURCHASEORDERLINE_ID ASC);

CREATE INDEX XIF2IPOLINESDOLINE ON IPOLINESDOLINE (WORKORDERLINE_ID ASC);

ALTER TABLE IPOLINESDOLINE
  ADD CONSTRAINT IPOLINESDOLINE_POL FOREIGN KEY (PURCHASEORDERLINE_ID)
  REFERENCES PURCHASEORDERLINE (PURCHASEORDERLINE_ID) ON DELETE CASCADE;

ALTER TABLE IPOLINESDOLINE
  ADD CONSTRAINT IPOLINESDOLINE_WOL FOREIGN KEY (WORKORDERLINE_ID)
  REFERENCES WORKORDERLINE (WORKORDERLINE_ID) ON DELETE CASCADE;

---SDORDERLINEDETAIL table---
CREATE SEQUENCE SEQ_SDORDERLINEDETAIL
START WITH 1
INCREMENT BY 1;

CREATE TABLE SDORDERLINEDETAIL
(
  SDORDERLINEDETAIL_ID NUMBER NOT NULL,
  WORKORDERLINE_ID     NUMBER NOT NULL,
  CUSTOMER_LINK_ID     NUMBER NOT NULL,
  CUSTOMEROWNED        VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD289
                       CHECK (CUSTOMEROWNED IN ('Y','N')),
  QUANTITYORDERED      NUMBER DEFAULT 0 NOT NULL,
  QUANTITYCANCELLED    NUMBER DEFAULT 0 NOT NULL,
  ORIGIN               NUMBER DEFAULT 0 NOT NULL CONSTRAINT ORIGIN_VALID8_1
                       CHECK (ORIGIN IN (0, 1)),
  PREWASH              NUMBER DEFAULT 0 NOT NULL CONSTRAINT PREWASH_VALID1_3
                       CHECK (PREWASH IN (0, 1)),
  SYSTEMUSER_ID        NUMBER NOT NULL,
  TIMESTAMP            DATE NOT NULL
)
;

COMMENT ON COLUMN SDORDERLINEDETAIL.CUSTOMEROWNED
  IS 'Indicates if item to be ordered will become customer owned or not when receiving it.';

COMMENT ON COLUMN SDORDERLINEDETAIL.ORIGIN
  IS 'Indicates if detail line was created manually or generated based upon one or more purchase requisition. Possible values:
0 =Generated
1 =Manual
';

COMMENT ON COLUMN SDORDERLINEDETAIL.PREWASH
  IS '0 - No prewash needed
1 - Prewash needed
';

ALTER TABLE SDORDERLINEDETAIL
  ADD CONSTRAINT XPKSDORDERLINEDETAIL PRIMARY KEY (SDORDERLINEDETAIL_ID);

CREATE UNIQUE INDEX XAK1SDORDERLINEDETAIL ON SDORDERLINEDETAIL (WORKORDERLINE_ID ASC, CUSTOMER_LINK_ID ASC, CUSTOMEROWNED ASC, ORIGIN ASC, PREWASH ASC);

CREATE INDEX XIF1SDORDERLINEDETAIL ON SDORDERLINEDETAIL (WORKORDERLINE_ID ASC);

CREATE INDEX XIF2SDORDERLINEDETAIL ON SDORDERLINEDETAIL (CUSTOMER_LINK_ID ASC);

ALTER TABLE SDORDERLINEDETAIL
  ADD CONSTRAINT SDORDERLINEDETAIL_WOL FOREIGN KEY (WORKORDERLINE_ID)
  REFERENCES WORKORDERLINE (WORKORDERLINE_ID) ON DELETE CASCADE;
  
ALTER TABLE SDORDERLINEDETAIL
  ADD CONSTRAINT SDORDERLINEDETAIL_CUSTLINK FOREIGN KEY (CUSTOMER_LINK_ID)
  REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);

ALTER TABLE SDORDERLINEDETAIL
  ADD CONSTRAINT SDORDERLINEDETAIL_CHECK1
  CHECK ((CUSTOMEROWNED = 'Y' AND CUSTOMER_LINK_ID IS NOT NULL) OR (CUSTOMEROWNED = 'N'));


---WORKORDER table---
ALTER TABLE WORKORDER ADD (SHIPTO NUMBER DEFAULT 0 NOT NULL CONSTRAINT SHIPTO_VALID2_1
                                  CHECK (SHIPTO BETWEEN 0 AND 3),
                           SHIPTOSTOCKROOM_ID NUMBER NULL,
                           DELIVERYPOINT_LINK_ID NUMBER NULL);
 
COMMENT ON COLUMN WORKORDER.SHIPTO
  IS 'Indicates if items when ordered at third party should be shipped to a different address than the ordering (processing) stockroom. Possible values:
0 = Ordering Stockroom
1 = Other Stockroom
2 = Ordering customer
3 = Specific delivery address
';

COMMENT ON COLUMN WORKORDER.SHIPTOSTOCKROOM_ID
  IS 'Will only be filled when items ordered at third party need to be shipped to another stockroom than the ordering (processing) stockroom';

COMMENT ON COLUMN WORKORDER.DELIVERYPOINT_LINK_ID
  IS 'Will only be filled in case items on the work order are intended to be shipped directly to the customer on a known delivery address as defined for a delivery point (ShipTo = 2).';

CREATE INDEX XIF33WORKORDER ON WORKORDER (SHIPTOSTOCKROOM_ID ASC);

CREATE INDEX XIF34WORKORDER ON WORKORDER (DELIVERYPOINT_LINK_ID ASC);

ALTER TABLE WORKORDER
  ADD CONSTRAINT WORKORDER_SHIPTOSTOCKROOM FOREIGN KEY (SHIPTOSTOCKROOM_ID)
  REFERENCES STOCKROOM (STOCKROOM_ID) ON DELETE SET NULL;
  
ALTER TABLE WORKORDER
  ADD CONSTRAINT WORKORDER_DELPOINT_LINK FOREIGN KEY (DELIVERYPOINT_LINK_ID)
  REFERENCES DELIVERYPOINT_LINK (DELIVERYPOINT_LINK_ID) ON DELETE SET NULL;


---WORKORDERSHIPPINGINFO TABLE---
CREATE TABLE WORKORDERSHIPPINGINFO
(
  WORKORDER_ID  NUMBER NOT NULL,
  NAME          VARCHAR2(30) NOT NULL,
  EXTRANAME1    VARCHAR2(30) NULL,
  EXTRANAME2    VARCHAR2(30) NULL,
  CONTACTPERSON VARCHAR2(30) NULL,
  STREET1       VARCHAR2(30) NOT NULL,
  STREET2       VARCHAR2(30) NULL,
  ZIPCODE       VARCHAR2(30) NOT NULL,
  CITY          VARCHAR2(30) NOT NULL,
  COUNTY        VARCHAR2(30) NULL,
  STATE         VARCHAR2(20) NULL,
  COUNTRY_ID    NUMBER NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP     DATE NOT NULL
)
;

ALTER TABLE WORKORDERSHIPPINGINFO
  ADD CONSTRAINT WORKORDERSHIPPINGINFO PRIMARY KEY (WORKORDER_ID);
  
CREATE INDEX XIF1WORKORDERSHIPPINGINFO ON WORKORDERSHIPPINGINFO (COUNTRY_ID);

ALTER TABLE WORKORDERSHIPPINGINFO
  ADD CONSTRAINT WORKORDERSHIPPINGINFO_WO FOREIGN KEY (WORKORDER_ID)
  REFERENCES WORKORDER (WORKORDER_ID) ON DELETE CASCADE;
  
ALTER TABLE WORKORDERSHIPPINGINFO
  ADD CONSTRAINT WORKORDERSHIPPINGINFO_COUNTRY FOREIGN KEY (COUNTRY_ID)
  REFERENCES COUNTRY (COUNTRY_ID);
