Invoice numbers look like a solved problem until you have to explain one that is missing. The EU VAT directive asks for "a sequential number, based on one or more series, which uniquely identifies the invoice" (Article 226(2) of Directive 2006/112/EC). National rules and auditors add their own expectations, and the most common question in practice is simply: where is number 17?
This article shows how the customer app from the series Building a customer app in Uniface 10 assigns invoice numbers in its Uniface services, why drafts don't get one, and the two checks that find a gap or a counter that is behind - including at the change of the year.
I'm not giving legal advice here. What exactly your invoices need depends on your country; the technique works for any numbering rule of the shape "prefix, year, running number".
Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) and the SQLite version of its SLE driver (3.50.2), October 2026.
The format
| Document | Number | Counter key |
|---|---|---|
| Quote | Q-2026-00001 |
QUOTE-2026 |
| Order | O-2026-00001 |
ORDER-2026 |
| Invoice | I-2026-00001 |
INVOICE-2026 |
| Credit note | C-2026-00001 |
CREDIT-2026 |
| Delivery note | D-2026-00001 |
DELIVERY-2026 |
| Purchase order | P-2026-00001 |
PURCHASE-2026 |
| Ticket | T-2026-00001 |
TICKET-2026 |
Every series starts at 1 each year. The counters live in one small table:
CREATE TABLE DOC_SEQ (
SEQ_KEY VARCHAR(20) NOT NULL PRIMARY KEY,
LAST_NO INTEGER NOT NULL
);
and the document table has a unique index on the number:
CREATE UNIQUE INDEX UX_SALES_DOC_NO ON SALES_DOC (DOC_NO);
The index is the last line of defence: whatever goes wrong in the code, two invoices can't end up with the same number. Drafts have DOC_NO = NULL, and SQLite allows any number of NULLs in a unique index.
Assigning a number
The invoice service has one private entry for it. Three SQL statements, all inside the transaction that issues the invoice:
entry NEXT_NO
params
string pType : IN
string pDate : IN
string pDocNo : OUT
string pError : OUT
endparams
variables
string vKey, vPrefix, vYear
endvariables
pDocNo = ""
pError = ""
vYear = pDate[1:4]
if (pType = "QUOTE")
vPrefix = "Q"
elseif (pType = "ORDER")
vPrefix = "O"
elseif (pType = "INVOICE")
vPrefix = "I"
else
vPrefix = "C"
endif
vKey = "%%(pType)-%%(vYear)"
sql "INSERT OR IGNORE INTO DOC_SEQ (SEQ_KEY, LAST_NO) VALUES ('%%(vKey)', 0)", "CUSTOMERS"
if ($status < 0)
pError = $concat("The number range could not be read (error ", $procerror, ").")
return -1
endif
sql "UPDATE DOC_SEQ SET LAST_NO = LAST_NO + 1 WHERE SEQ_KEY = '%%(vKey)'", "CUSTOMERS"
if ($status < 0)
pError = $concat("The number range could not be updated (error ", $procerror, ").")
return -1
endif
call GET_TEXT("SELECT '%%(vPrefix)-%%(vYear)-' || printf('%05d', LAST_NO) FROM DOC_SEQ WHERE SEQ_KEY = '%%(vKey)'", pDocNo)
if (pDocNo = "")
pError = "No document number could be assigned."
return -1
endif
return 0
end
sql is the ProcScript statement that sends SQL to the path $CUSTOMERS, which the assignment file maps to the SQLite file. It doesn't go through an entity, so the counter table needs no model in the Uniface repository. $status is negative on an error and $procerror gives the error number. GET_TEXT is a small helper that runs sql/data and returns the first column of the first row.
INSERT OR IGNORE handles the new year. On the first invoice of 2027 there is no row INVOICE-2027 yet. The statement creates it with 0; on every later call it does nothing. No job has to run on New Year's Eve, and nobody has to remember to "open" a new series.
UPDATE … + 1 before reading. Incrementing first and reading afterwards means the value read is the one this transaction reserved. SQLite allows one writer at a time; after the UPDATE, this connection holds the write lock until commit or rollback, so no other connection can take the same number in between.
The year comes from the issue date, not from the draft date. An invoice drafted on 30 December and issued on 2 January belongs to the new series.
Why drafts don't get a number
A common approach is to number a document when it is created. Then someone deletes a draft, and the series has a hole that is hard to explain.
Here, quotes and orders get their number when they are created - they are not part of the gapless requirement. Invoices and credit notes get theirs only when they are issued. Before that, a draft can be edited or deleted freely; it never touched the counter.
The increment is part of the same transaction as the invoice itself. If issuing fails after the number was taken and the transaction is rolled back, the UPDATE of the counter is undone too, and the next invoice gets the same number. That only holds if nothing commits in between - which is worth checking in every operation that runs during issuing.
Cancelling an issued invoice does not free its number either. It stays, with status CANCELLED, and a credit note with its own number from the credit series reverses it.
Check 1: count versus counter
If every issued invoice of a year has a number and every increment of the counter belongs to an issued invoice, then the counter equals the number of invoices with that year:
SELECT 'NUMBER_GAP', s.SEQ_KEY
FROM DOC_SEQ s
WHERE substr(s.SEQ_KEY, 1, 8) = 'INVOICE-'
AND s.LAST_NO != (SELECT COUNT(*)
FROM SALES_DOC d
WHERE d.DOC_TYPE = 'INVOICE'
AND d.DOC_NO IS NOT NULL
AND substr(d.DOC_NO, 3, 4) = substr(s.SEQ_KEY, 9, 4));
A mismatch means a number was taken without a document (counter too high: a gap) or a document has a number the counter doesn't know (counter too low). Either way someone has to look. The app's consistency check reports this, and a test proves it by moving the counter one step and expecting exactly one new finding.
Check 2: highest number in use versus counter
Count-based checks have a weakness: two errors can cancel each other out. And they only work for document types where every number must exist. The second check compares the counter with the highest number actually in use, for every series in the table:
SELECT d.DOC_TYPE || '-' || substr(d.DOC_NO, 3, 4) AS K,
CAST(substr(d.DOC_NO, 8) AS INTEGER) AS M
FROM SALES_DOC d
WHERE d.DOC_NO GLOB '?-[0-9][0-9][0-9][0-9]-[0-9]*'
UNION ALL
SELECT 'TICKET-' || substr(TICKET_NO, 3, 4), CAST(substr(TICKET_NO, 8) AS INTEGER)
FROM TICKET
WHERE TICKET_NO GLOB '?-[0-9][0-9][0-9][0-9]-[0-9]*'
UNION ALL
...
Grouped by key with MAX(M), this gives the highest number per series. The GLOB pattern makes sure only numbers in the official format are parsed - anything else (imported legacy numbers, test data) is ignored instead of producing nonsense.
Joined with the counter table, every series gets a status:
SELECT s.SEQ_KEY, s.LAST_NO, COALESCE(u.M, 0),
CASE WHEN COALESCE(u.M, 0) > s.LAST_NO THEN 'BEHIND' ELSE 'OK' END
FROM DOC_SEQ s
LEFT JOIN (/* used numbers, grouped */) u ON u.K = s.SEQ_KEY
UNION ALL
SELECT u.K, 0, u.M, 'MISSING'
FROM (/* used numbers, grouped */) u
WHERE u.K NOT IN (SELECT SEQ_KEY FROM DOC_SEQ)
ORDER BY 1;
- BEHIND: a document carries a higher number than the counter. The next document would get a number that already exists - the unique index would reject it and issuing would fail. This can happen after a manual correction of the counter or a partial restore.
- MISSING: documents with numbers of a series that has no counter row at all. The next document would start at 1 again.
The test creates both cases in a past year: a counter TICKET-1999 at 3 with a ticket T-1999-00005, and an invoice I-1998-00002 without a counter row - and expects BEHIND and MISSING.
The check as a Uniface operation
The check above is assembled in ProcScript from string pieces, so the same "used numbers" subquery can be inserted twice without writing it twice:
vPattern = "GLOB '?-[0-9][0-9][0-9][0-9]-[0-9]*'"
vUsed = "SELECT d.DOC_TYPE || '-' || substr(d.DOC_NO, 3, 4) AS K, CAST(substr(d.DOC_NO, 8) AS INTEGER) AS M FROM SALES_DOC d WHERE d.DOC_NO %%(vPattern)"
vUsed = "%%(vUsed) UNION ALL SELECT 'TICKET-' || substr(TICKET_NO, 3, 4), CAST(substr(TICKET_NO, 8) AS INTEGER) FROM TICKET WHERE TICKET_NO %%(vPattern)"
...
vUsed = "SELECT K, MAX(M) AS M FROM (%%(vUsed)) GROUP BY K"
vSql = "SELECT s.SEQ_KEY, s.LAST_NO, COALESCE(u.M, 0), CASE WHEN COALESCE(u.M, 0) > s.LAST_NO THEN 'BEHIND' ELSE 'OK' END FROM DOC_SEQ s LEFT JOIN (%%(vUsed)) u ON u.K = s.SEQ_KEY"
vSql = "%%(vSql) UNION ALL SELECT u.K, 0, u.M, 'MISSING' FROM (%%(vUsed)) u WHERE u.K NOT IN (SELECT SEQ_KEY FROM DOC_SEQ) ORDER BY 1"
sql/data vSql, "CUSTOMERS"
pData = $result
pCount = $itemcount(pData)
call COUNT_ISSUES(pData, pIssues)
Counting the problems is done in ProcScript, on the list that sql/data returned, instead of running the whole statement a second time with COUNT(*):
entry COUNT_ISSUES
params
string pData : IN
numeric pIssues : OUT
endparams
variables
string vRow, vState
endvariables
pIssues = 0
forlist vRow in pData
getitem vState, vRow, 4
if (vState != "OK")
pIssues = pIssues + 1
endif
endfor
return 0
end
The public operation NUMBER_RANGES returns the rows, the number of issues and the year-change count described next. It requires the administrator role; a system check calls the same entry and turns a non-zero count into an ERROR line.
The change of the year
Because new series are created on demand, there is nothing to do on 1 January. The report still shows one number for the year change: how many series exist only for previous years.
SELECT COUNT(*)
FROM DOC_SEQ
WHERE SEQ_KEY GLOB '*-[0-9][0-9][0-9][0-9]'
AND CAST(substr(SEQ_KEY, -4) AS INTEGER) < 2026
AND substr(SEQ_KEY, 1, length(SEQ_KEY) - 5) NOT IN (
SELECT substr(SEQ_KEY, 1, length(SEQ_KEY) - 5)
FROM DOC_SEQ
WHERE SEQ_KEY GLOB '*-2026');
In early January this is normal: last year's series exist, this year's will appear with the first document. In March, a series that still has no row for the current year means nobody issued such a document this year - which may be fine, or may be the hint that something stopped working.
Takeaways
One counter row per series and year, created with INSERT OR IGNORE.
Increment, then read, inside the transaction that creates the document.
Number invoices when they are issued, not when the draft is created.
Back it with a unique index on the number.
Check both: count against counter, and highest number in use against counter.
Don't parse what doesn't match the format - filter with GLOB first.
Top comments (0)