SELECT distinct decode(hl.lookup_code,'21',hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL','PSTIS')
,'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
,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 = 'US_IPEDS_JOB_CATEGORIES'
AND hl.lookup_code NOT IN ('12','22', '23','24')
AND hl.lookup_code = purt.value30(+)
AND hl.enabled_flag = 'Y'
AND purt.tax_unit_id(+) = userenv('sessionid')
AND purt.attribute1(+) = 'DGIPEDD'
GROUP BY hl.lookup_code
,hl.meaning
ORDER BY hl.lookup_code |