(ZYH000, BRRYCS, XM0000, XB0000, RYCWH0, YBKH00, DQBQ00, DQBQMC, BQYYID, DQKS00, DQKSMC, RYRQ00, CYDJRQ, CYRQ00, ZYTS00, FMBQBH, RYLB00, BRZT00, BRZTMC, FFBZBM, FFBZMC, FFBZBZ, JZDH00, CXJZDH, YBDJH0, BRID00, ZYID00, YEXM00, FBBH00, FBMC00, FBXX00, YBZXLB, YBLB00, YBLBMC, YBBRLB, HJJE00, BRZJE0, BCBXF0, ZFJE00, JZJE00, GFJE00, JMJE00, GRZHZF, TCJJZF, YBQTZF, MZBZJE, DLSBJE, SBGRZH, SBTCJJ, CYYSK0, CYYTK0, DZJE00, YSZJE0, ZZZJE0, YJJZE0, XJYE00, ZZYE00, XJZZYE, XJTKJE, ZZTKJE, XJBJJE, ZZBJJE, JSTKJE, JSBJJE, XJFKJE, ZZFKJE, JSFKJE, PJH000, JZZT00, JZZTMC, CXZTMC, PJXH00, FPHSBZ, JZRQ00, JZSJ00, JZRQSJ, JZY000, JZYXM0, CZYKS0, JZBQH0, JMYY00, YBZYH0, BCKBCS, TSBZBH, DBZBM0, JZFS00, JZFSMC, JKJKBZ, CFXMS0, YBJZRQ, YBJZSJ, YBRYRQ, YBCYRQ, YBCYSJ, YBXJZF, YPWQZF, XMWQZF, YPBFZF, XMBFZF, YBQFJE, YBZHYE, FYQSRQ, FYJZRQ, GSDJH0, YLZFJE, CFDXJE, GWYBZ0, 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, YBYL31, YBYL32, YBYL33, YBYL34, YBYL35, YBYL36, YBYL37, YBYL38, YBYL39, YBYL40, YBYL41, YBYL42, YBYL43, YBYL44, YBYL45, YBYL46, YBYL47, YBYL48, YBYL49, MXZJE0, YBSM01, YBSM02, YBSM03, YBSM04, YBSM05, YBSM06, YBSM07, YBSM08, YBSM09, YBSM10, JSBLMC, YBSM11, ICKH00, YBID00, BQXRXM, YRQSJ0, FPDYRQ, FPDYSJ, JSLX00, JSLXMC, CSLX00, CSLXMC, JKBID0, SFBCF0, DDLSH0, XYFY00, ZCYFY0, ZYFY00, CWFY00, ZCFY00, HLFY00, JCFY00, HYFY00, ZLFY00, SSFY00, FSFY00, SXFY00, WSCLFY, QTFY00, DXHJJE, RYN000, RYY000, RYR000, CYN000, CYY000, CYR000, HTFPDM, HTFPXH, CZY000, XTN000, XTY000, XTR000, TCZF00, YLZHZF) create or replace view sd_hospital.vw_zy_jzb000 as select b.ZYH000, b.BRRYCS, substr(SF_ZY_GETYEXMXS(b.XM0000,a.YEXM00),1,20) as XM0000, b.XB0000, b.RYCWH0, b.YBKH00, b.DQBQ00, --当前病人住院病区 (select BMMC00 from BM_BMBM00 where BMBH00 = b.DQBQ00) as DQBQMC, --当前病人住院病区名称 (select YYID00 from BM_BMBM00 where BMBH00 = b.DQBQ00) as BQYYID, --当前病人住院病区所属医院ID 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, --住院天数 b.FMBQBH, --分娩病情编号 BM_TYZD00.BH0000(ZDMC00=生育病种名称维护) b.RYLB00, --入院类别（普通、家庭病床、生育、工伤） b.BRZT00, --病人在院状态 decode(substr(b.BRZT00,1,1),'0','预约病人','1','入院登记','2','在院病人','3','出院登记','4','正常出院','5','出院欠费','6','医保年度结算病人','8','出院已审批','9','取消入院') as BRZTMC, a.FFBZBM, --付费病种编码 (select BZMC00 from BM_BZSFBZ where FBBH00 = a.FBBH00 and YBZXLB = a.YBZXLB and YBBRLB = a.YBBRLB 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.YEXM00, --婴儿姓名 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,50) as FBXX00 , --费别信息 a.YBZXLB, --医保大中心 a.YBLB00, --医保分中心 (select YBMC00 from IC_YBBRLB where FBBH00 = a.FBBH00 and YBLB00 = a.YBLB00 and rownum=1) as YBLBMC , --医保分中心名称 a.YBBRLB, --医保病人类 a.HJJE00, --合计应收金额 a.BRZJE0, --病人费用总金额(拟作废) a.BCBXF0, --住院医疗费总额(发给医保) a.ZFJE00, --自付金额 a.JZJE00, --记帐金额 a.GFJE00, --公费金额，减免时用 a.JMJE00, --优惠金额 a.GRZHZF, --个人医疗帐户支付 a.TCJJZF, --统筹基金支付 a.YBQTZF, --医保其他支付 a.MZBZJE, --民政补助金额(不含与统筹基金支付) a.DLSBJE, --独立商保金额(不含与统筹基金支付) a.SBGRZH, --商报个人账户 a.SBTCJJ, --商报统筹基金 a.CYYSK0, --出院应收款 a.CYYTK0, --出院应退款 decode(a.JZZT00,'2',(decode(CYYSK0,0,-1*CYYTK0,CYYSK0)),0) as DZJE00, --挂账金额 a.YSZJE0, --预交金总额(现金) a.ZZZJE0, --转账预交金总额 a.YSZJE0 + a.ZZZJE0 as YJJZE0, --预交金总额(现金+转账) a.XJYE00, --现金余额 a.ZZYE00, --转账余额 a.XJYE00 + a.ZZYE00 as XJZZYE, --现金转账余额 decode(sign(a.XJYE00),-1,0,a.XJYE00) as XJTKJE , --现金退款金额 decode(sign(a.ZZYE00),-1,0,a.ZZYE00) as ZZTKJE , --转账退款金额 decode(sign(a.XJYE00),-1,-a.XJYE00,0) as XJBJJE , --现金补交金额 decode(sign(a.ZZYE00),-1,-a.ZZYE00,0) as ZZBJJE , --转账补交金额 decode(sign(a.XJYE00 + a.ZZYE00),-1,0,a.XJYE00 + a.ZZYE00) as JSTKJE , --结算退款金额 decode(sign(a.XJYE00 + a.ZZYE00),-1,-(a.XJYE00 + a.ZZYE00),0) as JSBJJE , --结算补交金额 a.XJFKJE, --现金付款,为了方便打印发票而设置的应退还病人的现金余额 a.ZZFKJE, --转帐付款,为了方便打印发票而设置的应退还病人的转帐余额 a.XJFKJE + a.ZZFKJE as JSFKJE , --结算付款金额（现金+转账） a.PJH000, --打印的票据流水号,F.K=ZY_PJSYQK.PJH000 a.JZZT00, --结算状态0:已正常结算 1：已取消结算 decode(a.CXJZDH,0,decode(a.JZZT00,'0','正常','2','正常','被冲销'),'冲销') as JZZTMC, --结账状态名称 decode(a.CXJZDH,0,'正常','冲销') as CXZTMC, --冲销状态名称 substr(SF_ZY_LASTPJXH(a.PJH000),1,30) as PJXH00 , a.FPHSBZ, --发票回收标志,0:未回收,1:已回收 a.JZRQ00, --结账日期 a.JZSJ00, --结账时间 a.JZRQ00||' '||JZSJ00 as JZRQSJ , --结账日期时间 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, --特殊病种编号 a.DBZBM0, --单病种ICD10编码 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, --医保账户余额 a.FYQSRQ, --结算费用起始日期 a.FYJZRQ, --结算费用截止日期 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, --备用字段 a.YBYL44, --备用字段 a.YBYL45, --备用字段 a.YBYL46, --备用字段 a.YBYL47, --备用字段 a.YBYL48, --备用字段 a.YBYL49, --备用字段 a.MXZJE0, --单病种明细总额 a.YBSM01, --行政区号+区域 a.YBSM02, --是否择日住院 a.YBSM03, --年度结算截止日期 a.YBSM04, --血费报销ID(血费系统) a.YBSM05, --单病种登记流水号 a.YBSM06, --结算类型 0 医保 1生育 3保健 a.YBSM07, --新生儿费用发送医保返回单据号 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 (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 (select nvl(ZWXM00,'') from BM_YGBM00 where YGBH00 = (select nvl(JZY000,0) from ZY_JZB000 where JZDH00=a.CXJZDH and ZYID00=a.ZYID00)) as BQXRXM , --被消人姓名BQXRXM (select nvl(JZRQ00,'')||' '||nvl(JZSJ00,'') from ZY_JZB000 where JZDH00=a.CXJZDH and ZYID00=a.ZYID00) as YRQSJ0 , --原日期时间YRQSJ0 (select DYRQ00 from ZY_PJSYQK where PLBH00 = 3 and PJZT00 = '0' and SFDY00 = 'Y' and FZPJH0 = a.PJH000 and rownum = 1) as FPDYRQ , --发票打印日期 (select DYSJ00 from ZY_PJSYQK where PLBH00 = 3 and PJZT00 = '0' and SFDY00 = 'Y' and FZPJH0 = a.PJH000 and rownum = 1) as FPDYSJ , --发票打印时间 a.JSLX00, --结算类型 0:中途结算,1:出院结算2:呆账结算3:年度结算 decode(a.JSLX00,'0','中途结算','1','出院结算','2',decode(b.BRZT00,'4','挂账结算转正常结算','挂账结算'),'3','年度结算') as JSLXMC , -- 结算类型名称 --decode(a.FBBH00,3,decode(sign(a.HJJE00-a.ZFJE00),0,'传明细','实时结算'),'实时结算') as CSLXMC, --传送类型 因前面JZFS00不是很准，故这里新加这个字段 --decode(a.JZFS00,'0','传明细','1','实时结算','未定义') as CSLXMC, --传送类型，还是改为这样吧，发现有的实时结算也是百分百自付的(起付线未达到或已封顶) jlg a.CSLX00, decode(a.CSLX00,'0','传明细','1','实时结算','') as CSLXMC, --农保传送类型 a.JKBID0, --结账缴款表ID (select decode(nvl(max(SFBCF0),'0'),'0','0','1') from ZY_PJSYQK WHERE PLBH00 = 3 and FZPJH0 = a.PJH000) as SFBCF0, --是否被拆分 a.DDLSH0, (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (1)) as XYFY00, --西药费 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (2)) as ZCYFY0, --中成药 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (3)) as ZYFY00, --中药费 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (5)) as CWFY00, --床位费 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (6)) as ZCFY00, --诊查费 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (7)) as HLFY00, --护理费 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (8)) as JCFY00, --检查费 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (9)) as HYFY00, --化验费 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (10)) as ZLFY00, --治疗费 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (11)) as SSFY00, --手术费 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (15)) as FSFY00, --放射费 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 in (18)) as SXFY00, --输血费 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = A.JZDH00 and XMBH00 in (20)) as WSCLFY, --卫生材料费 (select nvl(sum(XMJE00),0) from ZY_JZMX00 where JZDH00 = a.JZDH00 and XMBH00 not in (1,2,3,5,6,7,8,9,10,11,15,18,20)) as QTFY00, --其他费用 SP_BM_RMB000(a.HJJE00) as DXHJJE,--合计金额大写 floor(b.RYRQ00/10000) as RYN000,--入院年 --新农合跨省结算订单流水号 floor(b.RYRQ00/100)-floor(b.RYRQ00/10000)*100 as RYY000, --入院月 floor(b.RYRQ00)-floor(b.RYRQ00/10000)*10000-(floor(b.RYRQ00/100)-floor(b.RYRQ00/10000)*100)*100 as RYR000, --出院日 floor(b.CYRQ00/10000) as CYN000, --出院年 floor(b.CYRQ00/100)-floor(b.CYRQ00/10000)*100 as CYY000, --入院月 floor(b.CYRQ00)-floor(b.CYRQ00/10000)*10000-(floor(b.CYRQ00/100)-floor(b.CYRQ00/10000)*100)*100 as CYR000, --出院日 (select htfpdm from zy_pjsyqk where pjh000=a.pjh000) as htfpdm, (select htfpxh from zy_pjsyqk where pjh000=a.pjh000) as htfpxh, (select zwxm00 from bm_ygbm00 where ygbh00=a.JZY000) as czy000, to_char(sysdate,'yyyy') as xtn000,--系统年 to_char(sysdate,'mm') as xty000,--系统月 to_char(sysdate,'dd') as xtr000, --系统日 (a.GWYBZ0+a.TCJJZF) tczf00, (a.GRZHZF-a.YBYL11) ylzhzf from ZY_JZB000 a,ZY_BRXXB0 b where a.ZYID00 = b.ZYID00;