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;