CREATE OR REPLACE TRIGGER tr_yj_yw0000_upd_pacs BEFORE INSERT OR DELETE OR UPDATE ON "SD_YJ"."YJ_YW0000" REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW declare ErrStr varchar2(200); ECUSTOM EXCEPTION; VCOUNTER NUMBER(5); VJCLBID bm_zlzd00.JCLBID%type; --20060427 VICKH00 ic_yhxx00.ICKH00%type;--定义为与此字段同类型，可变化 VJCLBMC varchar2(20); VKDKSBH bm_zlzd00.JKBH01%type; VYJKSBH bm_zlzd00.JKBH01%type; VBRDQKS YJ_YW0000.KDKSBH%type; VBRZJBH BM_BRXXB0.BRZJBH%type;--身份证号 VLBBH00 bm_zlzd00.Lbbh00%type; nRecordB INTEGER;--检查预约用20140820 nRecordC INTEGER;--检查预约用20140820 nRecordM INTEGER;--检查预约用20140820 cExam_SE VARCHAR2(8);--检查预约用20140820 VESBKS0 XT_XTCS00.VALUE0%type; cWardName VARCHAR2(30); --病区名称 2017.07.27 VYBSXJG YS_XMSQD0.YBSXJG%type; --疫病筛查 2020.03.26 Vflags varchar2(100); --疫病筛查编号 2020.03.26 V_TSBLXM XT_XTCS00.Value0%type; V_COUNT2 number; vZLXMID YJ_YW0000.ZLXMID%TYPE; vZDMC00 varchar2(100); -- MODIFICATION HISTORY -- Person Date Comments -- qks 2012.12.13 费别名称截取8位 -- qks 2012.12.26 部门编号改为通过函数sf_lis_dmzh获取 -- qks 2012.12.31 BRID00=nvl(WDWID0,lpad(BRID00,10,'0')); -- qks 2013.10.15 接口表EXAM_APPLY增加身份证号 -- qks 2014.02.21 修改函数spacs.check_idcard,支持15位身份证号 -- qks 2014.08.11 调用pacs_jjzt_change修改项目是否收费状态 -- qks 2015.04.22 病人家庭地址、电话按要求截取后再传给PACS; -- qks 2015.10.23 PACS产科部分项目exam_sub_class为空 -- qks 2015.12.23 BRID00=nvl(WDWID0,BRID00); -- qks 2016.01.19 由于集成平台上线,参数YJ_ESBFWDZKS控制部分科室收费不改状态; -- qks 2018.03.21 由于PACS检查预约平台上线,nvl(:NEW.JZQRR0,0)>0都要改状态; -- CP 2018.03.21 HIS退费时PACS删除对应申请单，同时退费的单据不传PACS -- qks 2018.04.09 作废直接删除 -- qks 2018.04.20 1、BM_YGBM00.YGBH00=4200(自助机)操作员执行后，自动更新PACS中间表项目的已收费; -- 2、CLIN_DIAG：如果诊断名称为空,改为取挂号诊断或住院诊断; --CP 2018.05.03 1、退费，作废等删除exam_apply表的操作增加拆单或剩余项目重新并单的功功能 --CP 2018.06.11 退费时更新exams表及exam_queue表 --CP 2018.07.03 项目作废调用拆单过程不成功。 --qks 2018.07.09 住院开单科室改为取病人当前科室。 --CP 2018.07.26 退费删除预约。 --CP 2018.07.27 exam_apply表添加病区代码，病区名称字段内容。 --CP 2018.11.16 作废和删除项目操作时也删除exam_item表对应记录和exam_schedule对应记录 --cp 2018.11.23 体检放射的骨密度和乳腺钼靶的项目设置检查子类 --CP 2018.12.07 取消退费申请时也删除了exam_item表 --qks 2018.12.14 改用联系人电话BRLXDH -- qks 2018.12.25 YJ_ESBFWDZKS改为YJ_ESBFWDZKS_APPYLZ;YJ_YW0000.ZXRXM0有值同时有启用不触发 -- qks 2019.01.07 --cp 2019-09-06 EDO根据检查项目设置检查子类 --cp 2019-09-16 EDO申请单取消，作废，退费时ＰＡＣＳ不同步(有门诊开单预约，住院后再开单扣费的流程) --cp 2019-09-18 09-16修改后出现一次多个医嘱时内镜项目会报该触发器违法唯一约束的错误 --qks 2020.03.26 如果电子病历申请单有记录异常值YS_XMSQD.YBSXJG有值的话，需要向Exam_apply表上的clin_symp传疫病筛查异常 flags传00000000000100000000；如果无值，放空; for GGJK-20200326-001 --qks 2020.04.01 门诊传疫病筛查异常判断改用YS_GZBDPC; --linshu 2020.09.14 增加了超声医学科中治疗类项目（BM_ZLZD00.LBBH00=5）也传送给Pacs by YJ9-20200910-001 --linshu 2021.01.18 在最新的基础上补上省人民病理项目都停止使用这个触发器 --linshu 2021.03.23 省人民诊断长度进行截取。B超类(US)截取至22个字符 YJ9-20210323-002 --dsm 2022.03.20 bqzy00变化写入exam_motive --huangjh 2023.07.31 bz0000||bqzy00写入exam_motive Begin --省人民病理项目都停止使用这个触发器 select NVL(max(VALUE0),'0') into V_TSBLXM from XT_XTCS00 where NAME00='YJ_BLXMTSGJCPT'; if V_TSBLXM = '1' then if deleting then vZLXMID := :old.zlxmid; else vZLXMID := :new.zlxmid; end if; select count(1) into V_COUNT2 from BM_ZLZD00 where LBBH00=3 and JCLBID in (6,7,9,12,5,4,1,3) and ZLXMID = vZLXMID; if V_COUNT2 > 0 then return; end if; end if; IF INSERTING then IF :NEW.ZLXMID>0 THEN ErrStr:='err1: 诊疗项目不存在ZLXMID='||to_char(:new.zlxmid); select LBBH00 into VLBBH00 FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID; if VLBBH00 = 5 and :new.YJKSBH = 2297 then SELECT nvl(JCLBID,0) INTO VJCLBID FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID; else SELECT JCLBID INTO VJCLBID FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID; end if; END IF; --检查类别处理 if (( VJCLBID >0 ) or ( VLBBH00 = 5 and :new.YJKSBH = 2297 )) and :new.SL0000>0 then ErrStr:='err2: 检查类别不存在JCLBID='||VJCLBID; if ( VLBBH00 = 5 and :new.YJKSBH = 2297 ) then VJCLBMC := 'US'; else select JCLBMC into VJCLBMC from bm_jclb00 where JCLBID=VJCLBID; end if; begin --select ickh00 into VICKH00 from ic_yhxx00 where brid00=:new.brid00 and ZT0000='1'; select replace(ickh00,'FZYB','') into VICKH00 from ic_yhxx00 where brid00=:new.brid00 and ZT0000='1'; EXCEPTION when others then VICKH00:=''; end; select max(BRZJBH) into Vbrzjbh from bm_brxxb0 where brid00=:new.brid00; select spacs.check_idcard(Vbrzjbh) into Vbrzjbh from dual; --主表 --2018.07.09 --select sf_pacs_dmzh(:new.KDKSBH,1) into VKDKSBH from dual; if :new.MZZYBZ='1' and nvl(:new.ZYGHID,0)>0 then select nvl(max(DQKS00),:new.KDKSBH) into VBRDQKS from zy_brxxb0 where zyid00=:new.ZYGHID; else VBRDQKS := :new.KDKSBH; end if; select sf_pacs_dmzh(VBRDQKS,1) into VKDKSBH from dual; select sf_pacs_dmzh(:new.YJKSBH,1) into VYJKSBH from dual; --检查预约用填充检查子类20140820 /*SELECT COUNT(*) INTO nRecordB FROM bm_zlzd00 WHERE zlxmid = :NEW.zlxmid AND :NEW.zlxmjc like '%B超%'; SELECT COUNT(*) INTO nRecordC FROM bm_zlzd00 WHERE zlxmid = :NEW.zlxmid AND :NEW.zlxmjc like '%彩超%'; if vjclbid = 2 and nRecordB = 1 then cExam_SE := '黑白超'; ELSif vjclbid = 2 and nRecordC = 1 then cExam_SE := '彩超'; ELSif vjclbid = 2 and (:NEW.zlxmid = '18509' or :NEW.zlxmid = '18492' or :NEW.zlxmid = '18493' or :NEW.zlxmid = '18478' or :NEW.zlxmid = '18504' or :NEW.zlxmid = '18515' or :NEW.zlxmid = '18503' or :NEW.zlxmid = '18501' or :NEW.zlxmid = '18483') then cExam_SE := '黑白超'; ELSif vjclbid = 2 and (:NEW.zlxmid = '18422' or :NEW.zlxmid = '18482' or :NEW.ZLXMID in (18418,18419,18423,18429,18430,18432,18453,30665,42339,42421)) then cExam_SE := '彩超'; ELSe cExam_SE := NULL; end if ;*/ if vjclbid = 2 then cExam_SE := '彩超';--超声现在只有彩超项目 end if; --2018-11-23 if vjclbid = 11 then if instr(:new.zlxmjc,'骨密度')>0 then cExam_SE := '骨密度';--体检放射子类 end if; if instr(:new.zlxmjc,'乳腺钼靶')>0 then cExam_SE := '乳腺钼靶';--体检放射子类 end if; end if; if vjclbid = 5 then --2019-09-06 if :new.zlxmid=18544 then cExam_SE := '胃镜'; end if; if :new.zlxmid=18546 then cExam_SE := '肠镜'; end if; if :new.zlxmid=70548 then cExam_SE := '胃镜'; end if; if :new.zlxmid=70549 then cExam_SE := '肠镜'; end if; if :new.zlxmid=23399 then cExam_SE := '胃镜'; end if; if :new.zlxmid=70743 then cExam_SE := '肠镜'; end if; end if; --2018-11-23 select max(dept_alias) into cWardName from spacs.dict_dept where dept_code=VKDKSBH and rownum=1; --2018.07.27 --插入前先做删除 --2019-09-18 delete spacs.exam_apply where apply_no=to_char(:new.yjdjh0); delete spacs.exam_apply_item where nvl(apply_id,apply_no)=to_char(:new.yjdjh0); --2018.12.14 qks substrb(nvl(BRDH00,BRLXDH),1,16) - > substrb(nvl(BRLXDH,BRDH00),1,16) --2020.03.26 qks 如果电子病历申请单有记录异常值YS_XMSQD.YBSXJG有值的话，需要向Exam_apply表上的clin_symp传疫病筛查异常 flags传00000000000100000000；如果无值，放空 --2020.04.01 qks 门诊传疫病筛查异常判断改用YS_GZBDPC; if :new.MZZYBZ='0' then select count(1) into VCOUNTER from YS_GZBDPC where GHID00=:new.ZYGHID and (wcyqs0='Y' or jcbqs0='Y' or jcyqs0='Y' or jcqzs0='Y' or spysbq='Y' or sqsfqz='Y' or nvl(lhggry,'N')='Y' or nvl(jwlyjc,'N')='Y' or nvl(BRZZ00,'无')<>'无'); if VCOUNTER>0 then --2020.04.13 增加CT项目的异常判断; if vjclbid = '1' then VYBSXJG := '疫病筛查异常'; Vflags := '00000000000100000000'; else VYBSXJG := ''; Vflags := ''; end if; else VYBSXJG := ''; Vflags := ''; end if; else select nvl(max(trim(YBSXJG)),'') into VYBSXJG from YS_XMSQD0 where GHID00=:new.ZYGHID and instrb(','||trim(YZMXID)||',',','||to_char(:new.YZCFID)||',')>0; if VYBSXJG is not null then Vflags := '00000000000100000000'; else Vflags := ''; end if; end if; if vJCLBID=2 then --省人民诊断长度进行截取。B超类(US)截取至11个字符,病理类截取至20个字符 YJ9-20210323-002 begin select substrb(decode(:NEW.ZDMC00,null,decode(:NEW.MZZYBZ,'0',SF_XT_GETMZZD(:NEW.ZYGHID),SF_XT_GETZYZD(:NEW.ZYGHID)),:NEW.ZDMC00),1,22) into vZDMC00 from dual; exception when others then vZDMC00 := '0'; end; else begin select substrb(decode(:NEW.ZDMC00,null,decode(:NEW.MZZYBZ,'0',SF_XT_GETMZZD(:NEW.ZYGHID),SF_XT_GETZYZD(:NEW.ZYGHID)),:NEW.ZDMC00),1,100) into vZDMC00 from dual; exception when others then vZDMC00 := '0'; end; end if; insert into spacs.EXAM_APPLY (APPLY_NO,SICK_ID,IC_CARD,NAME,SEX,BIRTH_DATE,CHARGE_TYPE, MAILING_ADDRESS,ZIP_CODE,PHONE_NUMBER,OUTPATIENT_NO,INPATIENT_NO, BED_NO,PATIENT_SOURCE,PRIORITY_ORDER,EXAM_CLASS,REQ_DEPT,REQ_PHYSICIAN, REQ_DATE,REQ_TIME,REQ_MEMO,CLIN_DIAG,PERFORM_DEPT,CHARGE_FLAG,visit_id,mi_card,IDENTITY_CARD,exam_sub_class, req_dept_name,perform_dept_name,age,req_hospital,hospital_code,costs,charges,req_ward_code,req_ward_name,clin_symp,flags,exam_motive,req_physician_code ,notice) select to_char(:new.yjdjh0),nvl(WDWID0,BRID00),trim(VICKH00),BRXM00,BRXB00,to_char(to_date(BRCSRQ,'yyyymmdd'),'yyyy-mm-dd'),substrb(FBMC00,1,8), substrb(nvl(BRJTDZ,TYDWMC),1,200),'',substrb(nvl(BRLXDH,BRDH00),1,16),decode(:new.MZZYBZ,'0',trim(:NEW.ZYHGHH),''),decode(:new.MZZYBZ,1,trim(:NEW.ZYHGHH),''), :new.CH0000,decode(:new.MZZYBZ,0,'门诊',1,'住院','外来'),decode(:new.JZBZ00,'Y','急诊','普通'),VJCLBMC,VKDKSBH,:new.KDYSXM, to_char(to_date(:new.kdrq00,'yyyymmdd'),'yyyy-mm-dd'),:new.kdsj00,:new.ZLXMJC, --substr(:NEW.ZDMC00,1,100), --substrb(decode(:NEW.ZDMC00,null,decode(:NEW.MZZYBZ,'0',SF_XT_GETMZZD(:NEW.ZYGHID),SF_XT_GETZYZD(:NEW.ZYGHID)),:NEW.ZDMC00),1,100), vZDMC00, VYJKSBH,decode(:NEW.SFDJH0,0,'0','1'),:NEW.zyghid,decode(trim(ybkh00),'0','',trim(ybkh00)),Vbrzjbh,cExam_SE --,(select BMMC00 from BM_BMBM00 where BMBH00=:new.KDKSBH) ,(select BMMC00 from BM_BMBM00 where BMBH00=VBRDQKS) --2018.07.09 ,(select BMMC00 from BM_BMBM00 where BMBH00=:new.YJKSBH) ,replace(substrb(SF_BQ_CSRQTONL(BRCSRQ),1,100),'小时','时') ,(select trim(YYJC00) from XT_YYXX00 where rownum=1) ,(select YYID00 from XT_YYXX00 where rownum=1) ,:new.zje000 ,:new.zje000 ,VKDKSBH ,cWardName,VYBSXJG,Vflags,nvl2(:new.bz0000,:new.bz0000||','||:new.bqzy00,:new.bqzy00),:new.KDYS00,'无' from VW_BM_BRXXB1 where brid00=:new.brid00; /* if :new.brid00=353205 then insert into spacs.EXAM_APPLY (APPLY_NO,SICK_ID,IC_CARD,NAME,SEX,BIRTH_DATE,CHARGE_TYPE, MAILING_ADDRESS,ZIP_CODE,PHONE_NUMBER,OUTPATIENT_NO,INPATIENT_NO, BED_NO,PATIENT_SOURCE,PRIORITY_ORDER,EXAM_CLASS,REQ_DEPT,REQ_PHYSICIAN, REQ_DATE,REQ_TIME,REQ_MEMO,CLIN_DIAG,PERFORM_DEPT,CHARGE_FLAG,visit_id,mi_card,IDENTITY_CARD,exam_sub_class, req_dept_name,perform_dept_name,age,req_hospital,hospital_code,costs,charges,req_ward_code,req_ward_name) select to_char(:new.yjdjh0),nvl(WDWID0,BRID00),trim(VICKH00),BRXM00,BRXB00,to_char(to_date(BRCSRQ,'yyyymmdd'),'yyyy-mm-dd'),substrb(FBMC00,1,8), substrb(nvl(BRJTDZ,TYDWMC),1,200),'',substrb(nvl(BRLXDH,BRDH00),1,16),decode(:new.MZZYBZ,'0',trim(:NEW.ZYHGHH),''),decode(:new.MZZYBZ,1,trim(:NEW.ZYHGHH),''), :new.CH0000,decode(:new.MZZYBZ,0,'门诊',1,'住院','外来'),decode(:new.JZBZ00,'Y','急诊','普通'),VJCLBMC,VKDKSBH,:new.KDYSXM, to_char(to_date(:new.kdrq00,'yyyymmdd'),'yyyy-mm-dd'),:new.kdsj00,:new.ZLXMJC, --substr(:NEW.ZDMC00,1,100), substrb(decode(:NEW.ZDMC00,null,decode(:NEW.MZZYBZ,'0',SF_XT_GETMZZD(:NEW.ZYGHID),SF_XT_GETZYZD(:NEW.ZYGHID)),:NEW.ZDMC00),1,100), VYJKSBH,decode(:NEW.SFDJH0,0,'0','1'),:NEW.zyghid,decode(trim(ybkh00),'0','',trim(ybkh00)),Vbrzjbh,cExam_SE --,(select BMMC00 from BM_BMBM00 where BMBH00=:new.KDKSBH) ,(select BMMC00 from BM_BMBM00 where BMBH00=VBRDQKS) --2018.07.09 ,(select BMMC00 from BM_BMBM00 where BMBH00=:new.YJKSBH) ,substrb(SF_BQ_CSRQTONL(BRCSRQ),1,100) ,(select trim(YYJC00) from XT_YYXX00 where rownum=1) ,(select YYID00 from XT_YYXX00 where rownum=1) ,:new.zje000 ,:new.zje000 ,VKDKSBH ,cWardName from VW_BM_BRXXB1 where brid00=:new.brid00; end if ; */ end if; --edit by lingh 201001017 --update spacs.exam_apply_item set spacs.exam_apply_item.amount= :new.Sl0000 -- where spacs.exam_apply_item.apply_no=:new.yjdjh0; --update spacs.exam_apply_item set cost=(COST*amount) -- where spacs.exam_apply_item.apply_no=:new.yjdjh0; -- update spacs.exam_apply_item set charge=(charge*amount) -- where spacs.exam_apply_item.apply_no=:new.yjdjh0; --201001017 ELSIF UPDATING THEN --更改 select max(BRZJBH) into Vbrzjbh from bm_brxxb0 where brid00=:new.brid00; select spacs.check_idcard(Vbrzjbh) into Vbrzjbh from dual; --end IF :NEW.ZLXMID>0 THEN ErrStr:='err1: 诊疗项目不存在ZLXMID='||to_char(:new.zlxmid); select LBBH00 into VLBBH00 FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID; if VLBBH00 = 5 and :new.YJKSBH = 2297 then --超声医学科治疗项目 SELECT nvl(JCLBID,0) INTO VJCLBID FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID; else SELECT JCLBID INTO VJCLBID FROM BM_ZLZD00 WHERE ZLXMID=:NEW.ZLXMID; end if; END IF; VESBKS0 := 'N'; if (VJCLBID is not null) then select nvl(max(trim(VALUE0)),'N') into VESBKS0 from xt_xtcs00 where name00='YJ_ESBFWDZKS_APPYLZ' and SYXT00 like '%7%'; if VESBKS0<>'N' then VESBKS0 := nvl(substr(VESBKS0,instrb(VESBKS0,'|')+1,length(VESBKS0)),'N'); end if; end if; --检查项目变动时 if ((VJCLBID is not null) or ( VLBBH00 = 5 and :new.YJKSBH = 2297 )) and (:NEW.ZLXMID<>:OLD.ZLXMID) and :new.SL0000>0 then ErrStr:='err2: 检查类别不存在JCLBID='||VJCLBID; if VLBBH00 = 5 and :new.YJKSBH = 2297 then --超声医学科治疗项目 VJCLBMC := 'US'; else select JCLBMC into VJCLBMC from bm_jclb00 where JCLBID=VJCLBID; end if; begin --select ickh00 into VICKH00 from ic_yhxx00 where brid00=:new.brid00 and ZT0000='1'; select replace(ickh00,'FZYB','') into VICKH00 from ic_yhxx00 where brid00=:new.brid00 and ZT0000='1'; EXCEPTION when others then VICKH00:=''; end; select max(BRZJBH) into Vbrzjbh from bm_brxxb0 where brid00=:new.brid00; select spacs.check_idcard(Vbrzjbh) into Vbrzjbh from dual; delete from spacs.EXAM_APPLY where APPLY_NO=to_char(:old.yjdjh0); --2018.07.09 --select sf_pacs_dmzh(:new.KDKSBH,1) into VKDKSBH from dual; if :new.MZZYBZ='1' and nvl(:new.ZYGHID,0)>0 then select nvl(max(DQKS00),:new.KDKSBH) into VBRDQKS from zy_brxxb0 where zyid00=:new.ZYGHID; else VBRDQKS := :new.KDKSBH; end if; select sf_pacs_dmzh(VBRDQKS,1) into VKDKSBH from dual; select sf_pacs_dmzh(:new.YJKSBH,1) into VYJKSBH from dual; --检查预约用填充检查子类20140820 /*SELECT COUNT(*) INTO nRecordB FROM bm_zlzd00 WHERE zlxmid = :NEW.zlxmid AND :NEW.zlxmjc like '%B超%'; SELECT COUNT(*) INTO nRecordC FROM bm_zlzd00 WHERE zlxmid = :NEW.zlxmid AND :NEW.zlxmjc like '%彩超%'; if vjclbid = 2 and nRecordB = 1 then cExam_SE := '黑白超'; ELSif vjclbid = 2 and nRecordC = 1 then cExam_SE := '彩超'; ELSif vjclbid = 2 and (:NEW.zlxmid = '18509' or :NEW.zlxmid = '18492' or :NEW.zlxmid = '18493' or :NEW.zlxmid = '18478' or :NEW.zlxmid = '18504' or :NEW.zlxmid = '18515' or :NEW.zlxmid = '18503' or :NEW.zlxmid = '18501' or :NEW.zlxmid = '18483') then cExam_SE := '黑白超'; ELSif vjclbid = 2 and (:NEW.zlxmid = '18422' or :NEW.zlxmid = '18482' or :NEW.ZLXMID in (18418,18419,18423,18429,18430,18432,18453,30665,42339,42421) ) then cExam_SE := '彩超'; ELSe cExam_SE := NULL; end if ;*/ if vjclbid=2 then cExam_SE := '彩超';--超声现在只有彩超项目 end if; --2018-11-23 if vjclbid = 11 then if instr(:new.zlxmjc,'骨密度')>0 then cExam_SE := '骨密度';--体检放射子类 end if; if instr(:new.zlxmjc,'乳腺钼靶')>0 then cExam_SE := '乳腺钼靶';--体检放射子类 end if; end if; --2018-11-23 if vjclbid = 5 then --2019-09-06 if :new.zlxmid=18544 then cExam_SE := '胃镜'; end if; if :new.zlxmid=18546 then cExam_SE := '肠镜'; end if; end if; select max(dept_alias) into cWardName from spacs.dict_dept where dept_code=VKDKSBH and rownum=1; --2018.07.27 --插入前先做删除 --2019-09-18 delete spacs.exam_apply where apply_no=to_char(:new.yjdjh0); delete spacs.exam_apply_item where nvl(apply_id,apply_no)=to_char(:new.yjdjh0); --主表 --2018.12.14 qks substrb(nvl(BRDH00,BRLXDH),1,16) - > substrb(nvl(BRLXDH,BRDH00),1,16) --2020.03.26 qks 如果电子病历申请单有记录异常值YS_XMSQD.YBSXJG有值的话，需要向Exam_apply表上的clin_symp传疫病筛查异常 flags传00000000000100000000；如果无值，放空 --2020.04.01 qks 门诊传疫病筛查异常判断改用YS_GZBDPC; if :new.MZZYBZ='0' then select count(1) into VCOUNTER from YS_GZBDPC where GHID00=:new.ZYGHID and (wcyqs0='Y' or jcbqs0='Y' or jcyqs0='Y' or jcqzs0='Y' or spysbq='Y' or sqsfqz='Y' or nvl(lhggry,'N')='Y' or nvl(jwlyjc,'N')='Y' or nvl(BRZZ00,'无')<>'无'); if VCOUNTER>0 then --2020.04.13 增加CT平扫项目的判断; if vjclbid = '1' then VYBSXJG := '疫病筛查异常'; Vflags := '00000000000100000000'; else VYBSXJG := ''; Vflags := ''; end if; else VYBSXJG := ''; Vflags := ''; end if; else select nvl(max(trim(YBSXJG)),'') into VYBSXJG from YS_XMSQD0 where GHID00=:new.ZYGHID and instrb(','||trim(YZMXID)||',',','||to_char(:new.YZCFID)||',')>0; if VYBSXJG is not null then Vflags := '00000000000100000000'; else Vflags := ''; end if; end if; if vJCLBID=2 then --省人民诊断长度进行截取。B超类(US)截取至11个字符,病理类截取至20个字符 YJ9-20210323-002 begin select substrb(decode(:NEW.ZDMC00,null,decode(:NEW.MZZYBZ,'0',SF_XT_GETMZZD(:NEW.ZYGHID),SF_XT_GETZYZD(:NEW.ZYGHID)),:NEW.ZDMC00),1,22) into vZDMC00 from dual; exception when others then vZDMC00 := '0'; end; else begin select substrb(decode(:NEW.ZDMC00,null,decode(:NEW.MZZYBZ,'0',SF_XT_GETMZZD(:NEW.ZYGHID),SF_XT_GETZYZD(:NEW.ZYGHID)),:NEW.ZDMC00),1,100) into vZDMC00 from dual; exception when others then vZDMC00 := '0'; end; end if; insert into spacs.EXAM_APPLY (APPLY_NO,SICK_ID,IC_CARD,NAME,SEX,BIRTH_DATE,CHARGE_TYPE, MAILING_ADDRESS,ZIP_CODE,PHONE_NUMBER,OUTPATIENT_NO,INPATIENT_NO, BED_NO,PATIENT_SOURCE,PRIORITY_ORDER,EXAM_CLASS,REQ_DEPT,REQ_PHYSICIAN, REQ_DATE,REQ_TIME,REQ_MEMO,CLIN_DIAG,PERFORM_DEPT,CHARGE_FLAG,visit_id,mi_card,IDENTITY_CARD,exam_sub_class, req_dept_name,perform_dept_name,age,req_hospital,hospital_code,costs,charges,req_ward_code,req_ward_name,clin_symp,flags,exam_motive ,notice) select to_char(:new.yjdjh0),nvl(WDWID0,BRID00),trim(VICKH00),BRXM00,BRXB00,to_char(to_date(BRCSRQ,'yyyymmdd'),'yyyy-mm-dd'),substrb(FBMC00,1,8), substrb(nvl(BRJTDZ,TYDWMC),1,200),'',substrb(nvl(BRLXDH,BRDH00),1,16),decode(:new.MZZYBZ,'0',trim(:NEW.ZYHGHH),''),decode(:new.MZZYBZ,1,trim(:NEW.ZYHGHH),''), :new.CH0000,decode(:new.MZZYBZ,0,'门诊',1,'住院','外来'),decode(:new.JZBZ00,'Y','急诊','普通'),VJCLBMC,VKDKSBH,:new.KDYSXM, to_char(to_date(:new.kdrq00,'yyyymmdd'),'yyyy-mm-dd'),:new.kdsj00,:new.ZLXMJC, --substr(:NEW.ZDMC00,1,100), vZDMC00, VYJKSBH,decode(:NEW.SFDJH0,0,'0','1'),:NEW.zyghid,decode(trim(ybkh00),'0','',trim(ybkh00)),Vbrzjbh,cExam_SE --,(select BMMC00 from BM_BMBM00 where BMBH00=:new.KDKSBH) --2018.07.09 ,(select BMMC00 from BM_BMBM00 where BMBH00=VBRDQKS) ,(select BMMC00 from BM_BMBM00 where BMBH00=:new.YJKSBH) ,replace(substrb(SF_BQ_CSRQTONL(BRCSRQ),1,100),'小时','时') ,(select trim(YYJC00) from XT_YYXX00 where rownum=1) ,(select YYID00 from XT_YYXX00 where rownum=1) ,:new.zje000 ,:new.zje000 ,VKDKSBH ,cWardName,VYBSXJG,Vflags,nvl2(:new.bz0000,:new.bz0000||','||:new.bqzy00,:new.bqzy00),'无' from VW_BM_BRXXB1 where brid00=:new.brid00; --edit by lingh 201001017 --update spacs.exam_apply_item set spacs.exam_apply_item.amount= :new.Sl0000 -- where spacs.exam_apply_item.apply_no=:new.yjdjh0; --update spacs.exam_apply_item set cost=(COST*amount) -- where spacs.exam_apply_item.apply_no=:new.yjdjh0; -- update spacs.exam_apply_item set charge=(charge*amount) -- where spacs.exam_apply_item.apply_no=:new.yjdjh0; --201001017 end if; if (VJCLBID is not null) and ((:new.kdksbh<>:OLD.kdksbh) OR (:new.KDYSXM<>:OLD.KDYSXM) OR (:new.YJKSBH<>:OLD.YJKSBH) OR (nvl(:new.BQZY00,' ')<>nvl(:OLD.BQZY00,' ')) or (nvl(:new.BZ0000,' ')<>nvl(:OLD.BZ0000,' '))) and instrb(','||VESBKS0||',',','||:new.YJKSBH||',')<=0 and VESBKS0<>'Y' and :new.SL0000>0 then --2018.07.09 --select sf_pacs_dmzh(:new.KDKSBH,1) into VKDKSBH from dual; if :new.MZZYBZ='1' and nvl(:new.ZYGHID,0)>0 then select nvl(max(DQKS00),:new.KDKSBH) into VBRDQKS from zy_brxxb0 where zyid00=:new.ZYGHID; else VBRDQKS := :new.KDKSBH; end if; select sf_pacs_dmzh(VBRDQKS,1) into VKDKSBH from dual; select sf_pacs_dmzh(:new.YJKSBH,1) into VYJKSBH from dual; update spacs.EXAM_APPLY set REQ_DEPT=VKDKSBH, REQ_PHYSICIAN=:new.KDYSXM, PERFORM_DEPT=VYJKSBH, IDENTITY_CARD=VBRZJBH, exam_motive=nvl2(:new.bz0000,:new.bz0000||','||:new.bqzy00,:new.bqzy00) where APPLY_NO=to_char(:old.yjdjh0); end if; --2018.07.26 if :new.SFDJH0<>:old.SFDJH0 and VJCLBID is not null and :new.SL0000>0 and :new.SFDJH0>0 --and not ((VESBKS0='Y' or instrb(','||VESBKS0||',',','||:new.YJKSBH||',')>0) and :new.ZXRXM0 is not null) --2018.12.25 qks and ( instrb(','||VESBKS0||',',','||:new.YJKSBH||',')<=0 and VESBKS0<>'Y' or nvl(:NEW.JZQRR0,0)>0 or nvl(:new.ZXR000,0)=4200) --2019.01.07 qks then update spacs.EXAM_APPLY_ITEM set CHARGE_FLAG='1' where APPLY_ID=to_char(:new.yjdjh0); --2018.12.25 qks 从 检查类别项目修改收费标志 移过来。 update spacs.EXAM_APPLY set CHARGE_FLAG='1',costs=:NEW.ZJE000,CHARGES=:NEW.ZJE000,IDENTITY_CARD=VBRZJBH where APPLY_NO=to_char(:new.yjdjh0); --update spacs.EXAM_APPLY_ITEM set CHARGE_FLAG='1' where APPLY_ID=to_char(:new.yjdjh0); --2018.07.26 select count(1) into VCOUNTER from spacs.exam_item where apply_no= to_char(:new.yjdjh0) and nvl(CHARGE_FLAG,'0')='0'; if VCOUNTER>0 then spacs.pacs_jjzt_change(to_char(:new.yjdjh0),'1'); end if; end if; --检查类别项目修改收费标志 2018.12.25 qks 暂时注释掉 /* if (:new.SFDJH0<>:old.SFDJH0 and VJCLBID is not null and (instrb(','||VESBKS0||',',','||:new.YJKSBH||',')<=0 and VESBKS0<>'Y' or nvl(:NEW.JZQRR0,0)>0) and :new.SL0000>0) or (nvl(:old.SFDJH0,0)>0 and VJCLBID is not null and nvl(:new.ZXR000,0)=4200 and nvl(:new.ZXR000,0)<>nvl(:OLD.ZXR000,0)) then update spacs.EXAM_APPLY set CHARGE_FLAG='1',costs=:NEW.ZJE000,CHARGES=:NEW.ZJE000 where APPLY_NO=to_char(:new.yjdjh0); --update spacs.EXAM_APPLY_ITEM set CHARGE_FLAG='1' where APPLY_ID=to_char(:new.yjdjh0); --2018.07.26 select count(1) into VCOUNTER from spacs.exam_item where apply_no= to_char(:new.yjdjh0) and nvl(CHARGE_FLAG,'0')='0'; if VCOUNTER>0 then spacs.pacs_jjzt_change(to_char(:new.yjdjh0),'1'); end if; end if; */ --退费单据退费成功 2018.03.22 --if :new.SFDJH0<>:old.SFDJH0 and VJCLBID is not null and :new.SL0000<0 and nvl(:new.cxdjh0,0)>0 then 2019-09-16 if :new.SFDJH0<>:old.SFDJH0 and VJCLBID is not null and :new.SL0000<0 and nvl(:new.cxdjh0,0)>0 and VJCLBID<>5 then --2019-09-16 --退费成功时删除该退费单对应的申请单 select JCLBMC into VJCLBMC from bm_jclb00 where JCLBID=VJCLBID; Spacs.proc_unmergeapplication(VJCLBMC,:new.cxdjh0,'','0');--2018-05-03 这个过程已含删除exam_apply和exam_apply_item表功能 delete spacs.exam_apply_item where apply_id=to_char(:new.cxdjh0);--2018-05-03 delete spacs.exam_apply where apply_no=to_char(:new.cxdjh0);--2018-05-03 delete spacs.exam_queue where exam_no in(select exam_no from spacs.v_exams where apply_no=to_char(:new.cxdjh0) and exam_status<='50'); --2018-06-11 update spacs.v_exams set exam_status='00' where exam_no in(select exam_no from spacs.v_exams where apply_no=to_char(:new.cxdjh0) and exam_status<='50'); --2018-06-11 insert into SPACS.EXAM_SCHEDULE_ACT (ACT_NO,APPLY_NO,ACT_TYPE,TERMINAL_TYPE,ACT_DATE,ACT_TIME,ACT_DESCRIPTION) values ('C'||SEQ_CANCELSCHEDULE_ACT_NO.nextval,to_char(:new.yjdjh0),'4','G',to_char(sysdate,'YYYY-MM-DD'),TO_CHAR(SYSDATE,'HH24:MI:SS'),'医嘱退费，检查预约记录作废。');--2018-11-16 --delete from spacs.exam_schedule where APPLY_NO=to_char(:new.cxdjh0); --2018-07-26 end if; --作废时,删除接口表记录 --if :new.xmzt00='6' and VJCLBID is not null then --2019-09-16 if :new.xmzt00='6' and VJCLBID is not null AND VJCLBID<>5 then --2019-09-16 --and instrb(','||VESBKS0||',',','||:new.YJKSBH||',')<=0 and VESBKS0<>'Y' then --2018.04.09 select JCLBMC into VJCLBMC from bm_jclb00 where JCLBID=VJCLBID; --2018.07.03 Spacs.proc_unmergeapplication(VJCLBMC,:new.yjdjh0,'','0');--2018-05-03 这个过程已含删除exam_apply和exam_apply_item表功能 insert into SPACS.EXAM_SCHEDULE_ACT (ACT_NO,APPLY_NO,ACT_TYPE,TERMINAL_TYPE,ACT_DATE,ACT_TIME,ACT_DESCRIPTION) values ('C'||SEQ_CANCELSCHEDULE_ACT_NO.nextval,to_char(:old.yjdjh0),'4','G',to_char(sysdate,'YYYY-MM-DD'),TO_CHAR(SYSDATE,'HH24:MI:SS'),'医嘱作废，检查预约记录作废。');--2018-11-16 delete from spacs.EXAM_APPLY_ITEM where APPLY_ID=to_char(:old.yjdjh0); --2018-05-03 delete from spacs.EXAM_APPLY where APPLY_NO=to_char(:old.yjdjh0); --2018-05-03 --delete from spacs.exam_schedule where APPLY_NO=to_char(:old.yjdjh0); --2018-11-16 --delete from spacs.exam_item where apply_no=to_char(:old.yjdjh0); --2018-11-16 update spacs.v_exams set exam_status='00' where exam_no in(select exam_no from spacs.v_exams where apply_no=to_char(:old.yjdjh0) and exam_status<='50'); --2019-03-19 end if; ELSIF DELETING THEN IF :old.ZLXMID>0 THEN ErrStr:='err1a: 诊疗项目不存在ZLXMID='||to_char(:old.zlxmid); SELECT JCLBID INTO VJCLBID FROM BM_ZLZD00 WHERE ZLXMID=:old.ZLXMID; END IF; --IF VJCLBID is not null then --2019-09-16 IF VJCLBID is not null and VJCLBID<>5 then select JCLBMC into VJCLBMC from bm_jclb00 where JCLBID=VJCLBID; --2018-05-03 insert into SPACS.EXAM_SCHEDULE_ACT (ACT_NO,APPLY_NO,ACT_TYPE,TERMINAL_TYPE,ACT_DATE,ACT_TIME,ACT_DESCRIPTION) values ('C'||SEQ_CANCELSCHEDULE_ACT_NO.nextval,to_char(:old.yjdjh0),'4','G',to_char(sysdate,'YYYY-MM-DD'),TO_CHAR(SYSDATE,'HH24:MI:SS'),'医嘱被删除，检查预约记录作废。'); Spacs.proc_unmergeapplication(VJCLBMC,:old.yjdjh0,'','0');--2018-05-03 这个过程已含删除exam_apply和exam_apply_item表功能 delete from spacs.EXAM_APPLY where APPLY_NO=to_char(:old.yjdjh0); --2018-05-03 --delete from spacs.exam_schedule where APPLY_NO=to_char(:old.yjdjh0); --delete from spacs.exam_schedule where APPLY_NO=to_char(:old.cxdjh0); --2018-11-16 /*if :new.SL0000>0 then --2018-12-07 delete from spacs.exam_item where apply_no=to_char(:old.cxdjh0); --2018-11-16 end if; */ --2018-12-07 END IF; END IF; --begin 将电子申请单ID写入PACS明细表 zhangjie 20210112 if VJCLBID is not null and nvl(:NEW.sqid00,0)<>nvl(:old.sqid00,0) and UPDATING then update spacs.EXAM_APPLY_ITEM set ORDER_NO=to_char(:new.sqid00) where APPLY_NO=to_char(:new.yjdjh0); end if ; --end EXCEPTION WHEN ECUSTOM THEN RAISE_APPLICATION_ERROR(-20030,ERRSTR||'！*'); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010,'@数据没有找到,'||errStr||'！*'); End;