CREATE OR REPLACE PROCEDURE "SP_TASK_ISTER_49"(
task_id_p out varchar,
parm_ne_sys_id varchar,
Param_sendMan varchar,
param_city_id number,
tasktitle varchar,
takeInfo varchar,
para_receiveman varchar,
p_monitoritemname varchar,
p_configid varchar,
acceptedtime varchar,
bsc_name varchar,
lac varchar,
ci varchar,
excetu out varchar ---之前是传的参数) is
Para_Task_id varchar(32);
Para_Stepid number;
para_record number;
para_receive varchar(1000);
para_deptid number;
para_count number;
para_count_id number;
para_count_monitor number := 0;
para_time varchar(1000);
para_jobid number;
para_counts number;
monitoritemid number;
ne_sys_id_chinaName varchar(100);
city_name varchar(1000);
para_oncity CHAR(2);
para_countonplan number;
Monday varchar(200);
Sunday varchar(200);
shifen varchar(200);
---系统参数
begin
---获取monitoritemid 如果不存在的话就不执行工单派发和作业计划执行
---------------------------
--网络日常问题处理工单
---------------------------
para_receive := para_receiveman;
if (para_receiveman is null) then
begin
--默认由系统派发组分发自动发送工单
para_deptid := 1;
select count(*)
into para_record
from cfg_task_autosendinfo a, cfg_map_dev_to_ne map
where a.sendtype = 0
and a.adm_area = map.city_id
and map.ne_cell_id = parm_ne_sys_id
and taskid = 49;
if (para_record = 0) then
begin
para_receive := 'admin';
end;
else
begin
select sendmanlist
into para_receive
from cfg_task_autosendinfo a, cfg_map_dev_to_ne map
where a.sendtype = 0
and a.adm_area = map.city_id
and map.ne_cell_id = parm_ne_sys_id
and taskid = 49;
end;
end if;
end;
else
begin
if (para_count >= 1) then
begin
select deptid
into para_deptid
from sec_userinfo
where loginname = Param_sendMan;
end;
else
begin
para_deptid := 1;
end;
end if;
end;
end if;
-----------------------------
---找到当天时间
select TO_CHAR(SYSDATE, 'YYYY-MM-DD') into para_time FROM DUAL;
---工单号码
--Para_Task_id:=task_id_p;
select to_char(sysdate, 'YYYYMM') into Para_Task_id from dual;
select 'NP' || Para_Task_id || lpad((case
when max(substr(TASK_ID, 9, 12)) is null then
1
else
to_number(max(substr(TASK_ID, 9, 12))) + 1
end),
4,
0)
into Para_Task_id
from mod_task
where template_id = 49
and substr(TASK_ID, 1, 8) = 'NP' || Para_Task_id;
---------------------------
--添加工单记录
task_id_p := Para_Task_id;
insert into mod_task
(task_id,
template_id,
createuser,
creatdate,
templatestepid,
taskstepid,
taskstate,
timeouttype,
task_topic)
values
(Para_Task_id,
49,
Param_sendMan,
sysdate,
24723,
0,
21,
0,
tasktitle);
--添加工单步骤----------------------------------
insert into mod_task_step
(id,
task_id,
startid,
endid,
exectime,
execman,
nextman,
taskstepstate,
tmpstepid,
isnostept)
values
(seq_taskstep.nextval,
Para_Task_id,
1,
2,
sysdate,
Param_sendMan,
para_receive,
1,
24723,
1);
------------------------------------------------
select max(id)
into Para_Stepid
from mod_task_step
where task_id = Para_Task_id;
update mod_task
set taskstepid = Para_Stepid
where task_id = Para_Task_id;
------------
-------------添加步骤
insert into mod_task_allstep
(id,
task_id,
startid,
nextid,
taskstepid,
execman,
nextman,
isnostept)
values
(SEQ_MODALLSTEP.NEXTVAL,
Para_Task_id,
2,
4,
24724,
para_receive,
'',
1);
--*工单主题
insert into mod_task_stepitemdata
(id, taskstepid, tmpstep_itemid, task_id, fielddata)
values
(seq_taskexhibittmp.nextval,
Para_Stepid,
30051,
Para_Task_id,
tasktitle);
--30064 *流水号
insert into mod_task_stepitemdata
(id, taskstepid, tmpstep_itemid, task_id, fielddata)
values
(seq_taskexhibittmp.nextval,
Para_Stepid,
30064,
Para_Task_id,
Para_Task_id);
--30065 城市
insert into mod_task_stepitemdata
(id, taskstepid, tmpstep_itemid, task_id, fielddata)
values
(seq_taskexhibittmp.nextval,
Para_Stepid,
30065,
Para_Task_id,
param_city_id);
select count(*)
into para_count
from sec_userinfo
where loginname = Param_sendMan;
--30052 *派单部门
insert into mod_task_stepitemdata
(id, taskstepid, tmpstep_itemid, task_id, fielddata)
values
(seq_taskexhibittmp.nextval,
Para_Stepid,
30052,
Para_Task_id,
para_deptid);
--30053 *派单人
insert into mod_task_stepitemdata
(id, taskstepid, tmpstep_itemid, task_id, fielddata)
values
(seq_taskexhibittmp.nextval,
Para_Stepid,
30053,
Para_Task_id,
Param_sendMan);
--30054 *联系方式
insert into mod_task_stepitemdata
(id, taskstepid, tmpstep_itemid, task_id, fielddata)
values
(seq_taskexhibittmp.nextval, Para_Stepid, 30054, Para_Task_id, '');
--30066 *紧急程度
insert into mod_task_stepitemdata
(id, taskstepid, tmpstep_itemid, task_id, fielddata)
values
(seq_taskexhibittmp.nextval, Para_Stepid, 30066, Para_Task_id, 1);
--30055 *派发时间
insert into mod_task_stepitemdata
(id, taskstepid, tmpstep_itemid, task_id, fielddata)
values
(seq_taskexhibittmp.nextval,
Para_Stepid,
30055,
Para_Task_id,
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
--30056 *处理时限
insert into mod_task_stepitemdata
(id, taskstepid, tmpstep_itemid, task_id, fielddata)
values
(seq_taskexhibittmp.nextval, Para_Stepid, 30056, Para_Task_id, 48);
--30057 问题简述
insert into mod_task_stepitemdata
(id, taskstepid, tmpstep_itemid, task_id, fielddata)
values
(seq_taskexhibittmp.nextval,
Para_Stepid,
30057,
Para_Task_id,
takeInfo);
--30061 附件
insert into mod_task_stepitemdata
(id, taskstepid, tmpstep_itemid, task_id, fielddata)
values
(seq_taskexhibittmp.nextval, Para_Stepid, 30061, Para_Task_id, '');
--30063 备注
insert into mod_task_stepitemdata
(id, taskstepid, tmpstep_itemid, task_id, fielddata)
values
(seq_taskexhibittmp.nextval, Para_Stepid, 30063, Para_Task_id, '');
select CITY_SIGN into para_oncity from cfg_city where city_id=0 and rownum<=1;
select COUNT(1) INTO para_countonplan from cfg_kidneyfunction t where t.function_id=410
and ((t.bj=1 and para_oncity='BJ') or (t.cq=1 and para_oncity='CQ') or
(t.GD=1 and para_oncity='GD') or (t.GX=1 and para_oncity='GX') or
(t.HEN=1 and para_oncity='HEN') or (t.HB=1 and para_oncity='HB') or
(t.SD=1 and para_oncity='SD') or (t.JS=1 and para_oncity='JS') or
(t.JX=1 and para_oncity='JX') or (t.SX=1 and para_oncity='SX') or
(t.GS=1 and para_oncity='GS'));
select count(1) into shifen from ne_cell_g where cov_area ='室内' and ne_sys_id =parm_ne_sys_id and city_id = param_city_id ;
SELECT TO_CHAR( SYSDATE - TO_NUMBER( TO_CHAR(SYSDATE,'D') ) - 5,'YYYY-MM-DD' ) 星期一 into Monday FROM DUAL;-- 当前天的上星期一
SELECT TO_CHAR( SYSDATE - TO_NUMBER( TO_CHAR(SYSDATE,'D') ) + 1,'YYYY-MM-DD' ) 星期日 into Sunday FROM DUAL;-- 当前天的上星期天
if(p_configid= 56 ) then ---问题小区
if(para_countonplan>=1) then ----坏小区
---实现作业计划
select count(*)
into para_count_monitor
from cfg_job_monitor
where city_id = param_city_id
and monitoritemname = p_monitoritemname
and configid = p_configid;
if (para_count_monitor >= 1) then
select monitoritemid
into monitoritemid
from cfg_job_monitor
where city_id = param_city_id
and monitoritemname = p_monitoritemname
and configid = p_configid;
select count(*)
into para_counts
from mod_job a, mod_jobitem b
where a.planitmeid = b.id
and planman = TO_CHAR(Param_sendMan)
and b.planitemid = 56;
if (para_counts >= 1) then
---获取地市名
select city_name into city_name from cfg_city where city_id =param_city_id;
select china_name into ne_sys_id_chinaName from ne_cell_g where ne_sys_id=parm_ne_sys_id;
---获取 JOBID
select count(0)
into para_count_id
from mod_job a, mod_jobitem b
where a.planitmeid = b.id
and planman = TO_CHAR(Param_sendMan)
and to_char(jobstarttime, 'YYYY-MM-DD ') =
to_char(sysdate, 'YYYY-MM-DD ')
and b.planitemid = p_configid;
if (para_count_id >= 1) then
select a.id
into para_jobid
from mod_job a, mod_jobitem b
where a.planitmeid = b.id
and planman = TO_CHAR(Param_sendMan)
and to_char(jobstarttime, 'YYYY-MM-DD ') =
to_char(sysdate, 'YYYY-MM-DD ')
and b.planitemid = p_configid;
else
para_jobid := 0;
end if;
insert into mod_job_monitor_result_detail
(jobid,
configid,
monitoritemid,
ne_sys_id,
city_id,
bsc_name,
lac,
ci,
taskidlist,
disposition,
acceptedtime, city_name,china_name)
values
(para_jobid,
p_configid,
monitoritemid,
parm_ne_sys_id,
param_city_id,
bsc_name,
lac,
ci,
task_id_p,
'工单发起',
to_date(acceptedtime, 'yyyy-MM-DD HH24:MI:ss'),city_name,ne_sys_id_chinaName);
end if;
else
excetu:=1; ---找不到monitoritemid
end if;
end if ;
end if ;
if (p_configid= 67) then ---数据业务性能
if(para_countonplan>=1) then
---实现作业计划
select count(*)
into para_count_monitor
from cfg_job_monitor
where city_id = param_city_id
and monitoritemname = p_monitoritemname
and configid = p_configid;
if (para_count_monitor >= 1) then
select monitoritemid
into monitoritemid
from cfg_job_monitor
where city_id = param_city_id
and monitoritemname = p_monitoritemname
and configid = p_configid;
select count(*)
into para_counts
from mod_job a, mod_jobitem b
where a.planitmeid = b.id
and planman = TO_CHAR(Param_sendMan)
and b.planitemid = 67;
if (para_counts >= 1) then
---获取地市名
select city_name into city_name from cfg_city where city_id =param_city_id;
select china_name into ne_sys_id_chinaName from ne_cell_g where ne_sys_id=parm_ne_sys_id;
---获取 JOBID
select count(0)
into para_count_id
from mod_job a, mod_jobitem b
where a.planitmeid = b.id
and planman = TO_CHAR(Param_sendMan)
and jobstarttime >= to_date(Monday, 'YYYY-MM-DD ')and
jobstarttime<= to_date(Sunday, 'YYYY-MM-DD ')
and b.planitemid = p_configid;
if (para_count_id >= 1) then
select a.id
into para_jobid
from mod_job a, mod_jobitem b
where a.planitmeid = b.id
and planman = TO_CHAR(Param_sendMan)
and jobstarttime >= to_date(Monday, 'YYYY-MM-DD ')and
jobstarttime<= to_date(Sunday, 'YYYY-MM-DD ')
and b.planitemid = p_configid;
else
para_jobid := 0;
end if;
insert into mod_job_monitor_result_detail
(weekjobid,
weekconfigid,
monitoritemid,
ne_sys_id,
city_id,
bsc_name,
lac,
ci,
taskidlist,
disposition,
acceptedtime, city_name,china_name)
values
(para_jobid,
p_configid,
monitoritemid,
parm_ne_sys_id,
param_city_id,
bsc_name,
lac,
ci,
task_id_p,
'工单发起',
to_date(acceptedtime, 'yyyy-MM-DD HH24:MI:ss'),city_name,ne_sys_id_chinaName);
end if;
else
excetu:=1; ---找不到monitoritemid
end if;
end if ;
end if ;
if (shifen>=1) then ---室分
if(para_countonplan>=1) then
---实现作业计划
select count(*)
into para_count_monitor
from cfg_job_monitor
where city_id = param_city_id
and monitoritemname = p_monitoritemname
and configid = p_configid;
if (para_count_monitor >= 1) then
select monitoritemid
into monitoritemid
from cfg_job_monitor
where city_id = param_city_id
and monitoritemname = p_monitoritemname
and configid = p_configid;
select count(*)
into para_counts
from mod_job a, mod_jobitem b
where a.planitmeid = b.id
and planman = TO_CHAR(Param_sendMan)
and b.planitemid = 56;
if (para_counts >= 1) then
---获取地市名
select city_name into city_name from cfg_city where city_id =param_city_id;
select china_name into ne_sys_id_chinaName from ne_cell_g where ne_sys_id=parm_ne_sys_id;
---获取 JOBID
select count(0)
into para_count_id
from mod_job a, mod_jobitem b
where a.planitmeid = b.id
and planman = TO_CHAR(Param_sendMan)
and jobstarttime >= to_date(Monday, 'YYYY-MM-DD ')and
jobstarttime<= to_date(Sunday, 'YYYY-MM-DD ')
and b.planitemid = p_configid;
if (para_count_id >= 1) then
select a.id
into para_jobid
from mod_job a, mod_jobitem b
where a.planitmeid = b.id
and planman = TO_CHAR(Param_sendMan)
and jobstarttime >= to_date(Monday, 'YYYY-MM-DD ')and
jobstarttime<= to_date(Sunday, 'YYYY-MM-DD ')
and b.planitemid = p_configid;
else
para_jobid := 0;
end if;
insert into mod_job_monitor_result_detail
(weekjobid,
configid,
monitoritemid,
ne_sys_id,
city_id,
bsc_name,
lac,
ci,
taskidlist,
disposition,
acceptedtime, city_name,china_name)
values
(para_jobid,
p_configid,
monitoritemid,
parm_ne_sys_id,
param_city_id,
bsc_name,
lac,
ci,
task_id_p,
'工单发起',
to_date(acceptedtime, 'yyyy-MM-DD HH24:MI:ss'),city_name,ne_sys_id_chinaName);
end if;
else
excetu:=1; ---找不到monitoritemid
end if;
end if ;
end if ;
commit;
end SP_TASK_ISTER_49;
5038
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
