Creating EBS data from APEX using APIs (2)

Previously we used the HR_PERSON_API to update the email address of a person.
In this post we will use the HR_EMPLOYEE_API to add a person.

The first thing to do is to look at the definition of the create employee call in the Oracle Integration Repository. (Login in EBS, in the navigator go to Integrated SOA Gateway, next go to Human Resource Suites > Human Resources > Employee and select Employee > Create Employee)

The second thing I tend to do is test the API in SQL Workshop in a PL/SQL anonymous block from a workspace linked to the APPS schema.

When I ran that block of code I got a ORA-01403: no data found error. I verified my call multiple times but I couldn’t see what I was doing wrong. Finally I send the statement to Thierry Vergult from Popay as I was out of ideas. He told me to try a trunc around sysdate for the parameter p_hire_date.
I verified the API again and p_hire_date should be a date, sysdate in my opinion is the most known date, so I was a bit sceptic at first… but I tried it nevertheless… and it worked!
This blew me out of the water! It showed me again that the API is full of surprises and whenever you hit an issue, ask a more experienced EBS person for ideas :-)

So once that was working I included the create procedure in my apex_api_pkg.

procedure create_person (
p_hire_date in date,
p_business_group_id in number,
p_last_name in varchar2,
p_first_name in varchar2,
p_date_of_birth in date,
p_sex in varchar2,
p_email_address in varchar2,
p_employee_number in out varchar2,
p_person_id out number,
p_assignment_id out number,
p_per_object_version_number out number,
p_asg_object_version_number out number,
p_per_effective_start_date out date,
p_per_effective_end_date out date,
p_full_name out varchar2,
p_per_comment_id out number,
p_assignment_sequence out number,
p_assignment_number out varchar2,
p_name_combination_warning out boolean,
p_assign_payroll_warning out boolean,
p_orig_hire_warning out boolean
)
is
l_employee_number varchar2(4000);
l_person_id number;
l_assignment_id number;
l_per_object_version_number number;
l_asg_object_version_number number;
l_per_effective_start_date date;
l_per_effective_end_date date;
l_full_name varchar2(4000);
l_per_comment_id number;
l_assignment_sequence number;
l_assignment_number varchar2(4000);
l_name_combination_warning boolean;
l_assign_payroll_warning boolean;
l_orig_hire_warning boolean;
begin
hr_employee_api.create_employee (
p_hire_date => p_hire_date,
p_business_group_id => p_business_group_id,
p_last_name => p_last_name,
p_sex => p_sex,
p_first_name => p_first_name,
p_date_of_birth => p_date_of_birth,
p_email_address => p_email_address,
p_employee_number => l_employee_number,
p_person_id => l_person_id,
p_assignment_id => l_assignment_id,
p_per_object_version_number => l_per_object_version_number,
p_asg_object_version_number => l_asg_object_version_number,
p_per_effective_start_date => l_per_effective_start_date,
p_per_effective_end_date => l_per_effective_end_date,
p_full_name => l_full_name,
p_per_comment_id => l_per_comment_id,
p_assignment_sequence => l_assignment_sequence,
p_assignment_number => l_assignment_number,
p_name_combination_warning => l_name_combination_warning,
p_assign_payroll_warning => l_assign_payroll_warning,
p_orig_hire_warning => l_orig_hire_warning
);
end create_person;

The finally step was to add a form on my page with the items and a process that calls the API.

The page process Create User looks like this:

Running the page shows you the create form.

Note that I didn’t make all fields visible, just the fields to get the proof of concept working.

In this post we called the API to create a person from within an APEX page. The catch here was to trunk the p_hire_date parameter.

Previous related posts:

Creating EBS data from APEX using APIs (2)

Comments