ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDE9B79D2E0530100007F1FF2 PER Business Group Y select pbg.name value, hla.location_code description from per_business_groups pbg, hr_locations_all hla where pbg.location_id=hla.location_id(+) order by pbg.name US ABA3B6EE99AC3F82E0530100007F0549 PER Person Type Y select distinct pptv.user_person_type value, pptv.system_person_type description from per_person_types_v pptv order by pptv.user_person_type US 0AC78BA7D0814AD51BD027EC4EE67A24 select haouv.name business_group, haouv2.name organization, hla.location_code location, hla.country, pjv.name job, pgv.name grade, xxen_util.meaning(paaf.employment_category,'EMP_CAT',3) employment_category, pptv.user_person_type person_type, count(*) headcount, sum(case when papf.sex='M' then 1 else 0 end) male_count, sum(case when papf.sex='F' then 1 else 0 end) female_count, round(avg(months_between(coalesce(:effective_date,sysdate),papf.original_date_of_hire)/12),1) avg_service_years, round(avg(months_between(coalesce(:effective_date,sysdate),papf.date_of_birth)/12),1) avg_age from hr_all_organization_units_vl haouv, (select papf.* from per_all_people_f papf where coalesce(:effective_date,sysdate) between papf.effective_start_date and papf.effective_end_date) papf, per_person_types_v pptv, (select paaf.* from per_all_assignments_f paaf where coalesce(:effective_date,sysdate) between paaf.effective_start_date and paaf.effective_end_date) paaf, per_assignment_status_types past, hr_all_organization_units_vl haouv2, per_jobs_vl pjv, per_grades_vl pgv, hr_locations_all hla where 1=1 and papf.business_group_id=haouv.organization_id and papf.person_id=paaf.person_id and papf.person_type_id=pptv.person_type_id and paaf.assignment_status_type_id=past.assignment_status_type_id and past.per_system_status='ACTIVE_ASSIGN' and paaf.primary_flag='Y' and paaf.assignment_type in ('E','C') and paaf.organization_id=haouv2.organization_id and paaf.job_id=pjv.job_id(+) and paaf.grade_id=pgv.grade_id(+) and paaf.location_id=hla.location_id(+) group by haouv.name, haouv2.name, hla.location_code, hla.country, pjv.name, pgv.name, xxen_util.meaning(paaf.employment_category,'EMP_CAT',3), pptv.user_person_type order by haouv.name, haouv2.name, hla.location_code, pjv.name, pgv.name Y US PER Headcount by Organization Active employee and contingent worker headcount summarized by business group, organization, location, job, grade, and employment category. Includes gender breakdown and average service years and age. Use Excel pivot tables for flexible grouping and analysis. Enginatics 1=1 :effective_date 1 10 1=1 haouv.name=:business_group LOV PER Business Group 8E2FF36EDE9B79D2E0530100007F1FF2 Y select pbg.name value, hla.location_code description from per_business_groups pbg, hr_locations_all hla where pbg.location_id=hla.location_id(+) order by pbg.name US Business Group 2 20 1=1 haouv2.name=:organization LOV custom Y select haouv.name value, hla.location_code description from hr_all_organization_units_vl haouv, hr_locations_all hla where haouv.location_id=hla.location_id(+) order by haouv.name US Organization 3 30 1=1 hla.location_code=:location LOV custom Y select hla.location_code value, hla.description from hr_locations_all hla where nvl(hla.inactive_date,sysdate+1)>sysdate order by hla.location_code US Location 4 40 1=1 pptv.user_person_type=:person_type LOV PER Person Type ABA3B6EE99AC3F82E0530100007F0549 Y select distinct pptv.user_person_type value, pptv.system_person_type description from per_person_types_v pptv order by pptv.user_person_type US Person Type 5 50 :effective_date Date sysdate Y US Effective Date