A customer app built in Uniface 10 stores a VAT ID per business customer. A tax check module should list IDs that are obviously wrong: too short, no country prefix, odd characters, or a prefix that does not fit the billing address. This is not a full validation with checksums per country, only a plausibility check that catches typos before an invoice goes out.
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).
Normalising first
Users type DE 123 456 789, de123456789 or DE123456789. All three are the same ID. The query compares the normalised value:
UPPER(REPLACE(p.VAT_ID, ' ', ''))
Four rules
- Length between 8 and 14 characters.
- The first two characters are letters:
substr(v, 1, 2) GLOB '[A-Z][A-Z]'. - The rest contains only digits and letters:
substr(v, 3) GLOB '*[^0-9A-Z]*'is true when any other character is present. - The prefix matches the country of the billing address. Greece is the exception: the address says
GR, the VAT prefix isEL.
GLOB is case-sensitive and uses Unix wildcards (*, ?, [...], [^...]). That is why the value is upper-cased first. LIKE would not help here, it has no character classes.
A few values:
VALUE LENGTH PREFIX OK BAD CHARACTERS
DE 123 456 789 11 1 0
de123456789 11 1 0
DE12345678-9 12 1 1
1E123456789 11 0 0
The billing country
The billing address is the first address of type BILLING per customer. The service builds that subquery once in an entry and inserts it into each statement that needs it:
entry BILL_SQL
params
string pSql : OUT
endparams
pSql = "(SELECT a.CUSTOMER_ID AS CUSTOMER_ID, UPPER(COALESCE(a.COUNTRY, '')) AS COUNTRY FROM CUSTOMER_ADDRESS a WHERE a.ADDRESS_ID = (SELECT MIN(b.ADDRESS_ID) FROM CUSTOMER_ADDRESS b WHERE b.CUSTOMER_ID = a.CUSTOMER_ID AND b.ADDRESS_TYPE = 'BILLING'))"
return 0
end
The whole operation
public operation VAT_ID_CHECK
params
string pData : OUT
numeric pCount : OUT
string pError : OUT
endparams
variables
string vBill
endvariables
pData = ""
pCount = 0
pError = ""
activate "RIGHTS_SVC".REQUIRE("READ", pError)
if ($status < 0)
return -1
endif
call BILL_SQL(vBill)
call READ_ROWS("SELECT c.CUSTOMER_ID, c.LAST_NAME || ', ' || c.FIRST_NAME AS NAME, p.VAT_ID, COALESCE(b.COUNTRY, '') AS COUNTRY, CASE WHEN length(UPPER(REPLACE(p.VAT_ID, ' ', ''))) < 8 OR length(UPPER(REPLACE(p.VAT_ID, ' ', ''))) > 14 THEN 'LENGTH' WHEN NOT substr(UPPER(REPLACE(p.VAT_ID, ' ', '')), 1, 2) GLOB '[A-Z][A-Z]' THEN 'PREFIX' WHEN substr(UPPER(REPLACE(p.VAT_ID, ' ', '')), 3) GLOB '*[^0-9A-Z]*' THEN 'CHARACTERS' ELSE 'COUNTRY' END AS PROBLEM FROM CUSTOMER_PARTY p JOIN CUSTOMER c ON c.CUSTOMER_ID = p.CUSTOMER_ID LEFT JOIN %%(vBill) b ON b.CUSTOMER_ID = p.CUSTOMER_ID WHERE COALESCE(p.VAT_ID, '') <> '' AND (length(UPPER(REPLACE(p.VAT_ID, ' ', ''))) < 8 OR length(UPPER(REPLACE(p.VAT_ID, ' ', ''))) > 14 OR NOT substr(UPPER(REPLACE(p.VAT_ID, ' ', '')), 1, 2) GLOB '[A-Z][A-Z]' OR substr(UPPER(REPLACE(p.VAT_ID, ' ', '')), 3) GLOB '*[^0-9A-Z]*' OR (COALESCE(b.COUNTRY, '') <> '' AND substr(UPPER(REPLACE(p.VAT_ID, ' ', '')), 1, 2) <> CASE WHEN b.COUNTRY = 'GR' THEN 'EL' ELSE b.COUNTRY END)) ORDER BY 1", pData, pCount, pError)
if ($status < 0)
return -1
endif
return 0
end
The CASE returns the first rule that fails as a short code, so the form can show "wrong length" instead of LENGTH. The WHERE repeats the rules; only rows with at least one problem come back. A customer without a billing address is only checked for format, not for the country.
Result with test data:
ID NAME VAT_ID COUNTRY PROBLEM
3500003 Wrongvat, Wim DE12 FR LENGTH
3500004 Mismatch, Mia DE987654321 AT COUNTRY
In the form this reads #3500004 Mismatch, Mia: DE987654321 (prefix differs from the billing country), billing country AT.
Limits
- No checksum. Several countries have one, and it would need code per country.
- The length range is wide on purpose. Per-country lengths would be the next step, for example with a small lookup table.
- It cannot tell whether the ID is registered. That needs the official online check.
Takeaways
- Normalise before you compare: spaces and case.
-
GLOBwith character classes covers simple format rules without a regex extension. - Return a short reason code from SQL and translate it in the UI.
- Keep known exceptions (GR/EL) in one place in the query.
Top comments (0)