Inventory Aging in FIFO

[Former Member]

Is anyone aware of a true aged inventory report that would work in a FIFO-based SAP Business One system?

I've read older posts on the SCN about such reports, but many of these age the inventory by the last purchase date.  That approach does not yield a valid aging, since the entire quantity on hand is aged according to the last receipt.  In a FIFO environment, a new layer is created for each receipt.  The layer information includes the layer date, open quantity remaining (originally set to the receipt quantity), and cost.  The open quantity is reduced as subsequent transactions consume quantities from the layer.  Presumably, this data would allow the total quantity on hand to be accurately aged for each contributing receipt.  The total on hand for a given item might include quantities in several age brackets.

I'm taking a stab at writing a query, but it appears that an Inventory Transfer creates the layer in the destination warehouse using the transfer date, and not the actual receipt date of the quantities being moved.  To properly age a transfered quantity, it'd be necessary to locate the origin of the quantity.  If the quantity had been transferred more than once, it'd be a nightmare.

There may be other complexities that I haven't even considered yet.  Therefore, I'm hoping that someone might have accomplished something in this area since the earlier posts.  I am aware of the Valogix add-on and the great features that it offers.  However, at this point, the client is looking only for a report, and not an inventory optimization package.

Feedback would be appreciated!
Dave

[Former Member]

Hi Dave,

This report will be super complicated by query. May you ignore all inventory transfer transactions?

Thanks, Gordon

[Former Member]

Gordon,

Thanks for your input... you were right... it was complicated.

I was able to develop the query using a combination of tables centered around OITM, OIVQ, and OIVL.  Based on his testing so far, the CFO seems happy with the result.  The report shows quantity and aged inventory in 10 age brackets, ranging from under 1 week to over 1 year.

Because the central table is the FIFO layers, I was not able to exclude transfers.  A transfer from one warehouse to another consumes layers from the old and creates layers in the new.  I could not find a reasonable way to skip these transactions and retain the original info across one (or more!) warehouse transfers.

Thanks, Dave

[Former Member]

You may skip these transactions by ObjectType.

[Former Member]

Either I was not clear, or I might not understand your comment (or both).  I am aware how ObjectType reflects the document type in transactions.

I can't skip transfers, because they move costed inventory from one warehouse to another, and affect the FIFO layer tables (which are cumulative, rather than transactional).  The downside to transfers is that the original receipt date is lost.

For example, I buy 5 units of product x in Jan for $10 and another 5 units in Feb for $11, both into whs W1.  Then in March, I transfer one unit to whs W2.  Financially, a $10 unit will be moved, and the Feb FIFO layer is reduced to 4 remaining units.  The new FIFO layer created for whs W2 will have a cost of $10 and today's date.  It would have been nice if the transfer had preserved the original January receipt date.

It might be possible to backtrack to find the original receipt date for a transferred item, but it'd be fairly complex.  Especially if items had been trasferred more than once.

Dave

[Former Member]

That is why I suggest if you can skip them in the first place. If you can not skip, well, you may find not only this but more troubles to get all correct layers' info.

[Former Member]

Hello David

i created FIFO Inventory report for my client

it show all the remaining quantity as per purchase

i developed it in crystal 

[malcolm_lamour]

Hi

I'm new to SAP Business One.

Would I be out of line to request a copy of the report?

thanks

Malcolm

[malcolm_lamour]

Hi Jay

Would you consider sending me this report?

I have to do it for my company and I have no idea where to begin.

thanks

Malcolm