create
PROCEDURE p_calc_periods 
    @c_lizenz varchar, 
    @c_user varchar
AS
  -- cleanup Temp. Table DT468_TEMP
  truncate table DT468_TEMP;

  update TEMP_PERIOD_CHANGES set done = 1 where done = 0;

  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 - (dbo.PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount, 
                                                                  method_3, value_3, unit_3, factor_bu_3) + 
                                                                   
                                      dbo.PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount - 
                                                           dbo.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) + 
                                      
                                      dbo.PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount -  
                                                           dbo.PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount, 
                                                                                       method_3, value_3, unit_3, factor_bu_3) - 
                                                                                                                       dbo.PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount - 
                                                                                                                                                                           dbo.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(dbo.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(dbo.PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount - 
                                                           dbo.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(dbo.PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount -  
                                                           dbo.PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount, 
                                                                                       method_3, value_3, unit_3, factor_bu_3) - 
                                                                                                                       dbo.PLANTA_F_CALC_PERIOD_FACTOR(units_period * factor_amount - 
                                                                                                                                                                           dbo.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,
                 ISNULL(kal.DI000999,0)                work_p,
                 di001234                           factor_amount,

                 ISNULL(p.DI001340,0)                  vacation,
                 ISNULL(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  <> CONVERT(datetime, '01.01.1970', 104)           -- Startperiod
          and     t.endper    <> CONVERT(datetime, '01.01.1970', 104)            -- Endperiod

          -- ony create periods, when the ressource has flag "Ressource wird beplant" = Y 
          -- and DI002499 = 1
          
          -- ony create periods, when the ressource has flag "Verfügbarkeit 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;

    --------------------------------------------
    -- new dt468
    MERGE INTO DT468
    USING
     (select * from DT468_TEMP
      where periode is not null) q
    on (di001326  = res 
    and di001327  = periode)

    WHEN MATCHED THEN UPDATE SET
    di001342 = grundlast,
    di001343 = operativ,
    di001344 = sonstiges,
    di001339 = einh_tag,
    di001337 = verf_kap,
    di002454 = verf_kap

    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. Lic.
    di000598,                                   -- OSK
    di001334,                                   -- owner
    di001335,                                   -- group
    di001336,                                   -- any
    di002375,
    di002371,
    di002372   
    )
    VALUES
    (
    res,                                       -- res
    periode,                                    -- period
    arbeit,                                    -- work_p
    fak_menge,                             -- Fac.-Amount/Day
    ferien,                                   -- holiday
    abwesenheit,                                   -- absence
    grundlast,                                 -- Per. Factor 3
    operativ,                                -- Per. Factor 4
    sonstiges,                                      -- Per. Factor 5
    einh_tag,                              -- Units/Period
    verf_kap,                                 -- avail. cap.
    verf_kap,                                 -- summ. Kap.
    --RSC,                                     -- RSC
    kommentar,                                   -- comment
    -- Objektschutz
    CONVERT(datetime, GetDate(), 104),         -- created on
    CONVERT(datetime, GetDate(), 104),         -- modified on
    7,                                         -- rwdu
    @c_user,                                   -- created by
    @c_user,                                   -- modified by
    @c_lizenz,                                 -- Own.Lic.
    '',                                        -- OSK
    7,                                         -- Owner
    7,                                         -- Group
    7,                                         -- Any
    fak_3,
    fak_4,
    fak_5
    );

   delete from TEMP_PERIOD_CHANGES where done = 1;

  -- cleanup Temp. Table DT468_TEMP
  truncate table DT468_TEMP;



