FND_GLOBAL.APPS_INITIALIZE: Initialize eBS sessions in SQL and PL/SQL

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.

FND_GLOBAL

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.

Name Description Remarks
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
CURRENT_LANGUAGE From userenv(‘LANG’)
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
PARTY_ID User party_id FND_USER.PERSON_PARTY_ID
NLS_LANGUAGE From V$NLS_PARAMETERS
NLS_NUMERIC_CHARACTERS From V$NLS_PARAMETERS
NLS_DATE_FORMAT From V$NLS_PARAMETERS
NLS_DATE_LANGUAGE From V$NLS_PARAMETERS
NLS_TERRITORY From V$NLS_PARAMETERS
NLS_SORT From V$NLS_PARAMETERS

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.

Organization Context

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

Security Context

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.

Product Initialization

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.

Custom Initialization

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

Leave a Reply

Your email address will not be published. Required fields are marked *