Tables and synonyms in EBS R12

I'm busy with interfacing on E-Business Suite R12, Accounts Receivables and Account Payables, using the api's. I found that there are also neat get-api's to fetch the created customer-accounts and customer-account-sites.

I created an customer-account-site, using the api and all went well in creating the underlying objects. But getting the site using the hz_cust_acct_site_bo_pub.get_cust_acct_site_bo api gave me an error to give the proper identifiers. Strangely enough you have not only to pass the p_cust_acct_site_id but also a p_cust_acct_site_os and p_cust_acct_site_osr. You have to now that the os and osr suffixes stand for "origininal system" and "original system reference", that relate to the entry in hz_orig_sys_references table for the site (owner_table_name='HZ_CUST_ACCT_SITES_ALL'). I expected that passing only the account-site-id would be enough, but the api checks it against the original system reference.

I found that there is also a validation-query done on the hz_cust_acct_sites table, with a cursor where originally the org_id was a parameter, but it is not used in the where clause anymore. The object hz_cust_acct_sites turns out to be a synonym on hz_cust_acct_sites_all, but allthough the latter gives me rows, the first one doesn't. Strange, since hz_cust_acct_sites is certainly not a view.

It turns out that IN EBS R12 a row level security policy is applied on the table, leveraging the VPD possibilities of Oracle DB 10g. And under water for the synonyms on the '%_all' tables an extra where clause condtion is applied using the org-id. This can be seen for example with:

select * from all_policies
where object_name = 'HZ_CUST_ACCT_SITES'

This shows that the mo_global.org_security function is used to return an extra where-clause-condition on org-id for the table.

But how to set the orig-id?
There are several options to do that. Earlier with 11.5.9 I created a package that set the responsibility for me from the initialisation block. Just using a function in that package (eg. get_responsibility) causes to execute the initialisation block (the last block in a package body) only once. When I call the get_responsibility function from the initialisation block of every package, I'm asured that the responsibility is set only once. The responsiblity can be set using the fnd_global.apps_initialize api. This needs some setup info ( user id , responsibility id, responsibility application id) and then you need to be sure that you have the appropriate organisation unit and thus org-id set.

For now I only have to have the orig-id set. I found with some tips that it is enough to do:

begin
mo_global.set_policy_context_server(p_access_mode => 'S'
                                  ,p_org_id      => 204);
end;

As access-mode you can give up 'S' for single, 'M' for multiple and 'A' for all. So you can set multiple org-id's. Well and the org-id of course applies to the value in hz_cust_acct_sites_all.org_id.

Executing the block above before doing your query would give results from hz_cust_acct_sites. And thus the get-api would have to give the appropriate result.

There are many other '%_ALL' tables with corresponding synonyms that work the same way.