Feed aggregator

For me there are two main use cases to integrate Oracle E-Business Suite (EBS) with Oracle Application Express (APEX):

1. In my APEX application I want to include/integrate data that exist in EBS
   e.g. I want to include my employees that are in EBS HR


2. The EBS application needs to be customised and I use APEX to do that
   e.g. I want to show a calendar with the birthdays of my employees
   or I want a chart about the different age categories in my company
   or I want to bulk update some information and there is no screen to do that

In both cases you want things to go seamlessly, you shouldn't be aware that you go from one technology to another. APEX and EBS data are in the Oracle database, so accessing data is not a big issue. Updating data in EBS you need to be careful with, as directly updating data through tables is not allowed (supported).

When I was reviewing ways to retrieve data from EBS and create/update/delete data in EBS from APEX I found a couple of methods that are interesting to explore further:

Retrieve data from EBSUpdate data in EBS1. ViewsX2. APIsXX3. OITs (Open Interface Tables)
X4. Webservices (incl. BPEL/SOA)XX
There are other ways to integrate or interface with EBS e.g. through EDI (Electronic Data Interchange) and EAI (Enterprise Application Integration), but from an APEX perspective the above four seemed to me the most interesting ones to explore further. In my next posts I will dig into integrating APEX and EBS with the above techniques.

References to interesting readings:

• Oracle Integration Repository
• Oracle Release 12.1 Documentation Library (see Integration)
• Oracle Apps Epicenter Blog

Previous related posts:

• E-Business Suite and APEX installation
• APEX and E-Business Suite integration (OBUG event)

Before doing the integration of Oracle Application Express (APEX) with E-Business Suite (EBs) you need to have both environments installed. The Oracle white paper talks about the installation of APEX, but not about installing E-Business Suite.


The white paper states; the prerequisites for the solution given are:

• Oracle E-Business Suite 12.1.3 or above
• Oracle E-Business Suite Patch 12316083
• Oracle Database 10.2.0.3 or above
• Oracle Application Express 3.2 or above

The first thing I needed was an E-Busisness Suite environment. Oracle provides some VM Templates for E-Business Suite 12.1.3. I wanted a complete demo system, like for example the HR schema in the Oracle database or the Sample Application in APEX. The VM Template for E-Business Suite includes the Vision demo.

I never installed EBs before, so I did some research how to do that. Finally I didn't proceed with installing E-Business Suite on our servers, as MCX was happy to provide us with a complete Oracle E-Business Suite 12.1.3 Vision installation on their servers. The version of the underlying Oracle database is 11.2.0.2.0. I also asked them to apply patch 12316083.

APEX needs to be installed on the same Oracle database as the Oracle E- Business suite database server.
We went with installing the latest version of APEX at the moment: APEX 4.1.

Installing APEX is not that hard, basically running some scripts. The only bit you have to be careful with is the choice of web listener. APEX supports three web listeners: mod_plsql, EPG and the APEX Listener.
The mod_plsql gateway is disabled by default by EBs R12, so you shouldn't go with that. The EPG is not recommended either as it will add additional load on the database server, so the best choice would be the APEX Listener. The APEX Listener is the recommended choice in any circumstance anyway, regardless of EBs. (on a related note; I'm giving a presentation about my experiences moving to the APEX Listener at OGh and ODTUG.)

So after installing APEX and using the APEX Listener on an EBs configuration, the architecture looks like this:


So now all the prerequisites are met and we can concentrate on the real integration part in the next post.

Previous related posts:

• APEX and E-Business Suite integration

For a couple of years now companies like for example Popay integrate APEX with E-Business Suite.

The learning curve of APEX is not that high, so people with a (technical) E-Business Suite background should be up and running in no time.

In April 2011, Oracle released a whitepaper how to Extending Oracle E-Business Suite Release 12 using Oracle Application Express. Before, although it worked, you were a bit on your own incase you had issues, with the whitepaper things changed, so now you can call Oracle support as well.

In the next days I will share my experiences trying to get up-to-speed with integrating APEX with Oracle E-Business Suite. My background with Oracle E-Business Suite is limited, but I know a lot about APEX and that whitepaper should get me a big step further in integrating with E-Business Suite.


On the 15th of February, the APEX and E-Business Suite SIGs of OBUG (Oracle Benelux User Group) hold a half day (FREE) seminar about this integration too. So if you want to see this integration live or you want to hear from companies who are successful in it, come and join us. You find the agenda below:

In my previous post on Index Organized Tables (IOT), I introduced the concept of the IOT Overflow Segment, where we can store columns that we may not want to include within the actual IOT index structure. Before we move on, I just wanted to cover off a few additional points that could be a trap for the [...]

In my previous introductory post, I illustrated how an Index Organized Table (IOT) might be worth consideration if most or all columns in a table were to be included within an index. I’m going to use a slightly different demo this time, replacing one of the columns with a much larger DESCRIPTION column, one which is [...]

Thought it was high time that I covered in a little detail the subject of Index Organized Tables (IOTs). When used appropriately, they can be an extremely useful method of storing and accessing data. Hopefully by the end of this series, you’ll have a better understanding of IOTs, their respective strengths and weaknesses and so perhaps [...]

Based on the excellent comments in the Quiz post, we have some clever cookies out there I guess the first thing to point out is that based in the basic scenario provided, the index shouldn’t ordinarily be continually growing in this fashion. Although the index values are monotonically increasing, the deletions are leaving behind fully emptied leaf blocks which [...]

I received an email recently that had a nice example of what can potentially go wrong with an index. Let’s first create a simple table with a unique index and populate it with 200,000 rows (following demo run on 11.2.0.1): So far, everything is as expected. With have an index with 200,000 rows that currently has [...]

Happy New Year !! As has been mentioned to me on quite a number of occasions, I’ve haven’t really covered the area of Index Organized Tables (IOTs) on this blog. This despite covering the subject of IOTs in quite some detail in my Index Internals Seminar. Which I readily admit is a bit of a shame as [...]

I just want to wish you all a very Merry Christmas and a very happy, safe and successful New Year. As always, thank you all so much for supporting the blog, with all your comments and nice feedback. Hopefully, you’ve found content here that has been of some use. Now that I’m back at Oracle [...]

There are some useful reports in SQL Developer that give you an insight in your APEX applications.Click on the Reports tab in SQL Developer and open the Application Express folder. There are many reports, for example the Application Statistics you see below.

In the first grid you see all the applications of your workspace (linked to the schema you are logged in as). If you click on an application, the detail tabs load.
Below you see another report - Workspace > Activity Summary


In APEX you have a lot of statistics about your application too (see below screenshot of the Monitor Activity screen). 

If you want to navigate across applications, the SQL Developer reports are really useful as it's very fast to navigate. Both APEX as SQL Developer have their strengths. If you can't find the information you need, you can also write a query yourselves based on the APEX dictionary.

I’ve been very quiet on the blogging front lately, so apologies for that. One of the main reasons is that after a period of nearly 10 years, I’ve decided to take up a position back at Oracle Corporation as a “Principal Solutions Consultant” in sunny Canberra. So things have been rather hectic, finishing up in [...]

Below you find some statistics of the APEX-Challenge for November. If you want to make fun and play the APEX Challenge too, you can register (or login) here.
Summary for Oracle Application Express Weekly APEX Quiz

Played between 5 November 2011 and 12 November 2011
Total # of players: 149
Total # of answers submitted: 149
100% correct submissions: 8
Average % correct: 60

5 November 2011: Leverage the APEX data dictionary views to answer questions about your application.
166 Players ♦ Avg. Correct: 60% ♦ Avg. Time: 202 seconds ♦ Rating: 4 stars

Summary for Oracle Application Express Weekly APEX Quiz

Played between 12 November 2011 and 19 November 2011
Total # of players: 142
Total # of answers submitted: 142
100% correct submissions: 15
Average % correct: 56

12 November 2011: The question shows how to correctly pass values using page items within branches and some of the limitations.
156 Players ♦ Avg. Correct: 57% ♦ Avg. Time: 129 seconds ♦ Rating: 4 stars

Summary for Oracle Application Express Weekly APEX Quiz

Played between 19 November 2011 and 26 November 2011
Total # of players: 141
Total # of answers submitted: 141
100% correct submissions: 33
Average % correct: 76

19 November 2011: To be aware of the functions and procedures contained in the APEX_UTIL package and other APEX Packages
145 Players ♦ Avg. Correct: 76% ♦ Avg. Time: 131 seconds ♦ Rating: 4 stars

If you want to see more questions, you find the library of previous questions and answers here.

I’ve been so busy lately, I just haven’t had any spare time to post. For now, the quick answer to the last quiz is that the second table was indeed an Index Organized Table (IOT). One of the nice benefits of an IOT is that when re-organised, unlike a Heap Table, all indexes remain valid, [...]

A few months ago we (APEX Evangelists) launched the APEX Challenge, a weekly quiz where you get a question about Oracle Application Express. Next to the fun of answering the questions and see your rank, you will hopefully learn something.
To make it a bit easier for the APEX community, to find the site, we looked at a specific url for the APEX Challenge.
Before you always had to go to the PL/SQL Challenge and take the APEX quiz from there, which you can still do, but now we enabled http://www.apex-challenge.com too.
We also have the apexquiz.com domain, so my question now is: which domain do you like most?

• stay at plsqlchallenge.com
• go to apex-challenge.com
• go to apexquiz.com

Tell us what you like... In a few weeks we will redirect the domain permanently.If you have suggestions to make the APEX challenge/quiz better, please share with us too.
Thanks so much.

On April 24, 2012 the Oracle Benelux User Group (OBUG) is organizing for the 5th time in succession, and in close collaboration with Oracle, OBUG Benelux Connect.
Possibly you attended in the past and you wish to present your customer case at the upcoming OBUG Benelux Connect 2012 conference. OBUG offers you the ultimate possibility to share your personal experience with other Oracle customers within the Benelux.OBUG Benelux 2012 will be held at the MECC in Maastricht, the Netherlands. This is an excellent venue that provides its guests with the very latest amenities in a strikingly professional ambiance. The MECC is located in Maastricht, 10 minutes from Maastricht Central Station with train connections to Amsterdam and Brussels and a 3 minute walk from the train station Maastricht-Randwyck. Maastricht is a very popular destination for congress visitors, because it is such an intimate place and the city has much to offer.
“Extend your Horizon”
Extend your Horizon is the theme for OBUG Benelux Connect 2012.The strength of OBUG Benelux Connect is the customer cases presented by our members and other Oracle customers: Learn from peers to maximize the value of your business-critical applications and technology. In over 40 sessions, speakers will share their experiences and business solutions with delegates in different streams, which gives them the possibility to investigate other products, extending their horizon. There are streams for Applications (Oracle eBS, Peoplesoft, Siebel, JD Edwards, Primavera), Technology (DBMS, JDeveloper/ADF, APEX, ...), Middleware (BPEL, SOA, EAS, ...) and Intelligence (Hyperion, OBI, GRC, ...).The crowning touch is that the Oracle user experience team will fly in from the USA again to conduct several onsite User Experience Sessions, where delegates have the ability to contribute to and influence product direction and design for Oracle’s next-generation products.
CALL FOR PRESENTATIONS
OBUG is looking for end-users, project leaders, implementers, IT and functional managers, CIO's to share their experience with Oracle products in a 45-minute presentation – (slides in English, presentations preferably in English).If you can make a valuable contribution to the success of our conference, please complete the online submission form on the OBUG website before December 9th, 2011.If you have any questions, please contact Chris Coolen, Executive Director Oracle Benelux User Group, as soon as possible by e-mail or by phone +31620677597.
The organization will make a selection from all contributions based on main criteria such as quality and diversity. Also membership of the OBUG, user or partner, will be taken into account.Speakers will receive a notification of our decision in week 2, 2012.
Hope to see you there.

Last Oracle Open World Martin (Giffy D'Souza, left on the picture) told me he was running for the ODTUG board and without a doubt, I told him I would support him.

I know Martin for some time now; we first met in 2008 when we did a project for the company he worked for at that time. From the very first moment I appreciated his personality and technical skills. Over the years Martin became more active in the APEX community; on his blog he shares his experiences, he helps people in the forum and presents at different Oracle conferences.
In the beginning of the year I nominated him to become an Oracle ACE which was accepted. He not only got that recognition, he also got the best presentation award at ODTUG's conference this year. Hopefully 2011 will bring him one more award... a place in the ODTUG board.
I hope the whole APEX community shares my feelings when I say: "Vote for Martin and get him in the board!". Being APEX as one of the main tracks at ODTUG I think it only makes sense to have an APEX representative in the board and I can't think of a better person than Martin. His personality makes the difference!
You can read more about Martin's campaign and his reasons running for the board on his blog.
All the best Martin! I hope you make it.