create or replace view vw_sf_brjfxx as
select
  a.DJH000,                                                             --单据流水号(与门诊病人费用表共用一个单据号)
  a.BRID00,                                                             --病人ID号
  nvl(f.XM0000,e.BRXM00) as BRXM00,                                     --姓名,不取BM_BRXXB0.BRXM00,考虑有的是现金病人,在BM_BRXXB0中没数据的情况
  nvl(f.XB0000,e.BRXB00) as BRXB00,                                     --性别
  e.BRCSRQ,                                                             --出生日期
  e.BRBLH0,                                                             --门诊病人病历号,或称为门诊号
  a.JFLBID,                                                             --帐户类别ID
  (select JFLBMC from BM_BRJFLB where JFLBID = a.JFLBID) as MZZHMC ,    --门诊病人帐户名称
  a.JFJE00,                                                             --缴费金额
  substr(SP_BM_RMB000(a.JFJE00),1,100) as DXJFJE,                       --大写缴费金额
  a.ZFFS00,                                                             --支付方式编码
  a.ZFFSMC,                                                             --支付方式名称
  decode(a.ZFFS00,'1',a.JFJE00,'') as XJJE00 ,                          --现金金额
  decode(a.ZFFS00,'2',a.JFJE00,'') as ZZJE00 ,                          --转帐金额
  decode(a.ZFFS00,'8',a.JFJE00,'') as YHKJE0 ,                          --银行卡金额
  a.ZPH000,                                                             --支票或银行本票号
  a.ZPDWMC,                                                             --出票单位/出票人
  a.ZPZH00,                                                             --出票单位帐号
  a.ZPKHYH,                                                             --出票的开户银行
  a.ZPCPRQ,                                                             --出票日期
  a.ZPCPYY,                                                             --出票原因
  a.ZPLX00,                                                             --票据类型(如:信用卡类别)
  a.ZPSPR0,                                                             --支票审批人
  a.ZPLXR0,                                                             --支票联系人
  a.ZPLXFS,                                                             --支票联系人的联系方式
  a.PJH000,                                                             --打印的预交金票据上的内部号
  a.JFRQ00,                                                             --操作日期
  a.JFSJ00,                                                             --操作时间
  substr(a.JFRQ00,1,4) as RQNIAN,                                       --缴费日期--年
  substr(a.JFRQ00,5,2) as RQYUE0,                                       --缴费日期--月
  substr(a.JFRQ00,7,2) as RQRI00,                                       --缴费日期--日
  substr(a.JFRQ00,1,4)||'年'||substr(a.JFRQ00,5,2)||'月'||substr(a.JFRQ00,7,2)||'日'||substr(a.JFSJ00,1,2)||'时'||substr(a.JFSJ00,4,2)||'分'||substr(a.JFSJ00,7,2)||'秒' as RQ0000,
  a.JFCZY0,                                                             --操作员编号
  a.CZYXM0,                                                             --操作员姓名
  a.BRYE00,                                                             --病人余额
  a.BRYE00 - a.JFJE00 as JFQYE0,                                        --病人交费前余额 
  a.BRYE00 - (select nvl(TYJE00,0) from SF_BRZHXX g where g.BRID00=e.BRID00 and g.JFLBID=1 and rownum=1) - a.JFJE00 as JFQYE0_EX, --病人交费前余额（另）
  a.JFBZ00,                                                             --交费标志,'0'交预交金,'1'退预交金
  decode(a.JFBZ00,'0','交费','1','退费','2','作废','未知') as JFBZMC,   --交费标志名称
  a.JZDH00,                                                             --结算单号
  a.JKBID0,                                                             --缴款单号
  f.PJXH00,                                                             --票据序列号,
  f.PLMC00,                                                             --票据类别名称
  trim(SP_BM_RMB000(a.JFJE00)||'('||'￥'||a.JFJE00||')') as DXXJE0,     --大小写
  decode(substr(SP_BM_RMB000(a.JFJE00),1,4),'(负数)',substr(SP_BM_RMB000(a.JFJE00),5)||'('||'￥'||substr(a.JFJE00,2)||')',SP_BM_RMB000(a.JFJE00)||'('||'￥'||a.JFJE00||')') as DXJFE1, --大写缴费金额
  (select nvl(ZYE000,0) from SF_BRZHXX g where g.BRID00=e.BRID00 and g.JFLBID=1 and rownum=1 ) as BRYE01,
  (select nvl(ZYE000,0) - nvl(TYJE00,0) from SF_BRZHXX g where g.BRID00=e.BRID00 and JFLBID=1 and rownum=1) as  BRYE02,
  f.CZYKS0,
  (select x.KMH000 from IC_YHXX00 x where a.BRID00=x.BRID00 and x.ZT0000='1' and rownum=1 ) as KMH000,
  (select XKH000 from BM_YGBM00 where YGBH00=a.JFCZY0) as XKH000,
  decode(a.JFBZ00, '0','收','1','退','2','作废')||'预交金'||decode(substr(SP_BM_RMB000(a.JFJE00),1,4),'(负数)',substr(SP_BM_RMB000(a.JFJE00),5),SP_BM_RMB000(a.JFJE00))||'('||'￥'||a.JFJE00||')'||'('||a.ZFFSMC||')' as DXJFE2,
  decode(f.PJZT00,'0',f.PJXH00,(select nvl(max(PJXH00),f.PJXH00) from SF_PJSYQK where FZPJH0=a.PJH000 and PJZT00='0')) as FZPJXH,  --最新票据序号             
  nvl((select decode(SFSKYJ,'0',0,'1',ICKJE0) from IC_YHXX00 where BRID00=a.BRID00 and ZT0000=1 and rownum=1),0) as ICKYJ0, ---IC卡押金
  SF_XT_CSRQTONYR(e.BRCSRQ,sysdate) as CSRQ01,   --出生年月日
  (select YYID00 from BM_BMBM00 where BMBH00 = f.CZYKS0 and rownum=1) as YYID00,   --医院ID
  h.zye000
from  SF_BRJFB0 a,BM_BRXXB0 e,SF_PJSYQK f,SF_BRZHXX h
where a.BRID00 = e.BRID00(+)   --考虑现金病人没有BRID00
  and a.PJH000 = f.PJH000(+)   --考虑医保病人挂号,挂号附加费没有打印在发票中的情况
  and a.brid00 = h.brid00(+)
;