-- Start of DDL Script for View SD_HOSPITAL.VW_SF_FPZM00
-- Generated 11-十一月-2013 13:57:28 from SD_HOSPITAL@XMTSFCYY

CREATE OR REPLACE VIEW vw_sf_fpzm00 (
   brid00,
   brxm00,
   ybid00,
   ybkh00,
   ghh000,
   pjh000,
   jzdh00,
   jzje00,
   gfje00,
   hjje00,
   jmje00,
   grzhzf,
   tcjjzf,
   sbgrzh,
   sbtcjj,
   zfje00,
   jzrq00,
   jzsj00,
   xjfkje,
   zzfkje,
   yhkje0,
   ybxjzf,
   ylzfje,
   cfdxje,
   gwybz0,
   bckbcs,
   jjzfe0,
   fybje0,
   sybxzf,
   sbzfe0,
   bjzfe0,
   ybyl01,
   ybyl02,
   ybyl03,
   ybyl04,
   ybyl05,
   ybyl06,
   ybyl07,
   ybyl08,
   ybyl09,
   ybyl10,
   ybyl11,
   ybyl12,
   ybyl13,
   ybyl14,
   ybyl15,
   ybyl16,
   ybyl17,
   ybyl18,
   ybyl19,
   ybyl20,
   ybyl21,
   ybyl22,
   ybyl23,
   ybyl24,
   ybyl25,
   ybyl26,
   ybyl27,
   ybyl28,
   ybyl29,
   ybyl30,
   zye000,
   xyfy00,
   zlfy00,
   syfy00,
   zcyfy0,
   fsfy00,
   zcfy00,
   zyfy00,
   ssfy00,
   gccfy0,
   jcfy00,
   hyfy00,
   ghfy00,
   qtfy00,
   dyrq00,
   ghks00,
   ghksmc,
   czyxm0 )
AS
select
A.BRID00,--病人id
E.BRXM00,--病人姓名 
E.YBID00,--医保id
E.YBKH00,--医保卡号
F.GHH000,--挂号号
A.PJH000,--票据号 
A.JZDH00,--结账单号
A.JZJE00,--记帐金额
A.GFJE00,--公费金额
A.HJJE00,--合计应收金额      
A.JMJE00,--减免金额
A.GRZHZF,--个人医疗帐户支付(医保返回)
A.TCJJZF,--统筹基金支付(医保返回)
A.SBGRZH,--商保个人帐户
A.SBTCJJ,--商保统筹基金
A.ZFJE00,--自付金额(医保返回)
A.JZRQ00,--结帐日期
A.JZSJ00,--结帐时间
B.XJJE00,--现金付款,为了方便打印发票而设置的
B.ZZJE00,--转帐付款
B.YHKJE0,--银行卡金额
A.YBXJZF,--医保现金支付
A.ylzfje,--乙类自付金额
A.cfdxje,--超封顶线金额
A.gwybz0,--公务员补助
A.bckbcs,--就诊次数
A.jjzfe0,--保健基金支付
A.FYBJE0,--非医保费
A.SYBXZF,--商业保险支付
A.SBZFE0,--商保基金支付(省属三家市属九家医疗机构) 
A.BJZFE0,--商保基金支付(省属三家市属九家医疗机构) 
A.YBYL01,
A.YBYL02,
A.YBYL03,
A.YBYL04,
A.YBYL05,
A.YBYL06,
A.YBYL07,
A.YBYL08,
A.YBYL09,
A.YBYL10,
A.YBYL11,--健康账户支付
A.YBYL12,--健康账户余额
A.YBYL13,--共济账户支付
nvl(A.YBYL14,0)YBYL14, --共济账户余额
nvl(A.YBYL15,0)YBYL15, --健康账户支付医保费用
nvl(A.YBYL16,0)YBYL16, --健康账户支付非医保费用
nvl(A.YBYL17,0)YBYL17, --减免金额(医保返回不包含统筹里面)
nvl(A.YBYL18,0)YBYL18, 
nvl(A.YBYL19,0)YBYL19, 
nvl(A.YBYL20,0)YBYL20, 
nvl(A.YBYL21,0)YBYL21, 
nvl(A.YBYL22,0)YBYL22, 
nvl(A.YBYL23,0)YBYL23, 
nvl(A.YBYL24,0)YBYL24,
nvl(A.YBYL25,0)YBYL25, 
nvl(A.YBYL26,0)YBYL26, 
nvl(A.YBYL27,0)YBYL27, 
nvl(A.YBYL28,0)YBYL28, 
nvl(A.YBYL29,0)YBYL29, 
nvl(A.YBYL30,0)YBYL30,
(select zye000 from sf_brzhxx bb where bb.brid00=a.brid00) zye000,--预交金余额
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 in (1))XYFY00, --西药费
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 in (6))ZLFY00, --治疗费
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 in (11))SYFY00, --输氧费
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 in (2))ZCYFY0,  --中成药
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 in (7))FSFY00,  --放射费
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 in (13))ZCFY00,--诊察费
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 in (3))ZYFY00,  --中药费
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 in (8))SSFY00,  --手术费
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 in (15))GCCFY0, --观察床
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 in (5))JCFY00,  --检查费
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 in (9))HYFY00,  --化验费
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 in (4))GHFY00,  --挂号费
(select nvl(SUM(XMJE00),0) from  SF_JZMX00 AA WHERE AA.jzdh00=A.jzdh00 AND XMBH00 not in (1,2,11,6,7,13,3,8,15,5,9,4))QTFY00, --其他费用
substr(B.DYRQ00,1,4)||'年'||substr(B.DYRQ00,5,2)||'月'||substr(B.DYRQ00,7,2)||'日' DYRQ00,
F.GHKS00, --挂号科室编码
(select bmmc00 from bm_bmbm00 FF where FF.BMBH00=f.GHKS00) GHKSMC,  --挂号科室名称
B.CZYXM0 --操作员姓名
from  SF_PJSYQK B,SF_JZB000 A,BM_YGBM00 D,BM_BRXXB0 E,SF_BRXXB0 F
where  PLBH00 = 1  --门诊发票类型
  and  B.PJZT00 <> '1'
  and  B.FZPJH0 = A.PJH000
  and  A.BRID00 = E.BRID00
  and  A.MZID00 = F.GHID00(+)
  and  B.CZY000 = D.YGBH00
/


-- End of DDL Script for View SD_HOSPITAL.VW_SF_FPZM00