500 Internal Server error when saving a company address in CA PPM

Document ID : KB000110884
Last Modified Date : 04/09/2018
Show Technical Document Details
Issue:
When attempting to save a postal address for a company record within CA PPM, a "500 Internal Server Error" message is returned. 
Environment:
CA PPM - All Versions
Cause:
The issue is that there are a number of records in a table called SRM_CONTACTS (which stores the Address and Postal Address data when saved to a company record), for which there should be two entries in that table for each of those company records - one entry has a PRINCIPAL_TYPE of "COMPANY_POSTAL" and the other has a PRINCIPAL_TYPE of "COMPANY_MAIN", however, a number of these records only have one entry, and are missing the "COMPANY_POSTAL" entry. 
Resolution:
Run XOG with the following queries to XOG out the records that have the problem, and then XOG them back in - this will correct the issue.

Query Used To Pull a List of Records in SRM_CONTACTS where there is a "COMPANY_POSTAL" entry, but no corresponding "COMPANY_MAIN" entry: 
select * from srm_contacts where principal_id in(SELECT PRINCIPAL_ID 
FROM SRM_CONTACTS 
where principal_type like 'COMPANY%' 
GROUP BY PRINCIPAL_ID 
having count(*)=1) 
and principal_type like 'COMPANY_POSTAL' 


ADDITIONAL FINDINGS: 
- we also found that by running a query to see which records have a "COMPANY_MAIN" entry, but NO "COMPANY_POSTAL" entry, and attempting to update one of those records, it has different but still problematic behavior - in that, it will blank out any info you put in the Postal Address section when you save it. It does not throw an error.  
- To fix these, XOG them out using the following query, and then XOG them back in:

select * from srm_contacts where principal_id in(SELECT PRINCIPAL_ID 
FROM SRM_CONTACTS 
where principal_type like 'COMPANY%' 
GROUP BY PRINCIPAL_ID 
having count(*)=1) 
and principal_type like 'COMPANY_MAIN'