Stock Aging report in SAP Business One

[Former Member]

Greetings,

I don't think this issue has been raised successfully on the forums (i checked, there's no real resolution at the moment).

The end user requires some form of stock aging that their auditors deem important but there's no such feature within SAP B1.

Their basic requirements would be:

Stock Code | Description | Date of Purchase | Current Month (qty) | 30-90 Days (qty) | 91-120 Days (qty) | 120 days and above (qty)

I'm wondering if this is even possible with the end user using multiple warehouses to stock their inventory, and some items are marked as serial items and others are marked as batch.

Any ideas?

[former_member186095 · Accepted solution]

I wonder why SAP did not provide inventory aging. In SAP B1 2005A, there is inventory audit report. It might be different with inventory aging but as a tool for stock audit for certain criteria available there, it could be helpful.

An addon from valogix that has inventory aging feature is available. Browse this link:

http://209.85.175.104/search?q=cache:EVgg8Mj8l0kJ:spain.valogix.com/pdf/Valogix_Parts_Planner_v4SE_%...inventoryaging%2BSAP&hl=id&ct=clnk&cd=8&gl=id&client=firefox-a

or this link:

http://209.85.175.104/search?q=cache:sWv0YCCf2y4J:www.valogix.com/Pdf/SAP/Valogix_Inventory_Planner_...inventoryaging%2BSAP&hl=id&ct=clnk&cd=9&gl=id&client=firefox-a

for more clear.

Rgds,

[former_member186095]

Hi,

Recently our customer asked about stock aging and then I asked it to SAP support. They said the aging inventory is not available.

So, they suggest to use valid tick box that is available in the item master data --> tab general. The customer finally developed it using SSRS.

Rgds,

[Former Member]

Any idea on how they did it via SQL Server Resolution Service then? (I'm guessing thats what SSRS is in your post)

[Former Member]

Hi,

You need write a user query for stock ageing analysis. The following query may be very usefull for you,

SELECT T0.ITEMCODE , T0.ONHAND as 'Total Qty',

CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND END '<45 Days(Qty)',

CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND*T0.AVGPRICE END '<45 Days(Value)',

CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND END '45 to 90 Days(Qty)' ,

CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND*T0.AVGPRICE END '45 to 90 Days(Value)',

CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND END '>90 Days(Qty)',

CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND*T0.AVGPRICE END '>90 Days(Value)'

FROM OITW T0 INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE

INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD

WHERE

T0.ONHAND>0

AND T0.WhsCode ='[%0]'

AND T2.ITMSGRPNAM = [%2]

Regards,

Venkatesan G.

[Former Member]

Thank you for pointing me in the right direction. Amended your query a little so the user input square brackets are there.

SELECT T0.ITEMCODE , T0.ONHAND as 'Total Qty', CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND END '<45 Days(Qty)', CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND*T0.AVGPRICE END '<45 Days(Value)', CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND END '45 to 90 Days(Qty)'  , CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND*T0.AVGPRICE END '45 to 90 Days(Value)', CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND END '>90 Days(Qty)', CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND*T0.AVGPRICE END '>90 Days(Value)'
FROM OITW T0 INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD WHERE T0.ONHAND>0 AND T0.WhsCode ='[%0]'
AND T2.ITMSGRPNAM = [%2]

Will continue to review the aging issue and maybe even update the forums with the results.

I can understand why in the query you've provided above its not really feaseable to have the Date of Purchase because its showing the stock quantity in total (which would make more sense instead of each item row by row)

Edited by: Davinder Singh on Apr 18, 2008 4:38 PM

[former_member186095]

Hi,

What I mean is using SQL server reporting services.

I know about it from our technical consultant. They develop the aging inventory using SSRS where after I check it is more complex because they use visual studio to develop it and then IE to make the user can see, modify or add something in the aging report.

Rgds,

[Former Member]

I also believe that the query provided by Venkatesan wont be so accurate because the aging is based on the 'Last Purchase Date' and if the user has purchased say, 100 units of item A on the 01/03/2007, the report will state the aging as 100 units in the specific aging (30 days) but if the user then purchases another 10 units of item A, the entire aging will show 110 units in 30 days and below.

I believe aging for stock items with serial items would be more possible as each serial item has an 'in' date.

Still reviewing if aging for all inventory items is work-able.

[former_member186095]

Hi,

I am sure that it is difficult to combine batch item and serial item in one query. It should be separated. If combined, there should be an option select item type, i.e. batch or serial.

For better query aging, would it be possible to involve OINM table ?

Rgds,

[former_member186095]

Hi,

This is a modified of Venkatesan's query :

SELECT distinct T0.ITEMCODE , T0.ONHAND as 'Total Qty',  T1.[ManBtchNum],  T1.[ManSerNum],
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND END '<45 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND*T0.AVGPRICE END '<45 Days(Value)',
CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND END '45 to 90 Days(Qty)' ,
CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND*T0.AVGPRICE END '45 to 90 Days(Value)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND END '>90 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND*T0.AVGPRICE END '>90 Days(Value)'
FROM OITW T0 INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE 
INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD left join ibt1 t3 on t3.itemcode = t0.itemcode and t3.whscode = t0.whscode
WHERE
T0.ONHAND>0
AND T0.WhsCode ='[%0]'

I have added ibt1 and distinct and joining whscode field in the ibt1 with whscode field in the oitw.

Hope it could be helpful

Rgds,

[Former Member]

I would strongly recommend if anyone wanted to age inventory to run a simple query like the one above for the serial / batch items separately (OSRI table).

The OINM (Whse Journal) table might come in handy, and I'll review how best it can be used.

[Former Member]

Jimmy, the query you've provided does help a little but it still suffers from taking the inventory total (see my previous reply)