Apps Initialize in R12

In R12, the old fnd_client_info.set_org_context  no longer seems sufficient to query data from apps views:

SQL> show user
USER is "APPS"




SQL> select fnd_profile.value('ORG_ID') from dual;

FND_PROFILE.VALUE('ORG_ID')
--------------------------------------------------------------------------------
85

SQL> select USERENV ('CLIENT_INFO') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------



SQL> exec fnd_client_info.set_org_context(85);

PL/SQL procedure successfully completed.

SQL> select USERENV ('CLIENT_INFO') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
85

SQL>  select count(*) from ra_customer_trx_partial_v;

  COUNT(*)
----------
         0  <-- We know there are plenty of rows!


SQL> exit


One needs to run  fnd_global.apps_initialize and   mo_global.init  as follows ...

sqlplus as apps again and ...

Get parameters for apps security ...

SQL> select user_id,responsibility_id,responsibility_application_id, security_group_id
from fnd_user_resp_groups
where user_id = (

    select user_id from fnd_user where user_name = 'ROBERT')
    and responsibility_id = (select responsibility_id from         fnd_responsibility_vl where responsibility_name =
    'Purchasing Super User')

;


USER_ID RESPONSIBILITY_ID RESPONSIBILITY_APPLICATION_ID SECURITY_GROUP_ID
---------- ----------------- ----------------------------- -----------------
     11787             20707                           201                 0

SQL> exec fnd_global.apps_initialize(11787,20707,201);

PL/SQL procedure successfully completed.

SQL> exec mo_global.init ('PO');

PL/SQL procedure successfully completed.

SQL> select USERENV ('CLIENT_INFO') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
85                                                    0

 
And test again ...


SQL> select count(*) from ra_customer_trx_partial_v;

  COUNT(*)
----------
     14857

Logging in as someone else with proxy users

Example of proxy users in Oracle 10g (and probably 9i), where one can login to a different database account without knowing the password to the other account:

SQL> show user
USER is "SYSTEM"
SQL> create user lm1 identified by pass1;

User created.

SQL> create user lm2 identified by pass2;

User created.

SQL> grant connect to lm1, lm2;

Grant succeeded.

SQL> alter user lm2 grant connect through lm1;

User altered.

SQL> connect lm1/pass1
Connected.
SQL> show user
USER is "LM1"
SQL> connect lm1[lm2]/pass1
Connected.
SQL> show user
USER is "LM2"  

... so lm1 was able to connect to lm2 without using the password pass2 for lm2.

This superceeds the "alter user identified by values" method in older releases (which is still valid).

To reverse,

SQL> alter user lm2 revoke connect through lm1;

User altered.

SQL> conn lm1/pass1
Connected.
SQL> show user
USER is "LM1"
SQL> connect lm1[lm2]/pass1
ERROR:
ORA-28150: proxy not authorized to connect as client
Warning: You are no longer connected to ORACLE.