PROMPT 205.TR_YJ_YW0000_BINS_UPD CREATE OR REPLACE TRIGGER TR_YJ_YW0000_BINS_UPD BEFORE INSERT OR UPDATE ON SD_YJ.YJ_YW0000 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare ErrStr varchar2(200); VJE0000 NUMBER(12,4); VSL0000 YJ_YW0000.SL0000%type; ECUSTOM EXCEPTION; VLbbh00 bm_zlzd00.LBBH00%type; VCOUNTER NUMBER(5); CS_YS_YSXMSQD XT_XTCS00.VALUE0%TYPE; SSQID00 NUMBER(10); Vbrzt00 zy_brxxb0.brzt00%type; Vzyid00 zy_brxxb0.zyid00%type; VJYXMSLBNDY1 CHAR(1); LS_COU number(5); -- PRAGMA AUTONOMOUS_TRANSACTION; --SGHID00: SF_BRXXB0.GHID00%TYPE; --SGHH000: SF_BRXXB0.GHH000%TYPE; -- MODIFICATION HISTORY -- Person Date Comments -- zhr 2007.11.07 把原来的TR_YJ_YW0000_BEFINSAPD触发器合并进来． -- zhr 2008.02.18 病人可能多次住院，确保(1)不能对非在院状态的病人进行开单，(2)不能对上次的住院记录进行开单。 -- zhr 2008.02.18 病人可能多次住院，确保(1)不能对非在院状态的病人进行记账，(2)不能对上次的住院记录进行记账。 -- qks 2008.05.30 门诊特殊病种的病人扣费后再进行冲销操作,产生的负数记录没有将特殊病种编号写入到yj_yw0000表 YJ-20080527-002。 -- csf 2010.02.05 增加参数控制检验项目数量只能是1。 -- chenqw 2010.03.11 max(char类型)要用trim 保护 -- xujy 2010.04.09 入院管理--修改住院病人信息:修改病人姓名的时候提示检验数量不能超过1 -- zhr 2010.04.22 病人处于非在院状态，不能开单的判断增加brzt00='1'条件 -- liuj 2011.01.27 修改触发器涉及调用本表的时候报错的情况. -- CSF 2011.02.24 当皮试类别带出的皮试项目，又带出药品，此时开处方的没有选择皮试类别，但修改的时候选择皮试类别，此时保存提示“等待资源时检测到死锁”,纠正bug -- CSF 2011.03.23 不得在触发器中加commit,注释commit -- liuj 2011.08.29 修改当开描述性手术时开未转诊收费项目报错。 -- dsm 2012.04.28 用法带出的诊疗项目可再带出药品 for MZYS-20120423-001 -- zhangwz 2012.05.09 修改医技单的状态信息时要同时处理检查治疗预约的数据 by MZYS-20120509-002. -- liuj 2012.06.21 BUG:多次入院时最后一次入院已经取消入院登记上次住院取消出院的情况下操作触发本触发器会提示已再次入院不让操作. for BQ-20120620-001 -- dsm 2012.08.14 测试时发现，冲销后复制的纪录会报错，原因是会去取特殊病种 MZYS-20120728-001 -- dsm 2013.01.23 医生站开出的所有检查单，如果要退费，一定要有医生的确认才能退费。for MZYS-20121204-001 -- dsm 2013.07.10 ZYGHID=-1时走与ZYGHID=0一样的情况 for YJ-20130531-001 -- dsm 2013.09.04 XMZT00=3 or XMZT00=5的 YYZT00不能设置成1 for YJ-20130902-001 Begin --取参数 begin select trim(VALUE0) into VJYXMSLBNDY1 from XT_XTCS00 where NAME00='YS_JYXMSLBNDY1'; exception when others then VJYXMSLBNDY1:='N'; end; IF INSERTING THEN--插入记录，自动填充病人ID和ZYHGHH. --追加判断 if (VJYXMSLBNDY1='Y') and (:new.SL0000>1) then if :NEW.ZLXMID>0 then ErrStr:='err1: 诊疗项目不存在ZLXMID='||to_char(:new.zlxmid); select LBBH00 into VLBBH00 from BM_ZLZD00 where ZLXMID=:NEW.ZLXMID; if VLBBH00=4 then errStr:='检验项目数量不能超过1'; Raise Ecustom; end if; end if; end if; --ZYGHID>0 使用该号获得病人信息, -- BRID00>0,ZYGHID00=0,使用BRID00获得信息 MZZYBZ='0', -- 其他直接使用参数作为病人信息,MZZYBZ='0' --自动修改诊疗项目简称 IF :NEW.KDRQ00 IS NULL THEN :NEW.KDRQ00:=TO_CHAR(SYSDATE,'YYYYMMDD'); :NEW.KDSJ00:=TO_CHAR(SYSDATE,'HH24:MI:SS'); END IF; IF :NEW.ZLXMID>0 THEN ErrStr:='err1: 诊疗项目不存在ZLXMID='||to_char(:new.zlxmid); SELECT ZLXMJC,DW0000,FBZXBZ,LBBH00 INTO :NEW.ZLXMJC,:NEW.DW0000,:NEW.FBZXBZ,VLBBH00 FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID; END IF; if :new.zlxmid=0 and :new.yjlrbz='S' then select '手术开未转诊项目' into :new.zlxmjc from dual; end if; IF (:NEW.YJDJH0 IS NULL) OR (:NEW.YJDJH0=0) THEN SELECT SQ_YJ_YW0000_YJDJH0.NEXTVAL INTO :NEW.YJDJH0 FROM DUAL; END IF; --插入开单医生姓名,所在部门 IF :NEW.KDYS00>=0 THEN ErrStr:='err2: 员工不存在YGBH00='||to_char(:NEW.KDYS00); SELECT ZWXM00,BMBH00 INTO :NEW.KDYSXM,:NEW.YSSZKS FROM BM_YGBM00 WHERE YGBH00=:NEW.KDYS00 AND ROWNUM=1; END IF; --自动填充病人ID和ZYHGHH.姓名、出生日期和性别 IF (:NEW.ZYGHID>0) THEN IF :NEW.MZZYBZ='1' THEN--住院病人 ErrStr:='err3: 住院病人不存在ZYID00='||to_char(:NEW.ZYGHID); SELECT Z.ZYH000,Z.BRID00,Z.XM0000,Z.XB0000,Z.CSRQ00,Z.RYCWH0,Z.BRZT00 INTO :NEW.ZYHGHH,:NEW.BRID00,:NEW.BRXM00,:NEW.XB0000,:NEW.CSRQ00,:NEW.CH0000,Vbrzt00 FROM ZY_BRXXB0 Z WHERE Z.ZYID00=:NEW.ZYGHID; --zhr 08.02.18 病人可能多次住院，确保(1)不能对非在院状态的病人进行开单，(2)不能对上次的住院记录进行开单。 if substr(Vbrzt00,1,1) not in ('1','2','3') then errStr:='病人处于非在院状态，不能开单'; Raise Ecustom; end if; select max(zyid00) into Vzyid00 from ZY_BRXXB0 WHERE BRID00=:NEW.BRID00 and substr(brzt00,1,1) in ('1','2','3'); IF Vzyid00<>:NEW.ZYGHID then errStr:='病人已经再次入院，请刷新后重新开单'; Raise Ecustom; end if; ELSE--门诊病人 ErrStr:='err4: 挂号号不存在GHID00='||to_char(:NEW.ZYGHID); SELECT GHH000,BRID00,XM0000,XB0000,CSRQ00 INTO :NEW.ZYHGHH,:NEW.BRID00,:NEW.BRXM00,:NEW.XB0000,:NEW.CSRQ00 FROM SF_BRXXB0 WHERE GHID00=:NEW.ZYGHID; END IF; END IF; IF(:NEW.BRID00>0)AND ((:NEW.ZYGHID=0) or (:NEW.ZYGHID=-1)) THEN --传入病人ID 增加 or (:NEW.ZYGHID=-1) for YJ-20130531-001 ErrStr:='err5: 病人不存在BRID00='||to_char(:NEW.BRID00); SELECT BRXM00,BRXB00,BRCSRQ INTO :NEW.BRXM00,:NEW.XB0000,:NEW.CSRQ00 FROM BM_BRXXB0 WHERE BRID00=:NEW.BRID00; :NEW.MZZYBZ:='0'; END IF; --插入诊断编号，自动填写诊断名称 IF (:NEW.ZDID00 is not null) and (:NEW.ZDMC00 is null) THEN ErrStr:='err6: 标准诊断项目不存在ICD900='||:NEW.ZDID00; SELECT JBMC00 INTO :NEW.ZDMC00 FROM XT_icd900 WHERE ICD900=:NEW.ZDID00; END IF; --住院病人填写病区 IF :NEW.MZZYBZ='1' THEN IF :NEW.KDBQ00 IS NULL THEN ErrStr:='err7: 住院号不存在ZYID00='||to_char(:NEW.ZYGHID); SELECT DQBQ00 INTO :NEW.KDBQ00 FROM ZY_BRXXB0 WHERE ZYID00=:NEW.ZYGHID; END IF; END IF; select SF_BQ_CSRQTONL(:new.csrq00) into :New.NL0000 from dual where rownum=1; --该内容，经确认二院没有使用，并且不应该放在该触发器中处理，现取消该功能处理2011.02.24 -- IF :NEW.DCBZ00='2' THEN--二院要求，青霉素皮试不收药品费，只收治疗费,因此药品要自动改为自备 ErrStr:='err8: 处方号不存在CFLSH0='||:NEW.YZCFID; -- UPDATE YF_MZCFMX SET SFZBY0='Y' WHERE CFLSH0=:NEW.YZCFID AND PSLBID=(select PSID00 from BM_PS0000 WHERE PSMC00='青霉素皮试'); -- SELECT nvl(SUM(B.LSDJ00*B.YPZSL0),0) INTO VJE0000 FROM YF_MZCFMX B -- WHERE B.CFLSH0=:NEW.YZCFID AND B.PSLBID=(select PSID00 from BM_PS0000 WHERE PSMC00='青霉素皮试') ; -- if VJE0000<>0 then -- UPDATE YF_MZCF00 A SET CFZJE0=A.CFZJE0-VJE0000 WHERE A.CFLSH0=:NEW.YZCFID; -- end if; -- END IF; --诊疗带出药品 IF :NEW.MZZYBZ in ('0','1') and :NEW.DCBZ00 in ('0','2','1') THEN--正常项目,用法带出的诊疗项目也有带出药品 for MZYS-20120423-001 if (NVL(:NEW.CXDJH0,0)>0) and (:new.sl0000<0) THEN--冲销 SELECT ZLDCCF,SL0000 INTO :NEW.ZLDCCF,VSL0000 FROM YJ_YW0000 WHERE YJDJH0=:NEW.CXDJH0; :NEW.ZLDCCF:=SF_YJ_ZLDCYP(:NEW.ZLXMID,:NEW.ZYGHID,:NEW.MZZYBZ,:NEW.YJKSBH,:NEW.KDYS00,VSL0000,:NEW.ZLDCCF,4,-1*:NEW.SL0000); ELSE :NEW.ZLDCCF:=SF_YJ_ZLDCYP(:NEW.ZLXMID,:NEW.ZYGHID,:NEW.MZZYBZ,:NEW.YJKSBH,:NEW.KDYS00,:NEW.SL0000,null,1,0); END IF; END IF; --分组号的处理 if (VLbbh00='4') and (nvl(:new.yjfzh0,0)=0) then :new.yjfzh0:=:new.yjdjh0; elsif VLbbh00<>'4' then :new.yjfzh0:=null; end if; if :new.kdbq00 is null then :new.kdbq00:=0; end if; --特殊病种编号 if :new.mzzybz='0' and nvl(:new.CXDJH0,0)<>0 and :new.SL0000<0 then -- add for MZYS-20120728-001 :new.SL0000<0 select trim(max(BH0000)) into :new.bh0000 from yj_yw0000 where yjdjh0=:new.CXDJH0; end if; ELSIF UPDATING THEN --更改 select count(*) into ls_cou from XT_XTCS00 where NAME00='YS_JCTFSFXYSQR' and VALUE0='Y'; if ls_cou=1 and :NEW.SFDJH0>0 and :OLD.SFDJH0=0 and nvl(:NEW.QRYSBH,-1)=-1 and :NEW.SL0000<0 and :NEW.MZZYBZ=0 then errStr:='医生还未退费确认，请先到门诊医生平台做退费确认!'; Raise Ecustom; end if; --追加判断 if (VJYXMSLBNDY1='Y') and (:new.SL0000>1) and (:old.SL0000<>:new.SL0000) then if :NEW.ZLXMID>0 then ErrStr:='err1: 诊疗项目不存在ZLXMID='||to_char(:new.zlxmid); select LBBH00 into VLBBH00 from BM_ZLZD00 where ZLXMID=:NEW.ZLXMID; if VLBBH00=4 then errStr:='检验项目数量不能超过1'; Raise Ecustom; end if; end if; end if; IF :NEW.ZLXMID<>:OLD.ZLXMID AND :NEW.ZLXMID>0 THEN ErrStr:='err9: 诊疗项目不存在ZLXMID='||to_char(:NEW.ZLXMID); SELECT ZLXMJC,DW0000,FBZXBZ,LBBH00 INTO :NEW.ZLXMJC,:NEW.DW0000,:NEW.FBZXBZ,VLBBH00 FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID; END IF; --修改诊断编号，自动诊断名称 IF (:NEW.ZDID00<>:OLD.ZDID00) THEN IF (:NEW.ZDID00='') THEN :NEW.ZDID00:=''; :NEW.ZDMC00:=NULL; ELSE ErrStr:='err10: 标准诊断项目不存在ICD900='||:NEW.ZDID00; SELECT JBMC00 INTO :NEW.ZDMC00 FROM XT_ICD900 WHERE ICD900=:NEW.ZDID00; END IF; END IF; --自动修改开单医生姓名和医生所在部门 IF (:NEW.KDYS00<>:OLD.KDYS00) AND (:NEW.KDYS00>0)THEN --修改开单医生编号，自动更改医生姓名 ErrStr:='err11: 员工不存在YGBH00='||to_char(:NEW.KDYS00); SELECT ZWXM00,BMBH00 INTO :NEW.KDYSXM,:NEW.YSSZKS FROM BM_YGBM00 WHERE YGBH00=:NEW.KDYS00 AND ROWNUM=1; END IF; --自动修改执行人姓名 IF (:NEW.ZXR000<>:OLD.ZXR000 AND :new.ZXR000 IS NOT NULL) THEN --修改执行人编号，自动更改姓名 IF :NEW.ZXR000=0 THEN :NEW.ZXRXM0:=NULL; ELSE ErrStr:='err12: 员工不存在YGBH00='||to_char(:NEW.ZXR000); SELECT ZWXM00 INTO :NEW.ZXRXM0 FROM BM_YGBM00 WHERE YGBH00=:NEW.ZXR000 AND ROWNUM=1; END IF; END IF; --自动修改报告人姓名 IF (:NEW.JGBGYS<>:OLD.JGBGYS AND :NEW.JGBGYS IS NOT NULL)THEN IF :NEW.JGBGYS =0 THEN--修改报告医生编号，自动更改姓名 :NEW.BGYSXM:=NULL; ELSE ErrStr:='err13: 员工不存在YGBH00='||to_char(:NEW.JGBGYS); SELECT ZWXM00 INTO :NEW.BGYSXM FROM BM_YGBM00 WHERE YGBH00=:NEW.JGBGYS AND ROWNUM=1; END IF; END IF; if :new.csrq00<>:old.csrq00 then select SF_BQ_CSRQTONL(:new.csrq00) into :New.NL0000 from dual where rownum=1; end if; IF ((:NEW.ZLXMID<>:OLD.ZLXMID) OR (:NEW.SL0000<>:OLD.SL0000)) AND (:NEW.MZZYBZ in ('0','1')) and (:NEW.DCBZ00 in ('0','2')) THEN :NEW.ZLDCCF:=SF_YJ_ZLDCYP(:NEW.ZLXMID,:NEW.ZYGHID,:NEW.MZZYBZ,:NEW.YJKSBH,:NEW.KDYS00,:NEW.SL0000,:NEW.ZLDCCF,2,:NEW.SL0000); ELSIF :NEW.XMZT00<>:OLD.XMZT00 AND :NEW.XMZT00='6' AND (:NEW.MZZYBZ in ('0','1')) and (:NEW.DCBZ00 in ('0','2')) THEN ErrStr:=SF_YJ_ZLDCYP(:NEW.ZLXMID,:NEW.ZYGHID,:NEW.MZZYBZ,:NEW.YJKSBH,:NEW.KDYS00,:NEW.SL0000,:NEW.ZLDCCF,5,:NEW.SL0000); END IF; IF :NEW.XMZT00<>:OLD.XMZT00 THEN IF :NEW.SFDJH0>0 AND :NEW.XMZT00 IN ('0','1') THEN ErrStr:='收过费的医技单不能修改状态'; RAISE ECUSTOM; END IF ; END IF; IF :NEW.ZJE000<>:OLD.ZJE000 AND :NEW.SFDJH0>0 THEN ErrStr:='收过费的医技单不能重新定价'; RAISE ECUSTOM; END IF; --分组号的处理 if (VLbbh00='4') and (nvl(:new.yjfzh0,0)=0) then :new.yjfzh0:=:new.yjdjh0; elsif VLbbh00<>'4' then :new.yjfzh0:=null; end if; --20041230门诊的医保病人如果帐户<0且费用未结算，不能执行 if :new.mzzybz='0' and nvl(:new.zxsl00,0)<>nvl(:old.zxsl00,0) and :new.zxsl00>0 and :new.sfdjh0>0 then select count(*) into Vcounter from sf_brxxb0 where fbbh00=3 and ghh000=:new.zyhghh and lbbh00='1'; if Vcounter>0 then --select count(*) into Vcounter from sf_brfy00 where djh000=:new.sfdjh0 and jflbid=1 and jzdh00=0; --if Vcounter>0 then select count(*) into Vcounter from sf_brzhxx where brid00=:new.brid00 and zye000+yxqfje<0; if Vcounter>0 then ErrStr:='该病人的院内预交金帐户余额为负数，请到收费处结算后再执行'; RAISE ECUSTOM; end if; end if; end if; if (:new.zxsl00<>:old.zxsl00) and (:new.sl0000-nvl(:new.cxsl00,0)<:new.zxsl00) then ErrStr:='执行数量'||to_char(:new.zxsl00)||'不能大于项目数量'||to_char(:new.sl0000)||'-冲消数量'||to_char(:new.cxsl00)||'!'; RAISE ECUSTOM; end if; if :old.sfdjh0=0 and :new.sfdjh0>0 and :new.mzzybz='1' then --zhr 08.02.18 病人可能多次住院，确保(1)不能对非在院状态的病人进行记账，(2)不能对上次的住院记录进行记账。 select zyid00,brzt00 into Vzyid00,Vbrzt00 from ZY_BRXXB0 WHERE ZYID00=(SELECT MAX(ZYID00) FROM ZY_BRXXB0 WHERE BRID00=:NEW.BRID00); IF Vzyid00<>:NEW.ZYGHID then errStr:='病人已经再次入院，不能对上次住院的单据进行记账，请重新开单'; Raise Ecustom; end if; if substr(Vbrzt00,1,1) not in ('1','2','3') then errStr:='病人处于非在院状态，不能记账'; Raise Ecustom; end if; end if; if (nvl(:new.YYID00,0)<>0) then --检查治疗预约数据的处理 begin select count(1) into Vcounter from YJ_YYBRB0 where YYID00=:new.YYID00 and YYZT00='0'; if (:new.XMZT00='6') and (:old.XMZT00<>'6') and (Vcounter>0) then begin update YJ_YYBRB0 set YYZT00='2',QXRQ00=to_char(sysdate,'YYYYMMDD'),QXSJ00=to_char(sysdate,'HH24:MI:SS'),BZ0000='项目已经取消' where YYID00=:new.YYID00; :new.YYID00:=0; end; elsif (:new.YJKSBH<>:old.YJKSBH) and (Vcounter>0) then begin update YJ_YYBRB0 set YYZT00='2',QXRQ00=to_char(sysdate,'YYYYMMDD'),QXSJ00=to_char(sysdate,'HH24:MI:SS'),BZ0000='项目执行科室发生变化' where YYID00=:new.YYID00; :new.YYID00:=0; end; elsif (nvl(:new.CXSL00,0)>=:new.SL0000) and (Vcounter>0) then begin update YJ_YYBRB0 set YYZT00='2',QXRQ00=to_char(sysdate,'YYYYMMDD'),QXSJ00=to_char(sysdate,'HH24:MI:SS'),BZ0000='项目冲销完毕' where YYID00=:new.YYID00; :new.YYID00:=0; end; -- elsif (:new.XMZT00='3' or :new.XMZT00='4') and (Vcounter>0) then --设置成已检状态 -- begin -- update YJ_YYBRB0 set YYZT00='1' where YYID00=:new.YYID00; -- end; end if; end; end if; END IF; --电子申请单 select count(1) into Vcounter from xt_xtcs00 where name00='YS_YSXMSQD' and UPPER(TRIM(value0))='Y'; if Vcounter>0 then CS_YS_YSXMSQD:='Y'; ELSE CS_YS_YSXMSQD:='N'; end if; if CS_YS_YSXMSQD='Y' then IF INSERTING THEN IF (CS_YS_YSXMSQD='Y') AND (:NEW.SQID00 IS NULL) AND (:NEW.MZZYBZ='0') THEN SELECT COUNT(1) INTO Vcounter from ys_xmsqd0 where sqdid0=(select sqdid0 from bm_zlzd00 where zlxmid=:NEW.ZLXMID) and ghid00=:NEW.ZYGHID; IF Vcounter>0 THEN SELECT SQID00 INTO SSQID00 from ys_xmsqd0 where sqdid0=(select sqdid0 from bm_zlzd00 where zlxmid=:NEW.ZLXMID) and ghid00=:NEW.ZYGHID AND ROWNUM=1; :NEW.SQID00:=SSQID00; -- Update YJ_YW0000 set SQID00=SSQID00 WHERE YJDJH0=:NEW.YJDJH0; END IF; END IF; END IF; IF UPDATING AND (:OLD.ZLXMID<>:NEW.ZLXMID) AND (:NEW.MZZYBZ='0') THEN SELECT COUNT(1) INTO Vcounter from ys_xmsqd0 where sqdid0=(select sqdid0 from bm_zlzd00 where zlxmid=:NEW.ZLXMID) and ghid00=:NEW.ZYGHID; IF Vcounter>0 THEN SELECT SQID00 INTO SSQID00 from ys_xmsqd0 where sqdid0=(select sqdid0 from bm_zlzd00 where zlxmid=:NEW.ZLXMID) and ghid00=:NEW.ZYGHID AND ROWNUM=1; :NEW.SQID00:=SSQID00; --Update YJ_YW0000 set SQID00=SSQID00 WHERE YJDJH0=:NEW.YJDJH0; ELSE :NEW.SQID00:=NULL; --Update YJ_YW0000 set SQID00 = NULL WHERE YJDJH0=:NEW.YJDJH0; END IF; END IF; END IF; -- commit; EXCEPTION WHEN ECUSTOM THEN RAISE_APPLICATION_ERROR(-20030,ERRSTR||'！*'); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010,'数据没有找到,'||errStr||'！*'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20020, NVL(SQLERRM, '原因不明出错！*')); End; / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%