CREATE OR REPLACE PROCEDURE sd_hospital.sp_sf_yjjye0_qkbb00 ( as_KSRQ00 IN CHAR, --开始日期 as_KSSJ00 IN CHAR, --开始时间 as_JSRQ00 IN CHAR, --结束日期 as_JSSJ00 IN CHAR, --结束时间 as_YYID00 IN CHAR, --各分院ID >0 分院 =0是全部医院 as_STFKTJ IN CHAR default 'N', --收退分开统计 as_STTCZF IN CHAR default 'N', --预计金收退统计剔除作废记录 ad_ID0000 OUT NUMBER, --统计报表对应的ID as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR --系统提示的错误信息 ) as ls_YGBH00 BM_YGBM00.YGBH00%TYPE; ls_JKDID0 ZY_BRJFB0.JKBID0%TYPE; --交费记录对应的缴款单ID ls_SRJE00 ZY_BRJFB0.JFJE00%TYPE; --收入金额 ls_TCJE00 ZY_BRJFB0.JFJE00%TYPE; --退出金额 ls_YWJE00 ZY_BRJFB0.JFJE00%TYPE; --业务金额 ls_XJZFJE ZY_BRJFB0.JFJE00%TYPE; --现金支付金额 ls_YBGRZH ZY_BRJFB0.JFJE00%TYPE; --医保个人帐户金额 ls_YBTCJJ ZY_BRJFB0.JFJE00%TYPE; --医保统筹基金金额 ls_SBZFJE ZY_BRJFB0.JFJE00%TYPE; --商保支付金额 ls_BEIZHU BM_GHKSTJ.BEIZHU%TYPE; LS_JFJE00 number(12,2); LS_JFCZY0 number(12); LS_ZWXM00 varchar2(100); LS_YGXZ00 varchar2(100); LS_MSTQY0 varchar2(20);--健康通 LS_YYTQY0 varchar2(20);--银医通 LS_BEIZH1 BM_GHKSTJ.BEIZH1%type;--:预交金上期余额 LS_BEIZH2 BM_GHKSTJ.BEIZH2%type;--:本期预交金收退合计 LS_BEIZH3 BM_GHKSTJ.BEIZH3%type;--:非收费员健康通合计 LS_BEIZH4 BM_GHKSTJ.BEIZH4%type;--:本期预交金结算 LS_BEIZH5 BM_GHKSTJ.BEIZH5%type;--:本期预交金余额 LS_YYID00 XT_YYXX00.YYID00%TYPE; SF_SFYJXGSJQY varchar2(20);--是否已进行过数据迁移(Y是N否默认N,有过数据迁移则从SF_YJJYE0计算预交金期前余额) LS_YJJYEXSFQY varchar2(20);--预交金余额是否+健康通转成入 LV_YJJSQJC number(12,2); --结算上期结存 LV_BRYJJJS number(12,2); -- LV_BQYJJJS number(12,2);--本期预交金结算 LV_BQYJJSTHJ number(12,2); --本期预交金收退合计 lv_BQYJJYE number(12,2); --本期预交金余额 lv_BQSR00 number(12,2); --本期收入 lv_BQTC00 number(12,2); --本期退出 lv_BQJC00 number(12,2); --本期结存 lv_FSFYJE number(12,2); --非收费员金额 lv_JKTHJ0 number(12,2); --健康通合计 lv_WXHJ00 number(12,2); --微信合计 lv_ZFBHJ0 number(12,2); --支付宝合计 LS_CZRQ00 char(8); LS_KSRQ00 char(8);--开始日期 LS_KSSJ00 char(8);--开始时间 LS_JSRQ00 char(8);--结束日期 LS_JSSJ00 char(8);--结束时间 CURSOR C_BRJFB0 IS select decode(ZFFS00,1,1,2,2,8,8,3,3,4,3,10,3,11,3,5,1,18,18,24,24,25,25,26,26)ZFFS00, NVL(sum(DECODE(Trim(D.YBLB00),'Z',decode(a.ZFFS00,10,0,11,0,A.JFJE00),A.JFJE00)),0) JFJE00, JFBZ00,JFCZY0 from SF_BRJFB0 a,BM_BMBM00 b ,BM_YGBM00 c,bm_brxxb0 d where a.JFCZY0 = c.YGBH00 and a.brid00=d.brid00 and decode(a.CZYKS0,0,c.BMBH00,a.CZYKS0)=b.BMBH00 --如果CZYKS0无数据，则取收费员当前对应科室 and JFLBID+0 = 1 and JFRQ00>= as_KSRQ00 and JFRQ00<= as_JSRQ00 and (b.YYID00 = as_YYID00 OR trim(as_YYID00) ='0') group by JFBZ00,decode(ZFFS00,1,1,2,2,8,8,3,3,4,3,10,3,11,3,5,1,18,18,24,24,25,25,26,26),JFCZY0; -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2007.09.26 增加统计现金，银行卡，记帐金额; -- xzw 2007.11.29 对商保病人商保个人账户，商保统筹基金的金额均都部算在医保费用中; -- xzw 2008.01.07 增加一条合计记录 -- zhr 2008.02.22 其中现金、转账、银行卡应该包含收和退 -- qks 2008.05.27 在“预交金收入”列后增加三列（其中：现金、银行卡、转帐）,不包含退MZSF-20080527-001 -- qks 2010.06.24 增加“电子钱包”列:GHKS10,GHKS11 -- qks 2010.07.21 传入参数增加as_STFKTJ -- zhangyc 2016.11.24 增加健康通支付 by MZSF-20161110-001 -- zhangyc 2016.11.25 把非收费员合并成一条记录 by MZSF-20161110-002 -- huangjy 2017.01.23 增加统计时剔除作废记录部分 by MZSF-20170117-001 --zhangyc 2017.08.02 增加微信ZFFS00=24，支付宝ZFFS00=25 BY MZSF-20170310-002 --zhangyc 2017.09.07 修改健康通统计 by MZSF-20170310-001 --zhangyc 2017.09.28 修正异常报错 by MZSF-20170928-001 --zhangyc 2017.10.12 修正上期余额错误 by MZSF-20171012-001 -- dsm 2017.11.07 本期预交金余额SF_SFYJXGSJQY=Y时不能再加微信，支付保合计 for MZSF-20171027-003 begin --参数SF_YJJYEQKBSTFK=Y as_STFKTJ=Y LS_YJJYEXSFQY:='N';--预交金余额是否扣除健康通转成入 LS_CZRQ00:=to_char(sysdate,'YYYYMMDD'); LS_KSRQ00:=nvl(AS_KSRQ00,to_char(sysdate,'YYYYMMDD'));--开始日期 LS_KSSJ00:=nvl(AS_KSSJ00,'00:00:01');--开始时间 LS_JSRQ00:=nvl(AS_JSRQ00,to_char(sysdate,'YYYYMMDD'));--结束日期 LS_JSSJ00:=nvl(AS_JSSJ00,'23:59:59');--结束时间 select SQ_BM_GHKSTJ_ID0000.nextval into ad_ID0000 from dual; select SF_SF_TYZD00('民生通系统参数','PmstOpen') into LS_MSTQY0 from dual; select SF_SF_TYZD00('银医通共享平台','payopen') into LS_YYTQY0 from dual; if as_YYID00<>'0' then LS_YYID00:=as_YYID00; else select SF_SF_GETYYID00() YYID00 into LS_YYID00 from dual; end if; --220007 福建省第二人民医院 220035:福建省老年医院 if LS_MSTQY0='Y' then if LS_YYID00 not in ('220007','220035') then LS_YJJYEXSFQY:='Y';--预交金余额是否+健康通转入 end if; end if; if LS_YYTQY0='Y' then LS_YJJYEXSFQY:='Y';--预交金余额是否+银医通转入 end if; begin select trim(VALUE0) into SF_SFYJXGSJQY from XT_XTCS00 where name00='SF_SFYJXGSJQY'; exception when others then SF_SFYJXGSJQY:='N'; end; for JF in C_BRJFB0 loop LS_JFJE00:=nvl(JF.JFJE00,0); LS_JFCZY0:=JF.JFCZY0; begin select ZWXM00,YGXZ00 into LS_ZWXM00,LS_YGXZ00 from BM_YGBM00 where YGBH00=LS_JFCZY0; exception when others then LS_ZWXM00:='未知' ; end; if (LS_MSTQY0='Y') or (LS_YYTQY0='Y') then --YGXZ00：员工性质(一个员工可设置多种性质'0'实习医生,'1'处方医生,'2'护士,'3'收费员,'4'药房人员,'5'系统维护人员'6'采购员,'7'药库保管员, if (instrb(nvl(LS_YGXZ00,'0'),'3')<=0) then --非收费员合并一条显示 LS_JFCZY0:=999998; LS_ZWXM00:='非收费员'; end if; end if; if as_STTCZF='Y' then if JF.JFBZ00 in (0) then --if JF.ZFFS00=1 then --现金 if JF.ZFFS00 in (1,2,8,18,24,25,26) then --现金,转长，银行卡 if JF.ZFFS00 in (18,26) then --银医通，健康通 暂时不加入预交金收入 LS_JFJE00:=0; end if; update BM_GHKSTJ set GHKS01=nvl(GHKS01,0)+LS_JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; if sql%notfound then insert into BM_GHKSTJ(ID0000,XMHJ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08, GHKS09,GHKS10,GHKS11,GHKS12,GHKS13,GHKS14,GHKS15,GHRQ00) values(ad_ID0000,LS_JFCZY0,LS_JFJE00,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,LS_ZWXM00); end if; if JF.ZFFS00=1 then --现金 update BM_GHKSTJ set GHKS04=nvl(GHKS04,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS07=nvl(GHKS07,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=2 then --转帐 update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS08=nvl(GHKS08,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=8 then --银行卡 update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS09=nvl(GHKS09,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=18 then --电子钱包 update BM_GHKSTJ set GHKS10=nvl(GHKS10,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS11=nvl(GHKS11,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=26 then --健康通 update BM_GHKSTJ set GHKS10=nvl(GHKS10,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS11=nvl(GHKS11,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=24 then --微信 update BM_GHKSTJ set GHKS12=nvl(GHKS12,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS13=nvl(GHKS13,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=25 then --支付宝 update BM_GHKSTJ set GHKS14=nvl(GHKS14,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS15=nvl(GHKS15,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; end if; end if; if JF.ZFFS00=3 then --医保 update BM_GHKSTJ set GHKS03=GHKS03+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; if sql%notfound then insert into BM_GHKSTJ(ID0000,XMHJ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08, GHKS09,GHKS10,GHKS11,GHKS12,GHKS13,GHKS14,GHKS15,GHRQ00) values(ad_ID0000,LS_JFCZY0,0,0,JF.JFJE00,0,0,0,0,0, 0,0,0,0,0,0,0,LS_ZWXM00); --select ad_ID0000,LS_JFCZY0,0,0,JF.JFJE00,0,0,0,0,0,0,0,0,ZWXM00 from BM_YGBM00 where YGBH00=LS_JFCZY0; end if; end if; --end if; elsif JF.JFBZ00 in (1) then --if JF.ZFFS00=1 then --现金 if JF.ZFFS00 in (1,2,8,18,24,25,26) then --现金 if JF.ZFFS00 in (18,26) then --银医通，健康通 暂时不加入预交金退出 LS_JFJE00:=0; end if; update BM_GHKSTJ set GHKS02=nvl(GHKS02,0)+LS_JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; if sql%notfound then insert into BM_GHKSTJ(ID0000,XMHJ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08, GHKS09,GHKS10,GHKS11,GHKS12,GHKS13,GHKS14,GHKS15,GHRQ00) values(ad_ID0000,LS_JFCZY0,0,LS_JFJE00,0,0,0,0,0,0, 0,0,0,0,0,0,0,LS_ZWXM00); --select ad_ID0000,LS_JFCZY0,0,LS_JFJE00,0,0,0,0,0,0,0,0,0,ZWXM00 from BM_YGBM00 where YGBH00=LS_JFCZY0; end if; --08.02.22 zhr其中现金、转账、银行卡应该包含收和退 if JF.ZFFS00=1 then --现金 update BM_GHKSTJ set GHKS04=nvl(GHKS04,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=2 then --转帐 update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=8 then --银行卡 update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=18 then --电子钱包 update BM_GHKSTJ set GHKS10=nvl(GHKS10,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=26 then --健康通 update BM_GHKSTJ set GHKS10=nvl(GHKS10,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=24 then --微信 update BM_GHKSTJ set GHKS12=nvl(GHKS12,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=25 then --支付宝 update BM_GHKSTJ set GHKS14=nvl(GHKS14,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; end if; end if; if JF.ZFFS00=3 then --医保 update BM_GHKSTJ set GHKS03=nvl(GHKS03,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; if sql%notfound then insert into BM_GHKSTJ(ID0000,XMHJ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08, GHKS09,GHKS10,GHKS11,GHKS12,GHKS13,GHKS14,GHKS15,GHRQ00) values(ad_ID0000,LS_JFCZY0,0,0,JF.JFJE00,0,0,0,0,0, 0,0,0,0,0,0,0,LS_ZWXM00); --select ad_ID0000,LS_JFCZY0,0,0,JF.JFJE00,0,0,0,0,0,0,0,0,ZWXM00 from BM_YGBM00 where YGBH00=LS_JFCZY0; end if; end if; end if; else if JF.JFBZ00 in (0,2) then --if JF.ZFFS00=1 then --现金 if JF.ZFFS00 in (1,2,8,18,24,25,26) then --现金,转长，银行卡 if JF.ZFFS00 in (18,26) then --银医通，健康通 暂时不加入预交金收入 LS_JFJE00:=0; end if; update BM_GHKSTJ set GHKS01=nvl(GHKS01,0)+LS_JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; if sql%notfound then insert into BM_GHKSTJ(ID0000,XMHJ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08, GHKS09,GHKS10,GHKS11,GHKS12,GHKS13,GHKS14,GHKS15,GHRQ00) values(ad_ID0000,LS_JFCZY0,LS_JFJE00,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,LS_ZWXM00); --select ad_ID0000,LS_JFCZY0,LS_JFJE00,0,0,0,0,0,0,0,0,0,0,ZWXM00 from BM_YGBM00 where YGBH00=LS_JFCZY0; end if; if JF.ZFFS00=1 then --现金 update BM_GHKSTJ set GHKS04=nvl(GHKS04,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS07=nvl(GHKS07,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=2 then --转帐 update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS08=nvl(GHKS08,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=8 then --银行卡 update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS09=nvl(GHKS09,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=18 then --电子钱包 update BM_GHKSTJ set GHKS10=nvl(GHKS10,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS11=nvl(GHKS11,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=26 then --健康通 update BM_GHKSTJ set GHKS10=nvl(GHKS10,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS11=nvl(GHKS11,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=24 then --微信 update BM_GHKSTJ set GHKS12=nvl(GHKS12,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS13=nvl(GHKS13,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=25 then --支付宝 update BM_GHKSTJ set GHKS14=nvl(GHKS14,0)+decode(as_STFKTJ,'Y',0,JF.JFJE00),GHKS15=nvl(GHKS15,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; end if; end if; if JF.ZFFS00=3 then --医保 update BM_GHKSTJ set GHKS03=GHKS03+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; if sql%notfound then insert into BM_GHKSTJ(ID0000,XMHJ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08, GHKS09,GHKS10,GHKS11,GHKS12,GHKS13,GHKS14,GHKS15,GHRQ00) values(ad_ID0000,LS_JFCZY0,0,0,JF.JFJE00,0,0,0,0,0, 0,0,0,0,0,0,0,LS_ZWXM00); --select ad_ID0000,LS_JFCZY0,0,0,JF.JFJE00,0,0,0,0,0,0,0,0,ZWXM00 from BM_YGBM00 where YGBH00=LS_JFCZY0; end if; end if; --end if; elsif JF.JFBZ00 in (1,3) then --if JF.ZFFS00=1 then --现金 if JF.ZFFS00 in (1,2,8,18,24,25,26) then --现金 if JF.ZFFS00 in (18,26) then --银医通，健康通 暂时不加入预交金退出 LS_JFJE00:=0; end if; update BM_GHKSTJ set GHKS02=nvl(GHKS02,0)+LS_JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; if sql%notfound then insert into BM_GHKSTJ(ID0000,XMHJ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08, GHKS09,GHKS10,GHKS11,GHKS12,GHKS13,GHKS14,GHKS15,GHRQ00) values(ad_ID0000,LS_JFCZY0,0,LS_JFJE00,0,0,0,0,0,0, 0,0,0,0,0,0,0,LS_ZWXM00); --select ad_ID0000,LS_JFCZY0,0,LS_JFJE00,0,0,0,0,0,0,0,0,0,ZWXM00 from BM_YGBM00 where YGBH00=LS_JFCZY0; end if; --08.02.22 zhr其中现金、转账、银行卡应该包含收和退 if JF.ZFFS00=1 then --现金 update BM_GHKSTJ set GHKS04=nvl(GHKS04,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=2 then --转帐 update BM_GHKSTJ set GHKS05=nvl(GHKS05,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=8 then --银行卡 update BM_GHKSTJ set GHKS06=nvl(GHKS06,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=18 then --电子钱包 update BM_GHKSTJ set GHKS10=nvl(GHKS10,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=26 then --健康通 update BM_GHKSTJ set GHKS10=nvl(GHKS10,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=24 then --微信 update BM_GHKSTJ set GHKS12=nvl(GHKS12,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; elsif JF.ZFFS00=25 then --支付宝 update BM_GHKSTJ set GHKS14=nvl(GHKS14,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; end if; end if; if JF.ZFFS00=3 then --医保 update BM_GHKSTJ set GHKS03=nvl(GHKS03,0)+JF.JFJE00 where ID0000=ad_ID0000 and XMHJ00=LS_JFCZY0; if sql%notfound then insert into BM_GHKSTJ(ID0000,XMHJ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06,GHKS07,GHKS08, GHKS09,GHKS10,GHKS11,GHKS12,GHKS13,GHKS14,GHKS15,GHRQ00) values(ad_ID0000,LS_JFCZY0,0,0,JF.JFJE00,0,0,0,0,0, 0,0,0,0,0,0,0,LS_ZWXM00); --select ad_ID0000,LS_JFCZY0,0,0,JF.JFJE00,0,0,0,0,0,0,0,0,ZWXM00 from BM_YGBM00 where YGBH00=LS_JFCZY0; end if; end if; end if; end if; -- end if; end loop; insert into BM_GHKSTJ(ID0000,GHRQ00,XMHJ00,GHKS01,GHKS02,GHKS03,GHKS04,GHKS05,GHKS06, GHKS07,GHKS08,GHKS09,GHKS10,GHKS11,GHKS12,GHKS13,GHKS14,GHKS15) 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) from BM_GHKSTJ where ID0000=ad_ID0000; --GHKS51:健康通合计 GHKS11:健康通收 GHKS10：健康通退 --GHKS52:微信合计 GHKS13：微信收 GHKS12：微信退 --GHKS53:支付宝合计 GHKS15：支付宝收 GHKS14：支付宝退 --GHKS61 ,BEIZH1 :预交金上期余额 GHKS62 ,BEIZH2:本期预交金收退合计 GHKS63 ,BEIZH3:非收费员健康通合计 GHKS64 ,BEIZH4:本期预交金结算 GHKS65 ,BEIZH5:本期预交金余额 if AS_STFKTJ='Y' then update BM_GHKSTJ set GHKS51=nvl(GHKS11,0)+nvl(GHKS10,0),GHKS52=nvl(GHKS13,0)+nvl(GHKS12,0),GHKS53=nvl(GHKS15,0)+nvl(GHKS14,0) where ID0000=ad_ID0000; else update BM_GHKSTJ set GHKS51=0+nvl(GHKS10,0),GHKS52=0+nvl(GHKS12,0),GHKS53=0+nvl(GHKS14,0) where ID0000=ad_ID0000; end if; --前台移到后台统一处理 --GHKS01 本期收入 GHKS02:本期退出 GHKS03:本期结存 begin select nvl(GHKS01,0),nvl(GHKS02,0),nvl(GHKS03,0),nvl(GHKS51,0),nvl(GHKS52,0),nvl(GHKS53,0) into lv_BQSR00,lv_BQTC00,lv_BQJC00,lv_JKTHJ0,lv_WXHJ00,lv_ZFBHJ0 from BM_GHKSTJ where ID0000=ad_ID0000 and GHRQ00='合计'; exception when others then lv_BQSR00:=0; --本期收入 lv_BQTC00:=0; --本期退出 lv_BQJC00:=0; --本期结存 lv_JKTHJ0:=0;--健康通合计 lv_WXHJ00:=0;--微信合计 lv_ZFBHJ0:=0;--支付宝合计 end; --结算上期结存 begin select sum(SQJC00) into LV_YJJSQJC from SF_YJJYE0 where CZRQ00=LS_KSRQ00 and BRID00=0; exception when others then LV_YJJSQJC:=0; end; if SF_SFYJXGSJQY='Y' and nvl(LV_YJJSQJC,0)<>0 then LV_YJJSQJC:=nvl(LV_YJJSQJC,0); else begin select nvl(sum(DECODE(Trim(D.YBLB00),'Z',DECODE(C.ZFFS00,10,0,11,0,C.JFJE00),C.JFJE00)),0) JE0000 into lv_YJJSQJC from SF_BRJFB0 C,BM_BRXXB0 D where 1 = 1 AND C.BRID00=D.BRID00 and C.ZFFS00 not in (14,15) and C.jfrq00 '0' then begin select sum(B.ZFJE00) JE0000 into LV_BQYJJJS from sf_brfy00 A,BM_BMBM00 F,SF_FYMX00 B where A.JFLBID+0 = 1 AND A.DJH000=B.DJH000 AND B.KDKS00=F.BMBH00 and A.CZRQ00 >=LS_KSRQ00 and A.CZRQ00 <=LS_JSRQ00 and A.CZRQ00||A.CZSJ00>=LS_KSRQ00||LS_KSSJ00 and A.CZRQ00||A.CZSJ00<=LS_JSRQ00||LS_JSSJ00 and (f.YYID00 = as_YYID00 OR trim(as_YYID00) ='0'); exception when others then LV_BQYJJJS:=0; end; else begin select sum(A.ZFJE00) JE0000 into LV_BQYJJJS from sf_brfy00 A where A.JFLBID+0 = 1 and A.CZRQ00 >=LS_KSRQ00 and A.CZRQ00 <=LS_JSRQ00 and A.CZRQ00||A.CZSJ00>=LS_KSRQ00||LS_KSSJ00 and A.CZRQ00||A.CZSJ00<=LS_JSRQ00||LS_JSSJ00; exception when others then LV_BQYJJJS:=0; end; end if; --LS_BEIZH4:='本期预交金结算: '||trim(to_char(nvl(LV_BQYJJJS,0),'999999999990.00'))||' 元';--本期预交金结算 LS_BEIZH4:='本期预交金结算'; --本期预交金收退合计=lv_BQSR00 + lv_BQTC00 + lv_BQJC00; LV_BQYJJSTHJ:=nvl(lv_BQSR00,0)+ nvl(lv_BQTC00,0) + nvl(lv_BQJC00,0); --LS_BEIZH2:='本期预交金收退合计: '||trim(to_char(nvl(LV_BQYJJSTHJ,0),'999999999990.00'))||' 元'; LS_BEIZH2:='本期预交金收退合计'; --本期预交金余额: begin select sum(BQJC00) into lv_BQYJJYE from SF_YJJYE0 where CZRQ00=LS_JSRQ00 and BRID00=0; exception when others then lv_BQYJJYE:=0; end; if SF_SFYJXGSJQY='Y' and nvl(lv_BQYJJYE,0)<>0 then lv_BQYJJYE:=nvl(lv_BQYJJYE,0); else --预交金上期结存LV_YJJSQJC + 本期预交金收退合计LV_BQYJJSTHJ - 本期预交金结算LV_BQYJJJS lv_BQYJJYE:=nvl(LV_YJJSQJC,0)+nvl(LV_BQYJJSTHJ,0)-nvl(LV_BQYJJJS,0); end if; --LS_BEIZH5:='本期预交金余额: '||trim(to_char(nvl(lv_BQYJJYE,0),'999999999990.00'))||' 元'; LS_BEIZH5:='本期预交金余额 '; if LS_MSTQY0='Y' then begin select to_number(SF_SF_MSTYEQKB(LS_KSRQ00,LS_KSSJ00,LS_JSRQ00,LS_JSSJ00,as_YYID00,26)) into lv_FSFYJE from dual; exception when others then lv_FSFYJE:=0; end; --LS_BEIZH3:='非收费员健康通金额:'||trim(to_char(nvl(lv_FSFYJE,0),'999999999990.00'))||' 元'; LS_BEIZH3:='非收费员健康通金额'; end if; if LS_YYTQY0='Y' then begin select to_number(SF_SF_MSTYEQKB(LS_KSRQ00,LS_KSSJ00,LS_JSRQ00,LS_JSSJ00,as_YYID00,18)) into lv_FSFYJE from dual; exception when others then lv_FSFYJE:=0; end; --LS_BEIZH3:='非收费员银医通金额:'||trim(to_char(nvl(lv_FSFYJE,0),'999999999990.00'))||' 元'; LS_BEIZH3:='非收费员银医通金额'; end if; -- by MZSF-20170310-001 /* 目前的计算是： 本期预交金余额:10742355.58= 10742512.54+10.00-7.00+900.00-1059.96= 10742355.58 本期预交金余额=预交金上期结存(10742512.54)+预交金收入(10.00)-预交金退出(7.00)+医保转入(900.00)-本期预交金结算(1059.96) 改成 本期预交金余额=预交金上期结存(10742512.54)+预交金收入(10.00)-预交金退出(7.00)+医保转入(900.00)-本期预交金结算(1059.96)+健康通转入(54.96) */ --lv_BQYJJYE:=nvl(lv_BQYJJYE,0)+nvl(lv_WXHJ00,0)+nvl(lv_ZFBHJ0,0); --MZSF-20171027-003移动到下面的else if LS_YJJYEXSFQY='Y' then lv_JKTHJ0:=nvl(lv_JKTHJ0,0)+nvl(lv_FSFYJE,0);--+健康通转入(54.96)+'非收费员健康通金额(0) lv_BQYJJYE:=nvl(lv_BQYJJYE,0)+nvl(lv_JKTHJ0,0); else lv_BQYJJYE:=nvl(lv_BQYJJYE,0)+nvl(lv_WXHJ00,0)+nvl(lv_ZFBHJ0,0); --MZSF-20171027-003移动到else end if; --GHKS61 ,BEIZH1 :预交金上期余额 GHKS62 ,BEIZH2:本期预交金收退合计 GHKS63 ,BEIZH3:非收费员健康通合计 GHKS64 ,BEIZH4:本期预交金结算 GHKS65 ,BEIZH5:本期预交金余额 update BM_GHKSTJ set GHKS61=nvl(LV_YJJSQJC,0), BEIZH1=LS_BEIZH1, --预交金上期余额 GHKS62=nvl(LV_BQYJJSTHJ,0), BEIZH2=LS_BEIZH2, --本期预交金收退合计 GHKS63=nvl(lv_FSFYJE,0), BEIZH3=LS_BEIZH3,--非收费员健康通 GHKS64=nvl(LV_BQYJJJS,0), BEIZH4=LS_BEIZH4,--本期预交金结算 GHKS65=nvl(lv_BQYJJYE,0), BEIZH5=LS_BEIZH5 --本期预交金余额 where ID0000=ad_ID0000 and GHRQ00='合计'; commit; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_YJJYE0_QKBB00('||as_KSRQ00||','||as_JSRQ00|| ',ad_ID0000,as_YHMSG0,as_SYSMSG)',1,150); ROLLBACK; END; / -- End of DDL Script for Procedure SD_HOSPITAL.SP_SF_YJJYE0_QKBB00