Make an APEX page look like an Oracle EBS R12 page

I made the folowing changes to let the APEX page more resemble an E-Business Suite R12 page:

• First of all, I created several Application Items (Shared Components > Logic) including EBS_APPLICATION_NAME and EBS_URL. These will be set in a routine check_ebs_credentials that will run when a page is launched (part of the Authorization Scheme I explained earlier).

• For the User Interface select APEX Theme 20 (Traditional Blue), a standard theme that comes along with APEX (one of the Legacy Themes)

• Logo (Shared Components > User Interface > User Interface Attributes)
  The Oracle Logo displayed in Oracle EBS R12 can also be displayed in the APEX application:
• First copy the file FNDSSCORP.gif  $OA_MEDIA (EBS) to the directory 'images' in APEX
• Logo Type: Image 
• Logo: /i/FNDSSCORP.gif 
• Logo attributes: alt="Oracle Logo" title="Oracle Logo" width="155" heigth="20" border="0" 

• Navigation Bar Entries (Shared Components > Navigation)
  In the E-Business Suite R12, self-service pages (HTML pages) like the APEX pages can be called from a menu in forms (java program) or a menu in the browser (HTML page). In the first case the HTML page is opened in a new browser window and when done the user has to close that window. In the latter case the HTML page appears in the same browser window and when done the user can navigate back to the menu. This is a bit of a problem for navigation. In the normal E-Busisness Suite self-service pages (using OAF) this is managed correctly: in the navigation bar you will see ‘Close window’ or ‘Home’. Because it seems not possible to determine how the APEX page is launched, only the ‘Home’ entry has been provided. For closing a window all browsers have built-in possibilities.
• Remove Logout
• Add Home. For 'URL Target' enter &EBS_URL. (don't forget the dot)
  EBS_URL holds the value icx_session_attribute '_USERORSSWAPORTALURL' but can also be composed of the value of profile option APPS_SERVLET_AGENT concatenated with /OA.jsp?OAFunc=OAHOMEPAGE#

• Template (Shared Components > Templates > Type: Page, Name: No Tabs).
  In the application I only make use of pages that use the page template named 'No tab'. The application name is added to the page header (however defined in the page body) and the user name has been removed from the footer as well as a copyright tekst was added to the bottom righthand corner. I couldn’t find how to set the built-in application substitution strings, so I made changes to the templates, however setting of the built-in application substitution strings would result in a more generic solution.
• Definition > Body: 
• The application name is added stored in the Application Item EBS_APPLICATION_NAME.
• The style attribute is necessary to display the name with the correct format. Image blank.gif is used to force some space between the logo and the application name. 
• Also two blank lines are added (one above the logo; one beneath the logo):
  
  <div id="t20PageHeader">
  <table border="0" cellpadding="0" cellspacing="0" summary="">
  <tr><td colspan=3> </td></tr>
  <tr>
  <td id="t20Logo" valign="top">#LOGO#<br />#REGION_POSITION_06#</td>
  <td id="t20HeaderMiddle" valign="top" width="100%"><img src="/i/blank.gif" width="20" border="0"><span title="EBS Application Name" style="font-family:Arial; color:#FFFFFF; font-size:16px; white-space:nowrap; font-weight:bold; vertical-align:top;">&EBS_APPLICATION_NAME.</span> #REGION_POSITION_07#<br /></td>
  <td id="t20NavBar" valign="top">#NAVIGATION_BAR#<br />#REGION_POSITION_08#</td>
  </tr>
  <tr><td colspan=3> </td></tr>
  </table>
  </div>
  <div id="t20BreadCrumbsLeft">#REGION_POSITION_01#</div>
  <table border="0" cellpadding="0" cellspacing="0" summary="" id="t20PageBody" width="100%" height="70%">
  <td width="100%" valign="top" height="100%" id="t20ContentBody">
  <div id="t20Messages">#GLOBAL_NOTIFICATION##SUCCESS_MESSAGE##NOTIFICATION_MESSAGE#</div>
  <div id="t20ContentMiddle">#BOX_BODY##REGION_POSITION_02##REGION_POSITION_04#</div>
  </td>
  <td valign="top" width="200" id="t20ContentRight">#REGION_POSITION_03#<br /></td>
  </tr>
  </table>
  

• Definition > Footer 
• Added an extra line to look it more like EBS R12. 
• Also a copyright text is added like in EBS R12. 
• The username has been deleted from the footer (&APP_USER.):
  
  <table border="0" cellpadding="0" cellspacing="0" summary="" id="t20PageFooter" width="100%">
  <tr><td colspan=3> </td></tr>
  <tr>
  <td id="t20Left" valign="top"><span id="t20UserPrompt"></span><br /></td>
  <td id="t20Center" valign="top">#REGION_POSITION_05#</td>
  <td id="t20Right" valign="top"><span id="t20Customize">#CUSTOMIZE#Copyright (c) 2014, Marc Weeren. All rights reserved.</span><br /></td>
  </tr>
  </table>
  <br class="t20Break"/>
  #FORM_CLOSE#
  #DEVELOPER_TOOLBAR#
  #GENERATED_CSS#
  #GENERATED_JAVASCRIPT#
  </body>
  </html>
  

Before:

After:

Authentication, Authorization and more Shared Components

So far I have not seen any customer who used Oracle Single Sign-on (OSSO) or Oracle Access Manager (OAM) collaborating with the Oracle E-Business Suite. So, I could not take advantage of the pre-configured Authentication Schemes in Oracle Application Express. In Oracle's White Paper a solution is described when using custom authentication for Oracle E-Business Suite Oracle. However, that solution is more suitable for a stand-alone application. For our problem, we are looking for a fully integrated application and therefor I developed another solution using the cookie settings in the icx_sessions table of Oracle E-Business Suite. Moreover, when checking the session variables I store more relevant Oracle E-Business Suite session information in Oracle Application Express Application Items ...

Application Items (Shared Components > Logic)
Scope: Application
Session State Protection: Restricted - May not be set from browser

Application Item Name	Comments
EBS_USER_ID	Key to User; To check EBS authorization and to set EBS context (icx_sessions)
EBS_RESP_ID	Key to Responsibility; To check EBS authorization and to set EBS context (icx_sessions)
EBS_RESP_APPL_ID	Key to Responsibility Application; To check EBS authorization and to set EBS context (icx_sessions)
EBS_SEC_GROUP_ID	Key to Security Group; To check EBS authorization and to set EBS context (icx_sessions)
EBS_TIME_OUT	Session Time Out in Oracle E-Business Suite (icx_sessions)
EBS_URL	URL to return to EBS Homepage from APEX (icx_session_attributes)
EBS_ORG_ID	EBS ORG_ID (icx_sessions) - MO: Operating Unit from Responsibility
EBS_APPLICATION_NAME	To be displayed at the left tophand corner (application_name from fnd_application_tl using EBS_RESP_APPL_ID)

The Application Items are used in queries or when setting the ‘environment’ (apps_initialize).

Authentication (Shared Components > Security)
The Oracle Application Express pages are directly launched from the E-Business Suite. Additional login is not desirable, so no Authentication Scheme.

Authorization (Shared Components > Security)
I created an Authorization Scheme 'Check EBS Credentials' that will check whether the user has a valid E-Business Suite session. If so, then session attributes are copied into the Application Items. If not, then an error message will be displayed that access is not allowed. The E-Business Suite function icx_sec.getsessioncookie is used to determine the session_id. This session_id is the key to retrieve additional information from the E-Business Suite tables icx_sessions and icx_session_attributes.

Authorization Schemes: Create> (B)

 

Next> (B)

 

Name: Check EBS Credentials
Scheme Type: PL/SQL Function Returning Boolean
PL/SQL Function Body:

   BEGIN
      RETURN apps.apex_global.check_ebs_credentials; 
   END;

Error message displayed when scheme violated:  "Access not allowed: No valid E-Business Suite session."
Evaluation point: once per page view
Create Authorizartion Scheme (B)

Code of function check_ebs_credentials (from package body apps.apex_global):

   FUNCTION check_ebs_credentials
      RETURN BOOLEAN
   IS
      c_ebs             VARCHAR2(240) := 'E-Business Suite';
      
      l_authorized      BOOLEAN;
      l_user_id         NUMBER;
      l_resp_id         NUMBER;
      l_resp_appl_id    NUMBER;    
      l_sec_group_id    NUMBER;
      l_org_id          NUMBER;
      l_time_out        NUMBER;
      l_ebs_url         VARCHAR2(100);
      l_appl_name       VARCHAR2(240);

      CURSOR get_apps_credentials
      IS
         SELECT iss.user_id
         ,      iss.responsibility_id
         ,      iss.responsibility_application_id
         ,      iss.security_group_id
         ,      iss.org_id
         ,      iss.time_out
         ,      isa.value
         FROM  apps.icx_sessions iss
         ,     apps.icx_session_attributes isa 
         WHERE iss.session_id = apps.icx_sec.getsessioncookie
         AND   isa.session_id = iss.session_id
         AND   isa.name = '_USERORSSWAPORTALURL';
         
      CURSOR get_appl_name (b_appl_id NUMBER)
      IS
         SELECT application_name
         FROM   apps.fnd_application_tl
         WHERE  application_id = b_appl_id
         AND    language = USERENV('LANG');
         
   BEGIN
      OPEN get_apps_credentials;
      FETCH get_apps_credentials 
      INTO l_user_id
      ,    l_resp_id 
      ,    l_resp_appl_id
      ,    l_sec_group_id
      ,    l_org_id
      ,    l_time_out
      ,    l_ebs_url;
      
      IF get_apps_credentials%NOTFOUND THEN  
         l_authorized := FALSE;
      ELSE
         l_authorized := TRUE;
         
         OPEN get_appl_name(l_resp_appl_id);
         FETCH get_appl_name INTO l_appl_name;
         IF get_appl_name%NOTFOUND THEN
            l_appl_name := c_ebs;
         END IF;
         CLOSE get_appl_name;

         apex_util.set_session_state('EBS_USER_ID',TO_CHAR(l_user_id));
         apex_util.set_session_state('EBS_RESP_ID',TO_CHAR(l_resp_id));
         apex_util.set_session_state('EBS_RESP_APPL_ID',TO_CHAR(l_resp_appl_id));
         apex_util.set_session_state('EBS_SEC_GROUP_ID',TO_CHAR(l_sec_group_id));
         apex_util.set_session_state('EBS_ORG_ID',TO_CHAR(l_org_id));      
         apex_util.set_session_state('EBS_TIME_OUT',TO_CHAR(l_time_out));      
         apex_util.set_session_state('EBS_URL',l_ebs_url);     
         apex_util.set_session_state('EBS_APPLICATION_NAME',l_appl_name);    

         apex_util.set_session_max_idle_seconds(l_time_out*60,'APPLICATION');         
      END IF;
      
      CLOSE get_apps_credentials;
      RETURN l_authorized;
      
   EXCEPTION
      WHEN OTHERS THEN
         IF get_apps_credentials%ISOPEN THEN CLOSE get_apps_credentials; END IF;
         RETURN FALSE;      
   END;

Setting the session timeout seems not to work. After a while the Oracle E-Business Suite session shows a timeout, but if you did not close the APEX page to upload a file, it still lets you upload and submit a file ... Is this a bug?

Security Attributes (Shared Components > Security)
Access to any page in the APEX application is not allowed when no E-Business Suite session is active. This is arranged by setting the Authorization Scheme as a Security Attribute. However, it is also possible to manage authorization per page. In the latter case don't set the authorization scheme as shared component.

Security > Security Attributes: Authorization

 

Authorization Scheme: Check EBS Credentials
Apply Changes (B)

PS: In an attempt to get the session timeout working, I also tried to set the Maximum Session Idle Time In Seconds to 1800 (default value E-Business Suite). This is also a Security Attribute: Session Timeout. I read somewhere that this was a a condition to get the session timeout working. Unfortunately, it didn't help. Besides setting the Maximum Session Idle Time In Seconds here at application level it was initially already set at instance level by the dba (Oracle Application Express Administration).

Installation / Activation of APEX in E-Business Suite R12

See Oracle Support Document 1306563.1 Extending Oracle E-Business Suite Release 12 using Oracle Application Express (APEX) for a white paper and a few scripts to get the integration of Application Express and E-Business Suite R12 working, including a demo application. In short:

• Install Application Express in the Oracle E-Business Suite database. Download the latest version of Application Express from download.oracle.com (Developer Tools) and follow the instructions. 
• Install Oracle REST Data Services (former APEX listener) as Web Listener 
• Preferred by Oracle over Oracle HTTP Server and Embedded PL/SQL Gateway
• Works standalone; Light use: no application server necessary (Web Logic, Glassfish)
• Also see the article Moving to the APEX Listener by Dimitri Gielis 
• Log into Oracle Application Express Administration
• Create Workspace
• Workspace Name: APEX_EBS
• Schema Name: APEX_EBS
• Set security setting ‘Allow Public Upload’ (Manage Instance)
• Apply interoperability patch 12316083

By the way, I didn't do the installation myself. It was done by the dba'ers of the party where the customer has outsourced / hosted it's E-Business Suite application.

Next, build your application in APEX: it’s all about rights !

At my custsomers site they have the next releases / versions:
- Oracle E-Business Suite: release 12.1.3
- Oracle Application Express: release 4.2.4.00.08
- Oracle Database: version 11.2.0.4.0
- Oracle Rest Data Services: version 2.0.7.91.15.01

In the white paper I missed the part how to make the APEX pages more look and feel like EBS pages ...

Need for change

When upgrading an E-Business Suite implementation from R11i to R12 we ran into a problem. In R11i the customer had a couple of custom pages for uploading and downloading Excel sheets with some validation on the filename and submitting a concurrent process to process the uploaded Excel Sheet. Those pages were built with HTML PL/SQL (htp package) and made accessible via functions of Function Type: 'SSWA plsql function that opens a new window (Kiosk Mode)'. It appears to me that the first E-Business Suite HTML pages (self-service pages) were built using this technique. However, this Function Type is no longer working in R12, although the system still accepted this Funcion Type as a valid value. So, we had to look for an alternative:

• (Re)use the generic Upload / Download functions introduced in R12 (System Administrator > Generic File Manager); 
• Build static HTML pages and put this file under $OA_HTML directory (Function Type : 'JSP Interoperable with OA' and HTML Call: name of the file);
• Build custom pages using OAF; 
• Integrate Oracle Application Express (APEX) and develop the same functionality.

It wouldn't be interesting for this blog if we had not opted for the last option :-)

Installing ...

With 'System > Administration > Users and Groups' I created the following users / groups:
user: oracle / groups: dba (primary), oracle
user: applmgr / groups: dba (primary), oracle

Than I created the root dir:
#mkdir /d01
#cd /d01
# mkdir -m 775 -p ebs/R12VIS/inst
# chown -R oracle:dba ebs

In the past I already build the staging area, so I started rapidwiz :
# cd [my staging area]
# cd startCD/Disk1/rapidwiz
# ./rapidwiz

I used Port Pool 0 and changed the Base Directory of the Database to /d01/ebs/R12VIS and fir the Application to /d01/ebs/R12VIS/inst.

And now it is 23:30 and rapidwiz is already running for 3 hours and it indicates it is on 22% .... I am sure it has also to do whith the device type (dynamicaly growing). But tomorrow I need my laptop for work. I will close all other programs to free up some memmory, but I am afraid that won't help much. I limitted the memmory of my virtual machine to 1536 MB (out of 4 GB). Besides, this is all about I/O ... Keep on working, I am going to sleep.

Next Try

A few months ago I started installing Oracle EBS in my Virtual Machine, but soon my harddisk started spinning. The virtual harddisk was stored on my external device, but the snapshot file was still stored on my C drive and it was enormously growing. So, my Virtual Machine crashed totally. Now I am trying it again. On May 24th, I will visit an OBUG session about APEX and EBS in Vilvoorde (Belgium). I hope to be able to experiment some things before that date.

I removed Oracle's Virtual Box from my laptop and downloaded the latest version from http://www.virtualbox.org/ (VirtualBox 4.0.6). Installation was easier than in the previous version: just keep clicking 'next'. I also installed the 'VirtualBox 4.0.6 Oracle VM VirtualBox Extension Pack', available on the same download page. Essential for me I guess, because I am using my an external disk drive using USB 2.0. I will remove the page with instructions How To Install Virtual Box from my blog. That page is outdated and now installation is so simple ...

After installation of Oracle's Virtual Box, I created a new virtual machine (MyEBS.R12):
- type harddisk is now dynamically expanding storage (max 300 GB);
- all virtual machines (including snapshots) are now stored on the external disk drive (Oracle VM VirtualBox Manager: File > Preferences > tab: General - Default Machine Folder);
- since VirtualBox release 4, installation of the Guest Additions (necessary for sharing disks with the host) also has changed (step B.4):

A.0 empty disk drive: Devices > CD/DVD devices > Remove disk from virtual drive
A.1 make VBoxGuestAdditions.iso accessible: Devices > CD/DVD devices > Enterprise-R5-U5-Server-x86_64-dvd.iso
A.2 open a terminal
A.3 cd /media/Enterprise Linux dvd 20100405/Server
A.4 rpm -i --nodeps --replacefiles xxx where xxx stands for
gcc-4.1.2-48.el5.x86_64.rpm
gcc-c++-4.1.2-48.el5.x86_64.rpm
glibc-2.5-49.i686.rpm 1)
glibc-2.5-49.x86_64.rpm 1)
glibc-common-2.5-49.x86_64.rpm 1)
glibc-devel-2.5-49.i386.rpm
glibc-devel-2.5-49.x86_64.rpm
libgcc-4.1.2-48.el5.i386.rpm 1)
libgcc-4.1.2-48.el5.x86_64.rpm 1)
libstdc++-devel-4.1.2-48.el5.i386.rpm
libstdc++-devel-4.1.2-48.el5.x86_64.rpm
libstdc++-4.1.2-48.el5.i386.rpm 1)
libstdc++-4.1.2-48.el5.x86_64.rpm 1)
make-3.81-3.el5.x86_64.rpm 1)
gdbm-1.8.0-26.2.1.i386.rpm
gdbm-1.8.0-26.2.1.x86_64.rpm 1)
libXp-1.0.0-8.1.el5.i386.rpm
libXp-1.0.0-8.1.el5.x86_64.rpm
libaio-0.3.106-5.i386.rpm 1)
libaio-0.3.106-5.x86_64.rpm 1)
libgomp-4.4.0-6.el5.x86_64.rpm
sysstat-7.0.2-3.el5.x86_64.rpm
util-linux-2.13-0.52.el5_4.1.x86_64.rpm 1)
compat-libstdc++-296-2.96-138.i386.rpm 1)
compat-libstdc++-33-3.2.3-61.i386.rpm 1)
compat-libstdc++-33-3.2.3-61.x86_64.rpm 1)
elfutils-libelf-devel-0.137-3.el5.x86_64.rpm
elfutils-libelf-devel-static-0.137-3.el5.x86_64.rpm
libaio-devel-0.3.106-5.x86_64.rpm
unixODBC-2.2.11-7.1.i386.rpm
unixODBC-devel-2.2.11-7.1.i386.rpm
unixODBC-2.2.11-7.1.x86_64.rpm
unixODBC-devel-2.2.11-7.1.x86_64.rpm
kernel-headers-2.6.18-194.el5.x86_64.rpm
kernel-devel-2.6.18-194.el5.x86_64.rpm (not mentioned in [ID 761566.1]).
1): were allready installed (message).
A.6 exit

B.0 empty disk drive: Devices > CD/DVD devices > Remove disk from virtual drive
B.1 make VBoxGuestAdditions.iso accessible: Devices > Install Guest Additions...
B.2 open a terminal
B.3 cd /media/VBOX[tab]
B.4 sh ./VBoxLinuxAdditions.run

C.0 Added Shared folder in Oracle VM VirtualBox: Devices > Shared Folders...
C.1 open a terminal
C.2 mkdir /tmp/share
C.3 chmod 777 /tmp/share
C.4 mount -t vboxsf share /tmp/share

Then I performed the same actions as at 12 December 2010 to get the system ready for installation of Oracle EBS R12.1.1.

Additional action (as on 13 June 2010):
==== OS Library Patch for Oracle HTTP Server
Download patch 6078836 from Oracle Support; Look for platform Linux x86 (you won’t find it using platform Linux x86-64).
Unzip the file.
cp libdb.so.2 /usr/lib

Prolongation

In December 2010 my WD 1TB My Book Studio Edition WD10000H1Q-00 stopped working. After I used it to backup some files from my older Windows XP PC and then reconnected it to my Windows 7 laptop, it kept saying "you need to format disk E; before you can use it". The fix was unbelievable simple: "chkdsk e: /f". However, it took me a while to find this 'solution' on the internet.

I hope I'll have some time coming weeks to complete my mission.
In the meantime I upgraded my Oracle VM Virual Box to version 4.0.4. I have no screenprints, as installation is very straightforward (keep clicking next).
Also I applied Service Pack 1 for Windows7 (64 bit).