EBS 审批层次人员

SELECT DISTINCT ps.position_structure_id position_structure_id, ps.name position_structure_name, pe.pos_structure_element_id pos_structure_element_id, hp.position_id position_id, hp.name position_name, ppf.person_id person_id, ppf.last_name last_name, pe.order_flag order_flag FROM per_pos_structure_versions pv, per_position_structures ps, (SELECT pos_structure_version_id, pos_structure_element_id, parent_position_id, order_flag FROM (SELECT pe.pos_structure_version_id, pe.pos_structure_element_id, pe.parent_position_id, ‘1’ order_flag FROM per_pos_structure_elements pe UNION ALL SELECT pe.pos_structure_version_id, pe.pos_structure_element_id, pe.subordinate_position_id parent_position_id, ‘2’ order_flag FROM per_pos_structure_elements pe WHERE NOT EXISTS (SELECT 1 FROM per_pos_structure_elements pe2 WHERE pe2.pos_structure_version_id = pe.pos_structure_version_id AND pe.subordinate_position_id = pe2.parent_position_id))) pe, per_people_f ppf, per_all_assignments_f paf, hr_positions_f hp WHERE pv.pos_structure_version_id = pe.pos_structure_version_id AND pv.position_structure_id = ps.position_structure_id AND ppf.person_id = paf.person_id AND pe.parent_position_id = hp.position_id AND hp.position_id = paf.position_id AND (paf.effective_end_date is null or paf.effective_end_date > sysdate) AND (hp.EFFECTIVE_END_DATE is null or hp.EFFECTIVE_END_DATE > sysdate) AND (ppf.EFFECTIVE_END_DATE is null or ppf.EFFECTIVE_END_DATE > sysdate) AND paf.effective_end_date >= SYSDATE /* AND ps.position_structure_id in (select t.approval_path_id from cux_po_budappr_header t)*/ ORDER BY ps.position_structure_id, pe.pos_structure_element_id, pe.order_flag;

来源链接：https://www.cnblogs.com/ivenlin/p/18681287