--THIS FILE IS NEEDED ONLY FOR THE ONLINE UPDATE OR WHEN UPGRADING FROM VERSION 8.9.4.5!!!

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