--THIS FILE IS NEEDED ONLY FOR THE ONLINE UPDATE OR WHEN UPGRADING FROM VERSION 8.8.3.7!!!

CREATE OR REPLACE procedure V8_CCM_update_ypkd_displorder as
  cursor allbookings is
    select ybom_id 
    from ybom
    order by ybom_id;
  rec_allbookings allbookings%ROWTYPE;
  
  cursor bookingpackages(ybomid number) is
    select ypkd_id, ypkd_starttime
    from ypkd
    where ypkd_ybom_id=ybomid
    order by ypkd_starttime, ypkd_id;
  rec_bookingpackages bookingpackages%ROWTYPE;
  displorder number;   
  chgdate date;
begin
  open allbookings;
  loop
    fetch allbookings into rec_allbookings;
    exit when allbookings%NOTFOUND;
   
    open bookingpackages(rec_allbookings.ybom_id);
    displorder:=1; chgdate:=null;
    loop
      fetch bookingpackages into rec_bookingpackages;
      exit when bookingpackages%NOTFOUND;
      
      if (chgdate is null) or (chgdate<>trunc(rec_bookingpackages.ypkd_starttime)) then
        displorder:=1;
        chgdate:=trunc(rec_bookingpackages.ypkd_starttime);
      end if;
      
      update ypkd set ypkd_displorder=displorder where ypkd_id=rec_bookingpackages.ypkd_id;
      displorder:=displorder+1;
    end loop;
    close bookingpackages;
    
  end loop;
  close allbookings;
  
  EXCEPTION
    when OTHERS then
      DBMS_OUTPUT.PUT_LINE('Exception in execution');
end;
/
