CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YJ_YW0000_YBCJ00
 (
 YJDJH0, BRID00, ZYGHID, ZYHGHH, MZZYBZ, BRXM00, XBMC00, CSRQ00, YEXM00,
ZLXMID, ZLXMJC, SL0000, DW0000, YJKSBH, KDYSXM, BRLX00, KDKSBH, KDRQ00, 
KDSJ00, JZBZ00, XMZT00, SFDJH0, ZJE000, BZ0000, KDBQ00, CH0000, NL0000, 
BBID00, TXM000, CYRXM0, BBCYSJ, SQKSMC, BBMC00, BBCYRQ, TXM001, JGZT00, 
SFTJ00, SQSJ00, QDSJ00, BBFSRQ, BBFSSJ, FSRXM0

 )
 AS 
select a.YJDJH0, a.BRID00, a.ZYGHID, a.ZYHGHH, a.MZZYBZ, a.BRXM00, a.XB0000, a.CSRQ00,
       a.YEXM00, a.ZLXMID, a.ZLXMJC, a.SL0000, a.DW0000, a.YJKSBH, a.KDYSXM,decode(a.MZZYBZ,'1','住院','0','门诊') BRLX00,
       a.KDKSBH, a.KDRQ00, a.KDSJ00, decode(a.JZBZ00,'Y','是','否'), a.XMZT00, a.SFDJH0, a.ZJE000, a.BZ0000,
       a.KDBQ00, a.CH0000, a.NL0000, trim(a.BBID00), a.TXM000,
      (select ZWXM00 from BM_YGBM00 where b.BBCJR0=YGBH00) CYRXM0,
       to_char(to_date(b.BBCYRQ||b.BBCYSJ,'YYYYMMDDHH24:MI:SS'),'MM.DD HH24:MI:SS') BBCYRQ,
      (select BMMC00 from BM_BMBM00 c where c.BMBH00=a.KDKSBH)SQKSMC,
      (select BBMC00 from BM_BBZD00 where BBID00=a.BBID00),
      b.BBCYRQ,d.TXM000 TXM001,b.JGZT00, b.SFTJ00,
      to_char(to_date(a.KDRQ00||a.KDSJ00,'YYYYMMDDHH24:MI:SS'),'MM.DD HH24:MI:SS') SQSJ00,
      substr(SF_JY_GET_QDSJ01(A.YJDJH0,A.ZLXMID),1,20) QDSJ00
      ,b.BBFSRQ,b.BBFSSJ,b.FSRXM0
from  YJ_YW0000 a,YJ_JYBBCY b,BM_ZLZD00 c,jy_yw0000 d
where a.YJDJH0=b.YJDJH0(+) and a.ZLXMID=c.ZLXMID and c.LBBH00='4' and a.yjdjh0=d.yjdjh0
/
CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YJ_YW0000_YBCJQR
 (
 YJDJH0, BRID00, ZYGHID, ZYHGHH, MZZYBZ, BRXM00, XBMC00, CSRQ00, YEXM00, 
ZLXMID, ZLXMJC, SL0000, DW0000, YJKSBH, KDYSXM, BRLX00, KDKSBH, KDRQ00,
KDSJ00, JZBZ00, XMZT00, SFDJH0, ZJE000, BZ0000, KDBQ00, CH0000, NL0000, 
BBID00, TXM000, CYRXM0, BBCYSJ, SQKSMC, BBMC00, BBCYRQ, TXM001, JGZT00, 
SFTJ00, SQSJ00, QDSJ00, BBFSRQ, BBFSSJ, FSRXM0

 )
 AS 
select a.YJDJH0, a.BRID00, a.ZYGHID, a.ZYHGHH, a.MZZYBZ, a.BRXM00, a.XB0000, a.CSRQ00,
       a.YEXM00, a.ZLXMID, a.ZLXMJC, a.SL0000, a.DW0000, a.YJKSBH, a.KDYSXM,decode(a.MZZYBZ,'1','住院','0','门诊') BRLX00,
       a.KDKSBH, a.KDRQ00, a.KDSJ00, decode(a.JZBZ00,'Y','是','否'), a.XMZT00, a.SFDJH0, a.ZJE000, a.BZ0000,
       a.KDBQ00, a.CH0000, a.NL0000, trim(a.BBID00), a.TXM000,
      (select ZWXM00 from BM_YGBM00 where b.BBCJR0=YGBH00) CYRXM0,
       to_char(to_date(b.BBCYRQ||b.BBCYSJ,'YYYYMMDDHH24:MI:SS'),'MM.DD HH24:MI:SS') BBCYRQ,
      (select BMMC00 from BM_BMBM00 c where c.BMBH00=a.KDKSBH)SQKSMC,
      (select BBMC00 from BM_BBZD00 c where c.BBID00=a.BBID00),
      b.BBCYRQ,
      --nvl(b.TXM000,a.TXM000) TXM001,
      d.txm000 txm001,
      b.JGZT00, b.SFTJ00,
      to_char(to_date(a.KDRQ00||a.KDSJ00,'YYYYMMDDHH24:MI:SS'),'MM.DD HH24:MI:SS') SQSJ00,
      substr(SF_JY_GET_QDSJ01(A.YJDJH0,A.ZLXMID),1,20) QDSJ00,b.BBFSRQ,b.BBFSSJ,b.FSRXM0
from  yj_YW0000 a,YJ_JYBBCY b,BM_ZLZD00 c,jy_yw0000 d
where ((a.MZZYBZ='1' AND XMZT00<>'3') OR a.MZZYBZ='0') and a.XMZT00<>'6' and a.YJDJH0=b.YJDJH0(+) and a.ZLXMID=c.ZLXMID and c.LBBH00='4' and a.yjdjh0=d.yjdjh0
      and not exists(select 1 from yj_yw0000 where cxdjh0=a.yjdjh0) and a.SL0000>0
/