Pir_Keyfigures_Read_For_Sum

@AbapCatalog.sqlViewName: 'PIR_V_RD_SUM'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #AUTOMATED
@ObjectModel.usageType: {serviceQuality: #X, sizeCategory: #L, dataClass: #MIXED}
@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'Read PIR Keyfigures'
define view Pir_Keyfigures_Read_For_Sum as 
   select from marc 
   inner join mara             on marc.matnr    = mara.matnr
   inner join sdm_aor          on sdm_aor.werks = marc.werks
                              and sdm_aor.dispo = marc.dispo
   left outer join Pir_All_Attributes as pir  
                               on pir.matnr = marc.matnr
                              and pir.werks = marc.werks
   left outer to one join Pir_Item_Calc_Att as pir_calc
                               on pir.bdzei = pir_calc.bdzei                              
   left outer to one join t141 as t141_marc on t141_marc.mmsta = marc.mmsta 
   left outer to one join t141 as t141_mara on t141_mara.mmsta = mara.mstae    
   left outer to one join t461p on t461p.strgr = marc.strgr
   left outer to one join t461s on t461s.stra1 = t461p.hptst
 {
   key sdm_aor.uname,
   key marc.matnr,
   key marc.werks,
   key coalesce(pir.berid, cast( marc.werks as berid)) as berid,
   key coalesce(pir.bedae, t461s.bedvp) as bedae,
   key coalesce(pir.versb, '00') as versb,                 -- Default for Version is '00' if not yet existing
   key coalesce(cast(pir.per_sdat as pph_per_sdat),'00010101') as per_sdat,     
       
   case when pir.released = '' or pir.plan_qty is null then '00010101'
        else pir.per_edat
   end as start_date_for_max,   -- for later determination of reach we only want the dates where we have released planned quantity
   case when ( marc.strgr = '' or mara.lvorm = 'X' or marc.lvorm = 'X' or t141_marc.dpbed = 'B' or t141_mara.dpbed = 'B' ) 
               and ( pir.plan_qty > pir.withdr_qty )
        then 'X'
        else ''
   end as forecast_is_superfluous, -- forecast open quantity should not exist if no strgr or material is deleted or not released for forecast maintainence
   case when  marc.strgr <> '' and mara.lvorm = '' and marc.lvorm = '' 
              and ( t141_marc.dpbed <> 'B' or t141_marc.dpbed is null) and ( t141_mara.dpbed <> 'B' or t141_mara.dpbed is null )
              and (pir.bedae is not null or t461s.bedvp is not null)
        then 'X'
        else ''
   end as forecast_is_needed,      -- forecast needed if strgr <> '' and released for maintenance and a requirements type is found via strategy group
   pir.bdzei,
   marc.perkz as perkz_master, 
   coalesce(pir.perkz_item,'') as perkz_item, 
   cast(pir.per_edat as pph_per_edat) as per_edat, 
   case when pir_calc.period_is_invalid <> 0 then pir_calc.period_is_invalid
        else case when marc.perkz <> 'M' and marc.perkz <> 'W' then 1
        else 0
        end
   end as period_is_invalid,
   pir.loevr, 
   pir.acc_past,
   pir.draft_qty,
   pir.plan_qty, 
   pir.withdr_qty, 
   pir.open_qty,   
   pir.meins as item_meins, 
   pir.pdatu,
   marc.lvorm as marc_lvorm,
   mara.lvorm as mara_lvorm,
   mara.meins as mara_meins,
   t141_marc.dpbed as marc_dpbed,
   t141_mara.dpbed as mara_dpbed,
   0 as lastchange,
   pir.laeda as last_change_dats,
   pir.aenam,
   marc.dispo
}