------------ SUPPLIERPRODUCT ------------
ALTER TABLE SUPPLIERPRODUCT ADD (SUPPLIERDELDAY_ID NUMBER NULL);

CREATE INDEX XIF3SUPPLIERPRODUCT ON SUPPLIERPRODUCT (SUPPLIERDELDAY_ID ASC);

ALTER TABLE SUPPLIERPRODUCT
  ADD CONSTRAINT SUPPLIERPRODUCT_SUPPDELDAY FOREIGN KEY (SUPPLIERDELDAY_ID)
  REFERENCES SUPPLIERDELDAY (SUPPLIERDELDAY_ID);

create or replace procedure tempCreate_supplierdelday is

  cursor c_supplierproduct is
    select sp.supplierproduct_id, sp.supplier_id, sp.deliverydays
      from supplierproduct sp
     where sp.supplierdelday_id is null;

  supplierdeldayID number;

begin

  for c in c_supplierproduct loop
    begin
      select sdd.supplierdelday_id
        into supplierdeldayID
        from supplierdelday sdd
       where c.supplier_id = sdd.supplier_id
         and c.deliverydays = sdd.deliverydays;
    
      update supplierproduct sp
         set sp.supplierdelday_id = supplierdeldayID
       where sp.supplierproduct_id = c.supplierproduct_id;
      commit;
    
    exception
      when no_data_found then
        begin
          select seq_supplierdelday.nextval
            into supplierdeldayID
            from dual;
          insert into supplierdelday sdd
            (supplierdelday_id,
             supplier_id,
             deliverydays,
             systemuser_id,
             timestamp)
            select supplierdeldayID,
                   c.supplier_id,
                   c.deliverydays,
                   getminsystemuser(),
                   sysdate
              from dual;
        
          update supplierproduct sp
             set sp.supplierdelday_id = supplierdeldayID
           where sp.supplierproduct_id = c.supplierproduct_id;
          commit;
        end;
        
        end;
    end loop;
  
  exception
when others then dbms_output.put_line(sqlerrm); rollback;

end tempCreate_supplierdelday;

/
execute tempCreate_supplierdelday;
drop procedure tempCreate_supplierdelday;