CREATE OR REPLACE PROCEDURE SP_SF_BRTK00 ( ad_BRID00 in number, --病人ID ad_BRFB00 in number , --费别编码 ad_CZY000 IN number , --操作员编码 as_CZYXM0 IN char , --操作员姓名 ad_CZYKS0 IN number , --操作员所在科室 as_XM0000 IN char , --病人姓名 as_YHMSG0 out varchar , --存储过程提示的错误信息 as_SYSMSG out varchar --系统提示的错误信息 ) as -- MODIFICATION HISTORY -- Person Date Comments -- qiulf 2019.11.14 createed by MZSF-20190911-002 病人退卡 -- zhangyc 2020.02.20 参数：SF_MZICKYJDJFS=Y 时,退卡前，ICK费停用金额先恢复 by MZSF-20200220-002 -- qiulf 2020.03.23 修正退卡无法原路退 by MZSF-20200210-002 -- linbin 2020.04.30 建卡与退卡时参数 SF_MZICKYJDJFS 值不一致时，保证病人都能退到卡费 by MZSF-20200426-001 -- qiulf 2021.09.02 修正MZSF-20200426-001需求病人未收卡费，多退了卡费的问题 by MZSF-20210902-002 -- qiulf 2021.09.28 修正查询卡费时间慢的问题 by MZSF-20210928-002 LS_COUNT0 number(5); LS_ZYE000 SF_BRZHXX.ZYE000%type; --病人账户总余额 LS_ICKH00 IC_YHXX00.ICKH00%type; LS_ICKJE0 IC_YHXX00.ICKJE0%type; LS_SFDM00 IC_YHXX00.SFDM00%type; LS_SFQZTK XT_XTCS00.VALUE0%type; LS_ICKRQ0 IC_YWLS00.CZRQ00%type; --IC卡办卡的日前 LS_DJH000 SF_BRFY00.DJH000%type; LS_PJH000 SF_PJSYQK.PJH000%type; LS_GFKJE0 XT_XTCS00.VALUE0%type; --参数SF_GFBRKYJSFAZFBLLHQ的值(特约单位病人)) LS_FBBH00 BM_BRFBB0.FBBH00%type; --费别编码 ls_YBLB00 IC_YBBRLB.YBLB00%type; LS_YBZXLB IC_YBBRLB.YBZXLB%type; LV_ZFJE00 SF_FYMX00.ZFJE00%type; LV_JZJE00 SF_FYMX00.JZJE00%type; LS_CZRQ00 char(8); LS_CZSJ00 char(8); LS_MZICKYJDJFS XT_XTCS00.VALUE0%type; LS_ICKNBH IC_YHXX00.ICKNBH%TYPE; --IC卡内码 LS_XZBRFB XT_XTCS00.VALUE0%type; -- ls_TYJE00 SF_BRZHXX.TYJE00%type; LS_ZHTYJE SF_BRZHXX.TYJE00%type; begin LS_CZRQ00 := to_char(sysdate,'YYYYMMDD'); LS_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); begin select ','||trim(VALUE0)||',' into LS_XZBRFB from XT_XTCS00 where name00 = 'SF_KZMZFBBRBNTK'; --控制某种费别的病人不允许退款 exception when others then LS_XZBRFB:=',-1,'; end; If (ls_XZBRFB<>',-1,') then if instr(ls_XZBRFB,','||to_char(ls_FBBH00)||',')>0 THEN as_YHMSG0:='该费别的病人不允许清帐或退卡！'; as_SYSMSG:='该费别的病人不允许清帐或退卡！'||SQLERRM; rollback; return; end if; end if; begin select trim(VALUE0) into LS_SFQZTK from XT_XTCS00 where NAME00='SF_SFKYQZTK'; --能否进行退卡清账操作 exception when others then LS_SFQZTK:='Y'; end; begin select trim(VALUE0) into LS_GFKJE0 from XT_XTCS00 where NAME00='SF_GFBRKYJSFAZFBLLHQ'; exception when others then ls_GFKJE0:='N'; end; begin select FBBH00,trim(YBLB00) into LS_FBBH00,LS_YBLB00 from BM_BRXXB0 where BRID00=ad_BRID00; exception when others then as_YHMSG0:='该病人的信息不完全，请联系管理员！'; as_SYSMSG:=SQLERRM||'该病人的信息不完全，请联系管理员！！, select * from bm_brxxb0 where BRID00='||to_char(ad_BRID00); ROLLBACK; return; end; begin select YBZXLB into LS_YBZXLB from IC_YBBRLB where FBBH00=LS_FBBH00 and YBLB00=ls_YBLB00; exception WHEN NO_DATA_FOUND THEN as_YHMSG0:='系统字典维护不完整,请察看详细信息后与系统管理员联系！'; as_SYSMSG:=SQLERRM||'select * from IC_YBBRLB where YBLB00='||ls_YBLB00||' and BFFH00='||to_char(ls_fbbh00); ROLLBACK; return; end; begin select trim(VALUE0) into LS_MZICKYJDJFS from XT_XTCS00 where NAME00='SF_MZICKYJDJFS'; --门诊ICK押金是否以帐户冻结方式出现，默认为N exception when others then LS_MZICKYJDJFS :='N'; end; --判断病人是否有多个门诊预交金账户存在 select count(*) into LS_COUNT0 from SF_BRZHXX where BRID00 = ad_BRID00 and SFDJ00 <> '2'; if ls_COUNT0 > 1 then as_YHMSG0:='病人在门诊有多个有效的预交金账户，不能进行退卡操作！'; as_SYSMSG:='建议:可以先将其它的预交金账目进行清账后再进行退卡！'||SQLERRM; ROLLBACK; return; end if; --判断病人是否还有其它的门诊预交金余额,若有,提示用户先退还病人余额后,再进行本操作 select NVL(sum(ZYE000),0) into ls_ZYE000 from SF_BRZHXX where BRID00 = ad_BRID00 and JFLBID <> 1 and SFDJ00 <> '2'; if ls_ZYE000 > 0 then as_YHMSG0:='病人的门诊账户除了门诊预交金额账户外,还有其它的门诊账户的余额,不能对病人的账户进行停户处理！'; as_SYSMSG:='建议:先用预交金退款的功能将病人除了门诊预交金账户外的其它账户的所有的余额退还给病人后,再进行本操作！'; ROLLBACK; return; end if; --对就诊卡进行处理 begin select ICKH00,NVL(ICKJE0,0),NVL(SFDM00,0) into ls_ICKH00,ls_ICKJE0,ls_SFDM00 from IC_YHXX00 where BRID00 = ad_BRID00 and ZT0000 = '1'; exception when NO_DATA_FOUND then as_YHMSG0:='病人没有有效的就诊卡,系统操作失败！'; as_SYSMSG:=SQLERRM; ROLLBACK; return; end; --2020.02.20 zhangyc 退卡，处理ICK卡费停用处理--begin-- select NVL(sum(TYJE00),0) into LS_ZHTYJE from SF_BRZHXX where BRID00 = ad_BRID00 and JFLBID=1; if LS_MZICKYJDJFS in ('Y') then if nvl(LS_ICKJE0,0)>0 and nvl(LS_ZHTYJE,0)>0 then if nvl(LS_ICKJE0,0)>=nvl(LS_ZHTYJE,0) then ls_TYJE00:=0; else ls_TYJE00:=nvl(LS_ZHTYJE,0)-nvl(LS_ICKJE0,0); end if; Update SF_BRZHXX Set TYJE00 = ls_TYJE00 where BRID00 = ad_BRID00 and JFLBID = 1; end if; end if; --2020.02.20 zhangyc 退卡，处理ICK卡费停用处理--End-- --判断办卡的时间是否是2007.01.01以后办，若是则不给予退卡（泉州第三医院） if LS_SFQZTK='N' then select CZRQ00 into ls_ICKRQ0 from IC_YWLS00 where ICKH00=ls_ICKH00 and YWCZ00 in ('1','2'); if ls_ICKRQ0>='20070101' then as_YHMSG0:='此卡是2007年1月1日以后办的卡，不能进行退卡清账操作！'; ROLLBACK; RETURN; end if; end if; if (ls_SFDM00 > 0)and(ls_ICKJE0>0) THEN --添加就诊卡的退费处理 select SQ_SF_BRJFB0_DJH000.nextval into ls_DJH000 from dual; select SQ_SF_PJSYQK_PJH000.nextval into ls_PJH000 from dual; if (ls_GFKJE0='Y') and (ls_FBBH00 not in(1,3)) then --公费病人ICK金额是否按BM_YBSFDY中的ZFBL00来结算 select NVL(ZFBL00*ls_ICKJE0,0),NVL((1-ZFBL00)*ls_ICKJE0,0) into lv_ZFJE00,lv_JZJE00 from bm_ybsfdy where FBBH00=ls_FBBH00 AND SFYP00='N' and YBZXLB=ls_YBZXLB and sfxmid=ls_SFDM00; Insert into SF_BRFY00(DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00, CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00) values(LS_DJH000,'0',0,1,ad_BRID00,as_XM0000,ad_BRFB00,-ls_ICKJE0,decode(lv_ZFJE00,0,0,-lv_ZFJE00),decode(lv_JZJE00,0,0,-lv_JZJE00),0,0, LS_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,ad_CZYKS0,0); Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,ZXKS00,YSKS00) select SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,SFXMID,XMMC00,'',DW0000,ls_ICKJE0,-1,-ls_ICKJE0,decode(lv_ZFJE00,0,0,-lv_ZFJE00),0,decode(lv_JZJE00,0,0,-lv_JZJE00), ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_CZYKS0,ad_CZYKS0,ad_CZYKS0 from BM_YYSFXM where SFXMID = ls_SFDM00; else --如果原来ls_MZICKYJDJFS未开参数，已收卡费，退卡还是要退卡费。 select count(1) into LS_COUNT0 from SF_BRFY00 a,SF_FYMX00 b where a.DJH000=b.DJH000 and XMBH00+0=ls_SFDM00 and a.BRID00=ad_BRID00 and b.HJJE00>0; if ls_MZICKYJDJFS<>'Y' and LS_COUNT0>0 then Insert into SF_BRFY00(DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00, CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00) values(ls_DJH000,'0',0,1,ad_BRID00,as_XM0000,ad_BRFB00,-ls_ICKJE0,-ls_ICKJE0,0,0,0, ls_CZRQ00,ls_CZSJ00,ad_CZY000,as_CZYXM0,ad_CZYKS0,0); Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,ZXKS00,YSKS00) select SQ_SF_FYMX00_MXID00.nextval,ls_DJH000,SFXMID,XMMC00,'',DW0000,ls_ICKJE0,-1,-ls_ICKJE0,-ls_ICKJE0,0,0, ls_CZRQ00,ls_CZSJ00,ls_CZRQ00,ls_CZSJ00,ad_CZYKS0,ad_CZYKS0,ad_CZYKS0 from BM_YYSFXM where SFXMID = ls_SFDM00; END if; end if; end if; --若就诊卡有对应的费用时,OVER！ select ICKNBH into ls_ICKNBH from IC_YHXX00 where ICKH00 = ls_ICKH00; insert into IC_YWLS00(YWID00,ICKH00,YGBH00,YWCZ00,CZRQ00,CZSJ00,FSJE00,ICKNBH) values(SQ_IC_YWLS00_YWID00.nextval,ls_ICKH00,ad_CZY000,'5',ls_CZRQ00,ls_CZSJ00,ls_ICKJE0,ls_ICKNBH); exception when others then as_YHMSG0:='出错原因未知！请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_BRTK00('||to_char(ad_BRID00)||','|| to_char(ad_BRFB00)||','||to_char(ad_CZY000)||','||as_CZYXM0||','||to_char(ad_CZYKS0)||','||as_XM0000||','|| 'as_YHMSG0,as_SYSMSG)',1,150); ROLLBACK; end;