Creating EBS Session form PLSQL

Basically, to create a EBS session you will need to use the fnd_global.apps_initialize API, you will need to pass 3 parameters to it, the user_id, the responsibility and the responsibility_application_id, all of which you can gather from the DB tables if you know the username of the user.

You get these parameters by using the query bellow, just change the user_name and the responsibility name.

select fu.user_id,
fr.responsibility_id ,
fr.application_id
from fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = 'BECERRAJ'
and fr.responsibility_name = 'System Administrator';

If you don't know the name of the responsibility, you can get it by looking at the fnd_responsibility_tl table

select responsibility_name, description from fnd_responsibility_tl;

Once you have the parameter you can run the API in your application or SQL session

begin fnd_global.apps_initialize(1233,2231,123); end;

or

exec fnd_global.apps_initialize(423,2231,123);

Sample PLSQL Application for Oracle Portal Users

This procedure reads the user from the SSO Cookie (portal.wwctx_api)
and creates a valid E-Business Suite session (assuming the username is the same
in both places).

Basically by querying the Apps owner schema, you can retrieve the user's responsibilities and the applications he uses, with this information we can call the fnd_global.apps_initialize function.

After the session is created, you will be able to use the fnd_global functions, and other EBS functionality, this is pretty handy when we need to integrate applications with the EBS, i.e. PLSQL portlets.

CREATE OR REPLACE PROCEDURE TESTJB IS

p_user_id number;
p_resp_id number;
p_resp_appl_id number;
p_request_group_id number;
lv_lang varchar2(100);

BEGIN

select fur.user_id
,fur.responsibility_id
,fur.responsibility_application_id
into p_user_id
,p_resp_id
,p_resp_appl_id
from fnd_user_resp_groups@db_link fur
,fnd_application_vl@db_link fa
,fnd_responsibility_vl@db_link frv
,fnd_user@db_link fu
where fur.responsibility_id = frv.responsibility_id
and fur.responsibility_application_id = frv.application_id
and frv.application_id = fa.application_id
and fa.application_name = 'Custom Application'
and fu.user_id = fur.user_id
and fu.user_name = UPPER(portal.wwctx_api.get_user)
and ( fur.end_date is null or trunc(fur.end_date) > trunc(sysdate) )
and ( fu.end_date is null or trunc(fu.end_date) > trunc(sysdate) )
and rownum < 2 ;


fnd_global.apps_initialize@db_link(p_user_id, p_resp_id, p_resp_appl_id);

SELECT request_group_id into p_request_group_id
FROM fnd_responsibility@db_link
WHERE responsibility_id = p_resp_id;

select userenv('LANG') into lv_lang from dual;

htp.p('<br>User_id='||p_user_id);
htp.p('<br>resp_id='||p_resp_id);
htp.p('<br>resp_appl_id='||p_resp_appl_id);
htp.p('<br>request_group_id='||p_request_group_id);
htp.p('<br>Lang='||lv_lang);

end;
/

**Some of the EBS views check for the userenv('LANG'); so make sure your DB
has the right NLS settings, also, Portal sessions use the browser's information
to define NLS settings so if your browser is not set to the same country as the
EBS DB you can have some problems, you can force the Language settings by using
Alter Session:

execute immediate 'ALTER SESSION SET NLS_TERRITORY=''AMERICA''';