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 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_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, 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(nvl(D.YBYL01,0)+nvl(D.YBYL18,0)) YBYL01, sum(nvl(D.YBYL05,0)) YBYL05, sum(D.YBYL24) DBBCJE, sum(D.YBYL46) ls_JZFP01, sum(D.YBYL47) ls_JZFP02, sum(D.YBYL48) ls_JZFP03, sum(d.YBYL50) ls_CSJZJE 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.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 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; 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_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; 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS88,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_BRZJE0,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS03,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_ZFJE00,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS04,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GRZHZF,ad_ID0000,ls_GRZHZF,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS05,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,LS_TCJJZF,ad_ID0000,LS_TCJJZF,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS06,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBGRZH,ad_ID0000,ls_SBGRZH,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS07,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBTCJJ,ad_ID0000,ls_SBTCJJ,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS08,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JZJE00,ad_ID0000,ls_JZJE00,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS09,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JMJE00,ad_ID0000,ls_JMJE00,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS13,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JJZFE0,ad_ID0000,ls_JJZFE0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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)+ls_SYBXZF ,GHKS11 = ls_ZYTS00 update BM_GHKSTJ set GHKS14 = nvl(GHKS14,0)+ls_SYBXZF ,XMHJ00=nvl(XMHJ00,0)+decode(sign(Instrb(','||trim(ls_ybbhsb)||',',',14,')),1,0,ls_SYBXZF) ,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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS14,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SYBXZF,ad_ID0000,ls_SYBXZF,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS19,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GWYBZ0,ad_ID0000,ls_GWYBZ0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS19,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GWYBZ0,ad_ID0000,ls_GWYBZ0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS15,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBZFE0,ad_ID0000,'0',ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS16,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_BJZFE0,ad_ID0000,ls_BJZFE0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS21,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YPWQZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS22,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_XMWQZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS23,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YPBFZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS24,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_XMBFZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS25,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YPFY00,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS26,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_MZJZJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS27,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YYZFJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS31,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_DBBCJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS32,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JZFP01,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS33,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JZFP02,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS34,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JZFP03,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS35,ID0000,XMHJ00,GHKS10,GHKS11,GHKS12,GHKS17,GHKS18) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_CSJZJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_DQKS00,ls_ZZYS00,ls_ZYID00); 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,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) select ad_ID0000,99,GHRQ00,GHKS01,GHKS02,GHKS10,GHKS12,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) from BM_GHKSTJ where ID0000 = ad_ID0000 group by ad_ID0000,GHRQ00,GHKS01,GHKS02,GHKS10,GHKS12,GHKS18; --删除数据 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) 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) 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) 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) 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) 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) from BM_GHKSTJ where ID0000 = ad_ID0000 and DYID00=99 group by GHKS18; 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; --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) 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) 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) 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) 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) 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) 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) 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) 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) 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(D.YBYL01),sum(d.YBYL05),0,0,count(distinct f.zyid00) RS0000,sum(D.YBYL24) DBBCJE, sum(D.YBYL46) JZFP01,sum(D.YBYL47) JZFP02,sum(D.YBYL48) JZFP03,sum(d.YBYL50) CSJZJE 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) 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(D.YBYL01),sum(d.YBYL05),0,0,count(distinct f.zyid00) RS0000,sum(D.YBYL24) DBBCJE, sum(D.YBYL46) JZFP01,sum(D.YBYL47) JZFP02,sum(D.YBYL48) JZFP03,sum(d.YBYL50) CSJZJE 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) 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(D.YBYL01),sum(d.YBYL05),0,0,count(distinct f.zyid00) RS0000,sum(D.YBYL24) DBBCJE, sum(D.YBYL46) JZFP01,sum(D.YBYL47) JZFP02,sum(D.YBYL48) JZFP03,sum(d.YBYL50) CSJZJE 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) 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(D.YBYL01),sum(d.YBYL05),0,0,COUNT(distinct f.zyid00) RS0000,sum(D.YBYL24) DBBCJE, sum(D.YBYL46) JZFP01,sum(D.YBYL47) JZFP02,sum(D.YBYL48) JZFP03,sum(d.YBYL50) CSJZJE 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) 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) 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) 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) 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;