Error 500 error because of duplicate OBS prj_obs_associations

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

While trying to open project details finding "Error 500 - Internal Server Error. The server could not retrieve the document due to server-configuration or other technical problems"

App-ca.logs

ERROR 2018-12-06 21:53:41,612 [http-nio-128.87.241.32-80-exec-4134] niku.xql2 (clarity:admin:%sessionid%:projmgr.projectProperties) Internal Processing exception 
java.lang.Exception: Invalid expression qobj: com.niku.union.persistence.PersistenceException: 
SQL error code: 1427 
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01427: single-row subquery returns more than one row 

Environment

Any Clarity release

Cause

• Customer managed to create duplicates in the prj_obs_associations-Table regarding the OBS department. The lookup 'odf_obs_fin_dept' shows duplicates.
• The subprojects OBS association have multiple OBS units linked. 

The following query will allow you to identify the project OBS associations:

select * from PRJ_OBS_ASSOCIATIONS where table_name = 'SRM_PROJECTS' and record_id = 'internal project ID' 

It should return one value per OBS unit.

The error is caused because it finds multiple OBS associations where it only expects one.

Resolution

1. XOG out the project and check the OBS associations.
2. Delete the undesired/wrong OBS unit value form the XOG file
3. XOG the project in.

OR

1. Delete from prj_obs_associations where table_name = 'SRM_PROJECTS' and record_id = 'internal project ID' 
2. Now the project should open fine in the UI
3. The OBS association is now blank. Re-associate the OBS unit through the UI