HZ_CUST_SITE_USES no rows returned

When query the following table it returns zero records.

SQL> select count(*) from HZ_CUST_SITE_USES;
COUNT(*)
---------- 0
SQL>

can you please help me some one

1 Answer

Per My Oracle Support (Doc ID 787677.1), You Have a Security Policy Enabled on the APPS Owned Synonym

This is a standard set-up associated with APPS@ERP owned synonyms in an Oracle R12 environment.

First, confirm that the APPS@ERP owned synonym when queried without setting an org specific security policy context will result in an empty set:

APPS@ERP>SELECT COUNT(1) FROM HZ_CUST_SITE_USES;
COUNT(1)
0 

Next confirm that a security policy has been applied to this APPS@ERP owned synonym:

APPS@erp>SELECT object_name, 2 policy_group, 3 policy_name, 4 PACKAGE, 5 FUNCTION, 6 sel, 7 enable 8 FROM dba_policies 9 WHERE object_name = 'HZ_CUST_SITE_USES';
OBJECT_NAME POLICY_GROUP POLICY_NAME PACKAGE FUNCTION SEL ENABLE
HZ_CUST_SITE_USES SYS_DEFAULT ORG_SEC MO_GLOBAL ORG_SECURITY YES YES 

Just confirm that the object type is a synonym:

APPS@erp>SELECT owner, 2 object_name, 3 object_type 4 FROM DBA_OBJECTS 5 WHERE 1 =1 6 AND OBJECT_NAME = 'HZ_CUST_SITE_USES';
OWNER OBJECT_NAME OBJECT_TYPE
APPS HZ_CUST_SITE_USES SYNONYM 

Query the APPS@ERP view associated with the AR owned table, HZ_CUST_SITE_USES_ALL:

APPS@erp>--APPS owned view r12.2
APPS@erp>SELECT ORG_ID, COUNT(1) FROM HZ_CUST_SITE_USES_ALL GROUP BY ORG_ID;
ORG_ID COUNT(1)
123 458
456 2658
789 1210 

Once the context is set for a session (org_id is 456), one can query results for that org_id:

APPS@erp>--set policy
APPS@erp>EXEC mo_global.set_policy_context('S', 456);
PL/SQL procedure successfully completed.
APPS@erp>SELECT COUNT(1) FROM HZ_CUST_SITE_USES;
COUNT(1)
2658