PER IPEDS Report - Degree Granting Institutes - Part A

  SELECT distinct decode( hl.lookup_code, '01', hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL','NAC'), hl.meaning) academic_rank 
                 ,hl.lookup_code
                 ,SUM(value3) NRMen        
                 ,SUM(value4) NRWmen       
                 ,SUM(value5) BnHMen       
                 ,SUM(value6) BnHWmen      
                 ,SUM(value7) Am_AlMen     
                 ,SUM(value8) Am_AlWmen    
                 ,SUM(value9) AMen         
                 ,SUM(value10) AWmen        
                 ,SUM(value11) NH_OPMen     
                 ,SUM(value12) NH_OPWmen    
                 ,SUM(value13) HMen         
                 ,SUM(value14) HWmen        
                 ,SUM(value15) WnHMen       
                 ,SUM(value16) WnHWmen      
                 ,SUM(value17) TmrMen       
                 ,SUM(value18) TmrWmen      
                 ,SUM(value19) URMen        
                 ,SUM(value20) URWmen  
                 ,SUM(value21) TotMen        
                 ,SUM(value22) TotWmen                                    
  FROM pay_us_rpt_totals purt
      ,hr_lookups hl	  
  WHERE hl.lookup_type = 'PQH_ACADEMIC_RANK'
  AND hl.enabled_flag = 'Y'  
  AND purt.value1(+) = :tenure_code  
  AND hl.lookup_code = purt.value2(+) 
  AND purt.tax_unit_id(+) = userenv('sessionid')
  AND purt.attribute1(+) = 'DGIPEDA'
  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