--科室类别BS55025
create or replace view esb_getksdl as
select   distinct c.BMBH00 DEPT_CODE,
                      c.BMMC00 DEPT_NAME,
                      c.PXXH00 SORT_NO,
                   -- '' HOSPITAL_DISTRICT_FLAG,
                   decode(B.YYID00,'224141','1','2')  HOSPITAL_DISTRICT_FLAG,
                      c.BH0000
                      from VW_YS_KSGHHB b,BM_BMBM00 a,BM_BMBM00 c
       where  b.GHKSBH=a.BMBH00 and PBLX00='0'
           and substrb(a.BH0000,1,length(a.BH0000)-2)=c.BH0000
           and b.GHMZSJ=to_char(sysdate,'YYYYMMDD')
           and b.GHJSSJ>=to_char((sysdate-1/48),'HH24:mi:ss')
           and b.GHQSSJ<=to_char((sysdate+1/48),'HH24:mi:ss')
           and not exists(select 1 from VW_YY_YYBDKS where BMBH00=b.GHKSBH)
           order by c.PXXH00,c.BH0000


--科室BS55012
CREATE OR REPLACE PROCEDURE ESB_XCYYKSLB(
  P_ZDBH00   in varchar2,   --终端编号
  P_FZXBH0   in varchar2,   --参保机构编号
  P_YYJGDM   in varchar2,   --医院机构代码
  P_CARDTYPE in varchar2,   --0 代表院内卡  1 代表社保卡
  P_CARDNO   in varchar2,   --IC卡号
  P_BH0000   in varchar2,
  P_ERRMSG   out varchar2,   --出错信息
  R_CUR1     out PKG_YY_QRY.MY_CURSOR --结果集游标
    --BMMC00：部门名称：BMBH00 部门编号：PXXH00：排序序号  BH0000:编号  SJBH00:上级部门对应编号 SJBMMC:上级部门对应名称
    )
AS
-- MODIFICATION HISTORY
-- Person      Date         Comments
-- zhangyc     2014.05.05   create
-- dsm         2016.11.01   利用BM_TYZD00.ZDMC0='预约科室邦定'设置 for ZZJJK-20161027-001
-- zhangyc     2016.12.26   增加DLBMBH.QYCKBH 条件限制
-- zhangyc     2016.12.29   增加独立的参数(SST_FZJMXSYGSJD)控制与参数YS_FZJMXSYGSJD 分开
-- qks         2017.02.04   增加参数SST_XCQHFHSJFL：获取有效预约科室 是否直接返回预约科室对应的上级分类编号以及名称
-- wjl         2021.12.14   增加条件P_YYJGDM判断（1总医院，2中医院），增加游标集出参YYJGDM
   YS_FZJMXSYGSJD  varchar2(100);
   LS_KSSJCE  number(10);
   LS_JSSJCE  number(10);
   LS_SFJSRQ  varchar2(2);
   LS_COUNT0  number(10);
   LS_GHMZSJ  varchar2(8);
   LS_GHKSSJ  varchar2(8);
   LS_GHJSSJ  varchar2(8);
   LS_ERRORS  varchar2(40);
   LS_BRID00  BM_BRXXB0.BRID00%type;
   LS_CARDNO  IC_YHXX00.ICKH00%type;
   LS_ZYZT00  varchar2(10);
   LS_QYCKBH  SST_YGBHDY.QYCKBH%type;
   LS_FHSJFL  varchar2(100);
   ls_bh0000  bm_bmbm00.bh0000%type;
begin
  LS_KSSJCE:=0; --开始时间差
  LS_JSSJCE:=0; --结束时间差
  LS_COUNT0:=0; --计数
  LS_SFJSRQ:='Y';--是否有结束日期
  LS_GHMZSJ:=to_char(sysdate,'YYYYMMDD');
  LS_ERRORS:='1';
  ls_bh0000:=nvl(P_BH0000,'0');
  if nvl(P_CARDNO,'0')<>'0' then
    select SF_SST_GETICKH00(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_ERRMSG:='未找到该病人的信息,请确认是否在院内已经建档登记!';
        return;
    end;

    select ZYZT00 Into LS_ZYZT00 from vw_ys_brxxb0 where BRID00=LS_BRID00;
    if LS_ZYZT00='1' then
         P_ERRMSG:='该病人为住院病人,不能在门诊就诊!';
        return;
    end if;
  end if;

  --自助机现场取号"获取有效预约科室"是否直接返回预约科室对应的上级分类编号以及名称,N:否 Y:是
  select nvl(trim(max(value0)),'N') into LS_FHSJFL from XT_XTCS00 where name00='SST_XCQHFHSJFL';
--  LS_FHSJFL:='Y';
   --自助机现场取号是否只显示一个时间段内的医生  N:否 Y: 是 ,M可显示排班时间可延长一个小时,K可显示排班时间开始前1小时 默认为N
  begin
    --select trim(VALUE0) into YS_FZJMXSYGSJD from XT_XTCS00 where NAME00='YS_FZJMXSYGSJD';
    select trim(VALUE0) into YS_FZJMXSYGSJD from XT_XTCS00 where NAME00='SST_FZJMXSYGSJD';
  exception
    when others then
     YS_FZJMXSYGSJD:='K'   ;
  end;
  if YS_FZJMXSYGSJD = 'Y' then
     LS_KSSJCE:=1; --排班结束时间后半小时
     LS_JSSJCE:=3; --排班时间开始前1.5小时
     LS_SFJSRQ:='Y';
  elsif YS_FZJMXSYGSJD = 'M' then
      LS_KSSJCE:=2; --排班结束时间后1个小时
      LS_JSSJCE:=3; --排班时间开始前1.5小时
      LS_SFJSRQ:='Y';
  elsif YS_FZJMXSYGSJD = 'K' then
      LS_KSSJCE:=2; --排班结束时间后1个小时
      LS_JSSJCE:=2; --排班时间开始前1小时
      LS_SFJSRQ:='Y';
  elsif YS_FZJMXSYGSJD = 'P' then
      LS_KSSJCE:=1; --排班结束时间后半个小时
      LS_JSSJCE:=1; --排班时间开始前半小时
      LS_SFJSRQ:='Y';
  else
      LS_KSSJCE:=2; --排班结束时间后1个小时
      LS_JSSJCE:=0;
      LS_SFJSRQ:='N';
  end if;

  --YS_FZJMXSYGSJD:='N';
  LS_GHKSSJ:=to_char((sysdate-LS_KSSJCE/48),'HH24:mi:ss');
  LS_GHJSSJ:=to_char((sysdate+LS_JSSJCE/48),'HH24:mi:ss');

   begin
      select QYCKBH into LS_QYCKBH   from SST_YGBHDY where ZZJBH0=P_ZDBH00;
     exception
       when others then
       LS_QYCKBH:='0';
   end;
   LS_QYCKBH:=nvl(LS_QYCKBH,0);

  select count(1) into LS_COUNT0 from
  (
  Select distinct b.GHKSMC BMMC00,a.BMBH00,a.PXXH00,a.BH0000 from VW_YS_KSGHHB b,BM_BMBM00 a
  where  b.GHKSBH=a.BMBH00 and PBLX00='0'
         and b.GHMZSJ=LS_GHMZSJ
         and b.GHJSSJ>=LS_GHKSSJ
         and decode(B.YYID00,'224141','1','2')=P_YYJGDM
         and ((b.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N'))
         and ((LS_QYCKBH='0' and 1=1) or (LS_QYCKBH<>'0' and instrb(','||LS_QYCKBH||',',','||to_char(b.GHKSBH)||',')>0))
         and not exists(select 1 from VW_YY_YYBDKS where BMBH00=b.GHKSBH)
         order by a.PXXH00,a.BH0000
  );
  if LS_COUNT0<=0 then
     P_ERRMSG:='没有排班记录,不能操作！';
    return;
  else
    if LS_FHSJFL = 'Y' then
      open r_cur1 for
      ----BMMC00：上级分类名称：BMBH00 上级分类部门编号：PXXH00：上级分类排序序号  BH0000:上级分类编号
      select distinct c.BMBH00 DEPT_CODE,
                      c.BMMC00 DEPT_NAME,
                      c.PXXH00 SORT_NO,
                      c.BH0000 NUMBERS,
            decode(B.YYID00,'224141','1','2') YYJGDM
                      from VW_YS_KSGHHB b,BM_BMBM00 a,BM_BMBM00 c
       where  b.GHKSBH=a.BMBH00 and PBLX00='0'
           and substrb(a.BH0000,1,length(a.BH0000)-2)=c.BH0000
           and b.GHMZSJ=LS_GHMZSJ
           and b.GHJSSJ>=LS_GHKSSJ
       and decode(B.YYID00,'224141','1','2')=P_YYJGDM
           and ((b.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N'))
           and ((LS_QYCKBH='0' and 1=1) or (LS_QYCKBH<>'0' and instrb(','||LS_QYCKBH||',',','||to_char(b.GHKSBH)||',')>0))
           and not exists(select 1 from VW_YY_YYBDKS where BMBH00=b.GHKSBH)
           order by c.PXXH00,c.BH0000;
    else
      open r_cur1 for
      ----BMMC00：部门名称：BMBH00 部门编号：PXXH00：排序序号  BH0000:编号
      select distinct a.BMBH00  DEPT_CODE,
                       b.GHKSMC DEPT_NAME,
      a.PXXH00 SORT_NO,
      a.BH0000 NUMBERS,
    decode(B.YYID00,'224141','1','2') YYJGDM
       from VW_YS_KSGHHB b,BM_BMBM00 a
       where  b.GHKSBH=a.BMBH00 and PBLX00='0'
           and b.GHMZSJ=LS_GHMZSJ
           and b.GHJSSJ>=LS_GHKSSJ
       and decode(B.YYID00,'224141','1','2')=P_YYJGDM
           and ((b.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N'))
           and ((LS_QYCKBH='0' and 1=1) or (LS_QYCKBH<>'0' and instrb(','||LS_QYCKBH||',',','||to_char(b.GHKSBH)||',')>0))
           and not exists(select 1 from VW_YY_YYBDKS where BMBH00=b.GHKSBH)
           and ((INSTR(BH0000,ls_bh0000)>0 and bh0000<>ls_bh0000) or ls_bh0000='0')
           order by a.PXXH00,a.BH0000;
    end if;
  end if;
exception
  when no_data_found then
      P_ERRMSG:=substrb(P_ERRMSG||'获取有效预约科室失败！'||SQLERRM||'错误位置:'||Ls_Errors,1,255);
    when others then
      P_ERRMSG:=substrb(P_ERRMSG||'获取有效预约科室失败！'||SQLERRM||'错误位置:'||Ls_Errors,1,255);
end;

--医生BS55013
CREATE OR REPLACE PROCEDURE ESB_XCYYYSLB(
  P_ZDBH00   in varchar2,   --终端编号
  P_FZXBH0   in varchar2,   --参保机构编号
  P_YYJGDM   in varchar2,   --医院机构代码
  P_CARDTYPE in varchar2,   --0 代表院内卡  1 代表社保卡
  P_CARDNO   in varchar2,   --IC卡号
  P_BMBH00   in varchar2,   --部门编号=SP_SST_YNYY_MZGHKS.R_CUR1.BMBH00
  P_ERRMSG   out varchar2,   --出错信息
  R_CUR1     out PKG_YY_QRY.MY_CURSOR --结果集游标
    --GHLBMC:挂号类别名称(参数SST_XCQHYSLBMC=Y时为医生类别名称) GHKSBH:挂号科室编码 YSYGBH:医生编号 YSXM00:医生姓名 XKH000:胸卡号 XSXH00：显示序号 GHHBID:挂号号表id  GHZKMC:专科名称 CKMC00:窗口名称,GHKSMC:挂号科室名称
    )
AS
-- MODIFICATION HISTORY
-- Person      Date         Comments
-- zhangyc     2014.05.05   create
-- dsm         2016.05.30   R_CUR1多返回YYSDBZ for YYGH-20160518-002
-- qks         2016.07.29   上午号凌晨0点后就可以取，下午号要到中午12点后才可以取,参数YS_FZJMXSYGSJD改为D; for ZZJJK-20160723-002
-- dsm         2016.10.31   多返回等待人数 for ZZJJK-20161027-001
-- zhangyc     2016.12.26   增加DLBMBH.QYCKBH 条件限制
-- zhangyc     2016.12.29   增加独立的参数(SST_FZJMXSYGSJD)控制与参数YS_FZJMXSYGSJD 分开
-- qks         2017.02.04   增加参数SST_XCQHFHSJFL：获取有效预约科室 是否直接返回预约科室对应的上级分类编号以及名称
-- qks         2017.02.06   增加参数SST_XCQHYSLBMC：自助机现场取号"获取有效排班医生",返回的挂号类别名称,是否改用医生类别名称
-- qks         2017.02.22   R_CUR1增加字段:GHKSMC 挂号科室名称
-- qks         2017.02.27   福清市医院自助机接口调用出现递归SQL级别1出现错误;
-- LIUJ        2017.03.16   出参排序增加诊室排序
-- zj          2017.09.01   限制住院病人不允许取号
-- zj          2017.09.18   限制14岁以下儿童不能取内科号
   YS_FZJMXSYGSJD  varchar2(100);
   LS_KSSJCE  number(10);
   LS_JSSJCE  number(10);
   LS_SFJSRQ  varchar2(2);
   LS_COUNT0  number(10);
   LS_GHMZSJ  varchar2(8);
   LS_GHKSSJ  varchar2(8);
   LS_GHJSSJ  varchar2(8);
   LS_ERRORS  varchar2(40);
   LS_BRID00  BM_BRXXB0.BRID00%type;
   LS_CARDNO  IC_YHXX00.ICKH00%type;
   LS_QYCKBH  SST_YGBHDY.QYCKBH%type;
   LS_FHSJFL  varchar2(100);
   LS_FHYSLB  varchar2(100);
   ls_nl0000  number(10,2);
begin
--P_ERRMSG:='系统故障，请到护士分诊台取号！';
--return;



  LS_KSSJCE:=0; --开始时间差
  LS_JSSJCE:=0; --结束时间差
  LS_COUNT0:=0; --计数
  LS_SFJSRQ:='Y';--是否有结束日期
  LS_GHMZSJ:=to_char(sysdate,'YYYYMMDD');
  LS_ERRORS:='1';
  if nvl(P_CARDNO,'0')<>'0' then
    select SF_SST_GETICKH00(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_ERRMSG:='未找到该病人的信息,请确认是否在院内已经建档登记!';
        return;
    end;
  end if;

SELECT trunc((sysdate-TO_DATE(BRCSRQ,'YYYYMMDD'))/365,2) INTO ls_nl0000 FROM BM_BRXXB0 WHERE BRID00=LS_BRID00;
   if (P_BMBH00=660 or P_BMBH00=706) AND ls_nl0000<=14 then
     P_ERRMSG:='14岁以下儿童请到儿科就诊！';
    return;
   END if;
--住院病人不允许取号
    if nvl(P_CARDNO,'0')<>'0' then
    select SF_SST_GETICKH00(P_CARDNO) into LS_CARDNO from dual;
    begin
      select zyzt00 into P_ERRMSG from ic_yhxx00 a,bm_brxxb0 b where  ICKH00 =LS_CARDNO and ZT0000 ='1' and a.brid00=b.brid00;
    if P_ERRMSG=1 then
     P_ERRMSG:='住院病人不允许取号！';
      return;
    end if ;
    P_ERRMSG :='';
    end;
  end if;

  --自助机现场取号"获取有效预约科室"是否直接返回预约科室对应的上级分类编号以及名称,N:否 Y:是
  select nvl(trim(max(value0)),'N') into LS_FHSJFL from XT_XTCS00 where name00='SST_XCQHFHSJFL';

  --自助机现场取号"获取有效排班医生",返回的挂号类别名称,是否改用医生类别名称,N:否 Y:是,默认为N
  select nvl(trim(max(value0)),'N') into LS_FHYSLB from XT_XTCS00 where name00='SST_XCQHYSLBMC';

  --自助机现场取号是否只显示一个时间段内的医生  N:否 Y: 是 ,M可显示排班时间可延长一个小时,K可显示排班时间开始前1小时 默认为N
  begin
    --select trim(VALUE0) into YS_FZJMXSYGSJD from XT_XTCS00 where NAME00='YS_FZJMXSYGSJD';
    select trim(VALUE0) into YS_FZJMXSYGSJD from XT_XTCS00 where NAME00='SST_FZJMXSYGSJD';
  exception
    when others then
     YS_FZJMXSYGSJD:='K'   ;
  end;
  if YS_FZJMXSYGSJD = 'Y' then
     LS_KSSJCE:=1; --排班结束时间后半小时
     LS_JSSJCE:=3; --排班时间开始前1.5小时
     LS_SFJSRQ:='Y';
  elsif YS_FZJMXSYGSJD = 'M' then
      LS_KSSJCE:=2; --排班结束时间后1个小时
      LS_JSSJCE:=3; --排班时间开始前1.5小时
      LS_SFJSRQ:='Y';
  elsif YS_FZJMXSYGSJD = 'K' then
      LS_KSSJCE:=2; --排班结束时间后1个小时
      LS_JSSJCE:=2; --排班时间开始前1小时
      LS_SFJSRQ:='Y';
  elsif YS_FZJMXSYGSJD = 'D' then
      LS_KSSJCE:=2; --排班结束时间后1个小时
      LS_JSSJCE:=0; --排班时间开始前1小时
      LS_SFJSRQ:='D';
   elsif YS_FZJMXSYGSJD = 'P' then
      LS_KSSJCE:=1; --排班结束时间后半个小时
      LS_JSSJCE:=1; --排班时间开始前半小时
      LS_SFJSRQ:='Y';       
  else
      LS_KSSJCE:=2; --排班结束时间后1个小时
      LS_JSSJCE:=0;
      LS_SFJSRQ:='N';
  end if;
  --YS_FZJMXSYGSJD:='N';
  LS_GHKSSJ:=to_char((sysdate-LS_KSSJCE/48),'HH24:mi:ss');
  LS_GHJSSJ:=to_char((sysdate+LS_JSSJCE/48),'HH24:mi:ss');

  begin
     select QYCKBH into LS_QYCKBH   from SST_YGBHDY where ZZJBH0=P_ZDBH00;
    exception
      when others then
      LS_QYCKBH:='0';
  end;
  LS_QYCKBH:=nvl(LS_QYCKBH,0);

  if LS_FHSJFL = 'Y' then
    select count(1) into LS_COUNT0
    from VW_YS_KSGHHB a,BM_YGBM00 b,BM_GHLBB0 c,bm_bmbm00 d,bm_bmbm00 e
         where a.GHYSBH=b.YGBH00  and a.GHLBBH=c.LBBH00
           and (a.GHKSBH=d.BMBH00 or exists(select 1 from VW_YY_YYBDKS where ZBMBH0=d.BMBH00 and A.GHKSBH=BMBH00) )
           and e.BMBH00=P_BMBH00
           and substrb(d.BH0000,1,length(d.BH0000)-2)=e.BH0000
           and a.GHMZSJ=LS_GHMZSJ
           and a.GHJSSJ>=LS_GHKSSJ
           and ((LS_QYCKBH='0' and 1=1) or (LS_QYCKBH<>'0' and instrb(','||LS_QYCKBH||',',','||to_char(a.GHKSBH)||',')>0))
           and ((a.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N')
                 or (LS_SFJSRQ='D') and (a.GHQSSJ>'12:00:00' and to_char(sysdate,'HH24:mi:ss')>='12:00:00' or a.GHQSSJ<='12:00:00')
                );

    if LS_COUNT0<=0 then
         P_ERRMSG:='没有排班记录,不能操作！';
      return;
    else
        open r_cur1 for
        --GHLBMC:挂号类别名称 GHKSBH:挂号科室编码 YSYGBH:医生编号 YSXM00:医生姓名 XKH000:胸卡号 XSXH00：显示序号 GHHBID:挂号号表id  GHZKMC:专科名称 CKMC00:窗口名称
        select distinct  c.XSXH00 SORT_NO,
        h.cjr000 CREATER_CODE,
        b.pysm00 PINYIN_CODE,
        b.XKH000 DR_NO,
        b.ZWXM00||'('||decode(LS_FHYSLB,'Y',h.YSLBMC,decode(instr(LBMC00,'号'),0,c.LBMC00,substr(c.LBMC00,1,instr(LBMC00,'号')-1)))||')' as REGIST_CATEG_NAME,
         a.GHKSBH DEPT_CODE,
         b.ZWXM00 as DR_NAME,
         b.YGBH00 as DR_CODE,
         a.GHHBID REGIST_ID,
         GHZKMC DEPT_ALIAS,
         CKMC00 WIN_NAME ,
         SF_SST_GetYYSDBZ(a.GHQSSJ,a.GHJSSJ) TIME_DISCR,
         (select count(*) from SF_HZBR00 where YSPBID=a.GHHBID and HZZT00='0') WAIT_NUMBER,
         GHZKMC||'('||(select decode(nvl(Min(HZXH00),0),0,'无',nvl(Min(HZXH00),0)) from YS_HBHZXH m,sf_ysghhb i where m.GHHBID=i.GHHBID and BRXM00 is null and YYBZ00<>'10' and ((YNBLMX is null) or  (YNBLMX is not null and instrb(','||trim(YNBLMX)||',',','||trim(HZXH00)||',')<=0) ) and m.ghhbid=a.ghhbid)||'号)' DEPT_NAME,
     (select decode(nvl(Min(HZXH00),0),0,'0',nvl(Min(HZXH00),0)) from YS_HBHZXH m,sf_ysghhb i where m.GHHBID=i.GHHBID  and BRXM00 is null and YYBZ00<>'10'  and ((YNBLMX is null) or  (YNBLMX is not null and instrb(','||trim(YNBLMX)||',',','||trim(HZXH00)||',')<=0) ) and m.ghhbid=a.ghhbid) WAIT_NO  --是否有号
        from VW_YS_KSGHHB a,BM_YGBM00 b,BM_GHLBB0 c,bm_bmbm00 d,bm_bmbm00 e,bm_yslb00 h
             where a.GHYSBH=b.YGBH00  and a.GHLBBH=c.LBBH00
               and ((LS_QYCKBH='0' and 1=1) or (LS_QYCKBH<>'0' and instrb(','||LS_QYCKBH||',',','||to_char(a.GHKSBH)||',')>0))
               and (a.GHKSBH=d.BMBH00  or exists(select 1 from VW_YY_YYBDKS where ZBMBH0=d.BMBH00 and A.GHKSBH=BMBH00) )
               and e.BMBH00=P_BMBH00 and a.tzbz00='0'
               and substrb(d.BH0000,1,length(d.BH0000)-2)=e.BH0000
               and a.GHMZSJ=LS_GHMZSJ
               and a.GHJSSJ>=LS_GHKSSJ
               and b.YSLB00 = h.YSLB00(+)
               and ((a.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N')
                   or (LS_SFJSRQ='D') and (a.GHQSSJ>'12:00:00' and to_char(sysdate,'HH24:mi:ss')>='12:00:00' or a.GHQSSJ<='12:00:00')
                   )
               order by h.cjr000,b.pysm00 ;
    end if;
  else
    select count(1) into LS_COUNT0
    from VW_YS_KSGHHB a,BM_YGBM00 b,BM_GHLBB0 c
         where a.GHYSBH=b.YGBH00  and a.GHLBBH=c.LBBH00 and a.tzbz00='0'
           and (a.GHKSBH=P_BMBH00 or exists(select 1 from VW_YY_YYBDKS where ZBMBH0=P_BMBH00 and A.GHKSBH=BMBH00) )
           and a.GHMZSJ=LS_GHMZSJ
           and a.GHJSSJ>=LS_GHKSSJ
           and ((LS_QYCKBH='0' and 1=1) or (LS_QYCKBH<>'0' and instrb(','||LS_QYCKBH||',',','||to_char(a.GHKSBH)||',')>0))
           and ((a.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N')
                 or (LS_SFJSRQ='D') and (a.GHQSSJ>'12:00:00' and to_char(sysdate,'HH24:mi:ss')>='12:00:00' or a.GHQSSJ<='12:00:00')
                );

    if LS_COUNT0<=0 then
         P_ERRMSG:='没有排班记录,不能操作！';
      return;
    else
        open r_cur1 for
        --GHLBMC:挂号类别名称 GHKSBH:挂号科室编码 YSYGBH:医生编号 YSXM00:医生姓名 XKH000:胸卡号 XSXH00：显示序号 GHHBID:挂号号表id  GHZKMC:专科名称 CKMC00:窗口名称
        select distinct c.XSXH00 SORT_NO,
        NULL CREATER_CODE,
        NULL  PINYIN_CODE,
        b.XKH000 DR_NO,
        b.ZWXM00||'('||decode(LS_FHYSLB,'Y',h.YSLBMC,decode(instr(LBMC00,'号'),0,c.LBMC00,substr(c.LBMC00,1,instr(LBMC00,'号')-1)))||')' as REGIST_CATEG_NAME, 
        a.GHKSBH DEPT_CODE,
         b.ZWXM00 as DR_NAME,
         b.YGBH00 as DR_CODE,
         a.GHHBID REGIST_ID ,
         GHZKMC DEPT_ALIAS,
         CKMC00 WIN_NAME ,
         SF_SST_GetYYSDBZ(a.GHQSSJ,a.GHJSSJ) TIME_DISCR,
         (select count(*) from SF_HZBR00 where YSPBID=a.GHHBID and HZZT00='0') WAIT_NUMBER,
         (select BMMC00 from bm_bmbm00 where BMBH00=a.GHKSBH)||(select nvl(Min(HZXH00),0) from YS_HBHZXH m,sf_ysghhb i where m.GHHBID=i.GHHBID  and BRXM00 is null and YYBZ00<>'10'      and ((YNBLMX is null) or  (YNBLMX is not null and instrb(','||trim(YNBLMX)||',',','||trim(HZXH00)||',')<=0) ) and m.ghhbid=a.ghhbid) DEPT_NAME,
       (select decode(nvl(Min(HZXH00),0),0,'0',nvl(Min(HZXH00),0)) from YS_HBHZXH m,sf_ysghhb i where m.GHHBID=i.GHHBID   and BRXM00 is null and YYBZ00<>'10'      and ((YNBLMX is null) or  (YNBLMX is not null and instrb(','||trim(YNBLMX)||',',','||trim(HZXH00)||',')<=0) ) and m.ghhbid=a.ghhbid) WAIT_NO
        from VW_YS_KSGHHB a,BM_YGBM00 b,BM_GHLBB0 c,bm_yslb00 h
             where a.GHYSBH=b.YGBH00  and a.GHLBBH=c.LBBH00 and a.tzbz00='0'
               and ((LS_QYCKBH='0' and 1=1) or (LS_QYCKBH<>'0' and instrb(','||LS_QYCKBH||',',','||to_char(a.GHKSBH)||',')>0))
               and (a.GHKSBH=P_BMBH00  or exists(select 1 from VW_YY_YYBDKS where ZBMBH0=P_BMBH00 and A.GHKSBH=BMBH00) )
               and a.GHMZSJ=LS_GHMZSJ
               and a.GHJSSJ>=LS_GHKSSJ
               and b.YSLB00 = h.YSLB00(+)
               and ((a.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N')
                   or (LS_SFJSRQ='D') and (a.GHQSSJ>'12:00:00' and to_char(sysdate,'HH24:mi:ss')>='12:00:00' or a.GHQSSJ<='12:00:00')
                   )
              order by a.GHKSBH,c.XSXH00,b.XKH000;
    end if;
  end if;
exception
  when no_data_found then
      P_ERRMSG:=substrb(P_ERRMSG||'获取有效排班医生失败！'||SQLERRM||'错误位置:'||Ls_Errors,1,255);
    when others then
      P_ERRMSG:=substrb(P_ERRMSG||'获取有效排班医生失败！'||SQLERRM||'错误位置:'||Ls_Errors,1,255);
end;