CREATE OR REPLACE PROCEDURE SP_SF_BRZHXX_ICKF00 ( Popflag in number , --0售卡 1退卡 2换卡 3补新卡 4挂失 5取消挂失 6报废卡 7报废卡回收 8重置 9直接改为空白卡 10退卡押金(收费模式) Pbrid00 in number , --ID号 Pickh00 in varchar2 , --IC卡号 Pygbh00 in number , --员工编号 Pkl0000 in varchar2 , --账户口令,就诊卡的口令 Poldick in varchar2 , --旧IC卡号 当Popflag=2时 Pskbz00 in char , --售卡标志 1售病人卡 2售员工卡 Pczy000 in number , --操作员 Pczyks0 in number , --操作员科室 Pcommit in varchar2 default 'N' , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 , --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 Pyybr00 in char default 'N' , --预约病人,该参数现不用 Psfsqyj in char default 'Y' , --是否扣卡押金 Pmzzybz in char default '0' , --门诊住院标志 0门诊 1住院 Psfybhk in char default 'N' , --是否医保换卡(院内直接换卡) Pzffsbh in number default null --支付方式编号 ) -- MODIFICATION HISTORY -- Person Date Comments -- lintj 2012.12.29 加 Pmzzybz -- lintj 2012.08.01 create -- qks 2012.09.25 修改错误提示内容 -- zhanghr 2012.10.30 增加记录新卡号功能 -- qks 2012.10.31 换卡和退卡，如果有收取卡费,IC_YHXX00.SFSKYJ改为1 -- qks 2013.04.11 SF_YYZCBR.ZSDJR0=Pczy000 -- qks 2013.06.25 增加入参Psfybhk -- jlg 2016.03.28 增加一卡通平台接口的处理 MZSF9-20160406-002 -- jlg 2016.04.12 一卡通平台接口处理修改 MZSF9-20160412-002 -- jlg 2016.12.05 退卡时冲负原收取的卡押金费用(为了兼容5.0的模式) MZSF9-20161205-003 -- jlg 2016.12.22 处理无门诊账户无卡病人的补卡问题 MZSF9-20161212-001 -- jlg 2017.01.16 处理无效卡（退卡）的病人的补卡问题 MZSF9-20170112-002 -- jlg 2017.09.08 处理退卡时原来收取的卡费会被退还的问题 MZSF9-20170907-001 -- jlg 2017.10.19 整理有关是否扣卡押金的问题 MZSF9-20171018-001 -- liwm 2018.03.21 记账病人卡押金相关 for MZSF9-20180317-002 -- liwm 2018.05.30 原先参数使用0，1做判断，但是费别编号是可能为0，或1的，改成Y，N for MZSF9-20180528-003 -- liwm 2018.06.07 门诊收费存储过程统一添加调用参数日志 for MZSF9-20180604-003 -- gzj 2018.08.14 新增Popflag=8就诊卡重置处理过程 for MZSF9-20180702-004 -- qks 2018.10.11 新增Popflag=9指定卡类代号(IC_YHXX00.KLDH00)的旧卡不做写卡操作,直接改为空白卡; for MZSF9-20181011-001 -- qks 2018.11.13 bug:解决莆田市民卡退卡问题; for MZSF9-20181113-002 -- qks 2018.12.04 新增Popflag=10,解决bug:门诊结算菜单下进行清账退卡操作,会出现先退卡再退卡费,导致卡费无法退还给病人; for MZSF9-20181203-003 -- pwt 2018.12.22 为适应分院模式功能改造，IC_YWLS00插入CZYKS0; for MZSF9-20181222-004 -- jlg 2019.07.22 增加自费卡补卡时任何情况下都要扣卡费的处理 MZSF9-20190722-001 -- jlg 2019.09.03 处理有些医院换卡也要扣卡费的情况（跟补卡一样），加参数SF_YNKHKSFQZSQKF处理 MZSF9-20190830-001 -- linzetao 2020.03.03 增加入参Pzffsbh(支付方式编号), 增加调用存储过程SP_SF_ICYJMX_INSERT, 记录IC卡押金操作明细 MZSF9-20200224-002 -- linzetao 2021.04.20 处理His57升9费用明细只能退升级前的卡押金 MZSF9-20210419-004 -- linzetao 2022.08.01 取费用表卡押金增加CXBZ00='Z'条件 MZSF9-20220801-001 -- wac 2024.06.28 现场脚本归档 MZSF13-20240628-001 ----------------------------------------------------------------------------------------------------------------------------------------------- as Vicyhxx IC_YHXX00%rowtype; Vickh00 IC_YHXX00.ICKH00%type; --原挂失卡卡号 Voldklb IC_YHXX00.ICKLB0%type; Vicknbh IC_YHXX00.ICKNBH%type; Vnewklb IC_YHXX00.ICKLB0%type; Vickje0 IC_YHXX00.ICKJE0%type; Vbtlbhk XT_XTCS00.VALUE0%type; Vmxid00 SF_FYMX00.MXID00%type; Vqyykt0 XT_XTCS00.VALUE0%type; --是否启用一卡通接口 Vthkf00 XT_XTCS00.VALUE0%type; --退卡时是否退还之前收取的卡费 Vczyxm0 BM_YGBM00.ZWXM00%type; --操作员姓名 Vsfdm00 IC_YHXX00.SFDM00%type; Vkmh000 IC_YHXX00.KMH000%type; --卡面号 Vickf00 SF_FYMX00.HJJE00%type; Vxjye00 SF_BRZHXX.XJYE00%type; --现金余额 Vsfskyj char(1); Vcounter number(14,4); --计数器变量 Vczrq00 char(8); Vczsj00 char(8); Vmzzhid SF_BRZHXX.MZZHID%type; Ecustom exception; Vsqltxt varchar2(200); VSF_SFYEPDSFKJDJF XT_XTCS00.VALUE0%type; --医保记账病人卡押金处理 Vfbbh00 varchar2(20); --病人费别编号 Vdsqykf XT_XTCS00.VALUE0%type; --院内卡补卡是否都收取原卡费 Vhksqkf XT_XTCS00.VALUE0%type; --院内卡换卡是否强制收取卡费 Vsfsqkf number(1); --是否收取卡费 Vxxxlog varchar2(2000); Vcznr00 varchar2(2000); Vtyjexg SF_BRZHXX.TYJE00%type; --停用金额修改额 Vytyje0 SF_BRZHXX.TYJE00%type; --病人原停用金额 VUpgradeDate BM_TYZD00.MC0000%type; --His升级日期 begin Pzxcgbz := 0; Vickf00 := 0; Vsfsqkf := 0; Vtyjexg := 0; --置停用金额修改额为0 Vczrq00 := to_char(sysdate,'yyyymmdd'); Vczsj00 := to_char(sysdate,'hh24:mi:ss'); Vxxxlog := substr('POPFLAG:'||nvl(to_char(POPFLAG), 'null')||','||'PICKH00:'||nvl(to_char(PICKH00), 'null')||','||'PCZY000:'||nvl(to_char(PCZY000), 'null')||','||'PCZYKS0:'||nvl(to_char(PCZYKS0), 'null')||','||'PCOMMIT:'||nvl(to_char(PCOMMIT), 'null'),1,2000); Pxxxx00 := ''; SP_TransLog(sysdate,'SP_SF_BRZHXX_ICKF00',Pczy000,Pczyks0,Vxxxlog); --获取操作员姓名 select nvl(max(ZWXM00),' ') into Vczyxm0 from BM_YGBM00 where YGBH00 = Pczy000; select * into Vicyhxx from IC_YHXX00 where ICKH00 = Pickh00; --门诊病人退卡时是否退还之前收取的卡费 1是0否 select nvl(max(trim(VALUE0)),'0') into Vthkf00 from XT_XTCS00 where NAME00='SF_MZBRTKSSFTHZQSQDKF'; --门诊院内卡补卡（并作废原卡）时，是否都收取原卡的卡费（不管之前是否扣卡押金），0否（按原来的规则收取）1是（都收取），默认0 select nvl(max(trim(VALUE0)),'0') into Vdsqykf from XT_XTCS00 where NAME00='SF_YNKBKSFDSQYKF'; --门诊院内卡换卡是否强制收取原卡的卡费，0否1是，默认0 select nvl(max(trim(VALUE0)),'0') into Vhksqkf from XT_XTCS00 where NAME00='SF_YNKHKSFQZSQKF'; --医保记账病人卡押金处理判断条件处理 select ','||nvl(max(VALUE0), 'N')||',' into VSF_SFYEPDSFKJDJF from XT_XTCS00 where NAME00 = 'SF_SFYEPDSFKJDJF'; select ','||nvl(max(FBBH00), '')||',' into Vfbbh00 from BM_BRXXB0 where BRID00 = Pbrid00; if instr(VSF_SFYEPDSFKJDJF, ',Y,') >0 and instr(VSF_SFYEPDSFKJDJF, Vfbbh00) > 0 then VSF_SFYEPDSFKJDJF := '1'; else VSF_SFYEPDSFKJDJF := '0'; end if; --His升级日期 VUpgradeDate := SF_XT_TYZDMC('HIS升级字典','UpgradeDate'); if VUpgradeDate is null then VUpgradeDate := '0'; end if; --售卡 if Popflag = 0 then if Vicyhxx.ZT0000 <> '0' then Ptsxx00 :='该卡已用,请更换诊疗卡!'; raise Ecustom; end if; select count(*) into Vcounter from IC_YHXX00 where BRID00=pbrid00 and ZT0000='1'; if Vcounter>0 then Ptsxx00 :='病人持有有效卡，不能再售卡!'; raise Ecustom; end if; --修改就诊卡的信息 update IC_YHXX00 set BRID00 = Pbrid00, ZT0000 = '1', ICKMM0 = Pkl0000 where ICKH00 = Pickh00 and ZT0000 = '0'; insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH,BRID00,CZYKS0) values(SQ_IC_YWLS00_YWID00.nextval,Pickh00,Pczy000,Pskbz00,Vczrq00,Vczsj00,Vicyhxx.ICKJE0,Vicyhxx.ICKNBH,Pbrid00,Pczyks0); if Psfsqyj='Y' then--要扣卡押金的情况 update IC_YHXX00 set SFSKYJ='1' where ICKH00=Pickh00; if Pmzzybz = '0' then update SF_BRZHXX set TYJE00 = nvl(TYJE00, 0) + Vicyhxx.ICKJE0 where BRID00 = Pbrid00 and JFLBID = 1; Vtyjexg := Vtyjexg + nvl(Vicyhxx.ICKJE0, 0); --记录本次停用金额修改值 --如果记账病人不扣卡押金，撤销上一部的操作，SFSKYJ设置为0 停用金额减回去 for MZSF9-20180303-002 MZSF9-20180317-002 if VSF_SFYEPDSFKJDJF='1' then update IC_YHXX00 set SFSKYJ='0' where ICKH00=Pickh00; update SF_BRZHXX set TYJE00 = nvl(TYJE00, 0) - Vicyhxx.ICKJE0 where BRID00 = Pbrid00 and JFLBID = 1; Vtyjexg := Vtyjexg - nvl(Vicyhxx.ICKJE0, 0); --记录本次停用金额修改值 end if; end if; else update IC_YHXX00 set SFSKYJ='0' where ICKH00=Pickh00; end if; end if; --退卡 if Popflag = 1 then if Vicyhxx.ZT0000 = '4' then Ptsxx00 :='非法卡,请重新输入病人的就诊卡号!'; raise Ecustom; end if; insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH,BRID00,CZYKS0) values(SQ_IC_YWLS00_YWID00.nextval,Vicyhxx.ICKH00,Pczy000,'5',Vczrq00,Vczsj00,Vicyhxx.ICKJE0,Vicyhxx.ICKNBH,nvl(Vicyhxx.BRID00,Pbrid00),Pczyks0); if Vthkf00 ='1' then --为了兼容处理5.0系统的模式，原来有收卡押金费用的，则需要进行冲负 jlg --但是如果之前是换卡收取的卡费，则不能被退还 jlg select nvl(sum(a.HJJE00),0) into Vickf00 from SF_FYMX00 a where a.XMBH00+0=Vicyhxx.SFDM00 and a.JFLBID = 1 and a.JZDH00+0= 0 and a.BRID00 = nvl(Vicyhxx.BRID00,Pbrid00) and a.CXBZ00='Z' and (VUpgradeDate = '0' or a.CZRQ00 < VUpgradeDate); if Vickf00>0 then if Vicyhxx.BRID00 is null then select max(ICKH00) into VICKH00 from IC_YHXX00 where BRID00=Pbrid00 and ZT0000='1'; end if; SP_SF_FYMX00_ICKF00(1, --0收费 1退费 nvl(VICKH00,Pickh00),--ICK号 Vmxid00, --收费 Pczy000, --操作员 Pczyks0, --操作员科室 'N', --是否提交 Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); end if; end if; --2018.11.13 莆田市民卡,已经生成了新的空白卡,不能作废; 注意：在用的莆田市民卡,前台程序已经把卡号被改掉了。for MZSF9-20181113-002 update IC_YHXX00 set ZT0000 = '4' where ICKH00 = Pickh00 and ZT0000 <> '4' and ZT0000<>'0'; if sql%notfound then update IC_YHXX00 set ZT0000 = '4' where brid00 = Pbrid00 and KMH000=Vicyhxx.KMH000 and ZT0000='1'; end if; if Vicyhxx.SFSKYJ='1' then if Pmzzybz = '0' then select nvl(max(TYJE00),0) into Vytyje0 from SF_BRZHXX where BRID00 = Pbrid00 and JFLBID = 1; --取原停用金额 update SF_BRZHXX set TYJE00 = greatest(nvl(TYJE00,0)-Vicyhxx.ICKJE0,0) where BRID00 = Pbrid00 and JFLBID = 1; Vtyjexg := Vtyjexg - least(Vytyje0,nvl(Vicyhxx.ICKJE0,0)); --记录本次停用金额修改值, 取原停用金额与IC卡金额较小值 end if; end if; end if; if Popflag = 2 then --换卡 if Vicyhxx.ZT0000 <> '0' then Ptsxx00 :='非法卡,请重新输入病人的新就诊卡号!'; raise Ecustom; end if; begin select ICKJE0,ICKLB0,ICKNBH into Vickje0, Voldklb, Vicknbh from IC_YHXX00 where ICKH00=Poldick; exception when no_data_found then Ptsxx00 :='警告:该病人旧的卡已不存在!'; raise Ecustom; end; select nvl(max(trim(VALUE0)),'Y') into Vbtlbhk from XT_XTCS00 where NAME00='SF_BTLXKSFYXHK'; --不同类型卡是否允许换卡。 if Vbtlbhk = 'N' then select nvl(max(trim(ICKLB0)),'0') into Vnewklb from IC_YHXX00 where ICKH00=Pickh00 ; if Voldklb<>Vnewklb then Ptsxx00 :='警告:不同类别的卡不能换!'; raise Ecustom; end if; end if; --处理旧卡 insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH,BRID00,NEWICK,CZYKS0) values(SQ_IC_YWLS00_YWID00.nextval,Poldick,Pczy000,'E',Vczrq00,Vczsj00,Vickje0,Vicknbh,Pbrid00,Pickh00,Pczyks0); update IC_YHXX00 set ZT0000 = '4' where ICKH00 = Poldick and ZT0000 <> '4'; select count(*) into Vcounter from IC_YHXX00 where ICKH00 = Poldick and SFSKYJ='1'; if Vcounter>0 then if Pmzzybz = '0' then --先退旧卡卡押金 update SF_BRZHXX set TYJE00 = nvl(TYJE00, 0) - Vickje0 where BRID00 = Pbrid00 and JFLBID = 1; Vtyjexg := Vtyjexg - Vickje0; --记录本次停用金额修改值 end if; end if; --处理新卡 update IC_YHXX00 set BRID00 = Pbrid00, ZT0000 = '1', ICKMM0 = Pkl0000 where ICKH00 = Pickh00 and ZT0000 = '0'; insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH,BRID00,CZYKS0) values(SQ_IC_YWLS00_YWID00.nextval,Pickh00,Pczy000,'C',Vczrq00,Vczsj00,Vicyhxx.ICKJE0,Vicyhxx.ICKNBH,Pbrid00,Pczyks0); if Psfsqyj='Y' then update IC_YHXX00 set SFSKYJ='1' where ICKH00=Pickh00; if Pmzzybz = '0' then --再扣新卡卡押金 update SF_BRZHXX set TYJE00 = nvl(TYJE00, 0) + Vicyhxx.ICKJE0 where BRID00 = Pbrid00 and JFLBID = 1; Vtyjexg := Vtyjexg + nvl(Vicyhxx.ICKJE0,0); --记录本次停用金额修改值 end if; --如果记账病人不扣卡费，处理 for MZSF9-20180303-002 MZSF9-20180317-002 if VSF_SFYEPDSFKJDJF='1' then update IC_YHXX00 set SFSKYJ='0' where ICKH00=Pickh00; update SF_BRZHXX set TYJE00 = nvl(TYJE00, 0) - Vicyhxx.ICKJE0 where BRID00 = Pbrid00 and JFLBID = 1; Vtyjexg := Vtyjexg - nvl(Vicyhxx.ICKJE0,0); end if; else update IC_YHXX00 set SFSKYJ='0' where ICKH00=Pickh00; end if; if Psfybhk = 'Y' then update bm_brxxb0 set YBKH00=(select KMH000 from IC_YHXX00 where BRID00=Pbrid00 and ICKH00 = Pickh00 and ZT0000='1') where BRID00=Pbrid00; update zy_brxxb0 set YBKH00=(select YBKH00 from BM_BRXXB0 where BRID00 = Pbrid00) where BRID00=Pbrid00 and substr(BRZT00,1,1) in ('2','3'); end if; --旧卡为院内卡，参数设定换卡也扣卡费的情况下 if Vhksqkf = '1' and Voldklb = '0' then SP_SF_FYMX00_ICKF00(0, --0收费 1退费 Poldick, --ICK号 Vmxid00, --收费 Pczy000, --操作员 Pczyks0, --操作员科室 'N', --是否提交 Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); end if; end if; if Popflag = 3 then --补卡 if Vicyhxx.ZT0000 <> '0' then Ptsxx00 :='该卡非空白卡,请插入空白的就诊卡号!'; raise Ecustom; end if; select count(*) into Vcounter from IC_YHXX00 where BRID00 = Pbrid00 and ZT0000 ='1'; if Vcounter>0 then Ptsxx00 :='该病人已存在有有效卡，不能补卡!'; raise Ecustom; end if; select nvl(max(ICKH00),'0'),nvl(max(ICKJE0),'0') into Vickh00,Vickje0 from IC_YHXX00 where BRID00 = Pbrid00 and ZT0000 = '2'; if Vickh00 = '0' then Ptsxx00 :='该病人卡还未挂失不能补卡!'; --raise Ecustom; --现在这种情况不报错，因为存在原先无卡的病人现在补卡的情况 jlg else --挂失补卡的话，先报废原挂失卡 SP_SF_BRZHXX_ICKF00( 6, --0售卡 1退卡 2换卡 3补新卡 4挂失 5取消挂失 6报废卡 7报废卡回收 Pbrid00, --ID号 Vickh00, --IC卡号 Pygbh00, --员工编号 Pkl0000, --账户口令,就诊卡的口令 Vickh00, --旧IC卡号 当Popflag=2时 Pskbz00, --售卡标志 1售病人卡 2售员工卡 Pczy000, --操作员 Pczyks0, --操作员科室 'N', --是否提交 Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00, --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 Pyybr00, --预约病人 Psfsqyj, --是否收取卡押金 Pmzzybz, --门诊住院标志 0门诊 1住院 Psfybhk, --是否医保换卡(院内直接换卡) Pzffsbh --支付方式编号 ); update IC_YWLS00 set NEWICK=Pickh00 where ICKH00=Vickh00 and YWCZ00='4' and CZRQ00=Vczrq00; end if; update IC_YHXX00 set BRID00 = Pbrid00, ZT0000 = '1', ICKMM0 = Pkl0000 where ICKH00 = Pickh00 and ZT0000 = '0'; insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH,BRID00,CZYKS0) values(SQ_IC_YWLS00_YWID00.nextval,Pickh00,Pczy000,'F',Vczrq00,Vczsj00,Vicyhxx.ICKJE0,Vicyhxx.ICKNBH,Pbrid00,Pczyks0); if Psfsqyj='Y' then update IC_YHXX00 set SFSKYJ='1' where ICKH00=Pickh00; if Pmzzybz = '0' then Vtyjexg := Vtyjexg + nvl(Vicyhxx.ICKJE0,0); update SF_BRZHXX set TYJE00 = nvl(TYJE00, 0) + Vicyhxx.ICKJE0 where BRID00 = Pbrid00 and JFLBID = 1; --如果病人没有账户信息资料, 则新增一笔 if sql%notfound then select SQ_SF_BRZHXX_ZHYEID.nextval into Vmzzhid from dual; insert into SF_BRZHXX(MZZHID,JFLBID,BRID00,SFDJ00,KHRQ00,KHSJ00, CZYBH0,CZYXM0,XJYE00,ZZYE00,ZYE000,YKZYE0,BJJE00,TYJE00,YXQFJE,YBZHYE,KL0000) values(Vmzzhid,1,Pbrid00,'0',Vczrq00,Vczsj00, Pczy000,Vczyxm0,0,0,0,0,0,Vicyhxx.ICKJE0,0,0,Pkl0000); --ad_TYJE00 end if; --如果记账病人不扣卡费，处理 if VSF_SFYEPDSFKJDJF='1' or Vdsqykf = '1' then update IC_YHXX00 set SFSKYJ='0' where ICKH00=Pickh00; update SF_BRZHXX set TYJE00 = nvl(TYJE00, 0) - Vicyhxx.ICKJE0 where BRID00 = Pbrid00 and JFLBID = 1; Vtyjexg := Vtyjexg - nvl(Vicyhxx.ICKJE0,0); end if; end if; else update IC_YHXX00 set SFSKYJ='0' where ICKH00=Pickh00; end if; if Pmzzybz = '0' then update SF_YYZCBR set ZT0000 = '1', ZSDJR0 = Pczy000, DJRQ00 = Vczrq00 where BRID00 = Pbrid00; end if; end if; if Popflag = 4 then --挂失 update IC_YHXX00 set ZT0000 = '2' where ICKH00 = Pickh00; insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH,BRID00,CZYKS0) values(SQ_IC_YWLS00_YWID00.nextval,Pickh00,Pczy000,'3',Vczrq00,Vczsj00,Vicyhxx.ICKJE0,Vicyhxx.ICKNBH,Vicyhxx.BRID00,Pczyks0); end if; if Popflag = 5 then --取消挂失 update IC_YHXX00 set ZT0000 = '1' where ICKH00 = Pickh00; insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH,BRID00,CZYKS0) values(SQ_IC_YWLS00_YWID00.nextval,Pickh00,Pczy000,'6',Vczrq00,Vczsj00,Vicyhxx.ICKJE0,Vicyhxx.ICKNBH,Vicyhxx.BRID00,Pczyks0); end if; if Popflag = 6 then --报废 begin select ICKJE0,ICKLB0,ICKNBH,SFSKYJ into Vickje0, Voldklb, Vicknbh,Vsfskyj from IC_YHXX00 where ICKH00=Poldick; exception when no_data_found then Ptsxx00 :='警告:该病人旧的卡已不存在!'; raise Ecustom; end; if Vsfskyj='1' then if Pmzzybz = '0' then --退还卡押金 update SF_BRZHXX set TYJE00 = nvl(TYJE00, 0) - Vickje0 where BRID00 = Pbrid00 and JFLBID = 1; Vtyjexg := Vtyjexg - Vickje0; end if; Vsfsqkf:=1; elsif Vdsqykf = '1' then Vsfsqkf:=1; elsif VSF_SFYEPDSFKJDJF='1' then --如果记账病人不扣卡费，在这里还是要扣旧的卡费 for MZSF9-20180303-002 MZSF9-20180317-002 Vsfsqkf:=1; end if; --有收取卡费 if Vsfsqkf = 1 then SP_SF_FYMX00_ICKF00(0, --0收费 1退费 Poldick, --ICK号 Vmxid00, --收费 Pczy000, --操作员 Pczyks0, --操作员科室 'N', --是否提交 Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); end if; update IC_YHXX00 set ZT0000 = '3' where ICKH00 = Pickh00; insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH,BRID00,CZYKS0) values(SQ_IC_YWLS00_YWID00.nextval,Pickh00,Pczy000,'4',Vczrq00,Vczsj00,Vicyhxx.ICKJE0,Vicyhxx.ICKNBH,Vicyhxx.BRID00,Pczyks0); end if; if Popflag = 7 then --报废卡回收 begin select ICKJE0,ICKLB0,ICKNBH,SFSKYJ into Vickje0, Voldklb, Vicknbh,Vsfskyj from IC_YHXX00 where ICKH00=Pickh00; exception when no_data_found then Ptsxx00 :='警告:该病人旧的卡已不存在!'; raise Ecustom; end; update IC_YHXX00 set ZT0000 = '4' where ICKH00 = Pickh00; insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH,BRID00,CZYKS0) values(SQ_IC_YWLS00_YWID00.nextval,Pickh00,Pczy000,'A',Vczrq00,Vczsj00,Vicyhxx.ICKJE0,Vicyhxx.ICKNBH,Vicyhxx.BRID00,Pczyks0); if Vsfskyj='1' then SP_SF_FYMX00_ICKF00(1, --0收费 1退费 Pickh00, --ICK号 Vmxid00, --收费 Pczy000, --操作员 Pczyks0, --操作员科室 'N', --是否提交 Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); end if; end if; if Popflag = 8 then --就诊卡重置 if Vicyhxx.ICKLB0 <> '2' then Ptsxx00 := '该卡是非医保卡,不能重置！'; raise Ecustom; end if; select count(*) into Vcounter from (select CFLSH0 from VW_SF_CFMX00 where BCCFH0 is null and THBZ00='0' and BRID00 = Pbrid00 union select to_char(YJDJH0) from VW_SF_YJYWMX where CXDJH0 is null and BRID00 = Pbrid00 ); if Vcounter > 0 then Ptsxx00 := '存在未结算的费用，请先对费用进行结算'; raise Ecustom; end if; select XJYE00 into Vxjye00 from SF_BRZHXX where BRID00 = Pbrid00; if Vxjye00 > 0 then Ptsxx00 := '请先退还卡内可用余额'; raise Ecustom; end if; update IC_YHXX00 set BRID00='',ZT0000='0' where BRID00 = Pbrid00; Vcznr00 := '修改 BRID00：' || Pbrid00 || ' ->'' ZT0000：' || Vicyhxx.ZT0000 || ' -> 0'; insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00) values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'IC_YHXX00',1,Pczy000,Vcznr00); end if; if Popflag = 9 then --直接改为空白卡 for MZSF9-20181011-001 update IC_YHXX00 set BRID00='',ZT0000='0' where ICKH00 = Pickh00 and ZT0000 in ('3','4','0'); if sql%notfound=false then insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH,BRID00,SFSKYJ,CZYKS0) values(SQ_IC_YWLS00_YWID00.nextval,Pickh00,Pczy000,'7',Vczrq00,Vczsj00,Vicyhxx.ICKJE0,Vicyhxx.ICKNBH,Vicyhxx.BRID00,Vicyhxx.SFSKYJ,Pczyks0); end if; end if; if Popflag = 10 then --退卡押金(收费模式) if Vthkf00 ='1' then --为了兼容处理5.0系统的模式，原来有收卡押金费用的，则需要进行冲负 jlg --但是如果之前是换卡收取的卡费，则不能被退还 jlg select nvl(sum(a.HJJE00),0) into Vickf00 from SF_FYMX00 a where a.XMBH00+0=Vicyhxx.SFDM00 and a.JFLBID = 1 and a.JZDH00+0= 0 and a.BRID00 = nvl(Vicyhxx.BRID00,Pbrid00) and a.CXBZ00='Z' and (VUpgradeDate = '0' or a.CZRQ00 < VUpgradeDate); if Vickf00>0 then if Vicyhxx.BRID00 is null then select max(ICKH00) into VICKH00 from IC_YHXX00 where BRID00=Pbrid00 and ZT0000='1'; end if; SP_SF_FYMX00_ICKF00(1, --0收费 1退费 nvl(VICKH00,Pickh00),--ICK号 Vmxid00, --收费 Pczy000, --操作员 Pczyks0, --操作员科室 Pcommit, --是否提交 Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); end if; end if; end if; --政和县启用一卡通平台接口 jlg select nvl(max(trim(VALUE0)),'N') into Vqyykt0 from XT_XTCS00 where NAME00='SF_SFQYYKTPTJK'; if Vqyykt0 ='Y' then select count(1) into Vcounter from PT_HIS_BRXX where HISBRID = Pbrid00 ; --and KH0000 = Pickh00; --如果该病人为一卡通病人 ,记录一卡通卡状态变更记录 卡状态 0可用 1已用 2冻结 3挂失 4注销 if Vcounter >0 then if Popflag = '0' then --0售卡 insert into PT_HIS_KZTBGB(LSH000,KH0000,KZT000,CZR000,SFBK00,PTREAD) values(SQ_PT_HIS_KZTBGB_LSH000.nextval,Pickh00,'1',Vczyxm0,'N','0'); elsif Popflag = '1' then --1退卡 insert into PT_HIS_KZTBGB(LSH000,KH0000,KZT000,CZR000,SFBK00,PTREAD) values(SQ_PT_HIS_KZTBGB_LSH000.nextval,Pickh00,'4',Vczyxm0,'N','0'); elsif Popflag = '2' then --2换卡 --更改PT_HIS_BRXX的KH0000为新卡号 update PT_HIS_BRXX set KH0000 = Pickh00,PTREAD = '0' where HISBRID = Pbrid00 ; --原卡注销 insert into PT_HIS_KZTBGB(LSH000,KH0000,KZT000,CZR000,SFBK00,PTREAD) values(SQ_PT_HIS_KZTBGB_LSH000.nextval,Poldick,'4',Vczyxm0,'N','0'); --建新卡(问题：这张新卡是否一卡通新卡？如果是一卡通新卡不是要先传给一卡通平台看下是否有效（一卡通入库）？，如果返回是无效呢？...) --jlg insert into PT_HIS_KZTBGB(LSH000,KH0000,KZT000,CZR000,SFBK00,PTREAD) values(SQ_PT_HIS_KZTBGB_LSH000.nextval,Pickh00,'1',Vczyxm0,'N','0'); elsif Popflag = '3' then --3补新卡 --更改PT_HIS_BRXX的KH0000为新卡号 select KMH000 into Vkmh000 from IC_YHXX00 where ICKH00 = Pickh00; update PT_HIS_BRXX set KH0000 = Pickh00,KMH000 = Vkmh000,PTREAD = '0' where HISBRID = Pbrid00 ; --记录补卡操作 ，Vickh00 原挂失卡卡号，从上面补卡操作里得到 insert into PT_HIS_KZTBGB(LSH000,KH0000,KZT000,CZR000,SFBK00,NEWKH0,NEWKMH,BKCZR0,PTREAD) values(SQ_PT_HIS_KZTBGB_LSH000.nextval,Vickh00,'4',Vczyxm0,'Y',Pickh00,Vkmh000,Vczyxm0,'0'); elsif Popflag = '4' then --4挂失 insert into PT_HIS_KZTBGB(LSH000,KH0000,KZT000,CZR000,SFBK00,PTREAD) values(SQ_PT_HIS_KZTBGB_LSH000.nextval,Pickh00,'3',Vczyxm0,'N','0'); elsif Popflag = '5' then --5取消挂失 insert into PT_HIS_KZTBGB(LSH000,KH0000,KZT000,CZR000,SFBK00,PTREAD) values(SQ_PT_HIS_KZTBGB_LSH000.nextval,Pickh00,'1',Vczyxm0,'N','0'); /*elsif Popflag = '6' then --6报废卡 --无此操作 elsif Popflag = '7' then*/ --7报废卡回收 --无此操作 end if; end if; end if; --在IC卡操作明细表里新增一笔 SP_SF_ICYJMX_INSERT( Pbrid00, --病人ID Pczy000, --操作员 Pczyks0, --操作员科室 Vtyjexg, --停用金额修改值 Pzffsbh, --支付方式 '', --备注 'N', --Pcommit Pzxcgbz, --执行成功标志 1成功 0失败 Ptsxx00, --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ); --换卡增加动态调用电子病历换卡接口(医大附二) if nvl(Pickh00,'0')<>nvl(Poldick,'0') then begin Vsqltxt:='begin SP_BLJK_BRHKJK(:v1,:v2,:v3,:v4,:v5,:v6); end;'; execute immediate Vsqltxt using in Pickh00,Pbrid00,Poldick,Pczy000,Vczyxm0,Pczyks0; exception when others then Pzxcgbz:=0; end; end if; Pzxcgbz:=1; if pcommit='Y' then commit; end if; exception when no_data_found then raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm,1,240)); when Ecustom then raise_application_error(-20010,substrb(Ptsxx00||'！*',1,240)); when others then raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240)); end;