create or replace procedure SP_ZY_PAY_TKDATA ( PBRID00 in number , --BRID00 PZYID00 in number , --ZYID00 PZYH000 in varchar2 , --住院号 PCZBZ00 in varchar2 , --结算类型 0：中途结算,1:出院结算 2:重新结算 11:床边出院结算 12:取消入院登记 PCXLX00 in varchar2 , --查询类型 0:应补交 1：应退款 2：取消结算补收 3：重新结算补收 PJZDH00 in number , --结账单号 PXJDJH0 in number , --结算退现金单号 PZZDJH0 in number , --结算退转账单号 PQTCS00 in varchar2 , --其他参数 PCXLSH0 out varchar2 , --退款查询流水号 PBCFYZE out number , --本次费用总额 PYBBCJC out number , --本次医保补偿金额 PBCZFJE out number , --本次自付金额 PYJJZE0 out number , --预交金总额 PYTZJE0 out number , --应退总额 PTKXJ00 out number , --可退款现金 PTKZZ00 out number , --可退款转账 PTKPOS0 out number , --可退款POS PTKWX00 out number , --可退款微信 PTKZFB0 out number , --可退款支付宝 PTKDZZF out number , --可退款电子支付(聚合支付) --同步HIS7.0出参 PTKQTJE out number , --可退款其它 --同步HIS7.0出参 PTKJLS0 out number , --退款记录数 PTKZJE0 out number , --退款总金额 PERRSTR out varchar2 --错误信息 ) -- MODIFICATION HISTORY -- Person Date Comments -- jlg 2018.08.07 5.0同名过程移植 ZYSF9-20180807-002 -- jlg 2018.08.13 将其中一些查询条件由BRID00改为ZYID00 ZYSF9-20180813-001 -- liwm 2018.08.24 统一支付住院结算退款计算过程修改 ZYSF9-20180824-001 -- zhangyc 2018.08.24 修改退款金额 ZYSF9-20180824-002 -- liwm 2018.11.28 易慧统一支付接口相关：按医院要去，统一支付退款时，需要根据原缴费时间顺序倒序做原路退回，望修改 ZYSF9-20181123-002 -- liwm 2018.12.26 融医通和离线POS退现金 ZYSF9-20181224-002 -- liwm 2019.06.27 省老年要求微信支付宝优先 ZYSF9-20190626-003 -- jlg 2020.06.21 同步HIS7.0，增加PTKDZZF,PTKQTJE出参 ZYSF9-20200619-001 -- jlg 2020.10.23 ZFMXBH增加'V'（云闪付）的处理 ZYSF9-20201022-006 -- zhangyc 2020.11.12 增加银行卡以及电子支付退款判断 by ZYSF9-20201113-001 -- zhangyc 2021.01.21 根据参数WDFXJTK控制某些缴费员工编码不允许退微信或支付宝 by ZYSF9-20210111-003 -- linzetao 2021.03.24 增加取消入院登记退款处理 ZYSF9-20210304-004 -- linzetao 2021.10.27 处理滚存可原路退交款 ZYSF9-20210906-001 -- dsm 2022.03.31 CUR_CYTKJE_SLN游标增加倒序排序 for ZYSF9EX-20220330-004 -- dsm 2022.04.09 ZFFS00=28的JX,JB的ZFMXBH要可以原路退LS_Refund_ZFMXBH ZYSF9EX-20220410-001 -- dsm 2022.04.26 LS_Refund_ZFMXBH没有获取参数值 ZYSF9EX-20220426-006 as LS_BCFYZE number(12,2); --本次费用总额 LS_YBBCJC number(12,2); --本次医保补偿金额 LS_BCZFJE number(12,2); --本次自付金额 LS_YJJZE0 number(12,2); --预交金总额 LS_YTZJE0 number(12,2); --应退总额 LS_TKXJ00 number(12,2); --可退款现金 LS_TKZZ00 number(12,2); --可退款转账 LS_TKPOS0 number(12,2); --可退款POS LS_TKWX00 number(12,2); --可退款微信 LS_TKZFB0 number(12,2); --可退款支付宝 LS_TKYHK0 number(12,2); --可退款银行卡 LS_TKJLS0 number(12,2); --退款记录数 LS_TKZJE0 number(12,2); --退款总金额 LS_TKQTJE number(12,2); --退款其它金额 LS_TKDZZF number(12,2); --可退款电子支付(聚合支付) LS_TKXJ01 number(12,2); LS_BRZJE0 number(12,2); LS_HJJE00 number(12,2); LS_GRZHZF number(12,2); LS_TCJJZF number(12,2); LS_ZFJE00 number(12,2); LS_YBYL05 number(12,2); LS_YSZJE0 number(12,2); LS_ZZZJE0 number(12,2); LS_XJYE00 number(12,2); LS_ZZYE00 number(12,2); LS_TKJE00 number(12,2); LS_YKTKTE_TEMP number(12,2); LS_WXZFBYHK number(12,2); --可退款微信.支付宝.银行卡 LS_KSRQ00 char(8); --开始日期 LS_JSRQ00 char(8); --结束日期 LS_TKYXTS number(5); --微信和支付退款有效天数 LS_PJH000 ZY_JZB000.PJH000%type; LS_ZFQDBM PAY_YJJ_MXCX.ZFQDBM%type; LS_MZCJBM BM_ZFFSMX.MZCJBM%type; LS_ZYCJBM BM_ZFFSMX.ZYCJBM%type; LS_YHKTS0 number(10); --银行卡退款有效天数 LS_XJKTJE_TEMP number(12,2); --出院结算/重新结算现金可退金额 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_YLTKJE number(12,2); --原路退款金额 LS_XJTKJE number(12,2); --现金退款金额 LS_YHTKJE number(12,2); --银行退款金额 LS_ZY_CYJSTKSFBDX XT_XTCS00.VALUE0%type; LS_Open_ZY varchar(100); --Y:易惠公司 M:医信公司 P:联空网络 R:健康之路当面付 Q:医联支付 LS_QTXX00 PAY_YJJ_MXCX.QTXX00%type; --其它信息 LS_BILLSN PAY_YJJ_MXCX.BILLSN%type; --查询内部ID=PAY_YJJ_MXCX.BILLSN (生成唯一值) LS_WDFXJTK varchar2(100); --统一支付微信或支付宝充值不允许窗口退款缴费员工编号=BM_YGMB00.YGBH00参数维护多个用,分隔(如:1,2) 默认为:N LS_ZTJSYLTK BM_TYZD00.MC0000%type; --中途结算滚存是否启用原路退款 LS_Refund_ZFMXBH varchar2(100);--门诊和住院统一支付可退款支付明细编码(BM_ZFFSMX.ZFMXBH)多值用|分隔 --出院结算退款中间表汇总 cursor CUR_TKHZJE is select ZFFS00,abs(sum(TKJE00))TKJE00 from PAY_YJJ_MXCX where CXLSH0=PCXLSH0 and BRID00=PBRID00 group by ZFFS00; --退款顺序优先微信支付宝，银行卡，现金 cursor CUR_CYTKJE_SLN is --微信和支付宝 select PXXH00,ZFFS00,ZFFSMC,ZFMXBH,DDLSH0,JYLSH0,YKTSH0,DJH000,JFJE00,SFYLTK,QTXX00,ZFQDBM from ( select 1 PXXH00,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, null as QTXX00,A.ZFQDBM 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 (24,25) and A.BRID00=PBRID00 and A.ZYGHID=PZYID00 and B.JZDH00=PJZDH00 and B.JFRQ00>=LS_KSRQ00 and B.JFRQ00<=LS_JSRQ00 and (LS_WDFXJTK = 'N' or instrb(','||LS_WDFXJTK||',',','||to_char(A.CZY000)||',')<=0) union all --滚存微信和支付宝 select 1 PXXH00,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,null as QTXX00,A.ZFQDBM from BM_ZFRZ00 A,ZY_JSZFMX B where 1=1 and A.DJH000=B.JFDJH0 and A.BRID00=B.BRID00 and A.ZYGHID=B.ZYID00 and A.DDLSH0=B.DDLSH0 and A.ZXZTBZ='1' and A.JFBZ00='0' and A.MZZYBZ='1' and B.YXBZ00='1' and B.CZBZ00 in ('7','8','9') 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,25) and A.BRID00=PBRID00 and A.ZYGHID=PZYID00 and B.CZRQ00>=LS_KSRQ00 and B.CZRQ00<=LS_JSRQ00 and (LS_WDFXJTK = 'N' or instrb(','||LS_WDFXJTK||',',','||to_char(A.CZY000)||',')<=0) and LS_ZTJSYLTK = '1' --启用中途结算原路退款 union all --银行卡 select 3 PXXH00, 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,trim(B.ZPDWMC) as QTXX00,A.ZFQDBM 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 (8) and A.BRID00=PBRID00 and A.ZYGHID=PZYID00 and B.JZDH00=PJZDH00 and B.JFRQ00>=LS_YHSKRQ and B.JFRQ00<=LS_JSRQ00 and B.ZFMXBH in ('K','L','M','U','V') and ((LS_YHSKBZ in ('Y') and 1=1) or (LS_YHSKBZ not in ('Y') and 1=2)) and (LS_WDFXJTK = 'N' or instrb(','||LS_WDFXJTK||',',','||to_char(A.CZY000)||',')<=0) union all --滚存银行卡 select 3 PXXH00,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, (select trim(ZPDWMC) from ZY_BRJFB0 where ZYID00=B.ZYID00 and DJH000=B.JFDJH0 and rownum=1) as QTXX00, A.ZFQDBM from BM_ZFRZ00 A,ZY_JSZFMX B where 1=1 and A.DJH000=B.JFDJH0 and A.BRID00=B.BRID00 and A.DDLSH0=B.DDLSH0 and A.ZYGHID=B.ZYID00 and A.ZXZTBZ='1' and A.JFBZ00='0' and A.MZZYBZ='1' and B.YXBZ00='1' and B.CZBZ00 in ('7','8','9') 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 A.ZYGHID=PZYID00 and B.CZRQ00>=LS_YHSKRQ and B.CZRQ00<=LS_JSRQ00 and B.ZFMXBH in ('K','L','M','U','V') and ((LS_YHSKBZ in ('Y') and 1=1) or (LS_YHSKBZ not in ('Y') and 1=2)) and (LS_WDFXJTK = 'N' or instrb(','||LS_WDFXJTK||',',','||to_char(A.CZY000)||',')<=0) and LS_ZTJSYLTK = '1' --启用中途结算原路退款 union all --电子支付 select 1 PXXH00,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,null as QTXX00,A.ZFQDBM 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 (A.ZFMXBH in ('B') or instrb('|'||LS_Refund_ZFMXBH||'|','|'||A.ZFMXBH||'|')>0) and ((LS_JHZFBZ in ('Y') and 1=1) or (LS_JHZFBZ not in ('Y') and 1=2)) and (LS_WDFXJTK = 'N' or instrb(','||LS_WDFXJTK||',',','||to_char(A.CZY000)||',')<=0) union all --滚存电子支付 select 1 PXXH00,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,null as QTXX00,A.ZFQDBM from BM_ZFRZ00 A,ZY_JSZFMX B where 1=1 and A.DJH000=B.JFDJH0 and A.BRID00=B.BRID00 and A.ZYGHID=B.ZYID00 and A.DDLSH0=B.DDLSH0 and A.ZXZTBZ='1' and A.JFBZ00='0' and A.MZZYBZ='1' and B.YXBZ00='1' and B.CZBZ00 in ('7','8','9') 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.CZRQ00>=LS_JHZFRQ and B.CZRQ00<=LS_JSRQ00 and (A.ZFMXBH in ('B') or instrb('|'||LS_Refund_ZFMXBH||'|','|'||A.ZFMXBH||'|')>0) and ((LS_JHZFBZ in ('Y') and 1=1) or (LS_JHZFBZ not in ('Y') and 1=2)) and (LS_WDFXJTK = 'N' or instrb(','||LS_WDFXJTK||',',','||to_char(A.CZY000)||',')<=0) and LS_ZTJSYLTK = '1' --启用中途结算原路退款 union all --现金 select 5 PXXH00,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,null as QTXX00,'' as ZFQDBM from ZY_BRJFB0 B where 1=1 and B.JFBZ00='0' and B.ZFFS00 in (1) and B.BRID00=PBRID00 and B.ZYID00=PZYID00 and B.JZDH00=PJZDH00 union all --离线pos select 5 PXXH00,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,null as QTXX00,'' as ZFQDBM from ZY_BRJFB0 B where 1=1 and B.JFBZ00='0' and B.ZFFS00 in (8) and nvl(B.ZFMXBH,'J') not in ('K','L','M','U','V') and B.BRID00=PBRID00 and B.ZYID00=PZYID00 and B.JZDH00=PJZDH00 ) order by PXXH00,DJH000 desc ; --可退款交款记录倒序优先 cursor CUR_CYTKJE is select aa.XH0000,aa.ZFFS00,aa.ZFFSMC,aa.ZFMXBH,aa.DDLSH0,aa.JYLSH0,aa.YKTSH0,aa.DJH000,aa.JFJE00,aa.SFYLTK,aa.QTXX00,aa.ZFQDBM from ( --微信和支付宝 select '1' as XH0000,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, null as QTXX00,A.ZFQDBM 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 (24,25) and A.BRID00=PBRID00 and A.ZYGHID=PZYID00 and B.JZDH00=PJZDH00 and B.JFRQ00>=LS_KSRQ00 and B.JFRQ00<=LS_JSRQ00 and (LS_WDFXJTK = 'N' or instrb(','||LS_WDFXJTK||',',','||to_char(A.CZY000)||',')<=0) union all --银行卡 select '1' as XH0000,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,trim(B.ZPDWMC) as QTXX00,A.ZFQDBM 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 (8) and A.BRID00=PBRID00 and A.ZYGHID=PZYID00 and B.JZDH00=PJZDH00 and B.JFRQ00>=LS_YHSKRQ and B.JFRQ00<=LS_JSRQ00 and B.ZFMXBH in ('K','L','M','U','V') and ((LS_YHSKBZ in ('Y') and 1=1) or (LS_YHSKBZ not in ('Y') and 1=2)) and (LS_WDFXJTK = 'N' or instrb(','||LS_WDFXJTK||',',','||to_char(A.CZY000)||',')<=0) union all --电子支付 select '1' as XH0000,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,null as QTXX00,A.ZFQDBM 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 (A.ZFMXBH in ('B') or instrb('|'||LS_Refund_ZFMXBH||'|','|'||A.ZFMXBH||'|')>0) and ((LS_JHZFBZ in ('Y') and 1=1) or (LS_JHZFBZ not in ('Y') and 1=2)) and (LS_WDFXJTK = 'N' or instrb(','||LS_WDFXJTK||',',','||to_char(A.CZY000)||',')<=0) union all --滚存微信和支付宝 select '1' as XH0000,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, null as QTXX00,A.ZFQDBM from BM_ZFRZ00 A,ZY_JSZFMX B where 1=1 and A.DJH000=B.JFDJH0 and A.BRID00=B.BRID00 and A.DDLSH0=B.DDLSH0 and A.ZYGHID=B.ZYID00 and A.ZXZTBZ='1' and A.JFBZ00='0' and A.MZZYBZ='1' and B.YXBZ00='1' and B.CZBZ00 in ('7','8','9') 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,25) and A.BRID00=PBRID00 and A.ZYGHID=PZYID00 and B.CZRQ00>=LS_KSRQ00 and B.CZRQ00<=LS_JSRQ00 and (LS_WDFXJTK = 'N' or instrb(','||LS_WDFXJTK||',',','||to_char(A.CZY000)||',')<=0) and LS_ZTJSYLTK = '1' --启用中途结算原路退款 union all --滚存银行卡 select '1' as XH0000,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, (select trim(ZPDWMC) from ZY_BRJFB0 where ZYID00=B.ZYID00 and DJH000=B.JFDJH0 and rownum=1) as QTXX00, A.ZFQDBM from BM_ZFRZ00 A,ZY_JSZFMX B where 1=1 and A.DJH000=B.JFDJH0 and A.BRID00=B.BRID00 and A.DDLSH0=B.DDLSH0 and A.ZYGHID=B.ZYID00 and A.ZXZTBZ='1' and A.JFBZ00='0' and A.MZZYBZ='1' and B.YXBZ00='1' and B.CZBZ00 in ('7','8','9') 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 A.ZYGHID=PZYID00 and B.CZRQ00>=LS_YHSKRQ and B.CZRQ00<=LS_JSRQ00 and B.ZFMXBH in ('K','L','M','U','V') and (LS_YHSKBZ in ('Y') and 1=1) or (LS_YHSKBZ not in ('Y') and 1=2) and (LS_WDFXJTK = 'N' or instrb(','||LS_WDFXJTK||',',','||to_char(A.CZY000)||',')<=0) and LS_ZTJSYLTK = '1' --启用中途结算原路退款 union all --滚存电子支付 select '1' as XH0000,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,null as QTXX00,A.ZFQDBM from BM_ZFRZ00 A,ZY_JSZFMX B where 1=1 and A.DJH000=B.JFDJH0 and A.BRID00=B.BRID00 and A.ZYGHID=B.ZYID00 and A.DDLSH0=B.DDLSH0 and A.ZXZTBZ='1' and A.JFBZ00='0' and A.MZZYBZ='1' and B.YXBZ00='1' and B.CZBZ00 in ('7','8','9') 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.CZRQ00>=LS_JHZFRQ and B.CZRQ00<=LS_JSRQ00 and (A.ZFMXBH in ('B') or instrb('|'||LS_Refund_ZFMXBH||'|','|'||A.ZFMXBH||'|')>0) and ((LS_JHZFBZ in ('Y') and 1=1) or (LS_JHZFBZ not in ('Y') and 1=2)) and (LS_WDFXJTK = 'N' or instrb(','||LS_WDFXJTK||',',','||to_char(A.CZY000)||',')<=0) and LS_ZTJSYLTK = '1' --启用中途结算原路退款 union all --现金 select decode(B.JSLX00,'2','2','1') as XH0000,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,null as QTXX00,'' as ZFQDBM from ZY_BRJFB0 B where 1=1 and B.JFBZ00='0' and B.ZFFS00 in (1) and B.BRID00=PBRID00 and B.ZYID00=PZYID00 and B.JZDH00=PJZDH00 union all --离线pos select '1' as XH0000,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,null as QTXX00,'' as ZFQDBM from ZY_BRJFB0 B where 1=1 and B.JFBZ00='0' and B.ZFFS00 in (8) and nvl(B.ZFMXBH,'J') not in ('K','L','M','U','V') and B.BRID00=PBRID00 and B.ZYID00=PZYID00 and B.JZDH00=PJZDH00 ) aa order by aa.XH0000,aa.DJH000 desc; --倒序优先 --取消结算退款 cursor CUR_QXJSTK is select A.ZFFS00,A.ZFFSMC,A.DDLSH0,A.JYLSH0,A.YKTSH0,A.DJH000,A.PJH000,A.JFRQ00 as CZRQ00,A.JFSJ00 as CZSJ00,A.JFJE00,A.JFJE00 as TKJE00 from ZY_BRJFB0 A where A.ZYID00=PZYID00 and A.JFBZ00='1' and A.JZDH00=PJZDH00 and A.ZFFS00 in (1,2,8,24,25,26,28) order by A.DJH000 desc; begin LS_BCFYZE:=0; --本次费用总额 LS_YBBCJC:=0; --本次医保补偿金额 LS_BCZFJE:=0; --本次自付金额 LS_YJJZE0:=0; --预交金总额 LS_YTZJE0:=0; --应退总额 LS_TKXJ00:=0; --可退款现金 LS_TKXJ01:=0; LS_TKZZ00:=0; --可退款转账 LS_TKPOS0:=0; --可退款POS LS_TKWX00:=0; --可退款微信 LS_TKZFB0:=0; --可退款支付宝 LS_TKYHK0:=0; LS_TKJLS0:=0; --退款记录数 LS_TKZJE0:=0; --退款总金额 LS_TKQTJE:=0; --退款其它金额 LS_TKJE00:=0; LS_YKTKTE_TEMP:=0; LS_TKDZZF:=0; --可退款电子支付(聚合支付) select nvl(max(VALUE0), 'N') into LS_ZY_CYJSTKSFBDX from XT_XTCS00 where NAME00 = 'ZY_CYJSTKSFBDX'; --Y:易惠公司 M:医信公司 P:联空网络 R:健康之路当面付 Q:医联支付 LS_Open_ZY:=nvl(substrb(SF_XT_TYZD00('支付统一平台参数','Open_ZY'),1,10),'N'); --微信支付宝退款有效天数 0:当天 begin LS_TKYXTS:=to_number(SF_XT_TYZD00('支付统一平台参数','TKYXTS')); exception when others then LS_TKYXTS:=0; end; --统一支付微信或支付宝充值不允许窗口退款缴费员工编号=BM_YGMB00.YGBH00参数维护多个用,分隔(如:1,2) 默认为:N LS_WDFXJTK:=nvl(substrb(SF_XT_TYZD00('支付统一平台参数','WDFXJTK'),1,100),'N'); --门诊或住院聚合支付交易是否原路退款以及退款有效天数(格式:是否开启,有效天数) Y:允许 N:不允许 默认:N,30 LS_JHZFCS:=nvl(substrb(SF_XT_TYZD00('支付统一平台参数','POSTKCS'),1,100),'N,30'); 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';--聚合支付原路退回标志 Y:是 N:否 LS_JHZFTS:=0; --聚合支付有效天数 end; --住院收费银行卡联机刷卡是否原路退款,有效天数(格式:是否开启,有效天数) Y:允许 N:不允许 默认:N,30 LS_YHSKCS:=nvl(substrb(SF_XT_TYZD00('支付统一平台参数','YHPOS_ZY'),1,100),'N,30'); 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'; --银行刷卡原路退回标志 Y:是 N:否 LS_YHSKTS:=0; --银行刷卡有效天数 end; LS_Refund_ZFMXBH:=substrb(SF_SF_TYZD00('支付统一平台参数','Refund_ZFMXBH'),1,100); LS_ZTJSYLTK := nvl(SF_XT_TYZDMC('支付统一平台参数','ZTJSGCSFQYYLTK'),'0'); --中途结算余额转存是否启用原路退款 LS_YHSKRQ:=to_char(sysdate-nvl(LS_YHSKTS,0),'YYYYMMDD'); --银行刷卡有效开始日期 LS_JHZFRQ:=to_char(sysdate-nvl(LS_JHZFTS,0),'YYYYMMDD'); --聚合支付有效开始日期 LS_KSRQ00:=to_char(sysdate-nvl(LS_TKYXTS,0),'YYYYMMDD'); --微信和支付宝有效开始日期 LS_JSRQ00:=to_char(sysdate,'YYYYMMDD'); --结束日期 select to_char(sysdate,'YYYYMMDD')||to_char(sysdate,'HH24MISS')|| lpad(SQ_ZY_YJJYLT_JSTKID.NEXTVAL,2,'0') into PCXLSH0 from dual; if LS_Open_ZY='Y' then LS_ZFQDBM:='YHPAY'; elsif LS_Open_ZY='M' then LS_ZFQDBM:='YXPAY'; elsif LS_Open_ZY='Q' then LS_ZFQDBM:='YLPAY'; elsif LS_Open_ZY='R' then LS_ZFQDBM:='JKZLPAY'; elsif LS_Open_ZY='B' then LS_ZFQDBM:='RBPAY'; elsif LS_Open_ZY='J' then LS_ZFQDBM:='YJPAY'; end if; if PCXLX00 in ('2','3') then -- 0:应补交 1：应退款 2：取消结算补收 3：重新结算补收 --3：取消结算补收 4：重新结算补收 -->HIS9.0取消结算没有补收操作 jlg --查找病人结算的记录 select BRZJE0,HJJE00,GRZHZF,TCJJZF,ZFJE00,YBYL05,YSZJE0,ZZZJE0,XJYE00,ZZYE00,PJH000 into LS_BRZJE0,LS_HJJE00,LS_GRZHZF,LS_TCJJZF,LS_ZFJE00,LS_YBYL05,LS_YSZJE0,LS_ZZZJE0,LS_XJYE00,LS_ZZYE00,LS_PJH000 from ZY_JZB000 where ZYID00=PZYID00 and JZDH00=PJZDH00; --本次费用总额:PBCFYZE if LS_BRZJE0=0 then LS_BCFYZE:=LS_HJJE00; else LS_BCFYZE:=LS_BRZJE0; end if; --本次自付金额 PBCZFJE if LS_ZFJE00<0 then LS_BCZFJE:=0;--本次自付金额 else LS_BCZFJE:=LS_ZFJE00; end if; --本次医保补偿金额LS_YBBCJC=总费用LS_BCFYZE-本次自付总额LS_BCZFJE LS_YBBCJC:=nvl(LS_BCFYZE,0)-nvl(LS_BCZFJE,0); --预交金总额：LS_YJJZE0=现金总额+转账总额 LS_YJJZE0:=nvl(LS_YSZJE0,0)+nvl(LS_ZZZJE0,0); --应退总额 LS_YTZJE0=现金余额+转账总额 LS_YTZJE0:=nvl(LS_XJYE00,0)+nvl(LS_ZZYE00,0); --可退款现金：LS_TKXJ00 LS_TKXJ00:=nvl(LS_XJYE00,0); --可退款转账 LS_TKZZ00 LS_TKZZ00:=nvl(LS_ZZYE00,0); --循环查找出院结算退款金额 LS_YTZJE0:=0;--应退总额 LS_YTZJE0=现金余额+转账总额 LS_TKXJ00:=0;--可退款现金：LS_TKXJ00 LS_TKZZ00:=0;--可退款转账 LS_TKZZ00 LS_TKPOS0:=0;--可退款POS LS_TKWX00:=0;--可退款微信 LS_TKZFB0:=0;--可退款支付宝 LS_TKQTJE:=0;--退款其它金额 for TK in CUR_QXJSTK loop if TK.ZFFS00=1 then LS_YTZJE0:=nvl(LS_YTZJE0,0)+TK.JFJE00; LS_TKXJ00:=nvl(LS_TKXJ00,0)+TK.JFJE00; elsif TK.ZFFS00=2 then LS_YTZJE0:=nvl(LS_YTZJE0,0)+TK.JFJE00; LS_TKZZ00:=nvl(LS_TKZZ00,0)+TK.JFJE00; elsif TK.ZFFS00=8 then LS_YTZJE0:=nvl(LS_YTZJE0,0)+TK.JFJE00; LS_TKPOS0:=nvl(LS_TKPOS0,0)+TK.JFJE00; elsif TK.ZFFS00=24 then LS_YTZJE0:=nvl(LS_YTZJE0,0)+TK.JFJE00; LS_TKWX00:=nvl(LS_TKWX00,0)+TK.JFJE00; elsif TK.ZFFS00=25 then LS_YTZJE0:=nvl(LS_YTZJE0,0)+TK.JFJE00; LS_TKZFB0:=nvl(LS_TKZFB0,0)+TK.JFJE00; elsif TK.ZFFS00=28 then LS_YTZJE0:=nvl(LS_YTZJE0,0)+TK.JFJE00; LS_TKDZZF:=nvl(LS_TKDZZF,0)+TK.JFJE00; else LS_YTZJE0:=nvl(LS_YTZJE0,0)+TK.JFJE00; LS_TKQTJE:=nvl(LS_TKQTJE,0)+TK.JFJE00; end if; insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,JYLSH0,YKTSH0,TKDDH0,DJH000,TKJE00,CRTDATE,CRTTIME,AMOUNT,TRADE_MODE,OUTTRADENO,ZFFS00,ZFFSMC,BRID00,JZDH00,BJDJH0) values(PCXLSH0,TK.DDLSH0,TK.JYLSH0,TK.PJH000,TK.DDLSH0,TK.DJH000,TK.JFJE00,TK.CZRQ00,TK.CZSJ00,abs(TK.TKJE00),'24',PJZDH00,TK.ZFFS00,TK.ZFFSMC,PBRID00,PJZDH00,0); end loop; select count(1),abs(sum(TKJE00)) into LS_TKJLS0,LS_TKZJE0 from PAY_YJJ_MXCX where CXLSH0=PCXLSH0; PBCFYZE:=nvl(LS_BCFYZE,0); --本次费用总额 PYBBCJC:=nvl(LS_YBBCJC,0); --本次医保补偿金额 PBCZFJE:=nvl(LS_BCZFJE,0); --本次自付金额 PYJJZE0:=nvl(LS_YJJZE0,0); --预交金总额 PYTZJE0:=nvl(LS_YTZJE0,0); --应退总额 PTKXJ00:=nvl(LS_TKXJ00,0); --可退款现金 PTKZZ00:=nvl(LS_TKZZ00,0); --可退款转账 PTKPOS0:=nvl(LS_TKPOS0,0); --可退款POS PTKWX00:=nvl(LS_TKWX00,0); --可退款微信 PTKZFB0:=nvl(LS_TKZFB0,0); --可退款支付宝 PTKDZZF:=nvl(LS_TKDZZF,0);--可退款电子支付(聚合支付) PTKJLS0:=nvl(LS_TKJLS0,0); --退款记录数 PTKZJE0:=nvl(LS_TKZJE0,0); --退款总金额 PTKQTJE:=nvl(LS_TKQTJE,0);--可退款其它 else --1:出院结算退款 -->0:应补交呢？(没有传0的情况？)jlg if PCZBZ00 in ('12') then --处理取消入院登记 LS_BRZJE0 := 0; LS_HJJE00 := 0; LS_GRZHZF := 0; LS_TCJJZF := 0; LS_ZFJE00 := 0; LS_YBYL05 := 0; begin select abs(JFJE00) into LS_XJYE00 from ZY_BRJFB0 where DJH000=PXJDJH0; exception when others then LS_XJYE00 := 0; end; LS_YSZJE0 := LS_XJYE00; begin select abs(JFJE00) into LS_ZZYE00 from ZY_BRJFB0 where DJH000=PZZDJH0; exception when others then LS_ZZYE00 := 0; end; LS_ZZZJE0 := LS_ZZYE00; --本次费用总额:PBCFYZE LS_BCFYZE := 0; --本次自付金额 PBCZFJE LS_BCZFJE := 0;--本次自付金额 --本次医保补偿金额LS_YBBCJC=总费用LS_BCFYZE-本次自付总额LS_BCZFJE LS_YBBCJC := 0; --预交金总额：LS_YJJZE0=现金总额+转账总额 LS_YJJZE0:=nvl(LS_YSZJE0,0)+nvl(LS_ZZZJE0,0); --应退总额 LS_YTZJE0=现金余额+转账总额 LS_YTZJE0:=nvl(LS_XJYE00,0)+nvl(LS_ZZYE00,0); --可退款现金：LS_TKXJ00 LS_TKXJ00:=nvl(LS_XJYE00,0); --可退款转账 LS_TKZZ00 LS_TKZZ00:=nvl(LS_ZZYE00,0); else select BRZJE0,HJJE00,GRZHZF,TCJJZF,ZFJE00,YBYL05,YSZJE0,ZZZJE0,XJYE00,ZZYE00 into LS_BRZJE0,LS_HJJE00,LS_GRZHZF,LS_TCJJZF,LS_ZFJE00,LS_YBYL05,LS_YSZJE0,LS_ZZZJE0,LS_XJYE00,LS_ZZYE00 from ZY_JZB000 where ZYID00=PZYID00 and JZDH00=PJZDH00; --本次费用总额:PBCFYZE if LS_BRZJE0=0 then LS_BCFYZE:=LS_HJJE00; else LS_BCFYZE:=LS_BRZJE0; end if; --本次自付金额 PBCZFJE if LS_ZFJE00<0 then LS_BCZFJE:=0;--本次自付金额 else LS_BCZFJE:=LS_ZFJE00; end if; --本次医保补偿金额LS_YBBCJC=总费用LS_BCFYZE-本次自付总额LS_BCZFJE LS_YBBCJC:=nvl(LS_BCFYZE,0)-nvl(LS_BCZFJE,0); --预交金总额：LS_YJJZE0=现金总额+转账总额 LS_YJJZE0:=nvl(LS_YSZJE0,0)+nvl(LS_ZZZJE0,0); --应退总额 LS_YTZJE0=现金余额+转账总额 LS_YTZJE0:=nvl(LS_XJYE00,0)+nvl(LS_ZZYE00,0); --可退款现金：LS_TKXJ00 LS_TKXJ00:=nvl(LS_XJYE00,0); --可退款转账 LS_TKZZ00 LS_TKZZ00:=nvl(LS_ZZYE00,0); end if; --写入中间表 if LS_ZY_CYJSTKSFBDX='Y' then --优先微信支付宝、银行卡 if nvl(abs(LS_TKXJ00),0)>0 then LS_TKJE00:=nvl(abs(LS_TKXJ00),0); for TKJE in CUR_CYTKJE_SLN loop --银行卡显示银行卡号 if TKJE.ZFFS00=8 and TKJE.QTXX00 is not null then LS_QTXX00:=substrb('银行卡号:('||TKJE.QTXX00||')',1,200); end if; --平台流水号不为空 if trim(TKJE.JYLSH0) is not null then LS_QTXX00:=substrb(LS_QTXX00||'平台流水号:('||trim(TKJE.JYLSH0)||')',1,200); end if; --渠道流水号不为空 if trim(TKJE.YKTSH0) is not null then LS_QTXX00:=substrb(LS_QTXX00||'渠道流水号:('||trim(TKJE.YKTSH0)||')',1,200); end if; LS_BILLSN:=SYS_GUID(); --查询内部ID=PAY_YJJ_MXCX.BILLSN (生成唯一值) if TKJE.JFJE00>=LS_TKJE00 then LS_YKTKTE_TEMP:=LS_TKJE00; else LS_YKTKTE_TEMP:=TKJE.JFJE00; end if; if LS_TKJE00>0 and TKJE.SFYLTK='Y' then insert into PAY_YJJ_MXCX(CXLSH0,BRID00,ZFFS00,ZFFSMC,DDLSH0,JYLSH0,YKTSH0,TKDDH0,DJH000,TKJE00,STATE,ZFMXBH,JZDH00,QTXX00,ZFQDBM,BILLSN) values(PCXLSH0,PBRID00,TKJE.ZFFS00,TKJE.ZFFSMC,TKJE.DDLSH0,TKJE.JYLSH0,TKJE.YKTSH0,TKJE.DDLSH0,TKJE.DJH000,-abs(LS_YKTKTE_TEMP),TKJE.SFYLTK,TKJE.ZFMXBH,PJZDH00,LS_QTXX00,TKJE.ZFQDBM,LS_BILLSN); end if; LS_TKJE00:=nvl(LS_TKJE00,0)-nvl(LS_YKTKTE_TEMP,0); if LS_TKJE00<=0 then LS_TKJE00:=0; end if; end loop; end if; else --默认倒序优先 if nvl(abs(LS_TKXJ00),0)>0 then LS_TKJE00:=nvl(abs(LS_TKXJ00),0); for TKJE in CUR_CYTKJE loop --银行卡显示银行卡号 if TKJE.ZFFS00=8 and TKJE.QTXX00 is not null then LS_QTXX00:=substrb('银行卡号:('||TKJE.QTXX00||')',1,200); end if; --平台流水号不为空 if trim(TKJE.JYLSH0) is not null then LS_QTXX00:=substrb(LS_QTXX00||'平台流水号:('||trim(TKJE.JYLSH0)||')',1,200); end if; --渠道流水号不为空 if trim(TKJE.YKTSH0) is not null then LS_QTXX00:=substrb(LS_QTXX00||'渠道流水号:('||trim(TKJE.YKTSH0)||')',1,200); end if; LS_BILLSN:=SYS_GUID(); --查询内部ID=PAY_YJJ_MXCX.BILLSN (生成唯一值) if TKJE.JFJE00>=LS_TKJE00 then LS_YKTKTE_TEMP:=LS_TKJE00; else LS_YKTKTE_TEMP:=TKJE.JFJE00; end if; if LS_TKJE00>0 and TKJE.SFYLTK='Y' then insert into PAY_YJJ_MXCX(CXLSH0,BRID00,ZFFS00,ZFFSMC,DDLSH0,JYLSH0,YKTSH0,TKDDH0,DJH000,TKJE00,STATE,ZFMXBH,JZDH00,QTXX00,ZFQDBM,BILLSN) values(PCXLSH0,PBRID00,TKJE.ZFFS00,TKJE.ZFFSMC,TKJE.DDLSH0,TKJE.JYLSH0,TKJE.YKTSH0,TKJE.DDLSH0,TKJE.DJH000,-abs(LS_YKTKTE_TEMP),TKJE.SFYLTK,TKJE.ZFMXBH,PJZDH00,LS_QTXX00,TKJE.ZFQDBM,LS_BILLSN); end if; LS_TKJE00:=nvl(LS_TKJE00,0)-nvl(LS_YKTKTE_TEMP,0); if LS_TKJE00<=0 then LS_TKJE00:=0; end if; end loop; end if; end if; --支付明细编码H:现金I:转账J:POS离线K:POS刷卡L:龙支付M:银联二维码D:微信离线E:微信扫码F:支付宝离线G:支付宝扫码A:榕医通B:聚合支付C:健康通N:银医通P:一卡通 select abs(sum(TKJE00)) into LS_YLTKJE from PAY_YJJ_MXCX where BRID00=PBRID00 and CXLSH0=PCXLSH0 and STATE='Y'; LS_XJTKJE:=nvl(LS_XJYE00,0)-nvl(LS_YLTKJE,0); if LS_XJTKJE<0 then LS_XJTKJE:=0; end if; --插入现金余额到中间表 LS_QTXX00:=''; --其它信息 LS_BILLSN:=SYS_GUID(); --查询内部ID=PAY_YJJ_MXCX.BILLSN (生成唯一值) LS_ZFQDBM:=''; --厂家编码 insert into PAY_YJJ_MXCX(CXLSH0,BRID00,ZFFS00,ZFFSMC,DDLSH0,JYLSH0,YKTSH0,TKDDH0,DJH000,TKJE00,STATE,ZFMXBH,JZDH00,QTXX00,ZFQDBM,BILLSN) values(PCXLSH0,PBRID00,1,'现金',PXJDJH0,'','','',PXJDJH0,-nvl(LS_XJTKJE,0),'N','H',PJZDH00,LS_QTXX00,LS_ZFQDBM,LS_BILLSN); --查找银行卡原路退款金额 select abs(sum(TKJE00)) into LS_YHTKJE from PAY_YJJ_MXCX where BRID00=PBRID00 and CXLSH0=PCXLSH0 and STATE='Y' and ZFFS00 in (8); if nvl(LS_YHTKJE,0)=0 then --未找到银行原路退款金额插入一笔0金额银行金额 LS_QTXX00:=''; --其它信息 LS_BILLSN:=SYS_GUID(); --查询内部ID=PAY_YJJ_MXCX.BILLSN (生成唯一值) LS_ZFQDBM:=''; --厂家编码 insert into PAY_YJJ_MXCX(CXLSH0,BRID00,ZFFS00,ZFFSMC,DDLSH0,JYLSH0,YKTSH0,TKDDH0,DJH000,TKJE00,STATE,ZFMXBH,JZDH00,QTXX00,ZFQDBM,BILLSN) values(PCXLSH0,PBRID00,8,'银行卡',PZZDJH0,null,null,null,null,0,'N','J',PJZDH00,LS_QTXX00,LS_ZFQDBM,LS_BILLSN); end if; --插入转账退款记录 if nvl(LS_ZZYE00,0)>0 then LS_QTXX00:=''; --其它信息 LS_BILLSN:=SYS_GUID(); --查询内部ID=PAY_YJJ_MXCX.BILLSN (生成唯一值) LS_ZFQDBM:=''; --厂家编码 insert into PAY_YJJ_MXCX(CXLSH0,BRID00,ZFFS00,ZFFSMC,DDLSH0,JYLSH0,YKTSH0,TKDDH0,DJH000,TKJE00,STATE,ZFMXBH,JZDH00,QTXX00,ZFQDBM,BILLSN) values(PCXLSH0,PBRID00,2,'转账',PZZDJH0,null,null,null,PZZDJH0,-abs(LS_TKZZ00),'N','I',PJZDH00,LS_QTXX00,LS_ZFQDBM,LS_BILLSN); end if; --修改中间表支付明细名称ZFMXMC update PAY_YJJ_MXCX a set a.ZFMXMC=substrb(SF_ZY_ZFMXMC(a.ZFFS00,a.ZFFSMC,a.ZFMXBH),1,20) where a.CXLSH0=PCXLSH0 and BRID00=PBRID00; --修改渠道编码 update PAY_YJJ_MXCX a set ZFQDBM=LS_ZFQDBM where a.CXLSH0=PCXLSH0 and BRID00=PBRID00 and (a.ZFQDBM is null or a.ZFQDBM not in ('YHPAY','YXPAY','YLPAY','JKZLPAY','RBPAY','YJPAY')); select count(1),abs(sum(TKJE00)) into LS_TKJLS0,LS_TKZJE0 from PAY_YJJ_MXCX where CXLSH0=PCXLSH0 and BRID00=PBRID00 and nvl(TKJE00,0)<>0 ; PBCFYZE:=nvl(LS_BCFYZE,0); --本次费用总额 PYBBCJC:=nvl(LS_YBBCJC,0); --本次医保补偿金额 PBCZFJE:=nvl(LS_BCZFJE,0); --本次自付金额 PYJJZE0:=nvl(LS_YJJZE0,0); --预交金总额 PYTZJE0:=nvl(LS_YTZJE0,0); --应退总额 PTKJLS0:=nvl(LS_TKJLS0,0); --退款记录数 PTKZJE0:=nvl(LS_TKZJE0,0); --退款总金额 LS_TKXJ00:=0; --可退款现金 LS_TKZZ00:=0; --可退款转账 LS_TKPOS0:=0; --可退款POS LS_TKWX00:=0; --可退款微信 LS_TKZFB0:=0; --可退款支付宝 LS_TKQTJE:=0; --可退款其它 --从中间表计算退款总额 for CUR in CUR_TKHZJE loop if CUR.ZFFS00=1 then LS_TKXJ00:=nvl(LS_TKXJ00,0)+nvl(CUR.TKJE00,0); elsif CUR.ZFFS00=2 then LS_TKZZ00:=nvl(LS_TKZZ00,0)+nvl(CUR.TKJE00,0); elsif CUR.ZFFS00=8 then LS_TKPOS0:=nvl(LS_TKPOS0,0)+nvl(CUR.TKJE00,0); elsif CUR.ZFFS00=24 then LS_TKWX00:=nvl(LS_TKWX00,0)+nvl(CUR.TKJE00,0); elsif CUR.ZFFS00=25 then LS_TKZFB0:=nvl(LS_TKZFB0,0)+nvl(CUR.TKJE00,0); elsif CUR.ZFFS00=28 then LS_TKDZZF:=nvl(LS_TKDZZF,0)+nvl(CUR.TKJE00,0); else LS_TKQTJE:=nvl(LS_TKQTJE,0)+nvl(CUR.TKJE00,0); end if; end loop; PTKXJ00:=nvl(LS_TKXJ00,0);--可退款现金 PTKZZ00:=nvl(LS_TKZZ00,0);--可退款转账 PTKPOS0:=nvl(LS_TKPOS0,0);--可退款POS PTKWX00:=nvl(LS_TKWX00,0);--可退款微信 PTKZFB0:=nvl(LS_TKZFB0,0);--可退款支付宝 PTKDZZF:=nvl(LS_TKDZZF,0);--可退款电子支付(聚合支付) PTKQTJE:=nvl(LS_TKQTJE,0);--可退款其它 end if; exception when no_data_found then PERRSTR:=substrb('执行过程SP_ZY_PAY_TKDATA异常!错误原因:'||sqlerrm,1,200); when others then PERRSTR:=substrb('执行过程SP_ZY_PAY_TKDATA异常!错误原因:'||sqlerrm,1,200); end;