Sunday, January 10, 2010

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]
  • 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.


  1. Evosys’ HCM practice is an experienced team of Oracle practitioners with depth and breadth across the suite of Oracle Human Capital Management modules.

    About Evosys: Evosys is a leading Oracle Partner providing customer focussed solutions for all your enterprise resource planning needs.We are a process-centric organization that believes in developing and delivering innovative solutions to the most advanced automation and integration problems of our customers.

    for any Oracle Services, Business Partnerships, Sales Referrals, Collaborations & Tieups, please contact


  2. Hello-
    I am trying this solution, for some reason the value for PARTY_ID is coming to 0 in the fast formula, and pl/sql function.
    Any idea why this is 0?

  3. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer
    LinkedIn profile -
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at | +91 - 9581017828.