Wednesday, September 20, 2006

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

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

Voila!

6 comments:

Ambi said...

Well this is useful.. however, if you have APPS password - that you must have to use Diagnostics - you can attach any responsibility to any user id. Just use fnd_user_pkg package. Example:

---------------
BEGIN
fnd_user_pkg.addresp(USER_NAME, 'SYSADMIN', 'SYSTEM_ADMINISTRATOR',
'ALL', null, sysdate, null);
END;
---------------

This will assign the System Administrator responsibility.

Cheers!
@mbi

Gareth said...

Thanks Ambi,
Handy script, thanks for the input. My point here is that you can use apps_initialize through SQL*Plus, SQL Developer etc to mimic having logged on, without having the responsibility assigned. Good for development/debugging/troubleshooting purposes from the backend.

iamchandru said...

How the org_id will be set for discoverer reports, because when I use the org based view oe_order_headers, its not fetching me any data. any ideas?

Gareth said...

If you are on R11i make sure the responsibility you choose when you log into Discoverer has Operating Unit profile option set. Discoverer will initialize it.

Gareth

iamchandru said...

Thanks Gareth for your quick reply, but I am in R12. What should I do? and what exact profile option i should set?

Gareth said...

You may need to set value of profile option "Initialization SQL Statement - Custom" for the responsibility that you are logging into discoverer as with the following:
For MOAC:
"begin mo_global.init('SQLAP'); end;"
where SQLAP is the short name of the module, e.g.
OE = Order Management
SQLAP = Payables
For General Ledger Security:
"begin GL_SECURITY_PKG.init(); end;"
For both:
"begin mo_global.init('SQLAP'); GL_SECURITY_PKG.init(); end;
Regards,
Gareth