Receive ‘System Error’ when an attribute is audited for Changes

Document ID : KB000122150
Last Modified Date : 25/03/2019
Show Technical Document Details
Issue:
Receive ‘System Error’ when an attribute such as a custom attribute (Solution Owner) attribute is audited for Changes in Clarity PPM.

Excerpt of 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 "YOUR_SCHEMA.ODF_AUD_5001168_FCT", line 5 
ORA-06512: at "YOUR_SCHEMA.T_PROJECT_OODF_CA_PROJECT", line 291 
ORA-04088: error during execution of trigger 'YOUR_SCHEMA.T_PROJECT_OODF_CA_PROJECT' 
 
Cause:
The error in the logs above indicates that it is not accepting the values that are being passed to the audit trigger. 
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 (IE in this case, the "Solution Owner" attribute), and the associated lookup ("Solution Owner" in this example.
  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.