SELECT DECODE(
ceil(((REGEXP_SUBSTR(num,'[0-9]',1,1))*5+
(REGEXP_SUBSTR(num,'[0-9]',1,2))*4+
(REGEXP_SUBSTR(num,'[0-9]',1,3))*3+
(REGEXP_SUBSTR(num,'[0-9]',1,4))*2+
(REGEXP_SUBSTR(num,'[0-9]',1,5))*7+
(REGEXP_SUBSTR(num,'[0-9]',1,6))*6+
(REGEXP_SUBSTR(num,'[0-9]',1,7))*5+
(REGEXP_SUBSTR(num,'[0-9]',1,8))*4
)/11) * 11 -
((REGEXP_SUBSTR(num,'[0-9]',1,1))*5+
(REGEXP_SUBSTR(num,'[0-9]',1,2))*4+
(REGEXP_SUBSTR(num,'[0-9]',1,3))*3+
(REGEXP_SUBSTR(num,'[0-9]',1,4))*2+
(REGEXP_SUBSTR(num,'[0-9]',1,5))*7+
(REGEXP_SUBSTR(num,'[0-9]',1,6))*6+
(REGEXP_SUBSTR(num,'[0-9]',1,7))*5+
(REGEXP_SUBSTR(num,'[0-9]',1,8))*4),
10,'INVALID',
(REGEXP_SUBSTR(num,'[0-9]',1,9)),'VALID',
'INVALID'
) FINAL_RESULT
--INTO l_result
FROM (SELECT
REGEXP_SUBSTR(REPLACE(REGEXP_SUBSTR(:p_trn_value,'^(CHE-[0-9]{3}.[0-9]{3}.[0-9]{3}) (MWST|TVA|IVA)$'),'.',''),'[0-9]+') NUM
FROM DUAL
);
Sample value is CHE-456.456.791 MWST
In words, the ninth digit should be equal to upper round(digit1*5+digit2*4+digit3*3+digit4*2+digit5*7+digit6*6+digit7*5+digit8*4)/11*11-(digit1*5+digit2*4+digit3*3+digit4*2+digit5*7+digit6*6+digit7*5+digit8*4)
That's it. Nice logic right :). It will be different for different countries.
No comments:
Post a Comment