Integration of Oracle Apex and E-Business Suite R12

This page is a compilation of the blogs I wrote summer 2014 about this subject.

1. Need for change
2. Installation / Activation of Apex in E-Business Suite R12
3. Authentication, Authorization and more Shared Components
4. Make an Apex page look like an Oracle EBS R12 page
5. Upload a file in Apex and submit concurrent request in EBS R12
6. Upload a file in Apex and write it to the UNIX filesystem
7. Download a file stored in a blob field from an Apex page

1. Need for change

When upgrading an E-Business Suite implementation from R11i to R12 we ran into the problem that some pages written in HTML PL/SQL (htp package) and made accessible via functions of function type: 'SSWA plsql function that opens a new window (Kiosk Mode)' were no longer working in R12 (however the system still accepted this funcion type). 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 :-)

2. 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

Next, build your application in Apex: it's all about rights !

Our solution has been built using the following 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

3. Authentication, Authorization and more Shared Components

So far I have not seen any customer who uses 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. However, that solution is more suitable for a stand-alone application where you can login with your Oracle E-Business Suite credentials. 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 additional 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)

See the code for package apex_global for the code of the function check_ebs_credentials

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

4. 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-Business 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 to be 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 text was added to the bottom right hand 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:

5. Upload a file in Apex and submit concurrent request in EBS R12

One of the pages I built in Application Express (Apex) as an extension to the Oracle E-Business Suite R12 (EBS R12) is having the possibility to upload an Excel file into a blob field and subsequently submit a concurrent process (batch job) in EBS R12 to process the Excel file. Although interesting too, in this post I won't bother you with how to process an Excel file in PL/SQL / Java. I will show how I managed solving the challenge with Apex, EBS R12 and the underlying database and moreover I will explain how I overcame some problems I encountered. Some special points for attention:
- Validation of the filename
- Success message
- Submit button
- Submit concurrent request in Oracle EBS
- Set 'environment' in submitted process

Validation of the filename
I had a look at the sample application 'Sample File Upload and Download' that comes along with Apex (see tab Packaged Applications on the Application Builder page). I noticed that validation of the filename is done via a database trigger (before insert). In case of an error a raise_application_error is done, resulting in a not so user friendly error messages. Due to the way Apex handles a field that is displayed as 'File Browse...' it is not possible to catch the filename in an earlier stage. However, it should be possible to solve this problem using javascript, but I am not a fan of such a contrived solution (my opinion).

Success message
In Apex it seems not to be possible to include a variabele in a Process Success Message that can be entered on Process definition page. After the concurrent process has been submitted in EBS R12 the user wants to see the request number, necessary to monitor the progress of the request and to view the request log in Oracle EBS. This is solved by calling the Apex routine g_print_success_message that can handle a composed string containing the request number.

Submit button
When testing the page, the Submit button disappeared after an error in the filename. This error is set by a before insert database trigger (so after the regular validations / exception to the normal flow Apex works). When the page has been created via the create page wizzard (normal way of working) then default the Condition at the Button definition page is set to check whether FILE_ID is NULL. However, the trigger that raises the error runs after the process that retrieves the primary key / fills the FILE_ID (Process 'Get PK'). When returning on the page the Submit button has disappeared because now FILE_ID has a value. The solution is to change the Condition Type at the Button definition page to 'Button Not Conditional'.

Submit concurrent request in Oracle EBS R12
After the Excel file has been inserted in the database, we want to start a batch job inside EBS R12 that processes the file. This custom process was already developed in the Oracle E-Business R11i implementation, but it is interesting what changes we had to make in order to get it working when submitted from Apex. Besides passing the file identification (little change), now we had to add some extra parameters to the concurrent program (also see next paragraph about setting the environment in the submitted process).
In the Page Processing lane of the Apex page in hand, we added a Process 'Start concurrent request' after the (standard) Process 'Process Row of ....'. In this process an anonymous block (of PL/SQL code) is entered in 'Source'. Submitting the concurrent process could be achieved by calling the standard Oracle EBS routine fnd_request.submit_request. It turned out that in order to get the concurrent program actually running we had to run apps_initialize first.
However, it is not allowed to call the standard Oracle routines straight. Apex has no permission to do so and for security reasons it is not preferred to solve this by granting execute rights to APEX_EBS on those routines. As a central point of access we created in the APPS schema (standard Oracle EBS user, that can access all data / all routines) a package called apex_global (as in Oracle's white paper). It is defined with an authid clause of AUTHID DEFINER, which means that each routine from this package will be run with the owners rights (APPS) as opposed to invokers rights (current user which is APEX_EBS). Of course APEX_EBS must be granted execute rights to this package. Also see the code for package apex_global.

So in the anonymous block on the Process definition (source) you will see a call to a routine in the apps.apex_global package and in the apps.apex_global package you will see the call to the standard Oracle EBS routine with exactly the same parameters.

Set 'environment' in submitted process
A concurrent process submitted from outside Oracle EBS means that all context has been lost. Normally, when a concurrent request is submitted in Oracle EBS, Oracle knows what the user is allowed to do / what data the user is allowed to see. This is managed by some settings.These settings can also be achieved by running the standard Oracle EBS routines fnd_global.apps_initialize and depending on the MOAC setting (multiple organization access control) also mo_global.init or mo_global.set_policy_context (for the exact syntax see further down this post).
Consequence is that we have to pass the necessary information to the concurrent process. Fortunattely all this information is retrieved from the session information we captured when the Apex page was launched by Oracle EBS (see my post 'Authentication, Authorization and more Shared Components'). This more or less confidential information is passed to the concurrent program via parameters.

Creating the page step by step

A. Table and sequence definition
B. Define APPS package apex_global
C. Page definition
D. Definition of concurrent program in Oracle EBS R12
E. Oracle EBS routine submitted in Apex
F. Add function to EBS R12

A. Table and sequence definition
Notice that the before insert or update trigger on apex_ebs.xxapx_files is not only for the validation of the filename, but also fills the audit information (created_by, creation_date, etc) and moreover the org_id (company) that is copied from the Application Items. Click on the link to see the database objects creation scripts.

C. Define APPS package apex_global
Create the package apex_global as database user APPS (not possible in the SQL Workshop in Apex):
Click on the link to see the code of package apex_global

B. Page definition
Most important part is the source in the process 'Start concurrent Process', a PL/SQL anonymous block that submits a concurrent process in EBS R12 and shows the request number assigned.

Application Builder

Create Page >

Select a Page Type: Form
Next >

Form on a Table or View
Next>

Table / View Owner: APEX_EBS (default)
Table / View Name: XXAPX_FILES
Next >

Page Number: 10
Page Name: Upload Excel File
Page Title: Upload Excel File
Region Template: Form Region (default)
Breadcrumb: do not add breadcrumb region to page (default)
Next >

Do not use tabs
Next >

Primary Key Type: Select Primary Key Column(s)
Primary Key Column 1: FILE_ID (defaulted)
Next >

Source Type: Existing sequence
Sequence: XXOIC_FILES_RATE_TABLE_S
Next >

Select Column(s): all (default; Will hide all columns after all, except FILE_DATA)
Next >

Changed from default:
Create Button Label: Submit
Show Save Button: No
Show Delete Button: No
Next>

Branching: stay on the page after Page Submit and Cancel (cancel button will de removed later on)
After Page Submit and Processing Branche to Page: 10
After Cancel Button Pressed Branche to Page: 10
Next >

Create >

Edit Page >

Page Rendering: Upload Rate Table > Regions > Body > Upload Rate Table > Region Buttons
Double Click Region Button CANCEL (or choose Edit after right mousclick)

Press Delete and confirm delete action.

Page Rendering: Upload Rate Table > Regions > Body > Upload Rate Table > Region Buttons
Double Click Region Button CREATE (or choose Edit after right mouseclick)

Default there is a check whether FILE_ID is NULL. This causes the 'Submit' button to disappear in case an error is raised by a database trigger (where validation on FILE_NAME takes place). In that case FILE_ID already has a value.
Condition Type: Button Not Conditional
Apply Changes

Page Rendering: Upload Rate Table > Regions > Body > Upload Rate Table > Items
Doubleclick on P10_FILE_NAME

Display As: Hidden

Do the same for the rest of the Page Items, except for Page Item named 'P10_FILE_DATA'.
Browse to the next Page Item by pressing >
After the last Page Item press Apply Changes

Values for Page Item 'P10_FILE_DATA':

Label :
Label: File Name
Settings:
Required: Yes
MIME Type Column: FILE_CONTENT_TYPE
Filename column: FILE_NAME
Character Set Column: ORACLE_CHARSET
BLOB last updated Column: UPLOAD_DATE
Display Download Link: No

Page Processing: Processing > Processes
Right mouseclick and click on Create

Process Type: PL/SQL

Process Attributes:
Name: Start Concurrent Request
Sequence: 50 (defaulted as latest process; Will renumber process 'Reset Page' next)
Point: On Submit – After Computations and Validations (defaulted)
Next >

PL/SQL anonymous block that submits a concurrent process in Oracle E-Business Suite and show the request number assigned. In Apex it seems not to be possible to include a variabele in a Success message. Therefore we make a call to the Apex routine apex_application.g_print_success_message.

DECLARE
   CURSOR c_filename (b_file_id NUMBER)
   IS
     SELECT file_name
     FROM xxapx_files
     WHERE 1=1
     AND file_id = b_file_id;

   l_request_id  NUMBER; 
   l_filename    VARCHAR2(100);
   
   l_user_id         NUMBER := TO_NUMBER(V('EBS_USER_ID'));
   l_resp_id         NUMBER := TO_NUMBER(V('EBS_RESP_ID'));
   l_resp_appl_id    NUMBER := TO_NUMBER(V('EBS_RESP_APPL_ID')); 
   l_sec_group_id    NUMBER := TO_NUMBER(V('EBS_SEC_GROUP_ID'));   

BEGIN
   -- initialiseren Oracle Apps
   apps.apex_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id,l_sec_group_id);
   
   OPEN c_filename (TO_NUMBER(V('P10_FILE_ID')));
   FETCH c_filename INTO l_filename;
   CLOSE c_filename;

   l_request_id := apps.apex_global.submit_request
                    ( application => 'XXCUST'
                    , program     => 'XXCUST_PROCESS_XLS_FILE'
                    , argument1   => V('P10_FILE_ID')
                    , argument2   => l_filename
                    , argument3   => to_char(l_user_id)
                    , argument4   => to_char(l_resp_id)
                    , argument5   => to_char(l_resp_appl_id)
                    , argument6   => to_char(l_sec_group_id));
   apex_application.g_print_success_message := 'Request ' || to_char(l_request_id) || ' has been started';
END;

Next >

The Succes Message is managed by the PL/SQL code. No Error Message.
Next >

No conditions, just like the by the Apex wizard already created processes.
Create Process

Page Processing: Processing > Processes
Double click on proces 'reset page'

Process Point
Sequence: 70
Apply Changes

D. Definition of concurrent program in Oracle EBS R12
This is the definition of the concurrent program in Oracle EBS R12 that is submitted while processing the Apex page. This request wiil be submitted from outside EBS, so we need to pass the necessary parameters to be able to set the right context in the Oracle EBS executable by calling Oracle's procedure apps_initialize. We will pass back the session information retrieved from icx_session when the Apex page was launched (also see authorization scheme Check EBS Credentials posted earlier at Authentication, Authorization and more Shared Components).

Path in EBS R12: System Administrator: Concurrent > Program > Executable

Executable: Process Uploaded Excel File
Short Name: XXCUST_PROCESS_XLS_FILE
Application: XXCUST Custom Application
Description: Process Uploaded Excel File
Execution Method: PL/SQL Stored Procedure
Execution File Name: XXCUST_PROCESS_XLS_FILE.main

Path in EBS R12: System Administrator: Concurrent > Program > Define

Program: Process Uploaded Excel File
Short Name: XXCUST_PROCESS_XLS_FILE
Application: XXCUST Custom Application
Description: Process Uploaded Excel File

Executable Name: Process Uploaded Excel File
Executable Method: PL/SQL Stored Procedure

Parameters (B)

Parameters:

Seq	Parameter	Description + Prompt	Value Set
10	p_file_id	File ID	FND_NUMBER
20	p_file_name	Filename	240 Characters
30	p_user_id	User ID	FND_NUMBER
40	p_resp_id	Responsibility ID	FND_NUMBER
50	p_resp_appl_id	Responsibility Application ID	FND_NUMBER
60	p_sec_group_id	Security Group ID	FND_NUMBER

E. Oracle EBS routine submitted in Apex
Just an outline of the code.

• In Oracle EBS the output parmeters p_errbuf and p_retcode are mandatary for procedures that are called from a concurrent program / executable. When the p-errbuf returns 1 it will show yellow (warning) on the view request screen and when 2 is returned it will show red (error).
• Also notice that the first statements in the main procedure are to set the environment, otherwise it cannot select data from 'MOAC tables'. Running apps_intiatialize is always necessary. Depending on the MOAC settings (Multi Organization Access Control) in Oracle EBS it is also necessary to run mo_global.init or in our situation (MOAC is activated, but only single org) run mo_global.set_policy_context with parameter 'S' (single) and the org_id of the organization applicable (retrieved from the profile option 'ORG_ID' that usually is set at responsability level which is set by running apps_initialize; this is the normal way of working in Oracle EBS).

CREATE OR REPLACE PACKAGE apps.xxcust_process_xls_file AUTHID CURRENT_USER
AS
   /****************************************************************
   *
   * PROGRAM NAME
   *    XXCUST_PROCESS_XLS_FILE.pks
   *
   * DESCRIPTION
   *    Process Uploaded Excel file
   *
   * CHANGE HISTORY
   * Who          When         What
   * ---------------------------------------------------------------
   * M. Weeren    01-07-2014   Initial Version
   *****************************************************************/

   PROCEDURE main ( p_errbuf        OUT VARCHAR2
                  , p_retcode       OUT VARCHAR2
                  , p_file_id       IN  NUMBER
                  , p_file_name     IN  VARCHAR2
                  , p_user_id       IN  NUMBER
                  , p_resp_id       IN  NUMBER
                  , p_resp_appl_id  IN  NUMBER
                  , p_sec_group_id  IN  NUMBER
                  );

END xxcust_process_xls_file;
/

CREATE OR REPLACE PACKAGE BODY apps.xxcust_process_xls_file
AS
   /****************************************************************
   *
   * PROGRAM NAME
   *    XXCUST_PROCESS_XLS_FILE.pkb
   *
   * DESCRIPTION
   *    Process Uploaded Excel file
   *
   * CHANGE HISTORY
   * Who          When         What
   * ---------------------------------------------------------------
   * M. Weeren    01-07-2014   Initial Version
   *****************************************************************/
   
   PROCEDURE main ( p_errbuf        OUT VARCHAR2
                  , p_retcode       OUT VARCHAR2
                  , p_file_id       IN  NUMBER
                  , p_file_name     IN  VARCHAR2
                  , p_user_id       IN  NUMBER
                  , p_resp_id       IN  NUMBER
                  , p_resp_appl_id  IN  NUMBER
                  , p_sec_group_id  IN  NUMBER)
   IS
      -- initialiaze Oracle Apps
      fnd_global.apps_initialize(p_user_id,p_resp_id,p_resp_appl_id,p_sec_group_id);
      mo_global.set_policy_context('S',fnd_profile.value('ORG_ID'));
  

      ....
  

   END;
END xxcust_process_xls_file;
/

F. Add function to EBS R12
This paragraph shows how to make the Apex page available in EBS R12. Hereafter the function can be attached to any menu where the user wants to get access to the Apex page (System Administrator: Application > Menu).

Path in EBS R12: System Administrator: Application > Function

Function:Apex UPLOAD XLS
User Function Name: Apex Upload Exel File
Description: Apex Upload Exel File
Type: SSWA jsp function
HTML Call: GWY.jsp?targetAppType=Apex&p=100:20:::::
(In this example 100 is the ID of the application in Apex and 20 is the number of the Page)

PS The complete GWY syntax is: GWY.jsp?targetAppType=Apex& p=: :::::. In this way it is possible to pass some session values via the URL to Apex: GWY.jsp?targetAppType=Apex&p=100:10:::::EBS_RESP_ID, EBS_APP_ID,EBS_SEC_GROUP:[RESPONSIBILITY_ID],[RESP_APPL_ID], [SECURITY_GROUP_ID].
But no USER_ID and no ORG_ID. Therefore in our application session parameters are retrieved from table icx_session using the function icx_sec.getsessioncookie to determine the actual session_id. Moreover it is safer not to pass parameters via the URL, despite the ability of Apex to manage whether manupalation of parameters via the URL must be ignored or not.

6. Upload a file in Apex and write it to the UNIX filesystem

A csv file is uploaded from Windows into a blob via Apex. Subsequently this blob is written to a file on Unix. There is a lot of code on the internet that do the job, but at the end you will see ctrl-M at the end of each line when opening the file in the vi editor. This is due to the difference between Windows and and Unix. Windows puts a carriage return and linefeed (CR/LF) at the end of each line whereas Unix is only using a linefeed. The extra symbol show up as ^M in vi. To get rid of this character we have to cast the raw (chunck of) data to char and then replace CHR(13) (the CR) by nothing before writing it to the filesystem. The rest of te code in this procedure write_dos_blob_to_unix_file is pretty much the same as other code you will find on the internet.

The page in Application Express (Apex) is similar to upload page I created earlier (see post Upload a file in Apex and submit concurrent request in EBS R12). Instead of submitting a request in the Oracle E-Business Suite R12 (EBS R12), the additional custom process ('Write BLOB to filesystem') calls a routine that writes the blob content to a unix file. This routine is added to the apex_global package, where it will run as APPS user (because of the authid clause). This was necessary because APPS already has the rights to write to the unix filesystem via directory 'XXCUST_TEMP_FILES'. (Here directory is an Oracle database object that contains the actual path on the filesystem and where rights can be assigned to).

Source in the process definition of the additional process 'Write BLOB to filesystem':

BEGIN
   apps.apex_global.write_dos_blob_to_unix_file('XXCUST_TEMP_FILES',TO_NUMBER(V('P12_FILE_ID')));
END;

Click on the link to see the code of package apex_global

7. Download a file stored in a blob field from an Apex page

I already described an Apex page where it was possible to upload a file to a database field of type blob. Here I will create a page on which you can download the file again from that same blob field. The trick is creating a report page where a additional field is shown as a download link. This additional field is a 'getlength' of that blob field, and the properties of that field contain the characteristics of the file to download (name, character set, etc).

Application Builder

Create Page >

Select 'Report'
Next >

Select 'Interactive Report'
Next >

Page Number: 11
Page Name: Download Excel File
Region Template: No Template (default)
Region Name: Download Excel File
Breadcrumb: - do not use breadcrumbs on this page
Next >

'Do not use tabs'
Next >

About SQL Query
- Added is the WHERE clause checking the operating unit (org_id) of the user. EBS_ORG_ID is an Application Item that contains the org_id linked to the the Oracle E-Business Suite responsibility from where the Apex page was launched.
- The column 'download' (has to be dbms_lob.getlength(file_data)) is added to make a link that will allow the user to download the file stored in the column FILE_DATA (blob).

SQL Query

SELECT 
   file_id,
   file_name,
   file_content_type,
   language,
   oracle_charset,
   file_format,
   file_data,
   upload_date,
   org_id,
   creation_date,
   created_by,
   last_update_date,
   last_updated_by,
   dbms_lob.getlength(file_data) file_size,
   dbms_lob.getlength(file_data) download
FROM apex_ebs.xxapx_files
WHERE org_id = V('EBS_ORG_ID')

Link to single row view: Yes
Uniquely Identify Rows by: Unique Column
Unique Column: FILE_ID
Next >

B: Create

B : Edit Page

Page Rendering: Download Uploaded Rate Tables > Regions > Body > Download Uploaded Rate Tables > Report Columns Expand Report Columns
DoubleClick on FILE_ID

Display Type: Hidden

Do the same for the rest of the Columns except for:
- FILE_NAME
- UPLOAD_DATE
- CREATED_BY (change Column Heading to 'Uploaded by')
- FILE_SIZE
- DOWNLOAD
Browse to the next Column by pressing >

For column DOWNLOAD change the following attributes:
Display Type: Standard Report Column
Number / Date Format: BLOB Format (select from List of Values)

The Blob Column Attributes become visible:

Format Mask: DOWNLOAD (default)
Table: XXAPX_FILES
Column: FILE_DATA
Primery Key Column 1: FILE_ID
Mimetype Column: FILE_CONTENT_TYPE
Filename Column: FILE_NAME
Last Updated Column: LAST_UPDATE_DATE
Charset Column: ORACLE_CHARSET
Content Dispostion: Inline
Download Text: Download (default)
B: Apply Changes

To integrate this page in Orace EBS see my post Upload a file in Apex and submit concurrent request in EBS R12, paragraph 6 Add function to EBS R12