--Order 20014329

ALTER TABLE DELIVERYSCAN
	ADD (PLANNEDDELIVERYDATE DATE NULL);

CREATE INDEX XIE4DELIVERYSCAN ON DELIVERYSCAN
(BUSINESSUNIT_ID, PLANNEDDELIVERYDATE, nvl(PACKINGNOTE_ID ,0));

update deliveryscan ds
set ds.planneddeliverydate = (select rc.drivingdate from routecontrol rc where rc.routecontrol_id = ds.routecontrol_id)
where ds.routecontrol_id is not null
and (ds.packingnote_id is null 
       or
     (ds.packingnote_id is not null and ds.scantimestamp>sysdate-30 and ds.packingnote_id in
             (select pn.packingnote_id from packingnote pn
              where pn.origin = 2
              and pn.packingnote_id = ds.packingnote_id
              and pn.status_id = (select s.status_id from status s where s.statustype = 'PN' and s.code = 20)
              )
              )
      );
commit;