create or replace PROCEDURE "P_CALC_PERIODS" (c_lizenz IN varchar2, c_user IN varchar2)
IS
BEGIN

  execute immediate q'[ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.''' ]';

  -- cleanup Temp. Table DT468_TEMP
  execute immediate 'truncate table DT468_TEMP';

  update TEMP_PERIOD_CHANGES set done = 1 where done = 0;

  -- create Data in kalk_per
  insert /*append */ into DT468_TEMP 
  select  res                                 res,
          period                             period,
          max(work_p)                         work_p,
          
          case when max(work_p) = 1
          then max(factor_amount) else 0 end          factor_amount,
          
          case when max(work_p) = 1
          then max(vacation) else 0 end         holiday,
          
          case when max(work_p) = 1
          then max(absence) else 0 end    absence,
          
          case when max(work_p) = 1
          then max(value_3) else 0 end        base_load,
          
          case when max(work_p) = 1  
          then max(value_4) else 0 end        functional,
          
          case when max(work_p) = 1  
          then max(value_5) else 0 end        misc,
          
          case when max(work_p) = 1
          then max(units_period) else 0 end     units_period,
            
          case when max(work_p) = 1 and min(absence) = 0 and min(vacation) = 0
          then 
          max(units_period * factor_amount - (PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount, 
                                                                  method_3, value_3, unit_3, factor_bu_3) + 
                                                                   
                                      PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount - 
                                                           PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount, 
                                                                                       method_3, value_3, unit_3, factor_bu_3), 
                                      method_4, value_4, unit_4, factor_bu_4) + 
                                      
                                      PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount -  
                                                           PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount, 
                                                                                       method_3, value_3, unit_3, factor_bu_3) - 
                                                                                                                       PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount - 
                                                                                                                                                                           PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount, 
                                                                                                                                                                                                       method_3, value_3, unit_3, factor_bu_3), 
                                                                                                                                                   method_4, value_4, unit_4, factor_bu_4), 
                                      method_5, value_5, unit_5, factor_bu_5))) 
          else 0 end avail_cap,
            
          max(RSC)                                  RSC,
          ''                                        comments,
          
          case when max(work_p) = 1 and min(absence) = 0 and min(vacation) = 0
          then 
          max(PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount, 
                                          method_3, value_3, unit_3, factor_bu_3)) 
          else 0 end fac_3,

          case when max(work_p) = 1 and min(absence) = 0 and min(vacation) = 0
          then 
          max(PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount - 
                                                           PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount, 
                                                                                       method_3, value_3, unit_3, factor_bu_3), 
                                    method_4, value_4, unit_4, factor_bu_4)) 
          else 0 end fac_4,

          case when max(work_p) = 1 and min(absence) = 0 and min(vacation) = 0
          then 
          max(PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount -  
                                                           PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount, 
                                                                                       method_3, value_3, unit_3, factor_bu_3) - 
                                                                                                                       PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount - 
                                                                                                                                                                           PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount, 
                                                                                                                                                                                                       method_3, value_3, unit_3, factor_bu_3), 
                                                                                                                                                   method_4, value_4, unit_4, factor_bu_4), 
                                    method_5, value_5, unit_5, factor_bu_5)) 
          else 0 end fac_5      
          
  from   
         (select r.di001218                         res,
                 kal.di000997                       period,
                 nvl(kal.DI000999,0)                work_p,
                 di001234                           factor_amount,
                 
                 nvl(p.DI001340,0)                  vacation,
                 nvl(p.DI001341,0)                  absence,
                 
                 r.di000270                         rsc,
                
                 di001233 units_period,
                 di001261 method_3,
                 di001267 method_4,
                 di001273 method_5,
                 di001262 value_3,
                 di001268 value_4,
                 di001274 value_5,
                 di001259 unit_3,
                 di001265 unit_4,
                 di001271 unit_5,
                 di001260 factor_bu_3,
                 di001266 factor_bu_4,
                 di001272 factor_bu_5

          from   dt467 r
          
          inner join TEMP_PERIOD_CHANGES t on (di001218 = res_id)
          
          left outer join DT430 kal  
          on (kal.DI000475  = r.di001229 
          and kal.DI000997 >= t.startper 
          and kal.DI000997 <= t.endper)
          
          left outer join dt468 p  
          on (r.di001218 = p.DI001326 and p.DI001327 = kal.DI000997)
          
          where   t.done = 1
          
          and     t.startper  <> to_date('01.01.1970', 'DD.MM.YYYY')            -- Startperiod
          and     t.endper    <> to_date('01.01.1970', 'DD.MM.YYYY')            -- Endperiod
          
          -- ony create periods, when the ressource has flag "Ressource wird beplant" = Y 
          -- and DI002499 = 1
          
          -- ony create periods, when the ressource has flag "Verfuegbarkeit DS anlegen" = Y 
          -- and di102153 = 1
          
          -- ony create periods, when the ressource is scheduled as VGR with effort
          -- and di001218 in (select di001392 from dt466 where di001423 > 0)  
          and     kal.DI000997 >= t.startper  
          and     kal.DI000997 <= t.endper
          ) f
          
  group by f.res, f.period
  order by f.res, f.period;

  commit;

    --------------------------------------------
    -- new dt468
    MERGE INTO DT468
    USING
     (select * from DT468_TEMP
      where period is not null) q
    on (di001326  = res 
    and di001327  = period)
    
    WHEN MATCHED THEN UPDATE SET
    di001342 = base_load,
    di001343 = functional,
    di001344 = misc,
    di001339 = units_period,
    di001337 = avail_cap,
    di002454 = avail_cap
    
    WHEN NOT MATCHED THEN INSERT
    (
    di001326,                                   -- res
    di001327,                                   -- period
    di001364,                                   -- work_p
    di001338,                                   -- Fac.-Amount/Day
    di001340,                                   -- holiday
    di001341,                                   -- absence
    di001342,                                   -- Per. Factor 3
    di001343,                                   -- Per. Factor 4
    di001344,                                   -- Per. Factor 5
    di001339,                                   -- Units/Period
    di001337,                                   -- avail. cap.
    di002454,                                   -- summ. cap.
    --DI101247,                                 -- RSC
    di002544,                                   -- comments
    -- Objektschutz
    di001328,                                   -- created on
    di001329,                                   -- modified on
    di001330,                                   -- rwdu
    di001331,                                   -- created by
    di001332,                                   -- modified by
    di001333,                                   -- Own. Li
    di000598,                                   -- OSK
    di001334,                                   -- owner
    di001335,                                   -- group
    di001336,                                   -- any
    di002375,
    di002371,
    di002372   
    )
    VALUES
    (
    res,                                       -- res
    period,                                    -- period
    work_p,                                    -- work_p
    factor_amount,                             -- Fac.-Amount/Day
    holiday,                                   -- holiday
    absence,                                   -- absence
    base_load,                                 -- Per. Factor 3
    functional,                                -- Per. Factor 4
    misc,                                      -- Per. Factor 5
    units_period,                              -- Units/Period
    avail_cap,                                 -- avail. cap.
    avail_cap,                                 -- summ. Kap.
    --RSC,                                     -- RSC
    comments,                                  -- comments
    -- Objektschutz
    to_date(sysdate,'DD.MM.RRRR'),             -- created on
    to_date(sysdate,'DD.MM.RRRR'),             -- modified on
    7,                                         -- rwdu
    c_user,                                    -- created by
    c_user,                                    -- modified by
    c_lizenz,                                  -- Own.Lic.
    '',                                        -- OSK
    7,                                         -- Owner
    7,                                         -- Group
    7,                                         -- Any
    fac_3,
    fac_4,
    fac_5
    );
  
   delete from TEMP_PERIOD_CHANGES where done = 1;

  commit;
  
  -- cleanup Temp. Table DT468_TEMP
  execute immediate 'truncate table DT468_TEMP';  
  
END p_calc_periods;