create or replace view vw_bq_zybrlb_ydhl as
select a.SZYS00,a.ZZYS00,a.ZRYS00,a.SXYS00,a.ZYID00,a.BRID00,a.MRN000,a.ZYH000,a.XM0000,
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 f.YBBRLB=r.YBBRLB) YBMC00,
a.CYDJRQ,
SF_BQ_CSRQTONL(a.CSRQ00) 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 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
nvl(e.FJH000,'其它房间') FJH000,
e.ZRHLZ0,
a.ZGHS00,--主管护士
(select ZWXM00 from BM_YGBM00 where YGBH00 = a.ZGHS00) ZGHSXM,--主管护士姓名
e.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.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 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 --病人类别
from ZY_BRXXB0 a, IC_YBBRLB b, ZS_YSZ000 c,BM_BMBM00 d,BM_CWK000 e, 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=e.CH0000 and a.DQBQ00=e.BQH000
 --and a.YSZID0 = c.YSZID0 and a.DQKS00=d.BMBH00 and a.RYCWH0=e.CH0000(+) and a.DQBQ00=e.BQH000(+)

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 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 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 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 DHTBR0,   --待核停病人
null TSSXMC    --病人类别
from BM_CWK000 a where CWZT00 in ('0','2')
;