Issue Impact

• Purchase Orders are not getting approved, and are getting stuck in the workflow under 'error' status.
• Purchase Order status shown as 'In Process'.

Issue Description

1. This issue can come for an existing employee, which is already attached in the Position Hierarchy.

2. When you see from the Front End all Employee and Purchasing setups looks fine.

3. No errors reported in 'Fill Employee Hierarchy' concurrent program

4. You see the following error when you run Application Diagnostics -> Diagnose -> Purchasing  Application -> Setup Checks -> User Approval

ERROR - This position is not attached to any Position Hierarchy
ACTION - Attach this Position to the default position hierarchy, which can be used for forwarding documents for approval

Example taken to demonstrate

• Employee = Smith, Mr. Jonathan
• Job = VPM200.Vice President Of Materials
• Position = VPM200.VP Materials

Investigation to find the root cause

Run following SQL to find the root cause of the issue

1. In Release 12.1.3 onwards first run this sql to setup the Operating Unit

call po_moac_utils_pvt.set_org_context(&org_id);

2. Find the employee id which is unable to approve the PO's

select employee_id from hr_employees_current_v where first_name like 'Jonathan%';

3. Find the approval path id for the Purchasing Document which is not getting approved

SELECT podt.document_subtype||' '||podt.document_type_code,
default_approval_path_id, forwarding_mode_code,
NVL(can_preparer_approve_flag, 'N')
FROM po_document_types_all podt
WHERE org_id = &org_id;

4. Now find whether there are any records that exists for this emplyee in PO_EMPLOYEE_HIERARCHIES_ALL table

select * from PO_EMPLOYEE_HIERARCHIES_ALL where employee_id = &employee_id

If no rows are returned, that confirms the issue that this table has not got populated from 'Fill Employee Hierarchy' concurrent program, and this is the root cause of this issue.

5. If you find a record in the above table for the given employee_id, then check whether there is a record for the Purchasing Document that is resulting in error by executing the following SQL. This SQL contains the combination of approval_path_id and emplyoee_id to confirm whether the emplyee has been setup to approve the Purchasing document.

SELECT poeh.superior_id,
hrec.full_name,
poeh.superior_level
FROM hr_employees_current_v hrec,
po_employee_hierarchies poeh
WHERE poeh.position_structure_id = &p_approval_path_id
AND poeh.employee_id = &p_person_id
AND hrec.employee_id = poeh.superior_id
AND poeh.superior_level > 0
ORDER BY poeh.superior_level, hrec.full_name;

Resolution - 1 

1. Having found the above issue, the quick fix is to assign another existing Position to the employee (or recreate a new position and assing it).

2. Run 'Fill Employee Hierarchy' program

3. Run Application Diagnostics to make sure there are no issues

4. Confirm if by creating and approving a new Purchase Order with this employee (without any workflow errors)

Resolution - 2

Raise a Support Request with Oracle Support to get a data fix or one-time patch fix

 

Environment

• Oracle E Business Suite Release 12.1.x