Oracle Purchasing Position Hierarchy Setup Query

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;