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

 

R12 Patch 5612820 Available: Changing the default layout format between RTF/PDF/Excel when submitting XML / BI Publisher Concurrent Request

In a previous post I provided a temporary solution for the issue where the default value for the Output Format of a BI Publisher based concurrent request was hardcoded to PDF.

• Changing the default layout format from PDF to Excel using Profile Option and FNDRSRUN Form Modification - Submitting BI Publisher Report Request

For those those customers lucky enough to be on Release 12 I'm glad to say Oracle has provided a patch for this enhancement, the base issue documented in Metalink Note 401328.1 or Bug 5612820 or Bug 5036916:

• R12 Patch 5612820 DEFAULT OUTPUT FORMAT ATTRIBUTE AT TEMPLATE LEVEL IS REQUIRED

This patch is included in R12 Release Update Pack 12.0.6, but as a note at the time of writing 5612820 is available on controlled release ... not sure of the reason since its in 12.0.6. The code base required for applying 5612820 is:

• R12.AD.A.delta.4
• R12.ATG_PF.A.delta.4

For those people on Release 11i, unfortunately you'll have to wait a little longer ... still awaiting the 11i version of the patch.

Here's a screenshot of the new Default Output Format field on the Templates page.

And verification that the default output format is indeed working...

Nice!

Enhancing the Oracle eBusiness Suite 11i/12 Homepage Menu via Firefox, Greasemonkey, XPath, Javascript & DOM: aka Foxiness Menus

Update: 12-Nov-08 Extended script for Release 12

The Background

One of the most user-unfriendly and neglected aspects of the eBusiness Suite in my opinion is the homepage. No sooner than you arrive there you really just wanna get out, and get out fast! The majority of people I know, including myself, do one of the following:

• Save one function from each of your primary responsibilities as a "Favorite" and thereafter only use the "Favorites" area of the homepage
• After choosing the responsibility just blinding pick the first "Forms" based menu entry so that you get into the forms navigator menu. At least for any responsibility that still has a majority of forms.

One of the aspects that I've desired for a while is a tree based Responsibility menu structure. Now Oracle does provide this, but when I last checked, admitedly a few years ago, it required Oracle Portal integration. As a bit of a refresher, there is a profile option called "Self Service Personal Home Page mode" which used to be able to be set to "Personal Home Page" and then clicking on the responsibility went straight into forms for a Forms based application.

But from 11.5.10, "Self Service Personal Home Page mode" must be set to "Framework Only" and hence you now have an extra couple of mouse clicks to get to where you want to go. At least EBS Release 11i/12 has show/hide responsibilities.

Where is all this going you ask? Well, for a bit of late night entertainment ... sad I know ;-) plus a bit of experimentation, considering Firefox's 4th Birthday was just a couple of days ago, and since I'm now comfortable using Firefox with EBS, I've created a Greasemonkey script to give a smidgen of intelligence to the Framework homepage Responsibility menu.

Introducing the Virtuate Oracle EBS Homepage Menu enhancement Greasemonkey Script!

So, what does this do? Well it turns this:

Into this:

With a quick video here ... apologies if its a bit big:

How do I install it?

Assuming you have Firefox and Greasemonkey, just click on this UserScripts.org link and then click the install button! If you have any hassles, you're more than welcome to fix the code on UserScripts.org (or let me know)! Open Source rocks!

Requirements

• Firefox
• Greasemonkey
• EBS Release 11i or Release 12
• Will be very grateful if someone turns this into an IE7Pro User Script in order to get it working with Internet Explorer!

References

• Is The 'Personal Home Page' Mode Supported In Oracle 11i Applications?

Related Posts

• Firefox with eBusiness Suite: Oracle JInitiator Plugin does not start - always get "must install plugin", Vista, Patch 5397653

 

Revisited: Why is mod_plsql not supported with the Oracle eBusiness Suite Release 12? Fusion Crossroads #1

In a prior post I raised the question of why mod_plsql was not supported with the eBusiness Suite R12. Excellent to see that Oracle was listening to the community and as Steven Chan's post highlights, Metalink Note:726711.1 has been created to address this question.

In summary you can still run mod_plsql connecting to the eBusiness Suite as long as the Apache/mod_plsql are in an isolated install and you address security and associated considerations. And of course as a customization it is not supported under the guise of the eBusiness Suite ... but hey your customizations never are!

Guess that means the same for the other sweet tools you'd connect/integrate to your eBusiness Suite ... like Application Express (APEX), PHP/Zend, Ruby on Rails, etc.

Secure storage of passwords in Oracle Applications via Encryption of Profile Option Values using dbms_obfuscation_toolkit and Forms Personalization

Have you ever had the need to store a password for accessing external systems in an Oracle Applications field such as Profile Option Values? In a few instances I've seen plain text passwords stored in Profile Option Values both at Site or User levels for scenarios such as connections to external FTP servers and the like. This raises a number of security concerns:

1. Anyone with System Administrator access can see the passwords, or worse:
2. Anyone with access to Profile Option Values can see the password(s) if the "visibility" on the profile option is not configured appropriately
3. Anyone with "read" access to the base table e.g. fnd_profile_option_values - at the database level can select the value from the table
4. Anybody watching over your shoulder can see the password as you enter it!

Well, now those prying eyes can be kept at bay, with a mix of dbms_obfuscation_toolkit, hex/raw conversions and Forms Personalization trickery. This is especially nice considering that now the Oracle Applications User passwords are also able to be made secure.

Our goals here are:

• Provide a way to securely input a password on the Oracle eBusiness Suite User interface using User Level Profile Option Values
• Encrypt the password on save
• Convert encrypted password to hex and for more friendly viewing on screen if necessary
• Ensure the encrypted hex version of the password has specific content so we know what is an encrypted value and what isn't
• Ensure when the User is entering the password it can't be seen on screen
• Provide a secure decryption routine for backend processes

Here's how we achieve this:

1. A PL/SQL package providing the encrypt and decrypt routines including the hex conversion with a marker so we know what is an encrypted password. The decrypt routine will only work for the "APPS" user.
2. A Profile Option to store the password
3. A Forms Personalization to call encryption routine, overwrite the Profile Option value, plus conceal the data entry field. Note custom.pll could be used for installations prior to 11.5.10.

Okay, so lets get to it.

1. PL/SQL Package.

I'll just provide an extract of the key psuedo code here, if anyone wants a sample package see my profile for contact information.

function encrypt ...
  l_enc := dbms_obfuscation_toolkit.desencrypt( input_string => p_value_in_multiple_of_8_bytes, key_string => my_private_key);
  return rawtohex(utl_raw.cast_to_raw(l_enc));
...
end encrypt;

function decrypt ...
  if user = 'APPS' then
    l_str := utl_raw.cast_to_varchar2(hextoraw(p_value));
    l_str  := dbms_obfuscation_toolkit.desdecrypt( input_string => l_str, key_string => my_private_key );
  end if;
  return l_str
...
end decrypt;

2. Profile Option

Navigate to Application Developer > Profile

3. Forms Personalization

Setup the "conceal" data entry when the row is for our password profile option

Setup the call to encrypt and overwrite the visible value

Copy visible value to backend column

4. Do the same Forms Personalization steps as above for the Personal Profile Values form (function FND_FNDPOMSV)

Sweet! All sorted.