PROMPT 105. TR_BQ_YJYZ00_AFTERUPD_SS --drop trigger TR_BQ_YJYZ00_AFTERUPD_SS; CREATE OR REPLACE TRIGGER TR_BQ_YJYZ00_AFTERUPD_SS before UPDATE OF YZZT00 ON BQ_YJYZ00 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW WHEN ((new.yzzt00='3' or new.yzzt00='0') and (new.yzlb00=6 or new.yzlb00=3 or new.yzlb00=5) and new.clbz00='1') declare Vssdh00 ss_yw0000.ssdh00%type; Vparams varchar2(188); Vzybrxx zy_brxxb0%rowtype; vCounter Number; vCounter2 Number; VYSTJHSCSSD CHAR(1); --医生开完手术医嘱，提交后，是否直接生成手术单 Y: 是 N: 否 默认为N VSSYZSFGJ CHAR(1); --医生开完手术医嘱，病区执行后，是否产生手术的医技业务单估计,Y:是；N:否 默认为Y VSSYZTJJZSJ Varchar2(8); -- 手o术医嘱提交截至时间,格式为：10:30:00,默认为0,表示不限制截至时间 VZDMC00 BQ_BRZDXX.ZDMC00%TYPE; VSFICU0 CHAR(1); --是否ICU VYZZXSCSSSQDDSFHB CHAR(1); --医嘱执行单执行生成手术申请单是否合并 VYZCFID YJ_YW0000.YZCFID%TYPE; --医嘱处方ID -- MODIFICATION HISTORY -- Person Date Comments -- yangy 2007.12.29 医生医嘱提交后直接生成手术单 -- yangy 2008.03.12 解决手术费用划价时不能收手术主项目的问题 -- yangy 2008.03.21 医生开完手术医嘱，病区执行后，不产生手术的医技业务单估计 -- yangy 2008.03.22 医生开完手术医嘱，如果是10：30分以后提交的不生成手术申请单 -- yangy 2008.03.28 医生开完手术医嘱，如果是频次为ST的手术，不管什么时候提交都生成手术申请单 -- yangy 2008.04.02 手术医嘱执行时，会很慢； -- yangy 2008.07.01 SS_YW0000.sqzd00由原来的ICD10码改为入院诊断名称 -- yangy 2008.10.22 ICU病区申请的手术业务申请科室应该算原科室、 -- yangh 2009.04.16 生成手术单前，判断是否已经有为安排的手术，没有的话就做合并操作。 -- yangy 2009.07.02 医嘱执行时，没有生成 SS_SSYJD0. begin select COUNT(*) into vCounter from vw_bm_ssbm00 where bmbh00=:NEW.ZXKS00; if vCounter=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 TRIM(NVL(MAX(VALUE0),'0')) INTO VSSYZTJJZSJ FROM XT_XTCS00 WHERE NAME00='SS_SSYZTJJZSJ'; SELECT UPPER(TRIM(NVL(MAX(VALUE0),'N'))) INTO VYZZXSCSSSQDDSFHB FROM XT_XTCS00 WHERE NAME00='BQ_YZZXSCSSSQDDSFHB'; VYZCFID := to_char(:NEW.YZMXID); /* IF (VYSTJHSCSSD='Y') AND (((:new.yzzt00<>'0') or (:old.yzzt00<>'7')) AND :NEW.YZZT00<>'0') THEN RETURN; END IF; IF (VYSTJHSCSSD<>'Y') AND (:new.yzzt00<>'3') THEN RETURN; END IF; */ begin select ZDMC00 INTO VZDMC00 FROM BQ_BRZDXX WHERE ZYID00=:NEW.ZYID00 and zdlb00='2' AND ROWNUM=1; exception when others then VZDMC00 := ''; end; select nvl(sficu0,'0') into VSFICU0 from bm_bmbm00 where bmbh00= :new.bqh000; IF (VYSTJHSCSSD='Y') AND ((:new.yzzt00='0') and (:old.yzzt00='7')) THEN IF VSSYZTJJZSJ<>'0' THEN if (to_char(sysdate,'HH24:MI:SS') > VSSYZTJJZSJ) AND ((UPPER(TRIM(:NEW.PCMC00)) is null) or (UPPER(TRIM(:NEW.PCMC00))<>'ST')) THEN RETURN; END IF; END IF; select COUNT(1) into vCounter2 from ss_yw0000 where zyghid=:new.zyid00 and(SSZT00='0'); if (vCounter2 > 0) and (VYZZXSCSSSQDDSFHB = 'Y') then select ssdh00 into Vssdh00 from (select * from ss_yw0000 where zyghid=:new.zyid00 and(SSZT00='0') order by sqrq00 desc) where rownum=1; insert into ss_ywmx00 (id0000,ssdh00,sfzss0,ssbh00,ssdjmc) select SQ_SS_SSYWMX_ID0000.nextval,Vssdh00,'1',:new.zlxmid,ssdjmc from bm_zlzd00 a,bm_ssdj00 b where a.zlxmid=:new.zlxmid and a.ssdjbh=b.ssdjbh(+); else 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,sqbqsm,czy000,JZBZ00) Values(Vssdh00,'1',Vzybrxx.zyh000,Vzybrxx.zyid00,Vzybrxx.brid00,Vzybrxx.xm0000,Vzybrxx.xb0000,Vzybrxx.csrq00, :new.zxks00,:new.kzys00,decode(VSFICU0,'1',Vzybrxx.yksh00,:new.ksh000),:new.bqh000, :new.KZYS00,'9',:new.qyrq00,:new.qysj00,'Y', :new.qyrq00,:new.qysj00,:new.bz0000,:new.yzmxid,VZDMC00,Vzybrxx.rysqk0,0, Decode(UPPER(TRIM(:NEW.PCMC00)),'ST','Y','N') ); --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) select SQ_SS_SSYWMX_ID0000.nextval,Vssdh00,'0',:new.zlxmid,ssdjmc from bm_zlzd00 a,bm_ssdj00 b where a.zlxmid=:new.zlxmid and a.ssdjbh=b.ssdjbh(+); end if; :new.SSDH00 := Vssdh00; END IF; IF (VYSTJHSCSSD='Y') AND (:new.yzzt00='3') THEN if VSSYZSFGJ<>'N' THEN IF VSSYZTJJZSJ<>'0' THEN if (to_char(sysdate,'HH24:MI:SS') > VSSYZTJJZSJ) AND ((UPPER(TRIM(:NEW.PCMC00)) is null) or (UPPER(TRIM(:NEW.PCMC00))<>'ST')) THEN RETURN; END IF; END IF; insert into ss_ssyjd0(ssdh00,yjdjh0,ssmzbz) select :OLD.SSDH00,yjdjh0,'0' from yj_yw0000 where zyghid = :new.zyid00 and yzcfid = VYZCFID; ELSE DELETE FROM BQ_YJYZJJ WHERE YZMXID = :NEW.YZMXID; DELETE FROM YJ_YW0000 WHERE zyghid = :new.zyid00 and yzcfid = VYZCFID AND MZZYBZ='1'; DELETE FROM YJ_YWMX00 WHERE YJDJH0 IN (SELECT YJDJH0 FROM YJ_YW0000 WHERE zyghid = :new.zyid00 and yzcfid = VYZCFID AND MZZYBZ='1'); DELETE FROM YJ_YWJJ00 WHERE YJDJH0 IN (SELECT YJDJH0 FROM YJ_YW0000 WHERE zyghid = :new.zyid00 and yzcfid = VYZCFID AND MZZYBZ='1'); END IF; END IF; IF (VYSTJHSCSSD<>'Y') AND (:new.yzzt00='3') THEN select COUNT(1) into vCounter2 from ss_yw0000 where zyghid=:new.zyid00 and(SSZT00='0'); if (vCounter2 > 0) and (VYZZXSCSSSQDDSFHB = 'Y') then select ssdh00 into Vssdh00 from (select * from ss_yw0000 where zyghid=:new.zyid00 and(SSZT00='0') order by sqrq00 desc) where rownum=1; insert into ss_ywmx00 (id0000,ssdh00,sfzss0,ssbh00,ssdjmc) select SQ_SS_SSYWMX_ID0000.nextval,Vssdh00,'1',:new.zlxmid,ssdjmc from bm_zlzd00 a,bm_ssdj00 b where a.zlxmid=:new.zlxmid and a.ssdjbh=b.ssdjbh(+); else 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,sqbqsm,czy000) Values(Vssdh00,'1',Vzybrxx.zyh000,Vzybrxx.zyid00,Vzybrxx.brid00,Vzybrxx.xm0000,Vzybrxx.xb0000,Vzybrxx.csrq00, :new.zxks00,:new.kzys00,decode(VSFICU0,'1',Vzybrxx.yksh00,:new.ksh000),:new.bqh000, :new.KZYS00,'9',:new.qyrq00,:new.qysj00,'Y', :new.qyrq00,:new.qysj00,:new.bz0000,:new.yzmxid,VZDMC00,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) select SQ_SS_SSYWMX_ID0000.nextval,Vssdh00,'0',:new.zlxmid,ssdjmc from bm_zlzd00 a,bm_ssdj00 b where a.zlxmid=:new.zlxmid and a.ssdjbh=b.ssdjbh(+); end if; if VSSYZSFGJ<>'N' THEN insert into ss_ssyjd0(ssdh00,yjdjh0,ssmzbz) select Vssdh00,yjdjh0,'0' from yj_yw0000 where zyghid = :new.zyid00 and yzcfid=VYZCFID; ELSE DELETE FROM BQ_YJYZJJ WHERE YZMXID = :NEW.YZMXID; DELETE FROM YJ_YW0000 WHERE zyghid = :new.zyid00 and yzcfid = VYZCFID AND MZZYBZ='1'; DELETE FROM YJ_YWMX00 WHERE YJDJH0 IN (SELECT YJDJH0 FROM YJ_YW0000 WHERE zyghid = :new.zyid00 and yzcfid = VYZCFID AND MZZYBZ='1'); DELETE FROM YJ_YWJJ00 WHERE YJDJH0 IN (SELECT YJDJH0 FROM YJ_YW0000 WHERE zyghid = :new.zyid00 and yzcfid = VYZCFID AND MZZYBZ='1'); END IF; END IF; exception when others then raise_application_error(-20001,substr(nvl(sqlerrm, '原因不明出错')||'！*'||Vparams,1,220)); end; /