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':退款 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, --出票单位帐号或医保中心帐号 ad_CZYKS0 in number, --操作员科室 ad_ZPDWID in number default null, --转账单位ID as_JYLSH0 in char default null, --交易流水号 as_YKTSH0 in char default null, --健康通流水号 as_JYLXMC in char default null, --交易类型名称 as_JGBM00 in char default null, --机构编码 as_JGMC00 in char default null, --机构名称 as_DDLSH0 in char default null --订单流水号 ) -- MODIFICATION HISTORY -- Person Date Comments -- lintj 2012.08.09 modify -- zhangwz 2012.08.27 对于 ls_YYID00 的引用从数字型变更为字符型。 -- zhanghr 2012.10.30 整改 -- zhanghr 2012.11.2 电子钱包不产生票据 ? -- jlg 2016.07.12 修改病人在住院时不能进行门诊预交金操作的处理 MZSF9-20160616-002 -- zhangyc 2017.06.26 增加微信支付(ZFFS00=24)和支付宝支付(ZFFS00=25); by MZSF9-20170328-001 -- jlg 2017.09.19 增加ZPDWID字段传参 MZSF9-20170919-002 -- jlg 2017.09.22 增加参数SF_SFYXZFBRTZZYE的处理 MZSF9-20170912-002 -- qks 2018.03.15 增加入参as_JYLSH0,as_YKTSH0,as_JYLXMC,as_JGBM00,as_JGMC00,as_DDLSH0; for MZSF9-20180315-001 as ls_JFLBID BM_BRJFLB.JFLBID%type; ls_ZFFSBH BM_BRZFFS.ZFFSBH%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_ZZYE00 SF_PJSYQK.ZZJE00%type; ls_TYDWID BM_BRXXB0.TYDWID%type; ls_ZYZT00 BM_BRXXB0.ZYZT00%type; ls_FBBH00 number; ls_SSWUTSCL char(4); ls_YJJPJ0 char(4); --预交金票据与预交金退一样 ls_PLBHCS char(4); ls_CZRQ00 char(8); ls_CZSJ00 char(8); ls_count0 number(5); ls_DZQBJE SF_PJSYQK.DZQBJE%type; --电子钱包金额 ls_YYID00 XT_YYXX00.YYID00%type; ls_ZYJKT0 XT_XTCS00.VALUE0%type; --住院病人在门诊的交、退款限制 ls_YXZZTK XT_XTCS00.VALUE0%type; --是否允许自费病人转账退款 ls_ZZXJTC XT_XTCS00.VALUE0%type; --预交金已转帐，是否可以以现金退出 Vzxcgbz number; Vtsxx00 varchar2(200); Vxxxx00 varchar2(200); 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; --爱健康卡客户限制 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; --住院病人门诊交退款控制 0:交、退款都限制 1:不允许交款 2 不允许退款 3 交、退都不限制 select nvl(max(trim(VALUE0)),'0') into ls_ZYJKT0 from XT_XTCS00 where NAME00='SF_ZYBRSFYXJKTKZ'; --门诊预交金退款票据是否取预交金交款票据序号 Y:是N否，默认N select nvl(max(trim(VALUE0)),'N') into ls_YJJPJ0 from XT_XTCS00 where name00 = 'SF_YJJPJ0'; --SF_YJJTKPJLX:当SF_YJJPJ0=Y时门诊预交金退款票据类型(SF_PJSYQK.PLBH00)于交款票据类型是否一致 select nvl(max(trim(VALUE0)),'Y') into ls_PLBHCS from XT_XTCS00 where name00 = 'SF_YJJTKPJLX'; --四舍五入部分的金额特殊处理,Y:是 select nvl(max(trim(VALUE0)),'N') into ls_SSWUTSCL from XT_XTCS00 where name00 = 'SF_SSWUTSCL'; --门诊是否允许自费病人退转账余额 1是0否，默认0 select nvl(max(trim(VALUE0)),'0') into ls_YXZZTK from XT_XTCS00 where name00 = 'SF_SFYXZFBRTZZYE'; --预交金已转帐，是否可以以现金退出 select nvl(max(trim(VALUE0)),'N') into ls_ZZXJTC from XT_XTCS00 where name00 = 'SF_ZZSRSFNYXJTC'; begin select SF_SF_GETYYID00() YYID00 into LS_YYID00 from dual; exception when others then LS_YYID00:='0'; end; select JFLBID into ls_JFLBID from BM_BRJFLB where JFLBMC = as_JFLBMC; select ZFFSBH into ls_ZFFSBH from BM_BRZFFS where ZFFSMC = as_ZFFSMC; if as_CZBZ00 = '1' then ls_JZDH00:=1; else ls_JZDH00:=0; end if; if ls_ZFFSBH in (8,24,25) then ls_YHKJE0 := ls_ZZYE00; ls_ZZYE00 := 0; end if; if ls_ZFFSBH = 18 then ls_DZQBJE := ls_ZZYE00; ls_ZZYE00 := 0; end if; --从BM_BRXXB0.ZYZT00获取病人住院状态 select BRXM00,BRXB00,ZYZT00,nvl(TYDWID,0),MZZHID,FBBH00 into ls_BRXM00,ls_BRXB00,ls_ZYZT00,ls_TYDWID,ls_MZZHID,ls_FBBH00 from BM_BRXXB0 where BRID00 = ad_BRID00; --病人如果处于住院状态，不允许进行门诊预交金操作 --加参数SF_ZYBRSFYXJKTKZ控制 0：限制交、退款 1限制交款 2限制退款 3 交、退都不限制 if (ls_ZYZT00='1') then if (ls_ZYJKT0='0') then as_YHMSG0:='该病人正在住院，系统设置不允许进行门诊预交金交退操作!'; as_SYSMSG:='该病人(BRID='||to_char(ad_BRID00)||')正在住院，系统设置不允许进行门诊预交金交退操作!'; rollback; return; elsif (ls_ZYJKT0='1') and (as_CZBZ00 ='0') then as_YHMSG0:='该病人正在住院，系统设置不允许进行门诊预交金交款操作!'; as_SYSMSG:='该病人(BRID='||to_char(ad_BRID00)||')正在住院，系统设置不允许进行门诊预交金交退操作!'; rollback; return; elsif (ls_ZYJKT0='2') and (as_CZBZ00 ='1') then as_YHMSG0:='该病人正在住院，系统设置不允许进行门诊预交金退款操作!'; as_SYSMSG:='该病人(BRID='||to_char(ad_BRID00)||')正在住院，系统设置不允许进行门诊预交金交退操作!'; rollback; return; end if; --其它情况允许操作 end if ; --0、判断该类病人的账户信息是否存在,若不存在，应该添加该病人的账户信息 select count(*) into ls_count0 from SF_BRZHXX where MZZHID=ls_MZZHID; if ls_count0=0 then as_YHMSG0:='该病人无门诊账户，不能操作'; as_SYSMSG:='数据异常'; rollback; return; end if; select SFDJ00,ZYE000,XJYE00,ZZYE00 into ls_SFDJ00,ls_TMPZYE,ls_TMPXJE,ls_TMPZZE from SF_BRZHXX where MZZHID=ls_MZZHID and JFLBID = ls_JFLBID; if ls_SFDJ00 <> '0' then as_YHMSG0:='该病人的账户的状态不是有效的状态,请先将该病人的账户转换成有效状态后,再进行本操作!!'; as_SYSMSG:=SQLERRM||'SFDJ00:='||ls_SFDJ00; rollback; return; end if; ls_ZJE000:=ad_XJYE00+ad_ZZYE00; if as_CZBZ00 = '1' then --若是退款的 if ls_TMPZYE <> ls_TMPXJE + ls_TMPZZE then as_YHMSG0:='该病人的账户的信息非法,请与系统管理员联系!'; as_SYSMSG:=SQLERRM||'该病人的帐户信息不合法,现金余额+转账余额不等于总余额!'; rollback; return; end if; if abs(ls_ZJE000) > ls_TMPZYE then as_YHMSG0:='该病人的账户余额不足于支付该病人本次操作的金额!'; as_SYSMSG:=SQLERRM||'目前的账户余额是:'||to_char(ls_TMPZYE); rollback; return; end if; if ls_ZZXJTC='Y' then ls_TMPXJE:=ls_TMPXJE+ls_TMPZZE; end if; if ad_XJYE00<0 and -1*ad_XJYE00>ls_TMPXJE then --退现金 as_YHMSG0:='该病人账户现金余额('||to_char(ls_TMPXJE)||')不足支付该病人本次退款的现金金额'; as_SYSMSG:=SQLERRM; rollback; return; end if; if ls_FBBH00=1 and ls_ZZYE00<0 and ls_YXZZTK<>'1' then as_YHMSG0:='自费病人不能做转账退款操作'; as_SYSMSG:=SQLERRM||'目前的账户转账余额是:'||to_char(ls_TMPZZE); rollback; return; end if; end if; 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,CZYKS0,ZPDWID, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) values(ad_PJH000,ad_BRID00,ls_JFLBID,ls_ZJE000,ls_ZFFSBH,as_ZFFSMC,as_ZPH000,as_ZPKHYH,as_ZPDWMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,as_CZBZ00,0,ls_JZDH00,1,ls_ZJE000,as_ZPZH00,ad_CZYKS0,ad_ZPDWID, as_JYLSH0,as_YKTSH0,as_JYLXMC,as_JGBM00,as_JGMC00,as_DDLSH0); else insert into SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0,ZPDWID, JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0) values(ad_PJH000,ad_BRID00,ls_JFLBID,ls_ZJE000,ls_ZFFSBH,as_ZFFSMC,as_ZPH000,as_ZPKHYH,as_ZPDWMC,ls_PJH000, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,0,as_CZBZ00,0,ls_JZDH00,1,ls_ZJE000,ad_CZYKS0,ad_ZPDWID, as_JYLSH0,as_YKTSH0,as_JYLXMC,as_JGBM00,as_JGMC00,as_DDLSH0); end if; SP_SF_BRJFB0_INSERT(ad_PJH000,ad_CZY000,ad_CZYKS0,'N',Vzxcgbz,as_YHMSG0,as_SYSMSG); --2、添加票据使用情况 if ls_ZFFSBH <> 18 then--电子钱包不产生票据 if as_CZBZ00 = '0' then --'0':交款,'1':退款 insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,HJJE00,CZYKS0) 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,ad_XJYE00+ls_ZZYE00+ls_YHKJE0+ls_DZQBJE,ad_CZYKS0 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,HJJE00,CZYKS0) 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,ad_XJYE00+ls_ZZYE00+ls_YHKJE0+ls_DZQBJE,ad_CZYKS0 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,HJJE00,CZYKS0) 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,ad_XJYE00+ls_ZZYE00+ls_YHKJE0+ls_DZQBJE,ad_CZYKS0 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; SP_SF_PJSYQK_INSERT( ls_PJH000, --票据号 ad_CZY000, --输入：操作员编码 ad_CZYKS0, --操作员科室 'N', --是否提交 Vzxcgbz, --执行成功标志 1成功 0失败 Vtsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Vxxxx00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); 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:='系统出错，请查看详细信息。如有不明，请与管理员联系！'||SQLERRM; as_SYSMSG:=substr('执行过程SP_SF_BRZHXX时发生错误。'||SQLERRM,1,150); rollback; end;