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