FUNCTION SF_ZY_PAY_KTKJE0 ( PZYID00 number ,--住院id PBRID00 number ,--病人ID PZFFS00 number ,--支付方式 PXTCS00 char --系统参数 ) -- MODIFICATION HISTORY -- Person Date Comments -- liwm 2018.03.06 create：统一支付平台接口改造; for ZYSF9-20180810-003 -- liwm 2018.08.17 住院统一支付退款问题解决 ZYSF9-20180817-003 -- liwm 2018.08.23 统一支付银联二维码原路退功能添加 ZYSF9-20180823-001 -- liwm 2018.12.04 退现金没有计算银行卡退款 ZYSF9-20181203-001 -- --------- ---------- ------- return number is LS_FHXX00 number(12,2); LS_XJYE00 number(12,2); LS_ZYE000 number(12,2); LS_ZZYE00 number(12,2); LS_TYJE00 number(12,2); LS_ZHYE00 number(12,2); LS_TKJE00 number(12,2); LS_DZZFCS varchar2(20); LS_TKYXTS number(5); --微信和支付退款有效天数 LS_KSRQ00 char(8); --开始日期 LS_JSRQ00 char(8); --结束日期 LS_TKKSRQ char(8); --退款开始日期 LS_XJTKTS number(5); LS_YHPOS0 number(12,2); --退款金额(银行卡) --LS_WXZFBXJTK varchar2(100);--微信或支付宝是否允许现金退款 Y:允许 N:不允许 默认值:N begin LS_FHXX00:=0; LS_XJTKTS:=1;--现金退款时，查找微信或支付支付的有效天数 默认1 --门诊是否启用易惠统一支付或院内微信支和付宝支付 Y:开启易惠统一支付接口 M:开启院内微信和支付接口 N:都不开启 select SF_SF_TYZD00('支付统一平台参数','Open_ZY') into LS_DZZFCS from dual; if trim(PXTCS00) is not null then LS_DZZFCS:=PXTCS00; end if; --微信支付宝退款有效天数 0:当天 begin select to_number(SF_SF_TYZD00('支付统一平台参数','TKYXTS')) into LS_TKYXTS from dual; exception when others then LS_TKYXTS:=0; end; LS_KSRQ00:=to_char(sysdate-LS_TKYXTS,'YYYYMMDD'); LS_JSRQ00:=to_char(sysdate,'YYYYMMDD'); if LS_XJTKTS>LS_TKYXTS then LS_XJTKTS:=LS_TKYXTS; else LS_XJTKTS:=LS_XJTKTS; end if; LS_TKKSRQ:=to_char(sysdate-LS_XJTKTS,'YYYYMMDD'); --可用余额 select SF_ZY_KYJE00(PZYID00,PBRID00,1,0) into LS_ZHYE00 from dual; --0 门诊 1住院 /BRID00 /交费类型 1：门诊 2:住院/返回类型 0:可用余额 1:总余额 2：现金余额 3:停用金额 4:转账余额 if PZFFS00 in (1,18,24,25,26) then select XJYE00,TYJE00 into LS_XJYE00, LS_TYJE00 from ZY_BRZHXX where ZYID00 = PZYID00; --select SF_ZY_KYJE00(PZYID00,PBRID00,1,3) into LS_TYJE00 from dual; if nvl(LS_TYJE00,0)<0 then LS_ZHYE00:=nvl(LS_XJYE00,0); else LS_ZHYE00:=nvl(LS_XJYE00,0)-nvl(LS_TYJE00,0); end if; if LS_ZHYE00<0 then LS_ZHYE00:=0; end if; --查找微信和支付支付金额 if PZFFS00 in (1) and LS_DZZFCS not in ('N') then select nvl(sum(nvl(JFJE00,0)+nvl(TKJE00,0)),0) into LS_TKJE00 from BM_ZFRZ00 A where BRID00=PBRID00 and ZFFS00 in (24,25,8) and ZXZTBZ='1' and JFBZ00='0' and nvl(JFJE00,0)+nvl(TKJE00,0)>0 and nvl(JGBM00,'00') not in ('02')--退款交易类型 01:正常退款 02:退款冲正03:支付平台退款成功 04：支付平台退款异常 and exists(select 1 from ZY_BRJFB0 B where B.ZYID00=PZYID00 and B.DJH000=A.DJH000 and ( B.ZFFS00 in (24,25) or (B.ZFFS00 = '8' and nvl(B.ZFMXBH, 'J') in ('K','L','M','V') )) and B.JFRQ00>=LS_TKKSRQ and B.JFRQ00<=LS_JSRQ00 and B.JSLX00<>'3'); if LS_ZHYE00-LS_TKJE00>=0 then LS_ZHYE00:=LS_ZHYE00-LS_TKJE00; elsif LS_TKJE00>0 then --LS_ZHYE00:=0; select nvl(sum(nvl(JFJE00,0)+nvl(TKJE00,0)),0) into LS_TKJE00 from BM_ZFRZ00 A where BRID00=PBRID00 and ZFFS00 in (24,25,8) and ZXZTBZ='1' and JFBZ00='0' and nvl(JFJE00,0)+nvl(TKJE00,0)>0 and nvl(JGBM00,'00') in ('02')--退款交易类型 01:正常退款 02:退款冲正03:支付平台退款成功 04：支付平台退款异常 and exists(select 1 from ZY_BRJFB0 B where B.ZYID00=PZYID00 and B.DJH000=A.DJH000 and (B.ZFFS00 in (24,25) or (B.ZFFS00 = '8' and nvl(B.ZFMXBH, 'J') in ('K','L','M','V') )) and B.JFRQ00>=LS_TKKSRQ and B.JFRQ00<=LS_JSRQ00 and B.JSLX00<>'3'); if LS_TKJE00>0 then LS_ZHYE00:=least(LS_ZHYE00,LS_TKJE00); else LS_ZHYE00:=0; end if; else LS_ZHYE00:=0; end if; end if; if PZFFS00 in (24,25) then if LS_DZZFCS not in ('N') then select nvl(sum(nvl(JFJE00,0)+nvl(TKJE00,0)),0) into LS_TKJE00 from BM_ZFRZ00 A where BRID00=PBRID00 and ZFFS00=PZFFS00 and ZXZTBZ='1' and JFBZ00='0' and nvl(JFJE00,0)+nvl(TKJE00,0)>0 and nvl(JGBM00,'00') not in ('02')--退款交易类型 01:正常退款 02:退款冲正03:支付平台退款成功 04：支付平台退款异常 and exists(select 1 from ZY_BRJFB0 B where B.ZYID00=PZYID00 and B.DJH000=A.DJH000 and B.ZFFS00=PZFFS00 and B.JFRQ00>=LS_KSRQ00 and B.JFRQ00<=LS_JSRQ00 and B.JSLX00<>'3'); if nvl(LS_TKJE00,0)<0 then LS_TKJE00:=0; end if; if nvl(LS_TKJE00,0)>nvl(LS_ZHYE00,0) then LS_ZHYE00:=nvl(LS_ZHYE00,0); else LS_ZHYE00:=nvl(LS_TKJE00,0); end if; else LS_ZHYE00:=0; end if; end if; elsif PZFFS00 in (2) then select ZZYE00 into LS_ZZYE00 from ZY_BRZHXX where ZYID00 = PZYID00; LS_ZHYE00:=LS_ZZYE00; elsif PZFFS00 in (8) then select XJYE00 into LS_XJYE00 from ZY_BRZHXX where ZYID00 = PZYID00; select TYJE00 into LS_TYJE00 from ZY_BRZHXX where ZYID00 = PZYID00; if nvl(LS_TYJE00,0)<0 then LS_ZHYE00:=nvl(LS_XJYE00,0); else LS_ZHYE00:=nvl(LS_XJYE00,0)-nvl(LS_TYJE00,0); end if; if LS_ZHYE00<0 then LS_ZHYE00:=0; end if; --银行卡刷卡 --if LS_WXZFBXJTK not in ('Y') then select sum(nvl(JFJE00,0)+nvl(TKJE00,0)) into LS_YHPOS0 from BM_ZFRZ00 A where BRID00=PBRID00 and ZFFS00 in (8) and ZXZTBZ='1' and JFBZ00='0' and nvl(JFJE00,0)+nvl(TKJE00,0)>0 and nvl(JGBM00,'00') not in ('02')--退款交易类型 01:正常退款 02:退款冲正03:支付平台退款成功 04：支付平台退款异常 and exists(select 1 from ZY_BRJFB0 B where B.ZYID00=PZYID00 and B.DJH000=A.DJH000 and B.ZFFS00 in (8) and B.JFRQ00>=to_char(sysdate-30,'YYYYMMDD') and B.JFRQ00<=LS_JSRQ00 --支付明细编码H:现金I:转账J:POS离线K:POS刷卡L:龙支付M:银联二维码D:微信离线E:微信扫码F:支付宝离线G:支付宝扫码A:榕医通B:聚合支付C:健康通N:银医通P:一卡通 and B.ZFMXBH in ('K','L','M','V') ); if nvl(LS_YHPOS0,0)<0 then LS_YHPOS0:=0; end if; if nvl(LS_YHPOS0,0)>nvl(LS_ZHYE00,0) then LS_ZHYE00:=nvl(LS_ZHYE00,0); else LS_ZHYE00:=nvl(LS_YHPOS0,0); end if; else LS_ZHYE00:=0; end if; --end if; if LS_ZHYE00<0 then LS_ZHYE00:=0; end if; LS_FHXX00:=nvl(LS_ZHYE00,0); return nvl(LS_FHXX00,0); end;