CREATE OR REPLACE procedure v8_upd_ccm_fix_bi as
cursor ybomcursor is
select ybom_id, ybom_zfac_id,
  (select count(1) from ybis where ybis_ybom_id=ybom_id and nvl(ybis_from_zfac_id, 0)=0 ) BisOfBooking, --exclude bi's shared
  (select count(1) from ybis where ybis_from_zfac_id=ybom_zfac_id and nvl(ybis_ybom_id, 0)=0 ) BisOfFa --exclude bi's shared
from ybom
where   trunc(sysdate) <=ybom_departure --1 way of considering what it is an today booking + future bookings
  and nvl(ybom_zfac_id, 0) > 0; --booking that has an FA linked
ybomrow ybomcursor%rowtype;

differences number;

begin
  open ybomcursor;
  loop
    fetch ybomcursor into ybomrow;
    exit when ybomcursor%notfound;
    if (ybomrow.bisofbooking = 0) and (ybomrow.bisoffa>0) then --link bi's of FA to ybom 
      update ybis set ybis_ybom_id=ybomrow.ybom_id
      where ybis_from_zfac_id=ybomrow.ybom_zfac_id;
    end if;
    if (ybomrow.bisofbooking > 0) and (ybomrow.bisoffa=0) then --link bi's of ybom to FA
      update ybis set ybis_from_zfac_id=ybomrow.ybom_zfac_id
      where ybis_ybom_id=ybomrow.ybom_id;
    end if;
    
    if (ybomrow.bisofbooking > 0) and (ybomrow.bisofbooking=ybomrow.bisoffa) then --equal as numbers need to check the dept codes

      select count(1) --count if differences in dept code structure of bi's
      into differences
      from
      (
        select v.ybid_zdco_id 
        ,(select ybis_ybom_id from ybis, ybid where ybis_id=ybid_ybis_id and ybis_ybom_id=ybomrow.ybom_id and ybid_zdco_id=v.ybid_zdco_id) ybom_id
        ,(select ybis_from_zfac_id from ybis, ybid where ybis_id=ybid_ybis_id and ybis_from_zfac_id=ybomrow.ybom_zfac_id and ybid_zdco_id=v.ybid_zdco_id) zfac_id
        from (
        select ybid_zdco_id from ybid, ybis where ybis_ybom_id=ybomrow.ybom_id and nvl(ybis_from_zfac_id, 0)=0 and ybis_id=ybid_ybis_id --exclude shared bi's
        union 
        select ybid_zdco_id from ybid, ybis where ybis_from_zfac_id=ybomrow.ybom_zfac_id and nvl(ybis_ybom_id, 0)=0 and ybis_id=ybid_ybis_id --exclude shared bi's
        ) v
      );
      
      if differences = 0 then --can do a merge of those subsets
       --delete ybid of bi's of booking that are not linked to FA
       delete from ybid
       where ybid_ybis_id in
         (select ybis_id from ybis where ybis_ybom_id=ybomrow.ybom_id and nvl(ybis_from_zfac_id, 0)=0 );
       --delete bi's of booking that are not linked to FA
       delete from ybis
       where ybis_ybom_id=ybomrow.ybom_id and nvl(ybis_from_zfac_id, 0)=0;
       --update bi's of FA with ybom_id
       update ybis
       set ybis_ybom_id=ybomrow.ybom_id
       where ybis_from_zfac_id=ybomrow.ybom_zfac_id and nvl(ybis_ybom_id, 0)=0;
      end if;
      
    end if;
  end loop;
  close ybomcursor;
  exception
    when no_data_found then 
      null;  
end;
/
