create or replace trigger TR_BQ_YJYZ00_AFTERUPD_SS AFTER UPDATE OF yzzt00 ON bq_yjyz00 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW WHEN ((new.yzzt00='3' or new.yzzt00='0' or new.yzzt00='4') and (new.yzlb00=6 or new.yzlb00=3 or new.yzlb00=5 or new.yzlb00=9) and new.clbz00='1') declare Vssdh00 ss_yw0000.ssdh00%type; Vbz0000 ss_yw0000.bz0000%type; Vparams varchar2(188); Vzybrxx zy_brxxb0%rowtype; vCounter4 Number; vCounter1 Number; vCounter2 Number; vCounter3 Number;--是否已经产生手术申请单 VYSTJHSCSSD CHAR(1); --医生开完手术医嘱，提交后，是否直接生成手术单 Y: 是 N: 否 默认为N VSSYZSFGJ CHAR(1); --医生开完手术医嘱，病区执行后，是否产生手术的医技业务单估计,Y:是；N:否 默认为Y VYZZXSCSSSQDDSFHB CHAR(1); --医嘱执行单执行生成手术申请单是否合并 VSS_QXYZSFSSTH CHAR(1);--取消医嘱是否手术退回 VSS_BQSSSQSFMSXSS CHAR(1);--病区的手术申请是否描述性手术 VSS_SFYFSZT CHAR(1);--是否有发送状态 VSS_SSSQSFSYHTBLJK char(1); VZS_SSTJSFSCZLXMYZ XT_XTCS00.VALUE0%type; Verrmsg VarChar2(255); --错误提示 Ecustom EXCEPTION ; --错误提示 vCounter Number; -- MODIFICATION HISTORY -- Person Date Comments -- qks 2007-08-30 解决病区手术类医嘱(要为短嘱)执行速度慢问题 -- yangy 2007.12.29 医生医嘱提交后直接生成手术单 -- yangy 2009.01.12 手术医嘱不产生医技业务估计 -- chenqw 2009.01.16 手术医嘱取消时手术被退回 -- chenqw 2009.01.16 手术医嘱提交时不发送到手术室，改为发送状态 -- yangy 2009.02.16 描述性的医嘱也要求可以发送到手术室 -- yangh 2009.04.12 生成手术单前，判断是否已经有为安排的手术，没有的话就做合并操作。 -- chenqw 2009.06.06 生成手术单前，判断是否已经有该申请单，如果有，则不再生成。 -- chenqw 2009.12.29 病区的手术申请备注中添加：'(病区申请)' -- liuj 2010.07.02 手术业务表ss_yw0000插入的数据内容增加sqzdmc字段。 -- csf 2011.07.25 手术名称保存致SS_YWMX00 -- liuj 2011.12.23 修改：VYZZXSCSSSQDDSFHB=N且该yzmxid已存在手术的时候如果设置有发送状态无法修改sszt00=6 by ZYYS-20111208-001 -- qks 2015.11.24 住院临床科室当成手术室处理,非手术类医嘱不生成SS_YW0000记录 BQHS9-20151124-002 -- liuj 2016.12.09 增加参数有使用海泰接口的时候不走这个触发器 SSMZ9-20161228-001 -- dongxb 2024.06.05 医大附二不走该触发器 for BQHS9.0-20240605-001 -- dongxb 2024.09.18 ZS_SSTJSFSCZLXMYZ为Y住院医生站产生手术信息，这边不产生 begin --医大附二不走该触发器 select count(1) into vCounter4 from XT_YYXX00 where YYID00 in('225085'); if vCounter4>0 then return; end if; SELECT UPPER(TRIM(NVL(MAX(VALUE0),'N'))) INTO VYSTJHSCSSD FROM XT_XTCS00 WHERE NAME00='SS_YSTJHSCSSD'; SELECT UPPER(TRIM(NVL(MAX(VALUE0),'Y'))) INTO VSSYZSFGJ FROM XT_XTCS00 WHERE NAME00='SS_SSYZSFGJ'; SELECT UPPER(TRIM(NVL(MAX(VALUE0),'N'))) INTO VSS_QXYZSFSSTH FROM XT_XTCS00 WHERE NAME00='SS_QXYZSFSSTH'; SELECT UPPER(TRIM(NVL(MAX(VALUE0),'N'))) INTO VSS_SFYFSZT FROM XT_XTCS00 WHERE NAME00='SS_SFYFSZT'; SELECT UPPER(TRIM(NVL(MAX(VALUE0),'N'))) INTO VYZZXSCSSSQDDSFHB FROM XT_XTCS00 WHERE NAME00='BQ_YZZXSCSSSQDDSFHB'; SELECT UPPER(TRIM(NVL(MAX(VALUE0),'N'))) INTO VSS_BQSSSQSFMSXSS FROM XT_XTCS00 WHERE NAME00='SS_BQSSSQSFMSXSS'; SELECT UPPER(TRIM(NVL(MAX(VALUE0),'N'))) INTO VSS_SSSQSFSYHTBLJK FROM XT_XTCS00 WHERE NAME00='SS_SSSQSFSYHTBLJK'; SELECT UPPER(TRIM(NVL(MAX(VALUE0),'N'))) INTO VZS_SSTJSFSCZLXMYZ FROM XT_XTCS00 WHERE NAME00='ZS_SSTJSFSCZLXMYZ'; if VSS_BQSSSQSFMSXSS = 'Y' then Vbz0000 := '(病区申请)'||:NEW.BZ0000; IF VSS_SSSQSFSYHTBLJK='Y' THEN --是否使用海泰电子病历接口 RETURN; END IF; else Vbz0000 := :NEW.BZ0000; end if; if VZS_SSTJSFSCZLXMYZ ='Y' then return; end if; select COUNT(*) into vCounter4 from vw_bm_ssbm00 where bmbh00=:NEW.ZXKS00; select count(*) into vCounter3 from ss_yw0000 where yzmxid=:new.yzmxid; if (vCounter4>0) and (VSSYZSFGJ='N') and (:new.yzzt00='3') and (VYSTJHSCSSD='Y') THEN DELETE FROM BQ_YJYZJJ WHERE YZMXID = :NEW.YZMXID; DELETE FROM YJ_YWMX00 WHERE YJDJH0 IN (SELECT YJDJH0 FROM YJ_YW0000 WHERE zyghid = :new.zyid00 and yzcfid = (:NEW.YZMXID) AND MZZYBZ='1'); DELETE FROM YJ_YWJJ00 WHERE YJDJH0 IN (SELECT YJDJH0 FROM YJ_YW0000 WHERE zyghid = :new.zyid00 and yzcfid = (:NEW.YZMXID) AND MZZYBZ='1'); DELETE FROM YJ_YW0000 WHERE zyghid = :new.zyid00 and yzcfid = (:NEW.YZMXID) AND MZZYBZ='1'; END IF; if (vCounter4>0) and (VSS_QXYZSFSSTH='Y') and (:new.yzzt00='4') then select COUNT(1) into vCounter1 from ss_yw0000 where yzmxid=:new.yzmxid and zyghid=:new.zyid00 and sszt00='2' and mzzybz='1'; if vCounter1=1 then raise Ecustom; else update ss_yw0000 set sszt00='1' where yzmxid=:new.yzmxid and zyghid=:new.zyid00 and sszt00<>'1'and mzzybz='1'; end if; return; end if; IF (VYSTJHSCSSD='Y') AND ((:new.yzzt00<>'0') or (:old.yzzt00<>'7')) THEN RETURN; END IF; IF (VYSTJHSCSSD<>'Y') AND (:new.yzzt00<>'3') THEN RETURN; END IF; if vCounter4<>0 then --2015.11.20 if :New.YZLB00<>6 then select count(1) into vCounter from bm_bmbm00 where bmbh00=:NEW.ZXKS00 and bmxz00 in ('0','1'); if vCounter>0 then RETURN; end if; end if; select COUNT(1) into vCounter2 from ss_yw0000 where zyghid=:new.zyid00 and(SSZT00='0' or SSZT00='2'); if (vCounter2 > 0) and (VYZZXSCSSSQDDSFHB = 'Y') then select ssdh00 into Vssdh00 from (select * from ss_yw0000 where zyghid=:new.zyid00 and(SSZT00='0' or SSZT00='2') order by sqrq00 desc) where rownum=1; insert into ss_ywmx00 (id0000,ssdh00,sfzss0,ssbh00,ssdjmc,ssxmjc) select SQ_SS_SSYWMX_ID0000.nextval,Vssdh00,'1',:new.zlxmid,ssdjmc,a.ZLXMJC from bm_zlzd00 a,bm_ssdj00 b where a.zlxmid=:new.zlxmid and a.ssdjbh=b.ssdjbh(+); else if vCounter3<>0 then if (:new.yzzt00='0') and (VSS_SFYFSZT='Y') then Update ss_yw0000 set sszt00='6' where yzmxid=:new.yzmxid and sszt00='0'; end if; return; end if; select SQ_SS_YW0000_SSDH00.nextval into Vssdh00 from dual where rownum=1; select * into Vzybrxx from zy_brxxb0 x where x.zyid00=:new.zyid00; insert into ss_yw0000 (ssdh00,mzzybz,zyhghh,zyghid,brid00,xm0000,xb0000,csrq00, ssks00,sqys00,sqks00,sqbq00,sqr000,srkslb,yyssrq,yysssj,yybz00, ssqsrq,ssqssj,bz0000,yzmxid,sqzd00,sqzdmc,sqbqsm,czy000) Values(Vssdh00,'1',Vzybrxx.zyh000,Vzybrxx.zyid00,Vzybrxx.brid00,Vzybrxx.xm0000,Vzybrxx.xb0000,Vzybrxx.csrq00, :new.zxks00,:new.kzys00,:new.ksh000,:new.bqh000, 0,'9',:new.qyrq00,:new.qysj00,'Y', :new.qyrq00,:new.qysj00,Vbz0000,:new.yzmxid,Vzybrxx.ryzd00,Vzybrxx.ryzdmc,Vzybrxx.rysqk0,0); update ss_yw0000 set sqr000=(select ygbh00 from bm_ygbm00 where zwxm00=:new.hdrxm0 and rownum=1) where ssdh00=Vssdh00; update ss_yw0000 set czy000=(select ygbh00 from bm_ygbm00 where zwxm00=:new.lrrxm0 and rownum=1) where ssdh00=Vssdh00; update ss_yw0000 set ssdjmc=(select ssdjmc from bm_zlzd00 a,bm_ssdj00 b where a.zlxmid=:new.zlxmid and a.ssdjbh=b.ssdjbh) where ssdh00=Vssdh00; insert into ss_ywmx00 (id0000,ssdh00,sfzss0,ssbh00,ssdjmc,ssxmjc) select SQ_SS_SSYWMX_ID0000.nextval,Vssdh00,'0',:new.zlxmid,ssdjmc,a.zlxmjc from bm_zlzd00 a,bm_ssdj00 b where a.zlxmid=:new.zlxmid and a.ssdjbh=b.ssdjbh(+); end if; insert into ss_ssyjd0(ssdh00,yjdjh0,ssmzbz) select Vssdh00,yjdjh0,'0' from yj_yw0000 where yzcfid=:NEW.YZMXID and zyghid=:new.zyid00; if (VSSYZSFGJ='N') and (:new.yzzt00='3') THEN DELETE FROM BQ_YJYZJJ WHERE YZMXID = :NEW.YZMXID; DELETE FROM YJ_YWMX00 WHERE YJDJH0 IN (SELECT YJDJH0 FROM YJ_YW0000 WHERE zyghid = :new.zyid00 and yzcfid = (:NEW.YZMXID) AND MZZYBZ='1'); DELETE FROM YJ_YWJJ00 WHERE YJDJH0 IN (SELECT YJDJH0 FROM YJ_YW0000 WHERE zyghid = :new.zyid00 and yzcfid = (:NEW.YZMXID) AND MZZYBZ='1'); DELETE FROM YJ_YW0000 WHERE zyghid = :new.zyid00 and yzcfid = (:NEW.YZMXID) AND MZZYBZ='1'; END IF; -- else -- Update ss_yw0000 set sszt00='3' where yzmxid=:new.yzmxid and sszt00 in ('0','2'); end if; if (:new.yzzt00='0') and (VSS_SFYFSZT='Y') then Update ss_yw0000 set sszt00='6' where yzmxid=:new.yzmxid ; end if; exception when Ecustom then raise_application_error(-20005,'手术已经被删除或已经被安排 '||'！*'); when others then raise_application_error(-20001,substr(nvl(sqlerrm, '原因不明出错')||'！*'||Vparams,1,220)); end; /