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 76E7826FB8D4F36193C9CF5267269689 select haouv.name business_group, papf.employee_number, papf.full_name, papf.email_address, xxen_util.meaning(papf.sex,'SEX',3) gender, papf.date_of_birth, trunc(months_between(sysdate,papf.date_of_birth)/12) age, papf.original_date_of_hire hire_date, ppos.adjusted_svc_date adjusted_service_date, trunc(months_between(sysdate,nvl(ppos.adjusted_svc_date,papf.original_date_of_hire))/12) service_years, mod(trunc(months_between(sysdate,nvl(ppos.adjusted_svc_date,papf.original_date_of_hire))),12) service_months, case when mod(trunc(months_between(sysdate,nvl(ppos.adjusted_svc_date,papf.original_date_of_hire))/12),5)=0 and trunc(months_between(sysdate,nvl(ppos.adjusted_svc_date,papf.original_date_of_hire))/12)>0 then trunc(months_between(sysdate,nvl(ppos.adjusted_svc_date,papf.original_date_of_hire))/12) end milestone_years, add_months(nvl(ppos.adjusted_svc_date,papf.original_date_of_hire), ceil(months_between(sysdate,nvl(ppos.adjusted_svc_date,papf.original_date_of_hire))/12)*12) next_anniversary, haouv2.name organization, pjv.name job, hapft.name position, pgv.name grade, hla.location_code location, papf2.full_name supervisor_name, xxen_util.meaning(paaf.employment_category,'EMP_CAT',3) employment_category, pastv.user_status assignment_status from hr_all_organization_units_vl haouv, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, per_periods_of_service ppos, (select paaf.* from per_all_assignments_f paaf where sysdate between paaf.effective_start_date and paaf.effective_end_date) paaf, per_assignment_status_types_v pastv, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf2, hr_all_organization_units_vl haouv2, per_jobs_vl pjv, hr_all_positions_f_tl hapft, per_grades_vl pgv, hr_locations_all hla where 1=1 and papf.business_group_id=haouv.organization_id and papf.person_id=ppos.person_id and ppos.actual_termination_date is null and papf.person_id=paaf.person_id and paaf.primary_flag='Y' and paaf.assignment_type='E' and paaf.assignment_status_type_id=pastv.assignment_status_type_id and paaf.supervisor_id=papf2.person_id(+) and paaf.organization_id=haouv2.organization_id and paaf.job_id=pjv.job_id(+) and paaf.position_id=hapft.position_id(+) and hapft.language(+)=userenv('lang') and paaf.grade_id=pgv.grade_id(+) and paaf.location_id=hla.location_id(+) order by haouv.name, trunc(months_between(sysdate,nvl(ppos.adjusted_svc_date,papf.original_date_of_hire))/12) desc, papf.full_name Y US PER Employee Length of Service Active employees with calculated length of service in years and months, based on the adjusted service date (or hire date if not set). Shows milestone years (multiples of 5), next service anniversary, age, and current assignment details. Useful for recognition programs, benefits eligibility, and workforce tenure analysis. Enginatics 1=1 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 trunc(months_between(sysdate,nvl(ppos.adjusted_svc_date,papf.original_date_of_hire))/12)>=:min_service_years Number US Minimum Service Years 4 40 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