AME: Requisition Approval Issue - Modify Dynamic Query

We are encountering issues with the setup for iProcurement requisition approval via AME, specifically with the supervisor chain of authority based on dollar amount. The intended process is for a requisition to go to the requester’s manager for approval. If the manager lacks the required delegation of authority (DOA), it should move up the chain until it reaches the appropriate approver.

While debugging the dynamic query used for this setup, I noticed that the code determining the supervisor hierarchy is not functioning as expected.

Example:

Requester: Janedoe

Query 1:

SELECT DISTINCT supervisor_id, level l1, connect_by_root supervisor_id sup  FROM per_all_assignments_f  WHERE trunc(sysdate) BETWEEN per_all_assignments_f.effective_start_date AND per_all_assignments_f.effective_end_date    AND assignment_type = 'E'    AND primary_flag = 'Y'    AND job_id IS NOT NULL  CONNECT BY person_id = PRIOR supervisor_id    AND trunc(sysdate) BETWEEN per_all_assignments_f.effective_start_date AND per_all_assignments_f.effective_end_date    AND supervisor_id IS NOT NULL    AND assignment_type = 'E'    AND primary_flag = 'Y'    AND job_id IS NOT NULL  START WITH person_id = {janedoe_id};