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?
Release : All supported Clarity releases
Component : CA PPM PROJECT MANAGEMENT
declarecursor h is select child_id, parent_id from INV_HIERARCHIES where parent_id is not null;beginexecute immediate 'truncate table INV_FLAT_HIERARCHIES';FOR h_rec IN hLOOPINV_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