C_MaterialDailyDemand

@Metadata.ignorePropagatedAnnotations: true
@Consumption.dbHints: ['NO_CS_JOIN']
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Material Daily Demand'
@VDM.viewType: #CONSUMPTION
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass:#TRANSACTIONAL


define view entity C_MaterialDailyDemand
  as select distinct from I_StockLevelProposal as _DemandDrivenProduct
    left outer join       I_MatlAvgDailyUsge1  as _AvgDailyUsage on  _DemandDrivenProduct.Material = _AvgDailyUsage.Material
                                                                 and _DemandDrivenProduct.Plant    = _AvgDailyUsage.Plant
                                                                 and _DemandDrivenProduct.MRPArea  = _AvgDailyUsage.MRPArea
  association [1..*] to P_TimeDependentStockBuffer as _ActualValues   on  $projection.Material = _ActualValues.Material
                                                                      and $projection.Plant    = _ActualValues.Plant
                                                                      and $projection.MRPArea  = _ActualValues.MRPArea
  association [1..1] to I_ProdBufLvlPrpslData      as _ProposalHeader on  $projection.Material              = _ProposalHeader.Material
                                                                      and $projection.Plant                 = _ProposalHeader.Plant
                                                                      and $projection.MRPArea               = _ProposalHeader.MRPArea
                                                                      and _ProposalHeader.ProposalEndDate   = '99991231'
                                                                      and _ProposalHeader.ProposalStartDate <= $session.system_date

{
  key _DemandDrivenProduct.Material,
  key _DemandDrivenProduct.Plant,
  key _DemandDrivenProduct.MRPArea,
  key _AvgDailyUsage.PostingDate,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      _ActualValues.CurrentAvgDailyUsage as CurrentAverageDailyUsage,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      case when (_AvgDailyUsage.PostingDate < _ProposalHeader.ProposalStartDate )
      then
      cast(0 as abap.dec( 13, 3 ))
      end                                as PlndIndepRqmtQuantity,
      @Semantics.quantity.unitOfMeasure: 'MATERIALBASEUNIT' 
case when (_AvgDailyUsage.PostingDate < _ProposalHeader.ProposalStartDate )
      then
      sum(_AvgDailyUsage.QuantityInBaseUnit)
      end                                as QuantityInBaseUnit,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      cast(0 as abap.dec( 13, 3 ))       as DailyPlndIndepRqmtQuantity,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      cast(0 as abap.dec( 13, 3 ))       as WeeklyPlndIndepRqmtQtyPerDay,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      cast(0 as abap.dec( 13, 3 ))       as MonthlyPlndIndepRqmtQtyPerDay,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      cast(0 as abap.dec(13, 3))         as TotalWeeklyPlndIndepRqmtQty,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      cast(0 as abap.dec(13, 3))         as TotalMonthlyPlndIndepRqmtQty,
      _AvgDailyUsage.MaterialBaseUnit
}
where
          _DemandDrivenProduct.ProposalEndDate            =  '99991231'
  and     _AvgDailyUsage.PostingDate <  _ProposalHeader.ProposalStartDate
  and(
    (     // Don't consider 543 if MRP Area Category = Plant (Customer incident: 500499/2020)

          _AvgDailyUsage.MRPAreaCategory            =  '01'
      and _AvgDailyUsage.GoodsMovementType          <> '543'
    )
    or    _AvgDailyUsage.MRPAreaCategory            =  '02'
    or    _AvgDailyUsage.MRPAreaCategory            =  '03'
  )
group by
  _DemandDrivenProduct.Material,
  _AvgDailyUsage.PostingDate,
  _DemandDrivenProduct.MRPArea,
  _AvgDailyUsage.MaterialBaseUnit,
  _DemandDrivenProduct.Plant,
  _ActualValues.BufferLevelStartDate,
  _ActualValues.CurrentAvgDailyUsage,
  _ProposalHeader.ProposalStartDate

union select from P_DailyWeeklyMnthlyPIRHistDate as _PIRHist
association [1..1] to I_ProdBufLvlPrpslData as _ProposalHeader on  _PIRHist.Material                 = _ProposalHeader.Material
                                                               and _PIRHist.ProductionPlant          = _ProposalHeader.Plant
                                                               and _PIRHist.MRPArea                  = _ProposalHeader.MRPArea
                                                               and _ProposalHeader.ProposalEndDate   = '99991231'
                                                               and _ProposalHeader.ProposalStartDate <= $session.system_date
{
  key _PIRHist.Material,
  key _PIRHist.ProductionPlant           as Plant,
  key _PIRHist.MRPArea,
  key DeliveryDate                       as PostingDate,
      cast(0 as abap.dec( 13, 3 ))       as CurrentAverageDailyUsage,
      sum(_PIRHist.QuantityInBaseUnit)   as PlndIndepRqmtQuantity,
      cast(0 as abap.dec( 13, 3 ))       as QuantityInBaseUnit,
      sum(DailyPlndIndepRqmtQuantity)    as DailyPlndIndepRqmtQuantity,
      sum(WeeklyPlndIndepRqmtQtyPerDay)  as WeeklyPlndIndepRqmtQtyPerDay,
      sum(MonthlyPlndIndepRqmtQtyPerDay) as MonthlyPlndIndepRqmtQtyPerDay,
      sum(TotalWeeklyPlndIndepRqmtQty)   as TotalWeeklyPlndIndepRqmtQty,
      sum(TotalMonthlyPlndIndepRqmtQty)  as TotalMonthlyPlndIndepRqmtQty,
      _ProposalHeader._BufMatPlant._Material.BaseUnit as MaterialBaseUnit
}
where
  _PIRHist.DeliveryDate >= _ProposalHeader.ProposalStartDate
group by
  _PIRHist.Material,
  _PIRHist.MRPArea,
  _PIRHist.ProductionPlant,
  _PIRHist.DeliveryDate,
  _ProposalHeader._BufMatPlant._Material.BaseUnit