Some important
queries related to po_fill_emp_hierarchy
in oracle purchasing
/* Get the list of all
the position hierarchies. */
SELECT pps.position_structure_id, ppsv.pos_structure_version_id
FROM
per_position_structures pps,
per_pos_structure_versions ppsv,
financials_system_parameters fsp
WHERE
NVL (pps.business_group_id, fsp.business_group_id) =
fsp.business_group_id
AND ppsv.position_structure_id = pps.position_structure_id
AND TRUNC (SYSDATE) BETWEEN date_from
AND NVL (date_to,
TRUNC ( (SYSDATE + 1)));
/* Get the list of positions in the security position structure. */
SELECT DISTINCT ppse.parent_position_id
FROM
per_pos_structure_elements ppse
WHERE
ppse.pos_structure_version_id = p_active_version
UNION
SELECT DISTINCT ppse.subordinate_position_id
FROM
per_pos_structure_elements ppse
WHERE
ppse.pos_structure_version_id = p_active_version;
/* Get the List of positions superior to the current one */
SELECT ppse.parent_position_id superior_id, MIN (LEVEL)
FROM per_pos_structure_elements ppse
CONNECT BY (ppse.subordinate_position_id =
PRIOR ppse.parent_position_id
AND ppse.pos_structure_version_id =
PRIOR ppse.pos_structure_version_id)
START WITH ( (ppse.subordinate_position_id
= p_position_id
AND ppse.pos_structure_version_id
= p_active_version)
AND NVL (ppse.business_group_id,
p_business_group_id) =
p_business_group_id)
GROUP BY ppse.parent_position_id;
