SELECT distinct decode(hl.lookup_code
,'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
,'27',hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL','LOT')
,hl.meaning) ipeds_job_category
,hl.lookup_code ipeds_job_code
,trim(to_char(:P_REPORT_DATE,'Month'))||' '||to_char(:P_REPORT_DATE,'DD,YYYY') Report_Date
,hl.lookup_code
,SUM(value2) NRMen
,SUM(value3) NRWmen
,SUM(value4) BnHMen
,SUM(value5) BnHWmen
,SUM(value6) Am_AlMen
,SUM(value7) Am_AlWmen
,SUM(value8) AMen
,SUM(value9) AWmen
,SUM(value10) NH_OPMen
,SUM(value11) NH_OPWmen
,SUM(value12) HMen
,SUM(value13) HWmen
,SUM(value14) WnHMen
,SUM(value15) WnHWmen
,SUM(value16) TmrMen
,SUM(value17) TmrWmen
,SUM(value18) URMen
,SUM(value19) URWmen
,SUM(value20) TotMen
,SUM(value21) TotWmen
,SUM(value20+value21) Total
FROM pay_us_rpt_totals purt
,hr_lookups hl
WHERE hl.lookup_type = 'US_IPEDS_JOB_CATEGORIES'
AND hl.enabled_flag = 'Y'
AND hl.lookup_code NOT IN ('21','22','23','24','28','29','30','12')
AND hl.lookup_code = purt.value1(+)
AND purt.tax_unit_id(+) = userenv('sessionid')
AND purt.attribute1(+) = 'DGIPEDH'
AND purt.attribute2(+) = 'SECTION2'
GROUP BY hl.meaning
,hl.lookup_code
ORDER BY hl.lookup_code |