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

• Regular-Expressions.info - How to Find or Validate an Email Address
• Regular Expressions in Oracle Database 10g on OTN
• Using Regular Expressions With Oracle Database 10g Documentation
• Thanks to email address checks in Drupal, Wordpress and various other Oracle specific sources.

Related Posts

• BI Publisher EBS Bursting 101: A tutorial/case study including Bursting Control File to email Suppliers Remittance Advice in 1 easy step
• Top Ten and Favorite Posts
• Top 8 Uses for Help Diagnostics Examine in eBiz
• Top 8 eBiz Developer Hooks