prompt 19、门诊病人交、退款操作的存储过程 SP_SF_BRZHXX create or replace procedure SP_SF_BRZHXX ( ad_BRID00 in number, --病人ID ad_CZY000 in number, --操作员ID as_CZYXM0 in char, --操作员姓名 as_JFLBMC in char, --账户类别、或称为交费类别 ad_XJYE00 in number, --病人现金余额 ad_ZZYE00 in number, --病人转账余额 ad_BJJE00 in number, --报警金额 ad_TYJE00 in number, --停用金额 ad_YXQFJE in number, --允许病人的欠费的最大金额 as_ZFFSMC in char, --支付方式 as_ZPH000 in char, --支票号 as_ZPKHYH in char, --出票的开户银行 as_ZPDWMC in char, --出票单位/出票人 as_YJJXH0 in char, --预交金票据号 as_CZBZ00 in char, --'0'或'1','0':交款,'1':退款 as_KTZZBZ in char default '0', --'0':可以退转账款,'1':不可以退转账款,'2':可以退转账款(不受医院参数控制) ad_PJH000 out number, --内部票据号 as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_COMMIT in char default 'Y', --是否提交 默认提交 as_ZPZH00 in char default null, --出票单位帐号或医保中心帐号 as_JFYY00 in varchar2 default null --缴费原因 ) -- MODIFICATION HISTORY -- Person Date Comments ---dsm 2007.07.26 煤矿医院特约单位的转帐余额不能退 ---dsm 2007.11.21 加参数as_KTZZBZ ---qks 2008.07.18 as_KTZZBZ值增加'2':可以退转账款(不受医院参数控制)MZSF-20080716-002 -- CSF 2009.07.15 增加是否提交参数 -- qks 2010.06.23 SF_PJSYQK增加字段DZQBJE -- qks 2010.08.18 正在住院病人允许交预交金,不允许退预交金 -- zhangyc 2010.10.20 长乐二院YYID00=222147 则允许交，不允许退，如果是其他医院，则交退都限制 -- zhangyc 2010.11.05 修改医院id取值(select SF_SF_GETYYID00() YYID00 from dual) -- CSF 2010.12.03 关于病人的缴退款控制不一致，加参数SF_ZYBRSFYXJKTKZ控制 0：限制缴、退款 1限制缴款 2限制退款 3 缴、退都不限制 -- qks 2011.01.12 增加传入参数as_ZPZH00 --zhangyc 2011.03.21 ZY_YJJPJ0改为SF_YJJPJ0 增加参数SF_YJJTKPJLX（退款和缴款票据类型编号是否一致 SF_PJSYQK.PLBH00） --zhanghr 2011.10.06 爱健康卡的客户只能由特定人员做预交金操作，其他人员不能操作 --zhangyc 2011.11.13 修正LS_YYID00=222147判断出错 by MZSF-20121109-001 --linzy 2015.01.22 增加缴费原因入参 by MZSF-20141212-002 --zhangyc 2015.10.30 支付方式=26(健康通支付)SF_PJSYQK.PHLYXZ插入N 同时修改触发tr_sf_sf_pjsyqk_insert限制 by MZSF-20151030-001 --zhangyc 2015.11.12 增加健康通现金提现 by MZSF-20151112-001 --zhangyc 2016.05.13 修正健康通体现时支付方式错误问题 by MZSF-20160509-002 -- qks 2016.05.28 增加参数SF_XZFBTFJS,限制某类费别病人不允许退预交金以及结算 by MZSF-20160512-001 -- xuzw 2017.03.20 启用一卡通平台，判断余额是否足够需加入其他医院预交金余额 MZSF-20170329-001 -- zhangyc 2017.06.02 增加（微信支付、支付宝支付）控制 by MZSF-20170508-001 -- zhangyc 2018.02.06 增加限制微信和支付： 'Y','M','P'，防止脱机支付 by MZSF-20171221-001 -- dsm 2019.07.02 泉二体检消费修改不能退款的费别判断增加ZFFS00<>2 归档 ZYSF-20190702-002 -- dsm 2019.08.23 SF_XJZXZZFFSTK='Y'时允许退支付宝 for MZSF-20190719-001 -- qiulf 2020.04.17 退现金记录缴费表TKJE00 by MZSF-20200227-003 -- zhangyc 2021.07.22 退款时要扣除信用授权金额 by MZSF-20210721-004 ---------- ------------- --------------------------------------------------------------------- as ls_JFLBID BM_BRJFLB.JFLBID%TYPE; ls_ZFFSBH BM_BRZFFS.ZFFSBH%TYPE; ls_ZFFSMC SF_BRJFB0.ZFFSMC%TYPE; ls_TMPZYE SF_BRZHXX.ZYE000%TYPE; ls_TMPXJE SF_BRZHXX.XJYE00%TYPE; ls_TMPZZE SF_BRZHXX.ZZYE00%TYPE; ls_ZJE000 SF_BRJFB0.JFJE00%TYPE; ls_PJH000 SF_BRJFB0.PJH000%TYPE; ls_BRXM00 BM_BRXXB0.BRXM00%TYPE; ls_BRXB00 BM_BRXXB0.BRXB00%TYPE; ls_MZZHID SF_BRZHXX.MZZHID%TYPE; ls_SFDJ00 SF_BRZHXX.SFDJ00%TYPE; ls_JZDH00 SF_BRJFB0.JZDH00%TYPE; ls_YHKJE0 SF_PJSYQK.YHKJE0%TYPE; --银行卡金额 ls_YHKJF0 SF_BRJFB0.JFJE00%TYPE; ls_ZZYE00 SF_PJSYQK.ZZJE00%TYPE; ls_sswrje SF_BRJFB0.JFJE00%TYPE; ls_TYDWID BM_BRXXB0.TYDWID%TYPE; ls_ZYZT00 BM_BRXXB0.ZYZT00%TYPE; ls_FBBH00 BM_BRXXB0.FBBH00%TYPE; ls_SSWUTSCL char(4); ls_YJJPJ0 char(4); --预交金票据与预交金退一样 ls_PLBHCS char(4); LS_FFJJ char(4); ls_CZRQ00 char(8); ls_CZSJ00 char(8); ls_count0 number(5); ls_DZQBJE SF_PJSYQK.DZQBJE%TYPE; --电子钱包金额 ls_YYID00 varchar2(6); SF_ZYBRSFYXJKTKZ XT_XTCS00.VALUE0%type;--住院病人在门诊的缴、退款限制 LS_PHLYXZ char(1); LS_ZPKHYH SF_BRJFB0.ZPKHYH%TYPE; ls_YKTYE0 SF_BRZHXX.YKTYE0%TYPE; ls_SFQYYKTPT XT_XTCS00.VALUE0%TYPE; LS_TYZFCS varchar2(100);--门诊是否启用微信或支付宝接口 Y:易惠公司(老年、长乐、省二、肺科) M:医信公司(罗源医院) P:联空网络(市皮医院) N:都不开启 SF_XJZXZZFFSTK varchar2(10); LS_ZXZT00 number; LS_XYFPAY number(12,2); --信用付金额 LS_KTXJYE number(12,2); --可退现金余额 begin ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); ls_YHKJE0 := 0; --银行卡金额 ls_DZQBJE := 0; --电子钱包金额 ls_ZZYE00 := ad_ZZYE00; LS_ZPKHYH :=as_ZPKHYH; LS_XYFPAY := 0;--信用付金额 LS_KTXJYE := 0;--可退现金余额 -- select nvl(max(trim(VALUE0)),'0') into SF_ZYBRSFYXJKTKZ from XT_XTCS00 where NAME00='SF_ZYBRSFYXJKTKZ'; begin select trim(value0) into LS_FFJJ from xt_xtcs00 where name00 = 'SF_ZY_FFJJ'; exception when others then LS_FFJJ:='N'; end; begin select trim(value0) into ls_YJJPJ0 from xt_xtcs00 where name00 = 'SF_YJJPJ0'; exception when others then ls_YJJPJ0:='N'; end; --SF_YJJTKPJLX:当SF_YJJPJ0=Y时门诊预交金退款票据类型(SF_PJSYQK.PLBH00)于交款票据类型是否一致 begin select trim(value0) into ls_PLBHCS from xt_xtcs00 where name00 = 'SF_YJJTKPJLX'; exception when others then ls_PLBHCS:='Y'; end; begin select trim(value0) into ls_SSWUTSCL from xt_xtcs00 where name00 = 'SF_SSWUTSCL'; exception when others then ls_SSWUTSCL:='N'; end; begin --select trim(value0) into ls_SFQYYKTPT from xt_xtcs00 where name00 = 'SF_SFQYYKTPT'; --是否启用一卡通平台（武夷山） select trim(MC0000) into ls_SFQYYKTPT from BM_TYZD00 where ZDMC00='一卡通平台参数' and BH0000='SF_SFQYYKTPT' and rownum=1; exception when others then ls_SFQYYKTPT:='N'; end; begin --select yyid00 into LS_YYID00 from xt_yyxx00 where rownum=1; select SF_SF_GETYYID00() YYID00 into LS_YYID00 from dual; exception when others then LS_YYID00:=0; end; --门诊是否启用微信或支付宝接口 Y:易惠公司(老年、长乐、省二、肺科) M:医信公司(罗源医院) P:联空网络(市皮医院) N:都不开启 begin select SF_SF_TYZD00('支付统一平台参数','Open_MZ') into LS_TYZFCS from dual; exception when others then LS_TYZFCS:='N'; end; if (LS_TYZFCS in ('Y','M','P')) and ((trim(as_ZFFSMC)='微信支付') or (trim(as_ZFFSMC)='支付宝支付')) then as_YHMSG0:=trim(as_ZFFSMC)||'支付错误,系统参数已开启联机支付,无法脱机支付充值或退款！'; as_SYSMSG:=as_YHMSG0; rollback; return; end if; select nvl(max(trim(value0)),'N') into SF_XJZXZZFFSTK from XT_XTCS00 where name00='SF_XJZXZZFFSTK'; --微信或支付宝不允许退款 if (as_CZBZ00='1') and ((trim(as_ZFFSMC)='微信支付') or (trim(as_ZFFSMC)='支付宝支付')) and SF_XJZXZZFFSTK='N' then as_YHMSG0:='系统限制支付方式为['||as_ZFFSMC||']不允许退款！'; as_SYSMSG:=as_YHMSG0; rollback; return; end if; if as_CZBZ00 = '1' then ls_JZDH00:=1; else ls_JZDH00:=0; end if; select JFLBID into ls_JFLBID from BM_BRJFLB where JFLBMC = as_JFLBMC; begin select ZFFSBH into LS_ZFFSBH from BM_BRZFFS where ZFFSMC = as_ZFFSMC; exception when others then LS_ZFFSBH:=1; end; if LS_ZFFSBH in (8,24,25) then --银行卡,微信支付,支付宝支付 ls_YHKJE0 := ad_ZZYE00; ls_ZZYE00 := 0; end if; if LS_ZFFSBH = 18 then ls_DZQBJE := ad_ZZYE00; ls_ZZYE00 := 0; end if; -- by MZSF-20151030-001 24:微信支付 25:支付宝支付 26：健康通支付 if LS_ZFFSBH in (24,25,26) then LS_PHLYXZ:='N'; else LS_PHLYXZ:='Y'; end if; if (as_CZBZ00='1')and (LS_ZFFSBH=26) then begin select JYLSH0 into LS_ZPKHYH from SF_YKTKFB where brid00=ad_BRID00 and ID0000=as_ZPH000; exception when others then LS_ZPKHYH:=''; end; end if; select BRXM00,BRXB00,ZYZT00,NVL(TYDWID,0),FBBH00 into ls_BRXM00,ls_BRXB00,ls_ZYZT00,ls_TYDWID,ls_FBBH00 from BM_BRXXB0 where BRID00 = ad_BRID00; --病人如果处于住院状态，不允许进行门诊预交金操作 --长乐二院(YYID00=222147)，则允许交，不允许退，如果是其他医院，则交退都限制 --if ls_ZYZT00='1' then --加参数SF_ZYBRSFYXJKTKZ控制 0：限制缴、退款 1限制缴款 2限制退款 3 缴、退都不限制 if (ls_ZYZT00='1' and as_CZBZ00 = '1' and LS_YYID00='222147') or (ls_ZYZT00='1' and LS_YYID00<>'222147' and SF_ZYBRSFYXJKTKZ='0') or (ls_ZYZT00='1' and LS_YYID00<>'222147' and SF_ZYBRSFYXJKTKZ='1' and as_CZBZ00='0') or (ls_ZYZT00='1' and LS_YYID00<>'222147' and SF_ZYBRSFYXJKTKZ='2' and as_CZBZ00='1') then as_YHMSG0:='该病人正在住院，不允许进行门诊预交金操作！'; as_SYSMSG:=SQLERRM||'SFDJ00:='||ls_SFDJ00; ROLLBACK; RETURN; end if ; if as_CZBZ00='1' and LS_ZFFSBH<>'2' then --泉二 体检LS_ZFFSBH<>'2' 加 select count(1) into ls_COUNT0 from xt_xtcs00 where name00 = 'SF_XZFBTFJS' and instrb(','||trim(value0)||',',','||to_char(ls_FBBH00)||',')>0; if ls_COUNT0>0 then as_YHMSG0:='该费别的病人不允许退款！'; as_SYSMSG:='该费别的病人不允许退款！'||SQLERRM; rollback; return; end if; end if; ls_ZJE000:=ad_XJYE00+ad_ZZYE00; --0、判断该类病人的账户信息是否存在,若不存在，应该添加该病人的账户信息 begin select MZZHID,SFDJ00,nvl(ZYE000,0),nvl(XJYE00,0),nvl(ZZYE00,0),nvl(YKTYE0,0) into ls_MZZHID,ls_SFDJ00,ls_TMPZYE,LS_TMPXJE,ls_TMPZZE,ls_YKTYE0 from SF_BRZHXX where BRID00 = ad_BRID00 and JFLBID = ls_JFLBID; if ls_SFDJ00 <> '0' then as_YHMSG0:='该病人的账户的状态不是有效的状态,请先将该病人的账户转换成有效状态后,再进行本操作！！'; as_SYSMSG:=SQLERRM||'SFDJ00:='||ls_SFDJ00; rollback; return; end if; --2021.07.22 退款时判断信用付授权金额--begin-- if as_CZBZ00='1' and as_ZFFSMC not in ('转账') and nvl(substrb(SF_SF_TYZD00('信用付接口参数字典','CreditPay_Open'),1,10),'0')='1' then LS_XYFPAY :=SF_SF_GETBRZHYE(0,ad_BRID00,1,20); --信用付金额 if nvl(LS_XYFPAY,0)>0 and nvl(LS_TMPXJE,0)>0 then if nvl(LS_TMPXJE,0)>=nvl(LS_XYFPAY,0) then LS_KTXJYE := nvl(LS_TMPXJE,0)-nvl(LS_XYFPAY,0);--可退现金余额=现金余额-信用授权金额 else LS_KTXJYE :=0;--可退现金余额 end if; if abs(ls_ZJE000)>nvl(LS_KTXJYE,0) then --本次退款金额>现金可退金额 报错 as_YHMSG0:='预交金退款失败,原因:退款金额超过账户现金允许可退金额!' ||'本次退款方式:'||as_ZFFSMC||',退款金额:'||trim(to_char(abs(ls_ZJE000),'999999990.99'))||'元,' ||'【账户现金余额:'||trim(to_char(LS_TMPXJE,'999999990.99'))||'元,' ||'其中包含信用授权金额:'||trim(to_char(LS_XYFPAY,'999999990.99'))||'元,' ||'最多可退金额:'||trim(to_char(LS_KTXJYE,'999999990.99'))||'元,' ||'】' ; as_SYSMSG:=as_YHMSG0; rollback; return; end if; end if; end if; --2021.07.22 退款时判断信用付授权金额--end-- if as_CZBZ00 = '1' then --若是退款的 --漳州平和医院要求转帐(实际上为医保卡)的预交金不能退，只能作废 MZSF-20050428-006 select count(*) into ls_COUNT0 from xt_yyxx00 where YYJC00='平和县医院' or YYJC00 like '%邵武%人民%'; if ls_COUNT0>0 then select count(*) into ls_COUNT0 from sf_brzhxx where BRID00 = ad_BRID00 and JFLBID = ls_JFLBID and ZZYE00>0 and XJYE000 then as_YHMSG0:='病人还有转帐余额,不能进行清账处理！'; ROLLBACK; return; end if; end if; select count(*) into ls_COUNT0 from xt_yyxx00 where YYQC00 like '%煤矿%'; if ls_COUNT0>0 and ls_TYDWID>0 and abs(ad_ZZYE00)>0 and as_KTZZBZ='0' then as_YHMSG0:='转帐余额不能退！'; ROLLBACK; return; end if; ----邵武医院要求银行卡(实际上关爱基金)的预交金不能退，只能作废 MZSF-20060621-002 --select count(*) into ls_COUNT0 from XT_YYXX00 where YYJC00 like '%邵武%人民%'; --if ls_COUNT0>0 then -- select sum(JFJE00) into ls_YHKJF0 from SF_BRJFB0 where BRID00=ad_BRID00 and ZFFS00='8'; -- select count(*) into ls_COUNT0 from SF_BRZHXX where BRID00=ad_BRID00 and JFLBID=ls_JFLBID and -- XJYE00+ZZYE00+ls_ZJE000-ls_YHKJF0<0; -- if ls_COUNT0>0 then -- as_YHMSG0:='关爱基金充值的预交金不能退！'; -- ROLLBACK; -- RETURN; -- end if; --end if; if ls_TMPZYE <> ls_TMPXJE + ls_TMPZZE then as_YHMSG0:='该病人的账户的信息非法,请与系统管理员联系！'; as_SYSMSG:=SQLERRM||'该病人的帐户信息不合法,现金余额+转账余额不等于总余额！'; ROLLBACK; return; end if; --一卡通平台的话，要加上其他医院金额 if ls_SFQYYKTPT='Y' then ls_TMPZYE:=ls_TMPZYE+ls_YKTYE0; end if; if abs(ls_ZJE000) > ls_TMPZYE then as_YHMSG0:='该病人的账户账户余额不足于支付该病人本次操作的金额,请稍候再试！'; as_SYSMSG:=SQLERRM||'目前的账户余额是:'||to_char(ls_TMPZYE); ROLLBACK; return; end if; end if; exception when no_data_found then --设置门诊病人的账户信息 Insert into SF_BRZHXX(MZZHID,JFLBID,BRID00,SFDJ00,KHRQ00,KHSJ00, CZYBH0,CZYXM0,XJYE00,ZZYE00,ZYE000,YKZYE0,BJJE00,TYJE00,YXQFJE) values(SQ_SF_BRZHXX_ZHYEID.nextval,ls_JFLBID,ad_BRID00,'0',ls_CZRQ00,ls_CZSJ00, ad_CZY000,as_CZYXM0,0,0,0,0,ad_BJJE00,ad_TYJE00,ad_YXQFJE); end; select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; select SQ_SF_BRJFB0_DJH000.nextval into ad_PJH000 from dual; --1、添加病人交费记录，对于病人余额的修改在触发器中修改 if as_CZBZ00='0' then Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,ZPZH00,JFYY00) values(ad_PJH000,ad_BRID00,ls_JFLBID,ls_ZJE000,ls_ZFFSBH,as_ZFFSMC,as_ZPH000,LS_ZPKHYH,as_ZPDWMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,as_CZBZ00,0,ls_JZDH00,1,ls_ZJE000,as_ZPZH00,as_JFYY00); else if (ls_SSWUTSCL='Y')and(ls_ZFFSBH=1)and(ls_ffjj='Y') then ls_sswrje:=ls_ZJE000-round(ls_ZJE000-0.04,1);--宁德四舍五入 Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,JFYY00) values(SQ_SF_BRJFB0_DJH000.nextval,ad_BRID00,ls_JFLBID,ls_sswrje,5,'四舍五入金额',as_ZPH000,LS_ZPKHYH,as_ZPDWMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,as_CZBZ00,0,ls_JZDH00,1,ls_sswrje,as_JFYY00); Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,JFYY00) values(ad_PJH000,ad_BRID00,ls_JFLBID,round(ls_ZJE000-0.04,1),ls_ZFFSBH,as_ZFFSMC,as_ZPH000,LS_ZPKHYH,as_ZPDWMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,as_CZBZ00,0,ls_JZDH00,1,ls_ZJE000,as_JFYY00); else if (LS_ZFFSBH=26) and (trim(as_ZPDWMC)='健康通账户现金转出') then ls_ZFFSBH:=1; ls_ZFFSMC:='现金'; else ls_ZFFSMC:=as_ZFFSMC; end if; Insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,JFYY00) values(ad_PJH000,ad_BRID00,ls_JFLBID,ls_ZJE000, ls_ZFFSBH,ls_ZFFSMC, as_ZPH000,LS_ZPKHYH,as_ZPDWMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,as_CZBZ00,0,ls_JZDH00,1,ls_ZJE000,as_JFYY00); end if; end if; --2、添加票据使用情况 if as_CZBZ00 = '0' then --'0'或'1','0':交款,'1':退款 Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,PHLYXZ) select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ad_XJYE00,ls_ZZYE00,ls_YHKJE0,ad_CZY000,as_CZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_BRXM00,ls_BRXB00,ad_BRID00,ls_DZQBJE,LS_PHLYXZ from BM_PJLXB0 where PLBH00 = 7 ; --收预交金票据 if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败！'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 7)'; ROLLBACK; RETURN; end if; else if (trim(ls_YJJPJ0)='Y') and (trim(ls_PLBHCS)='Y') then Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,PHLYXZ) select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ad_XJYE00,ls_ZZYE00,ls_YHKJE0,ad_CZY000,as_CZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_BRXM00,ls_BRXB00,ad_BRID00,ls_DZQBJE,LS_PHLYXZ from BM_PJLXB0 where PLBH00 = 7 ; --退预交金票据 else Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,PHLYXZ) select ls_PJH000,PLBH00,PLMC00,as_YJJXH0,ad_XJYE00,ls_ZZYE00,ls_YHKJE0,ad_CZY000,as_CZYXM0, ls_CZRQ00,ls_CZSJ00,'Y','0',ls_BRXM00,ls_BRXB00,ad_BRID00,ls_DZQBJE,LS_PHLYXZ from BM_PJLXB0 where PLBH00 = 2 ; --退预交金票据 end if; if SQL%NOTFOUND then as_YHMSG0:='系统字典不完整,所需的票据类型不存在,操作失败！'; as_SYSMSG:=SQLERRM||'票据类型不存在: Not exists(select * from bm_pjlxb0 where PLBH00 = 2)'; ROLLBACK; RETURN; end if; end if; select count(*) into ls_count0 from BM_JTKBR0 where BRID00=ad_BRID00 and KLB000='0'; if ls_count0>0 then if ad_CZY000<>99999 then as_YHMSG0:='爱健康卡客户,不能操作预交金！'; as_SYSMSG:='爱健康卡客户,不能操作预交金！'; ROLLBACK; RETURN; end if; end if; --缴费表记录现金退款 SP_PAY_JFBJRXJTK(ad_BRID00,ls_ZJE000,LS_ZFFSBH,'0',as_CZBZ00,LS_ZXZT00,as_YHMSG0); if LS_ZXZT00 <> 1 then as_SYSMSG:=as_YHMSG0; rollback; return; end if; if as_COMMIT='Y' then COMMIT; end if; exception when no_data_found then as_YHMSG0:='数据没有找到，请核对病人费别或病人的交费类别是否正确！'; as_SYSMSG:=SQLERRM; ROLLBACK; when others then as_YHMSG0:='数据库错误：出错原因未知！'; as_SYSMSG:=substr('执行存储过程错误:SP_SF_BRZHXX'||SQLERRM,1,150); ROLLBACK; end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%