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 |
