ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D471AB51A9E05362FB09050512Y 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 PER PEUSDGIPEDE US PER IPEDS Report - Degree Granting Institutes - Part E Imported from BI Publisher Application: Human Resources Source: IPEDS Report - Degree Granting Institutes - Part E Short Name: PEUSDGIPEDE DB package: PER_US_IPEDS_PKG BI Publisher :p_business_group_id :p_race_gender :p_report_date :p_report_type :p_tenured 1 -10 :p_business_group_id Char fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group Business Group ID 2 10 :p_report_date Date Y US Reporting Date Reporting Date 3 20 :p_tenured LOV Oracle HR_US_YES_NO Y select lookup_code id, meaning value, null description from hr_lookups where lookup_type='YES_NO' order by value,description Y US Tenured Is Org using Tenure Info