create or replace procedure tempCreate_supplierdelday is

  cursor c_supplier is
    select s.supplier_id, s.defaultdeliverydays
      from supplier s
     where s.defaultsupplierdelday_id is null;

  cursor c_aspt is
    select aspt.pricetermslevel,
           aspt.suppliermodel_id,
           aspt.aggsupplierproduct_id,
           aspt.aggsupplierproductsize_id,
           aspt.deliverydays,
           aspt.supplierpriceterms_id
      from aggsupplierpriceterms aspt
     where aspt.supplierdelday_id is null;

  cursor c_asot is
    select asot.ordertermslevel,
           asot.suppliermodel_id,
           asot.aggsupplierproduct_id,
           asot.aggsupplierproductsize_id,
           asot.deliverydays,
           asot.supplierorderingterms_id
      from aggsupplierorderingterms asot
     where asot.supplierdelday_id is null;


  supplierdeldayID number;
  supplierID       number;

begin
  --- SUPPLIER
  for supp in c_supplier loop
    select seq_supplierdelday.nextval into supplierdeldayID from dual;
  
    insert into supplierdelday sdd
      (supplierdelday_id,
       supplier_id,
       deliverydays,
       systemuser_id,
       timestamp)
      select supplierdeldayID,
             supp.supplier_id,
             supp.defaultdeliverydays,
             getminsystemuser(),
             sysdate
        from dual;
  
    update supplier s
       set s.defaultsupplierdelday_id = supplierdeldayID
     where s.supplier_id = supp.supplier_id;
    commit;
  
  end loop;

  --- AGGSUPPLIERPRICETERMS

  --pricetermslevel=1
  for c in c_aspt loop
    if c.pricetermslevel = 1 then
      begin
        select sdd.supplierdelday_id
          into supplierdeldayID
          from suppliermodel sm, supplierdelday sdd
         where sm.suppliermodel_id = c.suppliermodel_id
           and sdd.supplier_id = sm.supplier_id
           and sdd.deliverydays = c.deliverydays;
      exception
        when no_data_found then
          begin
          
            select seq_supplierdelday.nextval
              into supplierdeldayID
              from dual;
              
            select sm.supplier_id
            into supplierID
            from suppliermodel sm
            where c.suppliermodel_id = sm.suppliermodel_id;
          
            insert into supplierdelday sdd
              (supplierdelday_id,
               supplier_id,
               deliverydays,
               systemuser_id,
               timestamp)
              select supplierdeldayID,
                     supplierID,
                     c.deliverydays,
                     getminsystemuser(),
                     sysdate
                from dual;
            commit;
          end;
      end;
    end if;
  
    --pricetermslevel=2
    if c.pricetermslevel = 2 then
      begin
        select sdd.supplierdelday_id
          into supplierdeldayID
          from AggSupplierProduct asp, supplierdelday sdd
         where asp.aggsupplierproduct_id = c.aggsupplierproduct_id
           and sdd.supplier_id = asp.supplier_id
           and sdd.deliverydays = c.deliverydays;
      
      exception
        when no_data_found then
          begin
            select seq_supplierdelday.nextval
              into supplierdeldayID
              from dual;
          
          select asp.supplier_id
          into supplierID
          from aggsupplierproduct asp
          where asp.aggsupplierproduct_id = c.aggsupplierproduct_id;
          
            insert into supplierdelday sdd
              (supplierdelday_id,
               supplier_id,
               deliverydays,
               systemuser_id,
               timestamp)
              select supplierdeldayID,
                     supplierID,
                     c.deliverydays,
                     getminsystemuser(),
                     sysdate
                from dual;
            commit;
          end;
      end;
    
    end if;
  
    --pricetermslevel=3
    if c.pricetermslevel = 3 then
      begin
        select sdd.supplierdelday_id
          into supplierdeldayID
          from AggSupplierProduct     asp,
               supplierdelday         sdd,
               aggsupplierproductsize asps
         where asp.aggsupplierproduct_id = asps.aggsupplierproduct_id
           and asps.aggsupplierproductsize_id = c.aggsupplierproductsize_id
           and sdd.supplier_id = asp.supplier_id
           and sdd.deliverydays = c.deliverydays;
      exception
        when no_data_found then
          begin
            select seq_supplierdelday.nextval
              into supplierdeldayID
              from dual;
              
          select asp.supplier_id
          into supplierID
          from aggsupplierproduct asp, aggsupplierproductsize asps
          where asp.aggsupplierproduct_id = asps.aggsupplierproduct_id
          and asps.aggsupplierproductsize_id = c.aggsupplierproductsize_id;   
          
            insert into supplierdelday sdd
              (supplierdelday_id,
               supplier_id,
               deliverydays,
               systemuser_id,
               timestamp)
              select supplierdeldayID,
                     supplierID,
                     c.deliverydays,
                     getminsystemuser(),
                     sysdate
                from dual;
            commit;
          end;
      end;
    end if;
  
    update aggsupplierpriceterms aspt
       set aspt.supplierdelday_id = supplierdeldayID
     where aspt.supplierpriceterms_id = c.supplierpriceterms_id;
    commit;
  
  end loop;

  ---- AGGSUPPLIERORDERINGTERMS

  --ordertermslevel=1
  for c in c_asot loop
    if c.ordertermslevel = 1 then
      begin
        select sdd.supplierdelday_id
          into supplierdeldayID
          from suppliermodel sm, supplierdelday sdd
         where sm.suppliermodel_id = c.suppliermodel_id
           and sdd.supplier_id = sm.supplier_id
           and sdd.deliverydays = c.deliverydays;
      exception
        when no_data_found then
          begin
            select seq_supplierdelday.nextval
              into supplierdeldayID
              from dual;
              
            select sm.supplier_id
            into supplierID
            from suppliermodel sm
            where c.suppliermodel_id = sm.suppliermodel_id;
          
            insert into supplierdelday sdd
              (supplierdelday_id,
               supplier_id,
               deliverydays,
               systemuser_id,
               timestamp)
              select supplierdeldayID,
                     supplierID,
                     c.deliverydays,
                     getminsystemuser(),
                     sysdate
                from dual;
            commit;
          end;
      end;
    end if;
  
    --ordertermslevel=2
    if c.ordertermslevel = 2 then
      begin
        select sdd.supplierdelday_id
          into supplierdeldayID
          from AggSupplierProduct asp, supplierdelday sdd
         where asp.aggsupplierproduct_id = c.aggsupplierproduct_id
           and sdd.supplier_id = asp.supplier_id
           and sdd.deliverydays = c.deliverydays;
      
      exception
        when no_data_found then
          begin
            select seq_supplierdelday.nextval
              into supplierdeldayID
              from dual;
              
          select asp.supplier_id
          into supplierID
          from aggsupplierproduct asp
          where asp.aggsupplierproduct_id = c.aggsupplierproduct_id;
          
            insert into supplierdelday sdd
              (supplierdelday_id,
               supplier_id,
               deliverydays,
               systemuser_id,
               timestamp)
              select supplierdeldayID,
                     supplierID,
                     c.deliverydays,
                     getminsystemuser(),
                     sysdate
                from dual;
            commit;
          end;
      end;
    end if;
  
    --ordertermslevel=3
    if c.ordertermslevel = 3 then
      begin
        select sdd.supplierdelday_id
          into supplierdeldayID
          from AggSupplierProduct     asp,
               supplierdelday         sdd,
               aggsupplierproductsize asps
         where asp.aggsupplierproduct_id = asps.aggsupplierproduct_id
           and asps.aggsupplierproductsize_id = c.aggsupplierproductsize_id
           and sdd.supplier_id = asp.supplier_id
           and sdd.deliverydays = c.deliverydays;
      exception
        when no_data_found then
          begin
            select seq_supplierdelday.nextval
              into supplierdeldayID
              from dual;
          
          select asp.supplier_id
          into supplierID
          from aggsupplierproduct asp, aggsupplierproductsize asps
          where asp.aggsupplierproduct_id = asps.aggsupplierproduct_id
          and asps.aggsupplierproductsize_id = c.aggsupplierproductsize_id;  
          
            insert into supplierdelday sdd
              (supplierdelday_id,
               supplier_id,
               deliverydays,
               systemuser_id,
               timestamp)
              select supplierdeldayID,
                     supplierID,
                     c.deliverydays,
                     getminsystemuser(),
                     sysdate
                from dual;
            commit;
          end;
      end;
    end if;
  
    update aggsupplierorderingterms asot
       set asot.supplierdelday_id = supplierdeldayID
     where asot.supplierorderingterms_id = c.supplierorderingterms_id;
    commit;
  
  end loop;
  
  exception 
   when others 
   then 
      dbms_output.put_line (sqlerrm);
      rollback;

end tempCreate_supplierdelday;
/
execute tempCreate_supplierdelday;
drop procedure tempCreate_supplierdelday;
