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;