PER IPEDS Report - Degree Granting Institutes - Part E

Description

Imported from BI Publisher
Application: Human Resources
Source: IPEDS Report - Degree Granting Institutes - Part E
Short Name: PEUSDGIPEDE
DB package: PER_US_IPEDS_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

select a.tenure_code
      , a.tenure
      , a.lookup_code  iped_code
      , ipeds_job_category
      , b.attribute3	org_med_type		
      ,NVL(b.total,0) total			
from
(	 SELECT distinct decode(hl.lookup_code,'21',hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL','PST')||' - '||hl.meaning
                                        ,'22',hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL','PST')||' - '||hl.meaning
                                        ,'23',hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL','PST')||' - '||hl.meaning
                                        ,'24',hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL','PST')||' - '||hl.meaning                                                                                
                                        ,'25',hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL','PST')||' - '||hl.meaning
                                        ,'26',hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL','PST')||' - '||hl.meaning
                                        ,hl.meaning)  ipeds_job_category
                 ,hl.lookup_code 
                 ,ht.lookup_code tenure_code
                 ,decode(:P_TENURED,'Y',ht.meaning,trim(replace(ht.meaning,hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL','NTT'),''))) tenure                            
  FROM 
      hr_lookups hl	 
      ,hr_lookups ht			 
  WHERE hl.lookup_type = 'US_IPEDS_JOB_CATEGORIES'
  AND hl.lookup_code NOT IN ('12','36','37','38','39','40')
  AND hl.enabled_flag = 'Y'
  AND ht.lookup_type = 'PER_US_TENURE_REPORTING'	
  AND ht.lookup_code > decode(:P_TENURED,'Y',0,2)   
)a,
(
select purt.value1, purt.value30,purt.attribute3, sum(purt.value21+ purt.value22) total
from  pay_us_rpt_totals purt
where purt.tax_unit_id = userenv('sessionid')
and attribute1 = 'DGIPEDE'
and attribute3 = 'NON-MED'
group by purt.value1, purt.value30,purt.attribute3
)b
where a.tenure_code = b.value1(+)
and a.lookup_code = b.value30(+)
order by 1 asc, 3 asc