P_SlowMoMatPrMoStockDelta

@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass:#TRANSACTIONAL
@VDM.private: true
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck:#NOT_REQUIRED
//@EndUserText.label: 'Stock Delta'

define view entity P_SlowMoMatPrMoStockDelta as select from P_SlowMoMatPrMoStockFloat as rec
                                     left outer join I_Product                 as prod
                                                   on prod.Product = rec.Material    
   
                                            
{

  key rec.CalendarMonth,

  key rec.Material as Material,
  key rec.Plant as Plant,
  key rec.MaterialBaseUnit,    
  
  prod.ProductGroup as ProductGroup,
  prod.ProductType  as ProductType,
  
  
  //Stock per Month 

  rec.StockQtyM1,
  rec.StockQtyM2,
  rec.StockQtyM3,
  rec.StockQtyM4,  
  rec.StockQtyM5,
  rec.StockQtyM6,
  rec.StockQtyM7,
  rec.StockQtyM7 as target, 
  
  //Stock Delta from Month to previous Month

  rec.StockDeltaM2M1,
  rec.StockDeltaM3M2,
  rec.StockDeltaM4M3,
  rec.StockDeltaM5M4,
  rec.StockDeltaM6M5,
  
  //Ratio Stock Delta Month to previous Month devided by Stock of Pervious Month

  case when rec.StockDeltaM2M1 = 0 then 0 else case when rec.StockQtyM1 = 0 
       then 0 else rec.StockDeltaM2M1 / rec.StockQtyM1 end end as StockRatioM2M1,
  case when rec.StockDeltaM3M2 = 0 then 0 else case when rec.StockQtyM2 = 0 
       then 0 else rec.StockDeltaM3M2 / rec.StockQtyM2 end end as StockRatioM3M2,
  case when rec.StockDeltaM4M3 = 0 then 0 else case when rec.StockQtyM3 = 0 
       then 0 else rec.StockDeltaM4M3 / rec.StockQtyM3 end end as StockRatioM4M3,
  case when rec.StockDeltaM5M4 = 0 then 0 else case when rec.StockQtyM4 = 0 
       then 0 else rec.StockDeltaM5M4 / rec.StockQtyM4 end end as StockRatioM5M4,
  case when rec.StockDeltaM6M5 = 0 then 0 else case when rec.StockQtyM5 = 0 
       then 0 else rec.StockDeltaM6M5 / rec.StockQtyM5 end end as StockRatioM6M5,
    
  
  //Number of BOMs used in per Month 

  rec.NumberOfBomM5,
  rec.NumberOfBomM6,
  rec.NumberOfBomM7,
  rec.NumberOfBomM8,
  rec.NumberOfBomM9,
  rec.NumberOfBomM10,  
  
  //Number of BOMs Delta from Month to previous Month

  rec.NumberOfBomDeltaM6M5,   
  rec.NumberOfBomDeltaM7M6,  
  rec.NumberOfBomDeltaM8M7,  
  rec.NumberOfBomDeltaM9M8,  
  rec.NumberOfBomDeltaM10M9,
  
  //Ratio Number of BOMs Delta Month to previous Month devided by Number of BOMs of Pervious Month 

  case when rec.NumberOfBomDeltaM6M5 = 0 then 0 else case when rec.NumberOfBomM5 = 0 
       then 0 else rec.NumberOfBomDeltaM6M5 / rec.NumberOfBomM5 end end as BomRatioM6M5,  
  case when rec.NumberOfBomDeltaM7M6 = 0 then 0 else case when rec.NumberOfBomM6 = 0 
       then 0 else rec.NumberOfBomDeltaM7M6 / rec.NumberOfBomM6 end end as BomRatioM7M6,  
  case when rec.NumberOfBomDeltaM8M7 = 0 then 0 else case when rec.NumberOfBomM7 = 0 
       then 0 else rec.NumberOfBomDeltaM8M7 / rec.NumberOfBomM7 end end as BomRatioM8M7,  
  case when rec.NumberOfBomDeltaM9M8 = 0 then 0 else case when rec.NumberOfBomM8 = 0 
       then 0 else rec.NumberOfBomDeltaM9M8 / rec.NumberOfBomM8 end end as BomRatioM9M8,  
  case when rec.NumberOfBomDeltaM10M9 = 0 then 0 else case when rec.NumberOfBomM9 = 0 
       then 0 else rec.NumberOfBomDeltaM10M9 / rec.NumberOfBomM9 end end as BomRatioM10M9,              

  // Stock Intervall as number of consecutive previous Month without Stock Change

  0 as StockIntervalM1,
  case when rec.StockDeltaM2M1 <> 0 then 0 else 1 end as StockIntervalM2,
  case when rec.StockDeltaM3M2 <> 0 then 0 else case when rec.StockDeltaM2M1 <> 0 then 1 else 2 end end as StockIntervalM3,
  case when rec.StockDeltaM4M3 <> 0 then 0 else case when rec.StockDeltaM3M2 <> 0 then 1
                                       else case when rec.StockDeltaM2M1 <> 0 then 2 else 3 end end end as StockIntervalM4,     
  case when rec.StockDeltaM5M4 <> 0 then 0 else case when rec.StockDeltaM4M3 <> 0 then 1  
                                       else case when rec.StockDeltaM3M2 <> 0 then 2
                                       else case when rec.StockDeltaM2M1 <> 0 then 3 else 4 end end end end as StockIntervalM5, 
  case when rec.StockDeltaM6M5 <> 0 then 0 else case when rec.StockDeltaM5M4 <> 0 then 1  
                                       else case when rec.StockDeltaM4M3 <> 0 then 2
                                       else case when rec.StockDeltaM3M2 <> 0 then 3
                                       else case when rec.StockDeltaM2M1 <> 0 then 4 else 5 end end end end end as StockIntervalM6                            
}