CREATE OR REPLACE VIEW SD_HOSPITAL.VW_SF_ZZFPXX
(
BRID00, FBBH00, YBLB00, BRBLH0, BRXM00, BRXB00, PJH000, FZPJH0, PJXH00,
WBPJLY, PJZT00, WBID00, XJFKJE, ZZFKJE, YHKJE0, PJZTMC, FBMC00, YBLBMC,
TYDWQC, SFXNH0, YBZXLB, MZKYJE, WBLYMC, PJXJJE, PJZZJE, YBZHYE, FKFS00,
MZID00, JZDH00, YBDJH0, MXLSH0, GHH000, YBGHH0, BRGFZH, BRCSRQ, BRNL00,
YBKH00, YBID00, JZKH00, BRZJBH, BRLXDH, JZRQ00, JZSJ00, JZRQSJ, GSJZRQ,
JZY000, JMYY00, CZYBH0, CZYXM0, FPDYRQ, FPDYSJ, DYBZ00, GYJZD0, RQYEAR,
RQMON0, RQDAY0, TSBZBH, TSBZMC, BXLB00, GHKSMC, CZYKS0, JSYYID, ZHXJYE,
ZHZZYE, ZHZYE0, ZHTYJE, WBPJDM, WBPJXH, CJLYBM, BRZJE0, DXZJE0, SHIWAN,
WAN000, QIAN00, BAI000, SHI000, GE0000, JIAO00, FEN000, JZJE00, GFJE00,
HJJE00, JMJE00, GRZHZF, TCJJZF, SBGRZH, SBTCJJ, ZFJE00, XJFK00, ZZFK00,
YBXJZF, YLZFJE, CFDXJE, GWYBZ0, BCKBCS, JJZFE0, FYBJE0, SYBXZF, PMLJ00,
TMLJ00, KJCZY0, KJCZXM, KJPDBM, KJCZKS, KJKSMC, KJRQ00, KJSJ00, KJRQSJ,
DZFPXH, DZFPDM, DZJYM0, DZEWM0, ZZCZY0, ZZCZXM, ZZPDBM, ZZCZKS, ZZKSMC,
ZZFPRQ, ZZFPSJ, ZZRQSJ, ZZFPXH, ZZFPDM, CXCZY0, CXCZXM, CXPDBM, CXCZKS,
CXKSMC, CXRQ00, CXSJ00, CXRQSJ, CHFPXH, CHFPDM, CHJYM0, CHEWM0
)
AS
select
a.BRID00, --病人ID
a.FBBH00, --费别编号
a.YBLB00, --医保类别编号
d.BRBLH0, --病历号
d.BRXM00, --姓名
d.BRXB00, --性别
b.PJH000, --票据号
b.FZPJH0, --分组票据号=B.FZPJH0
b.PJXH00, --发票上的序号,指实际发票上的编号
b.WBPJLY, --外部票据来源 01博思预交金电子凭证 02待扩展...
b.PJZT00, --'0':有效,'1':已作废,'2':分多张发票打印
nvl(b.WBID00,0) as WBID00, --外部发票ID（SF_WBFPXX.WBID00）
b.XJJE00 as XJFKJE, --现金付款,为了方便打印发票而设置的
b.ZZJE00 as ZZFKJE, --转帐付款
b.YHKJE0, --银行卡金额
decode(b.PJZT00,'0','有效','1','冲销','2','拆分打印','3','作废') as PJZTMC, --票据状态
(select FBMC00 from BM_BRFBB0 where FBBH00 = a.FBBH00) as FBMC00, --费别名称
(select YBMC00 from IC_YBBRLB where FBBH00 = a.FBBH00 and YBLB00 = a.YBLB00 and rownum=1) as YBLBMC, --医保类别名称
(select DWMC00 from BM_TYDWB0 where BRID00 = a.BRID00) as TYDWQC, -- 特约单位全称
(select SFXNH0 from IC_YBBRLB where FBBH00=a.FBBH00 and YBLB00 = a.YBLB00 and rownum=1) as SFXNH0, --是否新农合
(select YBZXLB from IC_YBBRLB where FBBH00=a.FBBH00 and YBLB00 = a.YBLB00 and rownum=1) as YBZXLB, --医保中心类别
SF_SF_GETKYJE(a.BRID00) as MZKYJE, --门诊可用余额
decode(b.WBPJLY,'02','博思电子发票','03','博思纸质发票','04','电子发票作废红票(博思)','05','纸质发票作废红票(博思)') as WBLYMC, --外部票据来源名称
(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, --票据上的转账金额
min(a.YBZHYE) as YBZHYE, --医保账户余额
decode(b.XJJE00,0,' ','现金')||decode(b.ZZJE00,0,' ','转帐')||decode(b.YHKJE0,0,' ','银行卡') as FKFS00, --付款方式
max(a.MZID00) as MZID00, --门诊挂号信息表ID
max(a.JZDH00) as JZDH00, --门诊病人结帐单流水号
max(a.YBDJH0) as YBDJH0, --医保结算单据号
max(a.MXLSH0) as MXLSH0, --明细流水号
max(e.GHH000) as GHH000, --挂号号
max(e.YBGHH0) as YBGHH0, --医保挂号号
max(d.BRGFZH) as BRGFZH, --病人公费证号
max(d.BRCSRQ) as BRCSRQ, --病人出生日期
substr(SF_XT_CSRQTONL_EX(max(d.BRCSRQ),max(d.BRCSSJ)),1,10) as BRNL00, --病人年龄
max(d.YBKH00) as YBKH00, --医保卡号
max(d.YBID00) as YBID00, --医保ID
decode(max(d.YBKH00),'0',(select KMH000 from IC_YHXX00 where BRID00 = a.BRID00 and ZT0000='1'),max(d.YBKH00)) as JZKH00, --就诊卡号/社保卡卡号
max(d.BRZJBH) as BRZJBH, --病人身份证号
max(d.BRLXDH) as BRLXDH, --病人联系电话
max(a.JZRQ00) as JZRQ00, --结帐日期
max(a.JZSJ00) as JZSJ00, --结帐时间
max(a.JZRQ00)||max(a.JZSJ00) as JZRQSJ, --结算日期时间
substr(max(a.JZRQ00),1,4)||'-'||substr(max(a.JZRQ00),5,2)||'-'||substr(max(a.JZRQ00),7,2) as GSJZRQ, --格式化结账日期
max(a.JZY000) as JZY000, --结帐操作员编码 F.K=员工表.员工编号
max(a.JMYY00) as JMYY00, --减免原因
max(a.JZY000) as CZYBH0, --打印发票的操作员
max((select ZWXM00 from BM_YGBM00 where YGBH00=a.JZY000)) 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
substr(max(b.DYRQ00),1,4) as RQYEAR, --日期（年）
substr(max(b.DYRQ00),5,2) as RQMON0, --日期（月）
substr(max(b.DYRQ00),7,2) as RQDAY0, --日期（日）
max(a.TSBZBH) as TSBZBH, --特殊病种编号
max((select MC0000 from BM_TSBZB0 i,IC_YBBRLB j where i.YBZXLB = j.YBZXLB and j.YBLB00 = a.YBLB00 and i.BH0000 =a.TSBZBH and rownum=1)) as TSBZMC,--特殊病种名称
max(a.BXLB00) as BXLB00, --保险类别标志：普通/工伤/生育
max((select BMMC00 from BM_BMBM00 where BMBH00 = e.GHKS00)) as GHKSMC, --挂号科室名称
max(a.CZYKS0) as CZYKS0, --结算操作员科室
max((select YYID00 from BM_BMBM00 where BMBH00 = a.CZYKS0)) as JSYYID, --结算操作员科室所属医院ID
max(f.XJYE00) as ZHXJYE, --账户现金余额
max(f.ZZYE00) as ZHZZYE, --账户转账余额
max(f.ZYE000) as ZHZYE0, --账户总余额
max(f.TYJE00) as ZHTYJE, --账户停用金额
max(b.WBPJDM) as WBPJDM, --外部票据代码
max(b.WBPJXH) as WBPJXH, --外部票据序号
max(b.CJLYBM) as CJLYBM, --厂家来源编码 BS:博思，YH:易惠，待扩展。。。
sum(a.BRZJE0) as BRZJE0, --病人费用总金额
substr(SP_BM_RMB000(sum(a.BRZJE0)),1,100) as DXZJE0, --大写病人费用总金额
substr(SP_BM_RMBDX0(sum(a.BRZJE0)),1,1) as SHIWAN, --十万位金额大写
substr(SP_BM_RMBDX0(sum(a.BRZJE0)),2,1) as WAN000, -- 万位金额大写
substr(SP_BM_RMBDX0(sum(a.BRZJE0)),3,1) as QIAN00, -- 千位金额大写
substr(SP_BM_RMBDX0(sum(a.BRZJE0)),4,1) as BAI000, -- 百位金额大写
substr(SP_BM_RMBDX0(sum(a.BRZJE0)),5,1) as SHI000, -- 十位金额大写
substr(SP_BM_RMBDX0(sum(a.BRZJE0)),6,1) as GE0000, -- 个位金额大写
substr(SP_BM_RMBDX0(sum(a.BRZJE0)),7,1) as JIAO00, -- 角位金额大写
substr(SP_BM_RMBDX0(sum(a.BRZJE0)),8,1) as FEN000, -- 分位金额大写
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, --自付金额(医保返回)
sum(a.XJFKJE) as XJFK00, --现金付款金额
sum(a.ZZFKJE) as ZZFK00, --转帐付款金额
sum(a.YBXJZF) as YBXJZF, --医保现金支付
sum(a.YLZFJE) as YLZFJE, --乙类自付金额
sum(a.CFDXJE) as CFDXJE, --超封顶线金额
sum(a.GWYBZ0) as GWYBZ0, --公务员补助
sum(a.BCKBCS) as BCKBCS, --就诊次数
sum(a.JJZFE0) as JJZFE0, --保健基金支付
sum(a.FYBJE0) as FYBJE0, --非医保费
sum(a.SYBXZF) as SYBXZF, --商业保险支付
sum(a.PMLJ00) as PMLJ00, --普门累计
sum(a.TMLJ00) as TMLJ00, --特门累计
max(g.KJCZY0) as KJCZY0, --开具电子发票操作员BM_YGBM00.YGBH00
max((select ZWXM00 from BM_YGBM00 where YGBH00 = g.KJCZY0)) as KJCZXM, --
max(g.KJPDBM) as KJPDBM, --开具开票点编码
max(g.KJCZKS) as KJCZKS, --开具操作员科室BM_BMBM00.BMBH00
max((select BMMC00 from BM_BMBM00 where BMBH00 = g.KJCZKS)) as KJKSMC, --
max(g.KJRQ00) as KJRQ00, --开具日期
max(g.KJSJ00) as KJSJ00, --开具时间
max(g.KJRQ00)||max(g.KJSJ00) as KJRQSJ, --
max(g.DZFPXH) as DZFPXH, --电子发票序号
max(g.DZFPDM) as DZFPDM, --电子发票代码
max(g.DZJYM0) as DZJYM0, --电子校验码
max(g.DZEWM0) as DZEWM0, --电子二维码
max(g.ZZCZY0) as ZZCZY0, --纸质发票操作员BM_YGBM00.YGBH00
max((select ZWXM00 from BM_YGBM00 where YGBH00 = g.ZZCZY0)) as ZZCZXM, --
max(g.ZZPDBM) as ZZPDBM, --纸质开票点编码
max(g.ZZCZKS) as ZZCZKS, --纸质发票操作员科室BM_BMBM00.BMBH00
max((select BMMC00 from BM_BMBM00 where BMBH00 = g.ZZCZKS)) as ZZKSMC, --
max(g.ZZFPRQ) as ZZFPRQ, --纸质发票日期
max(g.ZZFPSJ) as ZZFPSJ, --纸质发票时间
max(g.ZZFPRQ)||max(g.ZZFPSJ) as ZZRQSJ, --
max(g.ZZFPXH) as ZZFPXH, --纸质发票序号
max(g.ZZFPDM) as ZZFPDM, --纸质发票代码
max(g.CXCZY0) as CXCZY0, --冲销操作员BM_YGBM00.YGBH00
max((select ZWXM00 from BM_YGBM00 where YGBH00 = g.CXCZY0)) as CXCZXM, --
max(g.CXPDBM) as CXPDBM, --冲销开票点编码
max(g.CXCZKS) as CXCZKS, --冲销操作员科室BM_BMBM00.BMBH00
max((select BMMC00 from BM_BMBM00 where BMBH00 = g.CXCZKS)) as CXKSMC, --
max(g.CXRQ00) as CXRQ00, --冲销日期
max(g.CXSJ00) as CXSJ00, --冲销时间
max(g.CXRQ00)||max(g.CXSJ00) as CXRQSJ, --
max(g.CHFPXH) as CHFPXH, --冲红发票序号
max(g.CHFPDM) as CHFPDM, --冲红发票代码
max(g.CHJYM0) as CHJYM0, --冲红校验码
max(g.CHEWM0) as CHEWM0 --冲红二维码
from SF_JZB000 a,SF_PJSYQK b,BM_BRXXB0 d,SF_BRXXB0 e,SF_BRZHXX f,SF_WBFPXX g
where a.BRID00 = d.BRID00
and a.BRID00 = f.BRID00
and b.PLBH00(+) = 1
and nvl(b.PJZT00,'0') = '0'
and a.HJJE00 > 0
and a.MZID00 = e.GHID00(+)
and a.PJH000 = b.FZPJH0
and nvl(b.WBID00,0) = g.WBID00(+)
and not exists (select 1 from SF_JZB000 where BRID00=a.BRID00 and CXJZDH=a.JZDH00)
group by a.BRID00,a.FBBH00,a.YBLB00,d.BRBLH0,d.BRXM00,d.BRXB00,b.PJH000,b.FZPJH0,b.PJXH00,b.WBPJLY,b.PJZT00,nvl(b.WBID00,0),b.XJJE00,b.ZZJE00,b.YHKJE0
/