Hello everyone, I would like to share a solution I recently worked on with a customer, and hope this is helpful!
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?
- On your Test environment, backup table INV_FLAT_HIERARCHIES
- 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.
- Verify the data before moving the solution to Production environment
------------------------------
Sr Principal Support Engineer, Broadcom
------------------------------