In Cmax v4 (Oracle 10), the Designations Report may return inconsistent results. This was first reported by Danville VA UW as follows:
1) Run the Designations Report for All Programs / No Agencies. Results appear.
2) Run again, but select a specific Program / No Agencies. Results would appear for some programs but not others.
3) Run again and select All Programs / All Agencies. Then choose Subtotal by Program/Agency. No data appears.
The problem was traced to a data error that is handled differently in Oracle 10 (Cmax 4) versus Oracle 9 (Cmax 3). The camOrgDivisionsV was failing. I fixed it with a cleaner query in DBUpdate, but this does not fix the data error.
To see if a customer has this data error, run this query:
select o.nameid, o.orgname1
from camorganizations o,
camrelationships r
where o.nameid = r.nameid
and exists (select * from camrelationships r2
where r2.nameid = r.relatednameid
and r2.relatednameid = r.nameid)
This returned two rows. It should always return no rows. If rows are returned the user has configured conflicting relationships like this:
Company A is the Parent Company of Company B
Company B is the Parent Company of Company A
The user must delete one of these conflicting relationships.
In Cmax v3 (Oracle 9), the camOrgDivisionsV view would not fail, so this problem was not apparent. The camOrgDivisionsV view is used in many other parts of the system, so we can expect to find other cases where this view is failing. Please document them here when they turn up.
Also, please be sure to fix both the data error and to run the updated version of DBUpdate. Thanks!