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:

• Updating EBS data from APEX using APIs (1)
• Querying EBS data from APEX using Views
• E-Business Suite and APEX integration (overview)
• E-Business Suite and APEX installation
• APEX and E-Business Suite integration (OBUG event)