Related

FIFO on xtuple

FIFO on xtuple

Published 2013-07-30 00:00:00


It can't be that difficult can it?......

Background


As part of our implementation for Xtuple, one of the key requirements was to value the stock using FIFO rather than the methods offered by default in Xtuple. After migrating from Netsuite, this became the next major task in our implementation. It took almost a year to develop, initially the first release took around 5 months, but it was only after many rounds of fixes and tweaks that we finally got to a situation where it would correctly calculate the stock valuation and correctly apply this to the General Ledger.


What is FIFO?


In an inventory managment based system, like xtuple, you can pick a number of methods to value the stock. The primary one available in xtuple being 'standard cost', which basically sets a single value to a item of stock and uses that to calculate the value of stock on hand and also the profit (eg. the difference between stock sold and how much it cost to buy). It then uses an item cost variance account to account for differences in purchases and this value.

FIFO stands for First in / First Out, in a FIFO based stock valuation, any stock that you sell is, as closely as possible, based on the cost your really paid to acquire the product. In our version of FIFO, we also include the Freight costs to acquire the product and can include any tax paid to acquire it.

The complexity of FIFO is based around tracking the sale of a purchase based on the order it came in,

In the classic example, you ordered 2 batches of product X, the first order you bought 100 pcs at $10, then a few months later you bought 200pcs at $8. The total stock valuation would be 100x$10 + 200x$8, now as you start selling the product, you remove 50pcs (and you account that as being sold from the first batch at $10), then later you sell 100pc. part of that is valued at the $10, the rest is valued at $8

Seems quite simple, but when you are selling thousands of items, with hundreds of orders, it starts getting more than a little bit complicated to work out.

Our approach.


Xtuple is basically a postgresql database with a rather large number of stored procedures, on top of this is a Desktop GUI, and we also developed a web based GUI. both interfaces generally talk to the database via the stored procedures (and some views). There is some direct interaction with the database tables, but for all transactions this tends to be done via the stored procedures.

In our initial design we did consider modifying the stored procedures, but eventually we settled on using triggers on certain tables to add richer data, this meant that to a large degree we could keep using xtuple code, and update whenever a release came out without to many implications.

Location control.


The standard package of xtuple only supports a single warehouse, it does however support multiple locations within that warehouse, in our implementation we needed over 100 consignment locations and about 5 different warehouses. So we implemented these warehouses as xtuple locations. Locations normally in xtuple refer to locations within a warehouse. We also needed to track the value of stock in all these locations, which also affected how the fifo implementation was done.


Tracking stock related changes.


The first part of the integration was creating a new table invfifo, which tracks both the invdetail and invhist tables, a new view was created that joined all 3 tables together and was used throughout to query information on stock. The tracking was done by a trigger on the invdetail table, as soon as a record was added and posted to the invdetail table, a new record in the invfifo table was created.

When the row is added to the invfifo table, it does not actually calculate the fifo value, as this is a time consuming process and as explained later, It would have made standard transactions in xtuple to slow to use. So all that is done is to keep track of the total incoming and total outgoing in a specific location. 

The effect of this was that we kept a invfifo_qty_before and invfifo_qty_after column, for both incoming and outgoing these numbers would increase. 

Dealing with voids


As with all systems that involve user interaction, there is an intrinsic need to handle data changes after they have been committed to the database. In Xtuple this is done generally by posting a reverse transaction which undoes the inital operation, so if you ship a product, it reduces the stock by X pcs, then to void it, you do not delete the transaction, rather create another transaction that increases the stock by the same X pcs.

This not only added complexity later on to the calculations, but also added considerable noise when looking at inventory history. To alleviate this, we ended up writing code that examines the history of stock and flags these reversals and the original transactions as void. and the stock movement (and later the price valuation) of these transactions are set to zero. 


Calculating the fifo value for incoming stock.


This is probably the simplest part of the equation, stock coming into the system is based on the original purchase price, so the unit cost just reflects the Purchase Order. In addition to this we add a proportion of the 'landed cost' (the freight cost and tax cost) to each unit cost to come up with a final value for the product.

As the frieght charges may be applied later than the original purchase, the full valuation of the history of a product in a location has to be redone quite often, as obviously the purchase price affects the sale value.


Calculating the fifo for outgoing stock.


This is where the qty_before and qty_after are used, using a simple query, we can sum the costs of incoming stock on a range of quantities for outgoing stock

The magic query that calculates this is below - this is encapsulated in a stored procedure.

SELECT   
        invfifo_cost_before +
            ((i_qty - invfifo_qty_before ) * invfifo_landedunitcost),
            
        invfifo_qty_after
        INTO
            v_ret,
            v_after
        FROM
                invdetailview
        WHERE
            invfifo_qty_before <= i_qty
            AND
            invfifo_qty_after >= i_qty
            AND    
            invdetail_location_id = i_location_id
            AND
            invhist_itemsite_id = i_itemsite_id
            AND
            invdetail_qty > 0
            AND
            invfifo_void = 0

As usual with any stock based system the whole code-base has to cover the situation where products are sold when there is no stock available (frequently as it has not arrived yet, or the data has not been entered). So all the calls to this code have to check if it returns a sensible value.


Running the calculations


In addition to the above calculations a few other scenarios have to be covered, stock relocation, is done in a pair, where the outgoing value is calculated first, then used directly for the value of incoming stock.

All these calculations are run from the beginning of time, on each location / product, however if nothing has changed or the sum of changes on the last run is $0 then it is not calculated.

What happens when running the calculations is that stock valuation is adjusted from one transaction to another, this is especially true as stock is relocated, and it's value is moved to a different location. When the calculation is run the first time, it frequently results in a large number of  valuations being changed, the sum of which is stored at the end in a summary table. if you run the same sequence again on the same location / stock. the sum change reduces until it eventually becomes $0 after a few goes.

Applying the valuations to the General Ledger.


Having manage to work out a valuation for all the transactions, this now needs to be applied to the  General Ledger. Each type of transaction affects different accounts in the general ledger, for example the fulfillment part of a Sales Order would reduce the value of the inventory Asset, and increase the costs of goods sold. For Sales orders, a total adjustment is applied as a Journal entry recorded as a FIFO adjustment, this is calculated based on the difference between the actually cost recorded by the transactions and the expected cost based on the product value. It has to be updated each time the value of the products being sold are changed.

These types of calculation have to be applied to Credit Memos, Invoices (without Sales) and Miscellaneous adjustments. They are run every night, so during the day, the valuation of the stock may become out of sync with reality, however if you look at the end of last months accounts they usually reflect the current position at that time.

Conclusion.


Well after quite a long period of testing, and debugging we finally got stock valuation figures for over 500K transactions within a few hundred dollars to match the GL account, and show an reasonable stock valuation. The initial run for a FIFO calculation takes around 2 hours on that number of transactions, but after that, the daily run takes about 20 minutes overnight to do the calculations and make the adjustments.  It does affect the speed of our processing of orders slightly, but this is more due to the issue that we have automated the distribution and receipt of large orders, rather than having to distribute each item line by line. 

If you need more information or are interested in using this code please contact sales@roojs.com.

Add Your Comment

    Follow us