Inventory Aging Reporting with S/4HANA Embedded Analytics

Have you ever thought of adding aging dimension to S/4HANA trx.MB52 warehouse stock data? If yes, then we are on the same page – aging information helps to manage material stock more effectively surfacing processes issues. Enjoy.

Inventory Aging report default layout is similar to trx. MB52 except for Stock Type going down and Aging Information going across.

Note: above is demo data and does not represent real system data

The report provides very detailed operational information, but can be easily transformed into powerful analytical tool removing unnecessary details and aggregating data. Inventory Aging is very dynamic report and allows to:

• Change aging ranges;
• Eliminate internal flow;
• Run report for previous date.

All above can be done changing selection screen values

Changing aging ranges is straight forward. Internal flow elimination allows to exclude internal flow of material within a plant. This is done by dropping additional details like Storage Location, Special Stock, Special Stock Indicator and aggregating data to a plant level. Such a way even though material stock can be shuffled around within plant its aging information will not be reset. Date parameter can be set for any date in the past to backdate report, but it only works for Qty (Disclaimer: Value fields do not provide accurate information because material valuation data is not time dependent).

Below screenshot demonstrates Internal Flow Elimination in action

Note: above is demo data and does not represent real system data

As you can see, with Internal Flow Elimination Y overall material stock became more aged, even though some Qty moved from more aged time bucket to less aged time buckets. It is due to the fact all material stock within a plant is treated as one bucket and FIFO principle applies consuming more aged stock first.

How Inventory Aging S/4HANA Embedded Analytics report is implemented? It is BEx query built on top of CDS view cube wrapped around CDS table function. Why things made so complicated instead of plain and simple CDS views? The report requires a use of BEx query to implement complex parameters checks and create dynamic column headers, etc. Also CDS table function is used take of advantage of SQL Script window functions (not available in CDS view) for stock calculation.

Inventory Aging report derives its data from two sources:

1. MATDOC table – stock quantity

2. MBEW table – stock value

In S/4HANA MBEW table is replaced with compatibility view and later made things complicated since CDS table function can not access ABAP layer CDS view and can only access native HANA tables.

It required to port MBV_MBEW CDS view code to AMDP HANA SQL Script    

So product valuation is a replica of SAP code and there is nothing more to add. Most fun part is material stock quantity and aging calculation. It is done in 5 simple steps:

1. Inventory flow selection and internal flow elimination;
2. Inventory inflow selection;
3. Inventory outflow selection;
4. Inventory and inventory aging calculation (quantity);
5. Inventory and inventory aging calculation (value);

Inventory flow selection and internal flow elimination

Inventory inflow selection

Inventory outflow selection

Inventory and inventory aging calculation (quantity)

Inventory and inventory aging calculation (value)

CDS view table function has input parameters that influence inventory and inventory aging calculation and passed from wrapping CDS view cube

CDS view cube has also input parameters that in their turn are passed from BEx query

BEx query is defined on top CDS view cube transient provider

Single value variables defined for CDS view cube parameters

ABAP class that implements RSROA_VARIABLES_EXIT_BADI BAdi defines variables initial values and validate their values

Finally BEx query defines Inventory Aging default layout with dimensions down and measures across