Monday, February 13, 2012

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)

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.

2 comments:

I was able to integrate a small sample from from APEX to EBS using the white paper by Oracle. One issue I have is when I log in to the APEX form via EBS it expects the user to log in again, even though the authentication is EBS authentication. I have double tripple checked all the steps in the white paper but was not able to resolve this issue. Any help is greatly appreciated!

Are you using Single Sign On in both APEX and EBS?That would only work with it...

However, if you use custom authentication, Popay wrote a package that will seamlessly log you in automatically in APEX from EBS. They made that open source at https://sites.google.com/a/popay.be/apex-on-apps/