How to Query Position Hierarchy Chain of Command For A User

How to query position hierarchy chain of command for a user?

This is helpful when troubleshooting issues like 'Approval List Could Not Be Generated' when using position hierarchy.

Solution:-
 
 1.  Use the following query to determine the hierarchies to which a particular User belongs, and to identify those persons and users above the starting point User within each relevant hierarchy for this user.


/* Formatted on 7/4/2014 11:29:53 AM (QP5 v5.115.810.9015) */
SELECT peha.position_structure_id,
       peha.employee_id,
       fndu.user_id,
       ppos.position_id,
       pps.name hierarchy,
       fndu.user_name username,
       papf.full_name person,
       ppos.name position,
       peha.superior_level superiorpositionlevel,
       ppos2.name superiorposition,
       papf2.full_name superiorperson,
       fndu2.user_name superiorusername,
       peha.superior_id superiorpersonid,
       fndu2.user_id superioruserid,
       ppos2.position_id superiorposid
FROM po_employee_hierarchies_all peha,
     per_positions ppos,
     per_positions ppos2,
     per_all_people_f papf,
     per_all_people_f papf2,
     fnd_user fndu,
     fnd_user fndu2,
     per_position_structures pps
WHERE     pps.business_group_id = peha.business_group_id
      AND pps.position_structure_id = peha.position_structure_id
      AND fndu2.employee_id = papf2.person_id
      AND papf2.person_id = peha.superior_id
      AND papf2.effective_end_date > SYSDATE
      AND papf.person_id = peha.employee_id
      AND papf.effective_end_date > SYSDATE
      AND ppos2.position_id = peha.superior_position_id
      AND ppos.position_id = peha.employee_position_id
      AND peha.superior_level > 0
      AND peha.employee_id = fndu.employee_id
      AND fndu.user_name = UPPER ('&StartingUsername')
ORDER BY peha.position_structure_id, peha.superior_level, papf2.full_name



The above query may return multiple names for a certain level.  This is because position hierarchy allows multiple persons to hold the same position.  When routing purchasing documents for approval the first person alphabetically (based on full_name) for that level position will be used for routing the document up the hierarchy.

2. Use the following query to find the Hierarchy value specified in the Purchasing document types form. 

This will show which position_structure_id to focus on in the above query.  Change the org_id value, document_type_code, and document_subtype as required, depending on the document and organization you are troubleshooting. 

This query will only return a result if the document type is currently setup for hierarchy routing.

/* Formatted on 7/4/2014 11:31:36 AM (QP5 v5.115.810.9015) */
SELECT pdt.document_subtype,
       pdt.document_type_code,
       pps.name hierarchy,
       pdt.default_approval_path_id
FROM po_document_types_all_b pdt, per_position_structures pps
WHERE     pps.position_structure_id = pdt.default_approval_path_id
      AND pdt.org_id = 204
      AND pdt.document_type_code = 'REQUISITION'
      AND pdt.document_subtype = 'PURCHASE'