prompt 门诊收费预交金退款冲正
create or replace procedure SP_SF_PAY_YJJ_REVERSE
(
 PCZY000 in number       ,--输入:操作员编码
 PCZYXM0 in varchar2     ,--输入:操作员姓名
 PCZYKS0 in number       ,--输入:操作员科室
 PBRID00 in number       ,--输入:BRID00
 PJFDJH0 in number       ,--输入:缴费单据号
 PJFDDH0 in varchar2     ,--输入:缴费订单流水号
 PTYPE00 in varchar2     ,--输入:冲正类型 01:充值冲正 02:退款冲正
 PXMLSTR in varchar2     ,--输入:XML参数
 PCOMMIT in varchar2     ,--输入:是否提交事务 Y:提交 N:不提交
 PZXZT00 out number      ,--输出:执行状态 0:失败 1：成功
 PERRMSG out varchar2    ,--输出:错误信息
 PDJH000 out number      ,--输出:单据号=SF_JFB000.DJH000
 PDDLSH0 out varchar2    ,--输出:订单流水号=BM_ZFRZ00.DJH000
 POUTXML out varchar2     --输出:XML 
)
as 
-- MODIFICATION HISTORY
-- Person      Date        Comments
--zhangyc     2024.11.05   created 门诊收费预交金退款冲正：SP_SF_PAY_YJJ_REVERSE by MZSF9-20240919-001
--zhangyc     2024.11.12   修正SF_PJSYQK.FZPJH0未写入导致前台无法显示 by MZSF9-20241113-002
--zhangyc     2024.12.10   修正异常冲正SF_PJSYQK.plbh00错误,收款是2:门诊预交金退款单,7:门诊预交金票据 by    MZSF9-20241211-001
  LS_COUNT0  number(5); 
  LS_CZRQ00  char(8);
  LS_CZSJ00  char(8);
  LS_OUTXML  varchar2(4000);--XML
  
  RBRXX      BM_BRXXB0%rowtype; --病人信息表
  RZFRZ      BM_ZFRZ00%rowtype; --支付日志
  LS_BRID00  BM_ZFRZ00.BRID00%type;--病人id
  LS_YDJH00  BM_ZFRZ00.DJH000%type;--原单据号
  LS_YDDLSH  BM_ZFRZ00.DDLSH0%type;--原订单流水号
  LS_DJH000  SF_BRJFB0.DJH000%type;--单据号
  LS_DDLSH0  SF_BRJFB0.DDLSH0%type;--订单流水号
  LS_PJH000  SF_BRJFB0.PJH000%type;--票据号
  LS_JFBZ00  SF_BRJFB0.JFBZ00%type;--'0'交预交金,'1'退预交金
  LS_JYLSH0  SF_BRJFB0.JYLSH0%type;--  
  LS_YKTSH0  SF_BRJFB0.YKTSH0%type;--
  LS_JYLXMC  SF_BRJFB0.JYLXMC%type;--
  LS_JGBM00  SF_BRJFB0.JGBM00%type;--
  LS_JGMC00  SF_BRJFB0.JGMC00%type;--
  
  LS_JYJE00   number(12,2);--交易金额
  LS_XJJE00   number(12,2); --现金金额
  LS_ZZJE00   number(12,2); --转账金额
  LS_YHKJE0   number(12,2); --银行卡金额 
  LS_DZQBJE   number(12,2); --电子钱包金额  
  LS_HJJE00   number(12,2); --合计金额
  LS_YJJPJ0  varchar2(100);  --预交金票据与预交金退一样
  LS_PLBHCS  varchar2(100);
  LS_YYID00  BM_PJLYQK.YYID00%type;  --医院ID
  LS_PJLXBM  BM_PJLYQK.PJLXBM%type;
  LS_PLBH00  BM_PJLYQK.PJLXBM%type;
  Pro_out_zxcgbz  number(10);--执行成功标志 1成功 0失败
  Pro_out_tsxx00  varchar2(400); --执行结果提示信息，成功的情况下也可能有警告信息
  Pro_out_xxxx00  varchar2(400); --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标  
begin
PZXZT00:=0;--输出:执行状态 0:失败 1：成功
PERRMSG:=null;--输出:错误信息
PDJH000:=0;--输出:单据号=SF_JFB000.DJH000
PDDLSH0:=null;--输出:订单流水号=BM_ZFRZ00.DJH000
POUTXML:=null;--输出:XML
LS_COUNT0:=0;
LS_CZRQ00 := to_char(sysdate,'YYYYMMDD');
LS_CZSJ00 := to_char(sysdate,'HH24:MI:SS');
if nvl(PCZYKS0,0)>0 then 
 select nvl(max(a.YYID00),'0') into LS_YYID00 from BM_BMBM00 a where a.BMBH00 = PCZYKS0;
else
 select nvl(max(a.YYID00),'0') into LS_YYID00 from BM_BMBM00 a,BM_YGBM00 B where a.BMBH00=B.BMBH00 and B.YGBH00 = PCZY000;  
end if; 


--步骤1：入参校验
if PTYPE00 not in ('01','02') then 
 PZXZT00:=0;--输出:执行状态 0:失败 1：成功
 PERRMSG:='冲正类型(01:充值冲正 02:退款冲正)传值错误';--输出:错误信息   
 return;    
end if;
--步骤2：查询基本信息
begin
  select * into RBRXX from BM_BRXXB0 a where a.BRID00=PBRID00;
  exception
  when others  then
   PZXZT00:=0;--输出:执行状态 0:失败 1：成功
   PERRMSG:='未找到有效的基本信息BRID00='||PBRID00;--输出:错误信息
   return;   
end;
LS_BRID00:=RBRXX.BRID00;--病人id

--步骤3：查询订单流水号
 begin
  select * into RZFRZ from BM_ZFRZ00 a where a.BRID00=PBRID00 
  and  ((nvl(PJFDJH0,0)=0 and 1=1) or (nvl(PJFDJH0,0)>0 and a.DJH000=PJFDJH0))
  and  ((PJFDDH0 is null and 1=1) or (PJFDDH0 is not null and a.DDLSH0=PJFDDH0))
  ;
  exception
  when others  then
   PZXZT00:=0;--输出:执行状态 0:失败 1：成功
   PERRMSG:='未找到有效的订单流水号';--输出:错误信息    
   return;   
 end;
  LS_YDJH00:=RZFRZ.DJH000;--原单据号
  LS_YDDLSH:=RZFRZ.DDLSH0;--原订单流水号
--步骤5:业务处理  
if PTYPE00='01' then --冲正类型 01:充值冲正
 PZXZT00:=0;--输出:执行状态 0:失败 1：成功
 PERRMSG:='充值冲正接口未完成';--输出:错误信息  
 return;
elsif PTYPE00='02' then --冲正类型 02:退款冲正
  if nvl(RZFRZ.DJH000,0)=0 then 
   PZXZT00:=0;--输出:执行状态 0:失败 1：成功
   PERRMSG:='未找到有效的缴费单据号DJH000='||LS_YDJH00||'and DDLSH0='||LS_YDDLSH;--输出:错误信息    
   return;      
  end if;
  if RZFRZ.JFBZ00='0' then --缴费备注0：交,1：退
   PZXZT00:=0;--输出:执行状态 0:失败 1：成功
   PERRMSG:='该笔缴费记录为充值，无法退款冲正DJH000='||LS_YDJH00||'and DDLSH0='||LS_YDDLSH;--输出:错误信息  
   return;      
  end if;
  LS_JFBZ00:='0';--'0'交预交金,'1'退预交金
  LS_YJJPJ0:=substrb(SF_XT_GETXTCSVALUE('SF_YJJPJ0','N',null),1,100);
  LS_PLBHCS:=substrb(SF_XT_GETXTCSVALUE('SF_YJJTKPJLX','Y',null),1,100);
  if LS_JFBZ00 = '0'  then  --'0':交款,'1':退款
    LS_PJLXBM:=7;
  else  
   if (trim(LS_YJJPJ0)='Y') and (trim(LS_PLBHCS)='Y') then
      LS_PJLXBM:=7;
   else
      LS_PJLXBM:=2;
   end if;
  end if;
  LS_PLBH00:=LS_PJLXBM;
  LS_JYLXMC:='平台退款失败HIS冲正';--
  select 'MZYJJCZ'||to_char(sysdate,'YYYYMMDDHH24MISS')||lpad(SQ_BM_ZFRZ00_DDLSH0.nextval,3,'0') into LS_DDLSH0  from dual; 
  select   SQ_SF_BRJFB0_DJH000.nextval  into  LS_DJH000   from  dual;
  select   SQ_SF_PJSYQK_PJH000.nextval  into  LS_PJH000   from  dual;
    
  LS_JYJE00:=abs(nvl(RZFRZ.JFJE00,0));--交易金额
  if RZFRZ.ZFFS00=1 then 
   LS_XJJE00:=LS_JYJE00; --现金金额
   LS_ZZJE00:=0; --转账金额
   LS_YHKJE0:=0; --银行卡金额
  elsif RZFRZ.ZFFS00=2 then
   LS_XJJE00:=0; --现金金额
   LS_ZZJE00:=LS_JYJE00; --转账金额
   LS_YHKJE0:=0; --银行卡金额
  elsif RZFRZ.ZFFS00=8 then
   LS_XJJE00:=0; --现金金额
   LS_ZZJE00:=0; --转账金额
   LS_YHKJE0:=LS_JYJE00; --银行卡金额
  else
   LS_XJJE00:=LS_JYJE00; --现金金额
   LS_ZZJE00:=0; --转账金额
   LS_YHKJE0:=0; --银行卡金额   
  end if;
  LS_DZQBJE:=0; --电子钱包金额  
  LS_HJJE00:=nvl(LS_XJJE00,0)+nvl(LS_ZZJE00,0)+nvl(LS_YHKJE0,0); --合计金额   
  --插入SF_BRJFB0
  insert into  SF_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPDWMC,ZPZH00,ZPKHYH,PJH000,
                  JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,WBHLID,WBHLJE,CZYKS0,
                  JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0,ZFMXBH,SHH000,APPBM0,APPMC0,APPYH0,YYJSYH,KMH000,BKE284)
  select LS_DJH000,aa.BRID00,aa.JFLBID,abs(aa.JFJE00),aa.ZFFS00,aa.ZFFSMC,aa.ZPH000,aa.ZPDWMC,aa.ZPZH00,aa.ZPKHYH,LS_PJH000,
                 LS_CZRQ00,LS_CZSJ00,PCZY000,PCZYXM0,0,LS_JFBZ00,0,0,1,abs(aa.WBHLJE),PCZYKS0,
                 aa.JYLSH0,aa.YKTSH0,aa.JYLXMC,aa.JGBM00,aa.JGMC00,LS_DDLSH0,aa.ZFMXBH,aa.SHH000,aa.APPBM0,aa.APPMC0,aa.APPYH0,aa.YYJSYH,aa.KMH000,aa.BKE284
  from  SF_BRJFB0 aa where aa.BRID00=LS_BRID00 and aa.DJH000=LS_YDJH00;
  --调用SP_SF_BRJFB0_INSERT
  SP_SF_BRJFB0_INSERT(LS_DJH000,PCZY000,PCZYKS0,PCOMMIT,Pro_out_zxcgbz,Pro_out_tsxx00,Pro_out_xxxx00);
  if Pro_out_zxcgbz=0 then 
     PZXZT00:=0;--输出:执行状态 0:失败 1：成功
   PERRMSG:='调用SP_SF_BRJFB0_INSERT失败,错误原因:'||Pro_out_tsxx00;--出参:错误信息,NULL表示正确
   return;  
  end if;  
   --插入SF_PJSYQK
   insert into SF_PJSYQK(PJH000,FZPJH0,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,
                         DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,HJJE00,CZYKS0,PJLYID,PHLYXZ,XSRQ00,YYID00)
   select LS_PJH000,LS_PJH000,PLBH00,PLMC00,'0',LS_XJJE00,LS_ZZJE00,LS_YHKJE0,PCZY000,PCZYXM0,
         LS_CZRQ00,LS_CZSJ00,'Y','0',RBRXX.BRXM00,RBRXX.BRXB00,RBRXX.BRID00,nvl(LS_DZQBJE,0),nvl(LS_HJJE00,0),PCZYKS0,0,'N',LS_CZRQ00,LS_YYID00
   from BM_PJLXB0 where PLBH00 = LS_PJLXBM ;

   SP_SF_PJSYQK_INSERT(LS_PJH000,PCZY000,PCZYKS0,PCOMMIT,Pro_out_zxcgbz,Pro_out_tsxx00,Pro_out_xxxx00,'0','2');
    
  --插入日志主表
  insert into BM_ZFRZ00(
    DDLSH0,CZRQ00,CZSJ00,CZY000,CZYXM0,JKLXBH,JKLXMC,ZDBH00,BRID00,BRXM00,
    BRXB00,YBKH00,YBID00,ICKH00,BRZJBH,BRDH00,MZZYBZ,JFBZ00,ZYGHID,ZYHGHH,
    JZDH00,YJDH00,ZFFS00,ZFFSMC,DJH000,JFJE00,JFRQ00,JFSJ00,ZXZTBZ,BRYE00,
    BZ0000,PTDDH0,ZFQD00,JYCGSJ,PTGZH0,JKXYBM,JKXYXX,JYLSH0,YKTSH0,JYLXMC,
    JGBM00,JGMC00,INCS00,INQTCS,OUTCS0,TKDDH0,TKJE00,TYJE00,PJXH00,ZPKHYH,
    ZPDWMC,ZPH000,ZFQDBM,FKTM00,ZFMXBH,NBJYDH,JKQQCS,TKDJJE,BKE284,PAYWAY 
    )
  select
    LS_DDLSH0,LS_CZRQ00,LS_CZSJ00,PCZY000,PCZYXM0,aa.JKLXBH,aa.JKLXMC,aa.ZDBH00,aa.BRID00,aa.BRXM00,
    aa.BRXB00,aa.YBKH00,aa.YBID00,aa.ICKH00,aa.BRZJBH,aa.BRDH00,aa.MZZYBZ,LS_JFBZ00,aa.ZYGHID,aa.ZYHGHH,
    aa.JZDH00,aa.YJDH00,aa.ZFFS00,aa.ZFFSMC,LS_DJH000,abs(aa.JFJE00),LS_CZRQ00,LS_CZSJ00,aa.ZXZTBZ,0,
    aa.BZ0000,aa.PTDDH0,aa.ZFQD00,aa.JYCGSJ,aa.PTGZH0,aa.JKXYBM,aa.JKXYXX,aa.JYLSH0,aa.YKTSH0,aa.JYLXMC,
    aa.JGBM00,aa.JGMC00,aa.INCS00,aa.INQTCS,aa.OUTCS0,DDLSH0,0 as TKJE00,0 as TYJE00,aa.PJXH00,aa.ZPKHYH,
    aa.ZPDWMC,aa.ZPH000,aa.ZFQDBM,aa.FKTM00,aa.ZFMXBH,aa.NBJYDH,aa.JKQQCS,aa.TKDJJE,aa.BKE284,aa.PAYWAY 
  from BM_ZFRZ00 aa where aa.BRID00=LS_BRID00 and aa.DJH000=LS_YDJH00 and aa.DDLSH0=LS_YDDLSH;
 
  --修改原单的状态
  update BM_ZFRZ00 aa set 
   aa.TQTK00='Z'  --支付退款状态 0:正常模式 1:交互前院内优先退款 2:结算后院内优先退款 3：结算后院内优先退款平台退款成功 4:结算后院内优先退款平台退款失败 7：交互前院内优先退款平台退款成功 Z:冲正
   where aa.BRID00=LS_BRID00 and aa.DJH000=LS_YDJH00 and aa.DDLSH0=LS_YDDLSH;  
 
 --修改支付日志表和缴费表
  update SF_BRJFB0 aa set 
   aa.JYLXMC=nvl(LS_JYLXMC,JYLXMC) -- 
  where aa.BRID00=LS_BRID00 and aa.DJH000=LS_DJH000 and aa.DDLSH0=LS_DDLSH0;
  update BM_ZFRZ00 aa set 
   aa.TKJYLX='02' ,--退款交易类型 00:充值 01:正常退款 02:退款冲正03:支付平台退款成功 04：支付平台退款异常
   aa.TQTK00='Z'  ,--支付退款状态 0:正常模式 1:交互前院内优先退款 2:结算后院内优先退款 3：结算后院内优先退款平台退款成功 4:结算后院内优先退款平台退款失败 7：交互前院内优先退款平台退款成功 Z:冲正
   aa.JGBM00='02' ,----退款交易类型 01:正常退款 02:退款冲正03:支付平台退款成功 04：支付平台退款异常
   aa.JYLXMC=nvl(LS_JYLXMC,JYLXMC) -- 
 where aa.BRID00=LS_BRID00 and aa.DJH000=LS_DJH000 and aa.DDLSH0=LS_DDLSH0;             
 
 --还原正单退款金额
 if RZFRZ.TKDDH0 is not null then 
  update BM_ZFRZ00 aa set 
   TKJE00=nvl(TKJE00,0)+LS_JYJE00  --退款金额
   where DDLSH0=RZFRZ.TKDDH0 and BRID00=RZFRZ.BRID00;
 end if;                
end if;
 
if  PCOMMIT='Y' then
 commit;
end if; 
PZXZT00:=1;--输出:执行状态 0:失败 1：成功
PERRMSG:=null;--输出:错误信息
PDJH000:=nvl(LS_DJH000,0);--输出:单据号=SF_JFB000.DJH000
PDDLSH0:=LS_DDLSH0;--输出:订单流水号=BM_ZFRZ00.DJH000
POUTXML:=LS_DDLSH0;--输出:XML  
exception
  when no_data_found then
    PZXZT00:=0;
    PERRMSG:=substrb('门诊预交金退款冲正异常!原因:'||sqlerrm,1,200);    
  when others   then
    PZXZT00:=0;
    PERRMSG:=substrb('门诊预交金退款冲正失败!原因:'||sqlerrm,1,200); 
     
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%