CREATE OR REPLACE TRIGGER TR_ZY_ZY_BRFY00_INSERT BEFORE INSERT ON SD_ZY.ZY_BRFY00 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare ls_ZZYE00 ZY_BRZHXX.ZZYE00%type; --病人账户转账余额 ls_XJYE00 ZY_BRZHXX.XJYE00%type; --病人账户现金余额 ls_YXQFJE ZY_BRZHXX.YXQFJE%type; --病人账户上设置的允许欠费金额 ls_TYJE00 ZY_BRZHXX.TYJE00%type; --病人账户上停用金额 ls_YBLB00 BM_BRXXB0.YBLB00%type; --病人的医保中心类别 ls_YBBRLB BM_BRXXB0.YBBRLB%type; --医保病人类别 ls_FBBH00 BM_BRXXB0.FBBH00%type; --费别编码 ls_YBQFJE ZY_BRZHXX.YXQFJE%type; --医保病人新费用允许欠费的金额 ls_YBQFJE1 ZY_BRZHXX.YXQFJE%type; --医保病人新费用允许欠费的金额 ls_SFDJ00 ZY_BRZHXX.SFDJ00%type; --病人账户状态 ls_WJSJE0 ZY_BRZHXX.WJSJE0%type; --病人未结算的费用总额 ls_SJWJJE ZY_BRZHXX.WJSJE0%type; --病人实际医保要进行计算的未结算的费用总额 ls_YBZHYE ZY_BRZHXX.YBZHYE%type; --医保帐户余额 ls_BRYKYE ZY_BRZHXX.YKZYE0%type; --病人预扣余额 ls_YBYZF0 ZY_BRZHXX.YBYZF0%type; --医保预支付金额 ls_YBYJS0 ZY_BRZHXX.YBYJS0%type; --医保预结算金额 ls_DBJE00 ZY_BRDB00.DBJE00%type; --病人担保金额 ls_ZYZFSX BM_YBBRLB.ZYZFSX%type; --医保所设置的上限 ls_YBQFBZ BM_YBBRLB.YBQFBZ%type; --医保起付标准 ls_YBZFBL BM_YBBRLB.YBZFBL%type; --医保支付比例 --以下是新增加的 ls_SFJSDW IC_YBBRLB.SFJSDW%TYPE; --判断是否结算单位 ls_YBZFZE ZY_BRFY00.HJJE00%TYPE; --医保支付的总金额 ls_YBGRFD ZY_BRFY00.HJJE00%TYPE; --医保支付的总金额中个人支付 ls_GRXJZF ZY_BRFY00.HJJE00%TYPE; --非医保支付部分的总金额 ls_sumje0 zy_fymx00.hjje00%type; --费用明细SUM金额 ls_ybzxlb bm_ybbrlb.ybzxlb%type; ls_zyzfsx_02 bm_ybbrlb.zyzfsx%type; --住院自付上限 ls_zyzfsx_01 bm_ybbrlb.zyzfsx%type; --住院自付上限 ls_ZYYJXX bm_ybbrlb.ZYYJXX%type; --住院预交金下限 ls_JFJE00 ZY_BRJFB0.JFJE00%type; --预交金总额 ZY_SFYGFSX varchar2(10); --是否有公费上限 ls_TMPSTR VARCHAR2(100); ls_sfqfxm number(14,4); ls_sfzzdbqf number(10); --是否是真正的欠费担保 ls_rqdsjd char(8); --日清单分割时间点 ls_qzsf varchar2(1); -- 是否强制收费 LS_DQBQ00 BM_BMBM00.BMBH00%type; --当前病区 LS_YSZID0 ZS_YSZ000.YSZID0%type; --医生组ID LS_KDBQ00 BM_BMBM00.BMBH00%type; --开单病区 LS_ZYID00 ZY_BRXXB0.ZYID00%type; --住院id LS_HJJE00 ZY_JZB000.HJJE00%type; --合计金额 --异常声明 E_JEBG00 EXCEPTION; --病人预交金不够 E_ZHZT00 EXCEPTION; --病人账户状态 E_FYYMX0 EXCEPTION; --费用与明细不一致 E_QCSXJE EXCEPTION; --超出上限金额 E_custom EXCEPTION; --非收治病人 LS_TTZHYE number(12,0); --团体可用余额 LS_TTBRID number(10); --团体病人ID LS_TTLX00 char(1); --团体类型 1:团体信息 0或null 团体成员信息 LS_SFTTCY number(10); --是否团体成员 0:不是 1:团体主账户 2:团体成员账户 LS_KYE000 ZY_BRZHXX.YKZYE0%type; VKYJESF char(1); Vbrzt00 zy_brxxb0.brzt00%type; LS_BZYBZX varchar2(2); LS_ZFJE00 zy_BRFY00.hjje00%type; --费用自费金额 ls_BZWFY0 zy_BRFY00.hjje00%type; --费用自费金额 LS_YJJE00 zy_BRFY00.hjje00%type; --费用自费金额 LS_DBZCWF ZY_BRFY00.DBZCWF%type;--单病种超标床位费 LS_DBZHCF ZY_BRFY00.DBZHCF%type;--单病种可另收材料 LS_DBZBM0 ZY_BRXXB0.DBZBM0%type; ls_SFXNH0 IC_YBBRLB.SFXNH0%type; LS_YBHDJE BM_DBZDEB.YBHDJE%type; LS_BRZTKQ BM_BRXXB0.BRZTQK%type; VBYXQFYBBR number(5); VKYJESF_DBZ varchar2(10); LS_LWKQFJE zy_BRFY00.hjje00%type; XT_ZFBRBYXTZ varchar2(10); LS_YYID00 XT_YYXX00.YYID00%type; LS_ERROR varchar2(100); LS_BMXZ00 BM_BMBM00.BMXZ00%type; ls_er char(150); -- MODIFICATION HISTORY -- Person Date Comments -- xzw 2007.07.04 注释掉"判断ZY_FYMX00与ZY_BRFY00中HJJE00不一致的时候"的情况 -- zhr 2008.02.05 SELECT FROM ZY_BRZHXX 增加FOR UPDATE,避免并发时，余额错误。 -- qks 2009.11.24 当BM_YBBRLB.ZYYJXX>0时：如果住院预交金总额0才判断YSZID0 for ZYSF-20160620-007 -- dsm 2017.03.26 单病种ZY_BRFY00.BZWFY0必须写入才能判断病种外费用的可用额for BQ-20170216-001 -- dsm 2017.07.24 LS_KYE000:=LS_YJJE00-ls_BZWFY0-ls_YBQFJE1+ls_YXQFJE-->LS_YJJE00-ls_YBQFJE1+ls_YXQFJE+1 省二归档MZYS-20170711-002 -- dsm 2017.08.07 自费,农合单病种病人如果未交够定额的预交金时,只能用不大于预交金总额的费用 for BQ-20170707-001 -- dsm 2018.02.27 城乡居民(BM_TYZD00.ZDMC00='医保预结算金额不做为透支金额的病人')不能做医保欠费 for BQ-20180227-002 -- dsm 2018.04.16 单病种病人的余额计算有问题加elsif ls_WJSJE0ls_rqdsjd THEN :new.QDRQ00:=to_char(to_date(:new.CZRQ00,'YYYYMMDD')+1,'YYYYMMDD'); end if; if :NEW.JFLBID = 0 or :NEW.JFLBID = 5 then --系统对于现金病人，不考虑其预交金账户的情况 return; end if; --说明:若病人有记账金额或其它支付的金额,从其它设定的地方扣除金额 --Modified on 2001-02-28,添加对医保病人欠费的处理 ls_YBQFJE:=0; ls_YBGRFD:=0; ls_zyzfsx_01:=0; ls_zyzfsx:=0; ls_TMPSTR:=''; begin select trim(value0) into ZY_SFYGFSX from xt_xtcs00 where name00='ZY_SFYGFSX'; exception when others then ZY_SFYGFSX:='N'; end; SELECT Z.BRZT00 INTO Vbrzt00 FROM ZY_BRXXB0 Z WHERE Z.ZYID00=:NEW.ZYID00; if substr(Vbrzt00,1,1) not in ('1','2') then select sum(HJJE00) into LS_HJJE00 from ZY_JZB000 a where a.ZYID00=:NEW.ZYID00; if LS_HJJE00 = 0 and :new.HJJE00>0 then --增加 :new.HJJE00>0允许费用更正ZYSF-20190418-007 LS_ERROR:= '病人已出科但没有经过结算，不能进行费用变更'; Raise E_custom; end if; end if; select BMXZ00 into ls_BMXZ00 from BM_BMBM00 where BMBH00=:new.CZYKS0; if substr(Vbrzt00,1,1)<>'2' and ls_BMXZ00<>'4' and ls_YYID00='225085' then LS_ERROR:= '病人已出科，不能进行费用变更'; Raise E_custom; end if; select nvl(trim(max(value0)),'N') into ls_qzsf from xt_xtcs00 where name00 = 'ZY_QZSF'; begin select substrb(value0,1,1) into VKYJESF from xt_xtcs00 where name00='XT_KYJESF'; exception when others then VKYJESF:='0';--'0'按医保预结算+院内支付比例计算+许可欠费,'1'直接返回帐户的预扣总余额 --'2'按医保预结算+许可欠费计算'3'只按医保预结算的计算'4'按医保预结算且不将医保个人账户余额计算在内 end; /* if :new.hjje00 <> 0 then--判断ZY_FYMX00与ZY_BRFY00中HJJE00不一致的时候 begin select nvl(sum(round(hjje00,2)),0) into ls_sumje0 from zy_fymx00 where djh000=:new.djh000; exception when others then ls_sumje0:=0; end; if :new.hjje00<>ls_sumje0 then RAISE E_FYYMX0; end if; end if;*/ select YBLB00,YBBRLB,FBBH00,TTBRID,TTLX00,BRZTQK into ls_YBLB00,ls_YBBRLB,ls_FBBH00,LS_TTBRID,LS_TTLX00 ,LS_BRZTKQ from BM_BRXXB0 where BRID00=:NEW.BRID00; select YBLB00,YBBRLB,FBBH00 into ls_YBLB00,ls_YBBRLB,ls_FBBH00 from ZY_BRXXB0 where ZYID00=:new.ZYID00; if (TRIM(ZY_SFYGFSX)='Y')and(ls_fbbh00 not in(1,3)) then--建阳公费病人增加上限 select ybzxlb into ls_ybzxlb from ic_ybbrlb where yblb00=ls_YBLB00 and fbbh00=ls_FBBH00; select zyzfsx into ls_zyzfsx_02 from bm_ybbrlb where ybzxlb=ls_ybzxlb and ybbrlb=ls_ybbrlb and fbbh00=ls_FBBH00; if ls_zyzfsx_02>0 then select nvl(jzje00,0) into ls_zyzfsx_01 from zy_brzhxx where brid00=:new.brid00; if (ls_zyzfsx_01>=ls_zyzfsx_02)or(:new.jzje00>=ls_zyzfsx_02) then raise E_QCSXJE; end if; end if; end if; select YBZXLB,SFJSDW,SFXNH0 into ls_YBLB00,ls_SFJSDW,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(DBZCWF,0)+nvl(DBZHCF ,0) into ls_XJYE00,ls_ZZYE00,ls_YXQFJE,ls_SFDJ00,ls_WJSJE0,ls_TYJE00,ls_YBZHYE,ls_BRYKYE,ls_YBYZF0,ls_YBYJS0,ls_BZWFY0 from ZY_BRZHXX where JFLBID=:NEW.JFLBID and BRID00=:NEW.BRID00 FOR UPDATE; if (ls_FBBH00=1)or(ls_FBBH00=3)or (ls_FBBH00=90) then :NEW.ZFJE00:=:NEW.HJJE00-:NEW.GFJE00; :NEW.JZJE00:=0; end if; if ls_SFDJ00 <> '0' then RAISE E_ZHZT00; end if; --判断该病人是否为医保病人,若是,计算医保允许欠费金额 BEGIN IF ls_SFJSDW = 'N' THEN ls_YBQFBZ:=0; ls_YBZFBL:=0; ls_ZYZFSX:=0; select NVL(sum(HJJE00-HJJE00*ZFBL00),0),NVL(sum(HJJE00*ZFBL00),0) into ls_YBZFZE,ls_GRXJZF from ZY_FYMX00 where DJH000=:NEW.DJH000; --计算医保允许欠费金额=医保总的支付金额-医保支付中个人支付的金额 ls_YBGRFD:=SF_ZY_GETYBXJ(:NEW.BRID00,ls_YBZFZE); ls_YBQFJE:=ls_YBZFZE - ls_YBGRFD; ELSE select NVL(YBQFBZ,0),NVL(YBZFBL,0),NVL(ZYZFSX,0),NVL(ZYYJXX,0) into ls_YBQFBZ,ls_YBZFBL,ls_ZYZFSX,ls_ZYYJXX from BM_YBBRLB where YBZXLB = ls_YBLB00 and YBBRLB = ls_YBBRLB and FBBH00 = ls_FBBH00; if ls_ZYYJXX>0 then select NVL(sum(JFJE00),0) into ls_JFJE00 from zy_brjfb0 where brid00 =:NEW.BRID00 and JFLBID = 2 and jzdh00 = 0; if ls_JFJE00 ls_ZYZFSX THEN --若病人的费用未超过上限,则 ls_SJWJJE:=ls_ZYZFSX; ELSE ls_SJWJJE:=NVL(:NEW.ZFJE00,0)+ls_WJSJE0; END IF; IF ls_YBYJS0 >= ls_ZYZFSX 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; --医保允许欠费金额=医院设定的医保允许欠费金额+医保个人帐户余额(Modified 不再添加医保的帐户余额) --ls_YBQFJE:=greatest(ls_YBQFJE,0)+ls_YBZHYE; 处理需求 BQ-20100621-003，因为ZY_BRZHXX.YBZHYE有负数的情况. ls_YBQFJE:=greatest(ls_YBQFJE,0)+greatest(ls_YBZHYE,0); END IF; exception WHEN OTHERS THEN ls_YBQFJE:=0; END; select count(*) into VBYXQFYBBR from BM_TYZD00 where ZDMC00='医保预结算金额不做为透支金额的病人' and MC0000=LS_BRZTKQ; if VBYXQFYBBR>0 then ls_YBQFJE:=0; end if; if ls_FBBH00<>3 then ls_YBQFJE:=0; end if; --修正非医保病人取医保允许欠费金额 IF ls_YBQFJE < 0 THEN ls_YBQFJE:=0; END IF; --通过Update ZY_BRDB00,使用该表的Trigger,来修改该病人的允许欠费金额 Update ZY_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'); IF (ls_XJYE00 + ls_ZZYE00 + ls_YBQFJE - ls_TYJE00) < NVL(:NEW.ZFJE00,0) THEN --若病人帐户的余额不够,这时要使用到允许欠费金额,由于欠费金额是从病人的担保信息中得到的,并且有时间限制,所以这时重新判断! --判断是否是真正的欠费担保 （因为医大附加，允许病人欠费录入） select count(*) into ls_sfzzdbqf from ZY_BRDB00 where BRID00=:NEW.BRID00 and JFLBID=:NEW.JFLBID and SFYX00='Y'; if ls_sfzzdbqf>0 then begin select NVL(sum(DBJE00),0) into ls_DBJE00 from ZY_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'); ls_YXQFJE:=ls_DBJE00; --重新计算病人的欠费金额 end; end if; END IF; --宁德医院特定项目允许欠费, 20040514 医大附二提出要求,手术室的毒麻处方允许欠费 --(特定项目允许欠费和特定科室允许欠费) --收费系统还要根据参数来决定是否允许欠费ZYSF_TDXMTDKS_YXQF --允许欠费的医技科室 医技系统 所有处方,所有医技 允许欠费 允许欠费 -->sfykbz 为2或3 --允许欠费的医技科室 收费系统 普通处方,不允许欠费;收费处.毒麻处方,允许欠费;收费处.所有医技,不允许欠费 --select count(1) into ls_sfqfxm from ZY_FYMX00 A -- WHERE DJH000=:NEW.DJH000 AND -- ( (SFYDJ0='1' or xmbh00>9000000000 or -- not EXISTS (select 1 from BM_YYQFXM -- where SFXMID=A.XMBH00 and TZRQ00 is null -- and TZSJ00 is null)) -- and(sfydj0='0' or xmbh00<>9999999997 or -- not exists (select xmbh00 from xt_xtcs00 b -- where Instr(','||Value0||',',','||to_char(a.kdks00)||',')>0 -- AND NAME00='YJ_YXQFKS_ZY'))); -- --IF ls_sfqfxm>0 THEN --允许欠费的项目设置 select NVL(sum(ZFJE00),0) into ls_sfqfxm from ZY_FYMX00 A where DJH000=:NEW.DJH000 and ((sfydj0<>'1') or exists (select 1 from xt_xtcs00 where name00='ZYSF_TDXMTDKS_YXQF' and Value0='Y')) and ( exists(select 1 from BM_YYQFXM where SFXMID=A.XMBH00 and TZRQ00 is null ) or (exists(select 1 from XT_XTCS00 b where Instr(','||trim(Value0)||',',','||decode(sign(a.xmbh00-9999999990),1,to_char(a.kdks00),a.zxks00)||',')>0 AND NAME00='YJ_YXQFKS_ZY') and (a.xmbh00<9999999990 or not exists (select 1 from xt_xtcs00 where name00='YJ_YXQFKS_ZY_CFYX' and value0='N'))) ); --获取是否是团体 0:不是 1:团体主账户 2:团体成员账户 begin select SF_ZY_CHECKTTBR(:NEW.BRID00,LS_TTBRID,LS_TTLX00) into LS_SFTTCY from dual; exception when others then LS_SFTTCY:=0; end; --团体成品判断--begin-- if LS_SFTTCY in (2) then --获取主账户账户余额 begin select SF_ZY_GETTTZHYE(:NEW.BRID00,'0') into LS_TTZHYE from dual; exception when others then LS_TTZHYE:=0; end; if (nvl(LS_TTZHYE,0)+nvl(LS_XJYE00,0)0) then LS_TMPSTR:='团体主账户可用金额:('||to_char(LS_TTZHYE)||')元'; RAISE E_JEBG00; end if; --支付时更新团体主账户的团体冻结金额 update ZY_BRZHXX set TTDJJE=nvl(TTDJJE,0)+nvl(:NEW.ZFJE00,0) where BRID00=LS_TTBRID and JFLBID =:NEW.JFLBID; update ZY_BRZHXX set TTDJJE=nvl(TTDJJE,0)+nvl(:NEW.ZFJE00,0) where BRID00=:NEW.BRID00 and JFLBID =:NEW.JFLBID; --团体成品判断--end-- --非团体成品判断--begin-- else -- BY ZYSF-20151109-001 费用转化时把允许欠费金额取出来避免提示金额不足 if nvl(:NEW.ZRZYLS,0)>0 then select nvl(YXQFJE,0)into LS_YXQFJE from ZY_BRZHXX where JFLBID=:NEW.JFLBID and BRID00=:NEW.BRID00; end if; ls_YXQFJE:=LS_SFQFXM+LS_YXQFJE; select SF_ZY_SFQYDBZJK(:new.ZYID00) into LS_BZYBZX from dual; select nvl(DBZBM0,' ') into LS_DBZBM0 from ZY_BRXXB0 where ZYID00=:new.ZYID00; if VKYJESF in ('6') or LS_BZYBZX<>'-1' then select SF_BQ_KYJE00(0,:new.BRID00,decode(VBYXQFYBBR,0,-1,1)) into LS_KYE000 from dual; --2018.03.01 dsm select NVL(sum(JFJE00),0) into LS_YJJE00 from zy_brjfb0 where brid00 =:new.BRID00 and JFLBID = 2 and jzdh00 = 0; else LS_KYE000 :=-9999999; end if; LS_YBHDJE:=0; if ((ls_FBBH00=1)or(ls_FBBH00=3 and ls_SFXNH0='1')) and LS_DBZBM0<>' ' then begin select YBHDJE into LS_YBHDJE from BM_DBZDEB where YBZXLB=LS_BZYBZX and DBZBM0=LS_DBZBM0; exception when others then LS_YBHDJE:=1; end; end if; if LS_BZYBZX<>'-1' and LS_DBZBM0<>' ' and (LS_YJJE00>=LS_YBHDJE) then -- LS_ZFJE00:=nvl(:new.BZWFY0,0); LS_ZFJE00:=0; --已经加在ZY_BRZHXX.DBZCWF+DBZHCF ls_YBQFJE:=0; -- ls_YBQFJE1:=ls_YBYJS0-ls_YBYZF0; ls_YBQFJE1:=ls_WJSJE0-ls_YBYZF0; --wzy单病种医保未结算金额计算错误改ls_YBYJS0-ls_YBYZF0为ls_WJSJE0-ls_YBYZF0 begin select substrb(value0,1,1) into VKYJESF_DBZ from xt_xtcs00 where name00='ZY_DBZKYESF'; exception when others then VKYJESF_DBZ:='0';--'0'有计算医保预结算返回参与计算可用额,'1'按医保预结算+院内支付比例计算+许可欠费 end; if VKYJESF_DBZ='0' then if (ls_FBBH00=1)or(ls_FBBH00=3 and ls_SFXNH0='1') then LS_KYE000:=1; elsif ls_WJSJE0'-1' and LS_DBZBM0<>' ' then IF :NEW.SFYKBZ <> '2' and :NEW.SFYKBZ <> '3' then --若使用标志为2,3时,则不判断病人帐户金额是否足够 if (LS_KYE000 - ls_TYJE00+LS_LWKQFJE)<0 and :new.ZFJE00 > 0 then LS_TMPSTR:=:NEW.xm0000||' 可用余额'||to_char(LS_KYE000+nvl(:new.DBZCWF,0)+nvl(:new.DBZHCF,0))||',允许欠费'||to_char(ls_YXQFJE)||',项目金额'||to_char(nvl(:new.ZFJE00,0))||';'; --XGH 增加:NEW.xm0000+ RAISE E_JEBG00; end if; end if; else if ls_qzsf <> 'Y' and (LS_KYE0000 AND (ls_XJYE00 + ls_ZZYE00 + ls_YXQFJE - ls_TYJE00+LS_LWKQFJE) < nvl(:new.ZFJE00,0) + ls_YBGRFD+ls_BZWFY0 and nvl(:new.ZFJE00,0) <> 0 then ls_TMPSTR:='医保个人支付:'||to_char(ls_YBGRFD); RAISE E_JEBG00; end if; ELSE IF :NEW.SFYKBZ <> '2' and :NEW.SFYKBZ <> '3' then --若使用标志为2,3时,则不判断病人帐户金额是否足够 if (ls_XJYE00 + ls_ZZYE00 + ls_YXQFJE + ls_YBQFJE - ls_TYJE00+LS_LWKQFJE) < nvl(:new.ZFJE00,0) and :new.ZFJE00 > 0 then LS_TMPSTR:='现金余额'||to_char(ls_XJYE00)||',转账余额'||to_char(ls_ZZYE00)||',允许欠费'||to_char(ls_YXQFJE)||',项目金额'||to_char(nvl(:new.ZFJE00,0))||';'; RAISE E_JEBG00; end if; END IF; END IF; END IF; end if ; end if; --非团体成品判断--end-- end if; --病人余额修改 :NEW.BRYE00:= LS_XJYE00 + LS_ZZYE00 - NVL(:NEW.ZFJE00,0); IF NVL(:NEW.ZFJE00,0) > 0 THEN if ls_ZZYE00 < NVL(:NEW.ZFJE00,0) then --转账余额不够,先扣完转账余额后，再扣现金余额 ls_XJYE00 := ls_XJYE00-(NVL(:NEW.ZFJE00,0) - ls_ZZYE00); ls_ZZYE00 := 0; else ls_ZZYE00 := ls_ZZYE00 - NVL(:NEW.ZFJE00,0); end if; ELSE ls_XJYE00 := ls_XJYE00-NVL(:NEW.ZFJE00,0); --若是冲销的，直接转到现金余额上 --现金余额不能超过病人的现金预交金 select least(nvl(sum(JFJE00),0),ls_XJYE00),greatest(ls_XJYE00-nvl(sum(JFJE00),0),0)+ls_ZZYE00 into ls_XJYE00,ls_ZZYE00 from ZY_BRJFB0 where BRID00=:new.BRID00 and JZDH00=0 and ZFFS00<>'2'; END IF; IF :NEW.SFYKBZ = '1' or :NEW.SFYKBZ = '3' THEN Update ZY_BRZHXX set XJYE00 = NVL(ls_XJYE00,0), --病人现金余额 ZZYE00 = NVL(ls_ZZYE00,0), --病人转账余额 YBTCJE = NVL(YBTCJE,0)+DECODE(ls_SFJSDW,'N',NVL(ls_YBZFZE,0),0), YBZFJE = NVL(YBZFJE,0)+DECODE(ls_SFJSDW,'N',NVL(ls_YBGRFD,0),0), --YXQFJE = ls_YXQFJE, --允许病人欠费金额(注意:该字段仍旧不做修改,否则会出错) WJSJE0 = NVL(WJSJE0,0) + NVL(:NEW.ZFJE00,0), --未结算金额 ZYE000 = NVL(ls_XJYE00,0) + NVL(ls_ZZYE00,0), --账户实际总余额 JZJE00 =JZJE00+NVL(:NEW.JZJE00,0) where JFLBID =:NEW.JFLBID and BRID00 =:NEW.BRID00; :NEW.BRYKYE:=ls_BRYKYE; ELSE Update ZY_BRZHXX set XJYE00 = NVL(ls_XJYE00,0), --病人现金余额 ZZYE00 = NVL(ls_ZZYE00,0), --病人转账余额 YBTCJE = NVL(YBTCJE,0)+DECODE(ls_SFJSDW,'N',NVL(ls_YBZFZE,0),0), YBZFJE = NVL(YBZFJE,0)+DECODE(ls_SFJSDW,'N',NVL(ls_YBGRFD,0),0), --YXQFJE = ls_YXQFJE, --允许病人欠费金额 WJSJE0 = NVL(WJSJE0,0) + NVL(:NEW.ZFJE00,0), --未结算金额 ZYE000 = NVL(ls_XJYE00,0) + NVL(ls_ZZYE00,0), --账户实际总余额 YKZYE0 = NVL(YKZYE0,0)-NVL(:NEW.ZFJE00,0),--NVL(ls_XJYE00,0) + NVL(ls_ZZYE00,0), --账户预扣总余额 JZJE00 = JZJE00+NVL(:NEW.JZJE00,0) where JFLBID =:NEW.JFLBID and BRID00 =:NEW.BRID00; :NEW.BRYKYE:=ls_BRYKYE - :NEW.ZFJE00; END IF; IF ls_SFJSDW = 'N' THEN if ls_YBGRFD <> 0 then --Update ZY_BRJFB0 set JFJE00 = NVL(JFJE00,0)+(-ls_YBGRFD) -- where BRID00 = :NEW.BRID00 -- and JFLBID = :NEW.JFLBID -- and JZDH00 = :NEW.JZDH00 -- and ZFFS00 = 3 and rownum =1 ; --IF SQL%NOTFOUND THEN Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,zyid00) values(SQ_ZY_BRJFB0_DJH000.nextval,:NEW.BRID00,:NEW.JFLBID,-ls_YBGRFD,3,'医保个人账户','',0, :NEW.CZRQ00,:NEW.CZSJ00,:NEW.CZY000,:NEW.CZYXM0,0,'1',0,:NEW.JZDH00,:new.zyid00); --END IF; end if; --由于如区医保的病人,只有医保个人帐户支付的才冲减病人的帐户余额,所以设JFLBID=0 if ls_YBZFZE - ls_YBGRFD <> 0 then --Update ZY_BRJFB0 set JFJE00 = NVL(JFJE00,0)+(-(ls_YBZFZE - ls_YBGRFD)) -- where BRID00 = :NEW.BRID00 -- and JFLBID = 0 -- and JZDH00 = :NEW.JZDH00 -- and ZFFS00 = 4 and rownum = 1; --IF SQL%NOTFOUND THEN Insert into ZY_BRJFB0(DJH000,BRID00,JFLBID,JFJE00,ZFFS00,ZFFSMC,ZPZH00,PJH000, JFRQ00,JFSJ00,JFCZY0,CZYXM0,BRYE00,JFBZ00,JKBID0,JZDH00,zyid00) values(SQ_ZY_BRJFB0_DJH000.nextval,:NEW.BRID00,0,-(ls_YBZFZE - ls_YBGRFD),4,'医保统筹基金','',0, :NEW.CZRQ00,:NEW.CZSJ00,:NEW.CZY000,:NEW.CZYXM0,0,'1',0,:NEW.JZDH00,:new.zyid00); --END IF; end if; END IF; --由于在实际情况中,不知为何现金余额+转帐余额总有一些数据与总余额不等,所以特添加以下数据,来保证! Update ZY_BRZHXX set XJYE00 = NVL(ZYE000,0) - NVL(ZZYE00,0) where JFLBID =:NEW.JFLBID and BRID00 =:NEW.BRID00; --解决病区[病人常规项目维护]菜单中第一个项目录入速度慢的问题060104 insert into bq_yxjsxm (zyid00,sfxmid) select :new.zyid00,xmbh00 from zy_fymx00 a,bm_yysfxm b where djh000=:new.djh000 and xmbh00<9999999990 and xmbh00=sfxmid and BMGZID||'*'='1*' and JSXM00='0' and xmbh00 not in (select sfxmid from bq_yxjsxm where zyid00=:new.zyid00); --增加医生组ID if :NEW.ZYID00>0 and nvl(:new.YSZID0,0)<=0 and :new.HJJE00>0 then -- for and :new.HJJE00>0 and ZYSF-20160620-007 begin select KDBQ00 into LS_KDBQ00 from ZY_FYMX00 where djh000=:new.djh000 and rownum=1; exception when others then ls_KDBQ00:=0; end; SELECT YSZID0,DQBQ00,ZYID00 INTO :NEW.YSZID0,LS_DQBQ00,LS_ZYID00 FROM ZY_BRXXB0 WHERE ZYID00= :NEW.ZYID00; if LS_KDBQ00>0 and LS_KDBQ00<>LS_DQBQ00 then --要从BQ_BRLDXX取历史的医生组 select nvl(max(YSZID0),0) into LS_YSZID0 from ( select YSZID0 from BQ_BRLDXX where ZYID00=LS_ZYID00 and BQH000=LS_KDBQ00 and nvl(YSZID0,0)>0 order by QSRQ00||QSSJ00 Desc) where rownum=1; :new.YSZID0:=LS_YSZID0; end if; --同个单据可能不是存在两个或以上的开单病区，所以医生组也要记录ZY_FYMX00表 --注释下面语句是因为改了写入zy_fymx00的过程 -- update ZY_FYMX00 set YSZID0=:NEW.YSZID0 where DJH000=:NEW.DJH000 and KDBQ00=LS_DQBQ00; -- update ZY_FYMX00 a set YSZID0=(select NVL(max(YSZID0),-1) from BQ_BRLDXX where ZYID00=LS_ZYID00 -- and BQH000=a.KDBQ00 AND YSZID0 IS NOT NULL and QSRQ00<=a.CZRQ00 and (JSRQ00>=a.CZRQ00 or JSRQ00 is null) ) -- where DJH000=:NEW.DJH000 and KDBQ00<>LS_DQBQ00; -- update ZY_FYMX00 a set YSZID0=(select b.YSZID0 from ZS_YSZ000 b,BM_BQKS00 c where b.ssksbh=c.ksh000 and c.yxbz00='1' and c.bqh000=a.KDBQ00 and rownum=1) -- where DJH000=:NEW.DJH000 and KDBQ00<>LS_DQBQ00; -- update ZY_FYMX00 set YSZID0=:NEW.YSZID0 where DJH000=:NEW.DJH000 and YSZID0=-1; end if; exception WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20901,'查找不到病人的账户信息'||'!*'); ROLLBACK; WHEN E_ZHZT00 THEN RAISE_APPLICATION_ERROR(-20902,'病人的账户状态不是有效的状态,请到收费处将该病人账户状态设置成有效的!'||'!*'); ROLLBACK; WHEN E_FYYMX0 THEN --E_FYYMX0 RAISE_APPLICATION_ERROR(-20902,'病人费用ZY_BRFY00中的HJJE00:'||TO_CHAR(:NEW.HJJE00)||'与ZY_FYMX00中的HJJE00:'||TO_CHAR(ls_sumje0)||'与一致'); ROLLBACK; WHEN E_JEBG00 THEN RAISE_APPLICATION_ERROR(-20903,'账户金额不够，操作失败！*'||ls_TMPSTR); ROLLBACK; when E_QCSXJE then RAISE_APPLICATION_ERROR(-20905,'公费病人费用自付金额超出上限！请转成自费病人'||'!*'); rollback; when E_CUSTOM then RAISE_APPLICATION_ERROR(-20905,LS_ERROR||ls_er||'！*'); rollback; WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20904,substr(nvl(sqlerrm,'出错原因不详'),1,100)||ls_er||'，请记录此信息并和系统管理员联系！'||'!*'); ROLLBACK; end; /