prompt 14、门诊医生挂号号表相关的存储过程 create or replace procedure SP_SF_YSGHHB ( as_KSRQ00 in char, --生成号表的开始日期 as_JSRQ00 in char, --生成号表的结束日期 as_YYID00 in char default '%', --医院ID as_YHMSG0 out varchar, --存储过程提示的错误信息 as_SYSMSG out varchar, --系统提示的错误信息 as_YSYGBH in number default 99999, --只转某个医生的号表 as_YSKSBH in number default 0 --只转某个科室的号表 ) -- MODIFICATION HISTORY -- Person Date Comments -- qks 2013.02.24 表SF_YSGHHB增加字段YNBLMX -- zhanghr 2013.02.24 整理 -- qks 2013.03.06 注释"删除停诊记录",改为 排班表停诊，号表也要跟着停诊。 -- zhangwz 2014.02.24 厦门口腔医院科室不分YYID00,排班表有分YYID00，故SF_YSGHHB增加YYID00数据。 -- zhangwz 2014.03.07 排班表有牙椅名称YYMC00数据，故SF_YSGHHB增加YYMC00数据。 -- zhangwz 2014.03.17 解决排班表update的时候有处理SF_YSGHHB.GHLBBH没有处理SF_YSGHHB.GHLBMC的BUG。 -- zhangwz 2014.04.01 号表变更or增加的时候写入中间表数据 SF_YYZJB0 用于后续相关数据到前置机的功能。 -- zhangwz 2014.06.16 排班表增加YSYGBH=0的科室排班数据，过程进行相应的修改 -- zhangwz 2014.06.23 厦门市口腔医院YSYGBH=0的排班数据为PBLX00='1'的科室排班 -- zhangwz 2014.06.27 已经停诊的挂号号表在重新生成的时候根据参数控制是否和排班表的停诊标识是一样 -- liuj 2014.07.26 增加传参科室号，可按科室生成号表 -- qks 2016.01.27 排班表增加字段SFLSPB;临时排班只允许生成7天内的号表记录 by MZHS9-20160201-001 -- qks 2016.02.22 临时排班不限制只生成7天内的号表记录 by MZHS9-20160223-001 -- qks 2016.02.25 会生成交叉的重复号表记录 by -- qks 2016.03.24 参数SF_YSHBTZZTGXFS=2：要求单个医生排班生成号表，要把对应的停用号表自动启用，如果是按全部生成，不选单个医生，那么停诊的号表不启用。 by MZHS9-20160324-001 -- qks 2016.06.06 as_YSKSBH=-99999表示SP_SF_YSGHHB_JOB调用,跳过有些异常,不予提示 by MZHS9-20160607-001 -- qks 2016.07.29 as_YSKSBH=-99999:已经开始挂号，不修改SF_YSGHHB.GHXHS0,YYXHS0,YYXHMX; by MZHS9-20160729-001 -- jlg 2018.05.11 处理未明确定义列的提示问题 GGMK9-20180511-001 -- liwm 2018.06.07 门诊收费存储过程统一添加调用参数日志 for MZSF9-20180604-003 -- qks 2019.12.17 省人民门诊导诊排班冬夏令时调整; for MZHS9-20191218-001 -- qks 2020.02.25 省人民：新增参数MZHS_YSPBXGGHHB,控制是否修改已生成的挂号号表; for MZHS9-20200227-001 -- liwm 2022.02.15 代景淮提交，在BM_MZSJB0查找不到数据时，优化提示语。 for MZSF9-20220215-001 -- qks 2022.04.20 省人民现场BM_MZSJMX.YXQSRQ,YXJSRQ字段长度为char(8),增加trim去空处理。 for MZSF9-20220418-004 -- chenHeyi 2023.03.02 增加参数MZHS_HBTKSTSJTS，同时段同诊室已经有排班是否提示并中止，0否1是；默认0 MZHS9-20230215-002 -- chenHeyi 2023.04.24 排班表停诊的医师，不执行同时段同诊室已经有排班的判断 as ls_KSRQ00 char(8); --生成号表的临时开始日期; ls_XQ0000 char(1); --存放星期几 ld_LOOP00 number(10); --循环次数 ls_GHMZSJ char(8); --挂号门诊时间 ls_COUNT0 number(5); ld_XZPBTS number(5); ls_YGBH00 number(5); ls_YHMSG0 varchar2(400); ls_SYSMSG varchar2(400); lv_ZXCGBZ varchar2(100); ls_DGYSBZ varchar2(1); lv_SF_YSHBTZZTGXFS XT_XTCS00.VALUE0%type; Pxxxx00 XT_PROLOG.CZNR00%type; --日志操作内容 Ecustom exception; ls_YSPBB0 SF_YSPBB0%rowtype; ls_BMMC00 BM_BMBM00.BMMC00%type; ls_ZWXM00 BM_YGBM00.ZWXM00%type; ls_KSSJ00 BM_MZSJB0.KSSJ00%type; ls_JSSJ00 BM_MZSJB0.JSSJ00%type; ls_GHHBID SF_YSGHHB.GHHBID%type; --GHHBID VS_PROCESS XT_XTRZ00.JCID00%type; ls_MZSJMX number(5); ls_PBXGHB XT_XTCS00.VALUE0%type; --系统参数MZHS_YSPBXGGHHB ls_SFGHHB number(5); --是否存挂号号表 ls_MZHS_HBTKSTSJTS XT_XTCS00.VALUE0%type;--参数MZHS_HBTKSTSJTS ls_temp00 varchar2(200); cursor CUR_SF_YSPBB0_XQ0000 is --排班表停诊，号表也要跟着停诊 select * from SF_YSPBB0 where YSPBXQ = ls_XQ0000 --and tzbz00='0' and YSKSBH in (select BMBH00 from BM_BMBM00 where YYID00 like nvl(as_YYID00,'%')||'%') --and PBLX00='0' and (YSYGBH=as_YSYGBH or as_YSYGBH=99999) and (YSKSBH=as_YSKSBH or as_YSKSBH=0 or as_YSKSBH=-99999) order by YSKSBH,YSYGBH,YSPBSJ; Vxxxlog varchar2(2000); begin Vxxxlog := substr('AS_KSRQ00:'||nvl(to_char(AS_KSRQ00), 'null')||','||'AS_JSRQ00:'||nvl(to_char(AS_JSRQ00), 'null')||','||'AS_YYID00:'||nvl(to_char(AS_YYID00), 'null')||','||'AS_YSYGBH:'||nvl(to_char(AS_YSYGBH), 'null')||','||'AS_YSKSBH:'||nvl(to_char(AS_YSKSBH), 'null'),1,2000); Pxxxx00 := ''; SP_TransLog(sysdate,'SP_SF_YSGHHB',0,0,Vxxxlog); begin select value0 into lv_SF_YSHBTZZTGXFS from XT_XTCS00 where name00='SF_YSHBTZZTGXFS'; exception when others then lv_SF_YSHBTZZTGXFS:='0'; end; --是否允许根据医师排班表,修改已生成的挂号号表：0不允许,1允许,默认为1 select nvl(max(trim(VALUE0)),'1') into ls_PBXGHB from XT_XTCS00 where NAME00='MZHS_YSPBXGGHHB'; select nvl(max(trim(VALUE0)),'0') into ls_MZHS_HBTKSTSJTS from XT_XTCS00 where name00 = 'MZHS_HBTKSTSJTS'; if as_YSYGBH <> 99999 and as_YSKSBH<>0 then ls_DGYSBZ := '1'; else ls_DGYSBZ := '0'; end if; if as_KSRQ00>as_JSRQ00 then as_YHMSG0:='日期无效,开始日期'''||as_KSRQ00||'''不能大于截至日期'''||as_JSRQ00||'''!'; as_SYSMSG:=SQLERRM; rollback; return; end if; select to_number(nvl(max(VALUE0),'0')) into ld_XZPBTS from XT_XTCS00 where name00='MZSF_XZTQPBTS';--限制提前排班天数 --if ld_XZPBTS>0 and as_YSYGBH=99999 and as_JSRQ00>to_char(sysdate+ld_XZPBTS,'YYYYMMDD') then if ld_XZPBTS>0 and as_JSRQ00>to_char(sysdate+ld_XZPBTS,'YYYYMMDD') then as_YHMSG0:='最多只能提前'||to_char(ld_XZPBTS)||'天进行排班!'; as_SYSMSG:=SQLERRM; rollback; return; end if; ls_KSRQ00 := as_KSRQ00; --1、删除旧的挂号号表的记录,修改成Update的语句 --Delete from SF_YSGHHB where GHMZSJ >= as_KSRQ00 and GHMZSJ <= as_JSRQ00; --2、生成新的挂号号表的记录; ld_LOOP00 := 1; ls_XQ0000 := to_char(to_Date(ls_KSRQ00,'YYYY.MM.DD'),'D'); ls_GHMZSJ := ls_KSRQ00; while ld_loop00 <= Round(to_date(as_JSRQ00,'YYYY.MM.DD')-to_date(as_KSRQ00,'YYYY.MM.DD'))+1 loop open CUR_SF_YSPBB0_XQ0000; loop fetch CUR_SF_YSPBB0_XQ0000 into ls_YSPBB0; exit when CUR_SF_YSPBB0_XQ0000%notfound; --2019.12.17 门诊导诊排班冬夏令时调整 select count(1) into ls_MZSJMX from BM_MZSJMX where MZSJ00 = ls_YSPBB0.YSPBSJ and SFYX00='Y' and trim(YXQSRQ)<=substrb(ls_GHMZSJ,5,4) and trim(YXJSRQ)>=substrb(ls_GHMZSJ,5,4); if ls_MZSJMX = 1 then select KSSJ00,JSSJ00 into ls_KSSJ00,ls_JSSJ00 from BM_MZSJMX where MZSJ00 = ls_YSPBB0.YSPBSJ and SFYX00='Y' and trim(YXQSRQ)<=substrb(ls_GHMZSJ,5,4) and trim(YXJSRQ)>=substrb(ls_GHMZSJ,5,4); else begin select KSSJ00,JSSJ00 into ls_KSSJ00,ls_JSSJ00 from BM_MZSJB0 where MZSJ00 = ls_YSPBB0.YSPBSJ; exception when NO_DATA_FOUND then ls_YHMSG0:='门诊字典时间表找不到'||ls_YSPBB0.YSPBSJ||'配置信息，请在门诊排班时间表维护好！'; as_SYSMSG:=sqlerrm; raise Ecustom; when others then ls_YHMSG0:='门诊字典时间表配置有误，请在门诊排班时间表维护好！'; as_SYSMSG:=sqlerrm; raise Ecustom; end; end if; --ls_COUNT0:=0; select count(1) into ls_COUNT0 from SF_YSGHHB a where (GHYGHS>0 --已经挂号 or exists (select 1 fROM YS_HBHZXH where GHHBID=a.GHHBID and BRID00 is not null) or ls_PBXGHB='0' ) and GHYSBH = ls_YSPBB0.YSYGBH and GHMZSJ = ls_GHMZSJ and GHQSSJ = ls_KSSJ00 and GHJSSJ = ls_JSSJ00 and ((GHYSBH=0 and YYID00 = ls_YSPBB0.YYID00 and ls_YSPBB0.YYID00 is not null and GHKSBH=ls_YSPBB0.YSKSBH) or (GHYSBH<>0)); if ls_COUNT0>0 then --已经开始挂号 update SF_YSGHHB SET TZQRBZ = 'N', TZBZ00 = decode(lv_SF_YSHBTZZTGXFS, '1',decode(TZBZ00,'1',TZBZ00,ls_YSPBB0.TZBZ00), '2',decode(ls_DGYSBZ,'1',ls_YSPBB0.TZBZ00,TZBZ00), ls_YSPBB0.TZBZ00), YYID00 = ls_YSPBB0.YYID00, --医院ID YYMC00 = ls_YSPBB0.YYMC00, --牙椅名称 SFLSPB = decode(ls_PBXGHB,'0',SFLSPB,decode(SFLSPB,'2','2',ls_YSPBB0.SFLSPB)) where GHYSBH = ls_YSPBB0.YSYGBH --挂号医生编号 and GHMZSJ = ls_GHMZSJ --门诊时间(YYYYMMDD) and GHQSSJ = ls_KSSJ00 --起始时间(00:00:00) and GHJSSJ = ls_JSSJ00 --截止时间(00:00:00) and ((GHYSBH=0 and YYID00 = ls_YSPBB0.YYID00 and ls_YSPBB0.YYID00 is not null and GHKSBH=ls_YSPBB0.YSKSBH) or (GHYSBH<>0)) ; if as_YYID00='%' and ls_PBXGHB='1' then --若号表已经被使用，除广东省中医院之外其它医院允许修改GHXHS0 --挂号限号数 YYXHS0 --预约限号数 YYXHMX --预约限号明细 三个字段 update SF_YSGHHB set GHXHS0 = decode(as_YSKSBH,-99999,GHXHS0,ls_YSPBB0.YSPBXH), --挂号限号数 YYXHS0 = decode(as_YSKSBH,-99999,YYXHS0,ls_YSPBB0.YSYYH0), --预约限号数 YYXHMX = decode(as_YSKSBH,-99999,YYXHMX,ls_YSPBB0.YYXHMX) --预约限号明细 --2020.02.25 注释掉 --TZBZ00 = decode(lv_SF_YSHBTZZTGXFS,'1',decode(TZBZ00,'1',TZBZ00,ls_YSPBB0.TZBZ00),'2',decode(ls_DGYSBZ,'1',ls_YSPBB0.TZBZ00,TZBZ00),ls_YSPBB0.TZBZ00), --SFLSPB = decode(SFLSPB,'2','2',ls_YSPBB0.SFLSPB) where GHYSBH = ls_YSPBB0.YSYGBH and --挂号医生编号 GHMZSJ = ls_GHMZSJ and --门诊时间(YYYYMMDD) GHQSSJ = ls_KSSJ00 and --起始时间(00:00:00) GHJSSJ = ls_JSSJ00 and --截止时间(00:00:00) ((GHYSBH=0 and YYID00 = ls_YSPBB0.YYID00 and ls_YSPBB0.YYID00 is not null and GHKSBH=ls_YSPBB0.YSKSBH) or (GHYSBH<>0)); end if; begin select GHHBID into ls_GHHBID from SF_YSGHHB where GHYSBH = ls_YSPBB0.YSYGBH and --挂号医生编号 GHMZSJ = ls_GHMZSJ and --门诊时间(YYYYMMDD) GHQSSJ = ls_KSSJ00 and --起始时间(00:00:00) GHJSSJ = ls_JSSJ00 and ((GHYSBH=0 and YYID00 = ls_YSPBB0.YYID00 and ls_YSPBB0.YYID00 is not null and GHKSBH=ls_YSPBB0.YSKSBH) or (GHYSBH<>0)); exception when others then ls_GHHBID:=0; end; if ls_GHHBID<>0 then insert into SF_YYZJB0(ID0000,GHHBID,CZLX00,CZY000,CZYKS0,CZRQ00,CZSJ00) select SQ_SF_YYZJB0_ID0000.nextval,ls_GHHBID,'1',null,null,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') from dual; end if; else --elsif ls_COUNT0=0 then --未开始挂号 ls_GHHBID:=0; if ls_PBXGHB = '1' then update SF_YSGHHB SET GHXHS0 = ls_YSPBB0.YSPBXH, --挂号限号数 YYXHS0 = ls_YSPBB0.YSYYH0, --预约限号数 YYXHMX = ls_YSPBB0.YYXHMX, --预约限号明细 GHHZCK = ls_YSPBB0.CKBH00, --医生就诊窗口 GHLBBH = ls_YSPBB0.MZLBID, --挂号类别编号 GHLBMC = ls_YSPBB0.YSMZLB, --挂号类别名称 GHKSBH = ls_YSPBB0.YSKSBH, --挂号科室编号 GHZKBH = ls_YSPBB0.YSZKBH, --专科名称编号 ZZGHBZ = ls_YSPBB0.ZZGHBZ, --自助挂号标志 MRXHS0 = ls_YSPBB0.YSPBXH, --默认限号数 HZQSXH = ls_YSPBB0.HZQSXH, -- FZYYS0 = ls_YSPBB0.FZYYS0, --分诊台预约数 FZYYMX = ls_YSPBB0.FZYYMX, --分诊预约明细 DHYYS0 = ls_YSPBB0.DHYYS0, --电话预约数 DHYYMX = ls_YSPBB0.DHYYMX, --电话预约明细 WLYYS0 = ls_YSPBB0.WLYYS0, --网络预约数 WLYYMX = ls_YSPBB0.WLYYMX, --网络预约明细 PJZZSJ = ls_YSPBB0.PJZZSJ, --平均诊治时间 TZQRBZ = 'N', DHYY01 = ls_YSPBB0.DHYY01, --电话预约数1 DHMX01 = ls_YSPBB0.DHMX01, --电话预约明细1 DHYY02 = ls_YSPBB0.DHYY02, --电话预约数2 DHMX02 = ls_YSPBB0.DHMX02, --电话预约明细2 DHYY03 = ls_YSPBB0.DHYY03, --电话预约数3 DHMX03 = ls_YSPBB0.DHMX03, --电话预约明细3 ZZZKBH = ls_YSPBB0.ZZZKBH, --自助专科编号 YNBLMX = ls_YSPBB0.YNBLMX, --院内保留明细 --TZBZ00 = decode(lv_SF_YSHBTZZTGXFS,'1',decode(TZBZ00,'1',TZBZ00,ls_YSPBB0.TZBZ00),'2',decode(ls_DGYSBZ,'1',ls_YSPBB0.TZBZ00,decode(TZBZ00,'0',TZBZ00,ls_YSPBB0.TZBZ00)),ls_YSPBB0.TZBZ00), TZBZ00 = decode(lv_SF_YSHBTZZTGXFS,'1',decode(TZBZ00,'1',TZBZ00,ls_YSPBB0.TZBZ00),'2',decode(ls_DGYSBZ,'1',ls_YSPBB0.TZBZ00,TZBZ00),ls_YSPBB0.TZBZ00), YYID00 = ls_YSPBB0.YYID00, --医院ID YYMC00 = ls_YSPBB0.YYMC00, --牙椅名称 PBLX00 = ls_YSPBB0.PBLX00, --排班类型 SFLSPB = decode(SFLSPB,'2','2',ls_YSPBB0.SFLSPB) --是否临时排班:1是,0否,临时排班不开放预约 where GHYSBH = ls_YSPBB0.YSYGBH --挂号医生编号 and GHMZSJ = ls_GHMZSJ --门诊时间(YYYYMMDD) and GHQSSJ = ls_KSSJ00 --起始时间(00:00:00) and GHJSSJ = ls_JSSJ00 --截止时间(00:00:00) and ((GHYSBH=0 and YYID00 = ls_YSPBB0.YYID00 and ls_YSPBB0.YYID00 is not null and GHKSBH=ls_YSPBB0.YSKSBH) or (GHYSBH<>0)) ; end if; --if sql%notfound and (ls_YSPBB0.TZBZ00='0') then if (ls_PBXGHB='1' and sql%notfound or ls_PBXGHB='0') and (ls_YSPBB0.TZBZ00='0') then --插入号表前必须对号表进行判断是否有交叉时间，如果有，原有号表又没被使用，则删除原有号表，如果有且被使用，则不按新排班表生成。 select count(*),max(GHKSBH) into ls_COUNT0,ls_YGBH00 from SF_YSGHHB where GHMZSJ=ls_GHMZSJ and GHYSBH = ls_YSPBB0.YSYGBH and ((ls_YSPBB0.YYID00 is not null and GHYSBH=0 and YYID00=ls_YSPBB0.YYID00 and GHKSBH=ls_YSPBB0.YSKSBH) or GHYSBH<>0 ) and ((GHJSSJ >= GHQSSJ and ((GHQSSJ <= ls_KSSJ00 and ls_KSSJ00 < GHJSSJ ) or ( GHQSSJ GHQSSJ) or (ls_KSSJ00>GHQSSJ and ls_JSSJ000 then --删除交叉时间但是没被使用的号表 --add by qks 2010.10.26 select max(BMMC00) into ls_bmmc00 from BM_BMBM00 where BMBH00=ls_YGBH00 ; if nvl(ls_YSPBB0.YSYGBH,0)=0 then ls_ZWXM00:='科室排班'; else select ZWXM00 into ls_ZWXM00 from BM_YGBM00 where YGBH00 = ls_YSPBB0.YSYGBH; end if; if as_YSKSBH<>-99999 then as_YHMSG0:='操作失败,医生:'||ls_ZWXM00||' 在'||ls_bmmc00||' 在'||ls_GHMZSJ||'的号表中排班时间有交叉!'; as_SYSMSG:=SQLERRM; rollback; return; else insert Into XT_XTCZRZ(CZID00,CZTable,CZLX00,CZRYBM,CZNR00,DATAID) values(SQ_XT_XTCZRZ_ID0000.NEXTVAL,'SP_SF_YSGHHB',1,0,as_YHMSG0||'^'||as_SYSMSG,-1); end if; --delete from SF_YSGHHB a where GHMZSJ=ls_GHMZSJ and GHYSBH = ls_YSPBB0.YSYGBH and -- ( (GHJSSJ >= GHQSSJ and ((GHQSSJ <= ls_KSSJ00 and ls_KSSJ00 < GHJSSJ ) or ( GHQSSJ ls_KSSJ00,JSSJ00->ls_JSSJ00 select ls_GHHBID,YSKSBH,YSZKBH,YSYGBH,MZLBID,YSMZLB,ls_GHMZSJ,ls_KSSJ00,ls_JSSJ00,YSPBXH,0,0,YSYYH0,CKBH00,YYXHMX,ZZGHBZ,HZQSXH,YSPBXH, FZYYS0,FZYYMX,DHYYS0,DHYYMX,WLYYS0,WLYYMX,PJZZSJ,DHYY01,DHMX01,DHYY02,DHMX02,DHYY03,DHMX03,ZZZKBH,ls_YGBH00,YNBLMX,decode(SFXH00,'1','0','1'),a.YYID00, YYMC00,PBLX00,SFLSPB from SF_YSPBB0 a,BM_MZSJB0 b where a.YSPBSJ = b.MZSJ00 and a.YSPBXQ = ls_XQ0000 and a.YSPBID = ls_YSPBB0.YSPBID; insert into SF_YYZJB0(ID0000,GHHBID,CZLX00,CZY000,CZYKS0,CZRQ00,CZSJ00) select SQ_SF_YYZJB0_ID0000.nextval,ls_GHHBID,'0',null,null,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') from dual; end if; end if; if ls_GHHBID=0 then --代表之前执行的是update begin select GHHBID into ls_GHHBID from SF_YSGHHB where GHYSBH = ls_YSPBB0.YSYGBH and --挂号医生编号 GHMZSJ = ls_GHMZSJ and --门诊时间(YYYYMMDD) GHQSSJ = ls_KSSJ00 and --起始时间(00:00:00) GHJSSJ = ls_JSSJ00 and ((GHYSBH=0 and YYID00 = ls_YSPBB0.YYID00 and ls_YSPBB0.YYID00 is not null and GHKSBH=ls_YSPBB0.YSKSBH) or (GHYSBH<>0)); exception when others then ls_GHHBID:=0; end; if ls_GHHBID<>0 then insert into SF_YYZJB0(ID0000,GHHBID,CZLX00,CZY000,CZYKS0,CZRQ00,CZSJ00) select SQ_SF_YYZJB0_ID0000.nextval,ls_GHHBID,'1',null,null,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') from dual; end if; end if; end if; --添加判断,判断同一个医生,在同一天中,是否有交叉的时间段 if nvl(ls_YSPBB0.YSYGBH,0)=0 then ls_ZWXM00:='科室排班'; else select ZWXM00 into ls_ZWXM00 from BM_YGBM00 where YGBH00 = ls_YSPBB0.YSYGBH; end if; --2019.12.17 BM_MZSJB0->BM_MZSJMX if ls_MZSJMX = 1 then select count(*) into ls_COUNT0 from SF_YSPBB0 A,BM_MZSJMX B where A.YSPBSJ = B.MZSJ00 and A.YSYGBH = ls_YSPBB0.YSYGBH and YSPBXQ = ls_YSPBB0.YSPBXQ and b.SFYX00='Y' and trim(b.YXQSRQ)<=substrb(ls_GHMZSJ,5,4) and trim(b.YXJSRQ)>=substrb(ls_GHMZSJ,5,4) and ((ls_YSPBB0.YYID00 is not null and A.YSYGBH=0 and A.YYID00=ls_YSPBB0.YYID00 and a.YSKSBH=ls_YSPBB0.YSKSBH) or (A.YSYGBH<>0) ) and ( (JSSJ00 >= KSSJ00 and ((B.KSSJ00 <= ls_KSSJ00 and ls_KSSJ00 < B.JSSJ00 ) or ( B.KSSJ00 0) ) and ( (JSSJ00 >= KSSJ00 and ((B.KSSJ00 <= ls_KSSJ00 and ls_KSSJ00 < B.JSSJ00 ) or ( B.KSSJ00 1 then --有一条以上时,就是不允许的 as_YHMSG0:='系统操作失败,排班表中医生:'||ls_ZWXM00||'在'||ls_GHMZSJ||'的号表中生成的排班的时间有交叉!'; as_SYSMSG:=SQLERRM; rollback; return; end if; --同时段同诊室已经有排班是否提示并中止 if ls_MZHS_HBTKSTSJTS='1' and ls_YSPBB0.TZBZ00='0' then select count(*) into ls_COUNT0 from VW_SF_YSGHHB a where a.GHYSBH <> ls_YSPBB0.YSYGBH and a.GHMZSJ = ls_GHMZSJ and a.CKBH00=ls_YSPBB0.CKBH00 and a.TZBZ00='0' and ((ls_YSPBB0.YYID00 is not null and a.GHYSBH=0 and a.YYID00=ls_YSPBB0.YYID00 and a.GHKSBH=ls_YSPBB0.YSKSBH) or (a.GHYSBH<>0) ) and ( (a.GHQSSJ < a.GHJSSJ and ((a.GHQSSJ <= ls_KSSJ00 and ls_KSSJ00 < a.GHJSSJ ) or ( a.GHQSSJ = a.GHJSSJ and ((a.GHQSSJ <= ls_KSSJ00 and ls_KSSJ00 < '23:59:59') or ( '00:00:01' 0 then --有一条以上时,就是不允许的 select min(GHHBID) into ls_GHHBID from VW_SF_YSGHHB a where a.GHYSBH <> ls_YSPBB0.YSYGBH and a.GHMZSJ = ls_GHMZSJ and a.CKBH00=ls_YSPBB0.CKBH00 and a.TZBZ00='0' and ((ls_YSPBB0.YYID00 is not null and a.GHYSBH=0 and a.YYID00=ls_YSPBB0.YYID00 and a.GHKSBH=ls_YSPBB0.YSKSBH) or (a.GHYSBH<>0) ) and ( (a.GHQSSJ < a.GHJSSJ and ((a.GHQSSJ <= ls_KSSJ00 and ls_KSSJ00 < a.GHJSSJ ) or ( a.GHQSSJ = a.GHJSSJ and ((a.GHQSSJ <= ls_KSSJ00 and ls_KSSJ00 < '23:59:59') or ( '00:00:01''' then raise Ecustom; end if; commit; exception when Ecustom then as_YHMSG0:=ls_YHMSG0; as_SYSMSG:=as_SYSMSG; rollback; when others then as_YHMSG0:='数据库错误：出错原因未知！'; as_SYSMSG:='调用过程错误:SP_SF_YSGHHB('||as_KSRQ00||','||as_JSRQ00||',as_YHMSG0,as_SYSMSG)'||SQLERRM; rollback; end; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%