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.