create or replace view vw_zy_wcybr0 as select a.ZYID00, a.BRID00, a.ZYH000, a.XM0000, a.XB0000, a.RYCWH0, a.DQBQ00, (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQBQ00) as DQBQMC, --当前病区名称 a.DQKS00, (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQKS00) as DQKSMC, --当前科室名称 a.BRZT00, decode(substr(a.BRZT00,1,1),'0','预约病人','1','入院登记','2','在院病人','3','出院登记','4','正常出院','5','出院欠费','6','医保年度结算病人','8','出院已审批','9','取消入院') as BRZTMC, decode(a.BRZT00,'1','待入院','21','待转入', decode(substr(a.BRZT00,1,1),'0','预约病人','1','入院登记','2','在院病人','3','出院登记','4','正常出院','5','出院欠费','6','医保年度结算病人','8','出院已审批','9','取消入院')) as BRZTJC, a.SZYS00, (select ZWXM00 from BM_YGBM00 where YGBH00 = a.SZYS00 and rownum = 1) as SZYSXM, --收治医生姓名 a.YSZID0, (select YSZMC0 from ZS_YSZ000 where YSZID0 = a.YSZID0) as YSZMC0, ' 'as SFMRCY, (select CWDJMC from BM_CWDJ00 where CWDJ00 = c.CWDJ00) as CWDJMC, --床位等级名称 (select sum(nvl(S.SFJE00,0)*J.SFCS00) from VW_BQ_CWJM00 J,VW_BQ_ZYSFXM S where J.SFXMID=S.SFXMID and J.CWDJ00=c.CWDJ00) as CWF000, --床位费 c.FJH000, --房间号 nvl(c.JZBZ00,'0') as JZBZ00, --急诊标志 0：非急诊 1：急诊 decode(c.XBXZ00,'0','不限','1','男','2','女') as XBXZMC, --性别限制 0不限1男2女 b.BRXB00 as FJXB00 --房间性别？ from ZY_BRXXB0 a,BM_BRXXB0 b,BM_CWK000 c where a.BRID00 = b.BRID00 and a.RYRQ00 >= '19000101' and a.DQBQ00 = c.BQH000(+) and a.RYCWH0 = c.CH0000(+) and (a.BRZT00 = '1' and a.RYRQ00>=to_char(sysdate-7,'YYYYMMDD') or a.BRZT00='21' and a.RYCWH0 is null) --未入科的或转科中的 and a.RYCWH0 is not null union all select a.ZYID00, a.BRID00, a.ZYH000, a.XM0000, a.XB0000, a.RYCWH0, a.DQBQ00, (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQBQ00) as DQBQMC, --当前病区名称 a.DQKS00, (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQKS00) as DQKSMC, --当前科室名称 a.BRZT00, decode(substr(a.BRZT00,1,1),'0','预约病人','1','入院登记','2','在院病人','3','出院登记','4','正常出院','5','出院欠费','6','医保年度结算病人','8','出院已审批','9','取消入院') as BRZTMC, '明日出院' as BRZTJC, a.SZYS00, (select ZWXM00 from BM_YGBM00 where YGBH00 = a.SZYS00 and rownum = 1) as SZYSXM, --收治医生姓名 a.YSZID0, (select YSZMC0 from ZS_YSZ000 where YSZID0 = a.YSZID0) as YSZMC0, '明日出院' as SFMRCY, (select CWDJMC from BM_CWDJ00 where CWDJ00 = c.CWDJ00) as CWDJMC, --床位等级名称 (select sum(nvl(S.SFJE00,0)*J.SFCS00) from VW_BQ_CWJM00 J, VW_BQ_ZYSFXM S where J.SFXMID=S.SFXMID and J.CWDJ00=c.CWDJ00) as CWF000, --床位费 c.FJH000, --房间号 nvl(c.JZBZ00,'0') as JZBZ00, --急诊标志 0：非急诊 1：急诊 decode(c.XBXZ00,'0','不限','1','男','2','女') as XBXZMC, --性别限制 0不限1男2女 b.BRXB00 as FJXB00 from ZY_BRXXB0 a,BM_BRXXB0 b,BM_CWK000 c where a.BRID00+0 = b.BRID00 and a.RYRQ00 >= '19000101' and a.DQBQ00 = c.BQH000(+) and a.RYCWH0 = c.CH0000(+) and a.BRZT00 = '20' --正常已入科的 and exists (select 1 from BQ_YJYZ00 where YZZT00='3' and QYRQ00=to_char(sysdate,'YYYYMMDD') and ZYID00=a.ZYID00 and ZLXMJC like '明日出院%') and nvl(c.CWXZ00,'0') not in ('2','4') --排除‘挂床’及‘择日住院’的床位 union all select 0 as ZYID00, 0 as BRID00, ' ' as ZYH000, ' ' as XM0000, ' ' as XB0000, CH0000 as RYCWH0, BQH000 as DQBQ00, (select BMMC00 from BM_BMBM00 where BMBH00 = a.BQH000) as DQBQMC, --当前病区名称 KSH000 as DQKS00, (select BMMC00 from BM_BMBM00 where BMBH00 = a.KSH000) as DQKSMC, --当前科室名称 ' ' as BRZT00, '空床' as BRZTMC, '空床' as BRZTJC, 0 as SZYS00, ' ' as SZYSXM, --收治医生姓名 a.YSZBH0 as YSZID0, (select YSZMC0 from ZS_YSZ000 where YSZID0 = a.YSZBH0) as YSZMC0, ' ' as SFMRCY, (select CWDJMC from BM_CWDJ00 where CWDJ00 = a.CWDJ00) as CWDJMC, --床位等级名称 (select sum(nvl(S.SFJE00,0)*J.SFCS00) from VW_BQ_CWJM00 J, VW_BQ_ZYSFXM S where J.SFXMID=S.SFXMID and J.CWDJ00=a.CWDJ00) as CWF000, --床位费 a.FJH000, --房间号 a.JZBZ00, --急诊标志 0：非急诊 1：急诊 decode(a.XBXZ00,'0','不限','1','男','2','女') as XBXZMC, --性别限制 0不限1男2女 decode(a.FJH000,null,null,(select XB0000 from ZY_BRXXB0 where ZYID00=(select ZYID00 from BM_CWK000 where BQH000=a.BQH000 and FJH000=a.FJH000 and ZYID00>0 and rownum=1))) as FJXB00 from BM_CWK000 a where a.KCBZ00 = 1 and a.CWXZ00 not in ('2','4') --排除‘挂床’及‘择日住院’的床位 union all select 0 ZYID00, a.BRID00, ' ' as ZYH000, b.BRXM00, b.BRXB00, a.CH0000, a.BQH000, (select BMMC00 from BM_BMBM00 where BMBH00 = a.BQH000) as DQBQMC, --当前病区名称 a.KSH000, (select BMMC00 from BM_BMBM00 where BMBH00 = a.KSH000) as DQKSMC, --当前科室名称 '0' BRZT00, '新病人' as BRZTMC, '新病人' as BRZTJC, a.YSGZH0, (select ZWXM00 from BM_YGBM00 where YGBH00 = a.YSGZH0 and rownum = 1) as SZYSXM, --收治医生姓名 a.YSZID0, (select YSZMC0 from zs_ysz000 where yszid0=a.YSZID0) as YSZMC0, ' 'as SFMRCY, (select CWDJMC from BM_CWDJ00 where CWDJ00=c.CWDJ00) as CWDJMC, --床位等级名称 (select sum(nvl(S.SFJE00,0)*J.SFCS00) from VW_BQ_CWJM00 J, VW_BQ_ZYSFXM S where J.SFXMID=S.SFXMID and J.CWDJ00=c.CWDJ00) as CWF000, --床位费 c.FJH000, --房间号 nvl(c.JZBZ00,'0') as JZBZ00, --急诊标志 0：非急诊 1：急诊 decode(c.XBXZ00,'0','不限','1','男','2','女') as XBXZMC, --性别限制 0不限1男2女 b.BRXB00 as FJXB00 from YS_BRZYSQ a,BM_BRXXB0 b,BM_CWK000 c where a.ZT0000 = '3' and a.JSBZ00 = '0' and nvl(a.ZYID00,0) <= 0 and a.TZYXRQ >= to_char(sysdate,'YYYYMMDD') and a.BRID00 = b.BRID00 and a.BQH000 = c.BQH000(+) and a.CH0000 = c.CH0000(+) and nvl(c.CWXZ00,'0') not in ('2','4') --排除‘挂床’及‘择日住院’的床位 union all --2021.09.09 省三院，增加在院病人显示 select a.ZYID00, a.BRID00, a.ZYH000, a.XM0000, a.XB0000, a.RYCWH0, a.DQBQ00, (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQBQ00) as DQBQMC, --当前病区名称 a.DQKS00, (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQKS00) as DQKSMC, --当前科室名称 a.BRZT00, decode(substr(a.BRZT00,1,1),'0','预约病人','1','入院登记','2','在院病人','3','出院登记','4','正常出院','5','出院欠费','6','医保年度结算病人','8','出院已审批','9','取消入院') as BRZTMC, '在院病人' as BRZTJC, a.SZYS00, (select ZWXM00 from BM_YGBM00 where YGBH00 = a.SZYS00 and rownum = 1) as SZYSXM, --收治医生姓名 a.YSZID0, (select YSZMC0 from ZS_YSZ000 where YSZID0 = a.YSZID0) as YSZMC0, '在院病人' as SFMRCY, (select CWDJMC from BM_CWDJ00 where CWDJ00 = c.CWDJ00) as CWDJMC, --床位等级名称 (select sum(nvl(S.SFJE00,0)*J.SFCS00) from VW_BQ_CWJM00 J, VW_BQ_ZYSFXM S where J.SFXMID=S.SFXMID and J.CWDJ00=c.CWDJ00) as CWF000, --床位费 c.FJH000, --房间号 nvl(c.JZBZ00,'0') as JZBZ00, --急诊标志 0：非急诊 1：急诊 decode(c.XBXZ00,'0','不限','1','男','2','女') as XBXZMC, --性别限制 0不限1男2女 b.BRXB00 as FJXB00 from ZY_BRXXB0 a,BM_BRXXB0 b,BM_CWK000 c where a.BRID00+0 = b.BRID00 and a.RYRQ00 >= '19000101' and a.DQBQ00 = c.BQH000(+) and a.RYCWH0 = c.CH0000(+) and a.BRZT00 = '20' --在院病人 and nvl(c.CWXZ00,'0') not in ('2','4') --排除‘挂床’及‘择日住院’的床位;