Introduction
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.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
