Oracle EBS: Initialising the APPS Environment in PL/SQL

How to set your user, responsibility, etc in PL/SQL to work with APPS functions

This blog post covers a fairly simple PL/SQL block that allows you to initialise your APPS environment from within PL/SQL allowing you to do things like run concurrent requests.

Language (NLS_LANGUAGE and NLS_TERRITORY)

The most important thing to start with is to make sure that your language and the current APPS configured languages and territory are the same in your session as they are on the server. You only need to do this step if you are looking onto a server configured with a different language. For example here in the UK our server is configured as AMERICAN.

To alter your session in PL/SQL you need to use EXECUTE IMMEDIATE;

execute immediate 'alter session set NLS_LANGUAGE = AMERICAN';
execute immediate 'alter session set NLS_TERRITORY = AMERICA';

If you want to see what your current settings are you can query FND_GLOBAL.NLS_LANGUAGE - in our case this turned out to either be GB or null.

Initialising Oracle EBS Environment

Clearly this issue has been around for a long time as Oracle provide a handy API in order to setup the environment. This API is part of the FND_GLOBAL package and is called APPS_INITIALIZE (note the US spelling). This API takes the following parameters;

USER_ID - The ID of the user (from FND_USER)
RESP_ID - The ID of the responsibility (from FND_RESPONSIBILITY)
RESP_APPL_ID - The ID of the application (also from FND_RESPONSIBILITY, APPLICATION_ID column)
SECURITY_GROUP_ID - This has a default value and in most cases you won't need to change it
SERVER_ID - Same with this

Once you have the correct values you can execute the call using the PL/SQL;

APPS.FND_GLOBAL.APPS_INITIALIZE(
  user_id      => v_UserId,
  resp_id      => v_RespId,
  resp_appl_id => v_RespAppId);

If successful you can then query values in FND_GLOBAL.

NOTE: It's worth probably saying that you just need to Initialize the environment - it's quite possible if you have an environment initialised as a lowly user you would still be able to pro grammatically run System Administrator Concurrent Requests with it.