CREATE OR REPLACE TRIGGER after_insert_stop
  after insert on stop
  for each row

declare
  lv_routeid     routecontrol.route_id%type;
  lv_drivingdate routecontrol.drivingdate%type;
begin
  select rc.route_id, rc.drivingdate
    into lv_routeid, lv_drivingdate
    from routecontrol rc
   where rc.routecontrol_id = :new.routecontrol_id;
   
  --update lastest information in uniqueitemroute
  update uniqueitemroute uir
     set uir.lastinstop_id = :new.stop_id
   where uir.uniqueitem_id in
         (select uinp.uniqueitem_id
            from uniqueitemnonpool uinp,
                 weareremployment  wemp,
                 wearer            w,
                 uniqueitemroute   uir
           where uinp.weareremployment_id = wemp.weareremployment_id
             and wemp.wearer_id = w.wearer_id
             and uir.uniqueitem_id = uinp.uniqueitem_id
             and w.customer_id = :new.customer_link_id
             and (wemp.deliverypoint_id = :new.deliverypoint_id or wemp.deliverypoint_id is null)
             and (uir.lastinstop_id is null and
                 uir.lastinroute_id = lv_routeid and
                 uir.lastindate = lv_drivingdate));

  update uniqueitemroute uir
     set uir.lastoutstop_id = :new.stop_id
   where uir.uniqueitem_id in
         (select uinp.uniqueitem_id
            from uniqueitemnonpool uinp,
                 weareremployment  wemp,
                 wearer            w,
                 uniqueitemroute   uir
           where uinp.weareremployment_id = wemp.weareremployment_id
             and wemp.wearer_id = w.wearer_id
             and uir.uniqueitem_id = uinp.uniqueitem_id
             and w.customer_id = :new.customer_link_id
             and (wemp.deliverypoint_id = :new.deliverypoint_id or wemp.deliverypoint_id is null)
             and (uir.lastoutstop_id is null and
                 uir.lastoutroute_id = lv_routeid and
                 uir.lastoutdate = lv_drivingdate));

  update uniqueitemroute uir
     set uir.plannedoutstop_id = :new.stop_id
   where uir.uniqueitem_id in
         (select uinp.uniqueitem_id
            from uniqueitemnonpool uinp,
                 weareremployment  wemp,
                 wearer            w,
                 uniqueitemroute   uir
           where uinp.weareremployment_id = wemp.weareremployment_id
             and wemp.wearer_id = w.wearer_id
             and uir.uniqueitem_id = uinp.uniqueitem_id
             and w.customer_id = :new.customer_link_id
             and (wemp.deliverypoint_id = :new.deliverypoint_id or wemp.deliverypoint_id is null)
             and (uir.plannedoutstop_id is null and
                 uir.plannedoutroute_id = lv_routeid and
                 uir.plannedoutdate = lv_drivingdate));
end after_insert_stop;
/

