REGEXP_REPLACE for HR Data Formatting

Regular expressions are good utilities to search & edit free form text data. According to Wikipedia, following is the definition of regular expression.

"In computing, regular expressions provide a concise and flexible means for identifying strings of text of interest, such as particular characters, words, or patterns of characters. A regular expression (often shortened to regex or regexp) is written in a formal language that can be interpreted by a regular expression processor, a program that either serves as a parser generator or examines text and identifies parts that match the provided specification"

This article specifically talks about usage of REGEXP_REPLACE "Regular Expression Replace" function for formatting some of the formatting needs of Oracle HR data. Below are some real life scenarios for which REGEXP_REPLACE can be put to good use.

Phone Number Formatting

In Oracle HR phone numbers fields are free form fields. That means HR user or emploee or manager can enter the data in any format they like, such as (123) 456 7890, 123-456-7890, 1234567890 ext:20 etc., When we want to report or send this data in an interface in a specific format, it really becomes challenging. Normally without regular expression, you can use traditional REPLACE function to tackle the problem as shown below:

select replace(replace(replace(phone_number,'-'),'('),'.') from per_phones;

This method works, if you have few characters to replace, but as the non-numeric characters increase this SQL could get ugly pretty fast.

Using REGEXP_REPLACE, you can solve the problem very easily. For formatting purpose all we want is extract all numeric values and ingnore all non-numeric values. Below is the simple SQL we can use to achieve this:


select REGEXP_REPLACE(phone_number,'[^[:digit:]]') from per_phones

Above SQL replaces all non-numeric characters with null and returns the value. Once you the number you can format it any way you want.

Address Line Formatting

Another common requirement i see on Oracle HR data is, address line formatting. Lot of time when we send the address data in interfaces, some vendors want us to takeout all punctuation and send the data. You can use REPLACE function to accomplish, but that gets complicated since you have to use REPLACE for each character. Below is the SQL using REGEXP_REPLACE to take out all punctuations from address lines.


select REGEXP_REPLACE(address_line1,'[[:punct:]]')
from per_addresses;

Global Security Profiles

Overview:

Security profiles are one of the most key functions of Oracle HCM suite. Security profile controls access to people records, when user logins to HCM applications. Security profiles can be created based on Organizations, Locations, and many other attributes of person record to define the access. Once the security profile is created, it is attached to a responsibility to enforce the security access. You can access security profile forms under "Security" section of Super HRMS Manager responsibility as show below.

Traditionally, users had option to create local security profiles only. That means, when user creates a security profile that automatically restricts people access to one business group only. With this method, you have to create atlease one security profile for each business group to complete the security setup. As shown below, if you have manufacturing people in Columbia & Sweden business groups, you have to create two security profiles to provide access to employees from both business groups.

Once these security profiles are created, user needs to attach these security profiles to both Sweden & Columbia HR responsibilities.

This method is fine as long as you have one or two business groups. As number of business groups increase, maintanance of security profiles becomes complicated due to high number of security profiles. If you need to make slight change to one (such as manufacturing group as shown above) security setup, you need to find these security profiles in all business groups and make the necessary changes to keep these profiles in sync. This becomes adminstrative nightmare as number of business groups and profiles increase.

To mitigate the administration effort, Oracle introduced global security profiles. Using this option, you can create one security profile which spans across all business groups or more than one business group, thus reducing the number of security profiles. For above given example, instead of creating security profiles for Sweden & Columbia, you can create one global security profile as shown below.

Once global security profile is created, user can attach the same global security profile to both Sweden & Columbia responsibilities. With this we reduced number of security profiles to one, which represents complete manufacturing division across all business groups.

One thing i would like to clarify is that, using global security profiles does not reduce the number of responsibilities, it only reduces the number of security profiles. User still needs to create one responsibility for each business group so that user can access people from each business group. Even though global security profile contains people from more than one business group, when user access the data in HR application using specific responsibility, he/she can only access people from business group which is attached to the responsibility.

Reporting:

If you are using Discoverer or custom reports for end user reporting, with some customization you can take advantage of global security profiles to report across multiple business groups. Normally to run reports in multiple business groups, user needs to switch responsibilities to run the report in each business group, extract the data, and combine it to complete the report for analysis or printing. With global security profiles, you can eliminate the issue with some customization on reporting side to make end user life easier.

Final Thoughts:

Following are some high level advantages of global security profiles:

1. Reduce number of security profiles
2. Simplify changes to security profiles
3. Accomodate global reporting across business groups

Hope this article is useful. Your feedback is most welcome.