CREATE OR REPLACE FUNCTION SF_SF_PAY_KTKJE0 ( PBRID00 number ,--病人ID PZFFS00 number ,--支付方式 PXTCS00 char --系统参数 ) -- MODIFICATION HISTORY -- Person Date Comments -- qks 2018.03.06 create：统一支付平台接口改造; for MZSF9-20180306-002 -- qks 2018.05.15 修正LS_TKJE00值为空,引起的判断异常; for MZSF9-20180516-001 2、 -- qks 2018.07.31 现金退款,要求“退款冲正”的微信和支付宝允许当成现金退款。 -- liwm 2018.08.22 医联的银联二维码支付支持原路退 MZSF9-20180822-001 -- liwm 2018.11.09 易慧POS原路退适配 MZSF9-20181024-002 ---liwm 2018.12.08 门诊预交金提款按交款倒序来退 MZSF9-20181123-006 -- liwm 2018.12.24 无法找到支付方式的退款用现金来退 MZSF9-20181222-003 -- --------- ---------- ------- 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_WX0000 number(12,2); --退款金额(微信) LS_ZFB000 number(12,2); --退款金额支付宝 LS_YHPOS0 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_WXZFBXJTK varchar2(100); --微信或支付宝是否允许现金退款 Y:允许 N:不允许 默认值:N LS_JHZFCS varchar2(100); --门诊或住院聚合支付交易是否原路退款以及退款有效天数(格式:是否开启,有效天数) Y:允许 N:不允许 默认:N,30 LS_JHZFBZ varchar2(10); --聚合支付原路退回标志 Y:是 N:否 LS_JHZFTS number(10); --聚合支付有效天数 LS_JHZFRQ char(8); --聚合支付开始日期 LS_YHSKCS varchar2(100); --门诊收费银行卡联机刷卡是否原路退款,有效天数(格式:是否开启,有效天数) Y:允许 N:不允许 默认:N,30 LS_YHSKBZ varchar2(10); --银行刷卡原路退回标志 Y:是 N:否 LS_YHSKTS number(10); --银行刷卡有效天数 LS_YHSKRQ char(8); --银行刷卡开始日期 LS_ZHYEKK number(12,2); --账户余额扣款变量 LS_XJTKJE number(12,2); --现金退款金额 LS_MBKTJE number(12,2); --每笔可退金额 --倒序原路退游标 cursor CUR_CYTKJE is select aa.ZFFS00,aa.ZFFSMC,aa.ZFMXBH,aa.DDLSH0,aa.JYLSH0,aa.YKTSH0,aa.DJH000,aa.JFJE00,aa.SFYLTK from ( --微信 select A.ZFFS00,A.ZFFSMC,decode(A.ZFFS00,24,nvl(B.ZFMXBH,'E'),25,nvl(B.ZFMXBH,'G'),B.ZFMXBH)ZFMXBH, A.DDLSH0,A.JYLSH0,A.YKTSH0,A.DJH000,(nvl(A.JFJE00,0)+nvl(A.TKJE00,0))as JFJE00,'Y' as SFYLTK from BM_ZFRZ00 A,SF_BRJFB0 B where 1=1 and A.DJH000=B.DJH000 and A.BRID00=B.BRID00 and A.ZXZTBZ='1' and A.JFBZ00='0' and A.MZZYBZ='0' and nvl(A.JFJE00,0)+nvl(A.TKJE00,0)>0 and nvl(A.JGBM00,'00') not in ('02')--退款交易类型 01:正常退款 02:退款冲正03:支付平台退款成功 04：支付平台退款异常 and B.ZFFS00 in (24) and A.BRID00=PBRID00 and B.JFRQ00>=LS_KSRQ00 and B.JFRQ00<=LS_JSRQ00 union all select A.ZFFS00,A.ZFFSMC,decode(A.ZFFS00,24,nvl(B.ZFMXBH,'E'),25,nvl(B.ZFMXBH,'G'),B.ZFMXBH)ZFMXBH, A.DDLSH0,A.JYLSH0,A.YKTSH0,A.DJH000,(nvl(A.JFJE00,0)+nvl(A.TKJE00,0))as JFJE00,'Y' as SFYLTK from BM_ZFRZ00 A,SF_BRJFB0 B where 1=1 and A.DJH000=B.DJH000 and A.BRID00=B.BRID00 and A.ZXZTBZ='1' and A.JFBZ00='0' and A.MZZYBZ='0' and nvl(A.JFJE00,0)+nvl(A.TKJE00,0)>0 and nvl(A.JGBM00,'00') not in ('02')--退款交易类型 01:正常退款 02:退款冲正03:支付平台退款成功 04：支付平台退款异常 and B.ZFFS00 in (25) and A.BRID00=PBRID00 and B.JFRQ00>=LS_KSRQ00 and B.JFRQ00<=LS_JSRQ00 union all --银行卡 select A.ZFFS00,A.ZFFSMC,B.ZFMXBH,A.DDLSH0,A.JYLSH0,A.YKTSH0,A.DJH000,(nvl(A.JFJE00,0)+nvl(A.TKJE00,0))as JFJE00,'Y' as SFYLTK from BM_ZFRZ00 A,SF_BRJFB0 B where 1=1 and A.DJH000=B.DJH000 and A.BRID00=B.BRID00 and A.ZXZTBZ='1' and A.JFBZ00='0' and A.MZZYBZ='0' and nvl(A.JFJE00,0)+nvl(A.TKJE00,0)>0 and nvl(A.JGBM00,'00') not in ('02')--退款交易类型 01:正常退款 02:退款冲正03:支付平台退款成功 04：支付平台退款异常 and B.ZFFS00 in (8) and A.BRID00=PBRID00 and B.JFRQ00>=LS_YHSKRQ and B.JFRQ00<=LS_JSRQ00 and B.ZFMXBH in ('K','L','M') and (LS_YHSKBZ in ('Y') and 1=1) or (LS_YHSKBZ not in ('Y') and 1=2) /*--9.0没有电子支付,这里注释掉 --union all --电子支付 select A.ZFFS00,A.ZFFSMC,B.ZFMXBH,A.DDLSH0,A.JYLSH0,A.YKTSH0,A.DJH000,(nvl(A.JFJE00,0)+nvl(A.TKJE00,0))as JFJE00,'Y' as SFYLTK from BM_ZFRZ00 A,ZY_BRJFB0 B where 1=1 and A.DJH000=B.DJH000 and A.BRID00=B.BRID00 and A.ZXZTBZ='1' and A.JFBZ00='0' and A.MZZYBZ='1' and nvl(A.JFJE00,0)+nvl(A.TKJE00,0)>0 and nvl(A.JGBM00,'00') not in ('02')--退款交易类型 01:正常退款 02:退款冲正03:支付平台退款成功 04：支付平台退款异常 and B.ZFFS00 in (28) and A.BRID00=PBRID00 and A.ZYGHID=PZYID00 and B.JZDH00=PJZDH00 and B.JFRQ00>=LS_JHZFRQ and B.JFRQ00<=LS_JSRQ00 and B.ZFMXBH in ('B') and (LS_JHZFBZ in ('Y') and 1=1) or (LS_JHZFBZ not in ('Y') and 1=2) */ union all --现金 select B.ZFFS00,B.ZFFSMC,nvl(B.ZFMXBH,'H')ZFMXBH,B.DDLSH0,null as JYLSH0,null as YKTSH0,B.DJH000,B.JFJE00 as JFJE00,'N' as SFYLTK from SF_BRJFB0 B where 1=1 and B.JFBZ00 in ('0', '1') and B.ZFFS00 in (1) and B.BRID00=PBRID00 union all --离线pos select B.ZFFS00,B.ZFFSMC,nvl(B.ZFMXBH,'J')ZFMXBH,B.DDLSH0,null as JYLSH0,null as YKTSH0,B.DJH000,B.JFJE00 as JFJE00,'N' as SFYLTK from SF_BRJFB0 B where 1=1 and B.JFBZ00 in ('0', '1') and B.ZFFS00 in (8) and nvl(B.ZFMXBH,'J') not in ('K','L','M') and B.BRID00=PBRID00 ) aa order by aa.DJH000 desc; begin LS_FHXX00:=0; LS_WX0000:=0;--退款金额(微信) LS_ZFB000:=0;--支付宝 LS_YHPOS0:=0;--退款金额(银行卡) LS_XJTKTS:=1;--现金退款时，查找微信或支付支付的有效天数 默认1 --LS_XJTKJE:=0; --门诊是否启用易惠统一支付或院内微信支和付宝支付 Y:开启易惠统一支付接口 M:开启院内微信和支付接口 N:都不开启 select SF_SF_TYZD00('支付统一平台参数','Open_MZ') 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; --微信或支付宝是否允许现金退款 Y:允许 N:不允许 默认值:N begin select upper(trim(SF_SF_TYZD00('支付统一平台参数','WXZFBXJTK_MZ'))) into LS_WXZFBXJTK from dual; exception when others then LS_WXZFBXJTK:='N'; end; LS_WXZFBXJTK:=nvl(LS_WXZFBXJTK,'N'); --门诊或住院聚合支付交易是否原路退款以及退款有效天数(格式:是否开启,有效天数) Y:允许 N:不允许 默认:N,30 begin select SF_SF_TYZD00('支付统一平台参数','POSTKCS') into LS_JHZFCS from dual; exception when others then LS_JHZFCS:='N,30'; end; begin LS_JHZFBZ:=substrb(LS_JHZFCS,1,1); --聚合支付原路退回标志 Y:是 N:否 LS_JHZFTS:=to_number(substrb(LS_JHZFCS,3,5));--聚合支付退款有效天数 exception when others then LS_JHZFBZ:='N'; LS_JHZFTS:=0; end; --门诊收费银行卡联机刷卡是否原路退款,有效天数(格式:是否开启,有效天数) Y:允许 N:不允许 默认:N,30 begin select SF_SF_TYZD00('支付统一平台参数','YHPOS_MZ') into LS_YHSKCS from dual; exception when others then LS_YHSKCS:='N,30'; end; begin LS_YHSKBZ:=substrb(LS_YHSKCS,1,1); --银行卡原路退回标志 Y:是 N:否 LS_YHSKTS:=to_number(substrb(LS_YHSKCS,3,5));--银行卡退款有效天数 exception when others then LS_YHSKBZ:='N'; LS_YHSKTS:=0; end; LS_YHSKRQ:=to_char(sysdate-LS_YHSKTS,'YYYYMMDD'); --银行刷卡有效开始日期 LS_JHZFRQ:=to_char(sysdate-LS_JHZFTS,'YYYYMMDD'); --聚合支付有效开始日期 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_SF_GETBRZHYE(0,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,8) then --健康通，还有26这两种先不管，后面有，要加 select SF_SF_GETBRZHYE(0,PBRID00,1,2) into LS_XJYE00 from dual; select SF_SF_GETBRZHYE(0,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,8,24,25) and LS_DZZFCS not in ('N') then LS_TKJE00 := 0;--现金退款金额 LS_WX0000 := 0;--退款金额(微信) LS_ZFB000 := 0;--支付宝 LS_YHPOS0 := 0;--退款金额(银行卡) --if PZFFS00 = '1' then--计算现金可退金额 LS_XJTKJE := 0; LS_MBKTJE := 0; for TK in CUR_CYTKJE loop LS_ZHYEKK := 0; LS_MBKTJE := 0; if LS_ZHYE00 > 0 then if TK.ZFFS00 = '1' or (TK.ZFFS00 = '8' and nvl(TK.ZFMXBH, 'J') not in ('K','L','M')) then --现金 LS_XJTKJE := LS_XJTKJE + TK.JFJE00; end if; if TK.JFJE00>0 then if LS_XJTKJE>0 then LS_MBKTJE := least(LS_XJTKJE,LS_ZHYE00); LS_XJTKJE := 0; elsif not (TK.ZFFS00 = '1' or (TK.ZFFS00 = '8' and nvl(TK.ZFMXBH, 'J') not in ('K','L','M'))) then LS_MBKTJE := least(TK.JFJE00,LS_ZHYE00); end if; LS_ZHYE00 := LS_ZHYE00 - LS_MBKTJE; end if; if TK.ZFFS00 = '1' or (TK.ZFFS00 = '8' and nvl(TK.ZFMXBH, 'J') not in ('K','L','M')) then --现金 LS_TKJE00 := LS_TKJE00 + LS_MBKTJE; elsif TK.ZFFS00 = '8' and nvl(TK.ZFMXBH, 'J') in ('K','L','M') then --银行卡 LS_YHPOS0 := LS_YHPOS0 + LS_MBKTJE; elsif TK.ZFFS00 = '24' then --微信 LS_WX0000 := LS_WX0000 + LS_MBKTJE; elsif TK.ZFFS00 = '25' then --支付宝 LS_ZFB000 := LS_ZFB000 + LS_MBKTJE; end if; end if; if LS_ZHYE00 <= 0 then LS_ZHYE00 :=0; exit; end if; end loop; if PZFFS00 = '1' then --现金 LS_ZHYE00 := nvl(LS_TKJE00, 0) + LS_ZHYE00; elsif PZFFS00 = '8' then --银行卡 LS_ZHYE00 := nvl(LS_YHPOS0, 0); elsif PZFFS00 = '24' then --微信 LS_ZHYE00 := nvl(LS_WX0000, 0); elsif PZFFS00 = '25' then --支付宝 LS_ZHYE00 := nvl(LS_ZFB000, 0); end if; end if; elsif PZFFS00 = '13' then --商业保险支付 LS_ZHYE00 :=0; elsif PZFFS00 in (2) then --转账退款 select SF_SF_GETBRZHYE(0,PBRID00,1,4) into LS_ZZYE00 from dual; LS_ZHYE00:=LS_ZZYE00; end if; if LS_ZHYE00<0 then LS_ZHYE00:=0; end if; LS_FHXX00:=nvl(LS_ZHYE00,0); return nvl(LS_FHXX00,0); end;