CREATE OR REPLACE procedure UpdateYevmDisplayOrder as

  ybomid number;
  eventdate date;
  dorder number; 
  evorder number; 
  prevyevmid number;
  systemdate date;
  
  cursor ybomcursor(fromdate date) is
    select ybom_id
    from ybom
    where ybom_arrival >=fromdate;
  ybomrow ybomcursor%rowtype;
  
  cursor yevmdatescursor(ybomid number) is
    select distinct trunc(yevm_starttime) yevm_date
    from yevm
    where yevm_ybom_id=ybomid;
  yevmdatesrow yevmdatescursor%rowtype;  
  
  cursor yevmcursor(ybomid number, eventdate date) is
    select a.yevm_displorder, a.yevm_id, a.yevm_master_yevm_id, a.yevm_starttime, 
    (select count(1) from yevm b where b.yevm_master_yevm_id = a.yevm_id) cntsubevents, 
    b.yevm_displorder sub_yevm_displorder, b.yevm_id sub_yevm_id, b.yevm_starttime sub_yevm_starttime 
    from yevm a, (select * from yevm b where b.yevm_ybom_id=ybomid ) b 
    where a.yevm_ybom_id=ybomid and trunc(a.yevm_starttime)=eventdate  
    and nvl(a.yevm_master_yevm_id, 0) = 0 and a.yevm_id=b.yevm_master_yevm_id(+) 
    order by a.yevm_displorder, b.yevm_displorder;
  yevmrow yevmcursor%rowtype;  
    
    
begin
  select wgbs_datevalue
  into systemdate
  from fideliodate; 

  open ybomcursor(systemdate);
  loop
    fetch ybomcursor into ybomrow;
    exit when ybomcursor%notfound;
    ybomid := ybomrow.ybom_id;
    
    open yevmdatescursor(ybomid);
    loop
      fetch yevmdatescursor into yevmdatesrow;
      exit when yevmdatescursor%notfound;
    
      eventdate := yevmdatesrow.yevm_date;
      
      dorder := 1; 
      evorder := 0; 
      prevyevmid := 0;
      
      open yevmcursor(ybomid, eventdate);
      loop
        fetch yevmcursor into yevmrow;
        exit when yevmcursor%notfound;
        
        if (prevyevmid <> yevmrow.yevm_id)  then  --only once for each master event
          if (dorder <> yevmrow.yevm_displorder) then 
            update yevm set yevm_displorder=dorder where yevm_id=yevmrow.yevm_id;
          end if;  
          dorder := dorder + 1;
        end if;  
        
        if (yevmrow.sub_yevm_id > 0) then --it is a subevent
          if dorder <> yevmrow.sub_yevm_displorder then
            update yevm set yevm_displorder=dorder where yevm_id=yevmrow.sub_yevm_id;
          end if;  
          dorder := dorder + 1;
        end if;  
        
        prevyevmid := yevmrow.yevm_id;
  
      end loop;
      close yevmcursor;
      
    end loop;
    close yevmdatescursor;  
  
  end loop;
  close ybomcursor;
  
end;
/
