CREATE OR REPLACE PROCEDURE SP_SF_BRJFB0_INSERT( -- MODIFICATION HISTORY -- Person Date Comments -- lintj 2012.07.20 create -- zhanghr 2012.10.30 整理 -- qks 2014.12.21 修改SF_BRJFB0.BRYE00值 -- jlg 2016.08.04 增加在病人账户余额在零与非零之间变动时，记录一卡通平台中间表信息 PT_HIS_YJJBG0 MZSF9-20160727-001 -- jlg 2016.09.08 增加处理余额变更含停用金额的问题 MZSF9-20160907-001 -- qks 2016.12.21 增加参数SF_MZYJJSTJY控制:门诊预交金额收款、退款是否进行跟SF_YJJYE0进行比对校验; for MZSF9-20161221-001 -- liwm 2018.06.07 门诊收费存储过程统一添加调用参数日志 for MZSF9-20180604-003 -- qks 2018.07.06 根据参数SF_ZZSRSFNYXJTC控制：以转账方式交的预交金,是否允许以现金方式退还给病人; for MZSF9-20180702-001 -- qks 2019.06.21 当参数SF_MZYJJSTJY不启用情况下,改为不调用SF_SF_ZHYE00函数获取患者账户余额(SF_YJJYE0表数据没有生成情况下,执行本函数速度很慢); for MZSF9-20190621-002 -- jlg 2020.03.18 处理微信或支付宝退款时，如果有转账余额，则会优先退到转账余额的问题 MZSF9-20200306-003 -- chenHeyi 2021.08.27 参数SF_MZYJJZZJECDXJYE:门诊病人帐户中现金余额为负数,以转帐方式交预交金,先冲抵现金余额为0,剩余金额再转入转帐余额 MZSF9-20210728-001 -- --------- ---------- ------------------------------------------- Pdjh000 in number , --单据号 Pczy000 in number , --输入：操作员编码 Pczyks0 in number , --操作员科室 Pcommit in varchar2 default 'N' , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ) as Vbrjfb0 SF_BRJFB0%rowtype; ls_ERRORS VARCHAR2(50); ls_ZZXJTC XT_XTCS00.VALUE0%type; ls_YBZFZZ XT_XTCS00.VALUE0%type; ls_XZBRFB XT_XTCS00.VALUE0%type; ls_YJJJY0 XT_XTCS00.VALUE0%type; ls_ZZYE00 SF_BRZHXX.ZZYE00%type; --病人账户转账余额 ls_XJYE00 SF_BRZHXX.XJYE00%type; --病人账户现金余额 ls_ZZJE00 SF_BRZHXX.ZZYE00%type; --病人账户转账金额 ls_XJJE00 SF_BRZHXX.XJYE00%type; --病人账户现金金额 ls_ZYE000 SF_BRZHXX.ZYE000%type; --病人账户总余额 ls_ZYE001 SF_BRZHXX.ZYE000%type; --病人账户总余额 ls_OLDZYE SF_BRZHXX.ZYE000%type; --病人旧的账户总余额 ls_TYJE00 SF_BRZHXX.TYJE00%type; --病人账户停用金额 ls_MZZHID SF_BRZHXX.MZZHID%type; --病人账户ID ls_MAXLSH XT_XTRZ00.LSH000%type; ls_FBBH00 BM_BRXXB0.FBBH00%type; ls_CZYKS1 number; ls_CZYKS0 number; ls_QYYKT0 XT_XTCS00.VALUE0%type; --是否启用一卡通平台 ls_ICKH00 IC_YHXX00.ICKH00%type; --病人的IC卡号 ls_YEBZ00 PT_HIS_YJJBG0.YEBZ00%type; --余额标识 ls_ZZCDXJYE XT_XTCS00.VALUE0%type; --参数SF_MZYJJZZJECDXJYE:门诊病人帐户中现金余额为负数,以转帐方式交预交金,先冲抵现金余额为0,剩余金额再转入转帐余额 MZSF9-20210728-001 Vcounter number; --记数器 Vsysdate date; Ecustom exception; --错误变量 Vxxxlog varchar2(2000); begin ls_ERRORS:='1'; Pzxcgbz:=0; Vsysdate:=sysdate; Vxxxlog := substr('PDJH000:'||nvl(to_char(PDJH000), 'null')||','||'PCZY000:'||nvl(to_char(PCZY000), 'null')||','||'PCZYKS0:'||nvl(to_char(PCZYKS0), 'null')||','||'PCOMMIT:'||nvl(to_char(PCOMMIT), 'null'),1,2000); Pxxxx00 := ''; SP_TransLog(Vsysdate,'SP_SF_BRJFB0_INSERT',Pczy000,Pczyks0,Vxxxlog); select * into Vbrjfb0 from SF_BRJFB0 where DJH000=Pdjh000; if Vbrjfb0.JFLBID<>1 then --非预交金 return; end if; ls_ERRORS:='2'; --预交金已转帐，是否可以以现金退出 select nvl(max(trim(VALUE0)),'N') into ls_ZZXJTC from XT_XTCS00 where name00 = 'SF_ZZSRSFNYXJTC'; --医保病人结算如有转账金额支付结算后统筹和医保账户返回金额是否充值到转账金额 Y:充值到转账金额 N:充值到现金金额 默认值:N select nvl(max(trim(VALUE0)),'N') into ls_YBZFZZ from XT_XTCS00 where name00 = 'SF_YBBRZZJEFS'; --控制某种费别的病人不允许退款 select nvl(max(trim(VALUE0)),',-1,') into ls_XZBRFB from XT_XTCS00 where name00 = 'SF_KZMZFBBRBNTK'; --是否启用一卡通平台接口 select nvl(max(trim(value0)),'N') into ls_QYYKT0 from XT_XTCS00 where name00='SF_SFQYYKTPTJK'; --门诊预交金额收款、退款,是否跟预交金余额进行校验 Y:校验 N:不校验 默认值:Y校验 select nvl(max(trim(VALUE0)),'Y') into ls_YJJJY0 from XT_XTCS00 where name00 = 'SF_MZYJJSTJY'; --参数SF_MZYJJZZJECDXJYE:门诊病人帐户中现金余额为负数,以转帐方式交预交金,先冲抵现金余额为0,剩余金额再转入转帐余额 MZSF9-20210728-001 select nvl(max(trim(VALUE0)),'0') into ls_ZZCDXJYE from XT_XTCS00 where name00 = 'SF_MZYJJZZJECDXJYE'; --是否是特殊病人对应的自费消费账户 select count(*) into Vcounter from BM_BRXTB0 where to_char(sysdate,'YYYYMMDD')>=QYRQ00 and to_char(sysdate,'YYYYMMDDHH24:MI:SS')>=QYRQ00||QYSJ00 and TZRQ00>=to_char(sysdate,'YYYYMMDD') and TZRQ00||TZSJ00>=to_char(sysdate,'YYYYMMDDHH24:MI:SS') and ZFBRID=Vbrjfb0.BRID00; if (Vcounter>0) and (Vbrjfb0.JFBZ00='1') then Ptsxx00:='自费消费账户不允许退款'; Raise Ecustom; end if; ls_ERRORS:='3'; --限制某费别的病人不允许退预交金 select nvl(max(FBBH00),0) into ls_FBBH00 from BM_BRXXB0 where BRID00=Vbrjfb0.BRID00; If (Vbrjfb0.JFBZ00='1') and (ls_XZBRFB<>'-1') then if instr(ls_XZBRFB,','||to_char(ls_FBBH00)||',')>0 THEN Ptsxx00:='该费别的病人不允许退款'; Raise Ecustom; return; end if; end if; ls_ERRORS:='4'; select MZZHID into ls_MZZHID from BM_BRXXB0 where BRID00=Vbrjfb0.BRID00; ls_ERRORS:='5'; --select nvl(ZYE000,0),greatest(XJYE00,0),greatest(ZZYE00,0) into ls_OLDZYE,ls_XJYE00,ls_ZZYE00 select nvl(ZYE000,0),XJYE00,ZZYE00 into ls_OLDZYE,ls_XJYE00,ls_ZZYE00 from SF_BRZHXX where JFLBID=Vbrjfb0.JFLBID and BRID00=Vbrjfb0.BRID00; ls_ERRORS:='6'; --在病人预交金退款时候，还应该注意若退的是现金，应从现金总金额上扣除，若退的是转账，应从转账总金额上扣除 --但若转账总金额小于100元时，又可以转为现金处理 if Vbrjfb0.JFJE00<0 then--退费 if Vbrjfb0.ZFFS00=2 then--支票 if ls_ZZYE00<-1*Vbrjfb0.JFJE00 then Ptsxx00:='余额不足，转账余额'||to_char(ls_ZZYE00)||',退费'||to_char(-1*Vbrjfb0.JFJE00); Raise Ecustom; else ls_ZZJE00:=Vbrjfb0.JFJE00; ls_XJJE00:=0; end if; elsif Vbrjfb0.ZFFS00 in (1,8,18,24,25) then--现金 if ls_XJYE00>=-1*Vbrjfb0.JFJE00 then--现金余额充足 ls_XJJE00:=Vbrjfb0.JFJE00; ls_ZZJE00:=0; elsif ls_XJYE00<-1*Vbrjfb0.JFJE00 and ls_XJYE00+ls_ZZYE00>=-1*Vbrjfb0.JFJE00 and (ls_ZZYE00<=100 and ls_ZZXJTC='N' or ls_ZZXJTC='Y') then--现金不足但总额足 if ls_ZZYE00<=-1*Vbrjfb0.JFJE00 then--转账不足 ls_ZZJE00:=-1*ls_ZZYE00; ls_XJJE00:=Vbrjfb0.JFJE00+-1*ls_ZZJE00; else--转账充足 ls_ZZJE00:=Vbrjfb0.JFJE00; ls_XJJE00:=0; end if; else Ptsxx00:='余额不足，现金余额'||to_char(ls_XJYE00)||',转账余额'||to_char(ls_ZZYE00)||',退费'||to_char(-1*Vbrjfb0.JFJE00); Raise Ecustom; end if; else--医保 if ls_ZZYE00>=-1*Vbrjfb0.JFJE00 then--转账余额充足 ls_ZZJE00:=Vbrjfb0.JFJE00; ls_XJJE00:=0; elsif ls_ZZYE00<-1*Vbrjfb0.JFJE00 and ls_XJYE00+ls_ZZYE00>=-1*Vbrjfb0.JFJE00 then--转账不足但总额足 ls_ZZJE00:=-1*ls_ZZYE00; ls_XJJE00:=Vbrjfb0.JFJE00+-1*ls_ZZJE00; else--总额不足 ls_ZZJE00:=-1*ls_ZZYE00; ls_XJJE00:=Vbrjfb0.JFJE00+-1*ls_ZZJE00; end if; end if; else--收款 if Vbrjfb0.ZFFS00=2 then--支票 if ls_ZZCDXJYE='1' and ls_XJYE00<0 then --现金余额为负数,先冲抵现金余额为0,剩余金额再转入转帐余额 MZSF9-20210728-001 if ABS(ls_XJYE00)<=Vbrjfb0.JFJE00 then ls_XJJE00:=ABS(ls_XJYE00); ls_ZZJE00:=Vbrjfb0.JFJE00-ABS(ls_XJYE00); else ls_XJJE00:=Vbrjfb0.JFJE00; ls_ZZJE00:=0; end if; else ls_ZZJE00:=Vbrjfb0.JFJE00; ls_XJJE00:=0; end if; elsif Vbrjfb0.ZFFS00 in (1,8,18,24,25) then--现金 ls_ZZJE00:=0; ls_XJJE00:=Vbrjfb0.JFJE00; else--医保 if ls_YBZFZZ='Y' then ls_ZZJE00:=Vbrjfb0.JFJE00; ls_XJJE00:=0; else ls_ZZJE00:=0; ls_XJJE00:=Vbrjfb0.JFJE00; end if; end if; end if; ls_ERRORS:='77'; update SF_BRZHXX set XJYE00 = NVL(XJYE00,0)+ls_XJJE00 , --病人现金余额 ZZYE00 = NVL(ZZYE00,0)+ls_ZZJE00 , --病人转账余额 ZYE000 = NVL(ZYE000,0)+Vbrjfb0.JFJE00, --账户实际总余额 YKZYE0 = NVL(YKZYE0,0)+Vbrjfb0.JFJE00 --预扣后的总余额 where BRID00 = Vbrjfb0.BRID00; ls_ERRORS:='88'; select ZYE000,TYJE00 into ls_ZYE000,ls_TYJE00 from SF_BRZHXX where BRID00=Vbrjfb0.BRID00; if ls_YJJJY0='Y' then ls_ZYE001 := SF_SF_ZHYE00(Vbrjfb0.BRID00); if ls_ZYE001<>ls_ZYE000 then Ptsxx00:='账户余额异常，请联系管理员'; Raise Ecustom; end if; end if; ls_ERRORS:='病人交费表的余额先修改'; --病人交费表的余额先修改 update SF_BRJFB0 set BRYE00= ls_ZYE000 where DJH000=Pdjh000; -- + Vbrjfb0.JFJE00 --启用一卡通平台,非医保病人 if (ls_QYYKT0 ='Y') and (ls_FBBH00 <>3) then --账户余额在零与非零之间变动时，记录一卡通平台中间表信息 ls_TYJE00 := greatest(ls_TYJE00,0); if (ls_OLDZYE-ls_TYJE00=0 and ls_ZYE000-ls_TYJE00<>0) or (ls_OLDZYE-ls_TYJE00<>0 and ls_ZYE000-ls_TYJE00=0) then select nvl(max(ICKH00),'0') into ls_ICKH00 from IC_YHXX00 where BRID00 = Vbrjfb0.BRID00 and ZT0000='1'; if ls_ICKH00 <>'0' then if ls_OLDZYE-ls_TYJE00<>0 and ls_ZYE000-ls_TYJE00=0 then --余额从非零到零，记录余额标识为0 ls_YEBZ00:= '0'; else ls_YEBZ00:= '1'; end if; update PT_HIS_YJJBG0 set YEBZ00 = ls_YEBZ00, PTREAD = '0' where HISBRID = Vbrjfb0.BRID00; if sql%notfound then insert into PT_HIS_YJJBG0(LSH000,HISBRID,KH0000,YEBZ00) values(SQ_PT_HIS_YJJBG0_LSH000.nextval,Vbrjfb0.BRID00,ls_ICKH00,ls_YEBZ00); end if; end if; end if; end if; Pzxcgbz:=1; if pcommit='Y' then commit; end if; exception when No_Data_Found then raise_application_error(-20001,dbms_utility.format_error_backtrace()||substrb(ls_ERRORS||'没有找到数据！*'||sqlerrm||Ptsxx00,1,240)); when Ecustom then raise_application_error(-20010,substrb(ls_ERRORS||Ptsxx00||'！*',1,240)); when others then raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240)); end;