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