--ABS-166473

--drop triggers
drop trigger before_update_deliverypoint;
drop trigger before_update_address; 

--update deliverypoint 
create or replace procedure updategpsdeliverypoint is
gv_message       abslog.message%type;
begin
  for x in (select dp.deliverypoint_id
              from deliverypoint dp
              join address a
                on a.address_id = dp.deliveryaddress_id
             where round(dp.gpslongitude, 5) = round(a.longitude, 5)
               and round(dp.gpslatitude, 5) = round(a.latitude, 5)
               and dp.gpstimestamp is not null) loop
    begin
      gv_message := 'Update coordinates for deliverypoint_id ' || x.deliverypoint_id; 
      update deliverypoint dp
         set dp.gpslongitude = null,
             dp.gpslatitude  = null,
             dp.gpstimestamp = null,
             dp.timestamp    = sysdate
       where dp.deliverypoint_id = x.deliverypoint_id;
    end;
    commit;
  end loop;
 for y in (select dp.deliverypoint_id, a.address_id
             from deliverypoint dp
             full outer join address a
               on a.address_id = dp.deliveryaddress_id
            where (dp.gpslatitude = '0.0' and dp.gpslongitude = '0.0')
               or (a.longitude = '0.0' and a.latitude = '0.0')) loop
      begin  
        gv_message := 'Update coordinates for deliverypoint_id ' || y.deliverypoint_id; 
        update deliverypoint dp 
           set dp.gpslongitude = null,
               dp.gpslatitude  = null,
               dp.gpstimestamp = null,
               dp.timestamp    = sysdate
         where dp.deliverypoint_id = y.deliverypoint_id; 
       
        gv_message := 'Update coordinates for address_id ' || y.address_id; 
        update address a   
           set a.longitude = null,
               a.latitude  = null,
               a.timestamp = sysdate
         where a.address_id = y.address_id;
      end;
      commit;
   end loop; 
exception
  when others then
    begin
      abslogwrite.abslog_write(5, 'updategpsdeliverypoint', null, 3, gv_message || ' ' || sqlerrm,0);
    end;                 
end updategpsdeliverypoint;
/
execute updategpsdeliverypoint;
drop procedure updategpsdeliverypoint;
