ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D471DD51A9E05362FB09050512Y select paf.supervisor_id, ppf.last_name||', '||ppf.first_name person_name, eiar.pei_information1 Rank_id, paf.job_id , eits.pei_information1 Tenure_Status_cd, fnd_date.canonical_to_date(decode(eits.pei_information1,'02', nvl(eits.pei_information4,eits.pei_information3), eits.pei_information2)) Tenure_Status_date, PQH_PQHUSEPR_XMLP_PKG.cf_tenure_statusformula(eits.pei_information1, eiar.pei_information1) CF_tenure_status, PQH_PQHUSEPR_XMLP_PKG.cf_academic_rankformula(eiar.pei_information1) CF_Academic_Rank, PQH_PQHUSEPR_XMLP_PKG.cf_job_nameformula(paf.job_id) CF_job_name from per_all_people_f ppf, per_assignments_f paf, per_people_extra_info eiar, per_people_extra_info eits where ppf.person_id = paf.person_id and sysdate between ppf.effective_start_date and ppf.effective_end_date and sysdate between paf.effective_start_date and paf.effective_end_date and sysdate between fnd_date.canonical_to_date(eiar.pei_information2) and nvl(fnd_date.canonical_to_date(eiar.pei_information3),sysdate) and ppf.business_group_id= :p_business_group_id and paf.business_group_id= :p_business_group_id and paf.primary_flag = 'Y' and eiar.information_type = 'PQH_ACADEMIC_RANK' and eits.information_type = 'PQH_TENURE_STATUS' and eits.pei_information1 <> '03' and eiar.person_id = ppf.person_id and eits.person_id = ppf.person_id and paf.supervisor_id=:manager_id order by eits.pei_information2 PQH PQHUSEPR_XML US PQH Existing Profile Imported from BI Publisher Description: Tenure: Existing Profile Report Application: Public Sector HR Source: Tenure: Existing Profile Report (XML) Short Name: PQHUSEPR_XML DB package: PQH_PQHUSEPR_XMLP_PKG BI Publisher :cp_business_group_name :cp_tendenied_cnt :cp_teneligible_cnt :cp_tentrack_cnt :cp_tenured_cnt :manager_id :p_and_clause :p_business_group_id :p_conc_request_id :p_direct_or_all :p_level :p_supervisor_id 1 -20 :p_business_group_id Char fnd_profile.value('PER_BUSINESS_GROUP_ID') US Business Group Id Business Group Id 2 10 :p_supervisor_id LOV Oracle PQH_US_SUPERVISOR Y select ppf.person_id id, ppf.full_name value, null description from per_people_f ppf,per_person_types ppt where ppf.business_group_id+0= fnd_global.per_business_group_id and ppt.person_type_id=ppf.person_type_id+0 and ppt.system_person_type in ('EMP','EMP_APL','EX_EMP','EX_EMP_APL') and ppf.effective_start_date= (select max(effective_start_date) from per_people_f ppf1 where ppf1.person_id=ppf.person_id) and ppf.person_id in (select person_id from per_all_assignments_f where manager_flag='Y' and sysdate between effective_start_date and effective_end_date) order by ppf.last_name,ppf.first_name Y US Supervisor Name Supervisor Name 3 20 :p_direct_or_all LOV Oracle PQH_DIRECT_HIERARCHY Y select lookup_code id, meaning value, null description from hr_lookups where lookup_type='PQH_DIRECT_HIERARCHY' order by lookup_code D Y US Direct/Hierarchy Direct/Hierarchical