Wednesday, April 22, 2009

PL/SQL code to validate a Greek VAT Number (ΑΦΜ)

FUNCTION check_afm (v_afm VARCHAR2)
      RETURN PLS_INTEGER
   AS
      resultvalid   PLS_INTEGER := 0;
      l_digit       PLS_INTEGER;
      s             PLS_INTEGER := 0;
      m             PLS_INTEGER;
   BEGIN
      IF LENGTH (v_afm) != 9
      THEN
         RETURN 0;
      END IF;

      IF v_afm = '000000000'
      THEN
         RETURN 0;
      END IF;

      IF    REGEXP_SUBSTR (v_afm, '\d{9}') IS NULL
         OR REGEXP_SUBSTR (v_afm, '\d{9}') != v_afm
      THEN
         RETURN 0;
      END IF;

      FOR i IN 1 .. 8
      LOOP
         l_digit := TO_NUMBER (SUBSTR (v_afm, i, 1));
--        DBMS_OUTPUT.PUT_LINE ( 'l_digit = ' || l_digit );
         s := s + l_digit * POWER (2, 9 - i);
--        DBMS_OUTPUT.PUT_LINE('s = ' || s );
      END LOOP;

      m := MOD (s, 11);

--    DBMS_OUTPUT.PUT_LINE('s = ' || s );
      IF (m = 10)
      THEN
         m := 0;
      END IF;

      IF m = ASCII (SUBSTR (v_afm, 9, 1)) - 48
      THEN
         RETURN 1;
      END IF;

      RETURN 0;
   END;






No comments: