CREATE OR REPLACE VIEW SD_HOSPITAL.VW_BQ_ZYBRLB_YDHL
 (
 SZYS00, ZZYS00, ZRYS00, SXYS00, ZYID00, BRID00, MRN000, ZYH000, XM0000, 
RYLB00, XB0000, CSRQ00, BRZT00, RYCWH0, RYRQ00, RYSJ00, YBMC00, CYDJRQ, 
BRNL00, RYTSMS, SSMC00, SSTS00, HLJB00, HLJBID, HLJBJC, ZDMC00, ZDM000, 
SZYSXM, ZZYSXM, ZRYSXM, SXYSXM, DQBQ00, YSZMC0, YSZID0, SSKSBH, SSKSMC, 
DQKS00, KYJE00, BRGMJL, DQBQMC, DQKSMC, ZYE000, YKZYE0, YBYZF0, FJH000, 
ZRHLZ0, ZGHS00, ZGHSXM, CWZT00, YBJGJC, YBZXLB, FBBH00, YBBRLB, BRNLXB, 
DKKTJE, RYRQSJ, RKRQSJ, LJFY00, LJYJJ0, BRLXDH, BRLXDZ, BRLXXM, JYBJ00, 
SFYXJS, ZRZYBZ, XYZBR0, SFLJBR, FFBZBM, BZWKYE, BZNZFY, BZSFJE, BZNKYE, 
BZNBL0, YJQK00, BZMC00, DHTBR0, TSSXMC, SFJZFP, DQLJID, SJCYRQ, SJCYSJ, 
ZRBR00, BWBR00, BZBR00, SFDJ00, LCH000, SFYCD0, RJSSBZ, HSZ000, HSZXM0, 
YJZFJE, QTJHBZ

 )
 AS 
select a.SZYS00,a.ZZYS00,a.ZRYS00,a.SXYS00,a.ZYID00,a.BRID00,a.MRN000,a.ZYH000,a.XM0000,a.RYLB00,
a.XB0000,a.CSRQ00,a.BRZT00,a.RYCWH0,SJRYRQ RYRQ00,
SJRYSJ RYSJ00,
--(select decode(b.YBJGJC,r.BRLBJC,b.YBJGJC,b.YBJGJC||'('||r.BRLBJC||')') from BM_YBBRLB r where b.YBZXLB=r.YBZXLB and b.YBBRLB=r.YBBRLB) YBMC00,
--存在病人医保名称取值为空的情况，修改为关联ZY_BRXXB0 a
(select decode(b.YBJGJC,r.BRLBJC,b.YBJGJC,b.YBJGJC||'('||r.BRLBJC||')') from BM_YBBRLB r where a.YBZXLB=r.YBZXLB and a.YBBRLB=r.YBBRLB) YBMC00,
a.CYDJRQ,
SF_BQ_CSRQTONL(a.CSRQ00,a.SJRYRQ) BRNL00,    --病人年龄
to_char(greatest(decode(nvl(SJCYRQ,'20991231'),'20991231',to_date(to_char(sysdate,'yyyyMMdd'),'yyyyMMdd'),to_date(a.SJCYRQ,'yyyymmdd'))-to_date(a.SJRYRQ,'yyyymmdd'),1))||'天' RYTSMS, --住院天数描述
(select x.SSMC00 from VW_SS_YW0000 x where x.ZYGHID(+)=a.ZYID00 and x.MZZYBZ='1' and x.SSZT00 not in ('1','3')
 and x.SSQSRQ=(select trim(max(y.SSQSRQ)) from SS_YW0000 y where y.ZYGHID(+)=x.ZYGHID and y.MZZYBZ='1'
 and y.SSZT00 not in ('1','3')) and rownum=1) SSMC00, --手术名称
(select decode(trim(max(x.SSQSRQ)),null,null,((to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd')-to_date(trim(max(x.SSQSRQ)),'yyyymmdd'))))
 from SS_YW0000 x where x.ZYGHID(+)=a.ZYID00 and x.MZZYBZ='1' and x.SSZT00 not in ('1','3')) SSTS00,  --手术天数
(select y.ZLXMJC from BM_ZLZD00 y where y.ZLXMID(+)=a.HLJBID) HLJB00,   --护理级别
a.HLJBID,
(select decode(substr(trim(y.ZLXMJC),1,1),'一','Ⅰ','二','Ⅱ','三','Ⅲ','四','Ⅳ',substr(trim(y.ZLXMJC),1,1))  from BM_ZLZD00 y where y.ZLXMID(+)=a.HLJBID) HLJBJC,   --护理级别简称
(select ZDMC00 from VW_DZBL_BRZDXX_ONE where ZYID00=a.ZYID00 and Rownum=1) ZDMC00,  --诊断名称
(select ZDM000 from VW_DZBL_BRZDXX_ONE where ZYID00=a.ZYID00 and Rownum=1) ZDM000,  --诊断编码
(select ZWXM00 from BM_YGBM00 where YGBH00 = a.SZYS00) SZYSXM,--收治医生姓名
(select ZWXM00 from BM_YGBM00 where YGBH00 = a.ZZYS00) ZZYSXM,--主治医生姓名
(select ZWXM00 from BM_YGBM00 where YGBH00 = a.ZRYS00) ZRYSXM,--主任医生姓名
(select ZWXM00 from BM_YGBM00 where YGBH00 = a.SXYS00) SXYSXM,--实习医生姓名
a.DQBQ00,   --当前病区
c.YSZMC0,   --医生组名称
a.YSZID0,   --医生组ID
c.SSKSBH,   --所属科室编号
c.SSKSMC,   --所属科室名称
a.DQKS00,   --病人所在当前科室
SF_BQ_KYJE00(a.ZYID00,a.BRID00,2)  KYJE00, --病人可用金额
(select YPMC00  from YJ_BRGMJL t where t.BRID00(+)=a.BRID00 and JGMS00 like '%+%' and rownum=1) BRGMJL,  --病人过敏药物
(select BMMC00 from BM_BMBM00 where BMBH00 = a.DQBQ00) DQBQMC,--当前病区名称
d.BMMC00 DQKSMC,--当前科室名称
(select ZYE000 from ZY_BRZHXX where ZYID00=a.ZYID00 and rownum=1) ZYE000,--帐户实际总余额(可能会有负的金额出现) add by rbh 20120821
(select YKZYE0 from ZY_BRZHXX where ZYID00=a.ZYID00 and rownum=1) YKZYE0,--预扣后的总余额 add by rbh 20120821
(select YBYZF0 from ZY_BRZHXX where ZYID00=a.ZYID00 and rownum=1) YBYZF0,--医保预支付金额
--nvl(e.FJH000,'其它房间') FJH000,
--e.ZRHLZ0,
(select nvl(FJH000,'其它房间') from BM_CWK000 where CH0000 = a.RYCWH0 and BQH000=a.DQBQ00 and rownum=1) FJH000,
(select ZRHLZ0 from BM_CWK000 where CH0000 = a.RYCWH0 and BQH000=a.DQBQ00 and rownum=1) ZRHLZ0,
a.ZGHS00,--主管护士
(select ZWXM00 from BM_YGBM00 where YGBH00 = a.ZGHS00) ZGHSXM,--主管护士姓名
--e.CWZT00, --床位状态
(select CWZT00 from BM_CWK000 where CH0000 = a.RYCWH0 and BQH000=a.DQBQ00 and rownum=1) CWZT00,--床位状态
b.YBJGSX YBJGJC, --医保机构简称
b.YBZXLB YBZXLB,--医保中心类别 add by zxz 2013-07-17
b.FBBH00 FBBH00,--病人费别编号 add by zxz 2013-07-17
f.YBBRLB YBBRLB,--医保病人类别 add by zxz 2013-07-17
SF_BQ_CSRQTONL(a.CSRQ00,a.SJRYRQ)||'('||a.XB0000||')' BRNLXB, --病人年龄加性别
(select nvl(sum(ZJE000),0) from VW_BQ_WJZXMCF_YDHL where ZYID00=a.ZYID00) DKKTJE,  --待扣可退金额
substr(a.RYRQ00,1,4)||'-'||substr(a.RYRQ00,5,2)||'-'||substr(a.RYRQ00,7,2)||' '||substr(a.RYSJ00,1,5) RYRQSJ, --入院日期时间
(select substr(QSRQ00,1,4)||'-'||substr(QSRQ00,5,2)||'-'||substr(QSRQ00,7,2)||' '||substr(QSSJ00,1,5)
 from BQ_BRLDXX where ZYID00=a.ZYID00 and QSZT00='新入' and rownum=1) RKRQSJ,   --入科日期时间
(select WJSJE0 from ZY_BRZHXX where ZYID00=a.ZYID00) LJFY00,        --累计发生费用
(select nvl(sum(x.JFJE00),0) from ZY_BRJFB0 x where x.ZYID00=a.ZYID00 and ZFFS00 in (1,2,8,24,25) and JFBZ00<>'2' and JZDH00=0) LJYJJ0,  --累计预交金
f.BRLXDH,  --联系人电话
f.BRLXDZ,  --联系人地址
f.BRLXXM,   --联系人姓名
-least(0,(SF_BQ_KYJE00(a.ZYID00,a.BRID00) - (select  nvl(sum(ZFJE00),0) from VW_BQ_WJZXMCF_YDHL where ZYID00=a.ZYID00))) JYBJ00,  --建议补交金额
a.SFYXJS,  --是否允许结算
a.ZRZYBZ,  --择日住院标志
(select decode( yzzt00,'0','1','0') from VW_BQ_YZLL00_ZYYDHL where zyid00=a.zyid00 and yzzt00='0' and rownum=1) XYZBR0,
decode((select count(1) from BQ_ZRZC00 where nvl(jsfs00,' ')=' ' and zyid00=a.zyid00),0,'0','1') SFLJBR,--是否路径病人1是0否
a.FFBZBM,
SF_ZY_GETDBZWKYYE(a.ZYID00)                             BZWKYE,--付费单病种外可用余额
(select BZNZFY from VW_ZY_DBZCZB where ZYID00=a.ZYID00 and TCRQ00 is null) BZNZFY,--单病种内费用
(select BZSFJE from VW_ZY_DBZCZB where ZYID00=a.ZYID00 and TCRQ00 is null) BZSFJE,--单病种定额
(select BZNKYE from VW_ZY_DBZCZB where ZYID00=a.ZYID00 and TCRQ00 is null) BZNKYE,--病种内余额
(select BZNBL0 from VW_ZY_DBZCZB where ZYID00=a.ZYID00 and TCRQ00 is null) BZNBL0,--病种内比例
(select YJQK00 from VW_ZY_DBZCZB where ZYID00=a.ZYID00 and TCRQ00 is null) YJQK00,--预警情况
(select BZMC00 from VW_ZY_DBZCZB where ZYID00=a.ZYID00 and TCRQ00 is null) BZMC00,--病种名称
(select decode(YZZT00,'3','1','4','1','2','1','9','1','0') from VW_BQ_YZLL00_ZYYDHL where ZYID00=a.zyid00 and
(((YZZT00 ='3' or YZZT00='4') and CLBZ00='0' and trim(TYYSHS) is not null and instr(nvl(TYYSHS,'0'),'/')<=0) or
((YZZT00 ='2' or YZZT00='9') and CLBZ00='0' and trim(TYSJ00) is not null)) and rownum=1) DHTBR0,  --1：待核停病人
f.TSSXMC, --病人类别
a.SFJZFP,  --是否精准扶贫
a.DQLJID,  --电子病历转入路径id
a.SJCYRQ,
a.SJCYSJ,
decode((select count(1) from BQ_BRLDXX where QSZT00='转入' and BQH000 = a.DQBQ00 and zyid00=a.ZYID00),0,'0','1') ZRBR00, --转入病人
decode((select count(1) from BQ_YJYZ00 where ZYID00=a.ZYID00 and ZLXMJC like '%病危通知%' and YZZT00 in ('1','2')),0,'0','1') BWBR00,--病危病人
decode((select count(1) from BQ_YJYZ00 where ZYID00=a.ZYID00 and ZLXMJC like '%病重通知%' and YZZT00 in ('1','2')),0,'0','1') BZBR00, --病重病人
(select SFDJ00 from ZY_BRZHXX where ZYID00=a.ZYID00 and rownum=1) SFDJ00,
nvl((select LCH000 from BM_CWK000 where CH0000 = a.RYCWH0 and BQH000=a.DQBQ00 and rownum=1),'未知') LCH000,--楼层号
SF_BQ_SFYZYCD(a.ZYID00) SFYCD0,--是否全部已查对
a.RJSSBZ, --是否日间手术
a.HSZ000,--护士长
(select ZWXM00 from BM_YGBM00 where YGBH00 = a.HSZ000) HSZXM0,--护士长姓名
(select YJZFJE from ZY_BRZHXX where ZYID00=a.ZYID00 and rownum=1) YJZFJE,--预结自费金额
a.QTJHBZ --千天计划标志
from ZY_BRXXB0 a, IC_YBBRLB b, ZS_YSZ000 c,BM_BMBM00 d, BM_BRXXB0 f
 where a.YBLB00=b.YBLB00 and a.FBBH00=b.FBBH00 and a.BRID00=f.BRID00
 and a.YSZID0 = c.YSZID0 and a.DQKS00=d.BMBH00 
and a.RYCWH0 is not null

union all

select null SZYS00,null ZZYS00,null ZRYS00,null SXYS00,null ZYID00,null BRID00, null MRN000,null ZYH000,
(select decode(CWZT00,'2','(已被包)',null) from ZY_BRXXB0 where ZYID00=a.ZYID00) XM0000,null RYLB00,
null XB0000,null CSRQ00,'20' BRZT00,CH0000 RYCWH0,
null RYRQ00,
null RYSJ00,
null YBMC00,
'20991231' CYDJRQ,
null BRNL00, --病人年龄
null RYTSMS, --住院天数描述
null SSMC00, --手术名称
null SSTS00, --手术天数
null HLJB00,   --护理级别
null HLJBID,
null HLJBJC,   --护理级别简称
null ZDMC00,  --诊断名称
null ZDM000,  --诊断编码
null SZYSXM,--收治医生姓名
null ZZYSXM,--主治医生姓名
null ZRYSXM,--主任医生姓名
null SXYSXM,--实习医生姓名
BQH000 DQBQ00,   --当前病区
null YSZMC0,   --医生组名称
null YSZID0,   --医生组ID
null SSKSBH,   --所属科室编号
null SSKSMC,   --所属科室名称
KSH000 DQKS00,   --病人所在当前科室
null KYJE00,   --病人可用金额
null BRGMJL,   --病人过敏药物
null DQBQMC,--当前病区名称
null DQKSMC,--当前科室名称
null ZYE000,--帐户实际总余额(可能会有负的金额出现) add by rbh 20120821
null YKZYE0,--预扣后的总余额 add by rbh 20120821
null YBYZF0,--医保预支付
null FJH000,
null ZRHLZ0,
null ZGHS00, --主管护士
null ZGHSXM, --主管护士姓名
CWZT00, --床位状态
null YBJGJC, --医保机构简称
null YBZXLB,--医保中心类别 add by zxz 2013-07-17
null FBBH00,--病人费别编号 add by zxz 2013-07-17
null YBBRLB,--医保病人类别 add by zxz 2013-07-17
(select decode(CWZT00,'2',RYCWH0||'['||XM0000||']',null) from ZY_BRXXB0 where ZYID00=a.ZYID00)  BRNLXB, --病人年龄加性别
null DKKTJE,  --待扣可退金额
null RYRQSJ,  --入院日期时间
null RKRQSJ,  --入科日期时间
null LJFY00,  --累计发生费用
null LJYJJ0,  --累计预交金
null BRLXDH,  --联系人电话
null BRLXDZ,  --联系人地址
null BRLXXM,   --联系人姓名
null JYBJ00,  --建议补交金额
null SFYXJS,  --是否允许结算
null ZRZYBZ,  --择日住院标志
null XYZBR0,   --新医嘱病人
null SFLJBR,   --是否路径病人
null FFBZBM,   --单病种编码
null BZWKYE,   --付费单病种外可用余额
null BZNZFY,   --单病种内费用
null BZSFJE,   --单病种定额
null BZNKYE,   --病种内余额
null BZNBL0,   --病种内比例
null YJQK00,   --预警情况
null BZMC00,   --病种名称
null DHTBR0,   --待核停病人
null TSSXMC,   --病人类别
null SFJZFP,   --是否精准扶贫
null DQLJID,   --电子病历转入路径id
null SJCYRQ,
null SJCYSJ,
null ZRBR00,   --转入病人
null BWBR00,
null BZBR00,
null SFDJ00,
nvl(LCH000,'未知'), --楼层号
'0',            --是否全部已查对
null RJSSBZ,    --日间手术标志
null HSZ000,
null HSZXM0,
0 YJZFJE,--预结自费金额
null QTJHBZ --千天计划标志
from BM_CWK000 a where CWZT00 in ('0','2')
/