prompt 137  住院纸质发票信息视图     VW_ZY_ZZFPXX
create or replace view VW_ZY_ZZFPXX
as
-- MODIFICATION HISTORY
-- Person     Date         Comments
-- jlg        2020.12.14   created 住院纸质发票信息视图 脱胎于VW_ZY_FPXX00 ZYSF9-20201214-002
-- jlg        2021.02.03   增加SJCYRQ字段；ZYTS00的计算由原来的CYRQ00改为SJCYRQ ZYSF9-20210126-003
-- jlg        2021.05.26   CDRG总金额组成增加CBYPF0字段 ZYSF9-20210524-005
-- jlg        2022.02.17   增加BJTDJH,BJTJE0,BJKJE0,BTKJE0字段 	ZYSF9EX-20220117-002
-- chenrenhai 2023.06.07   增加函数SF_MZ_GETICKXX，获取默认的卡面号
select
  b.ZYH000,                                                                                  --住院号
  b.BRRYCS,                                                                                  --住院次数
  substr(SF_ZY_GETYEXMXS(c.XM0000,a.YEXM00),1,50) as XM0000,                                 --姓名 
  c.XB0000,                                                                                  --性别
  a.YEXM00,                                                                                  --婴儿姓名
  b.RYCWH0,                                                                                  --住院床位号
  decode(b.YBKH00,'0',(select KMH000 from IC_YHXX00 where ICKH00=SF_MZ_GETICKXX(a.BRID00) and ZT0000='1'),b.YBKH00) as JZKH00,  --就诊卡号/社保卡卡号  
  decode(b.YBKH00,'0',decode(a.YBDJH0,'0','0',(select nvl(max(KMH000),'0') from IC_YHXX00 where BRID00=a.BRID00 and ZT0000='1' and KLDH00='YB-SBK')),b.YBKH00) as YBKH00, --医保卡号
  (select c.ICKH00 from IC_YHXX00 c where c.BRID00=a.BRID00 and c.ZT0000='1' and rownum = 1) as ICKH00,     --病人IC卡号
  (select d.YBID00 from BM_BRXXB0 d where d.BRID00=a.BRID00 and rownum = 1) as YBID00,                      --病人医保ID  
  b.DQBQ00,                                                                                  --当前病人住院病区
  (select BMMC00 from BM_BMBM00 where BMBH00 = b.DQBQ00) as DQBQMC,                          --当前病人住院病区名称
  b.DQKS00,                                                                                  --当前病人住院科室
  (select BMMC00 from BM_BMBM00 where BMBH00 = b.DQKS00) as DQKSMC,                          --当前病人住院科室名称
  b.RYRQ00,                                                                                  --入院日期
  b.CYDJRQ,                                                                                  --出院登记日期
  b.CYRQ00,                                                                                  --出院日期 
  b.SJCYRQ,                                                                                  --实际出院日期 
  greatest(1,to_date(b.SJCYRQ,'YYYYMMDD')-to_date(b.RYRQ00,'YYYYMMDD')) as ZYTS00,           --住院天数  
  a.FFBZBM  ,                                                                                --付费病种编码
 (select BZMC00 from BM_BZSFBZ where FBBH00 = a.FBBH00 and YBZXLB = a.YBZXLB and FFBZBM = a.FFBZBM and rownum=1) as FFBZMC, --付费病种名称  
  decode(a.FFBZBM,null,'','单') as FFBZBZ,                                                   --付费单病种病人标识      
  a.JZDH00,                                                                                  --住院病人结帐单流水号(SQ_ZY_JZB000_JZDH00)
  a.CXJZDH,                                                                                  --冲销结账单号
  a.YBDJH0,                                                                                  --医保返回的单据号
  a.BRID00,                                                                                  --病人ID
  a.ZYID00,                                                                                  --住院ID,F.K=ZY.BRXXB0.ZYID00
  a.FBBH00,                                                                                  --费别编号
  (select FBMC00 from BM_BRFBB0 where FBBH00 = a.FBBH00) as FBMC00,                          --费别名称 
  substr(SF_ZY_GETFBXX00(a.FBBH00,a.YBZXLB,a.YBLB00,a.YBBRLB),1,100) as FBXX00,              --费别信息
  a.YBZXLB,                                                                                  --医保大中心
  a.YBLB00,                                                                                  --医保分中心
  a.YBBRLB,                                                                                  --医保病人类
  decode(c.SFBCF0,'0',a.HJJE00,c.HJJE00) as HJJE00,                                          --合计应收金额
  substr(SP_BM_RMB000(decode(c.SFBCF0,'0',a.HJJE00,c.HJJE00)),1,100) as DXHJJE,              --大写合计金额
  decode(c.SFBCF0,'0',a.BRZJE0,c.HJJE00) as BRZJE0,                                          --病人费用总金额(拟作废)
  decode(c.SFBCF0,'0',a.BCBXF0,c.HJJE00) as BCBXF0,                                          --住院医疗费总额(发给医保)
  decode(c.SFBCF0,'0',a.ZFJE00,c.ZFJE00) as ZFJE00,                                          --自付
  decode(c.SFBCF0,'0',a.JZJE00,c.JZJE00) as JZJE00,                                          --记帐金额
  decode(c.SFBCF0,'0',a.GFJE00,0) as GFJE00,                                                 --公费金额，减免时用
  decode(c.SFBCF0,'0',a.JMJE00,c.JMJE00) as JMJE00,                                          --优惠金额
  decode(c.SFBCF0,'0',a.GRZHZF,c.GRZHZF) as GRZHZF,                                          --个人医疗帐户支付
  decode(c.SFBCF0,'0',a.TCJJZF,c.TCJJZF) as TCJJZF,                                          --统筹基金支付
  decode(c.SFBCF0,'0',a.YBQTZF,c.YBQTZF) as YBQTZF,                                          --医保其他支付
  decode(sign(nvl(decode(c.SFBCF0,'0',a.YBQTZF,c.YBQTZF),0)),0,'','医保其他支付：'||to_char(decode(c.SFBCF0,'0',a.YBQTZF,c.YBQTZF))) as YBQTEX,--医保其他支付（扩展显示）
  decode(c.SFBCF0,'0',a.MZBZJE,c.MZBZJE) as MZBZJE,                                          --民政补助金额(不含于统筹基金支付的)    
  decode(c.SFBCF0,'0',a.DLSBJE,c.DLSBJE) as DLSBJE,                                          --独立商保支付金额(不含于统筹基金支付的)
  decode(c.SFBCF0,'0',a.DLDBJJ,c.DLDBJJ) as DLDBJJ,                                          --商保统筹基金支付金额(不包含于统筹基金支付)  
  a.SBGRZH,                                                                                  --商保个人账户
  a.SBTCJJ,                                                                                  --商保统筹基金
  decode(c.SFBCF0,'1',0,a.CYYSK0) as CYYSK0,                                                 --出院应收款
  decode(c.SFBCF0,'1',0,a.CYYTK0) as CYYTK0,                                                 --出院应退款
  decode(c.SFBCF0,'1',0,a.YSZJE0) as YSZJE0,                                                 --预交金总额(现金)
  decode(c.SFBCF0,'1',0,a.ZZZJE0) as ZZZJE0,                                                 --转账预交金总额
  decode(c.SFBCF0,'1',0,a.YSZJE0 + a.ZZZJE0) as YJJZE0,                                      --预交金总额(现金+转账)
  decode(c.SFBCF0,'1',0,a.XJYE00) as XJYE00,                                                 --现金余额
  decode(c.SFBCF0,'1',0,a.ZZYE00) as ZZYE00,                                                 --转账余额
  decode(c.SFBCF0,'1',0,a.XJYE00 + a.ZZYE00) as XJZZYE,                                      --现金+转账余额 
  decode(c.SFBCF0,'1',0,decode(sign(a.XJYE00),-1,0,a.XJYE00)) as XJTKJE,                     --现金退款金额
  decode(c.SFBCF0,'1',0,decode(sign(a.ZZYE00),-1,0,a.ZZYE00)) as ZZTKJE,                     --转账退款金额
  decode(c.SFBCF0,'1',0,decode(sign(a.XJYE00),-1,-a.XJYE00,0)) as XJBJJE,                    --现金补交金额
  decode(c.SFBCF0,'1',0,decode(sign(a.ZZYE00),-1,-a.ZZYE00,0)) as ZZBJJE,                    --转账补交金额
  decode(c.SFBCF0,'1','0',decode(sign(a.XJYE00),1,'(退)'||to_char(a.XJYE00),'0')) as XJTKJE_EX,                   --现金退款金额(中文显示)
  decode(c.SFBCF0,'1','0',decode(sign(a.ZZYE00),1,'(退)'||to_char(a.ZZYE00),'0')) as ZZTKJE_EX,                   --转账退款金额(中文显示)
  decode(c.SFBCF0,'1','0',decode(sign(a.XJYE00),-1,'(补)'||to_char(-a.XJYE00),'0')) as XJBJJE_EX ,                --现金补交金额(中文显示)
  decode(c.SFBCF0,'1','0',decode(sign(a.ZZYE00),-1,'(补)'||to_char(-a.ZZYE00),'0')) as ZZBJJE_EX ,                --转账补交金额(中文显示)
  decode(c.SFBCF0,'1',0,greatest(a.XJYE00+a.ZZYE00+nvl(a.YHTKJE,0)+nvl(a.WXTK00,0)+nvl(a.ZFBTK0,0),0)) as JSTKJE,        --结算退款金额
  decode(c.SFBCF0,'1',0,greatest(-(a.XJYE00+a.ZZYE00+nvl(a.YHTKJE,0)+nvl(a.WXTK00,0)+nvl(a.ZFBTK0,0)),0)) as JSBJJE,     --结算补交金额    
  decode(c.SFBCF0,'1',0,a.XJFKJE) as XJFKJE,                                                 --现金付款,为了方便打印发票而设置的应退还病人的现金余额
  decode(c.SFBCF0,'1',0,a.ZZFKJE) as ZZFKJE,                                                 --转帐付款,为了方便打印发票而设置的应退还病人的转帐余额
  decode(c.SFBCF0,'1',0,a.XJFKJE + a.ZZFKJE) as JSFKJE,                                      --结算付款金额（现金+转账） 
  decode(c.SFBCF0,'1',0,a.YHTKJE) as YHTKJE,                                                 --银行退款金额
  decode(c.SFBCF0,'1',0,a.WXTK00) as WXTK00,                                                 --微信退款金额
  decode(c.SFBCF0,'1',0,a.ZFBTK0) as ZFBTK0,                                                 --支付宝退款金额 
  decode(c.SFBCF0,'1','',decode(sign(a.YHTKJE),1,'退POS:'||to_char(a.YHTKJE)||'元',''))    as YHTKJE_EX,                   --银行退款金额(中文显示)
  decode(c.SFBCF0,'1','',decode(sign(a.WXTK00),1,'退微信:'||to_char(a.WXTK00)||'元',''))   as WXTK00_EX,                   --微信退款金额(中文显示)
  decode(c.SFBCF0,'1','',decode(sign(a.ZFBTK0),1,'退支付宝:'||to_char(a.ZFBTK0)||'元','')) as ZFBTK0_EX,                   --支付宝退款金额(中文显示)        
  decode(c.SFBCF0,'1',0,greatest(a.XJYE00+nvl(a.YHTKJE,0)+nvl(a.WXTK00,0)+nvl(a.ZFBTK0,0),0)) as XJTKHJ,                   --现金退款合计（含微信、支付宝退款）
  a.JZZT00,                                                                                  --结算状态0:已正常结算  1：已取消结算
  decode(a.CXJZDH,0,decode(a.JZZT00,'0','正常','被冲销'),'冲销') as JZZTMC,                  --结账状态名称
  decode(a.CXJZDH,0,'正常','冲销') as CXZTMC,                                                --冲销状态名称
  a.FPHSBZ,                                                                                  --发票回收标志,0:未回收,1:已回收
  a.JZRQ00,                                                                                  --结账日期
  a.JZSJ00,                                                                                  --结账时间
  a.JZRQ00||' '||JZSJ00 as JZRQSJ,                                                           --结账日期时间
  substr(JZRQ00,1,4) as JZNIAN,                                                              --结账日期-年
  substr(JZRQ00,5,2) as JZYUE0,                                                              --结账日期-月
  substr(JZRQ00,7,2) as JZRI00,                                                              --结账日期-日
  a.JZY000,                                                                                  --结帐操作员编码  F.K=BM_YGBM00.YGBH00
  (select ZWXM00 from BM_YGBM00 where YGBH00 = a.JZY000) as JZYXM0,                          --结账员姓名
  a.CZYKS0,                                                                                  --操作员科室
  a.JZBQH0,                                                                                  --结账病区号(无用)
  a.JMYY00,                                                                                  --减免原因
  a.YBZYH0,                                                                                  --医保住院号
  a.BCKBCS,                                                                                  --病人住院次数
  a.TSBZBH,                                                                                  --特殊病种编号
  (select MC0000 from BM_TSBZB0 where YBZXLB = a.YBZXLB and BH0000 = a.TSBZBH) as TSBZMC,    --特殊病种名称  
  decode(a.TSBZBH,'','','特殊病种') TSBZBZ,                                                 --特殊病种标识
  a.DBZBM0,                                                                                  --单病种ICD10编码
  (select DBZMC0 from BM_DBZDEB where YBZXLB = a.YBZXLB and DBZBM0 = a.DBZBM0 and rownum=1) as DBZMC0,--单病种名称  
  (select YBHDJE from BM_DBZDEB where YBZXLB = a.YBZXLB and DBZBM0 = a.DBZBM0 and rownum=1) as YBHDJE,--单病种医保核定金额 
  a.JZFS00,                                                                                  --结算方式 实时结算 传送明细
  decode(a.JZFS00,'0','传明细','1','实时结算','未定义') as JZFSMC,                          --结算方式名称      
  a.JKJKBZ,                                                                                  --健康接口上传标志 1 上传 0 不上传
  a.CFXMS0,                                                                                  --住院处方项目总数
  a.YBJZRQ,                                                                                  --医保结账日期
  a.YBJZSJ,                                                                                  --医保结账时间
  a.YBRYRQ,                                                                                  --医保入院日期
  a.YBCYRQ,                                                                                  --医保出院日期
  a.YBCYSJ,                                                                                  --医保出院时间
  a.YBXJZF,                                                                                  --医保现金支付
  a.YPWQZF,                                                                                  --医保药品完全自费合计
  a.XMWQZF,                                                                                  --医保项目完全自费合计
  a.YPBFZF,                                                                                  --医保药品部分自付合计
  a.XMBFZF,                                                                                  --医保项目部分自付合计
  a.YBQFJE,                                                                                  --医保起伏线
  a.YBZHYE,                                                                                  --医保账户余额
  decode(a.FBBH00,3,'个人账户余额：'||a.YBZHYE,'') as YBGRYE ,                               --医保个人账户余额
  a.FYQSRQ,                                                                                  --结算费用起始日期
  a.FYJZRQ,                                                                                  --结算费用截止日期
  decode(a.JSLX00,'0','中途结算 '||a.FYQSRQ||'-'||a.FYJZRQ,'1','出院结算 '||a.FYQSRQ||'-'||a.FYJZRQ,'') as JSQZRQ,    --结算起止日期
  a.GSDJH0,                                                                                  --工伤登记号
  a.YLZFJE,                                                                                  --乙类自付金额
  a.CFDXJE,                                                                                  --超封顶线金额
  a.GWYBZ0,                                                                                  --公务员补助
  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,                                                                                  --共济账户支付
  a.YBYL14,                                                                                  --共济账户余额
  a.YBYL15,                                                                                  --健康账户支付医保费用
  a.YBYL16,                                                                                  --健康账户支付非医保费用
  a.YBYL17,                                                                                  --医保减免金额
  a.YBYL18,                                                                                  --民政(医疗救助基金)补助金额
  a.YBYL19,                                                                                  --超医保上限金额(非医保返回)
  a.YBYL20,                                                                                  --个人账户支付起付段(三明地区商业保险接口)
  a.YBYL21,                                                                                  --个人现金支付起付段(三明地区商业保险接口)
  a.YBYL22,                                                                                  --血费报销金额(血费系统)           
  a.YBYL23,                                                                                  --医保结算金额误差（总费用-定额）  
  a.YBYL24,                                                                                  --大额医疗费用报销金额             
  a.YBYL25,                                                                                  --单病种补偿金额(定额-总费用)      
  a.YBYL26,                                                                                  --大病补充补偿金额                 
  a.YBYL27,                                                                                  --保健医保统筹支付(已包含在统筹中) 
  a.YBYL28,                                                                                  --择日住院门诊总费用            
  a.YBYL29,                                                                                  --择日住院门诊范围内费用        
  a.YBYL30,                                                                                  --择日住院门诊统筹(医保未返回)  
  a.YBYL31,                                                                                  --个人支付金额(医保返回)
  a.YBYL32,                                                                                  --大病补偿个人支付差额(医保返回)
  a.YBYL33,                                                                                  --超标床位费用(付费单病种写入)
  a.YBYL34,                                                                                  --除外耗材费用(付费单病种写入)
  a.YBYL35,                                                                                  --医保返回的BCBXF0(付费单病种写入)
  a.YBYL36,                                                                                  --累计封顶金额
  a.YBYL37,                                                                                  --普通门诊累计
  a.YBYL38,                                                                                  --特殊门诊累计
  a.YBYL39,                                                                                  --CDRG个人支付金额 drgzfe      
  a.YBYL40,                                                                                  --大病本次补偿金额 dbbcbcje    
  a.YBYL41,                                                                                  --B账户支付金额 bkzfe0         
  a.YBYL42,                                                                                  --B账户余额 bkzhye             
  a.YBYL43,                                                                                  --可报高值耗材费(C-DRG)
  a.YBYL44,                                                                                  --超标床位费(C-DRG)    
  a.YBYL45,                                                                                  --超标高值耗材费(C-DRG)
  nvl(a.CBYPF0,0) as CBYPF0,                                                                 --C-DRG超标药品费
  a.YBYL46,                                                                                  --jzfp01 第一道精准扶贫 
  a.YBYL47,                                                                                  --jzfp02 第二道精准扶贫
  a.YBYL48,                                                                                  --jzfp03 综合救助
  a.YBYL49,                                                                                  --备用字段  --参数ZY_DBZJSZFJESF=1时,保留医保返回计算出来的医院支付金额
  a.YBYL50,                                                                                  --csjz00 慈善救助金额
  a.YBYL69,                                                                                  --特需医疗服务费（付费单病种写入）
  a.MXZJE0,                                                                                  --单病种明细总额
  a.YBSM01,                                                                                  --行政区号+区域                  
  a.YBSM02,                                                                                  --是否择日住院                   
  a.YBSM03,                                                                                  --年度结算截止日期               
  a.YBSM04,                                                                                  --血费报销ID(血费系统)           
  a.YBSM05,                                                                                  --单病种登记流水号               
  a.YBSM06,                                                                                  --结算类型 0 医保 1生育 3保健    
  a.YBSM07,                                                                                  --新生儿费用发送医保返回单据号 djlsh0
  a.YBSM08,                                                                                  --单病种数据拆分费用单据号     
  a.YBSM09,                                                                                  --病情编码或CDRG组别 bqbm00 
  a.YBSM10,                                                                                  --CDRG病例类型 jsbllx
  (select MC0000 from BM_TYZD00 where ZDMC00='CDRG病历类型' and BH0000 =a.YBSM10) as JSBLMC, --C-DRG结算病历类型名称   
  a.YBSM11,                                                                                  --CDRG结算ID monitorID  
  c.PJH000,                                                                                  --打印的票据流水号,F.K=ZY_PJSYQK.PJH000
  c.FZPJH0,                                                                                  --分组票据号 
  SF_ZY_GETYXPJH(c.FZPJH0) as YXPJH0,                                                        --有效的票据号   
  c.PJXH00,                                                                                  --打印的票据序号
  c.PJZT00,                                                                                  --票据状态
  decode(c.PJZT00,'0','有效',decode(substr(c.PJXH00,1,1),'-','虚拟','作废')) as PJZTMC,      --票据状态名称
  c.SFDY00,                                                                                  --是否打印,'Y':票据已打印,'N':票据未打印  
  c.CZY000,                                                                                  --打印操作员
  c.CZYXM0,                                                                                  --打印操作员姓名
  c.DYRQ00 as FPDYRQ ,                                                                       --发票打印日期
  c.DYSJ00 as FPDYSJ ,                                                                       --发票打印时间  
  decode(c.BDBZ00,'1','是','否') as SFBDBZ,                                                  --补打标志
  c.SFBBD0,                                                                                  --是否被补打,补打时旧发票和负发票改1;取消发票时,旧发票和负发票改1  
  c.JKDID0,                                                                                  --结账缴款单ID
  c.HTFPDM,                                                                                  --航天金税发票十位代码
  c.HTFPXH,                                                                                  --航天金税发票序号
  a.JSLX00,                                                                                  --结算类型 0:中途结算,1:出院结算2:呆账结算3:年度结算
  decode(a.JSLX00,'0','中途结算','1','出院结算','2','挂账结算','3','年度结算') as JSLXMC,    --结算类型名称  
  (select nvl(ZYE000,0) from ZY_BRZHXX where ZYID00 =a.ZYID00 and JFLBID = 2) as ZYE000,     --账户余额  
  decode(b.YBZYH0,'0',decode(a.ybdjh0,'0',b.ZYH000,a.YBZYH0),b.YBZYH0) as FPZYH0,            --发票打印的住院号(医保的取医保住院号)  
  decode(a.YBDJH0,'0',to_char(a.JZDH00),a.YBDJH0) as FPLSH0,                                 --发票打印的流水号(医保的取医保单据号)
  decode(b.YBZYH0,'0',decode(a.YBDJH0,'0',' ','('||trim(b.ZYH000)||')'),'('||trim(b.ZYH000)||')') as YNZYH0,  --院内住院号
  decode(a.YBYL05,0,0,a.TCJJZF+a.ZFJE00) as DBZXE0,                                          --单病种限额  
  substr(SP_BM_RMB000(decode(a.YBYL05,0,0,a.TCJJZF+a.ZFJE00)),1,100) as DXBZXE,              --大写单病种限额  
  decode(nvl(a.YBYL49,0)+a.YBYL05,0,decode(c.SFBCF0,'0',a.ZFJE00,c.ZFJE00),decode(c.SFBCF0,'0',a.ZFJE00-greatest(a.YBYL49,0),c.ZFJE00)) as YBZFJE,  --兼容单病种自付金额
  nvl(a.YBYL49,0)+a.YBYL05 as YYZFJE,                                                        --医院支付金额 
  substr(SP_BM_RMB000(nvl(a.YBYL35,0)),1,100) as DXYB35,                                     --医保返回的BCBXF0(付费单病种写入)的大写金额
  nvl(a.YBYL39,0)+nvl(a.YBYL43,0)+nvl(a.YBYL44,0)+nvl(a.YBYL45,0)+nvl(a.CBYPF0,0)  as CDRGJE,                                     --CDRG总金额
  substr(SP_BM_RMB000(nvl(a.YBYL39,0)+nvl(a.YBYL43,0)+nvl(a.YBYL44,0)+nvl(a.YBYL45,0)+nvl(a.CBYPF0,0)),1,100) as CDRGDX,          --CDRG总金额大写  
  nvl(a.XJYJZE,YSZJE0) as XJYJZE,                                                            --发票现金预交总额
  nvl(a.ZZYJZE,ZZZJE0) as ZZYJZE,                                                            --发票转账预交总额
  nvl(a.XJYJZE,YSZJE0) + nvl(a.ZZYJZE,ZZZJE0) as FPYJZE,                                     --发票总预交金额
  greatest(nvl(a.XJYJZE,YSZJE0) + nvl(a.ZZYJZE,ZZZJE0) - a.ZFJE00,0) as FPTKZE,              --发票应退款金额
  greatest(a.ZFJE00 - nvl(a.XJYJZE,YSZJE0) - nvl(a.ZZYJZE,ZZZJE0),0) as FPBSZE,              --发票应补收总额 
  a.BJTDJH,                                                                                  --结算补交退预交金单据号
  a.BJTJE0,                                                                                  --结算补交退预交金金额（有正负，正数为补交款，负数为补退款）    
  greatest(a.BJTJE0,0) as BJKJE0,                                                            --补交款金额（正数）
  greatest(-a.BJTJE0,0) as BTKJE0,                                                           --补退款金额（正数）     
  nvl(a.YBYL04,0) + nvl(a.YBYL33,0) + nvl(a.YBYL34,0) + nvl(a.YBYL69,0) as DBZJE0,                             --付费单病种总金额
  substr(SP_BM_RMB000(nvl(a.YBYL04,0) + nvl(a.YBYL33,0) + nvl(a.YBYL34,0) + nvl(a.YBYL69,0)),1,100) as DXBZJE, --大写付费单病种总金额
  a.ZFJE00 + a.GRZHZF + nvl(a.YBYL41,0) as GRZF01,                                                             --CGRG个人支付总额 
  decode(sign(a.YBYL01),1,'医疗救助:'||a.YBYL01,'') as YLJZ00,                                                 --医疗救助
  decode(sign(a.YBYL46),1,'第一扶贫:'||a.YBYL46,'') as YDFP00,                                                 --第一扶贫
  decode(sign(a.YBYL47),1,'第二扶贫:'||a.YBYL47,'') as YEFP00,                                                 --第二扶贫
  decode(sign(a.YBYL50),1,'慈善救助:'||a.YBYL50,'') as CSJZ00,                                                 --慈善救助
  decode(sign(a.YBYL24),1,'商保支付:'||a.YBYL24,'') as DBBC00,                                                 --商保支付（大病补偿）  
  nvl((select ZWXM00 from BM_YGBM00 i,BM_BMBM00 j where i.YGBH00 = j.BMJBR0 and j.BMBH00 = b.DQBQ00 and rownum=1),c.CZYXM0) as JBRXM0, --部门经办人姓名
  a.YBYL58,                                                                                  --大病补偿（大额医疗），新医保
  a.YBYL60,                                                                                  --医疗救助，新医保
  decode(nvl(a.YBYL24,0),0,nvl(a.YBYL58,0),a.YBYL24) as DBBC01,                              --判断新旧医保大病补偿（大额医疗）
  c.WBPJLY,                                                                                  --外部票据来源
  decode(c.WBPJLY,'02','博思电子发票','03','博思纸质发票','04','电子发票作废红票(博思)','05','纸质发票作废红票(博思)') as WBLYMC, --外部票据来源名称  
  c.WBPJXH,                                                                                  --外部票据序号
  c.WBPJDM,                                                                                  --外部票据代码
  c.CJLYBM,                                                                                  --厂家来源编码 BS:博思，YH:易惠，待扩展。。。
  nvl(c.WBID00,0) as WBID00,                                                                 --外部票据ID     
	g.KJCZY0,                                                                                  --开具电子发票操作员<FK>BM_YGBM00.YGBH00</FK>
	(select ZWXM00 from BM_YGBM00 where YGBH00 = g.KJCZY0) as KJCZXM,                          --
	g.KJPDBM,                                                                                  --开具开票点编码  
	g.KJCZKS,                                                                                  --开具操作员科室<FK>BM_BMBM00.BMBH00</FK>
	(select BMMC00 from BM_BMBM00 where BMBH00 = g.KJCZKS) as KJKSMC,                          --
	g.KJRQ00,                                                                                  --开具日期
	g.KJSJ00,                                                                                  --开具时间
	g.KJRQ00||g.KJSJ00 as KJRQSJ,                                                              --
	g.DZFPXH,                                                                                  --电子发票序号
	g.DZFPDM,                                                                                  --电子发票代码
	g.DZJYM0,                                                                                  --电子校验码	
	g.DZEWM0,                                                                                  --电子二维码
	g.ZZCZY0,                                                                                  --纸质发票操作员<FK>BM_YGBM00.YGBH00</FK>
	(select ZWXM00 from BM_YGBM00 where YGBH00 = g.ZZCZY0) as ZZCZXM,                          --  
	g.ZZPDBM,                                                                                  --纸质开票点编码
	g.ZZCZKS,                                                                                  --纸质发票操作员科室<FK>BM_BMBM00.BMBH00</FK>
	(select BMMC00 from BM_BMBM00 where BMBH00 = g.ZZCZKS) as ZZKSMC,                          --
	g.ZZFPRQ,                                                                                  --纸质发票日期
	g.ZZFPSJ,                                                                                  --纸质发票时间
	g.ZZFPRQ||g.ZZFPSJ as ZZRQSJ,                                                              --  
	g.ZZFPXH,                                                                                  --纸质发票序号
	g.ZZFPDM,                                                                                  --纸质发票代码
	g.CXCZY0,                                                                                  --冲销操作员<FK>BM_YGBM00.YGBH00</FK>
	(select ZWXM00 from BM_YGBM00 where YGBH00 = g.CXCZY0) as CXCZXM,                          --
	g.CXPDBM,                                                                                  --冲销开票点编码
	g.CXCZKS,                                                                                  --冲销操作员科室<FK>BM_BMBM00.BMBH00</FK>	
	(select BMMC00 from BM_BMBM00 where BMBH00 = g.CXCZKS) as CXKSMC,                          -- 
	g.CXRQ00,                                                                                  --冲销日期
	g.CXSJ00,                                                                                  --冲销时间
	g.CXRQ00||g.CXSJ00 as CXRQSJ,                                                              --
	g.CHFPXH,                                                                                  --冲红发票序号
	g.CHFPDM,                                                                                  --冲红发票代码
	g.CHJYM0,                                                                                  --冲红校验码
	g.CHEWM0                                                                                   --冲红二维码  
from ZY_JZB000 a,ZY_BRXXB0 b,ZY_PJSYQK c,ZY_WBFPXX g
where a.ZYID00 = b.ZYID00
  and a.PJH000 = c.FZPJH0
  and nvl(c.WBID00,0) = g.WBID00(+)    
  and c.PLBH00 = 3
  and nvl(c.PJZT00,'0') = '0'
/
---%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%