create or replace trigger TR_SF_YSGHHB_BEFINSAPDEL before insert or delete or update OF FZYYS0, YYXHS0, DHYYS0, GHXHS0, YYXHMX, DHYYMX, FZYYMX, WLYYS0, PJZZSJ, HZXHJS, TZBZ00, GHQSSJ,GHJSSJ, TZBZ01, WLYYMX,YNBLMX ON "SD_SF"."SF_YSGHHB" referencing new as new old as old for each row when (nvl(NEW.WBHBID,0) = 0) declare ErrStr varchar2(200); Cs_Ys_Xzygh0 CHAR(1); E_Ghxhs0 EXCEPTION; E_Ghxhs1 EXCEPTION; E_Yyxhs0 EXCEPTION; V_Ghxhs0 NUMBER(6); V_Count0 NUMBER(5); V_Yyxhs0 NUMBER(6); Vyzdycd number(5); Vhzdcd0 number(5); Vhzdnum number(5); Vhzdrs0 number(5); i number(5); Vhzqssj date; Vhzjzsj date; Vmessage varchar2(20); V_HZQSXH NUMBER(5);--候诊起始序号 v_FZYYMX sf_yspbb0.FZYYMX%type; v_DHYYMX sf_yspbb0.DHYYMX%type; v_WLYYMX sf_YSPBB0.WLYYMX%type; v_YNBLMX sf_ysghhb.YNBLMX%type; --v_PJZZSJ sf_YSPBB0.PJZZSJ%type; v_PJZZSJ varchar2(5); ls_hzxh00 ys_hbhzxh.hzxh00%type; ls_hzqssj ys_hbhzxh.HZQSSJ%type; Vsfkzxmyy XT_XTCS00.VALUE0%type; --是否开展厦门统一的预约模式 插入数据模式使用数据链路 Vmzhs_pjzzsjsfylfhd XT_XTCS00.VALUE0%type; --排班表的平均诊治时间是否用来分号段(YS_HBHZXH.HZSJ00),N 否 Y 是 默认N ls_yyid00 xt_yyxx00.yyid00%type; ls_GHJSSJ YS_HBHZXH.HZQSSJ%type; LS_JYXTPT XT_XTCS00.VALUE0%type; --是否禁用本系统的挂号排班,Y禁用,N不禁用,默认为N v_HZSJTS char(1); --‘1’:写入YS_HBHZXH.HZQSSJ起始时间：早上08:00-11:30,下午：夏季 15:00-18:00,其他 14:30-17:30 v_HZXH00 YS_HBHZXH.HZXH00%type; -- MODIFICATION HISTORY -- Person Date Comments -- qks 2011.06.19 广东中医院:增加条件and nvl(:new.YYSL00,0)=0,修改BRID00=NULL, -- csf 2011.08.18 省二模式下YS_HSGHXZHZXH=Y,YS_HBHZXH只要没被约，那么将YYBZ00先还原成1，再进行修改。 -- csf 2012.01.11 仅仅HZXHJS变化时候Cs_Ys_Xzygh0='Y'的模式无需处理 MZHS-20120112-001 -- qks 2013.04.01 院内保留明细号:YS_HBHZXH.YYBZ00=3 -- zhangwz 2014.04.01 厦门预约模式在修改停诊状态的时候写入中间表数据 SF_YYZJB0 用于后续相关数据到前置机的功能。 -- zhangwz 2014.07.01 对于挂号号表修改时增加号数时取时间算法的入参进行修改 -- zhangwz 2014.07.04 增加参数 MZHS_PJZZSJSFYLFHD 控制是否用平均诊治时间来进行 YS_HBHZXH.HZSJ00 的定义 -- zhangwz 2014.07.21 自助机预约需要YS_HBHZXH.HZQSSJ 从5.0的脚本里面去copy -- liuj 2014.07.24 根据厦门口腔的需求增加删除ys_hbhzxh以达到停诊剩余号的效果 -- liuj 2015.03.02 修改时段内号数太多写入ys_hbhzxh.hzqssj超过零点而不在时段范围内导致找不到的问题。 -- qks 2016.01.15 省人民医院(YYID00=220006)上午、下午排班HZQSSJ最后一个时段都要求提前半个小时 by MZHS9-20160115-001 -- qks 2017.01.09 当MZHS_SFJYBXTPP参数起用时,屏蔽此触发器 for MZHS9-20170109-002 -- qks 2017.01.22 触发条件增加YNBLMX变更,同时对院内保留明细也处理首个字符也为逗号,中文标点逗号改为英文标点逗号。for MZHS9-20170122-001 -- qks 2018.06.26 修正院内保留号被改为现场号问题; for MZHS9-20180626-001 -- yanghq 2018.10.16 注释出现跳号语句 for MZHS9-20181016-001 -- qks 2018.11.27 当使用统一预约平台(即SF_YSGHHB.WBHBID<>0)时,直接退出; for MZHS9-20181205-001 -- dsm 2021.05.03 超过排班时间的hzqssj设置成null Begin --==============================================其他医院做法============================================== select nvl(max(substr(VALUE0,1,1)),'N') into LS_JYXTPT from XT_XTCS00 where NAME00='MZHS_SFJYBXTPP'; if LS_JYXTPT='Y' then return; end if; select upper(trim(nvl(max(VALUE0),'N'))) into Cs_Ys_Xzygh0 from XT_XTCS00 where NAME00='YS_HSGHXZHZXH'; select upper(trim(nvl(max(VALUE0),'N'))) into Vmzhs_pjzzsjsfylfhd from XT_XTCS00 where NAME00='MZHS_PJZZSJSFYLFHD'; select upper(trim(nvl(max(VALUE0),'0'))) into v_HZSJTS from XT_XTCS00 where NAME00='YY_HBHZQSSJ'; begin select SF_SF_GETYYID00() YYID00 into ls_yyid00 from dual; EXCEPTION WHEN OTHERS THEN ls_yyid00:='0'; END; if Cs_Ys_Xzygh0 in ('Y') then if ls_yyid00 = '220006' then ls_GHJSSJ:=to_char(to_date(:new.GHJSSJ,'hh24:mi:ss')-1/48,'hh24:mi'); else ls_GHJSSJ:=substr(:new.GHJSSJ,1,5); end if; if inserting then--增加号表记录 :NEW.MRXHS0 := :NEW.GHXHS0; V_HZQSXH:=:NEW.HZQSXH; V_Ghxhs0 := 1; Vyzdycd:=Floor((to_date(:new.ghmzsj||:new.GHJSSJ,'yyyymmddhh24:mi:ss')-to_date(:new.ghmzsj||:new.ghqssj,'yyyymmddhh24:mi:ss'))*24*60); --取平均诊治时间 v_PJZZSJ := nvl(trim(:NEW.PJZZSJ),to_char(round(Vyzdycd/GREATEST(:NEW.GHXHS0,1),2))); while V_Ghxhs0<=:NEW.GHXHS0 loop if Vmzhs_pjzzsjsfylfhd='Y' then Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),V_Ghxhs0,:new.PJZZSJ); else Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),V_Ghxhs0); end if; if ((V_Ghxhs0-1)*to_number(v_PJZZSJ))>=Vyzdycd then ls_hzqssj:=substr(:new.GHJSSJ,1,5); else ls_hzqssj:=to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi'); end if; Insert into YS_HBHZXH (ID0000, GHHBID, HZXH00,HZDXH0,HZSJ00,HZQSSJ,ZZGHBZ) Values (SQ_YS_HBHZXH_ID0000.nextval, :new.GHHBID, V_Ghxhs0+V_HZQSXH-1,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), least(ls_hzqssj,ls_GHJSSJ),'0'); V_Ghxhs0:= V_Ghxhs0+1; end loop; elsif Updating then--修改挂号限号数 V_HZQSXH := :NEW.HZQSXH; V_Ghxhs0 := :OLD.GHXHS0; Vyzdycd:=Floor((to_date(:new.ghmzsj||:new.GHJSSJ,'yyyymmddhh24:mi:ss')-to_date(:new.ghmzsj||:new.ghqssj,'yyyymmddhh24:mi:ss'))*24*60); --取平均诊治时间 v_PJZZSJ := nvl(trim(:NEW.PJZZSJ),to_char(round(Vyzdycd/GREATEST(:NEW.GHXHS0,1),2))); if V_Ghxhs0<:NEW.GHXHS0 then--号改多 V_Ghxhs0 := :OLD.GHXHS0+1; while V_Ghxhs0<=:NEW.GHXHS0 loop Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,:new.GHXHS0,V_Ghxhs0); if ((V_Ghxhs0-1)*to_number(v_PJZZSJ))>=Vyzdycd then ls_hzqssj:=substr(:new.GHJSSJ,1,5); else ls_hzqssj:=to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi'); end if; Insert into YS_HBHZXH (ID0000, GHHBID, HZXH00,HZDXH0,HZSJ00,HZQSSJ,ZZGHBZ) Values (SQ_YS_HBHZXH_ID0000.nextval, :new.GHHBID, V_Ghxhs0+V_HZQSXH-1,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), least(ls_hzqssj,ls_GHJSSJ) ,'0'); V_Ghxhs0:= V_Ghxhs0+1; end loop; elsif V_Ghxhs0>:NEW.GHXHS0 then--号改少 -- while (V_Ghxhs0+1)>=:NEW.GHXHS0 loop select count(1) into V_Count0 from YS_HBHZXH where GHHBID=:NEW.GHHBID and HZXH00>=V_HZQSXH+(:NEW.GHXHS0) and BRID00 is not null; if V_Count0>0 then raise E_Ghxhs0; end if; Delete from YS_HBHZXH where GHHBID=:NEW.GHHBID and HZXH00>=V_HZQSXH+(:NEW.GHXHS0) and BRID00 is null; --V_Ghxhs0:= V_Ghxhs0-1; -- end loop; end if; if :NEW.TZBZ01<>:OLD.TZBZ01 then if :new.tzbz01='1' then Delete from YS_HBHZXH where GHHBID=:OLD.GHHBID and brid00 is null; elsif :new.tzbz01='0' then V_Ghxhs0 := 1; while V_Ghxhs0<=:NEW.GHXHS0 loop select count(1) into V_Count0 from ys_hbhzxh where ghhbid=:old.ghhbid and hzxh00=V_Ghxhs0; if V_Count0=0 then Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,:new.GHXHS0,V_Ghxhs0); if ((V_Ghxhs0-1)*to_number(v_PJZZSJ))>=Vyzdycd then ls_hzqssj:=substr(:new.GHJSSJ,1,5); else ls_hzqssj:=to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi'); end if; Insert into YS_HBHZXH (ID0000, GHHBID, HZXH00,HZDXH0,HZSJ00,HZQSSJ,ZZGHBZ) Values (SQ_YS_HBHZXH_ID0000.nextval, :new.GHHBID, V_Ghxhs0+V_HZQSXH-1,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), least(ls_hzqssj,ls_GHJSSJ) ,'0'); end if; V_Ghxhs0:= V_Ghxhs0+1; end loop; end if; end if; if (Vmzhs_pjzzsjsfylfhd='Y') then begin V_Ghxhs0 := 1; while V_Ghxhs0<=:NEW.GHXHS0 loop Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,:new.GHXHS0,V_Ghxhs0,:new.PJZZSJ); update YS_HBHZXH set HZSJ00=substrb(Vmessage,instrb(Vmessage,',')+1,11) where GHHBID=:new.GHHBID and HZXH00=V_Ghxhs0 and BRID00 is null; V_Ghxhs0:= V_Ghxhs0+1; end loop; end; end if; --修改平均诊治时间 if nvl(:NEW.PJZZSJ,' ')<>nvl(:OLD.PJZZSJ,' ') then update YS_HBHZXH set HZQSSJ= to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+least(((HZXH00-nvl(V_HZQSXH,1))*to_number(v_PJZZSJ)/(24*60)),(Vyzdycd/(24*60))),'hh24:mi') where GHHBID=:NEW.GHHBID and BRID00 is null; end if; else--删除号表记录 Delete from YS_HBHZXH where GHHBID=:OLD.GHHBID; end if; end if; if updating and (:new.GHQSSJ<>:old.GHQSSJ or :new.GHJSSJ<>:old.GHJSSJ) then --update YS_HBHZXH a set HZQSSJ=(select substr(least(to_char(to_date(:new.GHMZSJ||:new.GHQSSJ,'YYYYMMDDHH24:MI:SS')+(NVL(a.HZXH00,0)-NVL(:new.HZQSXH,0))*NVL(:new.PJZZSJ,0)/24/60,'YYYYMMDDHH24:MI:SS'), -- to_char(to_date(:new.GHMZSJ||:new.GHJSSJ,'YYYYMMDDHH24:MI:SS'),'YYYYMMDDHH24:MI:SS') ) ,9,5) -- from SF_YSGHHB b where GHHBID=a.GHHBID) --where GHHBID=:new.GHHBID and BRXM00 is null ; update YS_HBHZXH set HZQSSJ= to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+least(((HZXH00-nvl(V_HZQSXH,1))*to_number(v_PJZZSJ)/(24*60)),(Vyzdycd/(24*60))),'hh24:mi') where GHHBID=:NEW.GHHBID and BRXM00 is null; end if; --省二等医院要求预约号数不能多于总号数的一半 if Cs_Ys_Xzygh0='Y' then -- if updating OR inserting then if (updating and (:new.GHXHS0<>:old.GHXHS0 or :new.YYXHS0<>:old.YYXHS0 or :new.DHYYS0<>:old.DHYYS0 or :new.FZYYS0<>:old.FZYYS0 or :new.WLYYS0<>:old.WLYYS0 or :new.YYXHMX<>:old.YYXHMX or :new.DHYYMX<>:old.DHYYMX or :new.FZYYMX<>:old.FZYYMX or :new.WLYYMX<>:old.WLYYMX) or nvl(:new.YNBLMX,' ')<>nvl(:old.YNBLMX,' ') ) OR inserting then V_Yyxhs0 := :NEW.YYXHS0; -- if (V_Yyxhs0*2)> :NEW.GHXHS0 then -- Raise E_Yyxhs0; -- end if; update YS_HBHZXH set YYBZ00='1' where GHHBID=:new.GHHBID and BRXM00 is null;-- and YYBZ00='3'; --2018.10.16注释防止跳号 --while V_Yyxhs0>0 loop -- Update YS_HBHZXH set YYBZ00='3' where GHHBID=:new.GHHBID and HZXH00=(:NEW.HZQSXH-1)+(V_Yyxhs0*2) and BRXM00 is null; -- V_Yyxhs0:= V_Yyxhs0-1; --end loop; --处理分诊预约数 if :NEW.FZYYS0>0 and substr(:new.FZYYMX,-1)=','then --v_FZYYMX:=:new.FZYYMX; v_FZYYMX:=substr(:new.FZYYMX,1,length(:new.FZYYMX)-1); /*while instr(v_FZYYMX,',')>0 loop ls_hzxh00:=to_number(substr(v_FZYYMX,1,instr(v_FZYYMX,',')-1)); update YS_HBHZXH SET YYBZ00='4' where GHHBID=:new.GHHBID and HZXH00=ls_hzxh00; v_FZYYMX:=substr(v_FZYYMX,instr(v_FZYYMX,',')+1,length(v_FZYYMX)-instr(v_FZYYMX,',')); end loop; */ SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''5'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_FZYYMX||')'); elsif :NEW.FZYYS0>0 and substr(:new.FZYYMX,-1)<>','then v_FZYYMX:=substr(:new.FZYYMX,1,length(:new.FZYYMX)); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''5'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_FZYYMX||')'); end if; --处理电话预约数 if :NEW.DHYYS0>0 and substr(:new.DHYYMX,-1)=',' then v_DHYYMX:=substr(:new.DHYYMX,1,length(:new.DHYYMX)-1); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''4'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_DHYYMX||')'); elsif :NEW.DHYYS0>0 and substr(:new.DHYYMX,-1) <>',' then v_DHYYMX:=substr(:new.DHYYMX,1,length(:new.DHYYMX)); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''4'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_DHYYMX||')'); end if; --处理网络预约数 if :NEW.WLYYS0>0 and substr(:new.WLYYMX,-1)=',' then v_WLYYMX:=substr(:new.WLYYMX,1,length(:new.WLYYMX)-1); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''6'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_WLYYMX||')'); elsif :NEW.WLYYS0>0 and substr(:new.WLYYMX,-1)<>',' then v_WLYYMX:=substr(:new.WLYYMX,1,length(:new.WLYYMX)); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''6'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_WLYYMX||')'); end if; --if :new.YNBLMX is not null and nvl(:new.YNBLMX,' ')<>nvl(:old.YNBLMX,' ') then --2018.06.26 修正院内保留号被改为现场号问题 if :new.YNBLMX is not null then -- if substr(:new.YNBLMX,-1)=',' then -- v_YNBLMX:=substr(:new.YNBLMX,1,length(:new.YNBLMX)-1); -- elsif substr(:new.YNBLMX,-1)<>',' then -- v_YNBLMX:=:new.YNBLMX; -- end if; v_YNBLMX := trim(replace(:new.YNBLMX,'，',',')); if substr(v_YNBLMX,-1)=',' then v_YNBLMX:=substr(v_YNBLMX,1,length(v_YNBLMX)-1); end if; if substr(v_YNBLMX,1,1)=',' then v_YNBLMX:=substr(v_YNBLMX,2,length(v_YNBLMX)-1); end if; if nvl(v_YNBLMX,'0')<>'0' then SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''3'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_YNBLMX||') and YYBZ00=''1'' and BRXM00 is null '); end if; end if; end if; end if; --=====================以下是广东省中院要求按时间段进行挂号的处理===============================-- --生成号表时，填写SF_HBHZXH的数据。 --未开始挂号，修改限号数的，重新填写SF_HBHZXH的数据 --未开始挂号，修改预约限号数或限号的，重新修正SF_HBHZXH的预约信息(开始挂号的，不让修改预约限号数) --正式挂号后，进行加号的 select upper(trim(nvl(max(VALUE0),'N'))) into Cs_Ys_Xzygh0 from XT_XTCS00 where NAME00='YS_SFYXPTBRTQGH'; if Cs_Ys_Xzygh0='M' then --广东中院 --if (updating and :new.GHYGHS=0 and (:new.GHXHS0<>:old.GHXHS0 or nvl(:old.HZXHJS,-1)<>:new.HZXHJS)) then if inserting or (updating and :new.GHYGHS=0 and nvl(:new.YYSL00,0)=0 and (:new.GHXHS0<>:old.GHXHS0)) then delete from YS_HBHZXH where GHHBID=:new.GHHBID; --取候诊序号基数 Vyzdycd:=Floor((to_date(:new.ghmzsj||:new.ghjssj,'yyyymmddhh24:mi:ss')-to_date(:new.ghmzsj||:new.ghqssj,'yyyymmddhh24:mi:ss'))*24*60); Vhzdcd0:=60; Vhzdnum:=ceil(round(Vyzdycd/Vhzdcd0,2));--候诊段个数 Vhzdrs0:=ceil(round(:new.ghxhs0/Vhzdnum,2));--每个候诊段默认挂号人数 for i in 1..:new.GHXHS0 loop V_Count0:=ceil(i/Vhzdrs0); --候诊段序号 --取候诊段的起始和截至时间 Vhzqssj:=to_date(:new.ghmzsj||:new.ghqssj,'YYYYMMDDHH24:MI:SS')+Vhzdcd0*(V_Count0-1)/(60*24); Vhzjzsj:=to_date(:new.ghmzsj||:new.ghqssj,'YYYYMMDDHH24:MI:SS')+Vhzdcd0*V_Count0/(60*24); Vhzjzsj:=least(Vhzjzsj,to_date(:new.ghmzsj||:new.ghjssj,'YYYYMMDDHH24:MI:SS')); insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZSJ00,YYRQSJ,SFZJ00,SJHZXH,HZDXH0,YYID00) Values (SQ_YS_HBHZXH_ID0000.nextVal,:new.GHHBID,i+nvl(:new.HZXHJS,0),'1',to_char(Vhzqssj,'HH24:MI')||'-'||to_char(Vhzjzsj,'HH24:MI'),null,'0',null,V_Count0,null); end loop; end if; if updating and :new.GHYGHS=0 and nvl(:old.HZXHJS,0)<>nvl(:new.HZXHJS,0) then update YS_HBHZXH set HZXH00=HZXH00+nvl(:new.HZXHJS,0)-nvl(:old.HZXHJS,0) where GHHBID=:new.GHHBID; end if; if inserting or (updating and :new.GHYGHS=0 and nvl(:new.YYSL00,0)=0 and ((:new.GHXHS0<>:old.GHXHS0) or (:new.YYXHMX<>:old.YYXHMX) or (:new.YYXHS0<>:old.YYXHS0) or (:old.DHYYMX<>:new.DHYYMX) or (:old.FZYYMX<>:new.FZYYMX))) then Vyzdycd:=Floor((to_date(:new.ghmzsj||:new.ghjssj,'yyyymmddhh24:mi:ss')-to_date(:new.ghmzsj||:new.ghqssj,'yyyymmddhh24:mi:ss'))*24*60); Vhzdcd0:=60; Vhzdnum:=ceil(round(Vyzdycd/Vhzdcd0,2));--候诊段个数 Vhzdrs0:=ceil(round(:new.ghxhs0/Vhzdnum,2));--每个候诊段默认挂号人数 update YS_HBHZXH set YYBZ00='1',BRID00=NULL,SJHZXH=NULL where GHHBID=:new.GHHBID and YYBZ00<>'7'; SP_EXECUTE_SQL('update YS_HBHZXH set YYBZ00=''3'' where GHHBID='||to_char(:new.GHHBID)||' and ID0000 in (select ID0000 from '|| '(select ID0000,HZDXH0,rank()over(partition by HZDXH0 order by HZXH00) Rank00 '|| 'from YS_HBHZXH a where GHHBID='||to_char(:new.GHHBID)||' ) where RANK00<=nvl(SF_YS_HZDYYS(HZDXH0,'''||:new.YYXHMX||'''),0))'); --update YS_HBHZXH a set SJHZXH=(select count(*) from YS_HBHZXH where GHHBID=:new.GHHBID and YYBZ00='3' and HZXH00<=a.HZXH00) where GHHBID=:new.GHHBID and YYBZ00='3'; --修正预约优待标志'1'普通'2'优待'3'院外预约'4'院内电话预约'5'分诊预约,'6' 网络预约'7'当天预约 if :new.DHYYS0>0 then SP_EXECUTE_SQL('update YS_HBHZXH set YYBZ00=''4'' where GHHBID='||to_char(:new.GHHBID)||' and ID0000 in (select ID0000 from '|| '(select ID0000,HZDXH0,rank()over(partition by HZDXH0 order by HZXH00) Rank00 '|| 'from YS_HBHZXH a where GHHBID='||to_char(:new.GHHBID)||' and YYBZ00=''3'') where RANK00<=nvl(SF_YS_HZDYYS(HZDXH0,'''||:new.DHYYMX||'''),0))'); end if; if :new.FZYYS0>0 then SP_EXECUTE_SQL('update YS_HBHZXH set YYBZ00=''5'' where GHHBID='||to_char(:new.GHHBID)||' and ID0000 in (select ID0000 from '|| '(select ID0000,HZDXH0,rank()over(partition by HZDXH0 order by HZXH00) Rank00 '|| 'from YS_HBHZXH a where GHHBID='||to_char(:new.GHHBID)||' and YYBZ00=''3'') where RANK00<=nvl(SF_YS_HZDYYS(HZDXH0,'''||:new.FZYYMX||'''),0))'); end if; if :new.WLYYS0>0 then SP_EXECUTE_SQL('update YS_HBHZXH set YYBZ00=''6'' where GHHBID='||to_char(:new.GHHBID)||' and ID0000 in (select ID0000 from '|| '(select ID0000,HZDXH0,rank()over(partition by HZDXH0 order by HZXH00) Rank00 '|| 'from YS_HBHZXH a where GHHBID='||to_char(:new.GHHBID)||' and YYBZ00=''3'') where RANK00<=nvl(SF_YS_HZDYYS(HZDXH0,'''||:new.WLYYMX||'''),0))'); end if; end if; end if; --=====================以下是厦门医院预约前置机停诊信息中间表写入===============================-- begin select nvl(trim(VALUE0),'N') into Vsfkzxmyy from XT_XTCS00 where NAME00='YY_XMSYYYYQZJ'; exception when others then Vsfkzxmyy:='N'; end; if (Updating) and (Vsfkzxmyy='Y') and (:new.TZBZ00<>:old.TZBZ00) then insert into SF_YYZJB0(ID0000,GHHBID,TZBZ00,CZLX00,CZY000,CZYKS0,CZRQ00,CZSJ00) select SQ_SF_YYZJB0_ID0000.nextval,:new.GHHBID,:new.TZBZ00,'1',null,null,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') from dual; end if; if (Updating) and (:new.GHQSSJ<>:old.GHQSSJ) then if length(trim(:new.GHQSSJ))<8 then raise E_Ghxhs1; end if; end if; if v_HZSJTS=0 then -- for MZHS-20140207-001 select nvl(min(HZXH00),-1) into v_HZXH00 from YS_HBHZXH where GHHBID=:NEW.GHHBID and HZQSSJ||':00'>=:new.GHJSSJ; if v_HZXH00>0 then update YS_HBHZXH set HZQSSJ='' where GHHBID=:NEW.GHHBID and HZXH00>v_HZXH00 ; end if; end if ; exception when E_Ghxhs0 then raise_application_error(-20281, '修改挂号号表ID'||:NEW.GHHBID||'的挂号号表失败,原因:挂号限号数已经超过了已经挂的病人数!*'); rollback; when E_Ghxhs1 then raise_application_error(-20282, '修改挂号号表ID'||:NEW.GHHBID||'的挂号号表失败,原因:时间格式不对,不能为8:00:00格式应该为08:00:00,请与管理员联系!*'); rollback; when E_Yyxhs0 then raise_application_error(-20281, '修改或增加挂号号表ID'||:NEW.GHHBID||'的挂号号表失败,原因:挂号限号数至少应该是预约限号数的两倍!*'); rollback; When no_data_found then raise_application_error(-20010,'数据没有找到,'||errStr||'！*'); when others then raise_application_error(-20020, nvl(sqlerrm, '原因不明出错！*')); End;