Setting Org Context, Language Context for backend access to Oracle Apps

Usually to view data from most of the views through backend, we need to set the org context before we can query.

 

For example, in a fresh database session, if we try to query from ap_invoices:

 

select * from ap_invoices;

 

Result: 0 Rows.

 

This is because the client_info has not been set for the database session:

 

select USERENV ('CLIENT_INFO') from dual;

 

Result: null

 

Use the following command to set the org context:

 

begin

fnd_client_info.set_org_context(<ORG_ID>);

end;

 

Now, views like ap_invoices would fetch records.

 

Similarly, we can use apps_initialize call in fnd_global to perform the same task:

 

begin

  fnd_global.apps_initialize

    (  user_id => 309117

    ,  resp_id => 21539

    ,  resp_appl_id => 101

    ,  security_group_id => 0

    );

end;

 

Use the following query to easily fetch your user_id, resp_id, resp_appl_id and security_group_id

 

SELECT u.user_id user_id

,      r.responsibility_id resp_id

,      r.application_id resp_appl_id

,      r.responsibility_key

,      sg.security_group_id security_group_id

FROM   fnd_user u

,      fnd_user_resp_groups ur

,      fnd_responsibility r

,      fnd_security_groups sg

WHERE  u.user_name = 'RAVI'

AND    ur.responsibility_id = r.responsibility_id

AND    ur.user_id = u.user_id

AND    ur.security_group_id = sg.security_group_id;

 

 

But there are views which are language specific (those with a clause like "and FLV.LANGUAGE = userenv('LANG')") which would not respond even if we set the org_context

 

For example:

 

select * from fnd_common_lookups;

 

This would not fetch any rows from a backend session until we set the language context:

 

ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';

 

INSERT INTO FND_SESSIONS

VALUES (userenv('SESSIONID'), TRUNC(SYSDATE));

 

 

 

Ravi Parupudi Senior Applications Developer eBusiness Soulution Center
Network Rail Desk 2/251, 4 Travis Street Manchester Sqare One Manchester M12NY, UK	Extn: 085 50379 Work: +44 161 880 3379 Mobile: +44 759 075 0143 Email: ravi.parupudi@networkrail.co.uk