Obtaining the closing stock on a selected date

[guruprasad_pandurangi]

Often it is a tough and tedious way to get stock on a selected date using MSEG table in ECC 6.0.

This can be made easier by using combination of MBEW / MBEWH along with minimal use of MSEG table.

 

Our sample uses a Apr to Mar fiscal year.

The stock of the material as per MB5B is 3932 KG on 15.08.2015.

We will see how to derive this using combination of the MBEW / MBEWH / MSEG.

In this method, we will take closing stock of immediate earlier month as opening stock and to this we will add the receipts and subtract the issues till the selected key date.

First we derive the fiscal year and period of the key date 15.08.2015. This is 2015/05.

 

We need to compare this with fiscal / year period in MBEW table for the material in selected plant.

We note that it is 2018 / 12 in MBEW table, which is ahead of the key date.

 

We move to MBEWH for further calculations.

We notice that there is no information available for 2015/05, but we have info available for 2015/04.

Here 2015/05 denotes 31.08.2015 and 2015/04 denotes 31.07.2015.

The stock on 31.07.2015 is 100 KG.

Additional info:

A simple thumb rule to derive stock for missing period: The stock info available in immediate above year and period is to be taken as stock for the missing period.

Here stock on 2015/05 is same as stock on 2015/07 going by above logic, i.e. 3222.5.

 

For our case however we will consider stock on 2015/04 (31.07.2015) as opening stock.

The purpose of showing the stock derivation logic is to obtain them for our chosen cases if they are not available explicitly.

We will apply following formula till 15.08.2015 as mentioned earlier.

Closing stock on 15.08.2015  =  opening stock on 31.07.2015 + All receipts till 15.08.2015 – All issues till 15.08.2015.

For receipts and issues, we will use MSEG / MKPF tables. Doing this way we fetch minimum entries from these tables, thereby improving performance.

While using MSEG table, please ensure that only valuated movement types are fetched.

 

Extracting to excel.

Debit / credit	Qty
S	10
S	10
S	18
S	10
S	10
S	5
H	-3
S	2
H	-2
S	2
H	-10
H	-10
S	3,600.00
S	60
S	10
S	19
S	1
S	90
S	10
Net	3832

 

Applying formula

Closing stock on 15.08.2015	Opening stock on 31.07.2015 + All receipts till 15.08.2015 – All issues till 15.08.2015
3932	100 + 3857 - 25

Thus we have derived the same closing stock on 15.08.2015 which we noted in MB5B earlier.

Further considerations

We selected key date of 15.08.2015 - > 2015 / 05

Suppose we have following scenarios of fiscal year / period in MBEW table:

Fiscal year / period in MBEW	Remarks
2018 / 12	The case that was explained above.
2015 / 05	Same year / period as of key date. Still need to move to MBEWH table and apply the explained logic. Looking for closing date stock of earlier month.
2015 / 04	Year / period of MBEW is less than key date, so qty (LBKUM) in MBEW itself can be taken as stock on key date.

 

Conclusion and key takeaways:

The usual method of using MSEG / MKPF  table would be to fetch all receipts and issues from the beginning date till the key date.

This will have a huge negative impact on performance.

The explained logic also makes use of MKPF / MSEG tables, but only for a very small period thus restricting the need to fetch a huge amount of data.

This way only a minimal negative impact on performance will occur.

The same logic can be used to determine stock on key date at storage location level also, by combination of MARD, MARDH and MSEG tables, which will help in development of  accurate and efficient inventory and ageing reports. 

Note: For stock on storage location level, non valuated movement types also need to be considered.