Oracle HRMS API Employee People Migration

08/04/2013 Monday
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
          Nationality
          Ethinicity
          Sex
          Title
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.


DECLARE
  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;

BEGIN
  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
                                    );
COMMIT ;
END;

Now, lets check the results, by running the below SQL
SELECT person_id
      ,employee_number
      ,first_name
      ,last_name
      ,full_name
      ,date_of_birth
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