PQH IPEDS Report - Salary and Fringe Benefits Survey (SA) - Part A(obsoleted)

Description
Categories: BI Publisher
Columns: Rankcode, Gencode, Tot Number, Salary Range, Avg Sal ...
Application: Public Sector HR
Source: IPEDS Report - Salary and Fringe Benefits Survey (SA) - Part A (XML) (obsoleted)
Short Name: PQIPED3_XML
DB package: PQH_PQIPED3_XMLP_PKG
select 
hla.lookup_code RankCode,
peo.sex GenCode,
nvl(count(peo.person_id),0) tot_number,
round(sum(NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)) Salary_range,
round(nvl(avg(NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor),0)) avg_sal
from hr_lookups                    hla,
        per_all_people_f            peo,
        per_all_assignments_f   ass,
        per_contracts_f             pco,
        per_pay_proposals         ppp,
        per_pay_bases	             ppb,
        per_people_extra_info    ppea,
        per_jobs                        job,
        hr_lookups                     hl,
        per_shared_types          pst,
        per_shared_types          pst1
WHERE  	peo.person_id              	= ass.person_id
AND   peo.current_employee_flag = 'Y'
AND   ass.assignment_type  = 'E'
AND     peo.person_id               = pco.person_id
AND     peo.person_id              	= ppea.person_id
AND	 :p_report_date 
BETWEEN ass.effective_start_date AND ass.effective_end_date 
AND :p_report_date 
BETWEEN peo.effective_start_date AND peo.effective_end_date
AND	 :p_report_date 
BETWEEN pco.effective_start_date AND pco.effective_end_date
AND	:p_report_date 
BETWEEN fnd_date.canonical_to_date (ppea.pei_information2)   
                  AND  fnd_date.canonical_to_date(NVL(ppea.pei_information3, '4712/12/31 00:00:00'))
AND	ass.pay_basis_id		= ppb.pay_basis_id
AND	ass.assignment_id		= ppp.assignment_id
AND	hla.lookup_code			= ppea.pei_information1
AND	ppea.pei_information1		IS NOT NULL 
AND ppea.pei_information_category   	= 'PQH_ACADEMIC_RANK'
AND	hla.lookup_type			= 'PQH_ACADEMIC_RANK'
AND hla.lookup_code != '01'
AND  ass.primary_flag     		= 'Y'
and pco.type = 'FULL_TIME'
and pco.status = pst.system_type_cd
and pst.lookup_type = 'CONTRACT_STATUS'
and pst1.system_type_cd(+) = pst.system_type_cd
and pst1.lookup_type(+) = pst.lookup_type
and pst.business_group_id is null
and pst1.business_group_id(+) = :p_business_group_id
and nvl(pst1.information1,pst.information1) = 'Y'
AND pqh_employment_category.get_duration_in_months(pco.duration,                                pco.duration_units,                                                                      pco.business_group_id,                                                                                :p_report_date) < 9
AND job.job_information8 is not null
AND job.job_information8 in ('1', '2', '3')
AND	hl.lookup_code		= job.job_information8
AND pqh_employment_category.identify_empl_category(ass.employment_category,:cp_fr,:cp_ft,:cp_pr,:cp_pt)   IN ('FR')
AND	hl.lookup_type		= 'US_IPEDS_JOB_CATEGORIES'
AND   job.job_information_category   	= 'US'
AND   ass.job_id                  	=  job.job_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     ass.organization_id IN (
	SELECT	org.organization_id 
	FROM	hr_all_organization_units org
	WHERE	business_group_id = :p_business_group_id
                   AND           pqh_inst_type_pkg.get_inst_type(org.organization_id) = 'NON-MED' )
				   and hla.lookup_code=:Rank 
 and peo.sex=:Gender
group by  hla.lookup_code,peo.sex
union
select 
'01' RankCode,
peo.sex GenCode,
count(peo.person_id) tot_number,
round(sum(NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)) Salary_range,
round(avg(NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)) avg_sal
from per_all_people_f              peo,
        per_all_assignments_f    ass,
        per_contracts_f              pco,
        per_pay_proposals          ppp,
        per_pay_bases	              ppb,
        per_jobs                          job,
        hr_lookups                      hl,
        per_shared_types           pst,
        per_shared_types           pst1
WHERE  	peo.person_id              	= ass.person_id
AND   peo.current_employee_flag = 'Y'
AND   ass.assignment_type ='E'
AND     peo.person_id               = pco.person_id
AND	:p_report_date 
BETWEEN ass.effective_start_date AND ass.effective_end_date
AND	:p_report_date 
BETWEEN peo.effective_start_date AND peo.effective_end_date
AND	:p_report_date 
BETWEEN pco.effective_start_date AND pco.effective_end_date
AND	ass.pay_basis_id		= ppb.pay_basis_id
AND	ass.assignment_id		= ppp.assignment_id
AND  ass.primary_flag     		= 'Y'
AND not exists (select pea.person_id from per_people_extra_info pea
                where pea.person_id = peo.person_id
                and pea.information_type in 'PQH_ACADEMIC_RANK'
                and pea.pei_information1 in ('02','03','04','05','06')
                and :p_report_date 
				between fnd_date.canonical_to_date(pea.pei_information2)  
                and fnd_date.canonical_to_date(NVL(pea.pei_information3, '4712/12/31 00:00:00')))
and pco.type = 'FULL_TIME'
and pco.status = pst.system_type_cd
and pst.lookup_type = 'CONTRACT_STATUS'
and pst1.system_type_cd(+) = pst.system_type_cd
and pst1.lookup_type(+) = pst.lookup_type
and pst.business_group_id is null
and pst1.business_group_id(+) = :p_business_group_id
and nvl(pst1.information1,pst.information1) = 'Y'
AND pqh_employment_category.get_duration_in_months(pco.duration,                              pco.duration_units,                                                                             pco.business_group_id,                                                                               :p_report_date) < 9
AND job.job_information8 is not null
AND job.job_information8 in ('1', '2', '3')
AND	hl.lookup_code		= job.job_information8
AND pqh_employment_category.identify_empl_category(ass.employment_category,:cp_fr,:cp_ft,:cp_pr,:cp_pt)   IN ('FR')
AND	hl.lookup_type		= 'US_IPEDS_JOB_CATEGORIES'
AND   job.job_information_category   	= 'US'
AND   ass.job_id                  	=  job.job_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     ass.organization_id IN (
	SELECT	org.organization_id
	FROM	hr_all_organization_units org
	WHERE	business_group_id = :p_business_group_id
                   AND           pqh_inst_type_pkg.get_inst_type(org.organization_id) = 'NON-MED') 
 and '01'=:Rank
 and peo.sex=:Gender
group by peo.sex
Parameter Name SQL text Validation
Reporting Date
 
Date
Business Group ID