Skip to content



Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
This website has now moved to

Oracle HRMS API Employee People Migration | Print |  E-mail
Written by Anil Passi   
Monday, 13 November 2006
In this article, I will explain how to migrate or interface Employees / People records in Oracle HRMS application.

This article uses the GB (United Kingdom) verion of the API. The name of this API is hr_employee_api.create_gb_employee.

There is a bit of uncertainty whether fusion will use Oracle HRMS or Peoplesoft HRMS & Payroll.
Yet, most of the readers request me to write about Oracle HRMS.

First lets begin with questions and answers....

Question : Where is people data stored in Oracle HRMS?
Answer : It is stored in table named per_all_people_f

Question: But there is a table named per_people_f too?
Answer : WRONG. Per_people_f is a view on top of per_all_people_f. This view filters the list of records from  per_all_people_f.
This filtration happens in the where clause of view, based on security profile of the responsibility being used by user.

Question : What then is per_people_x?
Answer : This is a view on top of per_people_f, and it displays only those date tracked records that are effective as of sysdate.

Lets say you wish to create a person record of following data
Last name : Passi
First name : Anil
Title : MR.
NI Number : PX374383D
Date of birth 12-jan-1982
Person type : Employee
Employee number 90909090

Please note the following:-
1. This example demonstrates creation of Employee named Anil Passi with Employee Number 90909090.
In your case, you will be looping through the Legacy data and calling the below Oracle HRMS API to create Employee.
2. You may need to map following codes of Legacy system with values in Oracle HRMS
3. This migration activity will most probably be followed by creation or migration of Assignment records.
4. Copy past the code below, and run in your environment to see this work. However, do not forget to change the business group name.
5. For non-UK implementers, you will need to use non GB version of the API.

  x_emp_num VARCHAR2(200) := '90909090' ;
  x_business_group_id INTEGER;
  x_person_type_id    INTEGER;
  x_validate_mode     BOOLEAN := FALSE; 
  x_person_id INTEGER ;
  x_assignment_id INTEGER ;
  x_per_object_version_number NUMBER;
  x_asg_object_version_number NUMBER;
  x_per_effective_start_date  DATE;
  x_per_effective_end_date    DATE;
  x_full_name                 VARCHAR2(300);
  x_per_comment_id            NUMBER;
  x_assignment_sequence       NUMBER;
  x_assignment_number         VARCHAR2(10);
  x_name_combination_warning  BOOLEAN := FALSE;
  x_assign_payroll_warning    BOOLEAN := FALSE;
  x_orig_hire_warning         BOOLEAN := FALSE;

  SELECT business_group_id
  INTO x_business_group_id
  FROM per_business_groups
  WHERE NAME = '<<Your Business Group name or Setup Business Group>>';

  SELECT ppt.person_type_id
  INTO x_person_type_id
  FROM per_person_types ppt
  WHERE ppt.business_group_id = x_business_group_id
  AND ppt.user_person_type = 'Employee';

  hr_employee_api.create_gb_employee(p_validate                  => x_validate_mode
                                    ,p_hire_date                 => SYSDATE -- In this case
                                    ,p_business_group_id         => x_business_group_id
                                    ,p_last_name                 => 'Passi'
                                    ,p_sex                       => 'M'
                                    ,p_person_type_id            => x_person_type_id
                                    ,p_date_of_birth             => '12-JAN-1982'
                                    ,p_employee_number           => x_emp_num
                                    ,p_first_name                => 'Anil'
                                    ,p_known_as                  => ''
                                    ,p_marital_status            => ''
                                    ,p_middle_names              => ''
                                    ,p_ni_number                 => 'PX374383D'
                                    ,p_previous_last_name        => ''
                                    ,p_title                     => 'MR.'
                                    ,p_original_date_of_hire     => SYSDATE
                                    ,p_person_id                 => x_person_id
                                    ,p_assignment_id             => x_assignment_id
                                    ,p_per_object_version_number => x_per_object_version_number
                                    ,p_asg_object_version_number => x_asg_object_version_number
                                    ,p_per_effective_start_date  => x_per_effective_start_date
                                    ,p_per_effective_end_date    => x_per_effective_end_date
                                    ,p_full_name                 => x_full_name
                                    ,p_per_comment_id            => x_per_comment_id
                                    ,p_assignment_sequence       => x_assignment_sequence
                                    ,p_assignment_number         => x_assignment_number
                                    ,p_name_combination_warning  => x_name_combination_warning
                                    ,p_assign_payroll_warning    => x_assign_payroll_warning
                                    ,p_orig_hire_warning         => x_orig_hire_warning

Now, lets check the results, by running the below SQL
SELECT person_id
FROM per_all_people_f
WHERE creation_date > SYSDATE - 1;

Now, lets have a look at this record from the Oracle HRMS People Entry Screen

Comments (28)add
written by Sunil , November 19, 2006
I appreciate this. Pls is it possible to have your email id. I have got some other questions to ask you. eg Fast formular

manny thanks
written by Sunil , November 19, 2006
I appreciate this. Pls is it possible to have your email id. I have got some other questions to ask you. eg Fast formular

manny thanks
written by Anil Passi , November 20, 2006
Hi Sunil

Email me on

written by mukesh , December 15, 2006
Hi Anil,

I am doing Employee assignment conversion. after your people migration.So I am updating default assignment but in some cases one person having more than one assignment also so how i can handle history recrods and multiple assignments.
I know that APIs for this but wanna know how hisotry record will be picked up or how i need to handle it.

written by Supriya , December 22, 2006
Hi Anil,

I am trying to upload employee data into per_all_people_f and per_all_assignments_f using hr_in_employee_api and hr_in_assignment_api for India Localization. On using create_in_employee a default primary assignment is created automatically. Now how do I update the primary assignment created by hr_in_employee_api.

In hr_in_assignment_api there are two procedure to create a secondary assignment or to update the primary assignment. I do not want to create a secondary assignment but in update procedure jobs, position, grade and payroll are not given. Then how do i go updating the primary assignment.

Your inputs would be higly appriciated.

written by venkat , February 05, 2007
I am starting the career in apps. I am having a generic question.How to know what are the mandatory parameters that are required to be passed for an api. Most of the times even though we pass all the parameters which are not defaulted, i am getting this error

written by Anil Passi , February 06, 2007
Hi Madhu

You are seeing a ORA error for passed parameters, which means the issue is with syntax rather than business validation.

Email me your script, I will have a look.

Anil Passi
written by chunnu , March 08, 2007
For non-UK implementers, you will need to use non GB version of the API.i am in US i want to know what does it mean?
written by devi , March 09, 2007
hi anil,
im new to oracle HRMS, could you please send me any interface examples with the validations, to practice
written by seema , March 14, 2007
Hi Anil,
i am new to oracle apps.
I had a doubt.
usually in migration or conversion,we have an interface table which we populate before the data is populate in base tables through open interfaces.rt?
here in people data migration which is the interface table,open interface?
is per_all_people_f the base table?
written by seema , March 16, 2007
Hi Anil,
Thanks for the early response.I did understand the use of API but a little confused about the sequence of operation.
Considering the case of multiple records to be populated ,correct me if i am wrong.
1.load data onto temporary table using sql loader .
2.load data from temporary table onto base table(per_all_people_f) using hr_employee_api.create_us_employee.

the confusion is:
1.Is this the sequence?
2. Dont we use any interface table here?

Please help me out on this.Every input of yours would be highly appreciated.

thanks in advance
written by Debbie , March 21, 2007
I am trying to move qualifications for staff from our legacy system to Oracle HRMS. How do I go about uploading:
a) Schools / Colleges
b) Qualification Types
c) qualifications
d) Schools and Colleges attended
written by Anil Passi , March 24, 2007

Please post the error message that you are getting.

written by Anil Passi , June 04, 2007
Indeed for high volumes, you can use data pump

Refer to Metalink White paper Note 72564.1

This also contains examples for Data Pump

Anil Passi
written by Pravin , June 16, 2007
Hi Anil,

I am working on a project where i have to copy the employee data which is available in SQL server database to the ORACLE HRMS. PLease let me know how i can do it at the earliest.

Thanks in advance

written by sreenivas , June 26, 2007
Iam new to Oracle HRMS.Can you explain me an US employee(New hire) creation through backend and as wellas front end
written by Shivakumar , September 07, 2007
Hi Anil,
Name the diferent API's we used in Core HRMS and Payroll systems.
written by Krishna Juturi , September 28, 2007
It is a good one. i learn new things from this.

Could you please provide the validations for DOB(Date of Birth) & Date of Join

Thanks & Regards,
written by avni , October 09, 2007
Hi Anil

Wat if i have more than one Assignment?Say i have three Assignments.Now the moment i'l create an Employee ,automatically an assignment wd be created for him,and i need to update this assignment..right.
But out of my three assignments how can i choose which assignment shoud be updated which two shd be created as secondary assignments..

Ur help wd be Appreciated
written by Anil Passi , October 09, 2007
Hi Avni,

Ans 1
Yes thats correct, you will have to update the default assignment.

Ans 2
This will be driven by your business requirement. In case you are migrating data, there must be some equivalent column/attribute in the legacy data to indicate which assignment is primary.

Further related info
Secondary assignments can be created using API as below [use the API specific to your localization]

Also, you can alter the primary flag on assignment, by calling below API
hr_assignment_api.set_new_primary_asg(p_validate => p_validate,
p_effective_date => :p_effective_date,
p_person_id => :p_person_id,
p_assignment_id => :p_assignment_id,
p_object_version_number => :p_object_version_number,
p_effective_start_date => :p_effective_start_date,
p_effective_end_date => :p_effective_end_date);

Anil Passi
written by avni , October 09, 2007
Thanks Anil
Question on API
written by kp , October 16, 2007
Hi Anil
I have one question regarding the API's. Why we have 2 update procedures in HR_ASSIGNMENT_API. Like HR_ASSIGNMENT_API.UPDATE_EMP_ASG and HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA ?

written by AK , October 19, 2007
Hi Anil,
one of my requirement in HRMS is to send an alert message to a group of users,
whenever user updates the "projected" column on end employment form.
(In HRMS the Navigation path is FASTPATH -> end employment)

i can create an "after update" event alert to achieve the desired result.which fires
when they update the record. but i need to send an alert only when they update the
"projected" column on the End employment form(IN HRMS fastpath->end employment form).
but not when they update other columns on the End employment form.

"After update alert" fires when they update the record. but how to identify whether
they modified the "projected" column on End employment form.
(which is "PROJECTED_TERMINATION_DATE" column in per_periods_of_service).
Keep up your good work
Thanks for your help. highly Appreciated.

written by Saira B. , November 21, 2007
Excellent startup article. I am a beginner with oracle apps & its architecture and currently working on an Oracle HRMS implementation. Your article has helped alot.

I would need some more insight to Data Conversion Analysis Exercise.


- Saira
excellent articles
written by pran , December 11, 2007
hi Anil,
I am new to HRMS. your website is very helpful.
Could u pls post in the API's for HR and payroll as well.

written by Vamsi , January 24, 2008
Hi Anil,

I am trying to update an employees salary using HR_MAINTAIN_PROPOSAL_API.update_salary_proposal. But I am getting ORA-20001: YOU CANNOT UPDATE AN APPROVED SALARY DATE.

Could you please let me know if there is any way of updating an emloyees salary.

Thanks In Advance,
written by Buvi , February 26, 2008
If any employee is terminated, I want to delete the records of him in UK Business Group and the same details should be deleted in INDIA BG also for that employee. Could u suggest me to which API I can use to do this?
How to verify the employee records in HR Tables ??
written by Ameetha , March 03, 2008
Hi Anil..
This is an excellent site. Really thank you for updating useful information for us.

I am working for HR module and i have a query..
How to know the data in the per_all_people_f , per-all_assignments_f and per_periods_of_service are in Sync.

You must be logged in to a comment. Please register if you do not have an account yet.