PER IPEDS Report - Degree Granting Institutes - Part G

  SELECT distinct decode( hl.lookup_code, '01', hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL','NAC'), hl.meaning) academic_rank 
                 ,hl.lookup_code academic_code
                 ,SUM(value2) mnth9_men   
                 ,SUM(value3) mnth10_men                         
                 ,SUM(value4) mnth11_men       
                 ,SUM(value5) mnth12_men        
                 ,SUM(value6) mnth9_wmen      
                 ,SUM(value7) mnth10_wmen     
                 ,SUM(value8) mnth11_wmen  
                 ,SUM(value9) mnth12_wmen         
                 ,SUM(value10) tot_men        
                 ,SUM(value11) tot_wmen     
                 ,SUM(value12) men_sal_outlay    
                 ,SUM(value13) wmen_sal_outlay                 
  FROM pay_us_rpt_totals purt
      ,hr_lookups hl	  
  WHERE hl.lookup_type = 'PQH_ACADEMIC_RANK'
  AND hl.enabled_flag = 'Y'  
  AND hl.lookup_code = purt.value1(+) 
  AND purt.tax_unit_id(+) = userenv('sessionid')
  AND purt.attribute1(+) = 'DGIPEDG'
  AND purt.attribute2(+) = 'PART1'  
  GROUP BY decode( hl.lookup_code, '01', hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL','NAC') , hl.meaning)  
          ,hl.lookup_code  
  ORDER BY hl.lookup_code DESC