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.
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.
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.
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
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)
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
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.
Recommended Posts
