Low Level Documentation
 
Library Fieldpine Internals Replication Gds Security Fpos.dll Startup logic PosGreen Network PosGreen Install Process Multicast Protocol Data Structures Settings Logic Trace

Logic

Security Model Product Distribution Gateways Staff Login Stock Levels Shipping Addresses Agency Stores Remote Reports Sales Handlers

PosGreen Engine This engine provides the bulk of the POS processing at checkouts. It can be used directly or via central servers as a remote service.

Contexts & Sessions

Advanced

Spill Database Debugging

Reference Data

refdata Country refdata SystemString

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
  3. 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=?
  4. Calculate the changes since that time
    select sum(qty) as QtyChanges from stockmovements where locid=? and pid=? and pvariant=? and stockpoint=? and reason <> @TBS@
  5. 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

TableEffectQOH at this time
StockMovementsLane 123 sale recorded as -1 with Stockpoint=112
StockMovements10am stock count creates Stockpoint=12 and Qty=55
Stockpoints10am stock count creates row with stockpoint=12 and Qoh=5
StockMovementsLane 789 sale recorded as -1 with Stockpoint=114

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

TableEffectQOH at this time
StockMovementsLane 123 sale recorded as -1 with Stockpoint=112
StockMovementsLane 789 sale recorded as -1 with Stockpoint=111
StockMovements10am stock count creates Stockpoint=12 and Qty=55
Stockpoints10am 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