P_InvtryTurnoverStockPerDay

@AccessControl.authorizationCheck: #NOT_REQUIRED

@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass:#TRANSACTIONAL
@EndUserText.label: 'Inventory Turnover Stock Calculation'
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@DataAging.noAgingRestriction: true
// Copy of I_MATERIALSTOCK but this view filters also cancelled document items and also does not

// consider intra plant stock transfers as stock increase / decrease. It has a reduced field set too.

// This view shall be replaced with I_MATERIALSTOCK VDM View as soon as this referenced

// view is able to filter cancelled documents.

// For this reason, this view is not marked as VDM View as it is only used for Fiori.

define view entity P_InvtryTurnoverStockPerDay
  as select from           I_MaterialDocumentRecord as matdoc
  
  association [1..1] to I_Material                  as _Material                  on $projection.Material = _Material.Material
  association [1..1] to I_Plant                     as _Plant                     on $projection.Plant = _Plant.Plant
{
      // All fields have to be defined as key field and quantity/value have to be summarized on this lowest level

      // in addition to the annotation for aggregation (#SUM)

      // This is especiall required for consumption views used by users with restricted authorization to get the right results

      // (just because right now an INNER JOIN is created which may multiply the result set and hence the aggregate is wrong)

      //

      // Stock Identifier

  key matdoc.StockIdentifyingMaterial         as Material,
  key matdoc.Plant,
      // Further Stock Groups

  key matdoc.PostingDate                      as MatlDocLatestPostgDate,

      // Quantity and Value

  key matdoc.MaterialBaseUnit                 as MaterialBaseUnit,      
      @DefaultAggregation : #SUM
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      sum(matdoc.MatlCnsmpnQtyInMatlBaseUnit) as MatlCnsmpnQtyInMatlBaseUnit,

      @DefaultAggregation : #SUM
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'  
      cast(sum(
          case when matdoc.IssgOrRcvgStkIdfgPlant = ''                         //v2834623

              or matdoc.IssgOrRcvgStkIdfgPlant <> matdoc.Plant                        
              or matdoc.IssgOrRcvgStkIdfgMaterial <> matdoc.StockIdentifyingMaterial  //^2834623

           then
              case when  matdoc.MatlStkChangeQtyInBaseUnit < 0 then abs(matdoc.MatlStkChangeQtyInBaseUnit) else 0 end // do not consider intra-plant stock transfers here

              else 0 end
      )  as abap.dec(23, 3) )                     as MatlStkDecrQtyInMatlBaseUnit,

      // Associations for names and descriptions

      _Material,
      _Plant
}
// filter all storno documents

where matdoc.GoodsMovementIsCancelled      <> 'X'
  and matdoc.GoodsMovementCancellationType <> '2'
  and matdoc.GoodsMovementCancellationType <> '3'
  and  matdoc.MaterialBaseUnit             <> ' '
  and  matdoc.MaterialDocumentRecordType   <> 'AG_MDOC_CF'
  and  matdoc.InventorySpecialStockType    = ' '
group by
  matdoc.StockIdentifyingMaterial,
  matdoc.Plant,
  matdoc.PostingDate,
  matdoc.MaterialBaseUnit