--ABS-159007

--CONTAINERTYPE TABLE
ALTER TABLE CONTAINERTYPE ADD (TRANSPORTARTICLE_ID NUMBER NULL,
                               EXCLUDEFROMTRUCKCAPACALC VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CONTAINERTYPE_EXCLUDEFROMTRUCK
                                                                                         CHECK (EXCLUDEFROMTRUCKCAPACALC IN ('Y', 'N')));

CREATE INDEX XIF1CONTAINERTYPE ON CONTAINERTYPE (TRANSPORTARTICLE_ID);

ALTER TABLE CONTAINERTYPE
  ADD CONSTRAINT FK_CONTAINERTYPE_TRANSPORTARTICLE FOREIGN KEY (TRANSPORTARTICLE_ID)
      REFERENCES TRANSPORTARTICLE (TRANSPORTARTICLE_ID) ON DELETE SET NULL;


--ROUTE TABLE
ALTER TABLE ROUTE ADD (QTYDEDUCTFROMTRUCKCAPA NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ROUTE_QTYDEDUCT CHECK (QTYDEDUCTFROMTRUCKCAPA BETWEEN 0 AND 99));

COMMENT ON COLUMN ROUTE.QTYDEDUCTFROMTRUCKCAPA IS 'The number of containers that are deducted from the truck capacity calculation on the Truck Load Manager';

--PACKINGNOTE TABLE
ALTER TABLE PACKINGNOTE ADD (AUTOLINKTONEXTVISIT VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_PACKINGNOTE_AUTOLINK
                                                                                  CHECK (AUTOLINKTONEXTVISIT IN ('Y', 'N')));
COMMENT ON COLUMN PACKINGNOTE.AUTOLINKTONEXTVISIT IS 'Used on the Truck Load Manager. If a container / Packing note is disconnected from a route it can be set to automatically link it to the next visit for the same customer / delivery point';

--WORKSTATION TABLE
COMMENT ON COLUMN WORKSTATION.DELNOTEPRINTANDPROCESS IS '1 = print and process
                                                         2 = only process (no printing)
                                                         3 = only print (no immediate processing yet)
                                                         4 = only generate packing notes
                                                         5 = set to status Packed (packing finished but not yet delivered)
                                                         6 = set to status Packed and print delivery note';
                                                         
ALTER TABLE WORKSTATION DROP CONSTRAINT DELNOTEPRINTPROC_VALI;

ALTER TABLE WORKSTATION 
  ADD CONSTRAINT CHK_WORKSTATION_DELNOTEPRINTPROC
           CHECK (DELNOTEPRINTANDPROCESS IN (1, 2, 3, 4, 5, 6));                                                                                                                                          

--ROUTECONTROLEXTRAINFO TABLE
ALTER TABLE ROUTECONTROLEXTRAINFO ADD (NRCONTLEFTBEHINDEXCEEDINGMAX NUMBER NULL,
                                       NRCONTLEFTBEHINDBELOWMAX     NUMBER NULL,
                                       NRCONTAINERSMOVEDOUT         NUMBER NULL,
                                       NRCONTAINERSMOVEDIN          NUMBER NULL);
                                       
COMMENT ON COLUMN ROUTECONTROLEXTRAINFO.NRCONTLEFTBEHINDEXCEEDINGMAX IS 'Number of container left behind because they did not fit';
COMMENT ON COLUMN ROUTECONTROLEXTRAINFO.NRCONTLEFTBEHINDBELOWMAX IS 'Number of containers left behind although they would have fit';
COMMENT ON COLUMN ROUTECONTROLEXTRAINFO.NRCONTAINERSMOVEDOUT IS 'Number of containers that were moved out of this route (by truck load manager)';
COMMENT ON COLUMN ROUTECONTROLEXTRAINFO.NRCONTAINERSMOVEDIN IS 'Number of containers that were moved into this route (by truck load manager or truck load assistant)';
