Select ps.planid, 
REPLACE(ps.plannam,',','') PLAN_NAME,
 er.erid,
REPLACE(er.conam,',','') ER_NAME,
src.srceid, src.typecd,
decode (src.typecd,'A','Employer',
   'B','Employer',
   'D','Deferral',
   'F','EE Post-tax',
   'H','ER Match',
   'I','ER Match',
   'J','QNEC',
   'K','QMAC' ) type,SRC.ROTHCONTRCD ROTH_SOURCE,
src.typedscrptxt DESCRIPTION, src.aliasnam ALIASNAM, src.newcontrcd "NEW_CONTR_CODE",
 src.erpayelemid SRC_PAY_ELEM,
case when src.erpayelemid='0' then 'Default'
   when src.erpayelemid='1' then er.erpayelem1typecd 
   when src.erpayelemid='2' then er.erpayelem2typecd 
   when src.erpayelemid='3' then er.erpayelem3typecd 
   when src.erpayelemid='4' then er.erpayelem4typecd 
   when src.erpayelemid='5' then er.erpayelem5typecd 
   when src.erpayelemid='6' then er.erpayelem6typecd 
   when src.erpayelemid='7' then er.erpayelem7typecd 
   when src.erpayelemid='8' then er.erpayelem8typecd 
   when src.erpayelemid='9' then er.erpayelem9typecd 
   when src.erpayelemid='10' then er.erpayelem10typecd 
   when src.erpayelemid='11' then er.erpayelem11typecd 
   when src.erpayelemid='12' then er.erpayelem12typecd 
   when src.erpayelemid='13' then er.erpayelem13typecd 
   when src.erpayelemid='14' then er.erpayelem14typecd 
   when src.erpayelemid='15' then er.erpayelem15typecd 
   when src.erpayelemid='16' then er.erpayelem16typecd 
   when src.erpayelemid='17' then er.erpayelem17typecd 
   when src.erpayelemid='18' then er.erpayelem18typecd 
   when src.erpayelemid='19' then er.erpayelem19typecd 
   when src.erpayelemid='20' then er.erpayelem20typecd 
   end ER_PAY_ELEM_TYPE,
case when (src.erpayelemid='0' and src.typecd in ('A','B')) then 'Default Employer'
   when (src.erpayelemid='0' and src.typecd='D') then 'Default Deferral'
   when (src.erpayelemid='0' and src.typecd='F') then 'Default EE Post-tax'
   when (src.erpayelemid='0' and src.typecd in ('H','I')) then 'Default ER Match'
   when (src.erpayelemid='0' and src.typecd='J') then 'Default QNEC'
   when (src.erpayelemid='0' and src.typecd='K') then 'Default QMAC'
   when src.erpayelemid='0' then '' 
   when src.erpayelemid='1' then er.erpayelem1nam 
   when src.erpayelemid='2' then er.erpayelem2nam 
   when src.erpayelemid='3' then er.erpayelem3nam 
   when src.erpayelemid='4' then er.erpayelem4nam 
   when src.erpayelemid='5' then er.erpayelem5nam 
   when src.erpayelemid='6' then er.erpayelem6nam 
   when src.erpayelemid='7' then er.erpayelem7nam 
   when src.erpayelemid='8' then er.erpayelem8nam 
   when src.erpayelemid='9' then er.erpayelem9nam 
   when src.erpayelemid='10' then er.erpayelem10nam 
   when src.erpayelemid='11' then er.erpayelem11nam 
   when src.erpayelemid='12' then er.erpayelem12nam 
   when src.erpayelemid='13' then er.erpayelem13nam 
   when src.erpayelemid='14' then er.erpayelem14nam 
   when src.erpayelemid='15' then er.erpayelem15nam 
   when src.erpayelemid='16' then er.erpayelem16nam 
   when src.erpayelemid='17' then er.erpayelem17nam 
   when src.erpayelemid='18' then er.erpayelem18nam 
   when src.erpayelemid='19' then er.erpayelem19nam 
   when src.erpayelemid='20' then er.erpayelem20nam 
   end ER_PAY_ELEM_NAME,
SRC.VRUWEBSRCECD,SRC.VOICENUM, VNUM.VOICEDSCRP
from planstat ps
join  (select * from employer) er on er.erid=ps.erid
join (select planid, max(yrenddate) max from plandyn WHERE YRENDDATE>SYSDATE GROUP BY PLANID) pd on pd.planid=ps.planid
join (select * from plansrce) src on src.plaNid=ps.planid and src.yrenddate=pd.max
LEFT OUTER JOIN (SELECT * FROM VOICENUM) VNUM ON VNUM.VOICENUM=SRC.VOICENUM
--join (select * from plangrpdet) pg on pg.planid =ps.planid
--join (select * from plangrpmstr) pm on pm.plangrpid=pg.plangrpid
where src.newcontrcd='Y'
and src.typecd not in ('G','S','L''O')
and ps.planstatcd='A'
--and pm.plangrpuserid='YEDC2'