Setting your Oracle Applications session: fnd_global.apps_initialize (org_id)

From Garith Roberts post

If you are working with Oracle Applications, here's how you can initialize your session in whatever tool you are using to mimic the login process and pick up profile option values. The key profile option here is usually org_id so you can select from organization aware views, but it applied equally to other profile options, i.e. you can then use

fnd_profile.value('PROFILE_OPTION_NAME');

to get values from profile options. You need to be logged into the database as the APPS user. The examples set up the session for SYSADMIN user, System Administrator responsibility. e.g.

SQL*Plus

exec fnd_global.apps_initialize(0,20420,1);

e.g. for PL/SQL, TOAD, SQLDeveloper, SQL Navigator etc.:

begin fnd_global.apps_initialize(0,20420,1); end;

The parameters used here are:

1. User_ID
2. Responsibility_ID
3. Responsibility_Application_ID

To get these you have a couple of choices a) SQL - Replace SYSADMIN and System Administrator with your user and responsibility:

select 'begin fnd_global.apps_initialize(' ||
       fu.user_id || ',' ||
       fr.responsibility_id || ',' ||
       fr.application_id || '); end;' || chr(10) || '/'
from   fnd_user fu
,      fnd_responsibility_tl fr
where  fu.user_name = 'SYSADMIN'
and    fr.responsibility_name = 'System Administrator';

b) In your Oracle Applications forms session. Login as your user and navigate to the required responsibility. Open a function that uses Oracle forms Go to Help > Diagnostics > Examine In the Block enter $PROFILES$ In the field enter the appropriate field name for the parameter:

1. User_ID = USER_ID
2. Responsibility_ID = RESP_ID
3. Responsibility_Application_ID = RESP_APPL_ID

R12 Considerations
With the introduction of R12 Oracle introduced the concept of “Multi Organization Access Control” which allowed a user to operate across different organizations using a single responsibility. The introduction of this feature meant that the fnd_global.apps_initialize function was no longer sufficient to establish the context session and an additional API was introduced called MO_GLOBAL.
Within R12 after calling the apps_intialize API you must then call MO_GLOBAL.INIT(‘<PRODUCT_SHORT_NAME>’) where PRODUCT_SHORT_NAME is the product you are coding for i.e. ONT, PER, AMS etc…