CREATE OR REPLACE TRIGGER TR_SF_SF_BRFY00_INSERT BEFORE INSERT ON SD_SF.SF_BRFY00 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare ls_ZZYE00 SF_BRZHXX.ZZYE00%type; --病人账户转账余额 ls_XJYE00 SF_BRZHXX.XJYE00%type; --病人账户现金余额 ls_YXQFJE SF_BRZHXX.YXQFJE%type; --病人账户上设置的允许欠费金额 ls_TYJE00 SF_BRZHXX.TYJE00%type; --病人账户上停用金额 ls_YBLB00 BM_BRXXB0.YBLB00%type; --病人的医保中心类别 ls_YBBRLB BM_BRXXB0.YBBRLB%type; --医保病人类别 ls_FBBH00 BM_BRXXB0.FBBH00%type; --费别编号 ls_YBQFJE SF_BRZHXX.YXQFJE%type; --医保病人新费用允许欠费的金额 ls_SFDJ00 SF_BRZHXX.SFDJ00%type; --病人账户状态 ls_WJSJE0 SF_BRZHXX.WJSJE0%type; --病人未结算的费用总额 ls_SJWJJE SF_BRZHXX.WJSJE0%type; --病人实际医保要进行计算的未结算的费用总额 ls_YBZHYE SF_BRZHXX.YBZHYE%type; --医保账户余额 ls_BRYKYE SF_BRZHXX.YKZYE0%type; --病人预扣余额 ls_YBYZF0 SF_BRZHXX.YBYZF0%type; --医保预支付金额 ls_YBYJS0 SF_BRZHXX.YBYJS0%type; --医保预结算金额 ls_DBJE00 SF_BRDB00.DBJE00%type; --病人担保金额 ls_XKDBJE SF_BRDB00.DBJE00%type; --病人担保金额 ls_MZZFSX BM_YBBRLB.ZYZFSX%type; --医保所设置的上限 ls_YBQFBZ BM_YBBRLB.YBQFBZ%type; --医保起付标准 ls_YBZFBL BM_YBBRLB.YBZFBL%type; --医保支付比例 ls_TYDWMC BM_BRXXB0.TYDWMC%type; --特约单位名称 ls_tydwid bm_brxxb0.tydwid%type; --特约单位ID ls_brid00 bm_brxxb0.brid00%type; --病人ID ls_ZZZJE0 sf_brjfb0.jfje00%type; --病人总共转帐金额 added by jlg ls_XJZJE0 sf_brjfb0.jfje00%type; --病人总共现金金额 added by zhr ls_ZYE000 SF_BRZHXX.ZYE000%type; --病人账户总余额 ls_MZZHID SF_BRZHXX.MZZHID%type; --门诊账户ID -- SF_JSATSBZ varchar(4); TJ_DWJFFS varchar(2); ls_count number; ls_count2 number; ls_count3 number; ls_SFYX00 CHAR(1); --异常声明 E_XXCX00 EXCEPtION; --查询语句问题 E_JEBG00 EXCEPTION; --病人预交金不够 E_ZHZT00 EXCEPTION; --病人账户状态 E_TSBZBH EXCEPTION; --病人特殊病种 E_TJTSXX EXCEPTION; E_BRXXTY EXCEPTION; --病人信息停用 ls_YBZHYE2 SF_BRZHXX.YBZHYE%type; --医保账户余额 add by zhangwz by MZSF-20101122-001 ls_MSGHF0 XT_XTCS00.VALUE0%TYPE; --免收挂号费参数 ls_FYDJJE NUMBER(12,2); --费用登记可用金额 ls_FYJE00 NUMBER(12,2); lv_XTCS00_VALUE0 XT_XTCS00.VALUE0%TYPE; --系统参数值 lv_SFJXJEBZXZ char(1) ; --是否进行金额不足限制 Y 是 N 否 LS_SFTXYEBZ char(1) ; ---Y:提示账户余额不足 N:不提示 LS_SFTHZZ varchar2(2); --更正费用时是否退回转账金额 Y:是 N:否 v_processid sys.V_$session.process%TYPE; v_programname SYS.V_$SESSION.program%TYPE; LS_YYID00 XT_YYXX00.YYID00%TYPE; LS_SFXNH0 IC_YBBRLB.SFXNH0%TYPE; LS_YXQFJESYSZ XT_XTCS00.VALUE0%TYPE; LS_YXQFJESYSZ00 varchar2(2); LS_YXQFJESYSZ01 varchar2(2); LS_YXQFJESYSZ02 varchar2(2); LS_SF_SFXMZXNANX XT_XTCS00.VALUE0%TYPE; LS_SF_SFXMZXNUX XT_XTCS00.VALUE0%TYPE; LS_TEMPVALUE0 XT_XTCS00.VALUE0%TYPE; LS_BRXB00 BM_BRXXB0.BRXB00%TYPE; E_CUSTOM exception; LS_ERROR varchar2(300); LS_ZFJE00 number(12,2); LS_JMJE00 number(12,2); LS_YBJJE0 number(12,2); LS_YBJJE1 number(12,2); LS_SFQFZX number(5); LS_TSBZJM Varchar2(1);--是否启用特殊病种减免 LS_SFTJBJ Varchar2(1);--是否提示补交 LS_TSBZBH YJ_YW0000.BH0000%TYPE; vmsgStr varchar2(200); LS_SF_BLGBFSFXMBH XT_XTCS00.VALUE0%TYPE; vPmstOpen varchar2(10); VMSTYE0 number(12,2); SF_SWFBQFJESZ varchar2(100); VFBBH00_Temp00 number(5); VYXQFJE_Temp00 number(12,2); Verrmsg varchar2(200); Ecustom exception; ls_SFQYYKTPT XT_XTCS00.VALUE0%TYPE; ls_YKTYE0 SF_BRZHXX.YKTYE0%TYPE; ls_YNKFJE SF_BRZHXX.XJYE00%TYPE; --院内扣费金额 ls_YXQFJE1 SF_BRZHXX.YXQFJE%type; SF_PJBRCXTZZ XT_XTCS00.VALUE0%type; ls_YXQFJE2 SF_BRZHXX.YXQFJE%type; --科室允许欠费药品费用 ls_XJYE00_OLD SF_BRZHXX.XJYE00%TYPE; ls_ZZYE00_OLD SF_BRZHXX.XJYE00%TYPE; -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2007.07.04 欠费金额病人过滤掉体检的病人; -- ZHR 2007.07.11 邵武人民要求退的时候先补回现金帐户 -- jlg 2007.10.31 煤矿中心医院要求医保病人挂号及诊察费允许欠费 -- zhr 2007.07.11 邵武人民要求退的时候先补回现金帐户，但余额不能超过现金预交总额，剩余的退转帐，也不能超过转帐预交总额 -- zhr 2007.11.24 煤矿医院要求从先退现金，但现金余额不能超过病人现金交费－卡押金。 -- jlg 2008.04.21 福州第六医院 bm_brxxb0.sfyx00='N'停用的病人不允许再收费MZSF-20080313-001 -- qks 2008.07.03 广东省中医院 该触发器不运行MZSF-20080703-001 -- qks 2008.07.15 广东省中医院 yyid00>'A'改为 yyid00>='A' -- XZW 2008.07.21 医大附二医院要求泉州离休病人挂号及诊察费允许欠费 -- qks 2008.07.22 广东省中医院 改用医院全称判断 -- qks 2008.12.01 沙县医院要求医保病人诊察费允许欠费 -- xzw 2009.04.07 德化医院要求医保病人挂号允许欠费 -- xzw 2009.04.17 医大附二要求所有病人挂号都允许挂号欠费 -- xzw 2009.06.10 修正医大附二第二次挂号无法欠费的问题，允许欠费挂号55元 -- zhr 2009.07.08 修正医大附二医保病人第二次挂号无法欠费的问题，允许欠费挂号55元，但自费可以 -- zhr 2009.11.06 所有病人都可以采用担保欠费 -- yangh 2009.11.09 血库担保金额写入到账户表的DBJE00(SSLB00='1') -- zhr 2010.04.20 担保金额计算做了整理 -- CSF 2010.05.14 莆田附属不允许欠费挂号 -- CSF 2010.07.09 仙游妇幼医保允许欠费挂号 -- zhangwz 2010.09.07 计算允许欠费金额,某些医院允许欠费金额特别处理 in line 200,else 的情况下ls_yxqfje由nvl(sum(a.hjje00),0)更改为greatest(nvl(sum(a.hjje00),0),ls_yxqfje). by MZSF-20100903-004. -- zhangwz 2010.12.06 在参数SF_YBGRZHZWYXQFJE="N"的情况下是收费员的员工过账时能使用上医保病人的账户余额. by MZSF-20101122-001 -- zhangyc 2011.01.13 增加参数SF_YBBRFYDJKYJE_PT>0莆田妇幼保健医保病人允许费用登记 -- zhangwz 2011.03.22 参数SF_YBBRFYDJKYJE_PT>0的值只有操作数据的员工为收费员时才起作用.by MZSF-20110322-001 应张杰要求这个不进行限制了 by zhangwz on 2011.03.28 -- zhangwz 2011.12.08 增加参数 YS_YJJYEW0SYXGHBRLB 控制某些类别的病人预缴金不足的时候挂号能成功，不进行挂号金额不足的限制. by MZYS-20111130-001. -- zhangyc 2011.12.21 增加参数 SF_MGHFYBLB00 某类病人免收挂号费,挂号时账户余额不足可以挂号 by MZSF-20110428-001 -- zhangyc 2012.03.15 费用更正时如有转账金额支付 更正后退回到院内转账金额(SF_BRZHXX.ZZYE00) BY MZSF-20120210-001 -- zhangyc 2012.04.28 修正公费病人转自费后，做费用更正时jzje00值被改变。增加条件（:new.hjje00>0） by MZSF-20120426-001 -- CSF 2012.10.17 将担保金额转为病人的可投资金额时候，过滤SF_BRDB00.CZY000=99999（体检系统使用）的担保数据。MZSF-20121019-002 -- liuj 2013.02.26 由于莆田区域特殊的医保扣费模式，造成收费员性质的操作员登录医技系统可以对医保病人欠费扣费，现要求限制医技系统门诊病人不能欠费扣费。for MZSF-20130226-001 -- zhangyc 2013.03.07 参数SF_YBBRFYDJKYJE_PT 启用后，不在触发器做限制，有前台做控制停用金额 by MZSF-20130306-001 -- zhangyc 2013.04.09 增加厦门同安中医院yyid=223280 控制欠费允许插入挂号费和诊查费 mzfpid=4和13 by MZSF-20130318-001 -- zhangyc 2013.08.29 增加参数控制SF_YXQFJESYSZ 控制某类病人允许欠费金额是否用来挂号 by MZSF-20130820-001 -- linzy 2014.11.19 增加指定收费项目根据性别限制不能收费 BY MZSF-20141110-001 -- chenyw 2014.12.31 参数SF_YXQFJESYSZ不影响体检病人 by MZSF-20141230-002 -- linzy 2015.01.22 余额不足提示加上停用金额部分 by MZSF-20141212-002 -- zhangyc 2015.04.10 还原MZSF-20130306-001需求修改的bug by MZSF-20150410-002 -- zhangyc 2015.06.30 增加血透析病种支付功能 by MZSF-20150630-001 -- qks 2015.08.06 扩展参数SF_YXQFJESYSZ功能 by MZSF-20150725-001 --zhangyc 2015.08.13 健康通改造 by MZSF-20150811-001 -- qks 2016.06.12 增加参数SF_TFXXJHZZ by MZSF-20160612-002 -- dsm 2016.08.16 SF_SWFBQFJESZ设置的病人的费用超过限额时,不能再记帐应该先去结账 for MZYS-20160815-003 -- zhangyc 2016.11.22 去掉健康通获取余额条件and DJH000=:new.mzid00 by MZSF-20161122-001 -- zhangyc 2017.03.08 增加SF_YBBRFYDJKYJE_PT开启后增加通用字典员工维护 BM_TYZD00.ZDMC00=门诊费用登记允许欠费收费员维护 by MZSF-20170308-001 -- xuzw 2017.03.28 一卡通平台院外金额当允许欠费金额使用 MZSF-20170329-001 -- qks 2017.07.11 新增表SF_BRZHLS记录病人账户变动流水信息; for MZSF-20170710-001 -- dsm 2017.11.15 增加自费病人不能有JZJE00的限制 for MZSF-20170929-001 -- dsm 2018.05.14 如果是医保病人JZJE00要强制设置成0 归档 MZSF-20180514-003 -- dsm 2019.09.20 欠费项目用BM_YYQFXM来判断 for MZYS-20190919-002 -- dsm 2019.11.28 医大附二挂号允许欠费的功能屏蔽 for MZSF-20191127-002 -- cyhong 2020.06.09 “病人账户金额不够，请先补交预交金”后面冒号修改为中文字符冒号 -- dsm 2020.09.16 MZYS-20121127-002需求对YS_YJJYEW0SYXGHBRLB格式改动后此脚本没有修改 for MZYS-20200825-002 -- qiulf 2020.11.16 择日住院的金额为转账时统计的金额实际为现金 by MZSF-20201116-003 -- qiulf 2020.12.07 SF_PJBRCXTZZ参数控制保健病人冲销时退转账 by MZSF-20201111-002 -- huangjy 2021.06.21 增加允许欠费的药品登记扣费 by YF-20210520-003 -- dsm 2022.05.13 修改SF_BRZHXX时不能直接用变量，改成相对值(会违法约束) 归档 MZHS-20220426-001 -- -------- ------------- ---------------------------------------------------------------------------------------- begin LS_JMJE00:=0; ls_YNKFJE:=0; Select to_number(userenv('SESSIONID')) PROCESS into v_processid from dual where rownum=1; select program into v_programname from sys.V_$session where process=v_processid or AUDSID= v_processid; select count(1) into ls_count from xt_yyxx00 where yyqc00 like '%广东省中医院%'; --广东省中医院 该触发器不运行 if (ls_count>0) and ((:NEW.JFLBID = 0) or (:NEW.JFLBID = 5)) then return; end if; begin --取指定收费项目只限男或女性 --男性收费项目 select nvl(max(trim(VALUE0)),'N') into LS_SF_SFXMZXNANX from XT_XTCS00 where NAME00='SF_SFXMZXNANX'; --女性收费项目 select nvl(max(trim(VALUE0)),'N') into LS_SF_SFXMZXNUX from XT_XTCS00 where NAME00='SF_SFXMZXNUX'; select SF_SF_GETYYID00() YYID00 into LS_YYID00 from dual; exception when others then LS_YYID00:='0'; end; begin select nvl(trim(VALUE0),'N') into SF_PJBRCXTZZ from XT_XTCS00 where NAME00='SF_PJBRCXTZZ'; exception when others then SF_PJBRCXTZZ:='N'; end; --是否启用一卡通平台（武夷山） BEGIN --select trim(value0) into ls_SFQYYKTPT from xt_xtcs00 where name00 = 'SF_SFQYYKTPT'; select trim(MC0000) into ls_SFQYYKTPT from BM_TYZD00 where ZDMC00='一卡通平台参数' and BH0000='SF_SFQYYKTPT' and rownum=1; EXCEPTION WHEN OTHERS THEN ls_SFQYYKTPT:='N'; END; --三无费别无限制欠费(即结算后允许欠费回滚到设置值)费别编号和金额设置(第一位费别编号,第二位欠费金额)格式:A,B 默认值:0,0 begin Select trim(VALUE0) into SF_SWFBQFJESZ from xt_xtcs00 where name00 in ('SF_SWFBQFJESZ'); VFBBH00_Temp00:=to_number(substrb(SF_SWFBQFJESZ,1,instrb(SF_SWFBQFJESZ,',')-1)); VYXQFJE_Temp00:=to_number(substrb(SF_SWFBQFJESZ,instrb(SF_SWFBQFJESZ,',')+1,10)); exception when others then SF_SWFBQFJESZ:='0,0'; VFBBH00_Temp00:=0; VYXQFJE_Temp00:=0; end; if VYXQFJE_Temp00>0 and :new.BRFB00=VFBBH00_Temp00 then select nvl(WJSJE0,0) into LS_WJSJE0 from SF_BRZHXX where BRID00=:new.BRID00 and JFLBID=1; if LS_WJSJE0>=VYXQFJE_Temp00 then Verrmsg:='此三无病人的未结算金额已经超过了'||VYXQFJE_Temp00||'元,请先到收费处结算!'; raise Ecustom; end if; end if; select YBLB00,YBBRLB,FBBH00,TYDWMC,SFYX00,trim(BRXB00) into ls_YBLB00,ls_YBBRLB,ls_FBBH00,ls_TYDWMC,ls_SFYX00,LS_BRXB00 from BM_BRXXB0 where BRID00=:NEW.BRID00; if ls_SFYX00='N' then RAISE E_BRXXTY; end if; --判断指定的收费项目只限男或女 MZSF-20141110-001 begin if LS_BRXB00='男' then select count(*) into ls_count from SF_FYMX00 where DJH000=:new.DJH000 and instr(','||trim(LS_SF_SFXMZXNUX)||',',','||to_char(XMBH00)||',')>0; LS_TEMPVALUE0:=LS_SF_SFXMZXNUX; elsif LS_BRXB00='女' then select count(*) into ls_count from SF_FYMX00 where DJH000=:new.DJH000 and instr(','||trim(LS_SF_SFXMZXNANX)||',',','||to_char(XMBH00)||',')>0; LS_TEMPVALUE0:=LS_SF_SFXMZXNANX; end if; LS_ERROR:=''; if ls_count>0 then for F_XMBH in (select XMMC00 from SF_FYMX00 where DJH000=:new.DJH000 and instr(','||trim(LS_TEMPVALUE0)||',',','||to_char(XMBH00)||',')>0) loop if LS_ERROR is null then LS_ERROR:=F_XMBH.XMMC00; else LS_ERROR:=LS_ERROR||','||F_XMBH.XMMC00; end if; end loop; if LS_BRXB00='男' then LS_ERROR:='指定项目【'||LS_ERROR||'】只限于女性使用！'; elsif LS_BRXB00='女' then LS_ERROR:='指定项目【'||LS_ERROR||'】只限于男性使用！'; end if; raise E_CUSTOM; end if; --判断指定的收费项目只限男或女 MZSF-20141110-001 end if :NEW.JFLBID = 0 or :NEW.JFLBID = 5 then--现金病人只处理记帐金额问题 select YBLB00,YBBRLB,FBBH00,TYDWMC into ls_YBLB00,ls_YBBRLB,ls_FBBH00,ls_TYDWMC from BM_BRXXB0 where BRID00=:NEW.BRID00; if (ls_FBBH00=1)or(ls_FBBH00=3)or(ls_FBBH00=90) then :NEW.ZFJE00:=:NEW.HJJE00-:NEW.GFJE00; :NEW.JZJE00:=0; update SF_FYMX00 set ZFJE00=HJJE00-GFJE00,JZJE00=0 where DJH000=:new.DJH000; end if; return; end if; if :NEW.JFLBID = 1 and :new.BRFB00=1 and :new.JZJE00<>0 then --省二发行自费病人会有记账金额bug MZSF-20170929-001 :NEW.JZJE00:=0; :NEW.ZFJE00:=:NEW.HJJE00-:NEW.GFJE00; update SF_FYMX00 set ZFJE00=HJJE00-GFJE00,JZJE00=0 where DJH000=:new.DJH000; end if; --说明:若病人有记账金额或其它支付的金额,从其它设定的地方扣除金额 --Modified on 2001-02-28,添加对医保病人欠费的处理 ls_YBQFJE:=0; /* begin select trim(value0) into SF_JSATSBZ from xt_xtcs00 where name00='SF_JSATSBZ'; EXCEPTION when others then SF_JSATSBZ:='N'; end; --if SF_JSATSBZ='Y' then begin select count(1) into ls_count from( select tsbzbh from VW_SF_YBTZBZ where djh000=:new.djh000 and tsbzbh is not null group by tsbzbh); exception when others then ls_count:=0; end; */ -- if ls_count>1 then -- RAISE E_TSBZBH; --先注释，省二允许多个特殊病种一起结算 -- end if; --end if; begin select trim(value0) into TJ_DWJFFS from xt_xtcs00 where name00='TJ_DWJFFS'; EXCEPTION when others then TJ_DWJFFS:='N'; end; begin select trim(value0) into LS_YXQFJESYSZ from xt_xtcs00 where name00='SF_YXQFJESYSZ'; EXCEPTION when others then LS_YXQFJESYSZ:='0,0'; end; --LS_YXQFJESYSZ01:=substrb(trim(LS_YXQFJESYSZ),1,1); --LS_YXQFJESYSZ02:=substrb(trim(LS_YXQFJESYSZ),3,1); LS_YXQFJESYSZ01:=substrb(trim(LS_YXQFJESYSZ),1,instrb(trim(LS_YXQFJESYSZ),',')-1); LS_YXQFJESYSZ02:=substrb(trim(LS_YXQFJESYSZ),instrb(trim(LS_YXQFJESYSZ),',')+1,1); if trim(TJ_DWJFFS)='N' then--体检病人特殊处理 if :new.BRFB00=99 then select trim(tydwmc),TYDWID into ls_tydwmc,ls_tydwid from BM_BRXXB0 where BRID00=:NEW.BRID00; if (trim(ls_tydwmc)<>'') or (trim(ls_tydwmc) is not null) then select brid00 into ls_brid00 from BM_TYDWB0 where dwid00=ls_tydwid; if ls_brid00<>0 then begin select GHID00,GHH000,BRID00,XM0000,FBBH00 into :new.mzid00,:new.mzh000,:new.brid00,:new.xm0000,:new.BRFB00 from sf_brxxb0 where brid00=ls_brid00 and rownum=1 order by ghid00 desc; exception when others then RAISE E_TJTSXX; end; end if; end if; end if; end if; --建阳特殊处理 if (ls_fbbh00=3) and (ls_TYDWMC in('建阳市本级离休、5.12、二乙人员','建阳市乡镇离休、512人员')) then --由于离休病人已经没有医保个人账户余额，所以这类病人只判断自付部分是否足够即可 ls_YXQFJE:=SF_SF_GETYBJE(:NEW.BRID00,:NEW.DJH000);--医保支付部分 update SF_BRZHXX set yxqfje=nvl(yxqfje,0)+nvl(ls_YXQFJE,0) where JFLBID=:NEW.JFLBID and BRID00=:NEW.BRID00; end if; select YBZXLB,SFXNH0 into ls_YBLB00,LS_SFXNH0 from IC_YBBRLB where YBLB00 = ls_YBLB00 and FBBH00 = ls_FBBH00; --查找医保中心类别 select NVL(XJYE00,0),NVL(ZZYE00,0),NVL(YXQFJE,0),SFDJ00,NVL(WJSJE0,0),NVL(TYJE00,0), NVL(YBZHYE,0),NVL(YKZYE0,0),NVL(YBYZF0,0),NVL(YBYJS0,0),NVL(DBJE00,0),NVL(YKTYE0,0), NVL(ZYE000,0),MZZHID into ls_XJYE00,ls_ZZYE00,ls_YXQFJE,ls_SFDJ00,ls_WJSJE0,ls_TYJE00, ls_YBZHYE,ls_BRYKYE,ls_YBYZF0,ls_YBYJS0,ls_XKDBJE,ls_YKTYE0, ls_ZYE000,ls_MZZHID from SF_BRZHXX where JFLBID=:NEW.JFLBID and BRID00=:NEW.BRID00; ls_XJYE00_OLD:=ls_XJYE00; ls_ZZYE00_OLD:=ls_ZZYE00; if ls_SFDJ00 <> '0' then RAISE E_ZHZT00; end if; if ((ls_FBBH00=1)or(ls_FBBH00=3)or(ls_FBBH00=90)) and (:new.hjje00<>0) then --2018.05.14 :new.hjje00>0-->:new.hjje00<>0 :NEW.ZFJE00:=:NEW.HJJE00-:NEW.GFJE00; :NEW.JZJE00:=0; update sf_fymx00 set zfje00=hjje00-gfje00,jzje00=0 where djh000=:new.djh000; end if; if (:new.brfb00<>3) and (:new.brfb00<>99) then if LS_YXQFJESYSZ01 in ('1','3') then --参数SF_YXQFJESYSZ第一位为1或3时 自费病人允许可用欠费金额 ls_YXQFJE:=ls_YXQFJE; else ls_YXQFJE:=0; --自费病人不允许欠费 end if; end if; ls_YBZHYE2:=ls_YBZHYE; --2008.07.01 zhr if ls_YBZHYE>0 and ls_FBBH00=3 then --医保病人的个人账户额作为允许欠费金额 select count(*) into ls_count from XT_XTCS00 where name00='SF_YBGRZHZWYXQFJE' and VALUE0='Y'; if ls_count=0 then ls_YBZHYE:=0; end if; else ls_YBZHYE:=0; end if; --计算医保预结算时医保预支付金额 if :NEW.SFYKBZ = '1' then --若是收费处登记的 --判断该病人是否为医保病人,若是,进行医保欠费金额的调整 ls_YBQFJE := ls_YBYZF0; /* BEGIN select NVL(YBQFBZ,0),NVL(YBZFBL,0),NVL(MZZFSX,0) into ls_YBQFBZ,ls_YBZFBL,ls_MZZFSX from BM_YBBRLB where YBZXLB = ls_YBLB00 and YBBRLB = ls_YBBRLB; if :NEW.ZFJE00+ls_WJSJE0 > ls_MZZFSX then --若病人的费用未超过上限,则 ls_SJWJJE:=ls_MZZFSX; else ls_SJWJJE:=:NEW.ZFJE00+ls_WJSJE0; end if; if ls_YBYJS0 >= ls_MZZFSX then --- 医保预结算金额大于住院支付上限时，医保欠费金额为医保预支付金额 ls_YBQFJE := ls_YBYZF0 ; else select NVL(ls_YBYZF0+(ls_SJWJJE - DECODE(SIGN(ls_YBYJS0-ls_YBQFBZ),1,ls_YBYJS0,ls_YBQFBZ))*ls_YBZFBL,0) into ls_YBQFJE from dual; end if; exception WHEN OTHERS then ls_YBQFJE:=0; end; if ls_YBQFJE < 0 then ls_YBQFJE:=0; end if; else ls_YBQFJE:=0; ls_YBZHYE:=0; */ end if; ----设置欠费金额使用----begin-----by MZSF-20130820-001-- LS_YXQFJESYSZ00:='N'; --病人账户允许欠费金额使用范围设置第一位病人类别1:所有病人2:所有医保病人 3:自费病人 4:医保病人（不包含农合) 5: 农合病人（不包含医保）6：城镇医保 第二位欠费类别 1:所有费用欠费 2:挂号欠费 3:非挂号欠费 默认值:2,1 所有医保病人全部可欠费 if (LS_YXQFJESYSZ01<>'0') and (nvl(ls_YXQFJE,0)<>0) and (:new.BRFB00<>99) then --体检病人不做处理 if (instrb(LS_YXQFJESYSZ01,'1')>0) --1:所有病人 or ((instrb(LS_YXQFJESYSZ01,'2')>0) and (ls_FBBH00=3)) --2:所有医保病人 or ((instrb(LS_YXQFJESYSZ01,'3')>0) and (ls_FBBH00=1)) --3:自费病人 or ((instrb(LS_YXQFJESYSZ01,'4')>0) and (ls_FBBH00=3) and (LS_SFXNH0<>'1'))--4:医保病人（不包含农合) or ((instrb(LS_YXQFJESYSZ01,'5')>0) and (ls_FBBH00=3) and (LS_SFXNH0='1')) --5: 农合病人（不包含医保） or ((instrb(LS_YXQFJESYSZ01,'6')>0) and (ls_YBBRLB='C')) then LS_YXQFJESYSZ00:='Y'; else LS_YXQFJESYSZ00:='N'; ls_YXQFJE:=0; end if; if LS_YXQFJESYSZ00='Y' then if LS_YXQFJESYSZ02='1' then ls_YXQFJE:=ls_YXQFJE; --挂号欠费 elsif LS_YXQFJESYSZ02='2' then select count(*) into ls_count2 from sf_fymx00 a,bm_yysfxm b where a.djh000=:new.djh000 and a.xmbh00=b.sfxmid and mzfpid in (4,6); if ls_count2>0 then ls_YXQFJE:=ls_YXQFJE; else ls_YXQFJE:=0; end if; --挂号及病历本欠费 elsif LS_YXQFJESYSZ02='4' then select nvl(trim(max(value0)),'0') into LS_SF_BLGBFSFXMBH from xt_xtcs00 where name00='SF_BLGBFSFXMBH'; select count(*) into ls_count2 from sf_fymx00 a,bm_yysfxm b where a.djh000=:new.djh000 and a.xmbh00=b.sfxmid and (mzfpid in (4,6) or to_char(a.XMBH00)=LS_SF_BLGBFSFXMBH); if ls_count2>0 then ls_YXQFJE:=ls_YXQFJE; else ls_YXQFJE:=0; end if; else select count(*) into ls_count2 from sf_fymx00 a,bm_yysfxm b where a.djh000=:new.djh000 and a.xmbh00=b.sfxmid and mzfpid not in (4,6); if ls_count2>0 then ls_YXQFJE:=ls_YXQFJE; else ls_YXQFJE:=0; end if; end if; end if; end if; ----设置欠费金额使用----end----by MZSF-20130820-001-- --计算允许欠费金额，以下医院允许欠费金额特别处理。 select nvl(max(to_number(YYID00)),0) into ls_count from xt_yyxx00 where yyid00 in ('225088','225785','222211','227320','223583','220034','225085','224465','225898'); --'223368'莆田附属,'225088'泉州人民医院,'225785'彭胡,'222211'平潭,'227320'邵武市妇幼保健院,'223583'仙游妇幼 '220034'煤矿中心医院 '225898' 德化医院 if ( ls_count=225088) then--所有病人允许欠费，但要求只能是用作挂号。 select nvl(sum(a.hjje00),0) into ls_yxqfje from sf_fymx00 a,bm_yysfxm b where djh000=:new.djh000 and a.xmbh00=b.sfxmid and mzfpid in (4,6); elsif ls_count=224465 and :new.brfb00=3 and ls_count>0 then select nvl(sum(a.hjje00),0) into ls_yxqfje from sf_fymx00 a,bm_yysfxm b where djh000=:new.djh000 and a.xmbh00=b.sfxmid and mzfpid in (6); --elsif (ls_count=225085) then --and (:new.brfb00=16) then --泉州医大附二要求离休病人挂号可以欠费 -- select nvl(sum(a.hjje00),0)+greatest(0,-ls_XJYE00-ls_ZZYE00) into ls_yxqfje from sf_fymx00 a,bm_yysfxm b -- where djh000=:new.djh000 and a.xmbh00=b.sfxmid and mzfpid in (4,6); -- ls_yxqfje:=least(ls_yxqfje,55); elsif (ls_count=225085) then select nvl(sum(a.hjje00),0)+greatest(0,-ls_XJYE00-ls_ZZYE00) into ls_yxqfje from sf_fymx00 a where djh000=:new.djh000 ; ls_yxqfje:=least(ls_yxqfje,50); elsif ls_count=223583 and :new.brfb00=3 then --只允许医保病人欠费，但要求只能是用作挂号。 select nvl(sum(a.hjje00),0) into ls_yxqfje from sf_fymx00 a,bm_yysfxm b where djh000=:new.djh000 and a.xmbh00=b.sfxmid and mzfpid in (4,6); elsif ls_yxqfje>0 and :new.brfb00=3 and ls_count>0 then--只允许医保病人欠费，但要求只能是用作挂号。 select greatest(nvl(sum(a.hjje00),0),ls_yxqfje) into ls_yxqfje from sf_fymx00 a,bm_yysfxm b where djh000=:new.djh000 and a.xmbh00=b.sfxmid and mzfpid in (4,6); end if; --福州铁路中心医院220019,没有限制必须是用作挂号 --计算担保金额 --通过Update ZY_BRDB00,使用该表的Trigger,来修改该病人的允许欠费金额 Update SF_BRDB00 set SFYX00 = 'N' where BRID00=:NEW.BRID00 and JFLBID=:NEW.JFLBID and SFYX00='Y' and NVL(DBJSRQ,to_char(SYSDATE,'YYYYMMDD'))||NVL(DBJSSJ,to_char(sysdate,'HH24:MI:SS')) < to_char(SYSDATE,'YYYYMMDD')||to_char(sysdate,'HH24:MI:SS') and CZY000<>99999; --若病人帐户的余额不够,这时要使用到允许欠费金额,由于欠费金额是从病人的担保信息中得到的,并且有时间限制,所以这时重新判断! select NVL(sum(DBJE00),0) into ls_DBJE00 from SF_BRDB00 where BRID00=:NEW.BRID00 and JFLBID=:NEW.JFLBID and SFYX00='Y' and NVL(DBJSRQ,to_char(SYSDATE,'YYYYMMDD'))||NVL(DBJSSJ,to_char(sysdate,'HH24:MI:SS'))>=to_char(SYSDATE,'YYYYMMDD')||to_char(sysdate,'HH24:MI:SS') and SSLB00='2' and CZY000<>99999; if ls_XKDBJE<>ls_DBJE00 then update SF_BRZHXX set DBJE00=ls_DBJE00 where BRID00=:NEW.BRID00 and JFLBID=:NEW.JFLBID; end if; if ls_YBZHYE2>ls_YBZHYE and ls_FBBH00=3 and ls_YBZHYE2>0 then --医保病人的个人账户额在收费员操作记账的时候能使用上 by MZSF-20101122-001 select count(*) into ls_count from XT_XTCS00 where name00='SF_MZBRFYDJSFSYYBZHYE' and VALUE0='Y'; if ls_count>0 then select count(*) into ls_count2 from BM_YGBM00 where YGBH00=:NEW.CZY000 and instr(YGXZ00,'3')>0; if ls_count2>0 then ls_YBZHYE:=ls_YBZHYE2; end if; end if; end if; ---取医保病人费用登记可用金额(收费员) ls_FYDJJE:=0; ls_count2:=0; begin select count(1) into ls_count2 from BM_YGBM00 where YGBH00=:NEW.CZY000 and instr(YGXZ00,'3')>0; exception when others then ls_count2 :=0; end; --by MZSF-20170308-001 增加通用字典维护 select count(1) into LS_SFQFZX from BM_TYZD00 where ZDMC00='门诊费用登记允许欠费收费员维护' and BH0000=to_char(:NEW.CZY000); if (v_programname='PrjMZ_MZSF.exe') or (nvl(LS_SFQFZX,0)>0) then begin select to_number(trim(value0)) into LS_FYJE00 from xt_xtcs00 where name00='SF_YBBRFYDJKYJE_PT'; exception when others then LS_FYJE00 :=0; end; end if; /* begin select trim(value0) into ls_FYJE00 from xt_xtcs00 where name00='SF_YBBRFYDJKYJE_PT' and (v_programname='PrjMZ_MZSF.exe'); exception when others then ls_FYJE00 :=0; end; */ --ls_FYJE00:=0; --by MZSF-20130306-001 if (ls_FYJE00<>0) and (ls_count2=0) then --by MZSF-20110322-001 ls_FYJE00 :=0; end if; if (ls_FBBH00=3)and (ls_count2>0) then ls_FYDJJE:=ls_FYJE00; else ls_FYDJJE:=0; end if; if (ls_FYJE00<>0) and (ls_FBBH00=3) then select nvl(sum(a.hjje00),0) into ls_yxqfje from sf_fymx00 a,bm_yysfxm b where djh000=:new.djh000 and a.xmbh00=b.sfxmid and mzfpid in (4,6); end if; if (ls_count2=0) and (ls_FYJE00<>0) and (:new.brfb00=3) then select count(*) into ls_count3 from sf_fymx00 a,bm_yysfxm b where djh000=:new.djh000 and a.xmbh00=b.sfxmid and mzfpid in (4,6); if ls_count3>0 then ls_FYDJJE:=ls_FYJE00; end if; end if; -----------------------------------------begin 判断是否为允许金额不足也可以挂号成功的病人类别 ---------------- begin select value0 into lv_XTCS00_VALUE0 from XT_XTCS00 where name00='YS_YJJYEW0SYXGHBRLB'; exception when others then lv_XTCS00_VALUE0 :='N'; end; lv_SFJXJEBZXZ:='Y'; if trim(lv_XTCS00_VALUE0)<>'N' then begin begin select instr('#'||trim(lv_XTCS00_VALUE0)||'#','#'||to_char(FBBH00)||';'||YBZXLB||';'||YBBRLB||'#') into ls_count from SF_BRXXB0 where GHID00=:new.MZID00; exception when others then ls_count:=0; end; if ls_count=0 then begin select instr('#'||trim(lv_XTCS00_VALUE0)||'#','#'||to_char(FBBH00)||'#') into ls_count from SF_BRXXB0 where GHID00=:new.MZID00; exception when others then ls_count:=0; end; end if; if ls_count>0 then begin --厦门同安中医院诊查费MZFPID=13 YYID=223280 if trim(LS_YYID00) in ('223280') then select count(*) into ls_count2 from sf_fymx00 a,bm_yysfxm b --说明全部为挂号费 where djh000=:new.djh000 and a.xmbh00=b.sfxmid and b.mzfpid<>4 and b.MZFPID<>13; else select count(*) into ls_count2 from sf_fymx00 a,bm_yysfxm b --说明全部为挂号费 where djh000=:new.djh000 and a.xmbh00=b.sfxmid and b.mzfpid<>4 and b.MZFPID<>6; end if; if ls_count2=0 then begin lv_SFJXJEBZXZ:='N'; end; end if; end; end if; end; end if; -----------------------------------------end 判断是否为允许金额不足也可以挂号成功的病人类别 ---------------- -------by MZSF-20110428-001---免收挂号费,诊查费账户余额不够,不做限制SF_MGHFYBLB00(ic_ybbrlb.yblb00)-----begin-------- LS_SFTXYEBZ:='Y'; begin select trim(VALUE0) into ls_MSGHF0 from XT_XTCS00 where name00='SF_MGHFYBLB00'; exception when others then ls_MSGHF0:=null; end; if ls_MSGHF0 is not null then Select YBLB00 into ls_yblb00 from bm_brxxb0 where brid00=:NEW.BRID00; if Instrb(','||trim(ls_MSGHF0)||',',','||trim(ls_yblb00)||',')>0 then --厦门同安中医院诊查费MZFPID=13 YYID=223280 if trim(LS_YYID00) in ('223280') then select count(*) into ls_count2 from sf_fymx00 a,bm_yysfxm b --挂号费和诊查费 where djh000=:new.djh000 and a.xmbh00=b.sfxmid and b.mzfpid in (4,13); else select count(*) into ls_count2 from sf_fymx00 a,bm_yysfxm b --挂号费和诊查费 where djh000=:new.djh000 and a.xmbh00=b.sfxmid and b.mzfpid in (4,6); end if; if ls_count2=0 then --不是挂号费和诊查费,就要提示账户余额不足 LS_SFTXYEBZ:='Y'; else LS_SFTXYEBZ:='N'; end if; else LS_SFTXYEBZ:='Y'; end if; end if; ls_YXQFJE2:=0; -------by MZSF-20110428-001---免收挂号费,诊查费账户余额不够,不做限制SF_MGHFYBLB00(ic_ybbrlb.yblb00)-----end-------- --允许欠费项目判断 select nvl(sum(ZFJE00),0) into ls_YXQFJE1 from sf_fymx00 a where djh000=:new.djh000 and exists(select 1 from BM_YYQFXM where MZZYBZ in('0','2') and SFXMID=a.XMBH00 and TZRQ00 is null ) and exists(select 1 from XT_XTCS00 where name00='YS_ZYBZLZXMYXQF' and VALUE0='Y'); --允许欠费的药品费用 select nvl(sum(round(b.YPZSL0*b.LSDJ00,2)),0) into ls_YXQFJE2 from YF_MZCF00 a,YF_MZCFMX b where a.CFLSH0=b.CFLSH0 and a.DJH000=:new.DJH000 and exists(select 1 from BM_YD0000 where SFYXQF='1' and YPNM00=b.YPNM00 and TZRQ00 is null ) and exists(select 1 from BM_TYZD00 where ZDMC00='允许欠费发药的科室' and BH0000=a.GHKS00 and YXBZ00='1'); ----------------------获取健康通(民生通)余额------------begin---------------- VMSTYE0:=0; begin select Trim(MC0000) into vPmstOpen from BM_TYZD00 where ZDMC00='民生通系统参数' and BH0000='PmstOpen'; exception when others then vPmstOpen:='N'; end; if vPmstOpen='Y' then begin select nvl(YKTYE0,0) into VMSTYE0 from SF_YKTKFB where BRID00=:NEW.BRID00 and MKDM00='0'; --and DJH000=:new.mzid00; exception when others then VMSTYE0:=0; end; end if; ----------------------获取健康通(民生通)余额------------end---------------- --2015.06.29 增加血透析病种支付功能 by MZSF-20150630-001 LS_ZFJE00:=:NEW.ZFJE00; IF ls_SFQYYKTPT = 'Y' THEN LS_YBJJE0:=(NVL(VMSTYE0,0)+NVL(ls_FYDJJE,0)+NVL(ls_XJYE00,0) + NVL(ls_ZZYE00,0) + NVL(ls_YXQFJE,0) + NVL(ls_YBQFJE,0) + NVL(ls_YBZHYE,0))-ls_TYJE00+ NVL(ls_DBJE00,0) + NVL(ls_YKTYE0,0)+ls_YXQFJE1+ls_YXQFJE2; LS_YBJJE1:=NVL(VMSTYE0,0)+NVL(ls_XJYE00,0) + NVL(ls_ZZYE00,0) + NVL(ls_YXQFJE,0) + NVL(ls_YBQFJE,0) + NVL(ls_DBJE00,0)-NVL(ls_TYJE00,0) + NVL(ls_YKTYE0,0)+ls_YXQFJE1+ls_YXQFJE2; ELSE LS_YBJJE0:=(NVL(VMSTYE0,0)+NVL(ls_FYDJJE,0)+NVL(ls_XJYE00,0) + NVL(ls_ZZYE00,0) + NVL(ls_YXQFJE,0) + NVL(ls_YBQFJE,0) + NVL(ls_YBZHYE,0))-ls_TYJE00+ NVL(ls_DBJE00,0)+ls_YXQFJE1+ls_YXQFJE2; LS_YBJJE1:=NVL(VMSTYE0,0)+NVL(ls_XJYE00,0) + NVL(ls_ZZYE00,0) + NVL(ls_YXQFJE,0) + NVL(ls_YBQFJE,0) + NVL(ls_DBJE00,0)-NVL(ls_TYJE00,0)+ls_YXQFJE1+ls_YXQFJE2; END IF; LS_TSBZJM:='N';--是否启用病种减免 LS_SFTJBJ:='Y';--是否提示金额不足 LS_JMJE00:=0; --减免金额 -----1-----begin-----and (v_programname='PrjMZ_MZSF.exe') if (:NEW.BRFB00=3) and (:NEW.ZFJE00>0) then select SF_SF_XTCS00(:new.BRID00,'SF_YBJKQYJMSF')into LS_TSBZJM from dual; -----1.1-----begin----- if LS_TSBZJM='Y' then --参数启用 --获取看下是否是插入病种减免 begin select count(1) into LS_COUNT from sf_fymx00 a,bm_yysfxm b where djh000=:new.djh000 and a.xmbh00=b.sfxmid and a.jmbz00='5'; exception when others then LS_COUNT:=0; end; if LS_COUNT>0 then LS_SFTJBJ:='N'; else LS_SFTJBJ:='Y'; end if; -----1.1.1-----begin----- if LS_SFTJBJ='Y' then begin select max(BH0000)TSBZBH,count(1) into LS_TSBZBH,LS_COUNT from YJ_YW0000 where SFDJH0=:new.djh000 and BRID00=:new.BRID00 and BH0000 is not null and BH0000 in (Select BH0000 from BM_TYZD00 where ZDMC00='医院减免优惠特殊病种维护'); exception when others then LS_TSBZBH:=''; LS_COUNT:=0; end; -----1.1.1.1-----begin----- --提示补交金额时要扣除掉医院优惠10% if LS_COUNT>0 then LS_JMJE00:=ROUND(:NEW.ZFJE00*0.1,2); LS_ZFJE00:=LS_ZFJE00-LS_JMJE00; end if; -----1.1.1.1-----end----- end if; -----1.1.1-----end----- end if; -----1.1-----end----- end if; -----1-----end----- if :NEW.ZFJE00>0 and LS_YBJJE0ls_YXQFJE2 and ls_YXQFJE2=0 then vmsgStr:='病人账户金额不够，请先补交预交金： '||trim(to_char(nvl(LS_ZFJE00,0)-nvl(LS_YBJJE1,0),'9999990.99'))||'元,再进行本操作！'; RAISE E_JEBG00; end if; --病人余额先修改 :NEW.BRYE00:= ls_XJYE00 + ls_ZZYE00 - :NEW.ZFJE00; --允许欠费金额的修改 /* if (ls_XJYE00 + ls_ZZYE00) > :NEW.ZFJE00 then --若病人没有欠费,允许欠费没有变化 ls_YXQFJE:=ls_YXQFJE+ls_YBQFJE; else ls_YXQFJE:=ls_YXQFJE+ls_YBQFJE-(:NEW.ZFJE00-ls_XJYE00 - ls_ZZYE00);--若病人有欠费,允许欠费发生变化 end if; */ if :NEW.ZFJE00 > 0 then :new.ZZZHZF:=least(:NEW.ZFJE00,ls_ZZYE00);--转帐帐户支付金额 if ls_ZZYE00 < :NEW.ZFJE00 then --转账余额不够,先扣完转账余额后，再扣现金余额 ls_XJYE00 := ls_XJYE00-(:NEW.ZFJE00 - ls_ZZYE00); ls_ZZYE00 := 0; else ls_ZZYE00 := ls_ZZYE00 - :NEW.ZFJE00; end if; else -- ls_XJYE00 := ls_XJYE00-:NEW.ZFJE00; --若是冲销的，直接转到现金余额上 -- 上一句被注掉，改为如下 by jlg for MZSF-20050801-002 -- 先取该病人之前所有转帐的总金额 select count(*) into ls_count from xt_yyxx00 where (YYJC00 like '%邵武%人民%' or YYJC00 ='福建省煤矿中心医院'); if ls_count = 0 then select count(1) into ls_count from xt_xtcs00 where name00='SF_TFXXJHZZ' and value0='Y'; end if; if ls_count=0 then --保健病人走转账余额 if (SF_PJBRCXTZZ ='Y') and (instr(ls_TYDWMC,'保健')>0) then ls_ZZYE00:=ls_ZZYE00 - :NEW.ZFJE00; else if (:NEW.CXYY00='SP_SF_BRTF00_CFD000') or (:NEW.CXYY00='SP_SF_YJGZ00_YJD000') or (:NEW.CXYY00='SP_SF_BRTF00_REG000') then LS_SFTHZZ:='Y'; else LS_SFTHZZ:='N'; end if; if LS_SFTHZZ='Y' then ---费用更正时,由过程SP_SF_BRTF00_CFD000,SP_SF_YJGZ00_YJD000,SP_SF_BRTF00_REG000中处理转账金额 ls_ZZYE00:=ls_ZZYE00 - :new.ZZZHZF; ls_XJYE00:=ls_XJYE00-(:NEW.ZFJE00-:new.ZZZHZF); else --按原来方式处理转账金额 --退的时候先补回转帐余额，退的金额超出转帐总金额的部分则退回到现金余额 select NVL(sum(decode(ZPDWMC,'择日住院预交金转出',0,JFJE00)),0) into ls_ZZZJE0 from sf_brjfb0 where BRID00=:NEW.BRID00 and ZFFS00='2'; if ls_ZZYE00-:NEW.ZFJE00 > ls_ZZZJE0 then :new.ZZZHZF := ls_ZZYE00-ls_ZZZJE0; ls_XJYE00 := -:NEW.ZFJE00+ls_XJYE00-(ls_ZZZJE0-ls_ZZYE00); ls_ZZYE00 := ls_ZZZJE0; else :new.ZZZHZF := :NEW.ZFJE00; ls_ZZYE00 := ls_ZZYE00 - :NEW.ZFJE00; end if; end if; end if; else --邵武人民医院:退的时候先补回现金帐户，但余额不能超过现金预交总额，剩余的退转帐，也不能超过转帐预交总额 zhr 2007.07.11 启用 select sum(decode(ZFFS00,'2',JFJE00,0)),sum(decode(ZFFS00,'2',0,JFJE00)) into ls_ZZZJE0,ls_XJZJE0 from sf_brjfb0 where BRID00=:NEW.BRID00 and jflbid=1 ; --现金账户必须保留卡押金 select ls_XJZJE0-sum(ickje0) into ls_XJZJE0 from ic_yhxx00 where brid00=:new.brid00 and rownum=1 and not exists (select 1 from sf_fymx00 where djh000=:new.djh000 and xmbh00=sfdm00); if ls_XJYE00-:NEW.ZFJE00 > ls_XJZJE0 and (ls_ZZYE00+(ls_XJYE00-:NEW.ZFJE00 - ls_XJZJE0)>ls_ZZZJE0) then --现金余额超过现金预交,转帐余额超转帐预交 :new.ZZZHZF := ls_ZZYE00-ls_ZZZJE0; ls_XJYE00 := -:NEW.ZFJE00+ls_XJYE00-(ls_ZZZJE0-ls_ZZYE00); ls_ZZYE00 := ls_ZZZJE0; elsif ls_XJYE00-:NEW.ZFJE00 > ls_XJZJE0 then --现金余额超过现金预交,转帐余额未超转帐预交 :new.ZZZHZF :=:NEW.ZFJE00+(ls_XJZJE0-ls_XJYE00); ls_ZZYE00 := -:NEW.ZFJE00+ls_ZZYE00-(ls_XJZJE0-ls_XJYE00); ls_XJYE00 := ls_XJZJE0; else --现金余额未超过现金预交 :new.ZZZHZF := 0; ls_XJYE00 := ls_XJYE00 - :NEW.ZFJE00; end if; end if; end if; --暂时的 Update SF_BRZHXX set XJYE00 =XJYE00-(ls_XJYE00_OLD-ls_XJYE00), --病人现金余额 ZZYE00 =ZZYE00-(ls_ZZYE00_OLD-ls_ZZYE00), --病人转账余额 --YXQFJE = ls_YXQFJE, --允许病人欠费金额,不做修改,由于病人帐户余额有变化 --YBYJS0 = 0, zhr 2005.04.07 -->挪到SP_SF_BRFY00_REG000来处理 --YBYZF0 = 0, --YBZHYE = 0, WJSJE0 = WJSJE0 + :NEW.ZFJE00, --未结算金额 ZYE000 = ZYE000 - :NEW.ZFJE00, --账户实际总余额 YKZYE0 = YKZYE0 - :NEW.ZFJE00 --账户预扣总余额 where JFLBID =:NEW.JFLBID and BRID00 =:NEW.BRID00; if :NEW.ZFJE00<>0 then insert into SF_BRZHLS(ID0000,MZZHID,BRID00,JYLX00,JYDJH0,JYQZYE,JYJE00,JYHZYE) values(SQ_SF_BRZHLS_ID0000.nextval,ls_MZZHID,:NEW.BRID00,'2',:NEW.DJH000,ls_ZYE000,:NEW.ZFJE00,ls_ZYE000 - :NEW.ZFJE00); end if; /* if :NEW.SFYKBZ = '0' then --若要有预扣的 Update SF_BRZHXX set XJYE00 = ls_XJYE00, --病人现金余额 ZZYE00 = ls_ZZYE00, --病人转账余额 --YXQFJE = ls_YXQFJE, --允许病人欠费金额,不做修改,由于病人帐户余额有变化 WJSJE0 = WJSJE0 + :NEW.ZFJE00, --未结算金额 ZYE000 = ZYE000 - :NEW.ZFJE00, --账户实际总余额 YKZYE0 = YKZYE0 - :NEW.ZFJE00 --账户预扣总余额 where JFLBID =:NEW.JFLBID and BRID00 =:NEW.BRID00; else Update SF_BRZHXX set XJYE00 = ls_XJYE00, --病人现金余额 ZZYE00 = ls_ZZYE00, --病人转账余额 --YXQFJE = ls_YXQFJE, --允许病人欠费金额 WJSJE0 = WJSJE0 + :NEW.ZFJE00, --未结算金额 ZYE000 = ZYE000 - :NEW.ZFJE00 --账户实际总余额 where JFLBID =:NEW.JFLBID and BRID00 =:NEW.BRID00; end if; */ exception when E_CUSTOM then raise_application_error(-20907,LS_ERROR); rollback; when no_data_found then raise_application_error(-20901,'查找不到病人的账户信息,JFLBID='||to_char(:NEW.JFLBID)||',BRID00='||to_char(:NEW.BRID00)||'！*'); rollback; when E_XXCX00 then raise_application_error(-20902,'病人的信息查找不到!'||'！*'); rollback; when E_ZHZT00 then raise_application_error(-20902,'病人的账户状态不是有效的状态,请到收费处将该病人账户状态设置成有效的!'||'！*'); rollback; when E_JEBG00 then --raise_application_error(-20903,'病人账户金额不够，请先补交预交金: '||trim(to_char(nvl(:new.zfje00,0)-(NVL(ls_XJYE00,0) + NVL(ls_ZZYE00,0) + NVL(ls_YXQFJE,0) + NVL(ls_YBQFJE,0) + NVL(ls_DBJE00,0)-ls_TYJE00),'9999990.99'))||'元,再进行本操作！'||'！*'); raise_application_error(-20903,vmsgStr||'！*'); rollback; when E_TSBZBH then raise_application_error(-20904,'病人存在两种特殊病种以上一起登记，请分开登记'||'！*'); rollback; when E_TJTSXX then raise_application_error(-20905,'特约单位指定的共用病人在SF_BRXXB0中没有记录'||'！*'); rollback; when E_BRXXTY then raise_application_error(-20906,'该病人信息已无效BM_BRXXB0.SFYX00 = N!'||'！*'); rollback; when Ecustom then raise_application_error(-20908,substrb(Verrmsg||'！*',1,160)); rollback; when OTHERS then raise_application_error(-20907,substr(nvl(sqlerrm,'出错原因不详'),1,40)||'，请记录此信息并和系统管理员联系！'||'！*'); rollback; end; /