CREATE OR REPLACE PROCEDURE SP_ZY_JKKJXX_TJ0000 ( as_KSRQ00 in char, as_JSRQ00 in char, as_KSSJ00 in char, as_JSSJ00 in char, as_SFY000 in char, --收费员姓名 as_YYID00 in char, --医院ID ad_ID0000 out number, as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_TJFS00 in char default '0' --0:所有 ) as ls_YGBH00 BM_YGBM00.YGBH00%TYPE; ls_ZWXM00 BM_YGBM00.ZWXM00%TYPE; ls_JKDH00 BM_JKB000.JKDH00%TYPE; ls_XMJE00 BM_JKKJMX.XMJE00%TYPE ; --项目金额0 ls_BEIZH0 BM_JKKJMX.BEIZHU%TYPE ; --备注 ls_BEIZH1 BM_JKKJMX.BEIZHU%TYPE ; --备注1 ls_BEIZH2 BM_JKKJMX.BEIZHU%TYPE ; --备注1 ls_TEXT00 VARCHAR2(600); ls_COUNT0 NUMBER(5); ls_ZPSR00 NUMBER(12,2); --支票收入 ls_YHKSR0 NUMBER(12,2); --银行卡收入 ls_YHKJS0 NUMBER(12,2); --银行卡结算 ls_YBJS00 NUMBER(12,2); --医保结算 ls_JMBRYF NUMBER(12,2); --减免病人医药费 ls_CZYFY0 NUMBER(12,2); --冲住院费用 LS_JZJE00 NUMBER(12,2); --记账金额 ls_TJZJE0 NUMBER(12,2); --退记账款 ls_JZYSK0 NUMBER(12,2); --记账应收款 ls_CYYSK0 NUMBER(12,2); --出院病人应收款 ls_XMJE01 NUMBER(12,2); ls_XMJE02 NUMBER(12,2); ls_XMJE03 NUMBER(12,2); ls_XMJE04 NUMBER(12,2); ls_XMJE05 NUMBER(12,2); ls_XMJE06 NUMBER(12,2); ls_XMJE07 NUMBER(12,2); ls_XMJE08 NUMBER(12,2); ls_XMJE09 NUMBER(12,2); ls_XMJE10 NUMBER(12,2); ls_XMJE11 NUMBER(12,2); ls_XMJE12 NUMBER(12,2); ls_XMJE13 NUMBER(12,2); ls_XMJE14 NUMBER(12,2); ls_XMJE15 NUMBER(12,2); ls_XMJE16 NUMBER(12,2); ls_XMJE17 NUMBER(12,2); ls_XMJE18 NUMBER(12,2); ls_XMJE19 NUMBER(12,2); ls_XMJE20 NUMBER(12,2); ls_XMJE21 NUMBER(12,2); ls_XMJE22 NUMBER(12,2); ls_XMJE23 NUMBER(12,2); ls_XMJE24 NUMBER(12,2); ls_XMJE25 NUMBER(12,2); ls_XMJE26 NUMBER(12,2); ls_XMJE27 NUMBER(12,2); ls_XMJE28 NUMBER(12,2); ls_XMJE29 NUMBER(12,2); --保存在医院系统中计算的医保个人帐户支付的金额 ls_XMJE30 NUMBER(12,2); --暂时未使用 ls_XMJE31 NUMBER(12,2); --暂时未使用 ls_XMJE32 NUMBER(12,2); --暂时未使用 ls_XMJE33 NUMBER(12,2); ls_XMJE34 NUMBER(12,2); ls_XMJE35 NUMBER(12,2); --收预交金 ls_XMJE36 NUMBER(12,2); --退预交金 ls_sswr00 NUMBER(12,2); --四舍五入金额 ls_sswr01 NUMBER(12,2); --四舍五入金额 ls_KSBH00 BM_GHKSTJ_KSDY00.BH0000%TYPE; ls_KSBHJS NUMBER(4); CURSOR CUR_ZY_BRJFB0_SFY000 IS select YGBH00,ZWXM00 from BM_YGBM00 B where exists( select 1 from ZY_BRJFB0 A where A.JFCZY0 = B.YGBH00 AND A.JFRQ00 >= as_KSRQ00 AND A.JFRQ00 <= as_JSRQ00 AND A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 AND A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00) AND (B.ZWXM00||'_'||B.XKH000 = as_SFY000 or as_SFY000='所有收费员'); BEGIN --7、应缴金额=1、预交金收入+2、住院票据金额-3、预交金退出-4、退款收据+(4)现金暂存 ls_XMJE29:=0; --保存在医院系统中计算的医保个人帐户支付的金额 ls_KSBH00:='01'; ls_KSBHJS:=1; Select SQ_BM_YYSFTJ_ID0000.nextval into ad_ID0000 from dual; OPEN CUR_ZY_BRJFB0_SFY000; LOOP FETCH CUR_ZY_BRJFB0_SFY000 INTO ls_YGBH00,ls_ZWXM00; EXIT WHEN CUR_ZY_BRJFB0_SFY000%NOTFOUND; Insert into BM_GHKSTJ_KSDY00(ID0000,BH0000,GHKSBH,GHKSMC) values(ad_ID0000,ls_KSBH00,ls_YGBH00,ls_ZWXM00); ls_KSBHJS:=ls_KSBHJS+1; Select lpad(to_char(ls_KSBHJS),2,'0') into ls_KSBH00 from dual; END LOOP; CLOSE CUR_ZY_BRJFB0_SFY000; insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,1 ,' 1、预交金收入',0); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,37 ,' (1)、收预交金',1); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,38 ,' (2)、退预交金',2); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,2 ,' 2、住院票据金额',3); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,3 ,' 其中: (1)冲预收款',4); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,4 ,' (2)冲原收据',5); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,5 ,' (3)补交现金',6); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,6 ,' (4)补交支票',7); -- insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,7 ,' (5)银行卡结算',8); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,8 ,' (6)医保结算',9); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,11,' (7)减免病人医药费',10); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,12,' (8)记帐金额',11); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,9 ,' (9)出院病人应收款',12); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,13,' 3、预交金退出',13); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,14,' 其中: (1)冲住院费用',14); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,15,' (2)现金退出',15); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,16,' (3)应退还转账',16); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,17,' (4)现金暂存',17); --insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,18,' (5)四舍五入',18); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,20,' 4、退款收据',19); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,21,' 其中:(1)退非医保款',20); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,22,' (2)退医保款',21); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,23,' (3)退记帐款',22); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,39,' (4)退减免款',23); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,40,' (5)退出院病人应收款',24); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,19,' 5、收回旧收据',25); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,10,' 6、补开新收据',26); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,24,' 7、应缴金额 ',27); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,25,' 8、支票收入 ',28); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,26,' 9、银行卡收入 ',29); -- insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,28,'10、银行卡结算 ',30); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,29,'11、医保结算 ',31); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,30,'12、出院病人应收款',32); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,31,'13、减免病人医药费',33); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,32,'14、记帐应收款',34); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,34,'16、四舍五入',35); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,35,'19、实交现金',36); insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS80) values(ad_ID0000,36,'20、7-15合计',37); OPEN CUR_ZY_BRJFB0_SFY000; LOOP FETCH CUR_ZY_BRJFB0_SFY000 INTO ls_YGBH00,ls_ZWXM00; EXIT WHEN CUR_ZY_BRJFB0_SFY000%NOTFOUND; ls_ZPSR00 :=0; --支票收入 ls_YHKSR0 :=0; --银行卡收入 ls_YHKJS0 :=0; --银行卡结算 ls_YBJS00 :=0; --医保结算 ls_JMBRYF :=0; --减免病人医药费 ls_XMJE01 :=0; ls_XMJE02 :=0; ls_XMJE03 :=0; ls_XMJE04 :=0; ls_XMJE05 :=0; ls_XMJE06 :=0; ls_XMJE07 :=0; ls_XMJE08 :=0; ls_XMJE09 :=0; ls_XMJE10 :=0; ls_XMJE11 :=0; ls_XMJE12 :=0; ls_XMJE13 :=0; ls_XMJE14 :=0; ls_XMJE15 :=0; ls_XMJE16 :=0; ls_XMJE17 :=0; ls_XMJE18 :=0; ls_XMJE19 :=0; ls_XMJE20 :=0; ls_XMJE21 :=0; ls_XMJE22 :=0; ls_XMJE23 :=0; ls_XMJE24 :=0; ls_XMJE25 :=0; ls_XMJE26 :=0; ls_XMJE27 :=0; ls_XMJE28 :=0; ls_xmje35 :=0; ls_sswr00 :=0; ls_sswr01 :=0; select BH0000 into ls_KSBH00 from BM_GHKSTJ_KSDY00 where ID0000 = ad_ID0000 and GHKSBH = ls_YGBH00; --==预交金 --Update 1.预交金收入 select NVL(sum(B.XJJE00),0),NVL(sum(B.ZZJE00),0),NVL(sum(YHKJE0),0), ltrim(min(lpad(trim(B.pjxh00),30,' ')))||' - '||ltrim(max(lpad(trim(B.pjxh00),30,' '))) into ls_XMJE00,ls_ZPSR00,ls_YHKSR0,ls_BEIZH0 from ZY_BRJFB0 A,ZY_PJSYQK B where A.PJH000 = B.PJH000 and B.PLBH00 in (4,8) and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and A.JFCZY0 = ls_YGBH00 and B.PJZT00='0' and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); ls_XMJE01 := NVL(ls_XMJE00,0)+NVL(ls_ZPSR00,0)+NVL(ls_YHKSR0,0); --1.1收预交金 select NVL(sum(B.XJJE00),0)+NVL(sum(B.ZZJE00),0)+NVL(sum(YHKJE0),0) into ls_xmje35 from ZY_BRJFB0 A,ZY_PJSYQK B where A.PJH000 = B.PJH000 and B.PLBH00 in (4,8) and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and A.JFBZ00='0' and B.PJZT00='0' and A.JFCZY0 = ls_YGBH00 and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); --1.2退预交金 select NVL(sum(B.XJJE00),0)+NVL(sum(B.ZZJE00),0)+NVL(sum(YHKJE0),0) into ls_xmje36 from ZY_BRJFB0 A,ZY_PJSYQK B where A.PJH000 = B.PJH000 and B.PLBH00 in (4,8) and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and A.JFBZ00='1' and B.PJZT00='0' and A.JFCZY0 = ls_YGBH00 and A.JZDH00 <> 1 --作废的预交金票据不计算在内! and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); --==2、住院票据 select nvl(sum(HJJE00),0),--住院票据 --NVL(sum(SBGRZH+SBTCJJ+decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF),0), --2.6医保结算 --sum(decode(trim(D.YBBHSB),'Y',NVL(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+GWYBZ0,0),NVL(SBGRZH+SBTCJJ+decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+GWYBZ0,0))),--2.6医保结算 modify by xzw NVL(sum(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,GWYBZ0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SBGRZH+SBTCJJ)),0), NVL(abs(sum(JZJE00)),0), --记账金额 NVL(sum(GFJE00),0), --减免病人医药费 NVL(sum(CYYSK0),0) --出院病人应收款 into ls_XMJE02,ls_YBJS00,ls_JZJE00,ls_JMBRYF,ls_CYYSK0 from ZY_JZB000 A,BM_BRXXB0 C,IC_YBBRLB D where A.BRID00=C.BRID00 and A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where DYRQ00 >= as_KSRQ00 and DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 >= as_KSRQ00||as_KSSJ00 and DYRQ00||DYSJ00 <= as_JSRQ00||as_JSSJ00 and CZY000=ls_YGBH00 and BDBZ00 in ('0','8','9') and PLBH00=3 and substr(PJXH00,1,1)<>'-' and exists (select 1 from BM_BMBM00 where BMBH00=CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0'))); --2(2)冲原收据,或叫冲原发票 select NVL(abs(sum(a.ZFJE00+decode(d.sfjsdw,'N',a.GRZHZF,0)-a.CYYSK0)),0) into ls_XMJE04 from ZY_JZB000 A,BM_BRXXB0 C,IC_YBBRLB D where A.BRID00=C.BRID00 and A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where DYRQ00 >= as_KSRQ00 and DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 >= as_KSRQ00||as_KSSJ00 and DYRQ00||DYSJ00 <= as_JSRQ00||as_JSSJ00 and CZY000=ls_YGBH00 and BDBZ00 in ('0','8','9') and PLBH00=3 and substr(PJXH00,1,1)='-' and exists (select 1 from BM_BMBM00 where BMBH00=CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0'))); select ltrim(min(lpad(trim(B.pjxh00),30,' ')))||' - '||ltrim(max(lpad(trim(B.pjxh00),30,' '))) into ls_BEIZH1 from ZY_PJSYQK B where B.PLBH00=3 and B.CZY000 = ls_YGBH00 and B.DYRQ00 >= as_KSRQ00 and B.DYRQ00 <= as_JSRQ00 and B.DYRQ00||B.DYSJ00 >= as_KSRQ00||as_KSSJ00 and B.DYRQ00||B.DYSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); select ltrim(min(lpad(trim(B.pjxh00),30,' ')))||' - '||ltrim(max(lpad(trim(B.pjxh00),30,' '))) into ls_BEIZH2 from ZY_PJSYQK B where B.PLBH00=10 and B.CZY000 = ls_YGBH00 and B.DYRQ00 >= as_KSRQ00 and B.DYRQ00 <= as_JSRQ00 and B.DYRQ00||B.DYSJ00 >= as_KSRQ00||as_KSSJ00 and B.DYRQ00||B.DYSJ00 <= as_JSRQ00||as_JSSJ00 and substr(B.PJXH00,1,1) <> '-' and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); ls_BEIZH1:=ls_BEIZH1||','||ls_BEIZH2; --2(3)补交现金,支票金额 select abs(nvl(sum(C.XJYE00+C.ZZYE00),0)) into ls_XMJE05 from ZY_JZB000 C WHERE C.PJH000 in (select FZPJH0 from ZY_PJSYQK where DYRQ00 >= as_KSRQ00 and DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 >= as_KSRQ00||as_KSSJ00 and DYRQ00||DYSJ00 <= as_JSRQ00||as_JSSJ00 and CZY000=ls_YGBH00 and BDBZ00 in ('0','8','9') and PLBH00 =3 and exists (select 1 from BM_BMBM00 where BMBH00=C.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0'))) and ((C.XJYE00+C.ZZYE00<0 and C.HJJE00>0) or (C.XJYE00+C.ZZYE00>0 and C.HJJE00<0)) and c.CYYSK0=0; --2(4)补交转账 select NVL(sum(A.JFJE00),0) into ls_XMJE06 from ZY_BRJFB0 A,ZY_PJSYQK B,ZY_JZB000 C WHERE A.PJH000 = B.FZPJH0 and C.PJH000 = B.FZPJH0 and B.PLBH00 = 3 and A.ZFFS00 = 2 and A.JFLBID = 0 and A.JFBZ00 = 0 and A.JFJE00 > 0 and C.CXJZDH+0 = 0 and B.DYRQ00 >= as_KSRQ00 and B.DYRQ00 <= as_JSRQ00 and B.DYRQ00||B.DYSJ00 >= as_KSRQ00||as_KSSJ00 and B.DYRQ00||B.DYSJ00 <= as_JSRQ00||as_JSSJ00 and B.CZY000=ls_YGBH00 and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); ls_XMJE05:=ls_XMJE05-ls_XMJE06; --2(1)冲预收款=冲住院费用 ,改为倒算 2.1=2-2.2-2.3-2.4-2.5-2.6-2.7-2.8-2.9 ls_XMJE03:=ls_XMJE02-ls_XMJE04-ls_XMJE05-ls_XMJE06-ls_YBJS00-ls_JZJE00-ls_JMBRYF-ls_CYYSK0; --as_YHMSG0:=to_char(ls_XMJE03)||','||to_char(ls_XMJE02)||','||to_char(ls_XMJE05)||','||to_char(ls_XMJE06)||','||to_char(ls_YBJS00)||','||to_char(ls_JZJE00)||','||to_char(ls_JMBRYF)||','||to_char(ls_CYYSK0); --ROLLBACK; --return; --计算应退还中的金额四舍五入部分 select nvl(sum(jfje00),0) into ls_sswr01 from zy_brjfb0 A, zy_pjsyqk B where A.JFCZY0 = ls_YGBH00 and B.PLBH00 =3 and A.pjh000 = B.fzpjh0 --and a.jflbid=2 --and b.pjzt00='0' --and substr(B.PJXH00,1,1) <> '-' and A.ZFFS00 = 5 and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and b.BDBZ00 in ('0','8','9') and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); --计算3、预交金退出金额 select NVL(sum(C.XJJE00+C.YHKJE0+C.ZZJE00),0) into ls_XMJE12 from ZY_PJSYQK C where C.PLBH00 in (4,8) and C.SFDY00 = 'Y' and C.HSRQ00 >=as_KSRQ00 and C.HSRQ00 <=as_JSRQ00 and C.HSRQ00||C.HSSJ00 >=as_KSRQ00||as_KSSJ00 and C.HSRQ00||C.HSSJ00 <=as_JSRQ00||as_JSSJ00 and C.HSCZY0 = ls_YGBH00 and C.PJZT00='0' and exists (select 1 from BM_BMBM00 where BMBH00=C.HSYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); --计算3(3)应退还转账 select NVL(abs(sum(JFJE00)),0) into ls_XMJE15 from zy_brjfb0 A, zy_pjsyqk B where A.pjh000 = B.fzpjh0 and A.JFCZY0 = ls_YGBH00 and A.JFRQ00 >= as_KSRQ00 and A.JFRQ00 <= as_JSRQ00 and A.JFRQ00||A.JFSJ00 >= as_KSRQ00||as_KSSJ00 and A.JFRQ00||A.JFSJ00 <= as_JSRQ00||as_JSSJ00 and B.PLBH00 = 3 --预交金的部分不添加,否则冲住院费用相减时会不平 and A.ZFFS00 = 2 and substr(A.JFLBID,1,1)= 2 and B.PJZT00='0' and exists (select 1 from BM_BMBM00 where BMBH00=A.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); --ls_ZPSR00:=ls_ZPSR00-ls_XMJE15; --3(4)计算现金暂存的 select NVL(sum(CYYTK0),0) into ls_XMJE16 from ZY_JZB000 A, ZY_PJSYQK B where A.PJH000 = B.FZPJH0 and B.PLBH00 = 3 and B.CZY000 = ls_YGBH00 and B.DYRQ00 >= as_KSRQ00 and B.DYRQ00 <= as_JSRQ00 and B.DYRQ00||B.DYSJ00 >= as_KSRQ00||as_KSSJ00 and B.DYRQ00||B.DYSJ00 <= as_JSRQ00||as_JSSJ00 --and substr(B.PJXH00,1,1) <> '-' and B.PJZT00= '0' and B.BDBZ00 in ('0','8','9') and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); --3(2)应退还现金,用倒算方法 = 退出金额 - 冲住院费用 - 转帐 - 现金暂存 ls_XMJE14:=ls_XMJE12-ls_XMJE03-ls_XMJE16-ls_XMJE15; --3(2)应退还现金 --select sum(a.xjye00) into ls_XMJE14 from ZY_JZB000 A,BM_BRXXB0 C,IC_YBBRLB D -- where A.BRID00=C.BRID00 and C.YBLB00=D.YBLB00 and C.FBBH00=D.FBBH00 and -- A.PJH000 in (select FZPJH0 from ZY_PJSYQK where CZY000=ls_YGBH00 and DYRQ00>=as_KSRQ00 -- and DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 >= as_KSRQ00||as_KSSJ00 -- and DYRQ00||DYSJ00 <= as_JSRQ00||as_JSSJ00 and BDBZ00='0' and PLBH00=3 ); ---收回旧收据(补打发票) select NVL(sum(xjje00+zzje00+yhkje0),0) into ls_XMJE10 from ZY_PJSYQK B where B.PLBH00 in(3,10) --and PJZT00='0' and B.CZY000 = ls_YGBH00 and substr(B.PJXH00,1,1) <> '-' and B.DYRQ00 >= as_KSRQ00 and B.DYRQ00 <= as_JSRQ00 and B.DYRQ00||B.DYSJ00 >= as_KSRQ00||as_KSSJ00 and B.DYRQ00||B.DYSJ00 <= as_JSRQ00||as_JSSJ00 and B.BDBZ00='1'---补打发票 and exists (select 1 from BM_BMBM00 where BMBH00=B.CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0')); --==6、退款票据 select abs(nvl(sum(HJJE00),0)),--退款收据 --abs(NVL(sum(SBGRZH+SBTCJJ+decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF),0)),--退款的医保款 --abs(NVL(sum(decode(trim(D.YBBHSB),'Y',decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+GWYBZ0,SBGRZH+SBTCJJ+decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+GWYBZ0)),0)),--退款的医保款 abs(NVL(sum(decode(d.sfjsdw,'N',0,GRZHZF)+TCJJZF+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',15,')),1,0,GWYBZ0)+decode(sign(Instrb(','||trim(d.ybbhsb)||',',',14,')),1,0,SBGRZH+SBTCJJ)),0)), abs(NVL(sum(JZJE00),0)),--退记账款 abs(NVL(sum(GFJE00),0)),--退减免款 abs(NVL(sum(CYYSK0),0)),--退出院病人应收款 count(*) into ls_XMJE19,ls_XMJE20,ls_TJZJE0,ls_XMJE25,ls_XMJE26,ls_COUNT0 from ZY_JZB000 A,BM_BRXXB0 C,IC_YBBRLB D where A.BRID00=C.BRID00 and A.YBLB00=D.YBLB00 and A.FBBH00=D.FBBH00 and A.PJH000 in (select FZPJH0 from ZY_PJSYQK where CZY000 = ls_YGBH00 and DYRQ00 >= as_KSRQ00 and DYRQ00 <= as_JSRQ00 and DYRQ00||DYSJ00 >= as_KSRQ00||as_KSSJ00 and DYRQ00||DYSJ00 <= as_JSRQ00||as_JSSJ00 and BDBZ00 in ('0','8','9') and PLBH00=3 and substr(PJXH00,1,1)='-' and exists (select 1 from BM_BMBM00 where BMBH00=CZYKS0 and (trim(YYID00)=as_YYID00 or as_YYID00='0'))); --6(1)退非医保款 ls_XMJE19:=ls_XMJE19-ls_XMJE20-ls_TJZJE0-ls_XMJE25-ls_XMJE26; --银行卡结算 --ls_TEXT00:='Update BM_GHKSTJ set GHKS'||ls_KSBH00||' =0 ' -- ||' where ID0000 ='|| TO_CHAR(ad_ID0000)||' and DYID00 in(7,28)';--银行卡结算 --SP_EXECUTE_SQL(ls_TEXT00); --记账应收款 ls_JZYSK0:=NVL(ls_JZJE00,0)-NVL(ls_TJZJE0,0); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = ' ||TO_CHAR(NVL(ls_XMJE35,0)) ||' where ID0000 ='|| TO_CHAR(ad_ID0000)||' and DYID00 = 37';--收预交金 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = ' ||TO_CHAR(NVL(ls_XMJE36,0)) ||' where ID0000 ='|| TO_CHAR(ad_ID0000)||' and DYID00 = 38';--退预交金 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = ' ||TO_CHAR(NVL(ls_XMJE01,0))||' ,BEIZHU =' ||''''||ls_BEIZH0||''''||' where ID0000 =' || TO_CHAR(ad_ID0000)||' and DYID00 = 1'; SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||ls_KSBH00||' = ' ||TO_CHAR(NVL(ls_ZPSR00,0)) ||' where ID0000 ='|| TO_CHAR(ad_ID0000)||' and DYID00 = 25'; SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||ls_KSBH00||' = '||TO_CHAR(NVL(ls_YHKSR0,0)) ||' where ID0000 ='|| TO_CHAR(ad_ID0000)||' and DYID00 = 26'; SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||ls_KSBH00 ||' = '||TO_CHAR(NVL(ls_YHKJS0,0)) ||' where ID0000 ='||TO_CHAR (ad_ID0000)||' and DYID00 = 27'; SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||TO_CHAR(NVL(ls_YBJS00,0)-NVL(ls_XMJE20,0)) ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 29'; --医保结算 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||TO_CHAR(NVL(ls_YBJS00,0)) ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 8'; --医保结算 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||TO_CHAR(NVL(ls_XMJE02,0)) ||',BEIZHU ='||''''|| ls_BEIZH1||'''' ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 2'; --应收款收回 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||TO_CHAR(NVL(ls_XMJE03,0)) ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 3'; --冲预收款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||TO_CHAR(NVL(ls_XMJE04,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 4'; --冲原收据 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||TO_CHAR(NVL(ls_XMJE05,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 5'; --补交现金 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||TO_CHAR(NVL(ls_XMJE06,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 6'; --补交支票 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||TO_CHAR(NVL(LS_JZJE00,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 12'; --记账金额 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||TO_CHAR(NVL(ls_JMBRYF,0)) ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 11'; --特殊结算 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||TO_CHAR(NVL(ls_cyysk0,0)) ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 9'; --出院病人应收款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||TO_CHAR(NVL(ls_XMJE12,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 13'; --预交金退出 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||TO_CHAR(NVL(ls_XMJE03,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 14'; --冲住院费用 = 冲预收款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||TO_CHAR(NVL(ls_XMJE14,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 15'; --计算应退还现金 SP_EXECUTE_SQL(ls_TEXT00); --ls_TEXT00:='Update BM_GHKSTJ set GHKS' -- ||LS_KSBH00||' = '||TO_CHAR(NVL(-ls_sswr01,0)) -- ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 18'; --计算应退还四舍五入金额 --SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||TO_CHAR(NVL(ls_sswr00,0)+NVL(ls_sswr01,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 34'; --计算应退还四舍五入金额 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||TO_CHAR(NVL(ls_XMJE15,0))||' where ID0000 = ' ||TO_CHAR(ad_ID0000)||' and DYID00 = 16'; --计算应退还转账 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||TO_CHAR(NVL(ls_XMJE16,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 17'; --现金暂存 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||TO_CHAR(NVL(ls_XMJE12,0)-NVL(ls_XMJE03,0) -NVL(ls_XMJE15,0)-NVL(ls_XMJE16,0))--+NVL(ls_sswr01,0) ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 15'; --重新计算应退还现金,发现预交金退款时有问题 SP_EXECUTE_SQL(ls_TEXT00); --ls_XMJE14:=nvl(ls_XMJE12,0)-nvl(ls_XMJE03,0) -- -nvl(ls_XMJE15,0)-nvl(ls_XMJE16,0)+nvl(ls_sswr00,0); --ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' -- ||TO_CHAR(NVL(ls_XMJE12,0)-nvl(ls_XMJE14,0) -- -nvl(ls_XMJE15,0)-nvl(ls_XMJE16,0)-nvl(ls_sswr00,0)) -- ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 14'; --重新计算冲住院费用 --SP_EXECUTE_SQL(ls_TEXT00); --ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 -- ||' = '||TO_CHAR(NVL(ls_XMJE17,0)) -- ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 19'; --收回旧收据 --SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = NVL(GHKS'||LS_KSBH00 ||',0)+'||TO_CHAR(NVL(ls_XMJE10,0)) ||' where ID0000 ='||TO_CHAR( ad_ID0000)||' and DYID00 = 19'; --收回旧收据 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||TO_CHAR(NVL(ls_XMJE10,0)) ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 10'; --补开新收据 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||TO_CHAR(NVL(ls_XMJE19,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 21'; --退非医保款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||TO_CHAR(NVL(ls_XMJE20,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 22'; --退医保款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = '||TO_CHAR(NVL(ls_TJZJE0,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 23'; --退记账款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||TO_CHAR(NVL(ls_xmje25,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 39'; --退减免医药费 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||TO_CHAR(NVL(ls_XMJE26,0)) ||' where ID0000 = '||TO_CHAR(ad_ID0000)||' and DYID00 = 40'; --退出院病人应收款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||TO_CHAR(NVL(ls_XMJE19,0)+NVL(ls_XMJE20,0) +NVL(ls_TJZJE0,0)+nvl(ls_xmje25,0)+nvl(ls_xmje26,0)) ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 20'; --退款小计 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00 ||' = '||TO_CHAR(NVL(ls_JZYSK0,0)) ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 32'; --应收记账款 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||TO_CHAR(NVL(ls_JMBRYF-ls_XMJE25,0)) ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 31'; --减免病人医药费 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||TO_CHAR(NVL(ls_cyysk0-ls_XMJE26,0)) ||' where ID0000 ='||TO_CHAR(ad_ID0000)||' and DYID00 = 30'; --出院病人应收款 SP_EXECUTE_SQL(ls_TEXT00); ls_XMJE21:=NVL(ls_XMJE01,0)+NVL(ls_XMJE02,0)+NVL(ls_XMJE15,0);--+NVL(ls_SSWR00,0); ls_XMJE34:=NVL(ls_XMJE12,0)+NVL(ls_XMJE19,0) +NVL(ls_XMJE20,0)+NVL(ls_TJZJE0,0) +nvl(ls_xmje25,0)+nvl(ls_xmje26,0); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||TO_CHAR(NVL(ls_XMJE21,0)-NVL(ls_XMJE34,0)+nvl(ls_XMJE16,0)) --ls_XMJE16暂存现金(余钱逃跑病人) ||' where ID0000 ='||TO_CHAR( ad_ID0000) ||' and DYID00 = 24'; --应缴金额 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS'||LS_KSBH00||' = ' ||TO_CHAR(NVL(ls_XMJE21,0)-NVL(ls_XMJE34,0)+nvl(ls_XMJE16,0)) --ls_XMJE16暂存现金(余钱逃跑病人) ||' where ID0000 ='||TO_CHAR(ad_ID0000) ||' and DYID00 = 36'; --应缴金额 SP_EXECUTE_SQL(ls_TEXT00); ls_TEXT00:='Update BM_GHKSTJ set GHKS' ||LS_KSBH00||' = ' ||TO_CHAR( NVL(ls_XMJE21,0) -NVL(ls_XMJE34,0)+nvl(ls_XMJE16,0)--ls_XMJE16暂存现金(余钱逃跑病人) -NVL(ls_ZPSR00,0) -NVL(ls_YHKSR0,0) -NVL(ls_YHKJS0,0) -NVL(ls_YBJS00,0) +NVL(ls_XMJE20,0) -NVL(ls_JMBRYF,0) +NVL(ls_XMJE25,0) -NVL(Ls_JZYSK0,0) -NVL(ls_CYYSK0,0) +NVL(ls_xmje26,0) -nvl(ls_sswr01,0) ) ||' where ID0000 ='||TO_CHAR(ad_ID0000) ||' and DYID00 = 35'; --实交现金 SP_EXECUTE_SQL(ls_TEXT00); END LOOP; CLOSE CUR_ZY_BRJFB0_SFY000; Update BM_GHKSTJ set XMHJ00 = NVL(GHKS01,0)+ NVL(GHKS02,0)+ NVL(GHKS03,0)+ NVL(GHKS04,0)+ NVL(GHKS05,0)+ NVL(GHKS06,0)+ NVL(GHKS07,0)+ NVL(GHKS08,0)+ NVL(GHKS09,0)+ NVL(GHKS10,0) + NVL(GHKS11,0)+ NVL(GHKS12,0)+ NVL(GHKS13,0)+ NVL(GHKS14,0)+ NVL(GHKS15,0)+ NVL(GHKS16,0)+ NVL(GHKS17,0)+ NVL(GHKS18,0)+ NVL(GHKS19,0)+ NVL(GHKS20,0) + NVL(GHKS21,0)+ NVL(GHKS22,0)+ NVL(GHKS23,0)+ NVL(GHKS24,0)+ NVL(GHKS25,0)+ NVL(GHKS26,0)+ NVL(GHKS27,0)+ NVL(GHKS28,0)+ NVL(GHKS29,0)+ NVL(GHKS30,0) + NVL(GHKS31,0)+ NVL(GHKS32,0)+ NVL(GHKS33,0)+ NVL(GHKS34,0)+ NVL(GHKS35,0)+ NVL(GHKS36,0)+ NVL(GHKS37,0)+ NVL(GHKS38,0)+ NVL(GHKS39,0)+ NVL(GHKS40,0) + NVL(GHKS41,0)+ NVL(GHKS42,0)+ NVL(GHKS43,0)+ NVL(GHKS44,0)+ NVL(GHKS45,0)+ NVL(GHKS46,0)+ NVL(GHKS47,0)+ NVL(GHKS48,0)+ NVL(GHKS49,0)+ NVL(GHKS50,0) + NVL(GHKS51,0)+ NVL(GHKS52,0)+ NVL(GHKS53,0)+ NVL(GHKS54,0)+ NVL(GHKS55,0)+ NVL(GHKS56,0)+ NVL(GHKS57,0)+ NVL(GHKS58,0)+ NVL(GHKS59,0)+ NVL(GHKS60,0) + NVL(GHKS61,0)+ NVL(GHKS62,0)+ NVL(GHKS63,0)+ NVL(GHKS64,0)+ NVL(GHKS65,0)+ NVL(GHKS66,0)+ NVL(GHKS67,0)+ NVL(GHKS68,0)+ NVL(GHKS69,0)+ NVL(GHKS70,0) + NVL(GHKS71,0)+ NVL(GHKS72,0)+ NVL(GHKS73,0)+ NVL(GHKS74,0)+ NVL(GHKS75,0)+ NVL(GHKS76,0)+ NVL(GHKS77,0)+ NVL(GHKS78,0)+ NVL(GHKS79,0) where ID0000 = ad_ID0000; if as_TJFS00<>0 then --BY ZYSF-20120419-001 本合计值在前台不显示 只是过滤非零的收费员--- Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08,GHKS09,GHKS10,GHKS11,GHKS12,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS18,GHKS19,GHKS20, 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,GHKS46,GHKS47,GHKS48,GHKS49,GHKS50,GHKS51,GHKS52,GHKS53,GHKS54,GHKS55,GHKS56,GHKS57,GHKS58,GHKS59,GHKS60, GHKS61,GHKS62,GHKS63,GHKS64,GHKS65,GHKS66,GHKS67,GHKS68,GHKS69,GHKS70,GHKS71,GHKS72,GHKS73,GHKS74,GHKS75,GHKS76,GHKS77,GHKS78,GHKS79, XMHJ00,DYID00) select ad_ID0000,'999999',sum(GHKS01),sum(GHKS02),sum(GHKS03),sum(GHKS04),sum(GHKS05),sum(GHKS06),sum(GHKS07),sum(GHKS08),sum(GHKS09),sum(GHKS10),sum(GHKS11),sum(GHKS12),sum(GHKS13), sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS17),sum(GHKS18),sum(GHKS19),sum(GHKS20),sum(GHKS21),sum(GHKS22),sum(GHKS23),sum(GHKS24),sum(GHKS25),sum(GHKS26),sum(GHKS27),sum(GHKS28),sum(GHKS29), 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),sum(GHKS46),sum(GHKS47),sum(GHKS48),sum(GHKS49),sum(GHKS50),sum(GHKS51),sum(GHKS52),sum(GHKS53),sum(GHKS54),sum(GHKS55),sum(GHKS56),sum(GHKS57),sum(GHKS58),sum(GHKS59),sum(GHKS60), sum(GHKS61),sum(GHKS62),sum(GHKS63),sum(GHKS64),sum(GHKS65),sum(GHKS66),sum(GHKS67),sum(GHKS68),sum(GHKS69),sum(GHKS70),sum(GHKS71),sum(GHKS72),sum(GHKS73),sum(GHKS74),sum(GHKS75),sum(GHKS76),sum(GHKS77),sum(GHKS78),sum(GHKS79), sum(XMHJ00),0 from BM_GHKSTJ where ID0000 = ad_ID0000; end if; --COMMIT; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='数据库错误：出错原因未知！'; as_SYSMSG:=substr(sqlERRM,1,150); ROLLBACK; END; /