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

Document ID : KB000032749
Last Modified Date : 27/08/2018
Show Technical Document Details
Issue:

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

 

 

Environment:
Applies to all supported PAS environments for specified releases.
Cause:
Caused by CLRT-79156
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')