CREATE OR REPLACE PROCEDURE SP_SST_XCPH_XCPHDJ_YKT( P_Cardno IN varchar2, --就诊卡号 P_Organcode IN varchar2, --科室编号 P_Empid IN varchar2, --医生编号 P_ERRMESG out varchar2, --错误信息 p_CURSOR out PKG_YY_QRY.MY_CURSOR --结果集游标 ) AS LS_DQSD00 number(4); LS_GHJSSJ varchar2(8); LS_COUNT0 number; LS_CARDNO varchar2(220); LS_HZXH00 YS_HBHZXH.HZXH00%type; LS_GHHBID SF_YSGHHB.GHHBID%type; ls_BRID00 BM_BRXXB0.BRID00%type; --病人ID VYSGHHB SF_YSGHHB%rowtype; --医生挂号号表 LS_TSXX00 varchar2(500); LS_GHH000 SF_BRXXB0.GHH000%type; ls_DDRS00 number(5); LS_XCPHKS XT_XTCS00.VALUE0%type; ls_xb0000 BM_BRXXB0.brxb00%type; ls_nl0000 number(10); -- MODIFICATION HISTORY -- Person Date Comments -- qks 2013.03.27 Create -- qks 2013.04.08 修改Yysj00取值方式；取最小序号登记；SP_SF_BRXXB0_BRGH00增加入参Pcwts00 -- qks 2013.04.09 修改出参值 -- qks 2013.04.19 停诊不允许取号登记 -- qks 2013.04.23 退过号的号源,不允许自助机再取 -- qks 2015.11.16 结果集游标 增加ZSDZ00 -- qks 2015.11.19 03,07保留号也被病人取走；p_CURSOR.YSXM00同时增加“医师” -- qks 2016.02.22 自助机现场取号，不允许取临时排班号表 -- qks 2016.08.22 增加参数SST_LSPBXCPHKS：临时排班,允许取现场号科室编号,多个用逗号分开 begin select to_number(substr(to_char(sysdate,'HH24:MI:SS'),1,2)) into LS_DQSD00 from dual; if LS_DQSD00>12 then LS_GHJSSJ:='23:59:59'; else LS_GHJSSJ:='12:00:00'; end if; select SF_SST_GETICKH00_YKT(P_CARDNO) into LS_CARDNO from dual; begin select brid00 into LS_BRID00 from ic_yhxx00 where ICKH00 =LS_CARDNO and ZT0000 ='1'; exception when no_data_found then P_ERRMESG:='未找到该病人的信息,请确认是否在院内已经建档登记!'; return; end; --zhangjie 20210615 限制男性不能看妇科 select brxb00 into ls_xb0000 from bm_brxxb0 where brid00= LS_BRID00 ; if P_Organcode in (2193,10515) and ls_xb0000='男' then P_ERRMESG := '男性不能看妇科，请选择其它科室就诊！'; RETURN; end if ; --zhangjie 20210615 限制女性不能看男科 if P_Organcode in (2206) and ls_xb0000='女' then P_ERRMESG := '女性不能看男科，请选择其它科室就诊！'; RETURN; end if ; --zhangjie 20210615 限制大于18周岁不允许在儿科挂号 select (sysdate- to_date(decode(a.brcsrq,null,to_char(sysdate,'YYYYMMDD'),a.brcsrq),'YYYYMMDD'))/365 into ls_nl0000 From bm_brxxb0 a where brid00=LS_BRID00; if ls_nl0000>18 and P_Organcode='2192' then P_ERRMESG:='大于18周岁不允许在儿科挂号!'; RETURN ; end if; --取参数值 select nvl(max(trim(VALUE0)),'0') into LS_XCPHKS from XT_XTCS00 where NAME00 = 'SST_LSPBXCPHKS'; select nvl(min(C.GHHBID),0) into LS_GHHBID from SF_YSGHHB c,bm_bmbm00 d,bm_ygbm00 e where C.GHMZSJ=to_char(sysdate,'YYYYMMDD') and C.GHMZSJ||C.GHJSSJ>=to_char(sysdate,'YYYYMMDDHH24:MI:SS') and c.ghksbh=d.bmbh00 and c.GHYSBH=e.ygbh00 and c.ghksbh=P_Organcode and e.xkh000=P_Empid and (nvl(c.SFLSPB,'0')='0' or instrb(','||LS_XCPHKS||',',','||to_char(ghksbh)||',')>0) and Exists (select 1 from YS_HBHZXH where GHHBID=c.GHHBID and YXBZ00='1' and BRXM00 is null and ZY0000 is null and sign(instrb(','||c.ynblmx||',',','||HZXH00||','))=0 and sign(instrb(','||c.ynblmx||',',',0'||HZXH00||','))=0) and c.GHJSSJ<=LS_GHJSSJ; if LS_GHHBID = 0 then P_ERRMESG := '该医生挂号已满,建议您选择其他医生就诊！'; RETURN; end if; select * into VYSGHHB from SF_YSGHHB where ghhbid=LS_GHHBID; if VYSGHHB.TZBZ00 = '1' then P_ERRMESG := '该医生已停诊,建议您选择其他医生就诊！'; RETURN; end if; select nvl(min(HZXH00),'0') into LS_HZXH00 from YS_HBHZXH where GHHBID=LS_GHHBID and YXBZ00='1' and BRXM00 is null and ZY0000 is null and sign(instrb(','||VYSGHHB.ynblmx||',',','||HZXH00||','))=0 and sign(instrb(','||VYSGHHB.ynblmx||',',',0'||HZXH00||','))=0; if LS_HZXH00 = '0' then P_ERRMESG := '该医生挂号已满,建议您选择其他医生就诊！'; RETURN; end if; SP_SF_BRXXB0_BRGH00(LS_BRID00,-99999,VYSGHHB.GHKSBH,VYSGHHB.GHYSBH,VYSGHHB.GHHBID,'N',1,LS_HZXH00,'N',LS_GHH000,VYSGHHB.GHYSBH,VYSGHHB.GHKSBH,'N',LS_COUNT0,P_ERRMESG,LS_TSXX00,0,0,'1'); if LS_COUNT0<>1 then --Rollback; if P_ERRMESG is null then P_ERRMESG := '取号失败!'; end if; return; end if; -- open p_CURSOR for -- select P_Cardno Cardno , --病人卡号 -- f.BRXM00 Xming0 , --医生姓名 -- d.bmmc00 Ksmc00 , --科室名称 -- e.zwxm00 Ysxm00 , --医生姓名 -- to_char(sysdate,'YYYY-MM-DD') Yyrq00, --预约日期 -- --substrb(to_char(to_date(c.HZQSRQ||c.HZQSSJ,'YYYYMMDDHH24:MI:SS')+ -- -- nvl((select PJZZSJ from SF_YSGHHB where GHHBID=c.YSPBID),5)*(c.HZXH00-1)/1440,'YYYYMMDDHH24:MI:SS'),9,8) Yysj00 , --预约时间 -- case -- when substrb(to_char(to_date(c.HZQSRQ||c.HZQSSJ,'YYYYMMDDHH24:MI:SS')+nvl((select PJZZSJ from SF_YSGHHB where GHHBID=c.YSPBID),5)*(c.HZXH00-1)/1440,'YYYYMMDDHH24:MI:SS'),9,8)c.HZJZSJ then -- c.HZJZSJ -- else -- substrb(to_char(to_date(c.HZQSRQ||c.HZQSSJ,'YYYYMMDDHH24:MI:SS')+nvl((select PJZZSJ from SF_YSGHHB where GHHBID=c.YSPBID),5)*(c.HZXH00-1)/1440,'YYYYMMDDHH24:MI:SS'),9,8) -- end Yysj00, --预约时间 -- c.HZXH00 Yyhm00 --预约号码 -- from SF_HZBR00 c,bm_bmbm00 d,bm_ygbm00 e,BM_BRXXB0 f where C.GHH000=LS_GHH000 -- and C.DJRQ00=to_char(sysdate,'YYYYMMDD') -- and C.GHLB00=-99999 -- and c.GHKS00=d.bmbh00 -- and c.GHYS00=e.ygbh00 -- and c.BRID00=f.BRID00; SELECT COUNT(1) into ls_DDRS00 FROM vw_ys_hzbr00 where jzks00=VYSGHHB.GHKSBH and jzys00=VYSGHHB.GHYSBH and dlxh00>0 and HZXH00