create or replace PROCEDURE SD_HOSPITAL.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', --是否允许加号 PID0000 in number default 0, --号表候诊序号YS_HBHZXH.ID0000 PYWKHLX in char default '', --院外预约卡类型 0 健康卡 1 市民卡 2 医保卡 PYWKH00 in varchar2 default '0', --院外预约卡号(健康卡、市民卡、医保卡) PWLYYID in number default 0, --院外预约ID PGHCJ00 in varchar2 default '', --预约厂家名称 PBRXB00 in varchar2 default '', --病人性别 PBRCSRQ in varchar2 default '', --病人出生日期 PJHRZJH in varchar2 default '', --监护人证件号码 PJHRXM0 in varchar2 default '', --监护人姓名 PJHRSJH in varchar2 default '' --监护人手机号 ) as ls_WJSDJH number; --单据号 YYKF char(1);--预约是否扣费 1 扣费 0 不扣费 verrsql varchar2(120);--错误提示 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; ls_YWQXYYJG XT_XTCS00.VALUE0%TYPE; lv_YYID00 SF_YYBRB0.YYID00%TYPE; ls_YWQXRQ date; ls_YYSD01 SF_YYBRB0.YYSD00%type; -- MODIFICATION HISTORY -- Person Date Comments -- CSF 2009.09.07 create:创建过程预约业务登记 变更 取消 -- CSF 2009.10.20 记录备注，及修改ys_hbhzxh的YYBZ00值 -- CSF 2009.10.27 brid00=0允许修改病人的姓名和电话,保存后判断YYH000是否重复 -- CSF 2009.10.27 增加sfzh00的编辑保存 -- CSF 2009.10.27 传入变量sfzh00加默认值 -- CSF 2009.11.04 增加记录取消人 -- CSF 2009.11.10 修改预约记录时候，同时也修改ys_hzhzxh -- CSF 2009.11.25 判断选择的序号是否存在 -- CSF 2009.12.03 增加传入参数，是否提交 -- CSF 2010.01.04 取消时候按yyid00还原号表序号表 -- CSF 2010.01.11 还原取消的预约记录 -- CSF 2010.01.13 预约变更时候时候，可能号表也会发生变化，应该重新取 -- CSF 2010.01.28 加参数控制是否允许当天预约 -- CSF 2010.03.10 异常提示增加系统错误提示信息 -- CSF 2010.08.25 网络预约号不管是取消，还是变更，或是还原预约都不改变预约标志。 -- CSF 2010.09.28 错误信息处理 -- CSF 2010.10.11 网络预约记录在院内不取消，又被还原，还原时候应记录原有的WLYYID,否则导入的网络预约信息如果要修改该记录就改不成功。 -- qks 2010.11.03 广东省中医院PBZ0000='自助机预约登记'时, 更新医生号表记录 -- csf 2011.02.18 取消预约时候，sf_brfy00的czks00没有写入，现补写 -- qks 2011.02.19 处理广东省中医院当天预约以及非当天预约,增加入参PYSPBID,PYYSD00 -- chenyw 2011.03.11 区分分诊台收费 -- csf 2011.03.18 取消预约的退费不按参数控制，按原记录是否已经收费过，收过则同时退费 -- 用通用字典，字典名称为“按病人类别定义预约费优惠比例”来定义病人的优惠比例， -- 备注描述成FBBH00-YBZXLB-YBBRLB,DJ0000为优惠比例0-1之间 0为不优惠 1为100%优惠 -- qks 2011.04.07 处理广东省中医院已报到过，不算爽约 -- qks 2011.06.19 广东预约接口增加入参PQXLX00,修改YYSL00。 -- qks 2011.06.23 广东预约接口增加入参PSFZJ00,修改取消预约时的错误提示。 -- qks 2011.06.30 广东预约接口院外预约取消,只能取消自己公司的预约记录。 -- qks 2011.07.01 广东预约已停诊或加号的号表不允许再让预约;以及判断是否禁止预约加号。 -- qks 2011.07.05 广东预约:一天最多只允许预约2次;增加姓名跟电话号码判断失约次数;判断预约日期是否为对应号表的挂号日期;同个号表不允许重复预约;院外预约写入挂号厂家。 -- qks 2011.07.06 广东预约:目前不限制失约次数。 -- qks 2011.07.11 广东预约:一周看某个医生不能超过2个。 -- qks 2011.07.22 广东预约:限制停号也不允许预约 -- qks 2012.02.23 广东预约:取消时，优先取消本人预约对应的YS_HBHZXH记录。 -- qks 2012.04.18 广东预约:EM预约无法取消，提示“所选择的预约ID=？无法取消预约！” -- linzy 2013.04.06 广东预约:院外取消预约限制预约日期的前一天某一个时间点的号才允许取消 由参数YY_YWYYQXSJJG控制。by YYGH-20130321-001 -- linzy 2013.09.05 广东预约:预约取号SF_YY_YYXHMX增加参数,预约登记时更新SF_YSGHHB移动到最尾 解决锁号表问题 MZSF-20130830-001 -- linzy 2013.09.06 广东预约:病人身份证号为空时自动更新病人身份证号 by YYGH-20130613-001 -- linzy 2014.01.21 广东预约:采用广州集约式锁号后再进行预约时YS_HBHZXH的ID0000为锁号时的ID by YYGH-20130809-002 -- linzy 2014.02.28 SF_YYBRB0增加监护人姓名、监护人证件号、监护人手机号 YYGH-20140224-004 -- linzy 2014.03.07 增加持卡病人预约本院填写身份证自动更新到BM_BRXXB0 BY YYGH-20140228-001 -- dsm 2015.08.11 加号另取一个时段 -- dsm 2015.08.17 第1个加号substr(Vtmpstr,7,5)||':00'-->substr(Vtmpstr,1,5)||':00' for MZHS-20150817-001 ---piw 14岁以上病人不可预约儿科号 -- dsm 2016.04.08 提前撤销的号源不马上释放,由job调用SP_SF_SFTQTHHY统一释放 for MZSF-20160408-002 ---piw 20160602 当天预约已过时的可以预约加号 -- --------- ---------- -------------------------------------------------------------------------------------------------------------------- begin --取参数 begin select trim(VALUE0) into v_SFYYDTYY from XT_XTCS00 where NAME00='YY_SFYXDTYY'; exception when others then v_SFYYDTYY:='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; ---14岁以上病人不可预约儿科号 if PYYZT00='0' then Select b.BMMC00 into vYYKSMC from SF_YSGHHB a,BM_BMBM00 b,BM_YGBM00 c where a.GHHBID=PGHHBID and a.GHKSBH=b.BMBH00 and a.GHYSBH=c.YGBH00; if INSTR(vYYKSMC,'儿')>'0' and to_char(ADD_MONTHS(to_date(PYYRQ00,'yyyymmdd'),-168),'yyyymmdd')>PBRCSRQ then verrsql:='病人年龄大于14岁不能预约儿科号！'; raise no_data_found; end if; end if; /* if INSTR(vYYKSMC,'儿')>'0' and to_number(trim(replace(SF_YF_CSRQTONL(PBRCSRQ,PYYRQ00),'岁','')))>'14' then verrsql:='病人年龄大于14岁不能预约儿科号！'||trim(replace(SF_YF_CSRQTONL(PBRCSRQ,PYYRQ00),'岁','')); 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; --先判断去哪个收费标准 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; --linzy 取院外取消预约间隔时间(单位：小时)参数 select nvl(trim(max(VALUE0)),'N') into ls_YWQXYYJG from XT_XTCS00 where NAME00='YY_YWYYQXSJJG'; --取病人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; ls_YYSD01:=PYYSD00; --为了加号是写sf_YYBRB0 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 PYYRQ00=vDJRQ00 then -- vYYBZ00:='7'; --当天预约 -- vSFDTYY:='1';--当天预约 -- if PCZBZ00='3' then --取消 -- vYYBZ00 := '1'; -- end if; -- end if; 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,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; -- else -- begin -- Select a.YSKSBH,b.BMMC00,a.YSYGBH,c.ZWXM00,a.MZLBID into vYYKSBH,vYYKSMC,vYYYSBH,vYYYSXM,vGHLB00 -- from SF_YSPBB0 a,BM_BMBM00 b,BM_YGBM00 c -- where a.YSPBID=PYSPBID and a.YSKSBH=b.BMBH00 and a.YSYGBH=c.YGBH00; --PGHHBID -- exception -- when others then -- verrsql:='取排班信息出现错误，传入的YSPBID='||to_char(PYSPBID)||'没有取到数据!'; -- raise no_data_found; -- end; end if; ls_WJSDJH:=0; --登记 if PCZBZ00='1' then --更新医生号表记录 vYYJSSJ := PYYJSSJ; vGHCJ00 := ''; -- if v_yxtqgh='M' and (PBZ0000='自助机预约登记' or nvl(PICKH00,'0')<>'0') then -- select count(1) into V_COUNT0 from sf_yybrb0 where ICKH00=PICKH00 and yyzt00 in ('0','1') and BDRQ00 IS NULL; -- if V_COUNT0>2 then -- verrsql := '您已经有3次预约未及时进行就诊,抱歉！不允许再进行预约登记！'; -- raise E_SJCF; -- end if; -- end if; --if v_yxtqgh='M' and vSFDTYY='1' then if v_yxtqgh='M' then if PBZ0000='院外预约' then if PYYYGBH in (61655,61346) then verrsql := '用户ID有误,不允许预约!'; raise E_SJCF; end if; --预约厂家 if PGHCJ00 is null then vGHCJ00:=vYYYGXM; else vGHCJ00:=PGHCJ00; end if; end if; --判断预约日期是否为对应号表的挂号日期 if vYYRQ00<>PYYRQ00 then verrsql := '该病人预约日期'||PYYRQ00||'跟号表的就诊日期'||vYYRQ00||'不一致,请您重新选择!'; raise E_SJCF; end if; --同个号表不允许重复预约 ---- 20160602 当天预约已过时的可以预约加号 if PYYRQ00=to_char(sysdate,'yyyymmdd') then select count(1) into V_COUNT0 from sf_yybrb0 where GHHBID=PGHHBID and substr(yysd00,-5)>to_char(sysdate,'hh24:mi') 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; else 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; 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; --增加院外预约4点之后无法预约明天的号源 linzy 2013.03.21 /* if PYYLB00='2' and ls_YWQXYYJG<>'N' then select ceil((to_date(PYYRQ00||' '||ls_YWQXYYJG||',YYYYMMDD HH24:MI:SS')-1-sysdate)*24) into V_COUNT0 from dual; if V_COUNT0<=0 then verrsql:='超过'||ls_YWQXYYJG||'规定时间，无法预约明天的号源！'; raise E_SJCF; end if; 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 a 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 ---20110721 vTZBZ00='1' 修改成 vTZBZ00<>'0' 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,PGHHBID),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,YSPBID,YYSD00,SFDTYY,GHCJ00,WLYYID, BRXB00,BRCSRQ,JHRZJH,JHRXM0,JHRSJH) 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,PWLYYID, PBRXB00,PBRCSRQ,PJHRZJH,PJHRXM0,PJHRSJH); --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 --未有锁号记录 linzy if PID0000=0 then 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 nvl(max(HZXH00),300) into Vmaxhzh from YS_HBHZXH where GHHBID=PGHHBID and SFZJ00='1'; select max(HZDXH0)+1 into V_COUNT0 from YS_HBHZXH where GHHBID=PGHHBID and SFZJ00='0'; select SF_SF_GETLASTSD(PGHHBID) into Vtmpstr from dual; 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); if Vmaxhzh=300 then ls_YYSD01:=Vtmpstr; update SF_YYBRB0 set YYSD00=ls_YYSD01,YYJSSJ=substr(Vtmpstr,1,5)||':00' where YYID00=PYYID00; end if; 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; else --处理锁号记录 linzy update YS_HBHZXH set BRID00=PBRID00,YYID00=PYYID00,BRXM00=PBRXM00,YYBZ00=vYYBZ00 where GHHBID=PGHHBID and ID0000=PID0000; if sql%notfound then raise no_data_found; end if; end if; end if; --更新病人电话号码 update BM_BRXXB0 set BRDH00=nvl(PBRDH00,BRDH00) where BRID00=PBRID00; if v_yxtqgh='M' then --未锁号则更改预约数及预约明细 linzy if PID0000=0 then update SF_YSGHHB set YYXHS0=YYXHS0+1,YYXHMX=SF_YY_YYXHMX(YYXHMX,GHQSSJ,ls_YYSD01,1,PGHHBID),YYSL00=nvl(YYSL00,0)+1 where ghhbid = PGHHBID; end if; end if; --针对院外预约病人身份证呈为空时更新预约填写的身份证号信息 2014.03.07 放开只更新来自院外预约的限制 by YYGH-20140228-001 if nvl(PSFZH00,' ')<>' ' then update BM_BRXXB0 set BRZJBH=PSFZH00,BRZJLX='身份证' where BRID00=PBRID00 and (trim(BRZJBH) is null or instr('0123456789',substr(BRZJBH,1,1))<=0); 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, SFZH00=PSFZH00 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 BRDH00=PBRDH00 where BRID00=PBRID00; --取消 elsif PCZBZ00='3' then verrsql:='网络预约ID='||PWLYYID||'不存在，请检查！'; lv_YYID00 := PYYID00; if PWLYYID<>0 then select YYID00 into lv_YYID00 from SF_YYBRB0 where WLYYID=PWLYYID and YYZT00='0'; end if; verrsql:='预约ID='||lv_YYID00||'不存在，请检查！'; --取DJH000 Select DJH000,BZ0000,SFDTYY,GHHBID,YYSD00,YYZT00,YYRQ00,YYJSSJ into vDJH000,ls_bz0000,vSFDTYY,Temp_GHHBID,ls_YYSD00,ls_YYZT00,vYYRQ00,vYYJSSJ from SF_YYBRB0 where YYID00=lv_YYID00 and (nvl(PQXLX00,'0')<>'2' or PQXLX00='2' and YYYGBH=PYYYGBH); --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=YYSL00-1 where ghhbid = Temp_GHHBID; -- end if; if ls_YYZT00 = '2' then verrsql:='所选择的预约ID='||lv_YYID00||'已取消,无法再取消预约！'; raise E_SJCF; elsif ls_YYZT00 = '3'then verrsql:='所选择的预约ID='||lv_YYID00||'已就诊,无法再取消预约！'; raise E_SJCF; end if; -- linzy 指定间隔多少小时后院外预约无法取消 if ls_YWQXYYJG<>'N' and PQXLX00='2' then --begin -- select ceil(((sysdate+to_number(trim(ls_YWQXYYJG))/24)-to_date(vYYRQ00||' '||vYYJSSJ,'YYYYMMDD HH24:MI:SS'))*24) into V_COUNT0 from dual; --exception --when others then -- verrsql:='取消预约参数设置错误！'||sqlerrm; -- raise E_SJCF; --end; --截止预约前一天下午4点后无法取消预约 select ceil((to_date(vYYRQ00||' '||ls_YWQXYYJG,'YYYYMMDD HH24:MI:SS')-1-sysdate)*24) into V_COUNT0 from dual; if V_COUNT0<=0 then verrsql:='院外取消预约截止为前一天的'||ls_YWQXYYJG||'，请到医院办理预约取消！'; raise E_SJCF; end if; end if; --修改预约状态 verrsql:='预约取消业务处理，修改表SF_YYBRB0数据，产生错误，请检查！'; update SF_YYBRB0 set YYZT00='2',QXRQ00=vDJRQ00,QXSJ00=vDJSJ00,QXR000=PYYYGBH,BZ0000=PBZ0000,QXLX00=PQXLX00 where YYID00=lv_YYID00 and (YYZT00='1' or YYZT00='0'); if sql%notfound 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','6',vYYBZ00),YXBZ00=decode(PQXLX00,'2','0','1') where GHHBID=PGHHBID and HZXH00=PYYH000; update YS_HBHZXH set BRID00=null,YYID00=null,BRXM00=null,YYBZ00=decode(YYBZ00,'6','6',vYYBZ00),YXBZ00=decode(PQXLX00,'2','0','1') where YYID00=lv_YYID00; if vYYRQ00>to_char(sysdate,'YYYYMMDD') and nvl(PQXLX00,'0')='2' then update YS_HBHZXH a set YXBZ00='0',ZY0000='提前撤销预约' where GHHBID=PGHHBID and HZXH00=PYYH000 and exists(select 1 from SF_YSGHHB where GHHBID=a.GHHBID and TZBZ00='0'); end if; else --add by qks 2012.02.23 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 YYID00=lv_YYID00; if vID0000=0 then 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); end if; -- if vID0000 = 0 then -- verrsql:='所选择的预约ID='||PYYID00||'无法取消预约！'; -- raise E_SJCF; -- end if; if vID0000<>0 then update YS_HBHZXH set BRID00=null,YYID00=null,BRXM00=null,YYBZ00=vYYBZ00,ZY0000=decode(trim(ZY0000),'预约锁号',null,ZY0000) 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; if vYYRQ00>to_char(sysdate,'YYYYMMDD') and nvl(PQXLX00,'0')='2' then update YS_HBHZXH a set YXBZ00='0',ZY0000='提前撤销预约' where GHHBID=Temp_GHHBID and ID0000=vID0000 and exists(select 1 from SF_YSGHHB where GHHBID=a.GHHBID and TZBZ00='0'); end if; end if; update sf_ysghhb set YYXHS0=YYXHS0-1,YYXHMX=SF_YY_YYXHMX(YYXHMX,GHQSSJ,ls_YYSD00,-1,Temp_GHHBID),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; --还原取消 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,PGHHBID),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) 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 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 BRDH00=nvl(PBRDH00,BRDH00) 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; -- elsif (v_yxtqgh='M' and PYYRQ00<>vDJRQ00) then -- select count(1) into V_COUNT0 from sf_yybrb0 where yyrq00=Pyyrq00 and YSPBID=PYSPBID and YYZT00<>'2' and yyjssj=PYYJSSJ and nvl(wlyyid,0)=0; --PGHHBID -- select ceil((to_date(substrb(PYYJSSJ,1,5),'hh24:mi:ss')-to_date(b.kssj00,'hh24:mi:ss'))*24)+1,a.YNYYMX -- into V_sdxs00,V_ynyymx from sf_yspbb0 a,bm_mzsjb0 b where a.yspbsj=b.MZSJ00 and a.YSPBID=PYSPBID; -- select SF_YS_HZDYYS(V_sdxs00,V_ynyymx)- V_COUNT0 into V_COUNT0 from dual; -- -- if V_COUNT0<0 then -- if PCZBZ00 in ('1','2') then -- verrsql:='所选择的预约时段已全部预约完毕，请选择其它时段！'; -- else -- verrsql:='该预约时段已全部预约完毕，不能还原！'; -- end if; -- raise E_SJCF; -- end if; end if; end if; verrsql:=''; 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; /