create or replace procedure SP_ZY_YBBRJS_BB0000 ( as_KSRQ00 in char, --开始日期 as_JSRQ00 in char, --结束日期 as_KSSJ00 in char, --开始时间 as_JSSJ00 in char, --结束时间 as_CZYXM0 in char, --操作员姓名 as_YBZXMC in char, --医保中心名称 as_TJLB00 in char, --统计类别'0':按汇总显示,'1':按明细显示 as_BQMC00 in char, --病区名称 as_YYID00 in char, --分院ID as_ZTJS00 in char, --0统计中按实际日期统计，1按出院日期统计 as_BRYBZX in char default '-1', --病人医保类别 ad_ID0000 out number, --统计报表对应的ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_SFDZX0 in char default 'N', --是否按大中心统计 as_WSQK00 in number default 0, --外伤情况 0 全部 1外伤 2非外伤 as_SFKCGF in char default '0', --0:不扣除 1：扣除 as_HSFDBF in char default '0', --是否包含伙食费、代办费 0:包含 1:不包含 as_JZFP00 in char --0 所有病人 1 精准扶贫病人 ) -- MODIFICATION HISTORY -- Person Date Comments -- jlg 2007.08.08 增加商业保险支付金额的处理ZYSF-20070807-003 -- xzw 2008.02.22 根据ZY_JZB000字段CZYKS0来识别分院情况 -- xzw 2008.04.01 增加传入参数as_ZTJS00 -- xzw 2008.04.07 根据传入的as_ZTJS00值来统计是按实际日期统计，还是按出院日期统计 -- xzw 2008.08.26 增加保健基金支付,商保基金支付（省属三家市属九家医疗机构）结算 -- xzw 2008.09.04 有关联YGBH00字段的都加上YGBH00+0 -- XZW 2009.10.14 前台增加显示主治医生 -- qks 2009.12.15 出院日期和时间条件 改用ZY_JZB000.YBCYRQ以及YBCYSJ -- xzw 2010.03.10 增加公务员补助金额 -- qks 2010.03.24 修改公务员补助GWYBZ0、商保(ad_SBZHJE、ad_SBTJJE) 是否包含在统筹金额中的问题; -- zhangyc 2010.05.06 修改病人住院天数=(出院登记日期(zy_brxxb0.CYDJRQ)-入院日期(zy_brxxb0.ryrq00))+1 -- qks 2010.05.29 3.0升级解决速度慢问题 -- zhangyc 2011.01.10 增加as_BRYBZX 病人医保类别条件 -- zhangwz 2011.06.30 增加入参 as_SFDZX0 来实现按这个大中心进行查询. by ZYSF-20110621-003. -- zhangwz 2011.09.09 增加统计结账时的医保非医保部分金额数据的统计 by ZYSF-20110714-003. -- jinfl 2011.09.29 将除泉州地区的商保包含在统筹金额中. -- jinfl 2012.02.17 公务员补助独立的医保中心从通用字典表中获取 ZYSF-20120206-001 -- zhanghr 2012.03.21 修改脚本写法 ZYSF-20120321-001 -- gzj 2018.05.30 5.0移植到9.0的相关修改 ZYSF9-20180514-002 -- qks 2019.07.15 医保病人JZJE00改为也要统计; for ZYSF9-20190718-001 -- liwm 2019.08.16 住院医保病人结算报表菜单中：要求 增加“精准扶贫”可选框统计条件。 for ZYSF9-20190805-008 -- jlg 2019.11.18 将原来的FBBH00<>1的条件改为 FBBH00=3，将公费病人去掉，只统计医保病人 ZYSF9-20191115-001 -- jlg 2019.12.27 处理因参数ZY_SFBKGFBR不存在导致的报错问题 ZYSF9-20191227-007 -- linzetao 2020.05.21 增加“慈善救助金额”统计栏位 ZYSF9-20200515-004 -- linzetao 2020.08.18 调整大病补偿金额从ZY_JZB000.YBYL26改成ZY_JZB000.YBYL24栏位 ZYSF9-20200807-001 -- linzetao 2020.09.14 调整商业保险支付根据IC_YBBRLB.YBBHSB设置是否列入医保小计 ZYSF9-20200907-004 -- linzetao 2020.09.25 增加YBYL51(精准扶贫金额)的统计信息 ZYSF9-20200909-001 -- linzetao 2020.10.30 大病补偿金额调整为ybyl24+ybyl26 ZYSF9-20201030-001 -- linzetao 2020.11.09 民政救助当YBYL01为0时取YBYL60 ZYSF9-20200930-002 -- linzetao 2020.11.24 调整民政补助当YBYL01+YBYL18为0时取YBYL60, 大病补偿YBYL24+YBYL26为0时取YBYL58 ZYSF9-20201112-003 -- linzetao 2020.12.01 增加病人险种栏位, 取ZY_JZB000.AAE140_MC ZYSF9-20201111-005 -- linzetao 2020.12.15 增加新医保基金支付明细项目, 旧医保原各项明细不再取新医保项目 ZYSF9-20201201-002 -- linzetao 2020.12.17 增加统计政策范围外医疗费(YBYL63)和政策范围内医疗费(HJJE00-YBYL63)栏位 ZYSF9-20201215-002 -- jlg 2021.07.23 各明细项update时，增加and GHKS18 = ls_ZYID00的条件 ZYSF9-20210723-001 -- chenHeyi 2022.01.20 增加医保结算流水号(ZY_JZB000.YBDJH0，用BM_GHKSTJ.BEIZH2);游标,各明细项update及明细显示的字段与条件要加 ZYSF9-20220118-002 -- chenHeyi 2022.04.28 医保小计不需要重复加 商业保险支付 , 医保统筹已包含商业保险支付 ZYSF9-20220304-003 -- linzetao 2023.03.20 调整险种类型取值处理(ZY_JZB000.AAE140_MC->ZY_BRXXB0.AAE140_MC->BM_BRXXB0.AAE140_MC) ZYSF9-20230228-004 -- linzetao 2023.04.13 明细信息(DYID00=2)增加就诊凭证类型(bke284)栏位 ZYSF9-20230411-001 -- jlg 2023.06.12 根据门诊住院费别分开原则，将原来从BM_BRXXB0取费别信息的改为从ZY_BRXXB0取对应费别信息 ZYSF9-20230610-001 --宁德康复 2024.03.20 修改 ls_GWYBZ0 公务员补助不计入“医保小计” as ls_YBMC00 IC_YBBRLB.YBMC00%type; ls_BRID00 BM_BRXXB0.BRID00%type; ls_ZFFS00 ZY_BRJFB0.ZFFS00%type; ls_JFCZY0 ZY_BRJFB0.JFCZY0%type; ls_ZYID00 ZY_BRXXB0.ZYID00%type; ls_DQBQ00 ZY_BRXXB0.DQBQ00%type; ls_ZFJE00 ZY_BRJFB0.JFJE00%type; ls_GRZHZF ZY_BRJFB0.JFJE00%type; ls_TCJJZF ZY_BRJFB0.JFJE00%type; ls_SBGRZH ZY_BRJFB0.JFJE00%type; ls_SBTCJJ ZY_BRJFB0.JFJE00%type; ls_JJZFE0 ZY_BRJFB0.JFJE00%type; ls_SYBXZF ZY_BRJFB0.JFJE00%type; ls_JZJE00 ZY_BRJFB0.JFJE00%type; ls_JMJE00 ZY_BRJFB0.JFJE00%type; ls_ZYTS00 BM_GHKSTJ.GHKS11%type; --病人的住院天数 ls_COUNT0 number(12); ls_DQKS00 ZY_BRXXB0.DQKS00%type; ls_SBZFE0 ZY_BRJFB0.JFJE00%type; ls_BJZFE0 ZY_BRJFB0.JFJE00%type; ls_ZZYS00 ZY_BRXXB0.ZZYS00%type; ls_GWYBZ0 ZY_BRJFB0.JFJE00%type; ls_YBBHSB IC_YBBRLB.YBBHSB%type; ls_YBZXLB IC_YBBRLB.YBZXLB%type; --医保中心类别 ls_YPWQZF ZY_JZB000.YPWQZF%type; --药品完全自费 ls_XMWQZF ZY_JZB000.XMWQZF%type; --项目完全自费 ls_YPBFZF ZY_JZB000.YPBFZF%type; --药品部分自费 ls_XMBFZF ZY_JZB000.XMBFZF%type; --项目部分自费 ls_YPFY00 ZY_JZMX00.xmje00%type; --药品费 ls_MZJZJE ZY_JZB000.YBYL01%type; --民政救助 ls_YYZFJE ZY_JZB000.YBYL05%type; --医院支付 ls_BRZJE0 ZY_JZB000.BRZJE0%type; -- ls_DBBCJE ZY_JZB000.YBYL24%type; --大病补充补偿金额 ls_JZFP01 zy_jzb000.YBYL46%type; --第一道精准扶贫 ls_JZFP02 zy_jzb000.YBYL47%type; --第二道精准扶贫 ls_JZFP03 zy_jzb000.YBYL48%type; --综合救助 ls_CSJZJE ZY_JZB000.YBYL50%type; --慈善救助金额 ls_JZFPJE ZY_JZB000.YBYL51%type; --精准扶贫金额 ls_XZLX00 ZY_JZB000.AAE140_MC%type; --险种类型 ls_YBYL56 ZY_JZB000.YBYL56%type; --其中：统筹基金支付 ls_YBYL57 ZY_JZB000.YBYL57%type; --其中：公务员医疗补助(bkc059=bkc059_1+bkc059_2+bkc059_3) ls_YBYL58 ZY_JZB000.YBYL58%type; --其中:大病保险基金支付(商业保险支付) ls_YBYL59 ZY_JZB000.YBYL59%type; --其中：精准扶贫医疗叠加 ls_YBYL60 ZY_JZB000.YBYL60%type; --其中：医疗救助基金 ls_YBYL61 ZY_JZB000.YBYL61%type; --其中：其他基金支付 ls_YBYL62 ZY_JZB000.YBYL62%type; --其中：企业补充 ls_ZCWYLF ZY_JZB000.YBYL63%type; --政策范围外医疗费 ls_ZCNYLF ZY_JZB000.YBYL63%type; --政策范围内医疗费 ls_YBDJH0 ZY_JZB000.YBDJH0%type; --医保结算流水号 ls_BKE284 ZY_JZB000.BKE284%type; --身份认证类型(就诊凭证类型) ls_SFBKGFBR varchar2(200); LS_YBJSRBBSFXSCZYB varchar2(100); VYYID00 XT_YYXX00.YYID00%type; LS_XSXH00 number(2); cursor CUR_ZY_YBBRJS_BBTJ00 is select C.YBMC00,F.BRID00,F.ZYID00,F.DQBQ00,F.DQKS00,D.JZY000,f.ZZYS00,C.YBBHSB,D.YBDJH0,D.BKE284, sum(D.BRZJE0-SF_ZY_SFYLXM(nvl(as_HSFDBF,'0'),D.ZYID00,D.JZDH00,D.BRZJE0)) ls_BRZJE0, sum(D.ZFJE00-SF_ZY_SFYLXM(nvl(as_HSFDBF,'0'),D.ZYID00,D.JZDH00,D.ZFJE00)) LS_ZFJE00, sum(D.GRZHZF) LS_GRZHZF, sum(D.TCJJZF) LS_TCJJZF, sum(D.SBGRZH) LS_SBGRZH, sum(D.SBTCJJ) LS_SBTCJJ, sum(JJZFE0) LS_JJZFE0, sum(SYBXZF) LS_SYBXZF, sum(D.JZJE00) LS_JZJE00, sum(D.GFJE00) ls_JMJE00, sum(SBZFE0) ls_SBZFE0, sum(BJZFE0) ls_BJZFE0, sum(GWYBZ0) ls_GWYBZ0, sum(nvl(D.YPWQZF,0)) ls_YPWQZF, sum(nvl(D.XMWQZF,0)) ls_XMWQZF, sum(nvl(D.YPBFZF,0)) ls_YPBFZF, sum(nvl(D.XMBFZF,0)) ls_XMBFZF, sum((select sum(h.XMJE00) from ZY_JZMX00 h where h.XMBH00 in (1,2,3) and h.JZDH00=d.JZDH00)) YPFY00, --sum(decode(nvl(d.YBYL01,0)+nvl(D.YBYL18,0),0,nvl(d.YBYL60,0),nvl(d.YBYL01,0)+nvl(D.YBYL18,0))) YBYL01, sum(nvl(d.YBYL01,0)+nvl(D.YBYL18,0)) YBYL01, sum(nvl(D.YBYL05,0)) YBYL05, --sum(decode(nvl(D.YBYL24,0)+nvl(d.YBYL26,0),0,nvl(d.YBYL58,0),nvl(D.YBYL24,0)+nvl(d.YBYL26,0))) DBBCJE, sum(nvl(D.YBYL24,0)+nvl(d.YBYL26,0)) DBBCJE, sum(D.YBYL46) ls_JZFP01, sum(D.YBYL47) ls_JZFP02, sum(D.YBYL48) ls_JZFP03, sum(d.YBYL50) ls_CSJZJE, sum(d.YBYL51) ls_JZFPJE, max(nvl(d.AAE140_MC,nvl(f.AAE140_MC,h.AAE140_MC))) XZLX00, sum(nvl(d.YBYL56,0)) YBYL56, --其中：统筹基金支付 sum(nvl(d.YBYL57,0)) YBYL57, --其中：公务员医疗补助(bkc059=bkc059_1+bkc059_2+bkc059_3) sum(nvl(d.YBYL58,0)) YBYL58, --其中:大病保险基金支付(商业保险支付) sum(nvl(d.YBYL59,0)) YBYL59, --其中：精准扶贫医疗叠加 sum(nvl(d.YBYL60,0)) YBYL60, --其中：医疗救助基金 sum(nvl(d.YBYL61,0)) YBYL61, --其中：其他基金支付 sum(nvl(d.YBYL62,0)) YBYL62, --其中：企业补充 sum(nvl(d.YBYL63,0)) ZCWYLF, --政策范围外医疗费 sum(nvl(d.HJJE00,0)-nvl(d.YBYL63,0)) ZCNYLF --政策范围内医疗费 from IC_YBBRLB C,ZY_JZB000 D,BM_YGBM00 E,ZY_BRXXB0 F,BM_BMBM00 K,BM_BMBM00 G,BM_BRXXB0 h where D.yblb00 = C.yblb00 and D.FBBH00 = C.FBBH00 and D.FBBH00 = 3 and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and D.CZYKS0 = G.BMBH00 and F.BRID00 = h.BRID00 and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.fbbh00)||',')<=0))) and decode(as_BQMC00,'所有病区',F.DQBQ00,decode(K.BMXZ00,'9',F.DQBQ00,'0',F.DQKS00)) = K.BMBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and (G.YYID00 = as_YYID00 or as_YYID00 = '0') and (as_ZTJS00='0' and D.JZRQ00>=as_KSRQ00 and D.JZRQ00<=as_JSRQ00 or as_ZTJS00='1' and D.YBCYRQ>=as_KSRQ00 and D.YBCYRQ<=as_JSRQ00) and decode(as_ZTJS00,'0',D.JZRQ00,D.YBCYRQ)||decode(as_ZTJS00,'0',D.JZSJ00,D.YBCYSJ) >= as_KSRQ00||as_KSSJ00 and decode(as_ZTJS00,'0',D.JZRQ00,D.YBCYRQ)||decode(as_ZTJS00,'0',D.JZSJ00,D.YBCYSJ) <= as_JSRQ00||as_JSSJ00 and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and ((as_YBZXMC='异地医保' and C.YBZXLB='1' and c.yblb00<>'1') or ( as_YBZXMC<>'异地医保' and (C.YBMC00 = as_YBZXMC and as_SFDZX0='N') or (as_SFDZX0='Y' and C.YBZXLB = ls_YBZXLB ) or (as_YBZXMC = '0')) ) and (F.ybbrlb = as_BRYBZX or as_BRYBZX = '-1') and (as_WSQK00=0 or (as_WSQK00=1 and exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1')) or (as_WSQK00=2 and not exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1'))) and (as_JZFP00= '0' or as_JZFP00='1' and F.SFJZFP='1') group by C.YBMC00,F.BRID00,F.ZYID00,F.DQBQ00,D.JZY000,F.DQKS00,f.ZZYS00,C.YBBHSB,D.YBDJH0,D.BKE284; begin if as_YBZXMC<>'异地医保' then if (as_SFDZX0='Y') and (as_YBZXMC <> '0') then select YBZXLB into ls_YBZXLB from IC_YBBRLB where YBMC00=as_YBZXMC; else ls_ybzxlb:='Z'; --随便一个值 end if; end if; select nvl(max(trim(VALUE0)),'-1') into ls_SFBKGFBR from xt_xtcs00 where name00 in ('ZY_SFBKGFBR'); begin select trim(VALUE0) into LS_YBJSRBBSFXSCZYB from XT_XTCS00 where name00='ZY_YBJSRBBSFXSCZYB'; exception when others then LS_YBJSRBBSFXSCZYB := 'N'; end; select SF_SF_GETYYID00() YYID00 into Vyyid00 from dual; select SQ_BM_GHKSTJ_ID0000.nextval into ad_ID0000 from dual; open CUR_ZY_YBBRJS_BBTJ00; loop fetch CUR_ZY_YBBRJS_BBTJ00 into ls_YBMC00,ls_BRID00,ls_ZYID00,ls_DQBQ00,ls_DQKS00,ls_JFCZY0,ls_ZZYS00,ls_YBBHSB,ls_YBDJH0,ls_BKE284, ls_BRZJE0,ls_ZFJE00,LS_GRZHZF,LS_TCJJZF,LS_SBGRZH,LS_SBTCJJ,ls_JJZFE0,ls_SYBXZF,LS_JZJE00,ls_JMJE00, ls_SBZFE0,ls_BJZFE0,ls_GWYBZ0,ls_YPWQZF,ls_XMWQZF,ls_YPBFZF,ls_XMBFZF,ls_YPFY00,ls_MZJZJE,ls_YYZFJE, ls_DBBCJE,ls_JZFP01,ls_JZFP02,ls_JZFP03,ls_CSJZJE,ls_JZFPJE,ls_XZLX00,ls_YBYL56,ls_YBYL57,ls_YBYL58, ls_YBYL59,ls_YBYL60,ls_YBYL61,ls_YBYL62,ls_ZCWYLF,ls_ZCNYLF; exit when CUR_ZY_YBBRJS_BBTJ00%NOTFOUND; select to_date(nvl(CYDJRQ,to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')-to_date(ryrq00,'YYYYMMDD')+1 into ls_ZYTS00 from zy_brxxb0 where zyid00 = ls_ZYID00; if nvl(ls_BRZJE0,0) <>0 then --合计金额 update BM_GHKSTJ set GHKS88 = nvl(GHKS88,0)+ls_BRZJE0,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS88,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_BRZJE0,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_ZFJE00,0) <>0 then --自付金额 update BM_GHKSTJ set GHKS03 = nvl(GHKS03,0)+ls_ZFJE00 ,XMHJ00=nvl(XMHJ00,0) ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS03,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_ZFJE00,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_GRZHZF,0) <> 0 then --医保个人帐户 update BM_GHKSTJ set GHKS04 = nvl(GHKS04,0)+ls_GRZHZF ,XMHJ00=nvl(XMHJ00,0)+ls_GRZHZF ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS04,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GRZHZF,ad_ID0000,ls_GRZHZF,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(LS_TCJJZF,0)<>0 then --医保统筹基金 update BM_GHKSTJ set GHKS05 = nvl(GHKS05,0)+LS_TCJJZF ,XMHJ00=nvl(XMHJ00,0)+LS_TCJJZF ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS05,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,LS_TCJJZF,ad_ID0000,LS_TCJJZF,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_SBGRZH,0)<>0 then --商保个人帐户 update BM_GHKSTJ set GHKS06 = nvl(GHKS06,0)+ls_SBGRZH ,XMHJ00=nvl(XMHJ00,0)+decode(sign(Instrb(','||trim(ls_ybbhsb)||',',',14,')),1,0,ls_SBGRZH) ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS06,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBGRZH,ad_ID0000,ls_SBGRZH,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_SBTCJJ,0) <>0 then --商保统筹基金 update BM_GHKSTJ set GHKS07 = nvl(GHKS07,0)+ls_SBTCJJ ,XMHJ00=nvl(XMHJ00,0)+decode(sign(Instrb(','||trim(ls_ybbhsb)||',',',14,')),1,0,ls_SBTCJJ),GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS07,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBTCJJ,ad_ID0000,ls_SBTCJJ,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if (nvl(ls_JZJE00,0)<>0) then --公费记账金额 update BM_GHKSTJ set GHKS08 = nvl(GHKS08,0)+ls_JZJE00 ,XMHJ00=nvl(XMHJ00,0)+ls_JZJE00 ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS08,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JZJE00,ad_ID0000,ls_JZJE00,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if (nvl(ls_JMJE00,0)<>0)AND(nvl(LS_GRZHZF,0)=0) AND(nvl(LS_TCJJZF,0)=0) then --减免金额 update BM_GHKSTJ set GHKS09 = nvl(GHKS09,0)+ls_JMJE00 ,XMHJ00=nvl(XMHJ00,0)+ls_JMJE00 ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS09,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JMJE00,ad_ID0000,ls_JMJE00,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_JJZFE0,0) <>0 then --保健基金支付 update BM_GHKSTJ set GHKS13 = nvl(GHKS13,0)+ls_JJZFE0 ,XMHJ00=nvl(XMHJ00,0)+ls_JJZFE0 ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS13,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JJZFE0,ad_ID0000,ls_JJZFE0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_SYBXZF,0) <>0 then --商业保险支付 update BM_GHKSTJ set GHKS14 = nvl(GHKS14,0)+ls_SYBXZF ,XMHJ00=nvl(XMHJ00,0),GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS14,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SYBXZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; select count(*) into ls_count0 from BM_TYZD00 where ZDMC00='公务员补助独立医保中心名称' and MC0000=ls_YBMC00; if ls_count0=0 then if ls_YBMC00 in ('鲤城医疗保险分中心','丰泽医疗保险分中心','洛江医疗保险分中心','泉港医疗保险分中心','晋江医疗保险分中心','石狮医疗保险分中心', '南安医疗保险分中心','惠安医疗保险分中心','安溪医疗保险分中心','永春医疗保险分中心','德化医疗保险分中心','泉州市医疗保险中心') then ls_count0:=1; end if; end if; if ls_count0=0 then if nvl(ls_GWYBZ0,0) <>0 then --公务员补助 update BM_GHKSTJ set GHKS19 = nvl(GHKS19,0)+ls_GWYBZ0 ,XMHJ00=nvl(XMHJ00,0) ,GHKS11 = ls_ZYTS00 --ls_GWYBZ0 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS19,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GWYBZ0,ad_ID0000,0,--ls_GWYBZ0, 2024.03.20 lingxianyun ls_GWYBZ0 公务员补助不计入“医保小计” ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; else if nvl(ls_GWYBZ0,0) <>0 then --公务员补助 update BM_GHKSTJ set GHKS19 = nvl(GHKS19,0)+ls_GWYBZ0 ,XMHJ00=nvl(XMHJ00,0)+decode(sign(Instrb(','||trim(ls_ybbhsb)||',',',15,')),1,0,ls_GWYBZ0) ,GHKS11 = ls_ZYTS00 --ls_GWYBZ0 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS19,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GWYBZ0,ad_ID0000,0,--ls_GWYBZ0,2024.03.20 lingxianyun ls_GWYBZ0 公务员补助不计入“医保小计” ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; end if; if nvl(ls_SBZFE0,0) <>0 then --商保基金支付（省属三家市属九家医疗机构） update BM_GHKSTJ set GHKS15 = nvl(GHKS15,0)+ls_SBZFE0 ,XMHJ00=nvl(XMHJ00,0) ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS15,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBZFE0,ad_ID0000,'0',ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_BJZFE0,0) <>0 then --保健基金支付（省属三家市属九家医疗机构） update BM_GHKSTJ set GHKS16 = nvl(GHKS16,0)+ls_BJZFE0 ,XMHJ00=nvl(XMHJ00,0) ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS16,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_BJZFE0,ad_ID0000,ls_BJZFE0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_YPWQZF,0) <>0 then --药品完全自费 update BM_GHKSTJ set GHKS21 = nvl(GHKS21,0)+ls_YPWQZF,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS21,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YPWQZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_XMWQZF,0) <>0 then --项目完全自费 update BM_GHKSTJ set GHKS22 = nvl(GHKS22,0)+ls_XMWQZF,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS22,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_XMWQZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_YPBFZF,0) <>0 then --药品部分自费 update BM_GHKSTJ set GHKS23 = nvl(GHKS23,0)+ls_YPBFZF,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS23,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YPBFZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_XMBFZF,0) <>0 then --项目部分自费 update BM_GHKSTJ set GHKS24 = nvl(GHKS24,0)+ls_XMBFZF,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS24,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_XMBFZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_YPFY00,0) <>0 then --药品费 update BM_GHKSTJ set GHKS25 = nvl(GHKS25,0)+ls_YPFY00,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS25,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YPFY00,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_MZJZJE,0) <>0 then --民政救助 update BM_GHKSTJ set GHKS26 = nvl(GHKS26,0)+ls_MZJZJE,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS26,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_MZJZJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_YYZFJE,0) <>0 then --医院支付 update BM_GHKSTJ set GHKS27 = nvl(GHKS27,0)+ls_YYZFJE,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS27,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YYZFJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_DBBCJE,0) <>0 then --大病补充补偿金额 update BM_GHKSTJ set GHKS31 = nvl(GHKS31,0)+ls_DBBCJE,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS31,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_DBBCJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_JZFP01,0) <>0 then --第一道精准扶贫 update BM_GHKSTJ set GHKS32 = nvl(GHKS32,0)+ls_JZFP01,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS32,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JZFP01,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_JZFP02,0) <>0 then --第二道精准扶贫 update BM_GHKSTJ set GHKS33 = nvl(GHKS33,0)+ls_JZFP02,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS33,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JZFP02,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_JZFP03,0) <>0 then --综合救助 update BM_GHKSTJ set GHKS34 = nvl(GHKS34,0)+ls_JZFP03,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS34,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JZFP03,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_CSJZJE,0) <>0 then --慈善救助金额 update BM_GHKSTJ set GHKS35 = nvl(GHKS35,0)+ls_CSJZJE,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS35,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_CSJZJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_JZFPJE,0) <>0 then --精准扶贫金额 update BM_GHKSTJ set GHKS36 = nvl(GHKS36,0)+ls_JZFPJE,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS36,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JZFPJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_YBYL56,0) <>0 then --其中：统筹基金支付 update BM_GHKSTJ set GHKS37 = nvl(GHKS37,0)+ls_YBYL56,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS37,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YBYL56,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_YBYL57,0) <>0 then --其中：公务员医疗补助(bkc059=bkc059_1+bkc059_2+bkc059_3) update BM_GHKSTJ set GHKS38 = nvl(GHKS38,0)+ls_YBYL57,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS38,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YBYL57,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_YBYL58,0) <>0 then --其中:大病保险基金支付(商业保险支付) update BM_GHKSTJ set GHKS39 = nvl(GHKS39,0)+ls_YBYL58,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS39,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YBYL58,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_YBYL59,0) <>0 then --其中：精准扶贫医疗叠加 update BM_GHKSTJ set GHKS40 = nvl(GHKS40,0)+ls_YBYL59,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS40,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YBYL59,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_YBYL60,0) <>0 then --其中：医疗救助基金 update BM_GHKSTJ set GHKS41 = nvl(GHKS41,0)+ls_YBYL60,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS41,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YBYL60,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_YBYL61,0) <>0 then --其中：其他基金支付 update BM_GHKSTJ set GHKS42 = nvl(GHKS42,0)+ls_YBYL61,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS42,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YBYL61,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_YBYL62,0) <>0 then --其中：企业补充 update BM_GHKSTJ set GHKS43 = nvl(GHKS43,0)+ls_YBYL62,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS43,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YBYL62,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_ZCWYLF,0) <>0 then --政策范围外医疗费 update BM_GHKSTJ set GHKS44 = nvl(GHKS44,0)+ls_ZCWYLF,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS44,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_ZCWYLF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; if nvl(ls_ZCNYLF,0) <>0 then --政策范围内医疗费 update BM_GHKSTJ set GHKS45 = nvl(GHKS45,0)+ls_ZCNYLF,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and GHKS12=ls_DQKS00 and GHKS18 = ls_ZYID00 and BEIZH2=ls_YBDJH0 and BEIZH3=ls_BKE284; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS45,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18, BEIZH1,BEIZH2,BEIZH3) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_ZCNYLF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00, ls_XZLX00,ls_YBDJH0,ls_BKE284); end if; end if; end loop; close CUR_ZY_YBBRJS_BBTJ00; select count(*) into ls_COUNT0 from BM_GHKSTJ where ID0000 = ad_ID0000; --GHRQ00：医保名称 GHKS01:brid00 GHKS02:操作员 GHKS10:病区编码 GHKS12:科室编码 GHKS18:住院id GHKS88：总金额 --GHKS03 自付金额 GHKS04 医保个人帐户 GHKS05 医保统筹基金 GHKS06 商保个人帐户 GHKS07 商保统筹基金 --GHKS08 公费记账金额 GHKS09 减免金额 GHKS11 住院天数 GHKS13 保健基金支付 GHKS14 商业保险支付 GHKS15 商保基金支付 --GHKS16 保健基金支付 GHKS19 公务员补助 GHKS21:药品完全自费 GHKS22:项目完全自费 --GHKS23:药品部分自费 GHKS24:项目部分自费 GHKS25:药品费 GHKS26:民政救助,GHKS27:医院支付 GHKS28:全自费金额=GHKS21+GHKS22 --GHKS29:部分自费金额=GHKS23+GHKS24 GHKS30：人次 GHKS31:大病补充补偿金额 --汇总数据DYID00=99 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS01,GHKS02,GHKS10,GHKS12,GHKS18,BEIZH1,BEIZH2,BEIZH3, GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25, GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36, GHKS37,GHKS38,GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,99,GHRQ00,GHKS01,GHKS02,GHKS10,GHKS12,GHKS18,BEIZH1,BEIZH2,BEIZH3, sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),sum(GHKS35),sum(GHKS36), sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40),sum(GHKS41),sum(GHKS42),sum(GHKS43),sum(GHKS44),sum(GHKS45) from BM_GHKSTJ where ID0000 = ad_ID0000 group by ad_ID0000,GHRQ00,GHKS01,GHKS02,GHKS10,GHKS12,GHKS18,BEIZH1,BEIZH2,BEIZH3; --删除数据 delete BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00<>99; select count(*) into ls_COUNT0 from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99; --1:按收费员汇总DYID00=1 GHRQ00：医保名称 GHKS02:操作员 BEIZHU:收费员姓名 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26, GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36, GHKS37,GHKS38,GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,1,GHRQ00,GHKS02,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25),sum(GHKS26), sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),sum(GHKS35),sum(GHKS36), sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40),sum(GHKS41),sum(GHKS42),sum(GHKS43),sum(GHKS44),sum(GHKS45) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99 group by GHRQ00,GHKS02; update BM_GHKSTJ a set a.BEIZHU=(select b.ZWXM00 from BM_YGBM00 b where b.YGBH00=a.GHKS02 and rownum=1) where ID0000 = ad_ID0000 and DYID00=1; select count(1) into ls_count0 from ( select distinct GHKS01 from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,BEIZHU,GHKS02,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25, GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36, GHKS37,GHKS38,GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,1,'合计 '||'人数:'||to_char(ls_count0),'',999999999,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),sum(GHKS35),sum(GHKS36), sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40),sum(GHKS41),sum(GHKS42),sum(GHKS43),sum(GHKS44),sum(GHKS45) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=1; --2:显示明细信息 DYID00=2 GHRQ00：医保名称 GHKS01:brid00 GHKS18:ZYID00 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS01,GHKS02,GHKS18,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25, GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36, GHKS37,GHKS38,GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45, BEIZH1,BEIZH2,BEIZH3) select ad_ID0000,2,' ',max(GHKS01),max(GHKS02),GHKS18,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),sum(GHKS35),sum(GHKS36), sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40),sum(GHKS41),sum(GHKS42),sum(GHKS43),sum(GHKS44),sum(GHKS45), BEIZH1,BEIZH2,BEIZH3 from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99 group by GHKS18,BEIZH1,BEIZH2,BEIZH3; update BM_GHKSTJ a set a.BEIZHU=(select b.ZWXM00 from BM_YGBM00 b where b.YGBH00=a.GHKS02 and rownum=1) where ID0000 = ad_ID0000 and DYID00=2; update BM_GHKSTJ a set a.GHKS11=(select to_date(nvl(b.CYDJRQ,to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')-to_date(b.RYRQ00,'YYYYMMDD')+1 from ZY_BRXXB0 b where b.zyid00 =a.GHKS18 and rownum=1) where ID0000 = ad_ID0000 and DYID00=2; update BM_GHKSTJ a set a.BEIZH3=nvl((select trim(MC0000) from VW_BM_SFRZLX where YBJKLX='1' and BH0000=a.BEIZH3 and rownum=1),'院内卡') where ID0000 = ad_ID0000 and DYID00=2; --3：按病区汇总 DYID00=3 GHRQ00：医保名称 GHKS10:病区编号 BEIZHU:病区名称 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS10,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25, GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36, GHKS37,GHKS38,GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,3,GHRQ00,GHKS10,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),sum(GHKS35),sum(GHKS36), sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40),sum(GHKS41),sum(GHKS42),sum(GHKS43),sum(GHKS44),sum(GHKS45) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99 group by GHRQ00,GHKS10; update BM_GHKSTJ a set a.BEIZHU=(select b.BMMC00 from BM_BMBM00 b where b.BMBH00=a.GHKS10 and rownum=1) where ID0000 = ad_ID0000 and DYID00=3; update BM_GHKSTJ a set a.GHKS30=(select count(1) from ( select distinct GHKS01,GHRQ00,GHKS10 from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99)aa where aa.GHRQ00=a.GHRQ00 and aa.GHKS10=a.GHKS10) where ID0000 = ad_ID0000 and DYID00=3; insert into BM_GHKSTJ(ID0000,DYID00,BEIZHU,GHRQ00,GHKS10,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25, GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36, GHKS37,GHKS38,GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,3,'合计',' ',999999999,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),sum(GHKS35),sum(GHKS36), sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40),sum(GHKS41),sum(GHKS42),sum(GHKS43),sum(GHKS44),sum(GHKS45) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=3; select count(*) into ls_COUNT0 from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=3; --4：结算汇总表DYID00=4 --5：按医保中心汇总 DYID00=5 GHRQ00：医保名称 if trim(VYYID00) in ('220007','820007') then --省二合计排在第一行，异地第二 LS_XSXH00:=-4; else LS_XSXH00:=1; end if; insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25, GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36, GHKS37,GHKS38,GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,5,GHRQ00,0,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),sum(GHKS35),sum(GHKS36), sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40),sum(GHKS41),sum(GHKS42),sum(GHKS43),sum(GHKS44),sum(GHKS45) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99 group by GHRQ00; update BM_GHKSTJ a set a.GHKS30=(select count(1) from ( select distinct GHKS01,GHRQ00 from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99)aa where aa.GHRQ00=a.GHRQ00 ) where ID0000 = ad_ID0000 and DYID00=5; insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25, GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36, GHKS37,GHKS38,GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,5,'合计 ',LS_XSXH00,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),sum(GHKS35),sum(GHKS36), sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40),sum(GHKS41),sum(GHKS42),sum(GHKS43),sum(GHKS44),sum(GHKS45) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=5 ; --GHRQ00：医保名称 GHKS01:brid00 GHKS02:操作员 GHKS10:病区编码 GHKS12:科室编码 GHKS18:住院id GHKS88：总金额 --GHKS03 自付金额 GHKS04 医保个人帐户 GHKS05 医保统筹基金 GHKS06 商保个人帐户 GHKS07 商保统筹基金 --GHKS08 公费记账金额 GHKS09 减免金额 GHKS11 住院天数 GHKS13 保健基金支付 GHKS14 商业保险支付 GHKS15 商保基金支付 --GHKS16 保健基金支付 GHKS19 公务员补助 GHKS21:药品完全自费 GHKS22:项目完全自费 --GHKS23:药品部分自费 GHKS24:项目部分自费 GHKS25:药品费 GHKS26:民政救助,GHKS27:医院支付 GHKS28:全自费金额=GHKS21+GHKS22 --GHKS29:部分自费金额=GHKS23+GHKS24 GHKS30：人次 --ZY_YBJSRBBSFXSCZYB = 'Y' 城镇医保 if LS_YBJSRBBSFXSCZYB='Y' then insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03, GHKS04,GHKS05, GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19, GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26, GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32, GHKS33,GHKS34,GHKS35,GHKS36,GHKS37,GHKS38, GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,5,'城镇医保',LS_XSXH00+1,sum(D.HJJE00), sum(D.BRZJE0-D.ZFJE00-d.YBYL05) YBXJ00, sum(D.ZFJE00), sum(decode(sign(Instrb(','||trim(C.YBBHSB)||',',',14,')),1,D.GRZHZF,D.GRZHZF+D.SBGRZH)) GRZHZF, sum(decode(sign(Instrb(','||trim(C.YBBHSB)||',',',14,')),1,D.TCJJZF,D.SBTCJJ+D.TCJJZF)) TCJJZF, sum(SBGRZH) SBGRZH,sum(SBTCJJ) SBTCJJ,sum(D.JZJE00)JZJE00,sum(D.GFJE00) JMJE00, 0,sum(BJZFE0) BJZFE0,sum(SYBXZF) SYBXZF,sum(SBZFE0) SBZFE0,sum(JJZFE0) JJZFE0,sum(D.GWYBZ0) GWYBZ0, 0,0,0,0,0,sum(nvl(d.YBYL01,0)+nvl(d.YBYL18,0)), sum(d.YBYL05),0,0,count(distinct f.ZYID00) RS0000, sum(nvl(D.YBYL24,0)+nvl(d.YBYL26,0)) DBBCJE, sum(D.YBYL46) JZFP01,sum(D.YBYL47) JZFP02,sum(D.YBYL48) JZFP03,sum(d.YBYL50) CSJZJE,sum(d.YBYL51) JZFPJE, sum(d.YBYL56) YBYL56,sum(d.YBYL57) YBYL57,sum(d.YBYL58) YBYL58,sum(d.YBYL59) YBYL59,sum(d.YBYL60) YBYL60, sum(d.YBYL61) YBYL61,sum(d.YBYL62) YBYL62,sum(d.YBYL63) YBYL63,sum(d.HJJE00-d.YBYL63) from IC_YBBRLB C,ZY_JZB000 D,BM_YGBM00 E,ZY_BRXXB0 F,BM_BMBM00 K,BM_BMBM00 G where D.YBLB00 = C.YBLB00 and D.FBBH00 = C.FBBH00 and D.FBBH00=3 and d.YBBRLB='C' and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and D.CZYKS0 = G.BMBH00 and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.FBBH00)||',')<=0))) and decode(as_BQMC00,'所有病区',F.DQBQ00,decode(K.BMXZ00,'9',F.DQBQ00,'0',F.DQKS00)) = K.BMBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and (G.YYID00 = as_YYID00 or as_YYID00 = '0') and (as_ZTJS00='0' and D.JZRQ00>=as_KSRQ00 and D.JZRQ00<=as_JSRQ00 or as_ZTJS00='1' and D.YBCYRQ>=as_KSRQ00 and D.YBCYRQ<=as_JSRQ00) and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and ((as_YBZXMC='异地医保' and C.YBZXLB='1' and c.yblb00<>'1') or ( as_YBZXMC<>'异地医保' and (C.YBMC00 = as_YBZXMC and as_SFDZX0='N') or (as_SFDZX0='Y' and C.YBZXLB = ls_YBZXLB) or (as_YBZXMC = '0')) ) and (F.YBBRLB = as_BRYBZX or as_BRYBZX = '-1') and (as_WSQK00=0 or (as_WSQK00=1 and exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1')) or (as_WSQK00=2 and not exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1'))); end if; if (trim(VYYID00) in ('220007','820007')) and (trim(as_YBZXMC) = '0') and (as_YBZXMC<>'异地医保') then -- 省二医院显示 异地医保 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03, GHKS04,GHKS05, GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19, GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26, GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32, GHKS33,GHKS34,GHKS35,GHKS36,GHKS37,GHKS38, GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,5,'异地医保',LS_XSXH00+2,sum(D.HJJE00),sum(D.BRZJE0-D.ZFJE00-d.YBYL05) YBXJ00,sum(D.ZFJE00), sum(decode(sign(Instrb(','||trim(C.YBBHSB)||',',',14,')),1,D.GRZHZF,D.GRZHZF+D.SBGRZH)) GRZHZF, sum(decode(sign(Instrb(','||trim(C.YBBHSB)||',',',14,')),1,D.TCJJZF,D.SBTCJJ+D.TCJJZF)) TCJJZF, sum(SBGRZH) SBGRZH,sum(SBTCJJ) SBTCJJ,sum(D.JZJE00)JZJE00,sum(D.GFJE00) JMJE00, 0,sum(BJZFE0) BJZFE0,sum(SYBXZF) SYBXZF,sum(SBZFE0) SBZFE0,sum(JJZFE0) JJZFE0,sum(D.GWYBZ0) GWYBZ0, 0,0,0,0,0,sum(nvl(d.YBYL01,0)+nvl(d.YBYL18,0)), sum(d.YBYL05),0,0,count(distinct f.zyid00) RS0000, sum(nvl(D.YBYL24,0)+nvl(d.YBYL26,0)) DBBCJE, sum(D.YBYL46) JZFP01,sum(D.YBYL47) JZFP02,sum(D.YBYL48) JZFP03,sum(d.YBYL50) CSJZJE,sum(d.YBYL51) JZFPJE, sum(d.YBYL56) YBYL56,sum(d.YBYL57) YBYL57,sum(d.YBYL58) YBYL58,sum(d.YBYL59) YBYL59,sum(d.YBYL60) YBYL60, sum(d.YBYL61) YBYL61,sum(d.YBYL62) YBYL62,sum(d.YBYL63) ZCWYLF,sum(d.HJJE00-d.YBYL63) ZCNYLF from IC_YBBRLB C,ZY_JZB000 D,BM_YGBM00 E,ZY_BRXXB0 F,BM_BMBM00 K,BM_BMBM00 G where D.YBLB00 = C.YBLB00 and D.FBBH00 = C.FBBH00 and D.FBBH00 = 3 and C.YBZXLB = '1' and c.YBLB00 <> '1' and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and D.CZYKS0 = G.BMBH00 and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.FBBH00)||',')<=0))) and decode(as_BQMC00,'所有病区',F.DQBQ00,decode(K.BMXZ00,'9',F.DQBQ00,'0',F.DQKS00)) = K.BMBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and (G.YYID00 = as_YYID00 or as_YYID00 = '0') and (as_ZTJS00='0' and D.JZRQ00>=as_KSRQ00 and D.JZRQ00<=as_JSRQ00 or as_ZTJS00='1' and D.YBCYRQ>=as_KSRQ00 and D.YBCYRQ<=as_JSRQ00) and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and ( (as_YBZXMC='异地医保' and C.YBZXLB='1' and c.yblb00<>'1') or ( as_YBZXMC<>'异地医保' and (C.YBMC00 = as_YBZXMC and as_SFDZX0='N') or (as_SFDZX0='Y' and C.YBZXLB = ls_YBZXLB) or (as_YBZXMC = '0'))) and (F.YBBRLB = as_BRYBZX or as_BRYBZX = '-1') and (as_WSQK00=0 or (as_WSQK00=1 and exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1')) or (as_WSQK00=2 and not exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1'))); --IC_YBBRLB.YBZXLB=2 市医保小计 by ZYSF-20140816-001 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03, GHKS04,GHKS05, GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19, GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26, GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32, GHKS33,GHKS34,GHKS35,GHKS36,GHKS37,GHKS38, GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,5,'市医保小计',LS_XSXH00+3,sum(D.HJJE00),sum(D.BRZJE0-D.ZFJE00-d.YBYL05) YBXJ00,sum(D.ZFJE00), sum(decode(sign(Instrb(','||trim(C.YBBHSB)||',',',14,')),1,D.GRZHZF,D.GRZHZF+D.SBGRZH)) GRZHZF, sum(decode(sign(Instrb(','||trim(C.YBBHSB)||',',',14,')),1,D.TCJJZF,D.SBTCJJ+D.TCJJZF)) TCJJZF, sum(SBGRZH) SBGRZH,sum(SBTCJJ) SBTCJJ,sum(D.JZJE00)JZJE00,sum(D.GFJE00) JMJE00, 0,sum(BJZFE0) BJZFE0,sum(SYBXZF) SYBXZF,sum(SBZFE0) SBZFE0,sum(JJZFE0) JJZFE0,sum(D.GWYBZ0) GWYBZ0, 0,0,0,0,0,sum(nvl(d.YBYL01,0)+nvl(d.YBYL18,0)), sum(d.YBYL05),0,0,count(distinct f.ZYID00) RS0000, sum(nvl(D.YBYL24,0)+nvl(d.YBYL26,0)) DBBCJE, sum(D.YBYL46) JZFP01,sum(D.YBYL47) JZFP02,sum(D.YBYL48) JZFP03,sum(d.YBYL50) CSJZJE,sum(d.YBYL51) JZFPJE, sum(d.YBYL56) YBYL56,sum(d.YBYL57) YBYL57,sum(d.YBYL58) YBYL58,sum(d.YBYL59) YBYL59,sum(d.YBYL60) YBYL60, sum(d.YBYL61) YBYL61,sum(d.YBYL62) YBYL62,sum(d.YBYL63) ZCWYLF,sum(d.HJJE00-d.YBYL63) ZCNYLF from IC_YBBRLB C,ZY_JZB000 D,BM_YGBM00 E,ZY_BRXXB0 F,BM_BMBM00 K,BM_BMBM00 G where D.YBLB00 = C.YBLB00 and D.FBBH00 = C.FBBH00 and D.FBBH00 = 3 and C.YBZXLB = '2' and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and D.CZYKS0 = G.BMBH00 and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.FBBH00)||',')<=0))) and decode(as_BQMC00,'所有病区',F.DQBQ00,decode(K.BMXZ00,'9',F.DQBQ00,'0',F.DQKS00)) = K.BMBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and (G.YYID00 = as_YYID00 or as_YYID00 = '0') and (as_ZTJS00='0' and D.JZRQ00>=as_KSRQ00 and D.JZRQ00<=as_JSRQ00 or as_ZTJS00='1' and D.YBCYRQ>=as_KSRQ00 and D.YBCYRQ<=as_JSRQ00) and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and ((as_YBZXMC='异地医保' and C.YBZXLB='1' and c.YBLB00<>'1') or (as_YBZXMC<>'异地医保' and (C.YBMC00 = as_YBZXMC and as_SFDZX0='N') or (as_SFDZX0='Y' and C.YBZXLB = ls_YBZXLB) or (as_YBZXMC = '0'))) and (F.YBBRLB = as_BRYBZX or as_BRYBZX = '-1') and (as_WSQK00=0 or (as_WSQK00=1 and exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1')) or (as_WSQK00=2 and not exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1'))); --IC_YBBRLB.YBZXLB=@ 新农合小计 by ZYSF-20140816-001 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS02,GHKS88,XMHJ00,GHKS03, GHKS04,GHKS05, GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19, GHKS21,GHKS22,GHKS23,GHKS24,GHKS25,GHKS26, GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32, GHKS33,GHKS34,GHKS35,GHKS36,GHKS37,GHKS38, GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,5,'新农合小计',LS_XSXH00+3,sum(D.HJJE00),sum(D.BRZJE0-D.ZFJE00-d.YBYL05) YBXJ00,sum(D.ZFJE00), sum(decode(sign(Instrb(','||trim(C.YBBHSB)||',',',14,')),1,D.GRZHZF,D.GRZHZF+D.SBGRZH)) GRZHZF, sum(decode(sign(Instrb(','||trim(C.YBBHSB)||',',',14,')),1,D.TCJJZF,D.SBTCJJ+D.TCJJZF)) TCJJZF, sum(SBGRZH) SBGRZH,sum(SBTCJJ) SBTCJJ,sum(D.JZJE00)JZJE00,sum(D.GFJE00) JMJE00, 0,sum(BJZFE0) BJZFE0,sum(SYBXZF) SYBXZF,sum(SBZFE0) SBZFE0,sum(JJZFE0) JJZFE0,sum(D.GWYBZ0) GWYBZ0, 0,0,0,0,0,sum(nvl(d.YBYL01,0)+nvl(d.YBYL18,0)), sum(d.YBYL05),0,0,COUNT(distinct f.zyid00) RS0000, sum(nvl(D.YBYL24,0)+nvl(d.YBYL26,0)) DBBCJE, sum(D.YBYL46) JZFP01,sum(D.YBYL47) JZFP02,sum(D.YBYL48) JZFP03,sum(d.YBYL50) CSJZJE,sum(d.YBYL51) JZFPJE, sum(d.YBYL56) YBYL56,sum(d.YBYL57) YBYL57,sum(d.YBYL58) YBYL58,sum(d.YBYL59) YBYL59,sum(d.YBYL60) YBYL60, sum(d.YBYL61) YBYL61,sum(d.YBYL62) YBYL62,sum(d.YBYL63) ZCWYLF,sum(d.HJJE00-d.YBYL63) ZCNYLF from IC_YBBRLB C,ZY_JZB000 D,BM_YGBM00 E,ZY_BRXXB0 F,BM_BMBM00 K,BM_BMBM00 G where D.YBLB00 = C.YBLB00 and D.FBBH00 = C.FBBH00 and D.FBBH00 = 3 and C.YBZXLB = '@' and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and D.CZYKS0 = G.BMBH00 and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.FBBH00)||',')<=0))) and decode(as_BQMC00,'所有病区',F.DQBQ00,decode(K.BMXZ00,'9',F.DQBQ00,'0',F.DQKS00)) = K.BMBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and (G.YYID00 = as_YYID00 or as_YYID00 = '0') and (as_ZTJS00='0' and D.JZRQ00>=as_KSRQ00 and D.JZRQ00<=as_JSRQ00 or as_ZTJS00='1' and D.YBCYRQ>=as_KSRQ00 and D.YBCYRQ<=as_JSRQ00) and (E.ZWXM00 = as_CZYXM0 or as_CZYXM0 = '0') and ((as_YBZXMC='异地医保' and C.YBZXLB='1' and c.yblb00<>'1') or (as_YBZXMC<>'异地医保' and (C.YBMC00 = as_YBZXMC and as_SFDZX0='N') or (as_SFDZX0='Y' and C.YBZXLB = ls_YBZXLB) or (as_YBZXMC = '0'))) and (F.ybbrlb = as_BRYBZX or as_BRYBZX = '-1') and (as_WSQK00=0 or (as_WSQK00=1 and exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1')) or (as_WSQK00=2 and not exists(select 1 from ZY_RYDJB0 where ZYID00=f.ZYID00 and JLLB00 = '1'))); end if; --6：按科室汇总 DYID00=6 GHRQ00：医保名称 GHKS12:科室编号 BEIZHU:科室名称 insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS12,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25, GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36, GHKS37,GHKS38,GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,6,GHRQ00,GHKS12,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),sum(GHKS35),sum(GHKS36), sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40),sum(GHKS41),sum(GHKS42),sum(GHKS43),sum(GHKS44),sum(GHKS45) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99 group by GHRQ00,GHKS12; update BM_GHKSTJ a set a.BEIZHU=(select b.BMMC00 from BM_BMBM00 b where b.BMBH00=a.GHKS12 and rownum=1) where ID0000 = ad_ID0000 and DYID00=6; update BM_GHKSTJ a set a.GHKS30=(select count(1) from ( select distinct GHKS01,GHRQ00,GHKS12 from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99)aa where aa.GHRQ00=a.GHRQ00 and aa.GHKS12=a.GHKS12) where ID0000 = ad_ID0000 and DYID00=6; insert into BM_GHKSTJ(ID0000,DYID00,BEIZHU,GHRQ00,GHKS12,GHKS88,XMHJ00,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09, GHKS11,GHKS13,GHKS14,GHKS15,GHKS16,GHKS19,GHKS21,GHKS22,GHKS23,GHKS24,GHKS25, GHKS26,GHKS27,GHKS28,GHKS29,GHKS30,GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36, GHKS37,GHKS38,GHKS39,GHKS40,GHKS41,GHKS42,GHKS43,GHKS44,GHKS45) select ad_ID0000,6,'合计',' ',999999999,sum(GHKS88),sum(XMHJ00),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09), sum(GHKS11),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS19),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25), sum(GHKS26),sum(GHKS27),sum(GHKS21+GHKS22),sum(GHKS23+GHKS24),sum(GHKS30),sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),sum(GHKS35),sum(GHKS36), sum(GHKS37),sum(GHKS38),sum(GHKS39),sum(GHKS40),sum(GHKS41),sum(GHKS42),sum(GHKS43),sum(GHKS44),sum(GHKS45) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=6; commit; exception when others then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_YBBRJS_BB0000('||as_KSRQ00||','||as_JSRQ00||','||as_CZYXM0||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); rollback; end;