CA PPM: Impacts of deleting and recreating an attribute with the same ID to the Audit Trail

Document ID : KB000007228
Last Modified Date : 10/08/2018
Show Technical Document Details
Issue:

 

Strictly speaking, Clarity doesn't let you delete an attribute that is being used. If you try to delete the attribute that is in use you get Error 'The selected attributes can't be deleted as they are being used' and the list of dependent items is displayed.

If you have an attribute that is being audited and there are records in the CMN_AUDITS table, and later you decide to remove the attribute from being audited, Clarity let you delete the attribute even though there are records in the CMN_AUDITS table. This is not a defect.

However, if you recreate the attribute with the same ID but different data type it will impacts the Audit Trail.  The user won't be able to check the Audit Trail and will receive an Error:

Error 'System error. Contact system administrator.

Alert Alert:ODF-0800: No attributes were selected for auditing. Contact your administrator.

 

 

 

Steps to Reproduce:

1. In Task Object (or any object) create an attribute type String. ID=myattribute.

2. Add the attribute to Audit Trail (for insert/delete, update operation).

3. Open a Project and open a Task.

Enter a value for myattribute. Saved it.

Enter another value for myattribute. Saved it.

4. Check the Audit Trail Tab in the Task and see the rows for operation insert, update, update.

5. Go again to Task Object and try to delete attribute 'myattribute'.

You can not delete it. You get:

"Error: The selected attributes can't be deleted as they are being used. The error conditions are marked below.

Associated Items.

The following dependent items will also be processed, there may also be reports or database views/stored procedures/functions using these attribute(s) which will not be removed. This list should be very short, removing attributes that are used will affect the user experience.

Name Type Action

Property Update View - test (myattribute) View Attribute Delete

Attribute auditing must be removed myattribute Audited Attribute Auditing Invalid"

 

6. Go again to Task Object and remove the attribute myattribute from being audited

7. Try to delete the attribute again. Now you can delete it. You will receive:

"Attribute Description

myattribute

Associated Items

The following dependent items will also be processed, there may also be reports or database views/stored procedures/functions using these attribute(s) which will not be removed. This list should be very short, removing attributes that are used will affect the user experience.

Name.................................................... Type ......................Action

Property Update View - test (myattribute)...... View Attribute......Delete

<YES> <NO>"

 

Click on YES. The attribute myattribute gets deleted.

8. Query the CMN_AUDITS table. Run query: select * from CMN_AUDITS where attribute_code='myattribute'

The 3 rows still exist in the CMN_AUDITS  table for the insert and update operations.

9. In Task Object recreate the attribute with the same ID, but different data type. Data type=Lookup. ID=myattribute.

10. Add the attribute to Audit Trail (insert/delete, update operation).

11. Open a Project and open a Task.

Enter a value for myattribute. Saved it.

Enter another value for myattribute. Saved it.

12. Check the Audit Trail Tab in the Task

13. Check the Audit Trail Tab in the Project and select subobject Task

 

Actual Result: 

Receive Error:

Error 'System error. Contact system administrator.

Alert Alert:ODF-0800: No attributes were selected for auditing. Contact your administrator.

Cause:

The root cause is the same attribute ID is in the CMN_AUDITS table with two different data types.

 

 

 

 

Resolution:

 

 

Resolution:

The current fix is to purge the audit trail from the before the time the new ID was created & audited. But this can result in months of data loss, as you will lose all the audit records of the other audited attributes on the TASK object when the purge audit job is run.

 

Workaround:

Delete the second attribute (with data type Lookup in this example). To delete the attribute, you will need to remove it from being audited first.  After being delete it when you access the Audit Trail Tab in the Task or the Project you won’t get any error.
The records for the attributes with the same ID with different type (string and lookup in this example) will remain in the CMN_AUDITS table until the Purge Audit Trail job is run and delete them based on the “Days after which audit records will be purged” value that is set.