A customer app built in Uniface 10 keeps stock as movements: one row per receipt, delivery, transfer or correction in the table STOCK_MOVE, with a signed quantity. The current stock of a product in a warehouse is the sum of its movements. A check "stock below zero" on that sum misses the interesting case: a delivery booked before the receipt, corrected a week later. At the end everything adds up, but for a week the books said -5.
Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) on Windows, November 2026. The app uses SQLite through the SLE driver (SQLite 3.50.2).
The data
Quantities are stored in thousandths (QTY_MILLI), so 10000 means 10 pieces. Four movements of one product:
MOVE_ID WAREHOUSE MOVE_DATE QTY_MILLI TYPE
1 W1 2026-10-17 10000 RECEIPT
2 W1 2026-10-27 -15000 DELIVERY
3 W1 2026-11-06 8000 ADJUST
7 W2 2026-11-13 -500 ADJUST
The end balance in W1 is 3000, nothing to see. W2 is at -500 right now.
The running balance
SELECT PRODUCT_ID, WAREHOUSE_ID, MOVE_DATE, MOVE_ID, SUM(QTY_MILLI) OVER (PARTITION BY PRODUCT_ID, WAREHOUSE_ID ORDER BY MOVE_DATE, MOVE_ID) AS BAL FROM STOCK_MOVE
With ORDER BY inside OVER, SUM() becomes a running total: each row gets the sum of all rows of the same product and warehouse up to and including itself.
MOVE_ID WAREHOUSE MOVE_DATE QTY_MILLI BAL
1 W1 2026-10-17 10000 10000
2 W1 2026-10-27 -15000 -5000
3 W1 2026-11-06 8000 3000
7 W2 2026-11-13 -500 -500
Two details:
-
PARTITION BY PRODUCT_ID, WAREHOUSE_ID: the balance runs per product and warehouse. A transfer moves stock between warehouses; a balance per product only would hide it. -
ORDER BY MOVE_DATE, MOVE_ID: several movements on one day need a stable order. The id is the booking order.
Only the rows below zero
A window function cannot be used in WHERE on the same level, so the running sum goes into a subquery. The outer query keeps the rows below zero and groups them per product and warehouse: the lowest balance, the first day below zero, and how many movements left it negative.
public operation NEGATIVE_STOCK
params
string pData : OUT
numeric pCount : OUT
string pError : OUT
endparams
pData = ""
pCount = 0
pError = ""
activate "RIGHTS_SVC".REQUIRE("READ", pError)
if ($status < 0)
return -1
endif
call READ_ROWS("SELECT p.SKU, w.WAREHOUSE_CODE, MIN(r.BAL) AS LOWEST, MIN(r.MOVE_DATE) AS FIRST_DAY, COUNT(*) AS MOVES FROM (SELECT PRODUCT_ID, WAREHOUSE_ID, MOVE_DATE, MOVE_ID, SUM(QTY_MILLI) OVER (PARTITION BY PRODUCT_ID, WAREHOUSE_ID ORDER BY MOVE_DATE, MOVE_ID) AS BAL FROM STOCK_MOVE) r JOIN PRODUCT p ON p.PRODUCT_ID = r.PRODUCT_ID JOIN WAREHOUSE w ON w.WAREHOUSE_ID = r.WAREHOUSE_ID WHERE r.BAL < 0 GROUP BY p.SKU, w.WAREHOUSE_CODE ORDER BY 3, 1, 2", pData, pCount, pError)
if ($status < 0)
return -1
endif
return 0
end
READ_ROWS is a small entry in the same service that runs the statement and returns the rows as a Uniface list; RIGHTS_SVC.REQUIRE checks the role of the logged-in user. The service has no loop and no variables for the previous balance; the database does the work.
Result for the data above:
SKU WAREHOUSE LOWEST FIRST_DAY MOVES
TS34-A T34-W1 -5000 2026-10-27 1
TS34-A T34-W2 -500 2026-11-13 1
W1 shows up although its current stock is positive. That is the point of the check.
In the form
The form calls the operation and builds one line per row, for example TS34-A in T34-W1: lowest -5, first on 2026-10-27, 1 move(s) below zero. The division by 1000 happens in the form, the service returns the stored values.
Testing it
The test service inserts test movements with fixed ids, calls NEGATIVE_STOCK, expects two rows with the lowest balances -5000 (W1) and -500 (W2), and rolls everything back at the end. The same SQL ran beforehand with Python's sqlite3 against a copy of the database with the same test rows, so the expected values were known before the first compile.
Takeaways
- A sum at the end is not enough for stock; a running sum shows the history.
- Partition by everything that defines "one stock": product and warehouse.
- Make the order stable with a unique id after the date.
- Window functions go into a subquery when you want to filter on them.
Top comments (0)