create or replace view vw_zy_dbzjsqd as
select
  a.ZYID00,
  a.BRID00,
  b.ZYH000,
  a.JZDH00,
  a.PJH000,
  b.XM0000 as BRXM00,
  b.XB0000 as BRXB00,
  b.RYCWH0,
  trim(f.YBKH00) as YBKH00,
  trim(f.YBID00) as YBID00,
  b.DQBQ00,
  (select BMMC00  from BM_BMBM00 where BMBH00=b.DQBQ00) as DQBQMC,
  B.DQKS00,
  (select BMMC00  from BM_BMBM00 where BMBH00=b.DQKS00) as DQKSMC, 
  a.JZRQ00,
  a.JZSJ00,
  b.RYRQ00,
  b.CYDJRQ,
  b.CYRQ00,
  b.RYRQ00||'至'||b.CYRQ00 as JSRQSJ,   
  a.BRZJE0,                                                                --病人费用总金额
  a.JZJE00,                                                                --记帐金额
  a.GFJE00,                                                                --公费金额
  a.HJJE00,                                                                --合计应收金额
  a.JMJE00,                                                                --减免金额
  a.GRZHZF,                                                                --个人医疗帐户支付
  a.TCJJZF,                                                                --统筹基金支付
  a.SBGRZH,                                                                --商保个人帐户
  a.SBTCJJ,                                                                --商保统筹基金   
  a.ZFJE00,                                                                --自付金额
  a.ZFJE00+decode(g.SFJSDW,'N',GRZHZF,0) as SJZFJE,
  a.GWYBZ0,
  a.FFBZBM as DBZBM0,
  nvl((select BZMC00 from BM_BZSFBZ where FBBH00 = a.FBBH00 and YBZXLB= a.YBZXLB and FFBZBM = a.FFBZBM and rownum=1),
      (select DBZMC0 from BM_DBZDEB where YBZXLB= a.YBZXLB and DBZBM0 = a.FFBZBM and rownum=1)) as DBZMC0,
  a.YBYL35 as DBZDE0,                                                      --单病种限额
  substrb(SF_ZY_DBZQTXX(a.ZYID00,a.JZDH00,1),1,50) as  HCDBBM,             --除外耗材打包编码
  substrb(SF_ZY_DBZQTXX(a.ZYID00,a.JZDH00,2),1,50) as  HCDBMC,             --除外耗材打包名称
  a.YBYL34 as HCDBDJ,                                                      --除外耗材打包单价
  1 as     HCDBSL,                                                         --除外耗材打包数量
  '套' as  HCDBDW,                                                         --除外耗材打包单位
  a.YBYL34 as  HCDBJE,                                                     --除外耗材打包金额
  'CBZ001000000' as  CBCWBM,                                               --超标床位编码
  '超普通标准床位费' as  CBCWMC,                                           --超标床位名称
  '--' as  CBCWDJ,                                                         --超标床位包单价
  '--' as  CBCWSL,                                                         --超标床位数量
  '--' as  CBCWDW,                                                         --超标床位单位
  a.YBYL33 as CBCWJE,                                                      --超标床位金额
  to_date(nvl(B.SJCYRQ,to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')-to_date(B.SJRYRQ,'YYYYMMDD') as ZYTS00,  --住院天数
  decode(nvl(a.YBSM10,'00'),'00',nvl(YBYL35,0)+nvl(YBYL33,0)+nvl(YBYL34,0),a.HJJE00)  as BZFPJE,  --单病种发票金额
  '注:在医保最高支付限额内费用,按医保相关规定报销' as  BZXX00,  --备注信息
  substrb(a.JZRQ00,1,4)||'-'||substrb(a.JZRQ00,5,2)||'-'||substrb(a.JZRQ00,7,2) as RQ0000  --结算日期
from  ZY_JZB000 a,ZY_BRXXB0 b,BM_BRXXB0 f,IC_YBBRLB g
where  a.ZYID00 = b.ZYID00
  and  a.BRID00 = f.BRID00
  and  f.FBBH00 = g.FBBH00
  and  f.YBLB00 = g.YBLB00
;