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.
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:
* PFM_HIERARCHIES CHILD_ID ( ID PFM_PORTFOLIOS)
The IDs can be safely removed from the PFM_HIERARCHIES or PFM_FLAT_HIERARCHIES tables.