CREATE OR REPLACE PROCEDURE SP_ZY_KSSRHZ_RBB000 ( as_KSRQ00 in char, --开始日期 as_KSSJ00 in char, --开始时间 as_JSRQ00 in char, --结束日期 as_JSSJ00 in char, --结束时间 as_TJLB00 in char, --统计类别,'0'-发票项目 '1'-一级核算项目 2-二级核算项目 as_KSLB00 in char, --统计科室类别,'0':开单科室,'1':执行科室,'2':医生组 as_KSBH00 in char, --统计的科室编号 as_ybmc00 in char, --医保名称 as_jzlb00 in char, --记账类别'0'-全部,'1'-自付,'2'-记账,'3'-减免 as_YYID00 in char, --医院ID >0 各分院 =0全部 ad_ID0000 out number, --统计报表对应的ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 ad_YSZID0 in number default -1 --统计医生组 ) as ls_FPBH00 BM_YYHSXM.BH0000%TYPE; ls_FPXMMC BM_FPXM00.XMMC00%TYPE; ls_TJLB00 BM_FPXM00.BH0000%TYPE; ls_HJJE00 BM_MZSRTJ.xmje01%type;--ZY_FYMX00.HJJE00%TYPE; ls_SFTJGJJE varchar2(10); ls_GJJE00 BM_MZSRTJ.xmje01%type;--ZY_FYMX00.HJJE00%TYPE; ls_KSLB00 char(1); --统计类型 '0':开单科室,'1':执行科室,'2':医生组,'3' 执行药品为药房 cursor CUR_ZY_BM_FPXM00_TJ IS select '0' TJLB00,BH0000,XMMC00 from BM_FPXM00 union all select distinct '1' TJLB00,YJHSBH,HSXMMC from VW_BM_YJHSXM union all select '2' TJLB00,EJHSBH,HSXMMC from VW_BM_EJHSXM; -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2008.02.22 根据分院进行统计KDBQ00->KDKS00; -- jinfl 2013.01.09 解决统计一级核算时多条显示问题 ZYSF-20130109-001 -- linzy 2014.06.11 增加入参ad_YSZID0 统计医生组 by ZYSF-20140609-003 -- dsm 2016.07.01 增加购进金额的统计 for ZYSF-20160516-001 -- dsm 2019.07.10 连BM_BRXXB0的地方改ZY_BRXXB0 因为如果ZY_BRXXB0的费别如果和BM_BRXXB0不一致会导致查不出数据 ZYSF-20190710-003 -- dsm 2019.09.09 修正ZYSF-20190710-003的bug and D.ZYID00=C.ZYID00 for ZYSF-20190910-005 -- linbin 2020.07.31 药品的执行科室为药房系统参数ZY_ZYKSXMTJYPZXKSWYF控制 by ZYSF-20200721-002 -- ------- ------------ -------------------------------------------------------- begin begin --参数：住院科室收入统计报表药品执行科室是否为药房 select decode(count(VALUE0),0,as_KSLB00,'3') into ls_KSLB00 from XT_XTCS00 where NAME00='ZY_ZYKSXMTJYPZXKSWYF' and trim(VALUE0)='Y'; exception when others then ls_SFTJGJJE:='N' ; end; begin select nvl(max(trim(VALUE0)),'N') into ls_SFTJGJJE from XT_XTCS00 where NAME00='SF_KSSRHZTJGJJE' and VALUE0='Y'; exception when others then ls_SFTJGJJE:='N' ; end; select SQ_BM_MZSRTJ_ID0000.nextval into ad_ID0000 from dual; if as_TJLB00='0' then insert into BM_MZSRTJ(ID0000,XMBH01,XMBH02,XMJE01,XMJE02,BZ0000) select ID0000,BH0000,KSBH00,sum(XMJE01),sum(XMJE02),ls_SFTJGJJE from ( select ad_ID0000 ID0000,F.BH0000, decode(ls_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0),'3',decode(sign(A.XMBH00-9999999901),-1,A.ZXKS00,nvl(J.YFBMBH,K.YFBMBH))) KSBH00, decode(as_JZLB00,'0',sum(A.HJJE00),'1',sum(A.HJJE00-A.JZJE00-A.GFJE00),'2',sum(A.JZJE00),sum(A.GFJE00)) XMJE01,0 XMJE02 from ( select nvl(( select max(CFLSH0) from YF_ZYCF00 where DJH000 = B.DJH000 and YPDLBH = ( select YPDLBH from BM_YPLB00 where SFDM00=B.XMBH00 ) ),'') CFLSH0, nvl(( select max(YPQLPC) from YF_YZYPSQ where DJH000 = B.DJH000 and YPDLBH = ( select YPDLBH from BM_YPLB00 where SFDM00=B.XMBH00 ) ),0) YPQLPC, C.BRID00,C.ZYID00,B.XMBH00,B.KDKS00,B.ZXKS00,B.HJJE00,B.JZJE00,B.GFJE00,B.YSZID0 from ZY_FYMX00 B,ZY_BRFY00 C where B.DJH000 = C.DJH000 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00 ) A, BM_YYSFXM E,BM_FPXM00 F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H, YF_ZYCF00 J, YF_YZYPSQ K where A.XMBH00 = E.SFXMID and A.KDKS00 = H.BMBH00 and E.ZYFPID = F.FPXMID and D.BRID00 = A.BRID00 and D.ZYID00 = A.ZYID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and A.CFLSH0 = J.CFLSH0(+) and A.YPQLPC = K.YPQLPC(+) and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and ((ls_KSLB00='0' and KDKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and ZXKS00=as_KSBH00) or as_KSBH00 = '-1') and (ad_YSZID0=-1 or ad_YSZID0=nvl(A.YSZID0,0)) and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') group by F.BH0000,decode(ls_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0),'3',decode(sign(A.XMBH00-9999999901),-1,A.ZXKS00,nvl(J.YFBMBH,K.YFBMBH))) union all select ad_ID0000,F.BH0000,decode(ls_KSLB00,'0',I.DQKS00,'1',I.YFBMBH,'2',nvl(C.YSZID0,0),'3',I.YFBMBH) KSBH00,0,round(sum(J.YPZSL0*J.GJDJ00),2) from ZY_BRFY00 C,BM_YYSFXM E,BM_FPXM00 F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,YF_ZYCF00 I,YF_ZYCFMX J,BM_YPLB00 K where I.DJH000 = C.DJH000 and J.YPDLBH = K.LBBH00 and K.SFDM00 = E.SFXMID and I.DQKS00 = H.BMBH00 and E.ZYFPID = F.FPXMID and D.BRID00 = C.BRID00 and D.ZYID00 = C.ZYID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00 and ((ls_KSLB00='0' and I.DQKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and I.YFBMBH=as_KSBH00) or as_KSBH00 = '-1') and (H.YYID00 = as_YYID00 or as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(C.YSZID0,0)) and (G.YBMC00 = as_YBMC00 or as_YBMC00 = '所有病人') and I.CFLSH0=J.CFLSH0 and ls_SFTJGJJE='Y' group by F.BH0000,decode(ls_KSLB00,'0',I.DQKS00,'1',I.YFBMBH,'2',nvl(C.YSZID0,0),'3',I.YFBMBH) union all select ad_ID0000,F.BH0000,decode(ls_KSLB00,'0',J.DQKS00,'1',J.YFBMBH,'2',nvl(C.YSZID0,0),'3',J.YFBMBH) KSBH00,0,round(sum(J.YPZSL0*J.GJDJ00),2) from ZY_BRFY00 C,BM_YYSFXM E,BM_FPXM00 F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,YF_YZYPSQ J,BM_YPLB00 K where J.DJH000 = C.DJH000 and J.YPDLBH = K.LBBH00 and K.SFDM00 = E.SFXMID and J.DQKS00 = H.BMBH00 and E.ZYFPID = F.FPXMID and D.BRID00 = C.BRID00 and D.ZYID00 = C.ZYID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00 and ((ls_KSLB00='0' and J.DQKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and J.YFBMBH=as_KSBH00) or as_KSBH00 = '-1') and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(C.YSZID0,0)) and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') and ls_SFTJGJJE='Y' group by F.BH0000,decode(ls_KSLB00,'0',J.DQKS00,'1',YFBMBH,'2',nvl(C.YSZID0,0),'3',J.YFBMBH) ) group by ID0000,BH0000,KSBH00 ; elsif as_TJLB00='1' then insert into BM_MZSRTJ(ID0000,XMBH01,XMBH02,XMJE01,XMJE02,BZ0000) select ID0000,BH0000,KSBH00,sum(XMJE01),sum(XMJE02),ls_SFTJGJJE from ( select ad_ID0000 ID0000,F.YJHSBH BH0000, decode(ls_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0),'3',decode(sign(A.XMBH00-9999999901),-1,A.ZXKS00,nvl(J.YFBMBH,K.YFBMBH))) KSBH00, decode(as_JZLB00,'0',sum(A.HJJE00),'1',sum(A.HJJE00-A.JZJE00-A.GFJE00),'2',sum(A.JZJE00),sum(A.GFJE00)) XMJE01,0 XMJE02 from ( select nvl(( select max(CFLSH0) from YF_ZYCF00 where DJH000 = B.DJH000 and YPDLBH = ( select YPDLBH from BM_YPLB00 where SFDM00=B.XMBH00 ) ),'') CFLSH0, nvl(( select max(YPQLPC) from YF_YZYPSQ where DJH000 = B.DJH000 and YPDLBH = ( select YPDLBH from BM_YPLB00 where SFDM00=B.XMBH00 ) ),0) YPQLPC, C.BRID00,C.ZYID00,B.XMBH00,B.KDKS00,B.ZXKS00,B.HJJE00,B.JZJE00,B.GFJE00,B.YSZID0 from ZY_FYMX00 B,ZY_BRFY00 C where B.DJH000 = C.DJH000 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00 ) A, BM_YYSFXM E,VW_BM_YJHSXM F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H, YF_ZYCF00 J, YF_YZYPSQ K where A.XMBH00 = E.SFXMID and A.KDKS00 = H.BMBH00 and E.HSXMID = F.HSXMID and D.BRID00 = A.BRID00 and D.ZYID00 = A.ZYID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and A.CFLSH0 = J.CFLSH0(+) and A.YPQLPC = K.YPQLPC(+) and ((ls_KSLB00='0' and KDKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and ZXKS00=as_KSBH00) or as_KSBH00 = '-1') and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(A.YSZID0,0)) and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') group by F.YJHSBH,decode(ls_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0),'3',decode(sign(A.XMBH00-9999999901),-1,A.ZXKS00,nvl(J.YFBMBH,K.YFBMBH))) union all select ad_ID0000,F.YJHSBH,decode(ls_KSLB00,'0',I.DQKS00,'1',I.YFBMBH,'2',nvl(C.YSZID0,0),'3',I.YFBMBH) KSBH00,0,round(sum(J.YPZSL0*J.GJDJ00),2) from ZY_BRFY00 C,BM_YYSFXM E,VW_BM_YJHSXM F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,YF_ZYCF00 I,YF_ZYCFMX J,BM_YPLB00 K where I.DJH000 = C.DJH000 and J.YPDLBH = K.LBBH00 and K.SFDM00 = E.SFXMID and I.DQKS00 = H.BMBH00 and E.HSXMID = F.HSXMID and D.BRID00 = C.BRID00 and D.ZYID00 = C.ZYID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00 and ((ls_KSLB00='0' and I.DQKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and YFBMBH=as_KSBH00) or as_KSBH00 = '-1') and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(C.YSZID0,0)) and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') and I.CFLSH0=J.CFLSH0 and ls_SFTJGJJE='Y' group by F.YJHSBH,decode(ls_KSLB00,'0',I.DQKS00,'1',I.YFBMBH,'2',nvl(C.YSZID0,0),'3',I.YFBMBH) union all select ad_ID0000,F.YJHSBH,decode(ls_KSLB00,'0',J.DQKS00,'1',J.YFBMBH,'2',nvl(C.YSZID0,0),'3',J.YFBMBH) KSBH00,0,round(sum(J.YPZSL0*J.GJDJ00),2) from ZY_BRFY00 C,BM_YYSFXM E,VW_BM_YJHSXM F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,YF_YZYPSQ J,BM_YPLB00 K where J.DJH000 = C.DJH000 and J.YPDLBH = K.LBBH00 and K.SFDM00 = E.SFXMID and J.DQKS00 = H.BMBH00 and E.HSXMID = F.HSXMID and D.BRID00 = C.BRID00 and D.ZYID00 = C.ZYID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00 and ((ls_KSLB00='0' and J.DQKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and YFBMBH=as_KSBH00) or as_KSBH00 = '-1') and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(C.YSZID0,0)) and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') and ls_SFTJGJJE='Y' group by F.YJHSBH,decode(ls_KSLB00,'0',J.DQKS00,'1',J.YFBMBH,'2',nvl(C.YSZID0,0),'3',J.YFBMBH) ) group by ID0000,BH0000,KSBH00 ; else insert into BM_MZSRTJ(ID0000,XMBH01,XMBH02,XMJE01,XMJE02,BZ0000) select ID0000,BH0000,KSBH00,sum(XMJE01),sum(XMJE02),ls_SFTJGJJE from ( select ad_ID0000 ID0000,F.EJHSBH BH0000, decode(ls_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0),'3',decode(sign(A.XMBH00-9999999901),-1,A.ZXKS00,nvl(J.YFBMBH,K.YFBMBH))) KSBH00, decode(as_JZLB00,'0',sum(A.HJJE00),'1',sum(A.HJJE00-A.JZJE00-A.GFJE00),'2',sum(A.JZJE00),sum(A.GFJE00)) XMJE01,0 XMJE02 from ( select nvl(( select max(CFLSH0) from YF_ZYCF00 where DJH000 = B.DJH000 and YPDLBH = ( select YPDLBH from BM_YPLB00 where SFDM00=B.XMBH00 ) ),'') CFLSH0, nvl(( select max(YPQLPC) from YF_YZYPSQ where DJH000 = B.DJH000 and YPDLBH = ( select YPDLBH from BM_YPLB00 where SFDM00=B.XMBH00 ) ),0) YPQLPC, C.DJH000,C.BRID00,C.ZYID00,B.XMBH00,B.KDKS00,B.ZXKS00,B.HJJE00,B.JZJE00,B.GFJE00,B.YSZID0 from ZY_FYMX00 B,ZY_BRFY00 C where B.DJH000 = C.DJH000 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00 ) A, BM_YYSFXM E,VW_BM_EJHSXM F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H, YF_ZYCF00 J, YF_YZYPSQ K where A.XMBH00 = E.SFXMID and A.KDKS00 = H.BMBH00 and E.HSXMID = F.HSXMID and D.BRID00 = A.BRID00 and D.ZYID00 = A.ZYID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and A.CFLSH0 = J.CFLSH0(+) and A.YPQLPC = K.YPQLPC(+) and ((ls_KSLB00='0' and KDKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and ZXKS00=as_KSBH00) or as_KSBH00 = '-1') and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') and (ad_YSZID0=-1 or ad_YSZID0=nvl(A.YSZID0,0)) group by F.EJHSBH,decode(ls_KSLB00,'0',KDKS00,'1',ZXKS00,'2',nvl(A.YSZID0,0),'3',decode(sign(A.XMBH00-9999999901),-1,A.ZXKS00,nvl(J.YFBMBH,K.YFBMBH))) union all select ad_ID0000,F.EJHSBH,decode(ls_KSLB00,'0',I.DQKS00,'1',I.YFBMBH,'2',nvl(C.YSZID0,0),'3',I.YFBMBH) KSBH00,0,round(sum(J.YPZSL0*J.GJDJ00),2) from ZY_BRFY00 C,BM_YYSFXM E,VW_BM_EJHSXM F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,YF_ZYCF00 I,YF_ZYCFMX J,BM_YPLB00 K where I.DJH000 = C.DJH000 and J.YPDLBH = K.LBBH00 and K.SFDM00 = E.SFXMID and I.DQKS00 = H.BMBH00 and E.HSXMID = F.HSXMID and D.BRID00 = C.BRID00 and D.ZYID00 = C.ZYID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00 and ((ls_KSLB00='0' and I.DQKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and I.YFBMBH=as_KSBH00) or as_KSBH00 = '-1') and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(C.YSZID0,0)) and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') and I.CFLSH0=J.CFLSH0 and ls_SFTJGJJE='Y' group by F.EJHSBH,decode(ls_KSLB00,'0',I.DQKS00,'1',I.YFBMBH,'2',nvl(C.YSZID0,0),'3',I.YFBMBH) union all select ad_ID0000,F.EJHSBH,decode(ls_KSLB00,'0',J.DQKS00,'1',J.YFBMBH,'2',nvl(C.YSZID0,0),'3',J.YFBMBH) KSBH00,0,round(sum(J.YPZSL0*J.GJDJ00),2) from ZY_BRFY00 C,BM_YYSFXM E,VW_BM_EJHSXM F,ZY_BRXXB0 D,IC_YBBRLB G,BM_BMBM00 H,YF_YZYPSQ J,BM_YPLB00 K where J.DJH000 = C.DJH000 and J.YPDLBH = K.LBBH00 and K.SFDM00 = E.SFXMID and J.DQKS00 = H.BMBH00 and E.HSXMID = F.HSXMID and D.BRID00 = C.BRID00 and D.ZYID00 = C.ZYID00 and D.FBBH00 = G.FBBH00 and D.YBLB00 = G.YBLB00 and C.CZRQ00 >= as_KSRQ00 and C.CZRQ00 <= as_JSRQ00 and C.CZRQ00||C.CZSJ00 >= as_KSRQ00||as_KSSJ00 and C.CZRQ00||C.CZSJ00 <= as_JSRQ00||as_JSSJ00 and ((ls_KSLB00='0' and J.DQKS00=as_KSBH00) OR (ls_KSLB00 in ('1','3') and J.YFBMBH=as_KSBH00) or as_KSBH00 = '-1') and (H.YYID00 = as_YYID00 OR as_YYID00 = '0') and (ad_YSZID0=-1 or ad_YSZID0=nvl(C.YSZID0,0)) and (G.YBMC00=as_YBMC00 or as_YBMC00 = '所有病人') and ls_SFTJGJJE='Y' group by F.EJHSBH,decode(ls_KSLB00,'0',J.DQKS00,'1',J.YFBMBH,'2',nvl(C.YSZID0,0),'3',J.YFBMBH) ) group by ID0000,BH0000,KSBH00 ; end if; if ls_KSLB00='2' then update BM_MZSRTJ A set XMMC02=(select YSZMC0 from ZS_YSZ000 where YSZID0=to_number(A.XMBH02)) where ID0000=ad_ID0000; else update BM_MZSRTJ A set XMMC02=(select BMMC00 from BM_BMBM00 where BMBH00=to_number(A.XMBH02)) where ID0000=ad_ID0000; end if; --添加一条合计数 insert into BM_MZSRTJ(ID0000,XMBH01,XMMC02,XMJE01,BZ0000,XMJE02) select ad_ID0000,'00',' 合计',sum(NVL(XMJE01,0)),ls_SFTJGJJE,sum(nvl(XMJE02,0)) from BM_MZSRTJ where ID0000 = ad_ID0000 ; open CUR_ZY_BM_FPXM00_TJ; loop fetch CUR_ZY_BM_FPXM00_TJ into ls_TJLB00,ls_FPBH00,ls_FPXMMC; exit when CUR_ZY_BM_FPXM00_TJ%NOTFOUND; if ls_TJLB00=as_TJLB00 then select nvl(sum(XMJE01),0),nvl(sum(XMJE02),0) into ls_HJJE00,ls_GJJE00 from BM_MZSRTJ where ID0000 = ad_ID0000 and XMBH01 = ls_FPBH00; insert into BM_MZSRTJ(ID0000,XMBH01,XMMC01,XMBH02,XMMC02,XMJE01,BZ0000,XMJE02) values(ad_ID0000,ls_FPBH00,ls_FPXMMC,'-1',' 小计',ls_HJJE00,ls_SFTJGJJE,ls_GJJE00); end if; end loop; close CUR_ZY_BM_FPXM00_TJ; commit; exception when OTHERS then as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_ZY_KSSRHZ_RBB000('||as_KSRQ00||','||as_KSSJ00||','||as_JSRQ00||','|| as_JSSJ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); ROLLBACK; end;