Stock Levels

Within the database stock levels are stored in several places and current levels are dynamically calculated to get most accurate values.

Tables

Products_Inventory

Prior to {2021?} this table held the current stock levels and was adjusted by each transaction that altered stock. This meant it was the definitive source of stock levels. This table was stored in a single authority database and replicated as required to other stores.

After {2021?} this table maintained its previous purpose but was no longer the authoritive source of truth

If you simply require a reasonably good estimate of stock levels, query this table for a given Pid, LocationId and optionally PVariant

Values in this Products_Inventory table can be changed by the StockMovements logic when it detects inconsistencies.

StockMovements

This table contains a list of all individual stock movements including references to the source transaction. Transactions are recorded in this table as they are generated and a background task scans all transactions periodically and ensures they are recorded. This means that should a generating source transaction fail to record a stock movement for some reason, it will eventually be recorded.

StockPoints

A stockpoint defines a window of time between two physical counts of stock. All stockmovements are marked with the Stockpoint they belong too as are the source transactions

Reading Current Stock on Hand Levels

To get the current actual OnHand for a product you need to perform several steps - these are optimised within Fieldpine itself but will be required if manually querying the tables You only need this steps if you require the precise current level, the products_inventory table will suffice for most reporting needs

1. Determine the current Stockpoint# for the product and location you are interested in

   select max(stockpoint) from stockmovements where locid=? and pid=? and pvariant=?

2. Get the Qty on hand at the beginning of this period

select Qty as QtyAtStart from Stockmovements where locid=? and pid=? and pvariant=? and reason=@TBS@ and stockpoint=?

or

select qoh from stockpoints where locid=? and pid=? and pvariant=? and stockpoint=?

3. Calculate the changes since that time

   select sum(qty) as QtyChanges from stockmovements where locid=? and pid=? and pvariant=? and stockpoint=? and reason <> @TBS@

4. Current Onhand = QtyAtStart + QtyChanges

@@ Explain optomisation for non stocktaked items @@

Worked Example

Start: QtyOnHand = 3 Current Stockpoint=11

The following transactions occur

• 9am Lane 123 sells 1
• 10am A staff member does a stockcount and records 5 on the shelf
• 11am Lane 789 sells 1

Given the above sequence, the QtyOnHand should be showing as 4

Table	Effect	QOH at this time
StockMovements	Lane 123 sale recorded as -1 with Stockpoint=11	2
StockMovements	10am stock count creates Stockpoint=12 and Qty=5	5
Stockpoints	10am stock count creates row with stockpoint=12 and Qoh=5
StockMovements	Lane 789 sale recorded as -1 with Stockpoint=11	4

However lets consider the effect if the stockcount at 10am was done using an offline terminal and not actually sent to the retail server until 1pm

Table	Effect	QOH at this time
StockMovements	Lane 123 sale recorded as -1 with Stockpoint=11	2
StockMovements	Lane 789 sale recorded as -1 with Stockpoint=11	1
StockMovements	10am stock count creates Stockpoint=12 and Qty=5	5
Stockpoints	10am stock count creates row with stockpoint=12 and Qoh=5
StockMovements	Background tasks detect that Lane 789 sale is out of sequence and change the stockpoint number of that record to be 12	4