Oracle Apex and EBS Integration

Configuration from the Oracle APEX Perspective
Create Application in Workspace.
Create Application Items for Storing EBS Session Values.
Navigate to Authentication Schemes, Create Custom Authentication with 'No Authentication,' and Set it as the Current Authentication Scheme.

Navigate to Authorization Schemes and Create a New Custom Authorization Scheme Set to 'Once per Session.

Here first need to create XXAPX_APEX_GLOBAL_PKG and related procedures in APPS scheme or we can create it in custom scheme.
Navigate to Security Attributes and Implement the Following Changes.
Choose Your Custom Authentication Scheme as the Selected Authentication Scheme.

In the Database Session section, Insert the Following Initialization PL/SQL Code.

BEGIN

APPS.XXAPX_APEX_GLOBAL_PKG.APPS_INITIALIZE(

p_user_id => v('EBS_USER_ID')

,p_resp_id => v('EBS_RESP_ID')

,p_resp_appl_id => v('EBS_RESP_APPL_ID')

,p_security_group_id => v('EBS_SECURITY_GROUP_ID')

,p_server_id => NULL

,p_org_id => v('EBS_ORG_ID'));

END;

1. Global Package in the Apps Schema for Retrieving EBS Session Values.

/* Package Specification*/

create or replace PACKAGE XXAPX_APEX_GLOBAL_PKG AUTHID DEFINER AS

-- DEBUG

PROCEDURE debug (p_string VARCHAR2);

-- PAGE_ITEM_EXISTS

FUNCTION PAGE_ITEM_EXISTS(p_name IN VARCHAR2) RETURN BOOLEAN;

-- AUTO_LOGIN

FUNCTION AUTO_LOGIN

RETURN BOOLEAN;

-- APPS_INITIALIZE

PROCEDURE APPS_INITIALIZE(p_user_id IN NUMBER

,p_resp_id IN NUMBER

,p_resp_appl_id IN NUMBER

,p_security_group_id IN NUMBER DEFAULT 0

,p_server_id IN NUMBER DEFAULT -1

,p_org_id IN NUMBER DEFAULT -1);

-- VALIDATE_LOGIN

FUNCTION VALIDATE_LOGIN(p_username IN VARCHAR2

,p_password IN VARCHAR2)

RETURN BOOLEAN;

END XXAPX_APEX_GLOBAL_PKG;

/* Package Body*/

create or replace PACKAGE BODY XXAPX_APEX_GLOBAL_PKG AS

-- DEBUG

PROCEDURE debug (p_string VARCHAR2)

-- PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

--NULL;

INSERT INTO XXDPC_DEBUG_MSG VALUES (p_string);

COMMIT;

END;

-- PAGE_ITEM_EXISTS

FUNCTION PAGE_ITEM_EXISTS(p_name IN VARCHAR2) RETURN BOOLEAN IS

-- Cursor with Application Item info

CURSOR cur_api(pc_name VARCHAR2) IS

SELECT ITEM_NAME

FROM APEX_APPLICATION_ITEMS

WHERE APPLICATION_ID = v('APP_ID')

AND ITEM_NAME = UPPER(pc_name);

lv_item_name APEX_APPLICATION_PAGE_ITEMS.ITEM_NAME%TYPE := NULL;

BEGIN

IF (cur_api%ISOPEN) THEN

CLOSE cur_api;

END IF;

OPEN cur_api(pc_name => p_name);

FETCH cur_api

INTO lv_item_name;

CLOSE cur_api;

IF (lv_item_name IS NOT NULL) THEN

RETURN(TRUE);

ELSE

RETURN(FALSE);

END IF;

EXCEPTION

WHEN OTHERS THEN

RETURN(FALSE);

END PAGE_ITEM_EXISTS;

-- AUTO_LOGIN

FUNCTION AUTO_LOGIN

RETURN BOOLEAN IS

l_user_id ICX_SESSIONS.USER_ID%TYPE;

l_session_id ICX_SESSIONS.SESSION_ID%TYPE;

l_resp_id ICX_SESSIONS.RESPONSIBILITY_ID%TYPE;

l_function_id ICX_SESSIONS.FUNCTION_ID%TYPE;

l_resp_appl_id ICX_SESSIONS.RESPONSIBILITY_APPLICATION_ID%TYPE;

l_security_group_id ICX_SESSIONS.SECURITY_GROUP_ID%TYPE;

l_org_id ICX_SESSIONS.ORG_ID%TYPE;

l_time_out ICX_SESSIONS.TIME_OUT%TYPE;

l_ebs_url ICX_SESSION_ATTRIBUTES.VALUE%TYPE;

l_user_name FND_USER.USER_NAME%TYPE;

l_function_name FND_FORM_FUNCTIONS_VL.FUNCTION_NAME%TYPE;

l_authorized BOOLEAN;

L varchar2(32000);

-- Get the context information from the icx session cookie.

CURSOR c_session IS

SELECT ISE.USER_ID

,FUS.USER_NAME

,ISE.SESSION_ID

,ISE.RESPONSIBILITY_ID

,ISE.FUNCTION_ID

,ISE.RESPONSIBILITY_APPLICATION_ID

,ISE.SECURITY_GROUP_ID

,ISE.ORG_ID

,ISE.TIME_OUT

,(select ISA.VALUE from ICX_SESSION_ATTRIBUTES ISA

where ISA.SESSION_ID = ISE.SESSION_ID

AND ISA.NAME = '_USERORSSWAPORTALURL'

)value

FROM

ICX_SESSIONS ISE

,FND_USER FUS

WHERE-- (UPPER(FUS.USER_NAME) = UPPER(OWA_UTIL.GET_CGI_ENV('OAM_REMOTE_USER')) OR

ISE.SESSION_ID = APPS.ICX_SEC.GETSESSIONCOOKIE

AND ISE.USER_ID = FUS.USER_ID

AND ISE.DISABLED_FLAG = 'N'

AND ISE.LAST_CONNECT + NUMTODSINTERVAL(ISE.TIME_OUT,'MINUTE') > SYSDATE

order by LAST_CONNECT desc

-- AND ISA.SESSION_ID = ISE.SESSION_ID

;

CURSOR c_function(pc_function_id FND_FORM_FUNCTIONS_VL.FUNCTION_ID%TYPE) IS

SELECT FUNCTION_NAME

FROM FND_FORM_FUNCTIONS_VL

WHERE FUNCTION_ID = pc_function_id;

BEGIN

-- debug ('1 Remote user:'||OWA_UTIL.GET_CGI_ENV('OAM_REMOTE_USER')||'ICX SESSION Cookie:'||APPS.ICX_SEC.GETSESSIONCOOKIE);

OPEN c_session;

FETCH c_session

INTO l_user_id

,l_user_name

,l_session_id

,l_resp_id

,l_function_id

,l_resp_appl_id

,l_security_group_id

,l_org_id

,l_time_out

,l_ebs_url;

CLOSE c_session;

debug('2 User Name:'||l_user_name|| 'function id:'||l_function_id||',resp_id:'||l_resp_id||'resp appl id'||l_resp_appl_id);

--Start of Code Addition by SCHIVUKULA-KESTE as part of Defect#742. 17-MAR-2022

IF l_function_id IS NULL THEN

BEGIN

SELECT fff.function_id,frt.responsibility_id,fr.application_id

INTO l_function_id,l_resp_id,l_resp_appl_id

FROM fnd_user_resp_groups_direct furg,

fnd_user fu,

fnd_responsibility_tl frt,

fnd_responsibility fr,

fnd_application_tl fat,

fnd_application fa,

fnd_menu_entries_vl fme,

fnd_form_functions_tl fff

WHERE furg.user_id = fu.user_id

AND furg.responsibility_id = frt.responsibility_id

AND fr.responsibility_id = frt.responsibility_id

AND fa.application_id = fat.application_id

AND fr.application_id = fat.application_id

AND frt.LANGUAGE = Userenv('LANG')

AND fme.menu_id = fr.menu_id

AND fme.function_id = fff.function_id

AND fme.grant_flag = 'Y'

AND fu.user_id = l_user_id

AND ( furg.end_date IS NULL

OR furg.end_date >= Trunc(SYSDATE) )

AND (

fr.end_date IS NULL

fr.end_date > trunc(SYSDATE)

)

/*AND EXISTS (SELECT '1'

FROM fnd_lookup_values

WHERE

lookup_type = 'XXEPC_APEX_AUTH_FUNC'

AND meaning = fff.user_function_name

AND ( end_date_active IS NULL

OR end_date_active >= Trunc(SYSDATE)

)

AND enabled_flag = 'Y') */

AND ROWNUM=1; --In case the Function is Assigned under Multiple Responsibilities

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_function_id := -1;

WHEN OTHERS THEN

l_function_id := NULL;

END;

END IF;

debug('3 User Name:'||l_user_name|| 'function id:'||l_function_id||',resp_id:'||l_resp_id||'resp appl id'||l_resp_appl_id);

IF l_ebs_url is null THEN

l_ebs_url := substr(fnd_profile.value('ICX_PAY_SERVER'),1,instr( fnd_profile.value('ICX_PAY_SERVER'),'ibyecapp')-1)||'OA.jsp?OAFunc=OANEWHOMEPAGE';

END IF;

OPEN c_function(pc_function_id => l_function_id);

FETCH c_function

INTO l_function_name;

CLOSE c_function;

debug('4 Function Name:'||l_function_name||'EBS URL:'||l_ebs_url);

IF (ICX_SEC.CHECK_SESSION(l_session_id) = 'VALID') THEN

APEX_UTIL.SET_SESSION_STATE('EBS_RESP_ID',l_resp_id);

APEX_UTIL.SET_SESSION_STATE('EBS_RESP_APPL_ID',l_resp_appl_id);

APEX_UTIL.SET_SESSION_STATE('EBS_SECURITY_GROUP_ID',l_security_group_id);

APEX_UTIL.SET_SESSION_STATE('EBS_USER_ID',l_user_id);

APEX_UTIL.SET_SESSION_STATE('EBS_USER_NAME',l_user_name);

APEX_UTIL.SET_SESSION_STATE('EBS_ORG_ID',TO_CHAR(l_org_id));

APEX_UTIL.SET_SESSION_STATE('EBS_URL',l_ebs_url);

APEX_UTIL.SET_SESSION_STATE('EBS_SESSION_ID',APPS.ICX_SEC.GETSESSIONCOOKIE);

IF (PAGE_ITEM_EXISTS(p_name => 'EBS_BASE_URL')) THEN

APEX_UTIL.SET_SESSION_STATE('EBS_BASE_URL',SUBSTR(l_ebs_url,1,INSTR(l_ebs_url,'/OA_HTML')));

END IF;

APPS_INITIALIZE(p_user_id => v('EBS_USER_ID')

,p_resp_id => v('EBS_RESP_ID')

,p_resp_appl_id => v('EBS_RESP_APPL_ID')

,p_security_group_id => v('EBS_SECURITY_GROUP_ID')

,p_server_id => NULL

,p_org_id => v('EBS_ORG_ID'));

IF (FND_FUNCTION.TEST(l_function_name)) THEN

l_authorized := TRUE;

-- Call the standard apex login procedure to initialize a valid APEX session

WWV_FLOW_CUSTOM_AUTH_STD.LOGIN(p_uname => l_user_name

,p_password => ''

,p_session_id => APEX_UTIL.GET_SESSION_STATE('APP_SESSION'));

debug('5 After Auto Login');

--modified to derive the url from the correct responsibility that the user has if the function test fails

ELSIF l_function_name is not null THEN

BEGIN

SELECT fff.function_id,frt.responsibility_id,fr.application_id

INTO l_function_id,l_resp_id,l_resp_appl_id

FROM fnd_user_resp_groups_direct furg,

fnd_user fu,

fnd_responsibility_tl frt,

fnd_responsibility fr,

fnd_application_tl fat,

fnd_application fa,

fnd_menu_entries_vl fme,

fnd_form_functions_tl fff

WHERE furg.user_id = fu.user_id

AND furg.responsibility_id = frt.responsibility_id

AND fr.responsibility_id = frt.responsibility_id

AND fa.application_id = fat.application_id

AND fr.application_id = fat.application_id

AND frt.LANGUAGE = Userenv('LANG')

AND fme.menu_id = fr.menu_id

AND fme.function_id = fff.function_id

and fff.function_id = l_function_id

AND fme.grant_flag = 'Y'

AND fu.user_id = l_user_id

AND ( furg.end_date IS NULL

OR furg.end_date >= Trunc(SYSDATE) )

AND (

fr.end_date IS NULL

fr.end_date > trunc(SYSDATE)

) ;

debug('6 New Responsibility Id:'||l_resp_id||'Resp appl id:'||l_resp_appl_id);

APEX_UTIL.SET_SESSION_STATE('EBS_RESP_ID',l_resp_id);

APEX_UTIL.SET_SESSION_STATE('EBS_RESP_APPL_ID',l_resp_appl_id);

APPS_INITIALIZE(p_user_id => v('EBS_USER_ID')

,p_resp_id => v('EBS_RESP_ID')

,p_resp_appl_id => v('EBS_RESP_APPL_ID')

,p_security_group_id => v('EBS_SECURITY_GROUP_ID')

,p_server_id => NULL

,p_org_id => v('EBS_ORG_ID'));

IF (FND_FUNCTION.TEST(l_function_name)) THEN

l_authorized := TRUE;

-- Call the standard apex login procedure to initialize a valid APEX session

WWV_FLOW_CUSTOM_AUTH_STD.LOGIN(p_uname => l_user_name

,p_password => ''

,p_session_id => APEX_UTIL.GET_SESSION_STATE('APP_SESSION'));

debug('7 After Auto Login');

ELSE

l_authorized := FALSE;

debug('8 Function Test failed');

END IF;

exception

when no_data_found

then

l_authorized := FALSE;

debug('9 Function Test failed');

END;

ELSE

l_authorized := FALSE;

debug('10 Function Test failed');

END IF;

ELSE

l_authorized := FALSE;

debug('11 ICX Session is not authorized');

END IF;

RETURN l_authorized;

END AUTO_LOGIN;

-- APPS_INITIALIZE

PROCEDURE APPS_INITIALIZE(p_user_id IN NUMBER

,p_resp_id IN NUMBER

,p_resp_appl_id IN NUMBER

,p_security_group_id IN NUMBER DEFAULT 0

,p_server_id IN NUMBER DEFAULT -1

,p_org_id IN NUMBER DEFAULT -1) IS

CURSOR c_application(pc_application_id FND_APPLICATION.APPLICATION_ID%TYPE) IS

SELECT APPLICATION_SHORT_NAME

FROM FND_APPLICATION

WHERE APPLICATION_ID = pc_application_id;

l_application_short_name FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE;

BEGIN

OPEN c_application(pc_application_id => p_resp_appl_id);

FETCH c_application

INTO l_application_short_name;

CLOSE c_application;

FND_GLOBAL.APPS_INITIALIZE(user_id => p_user_id

,resp_id => p_resp_id

,resp_appl_id => p_resp_appl_id

,security_group_id => p_security_group_id

,server_id => p_server_id);

--MO_GLOBAL.INIT(p_appl_short_name => l_application_short_name);

MO_GLOBAL.SET_POLICY_CONTEXT('S',p_org_id);

END APPS_INITIALIZE;

-- VALIDATE_LOGIN

FUNCTION VALIDATE_LOGIN(p_username IN VARCHAR2

,p_password IN VARCHAR2)

RETURN BOOLEAN IS

BEGIN

RETURN FND_USER_PKG.VALIDATELOGIN(username => p_username

,password => p_password);

END VALIDATE_LOGIN;

END XXAPX_APEX_GLOBAL_PKG;

1. Configuring EBS to Invoke an Oracle APEX Page

Log into Oracle EBS with the System Administrator User.
Within the System Administrator menu, select 'Define Profile Option' to access Oracle Fusion Applications.

In the Profile -> System section, search for profiles containing '%APEX%' and click 'Find'.

It will open the following page. In the 'Site' option, you need to enter the Apex Application Base URL.

Ex. http://applupg1.dapeco.com.om:8081/apex

In 'Application -> Function,' create a function to open an Apex page from the EBS menu.

In Html call need to pass url and parameters for Ex.

GWY.jsp?targetAppType=APEX&p=BUDGET-MANAGEMENT-SYSTEM:HOME:::::EBS_RESP_ID,EBS_RESP_APPL_ID,EBS_SECURITY_GROUP_ID:[RESPONSIBILITY_ID],[RESP_APPL_ID],[SECURITY_GROUP_ID]