Apex + Apps

 How to combine and integrate Apex and Apps, using the best features from each side?

For me a tool is Apps integrated if the following query shows proper results: 


Integration sql

select
  fnd_profile.value( 'PER_BUSINESS_GROUP_ID') "Business group",
  fnd_profile.value( 'ORG_ID') "for non HR consultants",
  fnd_global.user_name login_name,
  fnd_date.date_to_displaydate( sysdate) today,
  (
    select count(*) 
    from per_people_x
    where current_employee_flag = 'Y'
  ) "# employees today",
  hr_general.decode_lookup( 'SEX', 'F') "female translated"
from dual

  • the Business Group profile option. In Hrms you can implement and define multiple business groups. Something like a legal entity, or company. You know, the stuff for which you need consultants. Give me a call. In an integrated environment, profile option values should be fully available. This comes down to knowing who you are, under which responsibility.
  • Speaking about multiple orgs.
  • fnd_global.user_name: which eBusiness suite user is running this query?
  • fnd_date.date_to_displaydate: and what are the NLS preferences of this user? Is she using dots or commas as decimal separator? What is her preferred date format?
  • per_people_x: this is a secured Hrms view. Complicated (and great) stuff.
  • And what is the language she is working in at this moment?
If you can make that query work properly in an Apps external tool (Business Objects, Cognos, Excell, ...), go for it.

An excellent white paper (pdf) from Rod West described in detail a way to integrate Apps and Apex. 

But we want to go a step further:
  • where the user logs in as normal in Oracle's eBusiness Suite
  • and starts an Apex application from an Apps menu, form function. So no individual Urls to spread. And Apps is used to define who can execute which functionality.
  • and even more important, no additional login. 
  • and in the background the integration part is supposed to know from which Apps contexts it is launched
  • and it must be very straight forward for the Apex developer to Apps-enable the Apex application.

Starting with the latest part: add popay_apex.set_apps_sessions; to the VPD plsql call to set security context of your application. That's it.

How to arrive there?
  1. Install the popay_apex package (once)
  2. define the package above as secured plsql, together with some apex packages (once)
  3. develop your Apex application. Set your authentication scheme to Database, and set the VPD call to the popay_apex package.
  4. Define a forms functions to launch apex from

Popay uses exactly the same approach for R12, but we had to add a jsp page, to complete the overall solution, since Oracle stopped supporting mod/plsql in R12. Oracle published also a paper (and patch), to integrate Apex within the eBusiness suite.

In case you would still prefer the jsp approach, you can contact Popay for more details.



Scott Spendolini is a regular poster on the Apex forum. If the topic is Apps integration, you will find his name very regularly.