create or replace view vw_sf_fpzm00 as select a.YBDJH0, --医保结算号 decode(a.YBDJH0,'0','自费','医保'）FBMC01, --根据医保流水号判断的费别,不发医保时显示自费 a.BRID00, --病人id e.BRBLH0, --病人病历号 e.BRXM00, --病人姓名 e.BRXB00, --病人性别 e.YBID00, --医保id e.YBKH00, --医保卡号 f.GHH000, --挂号号 b.PJH000, --票据号 a.JZDH00, --结账单号 a.FBBH00, --费别编号 (select FBMC00 from BM_BRFBB0 where FBBH00 = a.FBBH00) as FBMC00, --费别名称 a.JZJE00, --记帐金额 a.GFJE00, --公费金额 a.HJJE00, --合计应收金额 substr(SP_BM_RMB000(a.HJJE00),1,100) as DXHJJE, --大写合计金额 a.JMJE00, --减免金额 a.GRZHZF, --个人医疗帐户支付(医保返回) a.TCJJZF, --统筹基金支付(医保返回) a.SBGRZH, --商保个人帐户 a.SBTCJJ, --商保统筹基金 a.ZFJE00, --自付金额(医保返回) a.JZRQ00, --结帐日期 a.JZSJ00, --结帐时间 b.XJJE00 as XJFKJE, --现金付款,为了方便打印发票而设置的 b.ZZJE00 as ZZFKJE, --转帐付款 b.YHKJE0, --银行卡金额 a.YBXJZF, --医保现金支付 a.YLZFJE, --乙类自付金额 a.CFDXJE, --超封顶线金额 a.GWYBZ0, --公务员补助 a.BCKBCS, --就诊次数 a.JJZFE0, --保健基金支付 a.FYBJE0, --非医保费 a.SYBXZF, --商业保险支付 a.SBZFE0, --商保基金支付(省属三家市属九家医疗机构) a.BJZFE0, --商保基金支付(省属三家市属九家医疗机构) b.PJXH00, --发票序号_xumz f.GHRQ00, --挂号日期_xumz nvl(a.YBYL01,0) as YBYL01, nvl(a.YBYL02,0) as YBYL02, nvl(a.YBYL03,0) as YBYL03, nvl(a.YBYL04,0) as YBYL04, nvl(a.YBYL05,0) as YBYL05, nvl(a.YBYL06,0) as YBYL06, nvl(a.YBYL07,0) as YBYL07, nvl(a.YBYL08,0) as YBYL08, nvl(a.YBYL09,0) as YBYL09, nvl(a.YBYL10,0) as YBYL10, nvl(a.YBYL11,0) as YBYL11, --健康账户支付 nvl(a.YBYL12,0) as YBYL12, --健康账户余额 nvl(a.YBYL13,0) as YBYL13, --共济账户支付 nvl(a.YBYL14,0) as YBYL14, --共济账户余额 nvl(a.YBYL15,0) as YBYL15, --健康账户支付医保费用 nvl(a.YBYL16,0) as YBYL16, --健康账户支付非医保费用 nvl(a.YBYL17,0) as YBYL17, --减免金额(医保返回不包含统筹里面) nvl(a.YBYL18,0) as YBYL18, nvl(a.YBYL19,0) as YBYL19, nvl(a.YBYL20,0) as YBYL20, nvl(a.YBYL21,0) as YBYL21, nvl(a.YBYL22,0) as YBYL22, nvl(a.YBYL23,0) as YBYL23, nvl(a.YBYL24,0) as YBYL24, nvl(a.YBYL25,0) as YBYL25, nvl(a.YBYL26,0) as YBYL26, nvl(a.YBYL27,0) as YBYL27, nvl(a.YBYL28,0) as YBYL28, nvl(a.YBYL29,0) as YBYL29, nvl(a.YBYL30,0) as YBYL30, --择日住院门诊统筹(医保未返回) nvl(a.YBYL31,0) as YBYL31, --个人支付金额(医保返回) nvl(a.YBYL32,0) as YBYL32, --大病补偿个人支付差额(医保返回) nvl(a.YBYL33,0) as YBYL33, --备用字段 nvl(a.YBYL34,0) as YBYL34, --备用字段 nvl(a.YBYL35,0) as YBYL35, --备用字段 nvl(a.YBYL36,0) as YBYL36, --备用字段 nvl(a.YBYL37,0) as YBYL37, --备用字段 nvl(a.YBYL38,0) as YBYL38, --备用字段 nvl(a.YBYL39,0) as YBYL39, --备用字段 nvl(a.YBYL40,0) as YBYL40, --备用字段 nvl(a.YBYL41,0) as YBYL41, --备用字段 nvl(a.YBYL42,0) as YBYL42, --备用字段 nvl(a.YBYL43,0) as YBYL43, --备用字段 nvl(a.YBYL44,0) as YBYL44, --备用字段 nvl(a.YBYL45,0) as YBYL45, --备用字段 nvl(a.YBYL46,0) as YBYL46, --备用字段 nvl(a.YBYL47,0) as YBYL47, --备用字段 nvl(a.YBYL48,0) as YBYL48, --备用字段 nvl(a.YBYL49,0) as YBYL49, --备用字段 nvl(a.YBYL50,0) as YBYL50, nvl(a.YBYL51,0) as YBYL51, nvl(a.YBYL52,0) as YBYL52, nvl(a.YBYL53,0) as YBYL53, nvl(a.YBYL54,0) as YBYL54, nvl(a.YBYL55,0) as YBYL55, nvl(a.YBYL56,0) as YBYL56, nvl(a.YBYL57,0) as YBYL57, nvl(a.YBYL58,0) as YBYL58, nvl(a.YBYL59,0) as YBYL59, nvl(a.YBYL60,0) as YBYL60, nvl(a.YBYL61,0) as YBYL61, nvl(a.YBYL62,0) as YBYL62, nvl(a.YBYL63,0) as YBYL63, nvl(a.YBYL64,0) as YBYL64, nvl(a.YBYL65,0) as YBYL65, nvl(a.YBYL66,0) as YBYL66, nvl(a.YBYL67,0) as YBYL67, nvl(a.YBYL68,0) as YBYL68, nvl(a.YBYL69,0) as YBYL69, a.YBSM01, --行政区号+区域 a.YBSM02, --是否择日住院 a.YBSM03, --年度结算截止日期 a.YBSM04, --血费报销ID(血费系统) a.YBSM05, --单病种登记流水号 a.YBSM06, --结算类型 0 医保 1生育 3保健 a.YBSM07, --备用字段 a.YBSM08, --单病种数据拆分费用单据号 a.YBSM09, --字符型备用字段 a.YBSM10, --字符型备用字段 a.YBSM11, --字符型备用字段 a.YBSM12, --字符型备用字段 a.YBSM13, --字符型备用字段 a.YBSM14, --字符型备用字段 a.YBSM15, --字符型备用字段 a.YBSM16, --字符型备用字段 a.YBSM17, --字符型备用字段 a.YBSM18, --字符型备用字段 a.YBSM19, --字符型备用字段 a.YBSM20, --字符型备用字段 a.YBSM21, --字符型备用字段 a.YBSM22, --字符型备用字段 a.YBSM23, --字符型备用字段 a.YBSM24, --字符型备用字段 a.YBSM25, --字符型备用字段 a.YBSM26, --字符型备用字段 a.YBSM27, --字符型备用字段 a.YBSM28, --字符型备用字段 a.YBSM29, --字符型备用字段 (select nvl(sum(XMJE00),0) from SF_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (1)) as XYFY00, --西药费 (select nvl(sum(XMJE00),0) from SF_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (2)) as ZCYFY0, --中成药 (select nvl(sum(XMJE00),0) from SF_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (3)) as ZYFY00, --中药费 (select nvl(sum(XMJE00),0) from SF_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (8)) as JCFY00, --检查费 (select nvl(sum(XMJE00),0) from SF_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (10)) as ZLFY00, --治疗费 (select nvl(sum(XMJE00),0) from SF_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (14)) as FSFY00, --放射费 (select nvl(sum(XMJE00),0) from SF_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (11)) as SSFY00, --手术费 (select nvl(sum(XMJE00),0) from SF_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (9)) as HYFY00, --化验费 (select nvl(sum(XMJE00),0) from SF_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (4,6)) as ZCFY00, --诊察费 (select nvl(sum(XMJE00),0) from SF_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (5,18)) as GCCFY0, --观察床 (select nvl(sum(XMJE00),0) from SF_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (20)) as YCXCLF, --一次性材料费 (select nvl(sum(XMJE00),0) from SF_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 not in (1,2,3,4,5,6,8,9,10,11,14,18,20)) as QTFY00, --其他费用 substr(b.DYRQ00,1,4)||'年'||substr(b.DYRQ00,5,2)||'月'||substr(b.DYRQ00,7,2)||'日' as DYRQ00, --打印日期 f.GHKS00, --挂号科室编码 (select BMMC00 from BM_BMBM00 where BMBH00=f.GHKS00) as GHKSMC, --挂号科室名称 b.CZYXM0, --操作员姓名 (select ZYE000 from SF_BRZHXX where BRID00=a.BRID00) as ZYE000, --预交金余额 a.YBZHYE, --医保账户余额 nvl(a.GRZHZF,0) - nvl(a.YBYL11,0) as YLZHZF, --医疗账户支付 nvl(a.TCJJZF,0) + nvl(a.GWYBZ0,0) as TCZF00, --统筹支付（统筹基金支付+公务员补助） (select XKH000 from BM_YGBM00 where YGBH00=a.JZY000) as XKH000, --胸卡号 (select nvl(sum(JMJE00),0) from SF_FYMX00 where BRID00 = a.BRID00 and JZDH00+0 = a.JZDH00) as YNJMJE,--院内减免金额 e.ICZTMC ICZTMC from SF_JZB000 a,SF_PJSYQK b,BM_BRXXB0 e,SF_BRXXB0 f where b.PLBH00 = 1 and b.PJZT00 <> '1' and b.FZPJH0 = a.PJH000 and a.BRID00 = e.BRID00 and a.MZID00 = f.GHID00(+);