Filter portlets non-functioning due to duplicate fields in filter portlets

Document ID : KB000031365
Last Modified Date : 14/02/2018
Show Technical Document Details

 

Problem:

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" 

  1. Open any project, Navigate to Team Tab 
  2. Expand Project Team Staff Filter and observe columns 
  3. Expand Project Team Details Filter and observe columns 
    • Expected Results: No duplicate attributes in the filter 
    • Actual Results: Duplicate attributes in the filter 
  4. 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 
com.niku.union.persistence.PersistenceException: 
SQL error code: 1 
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (niku.ODF_VIEW_ATTRIBUTES_U3) violated 
Executed: 
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 ...]
 
 

Environment:

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. 

Cause:

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 
CODE=OVIEWS.OBJECT_CODE)
 AND DATA_SERVICE=OBJECT_CODE
 
  UNION ALL
 
  SELECT A.ID, A.CODE, A.VIEW_TYPE, A.PRINCIPAL_TYPE, A.DATA_SERVICE, A.OBJECT_CODE, 
  A.PARTITION_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)

Resolution:

Resolved in CA PPM 14.3.  

The Check Install Tool (clarity-tools) will check for this issue.

Workaround:

If the above query results in any corrupted filter views, execute the attached correction script to fix the issues with those filter views. 

  1. Extract and copy the DELETE_DUPLICATE_TEAM_FILTER_VIEWS.xml file to a directory on the CA PPM application server
  2. Open a command prompt window 
  3. 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  
  4. Restart application services or flush all caches to reflect the changes

Additional Information:

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.

 

File Attachments:
TEC1448231.zip