1 package body csy_kpi_pkg as
2 /* $Header: csykpib.pls 120.5 2011/12/27 08:25:33 shramana ship $ */
3
4 function getaudit(p_inc_id in number,
5 p_from_date in date,
6 l_dt in date) return number is
7 l_id number;
8 begin
9 select max(incident_audit_id) into l_id
10 from cs_incidents_audit_b aud_in
11 where aud_in.incident_id = p_inc_id
12 and aud_in.creation_date < p_from_date
13 and
14 nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt)
15 <>
16 nvl(nvl(aud_in.inc_responded_by_date,aud_in.incident_resolved_date),l_dt)
17 and (aud_in.inc_responded_by_date is not null or
18 aud_in.incident_resolved_date is not null);
19 return(l_id);
20 end;
21
22 function getaudit1(p_inc_id in number,
23 p_from_date in date,
24 p_to_date in date,
25 l_dt in date) return number is
26 l_id number;
27 begin
28 select max(incident_audit_id) into l_id
29 from cs_incidents_audit_b aud_in
30 where aud_in.incident_id = p_inc_id
31 and aud_in.creation_date between p_from_date and p_to_date
32 and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt)
33 <>
34 nvl(nvl(aud_in.inc_responded_by_date,aud_in.incident_resolved_date),l_dt)
35 and (aud_in.inc_responded_by_date is not null or
36 aud_in.incident_resolved_date is not null );
37
38 return(l_id);
39 end;
40
41 function get_agents_time(p_resource_id number,
42 p_start_date date,
43 p_end_date date) return number is
44 lp_cnt number;
45 j number;
46 l_msg_index_out number;
47 x_ret_stat varchar2(2000);
48 x_msg_count number;
49 x_msg_data varchar2 (2000);
50 x_shifts jtf_calendar_pub_24hr.shift_tbl_type;
51 l_total_time number ;
52 l_dt1 date;
53 l_dt2 date;
54 begin
55 l_total_time := 0;
56 -- no need to return negative number
57 if (p_end_date <= p_start_date) then
58 return 0;
59 end if;
60 jtf_calendar_pub_24hr.get_resource_shifts(1,fnd_api.g_false,p_resource_id,
61 'RS_EMPLOYEE',
62 p_start_date, p_end_date,
63 x_ret_stat,
64 x_msg_count,
65 x_msg_data,
66 x_shifts);
67 /*
68 --dbms_output.put_line('Return Status :'||x_ret_stat || ':');
69 --dbms_output.put_line('Return Message:'||x_msg_data || ':');
70 IF (FND_MSG_PUB.Count_Msg > 1) THEN
71 --Display all the error messages
72 FOR j in 1..FND_MSG_PUB.Count_Msg LOOP
73 FND_MSG_PUB.Get(p_msg_index=>j,
74 p_encoded=>'F',
75 p_data=>x_msg_data,
76 p_msg_index_out=>l_msg_index_out);
77 DBMS_OUTPUT.PUT_LINE(x_msg_data);
78 END LOOP;
79 ELSE
80 --Only one error
81 FND_MSG_PUB.Get(p_msg_index=>1,
82 p_encoded=>'F',
83 p_data=>x_msg_data,
84 p_msg_index_out=>l_msg_index_out);
85 DBMS_OUTPUT.PUT_LINE(x_msg_data);
86
87 END IF;
88 */
89 l_total_time := 0;
90 if (x_ret_stat = fnd_api.g_ret_sts_success) then
91 for lp_cnt in 1..x_shifts.count loop
92 /*
93 dbms_output.put_line('Start Dt:'||
94 to_char(x_shifts(lp_cnt).start_time,'dd-mon-yyyy hh24:mi:ss')||
95 ' End Time:' ||
96 to_char(x_shifts(lp_cnt).end_time,'dd-mon-yyyy hh24:mi:ss'));
97 */
98 -- get max of p_start_date and shifts.start_time
99 if (p_start_date > x_shifts(lp_cnt).start_time) then
100 l_dt1 := p_start_date;
101 else
102 l_dt1 := x_shifts(lp_cnt).start_time;
103 end if;
104 -- get min of p_end_date and shifts.end_time
105 if (p_end_date > x_shifts(lp_cnt).end_time) then
106 l_dt2 := x_shifts(lp_cnt).end_time;
107 else
108 l_dt2 := p_end_date;
109 end if;
110 --l_dt1 := greatest(p_start_date,x_shifts(lp_cnt).start_time);
111 --l_dt2 := least (p_end_date,x_shifts(lp_cnt).end_time);
112 /* 10/14/03 10:40cst
113 if condition is used to handle mismatch between shift data and
114 incident response/resolution dates. support between 10/1 to 10/10
115 agent's shift times are 10/4/ 9am to 5pm and 10/5 9 to 5 and so on.
116 But somehow agent resolves an incident on 10/3, in that case l_dt2 will
117 point to 10/3 but l_dt1 will point to 10/4 9am(begining of shift
118 during that period. so effectively, time taken to resolve is zero
119 */
120 if (l_dt2 >= l_dt1) then
121 l_total_time := l_total_time + l_dt2 - l_dt1;
122 end if;
123 end loop;
124 else l_total_time := p_end_date - p_start_date;
125 end if;
126 return l_total_time;
127 end;
128 -----------------------------------------
129 procedure upload_resp_and_resl(p_summary_date date,
130 p_incident_owner_id number,
131 p_owner_group_id number,
132 p_incident_severity_id number,
133 p_owner_type varchar2,
134 p_response_time number ,
135 p_requests_responded number ,
136 p_wait_on_agent_resp number ,
137 p_wait_on_others_resp number ,
138 p_requests_resolved number ,
139 p_resolve_time number ,
140 p_wait_on_agent_resl number ,
141 p_wait_on_int_org_resl number ,
142 p_wait_on_ext_org_resl number ,
143 p_wait_on_support_resl number ,
144 p_wait_on_customer_resl number ,
145 p_resp_sla_missed number ,
146 p_resl_sla_missed number ,
147 p_beginning_backlog number ,
148 p_ending_backlog number ,
149 p_sr_assigned number ,
150 p_sr_reassigned_to_others number ) is
151 begin
152 update csy_response_resolutions
153 set
154 total_response_time = nvl(total_response_time,0)+
155 nvl(p_response_time,0),
156 total_requests_responded = nvl(total_requests_responded,0)+
157 nvl(p_requests_responded,0),
158 total_wait_on_agent_resp = nvl(total_wait_on_agent_resp,0) +
159 nvl(p_wait_on_agent_resp,0),
160 total_wait_on_others_resp = nvl(total_wait_on_others_resp,0) +
161 nvl(p_wait_on_others_resp,0),
162 total_requests_resolved = nvl(total_requests_resolved,0)+
163 nvl(p_requests_resolved,0),
164 total_resolve_time = nvl(total_resolve_time,0)+
165 nvl(p_resolve_time,0),
166 total_wait_on_agent_resl = nvl(total_wait_on_agent_resl,0) +
167 nvl(p_wait_on_agent_resl,0),
168 total_wait_on_int_org_resl = nvl(total_wait_on_int_org_resl,0) +
169 nvl(p_wait_on_int_org_resl,0),
170 total_wait_on_ext_org_resl = nvl(total_wait_on_ext_org_resl,0) +
171 nvl(p_wait_on_ext_org_resl,0),
172 total_wait_on_support_resl = nvl(total_wait_on_support_resl,0) +
173 nvl(p_wait_on_support_resl,0),
174 total_wait_on_customer_resl = nvl(total_wait_on_customer_resl,0) +
175 nvl(p_wait_on_customer_resl,0),
176 total_resp_sla_missed = nvl(total_resp_sla_missed,0) +
177 nvl(p_resp_sla_missed,0),
178 total_resl_sla_missed = nvl(total_resl_sla_missed,0) +
179 nvl(p_resl_sla_missed,0),
180 beginning_backlog = nvl(p_beginning_backlog,beginning_backlog), --backlog is calculated afresh
181 ending_backlog = nvl(p_ending_backlog,ending_backlog), -- so no need to add to existing value
182 total_sr_assigned = nvl(total_sr_assigned,0)+p_sr_assigned,
183 total_sr_reassigned_to_others = nvl(total_sr_reassigned_to_others,0)+
184 nvl(p_sr_reassigned_to_others,0)
185 where summary_date = p_summary_date
186 and incident_owner_id = p_incident_owner_id
187 and owner_group_id = p_owner_group_id
188 and owner_type = p_owner_type
189 and incident_severity_id = p_incident_severity_id;
190 if (sql%notfound) then
191 insert into csy_response_resolutions
192 (summary_date,
193 incident_owner_id,
194 incident_severity_id,
195 owner_group_id,
196 owner_type,
197 TOTAL_RESPONSE_TIME ,
198 TOTAL_REQUESTS_RESPONDED ,
199 TOTAL_WAIT_ON_AGENT_RESP ,
200 TOTAL_WAIT_ON_OTHERS_RESP ,
201 TOTAL_REQUESTS_RESOLVED ,
202 TOTAL_RESOLVE_TIME ,
203 TOTAL_WAIT_ON_AGENT_RESL ,
204 TOTAL_WAIT_ON_INT_ORG_RESL ,
205 TOTAL_WAIT_ON_EXT_ORG_RESL ,
206 TOTAL_WAIT_ON_SUPPORT_RESL ,
207 TOTAL_WAIT_ON_CUSTOMER_RESL ,
208 TOTAL_RESP_SLA_MISSED ,
209 TOTAL_RESL_SLA_MISSED ,
210 BEGINNING_BACKLOG ,
211 ENDING_BACKLOG ,
212 TOTAL_SR_ASSIGNED ,
213 TOTAL_SR_REASSIGNED_TO_OTHERS ,
214 last_update_date ,
215 last_updated_by ,
216 creation_date ,
217 created_by ,
218 last_update_login ,
219 program_id ,
220 program_login_id ,
221 program_application_id ,
222 request_id
223 )
224 values (p_summary_date,
225 p_incident_owner_id,
226 p_incident_severity_id,
227 p_owner_group_id,
228 p_owner_type,
229 p_response_time ,
230 p_requests_responded ,
231 p_wait_on_agent_resp ,
232 p_wait_on_others_resp ,
233 p_requests_resolved ,
234 p_resolve_time ,
235 p_wait_on_agent_resl ,
236 p_wait_on_int_org_resl ,
237 p_wait_on_ext_org_resl ,
238 p_wait_on_support_resl ,
239 p_wait_on_customer_resl ,
240 p_resp_sla_missed ,
241 p_resl_sla_missed ,
242 p_beginning_backlog ,
243 p_ending_backlog ,
244 p_sr_assigned ,
245 p_sr_reassigned_to_others ,
246 sysdate ,
247 g_user_id ,
248 sysdate ,
249 g_user_id ,
250 g_login_user_id ,
251 g_conc_program_id ,
252 g_conc_login_id ,
253 g_conc_appl_id ,
254 g_conc_request_id
255 );
256 end if;
257 end;
258 ----------------------
259 procedure debug(l_msg varchar2) is
260 l_tmp varchar2(4000);
261 begin
262 /*
263 if (length(l_msg) > 4000) then
264 l_tmp := substr(l_msg,1,4000);
265 insert into biv_debug(message, report_id,seq_no) values (
266 l_tmp, 'XX', g_seq);
267 g_seq := g_seq + 1;
268 --
269 l_tmp := substr(l_msg,4001,4000);
270 insert into biv_debug(message, report_id,seq_no) values (
271 l_tmp, 'XX', g_seq);
272 else
273 insert into biv_debug(message, report_id,seq_no) values (
274 l_msg, 'XX', g_seq);
275 end if;
276 fnd_file.put_line(fnd_file.log,'Message No:'||to_char(g_seq) ||
277 ' =====================================================');
278 fnd_file.put_line(fnd_file.log,l_msg);
279 */
280 g_seq := g_seq + 1;
281 end;
282 procedure get_sr_backlog (p_from_date in date,
283 p_to_date in date) is
284 x number;
285 l_dt date;
286 cursor c_backlog is
287 select nvl(aud_out.incident_owner_id,-1), nvl(aud_out.group_id,-1),
288 nvl(aud_out.incident_severity_id,-1), count(*)
289 from cs_incidents_audit_b aud_out,
290 cs_incidents_all_b sr
291 where aud_out.incident_resolved_date is null
292 and aud_out.incident_id = sr.incident_id
293 and nvl(sr.incident_resolved_date,sysdate+1000) > l_dt
294 and incident_audit_id =
295 /* supposr conc program dates are 11/15 to 12/15
296 a SR was resolved on 12/10 for last time
297 was in resolved state from 11/1/ to 11/25
298 on 11/26 it was set to unresolved
299 for every date from 11/15 to 11/25, this query will find audit record
300 with NOT NULL incident_resolved date and hence it will not be counted as
301 Backlog
302 for l_dt = 11/26, it will find auidt rec with NULL incident_resolved_date
303 so for 11/26, it will be counted as BACKLOG
304 same thing will be applicable from 11/27 to 12/9 as incident was again set to
305 resolved on 12/10
306 ***/
307 /* why are we using subquery here? couldn't we just use the
308 above statement to figure out backlog?
309 Ans: No. suppose l_dt is 10-may-03, close_date is 30-may-03
310 it does not mean that this SR was backlog from 10 to 29th may
311 reason: it might be closed between 11-may to 25th may.
312 so it is not a backlog between those dates. It is the
313 subquery that will return a record representing closed SR for
314 all dates between 11th and 25th may and aut_out.status_flag = 'O'
315 will make it unselected and hence not counted
316 */
317 (select max(incident_audit_id)
318 from cs_incidents_audit_b aud_in
319 where aud_in.incident_id = aud_out.incident_id
320 and aud_in.creation_date < l_dt +1)
321 -- so that full day is taken
322 group by aud_out.incident_owner_id,
323 aud_out.group_id,
324 aud_out.incident_severity_id;
325 Type number_table_type is table of number index by binary_integer;
326 l_owner_arr number_table_type;
327 l_group_arr number_table_type;
328 l_sev_arr number_table_type;
329 l_backlog_arr number_table_type;
330 l_end_date date;
331 begin
332 l_dt := trunc(p_from_date);
333 l_end_date := trunc(p_to_date);
334 --dbms_output.put_line('From date:'||to_char(p_from_date,'dd-mon-yyyy hh24:mi:ss'));
335 --dbms_output.put_line('To date:'||to_char(p_to_date,'dd-mon-yyyy hh24:mi:ss'));
336 --dbms_output.put_line('End date:'||to_char(l_end_date,'dd-mon-yyyy hh24:mi:ss'));
337 -- These 2 updaes are needed because if backlog is reduced to 0 then c_backlog
338 -- cursor will not return any row and existing value will not be overwritten
339 update csy_response_resolutions
340 set ending_backlog = 0
341 where summary_date between trunc(p_from_date) and trunc(p_to_date);
342 update csy_response_resolutions
343 set beginning_backlog = 0
344 where summary_date between trunc(p_from_date+1) and trunc(p_to_date+1);
345 loop
346 if (l_dt > l_end_date ) then exit; end if;
347 open c_backlog;
348 fetch c_backlog bulk collect into l_owner_arr, l_group_arr,
349 l_sev_arr, l_backlog_arr;
350 close c_backlog;
351 if (l_owner_arr.count > 0) then
352 for j in l_owner_arr.first..l_owner_arr.last loop
353 upload_resp_and_resl(p_incident_owner_id =>l_owner_arr(j),
354 p_owner_group_id => l_group_arr(j),
355 p_incident_severity_id => l_Sev_arr(j),
356 p_summary_date => l_dt,
357 p_owner_type => 'A',
358 p_response_time => 0,
359 p_requests_responded => 0,
360 p_wait_on_agent_resp => 0,
361 p_wait_on_others_resp => 0,
362 p_requests_resolved => 0,
363 p_resolve_time => 0,
364 p_wait_on_agent_resl => 0,
365 p_wait_on_int_org_resl => 0,
366 p_wait_on_ext_org_resl => 0,
367 p_wait_on_support_resl => 0,
368 p_wait_on_customer_resl => 0,
369 p_resp_sla_missed => 0,
370 p_resl_sla_missed => 0,
371 p_beginning_backlog => null,
372 p_sr_assigned => 0,
373 p_sr_reassigned_to_others => 0,
374 p_ending_backlog => l_backlog_arr(j));
375 upload_resp_and_resl(p_incident_owner_id =>l_owner_arr(j),
376 p_owner_group_id => l_group_arr(j),
377 p_incident_severity_id => l_Sev_arr(j),
378 p_summary_date => l_dt+1,
379 p_owner_type => 'A',
380 p_response_time => 0,
381 p_requests_responded => 0,
382 p_wait_on_agent_resp => 0,
383 p_wait_on_others_resp => 0,
384 p_requests_resolved => 0,
385 p_resolve_time => 0,
386 p_wait_on_agent_resl => 0,
387 p_wait_on_int_org_resl => 0,
388 p_wait_on_ext_org_resl => 0,
389 p_wait_on_support_resl => 0,
390 p_wait_on_customer_resl => 0,
391 p_resp_sla_missed => 0,
392 p_resl_sla_missed => 0,
393 p_ending_backlog => null,
394 p_sr_assigned => 0,
395 p_sr_reassigned_to_others => 0,
396 p_beginning_backlog => l_backlog_arr(j));
397 end loop;
398 end if;
399 --dbms_output.put_line('Backlog Date:'|| to_char(l_dt,'dd-mon-yyyy hh24:mi:ss'));
400 l_dt := l_dt + 1;
401 end loop;
402 end get_sr_backlog;
403 ------------------------------------------------------
404 procedure get_resolution_timings (p_from_date in date,
405 p_to_date in date) is
406 --
407 --
408 /* we need separate cursor for timings and number of resolutions because
409 one query will not work due to determination of RESL_SLA_MISSED
410 same thing is applicable to response timings too
411 */
412 --
413 l_dt date ;
414 l_sql varchar2(4000);
415 cursor c_resolutions is
416 select nvl(aud.incident_owner_id,-1) incident_owner_id,
417 nvl(aud.incident_severity_id,-1) incident_severity_id,
418 nvl(aud.group_id,-1) owner_group_id,
419 trunc(aud.incident_resolved_date) incident_resolved_date,
420 count(aud.incident_id) resolutions,
421 count(decode(sign(sr.incident_resolved_date-
422 sr.expected_resolution_date),
423 1,1,null)) resl_sla_missed
424 from cs_incidents_audit_b aud, --this is audit rec for response
425 cs_incidents_all_b sr
426 where sr.incident_id = aud.incident_id
427 and (aud.incident_owner_id is not null or
428 aud.group_id is not null)
429 and aud.incident_audit_id =
430 ( select max(incident_audit_id)
431 from cs_incidents_audit_b aud_in
432 where aud_in.incident_id = aud.incident_id
433 and aud_in.creation_date between p_from_date
434 and p_to_date
435 and nvl(aud_in.old_incident_resolved_date,l_dt) <>
436 nvl(aud_in.incident_resolved_date,l_dt)
437 and aud_in.incident_resolved_date is not null
438 -- above cond is needed to make sure that sr is
439 -- responded. if this cond is not there then incident_resolved_date
440 -- as null to may get selected which is clearly not
441 -- responded condition.
442 )
443 -- above query will insure that selected response is the last response
444 group by aud.incident_owner_id,
445 aud.group_id,
446 aud.incident_severity_id,
447 trunc(aud.incident_resolved_date);
448 cursor c_resolutions_rev is
449 select /*+ ORDERED */
450 nvl(prev_resp.incident_owner_id,-1) incident_owner_id,
451 nvl (prev_resp.incident_severity_id,-1) incident_severity_id,
452 nvl (prev_resp.group_id,-1) owner_group_id,
453 trunc(prev_resp.incident_resolved_date) incident_resolved_date,
454 count(prev_resp.incident_id) resolutions,
455 count(decode(sign(sr.actual_resolution_date-
456 sr.expected_resolution_date),
457 1,1,null)) resl_sla_missed
458 from cs_incidents_audit_b curr_resp,
459 --this is audit rec for response in curr run dates
460 cs_incidents_audit_b prev_resp,
461 -- this is response in before curr run dates
462 cs_incidents_all_b sr
463 where sr.incident_id = prev_resp.incident_id
464 and curr_resp.incident_audit_id =
465 ( select max(incident_audit_id)
466 from cs_incidents_audit_b aud_in
467 where aud_in.incident_id = curr_resp.incident_id
468 and aud_in.creation_date between p_from_date
469 and p_to_date
470 and nvl(aud_in.old_incident_resolved_date,l_dt) <>
471 nvl(aud_in.incident_resolved_date,l_dt)
472 and aud_in.incident_resolved_date is not null
473 -- above cond is needed to make sure that sr is
474 -- responded. if this cond is not there then incident_resolved_date
475 -- as null to may get selected which is clearly not
476 -- responded condition.
477 )
478 -- above query will insure that selected response is the last response
479 and prev_resp.incident_id = curr_resp.incident_id
480 and prev_resp.incident_audit_id = ( select max(incident_audit_id)
481 from cs_incidents_audit_b aud_in1
482 where aud_in1.incident_id = curr_resp.incident_id
483 and aud_in1.creation_date < p_from_date
484 and nvl(aud_in1.old_incident_resolved_date,l_dt) <>
485 nvl(aud_in1.incident_resolved_date,l_dt)
486 and aud_in1.incident_resolved_date is not null
487 -- above cond is needed to make sure that sr is
488 -- responded. if this cond is not there then incident_resolved_date
489 -- as null to may get selected which is clearly not
490 -- responded condition.
491 )
492 group by prev_resp.incident_owner_id,
493 prev_resp.group_id,
494 prev_resp.incident_severity_id,
495 trunc(prev_resp.incident_resolved_date);
496 cursor c_resl_times is
497 select /*+ ORDERED */
498 nvl(aud.incident_owner_id,-1) incident_owner_id,
499 nvl (aud.incident_severity_id,-1) incident_severity_id,
500 nvl (aud.group_id,-1) owner_group_id,
501 trunc(aud.incident_resolved_date) incident_resolved_date,
502 sum(decode(aud.incident_owner_id, to_dttm.old_incident_owner_id,
503 -- decode(to_stat.status_class_code,'WAIT_ON_SUPPORT',
504 -- above decode is removed on 17-dec-03. resl time as per SRD
505 csy_kpi_pkg.get_agents_time(
506 aud.incident_owner_id,
507 decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),
508 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
509 aud.incident_resolved_date,to_dttm.creation_date)
510 )/*,0)*/,0)
511 ) * 1440 resl_time ,
512 -- we need to select distinct because each responded
513 -- audit will be joined with mulitple from and to aduit record
514 -- no need to get count, it is obtained in prev qry 10/15/03
515 --count(distinct aud.incident_id) responses,
516 sum( decode(to_stat.status_class_code,'WAIT_ON_SUPPORT',
517 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
518 aud.incident_resolved_date,to_dttm.creation_date)
519 -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0)
520 ) * 1440 wait_on_support,
521 sum(decode(to_stat.status_class_code,'WAIT_ON_CUSTOMER',
522 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
523 aud.incident_resolved_date,to_dttm.creation_date)
524 -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
525 * 1440 wait_on_customer,
526 sum(decode(to_stat.status_class_code,'WAIT_ON_INT_GROUP',
527 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
528 aud.incident_resolved_date,to_dttm.creation_date)
529 -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
530 * 1440 wait_on_int_org,
531 sum(decode(to_stat.status_class_code,'WAIT_ON_EXT_GROUP',
532 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
533 aud.incident_resolved_date,to_dttm.creation_date)
534 -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
535 * 1440 wait_on_ext_org,
536 /* in a period between from_dttm and to_dttm, who is owns the
537 service request? from_dttm.incident_owner_id. we could have used
538 to_dttm.old_incident_owner_id but if there is no change in owner
539 then old_incident_owner will be null. in that case we have to
540 use nvl(to_dttm.old_incident_owner_id, to_dttm.incident_owner_id).
541 */
542 sum(decode(from_dttm.incident_owner_id,aud.incident_owner_id,
543 decode(to_stat.status_class_code,'WAIT_ON_SUPPORT',
544 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
545 aud.incident_resolved_date,to_dttm.creation_date)
546 -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
547 ) * 1440 wait_on_agent
548 from cs_incidents_audit_b aud , --this is audit rec for response
549 cs_incidents_audit_b to_dttm , -- to date time
550 cs_incidents_audit_b from_dttm, -- from date time
551 cs_incident_Statuses_b to_stat
552 /* the pair of from_dttm to to_dttm will give the durating in which an agent
553 owned a serveice request.
554 cs_incidents_all_b sr -- only for incident_date */
555 where aud.incident_audit_id =
556 ( select max(incident_audit_id)
557 from cs_incidents_audit_b aud_in
558 where aud_in.incident_id = aud.incident_id
559 and aud_in.creation_date between p_from_date
560 and p_to_date
561 and nvl(aud_in.old_incident_resolved_date,l_dt) <>
562 nvl(aud_in.incident_resolved_date,l_dt)
563 and aud_in.incident_resolved_date is not null
564 -- above cond is needed to make sure that sr is
565 -- responded. if this cond is not there then incident_resolved_date
566 -- as null to may get selected which is clearly not
567 -- responded condition.
568 )
569 -- above query will insure that selected response is the last response
570 and to_dttm.incident_id = aud.incident_id
571 and to_dttm.creation_date <= aud.creation_date
572 and to_dttm.old_incident_status_id = to_stat.incident_status_id
573 and (to_dttm.incident_audit_id = aud.incident_audit_id or
574 ((nvl(to_dttm.old_incident_owner_id,-1) <>
575 nvl(to_dttm.incident_owner_id,-1) or
576 nvl(to_dttm.old_incident_status_id,-1) <>
577 nvl(to_dttm.incident_status_id,-1)) and
578 to_dttm.creation_date >= to_dttm.incident_date)
579 )
580 -- above will insure that to_dttm start from responded audit rec
581 and to_dttm.incident_id = from_dttm.incident_id
582 /*
583 and (nvl(from_dttm.old_incident_owner_id,-1) <>
584 nvl(from_dttm.incident_owner_id,-1)
585 or nvl(from_dttm.old_incident_status_id,-1) <>
586 nvl(from_dttm.incident_status_id,-1))
587 */
588 and from_dttm.incident_audit_id =
589 (select max(incident_audit_id) from cs_incidents_audit_b x
590 where x.incident_id = aud.incident_id
591 and ((nvl(x.old_incident_owner_id,-1) <>
592 nvl(x.incident_owner_id,-1) or
593 nvl(x.old_incident_status_id,-1) <>
594 nvl(x.incident_status_id,-1)) and
595 x.creation_date >= x.incident_date
596 )
597 and x.creation_date < to_dttm.creation_date
598 )
599 group by aud.incident_owner_id,
600 aud.group_id,
601 aud.incident_severity_id,
602 trunc(aud.incident_resolved_date)
603 ;
604 cursor c_resl_times_rev is
605 select /*+ ORDERED */
606 nvl(aud.incident_owner_id,-1) incident_owner_id,
607 nvl (aud.incident_severity_id,-1) incident_severity_id,
608 nvl (aud.group_id,-1) owner_group_id,
609 trunc(aud.incident_resolved_date) incident_resolved_date,
610 sum(decode(aud.incident_owner_id, to_dttm.old_incident_owner_id,
611 -- decode(to_stat.status_class_code,'WAIT_ON_SUPPORT',
612 -- 17-dec-03 above decode removed to make it as per SRD
613 csy_kpi_pkg.get_agents_time(
614 aud.incident_owner_id,
615 decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),
616 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
617 aud.incident_resolved_date,to_dttm.creation_date)
618 )/*,0)*/,0)
619 ) * 1440 resl_time ,
620 -- we need to select distinct because each responded
621 -- audit will be joined with mulitple from and to aduit record
622 -- 10/15/2003 count(distinct aud.incident_id) responses,
623 sum(decode(to_stat.status_class_code,'WAIT_ON_SUPPORT',
624 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
625 aud.incident_resolved_date,to_dttm.creation_date)
626 -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
627 * 1440 wait_on_support,
628 sum(decode(to_stat.status_class_code,'WAIT_ON_CUSTOMER',
629 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
630 aud.incident_resolved_date,to_dttm.creation_date)
631 -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
632 * 1440 wait_on_customer,
633 sum(decode(to_stat.status_class_code,'WAIT_ON_INT_GROUP',
634 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
635 aud.incident_resolved_date,to_dttm.creation_date)
636 -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
637 * 1440 wait_on_int_org,
638 sum(decode(to_stat.status_class_code,'WAIT_ON_EXT_GROUP',
639 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
640 aud.incident_resolved_date,to_dttm.creation_date)
641 -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
642 * 1440 wait_on_ext_org,
643 sum(decode(from_dttm.incident_owner_id,aud.incident_owner_id,
644 decode(to_stat.status_class_code,'WAIT_ON_SUPPORT',
645 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
646 aud.incident_resolved_date,to_dttm.creation_date)
647 -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
648 ) * 1440 wait_on_agent
649 from cs_incidents_audit_b curr_resp,
650 cs_incidents_audit_b aud , --this is audit rec for prior resolutions
651 cs_incidents_audit_b to_dttm , -- to date time
652 cs_incidents_audit_b from_dttm, -- from date time
653 cs_incident_statuses_b to_Stat
654 /* the pair of from_dttm to to_dttm will give the durating in which an agent
655 owned a serveice request.
656 cs_incidents_all_b sr -- only for incident_date */
657 where aud.incident_audit_id =
658 ( select max(incident_audit_id)
659 from cs_incidents_audit_b aud_in
660 where aud_in.incident_id = curr_resp.incident_id
661 and aud_in.creation_date < p_from_date
662 and nvl(aud_in.old_incident_resolved_date,l_dt) <>
663 nvl(aud_in.incident_resolved_date,l_dt)
664 and aud_in.incident_resolved_date is not null
665 -- above cond is needed to make sure that sr is
666 -- responded. if this cond is not there then incident_resolved_date
667 -- as null to may get selected which is clearly not
668 -- responded condition.
669 )
670 and curr_resp.incident_audit_id =
671 ( select max(incident_audit_id)
672 from cs_incidents_audit_b aud_in
673 where aud_in.incident_id = curr_resp.incident_id
674 and aud_in.creation_date between p_from_date
675 and p_to_date
676 and nvl(aud_in.old_incident_resolved_date,l_dt) <>
677 nvl(aud_in.incident_resolved_date,l_dt)
678 and aud_in.incident_resolved_date is not null
679 -- above cond is needed to make sure that sr is
680 -- responded. if this cond is not there then incident_resolved_date
681 -- as null to may get selected which is clearly not
682 -- responded condition.
683 )
684 and curr_resp.incident_id = aud.incident_id
685 -- This make sure that earlier resp to current response is selected
686 -- above query will insure that selected response is the last response
687 and to_dttm.incident_id = aud.incident_id
688 and to_dttm.creation_date <= aud.creation_date
689 and to_dttm.old_incident_status_id = to_stat.incident_status_id
690 and (to_dttm.incident_audit_id = aud.incident_audit_id or
691 ((nvl(to_dttm.old_incident_owner_id,-1) <>
692 nvl(to_dttm.incident_owner_id,-1) or
693 nvl(to_dttm.old_incident_status_id,-1) <>
694 nvl(to_dttm.incident_status_id,-1) and
695 to_dttm.creation_date >= to_dttm.incident_date))
696 )
697 -- above will insure that to_dttm start from responded audit rec
698 and to_dttm.incident_id = from_dttm.incident_id
699 /*
700 and (nvl(from_dttm.old_incident_owner_id,-1) <>
701 nvl(from_dttm.incident_owner_id,-1)
702 or nvl(from_dttm.old_incident_status_id,-1) <>
703 nvl(from_dttm.incident_status_id,-1))
704 */
705 and from_dttm.incident_audit_id =
706 (select max(incident_audit_id) from cs_incidents_audit_b x
707 where x.incident_id = aud.incident_id
708 and ((nvl(x.old_incident_owner_id,-1) <>
709 nvl(x.incident_owner_id,-1) or
710 nvl(x.old_incident_status_id,-1) <>
711 nvl(x.incident_status_id,-1)) and
712 x.creation_date >= x.incident_date
713 )
714 and x.creation_date < to_dttm.creation_date
715 )
716 group by aud.incident_owner_id,
717 aud.group_id,
718 aud.incident_severity_id,
719 trunc(aud.incident_resolved_date)
720 ;
721 l_owner_id cs_incidents_all_b.incident_owner_id % type;
722 l_sev_id cs_incidents_all_b.incident_severity_id % type;
723 l_group_id cs_incidents_all_b.owner_group_id % type;
724 l_summ_dt cs_incidents_all_b.close_date % type;
725 l_resl_time number;
726 l_no_of_resp number;
727 l_wait_on_support number;
728 l_wait_on_customer number;
729 l_wait_on_int_org number;
730 l_wait_on_ext_org number;
731 l_wait_on_agent number;
732 l_sla_missed number;
733 begin
734 l_dt := trunc(sysdate) - 10000;
735 open c_resl_times;
736 loop
737 fetch c_resl_times into l_owner_id, l_sev_id, l_group_id,
738 l_summ_dt, l_resl_time, /*l_no_of_resp, */
739 l_wait_on_support, l_wait_on_customer,
740 l_wait_on_int_org, l_wait_on_ext_org,
741 l_wait_on_agent;
742 if (c_resl_times%notfound) then exit; end if;
743 upload_resp_and_resl(p_incident_owner_id =>l_owner_id,
744 p_owner_group_id => l_group_id,
745 p_incident_severity_id => l_Sev_id,
746 p_summary_date => l_summ_dt,
747 p_owner_type => 'A',
748 p_response_time => 0,
749 p_requests_responded => 0,
750 p_wait_on_agent_resp => 0,
751 p_wait_on_others_resp => 0,
752 p_requests_resolved => 0,
753 p_resp_sla_missed => 0,
754 p_resl_sla_missed => 0,
755 p_beginning_backlog => null,
756 p_ending_backlog => null,
757 p_sr_assigned => 0,
758 p_sr_reassigned_to_others => 0,
759 p_resolve_time => l_resl_time ,
760 p_wait_on_support_resl => l_wait_on_support,
761 p_wait_on_customer_resl => l_wait_on_customer,
762 p_wait_on_int_org_resl => l_wait_on_int_org,
763 p_wait_on_ext_org_resl => l_wait_on_ext_org,
764 p_wait_on_agent_resl => l_wait_on_agent);
765 end loop;
766 close c_resl_times;
767 debug('After Sr Resolution Time:'||to_char(sysdate,'hh24:mi:ss'));
768 --
769 -- Now reverse the response time if an incident was responded earlier
770 open c_resl_times_rev;
771 loop
772 fetch c_resl_times_rev into l_owner_id, l_sev_id, l_group_id,
773 l_summ_dt, l_resl_time, /*l_no_of_resp, */
774 l_wait_on_support, l_wait_on_customer,
775 l_wait_on_int_org, l_wait_on_ext_org,
776 l_wait_on_agent;
777 if (c_resl_times_rev%notfound) then exit; end if;
778 upload_resp_and_resl(p_incident_owner_id => l_owner_id,
779 p_owner_group_id => l_group_id,
780 p_incident_severity_id => l_Sev_id,
781 p_summary_date => l_summ_dt,
782 p_owner_type => 'A',
783 p_response_time => 0,
784 p_requests_responded => 0,
785 p_wait_on_agent_resp => 0,
786 p_wait_on_others_resp => 0,
787 p_requests_resolved => 0,
788 p_resp_sla_missed => 0,
789 p_resl_sla_missed => 0,
790 p_beginning_backlog => null,
791 p_ending_backlog => null,
792 p_sr_assigned => 0,
793 p_sr_reassigned_to_others => 0,
794 p_resolve_time => l_resl_time *-1,
795 p_wait_on_support_resl => l_wait_on_support *-1,
796 p_wait_on_customer_resl => l_wait_on_customer *-1,
797 p_wait_on_int_org_resl => l_wait_on_int_org *-1,
798 p_wait_on_ext_org_resl => l_wait_on_ext_org *-1,
799 p_wait_on_agent_resl => l_wait_on_agent *-1);
800 end loop;
801 close c_resl_times_rev;
802 debug('After Sr Resolution Time Reversal:'||to_char(sysdate,'hh24:mi:ss'));
803 --
804 -- Get number of requests responded
805 --
806 open c_resolutions;
807 loop
808 fetch c_resolutions into l_owner_id, l_sev_id, l_group_id,
809 l_summ_dt, /*l_resl_time, 17-dec-03*/ l_no_of_resp, l_sla_missed;
810 if (c_resolutions%notfound) then exit; end if;
811 upload_resp_and_resl(p_incident_owner_id =>l_owner_id,
812 p_owner_group_id => l_group_id,
813 p_incident_severity_id => l_Sev_id,
814 p_summary_date => l_summ_dt,
815 p_owner_type => 'A',
816 p_response_time => 0,
817 p_requests_responded => 0,
818 p_wait_on_agent_resp => 0,
819 p_wait_on_others_resp => 0,
820 p_resolve_time => 0,
821 p_wait_on_agent_resl => 0,
822 p_wait_on_int_org_resl => 0,
823 p_wait_on_ext_org_resl => 0,
824 p_wait_on_support_resl => 0,
825 p_wait_on_customer_resl => 0,
826 p_resp_sla_missed => 0,
827 p_beginning_backlog => null,
828 p_ending_backlog => null,
829 p_sr_assigned => 0,
830 p_sr_reassigned_to_others => 0,
831 p_requests_resolved => l_no_of_resp,
832 p_resl_sla_missed => l_sla_missed);
833 end loop;
834 close c_resolutions;
835 debug('After Sr Resolution count:'||to_char(sysdate,'hh24:mi:ss'));
836 --
837 -- Get number of requests responded to be reversed
838 --
839 open c_resolutions_rev;
840 loop
841 fetch c_resolutions_rev into l_owner_id, l_sev_id, l_group_id,
842 l_summ_dt, /*l_resl_time, 17-dec-03*/ l_no_of_resp,l_sla_missed;
843 if (c_resolutions_rev%notfound) then exit; end if;
844 upload_resp_and_resl(p_incident_owner_id =>l_owner_id,
845 p_owner_group_id => l_group_id,
846 p_incident_severity_id => l_Sev_id,
847 p_summary_date => l_summ_dt,
848 p_owner_type => 'A',
849 p_response_time => 0,
850 p_requests_responded => 0,
851 p_wait_on_agent_resp => 0,
852 p_wait_on_others_resp => 0,
853 p_resolve_time => 0,
854 p_wait_on_agent_resl => 0,
855 p_wait_on_int_org_resl => 0,
856 p_wait_on_ext_org_resl => 0,
857 p_wait_on_support_resl => 0,
858 p_wait_on_customer_resl => 0,
859 p_resp_sla_missed => 0,
860 p_beginning_backlog => null,
861 p_ending_backlog => null,
862 p_sr_assigned => 0,
863 p_sr_reassigned_to_others => 0,
864 p_requests_resolved => l_no_of_resp*-1,
865 p_resl_sla_missed => l_sla_missed*-1);
866 end loop;
867 close c_resolutions_rev;
868 debug('After Sr Resolution count Reversal:'||to_char(sysdate,'hh24:mi:ss'));
869 --
870 exception
871 when others then
872 fnd_file.put_line(fnd_file.log,'Error:'||sqlerrm);
873 raise fnd_api.g_exc_unexpected_error;
874 end get_resolution_timings;
875 procedure upload_first_resolutions(p_sql varchar2,
876 p_owner_type varchar2,
877 p_from_date date,
878 p_to_date date,
879 p_upload_type varchar2
880 ) as
881 Type Resolution_cursor_type is ref cursor;
882 c_first_resolutions Resolution_cursor_type;
883 l_dt date ;
884 l_owner_id cs_incidents_all_b.incident_owner_id % type;
885 l_sev_id cs_incidents_all_b.incident_severity_id % type;
886 l_resol_dt cs_incidents_all_b.incident_date % type;
887 l_inv_item_id cs_incidents_all_b.inventory_item_id % type;
888 l_inv_org_id cs_incidents_all_b.inv_organization_id % type;
889 l_prob_code cs_incidents_all_b.problem_code % type;
890 l_resol_code cs_incidents_all_b.resolution_code % type;
891 l_sr_resolved number;
892 l_sr_reopen number;
893 l_sr_reopen2 number;
894 begin
895 l_dt := trunc(sysdate) +3000;
896 if p_upload_type = 'RESL' then
897 open c_first_resolutions for p_sql using l_dt, l_dt,p_from_date, p_to_date,
898 l_dt, l_dt;
899 elsif P_UPLOAD_TYPE = 'REOPEN' then
900 open c_first_resolutions for p_sql using p_from_date, p_to_date,
901 p_from_date, p_to_date;
902 elsif P_UPLOAD_TYPE = 'REVERSE' then
903 open c_first_resolutions for p_sql using p_from_date, p_to_date,
904 p_from_date, p_to_date,
905 p_from_date, p_from_date;
906 else
907 raise_application_error(-20001,'Invalid Load type');
908 end if;
909 loop
910 fetch c_first_resolutions into l_owner_id,
911 l_sev_id,
912 l_resol_dt,
913 l_inv_item_id,
914 l_inv_org_id,
915 l_prob_code,
916 l_resol_code,
917 l_sr_resolved,
918 l_sr_reopen,
919 l_sr_reopen2;
920 if c_first_resolutions%notfound then exit; end if;
921 update csy_resolution_qlty
922 set total_sr_resolved_1st_time = nvl(total_sr_resolved_1st_time,0) +
923 l_sr_resolved,
924 total_sr_reopened = nvl(total_sr_reopened,0)+l_sr_reopen,
925 tot_sr_reopened_once_or_more = nvl(tot_sr_reopened_once_or_more,0)+
926 l_sr_reopen2
927 where summary_date = l_resol_dt
928 and incident_owner_id = l_owner_id
929 and owner_type = p_owner_type
930 and incident_severity_id = l_sev_id
931 and inv_organization_id = l_inv_org_id
932 and inventory_item_id = l_inv_item_id
933 and resolution_code = l_resol_code
934 and problem_code = l_prob_code;
935 if (sql%notfound ) then
936 insert into csy_resolution_qlty (
937 SUMMARY_DATE ,
938 INCIDENT_OWNER_ID ,
939 OWNER_TYPE ,
940 INCIDENT_SEVERITY_ID ,
941 INV_ORGANIZATION_ID ,
942 INVENTORY_ITEM_ID ,
943 RESOLUTION_CODE ,
944 PROBLEM_CODE ,
945 TOTAL_SR_RESOLVED_1ST_TIME ,
946 TOTAL_SR_REOPENED ,
947 TOT_SR_REOPENED_ONCE_OR_MORE ,
948 last_update_date ,
949 last_updated_by ,
950 creation_date ,
951 created_by ,
952 last_update_login ,
953 program_id ,
954 program_login_id ,
955 program_application_id ,
956 request_id )
957 values (l_resol_dt ,
958 l_owner_id ,
959 p_owner_type ,
960 l_sev_id ,
961 l_inv_org_id ,
962 l_inv_item_id ,
963 l_resol_code ,
964 l_prob_code ,
965 l_sr_resolved ,
966 l_sr_reopen ,
967 l_sr_reopen2 ,
968 sysdate ,
969 g_user_id ,
970 sysdate ,
971 g_user_id ,
972 g_login_user_id ,
973 g_conc_program_id ,
974 g_conc_login_id ,
975 g_conc_appl_id ,
976 g_conc_request_id
977 );
978 end if;
979 end loop;
980 close c_first_resolutions;
981 exception
982 when others then
983 fnd_file.put_line(fnd_file.log,'Error:'||sqlerrm);
984 raise fnd_api.g_exc_unexpected_error;
985 end;
986 procedure get_sr_resolutions (p_from_date in date,
987 p_to_date in date) is
988 --cursor c_agent_resolutions is
989 l_sql varchar2(4000) ;
990 begin
991 l_sql := '
992 select first_rslvd.incident_owner_id ,
993 nvl(first_rslvd.incident_severity_id,-1) incident_severity_id,
994 trunc(first_rslvd.incident_resolved_date) summary_date ,
995 nvl(first_rslvd.inventory_item_id , -1 ) inventory_item_id ,
996 nvl(first_rslvd.inv_organization_id, -1 ) inv_organization_id ,
997 -- once prob and resol code are added to audit table,
998 -- change the source of these columns and remove sr table from
999 -- from clause
1000 nvl(first_rslvd.problem_code ,''-1'') problem_code ,
1001 nvl(first_rslvd.resolution_code ,''-1'') resolution_code ,
1002 count(first_rslvd.incident_id) sr_resolved ,
1003 0 sr_reopened,
1004 0 sr_reopened2
1005 from cs_incidents_audit_b first_rslvd,
1006 cs_incidents_all_b sr
1007 where sr.incident_id = first_rslvd.incident_id
1008 and first_rslvd.incident_owner_id is not null
1009 -- so that only those rec are selected where resolution date is
1010 -- set from null to NOT NULL
1011 and nvl(first_rslvd.incident_resolved_date, :l_dt) <>
1012 nvl(first_rslvd.old_incident_resolved_date,:l_dt)
1013 and first_rslvd.incident_resolved_date is not null
1014 -- select only resloutions in a given period
1015 and first_rslvd.creation_date between :p_from_date and :p_to_date
1016 -- select a resolution only if it first time resolution
1017 and not exists (select 1 from cs_incidents_audit_b x
1018 where x.incident_resolved_date is not null
1019 and nvl(x.incident_resolved_date, :l_dt) <>
1020 nvl(x.old_incident_resolved_date, :l_dt)
1021 and x.incident_id = first_rslvd.incident_id
1022 and x.incident_owner_id = first_rslvd.incident_owner_id
1023 and x.creation_date < first_rslvd.creation_date
1024 )
1025 -- this will give first resolution information in a given period
1026 group by first_rslvd.incident_owner_id,
1027 first_rslvd.incident_severity_id,
1028 first_rslvd.inventory_item_id,
1029 first_rslvd.inv_organization_id,
1030 first_rslvd.problem_code,
1031 first_rslvd.resolution_code,
1032 trunc(first_rslvd.incident_resolved_date)' ;
1033 debug(l_sql);
1034 debug('before call to upload_first_resolution');
1035 upload_first_resolutions(l_sql,'A',p_from_date, p_to_date,'RESL');
1036 -- group quality
1037 l_sql := replace(l_sql,'incident_owner_id','group_id');
1038 debug('before call to upload_first_resolution for group');
1039 upload_first_resolutions(l_sql,'G',p_from_date, p_to_date,'RESL');
1040 -- Reopen and reopen for second or subsequent times
1041 l_sql := '
1042 select incident_owner_id,
1043 incident_severity_id,
1044 summary_date,
1045 inventory_item_id,
1046 inv_organization_id,
1047 problem_code,
1048 resolution_code,
1049 0 sr_resolved,
1050 sum(rework) rework,
1051 sum(rework2) rework2 from (
1052 select last_unrslvd.incident_owner_id incident_owner_id,
1053 nvl(last_unrslvd.incident_severity_id,-1) incident_severity_id,
1054 trunc(last_unrslvd.creation_date) summary_date,
1055 last_unrslvd.incident_id incident_id,
1056 nvl(sr.inventory_item_id,-1) inventory_item_id,
1057 nvl(sr.inv_organization_id,-1) inv_organization_id,
1058 nvl(last_unrslvd.problem_code,''-1'') problem_code,
1059 nvl(last_unrslvd.resolution_code,''-1'') resolution_code,
1060 decode(count(last_unrslvd.old_incident_resolved_date),0,0,1) rework,
1061 /* if there are any old close dates, rewrk will be one. so for
1062 first reopen or second reopen
1063 it will always return 1*/
1064 decode(count(prev_unrsltns.old_incident_resolved_date),0,0, 1) rework2
1065 /* 1 mean there are atleast two reopen, 1 for last_unrslvd and
1066 1 from prev_unrsltns
1067 it will return 1 only if there are atleast 2 old close dates */
1068 from cs_incidents_audit_b last_unrslvd,
1069 cs_incidents_audit_b prev_unrsltns,
1070 cs_incidents_all_b sr
1071 where sr.incident_id = last_unrslvd.incident_id
1072 and last_unrslvd.incident_owner_id is not null
1073 and last_unrslvd.old_incident_resolved_date is not null
1074 and last_unrslvd.incident_resolved_date is null
1075 /* select only last reopen in a given period */
1076 and last_unrslvd.creation_date between :p_from_date and :p_to_date
1077 and last_unrslvd.incident_audit_id =
1078 (select max(incident_audit_id) from cs_incidents_audit_b x
1079 where x.old_incident_resolved_date is not null
1080 and x.incident_resolved_date is null
1081 and x.incident_id = last_unrslvd.incident_id
1082 and x.incident_owner_id = last_unrslvd.incident_owner_id
1083 and x.creation_date between :p_from_date and :p_to_date
1084 ) /* this will give last reopen information in a given period*/
1085 and prev_unrsltns.incident_id (+) = last_unrslvd.incident_id
1086 and prev_unrsltns.creation_date (+) < last_unrslvd.creation_date
1087 and prev_unrsltns.incident_owner_id (+) = last_unrslvd.incident_owner_id
1088 and prev_unrsltns.incident_resolved_date (+) is null
1089 and prev_unrsltns.old_incident_resolved_date (+) is not null
1090 /* above 5 lines join with audit table where same SR was set from
1091 resolved to unresolved*/
1092 group by last_unrslvd.incident_owner_id,
1093 last_unrslvd.incident_id,
1094 last_unrslvd.incident_severity_id,
1095 sr.inventory_item_id,
1096 sr.inv_organization_id,
1097 last_unrslvd.problem_code,
1098 last_unrslvd.resolution_code,
1099 trunc(last_unrslvd.creation_date))
1100 group by incident_owner_id,
1101 incident_severity_id,
1102 summary_date,
1103 inventory_item_id,
1104 inv_organization_id,
1105 problem_code,
1106 resolution_code';
1107 upload_first_resolutions(l_sql,'A',p_from_date, p_to_date, 'REOPEN');
1108 debug(l_sql);
1109 l_sql := replace(l_sql,'incident_owner_id','group_id');
1110 upload_first_resolutions(l_sql,'G',p_from_date, p_to_date, 'REOPEN');
1111 debug(l_sql);
1112 -- reversal of reopen and reopen for second or subsequent times
1113 l_sql := '
1114 select incident_owner_id,
1115 incident_severity_id,
1116 summary_date,
1117 inventory_item_id,
1118 inv_organization_id,
1119 problem_code,
1120 resolution_code,
1121 0 sr_resolved,
1122 -1 * sum(rework) rework,
1123 -1 * sum(reopen) reopen from (
1124 select prev_unrsltns.incident_owner_id,
1125 prev_unrsltns.incident_id,
1126 prev_unrsltns.incident_severity_id,
1127 trunc(prev_unrsltns.creation_date) summary_date,
1128 nvl(prev_unrsltns.inventory_item_id,-1) inventory_item_id,
1129 nvl(prev_unrsltns.inv_organization_id,-1) inv_organization_id,
1130 nvl(prev_unrsltns.problem_code,''-1'') problem_code,
1131 nvl(prev_unrsltns.resolution_code,''-1'') resolution_code,
1132 decode(count(prev_unrsltns.old_incident_resolved_date),0,0,1) rework,
1133 /* 1 */
1134 decode(count(prev_unrsltns1.old_incident_resolved_date),0,0, 1)reopen
1135 /** it will return 1 only if there are atleast 2 old close dates */
1136 from cs_incidents_audit_b curr_unrslvd,
1137 cs_incidents_audit_b prev_unrsltns,
1138 /* this indicates if a sr is reworked*/
1139 cs_incidents_audit_b prev_unrsltns1,
1140 /* this table indicates if a sr is reworked more than once*/
1141 cs_incidents_all_b sr
1142 where sr.incident_id = prev_unrsltns.incident_id
1143 and curr_unrslvd.incident_owner_id is not null
1144 and curr_unrslvd.old_incident_resolved_date is not null
1145 and curr_unrslvd.incident_resolved_date is null
1146 /* select only rework in a given period */
1147 and curr_unrslvd.creation_date between :p_from_date and :p_to_date
1148 and curr_unrslvd.incident_audit_id =
1149 (select max(incident_audit_id) from cs_incidents_audit_b x
1150 where x.old_incident_resolved_date is not null
1151 and x.incident_resolved_date is null
1152 and x.incident_id = curr_unrslvd.incident_id
1153 and x.incident_owner_id = curr_unrslvd.incident_owner_id
1154 and x.creation_date between :p_from_date and :p_to_date
1155 ) /* this will give last unresolution information
1156 in a given period */
1157 /* 2 */
1158 and prev_unrsltns.incident_id = curr_unrslvd.incident_id
1159 and prev_unrsltns.creation_date < :p_from_date
1160 /* here we need to look for unresolutions before concurrent program run.*/
1161 and prev_unrsltns.incident_resolved_date is null
1162 and prev_unrsltns.old_incident_resolved_date is not null
1163 and prev_unrsltns.incident_owner_id = curr_unrslvd.incident_owner_id
1164 and prev_unrsltns.incident_audit_id =
1165 (select max(y.incident_audit_id) from cs_incidents_audit_b y
1166 where y.incident_id = prev_unrsltns.incident_id
1167 and y.incident_owner_id = prev_unrsltns.incident_owner_id
1168 and y.creation_date < :p_from_date
1169 and y.incident_resolved_date is null
1170 and y.old_incident_resolved_date is not null
1171 )
1172 and prev_unrsltns1.incident_id (+) = prev_unrsltns.incident_id
1173 and prev_unrsltns1.creation_date (+) < prev_unrsltns.creation_date
1174 and prev_unrsltns1.incident_resolved_date (+) is null
1175 and prev_unrsltns1.old_incident_resolved_date (+) is not null
1176 and prev_unrsltns1.incident_owner_id (+) = prev_unrsltns.incident_owner_id
1177 group by prev_unrsltns.incident_owner_id,
1178 prev_unrsltns.incident_id,
1179 prev_unrsltns.incident_severity_id,
1180 trunc(prev_unrsltns.creation_date),
1181 prev_unrsltns.inventory_item_id,
1182 prev_unrsltns.inv_organization_id,
1183 prev_unrsltns.problem_code,
1184 prev_unrsltns.resolution_code)
1185 group by incident_owner_id,
1186 incident_severity_id,
1187 summary_date,
1188 inventory_item_id,
1189 inv_organization_id,
1190 problem_code,
1191 resolution_code';
1192 /* 1
1193 if there are any close dates, rewrk will be one. so for any reopen
1194 it will always return 1
1195 **/
1196 /* 2
1197 -- No need for outer join here like in previous query.
1198 in previous qry, we are not sure if
1199 -- there is any previous reopen. Here we are sure that count need
1200 to be subtracted
1201 -- only if previous reopens exists
1202 */
1203 debug('before call to upload_first_resolution reverse for Agent');
1204 debug(l_sql);
1205 upload_first_resolutions(l_sql,'A',p_from_date, p_to_date, 'REVERSE');
1206 -- Update for Group
1207 l_sql := replace(l_sql,'incident_owner_id','group_id');
1208 debug('before call to upload_first_resolution reverse for Group');
1209 debug(l_sql);
1210 upload_first_resolutions(l_sql,'G',p_from_date, p_to_date, 'REVERSE');
1211 ---
1212 ---
1213 exception
1214 when others then
1215 fnd_file.put_line(fnd_file.log,'Error:'||sqlerrm);
1216 raise fnd_api.g_exc_unexpected_error;
1217 end;
1218 procedure upload_assignments(p_sql varchar2,
1219 p_load_type varchar2,
1220 p_owner_type varchar2,
1221 p_from_date date,
1222 p_to_date date
1223 ) as
1224 l_dt date;
1225 l_owner_id jtf_rs_resource_extns.resource_id % type;
1226 l_group_id jtf_rs_groups_b.group_id % type;
1227 l_sev_id cs_incident_severities_b.incident_severity_id % type;
1228 l_sr_in number;
1229 l_sr_out number;
1230 Type AssignmentCursorType is ref cursor;
1231 c_agent_assignment AssignmentCursorType;
1232 l_incident_owner_id cs_incidents_all_b.incident_owner_id % type;
1233 l_owner_group_id cs_incidents_all_b.owner_group_id % type;
1234 begin
1235 if (p_load_type = 'ADD' ) then
1236 open c_agent_assignment for p_sql using
1237 p_from_date, p_to_date,
1238 p_from_date, p_to_date,
1239 p_from_date, p_to_date,
1240 p_from_date, p_to_date;
1241 else
1242 open c_agent_assignment for p_sql using
1243 p_from_date, p_to_date,
1244 p_from_date, p_to_date,
1245 p_from_date, p_from_date,
1246 --p_from_date, p_to_date,
1247 --p_from_date, p_to_date,
1248 --p_from_date, p_from_date,
1249 p_from_date, p_to_date,
1250 p_from_date, p_to_date,
1251 p_from_date, p_from_date;
1252 end if;
1253 l_incident_owner_id := -1;
1254 l_owner_group_id := -1;
1255 loop
1256 fetch c_agent_assignment into l_dt, l_owner_id, l_sev_id,
1257 l_sr_in, l_sr_out;
1258 if c_agent_assignment%notfound then exit; end if;
1259 if (p_owner_type = 'A') then
1260 l_incident_owner_id := l_owner_id;
1261 else
1262 l_owner_group_id := l_owner_id;
1263 end if;
1264 update csy_response_resolutions
1265 set total_sr_assigned = nvl(total_sr_assigned,0)+l_sr_in,
1266 total_sr_reassigned_to_others = nvl(total_sr_reassigned_to_others,0)+
1267 l_sr_out
1268 where summary_date = l_dt
1269 and incident_owner_id = l_incident_owner_id
1270 and owner_group_id = l_owner_group_id
1271 and owner_type = p_owner_type
1272 and incident_severity_id = l_sev_id;
1273 if (sql%notfound) then
1274 insert into csy_response_resolutions
1275 (summary_date,
1276 incident_owner_id,
1277 incident_severity_id,
1278 total_sr_assigned,
1279 total_sr_reassigned_to_others,
1280 owner_group_id,
1281 owner_type,
1282 last_update_date ,
1283 last_updated_by ,
1284 creation_date ,
1285 created_by ,
1286 last_update_login ,
1287 program_id ,
1288 program_login_id ,
1289 program_application_id ,
1290 request_id )
1291 values (l_dt,
1292 l_incident_owner_id,
1293 l_sev_id,
1294 l_sr_in,
1295 l_sr_out,
1296 l_owner_group_id,
1297 p_owner_type,
1298 sysdate ,
1299 g_user_id ,
1300 sysdate ,
1301 g_user_id ,
1302 g_login_user_id ,
1303 g_conc_program_id ,
1304 g_conc_login_id ,
1305 g_conc_appl_id ,
1306 g_conc_request_id );
1307 end if;
1308 end loop;
1309 close c_agent_assignment;
1310 end;
1311 procedure get_sr_agent_assignments(p_from_date in date,
1312 p_to_date in date) is
1313 --cursor c_agent_assignment is
1314 l_sql varchar2(8000);
1315 l_sql_in_sel varchar2(1000);
1316 l_sql_out_sel varchar2(1000);
1317 l_sql_in_sel_r varchar2(1000);
1318 l_sql_out_sel_r varchar2(1000);
1319 l_sql_in_whr varchar2(2000);
1320 l_sql_out_whr varchar2(2000);
1321 l_sql_in_group_by varchar2(1000);
1322 l_sql_out_group_by varchar2(1000);
1323 l_sql_sr_in_rev varchar2(4000);
1324 l_sql_sr_out_rev varchar2(4000);
1325 l_sql_sr_out_rev1 varchar2(4000);
1326 l_temp varchar2(4000);
1327 begin
1328 l_sql_in_sel := '
1329 select trunc(aud.creation_date) summary_date,
1330 aud.incident_owner_id incident_owner_id,
1331 nvl(incident_severity_id,-1) incident_severity_id,
1332 aud.incident_id incident_id_in,
1333 to_number(null) incident_id_out';
1334 l_sql_in_sel_r := '
1335 select aud.incident_id,
1336 aud.incident_owner_id';
1337 l_sql_in_whr := '
1338 from cs_incidents_audit_b aud
1339 where /*nvl(aud.incident_owner_id,-1) <> nvl(aud.old_incident_owner_id,-1)
1340 and aud.incident_owner_id is not null
1341 and */ aud.creation_date between :p_from_date and :p_to_date
1342 -- same condition are present in subquery too. it existance of these conditions outside the
1343 -- subquery is meaningless. remove it when modifying this query.
1344 and aud.incident_audit_id =
1345 (select max(incident_audit_id)
1346 from cs_incidents_audit_b aud_in
1347 where aud_in.incident_id = aud.incident_id
1348 and aud_in.creation_date between :p_from_date
1349 and :p_to_date
1350 and aud_in.incident_owner_id = aud.incident_owner_id
1351 -- above con will take care of aud_in.incident_woner_id
1352 -- is not null
1353 and aud_in.incident_owner_id is not null
1354 and (nvl(aud_in.incident_owner_id,-1) <>
1355 nvl(aud_in.old_incident_owner_id,-1) or
1356 aud_in.incident_severity_id <> nvl(aud_in.old_incident_severity_id,-1)
1357 )
1358 )';
1359 /*
1360 l_sql_in_group_by := '
1361 group by trunc(aud.creation_date),
1362 aud.incident_owner_id,
1363 nvl(aud.group_id,-1),
1364 incident_severity_id';
1365 ****/
1366 l_sql_out_sel := '
1367 select trunc(aud.creation_date) summary_date,
1368 aud.old_incident_owner_id incident_owner_id,
1369 nvl(incident_severity_id,-1) incident_severity_id,
1370 to_number(null) incident_id_in,
1371 aud.incident_id incident_id_out';
1372 l_sql_out_sel_r := '
1373 select aud.incident_id,
1374 aud.old_incident_owner_id';
1375 l_sql_out_whr := '
1376 from cs_incidents_audit_b aud
1377 where nvl(aud.incident_owner_id,-1) <> nvl(aud.old_incident_owner_id,-1)
1378 and aud.old_incident_owner_id is not null
1379 and aud.creation_date between :p_from_date and :p_to_date
1380 and aud.incident_audit_id =
1381 (select max(incident_audit_id)
1382 from cs_incidents_audit_b aud_in
1383 where aud_in.incident_id = aud.incident_id
1384 and aud_in.creation_date between :p_from_date and :p_to_date
1385 and (aud_in.old_incident_owner_id =aud.old_incident_owner_id or
1386 aud_in.incident_owner_id =aud.old_incident_owner_id )
1387 and nvl(aud_in.incident_owner_id,-1) <>
1388 nvl(aud_in.old_incident_owner_id,-1)
1389 )';
1390 /* in above statement comparaing aud.old_incident_owner_id ot new and old owner is required
1391 suppose a sr is assigned to A1 and then to A2 and then back to A1. if we do not use new and
1392 old owners, then audit record represent change to A2 will get selected and it will give 1
1393 reassigned to others for A1. comparing old and new both will prevent it.
1394 1/8/2004 smisra
1395 */
1396 /*
1397 l_sql_out_group_by := '
1398 group by trunc(aud.creation_date),
1399 aud.old_incident_owner_id,
1400 nvl(aud.old_group_id,-1),
1401 incident_severity_id';
1402 */
1403 l_sql := 'select summary_date,
1404 incident_owner_id,
1405 incident_severity_id,
1406 count(distinct incident_id_in) sr_in,
1407 count(distinct incident_id_out) sr_out
1408 from ( ' ||l_sql_in_sel || l_sql_in_whr || ' union ' ||
1409 l_sql_out_sel || l_sql_out_whr || ')
1410 group by summary_date, incident_owner_id,
1411 incident_severity_id' ;
1412 debug(l_sql);
1413 upload_assignments(l_sql,
1414 'ADD', 'A', p_from_date, p_to_date);
1415 l_sql := replace(l_sql,'incident_owner_id', 'group_id');
1416 debug(l_sql);
1417 upload_assignments(l_sql,
1418 'ADD', 'G', p_from_date, p_to_date);
1419 l_sql_sr_in_rev := '
1420 select trunc(prev_asgn.creation_date) summary_date,
1421 prev_asgn.incident_owner_id incident_owner_id,
1422 nvl(incident_severity_id,-1) incident_severity_id,
1423 prev_asgn.incident_id incident_id_in,
1424 to_number(null) incident_id_out
1425 from cs_incidents_audit_b prev_asgn, ( ' || l_sql_in_sel_r ||
1426 l_sql_in_whr ||'
1427 ) cur_asgn
1428 where cur_asgn.incident_id = prev_asgn.incident_id
1429 and cur_asgn.incident_owner_id = prev_asgn.incident_owner_id
1430 and nvl(prev_asgn.incident_owner_id,-1) <>
1431 nvl(prev_asgn.old_incident_owner_id,-1)
1432 and prev_asgn.incident_owner_id is not null
1433 and prev_asgn.creation_date < :p_from_date
1434 and prev_asgn.incident_audit_id =
1435 (select max(incident_audit_id)
1436 from cs_incidents_audit_b aud_in
1437 where aud_in.incident_id = prev_asgn.incident_id
1438 and aud_in.incident_owner_id = prev_asgn.incident_owner_id
1439 and (nvl(aud_in.incident_owner_id,-1) <>
1440 nvl(aud_in.old_incident_owner_id,-1) or
1441 aud_in.incident_severity_id <> nvl(aud_in.old_incident_Severity_id,-1)
1442 )
1443 and aud_in.incident_owner_id is not null
1444 and aud_in.creation_date < :p_from_date
1445 )';
1446 /**** this one is not used anymore
1447 l_sql_sr_out_rev := '
1448 select trunc(prev_asgn.creation_date) summary_date,
1449 prev_asgn.old_incident_owner_id incident_owner_id,
1450 nvl(incident_severity_id,-1) incident_severity_id,
1451 null incident_id_in,
1452 prev_asgn.incident_id incident_id_out
1453 from cs_incidents_audit_b prev_asgn, ( ' || l_sql_out_sel_r ||
1454 l_sql_out_whr || '
1455 ) cur_asgn
1456 where cur_asgn.incident_id = prev_asgn.incident_id
1457 and cur_asgn.old_incident_owner_id = prev_asgn.old_incident_owner_id
1458 and nvl(prev_asgn.incident_owner_id,-1) <>
1459 nvl(prev_asgn.old_incident_owner_id,-1)
1460 and prev_asgn.old_incident_owner_id is not null
1461 and prev_asgn.creation_date < :p_from_date
1462 and prev_asgn.incident_audit_id =
1463 (select max(incident_audit_id)
1464 from cs_incidents_audit_b aud_in
1465 where aud_in.incident_id = prev_asgn.incident_id
1466 and aud_in.old_incident_owner_id = prev_asgn.old_incident_owner_id
1467 and nvl(prev_asgn.incident_owner_id,-1) <>
1468 nvl(prev_asgn.old_incident_owner_id,-1)
1469 and prev_asgn.old_incident_owner_id is not null
1470 and aud_in.creation_date < :p_from_date
1471 )';
1472 ***** 3/5/04 smisra ********************************************/
1473 /* 10/17/2003
1474 This query will reverse any out assignments when a service request
1475 is assigned back to same agent. For example Agent A1 was assgined
1476 an SR on 10/1/03, on 10/4/03, same SR wasd assigned to Agent A2
1477 on 10/17/03, SR comes back to Agent A1. in that case there is need for
1478 two reversals, one for 10/1/03 correcting SR IN and one for 10/4/03
1479 correcting SR OUT. So on 10/17, for Agent A1, SR IN will be one and
1480 SR OUT will be zero.
1481 */
1482 l_sql_sr_out_rev1 := '
1483 select trunc(prev_asgn.creation_date) summary_date,
1484 prev_asgn.old_incident_owner_id incident_owner_id,
1485 nvl(incident_severity_id,-1) incident_severity_id,
1486 to_number(null) incident_id_in,
1487 prev_asgn.incident_id incident_id_out
1488 from cs_incidents_audit_b prev_asgn, ( ' || l_sql_in_sel_r ||
1489 l_sql_in_whr || '
1490 ) cur_asgn
1491 where cur_asgn.incident_id = prev_asgn.incident_id
1492 and cur_asgn.incident_owner_id = prev_asgn.old_incident_owner_id
1493 and nvl(prev_asgn.incident_owner_id,-1) <>
1494 nvl(prev_asgn.old_incident_owner_id,-1)
1495 and prev_asgn.old_incident_owner_id is not null
1496 and prev_asgn.creation_date < :p_from_date
1497 and prev_asgn.incident_audit_id =
1498 (select max(incident_audit_id)
1499 from cs_incidents_audit_b aud_in
1500 where aud_in.incident_id = prev_asgn.incident_id
1501 and aud_in.old_incident_owner_id = prev_asgn.old_incident_owner_id
1502 and nvl(aud_in.incident_owner_id,-1) <>
1503 nvl(aud_in.old_incident_owner_id,-1)
1504 and aud_in.old_incident_owner_id is not null
1505 and aud_in.creation_date < :p_from_date
1506 )';
1507 /* in this statement l_sql_sr_out_rev is commented out. reason whenever, SR is assigned to
1508 an agent, SR OUT are reveresed at that time. so no need to do it again
1509 1/8/2004 smisra
1510 */
1511 l_sql := ' select summary_date,
1512 incident_owner_id,
1513 incident_severity_id,
1514 count(distinct incident_id_in) * -1 sr_in,
1515 count(distinct incident_id_out) * -1 sr_our
1516 from ( ' || l_sql_sr_in_rev || ' union ' ||
1517 /* l_sql_sr_out_rev || ' union ' ||*/
1518 l_sql_sr_out_rev1 || ')
1519 group by summary_date,
1520 incident_owner_id,
1521 incident_severity_id';
1522 debug(l_sql);
1523 upload_assignments(l_sql,
1524 'REV', 'A', p_from_date, p_to_date);
1525
1526 l_sql := replace(l_sql,'incident_owner_id', 'group_id');
1527 debug(l_sql);
1528 upload_assignments(l_sql,
1529 'REV', 'G', p_from_date, p_to_date);
1530 /*
1531 */
1532 exception
1533 when others then
1534 fnd_file.put_line(fnd_file.log,'Error:'||sqlerrm);
1535 raise fnd_api.g_exc_unexpected_error;
1536 end;
1537 procedure get_sr_group_assignments(p_from_date in date,
1538 p_to_date in date) is
1539 begin
1540 null;
1541 /* 10/13/2003 Not used
1542 merge into csy_response_resolutions a using (
1543 select trunc(aud.creation_date) summary_date,
1544 aud.group_id group_id ,
1545 incident_severity_id incident_severity_id,
1546 count(distinct aud.incident_id) sr_in,
1547 0 sr_out
1548 from cs_incidents_audit_b aud
1549 where nvl(aud.group_id,-1) <> nvl(aud.old_group_id,-1)
1550 and aud.group_id is not null
1551 and aud.creation_date between p_from_date and p_to_date
1552 and aud.incident_audit_id =
1553 (select max(incident_audit_id)
1554 from cs_incidents_audit_b aud_in
1555 where aud_in.incident_id = aud.incident_id
1556 and aud_in.creation_date between p_from_date and p_to_date
1557 and aud_in.group_id = aud.group_id
1558 and nvl(aud_in.group_id,-1) <> nvl(aud_in.old_group_id,-1)
1559 and aud_in.group_id is not null
1560 )
1561 group by trunc(aud.creation_date),
1562 aud.group_id ,
1563 incident_severity_id
1564 union
1565 select trunc(aud.creation_date),
1566 aud.old_group_id ,
1567 incident_severity_id,
1568 0,
1569 count(distinct aud.incident_id) sr_agent_out
1570 from cs_incidents_audit_b aud
1571 where nvl(aud.group_id,-1) <> nvl(aud.old_group_id,-1)
1572 and aud.old_group_id is not null
1573 and aud.creation_date between p_from_date and p_to_date
1574 and aud.incident_audit_id =
1575 (select max(incident_audit_id)
1576 from cs_incidents_audit_b aud_in
1577 where aud_in.incident_id = aud.incident_id
1578 and aud_in.creation_date between p_from_date and p_to_date
1579 and aud_in.old_group_id =aud.old_group_id
1580 and nvl(aud_in.group_id,-1) <> nvl(aud_in.old_group_id,-1)
1581 and aud_in.old_group_id is not null
1582 )
1583 group by trunc(aud.creation_date),
1584 aud.old_group_id ,
1585 incident_severity_id) b
1586 on (a.summary_date = b.summary_date and
1587 a.owner_group_id = b.group_id and
1588 a.incident_severity_id = b.incident_Severity_id and
1589 a.incident_owner_id = -1 and
1590 a.owner_type = 'G')
1591 when matched then
1592 update set total_sr_assigned = nvl(a.total_sr_assigned,0) + b.sr_in,
1593 total_sr_reassigned_to_others
1594 = nvl(a.total_sr_reassigned_to_others,0) + b.sr_out
1595 when not matched then
1596 insert (summary_date ,
1597 owner_group_id ,
1598 incident_severity_id,
1599 total_sr_assigned ,
1600 total_sr_reassigned_to_others,
1601 incident_owner_id,
1602 owner_type)
1603 values (b.summary_date,
1604 b.group_id ,
1605 nvl(b.incident_severity_id,-1),
1606 b.sr_in,
1607 b.sr_out,
1608 -1, 'G');
1609 exception
1610 when others then
1611 fnd_file.put_line(fnd_file.log,'Error:'||sqlerrm);
1612 ***************************************************************************/
1613 end;
1614 procedure get_response_timings(p_from_date in date,
1615 p_to_date in date) as
1616 l_dt date ;
1617 l_sql varchar2(4000);
1618 l_sla_missed number;
1619 cursor c_responses is
1620 select nvl(aud.incident_owner_id,-1) incident_owner_id,
1621 nvl (aud.incident_severity_id,-1) incident_severity_id,
1622 nvl (aud.group_id,-1) owner_group_id,
1623 trunc(nvl(aud.inc_responded_by_date,incident_resolved_date)) inc_responded_by_date,
1624 count(aud.incident_id) responses,
1625 -- in responded_bydate is greater than obligation date, sla missed
1626 count(decode(sign(nvl(aud.inc_responded_by_date,aud.incident_resolved_date)-aud.obligation_date),
1627 1,1,null)) resp_sla_missed
1628 from cs_incidents_audit_b aud --this is audit rec for response
1629 where aud.incident_audit_id =
1630 ( select max(incident_audit_id)
1631 from cs_incidents_audit_b aud_in
1632 where aud_in.incident_id = aud.incident_id
1633 and aud_in.creation_date between p_from_date
1634 and p_to_date
1635 and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>
1636 nvl(nvl(aud_in.inc_responded_by_date,aud_in.incident_resolved_date),l_dt)
1637 and (aud_in.inc_responded_by_date is not null or
1638 aud_in.incident_resolved_date is not null)
1639 -- above cond is needed to make sure that sr is
1640 -- responded. if this cond is not there then inc_responded_by_date
1641 -- as null to may get selected which is clearly not
1642 -- responded condition.
1643 )
1644 -- above query will insure that selected response is the last response
1645 group by aud.incident_owner_id,
1646 aud.group_id,
1647 aud.incident_severity_id,
1648 trunc(nvl(aud.inc_responded_by_date,incident_resolved_date));
1649 cursor c_responses_rev is
1650 select /*+ ORDERED */
1651 nvl(prev_resp.incident_owner_id,-1) incident_owner_id,
1652 nvl (prev_resp.incident_severity_id,-1) incident_severity_id,
1653 nvl (prev_resp.group_id,-1) owner_group_id,
1654 trunc(nvl(prev_resp.inc_responded_by_date,prev_resp.incident_resolved_date)) inc_responded_by_date,
1655 --sum(prev_resp.inc_responded_by_date - prev_resp.incident_date) resp_time,
1656 -- we need to select distinct because each responded
1657 -- audit will be joined with mulitple from and to aduit record
1658 count(distinct prev_resp.incident_id) responses,
1659 -- in responded_bydate is greated than obligation date, sla missed
1660 count(decode(sign(nvl(prev_resp.inc_responded_by_date,prev_resp.incident_resolved_date)-prev_resp.obligation_date),
1661 1,1,null)) resp_sla_missed
1662 from cs_incidents_audit_b curr_resp,
1663 --this is audit rec for response in curr run dates
1664 cs_incidents_audit_b prev_resp
1665 -- this is response in before curr run dates
1666 where curr_resp.incident_audit_id =
1667 ( select max(incident_audit_id)
1668 from cs_incidents_audit_b aud_in
1669 where aud_in.incident_id = curr_resp.incident_id
1670 and aud_in.creation_date between p_from_date
1671 and p_to_date
1672 and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>
1673 nvl(nvl(aud_in.inc_responded_by_date,aud_in.incident_resolved_date),l_dt)
1674 and (aud_in.inc_responded_by_date is not null or
1675 aud_in.incident_resolved_date is not null)
1676 -- above cond is needed to make sure that sr is
1677 -- responded. if this cond is not there then inc_responded_by_date
1678 -- as null to may get selected which is clearly not
1679 -- responded condition.
1680 )
1681 -- above query will insure that selected response is the last response
1682 and prev_resp.incident_id = curr_resp.incident_id
1683 and prev_resp.incident_audit_id = ( select max(incident_audit_id)
1684 from cs_incidents_audit_b aud_in1
1685 where aud_in1.incident_id = curr_resp.incident_id
1686 and aud_in1.creation_date < p_from_date
1687 and nvl(nvl(aud_in1.old_inc_responded_by_date,aud_in1.old_incident_resolved_date),l_dt) <>
1688 nvl(nvl(aud_in1.inc_responded_by_date,aud_in1.incident_resolved_date),l_dt)
1689 and (aud_in1.inc_responded_by_date is not null or
1690 aud_in1.incident_resolved_date is not null)
1691 -- above cond is needed to make sure that sr is
1692 -- responded. if this cond is not there then inc_responded_by_date
1693 -- as null to may get selected which is clearly not
1694 -- responded condition.
1695 )
1696 group by prev_resp.incident_owner_id,
1697 prev_resp.group_id,
1698 prev_resp.incident_severity_id,
1699 trunc(nvl(prev_resp.inc_responded_by_date,prev_resp.incident_resolved_date));
1700 cursor c_resp_times is
1701 select /*+ ORDERED */
1702 nvl(aud.incident_owner_id,-1) incident_owner_id,
1703 nvl (aud.incident_severity_id,-1) incident_severity_id,
1704 nvl (aud.group_id,-1) owner_group_id,
1705 trunc(nvl(aud.inc_responded_by_date,aud.incident_resolved_date)) inc_responded_by_date,
1706 sum(decode(aud.incident_owner_id,to_dttm.old_incident_owner_id,
1707 csy_kpi_pkg.get_agents_time(to_dttm.old_incident_owner_id,
1708 decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),
1709 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
1710 nvl(aud.inc_responded_by_date,aud.incident_resolved_date),to_dttm.creation_date)),0)
1711 ) * 1440 resp_time,
1712 -- we need to select distinct because each responded
1713 -- audit will be joined with mulitple from and to aduit record
1714 --count(distinct aud.incident_id) responses,
1715 sum(decode(aud.incident_owner_id,to_dttm.old_incident_owner_id,
1716 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
1717 nvl(aud.inc_responded_by_date,aud.incident_resolved_date),to_dttm.creation_date)
1718 -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),
1719 0)
1720 ) * 1440 waiting_on_me,
1721 sum(decode(aud.incident_owner_id,to_dttm.old_incident_owner_id,
1722 0,
1723 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
1724 nvl(aud.inc_responded_by_date,aud.incident_resolved_date),to_dttm.creation_date)
1725 -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date)
1726 )) * 1440 not_waiting_on_me
1727 from cs_incidents_audit_b aud , --this is audit rec for response
1728 cs_incidents_audit_b to_dttm , -- to date time
1729 cs_incidents_audit_b from_dttm -- ,from date time
1730 /* the pair of from_dttm to to_dttm will give the durating in which an agent
1731 owned a serveice request. */
1732 where aud.incident_audit_id =
1733 ( select max(incident_audit_id)
1734 from cs_incidents_audit_b aud_in
1735 where aud_in.incident_id = aud.incident_id
1736 and aud_in.creation_date between p_from_date
1737 and p_to_date
1738 and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>
1739 nvl(nvl(aud_in.inc_responded_by_date,aud_in.incident_resolved_date),l_dt)
1740 and (aud_in.inc_responded_by_date is not null or
1741 aud_in.incident_resolved_date is not null)
1742 -- above cond is needed to make sure that sr is
1743 -- responded. if this cond is not there then inc_responded_by_date
1744 -- as null to may get selected which is clearly not
1745 -- responded condition.
1746 )
1747 -- above query will insure that selected response is the last response
1748 and to_dttm.incident_id = aud.incident_id
1749 and to_dttm.creation_date <= aud.creation_date
1750 and (to_dttm.incident_audit_id = aud.incident_audit_id or
1751 (nvl(to_dttm.old_incident_owner_id,-1) <>
1752 nvl(to_dttm.incident_owner_id,-1) and
1753 to_dttm.creation_date >= to_dttm.incident_date)
1754 )
1755 -- above will insure that to_dttm start from responded audit rec
1756 and aud.incident_id = from_dttm.incident_id
1757 /*
1758 and (nvl(from_dttm.old_incident_owner_id,-1) <>
1759 nvl(from_dttm.incident_owner_id,-1) or
1760 nvl(from_dttm.old_incident_date,trunc(sysdate-300)) <>
1761 nvl(from_dttm.incident_date,trunc(sysdate-300))
1762 )
1763 */
1764 and from_dttm.incident_audit_id =
1765 (select max(incident_audit_id) from cs_incidents_audit_b x
1766 where x.incident_id = aud.incident_id
1767 and ((nvl(x.old_incident_owner_id,-1) <>
1768 nvl(x.incident_owner_id,-1) and
1769 x.creation_date >= x.incident_date) or
1770 nvl(x.old_incident_date,trunc(sysdate-300)) <>
1771 nvl(x.incident_date,trunc(sysdate-300))
1772 )
1773 and x.creation_date < to_dttm.creation_date
1774 )
1775 group by aud.incident_owner_id,
1776 aud.group_id,
1777 aud.incident_severity_id,
1778 trunc(nvl(aud.inc_responded_by_date,aud.incident_resolved_date))
1779 ;
1780 cursor c_resp_times_rev is
1781 select /*+ ORDERED */
1782 nvl(aud.incident_owner_id,-1) incident_owner_id,
1783 nvl (aud.incident_severity_id,-1) incident_severity_id,
1784 nvl (aud.group_id,-1) owner_group_id,
1785 trunc(nvl(aud.inc_responded_by_date,aud.incident_resolved_date)) inc_responded_by_date,
1786 sum(decode(aud.incident_owner_id,to_dttm.old_incident_owner_id,
1787 csy_kpi_pkg.get_agents_time(to_dttm.old_incident_owner_id,
1788 decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),
1789 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
1790 nvl(aud.inc_responded_by_date,aud.incident_resolved_date),
1791 to_dttm.creation_date)),0)
1792 ) * 1440 resp_time,
1793 -- we need to select distinct because each responded
1794 -- audit will be joined with mulitple from and to aduit record
1795 --count(distinct aud.incident_id) responses,
1796 sum(decode(aud.incident_owner_id,to_dttm.old_incident_owner_id,
1797 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
1798 nvl(aud.inc_responded_by_date,aud.incident_resolved_date),to_dttm.creation_date)
1799 - decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),
1800 0)
1801 ) * 1440 waiting_on_me,
1802 sum(decode(aud.incident_owner_id,to_dttm.old_incident_owner_id,
1803 0,
1804 decode(aud.incident_audit_id,to_dttm.incident_audit_id,
1805 nvl(aud.inc_responded_by_date,aud.incident_resolved_date),to_dttm.creation_date)
1806 - decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date)
1807 )) * 1440 not_waiting_on_me
1808 /* replaced with above selection
1809 sum(decode(aud.incident_owner_id,to_dttm.old_incident_owner_id,
1810 to_dttm.creation_date-from_dttm.creation_date,0))
1811 * 1440 waiting_on_me,
1812 sum(decode(aud.incident_owner_id,to_dttm.old_incident_owner_id,
1813 0,to_dttm.creation_date-from_dttm.creation_date))
1814 * 1440 not_waiting_on_me
1815 */
1816 from cs_incidents_audit_b curr_resp,
1817 cs_incidents_audit_b aud , --this is audit rec for prior response
1818 cs_incidents_audit_b to_dttm , -- to date time
1819 cs_incidents_audit_b from_dttm -- ,from date time
1820 /* the pair of from_dttm to to_dttm will give the durating in which an agent
1821 owned a serveice request. */
1822 where aud.incident_audit_id = getaudit(curr_resp.incident_id,p_from_date,l_dt)
1823 /* commented out the below sql and replace by function getaudit to fix
1824 * the performance issue 10630116
1825 ( select max(incident_audit_id)
1826 from cs_incidents_audit_b aud_in
1827 where aud_in.incident_id = curr_resp.incident_id
1828 and aud_in.creation_date < p_from_date
1829 and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>
1830 nvl(nvl(aud_in.inc_responded_by_date,aud_in.incident_resolved_date),l_dt)
1831 and (aud_in.inc_responded_by_date is not null or
1832 aud_in.incident_resolved_date is not null)
1833 -- above cond is needed to make sure that sr is
1834 -- responded. if this cond is not there then inc_responded_by_date
1835 -- as null to may get selected which is clearly not
1836 -- responded condition.
1837 ) */
1838 and curr_resp.incident_audit_id = getaudit1(curr_resp.incident_id,p_from_date,p_to_date,l_dt)
1839 /* commented out the below sql and replace by function getaudit1 to fix
1840 * the performance issue 10630116
1841 ( select max(incident_audit_id)
1842 from cs_incidents_audit_b aud_in
1843 where aud_in.incident_id = curr_resp.incident_id
1844 and aud_in.creation_date between p_from_date
1845 and p_to_date
1846 and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>
1847 nvl(nvl(aud_in.inc_responded_by_date,aud_in.incident_resolved_date),l_dt)
1848 and (aud_in.inc_responded_by_date is not null or
1849 aud_in.inc_responded_by_date is not null )
1850 -- above cond is needed to make sure that sr is
1851 -- responded. if this cond is not there then inc_responded_by_date
1852 -- as null to may get selected which is clearly not
1853 -- responded condition.
1854 ) */
1855 and curr_resp.incident_id = aud.incident_id
1856 -- This make sure that earlier resp to current response is selected
1857 -- above query will insure that selected response is the last response
1858 and to_dttm.incident_id = aud.incident_id
1859 and to_dttm.creation_date <= aud.creation_date
1860 and (to_dttm.incident_audit_id = aud.incident_audit_id or
1861 (nvl(to_dttm.old_incident_owner_id,-1) <>
1862 nvl(to_dttm.incident_owner_id,-1) and
1863 to_dttm.creation_date >= to_dttm.incident_date)
1864 )
1865 -- above will insure that to_dttm start from responded audit rec
1866 and from_dttm.incident_id = curr_resp.incident_id
1867 /*
1868 and (nvl(from_dttm.old_incident_owner_id,-1) <>
1869 nvl(from_dttm.incident_owner_id,-1) or
1870 nvl(from_dttm.old_incident_date,trunc(sysdate-300)) <>
1871 nvl(from_dttm.incident_date,trunc(sysdate-300))
1872 )
1873 */
1874 and from_dttm.incident_audit_id =
1875 (select max(incident_audit_id) from cs_incidents_audit_b x
1876 where x.incident_id = curr_resp.incident_id
1877 and ((nvl(x.old_incident_owner_id,-1) <>
1878 nvl(x.incident_owner_id,-1) and
1879 x.creation_date >= x.incident_date) or
1880 nvl(x.old_incident_date,trunc(sysdate-300)) <>
1881 nvl(x.incident_date,trunc(sysdate-300))
1882 )
1883 and x.creation_date < to_dttm.creation_date
1884 )
1885 group by aud.incident_owner_id,
1886 aud.group_id,
1887 aud.incident_severity_id,
1888 trunc(nvl(aud.inc_responded_by_date,aud.incident_resolved_date))
1889 ;
1890 l_owner_id cs_incidents_all_b.incident_owner_id % type;
1891 l_sev_id cs_incidents_all_b.incident_severity_id % type;
1892 l_group_id cs_incidents_all_b.owner_group_id % type;
1893 l_summ_dt cs_incidents_all_b.close_date % type;
1894 l_resp_time number;
1895 l_no_of_resp number;
1896 l_wait_on_me number;
1897 l_wait_on_others number;
1898 begin
1899 l_dt := trunc(sysdate) - 10000;
1900 open c_resp_times;
1901 loop
1902 fetch c_resp_times into l_owner_id, l_sev_id, l_group_id,
1903 l_summ_dt, l_resp_time,
1904 l_wait_on_me, l_wait_on_others;
1905 if (c_resp_times%notfound) then exit; end if;
1906 upload_resp_and_resl(p_incident_owner_id =>l_owner_id,
1907 p_owner_group_id => l_group_id,
1908 p_incident_severity_id => l_Sev_id,
1909 p_summary_date => l_summ_dt,
1910 p_owner_type => 'A',
1911 p_requests_responded => 0,
1912 p_requests_resolved => 0,
1913 p_resolve_time => 0,
1914 p_wait_on_agent_resl => 0,
1915 p_wait_on_int_org_resl => 0,
1916 p_wait_on_ext_org_resl => 0,
1917 p_wait_on_support_resl => 0,
1918 p_wait_on_customer_resl => 0,
1919 p_resp_sla_missed => 0,
1920 p_resl_sla_missed => 0,
1921 p_beginning_backlog => null,
1922 p_ending_backlog => null,
1923 p_sr_assigned => 0,
1924 p_sr_reassigned_to_others => 0,
1925 p_response_time => l_resp_time,
1926 p_wait_on_agent_resp => l_wait_on_me,
1927 p_wait_on_others_resp => l_wait_on_others);
1928 /*
1929 dbms_output.put_line('Time:Owner:'|| to_char(l_owner_id) || ' ,Resptm:'||to_char(l_resp_time) ||
1930 ', Date:' ||to_char(l_summ_dt,'dd-mon-yy'));
1931 */
1932 end loop;
1933 close c_resp_times;
1934 debug('After Sr Response time:'||to_char(sysdate,'hh24:mi:ss'));
1935 --
1936 -- Now reverse the response time if an incident was responded earlier
1937 open c_resp_times_rev;
1938 loop
1939 fetch c_resp_times_rev into l_owner_id, l_sev_id, l_group_id,
1940 l_summ_dt, l_resp_time,
1941 l_wait_on_me, l_wait_on_others;
1942 if (c_resp_times_rev%notfound) then exit; end if;
1943 upload_resp_and_resl(p_incident_owner_id => l_owner_id,
1944 p_owner_group_id => l_group_id,
1945 p_incident_severity_id => l_Sev_id,
1946 p_summary_date => l_summ_dt,
1947 p_owner_type => 'A',
1948 p_requests_responded => 0,
1949 p_requests_resolved => 0,
1950 p_resolve_time => 0,
1951 p_wait_on_agent_resl => 0,
1952 p_wait_on_int_org_resl => 0,
1953 p_wait_on_ext_org_resl => 0,
1954 p_wait_on_support_resl => 0,
1955 p_wait_on_customer_resl => 0,
1956 p_resp_sla_missed => 0,
1957 p_resl_sla_missed => 0,
1958 p_beginning_backlog => null,
1959 p_ending_backlog => null,
1960 p_sr_assigned => 0,
1961 p_sr_reassigned_to_others => 0,
1962 p_response_time => l_resp_time *-1,
1963 p_wait_on_agent_resp => l_wait_on_me*-1,
1964 p_wait_on_others_resp => l_wait_on_others*-1);
1965 /*
1966 dbms_output.put_line('REV-Time:Owner:'|| to_char(l_owner_id) || ' ,Resptm:'||to_char(nvl(l_resp_time,-9)) ||
1967 ', Date:' ||to_char(l_summ_dt,'dd-mon-yy'));
1968 */
1969 end loop;
1970 close c_resp_times_rev;
1971 debug('After Sr Response time Reversal:'||to_char(sysdate,'hh24:mi:ss'));
1972 --
1973 -- Get number of requests responded
1974 --
1975 open c_responses;
1976 loop
1977 fetch c_responses into l_owner_id, l_sev_id, l_group_id,
1978 l_summ_dt, l_no_of_resp, l_sla_missed;
1979 if (c_responses%notfound) then exit; end if;
1980 upload_resp_and_resl(p_incident_owner_id =>l_owner_id,
1981 p_owner_group_id => l_group_id,
1982 p_incident_severity_id => l_Sev_id,
1983 p_summary_date => l_summ_dt,
1984 p_owner_type => 'A',
1985 p_response_time => 0,
1986 p_wait_on_agent_resp => 0,
1987 p_wait_on_others_resp => 0,
1988 p_requests_resolved => 0,
1989 p_resolve_time => 0,
1990 p_wait_on_agent_resl => 0,
1991 p_wait_on_int_org_resl => 0,
1992 p_wait_on_ext_org_resl => 0,
1993 p_wait_on_support_resl => 0,
1994 p_wait_on_customer_resl => 0,
1995 p_resl_sla_missed => 0,
1996 p_beginning_backlog => null,
1997 p_ending_backlog => null,
1998 p_sr_assigned => 0,
1999 p_sr_reassigned_to_others => 0,
2000 p_requests_responded => l_no_of_resp,
2001 p_resp_sla_missed => l_sla_missed);
2002 /*
2003 dbms_output.put_line('Owner:'|| to_char(l_owner_id) || ' ,Resp:'||to_char(l_no_of_resp) ||
2004 ', Date:' ||to_char(l_summ_dt,'dd-mon-yy'));
2005 */
2006 end loop;
2007 close c_responses;
2008 debug('After Sr Response Count:'||to_char(sysdate,'hh24:mi:ss'));
2009 --
2010 -- Get number of requests responded to be reversed
2011 --
2012 open c_responses_rev;
2013 loop
2014 fetch c_responses_rev into l_owner_id, l_sev_id, l_group_id,
2015 l_summ_dt, l_no_of_resp,l_sla_missed;
2016 if (c_responses_rev%notfound) then exit; end if;
2017 upload_resp_and_resl(p_incident_owner_id =>l_owner_id,
2018 p_owner_group_id => l_group_id,
2019 p_incident_severity_id => l_Sev_id,
2020 p_summary_date => l_summ_dt,
2021 p_owner_type => 'A',
2022 p_response_time => 0,
2023 p_wait_on_agent_resp => 0,
2024 p_wait_on_others_resp => 0,
2025 p_requests_resolved => 0,
2026 p_resolve_time => 0,
2027 p_wait_on_agent_resl => 0,
2028 p_wait_on_int_org_resl => 0,
2029 p_wait_on_ext_org_resl => 0,
2030 p_wait_on_support_resl => 0,
2031 p_wait_on_customer_resl => 0,
2032 p_resl_sla_missed => 0,
2033 p_beginning_backlog => null,
2034 p_ending_backlog => null,
2035 p_sr_assigned => 0,
2036 p_sr_reassigned_to_others => 0,
2037 p_requests_responded => l_no_of_resp*-1,
2038 p_resp_sla_missed => l_sla_missed*-1);
2039 /*
2040 dbms_output.put_line('REV: Owner:'|| to_char(l_owner_id) || ' ,Resp:'||to_char(l_no_of_resp) ||
2041 ', Date:' ||to_char(l_summ_dt,'dd-mon-yy'));
2042 */
2043 end loop;
2044 close c_responses_rev;
2045 debug('After Sr Response Count Reversal:'||to_char(sysdate,'hh24:mi:ss'));
2046 --
2047 exception
2048 when others then
2049 fnd_file.put_line(fnd_file.log,'Error:'||sqlerrm);
2050 raise fnd_api.g_exc_unexpected_error;
2051 end;
2052 procedure update_group_data (p_from_date date,
2053 p_to_date date) is
2054 cursor c_group_data is
2055 select owner_group_id,
2056 summary_date,
2057 incident_severity_id,
2058 sum(total_response_time ) resp_time,
2059 sum(total_requests_responded ) req_resp,
2060 sum(total_wait_on_agent_resp ) wait_on_agent_resp,
2061 sum(total_wait_on_others_resp ) wait_on_others_resp,
2062 sum(total_requests_resolved ) req_resl,
2063 sum(total_resolve_time ) resl_time,
2064 sum(total_wait_on_agent_resl ) wait_on_agent_resl,
2065 sum(total_wait_on_int_org_resl ) wait_on_int_org,
2066 sum(total_wait_on_ext_org_resl ) wait_on_ext_org,
2067 sum(total_wait_on_support_resl ) wait_on_support,
2068 sum(total_wait_on_customer_resl ) wait_on_customer,
2069 sum(total_resp_sla_missed ) resp_sla,
2070 sum(total_resl_sla_missed ) resl_sla,
2071 sum(beginning_backlog ) begblog,
2072 sum(ending_backlog ) endblog
2073 from csy_response_resolutions
2074 where summary_date between p_from_date and p_to_date
2075 and owner_type = 'A'
2076 group by owner_group_id,
2077 summary_date,
2078 incident_severity_id;
2079 begin
2080 for l_rec in c_group_data loop
2081 update csy_response_resolutions
2082 set total_response_time = l_rec.resp_time,
2083 total_requests_responded = l_rec.req_resp,
2084 total_wait_on_agent_resp = l_rec.wait_on_agent_resp,
2085 total_wait_on_others_resp = l_rec.wait_on_others_resp,
2086 total_requests_resolved = l_rec.req_resl,
2087 total_resolve_time = l_rec.resl_time,
2088 total_wait_on_agent_resl = l_rec.wait_on_agent_resl,
2089 total_wait_on_int_org_resl = l_rec.wait_on_int_org,
2090 total_wait_on_ext_org_resl = l_rec.wait_on_ext_org,
2091 total_wait_on_support_resl = l_rec.wait_on_support,
2092 total_wait_on_customer_resl = l_rec.wait_on_customer,
2093 total_resp_sla_missed = l_rec.resp_sla,
2094 total_resl_sla_missed = l_rec.resl_sla,
2095 beginning_backlog = l_rec.begblog,
2096 ending_backlog = l_rec.endblog
2097 where summary_date = l_rec.summary_date
2098 and owner_group_id = l_rec.owner_group_id
2099 and owner_type = 'G'
2100 and incident_owner_id = -1
2101 and incident_severity_id = l_rec.incident_severity_id;
2102 if (sql%notfound) then
2103 insert into csy_response_resolutions
2104 (summary_date,
2105 incident_owner_id,
2106 incident_severity_id,
2107 owner_group_id,
2108 owner_type,
2109 TOTAL_RESPONSE_TIME ,
2110 TOTAL_REQUESTS_RESPONDED ,
2111 TOTAL_WAIT_ON_AGENT_RESP ,
2112 TOTAL_WAIT_ON_OTHERS_RESP ,
2113 TOTAL_REQUESTS_RESOLVED ,
2114 TOTAL_RESOLVE_TIME ,
2115 TOTAL_WAIT_ON_AGENT_RESL ,
2116 TOTAL_WAIT_ON_INT_ORG_RESL ,
2117 TOTAL_WAIT_ON_EXT_ORG_RESL ,
2118 TOTAL_WAIT_ON_SUPPORT_RESL ,
2119 TOTAL_WAIT_ON_CUSTOMER_RESL ,
2120 TOTAL_RESP_SLA_MISSED ,
2121 TOTAL_RESL_SLA_MISSED ,
2122 BEGINNING_BACKLOG ,
2123 ENDING_BACKLOG ,
2124 last_update_date ,
2125 last_updated_by ,
2126 creation_date ,
2127 created_by ,
2128 last_update_login ,
2129 program_id ,
2130 program_login_id ,
2131 program_application_id ,
2132 request_id )
2133 values (l_rec.summary_date,
2134 -1,
2135 l_rec.incident_severity_id,
2136 l_rec.owner_group_id,
2137 'G',
2138 l_rec.resp_time,
2139 l_rec.req_resp,
2140 l_rec.wait_on_agent_resp,
2141 l_rec.wait_on_others_resp,
2142 l_rec.req_resl,
2143 l_rec.resl_time,
2144 l_rec.wait_on_agent_resl,
2145 l_rec.wait_on_int_org,
2146 l_rec.wait_on_ext_org,
2147 l_rec.wait_on_support,
2148 l_rec.wait_on_customer,
2149 l_rec.resp_sla,
2150 l_rec.resl_sla,
2151 l_rec.endblog,
2152 l_rec.begblog,
2153 sysdate ,
2154 g_user_id ,
2155 sysdate ,
2156 g_user_id ,
2157 g_login_user_id ,
2158 g_conc_program_id ,
2159 g_conc_login_id ,
2160 g_conc_appl_id ,
2161 g_conc_request_id );
2162 end if;
2163 end loop;
2164 end update_group_data;
2165 -----------------------------------
2166 procedure incremental_data_load(p_errbuf out nocopy varchar2,
2167 p_retcode out nocopy number) is
2168 l_from_date date;
2169 l_to_date date;
2170 l_ret_val boolean;
2171 begin
2172 g_user_id := fnd_global.user_id;
2173 g_login_user_id := fnd_global.login_id;
2174 g_conc_program_id := fnd_global.conc_program_id;
2175 g_conc_login_id := fnd_global.conc_login_id;
2176 g_conc_appl_id := fnd_global.prog_appl_id;
2177 g_conc_request_id := fnd_global.conc_request_id;
2178 select to_date(fnd_profile.value('CS_CSY_LAST_PROGRAM_RUN_DATE'),
2179 'YYYYMMDD HH24:MI:SS')
2180 into l_from_date
2181 from dual;
2182 if (l_from_date is null) then
2183 fnd_file.put_line(fnd_file.log,'Please Run: KPI summary: Initial Data Laod Set');
2184 p_errbuf := '"KPI Summary: Initial Data load Set" has never been run.';
2185 p_errbuf := p_errbuf || ' So Please Run:"KPI summary: Initial Data Load Set" before running Incremental Data Load';
2186 p_retcode := 2;
2187 return;
2188 end if;
2189 debug('From Date before Addition of 1 second:'||
2190 to_char(l_from_date,'dd-mon-yyyy hh24:mi:ss'));
2191 l_from_date := l_from_date + 1/86400;
2192 debug('From Date after Addition of 1 second:'||
2193 to_char(l_from_date,'dd-mon-yyyy hh24:mi:ss'));
2194 l_to_date := sysdate;
2195 debug('Before Sr assignments:'||to_char(sysdate,'hh24:mi:ss'));
2196 get_sr_agent_assignments (l_from_date, l_to_date);
2197 debug('After Sr assignments:'||to_char(sysdate,'hh24:mi:ss'));
2198 get_sr_backlog (l_from_date, l_to_date);
2199 debug('After Sr backlog :'||to_char(sysdate,'hh24:mi:ss'));
2200 get_sr_resolutions (l_from_date, l_to_date);
2201 debug('After Sr resolutions:'||to_char(sysdate,'hh24:mi:ss'));
2202 get_response_timings (l_from_date, l_to_date);
2203 debug('After Sr Resp Timing:'||to_char(sysdate,'hh24:mi:ss'));
2204 get_resolution_timings(l_from_date, l_to_date);
2205 debug('After Sr Resl timing:'||to_char(sysdate,'hh24:mi:ss'));
2206 /* we need to truncate l_from_date. reason l_from_date will be like 10-oct-2003 10:00:00
2207 so it will not sum the data for 10-oct-2003
2208 */
2209 --update_group_data (trunc(l_from_date), l_to_date);
2210 --change above line with the line below. Suppose a service request is reopened after 10
2211 --days and then resolved. Earlier resolution data will be corrected for the agent but
2212 -- for group it will not get corrected due to from_date value. So changing from date to
2213 -- last 365 days since kpi displays data only for up to last 365 days
2214 update_group_data (l_to_date-367, l_to_date);
2215 debug('After Group Data :'||to_char(sysdate,'hh24:mi:ss'));
2216 l_ret_val := fnd_profile.save('CS_CSY_LAST_PROGRAM_RUN_DATE',
2217 to_char(l_to_date,'YYYYMMDD hh24:mi:ss'),
2218 'SITE');
2219 if (l_ret_val) then
2220 --refresh_mvs(p_errbuf,p_retcode);
2221 commit;
2222 else
2223 rollback;
2224 --give error message;
2225 end if;
2226
2227 end;
2228 ----------------------------
2229 procedure initial_data_load(p_errbuf out nocopy varchar2,
2230 p_retcode out nocopy number) is
2231 l_min_date date;
2232 l_ret_val boolean;
2233 begin
2234 /**
2235 select min(creation_date) - 1
2236 into l_min_date
2237 from cs_incidents_audit_b;
2238 */
2239 delete from csy_response_resolutions;
2240 delete from csy_resolution_qlty;
2241 commit;
2242 l_min_date := trunc(sysdate) - 370;
2243 l_ret_val := fnd_profile.save('CS_CSY_LAST_PROGRAM_RUN_DATE',
2244 to_char(l_min_date,'YYYYMMDD hh24:mi:ss'),
2245 'SITE');
2246 if (l_ret_val) then
2247 commit;
2248 incremental_data_load(p_errbuf, p_retcode );
2249 else
2250 rollback;
2251 --give error message;
2252 end if;
2253 end;
2254 ---------------------------------------------------
2255 procedure refresh_mvs(p_errbuf out nocopy varchar2,
2256 p_retcode out nocopy number) is
2257 begin
2258 -- commented out for bug 13484635
2259 -- dbms_mview.refresh('CSY_AGENT_RESPN_RESOL_MV,CSY_GROUP_RESPN_RESOL_MV','cc');
2260 -- dbms_mview.refresh('CSY_RESOLUTION_QUALITY_MV','c')
2261 delete from CSY_AGENT_RESPN_RESOL_B;
2262 delete from CSY_GROUP_RESPN_RESOL_B;
2263 delete from CSY_RESOLUTION_QUALITY_B;
2264 insert into CSY_AGENT_RESPN_RESOL_B
2265 (PERIOD_TYPE,
2266 PERIOD_NAME,
2267 INCIDENT_SEVERITY_ID,
2268 INCIDENT_OWNER_ID,
2269 TOTAL_RESPONSE_TIME,
2270 TOTAL_SR_RESPONDED,
2271 TOTAL_RESP_SLA_MISSED,
2272 TOTAL_AGENT_WAIT,
2273 TOTAL_OTHERS_WAIT,
2274 TOTAL_RESOLVE_TIME,
2275 TOTAL_SR_RESOLVED,
2276 TOTAL_WAIT_AGENT,
2277 TOTAL_WAIT_SUPPORT,
2278 TOTAL_WAIT_INTERNAL,
2279 TOTAL_WAIT_EXTERNAL,
2280 TOTAL_WAIT_CUSTOMER,
2281 TOTAL_RESL_SLA_MISSED,
2282 BEGINNING_BACKLOG,
2283 ENDING_BACKLOG,
2284 TOTAL_SR_ASSIGNED,
2285 TOTAL_SR_REASSIGNED_TO_OTHERS)
2286 SELECT period_type,
2287 period_name,
2288 incident_severity_id,
2289 incident_owner_id,
2290 SUM(total_response_time) total_response_time,
2291 SUM(total_requests_responded) total_sr_responded,
2292 SUM(total_resp_sla_missed) total_resp_sla_missed,
2293 SUM(total_wait_on_agent_resp) total_agent_wait,
2294 SUM(total_wait_on_others_resp) total_others_wait,
2295 SUM(total_resolve_time) total_resolve_time,
2296 SUM(total_requests_resolved) total_sr_resolved,
2297 SUM(total_wait_on_agent_resl) total_wait_agent,
2298 SUM(total_wait_on_support_resl) total_wait_support,
2299 SUM(total_wait_on_int_org_resl) total_wait_internal,
2300 SUM(total_wait_on_ext_org_resl) total_wait_external,
2301 SUM(total_wait_on_customer_resl) total_wait_customer,
2302 SUM(total_resl_sla_missed) total_resl_sla_missed,
2303 SUM(decode(resp.summary_date, per.start_date, beginning_backlog, 0))
2304 beginning_backlog,
2305 SUM(decode(resp.summary_date, per.end_date, ending_backlog, 0))
2306 ending_backlog,
2307 SUM(total_sr_assigned) total_sr_assigned,
2308 SUM(total_sr_reassigned_to_others) total_sr_reassigned_to_others
2309 FROM csy_response_resolutions resp,
2310 csy_periods_v per
2311 WHERE resp.summary_date BETWEEN per.start_date
2312 AND per.end_date
2313 AND owner_type = 'A'
2314 GROUP BY period_type,
2315 period_name,
2316 incident_severity_id,
2317 incident_owner_id;
2318
2319 insert into CSY_GROUP_RESPN_RESOL_B
2320 (PERIOD_TYPE,
2321 PERIOD_NAME,
2322 INCIDENT_SEVERITY_ID,
2323 OWNER_GROUP_ID,
2324 TOTAL_RESPONSE_TIME,
2325 TOTAL_SR_RESPONDED,
2326 TOTAL_RESP_SLA_MISSED,
2327 TOTAL_AGENT_WAIT,
2328 TOTAL_OTHERS_WAIT,
2329 TOTAL_RESOLVE_TIME,
2330 TOTAL_SR_RESOLVED,
2331 TOTAL_WAIT_AGENT,
2332 TOTAL_WAIT_SUPPORT,
2333 TOTAL_WAIT_INTERNAL,
2334 TOTAL_WAIT_EXTERNAL,
2335 TOTAL_WAIT_CUSTOMER,
2336 TOTAL_RESL_SLA_MISSED,
2337 BEGINNING_BACKLOG,
2338 ENDING_BACKLOG,
2339 TOTAL_SR_ASSIGNED,
2340 TOTAL_SR_REASSIGNED_TO_OTHERS)
2341 SELECT period_type,
2342 period_name,
2343 incident_severity_id,
2344 owner_group_id,
2345 SUM(total_response_time) total_response_time,
2346 SUM(total_requests_responded) total_sr_responded,
2347 SUM(total_resp_sla_missed) total_resp_sla_missed,
2348 SUM(total_wait_on_agent_resp) total_agent_wait,
2349 SUM(total_wait_on_others_resp) total_others_wait,
2350 SUM(total_resolve_time) total_resolve_time,
2351 SUM(total_requests_resolved) total_sr_resolved,
2352 SUM(total_wait_on_agent_resl) total_wait_agent,
2353 SUM(total_wait_on_support_resl) total_wait_support,
2354 SUM(total_wait_on_int_org_resl) total_wait_internal,
2355 SUM(total_wait_on_ext_org_resl) total_wait_external,
2356 SUM(total_wait_on_customer_resl) total_wait_customer,
2357 SUM(total_resl_sla_missed) total_resl_sla_missed,
2358 SUM(decode(resp.summary_date, per.start_date, beginning_backlog, 0))
2359 beginning_backlog,
2360 SUM(decode(resp.summary_date, per.end_date, ending_backlog, 0))
2361 ending_backlog,
2362 SUM(total_sr_assigned) total_sr_assigned,
2363 SUM(total_sr_reassigned_to_others) total_sr_reassigned_to_others
2364 FROM csy_response_resolutions resp,
2365 csy_periods_v per
2366 WHERE resp.summary_date BETWEEN per.start_date
2367 AND per.end_date
2368 AND owner_type = 'G'
2369 GROUP BY period_type,
2370 period_name,
2371 incident_severity_id,
2372 owner_group_id;
2373
2374 insert into CSY_RESOLUTION_QUALITY_B
2375 (PERIOD_TYPE,
2376 PERIOD_NAME,
2377 OWNER_TYPE,
2378 INCIDENT_OWNER_ID,
2379 INCIDENT_SEVERITY_ID,
2380 TOTAL_SR_RESOLVED_1ST_TIME,
2381 TOTAL_SR_REOPENED,
2382 TOT_SR_REOPENED_ONCE_OR_MORE)
2383 SELECT period_type,
2384 period_name,
2385 owner_type,
2386 incident_owner_id,
2387 incident_severity_id,
2388 SUM(total_sr_resolved_1st_time) total_sr_resolved_1st_time,
2389 SUM(total_sr_reopened) total_sr_reopened,
2390 SUM(tot_sr_reopened_once_or_more) tot_sr_reopened_once_or_more
2391 FROM csy_resolution_qlty resl,
2392 csy_periods_v per
2393 WHERE resl.summary_date BETWEEN per.start_date
2394 AND per.end_date
2395 GROUP BY period_type,
2396 period_name,
2397 incident_severity_id,
2398 owner_type,
2399 incident_owner_id ;
2400 commit;
2401
2402
2403
2404 end refresh_mvs;
2405 ----------------------------
2406 function sev_names (p_imp_lvl number) return varchar2 is
2407 l_imp_lvl_rnk number;
2408 cursor c_severities is
2409 select name
2410 from cs_incident_severities_vl
2411 where importance_level = p_imp_lvl
2412 and trunc(sysdate) between nvl(start_date_active,sysdate-1) and nvl(end_date_active,sysdate+1)
2413 and incident_subtype = 'INC';
2414 --
2415 l_id cs_incident_severities_vl.incident_Severity_id % type;
2416 l_name cs_incident_severities_vl.name % type;
2417 l_imp_lvl cs_incident_severities_vl.importance_level % type;
2418 l_rnk number;
2419 l_all_sev varchar2(2000);
2420 l_count number;
2421 l_min_lvl number;
2422 l_loop_counter number;
2423 --
2424 begin
2425 select min(importance_level) into l_min_lvl
2426 from cs_incident_severities_b
2427 where trunc(sysdate) between nvl(start_date_active,sysdate-1) and nvl(end_date_active,sysdate+1)
2428 and incident_subtype = 'INC';
2429 l_count := 0;
2430 open c_severities;
2431 loop
2432 fetch c_severities into l_name;
2433 exit when c_severities%notfound;
2434 if (l_count = 0) then
2435 l_all_sev := l_name;
2436 else
2437 l_all_sev := l_all_sev || ', ' || l_name;
2438 end if;
2439 l_count := l_count + 1;
2440 end loop;
2441 close c_severities;
2442 if (l_min_lvl <> p_imp_lvl) then
2443 l_all_sev := l_all_sev || ' +';
2444 else
2445 l_all_sev := l_all_sev || ';';
2446 end if;
2447
2448 return l_all_sev;
2449 end sev_names;
2450 end ;
[Home] [Help]