--Order 20014424.40

ALTER TABLE STOP
	ADD (DELIVERYPOINT_ID NUMBER NULL);

ALTER TABLE STOP
	ADD (CONTACTPERSON_ID NUMBER NULL);

ALTER TABLE STOP
	ADD (STOPTYPE NUMBER CONSTRAINT StopType_valid1_1 CHECK (STOPTYPE IN (1, 2, 3, 4)));

COMMENT ON COLUMN STOP.STOPTYPE IS '1 = Get
2 = Bring
3 = Mixed
4 = Incidental
';

CREATE INDEX XIF6STOP ON STOP
(DELIVERYPOINT_ID   ASC);

CREATE INDEX XIF7STOP ON STOP
(CONTACTPERSON_ID   ASC);

ALTER TABLE STOP
ADD CONSTRAINT STOP_DELIVERYPOINT FOREIGN KEY (DELIVERYPOINT_ID) REFERENCES DELIVERYPOINT (DELIVERYPOINT_ID) ON DELETE SET NULL;

ALTER TABLE STOP
ADD CONSTRAINT STOP_CONTACTPERSON FOREIGN KEY (CONTACTPERSON_ID) REFERENCES CONTACTPERSON (CONTACTPERSON_ID) ON DELETE SET NULL;

update stop s
set s.stoptype = 4
where s.routestop_id is null
and (select rc.drivingdate from routecontrol rc where s.routecontrol_id = rc.routecontrol_id) >= trunc(sysdate - 7);
commit;

update stop s
set s.stoptype = (select rs.stoptype from routestop rs where s.routestop_id = rs.routestop_id)
,s.deliverypoint_id = (select rs.deliverypoint_id from routestop rs where s.routestop_id = rs.routestop_id)
,s.contactperson_id = (select rs.contactperson_id from routestop rs where s.routestop_id = rs.routestop_id)
where s.routestop_id is not null
and (select rc.drivingdate from routecontrol rc where s.routecontrol_id = rc.routecontrol_id) >= trunc(sysdate - 7);
commit;