Updating EBS data from APEX using APIs (1)

I didn't expect to many technical issues using E-Business Suite (EBS) APIs in APEX as it's basically a call to PL/SQL packages.

Next to that the EBS APIs seems to be well documented. I first didn't realise, but for a long time I was using the Oracle Integration Repository for EBS R11 (whereas I'm using R12).

For EBS R12 the Oracle Integration Repository ships as part of the E-Business Suite. To access it, in the Navigator menu, select the Integrated SOA Gateway responsibility, then click on the Integration Repository link.

Using the Oracle Integration Repository I would have found it more useful if I could define the number of results (maybe you can, but I didn't find that setting). At the moment I get only 10 results at a time, which is too low for me.

I also found it not that easy to find the right API to use. I guess it comes by experience. Next to that, the parameters are not always the same, so the API could be more consistent.

For example I want to create and edit a person. In the navigator I went to Human Resources Suite > Human Resource. That was logic for me. Next I looked into the list and saw Employee, so that was a logic choice for me. In there I found the HR_EMPLOYEE_API. That API allows to create an employee. So far so good, but where is the edit? I couldn't really find it, until I asked a friend and he told me to look for person. So when I went to HR Person(1) in the navigator I saw the HR_PERSON_API and in there you find the update and delete of a person.

When you look at the parameters; in HR_EMPLOYEE_API.CREATE_EMPLOYEE you see a parameter p_per_comments, in HR_PERSON_API.UPDATE_PERSON you see a parameter p_comments. It would have been easier if the parameters were consistent.

So once I got familiar with APIs, I started with the integration in my APEX app. Here are the steps to drill-down into a person from the people report (see previous post) and edit his or her email address.

Just as with the views I find it a good practice to not grant execute on the entire libraries of EBS to your own schema. I prefer to create my own packages in the APPS schema e.g. apex_api_pkg and call the correct API calls from there. The advantage is that you can add logging to your own package or do some other extra logic in there. For example APEX passes typically back values as strings, but some API calls need to be passed (next to varchar2) as a date, a number or a boolean. So you could choose to have all input strings in varchar2 in the apex_api_pkg and do the conversion inside the package to the correct one. In the below code I didn't do that, instead I went for an almost 1-on-1 mapping.

create or replace package apex_api_pkg
as

procedure update_person_email (
p_effective_date in date,
p_datetrack_update_mode in varchar2,
p_person_id in number,
p_email_address in varchar2,
p_object_version_number in out number,
p_employee_number out varchar2,
p_effective_start_date out date,
p_effective_end_date out date,
p_full_name out varchar2,
p_comment_id out number,
p_name_combination_warning out boolean,
p_assign_payroll_warning out boolean,
p_orig_hire_warning out boolean
);

end apex_api_pkg;
/

create or replace package body apex_api_pkg
as

procedure update_person_email (
p_effective_date in date,
p_datetrack_update_mode in varchar2,
p_person_id in number,
p_email_address in varchar2,
p_object_version_number in out number,
p_employee_number out varchar2,
p_effective_start_date out date,
p_effective_end_date out date,
p_full_name out varchar2,
p_comment_id out number,
p_name_combination_warning out boolean,
p_assign_payroll_warning out boolean,
p_orig_hire_warning out boolean
)
is
l_object_version_number number;
l_employee_number varchar2(4000);
l_effective_start_date date;
l_effective_end_date date;
l_full_name varchar2(4000);
l_comment_id number;
l_name_combination_warning boolean;
l_assign_payroll_warning boolean;
l_orig_hire_warning boolean;
begin
l_object_version_number := p_object_version_number;

hr_person_api.update_person (
p_effective_date => p_effective_date,
p_datetrack_update_mode => p_datetrack_update_mode,
p_person_id => p_person_id,
p_email_address => p_email_address,
p_object_version_number => l_object_version_number,
p_employee_number => l_employee_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_full_name => l_full_name,
p_comment_id => l_comment_id,
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 update_person_email;

end apex_api_pkg;
/

The next thing is to grant execute privileges on this package to your own user which is linked to your APEX workspace:

grant execute on apex_api_pkg to apex_ebs;

In the APEX application we create a new Form based on this procedure. I found that the APEX wizard didn't work in my case (but more on that in another post). I created a new Blank Page, add a region to it and a couple of items.

From my report I created an edit link to this new page and I pass the value of PERSON_ID to this page.

As I want to see the original data in my form (note that I didn't include all fields available in the API) I added a Fetch data process of type PL/SQL anonymous block with this code:

select full_name, email_address, effective_start_date, employee_number, object_version_number
into :P2_FULL_NAME, :P2_EMAIL_ADDRESS, :P2_EFFECTIVE_DATE, :P2_EMPLOYEE_NUMBER, :P2_OBJECT_VERSION_NUMBER
from apex_per_people_vw
where person_id = :P2_PERSON_ID;

When you hit the Apply Changes button I want the email address to update, that is why we have the Update email Process in Page Processing. The PL/SQL code is as follows:

declare
l_object_version_number number;
l_employee_number varchar2(4000);
l_effective_start_date date;
l_effective_end_date date;
l_full_name varchar2(4000);
l_comment_id number;
l_name_combination_warning boolean;
l_assign_payroll_warning boolean;
l_orig_hire_warning boolean;
begin
l_object_version_number := to_number(:P2_OBJECT_VERSION_NUMBER);

apps.apex_api_pkg.update_person_email (
p_effective_date => to_date(:P2_EFFECTIVE_DATE, 'DD-MON-YYYY'),
p_datetrack_update_mode => :P2_UPDATE_MODE,
p_person_id => to_number(:P2_PERSON_ID),
p_email_address => :P2_EMAIL_ADDRESS,
p_object_version_number => l_object_version_number,
p_employee_number => l_employee_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_full_name => l_full_name,
p_comment_id => l_comment_id,
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;

I just do the necessary to update the email, I don't do that much yet with the output parameters, but you could transfer that back to the page if you wanted to. Also note that the package is in the APPS schema, so don't forget to add the owner in front of it. You could create a synonym in your own schema if you preferred. For the view I find it important to have those in my own schema too, for packages only if I wanted to add some extra logic to it.

Running the page shows you the below form.

In EBS there are different ways (UPDATE, CORRECTION, UPDATE_OVERRIDE, UPDATE_CHANGE_INSERT), to update the record, as I wanted to test them out, I added the Update Mode select list to the form. An UPDATE you can only do once per day, CORRECTION is what I used to update the same record multiple times.

Note that we still didn't login into our APEX application as an EBS user, so EBS doesn't know who I'm. As EBS is keeping an audit of the records, I (the logged in APEX user) won't be seen in those audit records, instead it will be a general one (EBS sysadmin I suppose). In the next posts we will authenticate with EBS integrate tighter.

In this post the purpose was to make an EBS API call to update an email address of a person. While playing with the EBS API I came across some challenges and I had to ask for advice to more experienced EBS people. Which will also be shown in the next post; where I will create a person with the API...

Previous related posts: