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:
Hi Dimitri,
ReplyDeleteI 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
Hi Paolo,
ReplyDeleteThanks 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,
Dimitri
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.
ReplyDeleteThanks Dimitri,
ReplyDeleteThis is a good piece
Regards,
Kehinde Adeyemi
Hi Dimitri,
ReplyDeleteI 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