Query to find next approver in Oracle EBS

SELECT     LEVEL, e.*
      FROM (SELECT DISTINCT papf.person_id, papf.employee_number,
                            papf.full_name "EMPLOYEE_FULL_NAME",
                            paaf.supervisor_id,
                            papf1.employee_number "SUPERVISOR_EMP_NUMBER",
                            papf1.full_name "SUPERVISOR_FULL_NAME",
                            ppt.user_person_type,
                            papf.business_group_id
                       FROM apps.per_all_people_f papf,
                            apps.per_all_assignments_f paaf,
                            apps.per_all_people_f papf1,
                            apps.per_person_types ppt
                      WHERE papf.person_id = paaf.person_id
                        AND papf1.person_id = paaf.supervisor_id
                       -- AND papf.business_group_id = 142
                        AND papf.business_group_id = paaf.business_group_id
                        AND TRUNC (SYSDATE) BETWEEN TRUNC(NVL(papf.effective_start_date,sysdate)) AND TRUNC(NVL(papf.effective_end_date,sysdate))
                        AND TRUNC (SYSDATE) BETWEEN TRUNC(NVL(paaf.effective_start_date,sysdate)) AND TRUNC(NVL(paaf.effective_end_date,sysdate))
                        AND ppt.person_type_id = papf.person_type_id
                        AND ppt.user_person_type <> 'Ex-employee'
                        ) e
CONNECT BY PRIOR person_id = supervisor_id
START WITH person_id = 'Enter Employee ID'

Oracle AME API to find Next Approver in AME hierarchy

Below is quick way to find next approver in Oracle AME hierarchy  ( Using oracle AME APIs)

declare
a   VARCHAR2(100);
b ame_util.approversTable2;
BEGIN
 AME_API2.getNextApprovers4(applicationIdIn   => 200,
                            transactionTypeIn => 'APEXP' ,  --Application Type
                            transactionIdIn   => '9999',   --Transaction ID
                            approvalProcessCompleteYNOut => a,
                            nextApproversOut   => b);
END;

How to enable FND Debug for Oracle OTL

To set FND logging you need to set the following FND system profiles at User Level (User reproducing the issue) 

1. FND: Debug Log Enabled - Yes 
2. FND: Debug Log Level - Statement 
3. FND: Debug Log Module - per.plsql.% 

Logout then login to the Applications 

Get the user id of your user

select user_id
from fnd_user
where user_name = '????'

Delete the existing messages for this user

delete fnd_log_messages
where user_id = ????

Recreate/Reproduce  the issue

Run this query from any Sql Tool and export the results grid to an excel spreadsheet.

select *
from fnd_log_messages
where user_id = ????
order by log_sequence

Turn off the logging when done