ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D471E951A9E05362FB09050512 SELECT pqh_inst_type_pkg.get_inst_type(paf.organization_id) Org_Code1, hl.lookup_code Job_Code1, --pqh_inst_type_pkg.get_inst_type(paf.organization_id) Org_Code, --hl.lookup_code Job_Code, count(decode(hl.lookup_code,'1',DECODE(ppe.pei_information1,'01',1,null),'2',DECODE(ppe.pei_information1,'01',1,null),'3',DECODE(ppe.pei_information1,'01',1,null),null)) tenured1, count(decode(hl.lookup_code,'1',DECODE(ppe.pei_information1,'02',1,'04',1,null),'2',DECODE(ppe.pei_information1,'02',1,'04',1,null),'3',DECODE(ppe.pei_information1,'02',1,'04',1,null),null)) ontenure1, count(decode(hl.lookup_code,'1',null,'2',null,'3',null,peo.person_id)) nonFaculty1 --count(decode(hl.lookup_code,'1',DECODE(ppe.pei_information1,'01',1,null),'2',DECODE(ppe.pei_information1,'01',1,null),'3',DECODE(ppe.pei_information1,'01',1,null),null)) tenured, --count(decode(hl.lookup_code,'1',DECODE(ppe.pei_information1,'02',1,'04',1,null),'2',DECODE(ppe.pei_information1,'02',1,'04',1,null),'3',DECODE(ppe.pei_information1,'02',1,'04',1,null),null)) ontenure, --count(decode(hl.lookup_code,'1',null,'2',null,'3',null,peo.person_id)) nonFaculty from hr_lookups hl, per_jobs job , per_all_assignments_f paf, per_people_extra_info ppe, per_all_people_f peo, per_pay_proposals ppp, per_pay_bases ppb WHERE paf.person_id = ppe.person_id AND peo.person_id = paf.person_id AND peo.current_employee_flag = 'Y' AND paf.job_id = job.job_id AND ppe.information_type in 'PQH_TENURE_STATUS' AND :p_report_date BETWEEN paf.effective_start_date AND paf.effective_end_date AND :p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND fnd_date.canonical_to_date(ppe.pei_information2) <= :p_report_date AND hl.lookup_code = job.job_information8 AND job.job_information8 in ('1', '2', '3') AND job.job_information8 IS NOT NULL AND job.job_information_category = 'US' AND hl.lookup_type like 'US_IPEDS_JOB_CATEGORIES' AND paf.primary_flag = 'Y' AND paf.assignment_type = 'E' AND pqh_employment_category.identify_empl_category(paf.employment_category,:cp_fr,:cp_ft,:cp_pr, :cp_pt) = 'FR' AND paf.pay_basis_id = ppb.pay_basis_id AND paf.assignment_id = ppp.assignment_id AND ppp.change_date = (SELECT MAX(change_date) FROM per_pay_proposals pro WHERE ppp.assignment_id = pro.assignment_id AND pro.change_date <= :p_report_date AND pro.approved = 'Y') AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0 AND paf.organization_id in ( select organization_id from hr_all_organization_units where business_group_id = :p_business_group_id) and hl.lookup_code=:JobCode and pqh_inst_type_pkg.get_inst_type ( paf.organization_id )=:OrgCode group by pqh_inst_type_pkg.get_inst_type(paf.organization_id), hl.lookup_code PQH PQIPED1_XML US PQH IPEDS Report - Employees By Assigned Position (EAP) - Part A(obsoleted) Imported from BI Publisher Application: Public Sector HR Source: IPEDS Report - Employees By Assigned Position (EAP) - Part A (XML) (obsoleted) Short Name: PQIPED1_XML DB package: PQH_PQIPED1_XMLP_PKG BI Publisher :cp_fr :cp_ft :cp_pr :cp_pt :jobcode :line_num :orgcode :p_business_group_id :p_conc_request_id :p_report_date :p_report_date_t 1 -20 :p_business_group_id Char fnd_profile.value('PER_BUSINESS_GROUP_ID') US Business Group ID Business Group ID 2 10 :p_report_date Date Y US Reporting Date Reporting Date