CREATE OR REPLACE PROCEDURE SP_SST_YJJ_ZHCZ_YKT( P_YYJGDM in varchar2, --医院机构代码 P_CARDNO in varchar2, --卡号(当交易类型为1时，为卡号；当交易类型为2时，为住院号) P_CARDTYPE in varchar2, --0为院内卡、1为社保卡 P_CZJE00 in number, --充值金额 P_JYLSH0 in varchar2, --交易流水号 P_JYLX00 in varchar2, --交易类型 1.现金 2银行-->(1.门诊 2住院 ) P_YHLSH0 in varchar2, --银行流水号 P_ZDBH00 in varchar2, --终端编号 P_RESULT out varchar2, --返还状态 1:成功 0:失败 P_ERRMSG out varchar2 --错误信息，NULL表示正确 ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangyc 2012.04.27 create -- zhangyc 2013.12.02 增加交易流水号校验 --zhangyc 2014.01.01 重新整理 --zhangyc 2014.03.07 增加控制住院病人不允许充值 --zhangyc 2015.05.11 增加住院现金充值 by ZZJJK-20150508-001 --zhangyc 2015.06.02 by ZZJJK-20150602-001 --zhangyc 2015.10.20 健康通改造 --zhangyc 2015.12.15 增加LS_CZLB00=3 一卡通往院内充值处理 --zhangyc 2015.12.18 表SST_YJJ_ZHCZ增加字段XTGZH0 --zhangyc 2016.02.17 增加出参p_Result 1:成功 0:失败 --zhangyc 2017.10.16 增加微信或支付宝支付 --zhangyc 2018.03.15 增加：CZLB00（7聚合支付） --zhangyc 2018.04.24 微信和支付宝修改 --zhangyc 2018.08.10 SF_POSLSZ 增加写入DDLSH0 --zhangyc 2019.03.15 扩展JYLSH0至50长度以及增加参数控制是否插入日志表BM_ZFRZ00 --zhangyc 2020.06.19 增加参数SST_MZZZJCZCKTK_MZ控制是否可以原路退款 by ZZJJK-20200624-002 --zhangyc 2020.11.16 ZFFHLS对应YKTSH0、ZFQQLS对应JYLSH0 by ZZJJK-20201116-001 --zhangyc 2021.03.26 判断入参数P_JYLSH0 不为空当做院内ddlsh0 by ZZJJK-20210329-001 --zhangyc 2021.12.15 获取SST_YJJ_ZHCZ.PAYWAY写入到BM_ZFRZ00.PAYWAY by ZZJJK-20211216-001 LS_BRID00 bm_brxxb0.brid00%type; --病人ID LS_ZT0000 IC_YHXX00.ZT0000%type; --IC卡状态 LS_YGBH00 BM_YGBM00.YGBH00%TYPE; --操作员编号 LS_YGXM00 BM_YGBM00.ZWXM00%TYPE; --操作员姓名 LS_CZYKS0 BM_YGBM00.BMBH00%TYPE; -- LS_TYJE00 SF_BRZHXX.TYJE00%TYPE; --账户停用金额 LS_SFDJ00 SF_BRZHXX.SFDJ00%TYPE; --账户冻结标志 LS_JFLBMC BM_BRJFLB.JFLBMC%TYPE; --账户类别名称 LS_XJJE00 SF_BRZHXX.XJYE00%TYPE; --现金金额 LS_ZZJE00 SF_BRZHXX.ZZYE00%TYPE; --转账金额 LS_YHKJE0 number(12,2); LS_ZYE000 SF_BRZHXX.XJYE00%TYPE; --总余额 LS_ZFFSMC BM_BRZFFS.ZFFSMC%TYPE; --支付方式 LS_YHMSG0 varchar2(400); LS_SYSMSG varchar2(400); LS_MsgStr varchar2(100); LS_CARDNO varchar2(220); LS_ICKQZMC VARCHAR2(50); LS_BRXM00 BM_BRXXB0.BRXM00%type; LS_YBKH00 BM_BRXXB0.YBKH00%type; LS_YBID00 BM_BRXXB0.YBID00%type; LS_BRZJBH BM_BRXXB0.BRZJBH%type; LS_BRDH00 BM_BRXXB0.BRDH00%type; LS_CZRQ00 varchar2(8); LS_CZSJ00 varchar2(8); LS_CARNUM number(5); --卡号位数 LS_COUNT0 number(5); LS_DJH000 SF_BRJFB0.DJH000%type; LS_PJH000 SF_BRJFB0.PJH000%type; LS_ZPH000 SF_BRJFB0.ZPH000%type; LS_ZPZH00 SF_BRJFB0.ZPH000%type; LS_ZPKHYH SF_BRJFB0.ZPKHYH%type; LS_ZPDWMC SF_BRJFB0.ZPDWMC%type; LS_ZFFSBH SF_BRJFB0.ZFFS00%type; LS_BRXB00 BM_BRXXB0.BRXB00%type; LS_PJXH00 SF_PJSYQK.PJXH00%type; LS_CZLX00 varchar2(10); --充值类型 LS_ZYID00 ZY_BRXXB0.ZYID00%type; LS_ZYH000 ZY_BRXXB0.ZYH000%type; LS_BMBH00 BM_YGBM00.BMBH00%type; LS_DQBQ00 ZY_BRXXB0.DQBQ00%type; LS_PJLYID BM_PJLYQK.PJLYID%type; LS_ID0000 SF_YKTKFB.ID0000%type; LS_JFBZ00 varchar2(1); --缴费备注 0:交 1:退 LS_JFLBID number(2); LS_JFJE00 number(12,2); LS_JYLX00 varchar2(20);--交易类型 1.门诊 2住院 LS_CZLB00 varchar2(20);--充值类型 1 现金 2 银行卡 3 一卡通支付充值 4 微信支付 5 支付宝支付[新增微信、支付宝、公众号充值渠道] 6 微信公众号 LS_CZQX00 varchar2(20);--充值去向 1.预交金 2 一卡通 3:一卡通往院内充值 LS_YKTSH0 SF_BRJFB0.YKTSH0%type; LS_YKTLSH SST_YJJ_ZHCZ.YKTLSH%type; LS_SSTLSH SST_YJJ_ZHCZ.SSTLSH%type; --银行流水变量 LS_XTGZH0 SF_POSLSZ.XTGZH0%type; --票据流水号 P_SKLSH0 SF_POSLSZ.SKLSH0%type;--刷卡MIS流水号 P_SKZDH0 SF_POSLSZ.SKZDH0%type;--刷卡原交易终端流水 P_SKRQ00 SF_POSLSZ.SKRQ00%type;--刷卡原交易日期 P_SKCKH0 SF_POSLSZ.SKCKH0%type;--刷卡原交易参考号 P_SKQS00 SF_POSLSZ.SKQS00%type;--刷卡期数 P_KTH000 SF_POSLSZ.KTH000%type;--柜台号 P_MISLSH SF_POSLSZ.MISLSH%type;--POS.MIS流水号 P_KLX000 SF_POSLSZ.KLX000%type;--POS.卡类型 P_FKHMC0 SF_POSLSZ.FKHMC0%type;--POS.发卡行名称 P_JYMC00 SF_POSLSZ.JYMC00%type;--POS.交易名称 P_XTCKH0 SF_POSLSZ.XTCKH0%type;--POS.系统参考号 P_SQM000 SF_POSLSZ.SQM000%type;--POS.授权码 P_ZDH000 SF_POSLSZ.ZDH000%type;--POS.终端号 P_KHH000 SF_POSLSZ.KHH000%type;--POS.客户号 P_ZDLSH0 SF_POSLSZ.ZDLSH0%type;--POS.终端流水号 P_ZDPCH0 SF_POSLSZ.ZDPCH0%type;--POS.终端批次号 P_ICKXLH SF_POSLSZ.ICKXLH%type;--POS.IC卡序列号 P_PBHKH0 SF_POSLSZ.PBHKH0%type;--POS.屏蔽后的卡号 P_YZDLSH SF_POSLSZ.YZDLSH%type;--POS.原交易终端流水 P_YJYRQ0 SF_POSLSZ.YJYRQ0%type;--POS.原交易日期 P_YXTCKH SF_POSLSZ.YXTCKH%type;--POS.原交易参考号 P_LJJG00 SF_POSLSZ.LJJG00%type;--POS.联机结算结果 0－帐不平 1－帐平 P_XFJFJE SF_POSLSZ.XFJFJE%type;--POS.消费积分金额 P_XJJE00 SF_POSLSZ.XJYE00%type;--POS.现金净额 P_FQQS00 SF_POSLSZ.FQQS00%type;--POS.期数 P_CWSM00 SF_POSLSZ.CWSM00%type;--POS.错误说明 P_ZZJLSH SF_POSLSZ.ZZJLSH%type;--自助机pos流水号 P_JYRQ00 SF_POSLSZ.JYRQ00%type; P_JYSJ00 SF_POSLSZ.JYSJ00%type; LS_JSGZH0 varchar2(32); --结算系统跟踪号 LS_JGBM00 varchar2(30); --机构编码 LS_JGMC00 varchar2(50); --机构名称 LS_YKTYE0 number(12,2); --健康通余额 LS_JYLXMC varchar2(50); --交易类型名称 LS_JYLSH0 SF_BRJFB0.JYLSH0%type;--交易流水号-- varchar2(30); LS_KFZT00 varchar2(1); --扣费状态 LS_MKDM00 varchar2(1); --模块代码 LS_MKDMMC varchar2(30);--模块名称 LS_MSTLX0 varchar2(2); --交易类型 LS_JMYHKH SST_YJJ_ZHCZ.JMYHKH%type;--加密后的银行卡号 LS_ZFQQLS SST_YJJ_ZHCZ.ZFQQLS%type;--支付平台请求流水 LS_ZFFHLS SST_YJJ_ZHCZ.ZFFHLS%type; --支付平台返回流水 LS_ZFQDBM BM_ZFRZ00.ZFQDBM%type;--支付渠道编码 LS_JKLXBH BM_ZFRZ00.JKLXBH%type;--接口编码 LS_JKLXMC BM_ZFRZ00.JKLXMC%type;--接口名称 LS_MZZYBZ BM_ZFRZ00.JKLXMC%type;--门诊住院标志 0:门诊 1:住院 LS_ZYGHID BM_ZFRZ00.ZYGHID%type;--住院(ZYID00)或门诊(GHID000)流水号 LS_ZYHGHH BM_ZFRZ00.ZYHGHH%type;--住院(ZYH000)或门诊(GHH000)号 LS_JZDH00 BM_ZFRZ00.JZDH00%type;--结账单号 LS_YJDH00 BM_ZFRZ00.YJDH00%type;--医技单号 LS_ZXZTBZ BM_ZFRZ00.ZXZTBZ%type;--状态标志 0：失败 1：成功 2:缴费成功 LS_ZFQD00 BM_ZFRZ00.ZFQD00%type;--支付渠道=tradeMode LS_JYCGSJ BM_ZFRZ00.JYCGSJ%type;--交易成功时间(yyyyMMddHHmmss)=traceTime LS_PTGZH0 BM_ZFRZ00.PTGZH0%type;--支付平台跟踪号=traceNo LS_JKXYBM BM_ZFRZ00.JKXYBM%type;--响应编码=respCode true:成功 false:失败 LS_JKXYXX BM_ZFRZ00.JKXYXX%type;--响应信息=respMsg LS_SHH000 SF_BRJFB0.SHH000%type;--商户号 LS_DDLSH0 SF_BRJFB0.DDLSH0%type;--订单流水号 LS_ZFMXBH SF_BRJFB0.ZFMXBH%type;--支付明细编码=BM_ZFFSMX.ZFMXBH LS_PHLYXZ SF_PJSYQK.PHLYXZ%type;--票据领用限制 N:不限制 LS_YJJCZRZ XT_XTCS00.VALUE0%type; --自助机充值(SP_SST_YJJ_ZHCZ_YKT)是否插入日志表BM_ZFRZ00 Y:插入 N:不插入 默认值:N LS_CKTKCS varchar2(100);--门诊自助机充值支持退款退款支付方式编码多值用,隔开 默认值：0 LS_ZFRZZT varchar2(10);--支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 LS_PAYWAY BM_ZFRZ00.PAYWAY%type;-- begin P_RESULT:='0'; --1:成功 0:失败 LS_JGBM00:=''; --机构编码 LS_JGMC00:='易联众自助机'; --机构名称 LS_YKTYE0:=0; LS_CZRQ00:=to_char(sysdate,'YYYYMMDD'); LS_CZSJ00:=to_char(sysdate,'HH24:MI:ss'); LS_XJJE00:=0; LS_ZZJE00:=0; LS_YHKJE0:=0; LS_ZPH000:=null; LS_ZPKHYH:=null; LS_ZPDWMC:=null; LS_CZLX00:='1';--1.现金 2银行 LS_JFBZ00:='0';--交费备注 '0'交 1:退 LS_COUNT0:=0; P_SKLSH0:='';--刷卡MIS流水号 P_SKZDH0:='';--刷卡原交易终端流水 P_SKRQ00:='';--刷卡原交易日期 P_SKCKH0:='';--刷卡原交易参考号 P_SKQS00:='';--刷卡期数 P_KTH000:=P_YYJGDM;--柜台号 P_LJJG00:='';--POS.联机结算结果 0－帐不平 1－帐平 P_XFJFJE:=0;--POS.消费积分金额 P_XJJE00:=0;--POS.现金净额 P_FQQS00:='';--POS.期数 P_CWSM00:='';--POS.错误说明 P_ZZJLSH:=P_JYLSH0;--自助机pos流水号 LS_ZFQDBM:='ZZJ';--支付渠道编码 LS_SHH000:='自助机充值';--商户号 LS_PHLYXZ:='N';--票据领用限制 N:不限制 LS_MZZYBZ:='0';--门诊住院标志 0:门诊 1:住院 LS_JZDH00:=0;--结账单号 LS_YJDH00:=0;--医技单号 LS_ZFRZZT:='0';--支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 LS_ZXZTBZ:='0';--返还状态 1:成功 0:失败 LS_JKXYBM:='true';--响应编码=respCode true:成功 false:失败 LS_JKXYXX:='交易成功';--响应信息=respMsg LS_DDLSH0:=null;--获取订单流水号 LS_JGBM00:='00'; LS_ZYGHID:=0;--住院(ZYID00)或门诊(GHID000)流水号 LS_ZYHGHH:=null;--住院(ZYH000)或门诊(GHH000)号 LS_YKTSH0:=null;--渠道流水号=第三方平台（微信、支付宝流水号） --自助机充值(SP_SST_YJJ_ZHCZ_YKT)是否插入日志表BM_ZFRZ00 Y:插入 N:不插入 默认值:N begin select trim(value0) into LS_YJJCZRZ from XT_XTCS00 where name00='SST_ICKQZMC'; exception when others then LS_YJJCZRZ:='N'; end; LS_YJJCZRZ:=nvl(LS_YJJCZRZ,'N'); --SST_MZZZJCZCKTK_MZ 门诊自助机充值支持退款退款支付方式编码多值用,隔开 默认值：0 begin select substrb(trim(VALUE0),1,100) into LS_CKTKCS from XT_XTCS00 where NAME00 = 'SST_MZZZJCZCKTK_MZ'; exception when others then LS_CKTKCS:='0'; end; LS_MsgStr:='从中间表SST_YJJ_ZHCZ获取数据JYLSH0='||P_JYLSH0; begin select JYLX00,CZLB00,CZQX00,YKTLSH,SSTLSH,XTGZH0,nvl(YKTYE0,0)YKTYE0, YHLSH0,ZDLSH0,FKHDM0,FKHMC0,YHKH00, YHKLX0,YHSHH0,YHZDH0,JYRQ00,JYSJ00, JYCKH0,YHSQH0,YHPCH0,ICKXLH,YZDLSH, YJYRQ0,YJYCKH,substrb(JMYHKH,1,30)JMYHKH,substrb(ZFQQLS,1,50)ZFQQLS,substrb(ZFFHLS,1,50)ZFFHLS,substrb(ZFQDLS,1,30), PAYWAY into LS_JYLX00,LS_CZLB00,LS_CZQX00,LS_YKTLSH,LS_SSTLSH,LS_JSGZH0,LS_YKTYE0, P_MISLSH,P_ZDLSH0,P_JYMC00,P_FKHMC0,P_PBHKH0, P_KLX000,P_KHH000,P_ZDH000,P_JYRQ00,P_JYSJ00, P_XTCKH0,P_SQM000,P_ZDPCH0,P_ICKXLH,P_YZDLSH, P_YJYRQ0,P_YXTCKH,LS_JMYHKH,LS_ZFQQLS,LS_ZFFHLS,LS_YKTSH0,LS_PAYWAY from SST_YJJ_ZHCZ where JYLSH0=P_JYLSH0; exception when others then LS_JYLX00:='1'; LS_CZLB00:='1'; LS_CZQX00:='1'; end; LS_JYLX00:=nvl(LS_JYLX00,'1'); --交易类型 1.门诊 2住院 LS_CZLB00:=nvl(LS_CZLB00,'2'); --充值类型 1 现金 2 银行卡 3:一卡通往院内充值 4 微信支付 5 支付宝支付[新增微信、支付宝、公众号充值渠道] 6 微信公众号 LS_CZQX00:=nvl(LS_CZQX00,'1'); --充值去向 1.预交金 2 一卡通 LS_ZPH000:=P_JYLSH0; LS_ZPKHYH:=LS_YKTSH0; LS_JYLX00:=nvl(P_JYLX00,'1');--交易类型有前台入参传入 LS_JKLXBH:=0; LS_ZFQD00:=substrb(LS_ZFFHLS,1,50); LS_MsgStr:='自助机对应获取操作员编号和姓名:SST_YGBHDY'; begin select YGBH00,ZWXM00,BMBH00 into LS_YGBH00,LS_YGXM00,ls_CZYKS0 from VW_SST_YGBMDYXX where ZZJBH0=P_ZDBH00; exception when NO_DATA_FOUND then P_ERRMSG:='终端编号:'||P_ZDBH00||'在本院SST_YGBHDY表中未对应收费员!'; return; end; if P_CZJE00<=0 then P_ERRMSG:='充值金额错误，充值金额不能为零或负数!'; return; end if; LS_JFJE00:=P_CZJE00; LS_JFLBID:=1; LS_MsgStr:='获取院内卡号(SF_SST_GETICKH00_YKT)'; select SF_SST_GETICKH00_YKT(P_CARDNO) into LS_CARDNO from dual; LS_MsgStr:='获取该卡的病人的基本信息'; begin select a.BRXM00,a.BRXB00,b.BRID00,b.ZT0000,a.YBKH00,a.YBID00,A.BRZJBH,A.BRDH00 into LS_BRXM00,LS_BRXB00,LS_BRID00,LS_ZT0000,LS_YBKH00,LS_YBID00,LS_BRZJBH,LS_BRDH00 from bm_brxxb0 a, IC_YHXX00 b where a.brid00=b.brid00 and ICKH00 =LS_CARDNO and ZT0000=1 and rownum=1; exception when no_data_found then P_ERRMSG:='IC卡号:'||LS_CARDNO||'在本院IC_YHXX00表中未找到!'; return; end; select nvl(SFDJ00,0) into LS_SFDJ00 from SF_BRZHXX where BRID00=LS_BRID00 and JFLBID=LS_JFLBID; if LS_SFDJ00<>0 then P_ERRMSG:='该病人在本院的账户已被冻结或停用,请联系管理员!'; return; end if; LS_MsgStr:='获取自助机对应收费员的票据序号'; begin select PJDQH0,PJLYID into LS_PJXH00,LS_PJLYID from BM_PJLYQK where PJLXBM = 7 and SYBZ00='Y' and PJSYR0=LS_YGBH00; exception when others then LS_PJXH00:='0'; LS_PJLYID:=0; end; LS_MsgStr:='校验交易流水号是否存在'; select count(1)into LS_CarNum from SF_BRJFB0 where BRID00=LS_BRID00 and JYLSH0=P_JYLSH0 and JFCZY0 in (select YGBH00 from VW_SST_YGBMDYXX); if LS_CarNum>=1 then P_ERRMSG:='该交易流水号:'||P_JYLSH0||'已经存在无法充值!'; return; end if; --支付明细编码H:现金I:转账J:POS离线K:POS刷卡L:龙支付M:银联二维码D:微信离线E:微信扫码F:支付宝离线G:支付宝扫码A:榕医通B:聚合支付C:健康通N:银医通P:一卡通 if LS_CZLB00='1' then --现金 LS_XJJE00:=LS_JFJE00; LS_ZZJE00:=0; LS_YHKJE0:=0; LS_ZFFSBH:=1; LS_ZPDWMC:=''; LS_JYLXMC:='自助机现金充值'; LS_ZPH000:=P_JYLSH0;--交易流水号 LS_ZPZH00:=P_ZDBH00;--终端编码 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='现金'; LS_ZFMXBH:='H'; --支付明细编码=BM_ZFFSMX.ZFMXBH elsif LS_CZLB00='2' then --银行卡 LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=8; LS_ZPDWMC:=''; LS_JYLXMC:='自助机银行卡充值'; LS_ZPH000:=P_MISLSH;--交易流水号 LS_ZPZH00:=P_ZDBH00;--终端编码 LS_ZPKHYH:=P_FKHMC0;--银行名称 LS_ZPDWMC:=P_PBHKH0;--银行卡号 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='银行卡'; LS_ZFMXBH:='K'; --支付明细编码=BM_ZFFSMX.ZFMXBH elsif LS_CZLB00='3' then --3一卡通支付充值 LS_XJJE00:=LS_JFJE00; LS_ZZJE00:=0; LS_YHKJE0:=0; LS_ZFFSBH:=26; LS_JYLXMC:='健康通自助机消费支付'; LS_ZPDWMC:='健康通自助机消费支付'; LS_JGBM00:=LS_CZLB00; LS_JGMC00:='健康通充值'; LS_ZFMXBH:='P'; --支付明细编码=BM_ZFFSMX.ZFMXBH elsif LS_CZLB00='4' then --微信支付 LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=24; LS_ZPDWMC:=''; LS_JYLXMC:='自助机微信支付'; LS_ZPH000:=substrb(LS_ZFQQLS,1,50);--支付平台请求流水微信/支付宝/公众号 LS_ZPZH00:=P_ZDBH00;--终端编码 LS_ZPKHYH:='';--银行名称 LS_ZPDWMC:=substrb(LS_ZFFHLS,1,50);--交易流水号 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='微信支付'; LS_ZFMXBH:='E'; --支付明细编码=BM_ZFFSMX.ZFMXBH elsif LS_CZLB00='5' then --支付宝支付 LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=25; LS_ZPDWMC:=''; LS_JYLXMC:='自助机支付宝支付'; LS_ZPH000:=substrb(LS_ZFQQLS,1,50);--支付平台请求流水微信/支付宝/公众号 LS_ZPZH00:=P_ZDBH00;--终端编码 LS_ZPKHYH:='';--银行名称 LS_ZPDWMC:=substrb(LS_ZFFHLS,1,50);--银行卡号 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='支付宝支付'; LS_ZFMXBH:='G'; --支付明细编码=BM_ZFFSMX.ZFMXBH elsif LS_CZLB00='6' then --微信公众号 LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=24; LS_ZPDWMC:=''; LS_JYLXMC:='微信公众号'; LS_ZPH000:=substrb(LS_ZFQQLS,1,50);--支付平台请求流水微信/支付宝/公众号 LS_ZPZH00:=P_ZDBH00;--终端编码 LS_ZPKHYH:='';--银行名称 LS_ZPDWMC:=substrb(LS_ZFFHLS,1,50);--交易流水号 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='微信公众号'; LS_ZFMXBH:='E'; --支付明细编码=BM_ZFFSMX.ZFMXBH elsif LS_CZLB00='7' then --聚合支付 LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=28; LS_JYLXMC:='聚合支付'; LS_ZPH000:=substrb(LS_ZFQQLS,1,50);--支付平台请求流水微信/支付宝/公众号 LS_ZPZH00:=P_ZDBH00;--终端编码 LS_ZPKHYH:=LS_JMYHKH;--银行名称 LS_ZPDWMC:=substrb(LS_ZFFHLS,1,50);--交易流水号 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='聚合支付'; LS_ZFMXBH:='B'; --支付明细编码=BM_ZFFSMX.ZFMXBH else --3自助机其它充值 LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=8; LS_ZPDWMC:=''; LS_JYLXMC:='自助机其它充值'; LS_ZPH000:=P_MISLSH;--交易流水号 LS_ZPZH00:=P_ZDBH00;--终端编码 LS_ZPKHYH:=P_FKHMC0;--银行名称 LS_ZPDWMC:=P_PBHKH0;--银行卡号 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='银行卡'; LS_ZFMXBH:='K'; --支付明细编码=BM_ZFFSMX.ZFMXBH end if; LS_MsgStr:='获取订单流水号'; begin select ZFFSMC into LS_ZFFSMC from BM_BRZFFS where ZFFSBH=LS_ZFFSBH; exception when others then P_ERRMSG := '支付表(BM_BRZFFS)未找到有效的记录：ZFFSBH='||LS_ZFFSBH; return; end; LS_JKLXBH:=LS_ZFFSBH; LS_JKLXMC:=LS_ZFFSMC; --参数:0 门诊 1住院,BRID00,交费类型:1门诊 2住院 ,返回类型 0:可用余额 1:总余额 2：现金余额 3:停用金额 Select SF_SST_GetBRZHYE_YKT(0,LS_BRID00,LS_JFLBID,1) into LS_ZYE000 from dual; LS_ZYE000:=LS_ZYE000+LS_JFJE00; -------------1----begin--------------------- --充值类型 1 现金 2 银行卡 3:一卡通往院内充值 4 微信支付 5 支付宝支付[新增微信、支付宝、公众号充值渠道] 6 微信公众号 select SQ_SF_BRJFB0_DJH000.nextval into LS_DJH000 from dual; select SQ_SF_PJSYQK_PJH000.nextval into LS_PJH000 from dual; LS_MsgStr:='添加病人交费记录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,SHH000,DDLSH0,ZFMXBH) values(LS_DJH000,LS_BRID00,1,LS_JFJE00,LS_ZFFSBH,LS_ZFFSMC,LS_ZPH000,LS_ZPDWMC,LS_ZPZH00,LS_ZPKHYH,LS_PJH000, LS_CZRQ00,LS_CZSJ00,LS_YGBH00,LS_YGXM00,LS_JFBZ00,0,0,0,1,LS_ZYE000,LS_CZYKS0, LS_JYLSH0,LS_YKTSH0,LS_JYLXMC,LS_JGBM00,LS_JGMC00,LS_SHH000,LS_DDLSH0,LS_ZFMXBH); LS_MsgStr:='添加票据使用情况SF_PJSYQK'; Insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0,CZY000,CZYXM0, DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,CZYKS0,PJLYID,PHLYXZ) select LS_PJH000,PLBH00,PLMC00,LS_PJXH00,LS_XJJE00,LS_ZZJE00,LS_YHKJE0,LS_YGBH00,LS_YGXM00, LS_CZRQ00,LS_CZSJ00,'Y','0',LS_BRXM00,LS_BRXB00,LS_BRID00,0,LS_CZYKS0,LS_PJLYID,LS_PHLYXZ from BM_PJLXB0 where PLBH00 = 7 ; --收预交金票据 if LS_CZLB00='2' then --银行卡--begin-- select count(*) into LS_COUNT0 from SF_POSLSZ where BRID00=LS_BRID00 and ZZJLSH=P_JYLSH0; if LS_COUNT0>0 then P_ERRMSG := '该银行交易信息已经存在,无法继续充值！流水号:'||P_JYLSH0; return; end if; select SF_POSLSZ_XTGZH0.nextval into LS_XTGZH0 from dual; LS_MsgStr:='插入表SF_POSLSZ'; insert into SF_POSLSZ(XTGZH0,BRID00,BRXM00,CZY000,CZYXM0,DJH000,JFJE00,JFRQ00,JFSJ00,JYLX00, SKJE00,SKLSH0,SKZDH0,SKRQ00,SKCKH0,SKQS00,KTH000,JYJE00,MISLSH,JYSJ00, JYRQ00, KLX000,FKHMC0,JYMC00,XTCKH0,SQM000,ZDH000,KHH000,ZDLSH0,ZDPCH0,ICKXLH, PBHKH0,YZDLSH,YJYRQ0,YXTCKH,LJJG00,XFJFJE,XJYE00,ZT0000,CZBZ00,FQQS00, CWSM00,SFJS00,CZRQ00,CZSJ00,ZZJLSH,DDLSH0) values (LS_XTGZH0,LS_BRID00,LS_BRXM00,LS_YGBH00,LS_YGXM00,LS_DJH000, LS_JFJE00,LS_CZRQ00,LS_CZSJ00, '00', LS_JFJE00, P_SKLSH0, P_SKZDH0, P_SKRQ00, P_SKCKH0, P_SKQS00, P_KTH000, LS_JFJE00, P_MISLSH, P_JYSJ00, P_JYRQ00, P_KLX000, P_FKHMC0, P_JYMC00, P_XTCKH0, P_SQM000, P_ZDH000, P_KHH000, P_ZDLSH0, P_ZDPCH0, P_ICKXLH, P_PBHKH0, P_YZDLSH, P_YJYRQ0, P_YXTCKH, P_LJJG00, nvl(P_XFJFJE,0),nvl(P_XJJE00,0),1,1, P_FQQS00, P_CWSM00,0,LS_CZRQ00,LS_CZSJ00,P_ZZJLSH,LS_DDLSH0); end if; --银行卡--begin-- -------------1----end--------------------- --参数:0 门诊 1住院,BRID00,交费类型:1门诊 2住院 ,返回类型 0:可用余额 1:总余额 2：现金余额 3:停用金额 Select SF_SST_GetBRZHYE_YKT(0,LS_BRID00,LS_JFLBID,2) into LS_ZYE000 from dual; LS_MsgStr:='院内保留充值记录SF_SSTCZ0'; insert into SF_SSTCZ0(CARDNO, JYLSH0, ZDBH00 ,CZJE00 ,ZHYE00 , DJH000,ZFFSBH,JFRQ00,JFSJ00,BRXM00, JYRQ00,JYSJ00,FKHHH0, XTCKH0,SQH000,ZDH000,SHH000,YHLSH0, ZDPCH0,PBHKH0,YJYRQ0,YXTCKH,CWSM00, JMYHKH,ZFQQLS,ZFFHLS) values(P_CARDNO,P_JYLSH0,P_ZDBH00,P_CZJE00,ls_ZYE000, LS_DJH000,LS_ZFFSBH,LS_CZRQ00,ls_CZSJ00,ls_BRXM00, P_JYRQ00,P_JYSJ00,P_PBHKH0, P_XTCKH0,'',P_ZDH000,P_PBHKH0,P_MISLSH, P_ZDLSH0,P_PBHKH0,P_YJYRQ0,P_YXTCKH,P_SQM000, LS_JMYHKH,LS_ZFQQLS,LS_ZFFHLS); Select count(1) into LS_COUNT0 from SST_YJJ_ZHCZ where CARDNO=P_CARDNO and JYLSH0=P_JYLSH0; if nvl(LS_COUNT0,0)<=0 then insert into SST_YJJ_ZHCZ(cardno, Jylsh0, Zdbh00 ,Xming0 ,Czje00 ,Zhye00 ,CGJYSJ,Yjjye0,PJH000,PJXH00) values(P_CARDNO,P_JYLSH0,P_ZDBH00,LS_BRXM00,LS_JFJE00,LS_ZYE000,ls_CZRQ00||' '||ls_CZSJ00,LS_ZYE000,LS_PJH000,trim(ls_PJXH00)); else update SST_YJJ_ZHCZ set Xming0=LS_BRXM00,CZJE00=LS_JFJE00,ZHYE00=LS_ZYE000, Yjjye0=LS_ZYE000,CGJYSJ=LS_CZRQ00||' '||LS_CZSJ00, PJH000=LS_PJH000,PJXH00=trim(LS_PJXH00),BRID00=LS_BRID00, CZRQ00=LS_CZRQ00,CZSJ00=LS_CZSJ00,ZFFS00=LS_ZFFSBH,ZFFSMC=LS_ZFFSMC, DJH000=LS_DJH000,XTGZH0=LS_JSGZH0,JGBM00=LS_JGBM00,JGMC00=LS_JGMC00, YKTYE0=nvl(LS_YKTYE0,0),MZZYBZ='0',JYJE00=LS_JFJE00 where CARDNO=P_CARDNO and JYLSH0=P_JYLSH0; end if; LS_MsgStr:='插入表SST_YJJ_ZHCZ_LOG'; insert into SST_YJJ_ZHCZ_LOG(CARDNO,JYLSH0,ZDBH00,XMING0,CZJE00,ZHYE00,YJJYE0,CGJYSJ,PJH000,PJXH00, YHLSH0,ZDLSH0,FKHDM0,FKHMC0,YHKH00,JYJE00,JYLB00,YHKLX0,YHSHH0,YHZDH0, JYRQ00,JYSJ00,JYCKH0,YHSQH0,YHPCH0,ICKXLH,YZDLSH,YJYRQ0,YJYCKH, JYLX00,CZLB00,CZQX00,CZFS00,YKTLSH,SSTLSH,BRID00, CZRQ00,CZSJ00,ZFFS00,ZFFSMC,DJH000,XTGZH0,JGBM00,JGMC00,YKTYE0,MZZYBZ, JMYHKH,ZFQQLS,ZFFHLS,ZFQDLS) select CARDNO,JYLSH0,ZDBH00,XMING0,CZJE00,ZHYE00,YJJYE0,CGJYSJ,PJH000,PJXH00, YHLSH0,ZDLSH0,FKHDM0,FKHMC0,YHKH00,JYJE00,JYLB00,YHKLX0,YHSHH0,YHZDH0, JYRQ00,JYSJ00,JYCKH0,YHSQH0,YHPCH0,ICKXLH,YZDLSH,YJYRQ0,YJYCKH, '1',CZLB00,CZQX00,CZFS00,YKTLSH,SSTLSH,BRID00, CZRQ00,CZSJ00,ZFFS00,ZFFSMC,DJH000,XTGZH0,JGBM00,JGMC00,YKTYE0,'0', JMYHKH,ZFQQLS,ZFFHLS,ZFQDLS from SST_YJJ_ZHCZ where CARDNO=P_CARDNO and JYLSH0=P_JYLSH0; if instrb(','||LS_CKTKCS||',',','||LS_ZFFSBH||',')>0 then LS_ZFRZZT:='1';--支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 end if; if LS_ZFRZZT in ('1') then --支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 LS_ZXZTBZ:='1'; --返还状态 1:成功 0:失败 end if; --如果银行卡支持窗口退款支付明细编码=K 都在为空，避免退款判断有问他 if LS_ZFRZZT in ('1') and LS_ZFFSBH in (8) then LS_ZFMXBH:='K'; else LS_ZFMXBH:=''; end if; LS_MsgStr:='插入日志主表BM_ZFRZ00'; --插入日志主表 if LS_ZFRZZT in ('1','2') then --支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 --ZFQQLS:支付请求流水=院内订单流失号 if LS_ZFQQLS is not null and LS_ZFQQLS not in ('0') then LS_DDLSH0:=substrb(LS_ZFQQLS,1,30); LS_JYLSH0:=substrb(LS_ZFQQLS,1,50); elsif P_JYLSH0 is not null and P_JYLSH0 not in ('0') then LS_DDLSH0:=substrb(P_JYLSH0,1,30); LS_JYLSH0:=substrb(P_JYLSH0,1,50); else select SF_SF_PAY_DDLSH0('',0) into LS_DDLSH0 from dual; LS_JYLSH0:=substrb(P_JYLSH0,1,50); end if; if LS_ZFFHLS is not null and LS_ZFFHLS not in ('0') then LS_JYLSH0:=substrb(LS_ZFFHLS,1,50); LS_YKTSH0:=substrb(LS_ZFFHLS,1,50); end if; --插入中间表 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,ZFMXBH,FKTM00,JKQQCS,CZYKS0,TKDJJE,PAYWAY,JYCODE )values( LS_DDLSH0,LS_CZRQ00,LS_CZSJ00,LS_YGBH00,LS_YGXM00,12,'门诊自助机预交金充值',P_ZDBH00,LS_BRID00,trim(LS_BRXM00), LS_BRXB00,trim(LS_YBKH00),trim(LS_YBID00),trim(P_CARDNO),trim(LS_BRZJBH),trim(LS_BRDH00),LS_MZZYBZ,LS_JFBZ00,LS_ZYGHID,LS_ZYHGHH, 0,LS_DJH000,LS_ZFFSBH,LS_ZFFSMC,LS_DJH000,LS_JFJE00,LS_CZRQ00,LS_CZSJ00,LS_ZXZTBZ,LS_ZYE000, null,LS_JYLSH0,null,to_char(sysdate,'YYYYMMDD')||to_char(sysdate,'HH24MIss'),LS_JYLSH0,null,null,LS_JYLSH0,LS_YKTSH0,LS_JYLXMC, LS_JGBM00,LS_JGMC00,null,null,null,null,0,LS_TYJE00,'',LS_ZPKHYH, LS_ZPDWMC,LS_ZPH000,'',LS_ZFMXBH,'',null,LS_CZYKS0,0,LS_PAYWAY,LS_PAYWAY ); --更新缴费表 update SF_BRJFB0 set DDLSH0=LS_DDLSH0,JYLSH0=LS_JYLSH0,YKTSH0=LS_YKTSH0,ZFMXBH=LS_ZFMXBH where BRID00=LS_BRID00 and DJH000=LS_DJH000; end if; P_RESULT:='1'; --1:成功 0:失败 exception when no_data_found then P_ERRMSG:=substrb('预交金充值(SP_SST_YJJ_ZHCZ_YKT)错误:'||LS_MsgStr||' 详细信息:'||sqlerrm,1,200); P_RESULT:='0'; --1:成功 0:失败 return; when others then P_ERRMSG:=substrb('预交金充值(SP_SST_YJJ_ZHCZ_YKT)错误:'||LS_MsgStr||' 详细信息:'||sqlerrm,1,200); P_RESULT:='0'; --1:成功 0:失败 return; end; /