create or replace view vw_sf_fphb00 as select a.BRID00, --病人ID d.BRBLH0, --病历号 b.XM0000 as BRXM00, --姓名 b.XB0000 as BRXB00, --性别 a.FBBH00, --费别编号 (select FBMC00 from BM_BRFBB0 where FBBH00 = a.FBBH00) as FBMC00, --费别名称 b.PJH000, --票据号 a.PJH000 as FZPJH0, --分组票据号=B.FZPJH0 b.PJXH00 as FPBH00, --发票上的序号,指实际发票上的编号 max(a.JZDH00) as JZDH00, --门诊病人结帐单流水号 max(e.GHH000) as GHH000, --挂号号 max(e.YBGHH0) as YBGHH0, --医保挂号号 max(d.BRGFZH) as BRGFZH, --病人个人账户支付 max(d.YBKH00) as YBKH00, --医保卡号 max(d.YBID00) as YBID00, --医保ID max(d.TYDWMC) as TYDWMC, --特约单位名称 max(d.BRZTQK) as BRZTQK, --病人职退情况 max(a.MZID00) as MZID00, --门诊挂号信息表ID sum(a.BRZJE0) as BRZJE0, --病人费用总金额 B.XJJE00+B.ZZJE00+B.YHKJE0不一定等于A.BRZJE0 sum(a.JZJE00) as JZJE00, --记帐金额 sum(a.GFJE00) as GFJE00, --公费金额 sum(a.HJJE00) as HJJE00, --合计应收金额=自付金额-减免金额=(SF_BRFY00的自费金额) sum(a.JMJE00) as JMJE00, --减免金额 sum(a.GRZHZF) as GRZHZF, --个人医疗帐户支付(医保返回) sum(a.TCJJZF) as TCJJZF, --统筹基金支付(医保返回) sum(a.SBGRZH) as SBGRZH, --商保个人帐户 sum(a.SBTCJJ) as SBTCJJ, --商保统筹基金 sum(a.ZFJE00) as ZFJE00, --自付金额(医保返回) max(a.JZRQ00) as JZRQ00, --结帐日期 max(a.JZSJ00) as JZSJ00, --结帐时间 sum(b.XJJE00) as XJFKJE, --现金付款,为了方便打印发票而设置的 sum(b.ZZJE00) as ZZFKJE, --转帐付款 sum(b.YHKJE0) as YHKJE0, --银行卡金额 max(a.JZY000) as JZY000, --结帐操作员编码 F.K=员工表.员工编号 max(a.JMYY00) as JMYY00, --减免原因 max(b.CZY000) as CZYBH0, --打印发票的操作员 max(b.CZYXM0) as CZYXM0, --打印发票的操作员姓名 max(b.DYRQ00) as FPDYRQ, -- max(b.DYSJ00) as FPDYSJ, --发票打印时间 max(b.SFDY00) as DYBZ00, --打印标志,'0':发票未打印,'1':发票已打印 max(b.GYJZD0) as GYJZD0, --公医记账单ID max(b.PJZT00) as PJZT00, --'0':有效,'1':已作废,'2':分多张发票打印 min(a.YBZHYE) as YBZHYE, --医保账户余额 max(a.TSBZBH) as TSBZBH, --特殊病种编号 decode(max(a.TSBZBH),'','','特殊病种') as TSBZBS, --特殊病种标识 (select nvl(round(sum(QZFJE0+(XMJE00-QZFJE0)*ZFBL00),2),round(b.XJJE00,2)) from SF_PJSYMX where PJH000 = b.PJH000) as PJXJJE,--票据上的现金金额 (select nvl(round(sum((XMJE00-QZFJE0)*(1-ZFBL00)),2),round(b.ZZJE00,2)) from SF_PJSYMX where PJH000 = b.PJH000) as PJZZJE,--票据上的转账金额 max(b.BDBZ00) as BDBZ00, --是否补打 decode(max(b.FPTTMC),'',b.XM0000,max(b.FPTTMC)) as FPTTMC, --发票抬头名称 sum(a.SBZFE0) as SBZFE0, --商保支付额 sum(a.BJZFE0) as BJZFE0, --保健支付额 decode(max(a.YBDJH0),'0',to_char(b.PJH000),to_char(b.PJH000)||'('||decode(count(1),1,max(a.YBDJH0),substr(SF_SF_GETYBDJH0(b.FZPJH0),1,100))||')') as PJYBDJ, max(e.GHRQ00) as GHRQ00, min(a.TMLJ00) as TMLJ00, min(a.MSTYE0) as MSTYE0, replace(max(a.BXLB00),'普通','') as XSXX00, sum(a.YBYL13) as YBYL13, --共济账户支付 sum(a.YBYL24) as YBYL24, --大额医疗费用报销金额(sbzfje) max(a.TCID00) as TCID00, --套餐ID decode(max(a.TCID00),null,'套餐外','套餐内') as TCNWBZ, --套餐内、外标志 sum(a.YBYL05) as YBYL05, --医院支付 sum(a.YBYL46) as JZFP01, --第一道精准扶贫 sum(a.YBYL47) as JZFP02, --第二道精准扶贫 sum(a.YBYL48) as JZFP03, --综合救助 sum(a.YBYL50) as CSJZ00, --慈善救助 sum(a.YBYL01) as YBYL01, --城乡居民医疗救助 case when a.FBBH00=1 then (select max(KMH000) from IC_YHXX00 where BRID00=a.BRID00) else max(d.YBKH00) end as KMH000, --卡面号 (select YBZXLB from IC_YBBRLB where FBBH00 = a.FBBH00 and YBLB00=a.YBLB00 and rownum=1) as YBZXLB, --医保中心类别 substr(SF_SF_GETPJZCDYRQ(b.FZPJH0),1,8) as FRDYRQ, --发票最初打印日期 max(SF_SF_GETKYJE(d.BRID00)) as MZKYJE, --门诊可用余额 SF_SF_GETJSFYRQ(2,a.PJH000,1) FYQZRQ --票据对应的费用起始截止日期 from SF_JZB000 a,SF_PJSYQK b,BM_BRXXB0 d,SF_BRXXB0 e where b.PLBH00 = 1 and b.PJZT00 <> '1' and a.PJH000 = b.FZPJH0 and a.BRID00 = d.BRID00 and a.MZID00 = e.GHID00(+) group by a.BRID00,d.BRBLH0,b.XM0000,b.XB0000,a.FBBH00,a.YBLB00,b.PJH000,a.PJH000,b.PJXH00,b.FZPJH0,b.XJJE00,b.ZZJE00 ;