CREATE OR REPLACE TRIGGER SD_HOSPITAL.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); Vbrzt00 zy_brxxb0.brzt00%type; Vzyid00 zy_brxxb0.zyid00%type; ls_cou number(5); v_sfbtzs bm_zlzd00.sfbtzs%type; --是否只能开整数数量 LS_CFLSH0 YF_MZCF00.CFLSH0%type; Begin -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2007.12.17 自动增加病人的医生组ID -- zhr 2008.02.15 病人可能多次住院，确保(1)不能对非在院状态的病人进行开单，(2)不能对上次的住院记录进行开单。 -- zhr 2008.02.15 病人可能多次住院，确保(1)不能对非在院状态的病人进行记账，(2)不能对上次的住院记录进行记账。 -- yangy 2008.11.22 判断余额要加上医保账户余额 -- qks 2009.09.24 收过费的医技单不能修改收费单据号 增加条件判断 -- liuj 2011.05.16 修改当开描述性手术时开未转诊收费项目报错 for YJ-20120604-001 -- zhangwz 2012.05.09 修改医技单的状态信息时要同时处理检查治疗预约的数据 by MZYS-20120509-003. -- dsm 2012.08.21 增加YJ_JCYYFK for BQ-20120806-005 -- dsm 2013.01.23 去掉原有预约处理 for YJ-20140109-001 -- dsm 2014.05.05 YJ_YW0000.YSZID0为空时才从触发器写入 for BQ-20140505-002 -- dsm 2015.04.20 BZ0000='超过日收费上限'绕过限制 for BQ-20150408-002 -- dsm 2016.11.16 作废项目时预约资源要释放 for YJ-20161110-001 -- dsm 2017.07.29 作废检验项目时,如果BBZT00不为0不能作废 for BQ-20170729-001 -- dsm 2017.09.14 XT_ICD900获取ZDMC00时加trim for MZYS-20170905-001 -- LS 2017.11.09 择日住院病人收过费的医技单不用重新定价 BY ZYSF-20171018-002 -- hetf 2018.09.13 限制特定检查项目开出非整数量 BY MZYS-20180824-001 -- dsm 2019.08.17 增加调用SP_YS_YJDCYPCF for MZYS-20190807-001 -- dsm 2019.09.07 增加调用SP_YJ_YJDCYPCF for YF-20190805-001 -- dsm 2019.09.21 增加:new.XMZT00<>:old.XMZT00 for BQ-20190921-001 IF INSERTING THEN--插入记录，自动填充病人ID和ZYHGHH. /*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; select nvl(SFBTZS,'0') into v_sfbtzs from BM_ZLZD00 where ZLXMID = :new.ZLXMID; if (mod(:new.SL0000,1)<>0) then if (v_sfbtzs = '1') then errStr:='该项目： '||:new.zlxmjc||' 只允许开整数数量！'; Raise Ecustom; end if; end if; if :new.zlxmid=0 and :new.yjlrbz='S' then select '手术开未转诊项目' into :new.zlxmjc from dual; end if; if VLBBH00=3 then select count(*) into ls_cou from all_objects where OBJECT_NAME='YJ_JCYYFK' and OBJECT_TYPE='TABLE'; 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.15 病人可能多次住院，确保(1)不能对非在院状态的病人进行开单，(2)不能对上次的住院记录进行开单。 if substr(Vbrzt00,1,1) not in ('2','3') and nvl(:new.ZRZYLS,0)=0 then errStr:='病人处于非在院状态，不能开单'; Raise Ecustom; end if; select max(zyid00) into Vzyid00 from ZY_BRXXB0 WHERE BRID00=:NEW.BRID00; 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)THEN --传入病人ID 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 trim(ICD900)=trim(:new.ZDID00);--=rpad(:NEW.ZDID00,10,' '); 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; 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='青霉素皮试') ; UPDATE YF_MZCF00 A SET CFZJE0=A.CFZJE0-VJE0000 WHERE A.CFLSH0=:NEW.YZCFID; END IF; --诊疗带出药品 IF :NEW.MZZYBZ in ('0','1') and :NEW.DCBZ00 in ('0','2') THEN--正常项目 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 :new.MZZYBZ='0' and :new.SL0000>0 and :new.zlxmid>0 then SP_YS_YJDCYPCF(:new.YJDJH0,:new.ZLXMID,:new.ZYGHID,:new.KDKSBH,:new.KDYS00,LS_CFLSH0); 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='1') AND (:NEW.ZYGHID IS NOT NULL) and nvl(:new.yszid0,0)=0 THEN SELECT YSZID0 INTO :NEW.YSZID0 FROM ZY_BRXXB0 WHERE ZYID00= :NEW.ZYGHID; END IF; if ls_cou>0 then Insert into YJ_JCYYFK(YYID00,YJDJH0) values (0,:NEW.YJDJH0); end if; ELSIF UPDATING THEN --更改 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; select nvl(SFBTZS,'0') into v_sfbtzs from BM_ZLZD00 where ZLXMID = :new.ZLXMID; if (mod(:new.SL0000,1)<>0) then if (v_sfbtzs = '1') then ErrStr:='该项目： '||:new.zlxmjc||' 只允许开整数数量！'; Raise Ecustom; end if; 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 = rpad(:NEW.ZDID00,10,' '); 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') and :new.YEXM00 is null THEN ErrStr:='a收过费的医技单不能修改状态'||:OLD.XMZT00||'-->'||:new.XMZT00; RAISE ECUSTOM; END IF ; END IF; --BY ZYSF-20171018-002 IF nvl(:new.ZRZYLS,0)=0 THEN IF :NEW.ZJE000<>:OLD.ZJE000 AND :NEW.SFDJH0>0 and instr(nvl(:new.BZ0000,' '),'超过日收费上限')=0 THEN --BQ-20150408-002 ErrStr:='收过费的医技单不能重新定价'; RAISE ECUSTOM; END IF; IF :NEW.SFDJH0<>:OLD.SFDJH0 AND :OLD.SFDJH0>0 and :new.YEXM00 is null THEN IF instrb(:NEW.BZ0000||',',','||to_char(:OLD.SFDJH0)||',') <= 0 then ErrStr:='b收过费的医技单不能修改收费单据号'||:old.XMZT00||'-->'||:new.XMZT00; RAISE ECUSTOM; END IF; 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; --20041230门诊的医保病人如果帐户<0且费用未结算，不能执行 if :new.mzzybz='0' and nvl(:new.zxsl00,0)<>nvl(:old.zxsl00,0) and :new.zxsl00>0 and (:new.sfdjh0>0) AND (1=2) 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 VW_YS_BRZHXX where brid00=:new.brid00 and zye000+yxqfje+YBYE01-TYJE01<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 (:NEW.MZZYBZ='1') AND (:NEW.ZYGHID <> :OLD.ZYGHID) AND (:NEW.ZYGHID IS NOT NULL) and nvl(:new.yszid0,0)=0 THEN SELECT YSZID0 INTO :NEW.YSZID0 FROM ZY_BRXXB0 WHERE ZYID00= :NEW.ZYGHID; END IF; if :old.sfdjh0=0 and :new.sfdjh0>0 and :new.mzzybz='1' then --zhr 08.02.15 病人可能多次住院，确保(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 ('2','3') then errStr:='病人处于非在院状态，不能记账'; Raise Ecustom; end if; end if; if :old.sfdjh0=0 and :new.sfdjh0>0 then --YF-20190805-001 begin if :new.MZZYBZ='0' then SP_YJ_YJDCYPCKD(:new.YJDJH0,:new.ZLXMID,:new.ZYGHID,:new.KDKSBH,:new.KDYS00,:new.SL0000,LS_CFLSH0); else SP_YJ_YJDCYPCKD(:new.YJDJH0,:new.ZLXMID,:new.ZYGHID,:new.KDBQ00,:new.KDYS00,:new.SL0000,LS_CFLSH0); end if; :new.SM0000:=LS_CFLSH0; exception when others then LS_CFLSH0:=''; end; end if; if (nvl(:new.YYID00,0)<>0) and :new.XMZT00='6' then --检查治疗预约数据的处理 YJ-20161110-001 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 update YJ_JCHBXH set BRXM00='',YYID00=0,BRID00=0,YZID00=0,YJDJH0=0 where (YYID00,PBHBID,XH0000)=(select YYID00,PBHBID,YYH000 from YJ_YYBRB0 where YYID00=:new.YYID00); 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 if; end if; if updating and :NEW.ZLXMID>0 and :new.XMZT00='6' and nvl(:new.BBZT00,'0')<>'0' and :new.XMZT00<>:old.XMZT00 then --南平检验接口加 SELECT LBBH00 INTO VLBBH00 FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID; if VLBBH00=4 then ERRSTR:='检验项目已绑定条码或已签收,不能直接作废'; raise ECUSTOM; end if; end if; END IF; 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 tr_yj_yw0000_bins_upd;