CREATE OR REPLACE TRIGGER TR_JY_YJ_YW0000_AUP AFTER INSERT OR DELETE OR UPDATE ON SD_YJ.YJ_YW0000 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare V_count0 number; V_JYYWID number; V_VALUE0 varchar2(10); V_insert number; V_SL0000 number; V_ZDID00 XT_ICD900.ICD900%type; V_TXMFS0 char(1); V_ZHXMCF char(1); --组合项目拆分单据 V_ZLXMID BM_ZLZD00.ZLXMID%type; V_YXBZ00 JY_YW0000.YXBZ00%type; V_JGZT00 JY_YW0000.JGZT00%type; V_MZZYBZ JY_YW0000.MZZYBZ%type; V_DJH000 number; V_SFCSDY XT_XTCS00.VALUE0%type; --是否冲销打印 V_JSBBSFYXCX XT_XTCS00.VALUE0%type; --接收标本是否允许冲销 V_JYBBPYZQSFYXCX XT_XTCS00.VALUE0%type; --接收标本排样之前是否允许冲销 JY_TSSYNXZDY XT_XTCS00.VALUE0%type; --当条码方式为混合模式的时候 自打印是哪些 JY_JYCJSFCYZDYHYTM XT_XTCS00.VALUE0%type; V_YEXM00 JY_YW0000.YEXM00%type; V_YECSRQ varchar2(8); V_YEXB00 varchar2(1); JY_TXMSLZLXMID XT_XTCS00.VALUE0%type; JY_QYWEBFWBRLX XT_XTCS00.value0%type; JY_TJSJCSSFZJCPT XT_XTCS00.value0%type; JY_ZLXMQYKJ XT_XTCS00.VALUE0%type; V_YDYSHKCXZLXM XT_XTCS00.VALUE0%type; VYYID00 BM_BMBM00.YYID00%type; V_YYID00 XT_YYXX00.YYID00%type; VHBZYXM char(1); verr varchar2(10); cursor CUR_ZHXM00 is select distinct ZHXMID,ZLXMID,TDJTTM from XT_ZHMX00 where ZHXMID=V_ZLXMID; -- MODifICATIon HISTorY -- Person Date Comments -- Jxq 2009.07.30 增加参数JY_SFCXDYGDBG控制是否能冲销打印过的报告 -- LZY 2009.12.10 开医嘱时条码调用方式医技单据号生成 -- LZY 2009.12.29 开医嘱时条码调用方式改回按病人ID生成 -- lintj 2010.05.21 接收标本是否允许冲销 -- LZY 2010.06.23 已采集标本不允许删除 -- lintj 2010.08.31 对检验项目改非检验项目的处理 -- lintj 2010.10.10 1、增加：撤销冲销后，还原被冲销的记录 2、整理该触发器 -- chenyw 2010.11.10 插入jy_yw0000的cjksbh -- lintj 2010.12.02 是否允许删除或冲销标本的判断 -- chenyw 2011.06.03 同项目多数量拆分之后，如果冲销一条会报错，做处理 -- chenyw 2011.07.26 检验标本排样之前是否允许冲销 -- chenyw 2011.12.26 YJ_YW0000如果YEXM00不为空的话JY_YW0000中的病人姓名为YEXM00 -- lintj 2012.01.19 JY_YWMX00重复数据BUG处理 LIS-20120119-001 -- liuj 2012.03.20 部分冲销后再进行对剩下部分冲销时报错，因为jy_yw0000.jgzt00在第一次冲销时已置为7导致 for BQ-20120314-002 -- LIUJ 2012.04.20 当yj_yw0000的开单科室，病区变化时JY_YW0000的SQBQ00，SQKS00也跟着变化 for BQ-20120330-001 --chenyw 2012.09.27 修改sp_jy_jytxm 的传入参数 by LIS-20120927-001 --chenyw 2012.11.09 糖耐量,尿三杯等项目需要生成多个条形码需求处理 by LIS-20121013-001 --chenyw 2013.01.06 增加自打印和预定义条码来判断哪些需要自动生成条形码 by LIS-20121220-001 -- jinfl 2013.04.18 根据参数定义项目,打印与项目开单数量相同的条形码数 by LIS-20130408-002 --chenyw 2013.05.10 增加参数来控制是否自动生成条形码 for LIS-20130422-001 -- jinfl 2013.06.27 参数JY_TXMSLZLXMID=Y时，全部检验项目按照申请数量拆分条形码 归档LIS-20130613-003 -- chenyw 2014.09.26 参数控制哪些病人类型是走web服务 -- chenyw 2015.06.30 增加婴儿姓名 for LIS-20150617-001 -- yushenjun 2015.02.25 增加yj_yw0000.JCHRBZ判断门诊医生开互认检验项目，检验条形码不能绑于它 for LIS-20160204-001 -- nixj 2016.06.02 通过参数JY_QYWEBFWBRLX判断是否采用平台方式生成检验单 -- yushenjun 2016.09.27 修改YJLRBZ为T代表体检病人 for LIS-20160914-003 -- Nixj 2018.05.16 归档莲花修改内容 for LIS9-20180516-001 -- Nixj 2018.12.12 厦门莲花分院模式改造 for LIS9-20181212-002 -- chenhn 2019.07.24 老年医院体检申请单不走此触发器 for LIS9-20190724-001 -- wusd 2019.11.06 修复BQ_JSXX00触发后又去查BQ_JSXX00导致抛错的异常 for LIS9-20191105-001 -- Nixj 2020.08.04 根据参数来判断该项目是否所有分院可见 for LIS9-20200708-001 -- wusd 2020.12.16 处理无参数JY_TXMSLZLXMID报错问题 for LIS9-20201215-001 -- Nixj 2021.04.14 沙县医院门转住及住转门归档 for LIS9-20210414-003 -- wusd 2021.04.14 增加JY_YDYSHKCXZLXM控制已打印、已审核可冲销诊疗项目 for LIS9-20210413-005 -- wusd 2021.04.19 增加其他状态控制已打印、已审核可冲销诊疗项目 for LIS9-20210419-002 -- Nixj 2021.06.08 特定项目已报告状态允许冲销，但能打印 for LIS9-20210525-001 -- Nixj 2021.09.28 修正bug for LIS9-20210928-001 -- wusd 2022.06.15 JY_YW0000增加SFJE81、ZJE000 for LIS9-20220607-001 -- Nixj 2022.06.27 归档沙县修改 LIS9-20220622-001 begin --老年体检是否走集成平台 begin select trim(Value0) into JY_TJSJCSSFZJCPT from XT_XTCS00 where name00 = 'JY_TJSJCSSFZJCPT'; exception when others then JY_TJSJCSSFZJCPT:='N'; end; if (JY_TJSJCSSFZJCPT='Y') and (:NEW.YJLRBZ='T') then return; end if; begin select trim(Value0) into JY_QYWEBFWBRLX from XT_XTCS00 where name00 = 'JY_QYWEBFWBRLX'; exception when others then JY_QYWEBFWBRLX:='N'; end; begin select ','||nvl(trim(VALUE0),'')||',' into V_YDYSHKCXZLXM from XT_XTCS00 where NAME00='JY_YDYSHKCXZLXM'; exception when others then V_YDYSHKCXZLXM:=',,'; 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.YJLRBZ='T') then return; end if; if (substr(:new.zyhghh,1,2)='TJ') and (:new.YJLRBZ='N') then return; end if; elsif (JY_QYWEBFWBRLX='Y') or (JY_QYWEBFWBRLX='P' ) or ((:new.YJLRBZ<>'T') and (JY_QYWEBFWBRLX='H' )) or ((:new.YJLRBZ='T') and (JY_QYWEBFWBRLX='T' )) or ((:new.MZZYBZ = '1') and (JY_QYWEBFWBRLX = '1')) or ((:new.MZZYBZ = '0') and (JY_QYWEBFWBRLX = '0')) then return ; end if; select count(1) into V_count0 from jy_yw0000 where yjdjh0=:new.cxdjh0; if (:new.CXDJH0>0) and (V_count0>0) then select count(1) into V_count0 from jy_yw0000 where yjdjh0=:new.cxdjh0 and jgzt00 in ('2','4','6','8','9'); if V_count0>0 then raise_application_error(-20002,'该检验标本已处理，请撤销采集在冲销！*'); else update jy_yw0000 set jgzt00='7' where yjdjh0=:new.cxdjh0; end if; end if; if not ((:new.MZZYBZ = '0') or ((:new.MZZYBZ = '1') and ((:new.SFDJH0 > 0) or (:new.YZCFID is not null)))) then RETURN; end if; if nvl(:new.YEXM00,'-1')<>'-1' then V_YEXM00:=:new.YEXM00; if (:new.MZZYBZ='1') and (nvl(:old.YEXM00,'-1')='-1') then select trim(max(YECSRQ)),decode(trim(max(YEXB00)),'男','M','女','F','') into V_YECSRQ,V_YEXB00 from BQ_JSXX00 where ZYID00=:new.ZYGHID and YEXM00=V_YEXM00; else V_YECSRQ:=''; V_YEXB00:=''; end if; else V_YEXM00:=''; V_YECSRQ:=''; V_YEXB00:=''; end if; begin select VALUE0 into JY_ZLXMQYKJ from XT_XTCS00 where name00='JY_ZLXMQYKJ'; exception when others then JY_ZLXMQYKJ:=''; end; insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'JY_YW0000',1,0,'d'||V_YEXM00||','||nvl(V_YECSRQ,:new.CSRQ00)||','||V_YEXB00,:new.YJDJH0); begin select YYID00 into VYYID00 from BM_BMBM00 where BMBH00=nvl(:new.YJKSBH,:old.yjksbh); exception when others then --verr:=to_char(:new.YJKSBH,'99999'); raise_application_error(-20021,','||:new.YJKSBH||',YYID00 is not exist！*'); end; if (nvl(JY_ZLXMQYKJ,'0')<>'0') then if instr(','||JY_ZLXMQYKJ||',',','||:new.ZLXMID||',')>0 then VYYID00:='0'; end if; end if; if UPDATING and (:new.ZLXMID <> :old.ZLXMID) then delete from JY_YW0000 where YJDJH0=:old.YJDJH0; end if; if DELETING then if (:old.SL0000 < 0) and (:old.CXDJH0 > 0) then update JY_YW0000 set JGZT00='0' where YJDJH0=:old.CXDJH0 AND JGZT00 not in('6','8'); else select count(*) into V_count0 from JY_YW0000 where YJDJH0=:old.YJDJH0 and JGZT00 IN ('1') and ZLXMID<>99999999; if V_count0 > 0 then raise_application_error(-20002,'该检验标本已采集，请删除采集后再删除！*'); end if; select count(*) into V_count0 from JY_YW0000 where YJDJH0=:old.YJDJH0 and JGZT00 IN ('6','8','9'); if V_count0 > 0 then raise_application_error(-20002,'该检验单据已出报告，不能删除！*'); end if; delete from JY_YW0000 where YJDJH0=:old.YJDJH0; end if; RETURN; end if; begin select instr(','||trim(value0)||',',','||to_char(:new.ZLXMID)||',') into V_count0 from XT_XTCS00 where NAME00='JY_XGCXXM'; if V_count0 = 0 then select count(*) into V_count0 from BM_ZLZD00 where ZLXMID=:new.ZLXMID and LBBH00='4'; if V_count0 = 0 then RETURN; end if; end if; exception when NO_DATA_FOUND then V_count0 := 0; end; begin select trim(VALUE0) into V_TXMFS0 from XT_XTCS00 where NAME00='JY_TXMFS'; exception when others then V_TXMFS0 := '0'; end; begin select trim(VALUE0) into JY_TSSYNXZDY from XT_XTCS00 where NAME00='JY_TSSYNXZDY'; exception when others then JY_TSSYNXZDY := '1'; end; begin select trim(VALUE0) into V_ZHXMCF from XT_XTCS00 where NAME00='JY_ZHXMSFCF'; exception when others then V_ZHXMCF := 'N'; end; begin select trim(value0) into JY_JYCJSFCYZDYHYTM from xt_xtcs00 where name00='JY_JYCJSFCYZDYHYTM'; exception when others then JY_JYCJSFCYZDYHYTM:='N'; end; if :new.ZDID00 is null then begin if :new.MZZYBZ = '0' then select trim(ICD900) into V_ZDID00 from YS_BRZDXX where GHID00 = :new.ZYGHID and ZZDBZ0 = '3' and rownum=1; else select count(*) into V_count0 from BQ_BRZDXX where ZYID00 = :new.ZYGHID and ZDLB00='2' and rownum=1; if V_count0<>0 then select trim(ZDM000) into V_ZDID00 from BQ_BRZDXX where ZYID00 = :new.ZYGHID and ZDLB00='2' and rownum=1; else select trim(ZDM000) into V_ZDID00 from BQ_BRZDXX where ZYID00 = :new.ZYGHID and ZDLB00='3' and rownum=1; end if; end if; exception when NO_DATA_FOUND then V_ZDID00 := null; end; end if; /* select count(*) into V_count0 from XT_XTCS00 where NAME00='JY_CFDJXM' and instr(','||trim(VALUE0)||',',','||:new.ZLXMID||',')>0; if V_count0 > 0 then V_SL0000 := :new.SL0000; else V_SL0000 := 1; end if; */ begin select CFSL00 into V_count0 from JY_ZLCFSL where zlxmid=:new.ZLXMID; exception when others then V_count0:=0; end; if V_count0>0 then V_SL0000:=V_count0; else V_SL0000:=1; end if; --xtz jinfl归档 修改参数JY_TXMSLZLXMID=Y时，全部检验项目按照申请数量拆分条形码 --select trim(VALUE0) into JY_TXMSLZLXMID from XT_XTCS00 where NAME00='JY_TXMSLZLXMID'; begin select trim(VALUE0) into JY_TXMSLZLXMID from XT_XTCS00 where NAME00 = 'JY_TXMSLZLXMID'; exception when others then JY_TXMSLZLXMID:='N'; end; if JY_TXMSLZLXMID='Y' then V_SL0000 := :new.SL0000; else select count(*) into V_count0 from XT_XTCS00 where NAME00='JY_TXMSLZLXMID' and instr(','||trim(VALUE0)||',',','||:new.ZLXMID||',')>0; if V_count0 > 0 then V_SL0000 := :new.SL0000; end if; end if; V_insert := 0; select count(*) into V_count0 from BM_ZLZD00 where ZLXMID=:new.ZLXMID and LBBH00='4'and ZLXMJC LIKE '[急诊]%'; if V_count0 = 0 then V_YXBZ00 := :new.JZBZ00; else V_YXBZ00 := 'Y'; end if; if UPDATING then if (:new.XMZT00 <> '6') and (:old.XMZT00 = '6') then update JY_YW0000 set JGZT00 = '0' where YJDJH0=:old.YJDJH0; end if; if :new.YJLRBZ = 'T' then update JY_YW0000 set SFTJ00 = 'Y' where YJDJH0=:old.YJDJH0; end if; -- if :new.XMZT00 = '6' then -- select count(*) into V_count0 from JY_YW0000 where YJDJH0=:old.YJDJH0 and JGZT00 IN ('1') and ZLXMID<>99999999; -- if V_count0 > 0 then -- raise_application_error(-20002,'该检验标本已采集，请删除采集后再作废！*'); -- end if; -- end if; if :new.XMZT00 = '6' then select count(*) into V_count0 from JY_YW0000 where YJDJH0=:old.YJDJH0 and JGZT00 IN ('4','6','8','9') and ZLXMID<>99999999; if V_count0 > 0 then raise_application_error(-20002,'该检验标本检验科已处理，不能作废！*'); end if; end if; if :new.XMZT00 <> :old.XMZT00 then update JY_YW0000 set SFBZ00=:new.XMZT00 where YJDJH0 = :new.YJDJH0; end if; if :new.kdbq00<>:old.KDBQ00 then update JY_YW0000 set SQBQ00=:new.KDBQ00 where YJDJH0 = :new.YJDJH0; end if; if :new.KDKSBH<>:old.KDKSBH then update JY_YW0000 set SQKS00=:new.KDKSBH where YJDJH0 = :new.YJDJH0; end if; if (:new.XMZT00 = '6') and (:new.XMZT00 <> :old.XMZT00) and (instr(','||V_YDYSHKCXZLXM||',',:new.ZLXMID)<1) then update JY_YW0000 set JGZT00='7',TXM000=null where YJDJH0 = :new.YJDJH0; end if; if (:new.XMZT00 = '3') and (:old.XMZT00 <> '3') then select count(*) into V_count0 from XT_XTCS00 where NAME00='JY_TMFYZDZKS' and instr(','||trim(VALUE0)||',',','||:new.KDKSBH||',')>0; if V_count0 > 0 then begin select TMSFSF into V_DJH000 from JY_YW0000 where YJDJH0=:new.YJDJH0; exception when others then V_DJH000 := null; end; if V_DJH000 is not null then update SF_FYMX00 set ZXKS00=KDKS00,ZXYS00=KDYS00 where DJH000=V_DJH000; update SF_BRFY00 A set (CZYKS0,CZY000,CZYXM0)=(select KDKS00,KDYS00,ZWXM00 from SF_FYMX00 B,BM_YGBM00 C where B.DJH000=A.DJH000 and B.KDYS00=C.YGBH00 and rownum=1) where DJH000=V_DJH000; end if; end if; end if; if (:new.CXDJH0 = 0 or :new.CXDJH0 is null) then select count(*) into V_count0 from JY_YW0000 where YJDJH0=:new.YJDJH0 and rownum=1; if V_count0 > 0 then if (:new.ZLXMID <> :old.ZLXMID) or (:new.SL0000 <> :old.SL0000) then delete from JY_YW0000 where YJDJH0 = :old.YJDJH0; V_count0 := 0; end if; end if; if V_count0 = 0 then loop if (V_ZHXMCF = 'Y') and (V_count0 > 1) then V_ZLXMID := :new.ZLXMID; for row IN CUR_ZHXM00 loop insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'JY_YW0000',1,0,'a'||V_YEXM00||','||nvl(V_YECSRQ,:new.CSRQ00)||','||V_YEXB00,:new.YJDJH0); select SEQ_JY_YW0000_LSID00.NEXTVAL into V_JYYWID from DUAL; insert into JY_YW0000 (JYYWID,YJDJH0,YJFZH0,ZLXMID,ZYGHID,MZZYBZ,YXBZ00,LRY000, ZDID00,BBID00,SQRQ00,SQSJ00,SQKS00,SQBQ00,SQYS00,JYDLY0,JGZT00,SFBZ00,TDJTTM, BRXM00,YEXM00,XB0000,CSRQ00,CH0000,CJKSBH,YYID00,ZJE000,SFJE81) VALUES (V_JYYWID,:new.YJDJH0,nvl(:new.YJFZH0,0),row.ZLXMID,:new.ZYGHID,:new.MZZYBZ,V_YXBZ00, :new.LRY000,nvl(:new.ZDID00,V_ZDID00),:new.BBID00,:new.KDRQ00,:new.KDSJ00,:new.KDKSBH, :new.KDBQ00,:new.KDYS00,decode(:new.YZCFID,null,'0','1'),decode(:new.XMZT00,'6','7','0'), :new.XMZT00,row.TDJTTM,:new.BRXM00,V_YEXM00,nvl(V_YEXB00,decode(:new.XB0000,'男','M','女','F','W')),nvl(V_YECSRQ,:new.CSRQ00),:new.CH0000,:new.CJKSBH,VYYID00,:new.ZJE000, (select sum(nvl(C.TCJE00,D.SFJE00)*C.SFCS00) from XT_ZLSFGX C,BM_YYSFXM D where row.ZLXMID=C.ZLXMID and C.SFXMID=D.SFXMID)); insert into JY_YWMX00(JYMXID,JYYWID,ZLXMID,YYID00) select SEQ_JY_YWMX00_JYMXID.nextval,V_JYYWID,row.ZLXMID,VYYID00 from dual where not exists(select 1 from JY_YWMX00 where JYYWID=V_JYYWID and ZLXMID=row.ZLXMID); end loop; else insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'JY_YW0000',1,0,'b'||V_YEXM00||','||nvl(V_YECSRQ,:new.CSRQ00)||','||V_YEXB00,:new.YJDJH0); select SEQ_JY_YW0000_LSID00.NEXTVAL into V_JYYWID from DUAL; insert into JY_YW0000 (JYYWID,YJDJH0,YJFZH0,ZLXMID,ZYGHID,MZZYBZ,YXBZ00,LRY000, ZDID00,BBID00,SQRQ00,SQSJ00,SQKS00,SQBQ00,SQYS00,JYDLY0,JGZT00,SFBZ00,BRXM00,YEXM00,XB0000,CSRQ00,CH0000,CJKSBH,YYID00,ZJE000,SFJE81) VALUES (V_JYYWID,:new.YJDJH0,nvl(:new.YJFZH0,0),:new.ZLXMID,:new.ZYGHID,:new.MZZYBZ,V_YXBZ00, :new.LRY000,nvl(:new.ZDID00,V_ZDID00),:new.BBID00,:new.KDRQ00,:new.KDSJ00,:new.KDKSBH, :new.KDBQ00,:new.KDYS00,decode(:new.YZCFID,null,'0','1'),decode(:new.XMZT00,'6','7','0'),:new.XMZT00, :new.BRXM00,V_YEXM00,nvl(V_YEXB00,decode(:new.XB0000,'男','M','女','F','W')),nvl(V_YECSRQ,:new.CSRQ00),:new.CH0000,:new.CJKSBH,VYYID00,:new.ZJE000, (select sum(nvl(C.TCJE00,D.SFJE00)*C.SFCS00) from XT_ZLSFGX C,BM_YYSFXM D where :new.ZLXMID=C.ZLXMID and C.SFXMID=D.SFXMID)); select count(*) into V_count0 from YJ_YWMX00 where YJDJH0= :new.YJDJH0; if V_count0 = 0 then --YJ_YWMX00 为空 insert into JY_YWMX00(JYMXID,JYYWID,ZLXMID,YYID00) select SEQ_JY_YWMX00_JYMXID.nextval,V_JYYWID,:new.ZLXMID,VYYID00 from dual where not exists(select 1 from JY_YWMX00 where JYYWID=V_JYYWID and ZLXMID=:new.ZLXMID); else insert into JY_YWMX00(JYMXID, JYYWID, ZLXMID,YYID00) select SEQ_JY_YWMX00_JYMXID.nextval,V_JYYWID,ZLXMID,VYYID00 from (select distinct ZLXMID from YJ_YWMX00 where YJDJH0= :new.YJDJH0) a where not exists(select 1 from JY_YWMX00 where JYYWID=V_JYYWID and ZLXMID=a.ZLXMID); end if; end if; V_insert := V_insert + 1; EXIT when V_insert = V_SL0000; end loop; if JY_JYCJSFCYZDYHYTM='Y' then SP_JY_JYTXM0(:new.BRID00,:new.MZZYBZ,'N','','0','N'); else if V_TXMFS0 = '1' then SP_JY_JYTXM0(:new.BRID00,:new.MZZYBZ,'N','','0','N'); elsif (V_TXMFS0 = '2') and ( JY_TSSYNXZDY = :new.MZZYBZ) then SP_JY_JYTXM0(:new.BRID00,:new.MZZYBZ,'N','','0','N'); end if; end if; end if; if (:new.cxsl00>0 and :new.sl0000=:new.cxsl00) and (:new.HBZYXM<>'1') and (instr(V_YDYSHKCXZLXM,:new.ZLXMID)<1) then --主项数量全冲完才置jy_yw0000状态7 update JY_YW0000 set JGZT00='7',TXM000=null where YJDJH0=:new.YJDJH0; end if; end if; else ---------------------------------------------------------- insert into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) Values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'JY_YW0000',1,0,'c'||V_YEXM00||','||nvl(V_YECSRQ,:new.CSRQ00)||','||V_YEXB00,:new.YJDJH0); if (((:new.CXDJH0 = 0 or :new.CXDJH0 is null)) or (:new.YZCFID is not null and :new.SL0000>0)) then select count(*) into V_count0 from JY_YW0000 where YJDJH0=:new.YJDJH0 and rownum=1; if V_count0 = 0 then loop select count(*) into V_count0 from XT_ZHMX00 where ZHXMID=:new.ZLXMID; if (V_ZHXMCF = 'Y') and (V_count0 > 1) then V_ZLXMID := :new.ZLXMID; for row IN CUR_ZHXM00 loop select SEQ_JY_YW0000_LSID00.NEXTVAL into V_JYYWID from DUAL; insert into JY_YW0000 (JYYWID,YJDJH0,YJFZH0,ZLXMID,ZYGHID,MZZYBZ,YXBZ00,LRY000, ZDID00,BBID00,SQRQ00,SQSJ00,SQKS00,SQBQ00,SQYS00,JYDLY0,JGZT00,SFBZ00,TDJTTM, BRXM00,YEXM00,XB0000,CSRQ00,CH0000,CJKSBH,YYID00,ZJE000,SFJE81) VALUES (V_JYYWID,:new.YJDJH0,nvl(:new.YJFZH0,0),row.ZLXMID,:new.ZYGHID,:new.MZZYBZ,V_YXBZ00, :new.LRY000,nvl(:new.ZDID00,V_ZDID00),:new.BBID00,:new.KDRQ00,:new.KDSJ00,:new.KDKSBH, :new.KDBQ00,:new.KDYS00,decode(:new.YZCFID,null,'0','1'),decode(:new.XMZT00,'6','7','0'), :new.XMZT00,row.TDJTTM,:new.BRXM00,V_YEXM00,nvl(V_YEXB00,decode(:new.XB0000,'男','M','女','F','W')),nvl(V_YECSRQ,:new.CSRQ00),:new.CH0000,:new.CJKSBH,VYYID00,:new.ZJE000, (select sum(nvl(C.TCJE00,D.SFJE00)*C.SFCS00) from XT_ZLSFGX C,BM_YYSFXM D where row.ZLXMID=C.ZLXMID and C.SFXMID=D.SFXMID)); insert into JY_YWMX00(JYMXID,JYYWID,ZLXMID,YYID00) select SEQ_JY_YWMX00_JYMXID.nextval,V_JYYWID,row.ZLXMID,VYYID00 from dual where not exists(select 1 from JY_YWMX00 where JYYWID=V_JYYWID and ZLXMID=row.ZLXMID); end loop; else select SEQ_JY_YW0000_LSID00.NEXTVAL into V_JYYWID from DUAL; insert into JY_YW0000 (JYYWID,YJDJH0,YJFZH0,ZLXMID,ZYGHID,MZZYBZ,YXBZ00,LRY000, ZDID00,BBID00,SQRQ00,SQSJ00,SQKS00,SQBQ00,SQYS00,JYDLY0,JGZT00,SFBZ00,BRXM00,YEXM00,XB0000,CSRQ00,CH0000,CJKSBH,YYID00,ZJE000,SFJE81) VALUES (V_JYYWID,:new.YJDJH0,nvl(:new.YJFZH0,0),:new.ZLXMID,:new.ZYGHID,:new.MZZYBZ,V_YXBZ00, :new.LRY000,nvl(:new.ZDID00,V_ZDID00),:new.BBID00, :new.KDRQ00,:new.KDSJ00,:new.KDKSBH,:new.KDBQ00,:new.KDYS00,decode(:new.YZCFID,null,'0','1'), decode(:new.XMZT00,'6','7','0'),:new.XMZT00,:new.BRXM00,V_YEXM00,nvl(V_YEXB00,decode(:new.XB0000,'男','M','女','F','W')),nvl(V_YECSRQ,:new.CSRQ00),:new.CH0000,:new.CJKSBH,VYYID00,:new.ZJE000, (select sum(nvl(C.TCJE00,D.SFJE00)*C.SFCS00) from XT_ZLSFGX C,BM_YYSFXM D where :new.ZLXMID=C.ZLXMID and C.SFXMID=D.SFXMID)); select count(*) into V_count0 from YJ_YWMX00 where YJDJH0= :new.YJDJH0; if V_count0 = 0 then --YJ_YWMX00 为空 insert into JY_YWMX00(JYMXID,JYYWID,ZLXMID,YYID00) select SEQ_JY_YWMX00_JYMXID.nextval,V_JYYWID,:new.ZLXMID,VYYID00 from dual where not exists(select 1 from JY_YWMX00 where JYYWID=V_JYYWID and ZLXMID=:new.ZLXMID); else insert into JY_YWMX00(JYMXID,JYYWID,ZLXMID,YYID00) select SEQ_JY_YWMX00_JYMXID.nextval, V_JYYWID, ZLXMID,VYYID00 from (select distinct ZLXMID from YJ_YWMX00 where YJDJH0= :new.YJDJH0) a where not exists(select 1 from JY_YWMX00 where JYYWID=V_JYYWID and ZLXMID=a.ZLXMID); end if; end if; V_insert := V_insert + 1; EXIT when V_insert = V_SL0000; end loop; if JY_JYCJSFCYZDYHYTM='Y' then SP_JY_JYTXM0(:new.BRID00,:new.MZZYBZ,'N','','0','N'); else if V_TXMFS0 = '1' then SP_JY_JYTXM0(:new.BRID00,:new.MZZYBZ,'N','','0','N'); elsif (V_TXMFS0 = '2') and ( JY_TSSYNXZDY = :new.MZZYBZ) then SP_JY_JYTXM0(:new.BRID00,:new.MZZYBZ,'N','','0','N'); end if; end if; end if; end if; begin select HBZYXM into VHBZYXM from JY_HBZYXM where YJDJH0=:new.CXDJH0; exception when others then VHBZYXM:='0'; end; if (:new.SL0000 < 0) and (:new.CXDJH0 > 0) and (nvl(:new.HBZYXM,'0')<>'1') and (VHBZYXM<>'2') and ((instr(V_YDYSHKCXZLXM,V_ZLXMID)<1) or (V_JGZT00<>'6' and V_JGZT00<>'8' and V_JGZT00<>'9'))then select nvl(max(trim(VALUE0)),'N') into V_JSBBSFYXCX from XT_XTCS00 where NAME00 = 'JY_JSBBSFYXCX'; select nvl(max(trim(VALUE0)),'Y') into V_JYBBPYZQSFYXCX from XT_XTCS00 where NAME00='JY_JYBBPYZQSFYXCX'; select JGZT00,MZZYBZ,ZLXMID into V_JGZT00,V_MZZYBZ,V_ZLXMID from JY_YW0000 where YJDJH0=:new.CXDJH0 and JGZT00<>'7' and rownum=1; if V_JSBBSFYXCX = 'N' then if ((V_JGZT00 = '9') or (V_JGZT00 = '8')) and (instr(','||V_YDYSHKCXZLXM||',',V_ZLXMID)<1) then raise_application_error(-20090,'该检验标本检验科已打印，不能冲销！*'); end if; if (V_JGZT00 = '6') and (instr(','||V_YDYSHKCXZLXM||',',V_ZLXMID)<1) then raise_application_error(-20090,'该检验标本检验科已审核，不能冲销！*'); end if; if ((V_JGZT00 = '4') or (V_JGZT00 = '5')) and (instr(','||V_YDYSHKCXZLXM||',',V_ZLXMID)<1) then raise_application_error(-20090,'该检验标本检验科已排样，不能冲销！*'); end if; if ((V_JGZT00 = '2') and V_JYBBPYZQSFYXCX='N') and (instr(','||V_YDYSHKCXZLXM||',',V_ZLXMID)<1) then raise_application_error(-20090,'该检验标本检验科已签收，不能冲销！*'); end if; if ((V_JGZT00 = '1') and (V_MZZYBZ = '1') and V_JYBBPYZQSFYXCX='N') and (instr(','||V_YDYSHKCXZLXM||',',V_ZLXMID)<1) then raise_application_error(-20090,'该检验标本检验科已采集，不能冲销！*'); end if; end if; select nvl(MAX(trim(VALUE0)),'Y') into V_SFCSDY from XT_XTCS00 where NAME00='JY_SFCXDYGDBG'; --2009.07.30 JXQ if (V_SFCSDY = 'N') and (instr(','||V_YDYSHKCXZLXM||',',V_ZLXMID)<1) then select count(*) into V_count0 from JY_YW0000 where YJDJH0=:new.CXDJH0 and CBGRQ0 is not null; if V_count0 > 0 then raise_application_error(-20090,'该检验标本报告打印过，不能冲销！*'); end if; end if; end if; end if; update JY_YW0000 set ZJE000=:new.ZJE000 where YJDJH0=:new.YJDJH0; exception when others then raise_application_error(-20020, nvl(sqlerrm, '原因不明出错！*')); end;