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;
