create or replace function SF_YS_GHXZ00(Pghks00 number,Pghys00 number,Pbrid00 number) return varchar2 is vzxzghlb bm_bmbm00.xzghlb%type ;--总限制过类别 Vbmxz00 bm_bmbm00.bmxz00%type ;--挂号科室部门性质 Vbyzglb bm_ghlbb0.lbbh00%type ;--本院职工挂号类别 vxzbmygghlb bm_bmbm00.xzghlb%type ;--限制部门员工挂号类别 Vxzghlb bm_ybbrlb.xzghlb%type ;--限制挂号类别 Vghhbid SF_YSGHHB.GHHBID%type ;--挂号号表ID Vghxhs0 SF_YSGHHB.GHXHS0%type ;--挂号限号数 ls_YBLB00 BM_BRXXB0.YBLB00%TYPE ; ls_YBZXLB IC_YBBRLB.YBZXLB%TYPE ; ls_FBBH00 BM_BRXXB0.FBBH00%TYPE ; VMZID00 SF_BRXXB0.GHID00%TYPE ; ls_SFXNH0 IC_YBBRLB.SFXNH0%TYPE ; Vghlbbh varchar2(255) ;--挂号类别编号 Vghdjxz char(1) ;--挂号等级限制'Y'限制,允许向下兼容,'M'限制,不允许向下兼 --容,'N'不作等级限制 Vghksxz number(10) ;--对具体科室进行挂号限制 Vxzghdj number(10) ;--挂号等级限制 Vyxgmfh char(1) ;--允许挂免费号 V_COUNT0 Number(5) ;--临时变量 V_COUNT1 Number(5) ;--临时变量 V_COUNT10 Number(5) ;--临时变量 Vcounter number(10) ;--临时变量 Vxs0000 Number(2) ;--临时变量 Vdqxq00 CHAR(1) ;--当天星期 Vdqrq00 CHAR(8) ;--当前日期 Vdqsj00 CHAR(8) ;--当前时间 Vsfqtjz char(1) ;--是否全天急诊 Vjzlbbh varchar2(200) ;--急诊的挂号类别编号 Vfjzlbbh varchar2(200) ;--非急诊的挂号类别编号 ls_dtghxz CHAR(1) ;--病人同一天在 --同一个医生是否限制只挂一次普通 --挂号和一次急诊号 Y:是(特殊病种患者除外) M:是(特殊病种病人也一样限制) N:都不限制, 默认为N --Q非医保病人一个普通号一个急诊号，医保病人一个普通号急诊号不限 --D:只有一个普通号（特殊病种除外) ls_IP XT_XTRZ00.BZ0000%type; --限制IP Ecustom exception ;--错误变量 Verrmsg varchar2(255) ;--错误信息 ls_TSBZGHCSXZ char(1) ;--Y:特殊病种病人的挂号限制按照上一次收费处结算时特殊病种数目+1 N:该参数不做限制 ls_KSGHZDCS NUMBER(5) ; ls_XZMGYBZXGHCS XT_XTCS00.VALUE0%TYPE ;--限制每个医保中心挂号次数 ls_SFYXPTH CHAR(1) ;-- 是否允许挂 普通号 0：不允许 1：允许 ls_ybbrptghxz char(1) ; YS_BYZGYBSFYXYSZGH char(1) ;--本院职工医保是否允许在医生站挂号 Y允许 N否 YS_NLYBBRGHSSFYCBFGHLB XT_XTCS00.VALUE0%TYPE ;--年老医保病人挂号时是否隐藏部分挂号类别 YS_YBBRNL number(5) ;--医保病人年龄 YS_YCGHLBC XT_XTCS00.VALUE0%TYPE ;--隐藏的挂号类别串 Vghlbbh_clc varchar2(255) ;--挂号类别编号处理串 VS_PROCESS XT_XTRZ00.JCID00%Type; YS_YZSJYHB XT_XTCS00.VALUE0%TYPE ;--医生时间段与号别，默认为N(不启用) 启用格式为YYYYMMDDHH24:MI:SS-YYYYMMDDHH24:MI:SS;lbbh00,lbbh00(多个号用","隔开) YS_YZHB_clc varchar2(100);--义诊号别窜 vhjje00 sf_brfy00.hjje00%type; ls_YSGHCSXZ XT_XTCS00.VALUE0%TYPE ;--限制某个个医保中心医生次数 LS_YBZX00 varchar2(10); LS_YSGS00 varchar2(10); LS_GHCS00 varchar2(10); LS_PDJZFS XT_XTCS00.VALUE0%TYPE ;--判断急诊的方式 0根据时间段来判断是否急诊 1:根据登陆是否急诊科来判断 LS_SFTSGHFF XT_XTCS00.VALUE0%TYPE ;--是否进行特殊挂号分发 LS_BRXB00 BM_BRXXB0.BRXB00%TYPE ;--病人性别 LS_DQBMMC BM_BMBM00.BMMC00%TYPE ;--当前挂号科室名称 ls_GJXBXZGHKS XT_XTCS00.VALUE0%TYPE ;--是否根据性别限制挂号科室 ls_WJSFYTXJZTS XT_XTCS00.VALUE0%TYPE ;--未结算费用提醒截止天数 ls_TKSYYXGHBRZGH varchar2(10) ;--是否限制同一科室不允许重复挂号 ls_TKSGHPCLB XT_XTCS00.VALUE0%TYPE ;--同科室挂号排除的挂号类别 YS_TSSXMCMFGHLB XT_XTCS00.VALUE0%TYPE ;--bm_brxxb0.tssxmc免费挂号类别 ls_TKSGHBXZYXGHLB XT_XTCS00.VALUE0%TYPE ;--同科室挂号再挂号允许的挂号类别 cursor C_ghlbbh is select lbbh00,SFXEGH from bm_ghlbb0 a where SFYX00='Y' and lbbh00<>Vbyzglb and --挂号等级为-1说明本挂号类别不受等级限制 ((GHDJ00<=Vxzghdj and Vghdjxz='Y')or (GHDJ00=Vxzghdj and Vghdjxz='M') or (GHDJ00=-1) or (Vghdjxz='N')) --是否进特殊挂号分发 and (LS_SFTSGHFF = 'N' or SFXYFF <> '1' or exists(select 1 from YS_TSGHFF where GHLB00=a.LBBH00 and SFYX00='1' and YS0000=Pghys00)) --限制免费号 and (Vyxgmfh='Y' or LBBH00 in (select LBBH00 from BM_GHXMDY WHERE SYBZ00 = '1')) --是否本科室专用 and LBBH00 not in (select LBBH00 from BM_KSGHDY where BKSZY0='Y' and BMBH00<>Pghks00) and LBBH00<>-99999 --有效挂号天数内，本科室已经有挂号了，可以挂多个免费号 and ((ls_TKSYYXGHBRZGH='Y' and (LBBH00=-1 or instr(ls_TKSGHBXZYXGHLB,to_char(','||lbbh00||','))>0)) or ls_TKSYYXGHBRZGH='N') order by xsxh00,ghdj00,lbbh00; cursor C_jzghlbbh is select to_char(lbbh00) LBBH00,GHDJ00 from bm_ghlbb0 where SFYX00='Y' AND (LBMC00 LIKE '%急诊%' or GHDJ00 = -1); cursor C_fjzghlbbh is select to_char(lbbh00) LBBH00, GHDJ00 from bm_ghlbb0 where SFYX00='Y' AND (LBMC00 not LIKE '%急诊%' or GHDJ00 = -1); -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2007.07.17 老年医院六点以后要求只挂急诊号 -- YANGY 2007.07.19 同一医生同一天只能给同一病人挂一个号，挂第二个号时提示“本日已经挂过号 -- YANGY 2007.07.27 增加一个表可医院自己维护急诊时间段 -- YANGY 2007.08.16 限制一个科室最多只能挂几个号 -- YANGY 2007.08.17 一个医生一天可以挂一个普通号，一个急诊号 -- YANGY 2007.09.06 福州六院要求特殊病种病人也要限制挂号 -- YANGY 2007.09.30 省二院要求一天只能挂一个号，第二个急诊号也不能挂，特殊病种病人允许重复挂号 -- CSF 2008.06.18 福州六院要求医保病人也要限制挂号,但急诊除外 -- CSF 2009.07.23 省二要求，市医保病人，一个医生处一天只能挂一个号 -- CSF 2009.10.29 马尾医院要求：特殊病种病人的挂号限制按照上一次收费处结算时特殊病种数目+1; -- qks 2009.11.17 表BM_KSGHDY增加字段BKSZY0:是否本科室专用,值为'Y'时,其他科室不允许使用 -- zhr 2010.03.21 肺科要求限制某些IP某些时段不能开诊 -- CSF 2010.07.07 省二限制某些IP某些时段不能开诊，加日期的限制，某些日期不受这些限制。 -- CSF 2011.01.10 扩展参数XT_SFYXGH或参数YS_YXGMFH实现病人在同一天在同一个医生处第一次不允许挂免费号，第二次允许挂免费号 -- csf 2011.05.26 本院职工的医保病人不能在医生站挂号 YS_YBBYZGBRSFYXGH -- zhangwz 2011.05.31 扩展参数 YS_XZMGYBZXGHCS 为可支持多个医保中心的限制 by MZYS-20110527-001. -- zhangwz 2011.10.27 增加参数 YS_NLYBBRGHSSFYCBFGHLB 控制 年老医保病人挂号时是否隐藏部分挂号类别 by MZYS-20111020-001. -- csf 2011.11.08 限制某些IP某些时段不能开诊，在终端机器上使用不正常，所以取本进程ip改由XT_XTRZ00取，而不通过sys_context('userenv', 'ip_address') MZYS-20111108-004 -- csf 2011.11.17 加参数，处理指定时间段内只能挂限定号表MZYS-20111117-001 -- zhangwz 2012.03.12 解决参数 YS_XZMGYBZXGHCS 控制不住本院职工每个医生只能挂一个号的功能 by MZYS-20120306-001. -- zhangyc 2011.03.21 限制某个医保中心病人,一天只能看几个医生,一个医生只能挂几个号 参数:YS_YSGHCSXZ开启后(YS_XZMGYBZXGHCS设置无效) by MZYS-20120310-001 -- yangy 2012.09.22 急诊挂号类别判断 -- yangy 2012.09.26 根据挂号分发来进行挂号限制 -- yangy 2012.10.08 根据挂号表中挂号是否允许分发来判断挂号权限 -- yangy 2012.11.16 是否根据性别限制挂号 -- yangy 2012.11.19 如果医保病人在X天之前有未结算的费用，要进行提醒，不允许挂号 -- yangy 2012.12.05 计算未结算的费用的算法改变 -- qks 2014.05.23 农合病人 在X天之前有未结算的费用，不提醒，允许挂号 -- qks 2014.05.30 非农合医保病人 在X天之前有未扣费的费用，也要进行提醒，不允许挂号 -- zhaoxz 2016.08.04 三明医保中心要求，48小时内限制病人在同一科室不能重复挂号（参照MZYS-20150907-001修改）For MZYS9-20160729-001 -- zhaoxz 2016.11.02 18岁以上病人不允许在儿科科室挂号归号MZYS9-20161102-001 -- chenxx 2017.01.12 自费病人在48小时内在同一个科室只能收一次诊查费 For MZYS9-20170108-001 -- zhaoxz 2017.04.24 48小时同一个科室挂号限制，去除义诊号限制 For MZYS9-20170422-003 -- ruanbh 2017.09.13 6岁及以下儿童默认小儿挂号类别 MZYS9-20170912-001 -- ruanbh 2017.09.25 如果bm_brxxb0.tssxmc=参数SF_ZZJESFZY的值，则挂免费号 MZYS9-20170925-002 -- linyj 2017.11.10 48小时挂号类别限制再挂号允许的挂号类别 MZYS9-20171027-001 -- linyj 2017.11.22 48小时同一个科室挂号限制排除预挂号, 48小时同一个科室挂号限制改为允许多种 MZYS9-20171121-002 begin --如果指定了本院职工的挂号类别，直接返回本院职工类别; --如果该医生限制了挂号等级,并且有排班,直接返回排班定义的号表; --如果该医生限制了挂号等级,则要按医生的等级进行挂号限制 挂号类别等级为-1的说明该类别不受限制 --定义了该类别的病人做了挂号限制BM_YBBRLB --定义了该科室的挂号类别进行限制BM_KSGHDY --是否允许挂免费号 --医技系统简易门诊的默认挂号 ls_SFYXPTH:='1'; vzxzghlb:=' '; vxzbmygghlb:=' '; Vxzghlb:=' '; Vxzghdj:=1000; Vghlbbh:='-1'; Vyxgmfh:='N'; ls_YBZX00:='-1'; LS_YSGS00:='2'; LS_GHCS00:='1'; select trim(NVL(max(value0),'N')) into LS_SFTSGHFF FROM xt_xtcs00 where name00='YS_SFTSGHFF'; --20100321 zhr 肺科要求限制某些IP某些时段不能开诊 --20111108 ,由于终端机客户端取到的ip不准确，所以根据进程取XT_XTRZ0表的IP -- ls_IP:=trim(sys_context('userenv', 'ip_address')); Select userenv('SESSIONID') PROCESS Into VS_PROCESS from dual where rownum=1; Select nvl(max(BZ0000),0) Into ls_IP From XT_XTRZ00 Where JCID00=VS_PROCESS And LSH000=(select max(LSH000) from XT_XTRZ00 where JCID00=VS_PROCESS); select '*'||substrb(ls_IP,1,instrb(ls_IP,'#')-1)||'*' into ls_ip from dual; select count(*) into Vcounter from BM_TYZD00 where ZDMC00='限制开诊IP' and instrb('*'||trim(MC0000)||'*',ls_IP)>0; --(trim(MC0000)||'#' like '%'||ls_IP||'%' or (instrb(MC0000,'*')>0 and MC0000 like '%'||substrb(ls_IP,1,instrb(ls_IP,'*',-1)) ||'%')); if VCounter>0 then select count(*) into Vcounter from BM_TYZD00 where ZDMC00='限制开诊时间' and to_char(sysdate,'HH24:MI') between substrb(MC0000,1,instrb(MC0000,'-')-1) and substrb(MC0000,instrb(MC0000,'-')+1,5); if Vcounter>0 then select count(*) into Vcounter from BM_TYZD00 where ( (ZDMC00='不受限制开诊日期' and to_char(sysdate,'YYYYMMDD') between substrb(MC0000,1,instrb(MC0000,'-')-1) and substrb(MC0000,instrb(MC0000,'-')+1,8) ) or (ZDMC00='不受限制开诊星期' and instr(MC0000,to_char(sysdate-1,'D'))>0 )); if Vcounter=0 then Verrmsg:='对不起，此时段不允许接诊！'; raise Ecustom; end if; end if; end if; --义诊日期、返回义诊号别 select nvl(max(trim(VALUE0)),'N') INTO YS_YZSJYHB from XT_XTCS00 where NAME00='YS_YZSJYHB'; if YS_YZSJYHB<>'N' then if (to_char(sysdate,'YYYYMMDDhh24:MI:SS')>=SUBSTRB(YS_YZSJYHB,1,16) ) and (to_char(sysdate,'YYYYMMDDhh24:MI:SS')<=SUBSTRB(YS_YZSJYHB,18,16) ) then YS_YZHB_clc:=SUBSTRB(YS_YZSJYHB,35); if YS_YZHB_clc is not null then return YS_YZHB_clc; end if; end if; end if; select nvl(max(trim(value0)),'Y') into YS_BYZGYBSFYXYSZGH from XT_XTCS00 where name00='YS_BYZGYBSFYXYSZGH'; select TO_NUMBER(trim(NVL(max(value0),'0'))) into ls_KSGHZDCS FROM xt_xtcs00 where name00='YS_KSGHXZCS'; --限制某个医保中心病人一天一个医生的挂号次数，该参数描述的是具体的YBZXLB 不做限制为-1 默认为-1 select trim(NVL(max(value0),'-1')) into ls_XZMGYBZXGHCS FROM xt_xtcs00 where name00='YS_XZMGYBZXGHCS'; --限制某个医保中心病人,一天只能看几个医生,一个医生只能挂几个号参数:YS_YSGHCSXZ开启后(YS_XZMGYBZXGHCS设置无效) select trim(NVL(max(value0),'-1')) into ls_YSGHCSXZ FROM xt_xtcs00 where name00='YS_YSGHCSXZ'; select trim(NVL(max(value0),'N')) into ls_dtghxz FROM xt_xtcs00 where name00='YS_TYSDTGHXZ'; --ls_ybbrptghxz 医保病人普通挂号限制 N:不限制 Y:医保普通号限制一个 急诊号不限 --特殊病种病人的挂号限制按照上一次收费处结算时特殊病种数目+1 select trim(NVL(max(value0),'N')) into ls_TSBZGHCSXZ FROM xt_xtcs00 where name00='YS_TSBZGHCSXZ'; if ls_dtghxz='Q' then ls_ybbrptghxz:='Y'; ls_dtghxz:='Y'; else ls_ybbrptghxz:='N'; end if; --2016.08.04 zhaoxz 三明医保中心要求，48小时内限制病人在同一科室不能重复挂号。MZYS9-20160729-001 --2017.04.24 zhaoxz 三明医院，48小时挂号类别限制排除的挂号类别。 select trim(NVL(max(value0),'N')) into ls_TKSYYXGHBRZGH FROM xt_xtcs00 where name00='YS_TKSYYXGHBRZGH'; select trim(NVL(max(value0),'0')) into ls_TKSGHPCLB from XT_XTCS00 where name00='YS_TKSGHBXZGHLB'; if ls_TKSYYXGHBRZGH='Y' and Pbrid00>0 then select count(*) into Vcounter from VW_YS_YXGH00_MZDZBL where BRID00=Pbrid00 and FBBH00=3 and GHKS00=Pghks00 and instr(ls_TKSGHPCLB,to_char(','||GHLB00||','))=0 and to_char(GHLB00)<>ls_TKSGHPCLB and GHLB00<> -99999; if Vcounter>0 then --Verrmsg:='取挂号类别不能成功：病人在本科室还存在有效挂号，不能再挂号！'; --raise Ecustom; ls_TKSYYXGHBRZGH:='Y'; else ls_TKSYYXGHBRZGH:='N'; --2017.01.12 chenxx 自费病人在48小时内在同一个科室只能收一次诊查费 For MZYS9-20170108-001 select trim(NVL(max(value0),'N')) into ls_TKSYYXGHBRZGH FROM xt_xtcs00 where name00='YS_ZFBRTKSYYXGHBRZGH'; if ls_TKSYYXGHBRZGH='Y' and Pbrid00>0 then select count(*) into Vcounter from VW_YS_YXGH00_MZDZBL where BRID00=Pbrid00 and FBBH00=1 and GHKS00=Pghks00 and instr(ls_TKSGHPCLB,to_char(','||GHLB00||','))=0 and to_char(GHLB00)<>ls_TKSGHPCLB and GHLB00<> -99999; if Vcounter>0 then ls_TKSYYXGHBRZGH:='Y'; else ls_TKSYYXGHBRZGH:='N'; end if; end if; end if; end if; --2017.01.12 chenxx 48小时挂号类别限制允许的挂号类别 MZYS9-20171027-001 select trim(NVL(max(value0),'N')) into ls_TKSGHBXZYXGHLB FROM xt_xtcs00 where name00='YS_TKSGHBXZYXGHLB'; if Pbrid00>0 then begin select YBLB00,FBBH00,BRXB00 INTO ls_YBLB00,ls_FBBH00, LS_BRXB00 FROM BM_BRXXB0 WHERE BRID00=Pbrid00; select ybzxlb,sfxnh0 INTO ls_YBZXLB,ls_SFXNH0 from ic_ybbrlb where yblb00=ls_YBLB00 AND FBBH00=ls_FBBH00; exception when others then Verrmsg:='本院职工医保请到收费处挂号！'; raise Ecustom; end; end if; if (YS_BYZGYBSFYXYSZGH ='N') and (Pbrid00>0) then select count(*) into Vcounter from bm_ygbm00 where brid00=Pbrid00; if (ls_FBBH00='3') and (Vcounter>0) then Verrmsg:='本院职工医保请到收费处挂号！'; raise Ecustom; end if; end if; --根据病人年龄限制挂儿科 select count(*) into Vcounter From bm_bmbm00 where bmmc00 like '%儿科%' and bmbh00=Pghks00; if(Vcounter>0) then select (sysdate- to_date(decode(a.brcsrq,null,to_char(sysdate,'YYYYMMDD'),a.brcsrq),'YYYYMMDD'))/365 into Vcounter From bm_brxxb0 a where brid00=Pbrid00; if(Vcounter>18) then Verrmsg:='大于18周岁不允许在儿科挂号!'; raise Ecustom; end if; end if; --是否根据性别限制挂号，默认为Y要限制 select trim(nvl(max(value0),'Y')) into ls_GJXBXZGHKS FROM xt_xtcs00 where name00='YS_SFGJXBXZGHKS'; if (ls_GJXBXZGHKS = 'Y') AND (Pbrid00>0) then select count(1),nvl(max(trim(bmmc00)),'0') into Vcounter,LS_DQBMMC from BM_BMBM00 where BMBH00 = Pghks00 and (bmmc00 like '%男%' or bmmc00 like '%女%' or bmmc00 like '%妇%' or bmmc00 like '%产%'); if (Vcounter>0) then if (instrb(LS_DQBMMC,'男') >0) then if instrb(LS_BRXB00,'女') >0 then Verrmsg:='女性病人不能在男性科室挂号!'; raise Ecustom; end if; else if instrb(LS_BRXB00,'男') >0 then Verrmsg:='男性病人不能在女性相关科室挂号!'; raise Ecustom; end if; end if; end if; end if; --如果医保病人在X天之前有未结算的费用，要进行提醒 select trim(nvl(max(value0),'0')) into ls_WJSFYTXJZTS FROM xt_xtcs00 where name00='YS_WJSFYTXJZTS'; --未结算费用提醒截止天数，0代表不提醒， 1代表昨天之前(包括昨天)有未结算费用要提醒 if (ls_WJSFYTXJZTS <> '0') AND (Pbrid00 > 0) and (ls_FBBH00 = 3) and (ls_SFXNH0 = '0') then select nvl(sum(b.hjje00),0) into vhjje00 from SF_BRFY00 a, SF_FYMX00 b where a.DJH000=b.DJH000 and a.BRID00 = Pbrid00 and a.MZID00>0 AND b.JZDH00+0=0 and a.JZDH00+0=0 and b.CZRQ00 <= to_char(sysdate-to_number(ls_WJSFYTXJZTS),'yyyyMMdd') and b.CXBZ00='Z'; if vhjje00<=0 then select nvl(sum(ZJE000),0) into vhjje00 from YJ_YW0000 where BRID00=Pbrid00 and mzzybz='0' and xmzt00='1' and KDRQ000 and rownum=1; end if; if (vhjje00>0) then Verrmsg:='病人还有'||ls_WJSFYTXJZTS||'天前未结算的费用，请先到收费处结算!'; raise Ecustom; end if; end if; --限制某个医保中心病人,一天只能看几个医生,一个医生只能挂几个号YS_YSGHCSXZ开启后(YS_XZMGYBZXGHCS设置无效) by MZYS-20120310-001 -- if ls_YSGHCSXZ<>'-1' then select SF_YS_GETGHXZCS(ls_YBZXLB,0) into LS_YBZX00 from dual; --医保中心类别ybzxlb select SF_YS_GETGHXZCS(ls_YBZXLB,1) into LS_YSGS00 from dual; --允许看几个医生 select SF_YS_GETGHXZCS(ls_YBZXLB,2) into LS_GHCS00 from dual; --同一个医生运行挂号次数 end if; select count(*) into Vcounter from xt_yyxx00 where YYQC00='福建医科大学附属第二医院'; if Vcounter>0 then --如果有排班，直接取排班的号别 begin select to_char(GHLBBH),GHXHS0,GHHBID into Vghlbbh,Vghxhs0,Vghhbid from SF_YSGHHB where GHKSBH=Pghks00 and GHYSBH=Pghys00 and GHMZSJ=to_char(sysdate,'yyyymmdd') and GHQSSJ<=to_char(sysdate,'hh24:mi:ss') and GHJSSJ>=to_char(sysdate,'hh24:mi:ss'); select count(*) into Vcounter from sf_brxxb0 a,sf_ysghhb b where b.GHHBID=Vghhbid and a.thbz00<>'0' and b.GHKSBH=a.ghks00 and b.GHYSBH=a.JZYS00 and b.GHMZSJ=a.ghrq00 and b.GHQSSJ<=a.ghsj00 and b.GHJSSJ>=a.ghsj00; if Vghxhs0>Vcounter then return Vghlbbh; else return -1; end if; exception when others then Vghlbbh:='-1'; end; end if; --本院职工挂号类别限制 begin select to_number(value0) into Vbyzglb from xt_xtcs00 where name00='SF_BYZGGHLB'; exception when others then Vbyzglb:=-1; end; if Pbrid00>0 then--指定了病人 --病人是否是本院职工，如果是本院职工，只能挂本院职工号并立即返回,其他病人不能挂本院职工号 if Vbyzglb>0 then if (trim(ls_XZMGYBZXGHCS)<>'-1') and (LS_YBZX00='-1') then -- by MZYS-20120306-001. --add and (LS_YBZX00='-1') by MZYS-20120310-001 如果YS_YSGHCSXZ设置成功(YS_XZMGYBZXGHCS设置无效) SELECT COUNT(1) INTO V_COUNT0 FROM SF_BRXXB0 c WHERE c.BRID00=Pbrid00 AND c.JZYS00=Pghys00 AND c.GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND c.THBZ00<>'0' and c.ghlb00<>-99999 and exists(select 1 from BM_BRXXB0 a,IC_YBBRLB b where a.FBBH00=b.FBBH00 and a.YBLB00=b.YBLB00 and a.BRID00=c.BRID00 and instr(';'||trim(ls_XZMGYBZXGHCS)||';',';'||b.YBZXLB||';')>0); if V_COUNT0>0 then Verrmsg:='该病人在本医生处已经挂过号，不能重新挂号！'; raise Ecustom; end if; end if; --by MZYS-20120310-001 限制某个医保中心病人,一天只能看几个医生,一个医生只能挂几个号--参数:YS_YSGHCSXZ开启后(YS_XZMGYBZXGHCS设置无效) if (LS_YBZX00<>'-1') then select count(distinct jzys00) into V_COUNT0 from SF_BRXXB0 c WHERE c.BRID00=Pbrid00 AND c.JZYS00<>Pghys00 AND c.GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND c.THBZ00<>'0' and c.ghlb00<>-99999; if V_COUNT0>=to_number(LS_YSGS00) then Verrmsg:='该病人本日就诊医生已超过('||LS_YSGS00||')个，不能重新挂号！'; raise Ecustom; else select COUNT(1) into V_COUNT0 from SF_BRXXB0 c WHERE c.BRID00=Pbrid00 AND c.JZYS00=Pghys00 and c.GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND c.THBZ00<>'0' and c.ghlb00<>-99999; if V_COUNT0>=to_number(LS_GHCS00) then Verrmsg:='该病人在本医生挂号次数已超过('||LS_GHCS00||')个，不能重新挂号！'; raise Ecustom; end if; end if; end if; select count(*) into Vcounter from bm_ygbm00 where brid00=Pbrid00; if Vcounter>0 then return to_char(Vbyzglb); end if; end if; --限制部门的员工挂号 begin select nvl(xzghlb,' ') into vxzbmygghlb from bm_bmbm00 a,bm_ygbm00 b where a.bmbh00=b.bmbh00 and b.brid00=Pbrid00; exception when others then vxzbmygghlb:=' '; end; --bm_ybbrlb表限制了，该类病人所允许的挂号类别 begin select nvl(xzghlb,' ') into Vxzghlb from bm_brxxb0 a,bm_ybbrlb b,ic_ybbrlb c where a.fbbh00=c.fbbh00 and a.yblb00=c.yblb00 and a.ybbrlb=b.ybbrlb and a.fbbh00=b.fbbh00 and b.ybzxlb=c.ybzxlb and a.brid00=Pbrid00; exception when others then Vxzghlb:=' '; end; if ls_ybbrptghxz<>'N' then--医保普通号限制 SELECT COUNT(1) INTO V_COUNT10 FROM SF_BRXXB0 a, bm_ghlbb0 b WHERE a.ghlb00=b.lbbh00 and a.BRID00=Pbrid00 AND a.JZYS00=Pghys00 AND GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND a.THBZ00<>'0' and a.ghlb00<>-99999 and b.lbmc00 not like '%急诊%' and a.fbbh00='3'; end if; if ls_dtghxz<> 'N' THEN SELECT COUNT(1) INTO V_COUNT1 FROM SF_BRXXB0 WHERE BRID00=Pbrid00 AND JZYS00=Pghys00 AND GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND THBZ00<>'0' and ghlb00<>-99999; if V_COUNT1>0 then if (ls_dtghxz='Y') or (ls_dtghxz='D') THEN -- select YBLB00,FBBH00 INTO ls_YBLB00,ls_FBBH00 FROM BM_BRXXB0 WHERE BRID00=Pbrid00; -- select ybzxlb INTO ls_YBZXLB from ic_ybbrlb where yblb00=ls_YBLB00 AND FBBH00=ls_FBBH00; select COUNT(1) INTO V_COUNT0 FROM BM_TSBZB0 WHERE YBZXLB=ls_YBZXLB; ELSIF ls_dtghxz='M' THEN V_COUNT0:=0; end if; if V_COUNT0=0 THEN--参数ls_dtghxz='M' 或((ls_dtghxz='Y') or (ls_dtghxz='D')非特殊病种--病人) --if ls_dtghxz='D' THEN -- Verrmsg:='该病人当天已经在这个医生处挂过号，不能重新挂号'; -- raise Ecustom; -- end if; if V_COUNT1=1 then SELECT COUNT(1) INTO V_COUNT0 FROM SF_BRXXB0 a, bm_ghlbb0 b WHERE a.ghlb00=b.lbbh00 and a.BRID00=Pbrid00 AND a.JZYS00=Pghys00 AND GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND a.THBZ00<>'0' and a.ghlb00<>-99999 and b.lbmc00 like '%急诊%'; if V_COUNT0>0 then ls_SFYXPTH :='1'; else ls_SFYXPTH := '0'; end if; else Verrmsg:='该病人当天已经在这个医生处挂过一个普通号和一个急诊号，不能重新挂号!'; raise Ecustom; end if; end if; end if; end if; --限制某个医保中心病人一天一个医生的挂号次数，该参数描述的是具体的YBZXLB 不做限制为-1 默认为-1 if (trim(ls_XZMGYBZXGHCS)<>'-1') and (LS_YBZX00='-1') then --add and (LS_YBZX00='-1') by MZYS-20120310-001 如果YS_YSGHCSXZ设置成功(YS_XZMGYBZXGHCS设置无效) SELECT COUNT(1) INTO V_COUNT0 FROM SF_BRXXB0 c WHERE c.BRID00=Pbrid00 AND c.JZYS00=Pghys00 AND c.GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND c.THBZ00<>'0' and c.ghlb00<>-99999 and exists(select 1 from BM_BRXXB0 a,IC_YBBRLB b where a.FBBH00=b.FBBH00 and a.YBLB00=b.YBLB00 and a.BRID00=c.BRID00 -- and b.YBZXLB=ls_XZMGYBZXGHCS); by MZYS-20110527-001. and instr(';'||trim(ls_XZMGYBZXGHCS)||';',';'||b.YBZXLB||';')>0); if V_COUNT0>0 then Verrmsg:='该病人在本医生处已经挂过号，不能重新挂号！'; raise Ecustom; end if; end if; --by MZYS-20120310-001 限制某个医保中心病人,一天只能看几个医生,一个医生只能挂几个号--参数:YS_YSGHCSXZ开启后(YS_XZMGYBZXGHCS设置无效) if (LS_YBZX00<>'-1') then select count(distinct jzys00) into V_COUNT0 from SF_BRXXB0 c WHERE c.BRID00=Pbrid00 AND c.JZYS00<>Pghys00 AND c.GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND c.THBZ00<>'0' and c.ghlb00<>-99999; -- Verrmsg:='('||to_char(V_COUNT0)||':'||to_number(LS_YSGS00)||')'; --raise Ecustom; if V_COUNT0>=to_number(LS_YSGS00) then Verrmsg:='该病人本日就诊医生已超过('||LS_YSGS00||')个，不能重新挂号！'; raise Ecustom; else select COUNT(1) into V_COUNT0 from SF_BRXXB0 c WHERE c.BRID00=Pbrid00 AND c.JZYS00=Pghys00 and c.GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND c.THBZ00<>'0' and c.ghlb00<>-99999; -- Verrmsg:='('||to_char(V_COUNT0)||':'||to_number(LS_GHCS00)||')'; --raise Ecustom; if V_COUNT0>=to_number(LS_GHCS00) then Verrmsg:='该病人在本医生挂号次数已超过('||LS_GHCS00||')个，不能重新挂号！'; raise Ecustom; end if; end if; end if; --马尾医院，限制特殊病种病人挂号次数为，上一次收费出结算该病人的特殊病种数目+1; if ls_TSBZGHCSXZ='Y' THEN Select COUNT(*) into V_COUNT0 from BM_BRXXB0 WHERE BRID00=Pbrid00 and FBBH00='3'; if V_COUNT0>0 THEN SELECT NVL(MAX(MZID00),0) INTO VMZID00 FROM SF_JZB000 WHERE BRID00=Pbrid00; if VMZID00>0 THEN SELECT COUNT(DISTINCT TSBZBH) INTO V_COUNT0 FROM XT_YBTSBZ WHERE GHID00=VMZID00; else V_COUNT0:=0; end if; SELECT COUNT(1) INTO V_COUNT1 FROM SF_BRXXB0 WHERE BRID00=Pbrid00 AND JZYS00=Pghys00 AND GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND THBZ00<>'0' and ghlb00<>-99999; if V_COUNT1>V_COUNT0 THEN Verrmsg:='该病人上次的特殊病种数目为'||to_char(V_COUNT0)||'次，所以当天最多只能挂'||to_char(V_COUNT0+1)||'次号！'; raise Ecustom; end if; end if; end if; if ls_KSGHZDCS>0 THEN SELECT COUNT(1) INTO V_COUNT0 FROM SF_BRXXB0 WHERE BRID00=Pbrid00 AND GHKS00=Pghks00 AND GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND THBZ00<>'0' and ghlb00<>-99999; if V_COUNT0>=ls_KSGHZDCS then Verrmsg:='该病人当天已经在本科室挂过'||to_char(ls_KSGHZDCS)||'次号，不能重新挂号！'; raise Ecustom; end if; end if; end if; begin--取部门性质 select bmxz00 into Vbmxz00 from bm_bmbm00 where bmbh00=Pghks00; exception when others then return '-1'; end; --确定该部门是否允许挂免费的号 begin if Vbmxz00 ='5' then select substrb(Value0,1,1) into Vyxgmfh from xt_xtcs00 where name00='XT_SFYXGH'; else select substrb(Value0,1,1) into Vyxgmfh from xt_xtcs00 where name00='YS_YXGMFH'; end if; exception when others then Vyxgmfh:='N'; end; --福州市罗源县医院 (要求病人一天中在同一个医生处挂号允许挂免费号) if Vyxgmfh='M' then select count(*) into Vcounter from SF_BRXXB0 c where GHRQ00=to_char(SYSDATE,'YYYYMMDD') and c.BRID00=Pbrid00 AND c.JZYS00=Pghys00 AND c.GHRQ00= TO_CHAR(SYSDATE,'YYYYMMDD') AND c.THBZ00<>'0' and c.ghlb00<>-99999; if Vcounter>=1 then Vyxgmfh:='Y'; end if; end if; --是否根据医生的挂号级别来限制挂号等级,Y允许向下兼容M不允许向下兼容N不作限制 begin select substrb(value0,1,1) into Vghdjxz from xt_xtcs00 where name00='XT_GHDJXZ'; exception when others then Vghdjxz:='N'; end; begin if Vghdjxz in ('Y','M') then select ghdj00 into Vxzghdj from bm_ghlbb0 where lbbh00=(select MRGHLB from bm_ygbm00 where YGBH00=Pghys00); end if; exception when others then Vxzghdj:=1000; end; if vxzbmygghlb<>' ' and Vxzghlb<>' ' then vzxzghlb:= vxzbmygghlb; end if; if vxzbmygghlb<>' ' and Vxzghlb=' ' then vzxzghlb:=vxzbmygghlb; end if; if vxzbmygghlb=' ' and Vxzghlb<>' ' then vzxzghlb:=Vxzghlb; end if; if vxzbmygghlb=' ' and Vxzghlb=' ' then vzxzghlb:=' '; end if; --是否根据科室挂号对应关系表来限制挂号 select count(*) into Vghksxz from BM_KSGHDY where BMBH00=Pghks00; for GH in C_ghlbbh loop --是否根据科室挂号对应关系表来限制挂号 Vcounter:=0; if Vghksxz>0 then select count(*) into Vcounter from BM_KSGHDY where BMBH00=Pghks00 and LBBH00=gh.lbbh00; end if; if Vghksxz=0 or (Vghksxz>0 and Vcounter>0) then --bm_ybbrlb表限制了，该类病人所允许的挂号类别 if (instrb(','||vzxzghlb||',',','||to_char(GH.lbbh00)||',')>0) or (vzxzghlb=' ') then if Vghlbbh='-1' then Vghlbbh:=to_char(GH.lbbh00); else Vghlbbh:=Vghlbbh||','||to_char(GH.lbbh00); end if; -- ruanbh 2017.09.13 6岁及以下儿童默认小儿挂号类别 MZYS9-20170912-001 --如果有小儿挂号类别，并且病人小于等于6岁默认小儿挂号类别 if GH.SFXEGH='1' then select count(*) into Vcounter from BM_BRXXB0 where BRID00=Pbrid00 and SF_YS_CSRQTONL(BRCSRQ)<=6; if Vcounter>0 then Vghlbbh:=GH.lbbh00; exit; end if; end if; end if; end if; end loop; Vfjzlbbh:= ' '; --非急诊挂号类别 For FJZGH in C_fjzghlbbh loop if instrb(','||Vghlbbh||',', ','||FJZGH.LBBH00||',')>0 then Vfjzlbbh:=Vfjzlbbh||','||FJZGH.LBBH00; end if; end loop; if Vfjzlbbh <> ' ' then Vfjzlbbh:= SUBSTR(TRIM(Vfjzlbbh),2,(LENGTH(Vfjzlbbh)-1)); end if; Vjzlbbh:= ' '; For JZGH in C_jzghlbbh loop if instrb(','||Vghlbbh||',', ','||JZGH.LBBH00||',')>0 then Vjzlbbh:=Vjzlbbh||','||JZGH.LBBH00; end if; end loop; if Vjzlbbh<>' ' then select trim(NVL(max(value0),'0')) into LS_PDJZFS FROM xt_xtcs00 where name00='YS_PDJZFS'; Vjzlbbh:= SUBSTR(TRIM(Vjzlbbh),2,(LENGTH(Vjzlbbh)-1)); if (LS_PDJZFS = '0') then --根据急诊时间段来判断 Vsfqtjz := 'N'; select to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'HH24:MI:SS') , to_char(sysdate-1,'D') into Vdqrq00, Vdqsj00, Vdqxq00 from dual; select count(1) into V_COUNT0 from YS_JZSJB0 where rqks00<=Vdqrq00 and rqjz00>=Vdqrq00 and SFYX00='Y'; if V_COUNT0>0 THEN select SFQTJZ into Vsfqtjz from YS_JZSJB0 where rqks00<=Vdqrq00 and rqjz00>=Vdqrq00 and SFYX00='Y' and rownum=1; if Vsfqtjz='Y' THEN Vghlbbh:= Vjzlbbh; else select count(1) into V_COUNT0 from YS_JZSJB0 where rqks00<=Vdqrq00 and rqjz00>=Vdqrq00 and sjks00<=Vdqsj00 and sjjz00>=Vdqsj00 and SFYX00='Y'; if V_COUNT0>0 then Vghlbbh:= Vjzlbbh; end if; end if; else select count(1) into V_COUNT0 from YS_JZSJB0 where xqks00<=Vdqxq00 and xqjz00>=Vdqxq00 and SFYX00='Y'; if V_COUNT0>0 then select count(1) into V_COUNT0 from YS_JZSJB0 where xqks00<=Vdqxq00 and xqjz00>=Vdqxq00 and SFQTJZ='Y' and SFYX00='Y'; if V_COUNT0>0 THEN Vghlbbh:= Vjzlbbh; else select count(1) into V_COUNT0 from YS_JZSJB0 where xqks00<=Vdqxq00 and xqjz00>=Vdqxq00 and sjks00<=Vdqsj00 and sjjz00>=Vdqsj00 and SFYX00='Y'; if V_COUNT0>0 THEN Vghlbbh:= Vjzlbbh; end if; end if; end if; end if; if ls_SFYXPTH='0' then Vghlbbh:= Vjzlbbh; if Vjzlbbh= ' ' THEN Verrmsg:='该病人当天已经在这个医生处挂过一个普通号并且该医生没有有效的急诊号，不能重新挂号'; raise Ecustom; end if; end if; if V_COUNT10>0 then --如果普通号大于零,则返回的号别只能是急诊号 Vghlbbh:= Vjzlbbh; end if; elsif LS_PDJZFS = '1' then --通过登录部门是否急诊科来判断 select count(1) into V_COUNT0 from bm_bmbm00 where bmbh00=Pghks00 and bmmc00 like '%急诊%'; --急诊科挂号 if V_COUNT0 >0 then Vghlbbh:= Vjzlbbh; else Vghlbbh:= Vfjzlbbh; end if; elsif LS_PDJZFS = '2' then --不判断急诊号 Vghlbbh := Vghlbbh; end if; end if; --end if; --年老医保病人挂号时是否隐藏部分挂号类别 begin begin select trim(value0) into YS_NLYBBRGHSSFYCBFGHLB from XT_XTCS00 where NAME00='YS_NLYBBRGHSSFYCBFGHLB'; exception when others then YS_NLYBBRGHSSFYCBFGHLB:='N'; end; if YS_NLYBBRGHSSFYCBFGHLB<>'N' then begin begin select to_number(substr(YS_NLYBBRGHSSFYCBFGHLB,1,instr(YS_NLYBBRGHSSFYCBFGHLB,';')-1)) into YS_YBBRNL from dual; select ','||substr(YS_NLYBBRGHSSFYCBFGHLB,instr(YS_NLYBBRGHSSFYCBFGHLB,';')+1,length(trim(YS_NLYBBRGHSSFYCBFGHLB))-instr(YS_NLYBBRGHSSFYCBFGHLB,';'))||',' into YS_YCGHLBC from dual; exception when others then YS_NLYBBRGHSSFYCBFGHLB:='N'; end; if YS_NLYBBRGHSSFYCBFGHLB<>'N' then begin select count(*) into V_COUNT0 from BM_BRXXB0 where BRID00=Pbrid00 and FBBH00=3 and SF_YS_CSRQTONL(nvl(BRCSRQ,'20111027'))>=YS_YBBRNL; if (V_COUNT0>0) and (trim(YS_YCGHLBC) is not null) then Vghlbbh_clc:='-1'; --Vjzlbbh:=Vghlbbh; while length(Vghlbbh)>0 loop if instr(Vghlbbh,',')>0 then if instr(YS_YCGHLBC,','||substr(Vghlbbh,1,instr(Vghlbbh,',')-1)||',')=0 then if Vghlbbh_clc='-1' then Vghlbbh_clc:= substr(Vghlbbh,1,instr(Vghlbbh,',')-1); else Vghlbbh_clc:=Vghlbbh_clc||','||substr(Vghlbbh,1,instr(Vghlbbh,',')-1); end if; end if; Vghlbbh:=substr(Vghlbbh,instr(Vghlbbh,',')+1,length(Vghlbbh)-instr(Vghlbbh,',')); else if instr(YS_YCGHLBC,','||Vghlbbh||',')=0 then if Vghlbbh_clc='-1' then Vghlbbh_clc:= Vghlbbh; else Vghlbbh_clc:=Vghlbbh_clc||','||Vghlbbh; end if; end if; Vghlbbh:=''; end if; end loop; Vghlbbh:=Vghlbbh_clc; --Vghlbbh:=Vjzlbbh||' -- '||Vghlbbh; --测试比较用的 end if; end; end if; end; end if; --年老医保病人挂号时是否隐藏部分挂号类别 end; --##2017.09.25 ruanbh 如果bm_brxxb0.tssxmc=参数SF_ZZJESFZY的值，则挂免费号 select trim(nvl(max(VALUE0),'N')) into YS_TSSXMCMFGHLB FROM XT_XTCS00 where name00='YS_TSSXMCMFGHLB'; if YS_TSSXMCMFGHLB<>'N' then select count(*) into Vcounter from BM_BRXXB0 where BRID00=Pbrid00 and TSSXMC=(select substr(VALUE0,0,instr(VALUE0,'|')-1) from XT_XTCS00 where name00='SF_ZZJESFZY'); if Vcounter>0 then Vghlbbh:=YS_TSSXMCMFGHLB; end if; end if; --##2017.11.10 48小时挂号类别限制再挂号允许的挂号类别加上免费号 if ls_TKSYYXGHBRZGH='Y' and ls_TKSGHBXZYXGHLB <> 'N' and instr(Vghlbbh,'-1')=0 then if nvl(length(trim(Vghlbbh)),0) > 0 then Vghlbbh := Vghlbbh||',-1'; else Vghlbbh := '-1'; end if; end if; begin--20180827 限制未分发挂号类别的医生挂号 select count(*) into Vbmxz00 from ys_tsghff where ys0000=Pghys00; exception when others then return '-1'; end; if Vbmxz00=0 then return 0; else return Vghlbbh; end if; return Vghlbbh; exception when Ecustom then raise_application_error(-20010,Verrmsg); end;