Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Dynamic Stock Calculation in SAP Datasphere: Handling Non-Cumulative Stock Movements | S/4 IM

Sebastian_Gesiarz
Active Participant
4 weeks ago
982

converted_image.png

Ever wondered how to set up a dynamic solution for inventory snapshots—whether you’re looking at cumulative or non-cumulative stock? Curious about how this is done in S/4HANA’s VDM? If so, you’ve landed on the right post.

Let’s talk about what you need from three different example sources:

  • ECC:

    • You can grab your initial stock from MARD (the BX extractor).
    • MSEG is where you’ll find your delta movements (BF extractor).

  • BW Inbound Layer:

    • Your stock movements should be in an inbound ADSO before heading to the non-cumulative cube.
    • Missing the initial snapshot? No worries—just filter a BW cube query by date, publish it to HANA, where it will use the L-script into a Calculation View that SAP Datasphere can read.

  • S/4HANA:

You'll need to set up a harmonization layer that keeps track of two views:

  • Initial Stock State (loaded once and stays put)
  • Delta Movements (continually grows)

Once these views are in place, you can generate daily stock numbers—both cumulative and non-cumulative - for any time frame you want.

To achieve this, the S/4 logic of the Projection View - P_MaterialStockTimeSeries1 comes as an inspiration. It is the core view calculating the stock in S/4 HANA for any of the upper applications. It consists of three elements:

  1. I_MaterialStockPeriodsSingle
    1. Provides a range of dates, so that they can be joined to. Imagine this is just a table with a few dates, so if you specify the dates below, you will get the result as a table with all the days in between the provided range. The granularity can be changed by using a PERIODTYPE parameter. It accepts Quarters, Years, and Weeks. 
      Sebastian_Gesiarz_3-1739713888415.png

 

SELECT 
   I_MATERIALSTOCKPERIODSSINGLE~PERIODTYPE, 
   I_MATERIALSTOCKPERIODSSINGLE~FISCALYEARVARIANT, 
   I_MATERIALSTOCKPERIODSSINGLE~STARTDATE, 
   I_MATERIALSTOCKPERIODSSINGLE~ENDDATE, 
   I_MATERIALSTOCKPERIODSSINGLE~YEARPERIOD
 FROM 
  I_MATERIALSTOCKPERIODSSINGLE( P_STARTDATE = '20210610' , 
   P_ENDDATE = '20211010', 
   P_PERIODTYPE = 'D' )​

 

Sebastian_Gesiarz_2-1739712812647.png

  • P_MaterialStock_Aggr
    1. It provides an aggregated view of the stock, representing the issued and received stock (delta running) at a given time range. It takes all the changes to the material from the start to the end date.
      DateChangeAggregated Change Output
      01.01.2021(+) 33
      02.01.2021(-) 5-2
      03.01.2021(+) 108
  • P_MaterialStockByKeyDate1
    1. This view provides an opening balance for the first date of the range provided by the user. It aggregates the initial stock and all the movements until the one specified by the start date. Then this value is repeated over all the dates within the range specified by the user. 
      DateStock Value at 01.01.2021
      01.01.2021221
      02.01.2021221
      03.01.2021221

When those three components are used together in the code below, you can expect the following result:

DateChangeAggregated Change OutputStock Value at 01.01.2021Result
01.01.2021(+) 33221224
02.01.2021(-) 5-2221219
03.01.2021(+) 108221229

 

@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'PMATSTCKTIMESER1'

@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck:#NOT_REQUIRED
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass:#TRANSACTIONAL
@ClientHandling.algorithm: #SESSION_VARIABLE

@VDM.private: true
@VDM.viewType: #COMPOSITE

// The client field in the two JOIN conditions below is used as a pseudo condition
// because there is no real common field in both JOINed views
// but fields from both views are required in each result row
// (client can be used even if it is not declared in the DDL of the views itself but the generated SE11 view has a client field)


// Having the fields for PeriodType and PostingDates in the ON condition of the JOIN with the type and date parameter
// is quite faster than having the PeriodType and PostingDates in the WHERE clause

define view P_MaterialStockTimeSeries1
  with parameters
    P_StartDate  : vdm_v_start_date,
    P_EndDate    : vdm_v_end_date,
    P_PeriodType : nsdm_period_type
  as select from    I_MaterialStockPeriodsSingle(P_StartDate:  :P_StartDate,
                 P_EndDate:    :P_EndDate,
                 P_PeriodType: :P_PeriodType ) as a
    left outer join P_MaterialStock_Aggr       as b on  a.mandt                  =  b.mandt
                                                    and a.PeriodType             =  :P_PeriodType
                                                    and b.MatlDocLatestPostgDate >= :P_StartDate
                                                    and b.MatlDocLatestPostgDate <= a.EndDate
{
      // Period
  key a.PeriodType,
  key a.StartDate,
  key a.EndDate,
  key a.YearPeriod,
      // Stock Identifier
  key b.Material,
  key b.Plant,
  key b.StorageLocation,
  key b.Batch,
  key b.Supplier,
  key b.SDDocument,
  key b.SDDocumentItem,
  key b.WBSElementInternalID,
  key b.Customer,
  key b.InventoryStockType,
  key b.InventorySpecialStockType,
  key b.CostEstimate,    
      // Units
  key b.MaterialBaseUnit,
      // Stock Groups
  key b.CompanyCode,
  key a.FiscalYearVariant,
  key b.Currency,
      // Quantity and Value
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      sum(b.MatlWrhsStkQtyInMatlBaseUnit) as MatlWrhsStkQtyInMatlBaseUnit,
      
            //Consumption
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      sum( case when MatlDocLatestPostgDate < StartDate 
        then
         0
        else
         MatlCnsmpnQtyInMatlBaseUnit
        end   
      ) as MatlCnsmpnQtyInMatlBaseUnit,
      
      //Sum for Average
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      cast(sum( 
        case when MatlDocLatestPostgDate < StartDate 
        then
         cast( b.MatlWrhsStkQtyInMatlBaseUnit as abap.dec(25,3)) *  (dats_days_between(StartDate, EndDate)+1) 
        else
         cast( b.MatlWrhsStkQtyInMatlBaseUnit as abap.dec(25,3)) *  (dats_days_between(MatlDocLatestPostgDate, EndDate)+1) 
        end   
      ) as abap.dec(25,3)) as MatlStkQtySumInPeriod
}
group by
  a.PeriodType,
  a.StartDate,
  a.EndDate,
  a.YearPeriod,
  b.Material,
  b.Plant,
  b.StorageLocation,
  b.Batch,
  b.Supplier,
  b.SDDocument,
  b.SDDocumentItem,
  b.WBSElementInternalID,
  b.Customer,
  b.InventoryStockType,
  b.InventorySpecialStockType,
  b.CostEstimate,
  b.MaterialBaseUnit,
  b.CompanyCode,
  a.FiscalYearVariant,
  b.Currency

union all

// Total Stock before StartDate Parameter
select from       I_MaterialStockPeriodsSingle(P_StartDate:  :P_StartDate,
            P_EndDate:    :P_EndDate,
            P_PeriodType: :P_PeriodType )                             as a
  left outer join P_MaterialStockByKeyDate1(P_KeyDate:  :P_StartDate) as b on  a.mandt      = b.mandt
                                                                           and a.PeriodType = :P_PeriodType
{
      // Period
  key a.PeriodType,
  key a.StartDate,
  key a.EndDate,
  key a.YearPeriod,
      // Stock Identifier
  key b.Material,
  key b.Plant,
  key b.StorageLocation,
  key b.Batch,
  key b.Supplier,
  key b.SDDocument,
  key b.SDDocumentItem,
  key b.WBSElementInternalID,
  key b.Customer,
  key b.InventoryStockType,
  key b.InventorySpecialStockType,
  key b.CostEstimate,
      // Units
  key b.MaterialBaseUnit,
      // Stock Groups
  key b.CompanyCode,
  key a.FiscalYearVariant,
  key b.Currency,
      // Values
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      sum(b.MatlWrhsStkQtyInMatlBaseUnit)   as MatlWrhsStkQtyInMatlBaseUnit, // there is only one row per Date
      0 as MatlCnsmpnQtyInMatlBaseUnit,
      cast( MatlWrhsStkQtyInMatlBaseUnit as abap.dec(25,3)) *  (dats_days_between(StartDate, EndDate)+1) as MatlStkQtySumInPeriod
}
group by
  a.PeriodType,
  a.StartDate,
  a.EndDate,
  a.YearPeriod,
  b.Material,
  b.Plant,
  b.StorageLocation,
  b.Batch,
  b.Supplier,
  b.SDDocument,
  b.SDDocumentItem,
  b.WBSElementInternalID,
  b.Customer,
  b.InventoryStockType,
  b.InventorySpecialStockType,
  b.CostEstimate,
  b.MaterialBaseUnit,
  b.CompanyCode,
  a.FiscalYearVariant,
  b.Currency,
  MatlWrhsStkQtyInMatlBaseUnit

 

Now, to apply the same solution in the DSP view, one will need the Initial Stock State, Delta movements, and A dynamic view providing the date ranges. An example of the view implementation for the range of the dates with Day granularity, in the same view other granularities should be added:

 

    select
        'D'                                           as PeriodType,
        ' '                                           as FiscalYearVariant,
        concat(cd.YEAR, DAYOFYEAR(cd.DATE_SQL))       as YearPeriod,
        min(cd.DATE_SQL)                              as StartDate,
        max(cd.DATE_SQL)                              as EndDate
    from  
        "SAP.TIME.M_TIME_DIMENSION" as cd    
    where
          cd.DATE_SQL >= :IP_DATEFROM
      and cd.DATE_SQL <= :IP_DATETO
    group by
      cd.YEAR,
      DAYOFYEAR(cd.DATE_SQL)

 

Once those views are available, the following code can be used to dynamically create the non-cumulative stock snapshot for any date range:

  1. Just like in I_MaterialStockPeriodsSingle, select the date range and put it in the internal table:

 

lt_time_ser = 
    SELECT
        "PeriodType",
        "FiscalYearVariant",
        "YearPeriod",
        "StartDate",
        "EndDate"
    FROM
        <...>"(
            IP_DATEFROM => :IP_DATEFROM,
            IP_DATETO => :IP_DATETO,
            IP_PERIODTYPE => :IP_PERIODTYPE,
            IP_FISCALVARIANT => :IP_FISCALVARIANT
        )​

 

  • Just like in P_MaterialStock_Aggr, select the movements:

 

lt_movements = 
    SELECT
        a."PeriodType",
        a."FiscalYearVariant",
        a."YearPeriod",
        a."StartDate",
        a."EndDate",
        b.MATERIAL,
        b.PLANT,
        SUM(b."QTY") as "...",
        SUM(b."VAL") as "...",
        ...
    FROM 
        :lt_time_series AS a
    LEFT JOIN 
        ViewWithMovementsAndInit AS b ON(
            a."PeriodType"          =  :IP_PERIODTYPE
            AND b.PostingDate       >= :IP_DATEFROM
            AND b.PostingDate       <= a."EndDate"
        )
    GROUP BY
      (...)
;​

 

  • Just like in P_MaterialStockByKeyDate1, select the initial stock at the beginning of the range for each of the dates:

 

lt_opening_balance = 
    SELECT
        a."PeriodType",
        a."FiscalYearVariant",
        a."YearPeriod",
        a."StartDate",
        a."EndDate",
        b.MATERIAL,
        b.PLANT,
    	SUM(b."QTY") as "QTY",
    	SUM(b."VAL") as "VAL",
        (...)
    FROM 
        :lt_time_series AS a
    LEFT JOIN 
        ViewWithMovementsAndInit(
            IP_KEYDATE => ADD_DAYS(:IP_DATEFROM,-1) 
        ) AS b ON( 
            a."PeriodType" = :IP_PERIODTYPE
        )
    GROUP BY
        (...)
;​

 

If you would like to add cumulative figures, such as issued and received stock, simply select them and add them with the union at the end. 

Now that the dynamic view is available, you can create a history by using data flows which will select the data from this dynamic view and add it to the partitioned historical table. Virtual views with snapshots can be created on top of this view to support the stock changes that happened retroactively in the last few months.

You might ask, why all this hassle if the analytical model already supports the non-cumulative key figures? https://help.sap.com/docs/SAP_DATASPHERE/c8a54ee704e94e15926551293243fd1d/589908814adc4f16bb2e158794... Both for performance reasons and because the Analytical Model cannot be used as a data source like Query as an InfoProvider in BW or HANA view generated for a query with L script.

______________________________________________________________________________________

This blog explained how to build a dynamic stock snapshot by combining initial stock data with subsequent delta movements from various systems:

  • Data Sources:

    • ECC: Use tables like MARD (initial stock) and MSEG (delta movements).
    • BW Inbound Layer: Stock movements are stored in the inbound ADSO; if the initial snapshot is missing, it can be recreated via a date-filtered query on the BW cube and published to HANA.
    • S/4: Both the initial stock and subsequent changes are available in the MATDOC table, with a harmonization layer maintaining separate views for initial stock (static) and delta movements (growing).

  • S/4 Logic with Projection View:
    The core view P_MaterialStockTimeSeries1 is inspired by three elements:

    1. I_MaterialStockPeriodsSingle: Generates a continuous range of dates (with adjustable granularity).
    2. P_MaterialStock_Aggr: Aggregates issued and received stock over the period.
    3. P_MaterialStockByKeyDate1: Provides the opening balance as of the first date, repeated for the entire period.

  • Dynamic View for DSP:
    Similar logic is applied to create a dynamic view that:

    • Retrieves the date range,
    • Aggregates delta movements, and
    • Extracts the initial stock at the start of the range.
      This allows for generating non-cumulative snapshots and, if needed, adding cumulative figures through union operations.

  • Historical and Performance Considerations:
    The dynamic view can feed partitioned historical tables and virtual snapshot views, supporting retroactive stock changes. This approach is preferred over using the standard Analytical Model due to performance benefits and enhanced data source compatibility for BW or HANA query scenarios.

In essence, by harmonizing initial and delta stock data across systems and leveraging dynamic views, you can flexibly and efficiently compute daily stock snapshots for various reporting and analytical needs.

Labels in this area
Popular Blog Posts