prompt 移动支付退号和HIS退号以及门诊退费的原路退款到账过程 SD_SF.SF_YDZFLS_TFDZ CREATE OR REPLACE procedure SD_HOSPITAL.SP_SF_YDZFLS_TFDZ ( as_GHH000 in char, --挂号号 ad_JZDH00 in number, --结账单号 ad_CZY000 in number, --退费操作员 as_FYFL00 in char, --费用类型：0 挂号费(挂号费+诊查费) 1 门诊费用 as_ZFLX00 in char, --退回账号类型: 0-支付宝 1-微信 2-银联支付 ad_ZJE000 in number default 0, --HIS退费金额 as_CZBZ00 in char default '0', --操作标志：0-生成退费记录,1-同步第三方退款到账流水记录 as_ZFZH00 in varchar2 default '', --支付账号 as_JYLSH0 in varchar2 default '', --交易流水号 as_DZRQ00 in char default '', --退款到账日期 as_DZSJ00 in char default '', --退款到账时间 ad_DZJE00 in number default 0, --退款到账总金额 as_USEMSG out varchar2, as_SYSMSG out varchar2 ) as ls_count0 number(5); --计数器1 ls_count1 number(5); --计数器2 ls_count2 number(5); --计数器3 ls_CZRQ00 char(8); --系统日期 ls_CZSJ00 char(8); --系统时间 ls_TFLX00 char(1); --退费类型: 0-院内HIS(线下)主动退费,1-线上(微信)主动退费 ls_ZFLX00 char(1); --退回账号类型: 0-支付宝 1-微信 2-银联支付 lv_JZDH00 number(10); --HIS结账单号 lv_ZJE000 number(14,2); --HIS退费金额 lv_DZJE00 number(14,2); --退款到账金额 lv_SFTKDZ number(1); --是否退款到账成功：0-未退款到账, 1-已退款到账 Ecustom_1 EXCEPTION; --自定义错误1 Ecustom_2 EXCEPTION; --自定义错误2 -- MODIFICATION HISTORY -- Person Date Comments -- gaoyj 2016.05.14 Create by 记录线上(微信)、线下(院内HIS系统)的挂号/门诊费用的退费退款原路退回的到账记录 -- fcx 2016.06.07 增加处理线上(微信、支付宝)退号退款到账时并更新表SF_YDZFLS中JYLSH0字段值 -- ------ ------------- --------------------------------------------------- begin --确定退费类型 （退费类型: 0-线下主动退费,1-线上主动退费 ） if as_CZBZ00='0' then select count(*) into ls_count0 from BM_YGBM00 where YGBH00=ad_CZY000 and (ZWXM00 like '%微信%' or ZWXM00 like '%支付宝%' or ZWXM00 like '%银联%'); if ls_count0>0 then ls_TFLX00 := '1'; else ls_TFLX00 := '0'; end if; end if; --验证数据合法性 if as_CZBZ00='0' then --挂号号不允许为空 if trim(as_GHH000)='' then as_SYSMSG:='结账单号（GHH000）入参不能为空！'; Raise Ecustom_1; end if; --结账单号不允许为空 if nvl(ad_JZDH00,0)=0 then as_SYSMSG:='结账单号（JZDH00）入参不能为空！'; Raise Ecustom_1; end if; --退费金额不允许为空 if nvl(ad_ZJE000,0)=0 then as_SYSMSG:='退费金额（ZJE000）入参不能为空！'; Raise Ecustom_1; elsif ad_ZJE000>0 then lv_ZJE000 := -ad_ZJE000; elsif ad_ZJE000<0 then lv_ZJE000 := ad_ZJE000; end if; elsif as_CZBZ00='1' then --挂号号不允许为空 if trim(as_GHH000)='' then as_SYSMSG:='结账单号（GHH000）入参不能为空！'; Raise Ecustom_2; end if; --结账单号不允许为空 if nvl(ad_JZDH00,0)=0 then as_SYSMSG:='结账单号（JZDH00）入参不能为空！'; Raise Ecustom_2; end if; --支付账号不允许为空 if trim(as_ZFZH00)='' then as_SYSMSG:='支付账号（ZFZH00）入参不能为空！'; Raise Ecustom_2; end if; --交易流水号不允许为空 if trim(as_JYLSH0)='' then as_SYSMSG:='交易流水号（JYLSH0）入参不能为空！'; Raise Ecustom_2; end if; --到账日期、时间不允许为空 if trim(as_DZRQ00)='' or trim(as_DZSJ00)='' then as_SYSMSG:='到账日期（DZRQ00）、到账时间（DZSJ00）入参不能为空！'; Raise Ecustom_2; end if; --退款到账金额不允许为空 if nvl(ad_DZJE00,0)=0 then as_SYSMSG:='退款到账金额（DZJE00）入参不能为空！'; Raise Ecustom_2; elsif ad_DZJE00>0 then lv_DZJE00 := -ad_DZJE00; elsif ad_DZJE00<0 then lv_DZJE00 := ad_DZJE00; end if; end if; select to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') into ls_CZRQ00,ls_CZSJ00 from dual; ls_ZFLX00 := trim(as_ZFLX00); --*************************************************** --线上(微信)退号、院内退号与门诊退费操作时生成退费记录 --ZT0000(状态)：0-HIS退费, 1-退款并已到账 if as_CZBZ00='0' then --验证退回账号类型 （0-支付宝 1-微信 2-银联支付 ） if nvl(ls_ZFLX00,'N')='N' then select count(1) into ls_count0 from SF_YDZFLS where JZDH00=ad_JZDH00 and ZJE000>0; if ls_count0>0 then select decode(CZY000,'支付宝','0',2753,'银联','2','1','3') into ls_ZFLX00 from SF_YDZFLS where JZDH00=ad_JZDH00 and ZJE000>0 and rownum=1; else ls_ZFLX00 := '3'; end if; end if; select count(1) into ls_count1 from SF_YDZFLS_TFDZ where GHH000=as_GHH000 and FYFL00=as_FYFL00 and JZDH00=ad_JZDH00 and ZFLX00=ls_ZFLX00 and ZT0000='0'; if ls_count1<=0 then insert into SF_YDZFLS_TFDZ(LSH000,GHH000,JZDH00,FYFL00,TFLX00,CZRQ00,CZSJ00,CZY000,ZJE000,ZFLX00,ZT0000) values(SQ_SF_YDZFLS_LSH000.NextVal,as_GHH000,ad_JZDH00,as_FYFL00,ls_TFLX00,ls_CZRQ00,ls_CZSJ00,ad_CZY000,lv_ZJE000,ls_ZFLX00,'0'); /* insert into SF_YDZFLS_TFDZ(LSH000,GHH000,JZDH00,FYFL00,TFLX00,CZRQ00,CZSJ00,CZY000,ZJE000,ZFLX00,ZFZH00,JYLSH0,DZRQ00,DZSJ00,DZJE00,ZT0000) values(SQ_SF_YDZFLS_LSH000.NextVal,as_GHH000,ad_JZDH00,as_FYFL00,ls_TFLX00,ls_CZRQ00,ls_CZSJ00,ad_CZY000, lv_ZJE000,ls_ZFLX00,trim(as_ZFZH00),trim(as_JYLSH0),trim(as_DZRQ00),trim(as_DZSJ00),ad_DZJE00,'0'); */ else update SF_YDZFLS_TFDZ set TFLX00=ls_TFLX00,ZJE000=lv_ZJE000,CZRQ00=ls_CZRQ00,CZSJ00=ls_CZSJ00,CZY000=ad_CZY000, ZFZH00=decode(nvl(trim(as_ZFZH00),'0'),'0',ZFZH00,trim(as_ZFZH00)), JYLSH0=decode(nvl(trim(as_JYLSH0),'0'),'0',JYLSH0,trim(as_JYLSH0)), DZRQ00=decode(nvl(trim(as_DZRQ00),'0'),'0',DZRQ00,trim(as_DZRQ00)), DZSJ00=decode(nvl(trim(as_DZSJ00),'0'),'0',DZSJ00,trim(as_DZSJ00)), DZJE00=decode(nvl(ad_DZJE00,0),0,DZJE00,lv_DZJE00) where GHH000=as_GHH000 and FYFL00=as_FYFL00 and JZDH00=ad_JZDH00 and ZFLX00=ls_ZFLX00 and ZT0000='0'; end if; --*************************************************** --线上退号、线下退号与门诊退费操作时生成退费记录 --ZT0000(状态)：0-HIS退费, 1-退款并已到账 elsif as_CZBZ00='1' then --验证退回账号类型 （0-支付宝 1-微信 2-银联支付 ） if nvl(ls_ZFLX00,'N')='N' then select count(1) into ls_count0 from SF_YDZFLS where JYLSH0=trim(as_JYLSH0) and ZJE000>0; if ls_count0>0 then select decode(CZY000,'支付宝','0',2753,'银联','2','1','3') into ls_ZFLX00 from SF_YDZFLS where JYLSH0=trim(as_JYLSH0) and ZJE000>0 and rownum=1; else ls_ZFLX00 := '3'; end if; end if; --验证挂号号是否已在HIS系统中退号成功 if as_FYFL00='0' then select count(1) into ls_count0 from SF_BRXXB0 where GHH000=as_GHH000 and THBZ00='0'; if ls_count0<=0 then as_SYSMSG:='病人该次就诊的挂号号:'||as_GHH000||' 在HIS系统中不存在退号记录，请重新核对该挂号号是否正确！'; Raise Ecustom_2; end if; end if; --验证结账单号是否已在HIS系统中退费,同时验证退款到账金额是否相等 lv_ZJE000 := 0; select count(1) into ls_count0 from SF_BRFY00 where JZDH00=ad_JZDH00 and HJJE00<0; if ls_count0<=0 then as_SYSMSG:='该笔订单的结账单号:'||ad_JZDH00||' 在HIS系统中不存在退费记录，请重新核对该结账单号是否正确！'; Raise Ecustom_2; else select nvl(ZJE000,0) into lv_ZJE000 from SF_YDZFLS_TFDZ where JZDH00=ad_JZDH00 and FYFL00=as_FYFL00 and ZFLX00=ls_ZFLX00 and rownum=1; if round(lv_DZJE00,2)<>round(lv_ZJE000,2) then as_SYSMSG:='该交易流水号:'||as_JYLSH0||' 订单的退款到账金额:'||lv_DZJE00||' 与HIS退费金额:'||lv_ZJE000||' 不相符，请重新核对该退款到账金额是否正确！'; Raise Ecustom_2; end if; end if; --验证交易流水号是否重复使用 select count(1) into ls_count0 from SF_YDZFLS_TFDZ where JYLSH0=trim(as_JYLSH0) and ZT0000='1'; if ls_count0>0 then select nvl(JZDH00,0) into lv_JZDH00 from SF_YDZFLS_TFDZ where JYLSH0=trim(as_JYLSH0) and ZT0000='1'; if ad_JZDH00=lv_JZDH00 then as_SYSMSG:='该交易流水号:'||as_JYLSH0||' 的订单已退款到账了，不允许重复执行退款到账操作！'; Raise Ecustom_2; else as_SYSMSG:='该交易流水号:'||as_JYLSH0||' 重复使用，请重新核对交易流水号是否正确！'; Raise Ecustom_2; end if; end if; --********************** --根据结账单号执行退款到账操作 if nvl(ad_JZDH00,0)<>0 then select count(1) into ls_count1 from SF_YDZFLS_TFDZ where JZDH00=ad_JZDH00 --and GHH000=as_GHH000 and FYFL00=as_FYFL00 and ZFLX00=ls_ZFLX00 and ZT0000='0'; if ls_count1>0 then --退费并退款已到账的情况 update SF_YDZFLS_TFDZ set ZFZH00=decode(nvl(trim(as_ZFZH00),'0'),'0',ZFZH00,trim(as_ZFZH00)), JYLSH0=decode(nvl(trim(as_JYLSH0),'0'),'0',JYLSH0,trim(as_JYLSH0)), DZRQ00=decode(nvl(trim(as_DZRQ00),'0'),'0',DZRQ00,trim(as_DZRQ00)), DZSJ00=decode(nvl(trim(as_DZSJ00),'0'),'0',DZSJ00,trim(as_DZSJ00)), DZJE00=decode(nvl(ad_DZJE00,0),0,DZJE00,lv_DZJE00),ZT0000='1' where JZDH00=ad_JZDH00 and FYFL00=as_FYFL00 and ZFLX00=ls_ZFLX00 and ZT0000='0'; --and GHH000=as_GHH000 lv_SFTKDZ := 1; --已退款到账 else --退费但未退款到账的情况 select count(1) into ls_count1 from SF_YDZFLS_TFDZ where JZDH00=ad_JZDH00 and FYFL00=as_FYFL00 and ZFLX00=ls_ZFLX00 and ZT0000='1'; --and GHH000=as_GHH000 if ls_count1>0 then lv_SFTKDZ := 1; --已退款到账 as_SYSMSG:='该笔交易订单的结账单号:'||ad_JZDH00||' 已退款到账了，不允许重复退款到账操作！'; Raise Ecustom_2; else lv_SFTKDZ := 0; --未退款到账 --as_SYSMSG:='该笔订单的挂号号: '||as_GHH000||' ,结账单号:'||ad_JZDH00||' 两者不相匹配或在HIS系统中不存在退费操作，请联系HIS工程师咨询处理！'; --Raise Ecustom_2; end if; end if; end if; --********************** --补充操作：根据移动支付的交易流水号执行退款到账 if nvl(trim(as_JYLSH0),'N')<>'N' and lv_SFTKDZ=0 then select count(1) into ls_count1 from SF_YDZFLS_TFDZ where JYLSH0=trim(as_JYLSH0) and FYFL00=as_FYFL00 and ZFLX00=ls_ZFLX00 and ZT0000='0'; if ls_count1>0 then --退费并退款已到账的情况 update SF_YDZFLS_TFDZ set ZFZH00=decode(nvl(trim(as_ZFZH00),'0'),'0',ZFZH00,trim(as_ZFZH00)), JYLSH0=decode(nvl(trim(as_JYLSH0),'0'),'0',JYLSH0,trim(as_JYLSH0)), DZRQ00=decode(nvl(trim(as_DZRQ00),'0'),'0',DZRQ00,trim(as_DZRQ00)), DZSJ00=decode(nvl(trim(as_DZSJ00),'0'),'0',DZSJ00,trim(as_DZSJ00)), DZJE00=decode(nvl(ad_DZJE00,0),0,DZJE00,lv_DZJE00),ZT0000='1' where JYLSH0=trim(as_JYLSH0) and FYFL00=as_FYFL00 and ZFLX00=ls_ZFLX00 and ZT0000='0'; else --退费但未退款到账的情况 select count(1) into ls_count1 from SF_YDZFLS_TFDZ where JYLSH0=trim(as_JYLSH0) and FYFL00=as_FYFL00 and ZFLX00=ls_ZFLX00 and ZT0000='1'; if ls_count1>0 then as_SYSMSG:='该笔订单的交易流水号:'||trim(as_JYLSH0)||' 已退款到账了，不允许重复退款到账操作！'; Raise Ecustom_2; else as_SYSMSG:='该笔订单的挂号号: '||as_GHH000||' ,结账单号:'||ad_JZDH00||' 两者不相匹配或在HIS系统中不存在退费操作，请联系HIS工程师咨询处理！'; Raise Ecustom_2; end if; end if; end if; --********************** --增加处理线上(微信、支付宝)退号退款到账时并更新表SF_YDZFLS中JYLSH0字段值 2016.06.07 select count(1) into ls_count2 from SF_YDZFLS where ZFZH00=as_ZFZH00 and ZFLX00=ls_ZFLX00 and JZDH00=ad_JZDH00; if ls_count2>0 then update SF_YDZFLS set JYLSH0=decode(nvl(trim(JYLSH0),'N'),'0',trim(as_JYLSH0),'1',trim(as_JYLSH0),'N',trim(as_JYLSH0),JYLSH0) where ZFZH00=as_ZFZH00 and ZFLX00=ls_ZFLX00 and JZDH00=ad_JZDH00; end if; end if; exception when Ecustom_1 then as_USEMSG:='移动支付HIS退费调用"SP_SF_YDZFLS_TFDZ"过程出错！'; --as_SYSMSG:='挂号号:'||as_GHH000||',JZDH00:'||ad_JZDH00||',ZFZH00:'||trim(as_ZFZH00)||' '||',ad_DZJE00:'||ad_DZJE00||substr(SQLERRM,1,250); rollback; return; when Ecustom_2 then as_USEMSG:='移动支付线上退款到账调用"SP_SF_YDZFLS_TFDZ"过程出错！'; --as_SYSMSG:='挂号号:'||as_GHH000||',JZDH00:'||ad_JZDH00||',ZJE000:'||lv_ZJE000||' '||',ad_DZJE00:'||ad_DZJE00||substr(SQLERRM,1,250); rollback; return; when others then as_USEMSG:='调用"SP_SF_YDZFLS_TFDZ"过程操作出错！'; as_SYSMSG:=SQLERRM; rollback; return; end; /