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:
1. Convert legacy system into Oracle HR via API’s
2. Keep current employee numbers unchanged
3. Provide automatic employee numbering for all new employees

These business requirements would require completion of the following:
1. Establish Business Group with manual employee number generation
2. Convert legacy HR data via API’s
3. 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:
1. Continue with manual employee numbering after conversion
2. 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:
1. Establish Business Group with manual employee number generation
2. Convert legacy HR data via API’s
3. Determine a starting number for automatic employee numbering that is greater than the largest numeric converted employee
number
4. Through SQL*Plus run:
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 of the Business Group in question)
5. Through SQL*Plus run:
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)

Return to : Oracle Database, SQL, Application, Programming Tips