Table and Synonym of the same table produce different results

In OracleEBS, there are modules like, AP, AR, XLA etc. Each module has its own schema named the same as the corresponding module name. There's also the APPS schema. You can access different tables from different schema via a synonym for that objects created in the APPS schema. For example, there's a table in xla schema named xla.xla_transaction_entities and the table has a synonym in apps schema. That's, the following two select queries should produce identical result set:

select distinct entity_code from xla.xla_transaction_entities 

and

select distinct entity_code from apps.xla_transaction_entities 

However, the second query produced less results than the first one. Then, I dropped and recreated the apps.xla_transaction_entities synonym. Only after recreating the synonym, the above two queries produced identical result sets. The question is, why would it happen? What caused the synonym to produce different result set? As far as I know, synonyms are just that - synonyms. As the name implies they should produce the same result set as their corresponding tables.

Edit: In another test instance, I reproduced the same problem:

select distinct entity_code from apps.xla_transaction_entities 

produces the following:

THIRD_PARTY_MERGE MANUAL INTER_ASSET_TRANSACTIONS TRANSACTIONS DEPRECIATION 

select distinct entity_code from xla.xla_transaction_entities 

produces the following:

AP_PAYMENTS RCV_ACCOUNTING_EVENTS THIRD_PARTY_MERGE MANUAL ADJUSTMENTS PURCHASE_ORDER MTL_ACCOUNTING_EVENTS RECEIPTS INTER_ASSET_TRANSACTIONS AP_INVOICES TRANSACTIONS DEPRECIATION 

 select * from dba_synonyms where synonym_name = 'XLA_TRANSACTION_ENTITIES' 

gives me the following:

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME, DB_LINK APPS XLA_TRANSACTION_ENTITIES XLA XLA_TRANSACTION_ENTITIES 

select * from dba_synonyms where TABLE_NAME = 'XLA_TRANSACTION_ENTITIES' 

outputs the following:

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK APPS XLA_TRANSACTION_ENTITIES XLA XLA_TRANSACTION_ENTITIES APPS XLA_TRANSACTION_ENTITIES_UPG XLA XLA_TRANSACTION_ENTITIES 

So, you can see that APPS.XLA_TRANSACTION_ENTITES points to XLA.XLA_TRANSACTION_ENTITIES. And again, once I recreate the synonym, the problem is gone. The reason why this bothers me so much is because most of the customized reports we have coded used synonyms instead of actual table names. So, I am wondering whether unless I recreate all synonyms in the APPS schema the problem will persist or not.

3 Answers

Some tables in Oracle applications have VPD enabled. In this link you can find additional information

Are you using MO view like ap_invoices? For all org_ids' data -> Use `ap_invoices_all` For specific org_id's data -> 11i -> `dbms_application_info.set_client_info(&org_id);` R12 -> `mo_global.set_policy_context('S',&org_id);` 

Are you using VPD enabled tables like xla_transaction_entities? For specific application's data -> xla_security_pkg.set_security_context(&appl_id); For all applications' data -> xla_security_pkg.set_security_context(602); (Or) Use xla.xla_transaction_entities (Or) Use xla_transaction_entities_upg 

The xla_transaction_entities table is VPD(Virtual Private Database) enable .

VPD is a security feature which allows user to restrict accessing data from database at database level. Suppose a user is restricted to fetch data from application but he can use tools like SQLPlus to fetch data from table. To restrict this, the security administrator adds a security level(One of 3 types) does not all the rows/column are fetched .

so instead of using apps.xla_transaction_entities use xla.xla_transaction_entities.

for more info on VPD

Another Solution for this issue is to use the synonym apps.xla_transaction_entities_upg

This doesn't require us to bypass the security settings set on the xla_transaction_entities.

Source - Metalink Note : 2110926.1