Insignificant Payroll Run Results

Oracle Payroll process posts lot of data to database for each payroll run. This data grows exponentially as number of employees & number of payroll runs grow. Generally pay_run_results and pay_run_result_values are the tables which take most of the data during payroll run. As these tables grow, they start to cause performance degradation in processes/queries involving these tables. Oracle provides couple of different options to keep number of rows to minimum in these tables:

1. Use "Suppress insignificant indirect results " pay action parameter to not create any zero or null indirect results. Oracle also provides a patch "5744549" to purge existing null or zero indirect results. I have seen around 17% savings in pay_run_results table and 26% savings in payr_run_result_values table after implementing this solution.



2. You can also enable sparse matrix for specific legislation to not create some null indirect or direct run results. Oracle also provides a process to purge existing data eligible to to be purged by sparse matrix option. This process mainly supresses null data rows. I have seen additional 26% savings in pay_run_result_values table

Overall implementing one or both options will reduce payroll run results and slows down the growth of the tables over the time. By implementing both solutions, i have seen 17% savings in pay_run_results table & 45% savings in pay_run_result_values table.

Unique Person Number in Oracle HCM

I almost wanted to start this posting as "Unique Employee Number in Oracle HCM", then i thought of non-employees such as applicants and contingent workers, then decided to put "Unique Person Number in Oracle HCM" as posting title. Anyway, every enterprise wants to identify their people with some unique value. For US companies, SSN (or National Identifier) might work fine since it is unique for people who are living in US. This won't work for lot of Non-US companies since SSN (or something similar) concept doesn't exist in lot of countries. Even if it exists there might be local legislation which might prevent companies from using national identifier as key field for data exchange.

In Oracle HRMS, we have few other fields, which we might be able to use to identify people uniquely depeneding on the person type. Below are three fields which are most commonly used to identify uniquely based on person type:

1. Employee Number => For employees
2. Applicant Number => For applicants
3. Contingent Worker Number => For contractors

All of the three numbers mentioned above work fine in simple terms. But in a real world complex enterprise environment there are lot of issues using these numbers. Before we get into details, let's look at how these numbers get generated.

Number Generation Process (Traditional Way):

In Oracle HCM, employee number, applicant number and contingent worker number can be generated manualy or automatically. This setup is done at business group level and you can control the number generation mode (manual or automatic) for each type of number (employee, applicant, contingent) separately. If you have more than one business group, you need to set this number generation mode individually for each business group.

Since i mentioned business group, let me say few words about business group concept in Oracle HRMS. Having people in two business groups is pretty much equal to people living on two different planets. On a clear day you might have a shot at spotting another planet once in a while, but in Oracle HRMS you won't have a clue about people in other business groups (Let's hope this will change!!).

Coming back to unique number generation, if you decide to put automatic number generation, you can control the starting number for each number type in each business group separately. This functionality seems like very flexible option, but most of the time it creates problem when you have more than one business group. If you don't plan your number generation starting points properly, you will easily step into other person types in the same business group or in a different business group. The starting value for employee, applicant, contingent worker numbers are maintained in PER_NUMBER_GENERATION_CONTROLS table.

Following are some important drawbacks with this approach:

In an enterprise, a person can start as one person type and move to any other person over the period of time. For example, person can start as applicant and move to contingent worker or employee. In this case, this person could get three different numbers for employee, contingent, applicant records. In this scenario, if the person remains in the same business group, internally PERSON_ID will remain same to link all these different types of records, but this PERSON_ID is not really help the end user, who is working with actual data.

This problem becomes worse if person moves between business groups. An employee moving from one business group to another business group gets another new employee number since the employee number sequence is different from business group to business group. To make the situation ugly, the PERSON_ID also changes when a person moves between business groups.

Above mentioned situations get messier and messier, if the person keeps on change the person type and business groups over the period.

Number Generation Process (Elegent Way):

Most elegent and simpler way to tackle the problems mentioned about is to use fast formulas to generated employee numbers. Oracle provides following three fast formulas for generating nubmers for each type of persion:

• APL_NUMBER_GENRATION [For Applicants]
• EMP_NUMBER_GENERATION [For Employees]
• CWK_NUMBER_GENERATION [For Contingent Workers]

If any of these formulas are setup, Oracle will use them automatically to generated numbers. Following are some high-level steps to setup these formulas to generate one unique sequentional number for a person in Oracle HCM:

• Setup a database sequence with increments of 1
• Use this same sequence in all three fast formulas so that you always get a unique number irrespective of type of person
• To check to see wheter person is getting transferred from one person type to another [Either within or across business groups], use the PARTY_ID input value.
• If PARTY_ID is populated, you need to return existing number instead of generating a new number.

By following this approach, you will generate a new number and assign it to the person if he/she got entered in to HCM system for the first time [Irrespective of person type]. You will reuse the existing number [Either from employee or applicant or contingent worker], in case if the person is trasnferring from person type to person type.

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.

Integration Repository

If you are working with Oracle Applications for a while, probably you remember the days when you were desperately looking for an API you could use for an interface, conversion, or a custom program. APIs make life easier to integrate Oracle Applications with other systems. The problem is that, finding the right API is sometimes challenging. Typically we search metalink or search the database or post a question to a news groups or call an expert friend who might know the answer... Let me tell you one thing, your life just got easier!!.
Now Oracle has something called Integration Repository, also known as "iRep". Keep in mind "i" is not for "Internet", it is for "integration". Thanks to Oracle for being very consistent on the naming conventions!!

What is Integration Repository?
Integration repository is a collection of publicly callable APIs, interface tables, and integration points (also know as "touch points") across all modules of Oracle E-Business Suite. These APIs & Integration points help customers integrate Oracle E-Business Suite with other existing systems. As per Oracle, below is the definition of iRep:
"The Oracle Integration Repository is a compilation of information about the numerous service endpoints exposed by the Oracle E-Business Suite of applications. It provides a complete catalog of Oracle E-Business Suite's business service interfaces. The tool lets users easily discover and deploy the appropriate business service interface for integration with any system, application, or business partner."

Following are some of the integration points available in iRep:

• PL/SQL APIs
• Open Interface Tables
• Interface Views
• Concurrent Programs
• Java APIs
• Web Service
• XML Gateway Message
• eCommerce Gateway Message

Integration Repository organizes all these objects nicely by module so that you can browse them by the module you are interested. With iRep you can find lot of information about the API or Integration object without digging through the actual code. Following is some key information you can find about these integration objects on iRep:

• Functions available in API
• Source Code Location
• Version of the API/Object
• Details about input parameters and more...
  

Current State

Currently, for 11i10 version of E-Business Suite, Oracle is hosting the repository. You can access this repository at http://irep.oracle.com/. This web site is available for everyone (no need to have metalink access). Customers with prior to 11i10 versions, can also take advantage of the information on this site.

Below is a screen shot of employee related APIs from iRep web site:

This repository is integrated into the application with release 12i. With 12i, repository will also change to "SWAN" look & feel. Below is a screen print of iRep in release 12i.



Release 12.1

In release 12.1 of E-Business Suite, you can access the integration repository by assigning "Integrated SOA Gateway" responsibility to yourself. You will see one option called "Integrated Repository" under this responsibility to access the iRep.

Future State
Currently you might see some missing integration objects from this repository. Oracle is going to add more integration points to this repository as new releases come. But, the ultimate goal for this tool is to let customers add their own integration objects to the repository and publish them so that, other member of the project team can utilize these objects. Probably this is far away from reality, but at least oracle got good initial start.