Query Position Hierarchy Chain of Command For A User

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.

QUERY1

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.

QUERY2

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'