on Monday, August 17, 2009

Changing Employee Number Generation from Manual to Automatic

In Oracle HR, once a Business Group has employee number generation set to manual, Oracle does not provide a
means of changing it to automatic.  Consider the following business requirements:

¤Convert legacy system into Oracle HR via API's
¤Keep current employee numbers unchanged
¤Provide automatic employee numbering for all new employees

These business requirements would require completion of the following

¤Establish Business Group with manual employee number generation
¤Convert legacy HR data via API's
¤Change employee number generation to automatic within Business Group

Since Oracle does not provide the ability to change from manual to automatic, a company's options are

¤Continue with manual employee numbering after conversion
¤Establish Business Group with automatic employee number generation prior to conversion, and allow API's to assign new "automatic" numbers to each employee

Neither of these options meets the company's business requirements.

The business requirements can be met by completing the following

¤Establish Business Group with manual employee number generation
¤Convert legacy HR data via API's
¤Determine a starting number for automatic employee numbering that is greater than the largest numeric converted employee number
¤Through SQL given below
UPDATE PER_NUMBER_GENERATION_CONTROLS
SET NEXT_VALUE = (The starting number from #3 above)
WHERE TYPE = 'EMP'
AND BUSINESS_GROUP_ID    =
(The organization_id from hr_organization_units )


UPDATE HR_ORGANIZATION_INFORMATION
SET ORG_INFORMATION2 = 'A'
WHERE ORG_INFORMATION_CONTEXT =
'Business Group Information'
AND ORGAINZATION_ID = (Same as business_group_id from above SQL)