Function to check if the mail address and attach to a value set

---------------------------------------------------------------------------------------
/*Function to check if the mail address is valid. it checks for any valid email address.*/
--------------------------------------------------------------------------------------
   FUNCTION check_valid_email_address (
      p_email_address_i    IN   VARCHAR2,
   )
      RETURN VARCHAR2
   AS
      l_email_result   VARCHAR2 (200);
      l_cnt            NUMBER (10)    := 0;
      l_count          NUMBER (10)    := 0;
   BEGIN
IF (INSTR (p_email_address_i, '..')) > 0
      THEN
         l_email_result := g_bad_email;
      ELSIF (INSTR (p_email_address_i, ' ')) > 0
      THEN
         l_email_result := g_bad_email;
      ELSIF (INSTR (p_email_address_i, '.')) = 0
      THEN
         l_email_result := g_bad_email;
      ELSIF (INSTR (p_email_address_i, '@')) = 0
      THEN
         l_email_result := g_bad_email;
      ELSIF INSTR (SUBSTR (p_email_address_i,
                           INSTR (p_email_address_i, '@') + 1,
                           LENGTH (p_email_address_i)
                          ),
                   '@'
                  ) > 0
      THEN
         l_email_result := g_bad_email;
      ELSIF INSTR (SUBSTR (p_email_address_i,
                           INSTR (p_email_address_i, '@') - 1,
                           LENGTH (p_email_address_i)
                          ),
                   '_'
                  ) = 1
      THEN
         l_email_result := g_bad_email;
      ELSIF SUBSTR (p_email_address_i, 1, 1) = '@'
      THEN
         l_email_result := g_bad_email;
      ELSIF INSTR (UPPER (p_email_address_i), '@_') > 0
      THEN
         l_email_result := g_bad_email;
      ELSIF INSTR (UPPER (p_email_address_i), '@-') > 0
      THEN
         l_email_result := g_bad_email;
      ELSIF SUBSTR (p_email_address_i,
                    LENGTH (p_email_address_i),
                    LENGTH (p_email_address_i)
                   ) = '.'
      THEN
         l_email_result := g_bad_email;
      ELSE
         WHILE (l_cnt < LENGTH (p_email_address_i) - 1)
         LOOP
            BEGIN
               SELECT COUNT (*)
                 INTO l_count
                 FROM DUAL
                WHERE ASCII (SUBSTR (p_email_address_i, l_cnt, l_cnt + 1))
                         BETWEEN ASCII ('A')
                             AND ASCII ('Z')
                   OR ASCII (SUBSTR (p_email_address_i, l_cnt, l_cnt + 1))
                         BETWEEN ASCII ('a')
                             AND ASCII ('z')
                   OR ASCII (SUBSTR (p_email_address_i, l_cnt, l_cnt + 1))
                         BETWEEN ASCII (0)
                             AND ASCII (9)
                   OR ASCII (SUBSTR (p_email_address_i, l_cnt, l_cnt + 1)) IN
                         (ASCII ('_'),
                          ASCII ('-'),
                          ASCII ('.'),
                          ASCII ('@'),
                          ASCII ('''')
                         );
            END;
            --Increment the counter
            l_cnt := l_cnt + 1;
         END LOOP;

         IF (l_count >= 1)
         THEN
            l_email_result := p_email_address_i;
         END IF;
      END IF;

      RETURN l_email_result;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END check_valid_email_address;

---------------------------------
Value set for valid email address
---------------------------------

FND PLSQL  "declare
l_local VARCHAR2(200) := 'Invalid';
CURSOR validate_email_id
is
select 'Valid'
from dual
where check_valid_email_address(:!VALUE,'Y') = :!VALUE
and :!VALUE <> 'BAD_EMAIL_ADDRESS';
BEGIN
open validate_email_id;
fetch validate_email_id into l_local;
if validate_email_id%NOTFOUND then
fnd_message.set_name('XXCAS_PRJ','XXCAS_PRJ_INVALID_EMAIL_ID');
fnd_message.raise_error;
end if;
close validate_email_id;
end;"