-- ABS-38403/ ABS-38404

create or replace procedure updatescsdist is
  -- variable declaration
  lv_new_seqnumber number := 0;
  lv_old_seqnumber number := 0;
  lv_errormessage varchar2(500);
  -- cursors
  cursor get_stockcountschedules is
    select sc.stockcountschedule_id, max(scd.countsequence) max_sequence,
           min(sc.code) sccode, min(c.customernumber) customernumber
      from stockcountschedule sc, stockcountscheduledist scd, customer c
     where scd.stockcountschedule_id = sc.stockcountschedule_id
       and c.customer_id = sc.customer_id
     group by sc.stockcountschedule_id;
    
  cursor get_scd_lines1(lv_stockcountschedule_id number) is
    select scd1.*, cp.distributionpointnumber, dp.departmentnumber
      from stockcountscheduledist scd1, distributionpoint cp, department dp
     where cp.distributionpoint_id = scd1.distributionpoint_id
       and dp.department_id = cp.department_id
       and scd1.stockcountschedule_id = lv_stockcountschedule_id
     order by scd1.countsequence;

  cursor get_scd_lines2(lv_stockcountschedule_id number) is
    select scd2.stockcountscheduledist_id, scd2.countsequence, cp.distributionpointnumber, dp.departmentnumber
      from stockcountscheduledist scd2, distributionpoint cp, department dp
     where cp.distributionpoint_id = scd2.distributionpoint_id
       and dp.department_id = cp.department_id
       and scd2.stockcountschedule_id = lv_stockcountschedule_id
     order by scd2.countsequence;

begin
  -- Fill initial message
  lv_errormessage := '';
  -- Loop to select stockcount schedules
  for sc_rec in get_stockcountschedules loop
    begin
      -- Renumbering starts with maximum existing sequence number to avoid index violation
      lv_new_seqnumber := sc_rec.max_sequence;
      -- Loop to select lines per stockcount schedule
      for scd1_rec in get_scd_lines1(sc_rec.stockcountschedule_id) loop
        begin
          -- Increase count sequence number
          lv_new_seqnumber := lv_new_seqnumber + 1;
          -- Actually update line number
          update stockcountscheduledist scd3
             set scd3.countsequence = lv_new_seqnumber
           where scd3.stockcountscheduledist_id = scd1_rec.stockcountscheduledist_id;
        exception
          when others then
            null;
            lv_errormessage := 'Renumber failed for stock count schedule '||sc_rec.sccode;
            lv_errormessage := lv_errormessage ||', department '||scd1_rec.departmentnumber;
            lv_errormessage := lv_errormessage ||', cons. point '||scd1_rec.distributionpointnumber;
            lv_errormessage := lv_errormessage ||', old sequence'||scd1_rec.countsequence;
            lv_errormessage := lv_errormessage ||', new sequence'||lv_new_seqnumber;
            lv_errormessage := lv_errormessage ||' due to ';
            dbms_output.put_line(lv_errormessage || sqlerrm);
        end;
      end loop;
      -- Now renumber the lines once more, starting with 0
      lv_new_seqnumber := 0;
      -- Loop to select lines per stockcount schedule
      for scd2_rec in get_scd_lines2(sc_rec.stockcountschedule_id) loop
        begin
          -- Increase count sequence number
          lv_new_seqnumber := lv_new_seqnumber + 1;
           -- Actually update line number
          update stockcountscheduledist scd4
             set scd4.countsequence = lv_new_seqnumber
           where scd4.stockcountscheduledist_id = scd2_rec.stockcountscheduledist_id;
        exception
          when others then
            null;
            lv_errormessage := 'Renumber failed for stock count schedule '||sc_rec.sccode;
            lv_errormessage := lv_errormessage ||', department '||scd2_rec.departmentnumber;
            lv_errormessage := lv_errormessage ||', cons. point '||scd2_rec.distributionpointnumber;
            lv_errormessage := lv_errormessage ||', old sequence'||scd2_rec.countsequence;
            lv_errormessage := lv_errormessage ||', new sequence'||lv_new_seqnumber;
            lv_errormessage := lv_errormessage ||' due to ';
            dbms_output.put_line(lv_errormessage || sqlerrm);
        end;
      end loop; -- inner loop
    exception
      when no_data_found then
        null;
        lv_errormessage := 'Renumber failed for stock count schedule '||sc_rec.sccode;
        lv_errormessage := lv_errormessage ||', customer '||sc_rec.customernumber;
        lv_errormessage := lv_errormessage ||' due to ';
        dbms_output.put_line(lv_errormessage || sqlerrm);
    end;
  end loop; -- outer loop
  commit;
end updatescsdist;
/

execute updatescsdist;

drop procedure updatescsdist;