Compilation of APEX/EBS R12 integration posts summer 2014

Check the page Integration of Oracle Apex and E-Business Suite R12 for a compilation of all posts I wrote summer 2014 about Integration of Oracle Apex and E-Business Suite R12. SQL code has been moved to seperate pages (links available where applicable).

Download a file stored in a blob field from an APEX page

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