prompt 自助机门诊预交金综合操作 SP_SST_YJJ_ZHCZ_YKT create or replace procedure SP_SST_YJJ_ZHCZ_YKT ( P_YYJGDM in varchar2, --医院机构代码 P_CARDNO in varchar2, --卡号或住院号 P_CARDTYPE in varchar2, --0为院内卡、1为社保卡 P_CZJE00 in number, --充值金额 P_JYLSH0 in varchar2, --交易流水号(1.获取HIS流水号) P_JYLX00 in varchar2, --交易类型 (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 -- qks 2014.02.25 写入SST_YJJ_ZHCZ.CGJYSJ改用SF_BRJFB0.JFRQ00||JFSJ00 -- qks 2014.04.01 增加表SF_SSTCZ0 -- qks 2014.04.29 SF_SSTCZ0增加字段BRXM00 -- qks 2014.05.19 SF_PJSYQK.FZPJH0字段值要写入 -- zhangyc 2014.12.19 -- qks 2014.12.21 增加入参P_YKTLSH -- zhangyc 2014.12.23 一卡通支付记录到流水表SF_YKTKFB -- zhangyc 2016.01.07 重新整理脚本 -- zhangyc 2016.03.31 增加住院充值功能 -- zhangyc 2016.04.11 增加入参P_ZYHAO0 -- qks 2016.05.10 一卡通充值不写入SF_PJSYQK表 -- qks 2016.12.28 LS_CZLB00='2': as_ZPZH00、as_ZPKHYH、as_ZPDWMC取值修改(省人民) -- zhangyc 2018.04.23 增加微信和支付宝 -- jlg 2018.08.14 增加银联扫码支付方式及对应明细编号ZFMXBH ZZJJK-20180814-001 -- jlg 2018.09.04 增加BM_ZFRZ00中间表的写入 ZZJJK-20180904-001 -- jlg 2018.09.05 增加参数控制是否写BM_ZFRZ00日志表数据 ZZJJK-20180905-002 -- qks 2019.12.06 LS_CZYKS0值改为 不允许为空;修改ls_YYID00取值对应的函数SF_SF_GETYYID00; for MZSF9-20191206-003 -- zhangyc 2021.04.17 原路退款功能修改 by ZZJJK9-20210420-001 -- zhangyc 2021.09.15 CZLB00=13 多码融合微信 14 多码融合支付宝（改为通用字典对照:BM_TYZD00.ZDMC00='自助机充值类型(CZLB00)院内对照'） -- chenHeyi 2023.06.20 LS_ZFRZZT=1，LS_ZFFSBH=8且LS_ZFMXBH为空时，LS_ZFMXBH才赋值K -- --------- ---------- ------- 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_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_ZYE000 SF_BRZHXX.XJYE00%type; --总余额 ls_ZFFSMC BM_BRZFFS.ZFFSMC%type; --支付方式 ls_YHMSG0 varchar2(200); ls_SYSMSG varchar2(200); E_ERROR EXCEPTION; LS_CARDNO varchar2(220); LS_ICKQZMC varchar2(50); ls_CZRQ00 varchar2(8); ls_CZSJ00 varchar2(8); LS_COUNT0 number(5); --卡号位数 LS_DJH000 SF_BRJFB0.DJH000%type; ls_PJH000 SF_BRJFB0.PJH000%type; as_ZPH000 SF_BRJFB0.ZPH000%type; as_ZPZH00 SF_BRJFB0.ZPH000%type; as_ZPKHYH SF_BRJFB0.ZPKHYH%type; as_ZPDWMC SF_BRJFB0.ZPDWMC%type; ls_ZFFSBH SF_BRJFB0.ZFFS00%type; ls_ZJE000 SF_BRJFB0.JFJE00%type; LS_CZYKS0 SF_BRJFB0.CZYKS0%type; ls_PJXH00 SF_PJSYQK.PJXH00%type; ls_PJLYID BM_PJLYQK.PJLYID%type; LS_XJYE00 SF_BRZHXX.XJYE00%type; LS_YYID00 XT_YYXX00.YYID00%type; LS_ID0000 SF_YKTKFB.ID0000%type; ls_BRXM00 BM_BRXXB0.BRXM00%type; ls_BRXB00 BM_BRXXB0.BRXB00%type; ls_MZZHID BM_BRXXB0.MZZHID%type; LS_YBKH00 BM_BRXXB0.YBKH00%type; LS_YBID00 BM_BRXXB0.YBID00%type; LS_BRDH00 BM_BRXXB0.BRDH00%type; LS_BRZJBH BM_BRXXB0.BRZJBH%type; LS_YKTLSH varchar2(50); --一卡通流水号 LS_JYLX00 varchar2(20); --交易类型 1.门诊 2住院 LS_CZLB00 varchar2(20); --充值类型 1现金 2银行卡 3一卡通支付充值 4微信支付 5支付宝支付 6微信公众号 7聚合支付 8银联扫码 LS_CZQX00 varchar2(20); --充值去向 1.预交金 2 一卡通 LS_SSTLSH varchar2(30); LS_JSGZH0 varchar2(32); --结算系统跟踪号 LS_JGBM00 varchar2(30); --机构编码 LS_JGMC00 varchar2(50); --机构名称 LS_YKTYE0 number(12,2); --健康通余额 LS_JFJE00 number(12,2); LS_YHKJE0 number(12,2); LS_JYLXMC varchar2(50); --交易类型名称 LS_JFLBID number(2); LS_JYLSH0 varchar2(50); 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_YZDLSH SF_POSLSZ.YZDLSH%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_YJYRQ0 SST_YJJ_ZHCZ.YJYRQ0%type; --POS.原交易日期 P_PBHKH0 SST_YJJ_ZHCZ.YHKH00%type; --POS.屏蔽后的卡号 P_JYRQ00 SST_YJJ_ZHCZ.JYRQ00%type; P_JYSJ00 SST_YJJ_ZHCZ.JYSJ00%type; LS_KFZT00 varchar2(1); --扣费状态 LS_MKDM00 varchar2(1); --模块代码 LS_MKDMMC varchar2(30); --模块名称 LS_MSTLX0 varchar2(2); --交易类型 LS_JMYHKH varchar2(50); --加密后的银行卡号 LS_ZFQQLS varchar2(80); --支付平台请求流水 LS_ZFFHLS varchar2(80); --支付平台返回流水 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_JFBZ00 SF_BRJFB0.JFBZ00%type; ls_ZFMXBH SF_BRJFB0.ZFMXBH%type; --支付明细编号 LS_ZYID00 ZY_BRXXB0.ZYID00%type; LS_DQBQ00 ZY_BRXXB0.DQBQ00%type; LS_MZTYZF BM_TYZD00.MC0000%type; --通用字典参数值 门诊是否启用统一支付平台 LS_MZCZLB BM_TYZD00.MC0000%type; --通用字典参数值 自助机门诊不需要原路退回的充值类别 LS_INSTR0 number; --是否包含对应充值类别 Vzxcgbz number; Vtsxx00 varchar2(200); Vxxxx00 varchar2(200); LS_MsgStr varchar2(100); LS_PAYWAY BM_ZFRZ00.PAYWAY%type; --交易渠道编码 LS_SFTK00 varchar2(20); --是否支持窗口退款 1:是 0:否 LS_ZFRZZT varchar2(10);--支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 LS_CKTKCS varchar2(100);--门诊自助机充值支持退款退款支付方式编码多值用,隔开 默认值：0 --LS_YKTSH0 BM_ZFRZ00.YKTSH0%type; LS_CZY000 BM_ZFRZ00.CZY000%type; LS_CZYXM0 BM_ZFRZ00.CZYXM0%type; LS_ZFQDLS SST_YJJ_ZHCZ.ZFQDLS%type;--渠道流水号=第三方平台(微信、支付宝流水号) LS_operator_id SST_YJJ_ZHCZ.operator_id%type;--操作员_编码 LS_operator_name SST_YJJ_ZHCZ.operator_name%type;--操作员_姓名 LS_GHID00 SF_BRXXB0.GHID00%type; --挂号ID LS_GHH000 SF_BRXXB0.GHH000%type; LS_TKDJJE BM_ZFRZ00.TKDJJE%type; --退款冻结金额 -- LS_DZZFFSBH BM_ZFFSMX.ZFFSBH%type; --对照支付方式 LS_DZZFMXBH BM_ZFFSMX.ZFMXBH%type;--对照支付明细编码 begin p_Result:='0'; --1:成功 0:失败 LS_JGBM00:=''; --机构编码 LS_JGMC00:='易联众自助机'; --机构名称 LS_CZRQ00:=to_char(sysdate,'YYYYMMDD'); LS_CZSJ00:=to_char(sysdate,'HH24:MI:ss'); LS_XJJE00:=0; LS_ZZJE00:=0; as_ZPH000:=P_JYLSH0; --交易流水号(自助机流水号) P_ZZJLSH:=P_JYLSH0; --自助机pos流水号 LS_JFBZ00:='0'; --交费标识 '0'交 1:退 LS_ZFQDBM:='ZZJ'; --支付渠道编码 LS_SHH000:='自助机充值'; --商户号 LS_MZZYBZ:='0'; --门诊住院标志 0:门诊 1:住院 LS_ZYGHID:=0; LS_JZDH00:=0; --结账单号 LS_YJDH00:=0; --医技单号 LS_ZXZTBZ:='2'; --状态标志 0：失败 1：成功 2:缴费成功 LS_JKXYBM:='00'; --响应编码=respCode true:成功 false:失败 LS_JKXYXX:='交易成功'; --响应信息=respMsg LS_MsgStr:='从中间表SST_YJJ_ZHCZ获取数据JYLSH0='||P_JYLSH0; ls_ZFMXBH:=null; -- LS_ZFRZZT:='0';--支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 LS_ZXZTBZ:='0'; --返还状态 1:成功 0:失败 LS_DDLSH0:=null;--获取订单流水号 LS_GHID00:='0'; --挂号ID LS_GHH000:='0'; LS_TYJE00:=0; --账户停用金额 --从中间表获取交易类型、充值类型、充值去向 begin select JYLSH0,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,50) JMYHKH,substrb(ZFQQLS,1,50) ZFQQLS,substrb(ZFFHLS,1,50) ZFFHLS , PAYWAY as PAYWAY,SFTK00 as SFTK00,ZFQDLS,operator_id,operator_name into LS_JYLSH0,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_PAYWAY,LS_SFTK00,LS_ZFQDLS,LS_operator_id,LS_operator_name 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:一卡通往院内充值 LS_CZQX00:=nvl(LS_CZQX00,'1'); --充值去向 1.预交金 2 一卡通 as_ZPH000:=LS_JYLSH0; as_ZPKHYH:=LS_YKTLSH; --交易类型有前台入参传入 LS_JYLX00:=nvl(P_JYLX00,'1'); as_ZPDWMC:=null; --自助机对应获取操作员编号和姓名:SST_YGBHDY LS_MsgStr:='自助机对应获取操作员编号和姓名:SST_YGBHDY'; begin select YGBH00,ZWXM00 into ls_YGBH00,ls_YGXM00 from VW_SST_YGBMDYXX where ZZJBH0=P_ZDBH00; exception when NO_DATA_FOUND then P_ERRMSG:='终端编号:'||P_ZDBH00||'在本院SST_YGBHDY表中未对应收费员!'; return; end; begin select BMBH00 into LS_CZYKS0 from BM_YGBM00 where YGBH00=LS_YGBH00; exception when others then LS_CZYKS0:=0; end; --2019.12.06 if LS_CZYKS0 = 0 then P_ERRMSG:='收费员对应的科室不允许为空!'; return; end if; begin --select SF_SF_GETYYID00() YYID00 into LS_YYID00 from dual; select SF_SF_GETYYID00(LS_CZYKS0) YYID00 into LS_YYID00 from dual; exception when others then LS_YYID00:='0'; end; if P_CZJE00<=0 then P_ERRMSG:='充值金额错误，充值金额不能为零或负数!'; return; end if; LS_JFJE00:=P_CZJE00; LS_JFLBID:=1; select SF_SST_GETICKH00_YKT(P_CARDNO) into LS_CARDNO from dual; --获取该卡的病人的基本信息 begin select a.MZZHID,a.BRXM00,a.BRXB00,b.BRID00,b.ZT0000,a.YBKH00,a.YBID00,a.BRZJBH,a.BRDH00 into ls_MZZHID,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:='卡号:'||LS_CARDNO||'在本院所对应的病人信息未找到!'; return; end; select nvl(SFDJ00,0) into ls_SFDJ00 from SF_BRZHXX where BRID00=ls_brid00 and JFLBID=1; if ls_SFDJ00<>0 then P_ERRMSG:='该病人在本院的账户已被冻结或停用,请联系管理员!'; return; end if; --获取自助机对应收费员的票据序号-- 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; --校验交易流水号是否存在-- select count(1)into LS_COUNT0 from SF_BRJFB0 where BRID00=LS_BRID00 and JYLSH0=LS_JYLSH0 and JFCZY0 in (select YGBH00 from VW_SST_YGBMDYXX); if LS_COUNT0>=1 then P_ERRMSG:='该交易流水号:'||LS_JYLSH0||'已经存在无法充值!'; return; end if; if LS_CZLB00='1' then --现金 LS_XJJE00:=LS_JFJE00; LS_ZZJE00:=0; LS_YHKJE0:=0; LS_ZFFSBH:=1; as_ZPDWMC:=''; LS_JYLXMC:='自助机现金充值'; as_ZPH000:=LS_JYLSH0;--交易流水号 as_ZPZH00:=P_ZDBH00;--终端编码 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='现金'; LS_ZFMXBH:='H'; elsif LS_CZLB00='2' then --银行卡 LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=8; as_ZPDWMC:=''; LS_JYLXMC:='自助机银行卡充值'; as_ZPH000:=P_MISLSH;--交易流水号 as_ZPZH00:=P_PBHKH0;--银行卡号 as_ZPKHYH:=P_FKHMC0;--银行名称 as_ZPDWMC:=P_ZDBH00;--终端编码 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='银行卡'; LS_ZFMXBH:='K'; elsif LS_CZLB00='3' then --3一卡通支付充值 LS_XJJE00:=LS_JFJE00; LS_ZZJE00:=0; LS_YHKJE0:=0; LS_ZFFSBH:=18; as_ZPDWMC:='民生通自助机消费支付'; LS_JYLXMC:='民生通自助机消费支付'; as_ZPH000:=LS_YKTLSH;--交易流水号 as_ZPZH00:=P_ZDBH00;--终端编码 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='健康通充值'; LS_ZFMXBH:='P'; elsif LS_CZLB00='4' then --微信支付 LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=24; LS_JYLXMC:='微信'; as_ZPZH00:='';--银行卡号 as_ZPKHYH:='';--银行名称 as_ZPDWMC:=LS_ZFFHLS;--交易流水号 as_ZPH000:=LS_ZFQQLS;--支付平台请求流水微信/支付宝/公众号 LS_YKTLSH:=LS_ZFFHLS;--支付平台返回流水微信/支付宝/公众号 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_JYLXMC:='支付宝'; as_ZPZH00:='';--银行卡号 as_ZPKHYH:='';--银行名称 as_ZPDWMC:=LS_ZFFHLS;--银行卡号 as_ZPH000:=LS_ZFQQLS;--支付平台请求流水微信/支付宝/公众号 LS_YKTLSH:=LS_ZFFHLS;--支付平台返回流水微信/支付宝/公众号 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_JYLXMC:='微信公众号'; as_ZPZH00:='';--银行卡号 as_ZPKHYH:='';--银行名称 as_ZPDWMC:=LS_ZFFHLS;--交易流水号 as_ZPH000:=LS_ZFQQLS;--支付平台请求流水微信/支付宝/公众号 LS_YKTLSH:=LS_ZFFHLS;--支付平台返回流水微信/支付宝/公众号 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:='聚合支付'; as_ZPH000:=LS_ZFQQLS;--银行卡号 as_ZPZH00:=P_ZDBH00;--终端编码 as_ZPKHYH:=LS_JMYHKH;--银行名称 as_ZPDWMC:=LS_ZFFHLS;--交易流水号 LS_YKTLSH:=LS_JYLSH0;--交易流水号 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='聚合支付'; LS_ZFMXBH:='B'; --支付明细编码=BM_ZFFSMX.ZFMXBH elsif LS_CZLB00 ='8' then --银联扫码 --按银行卡处理 LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=8; LS_JYLXMC:='银联扫码'; as_ZPH000:=P_MISLSH;--交易流水号 as_ZPZH00:=P_PBHKH0;--银行卡号 as_ZPKHYH:=P_FKHMC0;--银行名称 as_ZPDWMC:=P_ZDBH00;--终端编码 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='银联扫码'; ls_ZFMXBH:='M'; --银联二维码 elsif LS_CZLB00='13' then --多码融合微信充值 LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=24; LS_JYLXMC:='多码融合微信充值'; as_ZPZH00:='';--银行卡号 as_ZPKHYH:='';--银行名称 as_ZPDWMC:=LS_ZFFHLS;--交易流水号 as_ZPH000:=LS_ZFQQLS;--支付平台请求流水微信/支付宝/公众号 LS_YKTLSH:=LS_ZFFHLS;--支付平台返回流水微信/支付宝/公众号 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='多码融合微信充值'; LS_ZFMXBH:='13'; --支付明细编码=BM_ZFFSMX.ZFMXBH elsif LS_CZLB00='14' then --多码融合支付宝充值 LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=25; LS_JYLXMC:='多码融合支付宝充值'; as_ZPZH00:='';--银行卡号 as_ZPKHYH:='';--银行名称 as_ZPDWMC:=LS_ZFFHLS;--交易流水号 as_ZPH000:=LS_ZFQQLS;--支付平台请求流水微信/支付宝/公众号 LS_YKTLSH:=LS_ZFFHLS;--支付平台返回流水微信/支付宝/公众号 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='多码融合支付宝充值'; LS_ZFMXBH:='14'; --支付明细编码=BM_ZFFSMX.ZFMXBH else begin select b.ZFFSBH,b.ZFMXBH,b.ZFMXMC into LS_DZZFFSBH,LS_DZZFMXBH,LS_JYLXMC from BM_TYZD00 a,BM_ZFFSMX b where b.ZFMXBH=trim(a.MC0000) and trim(a.MC0000) is not null and a.ZDMC00='自助机充值类型(CZLB00)院内对照' and a.BH0000=LS_CZLB00 ; exception when others then LS_DZZFFSBH:=null; --对照支付方式 LS_DZZFMXBH:=null;--对照支付明细编码 end; if LS_DZZFFSBH is not null then LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=LS_DZZFFSBH; LS_ZFMXBH:=LS_DZZFMXBH; LS_JYLXMC:=LS_JYLXMC; as_ZPZH00:='';--银行卡号 as_ZPKHYH:='';--银行名称 as_ZPDWMC:=LS_ZFFHLS;--交易流水号 as_ZPH000:=LS_ZFQQLS;--支付平台请求流水微信/支付宝/公众号 LS_YKTLSH:=LS_ZFFHLS;--支付平台返回流水微信/支付宝/公众号 LS_JGBM00:=LS_CZLB00; LS_JGMC00:=substrb(LS_JYLXMC,1,50); else --其它全部按银行卡处理 LS_ZZJE00:=0; LS_XJJE00:=0; LS_YHKJE0:=LS_JFJE00; LS_ZFFSBH:=8; LS_JYLXMC:='自助机银行卡充值'; as_ZPH000:=P_MISLSH;--交易流水号 as_ZPZH00:=P_PBHKH0;--银行卡号 as_ZPKHYH:=P_FKHMC0;--银行名称 as_ZPDWMC:=P_ZDBH00;--终端编码 LS_JGBM00:=LS_CZLB00; LS_JGMC00:='银行卡'; end if; end if; -- 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; LS_CZY000:=LS_YGBH00; LS_CZYXM0:=LS_YGXM00; if LS_operator_id is not null then --多点结算操作员_编码、多点结算操作员_名称 begin LS_CZY000:=to_number(LS_operator_id);--多点结算操作员_编码 LS_CZYXM0:=LS_operator_name;--多点结算操作员_名称 exception when others then LS_CZY000:=LS_YGBH00; LS_CZYXM0:=LS_YGXM00; end; end if; LS_MsgStr:='获取订单流水号'; select 'ZZ'||to_char(sysdate,'YYYYMMDD')||to_char(sysdate,'HH24MISS')||lpad(SQ_BM_ZFRZ00_DDLSH0.nextval,4,'0') into LS_DDLSH0 from dual; if (LS_CZQX00 in ('1')) then select SQ_SF_BRJFB0_DJH000.nextval into LS_DJH000 from dual; select SQ_SF_PJSYQK_PJH000.nextval into LS_PJH000 from dual; --1、添加病人交费记录，对于病人余额的修改在触发器中修改 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,YHKH00, YYID00,PLBH00,JYLSH0,YKTSH0,JYLXMC,JGBM00,JGMC00,SHH000,DDLSH0,ZFMXBH,SJCZY0) values(LS_DJH000,LS_BRID00,1,LS_JFJE00,LS_ZFFSBH,LS_ZFFSMC,as_ZPH000,as_ZPDWMC,as_ZPZH00,as_ZPKHYH,LS_PJH000, ls_CZRQ00,ls_CZSJ00,ls_YGBH00,ls_YGXM00,LS_ZYE000,0,0,0,1,LS_JFJE00,ls_CZYKS0,P_PBHKH0, ls_YYID00,7,LS_JYLSH0,LS_YKTLSH,LS_JYLXMC,LS_JGBM00,LS_JGMC00,LS_SHH000,LS_DDLSH0,LS_ZFMXBH,LS_CZY000); --2:修改病人账户金额 update SF_BRZHXX set XJYE00 =nvl(XJYE00,0)+LS_JFJE00, --病人现金余额 ZYE000 = nvl(ZYE000,0)+LS_JFJE00 --账户实际总余额 where BRID00 = ls_brid00 and MZZHID=ls_MZZHID; if nvl(LS_CZLB00,'0')<>'3' then --2016.05.10 --2、添加票据使用情况 insert into SF_PJSYQK(PJLYID,PJH000,PLBH00,PLMC00,PJXH00,XJJE00,ZZJE00,YHKJE0, CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,PJZT00,XM0000,XB0000,BRID00,DZQBJE,HJJE00,CZYKS0,YYID00,FZPJH0) select LS_PJLYID,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_JFJE00,ls_CZYKS0,ls_YYID00,ls_PJH000 from BM_PJLXB0 where PLBH00 = 7 ; --收预交金票据 end if; 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; 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,ZDBH00) 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,P_ZDBH00); end if; --银行卡--begin-- if LS_CZLB00 in ('3') then --3:一卡通往院内充值 LS_KFZT00:='2'; --状态0:其它数据 2:业务数据 4:无效数据 5:自助机民生通充值 LS_MKDM00:='4'; --模块代码1余额查询2账户充值3现金退款4消费支付5支付撤销6支付退货13预交金转出14民生通转入15结算返还16APP手机支付充值 LS_MKDMMC:='民生通自助机消费支付'; LS_MSTLX0:='03';--交易类型01充值02提现03消费04撤销05退货06冻结07解冻08转出09转入12医保返还 if (LS_SSTLSH is null) or (LS_SSTLSH='0') then LS_SSTLSH:=LS_JYLSH0; end if; LS_ID0000:=LS_SSTLSH; update SF_BRJFB0 set ZPH000=LS_ID0000,ZPKHYH=LS_YKTLSH, JYLSH0=LS_JYLSH0,YKTSH0=LS_YKTLSH, JYLXMC=LS_JYLXMC where BRID00=LS_BRID00 and DJH000=LS_DJH000; insert into SF_YKTKFB( ID0000,CZRQ00,CZSJ00,CZY000,ICKH00,BRID00,BRXM00,YBKH00,YBID00, KFRQ00,KFSJ00,ZFFS00,ZFFSMC,YKTYE0, DJH000,JYJE00,TFJE00,JFBZ00,CXDJH0,JZDH00,KFBZ00,KFZT00,ZDBH00,MKDM00, MKDMMC,CZFS00,JYLSH0,YKTLSH,JYLX00,XTGZH0,CZQX00 ) values( LS_ID0000,LS_CZRQ00,LS_CZSJ00,LS_YGBH00,LS_CARDNO,LS_BRID00,LS_BRXM00,LS_YBKH00,LS_YBID00, LS_CZRQ00,LS_CZSJ00,LS_ZFFSBH,LS_ZFFSMC,nvl(LS_YKTYE0,0), LS_DJH000,LS_JFJE00,0,'0',0,0,'',LS_KFZT00,P_ZDBH00,LS_MKDM00, LS_MKDMMC,LS_CZLB00,LS_JYLSH0,LS_YKTLSH,LS_MSTLX0,LS_JSGZH0,LS_CZQX00 ); end if; end if; if LS_CZQX00 in ('2') then begin LS_PJH000:=to_number(to_char(sysdate,'HH24MIss')); exception when others then LS_PJH000:=0; end; LS_PJXH00:='0'; LS_DJH000:=0; LS_KFZT00:='5'; --状态0:其它数据 2:业务数据 4:无效数据 5:自助机民生通充值 LS_MKDM00:='2'; --模块代码1余额查询2账户充值3现金退款4消费支付5支付撤销6支付退货13预交金转出14民生通转入15结算返还16APP手机支付充值 LS_MKDMMC:='民生通自助机账户充值'; LS_MSTLX0:='01';--交易类型01充值02提现03消费04撤销05退货06冻结07解冻08转出09转入12医保返还 select ZFFSBH,ZFFSMC into LS_ZFFSBH,LS_ZFFSMC from BM_BRZFFS where ZFFSBH=18; if (LS_SSTLSH is null) or (LS_SSTLSH='0') then LS_SSTLSH:=LS_JYLSH0; end if; LS_ID0000:=LS_SSTLSH; insert into SF_YKTKFB( ID0000,CZRQ00,CZSJ00,CZY000,ICKH00,BRID00,BRXM00,YBKH00,YBID00, KFRQ00,KFSJ00,ZFFS00,ZFFSMC,YKTYE0, DJH000,JYJE00,TFJE00,JFBZ00,CXDJH0,JZDH00,KFBZ00,KFZT00,ZDBH00,MKDM00, MKDMMC,CZFS00,JYLSH0,YKTLSH,JYLX00,XTGZH0,CZQX00 ) values( LS_ID0000,LS_CZRQ00,LS_CZSJ00,LS_YGBH00,LS_CARDNO,LS_BRID00,LS_BRXM00,LS_YBKH00,LS_YBID00, LS_CZRQ00,LS_CZSJ00,LS_ZFFSBH,LS_ZFFSMC,nvl(LS_YKTYE0,0), LS_DJH000,LS_JFJE00,0,'0',0,0,'',LS_KFZT00,P_ZDBH00,LS_MKDM00, LS_MKDMMC,LS_CZLB00,LS_JYLSH0,LS_YKTLSH,LS_MSTLX0,LS_JSGZH0,LS_CZQX00 ); end if; --参数: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; 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; ---院内保留充值记录 if LS_CZQX00 not in ('2') then 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) 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); end if; 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,operator_id,operator_name) 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,MZZYBZ, JMYHKH,ZFQQLS,ZFFHLS,ZFQDLS,operator_id,operator_name from SST_YJJ_ZHCZ where CARDNO=P_CARDNO and JYLSH0=P_JYLSH0; --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_MZTYZF:=nvl(SF_SF_TYZD00('支付统一平台参数','Open_MZ'),'N'); LS_MZCZLB:=nvl(SF_SF_TYZD00('支付统一平台参数','ZZJMZCZLB'),'0'); LS_INSTR0:=instr(','||LS_MZCZLB||',',','||LS_CZLB00||','); --参数是否包含对应充值类别 --如果系统参数SST_MZZZJCZCKTK_MZ 不开启并且ZZJMZCZLB未维护保留雨原来功能 if instrb(','||LS_CKTKCS||',',','||LS_ZFFSBH||',')>0 then if nvl(LS_INSTR0,0)>0 then --不退款类别 LS_ZFRZZT:='2';--支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 LS_ZXZTBZ:='0'; --状态标志 0：失败 1：成功 else LS_ZFRZZT:='1';--支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 LS_ZXZTBZ:='1'; --状态标志 0：失败 1：成功 end if; else if LS_MZTYZF <> 'N' and nvl(LS_INSTR0,0) = 0 then --充值类别不在参数设置的类别里 LS_ZFRZZT:='1';--支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 LS_ZXZTBZ:='1'; --状态标志 0：失败 1：成功 else LS_ZFRZZT:='2';--支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 LS_ZXZTBZ:='0'; --状态标志 0：失败 1：成功 end if; end if; --增加终端编码12个8或9 以及LS_SFTK00（是否支持窗口退款 1:是 0:否）判断 if P_ZDBH00 in ('999999999999','888888888888') or LS_SFTK00 in ('1') then --诊间、床边结算充值 LS_ZFRZZT:='1';--支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 LS_ZXZTBZ:='1'; --状态标志 0：失败 1：成功 end if; --如果银行卡支持窗口退款支付明细编码=K 都在为空，避免退款判断有问他 这里LS_ZFMXBH为空时才赋值K if LS_ZFRZZT in ('1') and LS_ZFFSBH in (8) and nvl(LS_ZFMXBH,' ')=' ' then LS_ZFMXBH:='K'; end if; --插入日志主表 if LS_ZFRZZT in ('1','2') then --支付日志状态 0：无 1:插入日志窗口退款 2:只插入日志 --获取订单流水号 if P_JYLSH0 is not null and P_JYLSH0 not in ('0') then LS_DDLSH0:=P_JYLSH0; else select SF_SF_PAY_DDLSH0('',0) into LS_DDLSH0 from dual; end if; --平台流水号=支付平台流水号 if LS_ZFQQLS is not null and LS_ZFQQLS not in ('0') then LS_JYLSH0:=LS_ZFQQLS; end if; --渠道流水号=第三方平台（微信、支付宝流水号） if LS_ZFFHLS is not null and LS_ZFFHLS not in ('0') then LS_YKTLSH:=LS_ZFFHLS; 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 )values( LS_DDLSH0,LS_CZRQ00,LS_CZSJ00,LS_CZY000,LS_CZYXM0,2,'门诊自助机预交金充值',P_ZDBH00,LS_BRID00,trim(LS_BRXM00), LS_BRXB00,trim(LS_YBKH00),trim(LS_YBID00),trim(P_CARDNO),trim(LS_BRZJBH),trim(LS_BRDH00),0,0,LS_GHID00,LS_GHH000, 0,LS_DJH000,LS_ZFFSBH,LS_ZFFSMC,LS_DJH000,LS_JFJE00,LS_CZRQ00,LS_CZSJ00,LS_ZXZTBZ,0, null,LS_JYLSH0,null,to_char(sysdate,'YYYYMMDD')||to_char(sysdate,'HH24MIss'),LS_JYLSH0,null,null,LS_JYLSH0,LS_YKTLSH,LS_JYLXMC, LS_JGBM00,LS_JGMC00,null,null,null,null,0,LS_TYJE00,'',null, null,null,'',LS_ZFMXBH,'',null,LS_CZYKS0,LS_TKDJJE,LS_PAYWAY ); --更新缴费表 update SF_BRJFB0 set DDLSH0=LS_DDLSH0,JYLSH0=LS_JYLSH0,YKTSH0=LS_YKTLSH,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:='预交金充值错误:'||SQLERRM; p_Result:='0'; --1:成功 0:失败 when others then P_ERRMSG:='预交金充值错误:'||SQLERRM; p_Result:='0'; --1:成功 0:失败 end; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%