--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_yetb_displorder as
  cursor allbookings is
    select ybom_id 
    from ybom
    order by ybom_id;
  rec_allbookings allbookings%ROWTYPE;
  
  cursor bookingevgroups(ybomid number) is
    select yetb_id, yetb_starttime
    from yetb
    where yetb_ybom_id=ybomid
    order by yetb_starttime, yetb_id;
  rec_bookingevgroups bookingevgroups%ROWTYPE;

  displorder number;   
  chgdate date;
begin
  open allbookings;
  loop
    fetch allbookings into rec_allbookings;
    exit when allbookings%NOTFOUND;
   
    open bookingevgroups(rec_allbookings.ybom_id);
    displorder:=1; chgdate:=null;
    loop
      fetch bookingevgroups into rec_bookingevgroups;
      exit when bookingevgroups%NOTFOUND;
      
      if (chgdate is null) or (chgdate<>trunc(rec_bookingevgroups.yetb_starttime)) then
        displorder:=1;
        chgdate:=trunc(rec_bookingevgroups.yetb_starttime);
      end if;
      
      update yetb set yetb_displorder=displorder where yetb_id=rec_bookingevgroups.yetb_id;
      displorder:=displorder+1;
    end loop;
    close bookingevgroups;
    
  end loop;
  close allbookings;
  
  EXCEPTION
    when OTHERS then
      DBMS_OUTPUT.PUT_LINE('Exception in execution');
end;
/
