CREATE OR REPLACE PROCEDURE SP_SF_MZSFYJK_HZBB00 ( as_KSRQ00 in char, --开始日期 as_JSRQ00 in char, --结束日期 as_KSSJ00 in char, --开始时间 as_JSSJ00 in char, --结束时间 as_CZYXM0 in char, --操作员姓名 as_YYID00 in char default '0', --医院ID 0：全部 ad_ID0000 out number, --统计报表对应的ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar --系统提示的错误信息 ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2012.05.15 增加入参医院ID by MZSF-20120419-004 -- zhangyc 2017.06.05 增加微信支付、支付宝支付、医院支付、健康通支付 by MZSF-20170508-001 LS_CZYXM0 SF_BRJFB0.CZYXM0%type; LS_ZFFS00 SF_BRJFB0.ZFFS00%type; LS_JE0000 SF_BRJFB0.JFJE00%type; CURSOR CUR_SF_MZSFYJK_HZBB00 IS select CZYXM0,ZFFS00,sum(JFJE00) FKJE00 from SF_BRJFB0 where JFRQ00>=as_KSRQ00 AND JFRQ00<=as_JSRQ00 AND JFRQ00||JFSJ00>=as_KSRQ00||as_KSSJ00 AND JFRQ00||JFSJ00<=as_JSRQ00||as_JSSJ00 AND ZFFS00 in (1,2,5,8,18,20,24,25,26) AND JFJE00>0 and (CZYXM0=as_CZYXM0 OR as_CZYXM0='0') and exists (select 1 from BM_BMBM00 AA,BM_YGBM00 BB where AA.BMBH00=BB.BMBH00 AND BB.YGBH00=JFCZY0 and (trim(AA.YYID00)=as_YYID00 or as_YYID00='0')) group by CZYXM0,ZFFS00 union all select CZYXM0,ZFFS00,sum(JFJE00) FKJE00 from SF_BRJFB0 where JFRQ00>=as_KSRQ00 AND JFRQ00<=as_JSRQ00 AND JFRQ00||JFSJ00>=as_KSRQ00||as_KSSJ00 AND JFRQ00||JFSJ00<=as_JSRQ00||as_JSSJ00 AND ZFFS00 IN (1,2,5,8,18,20,24,25,26) AND JFJE00<0 and (CZYXM0=as_CZYXM0 OR as_CZYXM0='0') and exists (select 1 from BM_BMBM00 AA,BM_YGBM00 BB where AA.BMBH00=BB.BMBH00 AND BB.YGBH00=JFCZY0 and (trim(AA.YYID00)=as_YYID00 or as_YYID00='0')) GROUP BY CZYXM0,ZFFS00 ; begin --GHKS01:收 款 GHKS11:ZFFS00=1: 现金 GHKS12:ZFFS00=2: 转账 GHKS13:ZFFS00=5: 四舍五入金额 GHKS14:ZFFS00=8: 银行卡 GHKS15:ZFFS00=18: 医银通 --GHKS16:ZFFS00=20: 医院支付 GHKS17:ZFFS00=24: 微信支付 GHKS18:ZFFS00=25: 支付宝支付 GHKS19:ZFFS00=26: 健康通支付 --GHKS02: 付 款 GHKS31:ZFFS00=1: 现金 GHKS32:ZFFS00=2: 转账 GHKS33:ZFFS00=5: 四舍五入金额 GHKS34:ZFFS00=8: 银行卡 GHKS35:ZFFS00=18: 医银通 --GHKS36:ZFFS00=20: 医院支付 GHKS37:ZFFS00=24: 微信支付 GHKS38:ZFFS00=25: 支付宝支付 GHKS39:ZFFS00=26: 健康通支付 --GHKS03:余 额=GHKS01-GHKS02 Select SQ_BM_GHKSTJ_ID0000.nextval into ad_ID0000 from dual; open CUR_SF_MZSFYJK_HZBB00; loop fetch CUR_SF_MZSFYJK_HZBB00 INTO ls_CZYXM0,LS_ZFFS00,ls_JE0000; exit when CUR_SF_MZSFYJK_HZBB00%NOTFOUND; if ls_JE0000 >0 then if LS_ZFFS00=1 then --收款 GHKS11:ZFFS00=1 update BM_GHKSTJ set GHKS01 =nvl(GHKS01,0)+nvl(LS_JE0000,0),GHKS11=nvl(GHKS11,0)+nvl(LS_JE0000,0) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS11,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=2 then --收款 GHKS12:ZFFS00=2: 转账 update BM_GHKSTJ set GHKS01 =nvl(GHKS01,0)+nvl(LS_JE0000,0),GHKS12=nvl(GHKS12,0)+nvl(LS_JE0000,0) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS12,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=5 then --收款 GHKS13:ZFFS00=5: 四舍五入金额 update BM_GHKSTJ set GHKS01 =nvl(GHKS01,0)+nvl(LS_JE0000,0),GHKS13=nvl(GHKS13,0)+nvl(LS_JE0000,0) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS13,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=8 then --收款 GHKS14:ZFFS00=8: 银行卡 update BM_GHKSTJ set GHKS01 =nvl(GHKS01,0)+nvl(LS_JE0000,0),GHKS14=nvl(GHKS14,0)+nvl(LS_JE0000,0) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS14,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=18 then --收款 GHKS15:ZFFS00=18: 医银通 update BM_GHKSTJ set GHKS01 =nvl(GHKS01,0)+nvl(LS_JE0000,0),GHKS15=nvl(GHKS15,0)+nvl(LS_JE0000,0) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS15,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=20 then --收款 GHKS16:ZFFS00=20: 医院支付 update BM_GHKSTJ set GHKS01 =nvl(GHKS01,0)+nvl(LS_JE0000,0),GHKS16=nvl(GHKS16,0)+nvl(LS_JE0000,0) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS16,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=24 then --收款 GHKS17:ZFFS00=24: 微信支付 update BM_GHKSTJ set GHKS01 =nvl(GHKS01,0)+nvl(LS_JE0000,0),GHKS17=nvl(GHKS17,0)+nvl(LS_JE0000,0) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS17,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=25 then --收款 GHKS18:ZFFS00=25: 支付宝支付 update BM_GHKSTJ set GHKS01 =nvl(GHKS01,0)+nvl(LS_JE0000,0),GHKS18=nvl(GHKS18,0)+nvl(LS_JE0000,0) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS18,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=26 then --收款 GHKS19:ZFFS00=26: 健康通支付 update BM_GHKSTJ set GHKS01 =nvl(GHKS01,0)+nvl(LS_JE0000,0),GHKS19=nvl(GHKS19,0)+nvl(LS_JE0000,0) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS01,GHKS19,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; end if; if ls_JE0000 <0 then if LS_ZFFS00=1 then --付款 GHKS31:ZFFS00=1 update BM_GHKSTJ set GHKS02 =-(nvl(GHKS02,0)+nvl(LS_JE0000,0)),GHKS31=-(nvl(GHKS31,0)+nvl(LS_JE0000,0)) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS02,GHKS31,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=2 then --付款 GHKS32:ZFFS00=2: 转账 update BM_GHKSTJ set GHKS02 =nvl(GHKS02,0)-nvl(LS_JE0000,0),GHKS32=-(nvl(GHKS32,0)+nvl(LS_JE0000,0)) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS02,GHKS32,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=5 then --付款 GHKS33:ZFFS00=5: 四舍五入金额 update BM_GHKSTJ set GHKS02 =nvl(GHKS02,0)-nvl(LS_JE0000,0),GHKS33=-(nvl(GHKS33,0)+nvl(LS_JE0000,0)) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS02,GHKS33,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=8 then --付款 GHKS34:ZFFS00=8: 银行卡 update BM_GHKSTJ set GHKS02 =(nvl(GHKS02,0)-nvl(LS_JE0000,0)),GHKS34=-(nvl(GHKS34,0)+nvl(LS_JE0000,0)) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS02,GHKS34,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=18 then --付款 GHKS35:ZFFS00=18: 医银通 update BM_GHKSTJ set GHKS02 =nvl(GHKS02,0)-nvl(LS_JE0000,0),GHKS35=-(nvl(GHKS35,0)+nvl(LS_JE0000,0)) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS02,GHKS35,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=20 then --付款 GHKS36:ZFFS00=20: 医院支付 update BM_GHKSTJ set GHKS02 =nvl(GHKS02,0)-nvl(LS_JE0000,0),GHKS36=-(nvl(GHKS36,0)+nvl(LS_JE0000,0)) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS02,GHKS36,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=24 then --付款 GHKS37:ZFFS00=24: 微信支付 update BM_GHKSTJ set GHKS02 =nvl(GHKS02,0)-nvl(LS_JE0000,0),GHKS37=-(nvl(GHKS37,0)+nvl(LS_JE0000,0)) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS02,GHKS37,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=25 then --付款 GHKS38:ZFFS00=25: 支付宝支付 update BM_GHKSTJ set GHKS02 =nvl(GHKS02,0)-nvl(LS_JE0000,0),GHKS38=-(nvl(GHKS38,0)+nvl(LS_JE0000,0)) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS02,GHKS38,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; if LS_ZFFS00=26 then --付款 GHKS39:ZFFS00=26: 健康通支付 update BM_GHKSTJ set GHKS02 =nvl(GHKS02,0)-nvl(LS_JE0000,0),GHKS39=-(nvl(GHKS39,0)+nvl(LS_JE0000,0)) where ID0000=ad_ID0000 and GHRQ00= LS_CZYXM0; if sql%notfound then insert into BM_GHKSTJ(GHRQ00,GHKS02,GHKS39,ID0000) values(LS_CZYXM0,nvl(LS_JE0000,0),nvl(LS_JE0000,0),ad_ID0000); end if; end if; end if; END LOOP; CLOSE CUR_SF_MZSFYJK_HZBB00; -- insert into BM_GHKSTJ(ID0000,DYID00,GHRQ00,GHKS01,GHKS02,GHKS03, GHKS11,GHKS12,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS18,GHKS19, GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36,GHKS37,GHKS38,GHKS39 ) select ad_ID0000,1,GHRQ00,sum(nvl(GHKS01,0)),sum(nvl(GHKS02,0)),sum(nvl(GHKS01,0))-sum(nvl(GHKS02,0)), sum(nvl(GHKS11,0)),sum(nvl(GHKS12,0)),sum(nvl(GHKS13,0)),sum(nvl(GHKS14,0)),sum(nvl(GHKS15,0)),sum(nvl(GHKS16,0)),sum(nvl(GHKS17,0)),sum(nvl(GHKS18,0)),sum(nvl(GHKS19,0)), sum(nvl(GHKS31,0)),sum(nvl(GHKS32,0)),sum(nvl(GHKS33,0)),sum(nvl(GHKS34,0)),sum(nvl(GHKS35,0)),sum(nvl(GHKS36,0)),sum(nvl(GHKS37,0)),sum(nvl(GHKS38,0)),sum(nvl(GHKS39,0)) from BM_GHKSTJ where ID0000 = ad_ID0000 and nvl(DYID00,0)=0 group by GHRQ00; delete BM_GHKSTJ where ID0000 = ad_ID0000 and nvl(DYID00,0)=0; --添加一条总计行 Insert into BM_GHKSTJ(ID0000,GHRQ00,GHKS01,GHKS02,GHKS03, GHKS11,GHKS12,GHKS13,GHKS14,GHKS15,GHKS16,GHKS17,GHKS18,GHKS19, GHKS31,GHKS32,GHKS33,GHKS34,GHKS35,GHKS36,GHKS37,GHKS38,GHKS39 ) select ad_ID0000,' 合计 ',sum(GHKS01),sum(GHKS02),sum(GHKS03), sum(GHKS11),sum(GHKS12),sum(GHKS13),sum(GHKS14),sum(GHKS15),sum(GHKS16),sum(GHKS17),sum(GHKS18),sum(GHKS19), sum(GHKS31),sum(GHKS32),sum(GHKS33),sum(GHKS34),sum(GHKS35),sum(GHKS36),sum(GHKS37),sum(GHKS38),sum(GHKS39) from BM_GHKSTJ where ID0000 = ad_ID0000 and nvl(DYID00,0)=1; --commit EXCEPTION WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_MZSRYB_RBB000('||as_KSRQ00||','||as_JSRQ00||','||'ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); ROLLBACK; END; /