Project "Self" record in the Hierarchy table (INV_HIERARCHIES) is removed after xogging back data when complete flag is set on the hierarchy node

Document ID : KB000111859
Last Modified Date : 22/08/2018
Show Technical Document Details
Issue:
STEPS TO REPRODUCE:
  1. Create Project A in Clarity with a year duration 1-Jan-2016 - 31-DEC-2016
  2. Create two other projects with the same duration and add them as sub projects of project A created in step 1 above.
  3. In project A the two projects created in step 2 display as child projects in the Hierarchy page.
  4. Xog out Project A using the a sample xog file attached (project_hier_read.xml)
  5. Xog the output file back into Clarity. Ensure that complete flag is set on the hierarchy node [complete = "true"].
  6. Execute Select * from INV_HIERARCHIES where CHILD_ID = 'ID of master project'
Expected result: CHILD_ID record is intact in the INV_HIERARCHIES
Actual result: CHILD_ID record is removed the INV_HIERARCHIES table
Cause:
This is caused by CLRT-80440
Resolution:
This is fixed within CA PPM 15.1
 
Additional Information:
This does not fix already missing records, but will prevent form occurring again.

Following query will help identifying the investments where the SELF record is missing:

Select 
INV.ID, 
Inv.Code, 
INV.NAME 
From 
Inv_Investments Inv 
Where 
NOT EXISTS (SELECT 1 FROM INV_HIERARCHIES IFH WHERE IFH.PARENT_ID IS NULL AND IFH.CHILD_ID = INV.ID) 

In order to fix missing records, please follow steps below:
  1. Download the attached ADD_MISSING_SELF_ROWS.zip file
  2. Unzip the ADD_MISSING_INV_HIERARCHIES_SELF_ROWS.xml file
  3. Place it on a temp folder on the PPM server
  4. Through command line, navigate to the temp folder above
  5. Run the following command:
   dbpatch -install -file ADD_MISSING_INV_HIERARCHIES_SELF_ROWS.xml -apply
 
File Attachments:
ADD_MISSING_SELF_ROWS.zip