1 PACKAGE BODY CCT_ServerDataInput_PUB AS
2 /* $Header: ccticiib.pls 120.2 2011/06/29 11:03:46 rgandhi ship $ */
3
4 /* start of comments
5 Record data for an Outbound (time, site, campaign, list) tuple combination.
6 Api name : Advanced_Outbound_Input
7 Type : Public
8 Pre-regs : None
9 Function : Record data to ICI Advanced Outbound table
10 Parameters :
11 :p_api_version IN NUMBER Required
12 Used by the API to compare the version numbers of incoming
13 calls to its current version number, returns an unexpected error
14 if they are incompatible.
15
16 :p_init_msg_list IN VARCHAR2 Optional
17 Default = FND_API.G_FALSE
18 The API message list must be initialized every time a program
19 calls an API.
20
21 :p_commit IN VARCHAR2 Optional
22 Default = FND_API.G_FALSE
23 Before returning to its caller, an API should check the value
24 of p_commit. If it is set to TRUE it should commit its work.
25
26 :p_validation_level IN NUMBER Optional
27 Default = FND_API.G_VALID_LEVEL_FULL
28 Determins which validation steps should be executed and which
29 should be skipped. Public APIs by definition have to perform FULL
30 validation on all the data passed to them
31
32 : x_return_status OUT VARCHAR2
33 Returns the result of all operations performed by the API
34
35 : x_msg_count OUT NUMBER
36 Holds the number of messages in the API message list.
37
38 : x_msg_data OUT VARCHAR2
39 Holds the message in an encoded format.
40
41 : p_time IN DATE Optional
42 Default = NULL
43 The time, to the minute, for this data. Input will be truncated
44 to the minute, and the data must be for the minute {yyyy-mon-dd
45 hh:mm:00.0} to {yyyy-mon-dd hh:mm:59.9}, local to the advanced
46 outbound server.
47
48 : p_site_id IN NUMBER Optional
49 Default = NULL
50 ID of the site. This is an index to the site definition table.
51
52 : p_site_name IN VARCHAR2 Optional
53 Default = NULL
54 Name of the site.
55
56 : p_campaign_id IN NUMBER Optional
57 Default = NULL
58 ID of the campaign. This is an index to the campaign definition table.
59
60 : p_campaign_name IN VARCHAR2 Optional
61 Default = NULL
62 Name of the campaign.
63
64 : p_list_id IN NUMBER Optional
65 Default = NULL
66 ID of the list. This is an index to the list definition table.
67
68 : p_list_name IN VARCHAR2 Optional
69 Default = NULL
70 Name of the list.
71
72 : p_busy_count IN NUMBER Optional
73 Default = NULL
74 Total occurences in this minute.
75
76 : p_connect_count IN NUMBER Optional
77 Default = NULL
78 Total occurences in this minute.
79
80 : p_answering_machine_count IN NUMBER Optional
81 Default = NULL
82 Total occurences in this minute.
83
84 : p_modem_count IN NUMBER Optional
85 Default = NULL
86 Total occurences in this minute.
87
88 : p_sit_count IN NUMBER Optional
89 Default = NULL
90 Total occurences in this minute. Standard identification tone?
91
92 : p_rna_count IN NUMBER Optional
93 Default = NULL
94 Total occurences in this minute. Ring no answer.
95
96 : p_other_count IN NUMBER Optional
97 Default = NULL
98 Total occurences in this minute.
99
100 : p_predictive_dials IN NUMBER Optional
101 Default = NULL
102 Total occurences in this minute. Dials in predictive mode.
103
104 : p_progressive_dials IN NUMBER Optional
105 Default = NULL
106 Total occurences in this minute. Dials in progressive mode.
107
108 : p_preview_dials IN NUMBER Optional
109 Default = NULL
110 Total occurences in this minute. Dials in preview mode.
111
112 : p_preview_time IN NUMBER Optional
113 Default = NULL
114 Total time agents spent previewing calls, this minute. In seconds.
115
116 : p_withdrawn_dials IN NUMBER Optional
117 Default = NULL
118 Totals dials that were later withdrawn, this minute.
119
120 : p_wait_time_average IN NUMBER Optional
121 Default = NULL
122 Average idle time for agents this minute. In seconds.
123
124 : p_wait_time_std_dev IN NUMBER Optional
125 Default = NULL
126 Standard deviation of idle time for agents this minute. In seconds.
127
128 : p_wait_time_cumulative_avg IN NUMBER Optional
129 Default = NULL
130 Average idle time for agents cumulative for today. In seconds.
131
132 : p_wait_time_cumulative_stddev IN NUMBER Optional
133 Default = NULL
134 Standard deviation of idle time for agents cumulative for today.
135 In seconds.
136
137 : p_wait_time_minimum IN NUMBER Optional
138 Default = NULL
139 Minimum idle time for agents this minute. In seconds.
140
141 : p_wait_time_maximum IN NUMBER Optional
142 Default = NULL
143 Maximum idle time for agents this minute. In seconds.
144
145 : p_wait_time_total IN NUMBER Optional
146 Default = NULL
147 Total idle time for agents this minute. In seconds.
148
149 : p_number_agents_predictive IN NUMBER Optional
150 Default = NULL
151 Number of agents working (predictive), sampled sometime this
152 minute.
153
154 : p_number_agents_outbound IN NUMBER Optional
155 Default = NULL
156 Number of agents working (outbound), sampled sometime this
157 minute. This number includes p_number_agents_predictive.
158
159 : p_number_working_dialers IN NUMBER Optional
160 Default = NULL
161 Number of dialers working, sampled sometime this minute.
162
163 : p_abandon_count IN NUMBER Optional
164 Default = NULL
165 Total occurences in this minute.
166
167 : p_abandon_percentage IN NUMBER Optional
168 Default = NULL
169 Percentage in this minute.
170
171 : p_callback_count IN NUMBER Optional
172 Default = NULL
173 Total occurences in this minute.
174
175 : p_callback_percentage IN NUMBER Optional
176 Default = NULL
177 Percentage in this minute.
178
179 : p_outcome_1_count IN NUMBER Optional
180 Default = NULL
181 Total occurences in this minute. User-defined outcome 1.
182
183 : p_outcome_2_count IN NUMBER Optional
184 Default = NULL
185 Total occurences in this minute. User-defined outcome 2.
186
187 : p_outcome_3_count IN NUMBER Optional
188 Default = NULL
189 Total occurences in this minute. User-defined outcome 3.
190
191 : p_records_start_of_day IN NUMBER Optional
192 Default = NULL
193 Constant number.
194
195 : p_records_remaining IN NUMBER Optional
196 Default = NULL
197 Number of records remaining in list. Sampled sometime this minute.
198
199 : p_predicted_exhaustion_date IN DATE Optional
200 Default = NULL
201 Estimate until the list is exhausted.
202
203 : p_recs_to_be_released_in_1 IN NUMBER Optional
204 Default = NULL
205 Estimated number of records to be released in the next minute.
206
207 : p_recs_to_be_released_in_5 IN NUMBER Optional
208 Default = NULL
209 Estimated number of records to be released in the next 5 minutes.
210
211 : p_recs_to_be_released_in_15 IN NUMBER Optional
212 Default = NULL
213 Estimated number of records to be released in the next 15 minutes.
214
215 : p_recs_to_be_released_in_60 IN NUMBER Optional
216 Default = NULL
217 Estimated number of records to be released in the next hour.
218
219 Version : Current Version 1.0
220 Previous Version n/a
221 Initial Version 1.0
222
223 end of comments */
224
225 PROCEDURE Advanced_Outbound_Input
226 ( p_api_version IN NUMBER,
227 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
228 p_commit IN VARCHAR2 := FND_API.G_FALSE,
229 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
230 x_return_status OUT nocopy VARCHAR2,
231 x_msg_count OUT nocopy NUMBER,
232 x_msg_data OUT nocopy VARCHAR2,
233
234 p_time IN DATE := NULL,
235
236 p_site_id IN NUMBER := NULL,
237 p_site_name IN VARCHAR2 := NULL,
238 p_campaign_id IN NUMBER := NULL,
239 p_campaign_name IN VARCHAR2 := NULL,
240 p_list_id IN NUMBER := NULL,
241 p_list_name IN VARCHAR2 := NULL,
242
243 p_busy_count IN NUMBER := NULL,
244 p_connect_count IN NUMBER := NULL,
245 p_answering_machine_count IN NUMBER := NULL,
246 p_modem_count IN NUMBER := NULL,
247 p_sit_count IN NUMBER := NULL,
248 p_rna_count IN NUMBER := NULL,
249 p_other_count IN NUMBER := NULL,
250
251 p_predictive_dials IN NUMBER := NULL,
252 p_progressive_dials IN NUMBER := NULL,
253 p_preview_dials IN NUMBER := NULL,
254 p_preview_time IN NUMBER := NULL,
255 p_withdrawn_dials IN NUMBER := NULL,
256
257 p_wait_time_average IN NUMBER := NULL,
258 p_wait_time_std_dev IN NUMBER := NULL,
259 p_wait_time_cumulative_avg IN NUMBER := NULL,
260 p_wait_time_cumulative_stddev IN NUMBER := NULL,
261 p_wait_time_minimum IN NUMBER := NULL,
262 p_wait_time_maximum IN NUMBER := NULL,
263 p_wait_time_total IN NUMBER := NULL,
264
265 p_number_agents_predictive IN NUMBER := NULL,
266 p_number_agents_outbound IN NUMBER := NULL,
267 p_number_working_dialers IN NUMBER := NULL,
268 p_abandon_count IN NUMBER := NULL,
269 p_abandon_percentage IN NUMBER := NULL,
270 p_callback_count IN NUMBER := NULL,
271 p_callback_percentage IN NUMBER := NULL,
272
273 p_outcome_1_count IN NUMBER := NULL,
274 p_outcome_2_count IN NUMBER := NULL,
275 p_outcome_3_count IN NUMBER := NULL,
276
277 p_records_start_of_day IN NUMBER := NULL,
278 p_records_remaining IN NUMBER := NULL,
279 p_predicted_exhaustion_date IN DATE := NULL,
280 p_recs_to_be_released_in_1 IN NUMBER := NULL,
281 p_recs_to_be_released_in_5 IN NUMBER := NULL,
282 p_recs_to_be_released_in_15 IN NUMBER := NULL,
283 p_recs_to_be_released_in_60 IN NUMBER := NULL
284 ) IS
285
286 l_api_name CONSTANT VARCHAR2(30) := 'Advanced_Outbound_Input';
287 l_api_version CONSTANT NUMBER := 1.0;
288 l_encoded VARCHAR2(1) := FND_API.G_FALSE;
289
290 l_site_id NUMBER := -1;
291 l_campaign_id NUMBER := -1;
292 l_list_id NUMBER := -1;
293
294 BEGIN
295 -- Standard Start of API savepoint
296 SAVEPOINT Advanced_Outbound_Input_PUB;
297
298 -- Standard call to check for call compatibility
299 IF NOT FND_API.Compatible_API_Call ( l_api_version,
300 p_api_version,
301 l_api_name,
302 G_PKG_NAME)
303 THEN
304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
305 END IF;
306
307 -- initialize message list if p_init_msg_list is set to TRUE
308 IF FND_API.To_Boolean( p_init_msg_list ) THEN
309 FND_MSG_PUB.initialize;
310 END IF;
311
312 -- Initialize the API return status to success
313 x_return_status := FND_API.G_RET_STS_SUCCESS;
314
315 -- API body
316
317 -- Insert record if p_time is given
318 BEGIN
319 IF p_time IS NOT NULL
320 THEN
321 -- Check p_site_id for valid value
322 IF p_site_id IS NOT NULL
323 THEN
324 l_site_id := p_site_id;
325 ELSIF p_site_name IS NOT NULL
326 THEN
327 DECLARE
328 CURSOR server_group_id_cur (v_site_name IN VARCHAR2)
329 IS
330 SELECT server_group_id
331 FROM ieo_svr_groups
332 WHERE UPPER (group_name) = UPPER (v_site_name)
333 ORDER BY server_group_id;
334 BEGIN
335 OPEN server_group_id_cur (p_site_name);
336 FETCH server_group_id_cur INTO l_site_id;
337 CLOSE server_group_id_cur;
338 END;
339 END IF;
340
341 -- Check p_campaign_id for valid value
342 IF p_campaign_id IS NOT NULL
343 THEN
344 l_campaign_id := p_campaign_id;
345 ELSIF p_campaign_name IS NOT NULL
346 THEN
347 DECLARE
348 CURSOR campaign_id_cur (v_campaign_name IN VARCHAR2)
349 IS
350 SELECT campaign_id
351 FROM ams_campaigns_all_tl
352 WHERE UPPER (campaign_name) = UPPER (v_campaign_name)
353 ORDER BY campaign_id;
354 BEGIN
355 OPEN campaign_id_cur (p_campaign_name);
356 FETCH campaign_id_cur INTO l_campaign_id;
357 CLOSE campaign_id_cur;
358 END;
359 END IF;
360
361 -- Check p_list_id for valid value
362 IF p_list_id IS NOT NULL
363 THEN
364 l_list_id := p_list_id;
365 ELSIF p_list_name IS NOT NULL
366 THEN
367 DECLARE
368 CURSOR list_header_id_cur (v_list_name IN VARCHAR2)
369 IS
370 SELECT list_header_id
371 FROM ams_list_headers_all
372 WHERE UPPER (list_name) = UPPER (v_list_name)
373 ORDER BY list_header_id;
374 BEGIN
375 OPEN list_header_id_cur (p_list_name);
376 FETCH list_header_id_cur INTO l_list_id;
377 CLOSE list_header_id_cur;
378 END;
379 END IF;
380
381 -- Bug 12597961
382
383 /* INSERT INTO bix_server_cp
384 (server_cp_id, minute, site_id, campaign_id, list_id,
385 busy_counts, connect_counts, answering_machine_counts,
386 modem_counts, sit_counts, rna_counts, other_counts,
387 predictive_dials, progressive_dials, preview_dials,
388 preview_time, withdrawn_dials,
389 average_wait_time, std_dev_wait_time,
390 average_cumulative_wait_time, std_dev_cumulative_wait_time,
391 minimum_wait_time, maximum_wait_time, total_wait_time,
392 number_agents_predictive, number_agents_outbound,
393 number_working_dialers, number_abandons, abandon_percentage,
394 number_callbacks, callback_percentage, dials_per_minute,
395 number_calls_outcome_1, number_calls_outcome_2,
396 number_calls_outcome_3,
397 number_records_start_of_day, number_records_remaining,
398 predicted_exhaustion_date, num_recs_to_be_released_next_1,
399 num_rec_to_be_released_next_5, num_rec_to_be_released_next_15,
400 num_rec_to_be_released_next_60)
401 SELECT bix_server_cp_s.nextval, trunc (p_time, 'MI'), l_site_id,
402 l_campaign_id, l_list_id,
403 p_busy_count, p_connect_count, p_answering_machine_count,
404 p_modem_count, p_sit_count, p_rna_count, p_other_count,
405 p_predictive_dials, p_progressive_dials, p_preview_dials,
406 p_preview_time, p_withdrawn_dials,
407 p_wait_time_average, p_wait_time_std_dev,
408 p_wait_time_cumulative_avg, p_wait_time_cumulative_stddev,
409 p_wait_time_minimum, p_wait_time_maximum, p_wait_time_total,
410 p_number_agents_predictive, p_number_agents_outbound,
411 p_number_working_dialers, p_abandon_count, p_abandon_percentage,
412 p_callback_count, p_callback_percentage,
413 p_predictive_dials + p_progressive_dials + p_preview_dials,
414 p_outcome_1_count, p_outcome_2_count, p_outcome_3_count,
415 p_records_start_of_day, p_records_remaining,
416 p_predicted_exhaustion_date,
417 p_recs_to_be_released_in_1, p_recs_to_be_released_in_5,
418 p_recs_to_be_released_in_15, p_recs_to_be_released_in_60
419 FROM dual; moved the comment 12699361 */
420 END IF;
421 EXCEPTION
422 WHEN OTHERS THEN
423 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
424 THEN
425 FND_MESSAGE.SET_NAME('CCT', 'CCT_SDI_AO_IN_INSERT_ERROR');
426 FND_MSG_PUB.Add;
427 END IF;
428
429 RAISE FND_API.G_EXC_ERROR;
430 END;
431
432 -- Signify Success
433 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
434 THEN
435 FND_MESSAGE.SET_NAME('CCT', 'CCT_SDI_AO_IN_RECORD_INSERTED');
436 FND_MSG_PUB.Add;
437 END IF;
438
439 -- End of API body
440
441 -- Standard check of p_commit
442 IF FND_API.To_Boolean( p_commit ) THEN
443 COMMIT WORK;
444 END IF;
445
446 -- Standard call to get message count and if count is 1, get message info
447 FND_MSG_PUB.Count_And_Get
448 ( p_encoded => l_encoded,
449 p_count => x_msg_count,
450 p_data => x_msg_data
451 );
452
453 EXCEPTION
454 WHEN FND_API.G_EXC_ERROR THEN
455 ROLLBACK TO Advanced_Outbound_Input_PUB;
456 x_return_status := FND_API.G_RET_STS_ERROR;
457
458 FND_MSG_PUB.Count_And_Get
459 ( p_encoded => l_encoded,
460 p_count => x_msg_count,
461 p_data => x_msg_data
462 );
463
464 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
465 ROLLBACK TO Advanced_Outbound_Input_PUB;
466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
467
468 FND_MSG_PUB.Count_And_Get
469 ( p_encoded => l_encoded,
470 p_count => x_msg_count,
471 p_data => x_msg_data
472 );
473
474 WHEN OTHERS THEN
475 ROLLBACK TO Advanced_Outbound_Input_PUB;
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477
478 IF FND_MSG_PUB.Check_Msg_Level
479 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
480
481 THEN
482 FND_MSG_PUB.Add_Exc_Msg
483 ( p_pkg_name => G_PKG_NAME,
484 p_procedure_name => l_api_name,
485 p_error_text => 'G_MSG_LVL_UNEXP_ERROR'
486 );
487 END IF;
488
489 FND_MSG_PUB.Count_And_Get
490 ( p_encoded => l_encoded,
491 p_count => x_msg_count,
492 p_data => x_msg_data
493 );
494
495 END Advanced_Outbound_Input;
496
497 /* ************************************************** */
498
499 /* start of comments
500 Record data for a Blending (time, site, media type, LOS category)
501 tuple combination.
502 Api name : Interaction_Blending_Input
503 Type : Public
504 Pre-regs : None
505 Function : Record data to ICI Interaction Blending table
506 Parameters :
507 :p_api_version IN NUMBER Required
508 Used by the API to compare the version numbers of incoming
509 calls to its current version number, returns an unexpected error
510 if they are incompatible.
511
512 :p_init_msg_list IN VARCHAR2 Optional
513 Default = FND_API.G_FALSE
514 The API message list must be initialized every time a program
515 calls an API.
516
517 :p_commit IN VARCHAR2 Optional
518 Default = FND_API.G_FALSE
519 Before returning to its caller, an API should check the value
520 of p_commit. If it is set to TRUE it should commit its work.
521
522 :p_validation_level IN NUMBER Optional
523 Default = FND_API.G_VALID_LEVEL_FULL
524 Determins which validation steps should be executed and which
525 should be skipped. Public APIs by definition have to perform FULL
526 validation on all the data passed to them
527
528 : x_return_status OUT VARCHAR2
529 Returns the result of all operations performed by the API
530
531 : x_msg_count OUT NUMBER
532 Holds the number of messages in the API message list.
533
534 : x_msg_data OUT VARCHAR2
535 Holds the message in an encoded format.
536
537 : p_time IN DATE Optional
538 Default = NULL
539 The time, to the minute, for this data. Input will be truncated
540 to the minute, and the data must be for the minute {yyyy-mon-dd
541 hh:mm:00.0} to {yyyy-mon-dd hh:mm:59.9}, local to the advanced
542 outbound server.
543
544 : p_site_id IN NUMBER Optional
545 Default = NULL
546 ID of the site. This is an index to the site definition table.
547
548 : p_site_name IN VARCHAR2 Optional
549 Default = NULL
550 Name of the site.
551
552 : p_media_type_id IN NUMBER Optional
553 Default = NULL
554 ID of the media type. This is an index to the media type
555 definition table.
556
557 : p_media_type IN VARCHAR2 Optional
558 Default = NULL
559 Name of the media type.
560
561 : p_los_id IN NUMBER Optional
562 Default = NULL
563 ID of the level of service category. This is an index to the
564 level of service definition table.
565
566 : p_los_name IN VARCHAR2 Optional
567 Default = NULL
568 Level of service category name.
569
570 : p_direction IN NUMBER Optional
571 Default = NULL
572 Type of LOS: Inbound (:= 1) or Outbound (:= 0)
573
574 : p_items_queued_count IN NUMBER Optional
575 Default = NULL
576 Number of items queued for this media type.
577
578 : p_items_serviced_count IN NUMBER Optional
579 Default = NULL
580 Number of items serviced for this media type.
581
582 : p_items_serviced_within_LOS IN NUMBER Optional
583 Default = NULL
584 Number of items serviced within LOS constraints for this media
585 type.
586
587 : p_items_not_serv_within_LOS IN NUMBER Optional
588 Default = NULL
589 Number of items not serviced within LOS constraints for this
590 media type.
591
592 : p_number_agents_working IN NUMBER Optional
593 Default = NULL
594 Number of agents currently working items of this media type.
595
596 : p_minimum_agents_required IN NUMBER Optional
597 Default = NULL
598 Minimum number of agents required by LOS category.
599
600 : p_items_left_to_be_serviced IN NUMBER Optional
601 Default = NULL
602 Number of items left to be serviced for an outbound LOS
603 category quota.
604
605 : p_items_serviced_today IN NUMBER Optional
606 Default = NULL
607 Number of items serviced, cumulative for today.
608
609 Version : Current Version 1.0
610 Previous Version n/a
611 Initial Version 1.0
612
613 end of comments */
614
615 PROCEDURE Interaction_Blending_Input
616 ( p_api_version IN NUMBER,
617 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
618 p_commit IN VARCHAR2 := FND_API.G_FALSE,
619 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
620 x_return_status OUT nocopy VARCHAR2,
621 x_msg_count OUT nocopy NUMBER,
622 x_msg_data OUT nocopy VARCHAR2,
623
624 p_time IN DATE := NULL,
625
626 p_site_id IN NUMBER := NULL,
627 p_site_name IN VARCHAR2 := NULL,
628 p_media_type_id IN NUMBER := NULL,
629 p_media_type IN VARCHAR2 := NULL,
630 p_los_id IN NUMBER := NULL,
631 p_los_name IN VARCHAR2 := NULL,
632
633 p_direction IN NUMBER := NULL,
634
635 p_items_queued_count IN NUMBER := NULL,
636 p_items_serviced_count IN NUMBER := NULL,
637 p_items_serviced_within_LOS IN NUMBER := NULL,
638 p_items_not_serv_within_LOS IN NUMBER := NULL,
639 p_number_agents_working IN NUMBER := NULL,
640 p_minimum_agents_required IN NUMBER := NULL,
641
642 p_items_left_to_be_serviced IN NUMBER := NULL,
643 p_items_serviced_today IN NUMBER := NULL
644 ) IS
645
646 l_api_name CONSTANT VARCHAR2(30) := 'Interaction_Blending_Input';
647 l_api_version CONSTANT NUMBER := 1.0;
648 l_encoded VARCHAR2(1) := FND_API.G_FALSE;
649
650 l_site_id NUMBER := -1;
651 l_media_type VARCHAR2(240) := '';
652 l_los_id NUMBER := -1;
653
654 l_items_serviced_within_LOS NUMBER := p_items_serviced_within_LOS;
655 l_items_not_serv_within_LOS NUMBER := p_items_not_serv_within_LOS;
656 l_quota NUMBER := 0;
657
658 BEGIN
659 -- Standard Start of API savepoint
660 SAVEPOINT Interaction_Blending_Input_PUB;
661
662 -- Standard call to check for call compatibility
663 IF NOT FND_API.Compatible_API_Call ( l_api_version,
664 p_api_version,
665 l_api_name,
666 G_PKG_NAME)
667 THEN
668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
669 END IF;
670
671 -- initialize message list if p_init_msg_list is set to TRUE
672 IF FND_API.To_Boolean( p_init_msg_list ) THEN
673 FND_MSG_PUB.initialize;
674 END IF;
675
676 -- Initialize the API return status to success
677 x_return_status := FND_API.G_RET_STS_SUCCESS;
678
679 -- API body
680
681 -- Insert record if p_time is given
682 BEGIN
683 IF p_time IS NOT NULL
684 THEN
685 -- Check p_site_id for valid value
686 IF p_site_id IS NOT NULL
687 THEN
688 l_site_id := p_site_id;
689 ELSIF p_site_name IS NOT NULL
690 THEN
691 DECLARE
692 CURSOR server_group_id_cur (v_site_name IN VARCHAR2)
693 IS
694 SELECT server_group_id
695 FROM ieo_svr_groups
696 WHERE UPPER (group_name) = UPPER (v_site_name)
697 ORDER BY server_group_id;
698 BEGIN
699 OPEN server_group_id_cur (p_site_name);
700 FETCH server_group_id_cur INTO l_site_id;
701 CLOSE server_group_id_cur;
702 END;
703 END IF;
704
705 -- Check p_media_type for valid value
706 -- Note that there is no media type table so p_media_type_id
707 -- is meaningless. Still, if it's given we can use it instead
708 -- of leaving the media type blank.
709 IF p_media_type IS NOT NULL
710 THEN
711 l_media_type := p_media_type;
712 ELSIF p_media_type_id IS NOT NULL
713 THEN
714 l_media_type := p_media_type_id;
715 END IF;
716
717 -- Check p_los_id for valid value
718 IF p_los_id IS NOT NULL
719 THEN
720 l_los_id := p_los_id;
721 ELSIF p_los_name IS NOT NULL
722 THEN
723 DECLARE
724 CURSOR wbsc_id_cur (v_los_name IN VARCHAR2)
725 IS
726 SELECT wbsc_id
727 FROM ieb_wb_svc_cats
728 WHERE UPPER (service_category_name) = UPPER (v_los_name)
729 ORDER BY wbsc_id;
730 BEGIN
731 OPEN wbsc_id_cur (p_los_name);
732 FETCH wbsc_id_cur INTO l_los_id;
733 CLOSE wbsc_id_cur;
734 END;
735 END IF;
736
737 -- 20000814 kcwong
738 -- For Inbound LOS, p_items_serviced_within_LOS and
739 -- p_items_not_serv_within_los are valid, so the corresponding
740 -- local variables are already set.
741 -- For Outbound LOS, we have to calculate the pseudo values.
742
743 -- This is a stupid hack and I'll probably go to Hell for this.
744 -- Outbound LOS only gives us p_items_serviced_count, and a quota
745 -- in another table. I have to map those into items_serviced and
746 -- items_not_serv so that the work blending report works for
747 -- both LOS types.
748
749 IF p_direction = 0
750 THEN
751 -- Look for a specific day (and time) quota
752 DECLARE
753 CURSOR quota_cur (v_los_id NUMBER, v_time IN DATE)
754 IS
755 SELECT covs.quota
756 FROM ieb_outb_svc_coverages covs,
757 ieb_service_plans plan,
758 ieb_wb_svc_cats cats
759 WHERE cats.wbsc_id = v_los_id
760 AND cats.svcpln_svcpln_id = plan.svcpln_id
761 AND covs.svcpln_svcpln_id = plan.svcpln_id
762 -- Get weekday match (for regular) or date match (for specific)
763 AND (covs.regular_schd_day = (TO_NUMBER (TO_CHAR (v_time, 'd')) - 1)
764 OR TRUNC (covs.spec_schd_date, 'dd') = TRUNC (v_time, 'dd'))
765 AND covs.begin_time_hhmm <= TO_NUMBER (TO_CHAR (v_time, 'hh24mi'))
766 AND covs.end_time_hhmm >= TO_NUMBER (TO_CHAR (v_time, 'hh24mi'))
767 -- will get two records if there is a specific match
768 ORDER BY schedule_type DESC;
769 BEGIN
770 OPEN quota_cur (l_los_id, p_time);
771 FETCH quota_cur INTO l_quota;
772 CLOSE quota_cur;
773 END;
774
775 l_items_serviced_within_LOS := p_items_serviced_count;
776 l_items_not_serv_within_LOS := (l_quota / 60.0) - p_items_serviced_count;
777 END IF;
778 -- 20000814 kcwong End
779
780 /* INSERT INTO bix_server_ib
781 (server_ib_id, minute, site_id, media_item_type,
782 los_category_id, los_category_direction,
783 number_of_items_queued, number_of_items_serviced,
784 items_serviced_within_los, items_not_serviced_within_los,
785 number_agents_working, minimum_number_of_agents_req,
786 items_left_to_be_serviced, items_serviced_today)
787 SELECT bix_server_ib_s.nextval, trunc (p_time, 'MI'), l_site_id,
788 l_media_type, l_los_id, p_direction,
789 p_items_queued_count, p_items_serviced_count,
790 l_items_serviced_within_LOS, l_items_not_serv_within_LOS,
791 p_number_agents_working, p_minimum_agents_required,
792 p_items_left_to_be_serviced, p_items_serviced_today
793 FROM dual;moved the comment12699361 */
794 END IF;
795 EXCEPTION
796 WHEN OTHERS THEN
797 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
798 THEN
799 FND_MESSAGE.SET_NAME('CCT', 'CCT_SDI_IB_IN_INSERT_ERROR');
800 FND_MSG_PUB.Add;
801 END IF;
802
803 RAISE FND_API.G_EXC_ERROR;
804 END;
805
806 -- Signify Success
807 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
808 THEN
809 FND_MESSAGE.SET_NAME('CCT', 'CCT_SDI_IB_IN_RECORD_INSERTED');
810 FND_MSG_PUB.Add;
811 END IF;
812
813 -- End of API body
814
815 -- Standard check of p_commit
816 IF FND_API.To_Boolean( p_commit ) THEN
817 COMMIT WORK;
818 END IF;
819
820 -- Standard call to get message count and if count is 1, get message info
821 FND_MSG_PUB.Count_And_Get
822 ( p_encoded => l_encoded,
823 p_count => x_msg_count,
824 p_data => x_msg_data
825 );
826
827 EXCEPTION
828 WHEN FND_API.G_EXC_ERROR THEN
829 ROLLBACK TO Interaction_Blending_Input_PUB;
830 x_return_status := FND_API.G_RET_STS_ERROR;
831
832 FND_MSG_PUB.Count_And_Get
833 ( p_encoded => l_encoded,
834 p_count => x_msg_count,
835 p_data => x_msg_data
836 );
837
838 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
839 ROLLBACK TO Interaction_Blending_Input_PUB;
840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
841
842 FND_MSG_PUB.Count_And_Get
843 ( p_encoded => l_encoded,
844 p_count => x_msg_count,
845 p_data => x_msg_data
846 );
847
848 WHEN OTHERS THEN
849 ROLLBACK TO Interaction_Blending_Input_PUB;
850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
851
852 IF FND_MSG_PUB.Check_Msg_Level
853 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
854
855 THEN
856 FND_MSG_PUB.Add_Exc_Msg
857 ( p_pkg_name => G_PKG_NAME,
858 p_procedure_name => l_api_name,
859 p_error_text => 'G_MSG_LVL_UNEXP_ERROR'
860 );
861 END IF;
862
863 FND_MSG_PUB.Count_And_Get
864 ( p_encoded => l_encoded,
865 p_count => x_msg_count,
866 p_data => x_msg_data
867 );
868
869 END Interaction_Blending_Input;
870
871 /* ************************************************** */
872
873 /* start of comments
874 Record data for a Multi Channel Manager (time, interaction classification)
875 tuple combination.
876 Api name : Multi_Channel_Manager_Input
877 Type : Public
878 Pre-regs : None
879 Function : Record data to ICI Multi Channel Manager table
880 Parameters :
881 :p_api_version IN NUMBER Required
882 Used by the API to compare the version numbers of incoming
883 calls to its current version number, returns an unexpected error
884 if they are incompatible.
885
886 :p_init_msg_list IN VARCHAR2 Optional
887 Default = FND_API.G_FALSE
888 The API message list must be initialized every time a program
889 calls an API.
890
891 :p_commit IN VARCHAR2 Optional
892 Default = FND_API.G_FALSE
893 Before returning to its caller, an API should check the value
894 of p_commit. If it is set to TRUE it should commit its work.
895
896 :p_validation_level IN NUMBER Optional
897 Default = FND_API.G_VALID_LEVEL_FULL
898 Determins which validation steps should be executed and which
899 should be skipped. Public APIs by definition have to perform FULL
900 validation on all the data passed to them
901
902 : x_return_status OUT VARCHAR2
903 Returns the result of all operations performed by the API
904
905 : x_msg_count OUT NUMBER
906 Holds the number of messages in the API message list.
907
908 : x_msg_data OUT VARCHAR2
909 Holds the message in an encoded format.
910
911 : p_time IN DATE Optional
912 Default = NULL
913 The time, to the minute, for this data. Input will be truncated
914 to the minute, and the data must be for the minute {yyyy-mon-dd
915 hh:mm:00.0} to {yyyy-mon-dd hh:mm:59.9}, local to the advanced
916 outbound server.
917
918 : p_interaction_center_id IN NUMBER Optional
919 Default = NULL
920 ID of the interaction center. This is an index to the interaction
921 center definition table.
922
923 : p_interaction_center_name IN VARCHAR2 Optional
924 Default = NULL
925 Name of the interaction center.
926
927 : p_interaction_class_id IN NUMBER Optional
928 Default = NULL
929 ID of the interaction classification. This is an index to the
930 interaction classification definition table.
931
932 : p_interaction_class_name IN VARCHAR2 Optional
933 Default = NULL
934 Name of the interaction classification.
935
936 : p_interactions_received IN NUMBER Optional
937 Default = NULL
938 Number of interactions received this minute.
939
940 : p_interactions_offered IN NUMBER Optional
941 Default = NULL
942 Number of interactions offered to agents this minute.
943
944 : p_interactions_answered IN NUMBER Optional
945 Default = NULL
946 Number of interactions answered by agents this minute.
947
948 : p_interactions_transferred IN NUMBER Optional
949 Default = NULL
950 Number of interactions transferred this minute.
951
952 : p_interactions_handled IN NUMBER Optional
953 Default = NULL
954 Number of interactions handled (by agents or automation) this minute.
955
956 : p_interactions_abandoned IN NUMBER Optional
957 Default = NULL
958 Number of interactions abandoned this minute. Technically,
959 abandoned calls are not interactions.
960
961 : p_speed_to_answer_avg IN NUMBER Optional
962 Default = NULL
963 Average speed to answer a call this minute. In seconds.
964 = p_speed_to_answer_total / p_interactions_answered
965
966 : p_speed_to_answer_std_dev IN NUMBER Optional
967 Default = NULL
968 Standard deviation for average speed to answer.
969
970 : p_speed_to_answer_total IN NUMBER Optional
971 Default = NULL
972 Total speed to answer time, in seconds.
973
974 : p_wait_to_abandon_avg IN NUMBER Optional
975 Default = NULL
976 Average wait to abandon a call this minute. In seconds.
977 = p_wait_to_abandon_total / p_interactions_abandoned
978
979 : p_wait_to_abandon_std_dev IN NUMBER Optional
980 Default = NULL
981 Standard deviation for average wait to abandon.
982
983 : p_wait_to_abandon_total IN NUMBER Optional
984 Default = NULL
985 Total wait to abandon, in seconds.
986
987 : p_percent_occupancy_rate IN NUMBER Optional
988 Default = NULL
989 0.0 <= percent <= 1.0
990 = p_total_talk_time / (p_total_talk_time + p_total_idle_time)
991
992 : p_percent_utilization_rate IN NUMBER Optional
993 Default = NULL
994 0.0 <= percent <= 1.0
995 = (p_total_talk_time + p_total_idle_time) / p_total_log_time
996
997 : p_percent_transfer_rate IN NUMBER Optional
998 Default = NULL
999 0.0 <= percent <= 1.0
1000 = p_interactions_transferred / p_interactions_answered
1001
1002 : p_total_talk_time IN NUMBER Optional
1003 Default = NULL
1004 Total talk time in seconds.
1005
1006 : p_total_hold_time IN NUMBER Optional
1007 Default = NULL
1008 Total hold time in seconds.
1009
1010 : p_total_idle_time IN NUMBER Optional
1011 Default = NULL
1012 Total idle time in seconds.
1013
1014 : p_total_wrap_time IN NUMBER Optional
1015 Default = NULL
1016 Total wrap time in seconds.
1017
1018 : p_total_log_time IN NUMBER Optional
1019 Default = NULL
1020 Total log time in seconds.
1021
1022 Version : Current Version 1.0
1023 Previous Version n/a
1024 Initial Version 1.0
1025
1026 end of comments */
1027
1028 PROCEDURE Multi_Channel_Manager_Input
1029 ( p_api_version IN NUMBER,
1030 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1031 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1032 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1033 x_return_status OUT nocopy VARCHAR2,
1034 x_msg_count OUT nocopy NUMBER,
1035 x_msg_data OUT nocopy VARCHAR2,
1036
1037 p_time IN DATE := NULL,
1038
1039 p_interaction_center_id IN NUMBER := NULL,
1040 p_interaction_center_name IN VARCHAR2 := NULL,
1041 p_interaction_class_id IN NUMBER := NULL,
1042 p_interaction_class_name IN VARCHAR2 := NULL,
1043
1044 p_interactions_received IN NUMBER := NULL,
1045 p_interactions_offered IN NUMBER := NULL,
1046 p_interactions_answered IN NUMBER := NULL,
1047 p_interactions_transferred IN NUMBER := NULL,
1048 p_interactions_handled IN NUMBER := NULL,
1049 p_interactions_abandoned IN NUMBER := NULL,
1050
1051 p_speed_to_answer_avg IN NUMBER := NULL,
1052 p_speed_to_answer_std_dev IN NUMBER := NULL,
1053 p_speed_to_answer_total IN NUMBER := NULL,
1054
1055 p_wait_to_abandon_avg IN NUMBER := NULL,
1056 p_wait_to_abandon_std_dev IN NUMBER := NULL,
1057 p_wait_to_abandon_total IN NUMBER := NULL,
1058
1059 p_percent_occupancy_rate IN NUMBER := NULL,
1060 p_percent_utilization_rate IN NUMBER := NULL,
1061 p_percent_transfer_rate IN NUMBER := NULL,
1062
1063 p_total_talk_time IN NUMBER := NULL,
1064 p_total_hold_time IN NUMBER := NULL,
1065 p_total_idle_time IN NUMBER := NULL,
1066 p_total_wrap_time IN NUMBER := NULL,
1067 p_total_log_time IN NUMBER := NULL
1068 ) IS
1069
1070 l_api_name CONSTANT VARCHAR2(30) := 'Multi_Channel_Manager_Input';
1071 l_api_version CONSTANT NUMBER := 1.0;
1072 l_encoded VARCHAR2(1) := FND_API.G_FALSE;
1073
1074 l_interaction_center_id NUMBER := -1;
1075 l_interaction_class_name VARCHAR2(240) := '';
1076
1077 BEGIN
1078 -- Standard Start of API savepoint
1079 SAVEPOINT MCM_Input_PUB;
1080
1081 -- Standard call to check for call compatibility
1082 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1083 p_api_version,
1084 l_api_name,
1085 G_PKG_NAME)
1086 THEN
1087 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1088 END IF;
1089
1090 -- initialize message list if p_init_msg_list is set to TRUE
1091 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1092 FND_MSG_PUB.initialize;
1093 END IF;
1094
1095 -- Initialize the API return status to success
1096 x_return_status := FND_API.G_RET_STS_SUCCESS;
1097
1098 -- API body
1099
1100 -- Insert record if p_time is given
1101 BEGIN
1102 IF p_time IS NOT NULL
1103 THEN
1104 -- Check p_interaction_center_id for valid value
1105 IF p_interaction_center_id IS NOT NULL
1106 THEN
1107 l_interaction_center_id := p_interaction_center_id;
1108 ELSIF p_interaction_center_name IS NOT NULL
1109 THEN
1110 DECLARE
1111 CURSOR server_group_id_cur (v_interaction_center_name IN VARCHAR2)
1112 IS
1113 SELECT server_group_id
1114 FROM ieo_svr_groups
1115 WHERE UPPER (group_name) = UPPER (v_interaction_center_name)
1116 ORDER BY server_group_id;
1117 BEGIN
1118 OPEN server_group_id_cur (p_interaction_center_name);
1119 FETCH server_group_id_cur INTO l_interaction_center_id;
1120 CLOSE server_group_id_cur;
1121 END;
1122 END IF;
1123
1124 -- Check p_interaction_class_name for valid value
1125 IF p_interaction_class_name IS NOT NULL
1126 THEN
1127 l_interaction_class_name := p_interaction_class_name;
1128 ELSIF p_interaction_class_id IS NOT NULL
1129 THEN
1130 DECLARE
1131 CURSOR classification_cur (v_interaction_class_id IN NUMBER)
1132 IS
1133 SELECT classification
1134 FROM cct_classifications
1135 WHERE classification_id = v_interaction_class_id
1136 ORDER BY classification;
1137 BEGIN
1138 OPEN classification_cur (p_interaction_class_id);
1139 FETCH classification_cur INTO l_interaction_class_name;
1140 CLOSE classification_cur;
1141 END;
1142 END IF;
1143
1144 /* INSERT INTO bix_server_mcm
1145 (server_mcm_id, minute,
1146 interaction_center_id, interaction_classification,
1147 interactions_received, interactions_offered,
1148 interactions_answered, interactions_transferred,
1149 interactions_handled, interactions_abandoned,
1150 average_speed_to_answer, std_dev_speed_to_answer,
1151 total_speed_to_answer,
1152 average_wait_to_abandoned, std_dev_wait_to_abandoned,
1153 total_wait_to_abandoned,
1154 percent_occupancy_rate, percent_utilization_rate,
1155 percent_transfer_rate,
1156 talk_time, hold_time, idle_time, wrap_time, log_time)
1157 SELECT bix_server_cp_s.nextval, trunc (p_time, 'MI'),
1158 l_interaction_center_id, l_interaction_class_name,
1159 p_interactions_received, p_interactions_offered,
1160 p_interactions_answered, p_interactions_transferred,
1161 p_interactions_handled, p_interactions_abandoned,
1162 p_speed_to_answer_avg, p_speed_to_answer_std_dev,
1163 p_speed_to_answer_total,
1164 p_wait_to_abandon_avg, p_wait_to_abandon_std_dev,
1165 p_wait_to_abandon_total,
1166 p_percent_occupancy_rate, p_percent_utilization_rate, p_percent_transfer_rate,
1167 p_total_talk_time, p_total_hold_time, p_total_idle_time,
1168 p_total_wrap_time, p_total_log_time
1169 FROM dual; moved the comment 12699361 */
1170 END IF;
1171 EXCEPTION
1172 WHEN OTHERS THEN
1173 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1174 THEN
1175 FND_MESSAGE.SET_NAME('CCT', 'CCT_SDI_MCM_IN_INSERT_ERROR');
1176 FND_MSG_PUB.Add;
1177 END IF;
1178
1179 RAISE FND_API.G_EXC_ERROR;
1180 END;
1181
1182 -- Signify Success
1183 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1184 THEN
1185 FND_MESSAGE.SET_NAME('CCT', 'CCT_SDI_MCM_IN_RECORD_INSERTED');
1186 FND_MSG_PUB.Add;
1187 END IF;
1188
1189 -- End of API body
1190
1191 -- Standard check of p_commit
1192 IF FND_API.To_Boolean( p_commit ) THEN
1193 COMMIT WORK;
1194 END IF;
1195
1196 -- Standard call to get message count and if count is 1, get message info
1197 FND_MSG_PUB.Count_And_Get
1198 ( p_encoded => l_encoded,
1199 p_count => x_msg_count,
1200 p_data => x_msg_data
1201 );
1202
1203 EXCEPTION
1204 WHEN FND_API.G_EXC_ERROR THEN
1205 ROLLBACK TO MCM_Input_PUB;
1206 x_return_status := FND_API.G_RET_STS_ERROR;
1207
1208 FND_MSG_PUB.Count_And_Get
1209 ( p_encoded => l_encoded,
1210 p_count => x_msg_count,
1211 p_data => x_msg_data
1212 );
1213
1214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1215 ROLLBACK TO MCM_Input_PUB;
1216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1217
1218 FND_MSG_PUB.Count_And_Get
1219 ( p_encoded => l_encoded,
1220 p_count => x_msg_count,
1221 p_data => x_msg_data
1222 );
1223
1224 WHEN OTHERS THEN
1225 ROLLBACK TO MCM_Input_PUB;
1226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1227
1228 IF FND_MSG_PUB.Check_Msg_Level
1229 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1230
1231 THEN
1232 FND_MSG_PUB.Add_Exc_Msg
1233 ( p_pkg_name => G_PKG_NAME,
1234 p_procedure_name => l_api_name,
1235 p_error_text => 'G_MSG_LVL_UNEXP_ERROR'
1236 );
1237 END IF;
1238
1239 FND_MSG_PUB.Count_And_Get
1240 ( p_encoded => l_encoded,
1241 p_count => x_msg_count,
1242 p_data => x_msg_data
1243 );
1244
1245 END Multi_Channel_Manager_Input;
1246
1247 END CCT_ServerDataInput_PUB;
[Home] [Help]