P_InvtryTurnoverStock

@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'PINVTOSTOCK'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED

@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass:#TRANSACTIONAL
@ClientHandling.algorithm: #SESSION_VARIABLE

@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 P_InvtryTurnoverStock
  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_CompanyCode               as _CompanyCode               on $projection.CompanyCode = _CompanyCode.CompanyCode
  association [1..1] to I_Plant                     as _Plant                     on $projection.Plant = _Plant.Plant
  association [0..1] to I_Supplier                  as _Supplier                  on $projection.Supplier = _Supplier.Supplier
  association [1..1] to I_InventoryStockType        as _InventoryStockType        on $projection.InventoryStockType = _InventoryStockType.InventoryStockType
  association [1..1] to I_InventorySpecialStockType as _InventorySpecialStockType on $projection.InventorySpecialStockType = _InventorySpecialStockType.InventorySpecialStockType
  association [1..1] to I_UnitOfMeasure             as _UnitOfMeasure             on $projection.MaterialBaseUnit = _UnitOfMeasure.UnitOfMeasure
{
      // 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 StockIdentifyingMaterial         as Material,
  key Plant,
  key Supplier,
  key InventoryValuationType,
  key InventoryStockType,
  key InventorySpecialStockType,
      // Further Stock Groups

  key CompanyCode,
  key FiscalYearVariant,
  key PostingDate                      as MatlDocLatestPostgDate,
      // Units

      @Semantics.unitOfMeasure
  key MaterialBaseUnit,
  key CostEstimate,

      // Quantity and Value

      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      @DefaultAggregation : #SUM
      sum(MatlStkChangeQtyInBaseUnit)  as MatlWrhsStkQtyInMatlBaseUnit,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      @DefaultAggregation : #SUM
      sum(MatlCnsmpnQtyInMatlBaseUnit) as MatlCnsmpnQtyInMatlBaseUnit,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      @DefaultAggregation : #SUM
      sum(
          case when IssuingOrReceivingPlant = ''
              or IssuingOrReceivingPlant <> Plant
              or IssgOrRcvgMaterial <> StockIdentifyingMaterial
           then
              case when  MatlStkChangeQtyInBaseUnit > 0 then MatlStkChangeQtyInBaseUnit else 0 end // do not consider intra-plant stock transfers here

              else 0 end
      )                                as MatlStkIncrQtyInMatlBaseUnit,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      @DefaultAggregation : #SUM
      sum(
          case when IssgOrRcvgStkIdfgPlant = ''                         //v2834623

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

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

              else 0 end
      )                                as MatlStkDecrQtyInMatlBaseUnit,

      // Associations for names and descriptions

      _UnitOfMeasure,
      _Material,
      _CompanyCode,
      _Plant,
      _Supplier,
      _InventoryStockType,
      _InventorySpecialStockType
}
// filter all storno documents

where GoodsMovementIsCancelled      <> 'X' 
  and GoodsMovementCancellationType <> '2' 
  and GoodsMovementCancellationType <> '3'
  and MaterialBaseUnit              <> ''
  and MaterialDocumentRecordType    <> 'AG_MDOC_CF'
group by
  StockIdentifyingMaterial,
  Plant,
  Supplier,
  InventoryValuationType,
  InventoryStockType,
  InventorySpecialStockType,
  CompanyCode,
  FiscalYearVariant,
  PostingDate,
  MaterialBaseUnit,
  CostEstimate