PFM_HIERARCHIES is a table in clarity for capturing portfolio hierarchies, and PFM_FLAT_HIERARCHIES is the equivalent where it flattens the hierarchy data such that is can be queried directly with SQL. It is noticed that these PFM tables are leaving orphaned child parent references long after the porfolios are removed from the system. This is causing some confusion when reporting from these PFM tables.
STEPS TO REPRODUCE:
Expected Result: Table returns rows that show Portfolio 1 as a child of Portfolio 3 ; we expected to see Portfolio1 removed from the hierarchy.
Actual Result: Tables show child parent references for Portfolio 1
This is caused by clarity product defect DE62777.
Defect currently being investigated by engineering.
As per the current code only direct children are deleted from the PFM_FLAT_HIERARCHIES or PFM_HIERARCHIES table after a child relationship is removed between two portfolios on the clarity UI. As a best practice the child portfolio association should be removed on the UI, and only then then the child portfolio should be deleted.
The following SQL query would help identify the orphaned child portfolio hierarchy id references from the Clarity schema:
SELECT * FROM PFM_HIERARCHIES WHERE CHILD_ID NOT IN (SELECT ID FROM PFM_PORTFOLIOS)
The IDs can be safely removed from the PFM_HIERARCHIES or PFM_FLAT_HIERARCHIES tables.