PO Approval and Hierarchy Details Script
PO Hierarchy Details Script
SELECT *
FROM (SELECT POS_STRUCTURE_VERSION_ID,
b.name structure_name,
PARENT_POSITION_ID,
(SELECT name
FROM hr_all_positions_f
WHERE position_id = PARENT_POSITION_ID)
PARENT_POSITION_NAME,
SUBORDINATE_POSITION_ID,
(SELECT name
FROM hr_all_positions_f
WHERE position_id = SUBORDINATE_POSITION_ID)
SUBORDINATE_POSITION_NAME
--(select full_name from per_all_people_f where person_id in(select person_id from per_all_assignments_f where position_id=SUBORDINATE_POSITION_ID))
FROM per_pos_structure_elements_v a, PER_POSITION_STRUCTURES b
WHERE 1 = 1
AND a.POS_STRUCTURE_VERSION_ID = b.POSITION_STRUCTURE_ID
AND POS_STRUCTURE_VERSION_ID <> 0)
WHERE STRUCTURE_NAME = 'XX_STRUTURE_NAME'
PO Approval Group Details Script
SELECT decode(ppca.org_id,83,'XXORG_NAME',181,'XXORG_NAME') Org_Name,
-- ppca.position_id,
(select name from per_positions where position_id=ppca.position_id)Position_Name,
-- ppca.job_id,
(select name from per_jobs where job_id=ppca.job_id) Job_Name,
-- ppca.control_group_id,
-- ppca.control_function_id,
control_function_name Document_Type,
control_group_name Approval_Group
FROM po_position_controls_all ppca,
po_control_groups_all pcga,
po_control_functions pcf
WHERE 1 = 1
AND ppca.control_group_id = pcga.control_group_id
AND ppca.control_function_id = pcf.control_function_id;
