Search This Blog

Tuesday, September 25, 2012

How To Retrieve Rows From Table Or Synonym For An ORG_ID In E-Business Suite 12

In 11.5.10.2, Org Specific Views are used to filter rows with a specific ORG_ID. 

For example 

In SQL*Plus:

exec dbms_application_info.set_client_info('&ORG_ID');
In SQL Developer or Toad:

Begin 
  dbms_application_info.set_client_info('&ORG_ID'); 
End;

In R12, Views are no longer used. Instead, Synonyms together with Security Policy are used to return rows for a single operating unit. 

Solution

The following provide the steps to retrieve Org Specific rows from a Synonym. In R12, mo_global.set_policy_context is used instead of dbms_application_info.set_client_info which is used in 11.5.10.2

To filter rows from synonym using org_id:

SQL*Plus 
exec mo_global.set_policy_context('S', &org_id);

Toad and SQL Developer 
Begin 
  mo_global.set_policy_context('S', &org_id); 
End;

Example in SQL*Plus 

As an example, run the following first to see how many org_ids are being used in Incentive Compensation
select org_id, count(*)
from cn_commission_headers_all
group by org_id;

Results in:

ORG_ID      COUNT(*)
---------- ----------
1733        6
204         2340 

So we can see that two row are returned.  Let's take org_id 204 and run:

exec mo_global.set_policy_context('S',204);

This sets org_id as the organization we will be looking into.  Run the following:

select org_id, count(*)
from cn_commission_headers
group by org_id;

Results in:

ORG_ID       COUNT(*)
---------- ----------
204          2340 

1 comment:

Nandkishor Wagh said...

wonderful piece of information, I had come to know about your blog from my friend Nandu , Hyderabad, I have read at least 7 posts of yours by now, and let me tell you, your website 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 ton once again, Regards, Synonyms In Oracle