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;