create or replace procedure P_ShiftDate (str_weeks in varchar2, move_budget in number)
is

stmt2 varchar2(3000);
days number;
c_null_date varchar2(255) := '01.01.70'; -- big bang date in python

BEGIN
   days := to_number(str_weeks) * 7;     -- convert weeks in days
   if days > 0 then
     -- select all columns with data type DATE for updating values
     FOR cur IN ( select c.TABLE_NAME as table_name, c.COLUMN_NAME as column_name
                  from cols c, dt415 dt
                  where 1 = 1
                  and DATA_TYPE = 'DATE'
                  and c.TABLE_NAME = dt.DI000216                             -- only planta tables
                  and ( dt.di000266 in ('Q5B')                               -- only tables that belong to Q5B
                        or dt.DI000216 in ('DT511','DT516','DT525','DT526')  -- or relevant tables from other schemas
                      )
                  and dt.DI000216 not in ('DT418','DT430','DT468')           -- but not calendar tables from Q5B
                 )
     LOOP
        BEGIN
           -- create update statement for each column in cursor cur
           stmt2 := 'update ' || cur.table_name || ' set ' || cur.column_name || ' = ' || cur.column_name || ' + ' || days || ' where ' ||
                     cur.column_name || ' is not null and ' || cur.column_name || ' > to_date(''' || c_null_date || ''', ''' || 'DD.MM.RR' ||''')';
           EXECUTE IMMEDIATE stmt2;
           EXCEPTION
              WHEN OTHERS THEN NULL;
        END;
     END LOOP;
   end if;
   
   -- move cost and budget per year data by one year
   if move_budget = 1 then
     -- move project years
     EXECUTE IMMEDIATE 'alter table "DT282" disable  constraint "FKDT282ID861"';
     update dt861 set DI023062 = DI023062+1, UUID=SYS_GUID();
     update dt282 set DI060472 = DI060472+1, UUID=SYS_GUID();
     EXECUTE IMMEDIATE 'alter table "DT282" enable  constraint "FKDT282ID861"';
     -- move project report years
     EXECUTE IMMEDIATE 'alter table "DT285" disable constraint "FKDT285ID835"';
     update dt835 set DI029231 = DI029231+1, UUID=SYS_GUID();
     update dt285 set DI060388 = DI060388+1, UUID=SYS_GUID();
     EXECUTE IMMEDIATE 'alter table "DT285" enable constraint "FKDT285ID835"';
     -- move portfolio years
     EXECUTE IMMEDIATE 'alter table DT283 disable  constraint "FKDT283ID859"';
     update dt859 set DI023053 = DI023053+1, UUID=SYS_GUID();
     update dt283 set DI060345 = DI060345+1, DI060429=DI060429+1, UUID=SYS_GUID();
     update dt286 set DI061326 = DI061326+1, UUID=SYS_GUID();
     EXECUTE IMMEDIATE 'alter table DT283 enable constraint "FKDT283ID859"';
     -- move portfolio report years
     EXECUTE IMMEDIATE 'alter table DT092 disable constraint "FKDT092ID091"';
     update dt091 set period_id = period_id+1, UUID=SYS_GUID();
     update dt092 set period_id = period_id+1, UUID=SYS_GUID();
     EXECUTE IMMEDIATE 'alter table DT092 enable constraint "FKDT092ID091"';

   end if;

   commit;
END;