create or replace procedure SP_ONEPAY_YJJYCCZ(
  PJKLXBH  in     number                       ,--输入:接口类型编号 
  PBRID00  in     number                       ,--输入:BRID00
  PCARDNO  in     varchar2                     ,--输入:卡号或住院号
  PCZY000  in     number                       ,--输入:操作员编码
  PCZYXM0  in     varchar2                     ,--输入:操作员姓名
  PMZZYBZ  in     varchar2                     ,--输入:门诊住院标志 0:门诊 1:住院
  PINCS00  in     varchar2                     ,--输入:其它参数(订单流水号|交易金额)
  PCOMMIT  in     varchar2  default 'N'        ,--输入:是否提交事务 Y:提交 N:不提交
  PZXZT00  out    number                       ,--输出:执行状态 0:失败 1：成功
  PDDLSH0  out    varchar2                     ,--输出:订单流水号
  PRECODE  out    varchar2                     ,--输出:返回码 00 成功
  PERRMSG  out    varchar2                      --输出:错误信息  
) 
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- jlg        2018.03.03   created  福州市统一预交金平台冲正 (HIS5.0同名过程搬移)MZSF9-20180303-001
-- linshu     2018.03.29   增加了账户计算  by MZSF9-20180328-001
-- linshu     2018.03.29   增加了住院收费冲正 by ZYSF9-20180328-002
-- chenHeyi   2021.12.30   将日志记录返回异常的进行冲正，分为两种：无缴费记录的：生成缴费记录与票据记录（门诊，住院），日志记录写入DJH000，JKXYBM；有缴费记录的：插入一条对冲缴费记录与日志记录并且写入原日志记录的TKDDH0   MZSF9-20211231-002
-- jlg        2022.05.10   SF_PJSYQK,ZY_PJSQYK补充FZPJH0的写入   MZSF9-20220510-001
-- chenHeyi   2022.05.25   当接口类型为1013 下载冲正，且查询平台交易为交易成功，院内未生成SF_BRJFB0记录时，JFJE00取 平台交易成功的金额生成SF_BRJFB0记录，票据记录，并修改病人帐户余额；   MZSF9-20220524-002
-- chenHeyi   2022.06.06   院内已生成SF_BRJFB0记录，且查询平台交易为交易成功时，要更新日志记录状态  MZSF9-20220601-002
-- chenHeyi   2022.08.19   回传冲正(有缴费记录)，根据参数SF_YJJTKPJLX，SF_YJJPJ0取票据类型  MZSF9-20220811-003 
-- linzetao   2023.05.29   修正异常冲正缴费表(SF_BRJFB0、ZY_BRJFB0)的JKBID0错误问题    MZSF9-20230512-005
-- ---------   ----------  ------- 
as
  RZFRZ          BM_ZFRZ00%rowtype; 
  LS_CZRQ00      char(8);
  LS_CZSJ00      char(8);
  LS_JYJE00      number(12,2);
  LS_FHXX00      varchar2(1000);
  LS_CXZT00      varchar2(20);
  LS_CXZTMC      varchar2(200);
  LS_JKLXMC      varchar2(20);
  LS_DDLSH0      BM_ZFRZ00.DDLSH0%type;
  LS_TKDDH0      BM_ZFRZ00.DDLSH0%type;
  LS_TKPJH0      SF_BRJFB0.PJH000%type;
  LS_JFBZ00      SF_BRJFB0.JFBZ00%type;
  LS_PJLXBM      BM_PJLYQK.PJLXBM%type;
  LS_YJJPJ0      varchar2(100);  --预交金票据与预交金退一样
  LS_PLBHCS      varchar2(100); 
  LS_TKDJH0      BM_ZFRZ00.DJH000%type;--缴费单据号
  LS_DJH000      SF_BRJFB0.DJH000%type;
  LS_PJH000      SF_BRJFB0.PJH000%type;
  LS_ZFFS00      BM_ZFRZ00.ZFFS00%type;--支付编码
  LS_ZFFSMC      BM_ZFRZ00.ZFFSMC%type;--支付方式名称
  LS_ZPH000      SF_BRJFB0.ZPH000%type; 
  LS_ZPZH00      SF_BRJFB0.ZPZH00%type;
  LS_ZPKHYH      SF_BRJFB0.ZPKHYH%type;
  LS_ZPDWMC      SF_BRJFB0.ZPDWMC%type;
  LS_JFCZY0      SF_BRJFB0.JFCZY0%type;
  LS_CZYXM0      SF_BRJFB0.CZYXM0%type;
  
  LS_SHH000      SF_BRJFB0.SHH000%type; --商户号
  LS_ZFMXBH      BM_ZFRZ00.ZFMXBH%type;--支付明细编码=BM_ZFFSMX.ZFMXBH( A:榕医通 B:聚合支付)
  LS_JGBM00      BM_ZFRZ00.JGBM00%type;--机构编码
  LS_JGMC00      BM_ZFRZ00.JGMC00%type;--机构名称
  LS_JYLXMC      BM_ZFRZ00.JYLXMC%type;--交易类型名称
  LS_ZFYGBH      BM_TYZD00.MC0000%type;--统一预交金平台支付院内收费员对应(BM_YGBM00.YGBH00)
  LS_JYLSH0      BM_ZFRZ00.JYLSH0%type;
  LS_CZYKS0      BM_YGBM00.BMBH00%type;
  LS_PJXH00      SF_PJSYQK.PJXH00%type;
  LS_PJLYID      BM_PJLYQK.PJLYID%type;
  LS_JFLBID      BM_BRJFLB.JFLBID%type;
  LS_JKXYBM      BM_ZFRZ00.JKXYBM%type;
  LS_XZHCJE      BM_ZFRZ00.JFJE00%type;  --下载或回传金额
  LS_DQBQ00      ZY_BRXXB0.DQBQ00%type;
  LS_XJJE00      number(12,2); --现金金额
  LS_ZZJE00      number(12,2); --转账金额
  LS_YHKJE0      number(12,2); --银行卡金额
  lvJKXYBMWKSFCG BM_TYZD00.MC0000%type;  --支付平台日志表的响应编码为空时是否视为异常 0否1是;默认0 
  lvJKXYBMCGBM   BM_TYZD00.MC0000%type;  --支付平台日志表响应编码为响应成功时所对应的编码值 ; 默认为00
  LS_FHJYJE      varchar2(20);
  LD_FHJYJE      number(12,2);  
  LS_PLMC00      BM_PJLXB0.PLMC00%type;  --票类名称                  
begin
  --PJKLXBH 1000:余额查询 1001：账户查询 1002：余额下载 1003：余额回传 1004：交易查询 1005：交易明细查询 1006：交易汇总查询 
  --1007：对账查询 1008:触发回传  1009:结算回传 1010:触发下载 1011:自助机下载 1012:自助机回传  1013：下载冲正 1014:回传冲正
  PZXZT00:=0; --执行状态 0:失败 1：成功
  PDDLSH0:='';
  PRECODE:='99';--输出:返回码 00 成功
  PERRMSG:=''; 
  LS_CZRQ00:=to_char(sysdate,'YYYYMMDD');
  LS_CZSJ00:=to_char(sysdate,'HH24:MI:SS');
  LD_FHJYJE:=0;
  
  lvJKXYBMWKSFCG:=nvl(substrb(SF_SF_TYZD00('福建预交金统一平台','JKXYBMWKSFCG'),1,10),'1'); 
  lvJKXYBMCGBM:=nvl(substrb(SF_SF_TYZD00('福建预交金统一平台','JKXYBMCGBM'),1,10),'00'); 
  LS_SHH000:='榕医通';
  LS_JYLXMC:='榕医通';
  LS_ZFMXBH:='A';--支付明细编码=BM_ZFFSMX.ZFMXBH( A:榕医通 B:聚合支付)
  LS_JGMC00:='异常冲正';
  LS_JGBM00:=PJKLXBH;
  select nvl(max(trim(value0)),'Y') into LS_PLBHCS from XT_XTCS00 where name00='SF_YJJTKPJLX';
  select nvl(max(trim(value0)),'Y') into LS_YJJPJ0 from XT_XTCS00 where name00='SF_YJJPJ0';
  
  --查询BM_YGBM00--
  begin
    select trim(MC0000) into LS_ZFYGBH from BM_TYZD00 where ZDMC00='福建预交金统一平台' and BH0000='ZFYGBH';
    select YGBH00,ZWXM00,BMBH00 into LS_JFCZY0,LS_CZYXM0,LS_CZYKS0  from  BM_YGBM00 where YGBH00=LS_ZFYGBH;
  exception
    when others  then
      LS_JFCZY0:=RZFRZ.CZY000;
      LS_CZYXM0:=RZFRZ.CZYXM0;
  end; 
  if LS_JFCZY0 is null then LS_JFCZY0:=RZFRZ.CZY000; end if;
  if LS_CZYXM0 is null then LS_CZYXM0:=RZFRZ.CZYXM0; end if;
  LS_CZYKS0:=nvl(LS_CZYKS0,0);
  
  if PJKLXBH= 1004 then 
    LS_JKLXMC:='交易查询';
  elsif PJKLXBH=1013 then 
    LS_JKLXMC:='余额下载冲正';
  elsif PJKLXBH=1014 then 
    LS_JKLXMC:='余额回传冲正';
  else
     LS_JKLXMC:='异常冲正';
  end if;
 
  begin
    select ZFFSBH,ZFFSMC into LS_ZFFS00,LS_ZFFSMC from BM_BRZFFS where ZFFSBH=28; --平台支付
  exception
    when others then
    LS_ZFFS00:=28;
    LS_ZFFSMC:='平台支付';
  end;
  
  begin
    select  SF_XT_RETURNINDEXEH(trim(PINCS00),'|', 1) into LS_TKDDH0 from dual; 
    select  to_number(SF_XT_RETURNINDEXEH(trim(PINCS00),'|', 2)) into LS_JYJE00 from dual;
  exception
    when others then
    LS_JYJE00:=0;
    LS_TKDDH0:='';
  end; 
  
  if PJKLXBH in (1004,1013,1014) and LS_TKDDH0 is not null then --查询交易结果
     select SF_ONEPAY_JYJGCX(1004,PMZZYBZ,LS_TKDDH0,PINCS00) into LS_FHXX00 from  dual;
  end if;
 
  --解析返回结构  00|交易成功|02|交易成功
  begin
    select  SF_XT_RETURNINDEXEH(trim(LS_FHXX00),'|', 1) into PRECODE from dual; 
    select  SF_XT_RETURNINDEXEH(trim(LS_FHXX00),'|', 2) into PERRMSG from dual;
    select  substrb(SF_XT_RETURNINDEXEH(trim(LS_FHXX00),'|', 3),1,20) into LS_CXZT00 from dual;
    select  substrb(SF_XT_RETURNINDEXEH(trim(LS_FHXX00),'|', 4),1,200) into LS_CXZTMC from dual;
    select  substrb(SF_XT_RETURNINDEXEH(trim(LS_FHXX00),'|', 5),1,20) into LS_FHJYJE from dual;
    select  substrb(SF_SF_RETURNINDEXEH(trim(LS_FHXX00),'|', 6),1,50) into LS_JYLSH0 from dual;
    if SF_XT_ISNUMBER(LS_FHJYJE)=1 then
      LD_FHJYJE:=to_number(LS_FHJYJE);
    end if;
  exception
    when others then
      PRECODE:='99';
      PERRMSG:='查询交易结果失败!';
      LS_CXZT00:='00';--00：未知状态 01：交易处理中 02：交易成功 03：异常状态 04：交易失败 20：未找到记录 只（02，04）交易明确成功，或明确失败，进行后续业务处理
      LS_CXZTMC:='未知状态';
  end; 
  
  if PRECODE not in ('00') and LS_CXZT00 not in ('02') then 
    PRECODE:=PRECODE;--输出:返回码 00 成功
    PERRMSG:=substrb('交易结果查询返回信息：返回码='||PRECODE||' 返回信息='||PERRMSG,1,255);
    rollback;
    return;
  end if;
 
  if PJKLXBH in (1004) then
     PRECODE:=PRECODE||'|'||LS_CXZT00;
     PERRMSG:=PERRMSG||'|'||LS_CXZTMC;
     return;
  end if;
  
   
  --if  PJKLXBH in (1013,1014) and LS_CXZT00  in ('04','20') then  --冲正
    --获取订单号
  select SF_SF_PAY_DDLSH0('',PMZZYBZ) into LS_DDLSH0 from dual;
  begin
    select * into RZFRZ  from BM_ZFRZ00 where DDLSH0=LS_TKDDH0;
  exception
    when others then
    PRECODE:='99';
    PERRMSG:='未找到有效订单!';
  end;   
  
  LS_TKDJH0:=RZFRZ.DJH000;  
  LS_XZHCJE:=nvl(RZFRZ.JFJE00,0);
  LS_JKXYBM:=RZFRZ.JKXYBM;
  if lvJKXYBMWKSFCG=0 then
    if LS_JKXYBM is null or LS_JKXYBM='' then  
      LS_JKXYBM:=lvJKXYBMCGBM;
    end if;
  end if;
  --JKXYBM为响应失败且无缴费记录的
  if nvl(LS_JKXYBM,' ')<>lvJKXYBMCGBM and RZFRZ.DJH000=0 then 
    if PJKLXBH=1013 and LS_CXZT00='02' and LS_XZHCJE=0 and LD_FHJYJE<>0 then
      LS_XZHCJE:=LD_FHJYJE;
    end if;
    if PMZZYBZ='0' then    --门诊收费     
      begin
        select  PJDQH0,PJLYID into LS_PJXH00,LS_PJLYID from  BM_PJLYQK  where PJLXBM =LS_PJLXBM and SYBZ00='Y' and  PJSYR0=LS_JFCZY0;
      exception
        when others  then
          LS_PJXH00:='0';
          LS_PJLYID:=0;
      end; 
      
      if  RZFRZ.JFBZ00='0' then --缴费备注 0：交 1:退
          LS_PJLXBM:=7;
        else   --1:退
          if (trim(LS_YJJPJ0)='Y') and (trim(LS_PLBHCS)='Y') then
            LS_PJLXBM:=7;
          else
            LS_PJLXBM:=2;
          end if;
        end if;
      
        LS_JFLBID:=1;
        LS_XJJE00:=0;--现金金额
      LS_ZZJE00:=0;--转账金额
      LS_YHKJE0:=LS_XZHCJE;--银行卡金额
    
      select   SQ_SF_PJSYQK_PJH000.nextval  into  LS_PJH000   from  dual;
      select   SQ_SF_BRJFB0_DJH000.nextval  into  LS_DJH000   from  dual;
      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,PLBH00)
        values(LS_DJH000,RZFRZ.BRID00,LS_JFLBID,LS_XZHCJE,RZFRZ.ZFFS00,RZFRZ.ZFFSMC,LS_ZPH000,LS_ZPDWMC,LS_ZPZH00,LS_ZPKHYH,
               LS_PJH000,LS_CZRQ00,LS_CZSJ00,LS_JFCZY0,LS_CZYXM0,0,RZFRZ.JFBZ00,0,0,1,
               /*RZFRZ.JFJE00,*/LS_XZHCJE,LS_CZYKS0,nvl(RZFRZ.JYLSH0,LS_JYLSH0),null,LS_JYLXMC,LS_JGBM00,LS_JGMC00,RZFRZ.DDLSH0,LS_ZFMXBH,LS_SHH000,LS_PJLXBM);
      --插入SF_PJSYQK
      insert into SF_PJSYQK(PJH000,FZPJH0,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,
                      DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,CZYKS0,PJLYID,PHLYXZ)
        select LS_PJH000,LS_PJH000,PLBH00,PLMC00,LS_PJXH00,LS_XJJE00,LS_ZZJE00,LS_YHKJE0,LS_JFCZY0,LS_CZYXM0,
          LS_CZRQ00,LS_CZSJ00,'Y','0',RZFRZ.BRXM00,RZFRZ.BRXB00,RZFRZ.BRID00,0,LS_CZYKS0,LS_PJLYID,'N'
            from BM_PJLXB0 where PLBH00 =LS_PJLXBM ; --收预交金票据
      --1:修改门诊病人账户金额      
      update  SF_BRZHXX   set    XJYE00 =nvl(XJYE00,0)+nvl(LS_YHKJE0,0), 
                                   ZYE000 = nvl(ZYE000,0)+nvl(LS_YHKJE0,0),
                                   YKZYE0 = nvl(YKZYE0,0)+nvl(LS_YHKJE0,0)                               
                          where    BRID00 = PBRID00;    
    else
      --住院
      if  RZFRZ.JFBZ00='0' then --缴费备注 0：交 1:退
          LS_PJLXBM:=8;
        else   --1:退
          if trim(LS_YJJPJ0)='Y'  then
            LS_PJLXBM:=8;
          else
            LS_PJLXBM:=4;
          end if;
        end if;
        
        --票据序号--
      begin
        select  PJDQH0,PJLYID into LS_PJXH00,LS_PJLYID from  BM_PJLYQK  where PJLXBM =LS_PJLXBM and SYBZ00='Y' and  PJSYR0=LS_JFCZY0;
      exception
        when others  then
           LS_PJXH00:='0';
           LS_PJLYID:=0;
      end; 

    
        LS_JFLBID:=2;
        LS_XJJE00:=0;--现金金额
      LS_ZZJE00:=0;--转账金额
      --LS_YHKJE0:=RZFRZ.JFJE00;--银行卡金额
      LS_YHKJE0:=LS_XZHCJE;--银行卡金额
       
        select DQBQ00 into LS_DQBQ00 from  ZY_BRXXB0 where ZYID00=RZFRZ.ZYGHID;
        
      select   SQ_ZY_PJSYQK_PJH000.nextval  into  LS_PJH000   from  dual;
      select   SQ_ZY_BRJFB0_DJH000.nextval  into  LS_DJH000   from  dual;
      --插入ZY_BRJFB0
      insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPKHYH,ZPDWMC,PJH000,
                              JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,JFYY00,
                              JFSHR0,JFSHSJ,ZYID00,DQBQ00,CZYKS0,JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,
                              DDLSH0,ZFMXBH,SHH000,PLBH00)
          values(LS_DJH000,RZFRZ.BRID00,LS_JFLBID,LS_XZHCJE,RZFRZ.ZFFS00,RZFRZ.ZFFSMC,LS_ZPH000,LS_ZPKHYH,LS_ZPDWMC,LS_PJH000,
                  LS_CZRQ00,LS_CZSJ00,LS_JFCZY0,LS_CZYXM0,0,RZFRZ.JFBZ00,0,0,null,
                  null,null,RZFRZ.ZYGHID,LS_DQBQ00,LS_CZYKS0,nvl(RZFRZ.JYLSH0,LS_JYLSH0),null,LS_JYLXMC,LS_JGBM00,LS_JGMC00,
                  RZFRZ.DDLSH0,LS_ZFMXBH,LS_SHH000,LS_PJLXBM);
      --插入ZY_PJSYQK
      insert into ZY_PJSYQK(PJH000,FZPJH0,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,
                            DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,CZYKS0,PJLYID,ZYID00,PHLYXZ)
             select LS_PJH000,LS_PJH000,PLBH00,PLMC00,LS_PJXH00,LS_XJJE00,LS_ZZJE00,LS_YHKJE0,LS_JFCZY0,LS_CZYXM0,
                LS_CZRQ00,LS_CZSJ00,'Y','0',RZFRZ.BRXM00,RZFRZ.BRXB00,LS_CZYKS0,LS_PJLYID,RZFRZ.ZYGHID,'N'   
              from BM_PJLXB0 where PLBH00 = LS_PJLXBM;--收预交金票据
      --修改住院病人账户余额
       update  ZY_BRZHXX set XJYE00 = nvl(XJYE00,0)+nvl(LS_YHKJE0,0) ,            --病人现金余额
                             ZYE000 = nvl(ZYE000,0)+nvl(LS_YHKJE0,0),           --账户实际总余额
                             YKZYE0 = nvl(YKZYE0,0)+nvl(LS_YHKJE0,0)            --预扣后的总余额
                     where   ZYID00 = RZFRZ.ZYGHID; 
    end if;
    
  
        
    
    --修改日志表缴费单号               
    update  BM_ZFRZ00 set 
      DJH000=LS_DJH000,
      JFJE00=LS_XZHCJE,
      JYLSH0=nvl(RZFRZ.JYLSH0,LS_JYLSH0),
      JKXYBM=lvJKXYBMCGBM,
      JKXYXX='异常冲正成功',
      BZ0000=substrb(LS_FHXX00,1,200)  
      where DDLSH0=LS_TKDDH0;
      
    LS_DDLSH0:=LS_TKDDH0;
  else  
    if PJKLXBH in (1013,1014) and LS_CXZT00  in ('04','20') then  --冲正    
      if PMZZYBZ='0' then    --门诊收费   
        begin
          select JFBZ00,PJH000,JFJE00 into LS_JFBZ00,LS_TKPJH0, LS_JYJE00 from SF_BRJFB0 where DJH000=LS_TKDJH0;
        exception
          when others then
          PRECODE:='99';
          PERRMSG:='未找到有效单据号!';
        end;
      else
        begin
          select JFBZ00,PJH000,JFJE00 into LS_JFBZ00,LS_TKPJH0, LS_JYJE00 from ZY_BRJFB0 where DJH000=LS_TKDJH0;
        exception
          when others then
          PRECODE:='99';
          PERRMSG:='未找到有效单据号!';
        end;
      end if;             
   
      if LS_JFBZ00='0' then 
          LS_JFBZ00:='1';
          LS_JYJE00:=-abs(LS_JYJE00);
      else
          LS_JFBZ00:='0';
          LS_JYJE00:=abs(LS_JYJE00);
      end if;
  
      if PMZZYBZ='0' then    --门诊收费 
        if  LS_JFBZ00='0' then --缴费备注 0：交 1:退
            LS_PJLXBM:=7;
          else   --1:退
            if (trim(LS_YJJPJ0)='Y') and (trim(LS_PLBHCS)='Y') then
              LS_PJLXBM:=7;
            else
              LS_PJLXBM:=2;
            end if;
          end if;
          select  PLMC00 into LS_PLMC00 from BM_PJLXB0 where PLBH00 =LS_PJLXBM ; --收预交金票据
          
          begin
          select  PJDQH0,PJLYID into LS_PJXH00,LS_PJLYID from  BM_PJLYQK  where PJLXBM =LS_PJLXBM and SYBZ00='Y' and  PJSYR0=LS_JFCZY0;
        exception
          when others  then
            LS_PJXH00:='0';
            LS_PJLYID:=0;
        end; 
              
        select   SQ_SF_PJSYQK_PJH000.nextval  into  LS_PJH000   from  dual;
        select   SQ_SF_BRJFB0_DJH000.nextval  into  LS_DJH000   from  dual;
        --插入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,CZYKS0,
                    JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0,PLBH00)
          select LS_DJH000,BRID00,JFLBID,-JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPDWMC,ZPZH00,ZPKHYH,LS_PJH000,
                  LS_CZRQ00,LS_CZSJ00,JFCZY0,CZYXM0,BRYE00,LS_JFBZ00,0,JZDH00,WBHLID,CZYKS0,
                  JYLSH0,YKTSH0,JYLXMC,JGBM00,'异常冲正',LS_DDLSH0,PLBH00
          from  SF_BRJFB0 where DJH000=LS_TKDJH0;      
        
                 
        --插入SF_PJSYQK
        insert into SF_PJSYQK(PJH000,FZPJH0,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0,
                    DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,CZYKS0,PJLYID)
          select LS_PJH000,LS_PJH000,LS_PJLXBM,LS_PLMC00,LS_PJXH00,-XJJE00,-ZZJE00,-YHKJE0,CZY000,CZYXM0,
                      LS_CZRQ00,LS_CZSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,CZYKS0,LS_PJLYID
           from SF_PJSYQK where PJH000=LS_TKPJH0;
            
          --1:修改门诊病人账户金额 
        update  SF_BRZHXX   set    XJYE00 =nvl(XJYE00,0)+nvl(LS_JYJE00,0), 
                                   ZYE000 = nvl(ZYE000,0)+nvl(LS_JYJE00,0),
                                   YKZYE0 = nvl(YKZYE0,0)+nvl(LS_JYJE00,0)                               
                          where    BRID00 = PBRID00;    
    else                       --住院收费  
      if  LS_JFBZ00='0' then --缴费备注 0：交 1:退
          LS_PJLXBM:=8;
        else   --1:退
          if trim(LS_YJJPJ0)='Y'  then
            LS_PJLXBM:=8;
          else
            LS_PJLXBM:=4;
          end if;
        end if; 
        select  PLMC00 into LS_PLMC00 from BM_PJLXB0 where PLBH00 =LS_PJLXBM ; --收预交金票据
        
        begin
        select  PJDQH0,PJLYID into LS_PJXH00,LS_PJLYID from  BM_PJLYQK  where PJLXBM =LS_PJLXBM and SYBZ00='Y' and  PJSYR0=LS_JFCZY0;
      exception
          when others  then
            LS_PJXH00:='0';
            LS_PJLYID:=0;
      end;   
      select   SQ_ZY_PJSYQK_PJH000.nextval  into  LS_PJH000   from  dual;
      select   SQ_ZY_BRJFB0_DJH000.nextval  into  LS_DJH000   from  dual;
      --插入ZY_BRJFB0
      insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPDWMC,ZPZH00,ZPKHYH,PJH000,
                  JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,CZYKS0,
                  JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,DDLSH0,PLBH00,ZYID00,DQBQ00,YYID00)
      select LS_DJH000,BRID00,JFLBID,-JFJE00,ZFFS00,ZFFSMC,ZPH000,ZPDWMC,ZPZH00,ZPKHYH,LS_PJH000,
              LS_CZRQ00,LS_CZSJ00,JFCZY0,CZYXM0,BRYE00,LS_JFBZ00,0,JZDH00,CZYKS0,
              JYLSH0,YKTSH0,JYLXMC,JGBM00,'异常冲正',LS_DDLSH0,8,RZFRZ.ZYGHID,DQBQ00,YYID00
      from  ZY_BRJFB0 where DJH000=LS_TKDJH0;      
                
      --插入SF_PJSYQK  
      insert into ZY_PJSYQK(PJH000,FZPJH0,PLBH00,PLMC00,PJXH00,HJJE00,XJJE00,ZZJE00,YHKJE0,ZFJE00,JMJE00,YHJE00,CZY000,CZYXM0,
                   DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,ZYID00,DZQBJE,CZYKS0,PJLYID)
        select LS_PJH000,LS_PJH000,LS_PJLXBM,LS_PLMC00,LS_PJXH00,-HJJE00,0,0,-YHKJE0,0,0,0,CZY000,CZYXM0,
                   LS_CZRQ00,LS_CZSJ00,SFDY00,PJZT00,XM0000,XB0000,RZFRZ.ZYGHID,DZQBJE,CZYKS0,LS_PJLYID
          from ZY_PJSYQK where PJH000=LS_TKPJH0;  
      
        --修改住院病人账户余额
       update  ZY_BRZHXX set XJYE00 = nvl(XJYE00,0)+nvl(LS_JYJE00,0) ,            --病人现金余额
                             ZYE000 = nvl(ZYE000,0)+nvl(LS_JYJE00,0),           --账户实际总余额
                             YKZYE0 = nvl(YKZYE0,0)+nvl(LS_JYJE00,0)            --预扣后的总余额
                     where   ZYID00 = RZFRZ.ZYGHID;  
        
    end if;              
        
   
    update BM_ZFRZ00 set TKDDH0=LS_DDLSH0,TKJE00=nvl(TKJE00,0)+LS_JYJE00,ZXZTBZ='3'  where DDLSH0=LS_TKDDH0;
         
    --插入日志主表
    insert into BM_ZFRZ00
    (
     DDLSH0,--订单流水号
     CZRQ00,--操作日期
     CZSJ00,--操作时间
     CZY000,--操作员编码
     CZYXM0,--操作员姓名
     JKLXBH,--接口类型编号
     JKLXMC,--接口类型名称
     ZDBH00,--终端编号(自助机)
     BRID00,--病人ID
     BRXM00,--病人姓名
     BRXB00,--病人性别
     YBKH00,--医保卡号
     YBID00,--医保ID
     ICKH00,--IC卡号
     BRZJBH,--证件编号
     BRDH00,--病人电话
     MZZYBZ,--门诊住院标志 0:门诊 1:住院
     JFBZ00,--缴费备注0：交,1：退
     ZYGHID,--住院(ZYID00)或门诊(GHID000)流水号
     ZYHGHH,--住院(ZYH000)或门诊(GHH000)号
     JZDH00,--结账单号
     YJDH00,--医技单号
     ZFFS00,--支付编码
     ZFFSMC,--支付方式名称
     DJH000,--单据流水号=SF_BRJFB0.DJH000
     JFJE00,--缴费金额
     JFRQ00,--缴费日期
     JFSJ00,--缴费时间
     ZXZTBZ,--状态标志 0：失败 1：成功
     BRYE00,--病人余额
     BZ0000,--备注信息
     PTDDH0,--支付平台充值订单号=chargeNo,退款订单号=refundNo
     TKDDH0,--商户退款订单号
     TKJE00,--退款金额
     ZFQD00,--支付渠道=tradeMode
     JYCGSJ,--交易成功时间(yyyyMMddHHmmss)=traceTime
     PTGZH0,--支付平台跟踪号=traceNo
     JKXYBM,--响应编码=respCode
     JKXYXX,--响应信息=respMsg
     JYLSH0,--交易流水号
     YKTSH0,--健康通流水号
     JYLXMC,--交易类型名称
     JGBM00,--机构编码
     JGMC00,--机构名称
     INCS00,--请求入参集合=param
     INQTCS,--扩展入参集合=extenalMap
     OUTCS0--返回参数
     )values
    (
     LS_DDLSH0,--订单流水号
     LS_CZRQ00,--操作日期
     LS_CZSJ00,--操作时间
     nvl(PCZY000,RZFRZ.CZY000),--操作员编码
     nvl(PCZYXM0,RZFRZ.CZYXM0),--操作员姓名
     PJKLXBH,--接口类型编号 100:微信支付 101：支付宝支付
     LS_JKLXMC,--接口类型名称
     '',--终端编号(自助机)
     RZFRZ.BRID00,--病人ID
     trim(RZFRZ.BRXM00),--病人姓名
     RZFRZ.BRXB00,--病人性别
     RZFRZ.YBKH00,--医保卡号
     RZFRZ.YBID00,--医保ID
     RZFRZ.ICKH00,--IC卡号
     RZFRZ.BRZJBH,--证件编号
     RZFRZ.BRDH00,--病人电话
     RZFRZ.MZZYBZ,--门诊住院标志 0:门诊 1:住院
     LS_JFBZ00,--缴费备注0：交,1：退
     RZFRZ.ZYGHID,--住院(ZYID00)或门诊(GHID000)流水号
     RZFRZ.ZYHGHH,--住院(ZYH000)或门诊(GHH000)号
     RZFRZ.JZDH00,--结账单号
     RZFRZ.YJDH00,--医技单号
     RZFRZ.ZFFS00,--支付编码
     RZFRZ.ZFFSMC,--支付方式名称
     LS_DJH000,--单据流水号=SF_BRJFB0.DJH000
     LS_JYJE00,--缴费金额
     LS_CZRQ00,--缴费日期
     LS_CZSJ00,--缴费时间
     '1',--状态标志 0：失败 1：成功
     0,--病人余额
     null,--备注信息
     null,--支付平台充值订单号=chargeNo,退款订单号=refundNo
     LS_TKDDH0,--商户退款订单号=outRefundNo
     0,
     null,--支付渠道=tradeMode
     null,--交易成功时间(yyyyMMddHHmmss)=traceTime
     null,--支付平台跟踪号=traceNo
     lvJKXYBMCGBM,--响应编码=respCode
     '冲正成功',--响应信息=respMsg
     RZFRZ.JYLSH0,--交易流水号
     RZFRZ.YKTSH0,--健康通流水号
     RZFRZ.JYLXMC,--交易类型名称
     RZFRZ.JGBM00,--机构编码
     '异常冲正',--机构名称
     null,--请求入参集合
     PINCS00,--扩展入参集合
     null--返回参数
     );
    end if; 
    --院内已生成SF_BRJFB0记录，且查询平台交易为交易成功时，要更新日志记录状态  MZSF9-20220601-002
    if PJKLXBH in (1013,1014) and LS_CXZT00  in ('02') then  --冲正
      --修改日志表缴费单号               
      update  BM_ZFRZ00 set 
        JKXYBM=lvJKXYBMCGBM,
        JKXYXX='异常冲正成功',
        BZ0000=substrb(LS_FHXX00,1,200)  
        where DDLSH0=LS_TKDDH0;
      
      LS_DDLSH0:=LS_TKDDH0;    
    end if; 
  end if;   
  PZXZT00:=1; --执行状态 0:失败 1：成功 
  PRECODE:='00';--输出:返回码 00 成功
  PERRMSG:='冲正成功';
  PDDLSH0:=LS_DDLSH0;--输出:订单流水号
 
exception
  when no_data_found then
    PZXZT00:=0;
    PERRMSG:=substrb('执行过程SP_ONEPAY_YJJYCCZ错误!'||sqlerrm,1,200);
    rollback;
    return;
  when others   then
    PZXZT00:=0;
    PERRMSG:=substrb('执行过程SP_ONEPAY_YJJYCCZ错误!'||sqlerrm,1,200); 
    rollback;
   return;  
end;
