小菜毛毛技术分享

与大家共同成长

  BlogJava :: 首页 :: 联系 :: 聚合  :: 管理
  164 Posts :: 141 Stories :: 94 Comments :: 0 Trackbacks
create or replace procedure alarm_remind --自动催单--
 AS

  u_timeout_sm_id   number(10); --seq--
  u_task_info_id    number(20);
  u_content         varchar2(1000); --短信内容--
  u_state           varchar2(4) := '0'; --default为0--
  u_result          number(4) := 2; --default为2--
  u_username        varchar2(20); --施工人ID--
  u_phone_number    varchar2(255); --施工人电话--
  u_slsj            date; -- 受理时间--
  u_sla             number(20); --总时限--
  u_sla_time        number(20); --总时限历时--
  u_balance         number(20); --时限差值--
  u_ErrorCode       number;
  u_ErrorMsg        varchar2(200);
  u_alarm_value_sla number(20); --总时限预警时间--
  u_wait_time       number(20); --暂缓的时间--
  u_alarm_state     varchar2(4);--预警状态--

  CURSOR u_task_info_sm IS      --定义游标
    select tai.task_info_id, tai.sla, tai.slsj
      from task_alarm_instance tai, task_info ti,task_alarm_autoremind taa
     where tai.task_info_id = ti.task_info_id
       and ti.state not in ('3', '5')
       and ti.task_tache_id = 5
       and tai.company_code=taa.company_code
       and taa.auto_state='1'
       and ti.task_info_id='133930';

begin
  OPEN u_task_info_sm;        --打开游标
  LOOP
    FETCH u_task_info_sm      --获取游标中的第一条记录
      into u_task_info_id, u_sla, u_slsj;
    select nvl(sum(tfl.end_date-tfl.start_date),0)
      into u_wait_time
      from task_flow_log tfl
     where tfl.task_info_id = u_task_info_id
       and tfl.state = 6;
    u_sla_time := round((to_date('2008-06-05 11:50:27','yyyy-mm-dd HH24:MI:SS')- u_slsj) * 1440);--to_date('2009-03-17 18:10:00','yyyy-mm-dd HH24:MI:SS') 代替sysdate测试--
    u_balance  := round(u_sla - u_sla_time + u_wait_time);
    EXIT WHEN u_task_info_sm%NOTFOUND;
    select ti.username
      into u_username
      from task_info ti
     where ti.task_info_id = u_task_info_id;
    select au.phone_number
      into u_phone_number
      from app_user au
     where au.username = u_username;
    select max(ts.timout_sm_id) + 1
      into u_timeout_sm_id
      from timeout_sm ts;
    select max(taii.alarm_value)
      into u_alarm_value_sla
      from task_alarm_instance_info taii
     where taii.task_info_id = u_task_info_id
       and u_balance >= taii.alarm_value
       and taii.task_tache_id = 0;
    select taii.alarm_state
      into u_alarm_state
      from task_alarm_instance_info taii
     where taii.alarm_value = u_alarm_value_sla
     and taii.task_info_id=u_task_info_id
     and taii.contact_type_id='0'
     and taii.task_tache_id='0'; --获取该条工单的某个预警是否已执行

    IF u_balance between u_alarm_value_sla-5 and u_alarm_value_sla+5 and u_alarm_state is null THEN
      u_content := u_task_info_id || ':工单距' || trunc(u_balance / 60) || '时' ||
                   mod(u_balance, 60) || '分钟超时,请尽快处理';
      insert into timeout_sm
      values
        (u_timeout_sm_id,
         u_task_info_id,
         u_content,
         sysdate,
         u_state,
         sysdate,
         u_result,
         u_username,
         u_phone_number,
         '',
         '',
         '',
         '');
        update task_alarm_instance_info
        set alarm_state='1'
        where alarm_value = u_alarm_value_sla
        and task_info_id=u_task_info_id
        and task_tache_id='0';
      commit;

    END IF;
  END LOOP;
  CLOSE u_task_info_sm;
EXCEPTION
  when others then
    u_ErrorCode := SQLCODE;
    u_ErrorMsg  := SQLERRM;
    DBMS_OUTPUT.put_line(u_ErrorCode || ' ' || u_ErrorMsg);
    rollback;
end alarm_remind;



posted on 2009-05-05 15:45 小菜毛毛 阅读(188) 评论(0)  编辑  收藏 所属分类: 数据库

只有注册用户登录后才能发表评论。


网站导航: