Oracle APEX Integration with Oracle E-Business Suite (EBS): A Complete Step-by-Step Guide
Oracle APEX Integration with Oracle E-Business Suite (EBS)
— Complete Step-by-Step Guide
📌 Article OverviewThis guide covers end-to-end Oracle APEX integration with Oracle E-Business Suite (EBS) R12.2.x — including architecture design, prerequisite configuration, APEX workspace setup, EBS REST services, authentication via Single Sign-On (SSO), and working code samples. Screenshots are simulated as realistic UI mockups.
Table of Contents
🔧 Requirements
1. Introduction
Oracle Application Express (APEX) is a low-code development platform built on Oracle Database. When integrated with Oracle E-Business Suite (EBS), it enables organizations to build modern, responsive web applications that leverage EBS business data and functions — without the limitations of Oracle Forms or OAF.
This integration allows DBAs and developers to:
- Build custom dashboards and reports on top of EBS data
- Expose EBS functionality via REST APIs consumed in APEX
- Implement Single Sign-On (SSO) so EBS users log into APEX seamlessly
- Leverage EBS security (responsibilities, profiles) within APEX apps
- Extend EBS with modern UI without core modifications
⚠️ Compatibility NoteThis guide targets Oracle EBS R12.2.x with Oracle Database 19c/21c and APEX 23.x / 24.x. Verify your specific patch levels against Oracle My Support Note1108523.1(APEX on EBS Interoperability Matrix).
2. Architecture Overview
The integration follows a layered architecture where APEX sits alongside EBS on the same Oracle Database, with the integration tier handling authentication and API communication.
3. Prerequisites & Requirements
| Component | Minimum Version | Recommended | Notes |
|---|---|---|---|
| Oracle EBS | R12.2.6 | R12.2.12+ | Ensure latest AD/TXK patches |
| Oracle Database | 19c (19.3) | 19c RU Jan 2024 | APEX requires multitenant or non-CDB |
| Oracle APEX | 21.2 | 24.1 | Install in APEX schema on EBS DB |
| ORDS | 21.4 | 24.1 | Must match APEX version family |
| Oracle HTTP Server | 12.1.3 | 12.2.x | mod_plsql for APEX routing |
| JDK | JDK 11 | JDK 17 | Required by ORDS standalone mode |
📋 Required EBS PatchesApply Oracle EBS patch19245498(REST Services Framework),17774755(ICX updates for APEX auth), and review MOS Note2277035.1for the full APEX on EBS certification matrix.
4. Step 1 — Installing Oracle APEX on EBS Database
APEX must be installed directly in the Oracle EBS database. Never install in a separate database for EBS integration — it must share the same DB instance to access APPS schema objects.
1
Download APEX 24.1 from OTN / My Oracle SupportDownload the APEX zip file and transfer it to the EBS database server.
2
Extract and navigate to the APEX directory
💻 Shell — Extract and Prepare APEX
# Unzip the downloaded APEX archive
unzip apex_24.1_en.zip -d /u01/oracle/apex
# Navigate to APEX installation directory
cd /u01/oracle/apex/apex
# Set Oracle environment (source EBS environment)
source /u01/oracle/EBS/apps/apps_st/appl/EBSapps.env run
# Verify database connectivity
sqlplus /nolog
SQL> connect / as sysdba
SQL> select name, db_unique_name from v$database;3
Run the APEX full installation scriptThis installs APEX into the APEX_240100 schema (version-specific).
🗄️ SQL — Install APEX (Full Installation)
-- Connect as SYSDBA from the apex/ directory
connect / as sysdba
-- Run the full APEX installation
-- Parameters: tablespace, files tablespace, temp tablespace, log path, lang
@apexins.sql APEX APEX TEMP /tmp/apex_install EN
-- This installs APEX components and may take 30-60 minutes
-- Monitor the log file in /tmp/apex_install/
-- After completion, verify installation
select VERSION, STATUS, LAST_UPDATED_BY
from APEX_RELEASE;
-- Expected: 24.1.x VALID SYS4
Set APEX Internal Admin PasswordConfigure the APEX instance admin account immediately after installation.
🗄️ SQL — Set APEX Admin Password
-- Set the APEX Internal admin password
BEGIN
APEX_UTIL.set_security_group_id(p_security_group_id => 10);
END;
/
BEGIN
APEX_UTIL.create_user(
p_user_name => 'ADMIN',
p_web_password => 'Welcome#1234',
p_developer_privs => 'ADMIN'
);
END;
/
COMMIT;
-- Alternatively use the password reset script
@apxchpwd.sql5
Grant Required Privileges to APEX on EBS APPS Schema
🗄️ SQL — Grant APEX Access to EBS APPS Objects
-- Connect as SYS or DBA
connect / as sysdba
-- Create a dedicated schema for APEX-EBS integration
CREATE USER apex_ebs_user IDENTIFIED BY "Welcome#Apex1"
DEFAULT TABLESPACE APEX
TEMPORARY TABLESPACE TEMP;
-- Grant required system privileges
GRANT CONNECT, RESOURCE, CREATE VIEW TO apex_ebs_user;
GRANT CREATE SYNONYM, CREATE SESSION TO apex_ebs_user;
GRANT APEX_ADMINISTRATOR_ROLE TO apex_ebs_user;
-- Grant access to EBS APPS schema synonyms and views
GRANT SELECT ON APPS.FND_USER TO apex_ebs_user;
GRANT SELECT ON APPS.FND_RESPONSIBILITY_VL TO apex_ebs_user;
GRANT SELECT ON APPS.FND_PROFILE_OPTION_VALUES TO apex_ebs_user;
GRANT EXECUTE ON APPS.FND_GLOBAL TO apex_ebs_user;
GRANT EXECUTE ON APPS.FND_PROFILE TO apex_ebs_user;
GRANT EXECUTE ON APPS.MO_GLOBAL TO apex_ebs_user;
-- Create public synonyms for easy access
CREATE PUBLIC SYNONYM fnd_user FOR APPS.FND_USER;
CREATE PUBLIC SYNONYM fnd_responsibility_vl FOR APPS.FND_RESPONSIBILITY_VL;5. Step 2 — Install and Configure Oracle REST Data Services (ORDS)
ORDS replaces mod_plsql for APEX 20.x+ and provides the REST gateway for EBS integration. Install ORDS on the EBS web tier server.
💻 Shell — ORDS Installation & Configuration
# Extract ORDS
unzip ords-24.1.zip -d /u01/oracle/ords
cd /u01/oracle/ords
# Install ORDS with APEX static files
java -jar ords.war install advanced \
--db-hostname ebsdb01.example.com \
--db-port 1521 \
--db-servicename EBSPROD \
--admin-user SYS \
--proxy-user APEX_PUBLIC_USER \
--apex-images /u01/oracle/apex/apex/images \
--log-folder /u01/oracle/ords/logs
# Configure ORDS connection pool
java -jar ords.war configdir /u01/oracle/ords/config
# Start ORDS standalone (for testing)
java -jar ords.war standalone \
--port 8080 \
--apex-images /u01/oracle/apex/apex/images &6. Step 3 — Create APEX Workspace for EBS Integration
Create a dedicated APEX workspace that maps to the apex_ebs_user schema created earlier.
✅ Workspace Created SuccessfullyWorkspaceEBS_INTEGRATIONhas been created and linked to schemaAPEX_EBS_USER. Log in at
/ords/apex using the workspace admin credentials to begin building.7. Step 4 — Enable EBS Integrated SOA Gateway REST Services
Oracle EBS 12.2 includes Integrated SOA Gateway (ISG) which can expose EBS business APIs as REST/SOAP services. We'll enable and deploy required REST endpoints.
1
Navigate to EBS ISG AdministrationIn EBS, go to: Integrated SOA Gateway → Administration → REST Service Repository
🗄️ SQL — Create Custom EBS REST Service Package for APEX
-- Create a wrapper package to expose EBS data to APEX
CREATE OR REPLACE PACKAGE APPS.xx_apex_ebs_api AS
-- Type definitions for employee data
TYPE t_employee_rec IS RECORD (
employee_id NUMBER,
full_name VARCHAR2(240),
email VARCHAR2(240),
job_title VARCHAR2(240),
department_name VARCHAR2(240),
hire_date DATE,
status VARCHAR2(30)
);
TYPE t_employee_tab IS TABLE OF t_employee_rec;
-- Get EBS user details by username
FUNCTION get_ebs_user(
p_user_name IN VARCHAR2
) RETURN FND_USER%ROWTYPE;
-- Get employees for APEX display
FUNCTION get_employees(
p_org_id IN NUMBER DEFAULT NULL
) RETURN t_employee_tab PIPELINED;
-- Validate EBS user credentials for APEX auth
FUNCTION validate_ebs_user(
p_username IN VARCHAR2,
p_password IN VARCHAR2
) RETURN BOOLEAN;
-- Initialize EBS session for APEX user context
PROCEDURE initialize_ebs_session(
p_user_name IN VARCHAR2,
p_resp_key IN VARCHAR2,
p_app_name IN VARCHAR2,
p_org_id IN NUMBER DEFAULT NULL
);
END xx_apex_ebs_api;
/
-- Package Body
CREATE OR REPLACE PACKAGE BODY APPS.xx_apex_ebs_api AS
FUNCTION get_ebs_user(p_user_name IN VARCHAR2)
RETURN FND_USER%ROWTYPE IS
l_user FND_USER%ROWTYPE;
BEGIN
SELECT *
INTO l_user
FROM FND_USER
WHERE USER_NAME = UPPER(p_user_name)
AND NVL(END_DATE, SYSDATE+1) > SYSDATE;
RETURN l_user;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END get_ebs_user;
FUNCTION get_employees(p_org_id IN NUMBER DEFAULT NULL)
RETURN t_employee_tab PIPELINED IS
l_rec t_employee_rec;
BEGIN
FOR r IN (
SELECT p.person_id,
p.full_name,
p.email_address,
j.name job_title,
d.NAME dept_name,
a.hire_date,
a.assignment_status_type_id
FROM per_all_people_f p,
per_all_assignments_f a,
per_jobs j,
hr_all_organization_units d
WHERE p.person_id = a.person_id
AND a.job_id = j.job_id
AND a.organization_id = d.organization_id
AND (p_org_id IS NULL OR a.organization_id = p_org_id)
AND SYSDATE BETWEEN p.effective_start_date AND p.effective_end_date
AND SYSDATE BETWEEN a.effective_start_date AND a.effective_end_date
AND a.primary_flag = 'Y'
) LOOP
l_rec.employee_id := r.person_id;
l_rec.full_name := r.full_name;
l_rec.email := r.email_address;
l_rec.job_title := r.job_title;
l_rec.department_name := r.dept_name;
l_rec.hire_date := r.hire_date;
l_rec.status := r.assignment_status_type_id;
PIPE ROW(l_rec);
END LOOP;
END get_employees;
FUNCTION validate_ebs_user(
p_username IN VARCHAR2,
p_password IN VARCHAR2
) RETURN BOOLEAN IS
l_result VARCHAR2(1);
BEGIN
-- Use FND_WEB_SEC to validate EBS user password
l_result := FND_WEB_SEC.validate_login(
user_name => UPPER(p_username),
pwd => p_password
);
RETURN (l_result = 'Y');
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END validate_ebs_user;
PROCEDURE initialize_ebs_session(
p_user_name IN VARCHAR2,
p_resp_key IN VARCHAR2,
p_app_name IN VARCHAR2,
p_org_id IN NUMBER DEFAULT NULL
) IS
l_user_id NUMBER;
l_resp_id NUMBER;
l_app_id NUMBER;
BEGIN
-- Get IDs from EBS metadata
SELECT user_id INTO l_user_id
FROM fnd_user
WHERE user_name = UPPER(p_user_name);
SELECT r.responsibility_id, r.application_id
INTO l_resp_id, l_app_id
FROM fnd_responsibility_vl r,
fnd_application a
WHERE r.responsibility_key = p_resp_key
AND a.application_short_name = p_app_name
AND r.application_id = a.application_id;
-- Initialize FND Global context
FND_GLOBAL.apps_initialize(
user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_app_id
);
-- Set org context if provided
IF p_org_id IS NOT NULL THEN
MO_GLOBAL.set_policy_context('S', p_org_id);
END IF;
-- Set FND Profile values
FND_PROFILE.put('ORG_ID', TO_CHAR(p_org_id));
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,
'EBS Session Init Failed: ' || SQLERRM);
END initialize_ebs_session;
END xx_apex_ebs_api;
/8. Step 5 — APEX Authentication: EBS Single Sign-On (SSO)
Configure APEX to authenticate users against EBS using a custom authentication plugin that validates EBS credentials and sets up the user session with the correct EBS responsibility context.
🔐 PL/SQL — APEX Custom Authentication Plugin (EBS)
-- ============================================================
-- APEX Authentication Plugin: EBS SSO Authentication
-- Install this as a Custom Authentication Plugin in APEX
-- ============================================================
CREATE OR REPLACE PACKAGE apex_ebs_user.pkg_apex_ebs_auth AS
-- Authentication function called by APEX
FUNCTION authenticate(
p_authentication IN APEX_PLUGIN.t_authentication,
p_plugin IN APEX_PLUGIN.t_plugin
) RETURN APEX_PLUGIN.t_authentication_auth_result;
-- Post-login procedure: sets up EBS context
PROCEDURE post_login(
p_authentication IN APEX_PLUGIN.t_authentication,
p_plugin IN APEX_PLUGIN.t_plugin
);
-- Logout procedure: clears EBS session
PROCEDURE logout(
p_authentication IN APEX_PLUGIN.t_authentication,
p_plugin IN APEX_PLUGIN.t_plugin
);
END pkg_apex_ebs_auth;
/
CREATE OR REPLACE PACKAGE BODY apex_ebs_user.pkg_apex_ebs_auth AS
FUNCTION authenticate(
p_authentication IN APEX_PLUGIN.t_authentication,
p_plugin IN APEX_PLUGIN.t_plugin
) RETURN APEX_PLUGIN.t_authentication_auth_result IS
l_result APEX_PLUGIN.t_authentication_auth_result;
l_username VARCHAR2(100) := UPPER(p_authentication.username);
l_password VARCHAR2(100) := p_authentication.password;
l_valid BOOLEAN := FALSE;
l_resp_key VARCHAR2(100);
l_app_name VARCHAR2(30);
BEGIN
-- Validate against EBS FND_WEB_SEC
l_valid := APPS.xx_apex_ebs_api.validate_ebs_user(
p_username => l_username,
p_password => l_password
);
IF l_valid THEN
l_result.is_authenticated := TRUE;
-- Store username in APEX application item
APEX_UTIL.set_session_state(
p_name => 'APP_USER',
p_value => l_username
);
-- Get default responsibility for this user from plugin attributes
l_resp_key := p_plugin.attribute_01; -- Set in Plugin config
l_app_name := p_plugin.attribute_02;
-- Initialize EBS session context
APPS.xx_apex_ebs_api.initialize_ebs_session(
p_user_name => l_username,
p_resp_key => l_resp_key,
p_app_name => l_app_name,
p_org_id => TO_NUMBER(p_plugin.attribute_03)
);
ELSE
l_result.is_authenticated := FALSE;
l_result.error_code := 'INVALID_CREDENTIALS';
l_result.error_message := 'Invalid EBS username or password.';
END IF;
RETURN l_result;
EXCEPTION
WHEN OTHERS THEN
l_result.is_authenticated := FALSE;
l_result.error_message := 'Authentication error: ' || SQLERRM;
RETURN l_result;
END authenticate;
PROCEDURE post_login(
p_authentication IN APEX_PLUGIN.t_authentication,
p_plugin IN APEX_PLUGIN.t_plugin
) IS
l_user FND_USER%ROWTYPE;
BEGIN
l_user := APPS.xx_apex_ebs_api.get_ebs_user(APEX_APPLICATION.g_user);
-- Store EBS user details in APEX application items for use in pages
APEX_UTIL.set_session_state('APP_EBS_USER_ID', l_user.user_id);
APEX_UTIL.set_session_state('APP_EBS_USER_EMAIL', l_user.email_address);
APEX_UTIL.set_session_state('APP_EBS_EMPLOYEE_ID',l_user.employee_id);
END post_login;
PROCEDURE logout(
p_authentication IN APEX_PLUGIN.t_authentication,
p_plugin IN APEX_PLUGIN.t_plugin
) IS
BEGIN
-- Clear EBS session (FND globals are PL/SQL package vars, reset on logout)
APEX_AUTHENTICATION.logout(
p_logout_url => p_plugin.attribute_04
);
END logout;
END pkg_apex_ebs_auth;
/9. Step 6 — Build APEX Application with EBS Data
Create a sample APEX application that displays EBS employee data, purchase orders, and provides a dashboard. Below is the core SQL and APEX page source for a typical EBS HR Dashboard.
9.1 APEX Interactive Report — EBS Purchase Orders
🗄️ SQL — APEX Region Source: PO Dashboard Query
-- APEX Interactive Report Region Source
-- Page: Purchase Orders Dashboard
-- Region Source Query (uses EBS APPS schema views)
SELECT
ph.segment1 po_number,
ph.type_lookup_code po_type,
vd.vendor_name supplier_name,
vd.vendor_site_code supplier_site,
ph.currency_code currency,
ph.amount_limit total_amount,
pl.line_num line_num,
pl.item_description description,
pl.quantity qty_ordered,
pl.unit_meas_lookup_code uom,
pl.unit_price unit_price,
(pl.quantity * pl.unit_price) line_total,
ph.status_lookup_code status,
CASE ph.status_lookup_code
WHEN 'APPROVED' THEN '<span class="apex-badge green">Approved</span>'
WHEN 'IN PROCESS'THEN '<span class="apex-badge orange">In Process</span>'
WHEN 'INCOMPLETE' THEN '<span class="apex-badge red">Incomplete</span>'
ELSE '<span class="apex-badge grey">'
|| ph.status_lookup_code
|| '</span>'
END status_badge,
ph.creation_date created_date,
fu.user_name created_by,
ph.org_id org_id
FROM
APPS.po_headers_all ph,
APPS.po_lines_all pl,
APPS.po_vendors vd,
APPS.fnd_user fu
WHERE
ph.po_header_id = pl.po_header_id
AND ph.vendor_id = vd.vendor_id
AND ph.created_by = fu.user_id
AND ph.org_id = NVL(:P_ORG_ID, ph.org_id)
AND ph.type_lookup_code IN ('STANDARD', 'BLANKET')
AND ph.creation_date >= NVL(:P_FROM_DATE, ph.creation_date - 90)
AND ph.creation_date <= NVL(:P_TO_DATE, SYSDATE)
ORDER BY
ph.creation_date DESC, ph.segment1;9.2 APEX Page Process — Submit EBS Purchase Requisition
🔄 PL/SQL — APEX Page Process: Create EBS Requisition
-- APEX Page Process: "Submit Requisition to EBS"
-- Page: New Requisition (Page 10)
-- Process Point: After Submit
DECLARE
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_requisition_id NUMBER;
l_requisition_num VARCHAR2(30);
l_segment1 VARCHAR2(30);
-- Requisition Header record
l_header_rec PO_REQUISITION_HEADERS_ALL%ROWTYPE;
BEGIN
-- Step 1: Initialize EBS context for submitting user
APPS.xx_apex_ebs_api.initialize_ebs_session(
p_user_name => :APP_USER,
p_resp_key => 'PURCHASING_BUYER',
p_app_name => 'PO',
p_org_id => TO_NUMBER(:P10_ORG_ID)
);
-- Step 2: Call the EBS PO Requisition API
PO_REQ_LINES_SV.create_requisition (
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_requisition_header_id=> l_requisition_id,
x_segment1 => l_segment1,
p_org_id => TO_NUMBER(:P10_ORG_ID),
p_preparer_id => TO_NUMBER(:APP_EBS_EMPLOYEE_ID),
p_type_lookup_code => 'PURCHASE',
p_description => :P10_DESCRIPTION,
p_item_id => TO_NUMBER(:P10_ITEM_ID),
p_quantity => TO_NUMBER(:P10_QUANTITY),
p_unit_price => TO_NUMBER(:P10_UNIT_PRICE),
p_uom_code => :P10_UOM,
p_need_by_date => TO_DATE(:P10_NEED_BY_DATE, 'DD-MON-YYYY'),
p_destination_type => :P10_DEST_TYPE,
p_deliver_to_location => TO_NUMBER(:P10_LOCATION_ID)
);
-- Step 3: Handle result
IF l_return_status = FND_API.g_ret_sts_success THEN
-- Store created requisition number in APEX item
:P10_REQ_NUMBER := l_segment1;
:P10_REQ_ID := l_requisition_id;
APEX_APPLICATION.g_print_success_message :=
'Requisition ' || l_segment1 || ' created successfully!';
ELSE
-- Collect and display EBS error messages
FOR i IN 1 .. l_msg_count LOOP
l_msg_data := FND_MSG_PUB.get(
p_msg_index => i,
p_encoded => 'F'
);
END LOOP;
APEX_ERROR.add_error(
p_message => 'EBS Error: ' || l_msg_data,
p_display_location => APEX_ERROR.c_inline_in_notification
);
END IF;
EXCEPTION
WHEN OTHERS THEN
APEX_ERROR.add_error(
p_message => 'Unexpected error: ' || SQLERRM,
p_display_location => APEX_ERROR.c_inline_in_notification
);
END;10. Step 7 — EBS REST API Integration from APEX
Use APEX Web Source Modules to consume EBS REST services exposed via ISG, or call ORDS REST APIs directly from APEX Dynamic Actions.
10.1 APEX Web Source Module — Consuming EBS REST API
🔌 JavaScript — APEX Dynamic Action: Call EBS REST API
// APEX Dynamic Action — JavaScript Code
// Trigger: Button "Load Employee Data"
// Action: Execute JavaScript Code
async function loadEBSEmployees() {
const orgId = $v('P5_ORG_ID') || 101;
// Call ORDS REST endpoint backed by EBS data
const response = await fetch(
`/ords/apex_ebs_user/employees/?org_id=${orgId}`,
{
method: 'GET',
headers: {
'Accept': 'application/json',
'Content-Type': 'application/json',
'Authorization': 'Bearer ' + $v('APP_SESSION_TOKEN')
}
}
);
if (!response.ok) {
apex.message.showErrors([{
type: 'error',
location: 'page',
message: 'Failed to load employee data: ' + response.statusText
}]);
return;
}
const data = await response.json();
// Populate APEX interactive grid or use apex.region
apex.region('emp-report').refresh();
apex.item('P5_EMP_COUNT').setValue(data.totalElements);
// Show success notification
apex.message.showPageSuccess(
`Loaded ${data.items.length} employees from EBS`
);
}
// Execute
loadEBSEmployees();10.2 ORDS REST Service on EBS Data
🗄️ SQL — Enable ORDS REST on EBS Employee View
-- Enable ORDS REST on the APEX_EBS_USER schema
-- Connect as apex_ebs_user
connect apex_ebs_user/"Welcome#Apex1"@EBSPROD
-- Enable ORDS for this schema
BEGIN
ORDS.enable_schema(
p_enabled => TRUE,
p_schema => 'APEX_EBS_USER',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'apex_ebs_user',
p_auto_rest_auth => TRUE
);
COMMIT;
END;
/
-- Create a REST module for EBS data
BEGIN
ORDS.define_module(
p_module_name => 'ebs.api.v1',
p_base_path => 'ebs/v1/',
p_items_per_page => 100,
p_status => 'PUBLISHED'
);
-- Employees endpoint
ORDS.define_template(
p_module_name => 'ebs.api.v1',
p_pattern => 'employees/'
);
ORDS.define_handler(
p_module_name => 'ebs.api.v1',
p_pattern => 'employees/',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source =>
'SELECT employee_id, full_name, email, job_title, '
|| ' department_name, hire_date, status '
|| 'FROM TABLE(APPS.xx_apex_ebs_api.get_employees('
|| ' p_org_id => :org_id))',
p_items_per_page => 200
);
-- Employee by ID endpoint
ORDS.define_template(
p_module_name => 'ebs.api.v1',
p_pattern => 'employees/:emp_id'
);
ORDS.define_handler(
p_module_name => 'ebs.api.v1',
p_pattern => 'employees/:emp_id',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_item,
p_source =>
'SELECT p.person_id, p.full_name, p.email_address,
j.name job_title, d.name department,
a.hire_date, p.national_identifier
FROM per_all_people_f p, per_all_assignments_f a,
per_jobs j, hr_all_organization_units d
WHERE p.person_id = a.person_id
AND a.job_id = j.job_id (+)
AND a.organization_id = d.organization_id (+)
AND p.person_id = :emp_id
AND SYSDATE BETWEEN p.effective_start_date
AND p.effective_end_date'
);
COMMIT;
END;
/11. Troubleshooting Common Issues
| Issue | Cause | Resolution |
|---|---|---|
| ORA-01403: No Data Found in FND_USER | User doesn't exist or is end-dated in EBS | Check SELECT * FROM fnd_user WHERE user_name='XX'; ensure END_DATE is null or future |
| APEX Login Loop | Authentication plugin returns null | Add debug logging in authenticate(); check FND_WEB_SEC.validate_login returns 'Y' |
| ORA-20001: EBS Session Init Failed | Invalid responsibility key or app name | Query SELECT responsibility_key FROM fnd_responsibility_vl to verify exact key |
| ORDS 404 on REST endpoint | Module not published or schema not enabled | Run ORDS.enable_schema and verify module status is PUBLISHED |
| OHS mod_plsql errors | DAD configuration missing for APEX | Add APEX DAD in dads.conf: PlsqlDatabasePassword for APEX_PUBLIC_USER |
| MO_GLOBAL not set error | Multi-Org context not initialized | Call MO_GLOBAL.init('PO') before any multi-org EBS API |
🔍 SQL — Diagnostic Queries
-- Check APEX installation status
SELECT version, status, last_updated_by
FROM apex_release;
-- Check ORDS REST modules
SELECT name, uri_prefix, status
FROM user_ords_modules;
-- Check EBS user is active
SELECT user_id, user_name, start_date,
NVL(end_date, TO_DATE('31-DEC-2099','DD-MON-YYYY')) end_date
FROM fnd_user
WHERE user_name = 'SYSADMIN';
-- Check active EBS responsibilities
SELECT r.responsibility_key,
r.responsibility_name,
a.application_short_name,
ur.start_date, ur.end_date
FROM fnd_user_resp_groups_direct ur,
fnd_responsibility_vl r,
fnd_application a
WHERE ur.user_id = FND_GLOBAL.user_id
AND ur.responsibility_id = r.responsibility_id
AND r.application_id = a.application_id
AND NVL(ur.end_date, SYSDATE+1) > SYSDATE;
-- Monitor APEX workspace sessions
SELECT workspace, user_name, login_name,
to_char(session_created,'DD-MON-YYYY HH24:MI') created,
last_request_on
FROM apex_workspace_sessions
ORDER BY last_request_on DESC
FETCH FIRST 20 ROWS ONLY;12. Summary & Next Steps
✅ You have successfully completed:APEX installation on EBS DB → ORDS configuration → Workspace creation → Custom EBS Authentication → PL/SQL API package → APEX pages with EBS data → ORDS REST services
With this integration in place, your team can rapidly build modern web applications on top of Oracle EBS without modifying core EBS code. Some recommended next steps:
- Implement Oracle Access Manager (OAM) for enterprise-grade SSO between EBS and APEX
- Use APEX Approvals to build EBS workflow approval interfaces
- Configure APEX Push Notifications for EBS concurrent program completion alerts
- Build APEX REST Data Sources to consume Oracle EBS Cloud REST APIs for hybrid deployments
- Implement Row-Level Security by filtering all APEX queries by
:APP_EBS_USER_ID - Apply MOS Patch 30733814 for APEX 23+ compatibility with EBS ICX tables
📚 References & Further ReadingMOS Note 1108523.1 (APEX on EBS Certification) · MOS Note 2277035.1 (APEX EBS Integration Guide) · Oracle ORDS Documentation 24.1 · Oracle EBS ISG REST Services Guide (Doc ID 1531929.1)
Techvisions · Cloud, AI & Managed Infrastructure(www.techvisions.sa)
Comments