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