-- ABS-72249 / ABS-74316

-------------------- DELIVERYLOAD -------------------- 
CREATE SEQUENCE SEQ_DELIVERYLOAD
START WITH 1
INCREMENT BY 1;

CREATE TABLE DELIVERYLOAD
( DELIVERYLOAD_ID             NUMBER NOT NULL,
  BUSINESSUNIT_ID             NUMBER NOT NULL,
  DELIVERYPOINT_ID            NUMBER NOT NULL,
  DELIVERYDATE                DATE NOT NULL,
  SEQUENCENUMBER              NUMBER DEFAULT 1 NOT NULL,
  PLANTDEPARTMENT_ID          NUMBER NULL,
  CODE                        VARCHAR2(40) NOT NULL,
  STATUS                      NUMBER NOT NULL 
                              CONSTRAINT STATUS_VALID9 CHECK (STATUS IN (1,2,3,4)),
  CREATIONTIMESTAMP           DATE DEFAULT SYSDATE NOT NULL,
  INITIALIZATIONTIMESTAMP     DATE NULL,
  INITIALIZATIONSYSTEMUSER_ID NUMBER NULL,
  OUTSCANSTARTTIMESTAMP       DATE NULL,
  OUTSCANSTARTSYSTEMUSER_ID   NUMBER NULL,
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL);
  
COMMENT ON COLUMN DELIVERYLOAD.SEQUENCENUMBER
  IS 'When the number of banks exceeds the maximum number of sort positions available, they must be spread over multiple DeliveryLoads using the SequenceNumber';
COMMENT ON COLUMN DELIVERYLOAD.CODE
  IS 'Code will be compiled: YYYYWWDDDSSCCCCCCCCCCCCXXXXXX
Where:
YYYY = Year
WW = Week number within year (determined from delivery date)
DDD = Day of week (MON, TUE etc.)
SS = SequenceNumber
CCCCCCCCCCCC = Customer Number with leading zeroes.
XXXXXX = Delivery point code with leading spaces';

COMMENT ON COLUMN DELIVERYLOAD.STATUS
  IS '1 = New
2 = Initialized
3 = Outscan started
4 = Closed';

ALTER TABLE DELIVERYLOAD
  ADD CONSTRAINT XPKDELIVERYLOAD PRIMARY KEY (DELIVERYLOAD_ID);
  
CREATE UNIQUE INDEX XAK1DELIVERYLOAD ON DELIVERYLOAD (BUSINESSUNIT_ID ASC, DELIVERYPOINT_ID ASC, DELIVERYDATE ASC, SEQUENCENUMBER ASC);
CREATE INDEX XIE1DELIVERYLOAD ON DELIVERYLOAD (BUSINESSUNIT_ID ASC, DELIVERYDATE ASC, STATUS ASC);
CREATE INDEX XIE2DELIVERYLOAD ON DELIVERYLOAD (BUSINESSUNIT_ID ASC, CODE ASC);
CREATE INDEX XIF1DELIVERYLOAD ON DELIVERYLOAD (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2DELIVERYLOAD ON DELIVERYLOAD (DELIVERYPOINT_ID ASC);
CREATE INDEX XIF3DELIVERYLOAD ON DELIVERYLOAD (PLANTDEPARTMENT_ID ASC);

ALTER TABLE DELIVERYLOAD
  ADD CONSTRAINT DELIVERYLOAD_BUSINESSUNIT FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
ALTER TABLE DELIVERYLOAD
  ADD CONSTRAINT DELIVERYLOAD_DELIVERYPOINT FOREIGN KEY (DELIVERYPOINT_ID)
  REFERENCES DELIVERYPOINT (DELIVERYPOINT_ID);
ALTER TABLE DELIVERYLOAD
  ADD CONSTRAINT DELIVERYLOAD_PLANTDEPARTMENT FOREIGN KEY (PLANTDEPARTMENT_ID)
  REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID) ON DELETE SET NULL;
  
  
-------------------- DELIVERYLOADDETAIL -------------------- 
CREATE SEQUENCE SEQ_DELIVERYLOADDETAIL
START WITH 1
INCREMENT BY 1;

CREATE TABLE DELIVERYLOADDETAIL
( DELIVERYLOADDETAIL_ID    NUMBER NOT NULL,
  DELIVERYLOAD_ID          NUMBER NOT NULL,
  WEARERINVENTORY_ID       NUMBER NOT NULL,
  SORTSTRING               VARCHAR2(40) NOT NULL,
  TODELIVERQUANTITY        NUMBER DEFAULT 1 NOT NULL,
  PERSONALIZEDAVAILABLEQTY NUMBER DEFAULT 0 NOT NULL,
  SORTPOSITION             NUMBER NOT NULL,
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL);
  

COMMENT ON COLUMN DELIVERYLOADDETAIL.SORTSTRING
  IS 'Will be filled with the Bank. Defining it as a SortString makes it more flexible to sort on other or more attributes.';
COMMENT ON COLUMN DELIVERYLOADDETAIL.TODELIVERQUANTITY
  IS 'Filled with the ChangesPerWeek';
COMMENT ON COLUMN DELIVERYLOADDETAIL.PERSONALIZEDAVAILABLEQTY
  IS 'Calculated during the initialization as the number of personalized garments that are available for delivery at that moment. Number of Pool items needed is calculated as DeliverQuantity – PersonalizedAvailableQty';
COMMENT ON COLUMN DELIVERYLOADDETAIL.SORTPOSITION
  IS 'The ''Pigeonhole'' number. Must be calculated in two steps. First step create detail records and fill the sortstring. Second step order by and group by sortstring and calculate a sequence number (starting with 1). This is stored as the SortPosition. All rows with the same SortString will get the same SortPosition.';

ALTER TABLE DELIVERYLOADDETAIL
  ADD CONSTRAINT XPKDELIVERYLOADDETAIL PRIMARY KEY (DELIVERYLOADDETAIL_ID);
  
CREATE UNIQUE INDEX XAK1DELIVERYLOADDETAIL ON DELIVERYLOADDETAIL (DELIVERYLOAD_ID ASC, WEARERINVENTORY_ID ASC);
CREATE INDEX XIE1DELIVERYLOADDETAIL ON DELIVERYLOADDETAIL (DELIVERYLOAD_ID ASC, SORTSTRING ASC);
CREATE INDEX XIE2DELIVERYLOADDETAIL ON DELIVERYLOADDETAIL (DELIVERYLOAD_ID ASC, SORTPOSITION ASC);
CREATE INDEX XIF1DELIVERYLOADDETAIL ON DELIVERYLOADDETAIL (DELIVERYLOAD_ID ASC);
CREATE INDEX XIF2DELIVERYLOADDETAIL ON DELIVERYLOADDETAIL (WEARERINVENTORY_ID ASC);


ALTER TABLE DELIVERYLOADDETAIL
  ADD CONSTRAINT DELIVERYLOADDETAIL_DELIVLOAD FOREIGN KEY (DELIVERYLOAD_ID)
  REFERENCES DELIVERYLOAD (DELIVERYLOAD_ID) ON DELETE CASCADE;
ALTER TABLE DELIVERYLOADDETAIL
  ADD CONSTRAINT DELIVERYLOADDETAIL_WEARERINV FOREIGN KEY (WEARERINVENTORY_ID)
  REFERENCES WEARERINVENTORY (WEARERINVENTORY_ID) ON DELETE CASCADE;

-------------------- DELIVERYLOADDETAILSCAN -------------------- 
CREATE SEQUENCE SEQ_DELIVERYLOADDETAILSCAN
START WITH 1 
INCREMENT BY 1;
  
CREATE TABLE DELIVERYLOADDETAILSCAN
( DELIVERYLOADDETAILSCAN_ID NUMBER NOT NULL,
  DELIVERYLOADDETAIL_ID     NUMBER NOT NULL,
  UNIQUEITEM_ID             NUMBER NOT NULL,
  SCANTYPE                  NUMBER NOT NULL 
                            CONSTRAINT SCANTYPE_VALID1 CHECK (SCANTYPE IN (0,1)),
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL);

COMMENT ON COLUMN DELIVERYLOADDETAILSCAN.SCANTYPE
  IS '0 = Personalized garment
1 = Pool item';

ALTER TABLE DELIVERYLOADDETAILSCAN
  ADD CONSTRAINT XPKDELIVERYLOADDETAILSCAN PRIMARY KEY (DELIVERYLOADDETAILSCAN_ID);

CREATE UNIQUE INDEX XAK1DELIVERYLOADDETAILSCAN ON DELIVERYLOADDETAILSCAN (DELIVERYLOADDETAIL_ID ASC, UNIQUEITEM_ID ASC);
CREATE INDEX XIE1DELIVERYLOADDETAILSCAN ON DELIVERYLOADDETAILSCAN (DELIVERYLOADDETAIL_ID ASC, SCANTYPE ASC);
CREATE INDEX XIF1DELIVERYLOADDETAILSCAN ON DELIVERYLOADDETAILSCAN (DELIVERYLOADDETAIL_ID ASC);
CREATE INDEX XIF2DELIVERYLOADDETAILSCAN ON DELIVERYLOADDETAILSCAN (UNIQUEITEM_ID ASC);


ALTER TABLE DELIVERYLOADDETAILSCAN
  ADD CONSTRAINT DELIVERYLOADDETSCAN_DLDETAIL FOREIGN KEY (DELIVERYLOADDETAIL_ID)
  REFERENCES DELIVERYLOADDETAIL (DELIVERYLOADDETAIL_ID) ON DELETE CASCADE;
ALTER TABLE DELIVERYLOADDETAILSCAN
  ADD CONSTRAINT DELIVERYLOADDETAILSCAN_UI FOREIGN KEY (UNIQUEITEM_ID)
  REFERENCES UNIQUEITEM (UNIQUEITEM_ID) ON DELETE CASCADE;