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;