Wednesday 23 August 2017

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.

4 comments: