CREATE OR REPLACE TRIGGER TR_JY_YW0000_BINS_UPD BEFORE INSERT OR DELETE OR UPDATE ON SD_JY.JY_YW0000 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare ErrStr varchar2(200); VJE0000 number(12,4); ECUSTOM exception; VLbbh00 bm_zlzd00.LBBH00%type; Vcount0 number(5); VNL0000 varchar2(50); VXB0000 varchar2(20); VBRXM00 JY_YW0000.BRXM00%type; VKWH000 JY_KWH000.KWH000%type; VRQSJ00 varchar2(20); VZWXM00 varchar2(20); V_CZLX00 char(1); V_TXM000 JY_YW0000.TXM000%type; V_PROCESS XT_XTRZ00.JCID00%type; V_YGBH00 XT_XTRZ00.YGBH00%type; intHZCD number(5,0); V_TJTMTSGZ XT_XTCS00.VALUE0%type; JY_QYWEBFWBRLX XT_XTCS00.VALUE0%type; JY_TJSFQS XT_XTCS00.VALUE0%type; JY_MZSFQS XT_XTCS00.VALUE0%type; JY_FJYKSBH XT_XTCS00.VALUE0%type; JY_CXZDSCDYYQDH XT_XTCS00.VALUE0%type; V_YDYSHKCXZLXM XT_XTCS00.VALUE0%type; V_YYID00 XT_YYXX00.YYID00%type; -- Person Date Comments -- Jxq 2009.09.21 　增加条码操作日志记录功能 -- JXQ 2010.02.23 条码日志记录JGZT00的变化 -- lintj 2010.10.29 更新排样时同时更新明细表 -- lintj 2010.11.01 更新明细表去除条件 -- chenyw 2010.12.10 出报告的报告单不允许修改急症标志 -- chenyw 2011.02.14 该功能该在程序里 -- chenyw 2015.04.21 条形码长度判断增加后缀字段 for LIS-20150421-001 -- chenyw 2015.05.21 增加个异常报错捕捉和提示 for LIS-20150521-001 -- chenyw 2015.06.30 脚本回撤 for LIS-20150617-001 -- Xutz 2015.10.28 高明中医院体检标本条码由体检系统生成前缀为年份，修改该触发器对该类型的条码不进行判断； for LIS-20151026-001 -- nixj 2016.06.02 集成平台与检验接口对接需要 for LIS-20160608-001 -- daihq 2016.12.10 集成平台与检验接口对接需要,把SGCJQZ赋值放在参数JY_QYWEBFWBRLX前面 for LIS9-20161210-001 -- Nixj 2018.05.16 归档莲花修改内容 for LIS9-20180516-001 -- Nixj 2018.11.16 去除体检类表名的影响 for LIS9-20181116-001 -- Nixj 2019.03.21 新增保护机制 for LIS9-20190314-001 -- wusd 2019.06.17 门诊采集时,增加JY_TJSFQS、JY_MZSFQS判断是否修改BBSDRQ for LIS9-20190425-005 -- Nixj 2020.01.20 当门诊未设置签收参数时,个别科室需要签收(设置参数)，在检验结果处理界面排样后撤销再次审核的时候会提示需要再签收。(已'签收'的状态会更改为'采集').请修正 for LIS9-20200103-002 -- Nixj 2020.03.25 检验待审核撤销是否自动删除对应结果 for LIS9-20200318-006 -- wusd 2021.04.14 增加JY_YDYSHKCXZLXM控制已打印、已审核可冲销诊疗项目 for LIS9-20210413-005 -- wusd 2022.03.30 增加部分TMX000操作日志 for LIS9-20220325-002 begin select nvl(max(trim(VALUE0)),'') into V_YDYSHKCXZLXM from XT_XTCS00 where NAME00='JY_YDYSHKCXZLXM'; if insertING then if :new.SQRQ00 is null then :new.SQRQ00:=to_char(sysdate,'YYYYMMDD'); :new.SQSJ00:=to_char(sysdate,'HH24:MI:SS'); end if; if :new.SGCJQZ is null then select QZ0000 into :new.SGCJQZ from JY_SGCJZD where DQZY00='Y' and rownum=1; end if; begin select length(trim(VALUE0)) into intHZCD from XT_XTCS00 where NAME00 in ('JY_JYTXMWSBBHZWH'); select trim(VALUE0) into JY_QYWEBFWBRLX from XT_XTCS00 where NAME00='JY_QYWEBFWBRLX'; exception when others then intHZCD:=0; JY_QYWEBFWBRLX:='N'; end; --注意福清这边因为走平台这样改 begin select YYID00 into V_YYID00 from XT_YYXX00 where ROWNUM=1; exception when others then raise_application_error(-20021,',YYID00 is not exist！*'); end; if V_YYID00='222285' then if (JY_QYWEBFWBRLX='P') and (:new.SFTJ00='Y') then return; end if; elsif(JY_QYWEBFWBRLX='P') or ((:new.SFTJ00='N') and (JY_QYWEBFWBRLX='H')) or ((:new.SFTJ00='Y') and (JY_QYWEBFWBRLX='T')) then return; end if; begin select decode(ZYZBID,4,'0','1') into :new.YBLX00 from BM_ZLZD00 A,YJ_YW0000 B where B.YJDJH0=:new.YJDJH0 and A.ZLXMID=B.ZLXMID; exception when others then :new.YBLX00 :='1'; end; if (:new.JYYWID is null) or (:new.JYYWID=0) then select SEQ_JY_YW0000_LSID00.NEXTVAL into :new.JYYWID from DUAL; end if; if (:new.ZYGHID > 0) and (:new.BRID00 = 0) then if :new.MZZYBZ='1' then --住院病人 ErrStr:='err3: 住院病人不存在ZYID00='||to_char(:new.ZYGHID); if (nvl(:new.YEXM00,'-1')='-1') then select Z.ZYH000,Z.BRID00,Z.XM0000,Z.XB0000,Z.CSRQ00,Z.RYCWH0 into :new.ZYHGHH,:new.BRID00,VBRXM00,VXB0000,:new.CSRQ00,:new.CH0000 from ZY_BRXXB0 Z where Z.ZYID00=:new.ZYGHID; if instr(VXB0000,'女') > 0 then :new.XB0000 :='F'; elsif instr(VXB0000,'男') > 0 then :new.XB0000 :='M'; else :new.XB0000 := 'W'; end if; -- if :new.BRXM00 is null then :new.BRXM00:=VBRXM00; -- end if; else select Z.ZYH000,Z.BRID00,Z.XM0000,Z.RYCWH0 into :new.ZYHGHH,:new.BRID00,VBRXM00,:new.CH0000 from ZY_BRXXB0 Z where Z.ZYID00=:new.ZYGHID; -- if :new.BRXM00 is null then :new.BRXM00:=VBRXM00; -- end if; end if; else--门诊病人 ErrStr:='err4: 挂号号不存在GHID00='||to_char(:new.ZYGHID); select GHH000,BRID00,XM0000,XB0000,CSRQ00 into :new.ZYHGHH,:new.BRID00,VBRXM00,VXB0000,:new.CSRQ00 from SF_BRXXB0 where GHID00=:new.ZYGHID; if instr(VXB0000,'女') > 0 then :new.XB0000 :='F'; elsif instr(VXB0000,'男') > 0 then :new.XB0000 :='M'; else :new.XB0000 := 'W'; end if; -- if :new.BRXM00 is null then :new.BRXM00:=VBRXM00; -- end if; end if; end if; if (:new.BRID00>0) and (:new.ZYGHID=0) and (:new.BRXM00 is null) then --传入病人ID ErrStr:='err5: 病人不存在BRID00='||to_char(:new.BRID00); select BRXM00,BRXB00,BRCSRQ into :new.BRXM00,VXB0000,:new.CSRQ00 from BM_BRXXB0 where BRID00=:new.BRID00; :new.MZZYBZ:='0'; if instr(VXB0000,'女') > 0 then :new.XB0000 :='F'; elsif instr(VXB0000,'男') > 0 then :new.XB0000 :='M'; else :new.XB0000 := 'W'; end if; end if; select SF_JY_CSRQTonL(:new.CSRQ00) into VNL0000 from DUAL where rownum=1; if VNL0000 is null then :new.NL0000 := null; :new.NLDW00 := null; else :new.NL0000 :=substr(VNL0000,1,length(VNL0000)-1); VNL0000 :=substr(VNL0000,length(VNL0000),1); if VNL0000 = '岁' then :new.NLDW00 :='Y'; elsif VNL0000 = '月' then :new.NLDW00 :='M'; else :new.NLDW00 :='D'; end if; end if; elsif UPDATING then --更改 if :new.SSR000 is null and :old.SSR000 is not null then if :new.CZY000 <> :old.SSR000 then select count(*) into Vcount0 from XT_XTCS00 where NAME00='JY_CJJSYH' and instr(','||trim(VALUE0)||',',','||:new.CZY000||',')>0; if Vcount0 = 0 then select ZWXM00 into VZWXM00 from BM_YGBM00 where YGBH00=:old.SSR000; raise_application_error(-20090,'样本'||:new.YBH000||'由'||VZWXM00||'上锁，您没有权限解锁！*'); end if; end if; end if; update JY_YWMX00 set PYRQ00=:new.PYRQ00, YBH000=:new.YBH000 where JYYWID=:new.JYYWID; if :new.CSRQ00<>:old.CSRQ00 then select SF_JY_CSRQTonL(:new.CSRQ00) into VNL0000 from DUAL where rownum=1; if VNL0000 is null then :new.NL0000 := null; :new.NLDW00 := null; else :new.NL0000 :=substr(VNL0000,1,length(VNL0000)-1); VNL0000 :=substr(VNL0000,length(VNL0000),1); if VNL0000 = '岁' then :new.NLDW00 :='Y'; elsif VNL0000 = '月' then :new.NLDW00 :='M'; else :new.NLDW00 :='D'; end if; end if; end if; begin select ','||VALUE0||',' into JY_CXZDSCDYYQDH from XT_XTCS00 where name00='JY_CXZDSCDYYQDH'; exception when NO_DATA_FOUND then JY_CXZDSCDYYQDH:=''; end; if (instr(JY_CXZDSCDYYQDH,','||:old.YQDH00||',')>0) and (:old.JGZT00='4') and (:new.JGZT00='2' or :new.JGZT00='1') then delete JY_JYJG00_TEMP where YBH000=:old.YBH000 and PYRQ00=:old.PYRQ00; end if; --门诊采集时 if (:new.JGZT00='1') and (:old.JGZT00='0') and (:new.MZZYBZ='0') then begin select trim(VALUE0) into JY_TJSFQS from XT_XTCS00 where NAME00 in ('JY_TJSFQS'); exception when others then JY_TJSFQS:='N'; end; if JY_TJSFQS <> 'Y' then JY_TJSFQS := 'N'; end if; begin select trim(VALUE0) into JY_MZSFQS from XT_XTCS00 where NAME00 in ('JY_MZSFQS'); exception when others then JY_MZSFQS:='N'; end; if JY_MZSFQS <> 'Y' then JY_MZSFQS := 'N'; end if; if (:new.SFTJ00 = 'Y' and JY_TJSFQS = 'Y') or (:new.MZZYBZ='0' and :new.SFTJ00 = 'N' and JY_MZSFQS='Y') then :new.BBSDRQ := null; :new.BBSDSJ := null; :new.BBSDR0 := null; else :new.BBSDRQ := :new.BBCYRQ; :new.BBSDSJ := :new.BBCYSJ; :new.BBSDR0 := :new.BBCJR0; end if; end if; if (:new.JGZT00 IN ('0','1','2','3')) and (:old.JGZT00 IN ('6','8','9')) and (instr(','||V_YDYSHKCXZLXM||',',:new.ZLXMID)<1) then raise_application_error(-20050,'该样本已经审核/打印，请先刷新再操作！*'); end if; if (:new.MZZYBZ='1') and (:new.JGZT00 IN ('0','1')) and (:old.JGZT00 IN ('4','6','8')) then :new.JGZT00 := '2'; end if; begin select VALUE0 into JY_FJYKSBH from XT_XTCS00 where NAME00='JY_FJYKSBH'; exception when others then JY_FJYKSBH:=''; end; if(instr(','||JY_FJYKSBH||',',','||:new.SQKS00||',')>0) and (:new.JGZT00 in ('0','1')) and (:old.JGZT00 in ('4','6','8')) then :new.JGZT00 := '2'; end if; begin select trim(VALUE0) into V_TJTMTSGZ from XT_XTCS00 where NAME00 in ('JY_TJTMTSGZ'); exception when others then V_TJTMTSGZ:='N'; end; if (:new.JGZT00 = '1') and (:old.JGZT00 = '0') and (:new.TXM000 is not null) and (:new.SFTJ00='N' or (:new.SFTJ00='Y' and V_TJTMTSGZ='N')) then begin select TXMCD0 into Vcount0 from JY_TXMLB0 where TXMQZ0=substr(:new.TXM000,1,2); exception when others then ErrStr:='条码前缀'||substr(:new.TXM000,1,2)||'在JY_TXMLB0没有维护！请检查是否输入正确！'; raise ECUSTOM; end; if (length(:new.TXM000) <> Vcount0) and (length(:new.TXM000)<>(Vcount0-intHZCD)) then ErrStr:='条码号'||:new.TXM000||'编码的格式不对！请检查是否输入正确！'; raise ECUSTOM; end if; end if; if ((:old.TXM000 is null) and (:new.TXM000 is not null)) or --2009.09.21 jxq ((:old.TXM000 is not null) and (:new.TXM000 is null)) or (trim(:new.JGZT00) = '3') then if (:old.TXM000 is null) and (:new.TXM000 is not null) then V_CZLX00 := '1'; --标本采集 V_TXM000 := :new.TXM000; end if; if (:old.TXM000 is not null) and (:new.TXM000 is null) then V_CZLX00 := '2'; --标本删除 V_TXM000 := :old.TXM000; end if; if (trim(:new.JGZT00) = '3') then V_CZLX00 := '3'; --标本退还 V_TXM000 := :old.TXM000; end if; select USERENV('SESSIonID') PROCESS into V_PROCESS from DUAL where rownum=1; begin select YGBH00 into V_YGBH00 from XT_XTRZ00 where JCID00=V_PROCESS and LSH000=(select MAX(LSH000) from XT_XTRZ00 where JCID00=V_PROCESS); exception when others then V_YGBH00:=0; end; insert into JY_CZJL00 (CZID00,JYYWID,CZLX00,CZTABLE,CZNR00,CZRQ00,CZSJ00,YGBH00,CZBM00,TXM000) VALUES (SEQ_JY_CZJL00_CZID00.NEXTVAL, :new.JYYWID, V_CZLX00, 'JY_YW0000', '标本操作：'||decode(V_CZLX00, '1', '采集', '2', '删除', '3', '退还')|| '，JGZT00:'||:old.JGZT00||'->'||:new.JGZT00, to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS'), V_YGBH00, 'TMCZ', V_TXM000); end if; --条形码操作日志 if ((:old.JGZT00='1') and (:new.JGZT00='2')) then--签收 insert into JY_CZJL00(CZID00,JYYWID,CZLX00,CZTABLE,CZNR00,CZRQ00,CZSJ00,YGBH00,CZBM00,TXM000) values(SEQ_JY_CZJL00_CZID00.NEXTVAL,:old.JYYWID,'1','JY_YW0000','标本操作：签收，JGZT00:1->2', to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'hh24:mi:ss'),:new.BBSDR0,'TMCZ',:old.TXM000); elsif ((:old.JGZT00='2') and (:new.JGZT00='1')) then--取消签收 insert into JY_CZJL00(CZID00,JYYWID,CZLX00,CZTABLE,CZNR00,CZRQ00,CZSJ00,YGBH00,CZBM00,TXM000) values(SEQ_JY_CZJL00_CZID00.NEXTVAL,:old.JYYWID,'1','JY_YW0000','标本操作：取消签收，JGZT00:2->1', to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'hh24:mi:ss'),:old.BBSDR0,'TMCZ',:old.TXM000); elsif ((:old.JGZT00='2') and (:new.JGZT00='4')) then insert into JY_CZJL00(CZID00,JYYWID,CZLX00,CZTABLE,CZNR00,CZRQ00,CZSJ00,YGBH00,CZBM00,TXM000) values(SEQ_JY_CZJL00_CZID00.NEXTVAL,:old.JYYWID,'1','JY_YW0000','标本操作：排样，JGZT00:2->4', to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'hh24:mi:ss'),:new.BGR000,'TMCZ',:old.TXM000); elsif ((:old.JGZT00='4') and (:new.JGZT00='2')) then insert into JY_CZJL00(CZID00,JYYWID,CZLX00,CZTABLE,CZNR00,CZRQ00,CZSJ00,YGBH00,CZBM00,TXM000) values(SEQ_JY_CZJL00_CZID00.NEXTVAL,:old.JYYWID,'1','JY_YW0000','标本操作：取消排样，JGZT00:4->2', to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'hh24:mi:ss'),:old.BGR000,'TMCZ',:old.TXM000); end if; --出报告的报告单,不允许修改急症标志 /*if :new.yxbz00<>:old.yxbz00 then if :old.jgzt00=:new.jgzt00 then if (:new.jgzt00='8' or :new.jgzt00='6' or :new.jgzt00='9') then raise_application_error(-20051,'该样本已经审核/出报告不允许修改急症状态，请确认！*'); end if; elsif (:new.jgzt00='8' or :new.jgzt00='6' or :new.jgzt00='9') and (:old.jgzt00='8' or :old.jgzt00='6' or :old.jgzt00='9') then raise_application_error(-20051,'该样本已经审核/出报告不允许修改急症状态，请确认！*'); end if; end if; */ else delete from JY_YWMX00 where JYYWID = :old.JYYWID; end if; if insertING or UPDATING then --select count(*) into Vcount0 from TJ_YJDJH0 where YJDJH0=:new.YJDJH0; --if Vcount0 > 0 then -- :new.SFTJ00 := 'Y'; --end if; if (:old.JGZT00='4' or :old.JGZT00='6' or :old.JGZT00='8') and (:new.JGZT00='1' or :new.JGZT00='2' or :new.JGZT00='0') and ((:new.YBH000 is not null) or (:new.PYRQ00 is not null)) and (instr(','||V_YDYSHKCXZLXM||',',:new.ZLXMID)<1) then ErrStr:='条码号'||:new.TXM000||' 操作异常，请联系管理员！'; raise ECUSTOM; end if; if :new.YXBZ00 = '0' then :new.YXBZ00 := 'N'; end if; if :new.YXBZ00 = '1' then :new.YXBZ00 := 'Y'; end if; if :new.BBID00 is null then begin select BBID00 into :new.BBID00 from BM_ZLZD00 where rownum=1 and ZLXMID=:new.ZLXMID; exception when others then :new.BBID00 := null; end; end if; if (:new.YBH000 is not null) and (:new.YQDH00 is null) then :new.YQDH00 :=substr(:new.YBH000,1,2); end if; if :new.SGCJQZ is null then select QZ0000 into :new.SGCJQZ from JY_SGCJZD where DQZY00='Y' and rownum=1; 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;