Query to fetch manager hierarchy based on Functional Reports-to

Summary:

Need help in fetching manager hierarchy based on most recently assigned Functional Reports-to manager.

Content (please ensure you mask any confidential information):

Hi,

I am trying to create a BIP report to fetch manager hierarchy based on Functional Reports-to manager in HCM. I noticed that an employee can be assigned multiple Functional Reports-to managers in the system. I need to fetch the manager hierarchy based on the most recently most recently assigned Functional Reports-to manager. I used the attached query to do so but this doesn't work in the below scenario -

Suppose an employee has 2 Functional Reports-to managers, A and B. Manager B is assigned after manager A, so B is the most recently assigned manager, now B also has 2 Functional Reports-to managers, C and D, out of which D is most recently assigned. In this case, the query fetches only the hierarchy of most recently assigned manager to B i.e., D and then his following hierarchy because of the max(Effective_Start_Date) logic I have used in the query.