PPM Support Tip: Pre-Validation CleanInvalidWorkItemsTMWorkUnits.sql should sometimes remove a row
When running CleanInvalidWorkItemsTMWorkUnits.sql, error ORA-00001: unique constraint (PPM.TM_WORK_UNITS_U1) violated
When updating 9.14.0005 to 9.14.0008, getting a Pre-Validation error:
We have detected some invalid TM Work Unit records.
Please run the CleanInvalidWorkItemsTMWorkUnits.sql in the <PPM_HOME>/deploy/804/SP4/phases/prevalidation directory to move these records and mark the workitems to be synched.
Then running the CleanInvalidWorkItemsTMWorkUnits.sql using the 914 directory, and an error is thrown:
ERROR at line 1:
ORA-00001: unique constraint (PPM.TM_WORK_UNITS_U1) violated
ORA-06512: at line 64
In CleanInvalidWorkItemsTMWorkUnits.sql, if there are 2 or more records which have the same WORK_ITEM_ID, WORK_ITEM_ID, WORK_ITEM_ID, only one record should be kept.
In this case, the record with WORK_ITEM_TYPE='PROJECT' should be deleted instead of setting WORK_ITEM_TYPE='TASK'.
1) Run the following SQL query:
WHERE work_item_id IN (SELECT task_id
WHERE parent_task_id IS NOT NULL)
AND work_item_id NOT IN (SELECT project_id
2) Check if there is a row(s) with work_item_type 'PROJECT', and if changing it to 'TASK' will violate the constraint.
3) If there is another row like this for the same, then BACKUP the result and delete it.
a. select TM_WORK_UNIT_ID from tm_work_units where work_item_type = 'PROJECT' and work_item_id=1234567;
b. update tm_work_units set work_item_type = 'PROJECT_OLD' where TM_WORK_UNIT_ID=<returned ID from above>
c. delete the row from tm_work_units where work_item_type is 'PROJECT_OLD' and TM_WORK_UNIT_ID is <returned ID from above>
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”