INV_FLAT_HIERARCHIES has outdated and orphaned data, how to repopulate it?

Products

• Clarity PPM On Premise

Issue/Introduction

INV_FLAT_HIERARCHIES has outdated and orphaned data, due to having parents, grandparents, great grandparents etc we cannot easily delete them. How to safely repopulate this table?

Environment

Release : All supported Clarity releases

Component : CA PPM PROJECT MANAGEMENT

Resolution

1. On your Test environment, backup table INV_FLAT_HIERARCHIES
2. Now run the code below:

declare

    cursor h is select child_id, parent_id from INV_HIERARCHIES where parent_id is not null;

begin

    execute immediate 'truncate table INV_FLAT_HIERARCHIES';

    FOR h_rec IN h

    LOOP

        INV_HIERS_FLAT_INSERT(h_rec.CHILD_ID, h_rec.PARENT_ID);

    end loop;

end;

 

This will truncate the table INV_FLAT_HIERARCHIES, and completely repopulate it from INV_HIERARCHIES, using the OOTB stored procedure. It will not copy any of the "orphans" or any self lines for any projects that's no longer have hierarchies, and it will only insert the records needed.

3. Verify the data before moving the solution to Production environment 

Additional Information

For any comments, our community discussion here