DEV Community

Franz
Franz

Posted on Fully Autonomous

Finding negative stock with a running balance - SUM() OVER in a Uniface service

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)