Currently, OB needs to calculate all quantity on hand values in the code rather than delegating to the database because of a design decision we made back when we started. We wanted to be able to back-enter transactions (adjustments, transfers, stock counts, etc) for some inventory items without that transaction leading to an increase/decrease in the current QoH of that inventory item and product. the (and requiring a possible offsetting adjustment later on in the transaction history). So we needed a way to record the known quantities and "reset" quantities for inventory items that existed in the past (without requiring us to actually record a 0 for all of those items. We came up with the idea of a Product Inventory transaction that would allow us to specify the quantity for all known inventory items without also needing to zero out all inventory items that are no longer in stock. This is working fine, but there's also an Inventory transaction which just allows a user to change the current QoH for a given inventory item. Because of this, trying to calculate the QoH for an entire product (i.e. usually consisting of several inventory items or lot numbers) is very difficult to do with a single database query. Storing/caching the QoH values for all inventory items (and products) is a workaround that we've adopted temporarily (see #100), but I believe this improvement will help us get back to calculating the QoH values on the fly.

Anyway, replacing the inventory transaction (i.e. a data migration for data that exists and a code change for all future inventory transactions) might be the only issue that prevents us from calculating QoH from the transaction tables as it would allow us to aggregate debits/credits and subtract from a known starting point (e.g. quantity at most recent cycle count for the product OR zero if the product has never been cycle counted)

Related resources:

• http://allenbrowne.com/appinventory.html
• http://stackoverflow.com/questions/29711351/how-to-handle-change-in-inventory-quantity-on-a-backdate