PQH IPEDS Report - Employees By Assigned Position (EAP) - Part B(obsoleted)
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Public Sector HR
Source: IPEDS Report - Employees By Assigned Position (EAP) - Part B (XML) (obsoleted)
Short Name: PQIPED2_XML
DB package: PQH_PQIPED2_XMLP_PKG
Application: Public Sector HR
Source: IPEDS Report - Employees By Assigned Position (EAP) - Part B (XML) (obsoleted)
Short Name: PQIPED2_XML
DB package: PQH_PQIPED2_XMLP_PKG
Run
PQH IPEDS Report - Employees By Assigned Position (EAP) - Part B(obsoleted) and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT pqh_inst_type_pkg.get_inst_type(paf.organization_id) Org_Code1, hl.lookup_code Job_Code1, 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 --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)) 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) = 'PR' 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 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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Reporting Date |
|
Date |