CREATE VIEW SD_HOSPITAL.VW_ZY_DBZJSQD
 (
 ZYID00, BRID00, ZYH000, JZDH00, PJH000, BRXM00, BRXB00, RYCWH0, YBKH00, 
YBID00, DQBQ00, DQBQMC, DQKS00, DQKSMC, JZRQ00, JZSJ00, RYRQ00, CYDJRQ, 
CYRQ00, JSRQSJ, BRZJE0, JZJE00, GFJE00, HJJE00, JMJE00, GRZHZF, TCJJZF, 
SBGRZH, SBTCJJ, ZFJE00, SJZFJE, GWYBZ0, DBZBM0, DBZMC0, DBZDE0, HCDBBM, 
HCDBMC, HCDBDJ, HCDBSL, HCDBDW, HCDBJE, CBCWBM, CBCWMC, CBCWDJ, CBCWSL, 
CBCWDW, CBCWJE, ZYTS00, BZFPJE, BZXX00, RQ0000

 )
 AS 
select
 A.ZYID00,
 A.BRID00,
 B.ZYH000,
 A.JZDH00,
 A.PJH000,
 B.XM0000,
 B.XB0000,
 B.RYCWH0,
 trim(F.YBKH00)YBKH00,
 trim(F.YBID00),
 B.DQBQ00,
 (select aa.BMMC00  from BM_BMBM00 aa where aa.BMBH00=B.DQBQ00) as DQBQMC,
 B.DQKS00,
 (select aa.BMMC00  from BM_BMBM00 aa where aa.BMBH00=B.DQKS00) as DQKSMC, 
 A.JZRQ00,
 A.JZSJ00,
 B.RYRQ00,
 B.CYDJRQ,
 B.sjcyrq,
 B.SJRYRQ||'至'||B.sjcyrq,
 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) SJZFJE,
 A.GWYBZ0,       
 A.DBZBM0,
 B.DBZMC0,
 SF_ZY_BZFPXMJE(A.ZYID00,A.JZDH00,'YBYL04')  as DBZDE0,--单病种限额
 substrb(SF_ZY_DBZQTXX(A.ZYID00,A.JZDH00,A.DBZBM0,0,1),1,50) as  HCDBBM,  --耗材打包编码
 substrb(SF_ZY_DBZQTXX(A.ZYID00,A.JZDH00,A.DBZBM0,0,2),1,50) as  HCDBMC,  --耗材打包名称
 SF_ZY_BZFPXMJE(A.ZYID00,A.JZDH00,'YBYL34')  as  HCDBDJ,  --耗材打包单价
 1  as  HCDBSL,  --耗材打包数量
 '套' as  HCDBDW,--耗材打包单位
 SF_ZY_BZFPXMJE(A.ZYID00,A.JZDH00,'YBYL34')  as  HCDBJE,  --耗材打包金额
 'CBZ001000000' as  CBCWBM,--超标床位编码
 '超普通标准床位费' as  CBCWMC,--超标床位名称
 '--' as  CBCWDJ, --超标床位包单价
 '--' as  CBCWSL,--超标床位数量
 '--' as  CBCWDW,--超标床位单位
 SF_ZY_BZFPXMJE(A.ZYID00,A.JZDH00,'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',SF_ZY_DBZFPJE(0,A.ZYID00,A.JZDH00,A.DBZBM0,B.DBZMC0,A.BRZJE0,A.HJJE00,A.TCJJZF,A.GRZHZF,A.ZFJE00,A.YBYL04,A.YBYL05,A.YBYL33,A.YBYL34,A.YBYL35),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
/