1 PACKAGE BODY CCT_ServerDataInput_PUB AS
2 /* $Header: ccticiib.pls 120.0 2005/06/02 09:29:53 appldev noship $ */
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 INSERT INTO bix_server_cp
382 (server_cp_id, minute, site_id, campaign_id, list_id,
383 busy_counts, connect_counts, answering_machine_counts,
384 modem_counts, sit_counts, rna_counts, other_counts,
385 predictive_dials, progressive_dials, preview_dials,
386 preview_time, withdrawn_dials,
387 average_wait_time, std_dev_wait_time,
388 average_cumulative_wait_time, std_dev_cumulative_wait_time,
389 minimum_wait_time, maximum_wait_time, total_wait_time,
390 number_agents_predictive, number_agents_outbound,
391 number_working_dialers, number_abandons, abandon_percentage,
392 number_callbacks, callback_percentage, dials_per_minute,
393 number_calls_outcome_1, number_calls_outcome_2,
394 number_calls_outcome_3,
395 number_records_start_of_day, number_records_remaining,
396 predicted_exhaustion_date, num_recs_to_be_released_next_1,
397 num_rec_to_be_released_next_5, num_rec_to_be_released_next_15,
398 num_rec_to_be_released_next_60)
399 SELECT bix_server_cp_s.nextval, trunc (p_time, 'MI'), l_site_id,
400 l_campaign_id, l_list_id,
401 p_busy_count, p_connect_count, p_answering_machine_count,
402 p_modem_count, p_sit_count, p_rna_count, p_other_count,
403 p_predictive_dials, p_progressive_dials, p_preview_dials,
404 p_preview_time, p_withdrawn_dials,
405 p_wait_time_average, p_wait_time_std_dev,
406 p_wait_time_cumulative_avg, p_wait_time_cumulative_stddev,
407 p_wait_time_minimum, p_wait_time_maximum, p_wait_time_total,
408 p_number_agents_predictive, p_number_agents_outbound,
409 p_number_working_dialers, p_abandon_count, p_abandon_percentage,
410 p_callback_count, p_callback_percentage,
411 p_predictive_dials + p_progressive_dials + p_preview_dials,
412 p_outcome_1_count, p_outcome_2_count, p_outcome_3_count,
413 p_records_start_of_day, p_records_remaining,
414 p_predicted_exhaustion_date,
415 p_recs_to_be_released_in_1, p_recs_to_be_released_in_5,
416 p_recs_to_be_released_in_15, p_recs_to_be_released_in_60
417 FROM dual;
418 END IF;
419 EXCEPTION
420 WHEN OTHERS THEN
421 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
422 THEN
423 FND_MESSAGE.SET_NAME('CCT', 'CCT_SDI_AO_IN_INSERT_ERROR');
424 FND_MSG_PUB.Add;
425 END IF;
426
427 RAISE FND_API.G_EXC_ERROR;
428 END;
429
430 -- Signify Success
431 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
432 THEN
433 FND_MESSAGE.SET_NAME('CCT', 'CCT_SDI_AO_IN_RECORD_INSERTED');
434 FND_MSG_PUB.Add;
435 END IF;
436
437 -- End of API body
438
439 -- Standard check of p_commit
440 IF FND_API.To_Boolean( p_commit ) THEN
441 COMMIT WORK;
442 END IF;
443
444 -- Standard call to get message count and if count is 1, get message info
445 FND_MSG_PUB.Count_And_Get
446 ( p_encoded => l_encoded,
447 p_count => x_msg_count,
448 p_data => x_msg_data
449 );
450
451 EXCEPTION
452 WHEN FND_API.G_EXC_ERROR THEN
453 ROLLBACK TO Advanced_Outbound_Input_PUB;
454 x_return_status := FND_API.G_RET_STS_ERROR;
455
456 FND_MSG_PUB.Count_And_Get
457 ( p_encoded => l_encoded,
458 p_count => x_msg_count,
459 p_data => x_msg_data
460 );
461
462 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
463 ROLLBACK TO Advanced_Outbound_Input_PUB;
464 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
465
466 FND_MSG_PUB.Count_And_Get
467 ( p_encoded => l_encoded,
468 p_count => x_msg_count,
469 p_data => x_msg_data
470 );
471
472 WHEN OTHERS THEN
473 ROLLBACK TO Advanced_Outbound_Input_PUB;
474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
475
476 IF FND_MSG_PUB.Check_Msg_Level
477 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
478
479 THEN
480 FND_MSG_PUB.Add_Exc_Msg
481 ( p_pkg_name => G_PKG_NAME,
482 p_procedure_name => l_api_name,
483 p_error_text => 'G_MSG_LVL_UNEXP_ERROR'
484 );
485 END IF;
486
487 FND_MSG_PUB.Count_And_Get
488 ( p_encoded => l_encoded,
489 p_count => x_msg_count,
490 p_data => x_msg_data
491 );
492
493 END Advanced_Outbound_Input;
494
495 /* ************************************************** */
496
497 /* start of comments
498 Record data for a Blending (time, site, media type, LOS category)
499 tuple combination.
500 Api name : Interaction_Blending_Input
501 Type : Public
502 Pre-regs : None
503 Function : Record data to ICI Interaction Blending table
504 Parameters :
505 :p_api_version IN NUMBER Required
506 Used by the API to compare the version numbers of incoming
507 calls to its current version number, returns an unexpected error
508 if they are incompatible.
509
510 :p_init_msg_list IN VARCHAR2 Optional
511 Default = FND_API.G_FALSE
512 The API message list must be initialized every time a program
513 calls an API.
514
515 :p_commit IN VARCHAR2 Optional
516 Default = FND_API.G_FALSE
517 Before returning to its caller, an API should check the value
518 of p_commit. If it is set to TRUE it should commit its work.
519
520 :p_validation_level IN NUMBER Optional
521 Default = FND_API.G_VALID_LEVEL_FULL
522 Determins which validation steps should be executed and which
523 should be skipped. Public APIs by definition have to perform FULL
524 validation on all the data passed to them
525
526 : x_return_status OUT VARCHAR2
527 Returns the result of all operations performed by the API
528
529 : x_msg_count OUT NUMBER
530 Holds the number of messages in the API message list.
531
532 : x_msg_data OUT VARCHAR2
533 Holds the message in an encoded format.
534
535 : p_time IN DATE Optional
536 Default = NULL
537 The time, to the minute, for this data. Input will be truncated
538 to the minute, and the data must be for the minute {yyyy-mon-dd
539 hh:mm:00.0} to {yyyy-mon-dd hh:mm:59.9}, local to the advanced
540 outbound server.
541
542 : p_site_id IN NUMBER Optional
543 Default = NULL
544 ID of the site. This is an index to the site definition table.
545
546 : p_site_name IN VARCHAR2 Optional
547 Default = NULL
548 Name of the site.
549
550 : p_media_type_id IN NUMBER Optional
551 Default = NULL
552 ID of the media type. This is an index to the media type
553 definition table.
554
555 : p_media_type IN VARCHAR2 Optional
556 Default = NULL
557 Name of the media type.
558
559 : p_los_id IN NUMBER Optional
560 Default = NULL
561 ID of the level of service category. This is an index to the
562 level of service definition table.
563
564 : p_los_name IN VARCHAR2 Optional
565 Default = NULL
566 Level of service category name.
567
568 : p_direction IN NUMBER Optional
569 Default = NULL
570 Type of LOS: Inbound (:= 1) or Outbound (:= 0)
571
572 : p_items_queued_count IN NUMBER Optional
573 Default = NULL
574 Number of items queued for this media type.
575
576 : p_items_serviced_count IN NUMBER Optional
577 Default = NULL
578 Number of items serviced for this media type.
579
580 : p_items_serviced_within_LOS IN NUMBER Optional
581 Default = NULL
582 Number of items serviced within LOS constraints for this media
583 type.
584
585 : p_items_not_serv_within_LOS IN NUMBER Optional
586 Default = NULL
587 Number of items not serviced within LOS constraints for this
588 media type.
589
590 : p_number_agents_working IN NUMBER Optional
591 Default = NULL
592 Number of agents currently working items of this media type.
593
594 : p_minimum_agents_required IN NUMBER Optional
595 Default = NULL
596 Minimum number of agents required by LOS category.
597
598 : p_items_left_to_be_serviced IN NUMBER Optional
599 Default = NULL
600 Number of items left to be serviced for an outbound LOS
601 category quota.
602
603 : p_items_serviced_today IN NUMBER Optional
604 Default = NULL
605 Number of items serviced, cumulative for today.
606
607 Version : Current Version 1.0
608 Previous Version n/a
609 Initial Version 1.0
610
611 end of comments */
612
613 PROCEDURE Interaction_Blending_Input
614 ( p_api_version IN NUMBER,
615 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
616 p_commit IN VARCHAR2 := FND_API.G_FALSE,
617 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
618 x_return_status OUT nocopy VARCHAR2,
619 x_msg_count OUT nocopy NUMBER,
620 x_msg_data OUT nocopy VARCHAR2,
621
622 p_time IN DATE := NULL,
623
624 p_site_id IN NUMBER := NULL,
625 p_site_name IN VARCHAR2 := NULL,
626 p_media_type_id IN NUMBER := NULL,
627 p_media_type IN VARCHAR2 := NULL,
628 p_los_id IN NUMBER := NULL,
629 p_los_name IN VARCHAR2 := NULL,
630
631 p_direction IN NUMBER := NULL,
632
633 p_items_queued_count IN NUMBER := NULL,
634 p_items_serviced_count IN NUMBER := NULL,
635 p_items_serviced_within_LOS IN NUMBER := NULL,
636 p_items_not_serv_within_LOS IN NUMBER := NULL,
637 p_number_agents_working IN NUMBER := NULL,
638 p_minimum_agents_required IN NUMBER := NULL,
639
640 p_items_left_to_be_serviced IN NUMBER := NULL,
641 p_items_serviced_today IN NUMBER := NULL
642 ) IS
643
644 l_api_name CONSTANT VARCHAR2(30) := 'Interaction_Blending_Input';
645 l_api_version CONSTANT NUMBER := 1.0;
646 l_encoded VARCHAR2(1) := FND_API.G_FALSE;
647
648 l_site_id NUMBER := -1;
649 l_media_type VARCHAR2(240) := '';
650 l_los_id NUMBER := -1;
651
652 l_items_serviced_within_LOS NUMBER := p_items_serviced_within_LOS;
653 l_items_not_serv_within_LOS NUMBER := p_items_not_serv_within_LOS;
654 l_quota NUMBER := 0;
655
656 BEGIN
657 -- Standard Start of API savepoint
658 SAVEPOINT Interaction_Blending_Input_PUB;
659
660 -- Standard call to check for call compatibility
661 IF NOT FND_API.Compatible_API_Call ( l_api_version,
662 p_api_version,
663 l_api_name,
664 G_PKG_NAME)
665 THEN
666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667 END IF;
668
669 -- initialize message list if p_init_msg_list is set to TRUE
670 IF FND_API.To_Boolean( p_init_msg_list ) THEN
671 FND_MSG_PUB.initialize;
672 END IF;
673
674 -- Initialize the API return status to success
675 x_return_status := FND_API.G_RET_STS_SUCCESS;
676
677 -- API body
678
679 -- Insert record if p_time is given
680 BEGIN
681 IF p_time IS NOT NULL
682 THEN
683 -- Check p_site_id for valid value
684 IF p_site_id IS NOT NULL
685 THEN
686 l_site_id := p_site_id;
687 ELSIF p_site_name IS NOT NULL
688 THEN
689 DECLARE
690 CURSOR server_group_id_cur (v_site_name IN VARCHAR2)
691 IS
692 SELECT server_group_id
693 FROM ieo_svr_groups
694 WHERE UPPER (group_name) = UPPER (v_site_name)
695 ORDER BY server_group_id;
696 BEGIN
697 OPEN server_group_id_cur (p_site_name);
698 FETCH server_group_id_cur INTO l_site_id;
699 CLOSE server_group_id_cur;
700 END;
701 END IF;
702
703 -- Check p_media_type for valid value
704 -- Note that there is no media type table so p_media_type_id
705 -- is meaningless. Still, if it's given we can use it instead
706 -- of leaving the media type blank.
707 IF p_media_type IS NOT NULL
708 THEN
709 l_media_type := p_media_type;
710 ELSIF p_media_type_id IS NOT NULL
711 THEN
712 l_media_type := p_media_type_id;
713 END IF;
714
715 -- Check p_los_id for valid value
716 IF p_los_id IS NOT NULL
717 THEN
718 l_los_id := p_los_id;
719 ELSIF p_los_name IS NOT NULL
720 THEN
721 DECLARE
722 CURSOR wbsc_id_cur (v_los_name IN VARCHAR2)
723 IS
724 SELECT wbsc_id
725 FROM ieb_wb_svc_cats
726 WHERE UPPER (service_category_name) = UPPER (v_los_name)
727 ORDER BY wbsc_id;
728 BEGIN
729 OPEN wbsc_id_cur (p_los_name);
730 FETCH wbsc_id_cur INTO l_los_id;
731 CLOSE wbsc_id_cur;
732 END;
733 END IF;
734
735 -- 20000814 kcwong
736 -- For Inbound LOS, p_items_serviced_within_LOS and
737 -- p_items_not_serv_within_los are valid, so the corresponding
738 -- local variables are already set.
739 -- For Outbound LOS, we have to calculate the pseudo values.
740
741 -- This is a stupid hack and I'll probably go to Hell for this.
742 -- Outbound LOS only gives us p_items_serviced_count, and a quota
743 -- in another table. I have to map those into items_serviced and
744 -- items_not_serv so that the work blending report works for
745 -- both LOS types.
746
747 IF p_direction = 0
748 THEN
749 -- Look for a specific day (and time) quota
750 DECLARE
751 CURSOR quota_cur (v_los_id NUMBER, v_time IN DATE)
752 IS
753 SELECT covs.quota
754 FROM ieb_outb_svc_coverages covs,
755 ieb_service_plans plan,
756 ieb_wb_svc_cats cats
757 WHERE cats.wbsc_id = v_los_id
758 AND cats.svcpln_svcpln_id = plan.svcpln_id
759 AND covs.svcpln_svcpln_id = plan.svcpln_id
760 -- Get weekday match (for regular) or date match (for specific)
761 AND (covs.regular_schd_day = (TO_NUMBER (TO_CHAR (v_time, 'd')) - 1)
762 OR TRUNC (covs.spec_schd_date, 'dd') = TRUNC (v_time, 'dd'))
763 AND covs.begin_time_hhmm <= TO_NUMBER (TO_CHAR (v_time, 'hh24mi'))
764 AND covs.end_time_hhmm >= TO_NUMBER (TO_CHAR (v_time, 'hh24mi'))
765 -- will get two records if there is a specific match
766 ORDER BY schedule_type DESC;
767 BEGIN
768 OPEN quota_cur (l_los_id, p_time);
769 FETCH quota_cur INTO l_quota;
770 CLOSE quota_cur;
771 END;
772
773 l_items_serviced_within_LOS := p_items_serviced_count;
774 l_items_not_serv_within_LOS := (l_quota / 60.0) - p_items_serviced_count;
775 END IF;
776 -- 20000814 kcwong End
777
778 INSERT INTO bix_server_ib
779 (server_ib_id, minute, site_id, media_item_type,
780 los_category_id, los_category_direction,
781 number_of_items_queued, number_of_items_serviced,
782 items_serviced_within_los, items_not_serviced_within_los,
783 number_agents_working, minimum_number_of_agents_req,
784 items_left_to_be_serviced, items_serviced_today)
785 SELECT bix_server_ib_s.nextval, trunc (p_time, 'MI'), l_site_id,
786 l_media_type, l_los_id, p_direction,
787 p_items_queued_count, p_items_serviced_count,
788 l_items_serviced_within_LOS, l_items_not_serv_within_LOS,
789 p_number_agents_working, p_minimum_agents_required,
790 p_items_left_to_be_serviced, p_items_serviced_today
791 FROM dual;
792 END IF;
793 EXCEPTION
794 WHEN OTHERS THEN
795 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
796 THEN
797 FND_MESSAGE.SET_NAME('CCT', 'CCT_SDI_IB_IN_INSERT_ERROR');
798 FND_MSG_PUB.Add;
799 END IF;
800
801 RAISE FND_API.G_EXC_ERROR;
802 END;
803
804 -- Signify Success
805 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
806 THEN
807 FND_MESSAGE.SET_NAME('CCT', 'CCT_SDI_IB_IN_RECORD_INSERTED');
808 FND_MSG_PUB.Add;
809 END IF;
810
811 -- End of API body
812
813 -- Standard check of p_commit
814 IF FND_API.To_Boolean( p_commit ) THEN
815 COMMIT WORK;
816 END IF;
817
818 -- Standard call to get message count and if count is 1, get message info
819 FND_MSG_PUB.Count_And_Get
820 ( p_encoded => l_encoded,
821 p_count => x_msg_count,
822 p_data => x_msg_data
823 );
824
825 EXCEPTION
826 WHEN FND_API.G_EXC_ERROR THEN
827 ROLLBACK TO Interaction_Blending_Input_PUB;
828 x_return_status := FND_API.G_RET_STS_ERROR;
829
830 FND_MSG_PUB.Count_And_Get
831 ( p_encoded => l_encoded,
832 p_count => x_msg_count,
833 p_data => x_msg_data
834 );
835
836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
837 ROLLBACK TO Interaction_Blending_Input_PUB;
838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
839
840 FND_MSG_PUB.Count_And_Get
841 ( p_encoded => l_encoded,
842 p_count => x_msg_count,
843 p_data => x_msg_data
844 );
845
846 WHEN OTHERS THEN
847 ROLLBACK TO Interaction_Blending_Input_PUB;
848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849
850 IF FND_MSG_PUB.Check_Msg_Level
851 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
852
853 THEN
854 FND_MSG_PUB.Add_Exc_Msg
855 ( p_pkg_name => G_PKG_NAME,
856 p_procedure_name => l_api_name,
857 p_error_text => 'G_MSG_LVL_UNEXP_ERROR'
858 );
859 END IF;
860
861 FND_MSG_PUB.Count_And_Get
862 ( p_encoded => l_encoded,
863 p_count => x_msg_count,
864 p_data => x_msg_data
865 );
866
867 END Interaction_Blending_Input;
868
869 /* ************************************************** */
870
871 /* start of comments
872 Record data for a Multi Channel Manager (time, interaction classification)
873 tuple combination.
874 Api name : Multi_Channel_Manager_Input
875 Type : Public
876 Pre-regs : None
877 Function : Record data to ICI Multi Channel Manager table
878 Parameters :
879 :p_api_version IN NUMBER Required
880 Used by the API to compare the version numbers of incoming
881 calls to its current version number, returns an unexpected error
882 if they are incompatible.
883
884 :p_init_msg_list IN VARCHAR2 Optional
885 Default = FND_API.G_FALSE
886 The API message list must be initialized every time a program
887 calls an API.
888
889 :p_commit IN VARCHAR2 Optional
890 Default = FND_API.G_FALSE
891 Before returning to its caller, an API should check the value
892 of p_commit. If it is set to TRUE it should commit its work.
893
894 :p_validation_level IN NUMBER Optional
895 Default = FND_API.G_VALID_LEVEL_FULL
896 Determins which validation steps should be executed and which
897 should be skipped. Public APIs by definition have to perform FULL
898 validation on all the data passed to them
899
900 : x_return_status OUT VARCHAR2
901 Returns the result of all operations performed by the API
902
903 : x_msg_count OUT NUMBER
904 Holds the number of messages in the API message list.
905
906 : x_msg_data OUT VARCHAR2
907 Holds the message in an encoded format.
908
909 : p_time IN DATE Optional
910 Default = NULL
911 The time, to the minute, for this data. Input will be truncated
912 to the minute, and the data must be for the minute {yyyy-mon-dd
913 hh:mm:00.0} to {yyyy-mon-dd hh:mm:59.9}, local to the advanced
914 outbound server.
915
916 : p_interaction_center_id IN NUMBER Optional
917 Default = NULL
918 ID of the interaction center. This is an index to the interaction
919 center definition table.
920
921 : p_interaction_center_name IN VARCHAR2 Optional
922 Default = NULL
923 Name of the interaction center.
924
925 : p_interaction_class_id IN NUMBER Optional
926 Default = NULL
927 ID of the interaction classification. This is an index to the
928 interaction classification definition table.
929
930 : p_interaction_class_name IN VARCHAR2 Optional
931 Default = NULL
932 Name of the interaction classification.
933
934 : p_interactions_received IN NUMBER Optional
935 Default = NULL
936 Number of interactions received this minute.
937
938 : p_interactions_offered IN NUMBER Optional
939 Default = NULL
940 Number of interactions offered to agents this minute.
941
942 : p_interactions_answered IN NUMBER Optional
943 Default = NULL
944 Number of interactions answered by agents this minute.
945
946 : p_interactions_transferred IN NUMBER Optional
947 Default = NULL
948 Number of interactions transferred this minute.
949
950 : p_interactions_handled IN NUMBER Optional
951 Default = NULL
952 Number of interactions handled (by agents or automation) this minute.
953
954 : p_interactions_abandoned IN NUMBER Optional
955 Default = NULL
956 Number of interactions abandoned this minute. Technically,
957 abandoned calls are not interactions.
958
959 : p_speed_to_answer_avg IN NUMBER Optional
960 Default = NULL
961 Average speed to answer a call this minute. In seconds.
962 = p_speed_to_answer_total / p_interactions_answered
963
964 : p_speed_to_answer_std_dev IN NUMBER Optional
965 Default = NULL
966 Standard deviation for average speed to answer.
967
968 : p_speed_to_answer_total IN NUMBER Optional
969 Default = NULL
970 Total speed to answer time, in seconds.
971
972 : p_wait_to_abandon_avg IN NUMBER Optional
973 Default = NULL
974 Average wait to abandon a call this minute. In seconds.
975 = p_wait_to_abandon_total / p_interactions_abandoned
976
977 : p_wait_to_abandon_std_dev IN NUMBER Optional
978 Default = NULL
979 Standard deviation for average wait to abandon.
980
981 : p_wait_to_abandon_total IN NUMBER Optional
982 Default = NULL
983 Total wait to abandon, in seconds.
984
985 : p_percent_occupancy_rate IN NUMBER Optional
986 Default = NULL
987 0.0 <= percent <= 1.0
988 = p_total_talk_time / (p_total_talk_time + p_total_idle_time)
989
990 : p_percent_utilization_rate IN NUMBER Optional
991 Default = NULL
992 0.0 <= percent <= 1.0
993 = (p_total_talk_time + p_total_idle_time) / p_total_log_time
994
995 : p_percent_transfer_rate IN NUMBER Optional
996 Default = NULL
997 0.0 <= percent <= 1.0
998 = p_interactions_transferred / p_interactions_answered
999
1000 : p_total_talk_time IN NUMBER Optional
1001 Default = NULL
1002 Total talk time in seconds.
1003
1004 : p_total_hold_time IN NUMBER Optional
1005 Default = NULL
1006 Total hold time in seconds.
1007
1008 : p_total_idle_time IN NUMBER Optional
1009 Default = NULL
1010 Total idle time in seconds.
1011
1012 : p_total_wrap_time IN NUMBER Optional
1013 Default = NULL
1014 Total wrap time in seconds.
1015
1016 : p_total_log_time IN NUMBER Optional
1017 Default = NULL
1018 Total log time in seconds.
1019
1020 Version : Current Version 1.0
1021 Previous Version n/a
1022 Initial Version 1.0
1023
1024 end of comments */
1025
1026 PROCEDURE Multi_Channel_Manager_Input
1027 ( p_api_version IN NUMBER,
1028 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1029 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1030 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1031 x_return_status OUT nocopy VARCHAR2,
1032 x_msg_count OUT nocopy NUMBER,
1033 x_msg_data OUT nocopy VARCHAR2,
1034
1035 p_time IN DATE := NULL,
1036
1037 p_interaction_center_id IN NUMBER := NULL,
1038 p_interaction_center_name IN VARCHAR2 := NULL,
1039 p_interaction_class_id IN NUMBER := NULL,
1040 p_interaction_class_name IN VARCHAR2 := NULL,
1041
1042 p_interactions_received IN NUMBER := NULL,
1043 p_interactions_offered IN NUMBER := NULL,
1044 p_interactions_answered IN NUMBER := NULL,
1045 p_interactions_transferred IN NUMBER := NULL,
1046 p_interactions_handled IN NUMBER := NULL,
1047 p_interactions_abandoned IN NUMBER := NULL,
1048
1049 p_speed_to_answer_avg IN NUMBER := NULL,
1050 p_speed_to_answer_std_dev IN NUMBER := NULL,
1051 p_speed_to_answer_total IN NUMBER := NULL,
1052
1053 p_wait_to_abandon_avg IN NUMBER := NULL,
1054 p_wait_to_abandon_std_dev IN NUMBER := NULL,
1055 p_wait_to_abandon_total IN NUMBER := NULL,
1056
1057 p_percent_occupancy_rate IN NUMBER := NULL,
1058 p_percent_utilization_rate IN NUMBER := NULL,
1059 p_percent_transfer_rate IN NUMBER := NULL,
1060
1061 p_total_talk_time IN NUMBER := NULL,
1062 p_total_hold_time IN NUMBER := NULL,
1063 p_total_idle_time IN NUMBER := NULL,
1064 p_total_wrap_time IN NUMBER := NULL,
1065 p_total_log_time IN NUMBER := NULL
1066 ) IS
1067
1068 l_api_name CONSTANT VARCHAR2(30) := 'Multi_Channel_Manager_Input';
1069 l_api_version CONSTANT NUMBER := 1.0;
1070 l_encoded VARCHAR2(1) := FND_API.G_FALSE;
1071
1072 l_interaction_center_id NUMBER := -1;
1073 l_interaction_class_name VARCHAR2(240) := '';
1074
1075 BEGIN
1076 -- Standard Start of API savepoint
1077 SAVEPOINT MCM_Input_PUB;
1078
1079 -- Standard call to check for call compatibility
1080 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1081 p_api_version,
1082 l_api_name,
1083 G_PKG_NAME)
1084 THEN
1085 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1086 END IF;
1087
1088 -- initialize message list if p_init_msg_list is set to TRUE
1089 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1090 FND_MSG_PUB.initialize;
1091 END IF;
1092
1093 -- Initialize the API return status to success
1094 x_return_status := FND_API.G_RET_STS_SUCCESS;
1095
1096 -- API body
1097
1098 -- Insert record if p_time is given
1099 BEGIN
1100 IF p_time IS NOT NULL
1101 THEN
1102 -- Check p_interaction_center_id for valid value
1103 IF p_interaction_center_id IS NOT NULL
1104 THEN
1105 l_interaction_center_id := p_interaction_center_id;
1106 ELSIF p_interaction_center_name IS NOT NULL
1107 THEN
1108 DECLARE
1109 CURSOR server_group_id_cur (v_interaction_center_name IN VARCHAR2)
1110 IS
1111 SELECT server_group_id
1112 FROM ieo_svr_groups
1113 WHERE UPPER (group_name) = UPPER (v_interaction_center_name)
1114 ORDER BY server_group_id;
1115 BEGIN
1116 OPEN server_group_id_cur (p_interaction_center_name);
1117 FETCH server_group_id_cur INTO l_interaction_center_id;
1118 CLOSE server_group_id_cur;
1119 END;
1120 END IF;
1121
1122 -- Check p_interaction_class_name for valid value
1123 IF p_interaction_class_name IS NOT NULL
1124 THEN
1125 l_interaction_class_name := p_interaction_class_name;
1126 ELSIF p_interaction_class_id IS NOT NULL
1127 THEN
1128 DECLARE
1129 CURSOR classification_cur (v_interaction_class_id IN NUMBER)
1130 IS
1131 SELECT classification
1132 FROM cct_classifications
1133 WHERE classification_id = v_interaction_class_id
1134 ORDER BY classification;
1135 BEGIN
1136 OPEN classification_cur (p_interaction_class_id);
1137 FETCH classification_cur INTO l_interaction_class_name;
1138 CLOSE classification_cur;
1139 END;
1140 END IF;
1141
1142 INSERT INTO bix_server_mcm
1143 (server_mcm_id, minute,
1144 interaction_center_id, interaction_classification,
1145 interactions_received, interactions_offered,
1146 interactions_answered, interactions_transferred,
1147 interactions_handled, interactions_abandoned,
1148 average_speed_to_answer, std_dev_speed_to_answer,
1149 total_speed_to_answer,
1150 average_wait_to_abandoned, std_dev_wait_to_abandoned,
1151 total_wait_to_abandoned,
1152 percent_occupancy_rate, percent_utilization_rate,
1153 percent_transfer_rate,
1154 talk_time, hold_time, idle_time, wrap_time, log_time)
1155 SELECT bix_server_cp_s.nextval, trunc (p_time, 'MI'),
1156 l_interaction_center_id, l_interaction_class_name,
1157 p_interactions_received, p_interactions_offered,
1158 p_interactions_answered, p_interactions_transferred,
1159 p_interactions_handled, p_interactions_abandoned,
1160 p_speed_to_answer_avg, p_speed_to_answer_std_dev,
1161 p_speed_to_answer_total,
1162 p_wait_to_abandon_avg, p_wait_to_abandon_std_dev,
1163 p_wait_to_abandon_total,
1164 p_percent_occupancy_rate, p_percent_utilization_rate,
1165 p_percent_transfer_rate,
1166 p_total_talk_time, p_total_hold_time, p_total_idle_time,
1167 p_total_wrap_time, p_total_log_time
1168 FROM dual;
1169 END IF;
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1173 THEN
1174 FND_MESSAGE.SET_NAME('CCT', 'CCT_SDI_MCM_IN_INSERT_ERROR');
1175 FND_MSG_PUB.Add;
1176 END IF;
1177
1178 RAISE FND_API.G_EXC_ERROR;
1179 END;
1180
1181 -- Signify Success
1182 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1183 THEN
1184 FND_MESSAGE.SET_NAME('CCT', 'CCT_SDI_MCM_IN_RECORD_INSERTED');
1185 FND_MSG_PUB.Add;
1186 END IF;
1187
1188 -- End of API body
1189
1190 -- Standard check of p_commit
1191 IF FND_API.To_Boolean( p_commit ) THEN
1192 COMMIT WORK;
1193 END IF;
1194
1195 -- Standard call to get message count and if count is 1, get message info
1196 FND_MSG_PUB.Count_And_Get
1197 ( p_encoded => l_encoded,
1198 p_count => x_msg_count,
1199 p_data => x_msg_data
1200 );
1201
1202 EXCEPTION
1203 WHEN FND_API.G_EXC_ERROR THEN
1204 ROLLBACK TO MCM_Input_PUB;
1205 x_return_status := FND_API.G_RET_STS_ERROR;
1206
1207 FND_MSG_PUB.Count_And_Get
1208 ( p_encoded => l_encoded,
1209 p_count => x_msg_count,
1210 p_data => x_msg_data
1211 );
1212
1213 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1214 ROLLBACK TO MCM_Input_PUB;
1215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1216
1217 FND_MSG_PUB.Count_And_Get
1218 ( p_encoded => l_encoded,
1219 p_count => x_msg_count,
1220 p_data => x_msg_data
1221 );
1222
1223 WHEN OTHERS THEN
1224 ROLLBACK TO MCM_Input_PUB;
1225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1226
1227 IF FND_MSG_PUB.Check_Msg_Level
1228 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1229
1230 THEN
1231 FND_MSG_PUB.Add_Exc_Msg
1232 ( p_pkg_name => G_PKG_NAME,
1233 p_procedure_name => l_api_name,
1234 p_error_text => 'G_MSG_LVL_UNEXP_ERROR'
1235 );
1236 END IF;
1237
1238 FND_MSG_PUB.Count_And_Get
1239 ( p_encoded => l_encoded,
1240 p_count => x_msg_count,
1241 p_data => x_msg_data
1242 );
1243
1244 END Multi_Channel_Manager_Input;
1245
1246 END CCT_ServerDataInput_PUB;
[Home] [Help]