ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C1AF7DEF44984244A654A6149CE63346Y select x.business_group, x.record_type, x.job, x.position, x.position_type, x.position_status, x.organization, x.location, x.entry_grade, x.valid_grades, x.permanent, x.fte, x.max_persons, x.headcount, x.incumbent_fte, x.vacancies, x.working_hours, x.frequency, x.earliest_hire_date, x.fill_by_date, x.supervisor_position, x.date_from, x.date_to, &detail_columns x.last_updated_by, x.last_update_date from ( select y.*, inc.headcount, inc.incumbent_fte, case when y.record_type_code='P' then greatest(nvl(y.max_persons,1)-nvl(inc.headcount,0),0) end vacancies from ( select haouv.name business_group, 'P' record_type_code, 'Position' record_type, 'P'||hapfv.position_id incumbent_key, hapfv.position_id, hapfv.job_id, pjv.name job, hapfv.name position, xxen_util.meaning(hapfv.position_type,'POSITION_TYPE',3) position_type, pstv.shared_type_name position_status, pstv.system_type_cd position_status_code, haouv2.name organization, hla.location_code location, (select pgv.name from per_grades_vl pgv where hapfv.entry_grade_id=pgv.grade_id) entry_grade, (select listagg(z.name,', ') within group (order by z.sequence, z.name) from (select y.*, sum(lengthb(y.name)+2) over (partition by y.position_id order by y.sequence, y.name) length from (select pvg.position_id, pgv.name, min(pgv.sequence) sequence from per_valid_grades pvg, per_grades_vl pgv where :effective_date between pvg.date_from and nvl(pvg.date_to,:effective_date) and pvg.grade_id=pgv.grade_id group by pvg.position_id, pgv.name) y) z where hapfv.position_id=z.position_id and z.length<=4000) valid_grades, xxen_util.yes(hapfv.permanent_temporary_flag) permanent, hapfv.fte, hapfv.max_persons, hapfv.working_hours, xxen_util.meaning(hapfv.frequency,'FREQUENCY',3) frequency, hapfv.earliest_hire_date, hapfv.fill_by_date, (select hapfv2.name from hr_all_positions_f_vl hapfv2 where hapfv.supervisor_position_id=hapfv2.position_id and :effective_date between hapfv2.effective_start_date and hapfv2.effective_end_date) supervisor_position, hapfv.date_effective date_from, hapfv.date_end date_to, xxen_util.user_name(hapfv.last_updated_by) last_updated_by, xxen_util.client_time(hapfv.last_update_date) last_update_date from hr_all_positions_f_vl hapfv, hr_all_organization_units_vl haouv, per_jobs_vl pjv, per_shared_types_vl pstv, hr_all_organization_units_vl haouv2, hr_locations_all hla where :effective_date between hapfv.effective_start_date and hapfv.effective_end_date and hapfv.business_group_id=haouv.organization_id and hapfv.job_id=pjv.job_id and hapfv.availability_status_id=pstv.shared_type_id(+) and hapfv.organization_id=haouv2.organization_id and hapfv.location_id=hla.location_id(+) union all select haouv.name business_group, 'J' record_type_code, 'Job without Positions' record_type, 'J'||pjv.job_id incumbent_key, to_number(null) position_id, pjv.job_id, pjv.name job, null position, null position_type, null position_status, null position_status_code, null organization, null location, null entry_grade, (select listagg(z.name,', ') within group (order by z.sequence, z.name) from (select y.*, sum(lengthb(y.name)+2) over (partition by y.job_id order by y.sequence, y.name) length from (select pvg.job_id, pgv.name, min(pgv.sequence) sequence from per_valid_grades pvg, per_grades_vl pgv where :effective_date between pvg.date_from and nvl(pvg.date_to,:effective_date) and pvg.grade_id=pgv.grade_id group by pvg.job_id, pgv.name) y) z where pjv.job_id=z.job_id and z.length<=4000) valid_grades, null permanent, to_number(null) fte, to_number(null) max_persons, to_number(null) working_hours, null frequency, to_date(null) earliest_hire_date, to_date(null) fill_by_date, null supervisor_position, pjv.date_from, pjv.date_to, xxen_util.user_name(pjv.last_updated_by) last_updated_by, xxen_util.client_time(pjv.last_update_date) last_update_date from per_jobs_vl pjv, hr_all_organization_units_vl haouv where :effective_date between pjv.date_from and nvl(pjv.date_to,:effective_date) and pjv.business_group_id=haouv.organization_id and pjv.job_id not in (select hapf.job_id from hr_all_positions_f hapf where :effective_date between hapf.effective_start_date and hapf.effective_end_date) ) y, ( select nvl2(paaf.position_id,'P'||paaf.position_id,'J'||paaf.job_id) incumbent_key, count(*) headcount, sum((select pabvf.value from per_assignment_budget_values_f pabvf where paaf.assignment_id=pabvf.assignment_id and pabvf.unit='FTE' and :effective_date between pabvf.effective_start_date and pabvf.effective_end_date)) incumbent_fte from per_all_assignments_f paaf, per_assignment_status_types past where :effective_date between paaf.effective_start_date and paaf.effective_end_date and paaf.assignment_type in ('E','C') and paaf.assignment_status_type_id=past.assignment_status_type_id and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN','ACTIVE_CWK','SUSP_CWK_ASG') and (paaf.position_id is not null or paaf.job_id is not null) group by nvl2(paaf.position_id,'P'||paaf.position_id,'J'||paaf.job_id) ) inc where y.incumbent_key=inc.incumbent_key(+) ) x &detail_from where 3=3 &detail_where order by x.business_group, x.job, x.position nulls first Initial version. US PER Jobs and Positions Positions valid at the Effective Date with their job, organization, status, valid grades, FTE, maximum persons, headcount and incumbent FTE, plus the jobs that have no position valid at the Effective Date, for enterprises that use jobs only. Headcount counts the active and suspended employee and contingent worker assignments of the position, or for a job without positions, the assignments of the job without a position. Incumbent FTE sums their FTE assignment budget values. Vacancies is the maximum persons of the position, one if blank, minus its headcount. Detail Level Incumbents adds one row per incumbent assignment. Detail Level Extra Information lists one row per position or job extra information record, a position row also showing the extra information of its job. Select an Information Type to show its segments as columns. Enginatics &detail_columns &detail_from &detail_where 3=3 :effective_date :information_type 1 10 :effective_date Date sysdate Y US Effective Date 2 20 3=3 x.business_group=: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 select pbg.name from per_business_groups pbg where pbg.business_group_id=fnd_profile.value('PER_BUSINESS_GROUP_ID') US Business Group 3 30 3=3 x.organization=:organization LOV custom Y select distinct haouv.name value, haouv0.name description from hr_all_positions_f hapf, hr_all_organization_units_vl haouv, hr_all_organization_units_vl haouv0 where hapf.organization_id=haouv.organization_id and haouv.business_group_id=haouv0.organization_id and (:$flex$.business_group is null or xxen_util.contains(:$flex$.business_group,haouv0.name)='Y') order by haouv.name US Organization 4 40 3=3 x.job=:job LOV PER Job B4E3DF78097FFB27E0530100007F704C Y select pjv.name value, haouv.name description from per_jobs_vl pjv, hr_all_organization_units_vl haouv where (:$flex$.operating_unit is null or pjv.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 pjv.business_group_id=haouv.organization_id and sysdate between pjv.date_from and nvl (pjv.date_to, sysdate) order by pjv.name US Job 5 50 3=3 x.position=: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 Position 6 60 3=3 x.record_type_code=:record_type LOV custom Y select 'P' id, 'Position' value, null description from dual union all select 'J' id, 'Job without Positions' value, null description from dual US Record Type 7 70 3=3 x.vacancies>0 and x.position_status_code='ACTIVE' LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Vacant Only Lists only active positions whose headcount is below their maximum persons, or below one where maximum persons is blank. 8 80 &detail_columns ia.assignment_number, (select papf.full_name from per_all_people_f papf where ia.person_id=papf.person_id and :effective_date between papf.effective_start_date and papf.effective_end_date) incumbent, (select nvl(papf.employee_number,papf.npw_number) from per_all_people_f papf where ia.person_id=papf.person_id and :effective_date between papf.effective_start_date and papf.effective_end_date) employee_number, ia.assignment_status, ia.assignment_fte, ia.assignment_start_date, LOV custom Y select 'P' id, 'Position' value, null description from dual union all select 'I' id, 'Incumbents' value, 'Incumbent assignments' description from dual union all select 'E' id, 'Extra Information' value, 'Position and job extra information' description from dual I US Detail Level Incumbents and Extra Information add one row per incumbent assignment or extra information record. 9 &detail_columns select x.column_text from ( select 0 seq, 'decode(eit.ddf,''Extra Position Info DDF'',''Position'',''Job'') extra_information_of, (select fdfcv.descriptive_flex_context_name from fnd_descr_flex_contexts_vl fdfcv where fdfcv.application_id=800 and eit.ddf=fdfcv.descriptive_flexfield_name and eit.information_type=fdfcv.descriptive_flex_context_code) information_type,' column_text from dual union all select min(fdfcuv.column_seq_num) seq, 'case eit.ddf '||listagg('when '''||fdfcuv.descriptive_flexfield_name||''' then xxen_util.display_flexfield_value(800,'''||fdfcuv.descriptive_flexfield_name||''',eit.information_type,'''||fdfcuv.application_column_name||''',eit.row_id,eit.information'||regexp_substr(fdfcuv.application_column_name,'\d+$')||')',' ') within group (order by fdfcuv.descriptive_flexfield_name)||' end "'||substrb(replace(fdfcuv.form_left_prompt,'"'),1,xxen_report.max_column_length)||'",' column_text from fnd_descr_flex_col_usage_vl fdfcuv where fdfcuv.application_id=800 and fdfcuv.descriptive_flexfield_name in ('Extra Position Info DDF','Extra Job Info DDF') and fdfcuv.descriptive_flex_context_code=:information_type and fdfcuv.enabled_flag='Y' and fdfcuv.display_flag='Y' group by substrb(replace(fdfcuv.form_left_prompt,'"'),1,xxen_report.max_column_length) ) x order by x.seq E US Detail Level 10 &detail_from , ( select 'P'||ppei.position_id incumbent_key, 'Extra Position Info DDF' ddf, ppei.information_type, ppei.rowid row_id, ppei.poei_information1 information1, ppei.poei_information2 information2, ppei.poei_information3 information3, ppei.poei_information4 information4, ppei.poei_information5 information5, ppei.poei_information6 information6, ppei.poei_information7 information7, ppei.poei_information8 information8, ppei.poei_information9 information9, ppei.poei_information10 information10, ppei.poei_information11 information11, ppei.poei_information12 information12, ppei.poei_information13 information13, ppei.poei_information14 information14, ppei.poei_information15 information15, ppei.poei_information16 information16, ppei.poei_information17 information17, ppei.poei_information18 information18, ppei.poei_information19 information19, ppei.poei_information20 information20, ppei.poei_information21 information21, ppei.poei_information22 information22, ppei.poei_information23 information23, ppei.poei_information24 information24, ppei.poei_information25 information25, ppei.poei_information26 information26, ppei.poei_information27 information27, ppei.poei_information28 information28, ppei.poei_information29 information29, ppei.poei_information30 information30 from per_position_extra_info ppei where (:information_type is null or ppei.information_type=:information_type) union all select 'J'||pjei.job_id incumbent_key, 'Extra Job Info DDF' ddf, pjei.information_type, pjei.rowid row_id, pjei.jei_information1 information1, pjei.jei_information2 information2, pjei.jei_information3 information3, pjei.jei_information4 information4, pjei.jei_information5 information5, pjei.jei_information6 information6, pjei.jei_information7 information7, pjei.jei_information8 information8, pjei.jei_information9 information9, pjei.jei_information10 information10, pjei.jei_information11 information11, pjei.jei_information12 information12, pjei.jei_information13 information13, pjei.jei_information14 information14, pjei.jei_information15 information15, pjei.jei_information16 information16, pjei.jei_information17 information17, pjei.jei_information18 information18, pjei.jei_information19 information19, pjei.jei_information20 information20, pjei.jei_information21 information21, pjei.jei_information22 information22, pjei.jei_information23 information23, pjei.jei_information24 information24, pjei.jei_information25 information25, pjei.jei_information26 information26, pjei.jei_information27 information27, pjei.jei_information28 information28, pjei.jei_information29 information29, pjei.jei_information30 information30 from per_job_extra_info pjei where (:information_type is null or pjei.information_type=:information_type) ) eit E US Detail Level 11 &detail_from , ( select nvl2(paaf.position_id,'P'||paaf.position_id,'J'||paaf.job_id) incumbent_key, paaf.person_id, paaf.assignment_number, pastv.user_status assignment_status, (select pabvf.value from per_assignment_budget_values_f pabvf where paaf.assignment_id=pabvf.assignment_id and pabvf.unit='FTE' and :effective_date between pabvf.effective_start_date and pabvf.effective_end_date) assignment_fte, paaf.effective_start_date assignment_start_date from per_all_assignments_f paaf, per_assignment_status_types_v pastv where :effective_date between paaf.effective_start_date and paaf.effective_end_date and paaf.assignment_type in ('E','C') and paaf.assignment_status_type_id=pastv.assignment_status_type_id and pastv.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN','ACTIVE_CWK','SUSP_CWK_ASG') ) ia I US Detail Level 12 &detail_where and eit.incumbent_key in (x.incumbent_key,'J'||x.job_id) E US Detail Level 13 &detail_where and x.incumbent_key=ia.incumbent_key(+) I US Detail Level 14 90 :information_type LOV custom Y select distinct fdfcv.descriptive_flex_context_code id, fdfcv.descriptive_flex_context_name value, fdfcv.description from fnd_descr_flex_contexts_vl fdfcv where fdfcv.application_id=800 and fdfcv.descriptive_flexfield_name in ('Extra Position Info DDF','Extra Job Info DDF') and fdfcv.enabled_flag='Y' and fdfcv.descriptive_flex_context_code<>'Global Data Elements' and :$flex$.detail_level='E' order by fdfcv.descriptive_flex_context_name US Information Type Position or job extra information type for Detail Level Extra Information. Its segments are shown as columns. 44AE121F2FC8429EAAF6769168A4267F Vacant Positions Active positions with fewer incumbents than their maximum persons, by organization. ENGINATICS 1 BUSINESS_GROUP 1 15 EARLIEST_HIRE_DATE 8 ENTRY_GRADE 16 FILL_BY_DATE 10 FTE 12 HEADCOUNT 13 INCUMBENT_FTE 3 JOB 7 LOCATION 11 MAX_PERSONS 2 ORGANIZATION 2 4 POSITION 3 Y 6 POSITION_STATUS 5 POSITION_TYPE 17 SUPERVISOR_POSITION 14 VACANCIES 9 VALID_GRADES Vacant Only Y S Site