Wednesday, October 29, 2014

Switzerland Tax Registration number logic from Jan 1st 2014

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.