European Article Number Check PlSql
About
An EAN-13 barcode (originally European Article Number) is a barcoding standard which is a superset of the original 12-digit Universal Product Code (UPC) system developed in North America. The EAN-13 barcode is defined by the standards organisation GS1. The numbers encoded in EAN-13 bar codes are product identification numbers which are called Japanese Article Number (JAN) in Japan. All the numbers encoded in UPC and EAN barcodes are known as Global Trade Item Numbers (GTIN), and they can be encoded in other GS1 barcodes.
Function Check
CREATE OR REPLACE function QAS_DWH.EAN_CODE_CHECK(p_eancode varchar2) RETURN varchar2 IS
v_code varchar2(18) := null;
v_check number := 0;
v_total number := 0;
begin
--
if length(p_eancode) != 18
then RETURN 'NOK';
end if;
v_code := p_eancode;
v_check := v_code; -- numeriek check
if substr(v_code,1,2) != '87'
then RETURN 'NOK';
end if;
if v_code = '000000000000000000'
then RETURN 'NOK';
end if;
v_total := (substr(v_code,1,1) * 3) + (substr(v_code,2,1) * 1) + (substr(v_code,3,1) * 3) +
(substr(v_code,4,1) * 1) + (substr(v_code,5,1) * 3) + (substr(v_code,6,1) * 1) +
(substr(v_code,7,1) * 3) + (substr(v_code,8,1) * 1) + (substr(v_code,9,1) * 3) +
(substr(v_code,10,1) * 1) + (substr(v_code,11,1) * 3) + (substr(v_code,12,1) * 1) +
(substr(v_code,13,1) * 3) + (substr(v_code,14,1) * 1) + (substr(v_code,15,1) * 3) +
(substr(v_code,16,1) * 1) + (substr(v_code,17,1) * 3)
;
v_check := mod(v_total,10);
if v_check = 0 then
v_check := 10;
end if;
if (10 - v_check) != substr(v_code,18,1)
then RETURN 'NOK '||v_total;
end if;
RETURN 'OK';
EXCEPTION
WHEN OTHERS THEN
RETURN 'NOK';
END;
/