Thursday, December 18, 2008

Email Address validation by Forms Personalization, Profile Options and Regular Expressions (regex) in the Oracle eBusiness Suite

In prior posts I've dealt with Forms Personalizations, and played with email e.g. via BI (XML) Publisher Bursting. In this post we'll come up with a simple Forms Personalization to ensure that data entry of email addresses results in well-formed email addresses. We'll use regular expressions: an underutilized feature in Oracle since 10g. Initially we'll look at the Remittance Email address on Supplier Sites. But the implementation will allow easy re-use for other email address fields in the EBS by storing the regular expression in a Profile Option.

The Regular Expression

Lets take a look at the regular expression I'll use for email address validation. This regular expression is a consolidation from a variety of sources, considers IPv4 and IPv6 addressing, and includes specific formatting to get around an Oracle Regex bug. Note it isn't the "full official" regex for email address validation - I wanted a one-liner! What does the regular expression below mean? Basically allow a bunch of characters before the @ and a bunch of characters after the @ considering IPv4 or IPv6 addressing. If anyone has any suggestions/issues/changes, please feel free to comment!

Update 27-JUL-2010: Changed regex to allow multiple hypens as it was only accepting one hyphen in hostname.

Update 09-MAY-2012: Changed regex to disallow leading/trailing periods in username and disallow leading periods in server/domain.

^[-a-zA-Z0-9_\+\^!#\$%&*+\/\=\?\`\|\{\}~\']+(\.([-a-zA-Z0-9_\+\^!#\$%&*+\/\=\?\`\|\{\}~\'])+)*@((([0-9a-zA-Z]*[-\w]*[0-9a-zA-Z])+\.)+[a-zA-Z]{2,9})|(\[([0-9]{1,3}(\.[0-9]{1,3}){3})|([0-9a-fA-F]{1,4}(\:[0-9a-fA-F]{1,4}){7})\])$

Profile Option

We'll store the regular express as a profile option. This allows a single source of truth for our email address validation logic. We could equally put it in a PL/SQL package, but then updates would require coding ... and no-one wants to code these days ;-)

Navigate to Application Developer, Profile
    Name: XXV8_REGEX_EMAIL
    Application: Virtuate (or your chosen modifications application)
    User Profile Option Name: Virtuate Regular Expression: Email Address
    Set to Site level visible/updatable only
    Save
Navigate to System Administrator, Profile, System
    Query "Virtuate Regular Expression: Email Address"
    Set site level value to the regular expression above
    Save

Forms Personalization

Okay, moving onto the good stuff. Now we'll setup the Forms Personalization to validate the Remittance Email address on the Supplier Sites, Payment tab.

Navigate to Payables Manager, Suppliers, Entry
    Query up an existing supplier and navigate to Sites
    Click Help, Diagnostics, Custom Code, Personalization from the menu.
    Enter the APPS password
Enter the Forms Personalization Header and Condition
    Seq: 10
    Description: Remittance Email Validation
    Trigger Event: WHEN-VALIDATE-RECORD
    Trigger Object: SITE
    Condition: nvl(:SITE.REMITTANCE_EMAIL,'X') != nvl(regexp_substr(:SITE.REMITTANCE_EMAIL,fnd_profile.value('XXV8_REGEX_EMAIL')),'X')
    Save
Enter the Forms Personalization Action
    Seq: 10
    Type: Message
    Description: Remittance Email Invalid
    Message Type: Error
    Message Text: Remittance Email is invalid. Please enter a well-formed email address. Ensure there are no spaces and check that periods are in the correct position. For example: firstname.lastname@gmail.com

Test it out!

Enter junk in the Remittance Email address on the Payment tab and save.

Code Reuse

To implement the same email address validation on other forms, run through the Forms Personalization steps above, identifying the new block and field, replacing SITE.REMITTANCE_EMAIL as required, and update the Error Message action message description / text with the field name.

If you identify a problem with the regular expression, you have one place to change it and it flows through to all the places you implemented the forms personalization the next time your Users log in!

Requirements/Restrictions

  • Regular Expression functionality (regexp_substr) requires that you are on Oracle Database 10g or higher.
  • The solution presented doesn't test email account validity via SMTP query for address verification.

References

Related Posts

 

7 comments:

malin said...

Hello.
Very good post.

Alternative way is use a stored procedure instead of profile option.

Seems to be a bit simpler.

Gareth said...

Hi Malin,

Stored procedure is fine, but means you'd need to change code to modify the regex expression. Since profile option is just setup it gives more flexibility for changes, and also provides ability for different values at responsibility, user etc levels.

Thanks for the comments.

Regards,
Gareth

Kron77 said...

Hi Gareth, excellent post, pretty much what I needed. One extension is to consider the need and ability to send to multiple addresses. I am using your idea but changed the regex to:

\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*([,]\s*\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*)*

which basically accepts any number of well formed email addresses separated by a comma and only a comma, which is what WF is expecting.

Gareth said...

Hi Kieron,

Glad you found it useful and thanks a bunch for the extension - I'm sure that will help someone else too!

Regards,
Gareth

Juan Jesús Montero said...

Dear Gareth.:

I’m an Oracle Applications-e-Business Suite developer, ex-Oracle Consulting from Spain (Madrid) with more than 12 years experience, and I’ve seen your interesting blog with many articles about the Oracle EBS, some of which possibly I’ve read and used in the past.

I’ve developed a web application in www.erp58.com where you can:

A) Upload content that is currently in your blog, and redirect this content to your blog to get more visits to your blog.
B) If you are a good developer for EBS, you can sell some of your code in the site, because it works also as a MARKETPLACE.

You can see the requirements for the code for sale here: www.erp58.com/help.jsp#reqfordevelopers
… and our fees here: www.erp58.com/help.jsp#fees
and how to upload here: www.erp58.com/help.jsp#fordevelopers



I’ve already uploaded an example from your blog, the solution with id. 1000120, just as an example to show how this would work and the link to your blog. Please do not hesitate to contact me if you want me to delete this article and the link.

I hope these are interesting news for you.

Please, contact me for further information in juanjesusmontero@hotmail.com

Best regards.

Juan Jesús.
Spain. Madrid.

Mobile Tech said...

It is not displaying any message after editing email and saving the form.

Mobile Tech said...

It is not displaying any message after editing email and saving the form. Plz help