System Error on Object Update with attribute audit - Troubleshooting

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

When updating any object instance in Clarity, i.e, Project, an error is thrown: 

Error in app-ca.logs:
ERROR 2018-11-16 20:15:56,513 [http-nio-80-exec-75] niku.xql2 (clarity:admin:session_id:projmgr.projectPropertiesPost) Internal Processing exception 
com.niku.union.persistence.PersistenceException: 
SQL error code: 1422 

Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01422: exact fetch returns more than requested number of rows 
ORA-06512: at "NIKU.ODF_AUD_5001168_FCT", line 5 
ORA-06512: at "NIKU.T_PROJECT_OODF_CA_PROJECT", line 291 
ORA-04088: error during execution of trigger 'NIKU.T_PROJECT_OODF_CA_PROJECT' 
 

How to proceed with troubleshooting this. If we disable the attribute it works however we need it enabled

Environment

Release: Any Supported Releases

Cause

Clarity is not accepting the values that are being passed to the audit trigger by the function as there are duplicates in the dynamic lookup query

Resolution

1. Look at the ODF_AUD_5001168_FCT which is a function that is getting created by the system any time you add an attribute to be audited.
2. Compare the SQL that is in the function versus the NSQL behind the lookup that is tied to the attribute (i.e. in this case, as example we will call the "Solution Owner" attribute), and the associated lookup ("Solution Owner".
3. If the NSQL in there is different (even just slightly differently) then what it is in the function  (which is when the attribute was originally audited for the first time), that indicates that someone modified the lookup after it was already tied to the attribute, but when you do that the function that is responsible for audit does not get updated as we do not allow for modification of the lookup once it is tied to an attribute (possibly done by XOG).
4. If the above is the case, create a new lookup using the exact same NSQL which is behind the Lookup (Solution Owner in this example) and tie that to the new attribute.
5. Since we can't update functions the one option to resolve the issue would be the above, to create another attribute and then you could do a gel script to copy data over from an old one to new one and continue with one attribute going forward.