mike_se Contributor.

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:
java.sql.SQLException: ORA-20003:
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

Defect QCCR1L53417

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:
FROM tm_work_units
WHERE work_item_id IN (SELECT task_id
FROM wp_tasks
WHERE parent_task_id IS NOT NULL)
AND work_item_id NOT IN (SELECT project_id
FROM pm_projects);

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>

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
Labels (1)
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.