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
  merge into UNIQUEITEMROUTE UIR_UPD
  using (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 :NEW.DELIVERYPOINT_ID = nvl(WEMP.DELIVERYPOINT_ID, :NEW.DELIVERYPOINT_ID) 
         ) SEL_MERGE
  on (SEL_MERGE.UNIQUEITEM_ID = UIR_UPD.UNIQUEITEM_ID)
  when matched then
    update
       set UIR_UPD.LASTINSTOP_ID = case
                                     when (UIR_UPD.LASTINSTOP_ID is null and
                                          UIR_UPD.LASTINROUTE_ID = LV_ROUTEID and
                                          UIR_UPD.LASTINDATE = LV_DRIVINGDATE) then
                                      :NEW.STOP_ID
                                     else
                                      UIR_UPD.LASTINSTOP_ID
                                   end,
           UIR_UPD.LASTOUTSTOP_ID = case
                                      when (UIR_UPD.LASTOUTSTOP_ID is null and 
                                           UIR_UPD.LASTOUTROUTE_ID = LV_ROUTEID and
                                           UIR_UPD.LASTOUTDATE = LV_DRIVINGDATE) then
                                       :NEW.STOP_ID
                                      else
                                       UIR_UPD.LASTOUTSTOP_ID
                                    end,
           UIR_UPD.PLANNEDOUTSTOP_ID = case
                                         when (UIR_UPD.PLANNEDOUTSTOP_ID is null and
                                              UIR_UPD.PLANNEDOUTROUTE_ID = LV_ROUTEID and
                                              UIR_UPD.PLANNEDOUTDATE = LV_DRIVINGDATE) then
                                          :NEW.STOP_ID
                                         else
                                          UIR_UPD.PLANNEDOUTSTOP_ID
                                       end;  
end after_insert_stop;
/
