--Order 20015534

ALTER TABLE PACKINGNOTE ADD (DELIVERYSIGNATURE_ID NUMBER NULL);

CREATE INDEX XIF958PACKINGNOTE ON PACKINGNOTE (DELIVERYSIGNATURE_ID);

ALTER TABLE PACKINGNOTE
  ADD CONSTRAINT PACKINGNOTE_DELIVERYSIGNATURE FOREIGN KEY (DELIVERYSIGNATURE_ID)
  REFERENCES DELIVERYSIGNATURE (STOP_ID) ON DELETE SET NULL;


create or replace procedure update_deliverysignature is
  nr_of_rows_merged number;
begin
  nr_of_rows_merged  := -1;
while nr_of_rows_merged  <> 0 loop
  merge into packingnote p
  using
  (select p.packingnote_id,
          p.stop_id deliverysignature_id
     from packingnote p,
          deliverysignature ds
    where ds.stop_id = p.stop_id
      and p.deliverysignature_id is null and rownum <= 50000) merge_query  -- nr of rows per commit
  on (p.packingnote_id=merge_query.packingnote_id)
  when matched then
   update
      set p.deliverysignature_id = merge_query.deliverysignature_id;
  nr_of_rows_merged := SQL%ROWCOUNT;
  COMMIT;
end loop;
end update_deliverysignature;
/

execute update_deliverysignature;