CREATE OR REPLACE procedure V8_CCM_update_yevm_displorder as

  cursor allbookings is
    select ybom_id 
    from ybom
    order by ybom_id;
  rec_allbookings allbookings%ROWTYPE;
  
  cursor bookingevents(ybomid number) is
    select yevm_id, yevm_starttime
    from yevm
    where yevm_ybom_id=ybomid
    order by nvl(yevm_master_yevm_id,0), yevm_starttime, yevm_id desc;
  rec_bookingevents bookingevents%ROWTYPE;
    
  displorder number;   
  chgdate date;
begin
  open allbookings;
  loop
    fetch allbookings into rec_allbookings;
    exit when allbookings%NOTFOUND;
   
    open bookingevents(rec_allbookings.ybom_id);
    displorder:=1; chgdate:=null;
    loop
      fetch bookingevents into rec_bookingevents;
      exit when bookingevents%NOTFOUND;
      
      if (chgdate is null) or (chgdate<>trunc(rec_bookingevents.yevm_starttime)) then
        displorder:=1;
        chgdate:=trunc(rec_bookingevents.yevm_starttime);
      end if;
      
      update yevm set yevm_displorder=displorder where yevm_id=rec_bookingevents.yevm_id;
      displorder:=displorder+1;
    end loop;
    close bookingevents;
    
  end loop;
  close allbookings;
  
  EXCEPTION
    when OTHERS then
      DBMS_OUTPUT.PUT_LINE('Exception in execution');
      
end;
/
