--ABS-109442 / ABS-118561

--- ROUTEEXTRAINFO TABLE ---
CREATE TABLE ROUTEEXTRAINFO
(
  ROUTE_ID NUMBER NOT NULL,
  MAINROUTE_ID    NUMBER NULL,
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
)
;

COMMENT ON COLUMN ROUTEEXTRAINFO.MAINROUTE_ID
  IS 'Used by COMBINEDROUTELOGISTICS module only and it contains the link to the main route where the current virtual route belongs to';

ALTER TABLE ROUTEEXTRAINFO
  ADD CONSTRAINT XPKROUTEEXTRAINFO PRIMARY KEY (ROUTE_ID);
  
CREATE INDEX XIF1ROUTEEXTRAINFO ON ROUTEEXTRAINFO (MAINROUTE_ID ASC);

ALTER TABLE ROUTEEXTRAINFO
  ADD CONSTRAINT FK_ROUTEEXINF_ROUTE FOREIGN KEY (ROUTE_ID)
  REFERENCES ROUTE (ROUTE_ID) ON DELETE CASCADE;
  
ALTER TABLE ROUTEEXTRAINFO
  ADD CONSTRAINT FK_ROUTEEXINF_MAINROUTE FOREIGN KEY (MAINROUTE_ID)
  REFERENCES ROUTE (ROUTE_ID) ON DELETE SET NULL;


--- ROUTECONTROLEXTRAINFO TABLE ---
ALTER TABLE ROUTECONTROLEXTRAINFO ADD (MAINROUTECONTROL_ID NUMBER NULL);

CREATE INDEX XIF1ROUTECONTROLEXTRAINFO  ON ROUTECONTROLEXTRAINFO  (MAINROUTECONTROL_ID ASC);

ALTER TABLE ROUTECONTROLEXTRAINFO
  ADD CONSTRAINT FK_ROUTECEXINF_ROUTEC FOREIGN KEY (MAINROUTECONTROL_ID)
  REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE SET NULL;
  
COMMENT ON COLUMN ROUTECONTROLEXTRAINFO.MAINROUTECONTROL_ID
  IS 'Used by COMBINEDROUTELOGISTICS module only and it contains the link to the main route where the current virtual route belongs to.';

  
