Deleting a timesheet and timeentry note leaves orphan and executes a invalid query which caused performance issues on the database

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Deleting a timesheet notes or timeentry notes leaves an orphan record on table ODF_CA_TIMESHEETNOTE & ODF_CA_TIMEENTRYNOTE and can cause performance issues are database level.

Steps to Reproduce:

1. Login to CA PPM 
2. Navigate to Timesheet Page 
3. Create a timesheet and timeentry note 
4. Check the PRNOTE table. The master data will be there and reference on ODF_CA_timeentrynote & odf_ca_timesheetnote 
5. Delete the timesheet and timeentry note 
6. Data gets deleted on PRNOTE and leaves orphan on ODF_CA_TIMESHEETNOTE & ODF_CA_TIMEENTRYNOTE

Expected Result: The data on ODF_CA_timeentrynote & odf_ca_timesheetnote should also be deleted

Actual Result: The data is orphaned and the delete statement which executes is slow as there is no id column on the PRNOTE table

 

 

Cause

Caused by CLRT-79156

Environment

Applies to all supported PAS environments for specified releases.

Resolution

This is fixed in CA PPM 14.4

Workaround: 
Note: The following workaround is provided by our Sustaining Engineering team for On Premise Customers.  If you implement Part I, the changes to the code file may be overwritten with subsequent patches or upgrades.  Be sure to review this developer, pre-approved customization to the code.  Once the issue is resolved in new version, be sure to check and verify the new files are installed and overwrites the existing code files.
If you are a Saas Customer, please contact CA Technical Support and reference this issue.
  
Part 1: Interim code modification to improve performance and avoid orphan records

For On Premise implementations, you can follow the steps to update the application code file and restart services

 

1. Open the file $clarity\META-INF\timeadmin\pmd\timeadminPMD.xml 

2. On Line 1307 correct the statement from : 

DELETE odf_ca_timesheetnote 

WHERE id in (select id 

from prnote 

where PRTABLENAME = 'PRTimeSheet' 

AND PRRECORDID = ?) 

 to 

          DELETE from odf_ca_timesheetnote 

WHERE id in (select prid 

from prnote 

where PRTABLENAME = 'PRTimeSheet' 

AND PRRECORDID = ?) 

3. Repeat the same for odf_ca_timeentrynote on line 1319: 

          DELETE odf_ca_timeentrynote 

WHERE id in (select id 

from prnote 

where PRTABLENAME = 'PRTimeEntry' 

AND PRRECORDID = ?) 

 to 

DELETE from odf_ca_timeentrynote 

WHERE id in (select prid 

from prnote 

where PRTABLENAME = 'PRTimeEntry' 

AND PRRECORDID = ?) 

4. Save and restart all CA PPM services. 

 

Part II: Clean-up script to remove orphaned records

 

To check for and remove the orphaned records due to this issue, please run the two queries periodically until this issue is fixed: 

 

1. Remove the Orphan Timessheet Note

DELETE from odf_ca_timesheetnote 

WHERE id not in (select prid 

 from prnote 

where PRTABLENAME = 'PRTimeSheet') 

2.   2. Remove the Orphan Timeentry Note

                   DELETE from odf_ca_timeentrynote 

WHERE id not in (select prid 

from prnote 

where PRTABLENAME = 'PRTimeEntry')