Overview

(work in progress)

Info about the tables involved for stock management on item level

• StockEntry: Valid locations for item are found here with the current quantity. The current quantity of each location here should match the last row in StockLog for this location.
• StockLog: Log of all transactions in stock. Each log entry is for one specific stock location.
  • ChangeQuantity is exactly that: the change in quantity for this transaction.
  • Quantiy is the quantity in stock at the location after the change.
  • IsPendingNegativeQuantity is a flag used to keep track of transactions that have led to negative quantity in stock, so that we can find these log entries when we add new batches to stock. IsPendingNegativeQuantity is set by an insert-trigger, and should never be updated unless it’s to make a correction to a known error. When inserting new rows to StockLog be aware that the trigger is there!
  • !! When a transaction is partially possible to take from a batch we should have 2 rows in stocklog. One with the batch-related reduction that leads to us emptying stock, and one with the negative quantity we end up with in stock. Let’s say we have 1 in stock but we have an invoice with 2. This leads to 2 rows in stocklog, one with -1 and batch-connection, and one with -1 and IsPendingNegativeQuantity.
• StockBatch: A quantity of an item added to stock at a specific time, such as when receiving items through goods receipt.
  • As of 3.1.39 every quantity in stock for an item must be found in a StockBatch.
  • OriginalQuantity is the quantity the batch was created with.
  • Quantity is the quantity currently in stock. The quantity of all batches for an item should always be the same as the total quantity in stock for all locations in StockEntry.
• StockBatchXStockLog: Connects a batch to log entries creating a log on batch detail level.
  • ChangeQuantity is the quantity change for the batch at the location given by the stocklog. So if a log entry involves multiple batches they will both have a row in StockBatchXStockLog with separate quantities. sum(ChangeQuantity) from StockBatchXStockLog where StockLogUID = :stockloguid should equal StockLog.ChangeQuantity as long as StockLog have IsPendingNegativeQuantity=false.
  • Quantity is the quantity of the batch at this location after change.
  • NegativeQuantityOffsetByStockLogUID is a reference to any StockLog that was used to fill a negative quantity. So if you receive items into stock and have a StockLog with IsPendingNegativeQuantity the StockBatch you received will be connected to the StockLog row with IsPendingNegativeQuantity (to attempt to fill this) and NegativeQuantityOffsetByStockLogUID will be the StockLog row of the received items.