Oracle APEX Plug-ins Competition

You have some Oracle Application Express (APEX) plug-ins written but didn't share them yet? or you plan on writing some new APEX plug-ins? Why not submit them to the APEX Plug-ins competition?!

You not only get the recognition of being an APEX plug-in developer, but you can actually win some prices too... and maybe even better, be the first 2012 ODTUG APEX Plug-in Developer of the year!

You find all the information and rules on the APEX plug-in competition page.

To get started on creating plug-ins it's always useful to look at existing plug-ins and steal with your eyes...

I look forward seeing your plug-in! Happy coding...

APEXBlogs reloaded

Since March 7th APEXBlogs.info didn't pick up new blog posts anymore.

Thanks for everybody mailing me, telling me or sending me feedback about the issue. It's nice that many people "missed" APEXBlogs... I'm sorry it took me some long to look into the issue.

Today I found some time to look a bit closed into the issue. Apparently in one of the blog posts somebody put a very strange character in. To fix my procedure I had to include following code:

So basically I replace that strange character with a space and everything worked again normally...

Over 20 new posts are included now in APEXBlogs, so happy reading again!

Apply APEX 4.1.1 Patch Set

Yesterday APEX 4.1.1 Patch set was made public. If you already have APEX 4.1 installed you can download patch number 13331096 from My Oracle Support.

If you are on a previous version of APEX you can download the complete install of APEX 4.1.1 from OTN.

I installed the patch today on my APEX 4.1 instances and it installed without a problem. It took not more than 5 minutes to complete the entire patch.

Two things to look for when you copy the images folder
- for Apache on Linux I used this command:
cp -rf patch/images /u01/ohs/apex/ (so without images folder)
- for the APEX Listener I used this command:
cp -rf patch/images/* /u01/app/glassfish3/glassfish/domain1/docroot/i (so without images folder)

When I first did the copy it included another images folder. If you go to your images folder and go into the themes folder you should find the new theme 24 folder. If you find it, than everything is copied ok.

Which brings me to; there is a new theme in APEX 4.1.1. Theme 24, the "cloudy" theme! This theme looks very nice and more 2012 (bigger fonts etc).
It would be great if Oracle would setup an official theme repository, like they did for plugins. Designing nice looking sites takes time and the web design "trends" change fast.


There was one other catch after applying the patch; some workspaces weren't accessible anymore. I got this error:

--
Workspace "" is inactive. Contact your administrator.
Contact your application administrator.
--

This was due to the way I created some of those workspaces in APEX 4.1.
Instead of going through the wizard I used the API to create the workspace:

apex_instance_admin.add_workspace(
  p_workspace_id       => v_workspace_id,
  p_workspace          => v_workspace,
  p_primary_schema     => v_primary_schema,
  p_additional_schemas => v_additional_schemas);

This worked fine before, but after applying the patch if you didn't run:

apex_instance_admin.enable_workspace('');

It doesn't work. Thanks to the APEX development team to diagnose the cause so quickly and come up with the solution. Patrick Wolf posted a script to fix any workspaces you may have with that issue.

Also don't forget to read about some possible changes of behaviour (depending compatibility setting) in the patch notes:

• Changes in Page and Region Caching
• Columns Used in Automatic Row Processing (DML) Processes

I definitely think it's good to apply this patch. I already applied some one off patches before for the urgent issues I was facing, but having this patch set brings you immediately to the latest and greatest!

Overview APEX -> EBS, next EBS -> APEX

In the previous posts I mainly focussed on getting information out of E-Business Suite and use it in my APEX application. This is especially useful for people using APEX.

Visually I see it like this:

Here is a complete overview of those posts:

Calling EBS Webservices from APEX

Using EBS Open Interface from APEX

Debugging EBS API calls in APEX

Updating EBS data from APEX using Triggers and APIs (3)

Creating EBS data from APEX using APIs (2)

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

I can imagine that people coming from the E-Business Suite are not that interested in creating stand-alone APEX applications, but they are more into creating an APEX page to extend EBS. They want to be in EBS, and in the menu some links will just open up some APEX pages. They want to see the information that is relevant at that time and have a seamless integration (so no extra logins etc).

I visualise that flow like this:

We can obviously use many of the techniques described in the other posts, but how does the authentication, authorisation etc work? Most of that is described in the Oracle white paper with a good example (starting at page 11). I might do some extra blog posts about that integration, but it will be in line with the white paper.

Calling EBS Webservices from APEX

Another way to integrate with E-Business Suite from APEX is through Webservices.

You could setup a complete BPEL and SOA environment or you can do it a bit simpler.

First you start by going to the EBS Integration Repository. I found it useful to view by Interface Type as then I could select XML Gateway Map.

For the different products inside EBS you find the different gateways. Most of them have a WSDL.
I won't give a complete run through demo as I'm currently working with HR and I didn't see an XML Gateway for that. Nevertheless I will tell you how I would do it.

In APEX go to Shared Components > Logic > Web Service References.

Create a new Webservice based on a WSDL (the one you selected from the EBS Integration Repository)

And follow the wizard from there. You don't need to search the UDDI, you can just copy the WSDL location. Once the wizard is finished I tend to create a new page (form and report) based on this Webservice so I don't need to create the possible parameters/items manually etc.

Finally I customise the page to fit my needs. That should be it... (in theory)

Using EBS Open Interface from APEX

I decided against integrating E-Business Suite Open Interface into my APEX application as I didn't really need it at the moment.

Nevertheless I want to quickly give my view on it. Open Interface tables (OIT) seems to be the common use to do interfacing with EBS. It looks especially interesting when you have to load a lot of records into EBS. Open Interfaces do a lot more than the API calls, in fact they may do multiple API calls at once and they keep error records too. Many products in EBS have a number of OIT available.

If you google for Open Interface table (or just open interface ebs) you find a lot more info than I'm giving here.

More info I found useful:

• Integration - Interface options (overview of integration possibilities)
• APIs vs Open Interfaces (nice diagram that shows the flow of APIs and Open Interface Tables)

Debugging EBS API calls in APEX

This picture comes from Dmitry Vostokov site.

While I was looking into E-Business Suite and especially the API calls, I wondered how I could debug what was going.

I find it important to understand what I'm doing. EBS is big, but the more I get into it, the more I want to know what is going on (because the more I get into having issues ;-)).

Although I don't have the time right now to really dive into a detailed EBS-APEX debugging post, I quickly want to share my ideas.

In EBS people tend to use the hr_utility package to trace what is going on. You can do something like :

apps.hr_utility.set_trace_options ('TRACE_DEST:DBMS_OUTPUT');

Unfortunately if you don't have SQL Plus or SQL Developer access to the EBS database and you only have APEX, what do you do? It would be nice if the TRACE_DEST parameter would allow HTP as a value, but it doesn't.

So here is some pseudo-code I thought would help me to debug from within my APEX app (or SQL Workshop):


BEGIN
  if APEX_APPLICATION.G_DEBUG
  then
    -- trace_dest possible values: DBMS_PIPE, DBMS_OUTPUT, PAY_LOG
    apps.hr_utility.set_trace_options ('TRACE_DEST:DBMS_PIPE');
    -- set trace on
    apps.hr_utility.trace_on;
    -- do EBS API call 
    /* EBS api call */
    -- custom message in EBS trace output
    apps.hr_utility.trace('Custom message in trace');
    -- custom message in APEX debug
    apex_debug_message.log_message (
      p_message    => 'My message',
      p_enabled    => TRUE,
      p_level      => 5
    );
    -- like to work with Tyler Muth's logger package
    -- logger.log('if the logger package is installed, another way to log a message');
    -- run some other code
    /* my custom code */
    -- set trace off
    apps.hr_utility.trace_off;
    -- log the EBS trace data to APEX or custom table
    /* insert the data of the EBS HR Utility PIPE into APEX debug messages */
    /* query debug output in a report or in APEX debug view */
  end if;
EXCEPTION
WHEN OTHERS 
THEN
  -- store your error in some logging/debugging of the above
  -- dbms_output.put_line(sqlerrm);
  apps.hr_utility.trace_off;
END;


I hope by reading the code you understand what I think would be a good way to debug your EBS code in APEX. I didn't find the time yet to write an entire EBS-APEX debug package, but maybe some of you already did or if you want to complete the above code, feel free to share it in the comments.

Happy debugging :-)

Previous related posts:

• Updating EBS data from APEX using Triggers and APIs (3)
• Creating EBS data from APEX using APIs (2)
• 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)

Updating EBS data from APEX using Triggers and APIs (3)

In the previous posts I showed how to call the EBS APIs in a Page Process of the APEX page.

Before that post I blogged about using views to query the EBS data. In this post we will do a combination of those techniques.

In APEX you can develop really fast; e.g. when you create a Form on table with Report, in less than a minute you have an Interactive Report where people can view and analyse the data in different ways. Clicking on the edit link in the report will allow you to update and delete (and create) the data.

By building your pages manually and creating the fetch and process data manually (as in the previous APIs posts), you lose a bit of productivity. By using updateable views you gain again some development speed. The updateable view technique, which means creating "INSTEAD OF" triggers on top of your views, is not specific for EBS, but you can use in any project (for example in 2006, I blogged about this feature when I was using it in DG Tournament).

When I tried to create a person or update the email address of a person through my updateable view I received following error:

Unless I missed a setting in EBS, it looks like the EBS API call I do in the trigger is not really "trigger compatible". When I discussed with Thierry and Paolo they told me about a parameter called p_validate (which changing didn't resolve my issue) and that using pragma autonomous_transaction might work. When I tried my trigger as an autonomous transaction I received following error:

That error was solved by adding a commit in the trigger.

So this is my "INSTEAD OF" trigger for the apex_per_people_vw in the APPS schema
(if you create the trigger on top of the view in your own schema you get an insufficient privilege error, see previous posts how I did the grants)

create or replace trigger apex_per_people_vw_trg
instead of insert or update on apex_per_people_vw
declare
  l_person_id                 number;
  l_assignment_id             number;
  l_per_object_version_number number;
  l_asg_object_version_number number;
  l_assignment_sequence       number;
  l_assignment_number         varchar2(4000);
  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;  
  -- use of Autonomous Transaction needed for this API
pragma autonomous_transaction;
begin
  if INSERTING 
  then
    apps.apex_api_pkg.create_person ( 
      p_hire_date                 => trunc(:NEW.effective_start_date), 
      p_business_group_id         => :NEW.business_group_id,     
      p_last_name                 => :NEW.last_name,     
      p_sex                       => :NEW.sex,
      p_first_name                => :NEW.first_name,     
      p_date_of_birth             => :NEW.date_of_birth,     
      p_email_address             => :NEW.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_effective_start_date, 
      p_per_effective_end_date    => l_effective_end_date, 
      p_full_name                 => l_full_name, 
      p_per_comment_id            => l_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 
    );
  elsif UPDATING 
  then
    l_object_version_number := :OLD.object_version_number;
    apps.apex_api_pkg.update_person_email (
      p_effective_date            => trunc(:NEW.effective_start_date),
      p_datetrack_update_mode     => 'CORRECTION',
      p_person_id                 => :OLD.person_id,
      p_email_address             => :NEW.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 if;
  -- as we user autonomous transaction a commit or rollback is necessary
  commit;
exception
  when others
  then
    rollback;
end;

On page 10 of the Oracle whitepaper about the integration of E-Business Suite and APEX you find another working example of the updateable view method with the FND_FLEX_VAL_API without an autonomous transaction.

So which technique is best? Using updateable views or call the APIs from the Page Processes?
It depends, they have both advantages and disadvantages, but sometimes you don't have a choice and can't use the trigger method.

Advantages using triggers:

• Code in one place regardless which APEX page/process inserts/updates/deletes on the view
• Able to use the APEX wizards to build report and form on top of the view with build-in APEX processes

Disadvantage (or things you should know) using triggers:

• Not every EBS API seems to work through triggers
• Might need to use Autonomous transaction in your trigger to get it working, but what are the side effects? (especially as I don't know what is going on inside the EBS API call)
• Recreating the view will lose the trigger, so make sure you can recreate the trigger
• APEX automatic row locking might interfere with EBS locking (the Oracle whitepaper doesn't mention anything of that, but Paolo posted a comment about this, also see documentation of FSP_DML_LOCK_ROW - I didn't experience this yet)
• Other people might not know about those triggers, so make sure it's clear to who you work with or who will take over the project to tell him you use triggers 
• Read Tom Kyte's article about triggers, so you know when to use/not use them

Advantages using custom process

• Full control when you want to run some code
• Works regardless of EBS API
• Different logic possible on different screens on the same data
• Easier to debug (how much time a process takes, debug output etc.)

Disadvantages using custom process

• Takes more time to build the pages and processes manually. There is a wizard to build on top of a procedure too, but I find that still slower than if you can use the wizard to build on top of a view/table.
• More code if you need to call the same procedure on multiple pages

If you experienced other advantages/disadvantages, feel free to comment.

Previous related posts:

• Creating EBS data from APEX using APIs (2)
• 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)

APEX SQL Workshop and Wizards issues in EBS environment

E-Business Suite (EBS) is a beast... it's the biggest environment I've ever seen.
If you run this query in the APPS schema:


select object_type, count(*)
  from user_objects
group by object_type
order by 2 desc


This is the result:

Over 50,000 packages, over 40,000 synonyms, over 30,000 views in one schema... impressive!

As I don't have direct access to the Oracle EBS database, I do everything through APEX.
I setup two workspaces, one linked to the APPS schema, where I only use SQL Workshop to access the objects and another workspace which holds my applications and database objects and grants to some objects in the APPS schema.

The issue with the Object Browser in SQL Workshop (in APEX) is that it will only show 10,000 objects.
So there is a chance that if you create your own view, synonym or package that you won't see it.
(There are ways to increase this limit, but as it's unsupported I won't mention how. And you would need to have access to the web server to make the change, which you might not have either.)

Even if you try to filter the result, it won't show up. Obviously you won't see many environments having over 10,000 objects in one schema, but I thought to post it anyway as some other EBS customers might hit the issue too and maybe when the Public Oracle Cloud is online and you do everything through the APEX screens you might hit this issue too (if it was an EBS environment for example).

So how do you get around this? If you want to change your view or you want to see the definitions of that view you can do:

select text
  from user_views 
 where view_name = 'PER_PEOPLE_F'

You can copy the definition, make the change and run it in SQL Workshop > SQL Commands to update your view. Querying the user_objects will show you all the objects for example starting with APEX:


select *
  from user_objects
 where object_name like 'APEX%'


For packages (and package bodies) it's a pain, especially if you want to develop those packages online. If you can't select the package you don't have the nice editor in APEX, so it makes development a bit harder. You could argue you should do development in for example Oracle SQL Developer or others, but in my case I didn't have direct access to the database, only through a browser...


The other issue I encountered was in the APEX wizards when I created a Form on a Procedure, when the package/procedure resides in the APPS schema. It kept spinning and spinning and after a couple of minutes I just closed my browser because I didn't want to wait longer.

I guess it's because the APPS schema is so big and reading from the data dictionary takes a long time, potentially even longer than the web server timeout.

The workaround here is to add a blank page, create an HTML region and add your items manually.
Also don't forget to add your process to the page to fetch and/or store the record.


Hope that helps some people who can't find their objects in SQL Workshop or who find the wizard is slow or doesn't complete in an EBS environment.

Release of 3.1 versions of SQL Developer and Modeler

It looks like today was releasing day for the Oracle SQL Developer team:

You can download Oracle SQL Developer 3.1 (07) or read on the new features e.g. DBA Utilities, Datapump wizard, a new Database Diff, ...

And also Oracle SQL Developer Data Modeler got a new version: 3.1.0 (700) which hold some new features like synchronisation between model and data dictionary and versioning of designs. You can download it here.

Installation and migration of settings of my previous version went without any issues. I couldn't live without those tools anymore.

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)

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:

• 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)