create or replace view vw_yj_ywcx_jzsj
(mzzybz, bmmc00, zyhghh, yjdjh0, zlxmjc, brxm00, xb0000, sfdjh0, kdysxm, sl0000, zxsl00, zdmc00, lrxm00, zxrxm0, dw0000, srrqsj, zxrqsj, zje000, xmzt00, cxdjh0, bz0000, jzrq00, yjksbh, sfyxm0, sfbcx0, zxrq00, zlxmid, kdys00, kdrq00, txm000, brid00)
as
select 
  '门诊' MZZYBZ, (select bmmc00 from bm_bmbm00 where bmbh00=y.kdksbh) BMMC00, Y.ZYHGHH, Y.YJDJH0, Y.ZLXMJC, Y.BRXM00, Y.XB0000, Y.SFDJH0, Y.KDYSXM,
  Y.SL0000, Y.ZXSL00, Y.ZDMC00, LRXM00, Y.ZXRXM0, Y.DW0000, Y.KDRQ00||Y.KDSJ00 SRRQSJ, Y.ZXRQ00||Y.ZXSJ00 ZXRQSJ, Y.ZJE000,
  DECODE(Y.XMZT00,'0','申请','1','计价','2','已收费','3','已执行','4','已报告','5','疑退','6','作废') XMZT00,
  Y.CXDJH0, Y.BZ0000, C.CZRQ00, Y.YJKSBH, C.CZYXM0,
  (select count(1) from YJ_YW0000 M where M.ZYGHID=Y.ZYGHID AND M.CXDJH0=Y.YJDJH0 AND M.XMZT00<>'6'),
  Y.ZXRQ00,Y.ZLXMID,Y.KDYS00, Y.KDRQ00, Y.TXM000,Y.BRID00
from YJ_YW0000 Y, BM_BMBM00 B, SF_BRFY00 C 
  where decode(nvl(Y.KDBQ00, 0), 0, KDKSBH, KDBQ00)=B.BMBH00(+)
    AND Y.SFDJH0 = C.DJH000 AND Y.MZZYBZ='0'
union all
select 
  '住院' MZZYBZ, (select bmmc00 from bm_bmbm00 where bmbh00=y.kdksbh) BMMC00, Y.ZYHGHH, Y.YJDJH0, Y.ZLXMJC, Y.BRXM00, Y.XB0000, Y.SFDJH0, Y.KDYSXM,
  Y.SL0000, Y.ZXSL00, Y.ZDMC00, LRXM00, Y.ZXRXM0, Y.DW0000, Y.KDRQ00||Y.KDSJ00 SRRQSJ, Y.ZXRQ00||Y.ZXSJ00 ZXRQSJ, Y.ZJE000,
  DECODE(Y.XMZT00,'0','申请','1','计价','2','已收费','3','已执行','4','已报告','5','疑退','6','作废') XMZT00,
  Y.CXDJH0, Y.BZ0000, C.CZRQ00, Y.YJKSBH, C.CZYXM0,
  (select count(1) from YJ_YW0000 M where M.ZYGHID=Y.ZYGHID AND M.CXDJH0=Y.YJDJH0 AND M.XMZT00<>'6'),
  Y.ZXRQ00,Y.ZLXMID,Y.KDYS00, Y.KDRQ00, Y.TXM000,Y.BRID00
from YJ_YW0000 Y, BM_BMBM00 B, ZY_BRFY00 C 
  where decode(nvl(Y.KDBQ00,0),0,KDKSBH,KDBQ00)=B.BMBH00(+)
    AND Y.SFDJH0=C.DJH000 AND Y.MZZYBZ='1';