Supervisor Hierarchy and Approval Limits in Oracle EBS R12

1--Supervisor Hierarchy of the employee
2SELECT LEVEL,
3       e.*
4FROM
5  (SELECT DISTINCT papf.person_id,
6                   papf.employee_number,
7                   papf.full_name "EMPLOYEE_FULL_NAME",
8                   paaf.supervisor_id,
9                   papf1.employee_number "SUPERVISOR_EMP_NUMBER",
10                   papf1.full_name "SUPERVISOR_FULL_NAME",
11                   pj.name job_name
12   FROM apps.per_all_people_f papf,
13        apps.per_all_assignments_f paaf,
14        apps.per_jobs pj,
15        apps.per_all_people_f papf1,
16        apps.per_person_types ppt
17   WHERE papf.person_id = paaf.person_id
18     AND papf1.person_id = paaf.supervisor_id
19     AND papf.business_group_id = 0
20     AND papf.business_group_id = paaf.business_group_id
21     AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
22     AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
23     AND ppt.person_type_id = papf.person_type_id
24     AND paaf.job_id=pj.job_id(+)
25     AND ppt.user_person_type 'Ex-employee') e CONNECT BY
26PRIOR supervisor_id=person_id
27START WITH person_id = 123456;
28
29--Approval Limits
30SELECT d.full_name,
31       e.job_id,
32       g.control_group_name,
33       h.object_code,
34       h.amount_limit,
35       h.*
36FROM per_all_people_f d,
37     per_all_assignments_f e,
38     po_position_controls_all f,
39     po_control_groups_all g,
40     po_control_rules h,
41     apps.po_control_functions pcf
42WHERE 1=1
43  AND d.effective_end_date > SYSDATE
44  AND d.person_id = e.person_id
45  AND e.effective_end_date > SYSDATE
46  AND e.job_id = f.job_id
47  AND f.control_group_id = g.control_group_id
48  AND g.control_group_id = h.control_group_id
49  AND pcf.control_function_name = 'Approve Purchase Requisitions'
50  AND f.control_function_id = pcf.control_function_id
51  AND g.org_id = 100
52  AND d.person_id=123456;