SQL to Find Employee – Supervisor Hierarchy details

Background

The Purchase order or Requisitions are not valid unless it is approved. In any of the procurement process, most of the issues occur in the approval of the purchasing document due to the issues in approval hierarchy and related setups.

Hence we have built a query to show the approval hierarchy of the Requestor of the Requisition or Purchase Order along with other required details like Supervisor Name, User Name, Expense account details etc.

This will help the support person to easily identify the issues in the hierarchy in a single query output and to provide required resolution. 

Resolution:

Step 1: 

Please pass 'Employee number' of Requester in the below code. Run the script download the output

SELECT

fu.user_name "User Name",

haou.name "Employee BG",

fu.employee_id "Employee ID @User",

gl.name "Emp. Ledger Name",

ppx.full_name "Employee Name",

ppx.employee_number "Employee Number",

ppx.person_id "Employee ID @ Person",

pjv.name "Employee Job Name",

pjv.approval_authority "Job Level",

hl.location_code "Employee Location",

gcc.concatenated_segments "Default Expense Account",

ppx1.full_name "Supervisor Name",

ppx1.person_id "Supervisor Emp ID @ Person",

ppx1.employee_number "SuperVisor Emp Num @ Person",

haou1.name "Supervisor BG",

CONNECT_BY_ISCYCLE

FROM

apps.per_people_x ppx,

apps.per_people_x ppx1,

apps.per_assignments_x pax,

apps.per_jobs_v pjv,

apps.gl_code_combinations_kfv gcc,

apps.hr_locations hl,

apps.fnd_user fu,

apps.gl_ledgers gl,

apps.hr_all_organization_units haou

,apps.hr_all_organization_units haou1

WHERE 1=1

and ppx.person_id = pax.person_id

and ppx.person_id = fu.employee_id(+)

and pax.job_id = pjv.job_id

and pax.default_code_comb_id = gcc.code_combination_id(+)

and pax.location_id = hl.location_id

and pax.set_of_books_id = gl.ledger_id(+)

and ppx1.person_id = pax.supervisor_id

and ppx.business_group_id = haou.organization_id

and ppx1.business_group_id = haou1.organization_id

connect by nocycle prior pax.supervisor_id = pax.person_id

start with  ppx.employee_number =  '10484'; ------------------------------------------------------------ The above Query provides below results.

User Name	Employee BG	Employee ID @User	Emp. Ledger Name
XXXX.BLANXXXX	XX Business Group	34658	XX Ledger
YYYY.YYYEST	XX Business Group	34513	XX Ledger
ZZZZ.ZZZZERT	XX Business Group	34643	XX Ledger

Employee Name	Employee Number	Employee ID @ Person	Employee Job Name	Job Level
Blan, M	10484	34658	Responsable Achats	0
Est, D	10339	34513	Direct. Projet	2
ERT, A	10469	34643	Directeur Général	5

Employee Location	Default Expense Account
London	12345.6789012.00000.03420.9999999.00000.0000.0000.0000
London	12345.6789012.00000.03700.9999999.00000.0000.0000.0000
London	12345.6789012.00000.03100.9999999.00000.0000.0000.0000

Supervisor Name	Supervisor Emp ID @ Person	SuperVisor Emp Num @ Person	Supervisor BG	CONNECT_BY_ISCYCLE
David	34513	10339	XX Business Group	0
Arnaud	34643	10469	XX Business Group	0
Marshall	34718	10004	XX Business Group	1

Step 2: 

From Table # 1, find the  Employee number of  'Supervisor' for the last record. In the above example it is (10004)

Step 3: 

Add the output from Step 2 into the Table # 1

The Final Output would be as follows:

User Name	Employee BG	Employee ID @User	Emp. Ledger Name
XXXX.BLANXXXX	XX Business Group	34658	XX Ledger
YYYY.YYYEST	XX Business Group	34513	XX Ledger
ZZZZ.ZZZZERT	XX Business Group	34643	XXLedger
	XX Business Group		XX Ledger

Employee Name	Employee Number	Employee ID @ Person	Employee Job Name	Job Level
Blan, M	10484	34658	Responsable Achats	0
Est, D	10339	34513	Direct. Projet	2
ERT, A	10469	34643	Directeur Général	5
Marshall	10004	34718	GM Western	15

Employee Location	Default Expense Account
London	12345.6789012.00000.03420.9999999.00000.0000.0000.0000
London	12345.6789012.00000.03700.9999999.00000.0000.0000.0000
London	12345.6789012.00000.03100.9999999.00000.0000.0000.0000

Supervisor Name	Supervisor Emp ID @ Person	SuperVisor Emp Num @ Person	Supervisor BG	CONNECT_BY_ISCYCLE
David	34513	10339	XX Business Group	0
Arnaud	34643	10469	XX Business Group	0
Marshall	34718	10004	XX Business Group	1
Revert	34643	10469	XX Business Group	0

Conclusion

From the final table we can find out that user id for the employee "Marshall" is not mapped. Hence the approval generation issue has occurred.  

This issue will be resolved if the employee and user id of the Marshall is mapped.