Concurrent Program After changing Hierarchy :- Fill Employee Hierarchy
Position Hierarchy - Query 1
===============================
SELECT name, position_structure_id
FROM per_position_structures_v
WHERE name LIKE 'ABC';
Current Version - Query 2
================================
SELECT pos_structure_version_id,
business_group_id,
position_structure_id,
version_number,
date_from,
date_to
FROM per_pos_structure_versions_v
WHERE position_structure_id = 61 -- From Query 1
and nvl( DATE_TO , sysdate+1) >= sysdate;
Parent Position - Query 3
================================
SELECT DISTINCT parent_position_id
FROM per_pos_structure_elements_v pse
WHERE pse.business_group_id = :p_business_group_id -- From Query 2
AND pse.pos_structure_version_id = :p_pos_structure_version_id -- From Query 2
AND NOT EXISTS
(SELECT subordinate_position_id
FROM per_pos_structure_elements_v pse1
WHERE pse1.business_group_id = :p_business_group_id
AND pse1.pos_structure_version_id = :p_pos_structure_version_id
AND pse1.subordinate_position_id = pse.parent_position_id)
Position Full Hierarchy - Query 4
================================
SELECT 0 levels,
pp.name position,
pse.parent_position_id position_id
,
pp.name PATH
FROM per_pos_structure_elements_v pse, per_positions pp
WHERE pse.business_group_id = :p_business_group_id -- From Query 2
AND pse.pos_structure_version_id = :p_pos_structure_version_id -- From Query 2
AND pse.parent_position_id = :p_parent_position_id -- From Query 3
AND pse.parent_position_id = pp.position_id
UNION
SELECT LEVEL,
has.name position,
has.position_id position_id
,
(SELECT name
FROM per_positions
WHERE position_id = :p_parent_position_id )
|| SYS_CONNECT_BY_PATH (has.name, ' --> ')
PATH
FROM (SELECT name, position_id
FROM apps.hr_all_positions_f_tl
WHERE language = USERENV ('LANG')) has,
per_pos_structure_elements pse
WHERE pse.business_group_id = :p_business_group_id
AND has.position_id = pse.subordinate_position_id
AND pse.pos_structure_version_id = :p_pos_structure_version_id
START WITH pse.parent_position_id = :p_parent_position_id
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
AND PRIOR pse.business_group_id = pse.business_group_id
ORDER BY 1
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;
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 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;
