Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

Contact Us

Oracle HRMS Payroll Migration
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 
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_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_id
  INTO x_business_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_id = x_business_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_id         => x_business_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;
 Image

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


Anil Passi

Comments   

0 #1 Sunil 2006-11-19 00:00
I appreciate this. Pls is it possible to have your email id. I have got some other questions to ask you. eg Fast formular

man ny thanks
Quote
0 #2 Sunil 2006-11-19 00:00
I appreciate this. Pls is it possible to have your email id. I have got some other questions to ask you. eg Fast formular

man ny thanks
Quote
0 #3 Anil Passi 2006-11-20 00:00
Hi Sunil

Email me on

Thanks
Ani l
Quote
0 #4 mukesh 2006-12-15 00:00
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.

Rgds
Mu kesh
Quote
0 #5 Supriya 2006-12-22 00:00
Hi Anil,

I am trying to upload employee data into per_all_people_ f and per_all_assignm ents_f using hr_in_employee_ api and hr_in_assignmen t_api for India Localization. On using create_in_emplo yee a default primary assignment is created automatically. Now how do I update the primary assignment created by hr_in_employee_ api.

In hr_in_assignmen t_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.

Regards,
S upriya
Quote
0 #6 venkat 2007-02-05 00:00
Anil,
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

thanks
venkat
Quote
0 #7 Anil Passi 2007-02-06 00:00
.
.
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.


Than ks,
Anil Passi
Quote
0 #8 chunnu 2007-03-08 00:00
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?
Quote
0 #9 devi 2007-03-09 00:00
hi anil,
im new to oracle HRMS, could you please send me any interface examples with the validations, to practice
Quote
0 #10 seema 2007-03-14 00:00
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?
Quote
0 #11 seema 2007-03-16 00:00
Hi Anil,
Thanks for the early response.I did understand the use of API but a little confused about the sequence of operation.
Con sidering 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_p eople_f) using hr_employee_api .create_us_empl oyee.

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
--seema
Quote
0 #12 Debbie 2007-03-21 00:00
Hi
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
thank s
Debbie
Quote
0 #13 Anil Passi 2007-03-24 00:00
Chunnu

Pleas e post the error message that you are getting.

Tha nks,
Anil
Quote
0 #14 Anil Passi 2007-06-04 00:00
Indeed for high volumes, you can use data pump

Refer to Metalink White paper Note 72564.1

This also contains examples for Data Pump

Thanks
Anil Passi
Quote
0 #15 Pravin 2007-06-16 00:00
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.

Th anks in advance

Prav in
Quote
0 #16 sreenivas 2007-06-26 00:00
Iam new to Oracle HRMS.Can you explain me an US employee(New hire) creation through backend and as wellas front end
Quote
0 #17 Shivakumar 2007-09-07 21:51
Hi Anil,
Name the diferent API's we used in Core HRMS and Payroll systems.
Thanks
Quote
0 #18 Krishna Juturi 2007-09-28 10:23
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,
Krishn a
Quote
0 #19 avni 2007-10-09 11:12
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..rig ht.
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
Quote
0 #20 Anil Passi 2007-10-09 11:25
Hi Avni,

Ans 1
----------
Ye s thats correct, you will have to update the default assignment.

An s 2
----------
Th is will be driven by your business requirement. In case you are migrating data, there must be some equivalent column/attribut e in the legacy data to indicate which assignment is primary.


Furt her related info
---------- -------------
S econdary assignments can be created using API as below [use the API specific to your localization]
hr_assignment_a pi.create_gb_se condary_emp_asg

Also, you can alter the primary flag on assignment, by calling below API
hr_assignment_a pi.set_new_prim ary_asg(p_valid ate => p_validate,
p_effective_dat e => :p_effective_dat e,
p_person_id => :p_person_id,
p_assignment_id => :p_assignment_i d,
p_object_versio n_number => :p_object_versio n_number,
p_effective_sta rt_date => :p_effective_sta rt_date,
p_effective_end _date => :p_effective_end _date);


Thanks,
Anil Passi
Quote
0 #21 avni 2007-10-09 11:45
Thanks Anil
Quote
0 #22 kp 2007-10-16 05:58
Hi Anil
I have one question regarding the API's. Why we have 2 update procedures in HR_ASSIGNMENT_A PI. Like HR_ASSIGNMENT_A PI.UPDATE_EMP_ASG and HR_ASSIGNMENT_A PI.UPDATE_EMP_ASG_CRITERIA ?
Quote
0 #23 AK 2007-10-19 00:59
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_TERM INATION_DATE" column in per_periods_of_ service).
Keep up your good work
Thanks for your help. highly Appreciated.
Quote
0 #24 Saira B. 2007-11-21 09:16
Anil
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.

Rega rds

- Saira
Quote
0 #25 pran 2007-12-11 14:45
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.

thanks
Quote
0 #26 Amir 2008-01-24 00:30
Hi Anil,

I am trying to update an employees salary using HR_MAINTAIN_PRO POSAL_API.updat e_salary_propos al. 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,
Vamsi.
Quote
0 #27 Buvi 2008-02-26 06:17
Hi,
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?
Quote
0 #28 Ameetha 2008-03-03 14:33
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_assignm ents_f and per_periods_of_ service are in Sync.

Regards,
Ameetha
Quote
0 #29 Durvesh 2008-05-11 06:44
hr_person_api.d elete_person to delete the person
Quote
0 #30 Mohamed Ibrahim 2008-05-29 07:01
Dear Anil,
Could you tell me if there is an API that take user_id and return employees that belong to this user ??
i wait your answer,
thanks in advance,
Quote
0 #31 Anil Passi 2008-05-29 09:19
You can try wf_directory api or do select employee_id from fnd_user where user_id=9999, where 9999 may be the userid
Quote
0 #32 zakkam 2008-06-20 03:28
plz tell me that i want up load at a time 100 employee in to hrms, and have that data in a excel sheet
format it is very urgent plz help me
Quote
0 #33 graeme 2008-09-23 17:12
that api is great for creating an employee. what about updating some details such as email_address? i can't find anything.
Quote
0 #34 abhilasha 2008-10-22 12:17
Hello Anil,
I need some urgent help from you please, am leaving to India this weekend and have to finish this API task before I leave.I'm no too familiar with HR ,I know its very complex mainly because of date tracking functionality.

My question is I need to write an API that would replace the existing manual process in place :

HR once a year manually populate the Assignment screen the Descriptive ff (salary Manager Descriptive FF --> ASS_ATTRIBUTE10 column of Per_assignments _f table).
I need to write an API that can up upload that field from the spreadsheet (Spread sheet provides Employee Number, Salary Manager and Effective Date information and with that information the API should be able topopulate the descriptive ff column).

Effec tive Date is the key when writing the API.Based on the effective date ,the API must be able to "update" and not "correct" the existing record-- this will create a new assignment starting on the effective date of the change.In addition ,if there is a new assignment record that began after the effective date of this change,the program must be able to"Insert" a new assignment record and not "Replace" any future records.

Thank s a lot for your help ,I just have two days to finish this ,and have minimal knowledge on HR tables.Your help would be greate appreciated.
Quote
0 #35 Alvaro Quinones 2009-07-14 12:41
Hello Anil:

There is any way of updating an emloyees salary.

Thanks.
Quote
0 #36 Shimin 2009-12-23 22:02
I'm new to HR, This website is very helpful . Thanks alot.
Quote
0 #37 Ahmad Ikram 2011-01-01 13:29
Dear Anil

Thanx for sharing such a valuable information. I'm new to HRMS. I want to load employees data which is in Excel into Oracle HRMS. Please guide me how to migrate data from Excel into Apps.

Thanx
Quote
0 #38 Vivian 2011-03-02 16:16
Hi Anil,

I have written a script to unenddate secondary assignment. The assignment is not updating - i'm using Hr_Assignment_A pi.UPDATE_US_EM P_ASG. Am I using the wrong api?
Also, I send you my script via email.

Thanks for all your help,
Vivian
Quote
0 #39 APPSGEEK 2011-06-15 10:44
Hi Anil,
I appreciate you help on many topics.
I am struck on a question , How to create a leaver using the API.
We are in a Migration project. we need to load the leavers data too.
Thanks in advance.

Regar ds,
Pavankumar.
Quote
0 #40 Amal 2011-12-29 12:35
Hi anil,
Can you help me how to correct the employee hire date from front end application actualy I am trying to do it but I get following error APP-PER-449825: cause you cannot change the original Hire Date of this employee as life events associated with this employee exist either in the started processed or detected status
Action run the back out life event program for the processed event and back out the other event to change the original hire date
Note that all payroll process have been rollback for this employee
Waitin g for your feedback
Regards
Amal
Quote
0 #41 Deepesh Aravandekar 2012-01-19 01:36
Hi Anil,

While running this API in 11i data was successfully loaded but while running in R12.1.3 i am getting following error:

ERROR at line 1:
ORA-20001: You are no longer permitted to enter a number for this person.
Please close and reopen the application window you are using. If you are using
an API then please rerun the script that calls the API. Doing this enables the
application to generate a sequential number automatically.
ORA-06512: at "APPS.HR_UTILIT Y", line 939
ORA-06512: at "APPS.PER_ALL_P EOPLE_F_ARIU", line 216
ORA-04088: error during execution of trigger 'APPS.PER_ALL_P EOPLE_F_ARIU'
O RA-06512: at "APPS.HR_EMPLOY EE_API", line 1034
ORA-06512: at line 18


Can you please suggest any solution me for the same.

Thanks for your support in advance.

Regar ds,
Deepesh A
Quote
0 #42 MIRAJ PATEL 2012-09-03 08:05
Hi Anil,
while running in R12.1.3 i am getting following error:

ORA-20001: You are no longer permitted to enter a number for this person.
Please close and reopen the application window you are using.
If you are using an API then please rerun the script that calls the API.
Doing this enables the application to generate a sequential number automatically.
ORA-06512: at "APPS.HR_UTILIT Y", line 939
ORA-06512: at "APPS.PER_ALL_P EOPLE_F_ARIU", line 216
ORA-04088: error during execution of trigger 'APPS.PER_ALL_P EOPLE_F_ARIU'
Quote
0 #43 MIRAJ KUMAR PATEL 2012-09-03 08:11
Hello Anil

while running API(hr_employee _api.create_emp loyee )in R12.1.3 i am getting following error:

ORA-20001: You are no longer permitted to enter a number for this person.
Please close and reopen the application window you are using.
If you are using an API then please rerun the script that calls the API.
Doing this enables the application to generate a sequential number automatically.
ORA-06512: at "APPS.HR_UTILIT Y", line 939
ORA-06512: at "APPS.PER_ALL_P EOPLE_F_ARIU", line 216
ORA-04088: error during execution of trigger 'APPS.PER_ALL_P EOPLE_F_ARIU'
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Aug 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
        1
  2  3  4  5  6  7  8
  9101112131415
16171819202122
23242526272829
3031     

Enquire For Training

Related Items

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner