Query to find po approval hierarchy in oracle apps

Query to find po approval hierarchy in oracle apps

In this post , We will be discuss about Query to find PO approval hierarchy in oracle apps. PO approval hierarchy will helps to extract the PO approval positional hierarchy. We will get the complete PO approval hierarchy from top to bottom as per the positions level in oracle apps. We need to know the top Position in the position hierarchy to extract the position based PO approval hierarchy in oracle apps using this query. We also need to know the business group id for this query. IF we have only the single Business group in the application then we can remove this parameter.

Tables Used By Query to find PO approval hierarchy in oracle apps

1.per_positions

2.hr_all_positions_f_tl

3.per_pos_structure_elements

4.per_pos_structure_elements_v

Detail SQL Query to find po approval hierarchy in oracle apps

Here below is the detail sql to find positional based PO approval hierarchy in oracle apps.

(SELECT DISTINCT HR_POS.NAME POSITION, HR_POS.position_id position_id,
(SELECT NAME
FROM per_positions
WHERE position_id = :V_TOP_POSITON_ID)
|| SYS_CONNECT_BY_PATH (HR_POS.NAME, '/') HIERARCHY_PATH
FROM (SELECT NAME, position_id
FROM apps.hr_all_positions_f_tl
WHERE LANGUAGE = USERENV ('LANG')) HR_POS,
per_pos_structure_elements pse
WHERE pse.business_group_id = :V_BUISNESS_GROUP_ID
AND HR_POS.position_id = pse.subordinate_position_id
START WITH pse.parent_position_id = :V_TOP_POSITON_ID --Top position in THE POSITION HIERARCHY
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
union
SELECT per_pos.NAME POSITION_NAME, pse.parent_position_id position_id,
per_pos.NAME HIERARCHY_PATH
FROM per_pos_structure_elements_v pse, per_positions per_pos
WHERE pse.business_group_id = :V_BUISNESS_GROUP_ID
AND pse.parent_position_id = per_pos.position_id
AND pse.parent_position_id = :V_TOP_POSITON_ID --Top position in THE POSITION HIERARCHY
) pos_Hierar
WHERE al.pos_id (+)= pos_Hierar.position_id
AND ass.pos_id(+) = pos_Hierar.position_id
order by path

Query to find po approval hierarchy in oracle apps