[Home] [Help]
PACKAGE BODY: APPS.BIX_ALERT
Source
1 package body BIX_ALERT AS
2 /* $Header: BIXPALRB.pls 115.9 2003/01/10 00:31:22 achanda ship $ */
3
4 FUNCTION Calculate_Actual
5 ( p_Organization_ID NUMBER
6 , p_period_set_Name VARCHAR2
7 , p_time_period VARCHAR2
8 )
9 RETURN NUMBER
10
11 IS
12 l_actual_value NUMBER;
13
14 BEGIN
15
16 l_actual_value := 0;
17 RETURN l_actual_value;
18
19 EXCEPTION
20 WHEN OTHERS THEN
21 l_actual_value := -1;
22
23 END Calculate_Actual;
24
25
26 FUNCTION Get_Target
27 ( p_computed_target_short_name VARCHAR2
28 )
29 RETURN NUMBER
30 IS
31 l_target NUMBER;
32 BEGIN
33 NULL;
34
35 EXCEPTION
36 WHEN OTHERS THEN
37 l_target := -99999;
38
39 END Get_Target;
40
41 PROCEDURE Start_Corrective_Action
42 ( p_wf_process VARCHAR2
43 , p_wf_item_type VARCHAR2
44 , p_message VARCHAR2
45 , p_notify_resp VARCHAR2
46 , p_report_name1 VARCHAR2
47 , p_report_param1 VARCHAR2
48 , p_responsibility_id NUMBER
49 , p_msg_subject VARCHAR2
50 )
51 IS
52 l_message VARCHAR2(250);
53 l_subject VARCHAR2(250) ;
54 l_report_name1 VARCHAR2(250) DEFAULT NULL;
55 l_report_param1 VARCHAR2(250) DEFAULT NULL;
56 l_ret_status VARCHAR2(1);
57 BEGIN
58
59 -- DBMS_OUTPUT.PUT_LINE('message' || p_report_name1);
60 l_report_name1 := p_report_name1;
61 l_subject := p_msg_subject;
62 BIS_UTIL.STRT_WF_PROCESS
63 ( p_exception_message => p_message
64 , p_msg_subject => l_subject
65 , p_exception_date => SYSDATE
66 , p_wf_process => p_wf_process
67 , p_item_type => p_wf_item_type
68 , p_report_name1 => l_report_name1
69 , p_report_name2 => l_report_name1
70 , p_report_name3 => l_report_name1
71 , p_report_name4 => l_report_name1
72 , p_report_param1 => p_report_param1
73 , p_notify_resp_name => p_notify_resp
74 , p_report_resp1_id => p_responsibility_id
75 , x_return_status =>l_ret_status );
76 --DBMS_OUTPUT.PUT_LINE('util proc tostart workflow called');
77 IF l_ret_status = FND_API.G_RET_STS_SUCCESS then
78 /*
79 DBMS_OUTPUT.PUT_LINE('Corrective Action Started. '||
80 p_notify_resp||' is notified.');
81 */
82 null;
83 ELSE
84 -- DBMS_OUTPUT.PUT_LINE('Failed to Start Corrective Action: '||l_ret_status);
85 NULL;
86 END IF;
87
88 EXCEPTION
89 WHEN OTHERS THEN
90 -- DBMS_OUTPUT.PUT_LINE('Exception when Starting Corrective Action.');
91 NULL;
92
93 END Start_Corrective_Action;
94 /* Alert for Abandon Calls */
95 PROCEDURE BIX_ABANDON_ALERT
96 ( p_target_Level_Short_Name VARCHAR2
97 )
98 IS
99 l_period_set_name VARCHAR2(15);
100 l_period_name VARCHAR2(15);
101 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
102 l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
103 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
104 l_actual NUMBER;
105 l_target NUMBER;
106 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
107 l_Actual_Tbl BIS_ACTUAL_PUB.Actual_Tbl_Type;
108 l_workflow_item_type VARCHAR2(8);
109 l_workflow_process VARCHAR2(30);
110 l_notify_resp VARCHAR2(100);
111 l_responsibility_ID NUMBER;
112 l_message VARCHAR2(250);
113 l_report VARCHAR2(250);
114 l_organization_ID NUMBER;
115 l_organization_tbl BIS_POSTACTUAL.t_orgTable;
116 l_msg_data VARCHAR2(250);
117 l_msg_count NUMBER;
118 l_msg_subject VARCHAR2(300);
119 l_return_status VARCHAR2(1);
120 i NUMBER := 0;
121 l_center VARCHAR2(200);
122 CURSOR cr_actual IS
123 Select round(sum(abandoned_count)/sum(number_of_interactions)*100, 2) abandoned_level,
124 to_char(hour,'MON-YYYY') month,
125 interaction_center_id center_id
126 from bix_sum_grp_cls
127 group by to_char(hour,'MON-YYYY'), interaction_center_id;
128 CURSOR cr_center(p_center_id NUMBER) IS
129 SELECT call_center_name center_name
130 FROM bix_call_center_v
131 WHERE call_center_id = p_center_id;
132
133 CURSOR cr_target(p_organization_id VARCHAR2,
134 p_center_id VARCHAR2, p_time VARCHAR2)
135 IS
136 SELECT tv.target_level_short_name
137 , tv.target_level_name
138 , tv.target_level_id
139 , tv.plan_name
140 , tv.org_level_value_id
141 , tv.time_level_value_id
142 , tv.target
143 -- , tv.computed_target_short_name
144 , tv.range1_low
145 , tv.range1_high
146 , tv.range2_low
147 , tv.range2_high
148 , tv.range3_low
149 , tv.range3_high
150 , tv.notify_resp1_id
151 , tv.notify_resp1_short_name
152 , tv.notify_resp2_id
153 , tv.notify_resp2_short_name
154 , tv.notify_resp3_id
155 , tv.notify_resp3_short_name
156 , tv.dim1_level_value_id
157 FROM BISFV_TARGETS tv
158 WHERE tv.target_level_short_name = p_target_level_short_name
159 AND tv.time_level_value_id like '%' || p_time || '%'
160 AND tv.org_level_value_id = p_organization_id
161 AND tv.dim1_level_value_name = p_center_id;
162
163 BEGIN
164
165 --dbms_output.put_line('In Abandon Call Rate Alert ');
166 l_target_level_rec.target_Level_Short_Name
167 := p_target_Level_Short_Name;
168 -- l_period_set_name := p_time_period;
169 -- l_period_name := p_time_period;
170 l_report := 'BIXACAB0';
171 FND_MESSAGE.SET_NAME('BIX', 'BIX_ABANDON_CALL_RATE');
172 l_msg_subject := FND_MESSAGE.GET;
173 IF l_msg_subject is NULL then
174 l_msg_subject := 'Abandon Call Rate PMF Notification';
175 END IF;
176 l_organization_id := -1;
177 -- Get the workflow process
178 SELECT workflow_item_type, workflow_process_short_name
179 INTO l_workflow_item_type, l_workflow_process
180 FROM bisbv_target_levels
181 WHERE target_level_short_name = p_target_level_short_name;
182
183 -- Get the KPIs users have selected to monitor on their homepage
184 BIS_ACTUAL_PUB.Retrieve_User_Selections
185 ( p_api_version => 1.0
186 ,p_Target_Level_Rec => l_Target_Level_Rec
187 ,x_Indicator_Region_Tbl => l_user_selection_Tbl
188 ,x_return_status => l_return_status
189 ,x_msg_count => l_msg_count
190 ,x_msg_data => l_msg_data
191 ,x_error_Tbl => l_error_tbl
192 );
193
194 -- Calculate Actual for all Months for All Call Centers
195 FOR cr_a in cr_actual LOOP
196
197 l_actual := cr_a.abandoned_level;
198 for cr_c in cr_center(cr_a.center_id) LOOP
199 l_center := cr_c.center_name;
200 end loop;
201 -- Post actual value for only those KPIs users have selected.
202 -- FOR i IN 1..l_user_selection_Tbl.COUNT LOOP
203 -- IF l_user_selection_tbl(i).Org_Level_Value_ID = l_organization_id THEN
204 /*
205 l_Actual_Tbl(i).target_Level_Short_Name
206 := p_target_level_short_name;
207 l_Actual_Tbl(i).Org_Level_value_ID := l_organization_id;
208 l_Actual_Tbl(i).time_Level_value_ID := cr_a.month;
209 l_Actual_Tbl(i).Target_Level_ID := cr_a.month;
210 l_Actual_Tbl(i).Actual := l_actual;
211
212 BIS_ACTUAL_PUB.POST_ACTUAL
213 ( p_api_version => 1.0
214 ,p_Actual_Rec => l_actual_Tbl(i)
215 ,x_return_status => l_return_status
216 ,x_msg_count => l_msg_count
217 ,x_msg_data => l_msg_data
218 ,x_error_tbl => l_error_tbl
219 );
220 */
221 -- END IF;
222 -- END LOOP;
223
224 -- Check for exceptions and start corrective action
225
226 FOR cr_t IN cr_target(l_organization_id, to_char(cr_a.center_id), cr_a.month
227 ) LOOP
228
229 IF cr_t.target IS NULL THEN
230 -- l_target := Get_Target(cr.computed_target_short_name);
231 NULL;
232 ELSE
233 l_target := cr_t.target;
234 END IF;
235 l_Actual_Tbl(1).target_Level_Short_Name
236 := p_target_level_short_name;
237 l_Actual_Tbl(1).Org_Level_value_ID := l_organization_id;
238 l_Actual_Tbl(1).time_Level_value_ID := cr_t.time_level_value_id;
239 l_Actual_Tbl(1).target_Level_ID := cr_t.target_level_id;
240 l_Actual_Tbl(1).Actual := l_actual;
241 l_Actual_Tbl(1).dim1_Level_value_ID := cr_t.dim1_level_value_id;
242 L_Actual_Tbl(1).target_level_name := cr_t.target_level_name;
243 BIS_ACTUAL_PUB.POST_ACTUAL
244 ( p_api_version => 1.0
245 ,p_Actual_Rec => l_actual_Tbl(1)
246 ,x_return_status => l_return_status
247 ,x_msg_count => l_msg_count
248 ,x_msg_data => l_msg_data
249 ,x_error_tbl => l_error_tbl
250 );
251
252
253 -- We're not on target....
254 IF l_actual <> l_target THEN
255 -- Check if actual is within the first range
256 IF l_actual NOT BETWEEN
257 cr_t.range2_low AND cr_t.range2_High
258 AND l_actual BETWEEN
259 cr_t.range1_low AND cr_t.range1_High
260 THEN
261 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE1');
262 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
263 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
264 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
265 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
266 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
267 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
268 l_message := FND_MESSAGE.GET;
269 l_notify_resp := cr_t.Notify_Resp1_short_name;
270 l_responsibility_id := cr_t.Notify_Resp1_ID;
271 --dbms_output.put_line('In First Range');
272 -- Check if actual is within the second range
273 ELSIF l_actual NOT BETWEEN
274 cr_t.range1_low AND cr_t.range1_High
275 AND l_actual BETWEEN
276 cr_t.range2_low AND cr_t.range2_High
277 THEN
278 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE2');
279 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
280 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
281 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
282 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
283 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
284 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
285 l_message := FND_MESSAGE.GET;
286 l_notify_resp := cr_t.Notify_Resp1_short_name;
287 l_responsibility_id := cr_t.Notify_Resp1_ID;
288 --dbms_output.put_line('In Second Range');
289 -- Check if actual is within the third range
290 ELSIF l_actual NOT BETWEEN
291
292 cr_t.range2_low AND cr_t.range2_High
293 AND l_actual BETWEEN
294 cr_t.range3_low AND cr_t.range3_High
295 THEN
296 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE3');
297 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
298 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
299 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
300 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
301 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
302 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
303 l_message := FND_MESSAGE.GET;
304 l_notify_resp := cr_t.Notify_Resp2_short_name;
305 l_responsibility_id := cr_t.Notify_Resp2_ID;
306 --dbms_output.put_line('In Third Range');
307 -- Check if actual is outside the third range
308 ELSIF l_actual NOT BETWEEN
309 cr_t.range3_low AND cr_t.range3_High
310 AND l_actual > l_target
311 THEN
312 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_OFFRANGE');
313 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
314 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
315 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
316 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
317 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
318 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
319 l_message := FND_MESSAGE.GET;
320 l_notify_resp := cr_t.Notify_Resp3_short_name;
321 l_responsibility_id := cr_t.Notify_Resp3_ID;
322 --dbms_output.put_line('Out of Third Range');
323 ELSIF l_actual < l_target
324 THEN
325 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_EXRANGE');
326 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
327 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
328 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
329 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
330 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
331 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
332 l_message := FND_MESSAGE.GET;
333 l_notify_resp := cr_t.Notify_Resp3_short_name;
334 l_responsibility_id := cr_t.Notify_Resp3_ID;
335 --dbms_output.put_line('less than First Range');
336 END IF;
337
338 -- We're on target!!
339 ELSE
340 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_INRANGE');
341 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
342 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
343 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
344 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
345 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
346 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
347 l_message := FND_MESSAGE.GET;
348 l_notify_resp := cr_t.Notify_Resp1_short_name;
349 l_responsibility_id := cr_t.Notify_Resp1_ID;
350 END IF;
351 /*
352 dbms_output.put_line('Calling Corrective Action' || cr_a.month ||
353 l_responsibility_id || l_notify_resp || l_workflow_process ||
354 l_workflow_item_type || l_message );
355 */
356 Start_Corrective_Action
357 ( p_wf_process => l_workflow_process
358 , p_wf_item_type => l_workflow_item_type
359 , p_message => l_message
360 , p_notify_resp => l_notify_resp
361 , p_report_name1 => l_report
362 , p_report_param1 => NULL
363 , p_responsibility_id => l_responsibility_id
364 , p_msg_subject => l_msg_subject
365 );
366 END LOOP; -- ends loop to check targets
367 END LOOP; -- ends loop to calculate actual
368
369 EXCEPTION
370 WHEN OTHERS THEN
371 l_message := 'Exception in Abandon Alert Procedure.';
372 --dbms_output.put_line(l_message);
373
374 END BIX_ABANDON_ALERT;
375
376 /* Alert for Service Level PMF */
377 PROCEDURE BIX_SERLVL_ALERT
378 ( p_target_Level_Short_Name VARCHAR2
379 )
380 IS
381 l_period_set_name VARCHAR2(15);
382 l_period_name VARCHAR2(15);
383 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
384 l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
385 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
386 l_actual NUMBER;
387 l_target NUMBER;
388 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
389 l_Actual_Tbl BIS_ACTUAL_PUB.Actual_Tbl_Type;
390 l_workflow_item_type VARCHAR2(8);
391 l_workflow_process VARCHAR2(30);
392 l_notify_resp VARCHAR2(100);
393 l_responsibility_ID NUMBER;
394 l_message VARCHAR2(250);
395 l_report VARCHAR2(250);
396 l_organization_ID NUMBER;
397 l_organization_tbl BIS_POSTACTUAL.t_orgTable;
398 l_msg_data VARCHAR2(250);
399 l_return_status VARCHAR2(1);
400 l_msg_count NUMBER;
401 l_msg_subject VARCHAR2(300);
402 i NUMBER := 0;
403 l_center VARCHAR2(32);
404 CURSOR cr_actual IS
405 Select round(sum(interactions_answered_live)/sum(number_of_interactions)*100, 2) service_level,
406 to_char(hour,'MON-YYYY') month,
407 interaction_center_id center_id
408 from bix_sum_grp_cls
409 group by to_char(hour,'MON-YYYY'), interaction_center_id;
410
411 CURSOR cr_center(p_center_id NUMBER) IS
412 SELECT call_center_name center_name
413 FROM bix_call_center_v
414 WHERE call_center_id = p_center_id;
415
416 CURSOR cr_target(p_organization_id VARCHAR2, p_center_id VARCHAR2,p_time VARCHAR2) IS
417 SELECT tv.target_level_short_name
418 , tv.target_level_name
419 , tv.target_level_id
420 , tv.plan_name
421 , tv.org_level_value_id
422 , tv.time_level_value_id
423 , tv.target
424 -- , tv.computed_target_short_name
425 , tv.range1_low
426 , tv.range1_high
427 , tv.range2_low
428 , tv.range2_high
429 , tv.range3_low
430 , tv.range3_high
431 , tv.notify_resp1_id
432 , tv.notify_resp1_short_name
433 , tv.notify_resp2_id
434 , tv.notify_resp2_short_name
435 , tv.notify_resp3_id
436 , tv.notify_resp3_short_name
437 , tv.dim1_level_value_id
438 FROM BISFV_TARGETS tv
439 WHERE tv.target_level_short_name = p_target_level_short_name
440 AND tv.time_level_value_id like '%' || p_time || '%'
441 AND tv.org_level_value_id = p_organization_id
442 AND tv.dim1_level_value_name = p_center_id;
443
444 BEGIN
445
446 l_target_level_rec.target_Level_Short_Name
447 := p_target_Level_Short_Name;
448 -- l_period_set_name := p_time_period;
449 -- l_period_name := p_time_period;
450 l_report := 'BIXSLVL0';
451 -- Get translated subject for notification
452 FND_MESSAGE.SET_NAME('BIX', 'BIX_SERVICE_LEVEL');
453 l_msg_subject := FND_MESSAGE.GET;
454 IF l_msg_subject is NULL then
455 l_msg_subject := 'Service Level PMF Notification';
456 END IF;
457 l_organization_id := -1;
458 -- Get the workflow process
459 SELECT workflow_item_type, workflow_process_short_name
460 INTO l_workflow_item_type, l_workflow_process
461 FROM bisbv_target_levels
462 WHERE target_level_short_name = p_target_level_short_name;
463
464 -- Get the KPIs users have selected to monitor on their homepage
465 BIS_ACTUAL_PUB.Retrieve_User_Selections
466 ( p_api_version => 1.0
467 ,p_Target_Level_Rec => l_Target_Level_Rec
468 ,x_Indicator_Region_Tbl => l_user_selection_Tbl
469 ,x_return_status => l_return_status
470 ,x_msg_count => l_msg_count
471 ,x_msg_data => l_msg_data
472 ,x_error_Tbl => l_error_tbl
473 );
474 -- Calculate Actual for all Months for All Call Centers
475 FOR cr_a in cr_actual LOOP
476
477 l_actual := cr_a.service_level;
478 for cr_c in cr_center(cr_a.center_id) LOOP
479 l_center := cr_c.center_name;
480 end loop;
481 -- Post actual value for only those KPIs users have selected.
482 -- FOR i IN 1..l_user_selection_Tbl.COUNT LOOP
483 -- IF l_user_selection_tbl(i).organization_id = l_organization_id THEN
484 -- END IF;
485 -- END LOOP;
486
487 -- Check for exceptions and start corrective action
488 FOR cr_t IN cr_target(l_organization_id,to_char(cr_a.center_id), cr_a.month) LOOP
489
490 IF cr_t.target IS NULL THEN
491 -- l_target := Get_Target(cr.computed_target_short_name);
492 NULL;
493 ELSE
494 l_target := cr_t.target;
495 END IF;
496 l_Actual_Tbl(1).target_Level_Short_Name
497 := p_target_level_short_name;
498 l_Actual_Tbl(1).Org_Level_value_ID := l_organization_id;
499 l_Actual_Tbl(1).time_Level_value_ID := cr_t.time_level_value_id;
500 l_Actual_Tbl(1).target_Level_ID := cr_t.target_level_id;
501 l_Actual_Tbl(1).Actual := l_actual;
502 l_Actual_Tbl(1).dim1_Level_value_ID := cr_t.dim1_level_value_id;
503 l_Actual_Tbl(1).target_level_name := cr_t.target_level_name;
504 BIS_ACTUAL_PUB.POST_ACTUAL
505 ( p_api_version => 1.0
506 ,p_Actual_Rec => l_actual_Tbl(1)
507 ,x_return_status => l_return_status
508 ,x_msg_count => l_msg_count
509 ,x_msg_data => l_msg_data
510 ,x_error_tbl => l_error_tbl
511 );
512 -- We're not on target....
513 IF l_actual <> l_target THEN
514 -- Check if actual is within the first range
515 IF l_actual NOT BETWEEN
516 cr_t.range2_low AND cr_t.range2_High
517 AND l_actual BETWEEN
518 cr_t.range1_low AND cr_t.range1_High
519 THEN
520 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE1');
521 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
522 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
523 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
524 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
525 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
526 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
527 l_message := FND_MESSAGE.GET;
528 l_notify_resp := cr_t.Notify_Resp1_short_name;
529 l_responsibility_id := cr_t.Notify_Resp1_ID;
530 -- dbms_output.put_line('In First Range' || l_message);
531 -- Check if actual is within the second range
532 ELSIF l_actual NOT BETWEEN
533 cr_t.range1_low AND cr_t.range1_High
534 AND l_actual BETWEEN
535 cr_t.range2_low AND cr_t.range2_High
536 THEN
537 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE2');
538 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
539 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
540 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
541 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
542 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
543 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
544 l_message := FND_MESSAGE.GET;
545 l_notify_resp := cr_t.Notify_Resp1_short_name;
546 l_responsibility_id := cr_t.Notify_Resp1_ID;
547 --dbms_output.put_line('In Second Range');
548 -- Check if actual is within the third range
549 ELSIF l_actual NOT BETWEEN
550 cr_t.range2_low AND cr_t.range2_High
551 AND l_actual BETWEEN
552 cr_t.range3_low AND cr_t.range3_High
553 THEN
554 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE3');
555 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
556 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
557 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
558 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
559 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
560 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
561 l_message := FND_MESSAGE.GET;
562 l_notify_resp := cr_t.Notify_Resp2_short_name;
563 l_responsibility_id := cr_t.Notify_Resp2_ID;
564 --dbms_output.put_line('In Third Range');
565 -- Check if actual is outside the third range
566 ELSIF l_actual NOT BETWEEN
567 cr_t.range3_low AND cr_t.range3_High
568 AND l_actual < l_target
569 THEN
570 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_OFFRANGE');
571 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
572 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
573 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
574 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
575 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
576 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
577 l_message := FND_MESSAGE.GET;
578 l_notify_resp := cr_t.Notify_Resp3_short_name;
579 l_responsibility_id := cr_t.Notify_Resp3_ID;
580 --dbms_output.put_line('Out of Third Range');
581 ELSIF l_actual > l_target
582 THEN
583 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_EXRANGE');
584 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
585 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
586 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
587 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
588 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
589 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
590 l_message := FND_MESSAGE.GET;
591 l_notify_resp := cr_t.Notify_Resp3_short_name;
592 l_responsibility_id := cr_t.Notify_Resp3_ID;
593 --dbms_output.put_line('less than First Range');
594 END IF;
595
596 -- We're on target!!
597 ELSE
598 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_INRANGE');
599 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
600 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
601 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
602 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
603 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
604 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
605 l_message := FND_MESSAGE.GET;
606 l_notify_resp := cr_t.Notify_Resp1_short_name;
607 l_responsibility_id := cr_t.Notify_Resp1_ID;
608 END IF;
609 --dbms_output.put_line('Calling Corrective Action');
610
611 --dbms_output.put_line( l_msg_subject);
612
613 Start_Corrective_Action
614 ( p_wf_process => l_workflow_process
615 , p_wf_item_type => l_workflow_item_type
616 , p_message => l_message
617 , p_notify_resp => l_notify_resp
618 , p_report_name1 => l_report
619 , p_report_param1 => NULL
620 , p_responsibility_id => l_responsibility_id
621 , p_msg_subject => l_msg_subject
622 );
623
624 END LOOP; -- ends loop to check targets
625 END LOOP; -- ends loop to calculate actual
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 l_message := 'Exception in Alert Procedure.';
630 --dbms_output.put_line(l_message);
631
632 END BIX_SERLVL_ALERT;
633
634
635 PROCEDURE BIX_AVGANS_ALERT
636 ( p_target_Level_Short_Name VARCHAR2
637 )
638 IS
639 l_period_set_name VARCHAR2(15);
640 l_period_name VARCHAR2(15);
641 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
642 l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
643 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
644 l_actual NUMBER;
645 l_target NUMBER;
646 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
647 l_Actual_Tbl BIS_ACTUAL_PUB.Actual_Tbl_Type;
648 l_workflow_item_type VARCHAR2(8);
649 l_workflow_process VARCHAR2(30);
650 l_notify_resp VARCHAR2(100);
651 l_responsibility_ID NUMBER;
652 l_message VARCHAR2(250);
653 l_report VARCHAR2(250);
654 l_organization_ID NUMBER;
655 l_organization_tbl BIS_POSTACTUAL.t_orgTable;
656 l_msg_data VARCHAR2(250);
657 l_msg_subject VARCHAR2(300);
658 l_return_status VARCHAR2(1);
659 l_msg_count NUMBER;
660 i NUMBER := 0;
661 l_center VARCHAR2(200);
662 CURSOR cr_actual IS
663 Select round(sum(speed_to_answer)/sum(number_of_interactions), 2) avg_speed_to_answer,
664 to_char(hour,'MON-YYYY') month,
665 interaction_center_id center_id
666 from bix_sum_grp_cls
667 group by to_char(hour,'MON-YYYY'), interaction_center_id;
668 CURSOR cr_center(p_center_id NUMBER) IS
669 SELECT call_center_name center_name
670 FROM bix_call_center_v
671 WHERE call_center_id = p_center_id;
672
673 CURSOR cr_target(p_organization_id VARCHAR2, p_center_id VARCHAR2, p_time VARCHAR2) IS
674 SELECT tv.target_level_short_name
675 , tv.target_level_name
676 , tv.target_level_id
677 , tv.plan_name
678 , tv.org_level_value_id
679 , tv.time_level_value_id
680 , tv.target
681 -- , tv.computed_target_short_name
682 , tv.range1_low
683 , tv.range1_high
684 , tv.range2_low
685 , tv.range2_high
686 , tv.range3_low
687 , tv.range3_high
688 , tv.notify_resp1_id
689 , tv.notify_resp1_short_name
690 , tv.notify_resp2_id
691 , tv.notify_resp2_short_name
692 , tv.notify_resp3_id
693 , tv.notify_resp3_short_name
694 , tv.dim1_level_value_id
695 FROM BISFV_TARGETS tv
696 WHERE tv.target_level_short_name = p_target_level_short_name
697 AND tv.time_level_value_id like '%' || p_time || '%'
698 AND tv.org_level_value_id = p_organization_id
699 AND tv.dim1_level_value_name = p_center_id;
700
701 BEGIN
702
703 l_target_level_rec.target_Level_Short_Name
704 := p_target_Level_Short_Name;
705 -- l_period_set_name := p_time_period;
706 -- l_period_name := p_time_period;
707 l_report := 'BIXASPB0';
708 -- Get translated subject from fnd message dictionary
709 FND_MESSAGE.SET_NAME('BIX','BIX_AVG_SPEED_ANSWER');
710 -- l_msg_subject := 'Average Speed to Answer PMF Notification';
711 l_msg_subject := FND_MESSAGE.GET;
712 l_organization_id := -1;
713 -- Get the workflow process
714 SELECT workflow_item_type, workflow_process_short_name
715 INTO l_workflow_item_type, l_workflow_process
716 FROM bisbv_target_levels
717 WHERE target_level_short_name = p_target_level_short_name;
718
719 -- Get the KPIs users have selected to monitor on their homepage
720 BIS_ACTUAL_PUB.Retrieve_User_Selections
721 ( p_api_version => 1.0
722 ,p_Target_Level_Rec => l_Target_Level_Rec
723 ,x_Indicator_Region_Tbl => l_user_selection_Tbl
724 ,x_return_status => l_return_status
725 ,x_msg_count => l_msg_count
726 ,x_msg_data => l_msg_data
727 ,x_error_Tbl => l_error_tbl
728 );
729
730 -- Calculate Actual for all Months for All Call Centers
731 FOR cr_a in cr_actual LOOP
732
733 l_actual := cr_a.avg_speed_to_answer;
734 for cr_c in cr_center(cr_a.center_id) LOOP
735 l_center := cr_c.center_name;
736 end loop;
737 -- Post actual value for only those KPIs users have selected.
738 -- FOR i IN 1..l_user_selection_Tbl.COUNT LOOP
739 -- IF l_user_selection_tbl(i).organization_id = l_organization_id THEN
740 -- END IF;
741 -- END LOOP;
742
743 -- Check for exceptions and start corrective action
744 FOR cr_t IN cr_target(l_organization_id, to_char(cr_a.center_id), cr_a.month) LOOP
745
746 IF cr_t.target IS NULL THEN
747 -- l_target := Get_Target(cr.computed_target_short_name);
748 NULL;
749 ELSE
750 l_target := cr_t.target;
751 END IF;
752 l_Actual_Tbl(1).target_Level_Short_Name
753 := p_target_level_short_name;
754 l_Actual_Tbl(1).Org_Level_value_ID := l_organization_id;
755 l_Actual_Tbl(1).time_Level_value_ID := cr_t.time_level_value_id;
756 l_Actual_Tbl(1).target_Level_ID := cr_t.target_level_id;
757 l_Actual_Tbl(1).Actual := l_actual;
758 l_Actual_Tbl(1).dim1_Level_value_ID := cr_t.dim1_level_value_id;
759 l_Actual_Tbl(1).target_level_name := cr_t.target_level_name;
760 BIS_ACTUAL_PUB.POST_ACTUAL
761 ( p_api_version => 1.0
762 ,p_Actual_Rec => l_actual_Tbl(1)
763 ,x_return_status => l_return_status
764 ,x_msg_count => l_msg_count
765 ,x_msg_data => l_msg_data
766 ,x_error_tbl => l_error_tbl
767 );
768 -- We're not on target....
769 IF l_actual <> l_target THEN
770 -- Check if actual is within the first range
771 IF l_actual NOT BETWEEN
772 cr_t.range2_low AND cr_t.range2_High
773 AND l_actual BETWEEN
774 cr_t.range1_low AND cr_t.range1_High
775 THEN
776 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE1');
777 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
778 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
779 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
780 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
781 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
782 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
783 l_message := FND_MESSAGE.GET;
784 /*
785 l_message := 'Hey! Something is wrong!'||' '||
786 'Target Level: '||cr_t.target_level_name||' '||
787 'Business Plan: '||cr_t.plan_name||' '|| ' for '||
788 l_center || ' for ' || cr_a.month || ' ' ||
789 'Target is: '||l_target||' '||
790 'Actual is: '||l_actual;
791 */
792 l_notify_resp := cr_t.Notify_Resp1_short_name;
793 l_responsibility_id := cr_t.Notify_Resp1_ID;
794 -- dbms_output.put_line('In First Range' || l_message);
795 -- Check if actual is within the second range
796 ELSIF l_actual NOT BETWEEN
797 cr_t.range1_low AND cr_t.range1_High
798 AND l_actual BETWEEN
799 cr_t.range2_low AND cr_t.range2_High
800 THEN
801 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE2');
802 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
803 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
804 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
805 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
806 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
807 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
808 l_message := FND_MESSAGE.GET;
809 /*
810 l_message := 'Hey! Something is wrong!'||' '||
811 'Target Level: '||cr_t.target_level_name||' '||
812 'Business Plan: '||cr_t.plan_name||' '|| ' for '||
813 l_center || ' for ' || cr_a.month || ' '||
814 'Target is: '||l_target||' '||
815 'Actual is: '||l_actual;
816 */
817 l_notify_resp := cr_t.Notify_Resp1_short_name;
818 l_responsibility_id := cr_t.Notify_Resp1_ID;
819 --dbms_output.put_line('In Second Range');
820 -- Check if actual is within the third range
821 ELSIF l_actual NOT BETWEEN
822 cr_t.range2_low AND cr_t.range2_High
823 AND l_actual BETWEEN
824 cr_t.range3_low AND cr_t.range3_High
825 THEN
826 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE3');
827 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
828 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
829 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
830 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
831 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
832 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
833 l_message := FND_MESSAGE.GET;
834 /*
835 l_message := 'Hey! Something is VERY wrong!'||' '||
836 'Target Level: '||cr_t.target_level_name||' '||
837 'Business Plan: '||cr_t.plan_name||' '|| ' for '||
838 l_center || ' for ' || cr_a.month || ' '||
839 'Target is: '||l_target||' '||
840 'Actual is: '||l_actual;
841 */
842 l_notify_resp := cr_t.Notify_Resp2_short_name;
843 l_responsibility_id := cr_t.Notify_Resp2_ID;
844 --dbms_output.put_line('In Third Range');
845 -- Check if actual is outside the third range
846 ELSIF l_actual NOT BETWEEN
847 cr_t.range3_low AND cr_t.range3_High
848 AND l_actual > l_target
849 THEN
850 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_OFFRANGE');
851 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
852 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
853 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
854 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
855 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
856 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
857 l_message := FND_MESSAGE.GET;
858 /*
859 l_message := 'Hey! Something is VERY VERY wrong!'||' '||
860 'Target Level: '||cr_t.target_level_name||' '||
861 'Business Plan: '||cr_t.plan_name||' '|| ' for '||
862 l_center || ' for ' || cr_a.month || ' ' ||
863 'Target is: '||l_target||' '||
864 'Actual is: '||l_actual;
865 */
866 l_notify_resp := cr_t.Notify_Resp3_short_name;
867 l_responsibility_id := cr_t.Notify_Resp3_ID;
868 --dbms_output.put_line('Out of Third Range');
869 ELSIF l_actual < l_target
870 THEN
871 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_EXRANGE');
872 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
873 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
874 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
875 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
876 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
877 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
878 l_message := FND_MESSAGE.GET;
879 /*
880 l_message := 'Excellent '||' '||
881 'Target Level: '||cr_t.target_level_name||' '||
882 'Business Plan: '||cr_t.plan_name||' '|| ' for '||
883 l_center || ' for ' || cr_a.month || ' ' ||
884 'Target is: '||l_target||' '||
885 'Actual is: '||l_actual;
886 */
887 l_notify_resp := cr_t.Notify_Resp3_short_name;
888 l_responsibility_id := cr_t.Notify_Resp3_ID;
889 --dbms_output.put_line('less than First Range');
890 END IF;
891
892 -- We're on target!!
893 ELSE
894 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_INRANGE');
895 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
896 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
897 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
898 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
899 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
900 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
901 l_message := FND_MESSAGE.GET;
902 /*
903 l_message := 'Good job!!'||' '||
904 'Target Level: '||cr_t.target_level_name||' '||
905 'Business Plan: '||cr_t.plan_name||' '|| ' for '||
906 l_center || ' for ' || cr_a.month || ' ' ||
907 'Target is: '||l_target||' '||
908 'Actual is: '||l_actual;
909 */
910 l_notify_resp := cr_t.Notify_Resp1_short_name;
911 l_responsibility_id := cr_t.Notify_Resp1_ID;
912 END IF;
913
914 --dbms_output.put_line( l_report);
915 /*
916 dbms_output.put_line('Calling Corrective Action' || cr_a.month ||l_responsibility_id || l_notify_resp || l_workflow_process || l_workflow_item_type || l_message);
917 */
918 Start_Corrective_Action
919 ( p_wf_process => l_workflow_process
920 , p_wf_item_type => l_workflow_item_type
921 , p_message => l_message
922 , p_notify_resp => l_notify_resp
923 , p_report_name1 => l_report
924 , p_report_param1 => NULL
925 , p_responsibility_id => l_responsibility_id
926 , p_msg_subject => l_msg_subject
927 );
928
929 END LOOP; -- ends loop to check targets
930 END LOOP; -- ends loop to calculate actual
931
932 EXCEPTION
933 WHEN OTHERS THEN
934 l_message := 'Exception in Average Speed to Answer Alert Procedure.';
935 --dbms_output.put_line(l_message);
936
937 END BIX_AVGANS_ALERT;
938 /* Alert for Occupancy Rate */
939 PROCEDURE BIX_OCCRATE_ALERT
940 ( p_target_Level_Short_Name VARCHAR2
941 )
942 IS
943 l_period_set_name VARCHAR2(15);
944 l_period_name VARCHAR2(15);
945 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
946 l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
947 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
948 l_actual NUMBER;
949 l_target NUMBER;
950 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
951 l_Actual_Tbl BIS_ACTUAL_PUB.Actual_Tbl_Type;
952 l_workflow_item_type VARCHAR2(8);
953 l_workflow_process VARCHAR2(30);
954 l_notify_resp VARCHAR2(100);
955 l_responsibility_ID NUMBER;
956 l_message VARCHAR2(250);
957 l_report VARCHAR2(250);
958 l_organization_ID NUMBER;
959 l_organization_tbl BIS_POSTACTUAL.t_orgTable;
960 l_msg_data VARCHAR2(250);
961 l_msg_count NUMBER;
962 l_msg_subject VARCHAR2(300);
963 l_return_status VARCHAR2(1);
964 i NUMBER := 0;
965 l_center VARCHAR2(200);
966 CURSOR cr_actual IS
967 Select round(sum(talk_time)/sum(talk_time + idle_time)*100, 2) occupancy_rate,
968 to_char(hour,'MON-YYYY') month,
969 interaction_center_id center_id
970 from bix_sum_grp_cls
971 group by to_char(hour,'MON-YYYY'), interaction_center_id;
972 CURSOR cr_center(p_center_id NUMBER) IS
973 SELECT call_center_name center_name
974 FROM bix_call_center_v
975 WHERE call_center_id = p_center_id;
976
977 CURSOR cr_target(p_organization_id VARCHAR2,
978 p_center_id VARCHAR2, p_time VARCHAR2)
979 IS
980 SELECT tv.target_level_short_name
981 , tv.target_level_name
982 , tv.target_level_id
983 , tv.plan_name
984 , tv.org_level_value_id
985 , tv.time_level_value_id
986 , tv.target
987 -- , tv.computed_target_short_name
988 , tv.range1_low
989 , tv.range1_high
990 , tv.range2_low
991 , tv.range2_high
992 , tv.range3_low
993 , tv.range3_high
994 , tv.notify_resp1_id
995 , tv.notify_resp1_short_name
996 , tv.notify_resp2_id
997 , tv.notify_resp2_short_name
998 , tv.notify_resp3_id
999 , tv.notify_resp3_short_name
1000 , tv.dim1_level_value_id
1001 FROM BISFV_TARGETS tv
1002 WHERE tv.target_level_short_name = p_target_level_short_name
1003 AND tv.time_level_value_id like '%' || p_time || '%'
1004 AND tv.org_level_value_id = p_organization_id
1005 AND tv.dim1_level_value_name = p_center_id;
1006
1007 BEGIN
1008
1009 l_target_level_rec.target_Level_Short_Name
1010 := p_target_Level_Short_Name;
1011 -- l_period_set_name := p_time_period;
1012 -- l_period_name := p_time_period;
1013 l_report := 'BIXOCCM0';
1014 /* get translated subject for notification */
1015 FND_MESSAGE.SET_NAME('BIX','BIX_OCCUPANCY_RATE');
1016 l_msg_subject := FND_MESSAGE.GET;
1017 if l_msg_subject is NULL THEN
1018 l_msg_subject := 'Occupancy Rate PMF Notification';
1019 end if;
1020 l_organization_id := -1;
1021 -- Get the workflow process
1022 SELECT workflow_item_type, workflow_process_short_name
1023 INTO l_workflow_item_type, l_workflow_process
1024 FROM bisbv_target_levels
1025 WHERE target_level_short_name = p_target_level_short_name;
1026 -- Get the KPIs users have selected to monitor on their homepage
1027 BIS_ACTUAL_PUB.Retrieve_User_Selections
1028 ( p_api_version => 1.0
1029 ,p_Target_Level_Rec => l_Target_Level_Rec
1030 ,x_Indicator_Region_Tbl => l_user_selection_Tbl
1031 ,x_return_status => l_return_status
1032 ,x_msg_count => l_msg_count
1033 ,x_msg_data => l_msg_data
1034 ,x_error_Tbl => l_error_tbl
1035 );
1036
1037 -- Calculate Actual for all Months for All Call Centers
1038 FOR cr_a in cr_actual LOOP
1039
1040 l_actual := cr_a.occupancy_rate;
1041 for cr_c in cr_center(cr_a.center_id) LOOP
1042 l_center := cr_c.center_name;
1043 end loop;
1044 -- Post actual value for only those KPIs users have selected.
1045 -- FOR i IN 1..l_user_selection_Tbl.COUNT LOOP
1046 -- IF l_user_selection_tbl(i).organization_id = l_organization_id THEN
1047 -- END IF;
1048 -- END LOOP;
1049
1050 -- Check for exceptions and start corrective action
1051 FOR cr_t IN cr_target(l_organization_id, to_char(cr_a.center_id), cr_a.month
1052 ) LOOP
1053
1054 IF cr_t.target IS NULL THEN
1055 -- l_target := Get_Target(cr.computed_target_short_name);
1056 NULL;
1057 ELSE
1058 l_target := cr_t.target;
1059 END IF;
1060 l_Actual_Tbl(1).target_Level_Short_Name
1061 := p_target_level_short_name;
1062 l_Actual_Tbl(1).Org_Level_value_ID := l_organization_id;
1063 l_Actual_Tbl(1).time_Level_value_ID := cr_t.time_level_value_id;
1064 l_Actual_Tbl(1).target_Level_ID := cr_t.target_level_id;
1065 l_Actual_Tbl(1).Actual := l_actual;
1066 l_Actual_Tbl(1).dim1_Level_value_ID := cr_t.dim1_level_value_id;
1067 l_Actual_Tbl(1).target_level_name := cr_t.target_level_name;
1068 BIS_ACTUAL_PUB.POST_ACTUAL
1069 ( p_api_version => 1.0
1070 ,p_Actual_Rec => l_actual_Tbl(1)
1071 ,x_return_status => l_return_status
1072 ,x_msg_count => l_msg_count
1073 ,x_msg_data => l_msg_data
1074 ,x_error_tbl => l_error_tbl
1075 );
1076 -- We're not on target....
1077 IF l_actual <> l_target THEN
1078 -- Check if actual is within the first range
1079 IF l_actual NOT BETWEEN
1080 cr_t.range2_low AND cr_t.range2_High
1081 AND l_actual BETWEEN
1082 cr_t.range1_low AND cr_t.range1_High
1083 THEN
1084 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE1');
1085 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1086 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1087 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1088 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1089 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1090 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1091 l_message := FND_MESSAGE.GET;
1092 /*
1093 l_message := 'Hey! Something is wrong!'||' '||
1094 'Target Level: '||cr_t.target_level_name||' '||
1095 'Business Plan: '||cr_t.plan_name||' '|| ' for '||
1096 l_center || ' for ' || cr_a.month || ' ' ||
1097 'Target is: '||l_target||' '||
1098 'Actual is: '||l_actual;
1099 */
1100 l_notify_resp := cr_t.Notify_Resp1_short_name;
1101 l_responsibility_id := cr_t.Notify_Resp1_ID;
1102 -- dbms_output.put_line('In First Range' || l_message);
1103 -- Check if actual is within the second range
1104 ELSIF l_actual NOT BETWEEN
1105 cr_t.range1_low AND cr_t.range1_High
1106 AND l_actual BETWEEN
1107 cr_t.range2_low AND cr_t.range2_High
1108 THEN
1109 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE2');
1110 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1111 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1112 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1113 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1114 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1115 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1116 l_message := FND_MESSAGE.GET;
1117 /*
1118 l_message := 'Hey! Something is wrong!'||' '||
1119 'Target Level: '||cr_t.target_level_name||' '||
1120 'Business Plan: '||cr_t.plan_name||' '|| ' for '||
1121 l_center || ' for ' || cr_a.month || ' '||
1122 'Target is: '||l_target||' '||
1123 'Actual is: '||l_actual;
1124 */
1125 l_notify_resp := cr_t.Notify_Resp1_short_name;
1126 l_responsibility_id := cr_t.Notify_Resp1_ID;
1127 --dbms_output.put_line('In Second Range');
1128 -- Check if actual is within the third range
1129 ELSIF l_actual NOT BETWEEN
1130
1131 cr_t.range2_low AND cr_t.range2_High
1132 AND l_actual BETWEEN
1133 cr_t.range3_low AND cr_t.range3_High
1134 THEN
1135 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE3');
1136 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1137 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1138 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1139 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1140 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1141 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1142 l_message := FND_MESSAGE.GET;
1143 /*
1144 l_message := 'Hey! Something is VERY wrong!'||' '||
1145 'Target Level: '||cr_t.target_level_name||' '||
1146 'Business Plan: '||cr_t.plan_name||' '|| ' for '||
1147 l_center || ' for ' || cr_a.month || ' '||
1148 'Target is: '||l_target||' '||
1149 'Actual is: '||l_actual;
1150 */
1151 l_notify_resp := cr_t.Notify_Resp2_short_name;
1152 l_responsibility_id := cr_t.Notify_Resp2_ID;
1153 --dbms_output.put_line('In Third Range');
1154 -- Check if actual is outside the third range
1155 ELSIF l_actual NOT BETWEEN
1156 cr_t.range3_low AND cr_t.range3_High
1157 AND l_actual < l_target
1158 THEN
1159 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_OFFRANGE');
1160 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1161 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1162 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1163 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1164 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1165 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1166 l_message := FND_MESSAGE.GET;
1167 /*
1168 l_message := 'Hey! Something is VERY VERY wrong!'||' '||
1169 'Target Level: '||cr_t.target_level_name||' '||
1170 'Business Plan: '||cr_t.plan_name||' '|| ' for '||
1171 l_center || ' for ' || cr_a.month || ' ' ||
1172 'Target is: '||l_target||' '||
1173 'Actual is: '||l_actual;
1174 */
1175 l_notify_resp := cr_t.Notify_Resp3_short_name;
1176 l_responsibility_id := cr_t.Notify_Resp3_ID;
1177 --dbms_output.put_line('Out of Third Range');
1178 ELSIF l_actual > l_target
1179 THEN
1180 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_EXRANGE');
1181 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1182 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1183 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1184 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1185 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1186 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1187 l_message := FND_MESSAGE.GET;
1188 /*
1189 l_message := 'Excellent '||' '||
1190 'Target Level: '||cr_t.target_level_name||' '||
1191 'Business Plan: '||cr_t.plan_name||' '|| ' for '||
1192 l_center || ' for ' || cr_a.month || ' ' ||
1193 'Target is: '||l_target||' '||
1194 'Actual is: '||l_actual;
1195 */
1196 l_notify_resp := cr_t.Notify_Resp3_short_name;
1197 l_responsibility_id := cr_t.Notify_Resp3_ID;
1198 --dbms_output.put_line('less than First Range');
1199 END IF;
1200
1201 -- We're on target!!
1202 ELSE
1203 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_INRANGE');
1204 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1205 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1206 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1207 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1208 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1209 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1210 l_message := FND_MESSAGE.GET;
1211 /*
1212 l_message := 'Good job!!'||' '||
1213 'Target Level: '||cr_t.target_level_name||' '||
1214 'Business Plan: '||cr_t.plan_name||' '|| ' for '||
1215 l_center || ' for ' || cr_a.month || ' ' ||
1216 'Target is: '||l_target||' '||
1217 'Actual is: '||l_actual;
1218 */
1219 l_notify_resp := cr_t.Notify_Resp1_short_name;
1220 l_responsibility_id := cr_t.Notify_Resp1_ID;
1221 END IF;
1222 /*
1223 dbms_output.put_line('Calling Corrective Action' || cr_a.month ||
1224 l_responsibility_id || l_notify_resp || l_workflow_process ||
1225 l_workflow_item_type || l_message );
1226 */
1227 Start_Corrective_Action
1228 ( p_wf_process => l_workflow_process
1229 , p_wf_item_type => l_workflow_item_type
1230 , p_message => l_message
1231 , p_notify_resp => l_notify_resp
1232 , p_report_name1 => l_report
1233 , p_report_param1 => NULL
1234 , p_responsibility_id => l_responsibility_id
1235 , p_msg_subject => l_msg_subject
1236 );
1237 END LOOP; -- ends loop to check targets
1238 END LOOP; -- ends loop to calculate actual
1239
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 l_message := 'Exception in Occupancy Rate Alert Procedure.';
1243 --dbms_output.put_line(l_message);
1244
1245 END BIX_OCCRATE_ALERT;
1246 /* Alert Procedure for Average Talk Time */
1247 PROCEDURE BIX_AVGTALK_ALERT
1248 ( p_target_Level_Short_Name VARCHAR2
1249 )
1250 IS
1251 l_period_set_name VARCHAR2(15);
1252 l_period_name VARCHAR2(15);
1253 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
1254 l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
1255 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1256 l_actual NUMBER;
1257 l_target NUMBER;
1258 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
1259 l_Actual_Tbl BIS_ACTUAL_PUB.Actual_Tbl_Type;
1260 l_workflow_item_type VARCHAR2(8);
1261 l_workflow_process VARCHAR2(30);
1262 l_notify_resp VARCHAR2(100);
1263 l_responsibility_ID NUMBER;
1264 l_message VARCHAR2(250);
1265 l_report VARCHAR2(250);
1266 l_organization_ID NUMBER;
1267 l_organization_tbl BIS_POSTACTUAL.t_orgTable;
1268 l_msg_data VARCHAR2(250);
1269 l_msg_subject VARCHAR2(300);
1270 l_return_status VARCHAR2(1);
1271 l_msg_count NUMBER;
1272 i NUMBER := 0;
1273 l_center VARCHAR2(200);
1274 CURSOR cr_actual IS
1275 Select round(sum(talk_time)/sum(interactions_answered_live)/60,2) avg_talk_time,
1276 to_char(hour,'MON-YYYY') month,
1277 interaction_center_id center_id
1278 from bix_sum_grp_cls
1279 group by to_char(hour,'MON-YYYY'), interaction_center_id;
1280 CURSOR cr_center(p_center_id NUMBER) IS
1281 SELECT call_center_name center_name
1282 FROM bix_call_center_v
1283 WHERE call_center_id = p_center_id;
1284
1285 CURSOR cr_target(p_organization_id VARCHAR2, p_center_id VARCHAR2, p_time VARCHAR2) IS
1286 SELECT tv.target_level_short_name
1287 , tv.target_level_name
1288 , tv.target_level_id
1289 , tv.plan_name
1290 , tv.org_level_value_id
1291 , tv.time_level_value_id
1292 , tv.target
1293 -- , tv.computed_target_short_name
1294 , tv.range1_low
1295 , tv.range1_high
1296 , tv.range2_low
1297 , tv.range2_high
1298 , tv.range3_low
1299 , tv.range3_high
1300 , tv.notify_resp1_id
1301 , tv.notify_resp1_short_name
1302 , tv.notify_resp2_id
1303 , tv.notify_resp2_short_name
1304 , tv.notify_resp3_id
1305 , tv.notify_resp3_short_name
1306 , tv.dim1_level_value_id
1307 FROM BISFV_TARGETS tv
1308 WHERE tv.target_level_short_name = p_target_level_short_name
1309 AND tv.time_level_value_id like '%' || p_time || '%'
1310 AND tv.org_level_value_id = p_organization_id
1311 AND tv.dim1_level_value_name = p_center_id;
1312
1313 BEGIN
1314
1315 l_target_level_rec.target_Level_Short_Name
1316 := p_target_Level_Short_Name;
1317 -- l_period_set_name := p_time_period;
1318 -- l_period_name := p_time_period;
1319 l_report := 'BIXATLK0';
1320 FND_MESSAGE.SET_NAME('BIX', 'BIX_AVG_TALK_TIME');
1321 l_msg_subject := FND_MESSAGE.GET;
1322 IF l_msg_subject is NULL then
1323 l_msg_subject := 'Average Talk Time PMF Notification';
1324 END IF;
1325 l_organization_id := -1;
1326 -- Get the workflow process
1327 SELECT workflow_item_type, workflow_process_short_name
1328 INTO l_workflow_item_type, l_workflow_process
1329 FROM bisbv_target_levels
1330 WHERE target_level_short_name = p_target_level_short_name;
1331
1332 -- Get the KPIs users have selected to monitor on their homepage
1333 BIS_ACTUAL_PUB.Retrieve_User_Selections
1334 ( p_api_version => 1.0
1335 ,p_Target_Level_Rec => l_Target_Level_Rec
1336 ,x_Indicator_Region_Tbl => l_user_selection_Tbl
1337 ,x_return_status => l_return_status
1338 ,x_msg_count => l_msg_count
1339 ,x_msg_data => l_msg_data
1340 ,x_error_Tbl => l_error_tbl
1341 );
1342
1343 -- Calculate Actual for all Months for All Call Centers
1344 FOR cr_a in cr_actual LOOP
1345
1346 l_actual := cr_a.avg_talk_time;
1347 for cr_c in cr_center(cr_a.center_id) LOOP
1348 l_center := cr_c.center_name;
1349 end loop;
1350 -- Post actual value for only those KPIs users have selected.
1351 -- FOR i IN 1..l_user_selection_Tbl.COUNT LOOP
1352 -- IF l_user_selection_tbl(i).organization_id = l_organization_id THEN
1353 -- END IF;
1354 -- END LOOP;
1355
1356 -- Check for exceptions and start corrective action
1357 FOR cr_t IN cr_target(l_organization_id, to_char(cr_a.center_id), cr_a.month) LOOP
1358
1359 IF cr_t.target IS NULL THEN
1360 -- l_target := Get_Target(cr.computed_target_short_name);
1361 NULL;
1362 ELSE
1363 l_target := cr_t.target;
1364 END IF;
1365 l_Actual_Tbl(1).target_Level_Short_Name
1366 := p_target_level_short_name;
1367 l_Actual_Tbl(1).Org_Level_value_ID := l_organization_id;
1368 l_Actual_Tbl(1).time_Level_value_ID := cr_t.time_level_value_id;
1369 l_Actual_Tbl(1).target_Level_ID := cr_t.target_level_id;
1370 l_Actual_Tbl(1).Actual := l_actual;
1371 l_Actual_Tbl(1).dim1_Level_value_ID := cr_t.dim1_level_value_id;
1372 l_Actual_Tbl(1).target_level_name := cr_t.target_level_name;
1373 BIS_ACTUAL_PUB.POST_ACTUAL
1374 ( p_api_version => 1.0
1375 ,p_Actual_Rec => l_actual_Tbl(1)
1376 ,x_return_status => l_return_status
1377 ,x_msg_count => l_msg_count
1378 ,x_msg_data => l_msg_data
1379 ,x_error_tbl => l_error_tbl
1380 );
1381 -- We're not on target....
1382 IF l_actual <> l_target THEN
1383 -- Check if actual is within the first range
1384 IF l_actual NOT BETWEEN
1385 cr_t.range2_low AND cr_t.range2_High
1386 AND l_actual BETWEEN
1387 cr_t.range1_low AND cr_t.range1_High
1388 THEN
1389 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE1');
1390 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1391 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1392 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1393 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1394 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1395 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1396 l_message := FND_MESSAGE.GET;
1397 l_notify_resp := cr_t.Notify_Resp1_short_name;
1398 l_responsibility_id := cr_t.Notify_Resp1_ID;
1399 -- dbms_output.put_line('In First Range' || l_message);
1400 -- Check if actual is within the second range
1401 ELSIF l_actual NOT BETWEEN
1402 cr_t.range1_low AND cr_t.range1_High
1403 AND l_actual BETWEEN
1404 cr_t.range2_low AND cr_t.range2_High
1405 THEN
1406 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE2');
1407 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1408 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1409 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1410 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1411 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1412 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1413 l_message := FND_MESSAGE.GET;
1414 l_notify_resp := cr_t.Notify_Resp1_short_name;
1415 l_responsibility_id := cr_t.Notify_Resp1_ID;
1416 --dbms_output.put_line('In Second Range');
1417 -- Check if actual is within the third range
1418 ELSIF l_actual NOT BETWEEN
1419 cr_t.range2_low AND cr_t.range2_High
1420 AND l_actual BETWEEN
1421 cr_t.range3_low AND cr_t.range3_High
1422 THEN
1423 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE3');
1424 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1425 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1426 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1427 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1428 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1429 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1430 l_message := FND_MESSAGE.GET;
1431 l_notify_resp := cr_t.Notify_Resp2_short_name;
1432 l_responsibility_id := cr_t.Notify_Resp2_ID;
1433 --dbms_output.put_line('In Third Range');
1434 -- Check if actual is outside the third range
1435 ELSIF l_actual NOT BETWEEN
1436 cr_t.range3_low AND cr_t.range3_High
1437 AND l_actual > l_target
1438 THEN
1439 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_OFFRANGE');
1440 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1441 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1442 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1443 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1444 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1445 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1446 l_message := FND_MESSAGE.GET;
1447 l_notify_resp := cr_t.Notify_Resp3_short_name;
1448 l_responsibility_id := cr_t.Notify_Resp3_ID;
1449 --dbms_output.put_line('Out of Third Range');
1450 ELSIF l_actual < l_target
1451 THEN
1452 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_EXRANGE');
1453 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1454 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1455 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1456 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1457 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1458 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1459 l_message := FND_MESSAGE.GET;
1460 l_notify_resp := cr_t.Notify_Resp3_short_name;
1461 l_responsibility_id := cr_t.Notify_Resp3_ID;
1462 --dbms_output.put_line('less than First Range');
1463 END IF;
1464
1465 -- We're on target!!
1466 ELSE
1467 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_INRANGE');
1468 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1469 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1470 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1471 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1472 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1473 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1474 l_message := FND_MESSAGE.GET;
1475 l_notify_resp := cr_t.Notify_Resp1_short_name;
1476 l_responsibility_id := cr_t.Notify_Resp1_ID;
1477 END IF;
1478 /*
1479 dbms_output.put_line('Calling Corrective Action' || cr_a.month ||
1480 l_responsibility_id || l_notify_resp || l_workflow_process
1481 || l_workflow_item_type || l_message);
1482 */
1483 Start_Corrective_Action
1484 ( p_wf_process => l_workflow_process
1485 , p_wf_item_type => l_workflow_item_type
1486 , p_message => l_message
1487 , p_notify_resp => l_notify_resp
1488 , p_report_name1 => l_report
1489 , p_report_param1 => NULL
1490 , p_responsibility_id => l_responsibility_id
1491 , p_msg_subject => l_msg_subject
1492 );
1493
1494 END LOOP; -- ends loop to check targets
1495 END LOOP; -- ends loop to calculate actual
1496
1497 EXCEPTION
1498 WHEN OTHERS THEN
1499 l_message := 'Exception in Average Talk Time Alert Procedure.';
1500 --dbms_output.put_line(l_message);
1501
1502 END BIX_AVGTALK_ALERT;
1503 /* Alert for Average Wait to Abandon */
1504 PROCEDURE BIX_AVGWAIT_ALERT
1505 ( p_target_Level_Short_Name VARCHAR2
1506 )
1507 IS
1508 l_period_set_name VARCHAR2(15);
1509 l_period_name VARCHAR2(15);
1510 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
1511 l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
1512 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1513 l_actual NUMBER;
1514 l_target NUMBER;
1515 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
1516 l_Actual_Tbl BIS_ACTUAL_PUB.Actual_Tbl_Type;
1517 l_workflow_item_type VARCHAR2(8);
1518 l_workflow_process VARCHAR2(30);
1519 l_notify_resp VARCHAR2(100);
1520 l_responsibility_ID NUMBER;
1521 l_message VARCHAR2(250);
1522 l_report VARCHAR2(250);
1523 l_organization_ID NUMBER;
1524 l_organization_tbl BIS_POSTACTUAL.t_orgTable;
1525 l_msg_data VARCHAR2(250);
1526 l_msg_count NUMBER;
1527 l_msg_subject VARCHAR2(300);
1528 l_return_status VARCHAR2(1);
1529 i NUMBER := 0;
1530 l_center VARCHAR2(200);
1531 CURSOR cr_actual IS
1532 Select round(sum(wait_time_to_abandon)/sum(number_of_interactions), 2) avg_wait_time_to_abandon,
1533 to_char(hour,'MON-YYYY') month,
1534 interaction_center_id center_id
1535 from bix_sum_grp_cls
1536 group by to_char(hour,'MON-YYYY'), interaction_center_id;
1537 CURSOR cr_center(p_center_id NUMBER) IS
1538 SELECT call_center_name center_name
1539 FROM bix_call_center_v
1540 WHERE call_center_id = p_center_id;
1541
1542 CURSOR cr_target(p_organization_id VARCHAR2,
1543 p_center_id VARCHAR2, p_time VARCHAR2)
1544 IS
1545 SELECT tv.target_level_short_name
1546 , tv.target_level_name
1547 , tv.target_level_id
1548 , tv.plan_name
1549 , tv.org_level_value_id
1550 , tv.time_level_value_id
1551 , tv.target
1552 -- , tv.computed_target_short_name
1553 , tv.range1_low
1554 , tv.range1_high
1555 , tv.range2_low
1556 , tv.range2_high
1557 , tv.range3_low
1558 , tv.range3_high
1559 , tv.notify_resp1_id
1560 , tv.notify_resp1_short_name
1561 , tv.notify_resp2_id
1562 , tv.notify_resp2_short_name
1563 , tv.notify_resp3_id
1564 , tv.notify_resp3_short_name
1565 , tv.dim1_level_value_id
1566 FROM BISFV_TARGETS tv
1567 WHERE tv.target_level_short_name = p_target_level_short_name
1568 AND tv.time_level_value_id like '%' || p_time || '%'
1569 AND tv.org_level_value_id = p_organization_id
1570 AND tv.dim1_level_value_name = p_center_id;
1571
1572 BEGIN
1573
1574 l_target_level_rec.target_Level_Short_Name
1575 := p_target_Level_Short_Name;
1576 -- l_period_set_name := p_time_period;
1577 -- l_period_name := p_time_period;
1578 l_report := 'BIXWABC0';
1579 FND_MESSAGE.SET_NAME('BIX', 'BIX_AVG_WAIT_TO_ABANDON');
1580 l_msg_subject := FND_MESSAGE.GET;
1581 IF l_msg_subject is NULL then
1582 l_msg_subject := 'Average Wait Time to Abandon PMF Notification';
1583 END IF;
1584 l_organization_id := -1;
1585 -- Get the workflow process
1586 SELECT workflow_item_type, workflow_process_short_name
1587 INTO l_workflow_item_type, l_workflow_process
1588 FROM bisbv_target_levels
1589 WHERE target_level_short_name = p_target_level_short_name;
1590
1591 -- Get the KPIs users have selected to monitor on their homepage
1592 BIS_ACTUAL_PUB.Retrieve_User_Selections
1593 ( p_api_version => 1.0
1594 ,p_Target_Level_Rec => l_Target_Level_Rec
1595 ,x_Indicator_Region_Tbl => l_user_selection_Tbl
1596 ,x_return_status => l_return_status
1597 ,x_msg_count => l_msg_count
1598 ,x_msg_data => l_msg_data
1599 ,x_error_Tbl => l_error_tbl
1600 );
1601
1602 -- Calculate Actual for all Months for All Call Centers
1603 FOR cr_a in cr_actual LOOP
1604
1605 l_actual := cr_a.avg_wait_time_to_abandon;
1606 for cr_c in cr_center(cr_a.center_id) LOOP
1607 l_center := cr_c.center_name;
1608 end loop;
1609 -- Post actual value for only those KPIs users have selected.
1610 -- FOR i IN 1..l_user_selection_Tbl.COUNT LOOP
1611 -- IF l_user_selection_tbl(i).organization_id = l_organization_id THEN
1612 -- END IF;
1613 -- END LOOP;
1614
1615 -- Check for exceptions and start corrective action
1616 FOR cr_t IN cr_target(l_organization_id, to_char(cr_a.center_id), cr_a.month
1617 ) LOOP
1618
1619 IF cr_t.target IS NULL THEN
1620 -- l_target := Get_Target(cr.computed_target_short_name);
1621 NULL;
1622 ELSE
1623 l_target := cr_t.target;
1624 END IF;
1625 l_Actual_Tbl(1).target_Level_Short_Name
1626 := p_target_level_short_name;
1627 l_Actual_Tbl(1).Org_Level_value_ID := l_organization_id;
1628 l_Actual_Tbl(1).time_Level_value_ID := cr_t.time_level_value_id;
1629 l_Actual_Tbl(1).target_Level_ID := cr_t.target_level_id;
1630 l_Actual_Tbl(1).Actual := l_actual;
1631 l_Actual_Tbl(1).dim1_Level_value_ID := cr_t.dim1_level_value_id;
1632 l_Actual_Tbl(1).target_level_name := cr_t.target_level_name;
1633 BIS_ACTUAL_PUB.POST_ACTUAL
1634 ( p_api_version => 1.0
1635 ,p_Actual_Rec => l_actual_Tbl(1)
1636 ,x_return_status => l_return_status
1637 ,x_msg_count => l_msg_count
1638 ,x_msg_data => l_msg_data
1639 ,x_error_tbl => l_error_tbl
1640 );
1641
1642 -- We're not on target....
1643 IF l_actual <> l_target THEN
1644 -- Check if actual is within the first range
1645 IF l_actual NOT BETWEEN
1646 cr_t.range2_low AND cr_t.range2_High
1647 AND l_actual BETWEEN
1648 cr_t.range1_low AND cr_t.range1_High
1649 THEN
1650 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE1');
1651 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1652 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1653 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1654 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1655 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1656 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1657 l_message := FND_MESSAGE.GET;
1658 l_notify_resp := cr_t.Notify_Resp1_short_name;
1659 l_responsibility_id := cr_t.Notify_Resp1_ID;
1660 -- dbms_output.put_line('In First Range' || l_message);
1661 -- Check if actual is within the second range
1662 ELSIF l_actual NOT BETWEEN
1663 cr_t.range1_low AND cr_t.range1_High
1664 AND l_actual BETWEEN
1665 cr_t.range2_low AND cr_t.range2_High
1666 THEN
1667 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE2');
1668 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1669 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1670 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1671 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1672 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1673 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1674 l_message := FND_MESSAGE.GET;
1675 l_notify_resp := cr_t.Notify_Resp1_short_name;
1676 l_responsibility_id := cr_t.Notify_Resp1_ID;
1677 --dbms_output.put_line('In Second Range');
1678 -- Check if actual is within the third range
1679 ELSIF l_actual NOT BETWEEN
1680
1681 cr_t.range2_low AND cr_t.range2_High
1682 AND l_actual BETWEEN
1683 cr_t.range3_low AND cr_t.range3_High
1684 THEN
1685 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE3');
1686 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1687 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1688 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1689 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1690 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1691 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1692 l_message := FND_MESSAGE.GET;
1693 l_notify_resp := cr_t.Notify_Resp2_short_name;
1694 l_responsibility_id := cr_t.Notify_Resp2_ID;
1695 --dbms_output.put_line('In Third Range');
1696 -- Check if actual is outside the third range
1697 ELSIF l_actual NOT BETWEEN
1698 cr_t.range3_low AND cr_t.range3_High
1699 AND l_actual > l_target
1700 THEN
1701 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_OFFRANGE');
1702 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1703 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1704 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1705 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1706 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1707 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1708 l_message := FND_MESSAGE.GET;
1709 l_notify_resp := cr_t.Notify_Resp3_short_name;
1710 l_responsibility_id := cr_t.Notify_Resp3_ID;
1711 --dbms_output.put_line('Out of Third Range');
1712 ELSIF l_actual < l_target
1713 THEN
1714 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_EXRANGE');
1715 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1716 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1717 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1718 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1719 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1720 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1721 l_message := FND_MESSAGE.GET;
1722 l_notify_resp := cr_t.Notify_Resp3_short_name;
1723 l_responsibility_id := cr_t.Notify_Resp3_ID;
1724
1725 --dbms_output.put_line('less than First Range');
1726 END IF;
1727
1728 -- We're on target!!
1729 ELSE
1730 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_INRANGE');
1731 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1732 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1733 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1734 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1735 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1736 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1737 l_message := FND_MESSAGE.GET;
1738 l_notify_resp := cr_t.Notify_Resp1_short_name;
1739 l_responsibility_id := cr_t.Notify_Resp1_ID;
1740 END IF;
1741 /*
1742 dbms_output.put_line('Calling Corrective Action' || cr_a.month ||
1743 l_responsibility_id || l_notify_resp || l_workflow_process ||
1744 l_workflow_item_type || l_message );
1745 */
1746
1747 Start_Corrective_Action
1748 ( p_wf_process => l_workflow_process
1749 , p_wf_item_type => l_workflow_item_type
1750 , p_message => l_message
1751 , p_notify_resp => l_notify_resp
1752 , p_report_name1 => l_report
1753 , p_report_param1 => NULL
1754 , p_responsibility_id => l_responsibility_id
1755 , p_msg_subject => l_msg_subject
1756 );
1757 END LOOP; -- ends loop to check targets
1758 END LOOP; -- ends loop to calculate actual
1759
1760 EXCEPTION
1761 WHEN OTHERS THEN
1762 l_message := 'Exception in Abandon Alert Procedure.';
1763 --dbms_output.put_line(l_message);
1764
1765 END BIX_AVGWAIT_ALERT;
1766 /* Alert for Abandon Calls */
1767 PROCEDURE BIX_UTLRATE_ALERT
1768 ( p_target_Level_Short_Name VARCHAR2
1769 )
1770 IS
1771 l_period_set_name VARCHAR2(15);
1772 l_period_name VARCHAR2(15);
1773 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
1774 l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
1775 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1776 l_actual NUMBER;
1777 l_target NUMBER;
1778 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
1779 l_Actual_Tbl BIS_ACTUAL_PUB.Actual_Tbl_Type;
1780 l_workflow_item_type VARCHAR2(8);
1781 l_workflow_process VARCHAR2(30);
1782 l_notify_resp VARCHAR2(100);
1783 l_responsibility_ID NUMBER;
1784 l_message VARCHAR2(250);
1785 l_report VARCHAR2(250);
1786 l_organization_ID NUMBER;
1787 l_organization_tbl BIS_POSTACTUAL.t_orgTable;
1788 l_msg_data VARCHAR2(250);
1789 l_msg_count NUMBER;
1790 l_msg_subject VARCHAR2(300);
1791 l_return_status VARCHAR2(1);
1792 i NUMBER := 0;
1793 l_center VARCHAR2(200);
1794 CURSOR cr_actual IS
1795 Select round(sum(talk_time)/sum(talk_time + idle_time) * 100, 2) utilization_rate,
1796 to_char(hour,'MON-YYYY') month,
1797 interaction_center_id center_id
1798 from bix_sum_grp_cls
1799 group by to_char(hour,'MON-YYYY'), interaction_center_id;
1800 CURSOR cr_center(p_center_id NUMBER) IS
1801 SELECT call_center_name center_name
1802 FROM bix_call_center_v
1803 WHERE call_center_id = p_center_id;
1804
1805 CURSOR cr_target(p_organization_id VARCHAR2,
1806 p_center_id VARCHAR2, p_time VARCHAR2)
1807 IS
1808 SELECT tv.target_level_short_name
1809 , tv.target_level_name
1810 , tv.target_level_id
1811 , tv.plan_name
1812 , tv.org_level_value_id
1813 , tv.time_level_value_id
1814 , tv.target
1815 -- , tv.computed_target_short_name
1816 , tv.range1_low
1817 , tv.range1_high
1818 , tv.range2_low
1819 , tv.range2_high
1820 , tv.range3_low
1821 , tv.range3_high
1822 , tv.notify_resp1_id
1823 , tv.notify_resp1_short_name
1824 , tv.notify_resp2_id
1825 , tv.notify_resp2_short_name
1826 , tv.notify_resp3_id
1827 , tv.notify_resp3_short_name
1828 , tv.dim1_level_value_id
1829 FROM BISFV_TARGETS tv
1830 WHERE tv.target_level_short_name = p_target_level_short_name
1831 AND tv.time_level_value_id like '%' || p_time || '%'
1832 AND tv.org_level_value_id = p_organization_id
1833 AND tv.dim1_level_value_name = p_center_id;
1834
1835 BEGIN
1836
1837 l_target_level_rec.target_Level_Short_Name
1838 := p_target_Level_Short_Name;
1839 -- l_period_set_name := p_time_period;
1840 -- l_period_name := p_time_period;
1841 l_report := 'BIXUTLM0';
1842 FND_MESSAGE.SET_NAME('BIX', 'BIX_UTIL_RATE');
1843 l_msg_subject := FND_MESSAGE.GET;
1844 IF l_msg_subject is NULL then
1845 l_msg_subject := 'Utilization Rate PMF Notification';
1846 END IF;
1847 --dbms_output.put_line('In First Range' || l_message);
1848 l_organization_id := -1;
1849 -- Get the workflow process
1850 SELECT workflow_item_type, workflow_process_short_name
1851 INTO l_workflow_item_type, l_workflow_process
1852 FROM bisbv_target_levels
1853 WHERE target_level_short_name = p_target_level_short_name;
1854
1855 -- Get the KPIs users have selected to monitor on their homepage
1856 BIS_ACTUAL_PUB.Retrieve_User_Selections
1857 ( p_api_version => 1.0
1858 ,p_Target_Level_Rec => l_Target_Level_Rec
1859 ,x_Indicator_Region_Tbl => l_user_selection_Tbl
1860 ,x_return_status => l_return_status
1861 ,x_msg_count => l_msg_count
1862 ,x_msg_data => l_msg_data
1863 ,x_error_Tbl => l_error_tbl
1864 );
1865
1866 -- Calculate Actual for all Months for All Call Centers
1867 FOR cr_a in cr_actual LOOP
1868
1869 l_actual := cr_a.utilization_rate;
1870 for cr_c in cr_center(cr_a.center_id) LOOP
1871 l_center := cr_c.center_name;
1872 end loop;
1873 -- Post actual value for only those KPIs users have selected.
1874 -- FOR i IN 1..l_user_selection_Tbl.COUNT LOOP
1875 -- IF l_user_selection_tbl(i).organization_id = l_organization_id THEN
1876 -- END IF;
1877 -- END LOOP;
1878
1879 -- Check for exceptions and start corrective action
1880
1881 FOR cr_t IN cr_target(l_organization_id, to_char(cr_a.center_id), cr_a.month
1882 ) LOOP
1883
1884 IF cr_t.target IS NULL THEN
1885 -- l_target := Get_Target(cr.computed_target_short_name);
1886 NULL;
1887 ELSE
1888 l_target := cr_t.target;
1889 END IF;
1890 l_Actual_Tbl(1).target_Level_Short_Name
1891 := p_target_level_short_name;
1892 l_Actual_Tbl(1).Org_Level_value_ID := l_organization_id;
1893 l_Actual_Tbl(1).time_Level_value_ID := cr_t.time_level_value_id;
1894 l_Actual_Tbl(1).target_Level_ID := cr_t.target_level_id;
1895 l_Actual_Tbl(1).Actual := l_actual;
1896 l_Actual_Tbl(1).dim1_Level_value_ID := cr_t.dim1_level_value_id;
1897 l_Actual_Tbl(1).target_level_name := cr_t.target_level_name;
1898 BIS_ACTUAL_PUB.POST_ACTUAL
1899 ( p_api_version => 1.0
1900 ,p_Actual_Rec => l_actual_Tbl(1)
1901 ,x_return_status => l_return_status
1902 ,x_msg_count => l_msg_count
1903 ,x_msg_data => l_msg_data
1904 ,x_error_tbl => l_error_tbl
1905 );
1906 -- We're not on target....
1907 IF l_actual <> l_target THEN
1908 -- Check if actual is within the first range
1909 IF l_actual NOT BETWEEN
1910 cr_t.range2_low AND cr_t.range2_High
1911 AND l_actual BETWEEN
1912 cr_t.range1_low AND cr_t.range1_High
1913 THEN
1914 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE1');
1915 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1916 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1917 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1918 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1919 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1920 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1921 l_message := FND_MESSAGE.GET;
1922 l_notify_resp := cr_t.Notify_Resp1_short_name;
1923 l_responsibility_id := cr_t.Notify_Resp1_ID;
1924 -- dbms_output.put_line('In First Range' || l_message);
1925 -- Check if actual is within the second range
1926 ELSIF l_actual NOT BETWEEN
1927 cr_t.range1_low AND cr_t.range1_High
1928 AND l_actual BETWEEN
1929 cr_t.range2_low AND cr_t.range2_High
1930 THEN
1931 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE2');
1932 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1933 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1934 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1935 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1936 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1937 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1938 l_message := FND_MESSAGE.GET;
1939 l_notify_resp := cr_t.Notify_Resp1_short_name;
1940 l_responsibility_id := cr_t.Notify_Resp1_ID;
1941 --dbms_output.put_line('In Second Range');
1942 -- Check if actual is within the third range
1943 ELSIF l_actual NOT BETWEEN
1944
1945 cr_t.range2_low AND cr_t.range2_High
1946 AND l_actual BETWEEN
1947 cr_t.range3_low AND cr_t.range3_High
1948 THEN
1949 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE3');
1950 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1951 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1952 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1953 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1954 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1955 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1956 l_message := FND_MESSAGE.GET;
1957 l_notify_resp := cr_t.Notify_Resp2_short_name;
1958 l_responsibility_id := cr_t.Notify_Resp2_ID;
1959 --dbms_output.put_line('In Third Range');
1960 -- Check if actual is outside the third range
1961 ELSIF l_actual NOT BETWEEN
1962 cr_t.range3_low AND cr_t.range3_High
1963 AND l_actual < l_target
1964 THEN
1965 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_OFFRANGE');
1966 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1967 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1968 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1969 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1970 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1971 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1972 l_message := FND_MESSAGE.GET;
1973 l_notify_resp := cr_t.Notify_Resp3_short_name;
1974 l_responsibility_id := cr_t.Notify_Resp3_ID;
1975 --dbms_output.put_line('Out of Third Range');
1976 ELSIF l_actual > l_target
1977 THEN
1978 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_EXRANGE');
1979 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1980 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1981 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1982 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1983 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1984 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
1985 l_message := FND_MESSAGE.GET;
1986 l_notify_resp := cr_t.Notify_Resp3_short_name;
1987 l_responsibility_id := cr_t.Notify_Resp3_ID;
1988 --dbms_output.put_line('less than First Range');
1989 END IF;
1990
1991 -- We're on target!!
1992 ELSE
1993 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_INRANGE');
1994 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
1995 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
1996 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
1997 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
1998 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
1999 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
2000 l_message := FND_MESSAGE.GET;
2001 l_notify_resp := cr_t.Notify_Resp1_short_name;
2002 l_responsibility_id := cr_t.Notify_Resp1_ID;
2003 END IF;
2004
2005 --dbms_output.put_line('Calling Corrective Action');
2006 /*
2007 dbms_output.put_line('Calling Corrective Action' || cr_a.month ||
2008 l_responsibility_id || l_notify_resp || l_workflow_process ||
2009 l_workflow_item_type || l_message );
2010 */
2011 Start_Corrective_Action
2012 ( p_wf_process => l_workflow_process
2013 , p_wf_item_type => l_workflow_item_type
2014 , p_message => l_message
2015 , p_notify_resp => l_notify_resp
2016 , p_report_name1 => l_report
2017 , p_report_param1 => NULL
2018 , p_responsibility_id => l_responsibility_id
2019 , p_msg_subject => l_msg_subject
2020 );
2021 END LOOP; -- ends loop to check targets
2022 END LOOP; -- ends loop to calculate actual
2023
2024 EXCEPTION
2025 WHEN OTHERS THEN
2026 l_message := 'Exception in Utilization Rate Alert Procedure.';
2027 --dbms_output.put_line(l_message);
2028
2029 END BIX_UTLRATE_ALERT;
2030 /* Alert procedure for Calls Answered */
2031 PROCEDURE BIX_CALLSANS_ALERT
2032 ( p_target_Level_Short_Name VARCHAR2
2033 )
2034 IS
2035 l_period_set_name VARCHAR2(15);
2036 l_period_name VARCHAR2(15);
2037 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
2038 l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
2039 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2040 l_actual NUMBER;
2041 l_target NUMBER;
2042 l_Target_Rec BIS_TARGET_PUB.Target_Rec_Type;
2043 l_Actual_Tbl BIS_ACTUAL_PUB.Actual_Tbl_Type;
2044 l_workflow_item_type VARCHAR2(8);
2045 l_workflow_process VARCHAR2(30);
2046 l_notify_resp VARCHAR2(100);
2047 l_responsibility_ID NUMBER;
2048 l_message VARCHAR2(250);
2049 l_report VARCHAR2(250);
2050 l_organization_ID NUMBER;
2051 l_organization_tbl BIS_POSTACTUAL.t_orgTable;
2052 l_msg_data VARCHAR2(250);
2053 l_msg_count NUMBER;
2054 l_msg_subject VARCHAR2(300);
2055 l_return_status VARCHAR2(1);
2056 i NUMBER := 0;
2057 l_center VARCHAR2(200);
2058 CURSOR cr_actual IS
2059 Select sum(interactions_answered_live) calls_ansd,
2060 to_char(hour,'MON-YYYY') month,
2061 interaction_center_id center_id
2062 from bix_sum_grp_cls
2063 group by to_char(hour,'MON-YYYY'), interaction_center_id;
2064 CURSOR cr_center(p_center_id NUMBER) IS
2065 SELECT call_center_name center_name
2066 FROM bix_call_center_v
2067 WHERE call_center_id = p_center_id;
2068 CURSOR cr_target(p_organization_id VARCHAR2,
2069 p_center_id VARCHAR2, p_time VARCHAR2)
2070 IS
2071 SELECT tv.target_level_short_name
2072 , tv.target_level_name
2073 , tv.target_level_id
2074 , tv.plan_name
2075 , tv.org_level_value_id
2076 , tv.time_level_value_id
2077 , tv.target
2078 -- , tv.computed_target_short_name
2079 , tv.range1_low
2080 , tv.range1_high
2081 , tv.range2_low
2082 , tv.range2_high
2083 , tv.range3_low
2084 , tv.range3_high
2085 , tv.notify_resp1_id
2086 , tv.notify_resp1_short_name
2087 , tv.notify_resp2_id
2088 , tv.notify_resp2_short_name
2089 , tv.notify_resp3_id
2090 , tv.notify_resp3_short_name
2091 , tv.dim1_level_value_id
2092 FROM BISFV_TARGETS tv
2093 WHERE tv.target_level_short_name = p_target_level_short_name
2094 AND tv.time_level_value_id like '%' || p_time || '%'
2095 AND tv.org_level_value_id = p_organization_id
2096 AND tv.dim1_level_value_name = p_center_id;
2097
2098 BEGIN
2099 --dbms_output.put_line('In Calls Answered Rate Alert ');
2100 l_target_level_rec.target_Level_Short_Name
2101 := p_target_Level_Short_Name;
2102 -- l_period_set_name := p_time_period;
2103 -- l_period_name := p_time_period;
2104 l_report := 'BIXANCT0';
2105 FND_MESSAGE.SET_NAME('BIX', 'BIX_CALLS_ANSWERED');
2106 l_msg_subject := FND_MESSAGE.GET;
2107 IF l_msg_subject is NULL then
2108 l_msg_subject := 'Calls Answered PMF Notification';
2109 END IF;
2110 l_organization_id := -1;
2111 -- Get the workflow process
2112 SELECT workflow_item_type, workflow_process_short_name
2113 INTO l_workflow_item_type, l_workflow_process
2114 FROM bisbv_target_levels
2115 WHERE target_level_short_name = p_target_level_short_name;
2116
2117 -- Get the KPIs users have selected to monitor on their homepage
2118 BIS_ACTUAL_PUB.Retrieve_User_Selections
2119 ( p_api_version => 1.0
2120 ,p_Target_Level_Rec => l_Target_Level_Rec
2121 ,x_Indicator_Region_Tbl => l_user_selection_Tbl
2122 ,x_return_status => l_return_status
2123 ,x_msg_count => l_msg_count
2124 ,x_msg_data => l_msg_data
2125 ,x_error_Tbl => l_error_tbl
2126 );
2127
2128 -- Calculate Actual for all Months for All Call Centers
2129 FOR cr_a in cr_actual LOOP
2130 l_actual := cr_a.calls_ansd;
2131 for cr_c in cr_center(cr_a.center_id) LOOP
2132 l_center := cr_c.center_name;
2133 end loop;
2134 -- Check for exceptions and start corrective action
2135 FOR cr_t IN cr_target(l_organization_id, to_char(cr_a.center_id),cr_a.month) LOOP
2136 IF cr_t.target IS NULL THEN
2137 -- l_target := Get_Target(cr.computed_target_short_name);
2138 NULL;
2139 ELSE
2140 l_target := cr_t.target;
2141 END IF;
2142 l_Actual_Tbl(1).target_Level_Short_Name
2143 := p_target_level_short_name;
2144 l_Actual_Tbl(1).Org_Level_value_ID := l_organization_id;
2145 l_Actual_Tbl(1).time_Level_value_ID := cr_t.time_level_value_id;
2146 l_Actual_Tbl(1).target_Level_ID := cr_t.target_level_id;
2147 l_Actual_Tbl(1).Actual := l_actual;
2148 l_Actual_Tbl(1).dim1_Level_value_ID := cr_t.dim1_level_value_id;
2149 L_Actual_Tbl(1).target_level_name := cr_t.target_level_name;
2150 BIS_ACTUAL_PUB.POST_ACTUAL
2151 ( p_api_version => 1.0
2152 ,p_Actual_Rec => l_actual_Tbl(1)
2153 ,x_return_status => l_return_status
2154 ,x_msg_count => l_msg_count
2155 ,x_msg_data => l_msg_data
2156 ,x_error_tbl => l_error_tbl
2157 );
2158 -- We're not on target....
2159 IF l_actual <> l_target THEN
2160 -- Check if actual is within the first range
2161 IF l_actual NOT BETWEEN
2162 cr_t.range2_low AND cr_t.range2_High
2163 AND l_actual BETWEEN
2164 cr_t.range1_low AND cr_t.range1_High
2165 THEN
2166 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE1');
2167 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
2168 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
2169 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
2170 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
2171 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
2172 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
2173 l_message := FND_MESSAGE.GET;
2174 l_notify_resp := cr_t.Notify_Resp1_short_name;
2175 l_responsibility_id := cr_t.Notify_Resp1_ID;
2176 --dbms_output.put_line('In First Range');
2177 -- Check if actual is within the second range
2178 ELSIF l_actual NOT BETWEEN
2179 cr_t.range1_low AND cr_t.range1_High
2180 AND l_actual BETWEEN
2181 cr_t.range2_low AND cr_t.range2_High
2182 THEN
2183 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE2');
2184 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
2185 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
2186 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
2187 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
2188 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
2189 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
2190 l_message := FND_MESSAGE.GET;
2191 l_notify_resp := cr_t.Notify_Resp1_short_name;
2192 l_responsibility_id := cr_t.Notify_Resp1_ID;
2193 --dbms_output.put_line('In Second Range');
2194 -- Check if actual is within the third range
2195 ELSIF l_actual NOT BETWEEN
2196 cr_t.range2_low AND cr_t.range2_High
2197 AND l_actual BETWEEN
2198 cr_t.range3_low AND cr_t.range3_High
2199 THEN
2200 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_RANGE3');
2201 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
2202 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
2203 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
2204 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
2205 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
2206 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
2207 l_message := FND_MESSAGE.GET;
2208 l_notify_resp := cr_t.Notify_Resp2_short_name;
2209 l_responsibility_id := cr_t.Notify_Resp2_ID;
2210 --dbms_output.put_line('In Third Range');
2211 -- Check if actual is outside the third range
2212 ELSIF l_actual NOT BETWEEN
2213 cr_t.range3_low AND cr_t.range3_High
2214 AND l_actual > l_target
2215 THEN
2216 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_OFFRANGE');
2217 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
2218 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
2219 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
2220 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
2221 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
2222 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
2223 l_message := FND_MESSAGE.GET;
2224 l_notify_resp := cr_t.Notify_Resp3_short_name;
2225 l_responsibility_id := cr_t.Notify_Resp3_ID;
2226 --dbms_output.put_line('Out of Third Range');
2227 ELSIF l_actual < l_target
2228 THEN
2229 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_EXRANGE');
2230 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
2231 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
2232 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
2233 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
2234 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
2235 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
2236 l_message := FND_MESSAGE.GET;
2237 l_notify_resp := cr_t.Notify_Resp3_short_name;
2238 l_responsibility_id := cr_t.Notify_Resp3_ID;
2239 END IF;
2240 -- We're on target!!
2241 ELSE
2242 FND_MESSAGE.SET_NAME('BIX', 'BIX_PMF_INRANGE');
2243 FND_MESSAGE.SET_TOKEN('BIX_TARGET_NAME', cr_t.target_level_name);
2244 FND_MESSAGE.SET_TOKEN('BIX_CENTER', l_center);
2245 FND_MESSAGE.SET_TOKEN('BIX_TARGET', l_target);
2246 FND_MESSAGE.SET_TOKEN('BIX_ACTUAL', l_actual);
2247 FND_MESSAGE.SET_TOKEN('BIX_PLAN', cr_t.plan_name);
2248 FND_MESSAGE.SET_TOKEN('BIX_TIME', cr_a.month);
2249 l_message := FND_MESSAGE.GET;
2250 l_notify_resp := cr_t.Notify_Resp1_short_name;
2251 l_responsibility_id := cr_t.Notify_Resp1_ID;
2252 END IF;
2253 Start_Corrective_Action
2254 ( p_wf_process => l_workflow_process
2255 , p_wf_item_type => l_workflow_item_type
2256 , p_message => l_message
2257 , p_notify_resp => l_notify_resp
2258 , p_report_name1 => l_report
2259 , p_report_param1 => NULL
2260 , p_responsibility_id => l_responsibility_id
2261 , p_msg_subject => l_msg_subject
2262 );
2263 END LOOP; -- ends loop to check targets
2264 END LOOP; -- ends loop to calculate actual
2265 EXCEPTION
2266 WHEN OTHERS THEN
2267 l_message := 'Exception in Calls Answered Alert Procedure.';
2268 --dbms_output.put_line(l_message);
2269
2270 END BIX_CALLSANS_ALERT;
2271 END BIX_ALERT;