To access eBS functionality through SQL clients or PL/SQL, it can be necessary to initialize an eBS session.
For example when you need to reference eBS views, access API’s or start concurrent requests from outside eBS.
To initialize an eBS session the API FND_GLOBAL.APPS_INITIALIZE can be used. It has 3 required parameters and 2 optional parameters:
USER_ID -> This is the FND_USER.USER_ID for which is a session is initialized.
RESP_ID -> The FND_RESPONSIBILITY.RESPONSIBILITY_ID that will be used in this session.
RESP_APPL_ID -> The FND_RESPONSIBILITY.APPLICATION_ID for the RESPONSIBILITY_ID.
SECURITY_GROUP_ID -> An optional SECURITY_GROUP_ID. (Default = 0)
SERVER_ID -> An optional SERVER_ID. (Optional, default = -1)
The SERVER_ID will mostly be used in case of a DMZ or server dependent services.
The APPS_INITIALIZE procedure will accept any numeric value for the parameters. But the session will only be initialized successfully with at least a valid USER_ID.
In this article, we will look at the session initialization through APPS_INITIALIZE. The following steps are executed:
– Set up a PL/SQL table in FND_GLOBAL with some session variables. These variables are accessible through functions in FND_GLOBAL.
– Set up a sys_context (‘FND’) with the same variables.
– Set the organization context.
– Set up the Security Profile in case of HR Security. I will document the details of the HR Security later and skip the details in this article.
– Set the Consumer Resource Group.
– Execute product specific initialization routines.
– Execute the Custom SQL Initialization routine.
In the FND_GLOBAL package body a PL/SQL table is set up to hold session variables. Not all available variables will be set through APPS_INITIALIZE (e.g. CONC_REQUEST_ID that is only set on submitting a concurrent request). And not all variables are available (e.g. CUSTOMER_ID which is derived from the customer field in FND_USER).
Undefined variables are set to -1 for NUMBER values and to NULL for VARCHAR values.
|USER_ID||User_id from FND_USER||Required, not validated|
|USER_NAME||User_name from FND_USER||Optional, based on USER_ID|
|RESP_ID||Responsibility_id||Optional, not validated|
|RESP_NAME||Responsibility_name||Optional, based on RESP_ID|
|RESP_APPL_ID||Resp. application_id||Optional, based on RESP_ID|
|APPLICATION_NAME||Resp. application_name||Optional, based on RESP_APPL_ID|
|APPLICATION_SHORT_NAME||Resp. application_short_name||Optional, based on RESP_APPL_ID|
|SECURITY_GROUP_ID||Security Group ID||Optional, validated|
|PER_BUSINESS_GROUP_ID||Business Group ID||Derived from Profile Option|
|PER_SECURITY_PROFILE_ID||HR Security Profile||Derived from Profile Option|
|BASE_LANGUAGE||Base Language||From FND_LANGUAGES|
|EMPLOYEE_ID||User employee_id||Optional, from FND_USER|
|CUSTOMER_ID||User customer_id||Optional, from FND_USER|
|SUPPLIER_ID||User supplier_id||Optional, from FND_USER|
|SESSION_ID||Unique session identifier||AUDSID from v$session|
|SERVER_ID||Server_id if entered as param.||Optional from FND_NODES|
|ORG_ID||Operating Unit ID||Derived from Profile Option|
|ORG_NAME||Operating Unit Name||Based on ORG_ID|
These variables are also stored in a SYS_CONTEXT with name ‘FND’:
SQL> select FND_GLOBAL.USER_NAME from dual; USER_NAME ------------------------------------------- SYSADMIN SQL> select sys_context('FND','USER_NAME') from dual; SYS_CONTEXT('FND','USER_NAME') -------------------------------------------------------- SYSADMIN
There will however be NO noticeable performance difference between the two. So I advise you to use FND_GLOBAL as it is the documented solution.
The algorithm to derive the Organization Context is as follows.
For pre-R12 environments: Use profile option ‘MO: Operating Unit’ (ORG_ID).
For R12 and beyond, the MOAC structure can be used. First check if profile option ‘MO: Security Profile’ (XLA_MO_SECURITY_PROFILE_LEVEL) is set.
If this profile is set, check profile option ‘MO: Default Operating Unit’ (DEFAULT_ORG_ID).
If DEFAULT_ORG_ID is not set, the ORG_ID will not be initialized.
If XLA_MO_SECURITY_PROFILE_LEVEL is not set, profile option ‘MO: Operating Unit’ (ORG_ID) is used.
The ORG_ID and SECURITY_GROUP_ID are then stored in the sessions CLIENT_INFO. ORG_ID starts at position 1 and is limited to 10 characters. The SECURITY_GROUP_ID starts at position 55.
The CLIENT_INFO is the most common check for eBS database views to verify Organization Context.
Profile Option Cache
After setting the variables, the Profile Option Value cache is initialized. However APPS_INITIALIZE does not set any profile option values yet. It only sets up the required cache (which is also a PL/SQL table).
A number of profile options are used for the initialization, but they are not yet stored in the Profile Option Value cache. In an upcoming article, I’ll go into more detail about the Profile Option Value Cache and the way eBS uses it
Then the security context is initialized. The ORG_ID and SECURITY_GROUP_ID are set in the sessions CLIENT_INFO. ORG_ID is stored in the first 10 characters, while SECURITY_GROUP_ID is stored from the 55th position.
Resource Consumer Group
In the profile option ‘FND: Resource Consumer Group’ (FND_RESOURCE_CONSUMER_GROUP) a Resource Consumer Group can be selected. With this setting the database Resource Manager can limit the database resources that the user can use. If this profile option is set, APPS_GLOBAL.APPS_INITIALIZE will alter the session to use the required Resource Consumer Group.
Individual Oracle Products can execute their own initialization routines. The defined routines are in table FND_PRODUCT_INITIALIZATION. Each initialization can be executed based on specific conditions. These conditions are stored in FND_PRODUCT_INIT_CONDITION.
The setup of these initialization routines is done through the package FND_PRODUCT_INITIALIZATION_PKG. The APPS_INITIALIZE will check the conditions and dependencies on the initialization routines and execute the routines in the required order.
You may be aware that you can also set up your own Custom Initialization Routine through the profile option: ‘Initialization SQL Statement – Custom’. If this profile is set, it will also be executed through FND_GLOBAL.APPS_INITIALIZE.
APPS_INITIALIZE has now finished initializing your session. Other eBS software can use the FND_GLOBAL functions, the SYS_CONTEXT or CLIENT_INFO to verify the session and security settings.
P.S. For quick scripts: stiffmaster.nl