create or replace view vw_zy_fpxx00 as 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',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, --出院日期 greatest(1,to_date(b.CYRQ00,'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) 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) as CDRGJE, --CDRG总金额 substr(SP_BM_RMB000(nvl(a.YBYL39,0)+nvl(a.YBYL43,0)+nvl(a.YBYL44,0)+nvl(a.YBYL45,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, --发票应补收总额 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 SF_ZY_GETYBBZXMJE(a.JZDH00,'ghhzje') as GHHZJE, --工会互助补助金额 nvl(a.CBYPF0,0) CBYPF0, --C-DRG超标药品费 nvl(a.PTBCTS,0) PTBCTS, --普通病床天数 nvl(a.RBZFY0,0) RBZFY0 , --日标准费用 decode(nvl(a.PTBCTS,0),0,0,a.PTBCTS*a.RBZFY0 ) bzfy00, --标准费用 substr(SP_BM_RMB000(decode(nvl(a.PTBCTS,0),0,0,a.PTBCTS*a.RBZFY0 )),1,100) as bzfydx, --标准金额大写 nvl((select sum(jfje00) from zy_brjfb0 where zffs00=28 and zfmxbh='XF' and plbh00 in (4,8) and zyid00=a.zyid00),0) yxzmje ---用血直免金额 from ZY_JZB000 a,ZY_BRXXB0 b,ZY_PJSYQK c where a.ZYID00 = b.ZYID00 and a.PJH000 = c.FZPJH0 and c.PLBH00 = 3;