SELECT distinct peha.employee_position_id, pap1.name position_name, pcr.amount_limit, peha.superior_position_id superior_id,
-------------------------------------------------------------
CASE
WHEN pcr.amount_limit > 1000000 THEN
'Executive'
WHEN pcr.amount_limit > 60000 THEN
'Manager'
ELSE 'Staff'
END role_type,
--------------------------------------------------------------
pap2.name superior_name,
ppsv.name hierarchy, pcga.control_group_name, pcf.control_function_name, pcga.org_id
-----------------------------------------------------------------------------------------------------------------------------
FROM
apps.po_employee_hierarchies_all peha,
apps.per_all_positions pap1,
apps.per_all_positions pap2,
apps.per_position_structures_v ppsv,
apps.po_position_controls_all ppca,
apps.po_control_groups_all pcga,
apps.po_control_rules pcr,
apps.po_control_functions pcf
------------------------------------------------------------------------------------------------------------------------------
WHERE 1 = 1
and peha.employee_position_id = pap1.position_id
and peha.superior_position_id = pap2.position_id
and peha.position_structure_id = ppsv.position_structure_id
and pap1.position_id = ppca.position_id --- eliminated 3 out of 20 positions
and ppca.control_group_id = pcga.control_group_id
and pcga.control_group_id = pcr.control_group_id
and pcga.org_id = ppca.org_id
and ppca.control_function_id = pcf.control_function_id
----------------------------------------------------------------------------
and peha.superior_level = 1
--and ppca.position_control_id is not null -- don't really know what this does??
and pcga.enabled_flag = 'Y'
and pcr.inactive_date is NULL
and pcf.enabled_flag = 'Y'
and pcr.amount_limit > 0
and peha.business_group_id = 202
and ppca.org_id = 204
--and pcf.control_function_name = 'Approve Purchase Requisitions'
-------------------------------------------------------------------------------------------------------------
and peha.position_structure_id = 3
--and ppsv.name=:Hierarchy_Name
and 2=2
and 3=3
------------------------------------------------------------------------------------------
UNION ALL --- Second Query retrieves top position in hierarchy
------------------------------------------------------------------------------------------
SELECT distinct peha.employee_position_id, pap1.name position_name, pcr.amount_limit, null superior_id,
------------------------------------------------------------------------------------------
CASE
WHEN pcr.amount_limit > 1000000 THEN
'Executive'
WHEN pcr.amount_limit > 60000 THEN
'Manager'
ELSE 'Staff'
END role_type,
--------------------------------------------------------------
pap2.name superior_name,
ppsv.name hierarchy, pcga.control_group_name, pcf.control_function_name, pcga.org_id
----------------------------------------------------------------------------------------------------------
FROM
apps.po_employee_hierarchies_all peha,
apps.per_all_positions pap1,
apps.per_all_positions pap2,
apps.per_position_structures_v ppsv,
apps.po_position_controls_all ppca,
apps.po_control_groups_all pcga,
apps.po_control_rules pcr,
apps.po_control_functions pcf
------------------------------------------------------------------------------------------------------------
WHERE 1 = 1
and peha.employee_position_id = pap1.position_id
and peha.superior_position_id = pap2.position_id
and peha.position_structure_id = ppsv.position_structure_id
and pap1.position_id = ppca.position_id --- eliminated 3 out of 20 positions
and ppca.control_group_id = pcga.control_group_id
and pcga.control_group_id = pcr.control_group_id
and pcga.org_id = ppca.org_id
and ppca.control_function_id = pcf.control_function_id
----------------------------------------------------------------------------
and peha.superior_level = 0
--and ppca.position_control_id is not null -- don't know what this does??
and pcga.enabled_flag = 'Y'
and pcr.inactive_date is NULL
and pcf.enabled_flag = 'Y'
and pcr.amount_limit > 0
and peha.business_group_id = 202
and ppca.org_id = 204
--and pcf.control_function_name = 'Approve Purchase Requisitions'
and pap1.name = 'EX140.Chief Financial Officer' -- Top position in hierarchy
and peha.position_structure_id = 3
------------------------------------------------------------------------------------------------------
---and ppsv.name=:Hierarchy_Name
and 2=2
and 3=3
and 4=4
-----------------------------------------------------------------------------------
ORDER BY position_name |
