0 Replies Latest reply: Jul 31, 2013 12:35 AM by AncientbearXu RSS

    Multi-OU selection in PL/SQL




      I get a requirement of providing View which contains records of Multi-OU transactions to other DB via DB-Link.


      All the processes are in backend, and invoking several seeded DB functions of EBS 12.1.3. The functions using synonym for Multi-Org Access but not the base table, thus have to initialize the DB session for Multi-OU.


      The steps I have made:

      1. Setup a responsibility for Multi-Org Access

      2. Initialize the DB session


          fnd_global.apps_initialize(user_id      => user_id1,

                                     resp_id      => responsibility_id1,

                                     resp_appl_id => application_id1



          MO_GLOBAL.SET_POLICY_CONTEXT(p_access_mode => 'M', p_org_id => null);


      3. Write a customized function xx_function to invoke seeded functions, and then using in the view as a column. Since mo_global.init including DML actions, also use AUTONOMOUS_TRANSACTION in xx_function.


      Here comes the problem:

      SQL Statement1, return 0 record:

      select xx_function(txn.id) from transaction_table txn;


      SQL Statement2, return normal value;

      select xx_function(1000) from dual; --1000 is a detail transaction ID.

      Is there any restriction to use mo initialize directly in select sql?


      Please help me.


      Best Rgds