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

    Multi-OU selection in PL/SQL

    AncientbearXu

      Hi

       

      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.init('PO');

          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

      James