Due to the corruption of data in ODF_VIEWS and ODF_VIEW_ATTRIBUTES tables, some of the filter portlets show duplicate fields. This is also making these filter portlets non functional and trying to modify the layout/displayed fields of such filters results in a system error. This seems to occur after performing an upgrade to the system.
Steps to Reproduce:
While this corruption may happen for any of the stock filter portlets, here is an example where the filter that is corrupted is "Project Team - Staff Filter"
- Open any project, Navigate to Team Tab
- Expand Project Team Staff Filter and observe columns
- Expand Project Team Details Filter and observe columns
- Expected Results: No duplicate attributes in the filter
- Actual Results: Duplicate attributes in the filter
- Go to Configure, List Filter section, Layout and make any change and Save
- Expected Results: Layout changes should save successfully
- Actual Results: System error, the following error shows up in the app log
ERROR 2015-07-13 17:23:03,154 [http-bio-80-exec-32] niku.xql2 (clarity:admin:14426665__6588ACDA-3798-4FC3-86E4-39C3D5205330:odf.saveObjectUserListFilterLayout) Internal Processing exception
SQL error code: 1
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (niku.ODF_VIEW_ATTRIBUTES_U3) violated
INSERT INTO ODF_VIEW_ATTRIBUTES
[... more ...]
Caused by: java.sql.SQLIntegrityConstraintViolationException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (niku.ODF_VIEW_ATTRIBUTES_U3) violated
[... more ...]
Applies to all supported PAS environments for specified releases.
The attached script provided in the Resolution applies to both Oracle or Microsoft SQL Server database environments.
Caused by CLRT-78771
The following query identifies the list of affected filter views in the system. Note that this query lists only the views corresponding to the filter configuration at the admin end (i.e. PRINCIPAL_TYPE=ADMINISTRATOR)
SELECT OVIEWS.ID, OVIEWS.CODE, OVIEWS.VIEW_TYPE, OVIEWS.PRINCIPAL_TYPE, OVIEWS.DATA_SERVICE, OVIEWS.OBJECT_CODE, OVIEWS.PARTITION_CODE
FROM ODF_VIEWS OVIEWS
WHERE VIEW_TYPE= 'filter'
AND UPPER(PRINCIPAL_TYPE) ='ADMINISTRATOR'
AND OVIEWS.OBJECT_CODE NOT IN (SELECT CODE FROM ODF_OBJECTS WHERE
SELECT A.ID, A.CODE, A.VIEW_TYPE, A.PRINCIPAL_TYPE, A.DATA_SERVICE, A.OBJECT_CODE,
FROM ODF_VIEWS A
WHERE EXISTS (SELECT B.ID
FROM ODF_VIEWS B
WHERE B.VIEW_TYPE = A.VIEW_TYPE
AND B.PRINCIPAL_TYPE = A.PRINCIPAL_TYPE
AND B.CODE = A.CODE
AND B.OBJECT_CODE = A.OBJECT_CODE
AND B.PARTITION_CODE = A.PARTITION_CODE
AND UPPER(B.PRINCIPAL_TYPE) ='ADMINISTRATOR'
AND B.ID < A.ID)
Resolved in CA PPM 14.3.
The Check Install Tool (clarity-tools) will check for this issue.
If the above query results in any corrupted filter views, execute the attached correction script to fix the issues with those filter views.
- Extract and copy the DELETE_DUPLICATE_TEAM_FILTER_VIEWS.xml file to a directory on the CA PPM application server
- Open a command prompt window
- Execute the $CLARITY_HOME\bin\dbpatch command to run the correction script (with qualified path to command and to the file)
- dbpatch -install -file DELETE_DUPLICATE_TEAM_FILTER_VIEWS.xml -apply
- Restart application services or flush all caches to reflect the changes
Reference CA PPM Resolved Defects Index for CA PPM 14.3
Reference the Release Notes and Installation documentation for more information regarding the Check Install Tool.