I am facing error on saving custom created lookup on team tab. When i select value in lookup in team tab(Staff Member Properties) for a resource and hit save. i am getting "ERROR System error. Contact system administrator."
The error is thrown when i enable audit trail for attribute created.
So i checked in logs i found following error logs.
Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01422: exact fetch returns more than requested number of rowsORA-06512: at "CLARITY.ODF_AUD_5001357_FCT", line 5ORA-06512: at "CLARITY.T_TEAM_OODF_CA_TEAM", line 12ORA-04088: error during execution of trigger 'CLARITY.T_TEAM_OODF_CA_TEAM'
at com.ca.clarity.jdbc.oraclebase.ddcr.b(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcr.a(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcq.b(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcq.a(Unknown Source) at com.ca.clarity.jdbc.oracle.ddam.t(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde3.y(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde3.u(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dddz.execute(Unknown Source) at sun.reflect.GeneratedMethodAccessor76.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:68) at org.logicalcobwebs.cglib.proxy.Proxy$ProxyImpl$$EnhancerByCGLIB$$7bec191a.execute(<generated>) at com.niku.union.persistence.jdbc.SQLTracePreparedStatement.execute(SQLTracePreparedStatement.java:112) at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2848) ... 96 more
The "CLARITY.T_TEAM_OODF_CA_TEAM" is a OOTB trigger which gets created when we enable audit trail for attributes in team tab. So above trigger is using an OOTB function "CLARITY.ODF_AUD_5001357_FCT" which is created based on custom lookup attribute which i have created in team tab. The function is returning more then 1 records which is causing error. If i remove audit trail for attribute which i have created and then try to save team page by selecting value in the lookup is working fine.
Please suggest on me on this issue.
The version of CA PPM is 184.108.40.206
Moving to the PPM group. Please post your queries in the correct group to esure a prompt response
Thank you Hallett German. I am new to community so was not able to know proper group.
If you look at the source code for the T_TEAM_OODF_CA_TEAM trigger you should be able to see what 'select' it is doing when its trying to create the audit entry. I would guess that you have some duplicated lookup names or something like that which is causing the issue.
I think that the trigger calls a function to 'decode' any lookup values - I would guess its in calling that function that the duplicate row is encountered
(the trigger T_TEAM_OODF_CA_TEAM is created dynamically based upon what you have selected to audit, so it will be down to something specific in your system)
Thank you David for reply.
Yes T_TEAM_OODF_CA_TEAM is calling CLARITY.ODF_AUD_5001357_FCT internally. We have requirement such as the function returns multiple records.
To elaborate more on this ,let say we have object as "X" which is sub object of project. The relation ship between object "X" and project is M:1. Means 2 different projects can have same object "X" data.
So we created new lookup attribute in team tab to show all the object "X" data for particular project team. The lookup attribute which i have created is an parameterized lookup. I am passing project id as parameter. So when i enable audit trail for attribute i am getting error. when i change record in application the trigger "T_TEAM_OODF_CA_TEAM" gets executed which calls function CLARITY.ODF_AUD_5001357_FCT internally. So function is getting multiple records(as we have requirement) and its failing. I tried to change the OOTB function and trigger to fetch only single record but it's giving error in resource finder as i am trying to change OOTB function in DB.
"say we have object as "X" which is sub object of project. The relation ship between object "X" and project is M:1. Means 2 different projects can have same object "X" data."
If X is a sub-object of a project, then (by definition) any single instance of X may only belong to one project ; of course different projects may have (different) instances of X that appear to be the same (but are in fact different records). I would GUESS that your lookups are not set up right - perhaps they are returning a "code" rather than a unique id (as the instance of X) and the "code" is not unique (across all instances of X across multiple projects) and hence the error in the system-generated function.
I think you need to look at whether your lookups used against your attributes are correct ; I think that that is where the problem is.
(lot of GUESS work here though)
Thank you David. I will check on the lookup query.