Uniface ProcScript: Fixing Money Rounding with SQLite

Résumé

Pixel shares a Uniface 10 credit-module lesson on rounding money reliably. Modulo-based ProcScript rounding failed 2 of 60 tests because revenue divided by 6 could include fractions; a SQLite SELECT using integer division and CAST produces credit limits in full 10-euro increments.

DEV Community

Rounding money in Uniface ProcScript - why the modulo operator was the wrong tool

The customer app from the series Building a customer app in Uniface 10 got a credit module in stage 31. One operation suggests a credit limit per customer: two months of revenue, rounded down to full 10 euros, half of it if the customer paid late once or twice, and nothing at all from three late payments on. Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) on Windows, October 2026. The app uses SQLite through the SLE driver (SQLite 3.50.2). The amounts All money columns of the app are integer cents. Two months of revenue are the revenue of the last 12 months divided by 6, and "full 10 euros" is a multiple of 1000 cents. What went wrong The first version did the rounding in ProcScript with the modulo operator. The test service of the stage ran the operation against fixed test data and compared with the expected amount. Two of 60 tests failed: the suggestion came back with a fraction (1000,33) instead of a multiple of 1000 cents. revenue / 6 is not an integer in most cases. In these tests % only worked with the integer part of the value, so subtracting the remainder from the value kept the fraction. Instead of looking for the right combination of ProcScript functions, the rounding moved to the place where the numbers come from anyway. The working version The helper COUNT_SQL runs a statement that returns one number: entry COUNT_SQL
params
string pSql : IN
numeric pCount : OUT
endparams
pCount = -1
sql pSql, "CUSTOMERS"
if ($status >= 0)
pCount = $result
endif
return 0
end
The rounding is one SELECT. With integer cents, / in SQLite is already an integer division; CAST(... AS INTEGER) keeps the result an integer even if a value ever arrives as REAL. For positive amounts both mean rounding down: call COUNT_SQL("SELECT CAST(%%(vRevenue) / 6000 AS INTEGER) * 1000 FROM (SELECT 1 AS X)", vBase)
Half of it, again on full 10 euros: call COUNT_SQL("SELECT CAST(%%(vBase) / 2000 AS INTEGER) * 1000 FROM (SELECT 1 AS X)", vBase)
The whole entry: entry SUGGEST_OF
params
numeric pCustomerId : IN
numeric pSuggestCents : OUT
string pReason : OUT
endparams
variables
string vCond, vPaid, vText
numeric vRevenue, vLate, vLateOpen, vBase
endvariables
pSuggestCents = 0
pReason = ""
call REV_COND(vCond)
call PAID_SQL(vPaid)
call COUNT_SQL("SELECT COALESCE(SUM(d.GROSS_CENTS), 0) FROM SALES_DOC d WHERE %%(vCond) AND d.CUSTOMER_ID = %%(pCustomerId) AND d.DOC_DATE > date('now', 'localtime', '-1 year')", vRevenue)
call COUNT_SQL("SELECT COUNT() FROM SALES_DOC d JOIN %%(vPaid) p ON p.DOC_ID = d.DOC_ID WHERE d.CUSTOMER_ID = %%(pCustomerId) AND d.DOC_TYPE = 'INVOICE' AND d.STATUS = 'PAID' AND d.DUE_DATE IS NOT NULL AND p.LASTPAY > d.DUE_DATE AND d.DOC_DATE > date('now', 'localtime', '-1 year')", vLate)
call COUNT_SQL("SELECT COUNT(
) FROM SALES_DOC d WHERE d.CUSTOMER_ID = %%(pCustomerId) AND d.DOC_TYPE = 'INVOICE' AND d.STATUS IN ('ISSUED', 'PARTLY_PAID') AND d.DUE_DATE < date('now', 'localtime')", vLateOpen)
vLate = vLate + vLateOpen
if (vRevenue <= 0)
pReason = "No revenue in the last 12 months."
return 0
endif
call COUNT_SQL("SELECT CAST(%%(vRevenue) / 6000 AS INTEGER) * 1000 FROM (SELECT 1 AS X)", vBase)
call FMT_CENTS(vRevenue, vText)
if (vLate >= 3)
pReason = "Revenue %%(vText) in 12 months, %%(vLate) late payment(s): no limit suggested (prepayment)."
return 0
endif
if (vLate > 0)
call COUNT_SQL("SELECT CAST(%%(vBase) / 2000 AS INTEGER) * 1000 FROM (SELECT 1 AS X)", vBase)
pReason = "Revenue %%(vText) in 12 months, %%(vLate) late payment(s): one month of revenue."
else
pReason = "Revenue %%(vText) in 12 months, paid on time: two months of revenue."
endif
pSuggestCents = vBase
return 0
end
The same pitfall in a median The lifecycle module computes the median of "days until the first order". The obvious way to find the middle of a sor...
Uniface ProcScript: Fixing Money Rounding with SQLite
Powered by Readybot.io

Les dernières actus de Pixel

Incident Response: Look Beyond the Red Alert

Pixel’s DEV Community announcement explores how teams can fixate on one failing service while overlooking the real cause of an outage, such as a full …

Sach: AI Fact-Checks WhatsApp Forwards in India

Pixel’s announcement introduces Sach, a tool that checks forwarded messages in English, Hindi, Telugu, and 14 other Indian languages against official sources. It gives cited …

Java 25 Removes SecurityManager: What to Fix

Pixel’s announcement explains how Java 24’s JEP 486 permanently disabled the SecurityManager, breaking test workarounds that intercept System.exit. It recommends returning exit codes or throwing …