Attached is an example of stock aging in SAP. In this example I have used the MSEG (Document Segment) and MKPF (Material Document Header) tables to identify both the current stock position and the purchases (the In movements of stock). The final stock value can then be distributed across the most recent stock in movement records to assign a date as to when this stock was received (based on a FIFO - first in first out methodology). The MKPF table is required as there was an incomplete set of records in the BUDAT (stock-in date) field in the MSEG table in this implementation (this may be different at other sites).
The basic process is as follows:
1. identify the purchase instances (headers) from the MKPF table. This is required to get the stock-in date for each purchase instance (BUDAT).
2. Join in the purchase records (where SHKZG = 'S' to identify in movements of stock) and the unit cost of these movements by material (MATNR) and location (LGORT). The MSEG and MKPF tables join based on the MANDT, MBLNR and MJAHR fields. I have chosen to right join the MSEG table as I only require the purchase records (i.e. SHKZG = 'S').
3. Calculate the latest final stock values for each Material (MATNR) and Location (LGORT) and add this against each purchase line. This does repeat the final stock value across many lines but that is used in the next step.
4. Calculate the distribution of the current stock over the most recent preceding purchases. I have also calculated the cost of the stock based on the unit value of the final stock as calculated in step 3.
As this involves a bit of logic to look at preceeding values etc I have attached an example which shows the 4 steps in the coding that you can copy and reuse. The example is based on the QVDs as they would be generated using the SAP Connector script generator so if you have used this it should be an easy reload of this data. Note the MATNR field is renamed to make it a key in MSEG as [%MATNR_KEY]. Any clarifications or improvements please feel free to add in comments below.
