create or replace procedure SP_ZY_YJJ_TKDATA
(
 PBRID00  in  number    ,--病人ID
 PZYID00  in  number    ,--住院ID
 PZFFS00  in  number    ,--支付方式
 PZFFSMC  in  varchar2  ,--支付方式名称
 PJYJE00  in  number    ,--交易金额
 PQTCS00  in  varchar2  ,--其他参数
 PCXLSH0  out varchar2  ,--退款查询流水号
 PTKJLS0  out number    ,--退款记录数
 PTKJE00  out number    ,--退款金额
 PERRSTR  out varchar2  ,--错误信息
 PXTCS00  in  varchar2 default 'Y', --系统参数
 PSFCYE0  in  char default '0'   --是否超余额退款, 0否1是
)
as 
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- liwm        2018.03.06  create：统一支付平台接口改造; for ZYSF9-20180810-003
-- liwm        2018.08.23  统一支付银联二维码原路退功能添加  ZYSF9-20180823-001
-- linzetao    2020.08.27  增加入参PSFCYE0(是否超余额退款), 超余额退款且参数ZY_YJJWXZFBTKSFYXCYETK开启时不判断退款金额是否超过余额      ZYSF9-20200713-001
-- linzetao    2021.02.04  增加聚合支付(zffs00=28)原路退功能  ZYSF9-20210128-003
-- linzetao    2021.03.11  处理参数ZY_YJJWXZFBTKSFYXCYETK扩展   ZYSF9-20210223-006
-- linzetao    2021.05.24  处理超余额退款时退款金额超过可用余额无法退款问题    ZYSF9-20210504-001
  LS_COUNT0  number(12,2);
  LS_JYJE00  number(12,2);--交易金额
  LS_ZHYE00  number(12,2);
  LS_TKJE00  number(12,2);
  LS_YKTKTE_TEMP  number(12,2);
  LS_DZZFCS  varchar2(20);
  LS_TKYXTS  number(5);
  LS_YXCYE0  XT_XTCS00.VALUE0%type;
  LS_KSRQ00  char(8);   --开始日期
  LS_JSRQ00  char(8);   --结束日期
  
  CURSOR CUR_TKJE  is
  select A.DDLSH0,A.JYLSH0,A.YKTSH0,A.DJH000,(nvl(JFJE00,0)+nvl(TKJE00,0))as JFJE00 
          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.BRID00=PBRID00 and  B.ZYID00 = PZYID00 and B.DJH000=A.DJH000
                     and B.ZFFS00=PZFFS00 and B.JFRQ00>=LS_KSRQ00 and  B.JFRQ00<=LS_JSRQ00)
          order by A.DJH000 desc;
                     
                     
begin
  PCXLSH0:='';--退款查询流水号
  PTKJLS0:=0;--退款记录数
  PTKJE00:=0;--退款金额
  PERRSTR:=0;--错误信息
  LS_JYJE00:=nvl(PJYJE00,0);--交易金额
  --门诊是否启用易惠统一支付或院内微信支和付宝支付 Y:开启易惠统一支付接口 M:开启院内微信和支付接口 N:都不开启
     --select SF_SF_TYZD00('支付统一平台参数','Open_MZ') into LS_DZZFCS from dual; 
     --微信支付宝退款有效天数 0:当天
  begin
    select to_number(SF_SF_TYZD00('支付统一平台参数','TKYXTS')) into LS_TKYXTS from dual; 
  exception
    when others then
      LS_TKYXTS:=0;
  end;
   
  select nvl(trim(max(VALUE0)),'0') into LS_YXCYE0 from XT_XTCS00 where name00='ZY_YJJWXZFBTKSFYXCYETK';
   
  LS_KSRQ00:=to_char(sysdate-LS_TKYXTS,'YYYYMMDD'); 
  LS_JSRQ00:=to_char(sysdate,'YYYYMMDD');
   
  select to_char(sysdate,'YYYYMMDD')||to_char(sysdate,'HH24MISS')|| lpad(SQ_ZY_YJJYLT_YJJTKID.NEXTVAL,2,'0') into PCXLSH0 from dual;
  --0 门诊 1住院 /BRID00 /交费类型 1：门诊  2:住院/返回类型 0:可用余额 1:总余额 2：现金余额 3:停用金额  4:转账余额
  select SF_ZY_KYJE00(PZYID00,PBRID00,1,0) into  LS_ZHYE00 from dual;  --可用余额
  select SF_ZY_PAY_KTKJE0(PZYID00,PBRID00,PZFFS00,PXTCS00)into LS_TKJE00 from dual; --可退款金额
   
  if PZFFS00 not in (24,25,8,28) or nvl(PSFCYE0,'0') <> '1' or nvl(SF_XT_RETURNINDEXEH(LS_YXCYE0,'|',1),'0') not in ('1','2') then
    if LS_TKJE00<=0 then 
      PERRSTR:='该病人本次'||PZFFSMC||'退款金额为0,无法退款！';
      return;
    end if;
  end if;
     
  if PZFFS00 in (24,25,8,28) then --微信支付宝
    if nvl(PSFCYE0,'0') <> '1' or nvl(SF_XT_RETURNINDEXEH(LS_YXCYE0,'|',1),'0') not in ('1','2') then
      --非超余额退款操作或参数ZY_YJJWXZFBTKSFYXCYETK为不允许时, 需判断账户余
      --判断账户余额
      if nvl(abs(LS_JYJE00),0)>nvl(LS_ZHYE00,0) then
        PERRSTR:='该病人账户余额不足:可用余额:'||trim(to_char(nvl(LS_ZHYE00,0),'9999999990.00'))||'元'
                     ||'本次退款金额:'||trim(to_char(nvl(abs(LS_JYJE00),0),'9999999990.00'))||'元';  
        return;
      end if;
    end if;
      
    if nvl(PSFCYE0,'0') <> '1' or nvl(SF_XT_RETURNINDEXEH(LS_YXCYE0,'|',1),'0') not in ('1','2') then
      --非超余额退款操作或参数ZY_YJJWXZFBTKSFYXCYETK为不允许时, 需判断对应的支付可退款金额
      --判断本次对应的支付可退款金额
      if nvl(abs(LS_JYJE00),0)>nvl(abs(LS_TKJE00),0) then
        PERRSTR:='该病人本次'||PZFFSMC||'退款最大允许退款金额:'||trim(to_char(nvl(abs(LS_TKJE00),0),'9999999990.00'))||'元';
        return;
      end if;
    end if;
      
    --整理退款数据
    if nvl(abs(LS_JYJE00),0)>0 then 
      LS_TKJE00:=nvl(abs(LS_JYJE00),0);
      for TKJE  in CUR_TKJE loop
        if TKJE.JFJE00>=LS_TKJE00 then 
          LS_YKTKTE_TEMP:=LS_TKJE00;
        else
          LS_YKTKTE_TEMP:=TKJE.JFJE00;
        end if;
        
        if LS_TKJE00>0 then 
          insert into PAY_YJJ_MXCX(CXLSH0,DDLSH0,JYLSH0,YKTSH0,TKDDH0,DJH000,TKJE00)
              values(PCXLSH0,TKJE.DDLSH0,TKJE.JYLSH0,TKJE.YKTSH0,TKJE.DDLSH0,TKJE.DJH000,-abs(LS_YKTKTE_TEMP));
        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;
      
    select count(1),abs(sum(TKJE00)) into PTKJLS0,PTKJE00 from PAY_YJJ_MXCX where CXLSH0=PCXLSH0;
      
    PCXLSH0:=PCXLSH0;--退款查询流水号
    PTKJLS0:=PTKJLS0;--退款记录数
    PTKJE00:=PTKJE00;--退款金额
    PERRSTR:='';--错误信息
      
  end if;
      
exception
  when no_data_found then
    PERRSTR:='执行过程SP_ZY_YJJ_TKDATA异常!';
  when others   then
    PERRSTR:='执行过程SP_ZY_YJJ_TKDATA异常!'; 
end;