insert into XT_XTCS00(NAME00,VALUE0,FLAG00,BZ0000,FL0000,SYXT00)values
('MZHS_GQYYSFZDBH','N','1','过期预约是否自动补号源，Y表示过期预约自动补号源，N不自动补号源，默认N','过程中SP_YY_GQZDSFHY','999');
commit;

CREATE OR REPLACE PROCEDURE SP_YY_GQZDSFHY
as
  LS_YCSJ00  number(5);
  LS_NOWSJ0  varchar2(10);
  Vtmpstr     varchar2(30);        --临时变量
  Vmaxhzh     number(5);           --最大候诊序号
  Vcounter    number(10);
  LS_GQYYSFZDBH varchar2(10);
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm         2016.12.13  create for MZYS-20161208-002
-- dsm         2017.03.10  在0点到7点之间任务不做处理 for YYGH-20170310-001
-- dsm         2017.03.20  过期号源，作废原来号源在最后加一条新号源 for YYGH-20170320-001
-- dsm         2017.03.26  加异常处理归档 MZYS-20170424-002
-- yanghq      2018.04.18  修改参数名称YS_YYJSSJYCSJ for MZHS-20180413-001
begin
  select to_char(sysdate,'HH24:MI:SS') into LS_NOWSJ0 from dual;
  if LS_NOWSJ0>='00:00:00' and LS_NOWSJ0<='07:00:00' then
    return;
  end if;
  select to_number(nvl(max(trim(VALUE0)),'0')) into LS_YCSJ00 from XT_XTCS00 where NAME00='MZHS_YYJSSJYCSJ';
  if LS_YCSJ00=0 then
    return;
  end if;
   select upper(trim(nvl(max(VALUE0),'N'))) into LS_GQYYSFZDBH from XT_XTCS00 where NAME00='MZHS_GQYYSFZDBH';
   
  if LS_YCSJ00>0 then
    for c_yybr in (select GHHBID,YYH000,YYID00,(select ID0000 from YS_HBHZXH where GHHBID=a.GHHBID and HZXH00=a.YYH000) ID0000 from SF_YYBRB0  a 
    where YYRQ00=to_char(sysdate,'YYYYMMDD') and YYZT00='0'
           and to_char(sysdate-LS_YCSJ00/1440,'HH24:MI:SS')>YYJSSJ)
    loop
      update YS_HBHZXH set BRXM00=null,YYID00=null,BRID00=null
         ,ZY0000='(过期:)'||to_char(sysdate,'YYYYMMDDHH24:MI:SS')
         ,YXBZ00='0'  where GHHBID=c_yybr.GHHBID and HZXH00=c_yybr.YYH000;
      update SF_YYBRB0 set YYZT00='4' where YYID00=c_yybr.YYID00;
  if LS_GQYYSFZDBH<>'N' then
      select max(HZSJ00),max(HZDXH0),max(HZXH00)  into Vtmpstr,Vcounter,Vmaxhzh
        from YS_HBHZXH where GHHBID=c_yybr.GHHBID  ;
      insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZSJ00,YYRQSJ,SFZJ00,SJHZXH,HZDXH0,YYID00,ZY0000 )
      select SQ_YS_HBHZXH_ID0000.nextVal,c_yybr.GHHBID,Vmaxhzh+1,'1',Vtmpstr,null,'0',null,Vcounter,null,'(补过期:)'||c_yybr.ID0000  from  dual;   
      begin
        update sf_ysghhb set  YYXHS0=YYXHS0-1,YYSL00=YYSL00-1 where ghhbid=c_yybr.GHHBID;
          exception when others then
        Vcounter:=1;
      end;
    END IF;     
    end loop;
    commit;
  end if;
end;
/