Wednesday 4 March 2009

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:

mo_global.set_policy_context_server(p_access_mode => 'S'
,p_org_id => 204);
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.


Unknown said...

interesting piece of information, I had come to know about your web-page from my friend pramod, jaipur,i have read atleast eight posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a million once again, Regards, Synonyms In Oracle

Chinnappa said...

Brilliant Post. It was very helpful.