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;
/

create or replace function apply_custom_daylight_saving(inputdate DATE) return DATE as
/* Customised function to calculate correctly dailight saving time differences for ics calendar files.
   Function is used in the ics base views.
   Function is set standard on the dailight saving times UTC+1 for time zone Amsterdam,Berlin,Stockholm,Rome,Brussels etc.  
        DSTup := next_day(last_day(to_date('01/03/'||to_char(yearofdate) ,'dd/mm/yyyy')) - 7,'sunday') + 2 * 60 / 1440; Date and time summertime is applied
        DSTdown := next_day(last_day(to_date('01/10/'||to_char(yearofdate) ,'dd/mm/yyyy')) - 7,'sunday') + 3 * 60 / 1440;   Date and time wintertime is applied.
  Above Statement return the date and time of dailight saving change. EG DSTup, . We take the first day of the month when summertime takes place, in our time zone 01 of March.
  We look up the last day of this month for the given year substract 7 days and look up the next Sunday (time= 00:00). Then we add 2 hours as time switch happens the Sunday morning 
  at 02:00 am.
 In case your region has a different timezone with different dates for Summer and Winter time you need to adjust DSTup and Dstdown accordingly.        
In case you region is not affected by day light saving times, inputdate stays the same , so  change the function as follows:
 yearofdate NUMBER;
  DSTup DATE; 
  DSTdown DATE;
  flaginputdate NUMBER;
  flagcurrentdate NUMBER;
  DSThours NUMBER;
begin  
  return inputdate; 
end;
*/
  yearofdate NUMBER;
  DSTup DATE; 
  DSTdown DATE;
  flaginputdate NUMBER;
  flagcurrentdate NUMBER;
  DSThours NUMBER;
begin
  yearofdate := to_number(to_char(inputdate, 'YYYY'));
  DSTup := next_day(last_day(to_date('01/03/'||to_char(yearofdate) ,'dd/mm/yyyy')) - 7,'sunday') + 2 * 60 / 1440;
  DSTdown := next_day(last_day(to_date('01/10/'||to_char(yearofdate) ,'dd/mm/yyyy')) - 7,'sunday') + 3 * 60 / 1440;
  flaginputdate := -1;
  if (DSTup<=inputdate) and (inputdate<=DSTdown) then
    flaginputdate := 1;
  end if;
  --do the same for current date
  yearofdate := to_number(to_char(sysdate, 'YYYY'));
  DSTup := next_day(last_day(to_date('01/03/'||to_char(yearofdate) ,'dd/mm/yyyy')) - 7,'sunday') + 2 * 60 / 1440;
  DSTdown := next_day(last_day(to_date('01/10/'||to_char(yearofdate) ,'dd/mm/yyyy')) - 7,'sunday') + 3 * 60 / 1440;
  flagcurrentdate := -1;
  if (DSTup<=sysdate) and (sysdate<=DSTdown) then
    flagcurrentdate := 1;
  end if;
  
  DSThours := 0;
  if flaginputdate <> flagcurrentdate then
    DSThours := 1;
    if flaginputdate = 1 then
      Dsthours := - 1;
    end if;
  end if;
  
  return inputdate + DSThours /24;
end;
/

CREATE OR REPLACE PROCEDURE V8_UPD_zcnv_constraint IS
  sql_text VARCHAR2(200);
BEGIN
   FOR cnt IN (select owner, table_name, constraint_name from user_cons_columns where owner = SYS_CONTEXT('USERENV','SESSION_USER') and table_name = 'ZCNV' and column_name = 'ZCNV_SHORTDESC')
   LOOP
      sql_text:='ALTER TABLE '||cnt.owner||'.'||cnt.table_name||' DROP CONSTRAINT '||cnt.constraint_name;
      BEGIN
        EXECUTE IMMEDIATE sql_text;
        EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
      END;
   END LOOP;
END V8_UPD_zcnv_constraint;
/
