Thursday 21 January 2016
Integrating APEX with Oracle E-Business Suite :
In order to connect from Oracle E-Business Suite to your new Oracle Application Express
Applications, a mechanism is required to hand control from Oracle E-Business Suite to Oracle Application Express. The following actions need to be performed:
We are going to use the cookie settings in the icx_sessions table of Oracle E-Business Suite.
Because, 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
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>
Next>
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 Authorization Scheme
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;
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.
In order to connect from Oracle E-Business Suite to your new Oracle Application Express
Applications, a mechanism is required to hand control from Oracle E-Business Suite to Oracle Application Express. The following actions need to be performed:
1. Create an Oracle Application Express application
2. Configure the Oracle Application Express login process if you want to authenticate user before accessing the application.
3. Define Oracle Application Express authorizations
4. Define Oracle E-Business Suite profile and functions
5. Link functions to menus and responsibility
Perform below activities from APEX side :
Creating an Oracle Application Express ApplicationWe are going to use the cookie settings in the icx_sessions table of Oracle E-Business Suite.
Because, 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) |
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>
Next>
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 Authorization Scheme
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;
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.
Perform below activities from EBS side :
Update the FND: APEX URL profile option with the correct setting at the site level using the following steps:
1. Log in to Oracle E-Business Suite with
the SYSADMIN user
(http ://<EBS_Hostname>:8074/OA_HTML/AppsLogin)
2. Navigate to the System Administrator
responsibility > Profile > System menu option
3. Search for Profile %APEX%, click Find
4. for profile FND: APEX URL enter Site http://<EBS_Hostname>:8080/apex
5.
Save the profile
Perform the following steps to define the
Oracle E-Business Suite functions:
1. Navigate to the System Administrator responsibility
> Application > Function menu option
2. For calls to the page without
responsibility, create a function with the following details:
Description -
Function: APEX_FUN
User
Function Name: APEX Extension
Properties
–
Type:
JSP
Web HTML –
HTML Call: GWY.jsp?targetAppType=APEX&
p=<APEX Application Id>:
<APEX
Page>:<Session>:<Request>:<Debug>:<Clear Cache>:
<Parameter Pairs>
{ For example, to call Oracle Application
Express application 104, Page 1
use GWY.jsp?targetAppType=APEX&p=104:1:0::NO:1:
, all other parameters are
optional}
Add menu component under required Menu as
shown below-
Menu
Name:
User
Menu Name : [Specify User Menu Name here]
Menu
Type: Standard
3.
Create menu component –
Sequence:
1
Prompt:
Launch APEX Page
Function: APEX Extension
4.
Save the menu.
Thanks!
ReplyDeleteOn which node Apex has to be installed. Is it on Dbtier or Appstier. Can you please provide the steps.
ReplyDeletehttps://EBS_HOSTNAME:8080/apex
https://michaelhichwa.blogspot.com/2015/03/apexoraclecom-has-been-upgraded-to-pre.html?showComment=1602484981880#c4296175550814934023
ReplyDeleteThanks for sharing this blog. The content is beneficial and useful. Very informative post. Visit here to learn more about Data Warehousing companies and Data analytics Companies. I am impressed by the information that you have on this blog. Thanks once more for all the details.Visit here for Top Big Data Companies.
ReplyDelete