CREATE OR REPLACE VIEW SD_HOSPITAL.VW_SS_YWJJ00
 (
 YJDJH0, ZYHGHH, ZYGHID, KDYS00, KDYSXM, KDKSBH, KDRQ00, KDSJ00, LRXM00, 
XMZT00, ZJE000, CXDJH0, YJKSBH, BRID00, MZZYBZ, ZLXMJC, JZBZ00, BWMC00, 
ZDMC00, SL0000, ZXSL00, SFDJH0, ZLXMID, DCBZ00, BRXM00, XB0000, YZCFID, 
KDKSMC, SFXMID, SFXS00, SFCS00, JJJE00, GFJE00, JZJE00, ZFJE00, YBBZ00, 
SFXX00, SFSX00, ID0000, SFXMMC, GJBM00, LSCL00, BH0000, SFLB00, TXM000, 
XKZH00, SCPH00, TSBZBH, TSBZMC, ZXDCBZ, WZDWM0, GZJLH0, ZTM000, CTM000, 
BSM000, HCID00, HCDJ00, HCPH00, HCGYS0, HCYXQ0, HCZCZH, QP0000, CXSL00, 
ZTMC00, YSZID0, ZFFSBZ, YJLRBZ, BKEH83, GJBZMC, YEXM00, GCPBZ0, SSYSXM, 
GYHSXM, BRZJLX, BRZJBH, BRXB00, NL0000, CH0000, SSMC00

 )
 AS 
select a.YJDJH0,ZYHGHH,ZYGHID,KDYS00,KDYSXM,KDKSBH,KDRQ00,KDSJ00,LRXM00,XMZT00,nvl(b.JJJE00*b.SFCS00*b.SL0000,a.ZJE000) ZJE000,CXDJH0,YJKSBH,
       brid00,mzzybz,zlxmjc,jzbz00,bwmc00,zdmc00,a.sl0000,zxsl00,SFDJH0,ZLXMID,DCBZ00,BRXM00,XB0000,
       YZCFID,
       (select t.BMMC00 from BM_BMBM00 t where a.KDKSBH=t.BMBH00)KDKSMC,
       b.SFXMID,b.SL0000 SFXS00,SFCS00,JJJE00,GFJE00,JZJE00,ZFJE00,YBBZ00,b.SFXX00,b.SFSX00,b.ID0000,
       c.XMMC00 SFXMMC,b.GJBM00,c.LSCL00,c.BH0000,b.SFLB00,b.TXM000,b.XKZH00,B.SCPH00,a.BH0000,a.MC0000,
       nvl(ZXDCBZ,'0') ZXDCBZ,b.WZDWM0,b.GZJLH0,b.ZTM000,b.CTM000,b.BSM000,b.HCID00,
       b.HCDJ00,b.HCPH00,b.HCGYS0,b.HCYXQ0,b.HCZCZH,nvl(b.QP0000,'N') QP0000,nvl(a.CXSL00,0) CXSL00,
       decode(sign(a.SL0000),-1, decode(a.XMZT00,'1','退费申请','2','已冲销','3','已冲销','4','报告已出','未知'), decode(a.XMZT00,'1','计价','2','收费','3','执行','4','报告已出','未知')) ZTMC00
       ,a.YSZID0,nvl(b.ZFFSBZ,'N'),a.YJLRBZ,c.BKEH83,c.GJBZMC,a.YEXM00,
       (select nvl(GCPBZ0,'N') from ZY_BRXXB0 where ZYID00=a.ZYGHID and rownum=1 ) GCPBZ0,
       (select w.SSYSXM from SS_YW0000 w,SS_SSYJD0 y where  w.SSDH00=y.SSDH00 and y.YJDJH0=a.YJDJH0 and rownum=1) SSYSXM,
       (select w.GYHSXM from SS_YW0000 w,SS_SSYJD0 y where  w.SSDH00=y.SSDH00 and y.YJDJH0=a.YJDJH0 and rownum=1) GYHSXM,
       (select BRZJLX from BM_BRXXB0 where BRID00=a.BRID00 ) BRZJLX,
       (select BRZJBH from BM_BRXXB0 where BRID00=a.BRID00 ) BRZJBH,
       (a.XB0000) BRXB00,
       sf_bq_csrqtonl(a.CSRQ00) NL0000,
       (select RYCWH0 from ZY_BRXXB0 where ZYID00=a.ZYGHID and rownum=1) CH0000,
       (select to_char(wm_concat(w.SSXMJC)) from SS_YWMX00 w,SS_SSYJD0 y where  w.SSDH00=y.SSDH00 and y.YJDJH0=a.YJDJH0) SSMC00
from YJ_YW0000 a,YJ_YWJJ00 b,BM_YYSFXM c
where a.YJDJH0=b.YJDJH0 and b.SFXMID=c.SFXMID and a.XMZT00 not in ('4','5','6')
/