CREATE OR REPLACE PROCEDURE SP_ZY_YBBRJS_DBZTJB ( as_KSRQ00 IN CHAR, --开始日期 as_JSRQ00 IN CHAR, --结束日期 as_KSSJ00 IN CHAR, --开始时间 as_JSSJ00 IN CHAR, --结束时间 as_BRYBZX IN CHAR, --病人类别 as_YBZXMC IN CHAR, --医保中心名称 as_BQMC00 IN CHAR, --病区名称 as_YYID00 in char, --分院ID as_TJFS00 IN CHAR, --0：按结算时间统计，1按出院结算统计 as_SFDZX0 in char default 'N', --是否按大中心统计 ad_ID0000 OUT NUMBER, --统计报表对应的ID as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR, --系统提示的错误信息 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_IFJZFP in char default '0' --0：统计所有病人;1:只统计精准扶贫病人 ) as -- MODIFICATION HISTORY -- Person Date Comments -- jinfl 2011.10.18 Create; -- jinfl 2013.01.10 修改特殊病种统计方式 ZYSF-20130108-001 -- zhangyc 2013.11.20 增加列民政救助 医院支付 by ZYSF-20130723-001 -- jinfl 2013.11.22 增加判断外伤情况 ZYSF-20131016-001 -- zhangyc 2014.02.12 增加按按异地医保统计 by ZYSF-20140207-002 -- zhangyc 2014.10.16 改为统计单病种 by ZYSF-20141009-002; -- zhangyc 2015.04.08 增加YBYL18(民政(医疗救助基金)补助金额)合并到YBYL01 by ZYSF-20141211-001 -- zhangyc 2015.05.14 修正统计报错 by ZYSF-20150513-001 -- zhangyc 2015.10.23 增加入参as_HSFDBF(是否包含伙食费、代办费 0:包含 1:不包含)增加SF_ZY_SFYLXM取值 by ZYSF-20151019-001 -- zhangyc 2017.10.15 增加统计发票明细数据 by ZYSF-20171014-001 -- dsm 2018.07.30 增加公务员补助 for ZYSF-20180712-004 -- linshu 2018.08.10 增加入参as_IFJZFP是否只统计精准扶贫住院病人 by ZYSF-20180726-002 -- qiulf 2020.02.19 增加BEIZU6表示病种编码 by ZYSF-20190111-001 -- linbin 2020.05.12 将民政救助与民政补助分开体现 by ZYSF-20200413-001 -- dsm 2020.09.14 SBJJZF,SBGRZF,GWYBZ改从ZY_BRJFB0获取 for ZYSF-20200914-002 -- linbin 2020.11.27 增加GHKS63，GHKS64存放单病种结算人次和符合单病种人次，以便计算纳入率 by ZYSF-20201020-002 -- linbin 2021.03.22 单病种名称改从ZY_JZB000获取，找不到再从BM_DBZDEB获取 by ZYSF-20210319-001 -- linbin 2021.04.15 修正需求ZYSF-20201020-002问题，合计金额不重复统计 by ZYSF-20210413-004 -- linbin 2021.07.15 增加GHKS65，GHKS66存放病种定额和除外耗材费 by ZYSF-20210521-002 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_ZZYS00 ZY_BRXXB0.ZZYS00%TYPE; ls_ZFJE00 ZY_BRJFB0.JFJE00%TYPE; ls_GRZHZF ZY_BRJFB0.JFJE00%TYPE; ls_jjzfe0 ZY_BRJFB0.JFJE00%TYPE; ls_TCJJZF ZY_BRJFB0.JFJE00%TYPE; ls_SBGRZH ZY_BRJFB0.JFJE00%TYPE; ls_SBTCJJ ZY_BRJFB0.JFJE00%TYPE; ls_JZJE00 ZY_BRJFB0.JFJE00%TYPE; ls_ybxjzf ZY_BRJFB0.JFJE00%TYPE; ls_fybje0 ZY_BRJFB0.JFJE00%TYPE; ls_ZYTS00 BM_GHKSTJ.GHKS11%TYPE; --病人的住院天数 -- ls_COUNT0 NUMBER(12); ls_MC0000 bm_dbzdeb.DBZMC0%TYPE; ls_DBZBM0 bm_dbzdeb.DBZBM0%TYPE; ls_YBZXLB IC_YBBRLB.YBZXLB%TYPE; --医保中心类别 ls_MZJZJE ZY_JZB000.YBYL01%TYPE; --民政救助 ls_MZBZJE ZY_JZB000.YBYL18%TYPE; --民政补助 ls_YYZFJE ZY_JZB000.YBYL05%TYPE; --医院支付 LS_FPXMBH ZY_JZMX00.XMBH00%TYPE; ls_SFBKGFBR varchar2(200); LS_HJJE00 number(12,4); LS_HJJE01 number(12,4); LS_FPMXTJ varchar2(100); ls_GWYBZ0 ZY_JZB000.GWYBZ0%type; ls_RSHNLL XT_XTCS00.VALUE0%type; cursor CUR_ZY_YBBRJS_BBTJ00 IS select c.YBMC00,d.BRID00,f.ZYID00,f.DQBQ00,d.JZY000,D.DBZBM0,D.DBZMC0, --sum(d.ZFJE00) LS_ZFJE00, 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(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and PJH000 = D.PJH000 and ZFFS00=10),0)) LS_SBGRZH, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and PJH000 = D.PJH000 and ZFFS00=11),0)) LS_SBTCJJ, SUM(d.JZJE00) LS_JZJE00, --sum(d.YBXJZF) ls_ybxjzf, sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and PJH000 = D.PJH000 and ZFFS00=13),0)) LS_SYBXZF, sum(d.jjzfe0) ls_jjzfe0, sum(d.FYBJE0) ls_FYBJE0, --sum(d.ybyl01), sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and PJH000 = D.PJH000 and ZFFS00=22),0)) ybyl01, -- sum(d.ybyl18), sum(nvl((select JFJE00 from ZY_BRJFB0 where JZDH00=d.JZDH00 and PJH000 = D.PJH000 and ZFFS00=22),0)) ybyl18, sum(ybyl05+YBYL25), --sum(D.HJJE00) sum(D.HJJE00-SF_ZY_SFYLXM(nvl(as_HSFDBF,'0'),D.ZYID00,D.JZDH00,D.HJJE00)), sum(decode(sign(instrb(','||trim(C.YBBHSB)||',',',15,')),1,0,D.GWYBZ0)) GWYBZ0 from ZY_JZB000 D,IC_YBBRLB C,BM_YGBM00 E,ZY_BRXXB0 F,BM_BMBM00 K where D.YBLB00 = C.YBLB00 and C.YBLB00 <>'0' and D.DBZBM0 is not null and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and F.DQBQ00 = K.BMBH00 and D.FBBH00 = C.FBBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.fbbh00)||',')<=0))) and ((as_TJFS00='0' and (d.JZRQ00 >= as_KSRQ00 and d.JZRQ00 <= as_JSRQ00 and d.JZRQ00||D.JZSJ00 >= as_KSRQ00||as_KSSJ00 and d.JZRQ00||D.JZSJ00 <= as_JSRQ00||as_JSSJ00)) or (as_TJFS00='1' and (F.CYRQ00 >= as_KSRQ00 and F.CYRQ00 <= as_JSRQ00 and F.CYRQ00||F.CYSJ00 >= as_KSRQ00||as_KSSJ00 and F.CYRQ00||F.CYSJ00 <= as_JSRQ00||as_JSSJ00))) 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 (K.YYID00 = as_YYID00 or as_YYID00 = '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(c.YBZXLB=ls_YBZXLB and as_SFDZX0='Y') or (as_YBZXMC = '所有医保病人'))) and (( as_IFJZFP='1' and F.SFJZFP='1' ) or ( as_IFJZFP='0' and 1=1 )) group by c.YBMC00,d.BRID00,f.ZYID00,f.DQBQ00,d.JZY000,D.DBZBM0,D.DBZMC0; cursor CUR_ZY_YBBRJS_FHDBZ0 IS select c.YBMC00,d.BRID00,f.ZYID00,f.DQBQ00,d.JZY000,D.DBZBM0,D.DBZMC0, sum(D.HJJE00-SF_ZY_SFYLXM(nvl(as_HSFDBF,'0'),D.ZYID00,D.JZDH00,D.HJJE00)) from ZY_JZB000 D,IC_YBBRLB C,BM_YGBM00 E,ZY_BRXXB0 F,BM_BMBM00 K where D.YBLB00 = C.YBLB00 and C.YBLB00 <>'0' and exists ( select 1 from ( select X.ZYID00,X.ZDM000,X.ZDMC00,Y.WHLB00,Y.ZDBM00,Y.ZDBM01 from BQ_BRZDXX X, BM_DBZZDB Y where X.ZDM000=Y.ZDBM00 and X.ZDLB00='3' and ( Y.WHLB00='0' and X.ZDM000=Y.ZDBM00 or Y.WHLB00='1' and X.ZDM000 like '%'||Y.ZDBM01||'%' ) ) Z where Z.ZYID00 = D.ZYID00 ) and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and F.DQBQ00 = K.BMBH00 and D.FBBH00 = C.FBBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.fbbh00)||',')<=0))) and ((as_TJFS00='0' and (d.JZRQ00 >= as_KSRQ00 and d.JZRQ00 <= as_JSRQ00 and d.JZRQ00||D.JZSJ00 >= as_KSRQ00||as_KSSJ00 and d.JZRQ00||D.JZSJ00 <= as_JSRQ00||as_JSSJ00)) or (as_TJFS00='1' and (F.CYRQ00 >= as_KSRQ00 and F.CYRQ00 <= as_JSRQ00 and F.CYRQ00||F.CYSJ00 >= as_KSRQ00||as_KSSJ00 and F.CYRQ00||F.CYSJ00 <= as_JSRQ00||as_JSSJ00))) 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 (K.YYID00 = as_YYID00 or as_YYID00 = '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(c.YBZXLB=ls_YBZXLB and as_SFDZX0='Y') or (as_YBZXMC = '所有医保病人'))) and (( as_IFJZFP='1' and F.SFJZFP='1' ) or ( as_IFJZFP='0' and 1=1 )) group by c.YBMC00,d.BRID00,f.ZYID00,f.DQBQ00,d.JZY000,D.DBZBM0,D.DBZMC0; cursor CUR_ZY_YBBRJS_FPMX00 IS select C.YBMC00,D.BRID00,F.ZYID00,F.DQBQ00,D.JZY000,D.DBZBM0,D.DBZMC0,B.XMBH00, sum(B.XMJE00) from ZY_JZB000 D,ZY_JZMX00 B,IC_YBBRLB C,BM_YGBM00 E,ZY_BRXXB0 F,BM_BMBM00 K where D.YBLB00 = C.YBLB00 and C.YBLB00 <>'0' and D.DBZBM0 is not null and D.JZDH00 = B.JZDH00 and D.JZY000 = E.YGBH00 and D.ZYID00 = F.ZYID00 and F.DQBQ00 = K.BMBH00 and D.FBBH00 = C.FBBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.fbbh00)||',')<=0))) and ((as_TJFS00='0' and (d.JZRQ00 >= as_KSRQ00 and d.JZRQ00 <= as_JSRQ00 and d.JZRQ00||D.JZSJ00 >= as_KSRQ00||as_KSSJ00 and d.JZRQ00||D.JZSJ00 <= as_JSRQ00||as_JSSJ00)) or (as_TJFS00='1' and (F.CYRQ00 >= as_KSRQ00 and F.CYRQ00 <= as_JSRQ00 and F.CYRQ00||F.CYSJ00 >= as_KSRQ00||as_KSSJ00 and F.CYRQ00||F.CYSJ00 <= as_JSRQ00||as_JSSJ00))) 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 (K.YYID00 = as_YYID00 or as_YYID00 = '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(c.YBZXLB=ls_YBZXLB and as_SFDZX0='Y') or (as_YBZXMC = '所有医保病人'))) and (( as_IFJZFP='1' and F.SFJZFP='1' ) or ( as_IFJZFP='0' and 1=1 )) group by C.YBMC00,D.BRID00,F.ZYID00,F.DQBQ00,D.JZY000,D.DBZBM0,D.DBZMC0,B.XMBH00; cursor CUR_ZY_YBBRJS_BZFPMX IS select C.YBMC00,D.BRID00,F.ZYID00,F.DQBQ00,D.JZY000,D.DBZBM0,D.DBZMC0, max(SF_ZY_BZFPXMJE(D.ZYID00,D.JZDH00,'YBYL04')) DBZDE0,sum(SF_ZY_BZFPXMJE(D.ZYID00,D.JZDH00,'YBYL34')) CWHCF0 from ZY_JZB000 D,IC_YBBRLB C,ZY_BRXXB0 F,BM_BMBM00 K where D.YBLB00 = C.YBLB00 and C.YBLB00 <>'0' and D.DBZBM0 is not null and D.ZYID00 = F.ZYID00 and F.DQBQ00 = K.BMBH00 and D.FBBH00 = C.FBBH00 and (K.BMMC00 = as_BQMC00 or as_BQMC00 = '所有病区') and ((as_SFKCGF='0') or ((as_SFKCGF='1') and (instrb(','||ls_SFBKGFBR||',',','||to_char(d.fbbh00)||',')<=0))) and ((as_TJFS00='0' and (d.JZRQ00 >= as_KSRQ00 and d.JZRQ00 <= as_JSRQ00 and d.JZRQ00||D.JZSJ00 >= as_KSRQ00||as_KSSJ00 and d.JZRQ00||D.JZSJ00 <= as_JSRQ00||as_JSSJ00)) or (as_TJFS00='1' and (F.CYRQ00 >= as_KSRQ00 and F.CYRQ00 <= as_JSRQ00 and F.CYRQ00||F.CYSJ00 >= as_KSRQ00||as_KSSJ00 and F.CYRQ00||F.CYSJ00 <= as_JSRQ00||as_JSSJ00))) 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 (K.YYID00 = as_YYID00 or as_YYID00 = '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(c.YBZXLB=ls_YBZXLB and as_SFDZX0='Y') or (as_YBZXMC = '所有医保病人'))) and (( as_IFJZFP='1' and F.SFJZFP='1' ) or ( as_IFJZFP='0' and 1=1 )) group by C.YBMC00,D.BRID00,F.ZYID00,F.DQBQ00,D.JZY000,D.DBZBM0,D.DBZMC0; BEGIN if as_YBZXMC<>'异地医保' then if (as_SFDZX0='Y') and (as_YBZXMC <> '所有医保病人') then select YBZXLB into ls_YBZXLB from IC_YBBRLB where YBMC00=as_YBZXMC; else ls_ybzxlb:='Z'; --随便一个值 end if; end if; select trim(VALUE0) into ls_SFBKGFBR from xt_xtcs00 where name00 in ('ZY_SFBKGFBR'); --住院医保病人结算报表(单病种统计)是否显示发票明细,Y:显示 N:不显示 默认值:N begin select nvl(trim(VALUE0),'N') into LS_FPMXTJ from XT_XTCS00 where name00 in ('ZY_DBZTJBBSFXSFPMX'); exception when others then LS_FPMXTJ:='N'; end; 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_JFCZY0,ls_DBZBM0,LS_MC0000, ls_ZFJE00,LS_GRZHZF,LS_TCJJZF,LS_SBGRZH,LS_SBTCJJ,LS_JZJE00,ls_ybxjzf,ls_jjzfe0,ls_fybje0, ls_MZJZJE,ls_MZBZJE,ls_YYZFJE,LS_HJJE00,ls_GWYBZ0; exit when CUR_ZY_YBBRJS_BBTJ00%NOTFOUND; if nvl(ls_DBZBM0,'')<>'' and nvl(LS_MC0000,'')='' then begin select substrb(DBZMC0,1,100) into LS_MC0000 from BM_DBZDEB where DBZBM0=ls_DBZBM0 and rownum=1; exception when others then ls_MC0000:=null; end; end if; select to_date(NVL(cyrq00,to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')-to_date(ryrq00,'YYYYMMDD') into ls_ZYTS00 from zy_brxxb0 where zyid00 = ls_ZYID00; if NVL(LS_HJJE00,0) <>0 then update BM_GHKSTJ set GHKS88 = NVL(GHKS88,0)+LS_HJJE00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS88,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,LS_HJJE00,ad_ID0000,0,ls_DQBQ00,0,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); 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)+ls_ZFJE00 , GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS03,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_ZFJE00,ad_ID0000,ls_ZFJE00,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); end if; end if; if NVL(ls_ybxjzf,0) <> 0 then --医保个人自付 update BM_GHKSTJ set GHKS09 = NVL(GHKS09,0)+ls_ybxjzf ,GHKS11 = ls_ZYTS00 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS09,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GRZHZF,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); 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 BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS04,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GRZHZF,ad_ID0000,ls_GRZHZF,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); 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 BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS05,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,LS_TCJJZF,ad_ID0000,LS_TCJJZF,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); 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)+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 BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS06,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBGRZH,ad_ID0000,ls_SBGRZH,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); 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)+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 BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS07,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_SBTCJJ,ad_ID0000,ls_SBTCJJ,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); end if; end if; if NVL(ls_jjzfe0,0) <> 0 then --保健基金自付 update BM_GHKSTJ set GHKS12 = NVL(GHKS12,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 BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS12,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_jjzfe0,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); end if; end if; if (NVL(ls_JZJE00,0)<>0)and(NVL(LS_GRZHZF,0)=0) and(NVL(LS_TCJJZF,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 BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS08,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_JZJE00,ad_ID0000,ls_JZJE00,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); end if; end if; if NVL(LS_FYBJE0,0)<>0 then --非医保项目金额 update BM_GHKSTJ set GHKS13 = NVL(GHKS13,0)+LS_FYBJE0 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS13,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_FYBJE0,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); end if; end if; if NVL(ls_MZJZJE,0)<>0 then --民政救助 update BM_GHKSTJ set GHKS26 = NVL(GHKS26,0)+ls_MZJZJE where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS26,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_MZJZJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); end if; end if; if NVL(ls_YYZFJE,0)<>0 then --医院支付 update BM_GHKSTJ set GHKS27 = NVL(GHKS27,0)+ls_YYZFJE where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS27,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_YYZFJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); end if; end if; if NVL(ls_GWYBZ0,0)<>0 then --公务员补助 update BM_GHKSTJ set GHKS62 = NVL(GHKS62,0)+ls_GWYBZ0 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS62,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_GWYBZ0,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); end if; end if; if NVL(ls_MZJZJE,0)<>0 then --民政补救助 update BM_GHKSTJ set GHKS32 = NVL(GHKS32,0)+ls_MZBZJE where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS32,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,ls_BRID00,ls_JFCZY0,ls_MZBZJE,ad_ID0000,0,ls_DQBQ00,ls_ZYTS00,ls_ZYID00,ls_MC0000,ls_DBZBM0,1,0); end if; end if; END loop; CLOSE CUR_ZY_YBBRJS_BBTJ00; --2020.11.27 begin by ZYSF-20201020-002 begin select trim(VALUE0) into ls_RSHNLL from XT_XTCS00 where name00 in ('ZY_DBZHZBSFXSFHRSHNLL'); exception when others then ls_RSHNLL:='N'; end; if ls_RSHNLL='Y' then open CUR_ZY_YBBRJS_FHDBZ0; loop fetch CUR_ZY_YBBRJS_FHDBZ0 INTO ls_YBMC00,ls_BRID00,ls_ZYID00,ls_DQBQ00,ls_JFCZY0,ls_DBZBM0,LS_MC0000,LS_HJJE00; exit when CUR_ZY_YBBRJS_FHDBZ0%NOTFOUND; if nvl(ls_DBZBM0,'')<>'' and nvl(LS_MC0000,'')='' then begin select substrb(DBZMC0,1,100) into LS_MC0000 from BM_DBZDEB where DBZBM0=ls_DBZBM0 and rownum=1; exception when others then ls_MC0000:=null; end; end if; if NVL(LS_HJJE00,0) <>0 then --update BM_GHKSTJ set GHKS88 = NVL(GHKS88,0)+LS_HJJE00, GHKS64=1 update BM_GHKSTJ set GHKS64=1 where GHRQ00 = ls_YBMC00 and ID0000=ad_ID0000 and GHKS01 = ls_BRID00 and GHKS02 = ls_JFCZY0 and GHKS10 = ls_DQBQ00 and BEIZHU=ls_MC0000; if SQL%NOTFOUND then Insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS02,GHKS88,ID0000,XMHJ00,GHKS10,GHKS11,DYID00,BEIZHU,BEIZH6,GHKS63,GHKS64) values(ls_YBMC00,-1,ls_JFCZY0,0,ad_ID0000,0,ls_DQBQ00,0,ls_ZYID00,ls_MC0000,ls_DBZBM0,0,1); end if; end if; END loop; CLOSE CUR_ZY_YBBRJS_FHDBZ0; end if; --2020.11.27 end by ZYSF-20201020-002 update BM_GHKSTJ set GHKS17=GHKS62 where ID0000=ad_ID0000; if LS_FPMXTJ='Y' then --BEIZH1:当前科室名称 update BM_GHKSTJ set BEIZH1=(select B.BMMC00 from ZY_BRXXB0 A,BM_BMBM00 B where A.DQKS00=B.BMBH00 and A.ZYID00=DYID00 and rownum=1) where ID0000=ad_ID0000; -- BEIZH2:主治医生 update BM_GHKSTJ set BEIZH2=(select B.ZWXM00 from ZY_BRXXB0 A,BM_YGBM00 B where nvl(A.ZZYS00,A.ZRYS00)=B.YGBH00 and A.ZYID00=DYID00 and rownum=1) where ID0000=ad_ID0000; --循环插入发票明细项目 open CUR_ZY_YBBRJS_FPMX00; loop fetch CUR_ZY_YBBRJS_FPMX00 into LS_YBMC00,LS_BRID00,LS_ZYID00,LS_DQBQ00,LS_JFCZY0,LS_DBZBM0,LS_MC0000,LS_FPXMBH, LS_HJJE00; exit when CUR_ZY_YBBRJS_FPMX00%NOTFOUND; if nvl(ls_DBZBM0,'')<>'' and nvl(LS_MC0000,'')='' then begin select substrb(DBZMC0,1,100) into LS_MC0000 from BM_DBZDEB where DBZBM0=ls_DBZBM0 and rownum=1; exception when others then ls_MC0000:=null; end; end if; if LS_FPXMBH=1 then --西药费 GHKS41 update BM_GHKSTJ set GHKS41 = nvl(GHKS41,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=2 then --成药费 GHKS42 update BM_GHKSTJ set GHKS42 = nvl(GHKS42,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=3 then --草药费 GHKS43 update BM_GHKSTJ set GHKS43 = nvl(GHKS43,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=4 then --挂号费 GHKS44 update BM_GHKSTJ set GHKS44 = nvl(GHKS44,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=5 then --床位费 GHKS45 update BM_GHKSTJ set GHKS45 = nvl(GHKS45,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=6 then --诊察费 GHKS46 update BM_GHKSTJ set GHKS46 = nvl(GHKS46,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=7 then --护理费 GHKS47 update BM_GHKSTJ set GHKS47 = nvl(GHKS47,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=8 then --检查费 GHKS48 update BM_GHKSTJ set GHKS48 = nvl(GHKS48,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=9 then --化验费 GHKS49 update BM_GHKSTJ set GHKS49 = nvl(GHKS49,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=10 then --治疗费 GHKS50 update BM_GHKSTJ set GHKS50 = nvl(GHKS50,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=11 then --手术费 GHKS51 update BM_GHKSTJ set GHKS51 = nvl(GHKS51,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=12 then --救护车费 GHKS52 update BM_GHKSTJ set GHKS52 = nvl(GHKS52,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=13 then --其他费 GHKS53 update BM_GHKSTJ set GHKS53 = nvl(GHKS53,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=14 then --MRI费 GHKS54 update BM_GHKSTJ set GHKS54 = nvl(GHKS54,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=15 then --CT费 GHKS55 update BM_GHKSTJ set GHKS55 = nvl(GHKS55,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=16 then --彩超费 GHKS56 update BM_GHKSTJ set GHKS56 = nvl(GHKS56,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=17 then --输氧费 GHKS57 update BM_GHKSTJ set GHKS57 = nvl(GHKS57,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=18 then --输血费 GHKS58 update BM_GHKSTJ set GHKS58 = nvl(GHKS58,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=19 then --麻醉费 GHKS59 update BM_GHKSTJ set GHKS59 = nvl(GHKS59,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; elsif LS_FPXMBH=20 then --麻醉相关项目费 GHKS60 update BM_GHKSTJ set GHKS60 = nvl(GHKS60,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; else --其他医疗费 GHKS61 update BM_GHKSTJ set GHKS61 = nvl(GHKS61,0)+LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; end if; end loop; close CUR_ZY_YBBRJS_FPMX00; end if; --循环插入“病种定额”和“除外耗材费” open CUR_ZY_YBBRJS_BZFPMX; loop fetch CUR_ZY_YBBRJS_BZFPMX into LS_YBMC00,LS_BRID00,LS_ZYID00,LS_DQBQ00,LS_JFCZY0,LS_DBZBM0,LS_MC0000,LS_HJJE00,LS_HJJE01; exit when CUR_ZY_YBBRJS_BZFPMX%NOTFOUND; if nvl(ls_DBZBM0,'')<>'' and nvl(LS_MC0000,'')='' then begin select substrb(DBZMC0,1,100) into LS_MC0000 from BM_DBZDEB where DBZBM0=ls_DBZBM0 and rownum=1; exception when others then ls_MC0000:=null; end; end if; -- 病种定额(不需要累计) update BM_GHKSTJ set GHKS65 = LS_HJJE00 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; -- 除外耗材费 update BM_GHKSTJ set GHKS66 = nvl(GHKS66,0)+LS_HJJE01 where ID0000=ad_ID0000 and GHRQ00=LS_YBMC00 and GHKS01=LS_BRID00 and GHKS02=LS_JFCZY0 and GHKS10=LS_DQBQ00 and BEIZHU=LS_MC0000 and DYID00=ls_ZYID00; end loop; close CUR_ZY_YBBRJS_BZFPMX; /* select count(*) into ls_COUNT0 from BM_GHKSTJ where ID0000 = ad_ID0000; --添加一条总计行 Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,XMHJ00,GHKS11,GHKS09,GHKS12,GHKS13,GHKS26,GHKS27, GHKS41,GHKS42,GHKS43,GHKS44,GHKS45,GHKS46,GHKS47,GHKS48,GHKS49,GHKS50, GHKS51,GHKS52,GHKS53,GHKS54,GHKS55,GHKS56,GHKS57,GHKS58,GHKS59,GHKS60, GHKS61,GHKS62) select ad_ID0000,' 合计(共'||to_char(ls_COUNT0)||'人)',0,0,sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07), sum(GHKS08),sum(XMHJ00),sum(GHKS11),SUM(GHKS09),SUM(GHKS12),sum(GHKS13),sum(GHKS26),sum(GHKS27), sum(GHKS41) XYF000,sum(GHKS42) CYF000,sum(GHKS43) ZCYF00,sum(GHKS44) GHF000,sum(GHKS45) CWF000,sum(GHKS46) ZCF000,sum(GHKS47) HLF000, sum(GHKS48) JCF000,sum(GHKS49) HYF000,sum(GHKS50) ZLF000,sum(GHKS51) SSF000,sum(GHKS52) JHCF00,sum(GHKS53) QTF000,sum(GHKS54) MRIF00, sum(GHKS55) CTF000,sum(GHKS56) CCF000,sum(GHKS57) SYF000,sum(GHKS58) SXF000,sum(GHKS59) MZF000,sum(GHKS60) MZXGF0,sum(GHKS61) ,sum(GHKS62) from BM_GHKSTJ where ID0000 = ad_ID0000; */ --commit; EXCEPTION when OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_YBBRJS_DBZTJB('||as_KSRQ00||','||as_JSRQ00||',ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); ROLLBACK; END;