Thursday, January 9, 2014

Initializing Session in TOAD,SQL Developer,SQL*Plus using sql/plsql

Apps Initialization in TOAD , SQL Developer , SQL*Plus



FND_GLOBAL.APPS_INITIALIZE

It is used for initializing the session before calling any public or private API's in Oracle Ebusiness suite. It’s not required for all the API's but its recommended that you set this profile before making any calls to either private or public API.
Listed below is a sample call to FND_GLOBAL.APPS_INITIALIZE function

fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
                           resp_id=>l_resp_id,
                           resp_appl_id=>l_resp_appl_id );

1. l_user_id is the fnd user ID which will be utilized during the call.
2. l_resp_id is the responsibility ID
3. l_resp_appl_id is the responsibility application ID.
4. For SYSADMIN, utilize the following query to get the respective values

            select fnd.user_id ,
                       fresp.responsibility_id,
                       fresp.application_id
           from    fnd_user fnd
               ,       fnd_responsibility_tl fresp
           where  fnd.user_name = 'SYSADMIN'
             and    fresp.responsibility_name = 'Order Management Super User';


For example- Launching concurrent request from toad or sql developer-

Declare
request_id number;

Begin
     fnd_global.APPS_INITIALIZE (user_id        =>1141 ,
                                  resp_id        => 50678,
                                  resp_appl_id   => 724);
    dbms_output.put_line('APPS INTIALIZE') ;    
    COMMIT;

request_id:=fnd_request.submit_request (
                                  'XBOL' – This is concurrent programs application name
                                 ,'CMCSDPVW'
                                 , NULL
                                 , NULL
                                 , FALSE);

                                 commit;

dbms_output.put_line('request id-'||request_id) ;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('eRROR-'||SQLERRM) ;
End;

For SQL*Plus:
You can apps initialize by running below query-

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

Other ways of intialization session for specific organization in toad or sql developer-

Begin
fnd_global.apps_initialize(0,52091,1);
end;

begin
fnd_client_info.set_org_context(52091);
end;

begin
dbms_application_info.set_client_info(52091);           
end;

Here 52901 is org_id .

Set language/terriotary from Toad

select * from V$NLS_PARAMETERS;

alter session set NLS_LANGUAGE ='SPANISH';


NLS_TERRITORY
NLS_CURRENCY
NLS_CALENDAR
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT

1 comment: