ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B4E3DF780980FB27E0530100007F704C PER Position Y select pp.name value, pjv.name description from per_positions pp, per_jobs_vl pjv where (:$flex$.operating_unit is null or pp.business_group_id in (select haouv.business_group_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and sysdate between pp.date_effective and nvl(pp.date_end,sysdate) and pp.job_id=pjv.job_id order by pp.name US 22F57B8CD38F8713E06362FB09057B6CY 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 US MDS Test PO Hierarchy Mark's Test PO Hierachy Report 1=1 2=2 3=3 4=4 1 20 2=2 ppsv.name=:position_structure LOV Oracle PER_POS_STRUCTS Y select s.position_structure_id id, s.name value, null description from per_position_structures s where s.business_group_id=fnd_global.per_business_group_id order by value,description US Position Structure Position Structure 2 30 3=3 pcf.control_function_name=:document_type LOV custom Y select pcf.control_function_name value, flv.meaning description from po_control_functions pcf, fnd_lookup_values flv where pcf.document_type_code=flv.lookup_code(+) and flv.lookup_type(+)='DOCUMENT TYPE' and flv.view_application_id(+)=201 and flv.language(+)=userenv('lang') and flv.security_group_id(+)=0 order by flv.meaning, pcf.control_function_name US Document Type 3 40 4=4 pap1.name=:top_position LOV PER Position B4E3DF780980FB27E0530100007F704C Y select pp.name value, pjv.name description from per_positions pp, per_jobs_vl pjv where (:$flex$.operating_unit is null or pp.business_group_id in (select haouv.business_group_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and sysdate between pp.date_effective and nvl(pp.date_end,sysdate) and pp.job_id=pjv.job_id order by pp.name US Top Position