CREATE OR REPLACE PROCEDURE SP_SST_YNYY_MZGHKS(
  P_ZDBH00   in varchar2,   --终端编号
  P_FZXBH0   in varchar2,   --参保机构编号
  P_YYJGDM   in varchar2,   --医院机构代码
  P_CARDTYPE in varchar2,   --0 代表院内卡  1 代表社保卡
  P_CARDNO   in varchar2,   --IC卡号
  P_ERRMSG   out varchar2,   --出错信息
  R_CUR1     out PKG_YY_QRY.MY_CURSOR --结果集游标
    --BMMC00：部门名称：BMBH00 部门编号：PXXH00：排序序号  BH0000:编号
    )
AS
-- MODIFICATION HISTORY
-- Person      Date         Comments
-- zhangyc     2014.05.05   create
   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);
begin
  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;

  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: 是 ,M可显示排班时间可延长一个小时 默认为N
  begin
    select trim(VALUE0) into YS_FZJMXSYGSJD from XT_XTCS00 where NAME00='YS_FZJMXSYGSJD';
  exception
    when others then
     YS_FZJMXSYGSJD:='N'   ;
  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';
  else
      LS_KSSJCE:=2; --排班结束时间后1个小时
      LS_JSSJCE:=0;
      LS_SFJSRQ:='N';
  end if;
 LS_GHKSSJ:=to_char((sysdate-LS_KSSJCE/48),'HH24:mi:ss');
 LS_GHJSSJ:=to_char((sysdate+LS_JSSJCE/48),'HH24:mi:ss');

 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 B.TZBZ00='0'
        and ((b.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N'))
        order by a.PXXH00,a.BH0000
 );
 if LS_COUNT0<=0 then
     P_ERRMSG:='没有排班记录,不能操作！';
   return;
 else
    open r_cur1 for
    ----BMMC00：部门名称：BMBH00 部门编号：PXXH00：排序序号  BH0000:编号
    select distinct b.GHKSMC BMMC00,a.BMBH00,a.PXXH00,a.BH0000,TRIM(A.YYID00) FYID00,(SELECT TRIM(YYJC00) FROM XT_YYXX00 WHERE YYID00=A.YYID00) FYMC00 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 B.TZBZ00='0'
        and ((b.GHQSSJ<=LS_GHJSSJ and LS_SFJSRQ='Y') or (LS_SFJSRQ='N'))
        order by a.PXXH00,a.BH0000;
 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;