create or replace view vw_zy_brxxb0 as
select
  a.ZYID00,
  a.BRID00,
  b.BRBLH0,
  a.MRN000,
  a.ZYH000,
  a.BRRYCS,
  a.YBZYH0,
  a.YBZYCS,
  a.XM0000,
  a.XB0000,
  a.CSRQ00,
  substr(SF_BQ_CSRQTONL(a.CSRQ00),1,10) as BRNL00 ,  
  a.FBBH00,
  (select FBMC00 from BM_BRFBB0 where FBBH00 = a.FBBH00) as FBMC00,   --费别名称
  a.LBBH00,
  a.RYRQ00,
  a.RYSJ00,
  a.RYRQ00||' '||a.RYSJ00 as RYRQSJ,
  a.RYCZY0,
  a.CZYXM0,
  a.RYZD00,
  a.RYZDMC,
  a.RYSQK0,
  a.RYLB00,
  a.ZYMD00,
  a.RYFS00,
  a.RYBQ00,
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.RYBQ00) as RYBQMC,  --入院病区名称
  a.RZKS00,
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.RZKS00) as RZKSMC,  --入住科室名称
  a.RYCWH0,
  substr(SF_XT_GETPXCWH(a.RYCWH0),1,10) as PXCWH0 ,                  --排序用床位号  
  a.DQBQ00,
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQBQ00) as DQBQMC,  --当前病区名称
  (select YYID00 from BM_BMBM00 where BMBH00 = a.DQBQ00) as BQYYID,  --当前病区所在分院ID  
  a.DQKS00,
  (select BMMC00 from BM_BMBM00 where BMBH00 = a.DQKS00) as DQKSMC,  --当前科室名称
  a.RCYFH0,
  a.CYRQ00,
  a.CYSJ00,
  a.CYRQ00||' '||a.CYSJ00 as CYRQSJ,
  (select ZDMC00 from BQ_BRZDXX where ZYID00=a.ZYID00 and ZDLB00='3' and rownum=1) as CYZDMC, --出院诊断
  a.CYCZY0,
  a.CYYGXM,
  a.ZZYS00,
  a.ZZYSXM,
  a.ZRYS00,
  a.ZRYSXM,
  a.SXYS00,
  a.SXYSXM,
  a.HLJBID,
  (select ZLXMJC from BM_ZLZD00 where ZLXMID = a.HLJBID and rownum = 1) as HLJBMC,  --护理级别名称
  a.BRZT00,
  decode(substr(a.BRZT00,1,1),'0','预约病人','1','入院登记','2','在院病人','3','出院登记','4','正常出院','5','出院欠费','6','医保年度结算病人','8','出院已审批','9','取消入院') as BRZTMC,
  a.CYDJRQ,
  a.CYDJSJ,
  a.SZYS00,
  (select ZWXM00 from BM_YGBM00 where YGBH00 = a.SZYS00 and rownum = 1) as SZYSXM,  --收治医生姓名
  a.YBLB00,
  c.YBMC00,                     --医保名称
  nvl(c.YBBHSB,'N') as YBBHSB,  --医保包含商保
  nvl(c.SFXNH0,'0') as SFXNH0,  --是否新农合
  a.YBZXLB,
  a.YBBRLB,
  a.SSMC00,
  a.SHJG00,
  a.CYXJ00,
  a.GSDJH0,
  b.YBKH00,
  a.YBQH00,
  a.YKSH00,
  a.ZKJCBZ,
  a.SJCYRQ,
  a.CYJSRQ,
  a.CYJSSJ,
  a.SJRYRQ,
  a.SJRYSJ,
  a.SJRYRQ||' '||a.SJRYSJ as JZRQSJ,
  a.CSLX00,                                                   --农合传送类型,1实时结算,0传明细
  decode(a.CSLX00,'0','传明细','1','实时结算','') as CSLXMC, 
  nvl(a.SHBZH0,b.BRZJBH) as SHBZH0,
  a.FYHJJE,
  a.YSZID0,
  a.DQLJID,
  a.YYLBID,
  a.YYKBRQ,
  a.QKDJBH,
  a.DBLB00,
  a.DBLBMC,
  a.BLCJRQ,
  a.CYCZRQ,
  a.SJCYSJ,
  a.ZGHS00,
  (select ZWXM00 from BM_YGBM00 where YGBH00 = a.ZGHS00) as ZGHSXM,               --主管护士姓名
  a.ZRHLZ0,                                                                       --责任护理组
  b.YBID00,
  (select nvl(sum(ZYE000-TYJE00),0) from ZY_BRZHXX  where ZYID00=a.ZYID00 and SFDJ00='0' ) as ZHZYE0, --账户余额
  d.BRLBMC,
  decode(c.YBJGJC,d.BRLBJC,c.YBJGJC,c.YBJGJC||'('||d.BRLBJC||')') as FBXX00,      --费别信息
  substr(SF_XT_GETPYSM(a.XM0000),1,10) as PYSM00,                                 --拼音首码
  substr(SF_XT_GETWBSM(a.XM0000),1,10) as WBSM00,                                 --五笔首码  
  nvl(SFYXJS,'1') as SFYXJS,                                                      --是否允许结算,‘0’护士不允许 ‘1’允许 ‘2’允许患者提前结算
  a.SQID00,                                                                       --社区服务中心ID
  (select SQMC00 from BM_SQZD00 where SQID00=a.SQID00) as SQMC00,                  --社区服务中心名称
  d.CWZGBZ,                                                                       --床位最高标准
  a.ZYID00 as EWM001,                                                             --碗带二维码
  a.FMBQBH,                                                                       --生育分娩病情编号
  a.ZRZYBZ,                                                                       --择日住院标志: 0非择日,1择日住院,2择日转正式住院
  c.RYLBBZ,                                                                       --入院类别标志：1：传值 01，02，03..;2：传普通，家庭病床 3和空:人民医院模式，默认为3
  a.DBZBM0,                                                                       --单病种编码
  a.DBZMC0,                                                                       --单病种名称
  decode(a.DBZBM0,null,'','单') as DBZBZ0,                                        --单病种病人标识
  a.FFBZBM,                                                                       --付费病种编码
  decode(a.FFBZBM,null,'','单') as FFBZBZ,                                        --付费单病种病人标识
  a.YBJHBZ,                                                                       --医保收费交互标识  0未发送 1已发送未接收 2已接收
  a.JSHDZT,                                                                       --出院结算核对状态  0未核对 1已核对；默认0
  decode(a.JSHDZT,'0','未核对','1','已核对') as HDZTMC,                           --出院结算核对状态名称
  a.HDRQ00,                                                                       --核对日期
  a.HDSJ00,                                                                       --核对时间
  a.HDR000,                                                                       --核对人
  (select ZWXM00 from BM_YGBM00 where YGBH00 = a.HDR000) as HDRXM0,               --核对人姓名
  (select nvl(max(WJSJE0),0) from ZY_BRZHXX where ZYID00 =a.ZYID00) as WJSJE0,    --未结算费用金额
  (select nvl(max(ZYE000),0) from ZY_BRZHXX where ZYID00 =a.ZYID00) as YJJZYE,    --预交金总余额
  SF_ZY_KYJE00(a.ZYID00,0) as YJKYJE,                                             --可用预交金金额  
  a.SCBLLX,                                                                       --C-DRG病历类型
  (select MC0000 from BM_TYZD00 where ZDMC00='CDRG病历类型' and BH0000 =a.SCBLLX) as SCBLMC, --C-DRG病历类型名称
  a.CDRGFS,                                                                       --CDRG发送状态 0未发送 1已发送
  decode(a.CDRGFS,'0','未发送','1','已发送') as CDFSMC,                           --CDRG发送状态名称
  a.YLZZDH,                                                                       --新农合跨省结算--医疗转诊单号 
  SF_XT_RETURNINDEXEH(a.CHSFXX,'|', 1) as CHSFDM,                                 --参合省代码
  SF_XT_RETURNINDEXEH(a.CHSFXX,'|', 2) as CHSFMC,                                 --参合省名称
  a.CHSFXX,                                                                       --参合省代码和名称
  SF_XT_RETURNINDEXEH(a.CHSQXX,'|', 1) as CHSQDM,                                 --参合市代码
  SF_XT_RETURNINDEXEH(a.CHSQXX,'|', 2) as CHSQMC,                                 --参合市名称
  a.CHSQXX,                                                                       --参合市代码和名称
  SF_XT_RETURNINDEXEH(a.CHXQXX,'|', 1) as CHXQDM,                                 --参合县代码
  SF_XT_RETURNINDEXEH(a.CHXQXX,'|', 2) as CHXQMC,                                 --参合县名称
  a.CHXQXX,                                                                       --参合县代码和名称
  substrb(a.ZYQTXX,1,200) as ZYQTXX,                                              --新农合跨省结算--住院其他信息(步骤状态) 
  a.ZYLSH0,
  (select yyqc00 from xt_yyxx00 where yyid00=(select yyid00 from bm_bmbm00 where bmbh00=a.dqks00))  YYMC00  --医院名称
  from ZY_BRXXB0 a,BM_BRXXB0 b,IC_YBBRLB c,BM_YBBRLB d
  where  a.BRID00+0 = b.BRID00
    and  a.YBLB00 = c.YBLB00
    and  a.FBBH00 = c.FBBH00  
    and  a.FBBH00 = d.FBBH00
    and  a.YBBRLB = d.YBBRLB
    and  c.YBZXLB = d.YBZXLB
    and  a.RYRQ00 >= '19000101';