Monday, February 06, 2012

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


Paolo Marzucco said...

Hi Dimitri,
I am developing Apex applications integrated with the eBs for Popay since 2006 and I find it simpler to create a view that contains all the data that I want to change or display in Apex and then create an INSTEAD OF trigger attached to that view. Inside the trigger, for each insert/update/delete operation I just call a package with the corresponding API wrapper procedures (similar to your examples) and a good amount of logging.

The advantage of using a view with an INSTEAD OF trigger is that you can:
1) completely hide the API complexity (including the necessary DateTrack logic) in a wrapper package;
2) seamlessly take advantage of all the Apex goodness (wizards, Automatic row processing, etc.).

Just one warning: make sure to disable the automatic row locking feature in Apex (using FSP_DML_LOCK_ROW), because the eBS APIs will try to do their own locking as well.

Hope it's helpful,
Paolo Marzucco

Dimitri Gielis said...

Hi Paolo,

Thanks a lot for your comment.

My next post about integrating EBS and APEX is about using views and instead of triggers (this method is also described in the whitepaper).

I agree with you about the advantages, but I do see some disadvantages too which will I cover in the upcoming blog post.

I wasn't aware of the automatic row locking issue yet, so I will definitely check that out too.

Thanks again,

Unknown said...

Hi dear i done My MBA. and finding job regarding HR. I Like your post. it's really good. Thanks for sharing it. i currently study from this Human Resource Management. it's also provide good information regarding MBA student specially from HR depart.

Anonymous said...

Thanks Dimitri,

This is a good piece

Kehinde Adeyemi

marees waran said...

Hi Dimitri,

I like your post.. Its not load to base table use this api for apex. Its taken long time process but not loaded.. error not show. any ideas please do needful