CREATE OR REPLACE PROCEDURE SP_SST_GHDP_MZYYKS(
  P_YYRQ00 in  char,
  P_YYSDBZ in  number,
  P_ERRMSG out varchar2,   --出错信息
    r_cur1  out PKG_YY_QRY.MY_CURSOR --结果集游标
    )
AS
-- MODIFICATION HISTORY
-- Person      Date         Comments
-- dsm         2012.05.28   create
-- zhangyc     2014.01.21   增加剩余可用号源sykyhy
-- zhangyc     2014.02.08   增加过滤早上只显示12：00之前号源 下午只显示 17：30之前号源
-- zhangyc     2014.04.14   按专业科室分组SF_YSGHHB.GHZKBH=BM_ZKBM00.ZKBM00(+) by ZZJJK-20140317-001
-- yanghq      2018.02.26   在预约挂号界面在上午的时候要看到下午的号源
  ls_YHMSG0  varchar(200);
  E_ERROR    exception;
  ls_yyqsrq  varchar2(8);
  ls_yyjsrq  varchar2(8);
  ls_DQSJ00  varchar2(8);
  ls_yyts00  XT_XTCS00.VALUE0%type;
  LS_DQSD00  number(4);
  LS_GHJSSJ  varchar2(8);
  ls_YYBZ00  varchar2(1);
  LS_YYSDBZ  varchar2(2);
begin
  begin
    select trim(VALUE0) into ls_yyts00 from XT_XTCS00 where NAME00='YY_YYTQTS';
  exception
    when others then
     ls_yyts00:='7'   ;
  end;
  ls_DQSJ00:=to_char(sysdate,'HH24:MI:SS');
  if P_YYRQ00> to_char(sysdate,'YYYYMMDD') then
    ls_DQSJ00:='00:00:00';
  end if;
  ls_yyjsrq:=to_char(sysdate+to_number(ls_yyts00),'YYYYMMDD');
  ls_yyqsrq:=to_char(sysdate,'YYYYMMDD');
  select to_number(substr(to_char(sysdate,'HH24:MI:SS'),1,2)) into LS_DQSD00 from dual;
  if   LS_DQSD00>12 then LS_GHJSSJ:='18:00:00';
  else LS_GHJSSJ:='12:00:00';
  end if;

  if LS_DQSD00<12 and P_YYSDBZ=2 then
    ls_DQSJ00:= '12:00:00';
    LS_GHJSSJ:= '18:00:00';
  end if;
  ls_YYBZ00:='1';
  select  decode(sign('12'-LS_DQSD00),0,'1',1,'1','2') into LS_YYSDBZ from dual;
  if P_YYRQ00=to_char(sysdate,'YYYYMMDD') and  LS_YYSDBZ=P_YYSDBZ then
    ls_YYBZ00:='0';
     ls_DQSJ00:=to_char(sysdate,'HH24:MI:SS');
  end if;
  open r_cur1 for
  select trim(YYKSMC)YYKSMC,to_char(nvl(sum(sykyhy),0))sykyhy
from
(
  --按挂号科室名称
select  GHKSMC YYKSMC ,(select  count(1) from YS_HBHZXH b
         where b.GHHBID=a.GHHBID and nvl(b.BRXM00,'0')='0' and nvl(b.HZQSSJ,' ')<>' ' --and YYBZ00<>'6'
             and  B.HZQSSJ||':00'>=ls_DQSJ00
             and  B.HZQSSJ||':00'<=LS_GHJSSJ   --增加时间结束时间条件
             and ((YYBZ00='6' and ls_YYBZ00='1') or ls_YYBZ00='0')
           -- and  substrb(b.HZSJ00,1,5)||':00'<=ls_DQSJ00
           -- and  substrb(b.HZSJ00,7,5)||':00'>=ls_DQSJ00
           )sykyhy
      from vw_sf_ysghhb a where GHMZSJ>=LS_YYQSRQ and GHMZSJ<=LS_YYJSRQ and TZBZ00='0'
        and GHMZSJ=P_YYRQ00 and decode(sign('12'-substr(GHJSSJ,1,2)),0,'1',1,'1','2')=P_YYSDBZ
        and GHMZSJ||GHJSSJ>=P_YYRQ00||ls_DQSJ00 -- and GHMZSJ||GHJSSJ<=P_YYRQ00||LS_GHJSSJ
)  aa group by YYKSMC;

--按专科名称分组
/*select  nvl(GHZKMC,'未知专科') YYKSMC ,(select  count(1) from YS_HBHZXH b
         where b.GHHBID=a.GHHBID and nvl(b.BRXM00,'0')='0' and nvl(b.HZQSSJ,' ')<>' ' --and YYBZ00<>'6'
             and  B.HZQSSJ||':00'>=ls_DQSJ00
             and  B.HZQSSJ||':00'<=LS_GHJSSJ   --增加时间结束时间条件
             and ((YYBZ00='6' and ls_YYBZ00='1') or ls_YYBZ00='0')
           )sykyhy
          -- ,a.GHKSMC
      from vw_sf_ysghhb a where GHMZSJ>=LS_YYQSRQ and GHMZSJ<=LS_YYJSRQ and TZBZ00='0'
        and GHMZSJ=P_YYRQ00
        and decode(sign('12'-substr(GHJSSJ,1,2)),0,'1',1,'1','2')=P_YYSDBZ
        and GHMZSJ||GHJSSJ>=P_YYRQ00||ls_DQSJ00 -- and GHMZSJ||GHJSSJ<=P_YYRQ00||LS_GHJSSJ
)  aa group by YYKSMC;*/

exception
  WHEN NO_DATA_FOUND THEN
                P_ERRMSG:='数据没有找到!'||SQLERRM;
                --ROLLBACK;
end;