修改内容： if (YY_YYSFDXTZ='Y') and (PBRXM00 is not null) and(PBRDH00 is not null) and (nvl(instrb(YY_SFSQYYFCJMC,'/'||PWLYYMC||'/'),0)=0) then LS_DXTSXX := PBRXM00||','||'您好！您已预约了'||trim(LS_YYJC00)||' '||vYYKSMC||' '||vYYYSXM||'医生 '||substr(PYYRQ00,1,4)||'年'||substr(PYYRQ00,5,2)||'月'||substr(PYYRQ00,7,2)||'日'||LS_HZQSSJ||'就诊'|| ',预约号:'||to_char(PYYH000)||'请您于就诊当日提前打印好候诊单，准时就诊！'; end if; CREATE OR REPLACE PROCEDURE SP_YY_INSERT_SF_YYBRB0 ( PYYID00 in number, --预约编号 SQ_SF_YYBRBO_YYID00.nextval PGHHBID in number,--挂号号 SF_YSGHHB.GHHBID PYYH000 in number,--预约号 PBRID00 in number,--病人编号 BM_BRXXB0.BRID00 PBRXM00 in varchar2,--病人姓名 BM_BRXXB0.BRXM00 PBRDH00 in varchar2 ,--电话号码 BM_BRXXB0.BRDH00 PKMH000 in varchar2, --卡面号 PICKH00 in varchar2,--就诊卡号 IC_YHXX00.ICKH00 PYYLB00 in char,--预约类别编号 0电话预约 1分诊台预约 2网络预约 PYYRQ00 in char,--预约日期 PYYJSSJ in char,--结束时间 PYYZT00 in char,--预约状态 0 登记 1 确认(预挂号) 2 取消 3 就诊 PYYYGBH in number,--预约操作员编号 BM_YGBM00.YGBH00 PBZ0000 in varchar2,--备注 PCZYBM0 in NUMBER,--操作科室 PCZBZ00 in char,--操作标志 1 登记 2 变更 3 取消 4 还原取消记录 PSFZH00 in varchar2 default '', --身份证号 PCOMMIT in char default 'Y', --是否提交 'Y'提交N不提交 PYSPBID in NUMBER default 0, --医生排班ID PYYSD00 in char default '', --预约时段 PQXLX00 in char default '', --取消类型 0院内手工取消,1院内自动取消,2是院外取消 PSFZJ00 in char default 'N', --是否允许加号 PSFCZ00 in char default '0', --是否初诊 '0' 初诊 '1'复诊 PSFSH00 in char default '0', --是否术后 '0' 不是 '1'是 PSFSQZZ in char default '0', --是否社区转诊 '0'不是 '1'是 PWLYYMC in varchar2 default '' --网络预约厂家 ) as ls_WJSDJH number; --单据号 YYKF char(1);--预约是否扣费 1 扣费 0 不扣费 verrsql varchar2(220);--错误提示 Temp_yyh number;--变更时存储旧预约号 Temp_GHHBID SF_YSGHHB.GHHBID%type;--挂号号表 vYYYGXM BM_YGBM00.ZWXM00%type;--预约员工姓名 vDJH000 SF_BRFY00.DJH000%type;--单据号 SF_BRFY00.DJH000 vDJRQ00 char(8);--操作日期 vDJSJ00 char(8);--操作时间 vYYKSBH BM_BMBM00.BMBH00%type;--预约科室编号 SF_YSGHHB.GHKSBH vYYKSMC BM_BMBM00.BMMC00%type;--预约科室名称 vYYYSBH BM_YGBM00.YGBH00%type;--预约医生编号 SF_YSGHHB.GHYSBH vYYYSXM BM_YGBM00.ZWXM00%type;--预约医生姓名 vGHLB00 SF_YSGHHB.GHLBBH%type;--挂号类别 SF_YSGHHB.GHLBBH vSFXMID BM_YYSFXM.SFXMID%type;--收费项目编号 vYYBZ00 YS_HBHZXH.YYBZ00%type;--预约标志 '1'普通'2'优待 '3'预约 '4'电话 '5'分诊 '6'网络 vSFDTYY SF_YYBRB0.SFDTYY%type;--是否当天预约,'1'当天预约 ls_FBBH00 IC_YBBRLB.FBBH00%TYPE; --费别编号 ls_YBBRLB BM_BRXXB0.YBBRLB%TYPE; --病人类别 ls_YBZXLB IC_YBBRLB.YBZXLB%TYPE; --医保中心类别 ls_GFBL00 NUMBER(12,2); --公费报销比例 ls_JZBL00 NUMBER(12,2); --记账报销比例 ls_MXZFJE SF_FYMX00.ZFJE00%TYPE; --费用明细表的自付金额 ls_MXJZJE SF_FYMX00.JZJE00%TYPE; --费用明细表的记账金额 ls_MXGFJE SF_FYMX00.GFJE00%TYPE; --费用明细表的公费金额 ls_MXHJJE SF_FYMX00.HJJE00%TYPE; --费用明细表的合计金额 ls_yhbl00 bm_yyhsxm.yhbl00%type; ls_ZFBL00 BM_YBSFDY.ZFBL00%type; --费用自付比例 E_SJCF EXCEPTION; V_COUNT0 NUMBER(5,0); V_XQ0000 NUMBER(5,0); v_YYID00 SF_YYBRB0.YYID00%type; --预约id v_SFYYDTYY CHAR(10); --是否允许当天预约 ls_bz0000 SF_YYBRB0.BZ0000%type; v_yxtqgh xt_xtcs00.value0%type; V_ynyymx sf_yspbb0.fzyymx%type; V_sdxs00 number(5); ls_CKBH00 SF_YSGHHB.GHHZCK%type; --分诊窗口编号 ls_FZCOUNT number(3); --分诊计数台 vID0000 YS_HBHZXH.ID0000%type; ls_YYSD00 SF_YYBRB0.YYSD00%type; Vmaxhzh number(10); --最大候诊序号 Vtmpstr YS_HBHZXH.HZSJ00%type; vTZBZ00 SF_YSGHHB.TZBZ00%type; vSFZJGH SF_YSGHHB.SFZJGH%type; vSFJZJH SF_YSGHHB.SFJZJH%type; vYYJSSJ SF_YYBRB0.YYJSSJ%type; vYYRQ00 SF_YYBRB0.YYRQ00%type; vGHCJ00 SF_YYBRB0.GHCJ00%type; ls_yyzt00 SF_YYBRB0.yyzt00%type; YY_SFSQYYFCJMC XT_XTCS00.VALUE0%type; -- 取某个厂家的是否扣费参数,可定义多个厂家，每个厂家之间用逗号隔开 ls_SYSMSG varchar2(1000); lv_ZXCGBZ varchar2(100); V_sfxmkqyy xt_xtcs00.value0%type; LS_DXTSXX varchar(500); LS_KSWZXX varchar(100); LS_WB0001 BM_MZSJB0.mzsj00%TYPE; LS_WB0002 BM_MZSJB0.mzsj00%TYPE; ls_YYHYGLMS XT_XTCS00.VALUE0%type; --预约号源管理模式:0=HIS自己管理,1=传给健康之路,默认值为0 LS_YYJC00 XT_YYXX00.YYJC00%type; --医院简称 vDGSJZRQ CHAR(14);--带格式就诊日期 YY_YYSFDXTZ XT_XTCS00.VALUE0%type; --预约是否短信通知参数 LS_HZQSSJ YS_HBHZXH.HZQSSJ%type; ls_hzzt00 sf_hzbr00.hzzt00%type; -- MODIFICATION HISTORY -- Person Date Comments -- csf 2011.11.03 增加记录网络预约的厂家，只处理了txt文本导入的 -- csf 2012.01.06 健康之路预约病人不收预约费 按参数YY_SFSQYYFCJMC控制 YYGH-20120112-001 -- zhangwz 2014.04.01 预约操作的时候写入中间表数据 SF_YYZJB0 用于后续相关数据到前置机的功能。 -- zhangwz 2014.06.16 排班表增加YSYGBH=0的科室排班数据，过程进行相应的修改 -- liuj 2015.07.01 限制号时间过去不能约 -- qks 2016.01.06 省人民：取消预约,yyzt00由6改为1 -- qks 2017.05.24 省人民：数据库巡查整改归档; for MZHS9-20170524-001 -- qks 2017.12.18 verrsql定义从varchar2(120)改为varchar2(220); -- qks 2018.01.15 健康之路调用取消预约挂号接口修改; MZHS9-20180115-001 -- luowf 2020.12.14 增加参数YY_YGBRYTTYGYSZNYYYC限制一个病人一天内同一个医生只能预约一次 MZHS9-20201210-002 -- luowf 2020.12.16 预约是否通过短信提醒患者 MZHS9-20201210-001 -- --------- ---------- -------------------------------------------------- begin --取参数 begin select trim(VALUE0) into v_SFYYDTYY from XT_XTCS00 where NAME00='YY_SFYXDTYY'; exception when others then v_SFYYDTYY:='N'; end; --取参数 预约是否短信通知 begin select trim(VALUE0) into YY_YYSFDXTZ from XT_XTCS00 where NAME00='YY_YYSFDXTZ'; exception when others then YY_YYSFDXTZ:='N'; end; --取某个厂家的是否扣费参数,可定义多个厂家，每个厂家之间用逗号隔开 begin select trim(VALUE0) into YY_SFSQYYFCJMC from XT_XTCS00 where NAME00='YY_SFSQYYFCJMC'; exception when others then YY_SFSQYYFCJMC:='N'; end; --预约号源管理模式 select nvl(max(trim(VALUE0)),'0') into ls_YYHYGLMS from XT_XTCS00 where NAME00='YY_YYHYGLMS'; begin select trim(VALUE0) into V_sfxmkqyy from XT_XTCS00 where NAME00='YS_SFXMKQYY'; exception when others then V_sfxmkqyy:='N'; end; --取参数 select upper(trim(nvl(max(VALUE0),'N'))) into v_yxtqgh from XT_XTCS00 where NAME00='YS_SFYXPTBRTQGH'; --取当前操作日期、时间 select to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') into vDJRQ00,vDJSJ00 from dual; if (v_SFYYDTYY='N') and (PCZBZ00 in ('1','2','4') ) and (PYYRQ00=vDJRQ00 ) then --不允许操作当天的预约信息 verrsql:='预约的就诊日期不能是当天！'; raise no_data_found; end if; if ( PYYRQ00'M') or (v_yxtqgh='M' and PYYRQ00=vDJRQ00) then if (v_yxtqgh<>'M') then select count(*) into V_COUNT0 from YS_HBHZXH where GHHBID=PGHHBID and HZXH00=PYYH000; if V_COUNT0=0 then verrsql:='你所选择的医生排班表记录及序号不存在'||'select count(*) from YS_HBHZXH where GHHBID='||to_char(PGHHBID)||' and HZXH00='||to_char(PYYH000)||' '; raise no_data_found; end if; end if; select HZQSSJ into LS_HZQSSJ from YS_HBHZXH where GHHBID=PGHHBID and HZXH00=PYYH000; -- select count(*) into V_COUNT0 from YS_HBHZXH where GHHBID=PGHHBID and HZXH00=PYYH000 and PYYRQ00||substrb(nvl(trim(HZQSSJ),'00:01'),1,5)>=to_char(sysdate,'YYYYMMDDHH24:MI'); -- if V_COUNT0=0 then -- verrsql:='你所选择的号数已过时，不能预约!'; -- raise no_data_found; -- end if; --取扣费方式过程 不自动扣费 begin select trim(value0) into YYKF from XT_XTCS00 where NAME00 ='YY_YYKF'; exception when others then YYKF:='0'; end; if PBRID00=0 then --也不扣费 YYKF:='0'; end if; if instrb(YY_SFSQYYFCJMC,','||PWLYYMC||',')>0 then --指定某个网络厂家不收预约费 YYKF:='0'; end if; --先判断去哪个收费标准 begin select c.sckbh0 into ls_CKBH00 from SF_YSGHHB b,bm_ckdy00 c where b.GHHZCK=c.dckbh0 and b.GHHBID=PGHHBID; select count(1) into ls_FZCOUNT from XT_XTCS00 where instrb(','||trim(VALUE0)||',',','||to_char(ls_CKBH00)||',')>0 and NAME00='YS_FZTQYLYSFBZ'; exception when others then ls_FZCOUNT:=0; end; --取收费项目编号 if ls_FZCOUNT>0 then begin select to_number(trim(value0)) into vSFXMID from xt_xtcs00 where name00='YY_YYSFXMID2' and value0>'0'; exception when others then begin select to_number(trim(value0)) into vSFXMID from xt_xtcs00 where name00='YY_YYSFXMID'; exception when others then vSFXMID:=0; end; end; else begin select to_number(trim(value0)) into vSFXMID from XT_XTCS00 where NAME00='YY_YYSFXMID'; exception when others then vSFXMID:=0; end; end if; --取病人FBBH00,YBBRLB,YBZXLB if PBRID00>0 then begin select A.FBBH00,A.YBBRLB,B.YBZXLB into ls_FBBH00,ls_YBBRLB,ls_YBZXLB from BM_BRXXB0 A,IC_YBBRLB B where A.YBLB00 = B.YBLB00 and A.FBBH00 = B.FBBH00 and A.BRID00 = PBRID00 ; exception when others then verrsql:='select A.FBBH00,A.YBBRLB,B.YBZXLB into ls_FBBH00,ls_YBBRLB,ls_YBZXLB from BM_BRXXB0 A,IC_YBBRLB B where A.YBLB00 = B.YBLB00 and A.FBBH00 = B.FBBH00 and A.BRID00 = '||to_char(PBRID00)||'该语句取不到数据出错'; raise no_data_found; end; end if; if (vSFXMID>0) and (PBRID00>0) then --是否定义项目的分摊比例 begin ls_yhbl00:=1; --取优惠比例 select nvl(max(DJ0000),-1) into ls_yhbl00 from BM_TYZD00 where ZDMC00='按病人类别定义预约费优惠比例' and YXBZ00='1' and instrb(','||trim(BZ0000)||',',','||to_char(ls_FBBH00)||'-'||ls_YBZXLB||'-'||ls_YBBRLB||',')>0 and rownum=1 ; if (ls_yhbl00<0) or (ls_yhbl00>1) then ls_yhbl00:=1; else ls_yhbl00:=1-ls_yhbl00; end if; select nvl(ZFBL00,1),NVL(1-ZFBL00-JMBL00,0),NVL(JMBL00,0),NVL(XMJE00*ls_yhbl00,0), NVL(ZFJE00*ls_yhbl00,0),NVL(XMJE00*ls_yhbl00-ZFJE00*ls_yhbl00-JMJE00*ls_yhbl00,0), NVL(JMJE00*ls_yhbl00,0) into ls_ZFBL00,ls_JZBL00,ls_GFBL00,ls_MXHJJE,ls_MXZFJE,ls_MXJZJE,ls_MXGFJE from VW_BM_YBSFDY where YBBRLB = ls_YBBRLB and YBZXLB = ls_YBZXLB and SFXMID = vSFXMID and FBBH00 = ls_FBBH00 and SFYP00 = 'N'; --是否药品 exception when others then ls_JZBL00 := 0; ls_GFBL00 := 0; ls_MXHJJE := 0; ls_MXZFJE := 0; ls_MXJZJE := 0; ls_MXGFJE := 0; verrsql:='获取该预约挂号费的自付比例、记账比例时,出现异常错误!'||'select * from VW_BM_YBSFDY where SFYP00 = N and YBBRLB ='||ls_YBBRLB||' and YBZXLB='||ls_YBZXLB||' and SFXMID='||to_char(vSFXMID)||'and FBBH00='||to_char(ls_FBBH00); raise no_data_found; END; end if; --取操作员姓名 select ZWXM00 into vYYYGXM from BM_YGBM00 where YGBH00=PYYYGBH; --预约标志处理 if PYYLB00='0' then vyybz00:='4';--电话 elsif PYYLB00='1' then vyybz00:='5';--分诊 else vyybz00:='6';--网络预约 end if; vSFDTYY:='0'; if v_yxtqgh='M' and PCZBZ00='3' then --取消 vYYBZ00 := '1'; if PYYRQ00=vDJRQ00 then --当天预约 vYYBZ00:='7'; vSFDTYY:='1'; end if; end if; --取预约的排班信息 --if (v_yxtqgh<>'M') or (v_yxtqgh='M' and vSFDTYY='1') then if PCZBZ00<>'3' then begin Select a.GHKSBH,b.BMMC00,a.GHYSBH,decode(a.GHYSBH,0,b.BMMC00,c.ZWXM00),a.GHLBBH,a.TZBZ00,a.SFZJGH,a.SFJZJH,a.GHMZSJ into vYYKSBH,vYYKSMC,vYYYSBH,vYYYSXM,vGHLB00,vTZBZ00,vSFZJGH,vSFJZJH,vYYRQ00 from SF_YSGHHB a,BM_BMBM00 b,BM_YGBM00 c where a.GHHBID=PGHHBID and a.GHKSBH=b.BMBH00 and a.GHYSBH=c.YGBH00(+); exception when others then verrsql:='取排班信息出现错误，传入的GHHBID='||to_char(PGHHBID)||'没有取到数据!'; raise no_data_found; end; end if; --取变量LS_YYJC00 select nvl(MAX(YYJC00),' ') into LS_YYJC00 from XT_YYXX00 where YYID00=(select YYID00 from BM_BMBM00 where BMBH00=vYYKSBH); ls_WJSDJH:=0; --登记 if PCZBZ00='1' then --一个病人一天内同一个医生只能预约一次 begin select count(1) into V_COUNT0 from XT_XTCS00 where NAME00='YY_YGBRYTTYGYSZNYYYC' and VALUE0='Y'; exception when others then V_COUNT0 := 0; end; if V_COUNT0 > 0 then V_COUNT0 :=0; if PYYLB00 <> 0 then select count(1) into V_COUNT0 from SF_YYBRB0 where YYYSBH=vYYYSBH and YYRQ00=PYYRQ00 and BRID00=PBRID00 and YYZT00 in ('0','1') and BDRQ00 is null and BRDH00=PBRDH00; else select count(1) into V_COUNT0 from SF_YYBRB0 where YYYSBH=vYYYSBH and YYRQ00=PYYRQ00 and YYZT00 in ('0','1') and BRXM00=PBRXM00 and BRDH00=PBRDH00; end if; if V_COUNT0 > 0 then verrsql := '您已预约该医生'||PYYRQ00||'的号源,不允许再重复预约!'; raise no_data_found; end if; end if; --更新医生号表记录 vYYJSSJ := PYYJSSJ; vGHCJ00 := ''; if V_sfxmkqyy='Y' then select count(1) into V_COUNT0 from sf_yybrb0 where (BRXM00=PBRXM00 and BRDH00=Trim(PBRDH00) or PICKH00<>'0' and ICKH00=PICKH00) and yyzt00='0' and (yyrq00=to_char(to_date(PYYRQ00,'YYYYMMDD')-1,'YYYYMMDD') or yyrq00=to_char(to_date(PYYRQ00,'YYYYMMDD')+1,'YYYYMMDD')) and yyysbh=vYYYSBH; if V_COUNT0>0 then verrsql := '本院限制:相邻两天同一个病人不能预约同一个医生!'; raise E_SJCF; end if; end if; select count(*) into V_COUNT0 from SF_YYBRB0 where YYRQ00>=to_char(sysdate,'YYYYMMDD') and GHHBID=PGHHBID and YYH000=PYYH000 and YYZT00<>'2'; if V_COUNT0>0 then verrsql:='所选择的预约号已被占用，请选择其它号！'; raise E_SJCF; end if; --同个号表不允许重复预约 select count(1) into V_COUNT0 from sf_yybrb0 where GHHBID=PGHHBID and ((BRXM00=PBRXM00 and BRDH00=Trim(PBRDH00)) or (PICKH00<>'0' and ICKH00=PICKH00)) and yyzt00 in ('0','1'); if V_COUNT0>0 then verrsql := '该病人已预约'||PYYRQ00||'同个医生同一时段就诊,不允许再重复预约!'; raise E_SJCF; end if; /* select count(*) into V_COUNT0 from YS_HBHZXH where GHHBID=PGHHBID and HZXH00=PYYH000 and PYYRQ00||substrb(nvl(trim(HZQSSJ),'00:01'),1,5)>=to_char(sysdate,'YYYYMMDDHH24:MI'); if V_COUNT0=0 then verrsql:='你所选择的号数已过时，不能预约!'; raise E_SJCF; end if; */ if v_yxtqgh='M' then if PBZ0000='院外预约' then if PYYYGBH in (61655,61346) then verrsql := '用户ID有误,不允许预约!'; raise E_SJCF; end if; vGHCJ00:=vYYYGXM; end if; --判断预约日期是否为对应号表的挂号日期 if vYYRQ00<>PYYRQ00 then verrsql := '该病人预约日期'||PYYRQ00||'跟号表的就诊日期'||vYYRQ00||'不一致,请您重新选择!'; raise E_SJCF; end if; --增加姓名跟电话号码判断失约次数 select count(1) into V_COUNT0 from sf_yybrb0 where (BRXM00=PBRXM00 and BRDH00=Trim(PBRDH00) or PICKH00<>'0' and ICKH00=PICKH00) and yyrq00=PYYRQ00 and yyzt00<>'2'; if V_COUNT0>1 then verrsql := '该病人预约日期'||PYYRQ00||'有2次预约记录,不允许再预约!'; raise E_SJCF; end if; --一周看某个医生不能超过2次 select decode(to_number(to_char(to_date(PYYRQ00,'YYYYMMDD'),'D')),1,7,to_number(to_char(to_date(PYYRQ00,'YYYYMMDD'),'D'))-1) into V_XQ0000 from dual; select count(1) into V_COUNT0 from sf_yybrb0 where (BRXM00=PBRXM00 and BRDH00=Trim(PBRDH00) or PICKH00<>'0' and ICKH00=PICKH00) and yyzt00<>'2' and yyrq00>=to_char(to_date(PYYRQ00,'YYYYMMDD')-V_XQ0000+1,'YYYYMMDD') and yyrq00<=to_char(to_date(PYYRQ00,'YYYYMMDD')+7-V_XQ0000,'YYYYMMDD') and yyysbh=vYYYSBH; if V_COUNT0>1 then verrsql := '该病人本周预约医生'||vYYYSXM||'2次,不允许再预约!'; raise E_SJCF; end if; if nvl(PYYSD00,'0')<>'0' then vYYJSSJ:= substr(PYYSD00,1,5)||':00'; end if; if vTZBZ00<>'0' then verrsql := '该医生预约日期'||PYYRQ00||'已停诊！'; raise E_SJCF; end if; if vSFZJGH='1' then verrsql := '该医生预约日期'||PYYRQ00||'全部预约完毕！'; raise E_SJCF; end if; update sf_ysghhb set YYXHS0=YYXHS0+1,YYXHMX=SF_YY_YYXHMX(YYXHMX,GHQSSJ,PYYSD00,1),YYSL00=nvl(YYSL00,0)+1 where ghhbid = PGHHBID; end if; ----写入费用明细表 if trim(YYKF)='1' then --预约时扣费 --获取单据号 select SQ_SF_BRJFB0_DJH000.nextval into ls_WJSDJH from dual; verrsql:='预约费用处理，将数据插入插入表SF_FYMX00时，产生错误，请检查！'; Insert into SF_FYMX00 (MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00, XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00,CZRQ00,CZSJ00, ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00, SFYDJ0,ZFBL00) select SQ_SF_FYMX00_MXID00.nextval,ls_WJSDJH,SFXMID,XMMC00,'',DW0000,ls_MXHJJE, 1,ls_MXHJJE,ls_MXZFJE,ls_MXGFJE,ls_MXJZJE,vDJRQ00,vDJSJ00, vDJRQ00,vDJSJ00,PCZYBM0,vYYYSBH,vYYKSBH,vYYYSBH,0, 0,LS_ZFBL00 from BM_YYSFXM where SFXMID = vSFXMID; --写入病人费用表 verrsql:='预约费用处理，将数据插入插入表SF_BRFY00时，产生错误，请检查！'; insert into SF_BRFY00 (DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00, HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00,CZRQ00,CZSJ00, CZY000,CZYXM0,CZYKS0,JZDH00) select ls_WJSDJH,0,0,1,PBRID00,PBRXM00,LS_FBBH00, HJJE00,HJJE00,JZJE00,GFJE00,0,vDJRQ00,vDJSJ00, PYYYGBH,vYYYGXM,PCZYBM0,0 from SF_FYMX00 where DJH000=ls_WJSDJH ; end if; vYYJSSJ:= substr(vYYJSSJ,1,5)||':00'; verrsql:='预约登记将数据插入插入表SF_YYBRB0时，产生错误，请检查！'; insert into SF_YYBRB0( YYID00,JZID00,GHHBID,YYH000,BRID00,BRXM00,BRDH00,KMH000, ICKH00,YYKSBH,YYKSMC,YYYSBH,YYYSXM,YYLB00,YYRQ00, YYJSSJ,YYZT00,YYYGBH,YYYGXM,DJH000,GHLB00,DJRQ00,DJSJ00,CZYBM0,BZ0000,SFZH00,YSPBID,YYSD00,SFDTYY,GHCJ00,SFCZ00, SFSH00,SFSQZZ,WLYYMC) values( PYYID00,0,PGHHBID,PYYH000,PBRID00,PBRXM00,PBRDH00,PKMH000, PICKH00,vYYKSBH,vYYKSMC,vYYYSBH,vYYYSXM,PYYLB00,PYYRQ00, vYYJSSJ,PYYZT00,PYYYGBH,vYYYGXM,ls_WJSDJH,vGHLB00,vDJRQ00,vDJSJ00,PCZYBM0, PBZ0000,PSFZH00,PYSPBID,PYYSD00,vSFDTYY,vGHCJ00,PSFCZ00,PSFSH00,PSFSQZZ,PWLYYMC); --if (v_yxtqgh<>'M') or (v_yxtqgh='M' and vSFDTYY='1') then if (v_yxtqgh<>'M') then --更新医生号表侯诊序号 update YS_HBHZXH set BRID00=PBRID00,YYID00=PYYID00,BRXM00=PBRXM00,YYBZ00=vYYBZ00 where GHHBID=PGHHBID and HZXH00=PYYH000; else select nvl(min(ID0000),0) into vID0000 from YS_HBHZXH where GHHBID=PGHHBID and substrb(hzsj00,1,5)=substrb(PYYSD00,1,5) and YYBZ00='1' and YXBZ00='1' and GHH000 is NULL; if vID0000 = 0 then if PSFZJ00 = 'Y' then --允许医生在最后一个时段加号预约 if nvl(vSFJZJH,'0')='1' then verrsql := '该医生预约日期'||PYYRQ00||'('||PYYSD00||')禁止预约加号,不允许预约!'; raise E_SJCF; end if; select max(HZSJ00),max(HZDXH0),max(HZXH00) into Vtmpstr,V_COUNT0,Vmaxhzh from YS_HBHZXH where GHHBID=PGHHBID; if Vmaxhzh>0 then update sf_ysghhb set GHXHS0=GHXHS0+1 where ghhbid = PGHHBID; insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,BRID00,BRXM00,YYBZ00,HZSJ00,YYRQSJ,SFZJ00,SJHZXH,HZDXH0,YYID00) Values(SQ_YS_HBHZXH_ID0000.nextVal,PGHHBID,Vmaxhzh+1,PBRID00,PBRXM00,vYYBZ00,Vtmpstr,null,'1',null,V_COUNT0,PYYID00); end if; else verrsql:='所选择的预约时段'||PYYSD00||'已全部预约完毕，请选择其它时段！'; raise E_SJCF; end if; else update YS_HBHZXH set BRID00=PBRID00,YYID00=PYYID00,BRXM00=PBRXM00,YYBZ00=vYYBZ00 where GHHBID=PGHHBID and ID0000=vID0000 and YYBZ00='1' and YXBZ00='1' and GHH000 is NULL; if sql%notfound then raise no_data_found; end if; end if; end if; if V_sfxmkqyy='Y' and PYYLB00<>'2' then select to_char(to_date(PYYRQ00,'yyyymmdd'),'yyyy"年"mm"月"dd"日"') into vDGSJZRQ from dual; select MZSJ00 into LS_WB0001 from BM_MZSJB0 where JSSJ00>=vDJSJ00 and KSSJ00<=vDJSJ00 and rownum=1; select MZSJ00 into LS_WB0002 from BM_MZSJB0 where JSSJ00>=vYYJSSJ and KSSJ00<=vYYJSSJ and rownum=1; begin select '('||yymc00||kswz00||')'||bmmc00 into LS_KSWZXX from BM_KSWZDY where bmbh00=vYYKSBH and yyid00 in (select yyid00 from sf_ysghhb where ghhbid=PGHHBID); exception WHEN NO_DATA_FOUND THEN verrsql:='未找到科室位置信息,请联系管理员!'; raise no_data_found; end; if vYYYSBH=0 then LS_DXTSXX:='您已成功预约'||vDGSJZRQ||'('||LS_WB0002||')'||' 厦门市口腔医院'||ls_kswzxx||'科室号'; else LS_DXTSXX:='您已成功预约'||vDGSJZRQ||' '||vYYJSSJ||'('||LS_WB0002||')'||' 厦门市口腔医院'||ls_kswzxx||nvl(vYYYSXM,vYYKSMC)||'医生'; end if; if (PYYRQ00=vDJRQ00 ) and (LS_WB0001=LS_WB0002) then LS_DXTSXX:=LS_DXTSXX||'，候诊序号：'||to_char(PYYH000)||',请提前15分钟到达候诊（温馨提示：候诊时间非就诊时间，为防止过号请患者耐心在候诊区等候！）'; else LS_DXTSXX:=LS_DXTSXX||',请提前15分钟到达到医院自助机或导诊处进行预约确认！为了保证您按时就诊，请及时进行"预约确认"，超过预约时间进行确认的视为迟到，您的就诊资格可能被取消（温馨提示：候诊时间非就诊时间，为防止过号请患者耐心在候诊区等候！）'; end if; --写入短信表 insert into DX_DXB000(DXID00,DXLX00,DXZT00,DXNR00,FSHM00,JSHM00,FSXT00,BZ0000) values (SQ_DX_DXB000_DXID00.nextval,'0','0',LS_DXTSXX,'05922678555',PBRDH00,'MZHS','门诊护士站'); end if; --更新病人电话号码 update BM_BRXXB0 set BRLXDH=nvl(PBRDH00,BRLXDH) where BRID00=PBRID00; insert into SF_YYZJB0(ID0000,GHHBID,YYID00,HZID00,CZLX00,CZY000,CZYKS0,CZRQ00,CZSJ00) select SQ_SF_YYZJB0_ID0000.nextval,PGHHBID,PYYID00,null,'0',PYYYGBH,PCZYBM0,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') from dual; --厦门口腔短信内容清一下 LS_DXTSXX := ''; --添加预约信息 有预约电话 的才能发短信，外网预约厂家不发送比如健康之路（健康之路已经有短信发送功能了） if (YY_YYSFDXTZ='Y') and (PBRXM00 is not null) and(PBRDH00 is not null) and (nvl(instrb(YY_SFSQYYFCJMC,'/'||PWLYYMC||'/'),0)=0) then LS_DXTSXX := PBRXM00||','||'您好！您已预约了'||trim(LS_YYJC00)||' '||vYYKSMC||' '||vYYYSXM||'医生 '||substr(PYYRQ00,1,4)||'年'||substr(PYYRQ00,5,2)||'月'||substr(PYYRQ00,7,2)||'日'||LS_HZQSSJ||'就诊'|| ',预约号:'||to_char(PYYH000)||'请您于就诊当日提前打印好候诊单，准时就诊！'; end if; -- 变更 elsif PCZBZ00='2' then --取DJH000 Select DJH000 into vDJH000 from SF_YYBRB0 where YYID00=PYYID00; verrsql:='预约变更业务处理，修改表SF_YYBRB0数据中YYH000，产生错误，请检查！'; select YYH000,GHHBID into Temp_yyh,Temp_GHHBID from SF_YYBRB0 where YYID00=PYYID00; verrsql:='预约变更业务处理，修改表SF_YYBRB0数据，产生错误，请检查！'; --修改预约挂号表数据 update SF_YYBRB0 set GHHBID=PGHHBID,YYH000=PYYH000, YYKSBH=vYYKSBH,YYKSMC=vYYKSMC,YYYSBH=vYYYSBH,YYYSXM=vYYYSXM, YYLB00=PYYLB00,YYRQ00=PYYRQ00,YYJSSJ=PYYJSSJ, GHLB00=vGHLB00,YYYGBH=PYYYGBH,YYYGXM=vYYYGXM, DJRQ00=vDJRQ00,DJSJ00=vDJSJ00,BZ0000=PBZ0000,BRDH00=PBRDH00, SFCZ00=pSFCZ00,SFSH00=pSFSH00,SFSQZZ=pSFSQZZ, SFZH00=PSFZH00,WLYYMC=PWLYYMC where YYID00=PYYID00; --电话预约允许修改姓名和电话 if PBRID00=0 then update SF_YYBRB0 set BRXM00=PBRXM00 where YYID00=PYYID00; end if; verrsql:='预约变更业务处理，修改表YS_HBHZXH据中BRID00，产生错误，请检查！'; --if Temp_yyh<> PYYH000 then update YS_HBHZXH set BRID00=null,YYID00=null,BRXM00=null,YYBZ00=decode(YYBZ00,'6','6',vYYBZ00) where GHHBID=Temp_GHHBID and HZXH00=Temp_yyh; update YS_HBHZXH set BRID00=PBRID00,YYID00=PYYID00,BRXM00=PBRXM00,YYBZ00=decode(YYBZ00,'6','6',vYYBZ00) where GHHBID=PGHHBID and HZXH00=PYYH000; --end if; if trim(yykf)='1' then --预约时扣费 --修改费用明细表 verrsql:='预约变更业务处理，修改表SF_FYMX00数据，产生错误，请检查！'; update SF_FYMX00 SET KDKS00=vYYKSBH,KDYS00=vYYYSBH,ZXKS00=vYYKSBH,ZXYS00=vYYKSBH where DJH000=vDJH000; end if; --更新病人电话号码 update BM_BRXXB0 set BRLXDH=PBRDH00 where BRID00=PBRID00; insert into SF_YYZJB0(ID0000,GHHBID,YYID00,HZID00,CZLX00,CZY000,CZYKS0,CZRQ00,CZSJ00) select SQ_SF_YYZJB0_ID0000.nextval,PGHHBID,PYYID00,null,'1',PYYYGBH,PCZYBM0,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') from dual; --预约短信内容 if (YY_YYSFDXTZ='Y') and (PBRXM00 is not null) and (PBRDH00 is not null) and(nvl(instrb(YY_SFSQYYFCJMC,'/'||PWLYYMC||'/'),0)=0) then LS_DXTSXX :=PBRXM00||','||'您好！您的预约单：'||to_char(PYYID00)||'的预约信息已变更为：'||trim(LS_YYJC00)||' '||vYYKSMC||' '||vYYYSXM||'医生 '||substr(PYYRQ00,1,4)||'年'||substr(PYYRQ00,5,2)||'月'||substr(PYYRQ00,7,2)||'日'|| '就诊,队列号：'||to_char(PYYH000)||' 请您于就诊当日提前打印好候诊单，准时候诊！'; end if; --取消 elsif PCZBZ00='3' then verrsql:='预约ID='||PYYID00||'不存在，请检查！'; --取DJH000 Select DJH000,BZ0000,SFDTYY,GHHBID,YYSD00,YYZT00,YYH000 into vDJH000,ls_bz0000,vSFDTYY,Temp_GHHBID,ls_YYSD00,ls_YYZT00,Temp_yyh from SF_YYBRB0 where YYID00=PYYID00 and (nvl(PQXLX00,'0')<>'2' or PQXLX00='2' and YYYGBH=PYYYGBH); if ls_YYZT00 = '2' then verrsql:='所选择的预约ID='||PYYID00||'已取消,无法再取消预约！'; raise E_SJCF; elsif ls_YYZT00 = '3'then verrsql:='所选择的预约ID='||PYYID00||'已就诊,无法再取消预约！'; raise E_SJCF; end if; --修改预约状态 verrsql:='预约取消业务处理，修改表SF_YYBRB0数据，产生错误，请检查！'; update SF_YYBRB0 set YYZT00='2',QXRQ00=vDJRQ00,QXSJ00=vDJSJ00,QXR000=PYYYGBH,BZ0000=PBZ0000,QXLX00=PQXLX00 where YYID00=PYYID00 and (YYZT00='1' or YYZT00='0'); if sql%notfound then verrsql:='所选择的预约ID='||PYYID00||'已取消或就诊,无法再取消预约！'; raise E_SJCF; end if; --取消预约时如果定义的是网络预约号源，然后又被电话预约等先占用，那么在取消的时候YYBZ00要弄成6 select count(*) into V_COUNT0 from SF_YSGHHB where GHHBID=PGHHBID and instr(','||WLYYMX||',',','||PYYH000||',')>0; if (V_COUNT0>0) and (vYYBZ00<>'6') then vYYBZ00:='6'; end if; ----处理 诊室已叫诊 还能取消预约问题 Select nvl(max(hzzt00),0) into ls_hzzt00 from SF_YYBRB0 a, sf_hzbr00 b where a.YYID00 = PYYID00 and a.brid00 = b.brid00 and a.ghhbid = b.yspbid and b.hzzt00 = '4'; if ls_hzzt00='4' then verrsql:='所选择的预约ID='||PYYID00||'已叫诊,无法再取消预约,请联系医生诊结后重新预约！'; raise E_SJCF; end if; verrsql:='预约变更业务处理，修改表YS_HBHZXH据中BRID00，产生错误，请检查！'; --if (v_yxtqgh<>'M') or (v_yxtqgh='M' and vSFDTYY='1') then if (v_yxtqgh<>'M') then --update YS_HBHZXH set BRID00=null,YYID00=null,BRXM00=null,YYBZ00=decode(YYBZ00,'6',decode(ls_YYHYGLMS,'1','1','6'),vYYBZ00) where GHHBID=PGHHBID and HZXH00=PYYH000; --update YS_HBHZXH set BRID00=null,YYID00=null,BRXM00=null,YYBZ00=decode(YYBZ00,'6','6',vYYBZ00) where YYID00=PYYID00; --2017.05.24 --update YS_HBHZXH set BRID00=null,YYID00=null,BRXM00=null,YYBZ00=decode(YYBZ00,'6',decode(ls_YYHYGLMS,'1','1','6'),vYYBZ00) where GHHBID=PGHHBID and YYID00=PYYID00; --2018.01.15 update YS_HBHZXH set BRID00=null,YYID00=null,BRXM00=null,YYBZ00=decode(YYBZ00,'6','1',vYYBZ00) where GHHBID=Temp_GHHBID and HZXH00=Temp_yyh; else select nvl(max(ID0000),0) into vID0000 from YS_HBHZXH where GHHBID=Temp_GHHBID and YYBZ00 in ('3','4','5','6','7') and YXBZ00='1' and GHH000 is null and substrb(ls_YYSD00,1,5)=substrb(HZSJ00,1,5); if vID0000 = 0 then verrsql:='所选择的预约ID='||PYYID00||'无法取消预约！'; raise E_SJCF; end if; update YS_HBHZXH set BRID00=null,YYID00=null,BRXM00=null,YYBZ00=vYYBZ00 where GHHBID=Temp_GHHBID and ID0000=vID0000 and YYBZ00 in ('3','4','5','6','7') and YXBZ00='1' and GHH000 is null; if sql%notfound then raise no_data_found; end if; update sf_ysghhb set YYXHS0=YYXHS0-1,YYXHMX=SF_YY_YYXHMX(YYXHMX,GHQSSJ,ls_YYSD00,-1),YYSL00=YYSL00-1 where ghhbid = Temp_GHHBID; end if; if vDJH000>0 then --预约时扣费 --获取单据号 select SQ_SF_BRJFB0_DJH000.nextval into ls_WJSDJH from dual; --添加费用信息明细表的冲销记录 verrsql:='预约冲销业务处理，修改表F_FYMX00数据，产生错误，请检查！'; Insert into SF_FYMX00(MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00,JMJE00,JMSHR0,JMYY00, CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,KDKS00,KDYS00,KDBQ00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,CXBZ00,ZFBL00) select SQ_SF_FYMX00_MXID00.nextval,ls_WJSDJH,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,-XMSL00,-HJJE00,-ZFJE00,-GFJE00,-JZJE00,-JMJE00,JMSHR0,JMYY00, vDJRQ00,vDJSJ00,vDJRQ00,vDJSJ00,KDKS00,KDYS00,KDBQ00,ZXKS00,ZXYS00,YSKS00,SFYDJ0,'*',ZFBL00 from SF_FYMX00 where DJH000=vDJH000 and cxbz00='Z'; verrsql:='预约冲销业务处理，修改表SF_BRFY00数据，产生错误，请检查！'; Insert into SF_BRFY00(DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00, CZRQ00,CZSJ00,CZY000,CZYXM0,CZYKS0,JZDH00,CXDJH0,CXPJR0,CXYY00) select ls_WJSDJH,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00,-HJJE00,-ZFJE00,-JZJE00,-GFJE00,0, vDJRQ00,vDJSJ00,PYYYGBH,vYYYGXM,PCZYBM0,decode(JZDH00,0,0,JZDH00),DJH000,vYYYGXM,'预约冲销' from SF_BRFY00 where DJH000=vDJH000; --以下两句的位置开始到这,Modified on 2002-12-24 --本句之前曾用EXISTS,但速度很慢,又改成用IN verrsql:='预约冲销业务处理，修改表SF_FYMX00中CXBZ00数据，产生错误，请检查！'; Update SF_FYMX00 A Set CXBZ00 = '+' where A.DJH000=vDJH000; --in (select DJH000 from SF_BRFY00 where MZH000 = 0 and CXDJH0+0 = 0 );--DJH000 = ls_OLDDJH; --已被冲销 verrsql:='预约冲销业务处理，修改表SF_BRFY00中的CXDJH0数据，产生错误，请检查！'; --2.2.1、Update 旧的费用明细信息记录，SF_BRFY00表中旧的费用记录不做修改 Update SF_BRFY00 Set CXDJH0 = ls_WJSDJH, --这样可以保证只有一条有效的记录 CXPJR0 = vYYYGXM, CXYY00 = '预约冲销' where DJH000=vDJH000 and HJJE00 > 0; -- where MZH000 = as_GHH000 and HJJE00 > 0; --DJH000 = ls_OLDDJH end if; insert into SF_YYZJB0(ID0000,GHHBID,YYID00,HZID00,CZLX00,CZY000,CZYKS0,CZRQ00,CZSJ00) select SQ_SF_YYZJB0_ID0000.nextval,PGHHBID,PYYID00,null,'2',PYYYGBH,PCZYBM0,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') from dual; --预约短信内容 begin Select a.GHKSBH,b.BMMC00,a.GHYSBH,decode(a.GHYSBH,0,b.BMMC00,c.ZWXM00),a.GHLBBH,a.TZBZ00,a.SFZJGH,a.SFJZJH,a.GHMZSJ into vYYKSBH,vYYKSMC,vYYYSBH,vYYYSXM,vGHLB00,vTZBZ00,vSFZJGH,vSFJZJH,vYYRQ00 from SF_YSGHHB a,BM_BMBM00 b,BM_YGBM00 c where a.GHHBID=PGHHBID and a.GHKSBH=b.BMBH00 and a.GHYSBH=c.YGBH00(+); exception when others then verrsql:='取排班信息出现错误，传入的GHHBID='||to_char(PGHHBID)||'没有取到数据!'; raise no_data_found; end; if (YY_YYSFDXTZ='Y') and (PBRXM00 is not null) and (PBRDH00 is not null) and (nvl(instrb(YY_SFSQYYFCJMC,'/'||PWLYYMC||'/'),0)=0) then LS_DXTSXX:=PBRXM00||','||'您好！您的预约单：'||to_char(PYYID00)||'的预约信息('||PYYRQ00||' '||LS_HZQSSJ||' '||vYYKSMC||' '||vYYYSXM||'医生 '||')已成功取消'; end if; --还原取消 elsif PCZBZ00='4' then --取预约id select SQ_SF_YYBRB0_YYID00.nextval into v_yyid00 from dual; --取YYRQ00 Select SFDTYY,YYSD00 into vSFDTYY,ls_YYSD00 from SF_YYBRB0 where YYID00=PYYID00; --if v_yxtqgh='M' and vSFDTYY='1' then if v_yxtqgh='M' then update sf_ysghhb set YYXHS0=YYXHS0+1,YYXHMX=SF_YY_YYXHMX(YYXHMX,GHQSSJ,ls_YYSD00,1),YYSL00=nvl(YYSL00,0)+1 where ghhbid = PGHHBID; end if; ----写入费用明细表 if trim(YYKF)='1' then --预约时扣费 --获取单据号 select SQ_SF_BRJFB0_DJH000.nextval into ls_WJSDJH from dual; verrsql:='还原预约登记,预约费用处理，将数据插入插入表SF_FYMX00时，产生错误，请检查！'; Insert into SF_FYMX00 (MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00, XMSL00,HJJE00,ZFJE00,GFJE00,JZJE00,CZRQ00,CZSJ00, ZXRQ00,ZXSJ00,KDKS00,KDYS00,ZXKS00,ZXYS00,YSKS00, SFYDJ0,ZFBL00) select SQ_SF_FYMX00_MXID00.nextval,ls_WJSDJH,SFXMID,XMMC00,'',DW0000,ls_MXHJJE, 1,ls_MXHJJE,ls_MXZFJE,ls_MXGFJE,ls_MXJZJE,vDJRQ00,vDJSJ00, vDJRQ00,vDJSJ00,PCZYBM0,vYYYSBH,vYYKSBH,vYYYSBH,0, 0,LS_ZFBL00 from BM_YYSFXM where SFXMID = vSFXMID; --写入病人费用表 verrsql:='还原预约登记,费用处理，将数据插入插入表SF_BRFY00时，产生错误，请检查！'; insert into SF_BRFY00 (DJH000,MZH000,MZID00,JFLBID,BRID00,XM0000,BRFB00, HJJE00,ZFJE00,JZJE00,GFJE00,BRYE00,CZRQ00,CZSJ00, CZY000,CZYXM0,CZYKS0,JZDH00) select ls_WJSDJH,0,0,1,PBRID00,PBRXM00,LS_FBBH00, HJJE00,HJJE00,JZJE00,GFJE00,0,vDJRQ00,vDJSJ00, PYYYGBH,vYYYGXM,PCZYBM0,0 from SF_FYMX00 where DJH000=ls_WJSDJH ; end if; verrsql:='还原预约登记将数据插入插入表SF_YYBRB0时，产生错误，请检查！'; insert into SF_YYBRB0( YYID00,JZID00,GHHBID,YYH000,BRID00,BRXM00,BRDH00,KMH000, ICKH00,YYKSBH,YYKSMC,YYYSBH,YYYSXM,YYLB00,YYRQ00, YYJSSJ,YYZT00,YYYGBH,YYYGXM,DJH000,GHLB00,DJRQ00,DJSJ00,CZYBM0,BZ0000,SFZH00,WLYYID,YSPBID,YYSD00,SFDTYY,SFCZ00, SFSH00,SFSQZZ,WLYYMC) select v_yyid00,0,GHHBID,YYH000,BRID00,BRXM00,BRDH00,KMH000, ICKH00,YYKSBH,YYKSMC,YYYSBH,YYYSXM,PYYLB00,YYRQ00, YYJSSJ,PYYZT00,PYYYGBH,vYYYGXM,ls_WJSDJH,GHLB00,vDJRQ00,vDJSJ00,PCZYBM0, BZ0000||' 被还原YYID'||PYYID00,SFZH00,WLYYID,YSPBID,YYSD00,SFDTYY,SFCZ00,SFSH00,SFSQZZ,PWLYYMC from SF_YYBRB0 where YYID00=PYYID00; --if (v_yxtqgh<>'M') or (v_yxtqgh='M' and PYYRQ00=vDJRQ00) then if (v_yxtqgh<>'M') then --更新医生号表侯诊序号 update YS_HBHZXH set BRID00=PBRID00,YYID00=V_YYID00,BRXM00=PBRXM00,YYBZ00=decode(YYBZ00,'6','6',vYYBZ00) where GHHBID=PGHHBID and HZXH00=PYYH000; else select nvl(min(ID0000),0) into vID0000 from YS_HBHZXH where GHHBID=PGHHBID and substrb(hzsj00,1,5)=substrb(ls_YYSD00,1,5) and YYBZ00='1' and YXBZ00='1' and GHH000 is NULL; if vID0000 = 0 then verrsql:='所选择的预约时段'||ls_YYSD00||'已全部预约完毕，无法还原取消！'; raise E_SJCF; end if; update YS_HBHZXH set BRID00=PBRID00,YYID00=PYYID00,BRXM00=PBRXM00,YYBZ00=vYYBZ00 where GHHBID=PGHHBID and ID0000=vID0000 and YYBZ00='1' and YXBZ00='1' and GHH000 is NULL; if sql%notfound then raise no_data_found; end if; end if; --更新病人电话号码 update BM_BRXXB0 set BRLXDH=nvl(PBRDH00,BRLXDH) where BRID00=PBRID00; --将原有的记录标记为已还原 update SF_YYBRB0 set BZ0000=BZ0000||' 已还原' where YYID00=PYYID00; end if; if PCZBZ00 in ('1','2','4') then --if ((v_yxtqgh<>'M') or (v_yxtqgh='M' and PYYRQ00=vDJRQ00)) then if (v_yxtqgh<>'M') then select count(*) into V_COUNT0 from SF_YYBRB0 where YYRQ00>=vDJRQ00 and GHHBID=PGHHBID and YYH000=PYYH000 and YYZT00<>'2'; if V_COUNT0>1 then if PCZBZ00 in ('1','2') then verrsql:='所选择的预约号已被占用，请选择其它号！'; else verrsql:='该预约信息的预约号已被占用，不能还原！'; end if; raise E_SJCF; end if; end if; end if; verrsql:=''; if instrb(YY_SFSQYYFCJMC,','||PWLYYMC||',')>0 then --指定某个网络厂家不收预约费 update SF_YYBRB0 set DJH000=1 where YYID00=PYYID00; end if; --写入短信表 if (LS_DXTSXX is not null) and (YY_YYSFDXTZ='Y') then insert into YJ_DXTZ00(DXID00,DXNRFL,DXNR00,YWRQ00,CZRQ00,CZSJ00,DH0000,JSR000,BRID00) values(SQ_YJ_DXTZ00_DXID00.nextval,'预约信息',LS_DXTSXX,vDJRQ00,vDJRQ00,vDJSJ00,PBRDH00,PBRXM00,PBRID00); end if; if PCOMMIT='Y' then commit; end if; exception when E_SJCF then RAISE_APPLICATION_ERROR(-20019,verrsql||'！*'); rollback; when no_data_found then RAISE_APPLICATION_ERROR(-20019,verrsql||'！*'||SqlErrm); rollback; when others then RAISE_APPLICATION_ERROR(-20019,verrsql||'！*'||SqlErrm); rollback; end;