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
;
