One commonly requested report is to show the age of stock in your warehouse. I've found that the easiest way to get this information is through SAP Business Information Warehouse (BW). This is the best way when batch management is not implemented or batch sizes are large and multiple in and out movements occur for a batch.

Here's why: The stock age calculation is load-intensive because it happens at the lowest level and is a back calculation. BW is meant for this type of reporting, while R/3 could bog down on the calculation. With BW, you can also change the age buckets and, with some formula variables, leave it to the user to decide which age buckets to use.

I will show you how to calculate the stock age based on first in, first out (FIFO) logic. The calculation is done using the Boolean operators available in the BW Query Designer. No changes to the data model of the InfoCube are necessary.

This query works at the level at which material movements take place. If batch management is implemented, then the lowest level is plant/material/batch. Without batch management, the lowest level is plant/material. With this query, you can trace a material back based on the material documents. If a material document changes the identity of the material in any way — for example, a material-to- material transfer — then this query won't work for that material as the documents cannot be traced back.

Because this query is based on holding time, you can use it for all industries. If the location is a producing plant, then the holding location is a factory and it starts from the goods receipt after production. If the holding location is a warehouse, then it is based on the goods receipt at the warehouse, which in effect becomes holding time.

Because it relies on holding time, this query does not always produce precise results, but it provides a general idea of stock age. Without batch management, this query is probably the best way to get stock age

Build the Query

I will build a sample stock age query based on InfoCube 0IC_C03. You can adapt the logic to other InfoCubes. The age of the stock is divided into buckets of fewer than 15 days, 15 to 30 days, 31 to 60 days, 61 to 120 days, 121 to 180 days, and more than 180 days. The basic steps in the calculation are:

1. Find the closing stock on the base date.

2. Calculate the receipts in the first bucket (fewer than 15 days). Stock issues are not required to be taken into account as the calculating principle is FIFO.

3. Subtract these receipts from the closing stock. If the closing stock is more than the receipts, display the receipts in the last 15 days. Otherwise, display the closing stock.

4. Calculate the stock that is unaccounted for at the end of the bucket.

5. Find the receipts for the next bucket.

6. If the value in step 4 is positive, then subtract the receipts from the value. Otherwise, display zero.

7. Repeat steps 4 to 6 for each bucket.

8. In the last bucket, display all the stock that is open.

Now let's set up the query in BW. Go to Query Designer through the BEx Analyzer and click on the new query icon.

From the InfoCube selection screen, select the Material Movements InfoCube (0IC_C03) by following the menu path Supply Chain Management>Inventory Management> Material Movements (as of 3.0B), as Figure 1 shows. Drag Material and Plant into the Rows area (Figure 2). Then drag Valuated stock qty (0VALSTCKQTY), which represents the stock on the selected date, into the Columns area (Figure 3).

Figure 1

Select the Material Movements InfoCube

Figure 2

Drag Material and Plant into the Rows area

Figure 3

Drag Valuated stock qty into the Columns area

This is the starting point for all calculations. Right-click and select Edit. In the Edit Selection screen, add the restrictions for Valuated stock qty shown in Figure 4. The variable Key Date of the Query (Single-Value Entry, Required) forms the base date for the following calculations and is available in the variable entry screen.

Figure 4

Valuated stock qty on base date (step 1)

Receipts <15 Days

This is the material received in the last 14 days. Drag RecQtyValStck (0RECVALSTCK) to the Columns area. Add a restriction based on Calendar day. After dragging Calendar day to the restriction screen as in the previous step, right-click on the characteristic Calendar day and select Restrict (Figure 5). On the next screen, select Value Range in the selection drop-down menu and Between in the second drop-down box (Figure 6). Move the variable Key date of the query (Single-Value Entry, Mandatory Field), 0P_CSDAT to the right side. Then right-click on the variable in the box on the right and select Specify Variable Offsets.

This brings up two windows that look like Figure 7. Click on OK without changing the first window and type in -14 in the second one. Press OK again to go back to the main Query Designer screen. Now, Right-click on the key figure Receipts < 15 Days and select Properties (Figure 8). Check the Hide box next to the Display drop-down menu (Figure 9).

Figure 5

Select Restrict for the RecQtyValStck key figure

Figure 6

Begin setting the value range for the RecQtyValStck key figure

Figure 7

Enter -14 in the second of these windows

Figure 8

Select Properties for the key figure

Figure 9

Make sure the Hide box is checked off

Repeat this process for the Receipts 15-30 Days, Receipts 31-60 Days, Receipts 61-120 Days, and Receipts 121-180 Days calculations, entering the appropriate values for the time periods.

<15 Days

This represents material received in the last 14 days and in stock. The calculation checks if the quantity received is more or less than the quantity in stock. To carry out this calculation, right-click on Key Figures in the Columns area and select New Formula (Figure 10).

Drag the Key Figures to make the formula (Figure 11). Figure 12 shows the actual formula, which is equivalent to the following logic:

If (('Valuated stock qty' - 'Receipts < 15 Days') >=
0 )and ('Receipts < 15 Days' >=0) then
    Result = 'Receipts < 15 Days'
Else
    Result = 'Valuated Stock Quantity'
End If

Figure 10

Select New Formula to begin the <15 days="">15>

Formula for <15 Days

Calculation: ((('Valuated stock qty' - 'Receipts
<15 Days') >= 0) AND ('Receipts <15 Days' >= 0))
* 'Receipts <15 Days' + NOT ((('Valuated stock
qty' - 'Receipts <15 Days') >= 0) AND ('Receipts
<15 Days' >= 0)) * 'Valuated stock qty'

Formula for 15-30 Days

Calculation: ((('End of 14th Day' - 'Receipts 15-
30 Days') >= 0) AND ('Receipts 15-30 Days' >=
0)) * 'Receipts 15-30 Days' + NOT ((('End of
14th Day' - 'Receipts 15-30 Days') >= 0) AND
('Receipts 15-30 Days' >= 0)) * 'End of 14th Day'

Formula for 31-60 Days

Calculation: ((('End of 30th Day' - 'Receipts 31-
60 Days') >= 0) AND ('Receipts 31-60 Days' >=
0)) * 'Receipts 31-60 Days' + NOT ((('End of
30th Day' - 'Receipts 31-60 Days') >= 0) AND
('Receipts 31-60 Days' >= 0)) * 'End of 30th
Day'

Formula for 61-120 Days

Calculation: ((('End of 60th Day' - 'Receipts 61-
120 Days') >= 0) AND ('Receipts 61-120 Days' >=
0)) * 'Receipts 61-120 Days' + NOT ((('End of
60th Day' - 'Receipts 61-120 Days') >= 0) AND
('Receipts 61-120 Days' >= 0)) * 'End of 60th
Day'

Formula for 121-180 Days

Calculation: ((('End of 120th Day' - 'Receipts
121-180 Days') >= 0) AND ('Receipts 121-180
Days' >= 0)) * 'Receipts 121-180 Days' + NOT
((('End of 120th Day' - 'Receipts 121-180 Days')
>= 0) AND ('Receipts 121-180 Days' >= 0)) * 'End
of 120th Day'

In the case of the BEx Boolean operators, the operator returns a value of 1 if the condition is true and 0 if it is false. In the above example, if the receipts in the last 15 days are more than the valuated stock quantity, then all of it must have been received in the last 15 days. However, if the stock is more than the receipts in the last 15 days, then all the receipts in the last 15 days are still available in stock.

Repeat this process for the 15-30 Days, 31-60 Days, 61-120 Days, and 121-180 Days calculations, entering the appropriate values in the Boolean formula as Figure 12 shows.

End of 14th Day

This is the unaccounted-for stock at the end of 15 days and forms the basis for all further calculations. The result of this calculated key figure is the quantity not accounted for in step 3 ('Valuated stock qty' - '<15 Days'). It is used as the base for the next bucket calculation, just as the key figure Valuated stock qty was used for steps 2 and 3. If all the stock was received in the last 15 days, this becomes zero and there is nothing further to calculate. This is built into the subsequent key figures. This formula should also be hidden the same way Receipts < 15 Days was done.

Enter the appropriate values in the same equation for End of 30th Day, End of 60th Day, and End of 120th Day.

>180 Days

This last calculation is a little different from the earlier ones. Any open stock at the end of the 180th day has to have been received after 180 days, and there is no need to check this. The open quantity is just posted. The formula ('End of 120th Day' - 'Receipts 121-180 Days') is equivalent to the calculation of the stock at the end of the Nth day:

(('End of 120th Day' - '121-180 Days') >= 0) * ('End of 
120th Day' 
        - '121-180 Days') + (('End of 120th Day' - '121-180 Days') <= 0) * 
        0

This is equivalent to the following logic:

If (('End of 120th Day' - 'Receipts 121-180 Days')
>= 0 )then
    Result = ('End of 120th Day' - 'Receipts 121-180
Days')
Else (if ('End of 120th Day' - 'Receipts 121-180
Days') <= 0)
    Result = 0
End If

The final query should look like the one in Figure 13. Note the report format on the bottom right of the screen.

Figure 13

Final stock age query and report format